Dynamische Auswertung Gäste Hotel/Monat

[NickL]

Hallo liebe Gemeinschaft,

ich richte mich heute mit einer dynamischen Übersicht an euch, die mich selbst vor eine große Herausforderung stellt. 

Freue mich auf eure Unterstützung und gebe gern auch etwas zurück :)

Anbei meine Exceldatei mit der Bezeichnung "Hotels".

Wichtig ist mir Sheet "Evaluation".

B1 und D1 würde ich gern flexibel füllen wollen - Monat / Hotel

Zeile 4 + 5 zeigt dazu jetzt das händisch erfasste Resultat an. Die gelb-markierten Zeilen sollen jedoch dynamisch sein, sodass sich weitere Zeilen automatisch öffnen, wenn mehr Gäste als nur 2 zur jeweiligen Zeit im jeweiligen Hotel sind.

Zeile 3 soll fixiert bleiben.

Zeile 6 soll je nach Anzahl der Gäste nach unten wandern und immer die Gesamtsumme anzeigen.

Vielen Dank im Voraus und ein schönes Wochenende wünsche ich euch!

Nick

[WillWissen]

Hi Nick,

formatiere deine Liste als Tabelle (reinklicken und STRG-L oder -T drücken) und gib im sich öffnenden Reiter "Tabellentools" an, dass du eine Ergebniszeile willst. So sieht es dann aus:

Arbeitsblatt mit dem Namen 'Evaluation'
	B	C	D	E	F	G
1	June	Hotel	Hotel2
2
3	Arrival	Departure	Overnights	Room	Price	Total
4	13.06.2020	30.06.2020	17	SR	45,00 €	765,00 €
5	19.06.2020	21.06.2020	2	DR	55,00 €	110,00 €
6	22.06.2020	07.07.2020	15	Königssuite	785,00 €	11.775,00 €
7	Ergebnis					12.650,00 €

