Tipps und Tricks rund um Excel

A	B	C	D	E	F	G	H	I/J	K
L	M	N	O	P/Q	R	S	T	U-W	X-Z

 

Wenn Uhrzeit größer als ...

In einer Tabelle werden Seminare erfasst. Es gibt Seminare, die um 09:00 Uhr beginnen und dann immer ganztägig stattfinden; andere Seminare beginnen um 14:00 Uhr und sind dann halbtägig. Die Seminarräume kosten für einen halben Tag weniger als für einen ganzen.

In Spalte B soll jeweils errechnet werden:

Wenn der Beginn nach 13:00 Uhr ist, dann verwende den Preis aus D2, sonst aus E2:

Leider kann man mit WENN nicht einfach abfragen, ob A2>13:00 ist. Das endet in einem Fehler. Excel "sieht" die Uhrzeit nicht, wie bereits in Uhrzeiten in dezimaler Schreibweise beschrieben. Auch für diese Abfrage brauchen wir die Multiplikation mit 24:

=WENN(A2*24>13;D2;E2)

Und für das korrekte Kopieren nach unten, brauchen wir den absoluten Bezug:

=WENN(A2*24>13;$D$2;$E$2)

Noch einen Schritt weiter, geht die Abfrage, wenn der Preis nicht nur nach halb- und ganztags unterschieden werden muss, sondern auch nach Teilnehmeranzahl.

Von 0 bis 6 Teilnehmern kosten die Räume halbtags 380 € und ganztags 480 €. Ab 7 bis 15 Teilnehmer sind es 420 € bzw. 540 € und ab 16 Teilnehmern sind es 460/580 €.
In Spalte C wird mit dem SVERWEIS der Preis in Abhängigkeit von Teilnehmerzahl und Beginnzeit berechnet.

Die Formel lautet für C2:

=SVERWEIS(A2;$F$1:$H$4;WENN(B2*24>13;2;3);WAHR)

Zur Erklärung:

=SVERWEIS(A2;	Suche nach der Teilnehmerzahl aus A2
$F$1:$H$4;	in der Matrix in Zelle F1 bis H4
WENN(B2*24>13;2;	wenn die Uhrzeit in B2 größer als 13 ist, dann verwende Spalte 2
3);	ansonsten verwende den Wert aus Spalte 3
WAHR)	WAHR sorgt dafür, die Matrix als "von - bis" verstanden wird

Das letzte Argument muss WAHR sein - sonst werden nur exakte Teilnehmerzahlen gesucht. Hier brauchen wir aber Werte für alle Teilnehmer von 1 bis 6, von 7 bis 15 und darüber.

 

20.10.2014