Zelle	Formel
D4	=WENNFEHLER(DATEDIF(B4;C4;"d");"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Innerhalb der Tabelle kannst du dich jetzt ganz einfach mit der Tab-Taste vorwärts bewegen. Sobald du (zur Demonstration grün hinterlegt) in der letzten Zelle bist und erneut die Tab-Taste bemühst, fügt Excel automatisch eine neue Zeile an, übernimmt Formeln und Formatierung und schiebt die Ergebniszeile weiter.

Btw, ich habe in deiner DATEDIF()-Formel noch ein WENFEHLER() davor gepackt (mich hat der Hinweis #ZAHL! genervt, solange nicht alles ausgefüllt war).

[LCohen]

D4: =MAX(B4:C4;HEUTE())-B4

[NickL]

Vielen Dank Günter und LCohen. Ein Stück bin ich weitergekommen aber leider noch nicht ans Ziel gelangt. Siehe Anhang.

1. Generell ist es mir nicht gelungen, alle Gäste im Juni im Hotel2 anzuzeigen. Die Formel bringt immer wieder dasselbe Ergebnis = Person AB. Ich möchte aber gern alle Gäste auflisten, die im Juni aus dem Hotel2 abreisen. D.h. es fehlt noch AI - siehe Import. 

2. Finde ich keine geeignete Formel, die mir den Preis im Juni / Hotel2 / Single room = 45 wiedergibt.

Wäre über eine weiterführende Unterstützung / Hilfe sehr dankbar :)

Grüße,

Nick

[WillWissen]

Hi Nick,

bis auf den Preis (muss mal sehen, was mir dazu einfällt ) wäre das mein Vorschlag:

Arbeitsblatt mit dem Namen 'Evaluation'
	A	B	C	D	E	F	G
2
3	Name	Arrival	Departure	Overnights	Room	Price	Total
4	HH	17.05.2020	23.05.2020	6	DR		0
5	KD	05.05.2020	15.05.2020	10	DR		0
6							#WERT!
7							#WERT!
8							#WERT!
9							#WERT!
10							#WERT!
11	Total						#WERT!

Zelle	Formel
A4	=WENNFEHLER(INDEX(Import!B:B;AGGREGAT(15;6;ZEILE(Import!$B$1:$B$15)/(Import!$A$1:$A$15=Evaluation!$B$1)/(Import!$E$1:$E$15=$D$1);ZEILE(A1)));"")
B4	=WENNFEHLER(SVERWEIS([@Name];Import!B:C;2;FALSCH);"")
C4	=WENNFEHLER(SVERWEIS([@Name];Import!B:D;3;FALSCH);"")
D4	=WENNFEHLER(DATEDIF(B4;C4;"d");"")
E4	=WENNFEHLER(SVERWEIS([@Name];Import!B:F;5;FALSCH);"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[NickL]

Hi Günter,

die Formel in A4 sieht vielversprechend aus aber funktioniert leider bei mir nicht. Ich denke, dass ich sie nicht funktioniert, da ich nicht weiß, wie ich sie 100% interpretieren und ggf. anpassen kann. Magst du mir da vielleicht eine kurze Erläuterung zu geben? :)

Könnten wir den noch "offenen" Preis über einen sverweis mit mehrere Kriterien lösen?

Vielen Dank noch einmal im Voraus!

Nick

[WillWissen]

Hi Nick,

du hast die Formeln nicht 1:1 übernommen.

Fangen wir mal beim SVERWEIS() an. Das schreibst du:

=WENNFEHLER(SVERWEIS([@Name];Import!B:D;3;'FALSCH');"")

Lass die Hochkommas weg oder schreibe eine 0, dann bekommst du statt #NAME! das richtige Ergebnis.

INDEX/AGGREGAT() passt bei mir; ich weiß leider nicht, was du in deiner Originaldatei drin hast.

Arbeitsblatt mit dem Namen 'Evaluation'
	A	B	C
3	Name	Arrival	Departure
4	AB	13.06.2020	#NAME?
5	AI	19.06.2020	#NAME?
6			#NAME?

Zelle	Formel
A4	=WENNFEHLER(INDEX(Import!B:B;AGGREGAT(15;6;ZEILE(Import!$B$1:$B$15)/(Import!$A$1:$A$15=Evaluation!$B$1)/(Import!$E$1:$E$15=$D$1);ZEILE(A1)));"")
B4	=WENNFEHLER(SVERWEIS([@Name];Import!B:C;2;0);"")
C4	=WENNFEHLER(SVERWEIS([@Name];Import!B:D;3;'FALSCH');"")
D4	=WENNFEHLER(DATEDIF(B4;C4;"d");"")
E4	=WENNFEHLER(SVERWEIS([@Name];Import!B:F;5;'FALSCH');"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Sobald die Daten richtig geschrieben werden, verschwindet die Meldung auch bei der DATEDIF()-Fkt.

Um mit einem SVERWEIS() die Preise zu holen, müssen die Tabellen im Blatt Prices umgebaut werden. Kreuztabellen sind kaum auswertbar. Ich werde aber vermutlich heute nicht mehr dazu kommen - bekomme noch Besuch (wer weiß, wie lange der bleibt. )

[NickL]

Danke für die Anmerkungen Günter, jetzt scheint alles zu funktionieren. Ich musste die Formeln noch ins Englische übersetzen.

Prices habe ich jetzt anders angeordnet und versucht mit der englischen Version folgender Formel einen sverweis zu erstellen, mit mehrere Kriterien. Leider funktioniert auch das nicht nach einigen Versuchen ;/

[b]=SVERWEIS(F5&G5&H5;WAHL({1.2.3};A:A&B:B&C:C;D:D);2;0)[/b]

Im Anhang sende ich meine letzte Version. Vielen Dank schon mal für alles!!!

Nick

[LCohen]

Evaluation!F4: =INDEX(Prices!C:F;VERGLEICH(D$1&B$1;Prices!A$1:A$99&Prices!B$1:B$99;);2+2*([@Room]="DR"))

[NickL]

Danke LCohen das scheint sehr gut zu funktionieren. Gibt es denn auch eine Möglichkeit, dass ich aus den Zellen F7-32 und G7-32 die Werte bzw. Fehlermeldungen rauskriege, sodass vor allem ein Gesamtbetrag errechnet wird?

Vielen lieben Dank!