Dynamische Tabelle mit SummeWenn und Bereich verschieben funktioniert nicht

[FCA1907]

Hallo,

ich versuche mich schon länger an der angefügten Tabelle.
Es handelt sich dabei um die Belegungszahlen eines Hotels nach Datum dich ich gerne dynamisch, je nach Angabe im Drop- Down Menü, auswerten möchte.
Ziel ist es z.B für das Feld D17 die Summe der jeweils belegten Zimmer für alle Montage im angegen Zeitraum zu erhalten.
Meine bisheriger Zwischenerfolg ist die Formel

=SUMMEWENN(BEREICH.VERSCHIEBEN($C$4:$ND$8;0;VERGLEICH($N$15;$E$3:$ND$3;0)-1;1;VERGLEICH($N$16;$E$3:$ND$3;0)-VERGLEICH($N$15;$E$3:$ND$3;0)+10);D15;$C$8:$ND$8)

Diese funktioniert allerdings nur wenn ich die Angabe bei VON auf dem 2-Montag belassen. Die BIS Werte hingegen kann ich beliebig verschieben und erhalte richtige Ergebnisse.

Hat jemand eine Idee wo der Fehler liegt, sodass ich auch bei einer anderen VON Angabe richtige Werte erhalte?

VG


  Hotelstatistik.xlsx (Größe: 17,58 KB / Downloads: 5)

[steve1da]

Hola,

so?

Code:

=SUMMENPRODUKT(($E$4:$AM$4=D15)*($E$8:$AM$8)*(SPALTE($E$1:$AM$1)>=VERGLEICH($N$15;$E$3:$AM$3;0))*(SPALTE($E$1:$AM$1)<=VERGLEICH($N$16;$E$3:$AM$3;0)))

Gruß,
steve1da

[FCA1907]

Hola,

so?

Code:

=SUMMENPRODUKT(($E$4:$AM$4=D15)*($E$8:$AM$8)*(SPALTE($E$1:$AM$1)>=VERGLEICH($N$15;$E$3:$AM$3;0))*(SPALTE($E$1:$AM$1)<=VERGLEICH($N$16;$E$3:$AM$3;0)))

Gruß,
steve1da

Leider nein.

Vielleicht noch einmal zur Klarstellung: Ich möchte die Summe der belegte Zimmer im angebenen Zeitraum für den jeweiligen Wochentag wissen.
Für den Zeitraum 2-Montag bis 4- Sonntag habe ich ich folgende Werte

2-Montag: 37 belegte Zimmer
3-Montag: 30 belegte Zimmer
4-Montag: 81 belegte Zimmer

In der Tabelle soll dann die Summe daraus erscheinen, also 148.

[steve1da]

Hola,

4-Montag liegt aber nach 4-Sonntag. Warum ist der dann noch in der Auswertung mit drin?

Gruß,
steve1da

[Jockel]

Hallo ich habe eine längere Formel und ein anderes Ergebnis..:

PHP-Code:

=SUMMEWENN(INDEX($E$4:$AM$4;;VERGLEICH($N$15;$E$3:$AM$3;0)):INDEX($E$8:$AM$8;;VERGLEICH($N$16;$E$3:$AM$3;0));D15;INDEX($E$8:$AM$8;;VERGLEICH($N$15;$E$3:$AM$3;0)):INDEX($E$8:$AM$8;;VERGLEICH($N$16;$E$3:$AM$3;0))) 


[Jockel]

Leider nein.

...

In der Tabelle soll dann die Summe daraus erscheinen, also 148.

Hallo, aber das kommt bei steve1da's Formel doch heraus...!!!!

Arbeitsblatt mit dem Namen 'Tabelle2'
	D
22	148

Zelle	Formel
D22	=SUMMENPRODUKT(($E$4:$AM$4=D15)*($E$8:$AM$8)*(SPALTE($E$1:$AM$1)>=VERGLEICH($N$15;$E$3:$AM$3;0))*(SPALTE($E$1:$AM$1)<=VERGLEICH($N$16;$E$3:$AM$3;0)))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

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

[Jockel]

Hallo, ich hatte die Aufgabe so verstanden und mit Power Query auf Richtigkeit überprüft..:

Arbeitsblatt mit dem Namen 'Tabelle2'
	C	D	E	F	G	H	I	J	K
14	Auswertung nach Wochentagen
15			Montag	Dienstag	Mittwoch	Donnerstag	Freitag	Samstag	Sonntag
16
17	2017: Belegte Zimmer		148	180	200	134	77	100	49
18	2017: Ø Belegte Zimmer in Prozent
19
20
21	Name des Tags	bel. Zimmer im ZR
22	Montag	148
23	Dienstag	180
24	Mittwoch	200
25	Donnerstag	134
26	Freitag	77
27	Samstag	100
28	Sonntag	49

Zelle	Formel
E17	=SUMMEWENN(INDEX($F$4:$AN$4;;VERGLEICH($O$15;$F$3:$AN$3;0)):INDEX($F$8:$AN$8;;VERGLEICH($O$16;$F$3:$AN$3;0));E15;INDEX($F$8:$AN$8;;VERGLEICH($O$15;$F$3:$AN$3;0)):INDEX($F$8:$AN$8;;VERGLEICH($O$16;$F$3:$AN$3;0)))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

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

[FCA1907]

Hallo, ich hatte die Aufgabe so verstanden und mit Power Query auf Richtigkeit überprüft..:

Arbeitsblatt mit dem Namen 'Tabelle2'
	C	D	E	F	G	H	I	J	K
14	Auswertung nach Wochentagen
15			Montag	Dienstag	Mittwoch	Donnerstag	Freitag	Samstag	Sonntag
16
17	2017: Belegte Zimmer		148	180	200	134	77	100	49
18	2017: Ø Belegte Zimmer in Prozent
19
20
21	Name des Tags	bel. Zimmer im ZR
22	Montag	148
23	Dienstag	180
24	Mittwoch	200
25	Donnerstag	134
26	Freitag	77
27	Samstag	100
28	Sonntag	49

Zelle	Formel
E17	=SUMMEWENN(INDEX($F$4:$AN$4;;VERGLEICH($O$15;$F$3:$AN$3;0)):INDEX($F$8:$AN$8;;VERGLEICH($O$16;$F$3:$AN$3;0));E15;INDEX($F$8:$AN$8;;VERGLEICH($O$15;$F$3:$AN$3;0)):INDEX($F$8:$AN$8;;VERGLEICH($O$16;$F$3:$AN$3;0)))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

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

Vielen Dank , das hat bei Anwendung auf den vollständigen Datensatz einwandfrei funktioniert. Nun kann ich die Daten problemlos auch von z.B. Mittwoch- Dienstag nach beliebigen Kalenderwochen usw. auswerten.

Eine weitere Frage hätte ich noch zum dynamischen Diagramm.
Die nun verwendeten Daten werden automatisch auch in einem Diagramm dargestellt. Je nachdem wie viele Kalenderwochen ich auswähle fällt der Balken für die Summe der Anzahl der belegten Betten mal größer und kleiner aus. Gibt es denn auch die Möglichkeit die Y-Achsen Legende automatisch an die Daten anzupassen?

Wenn ich die Werte für ein ganzes Jahr nehme muss die Achse schleißlich anders beschriftet werden wie nur für zwei Wochen.



VG

---

Vielen Dank , das hat bei Anwendung auf den vollständigen Datensatz einwandfrei funktioniert. Nun kann ich die Daten problemlos auch von z.B. Mittwoch- Dienstag nach beliebigen Kalenderwochen usw. auswerten.

Eine weitere Frage hätte ich noch zum dynamischen Diagramm.
Die nun verwendeten Daten werden automatisch auch in einem Diagramm dargestellt. Je nachdem wie viele Kalenderwochen ich auswähle fällt der Balken für die Summe der Anzahl der belegten Betten mal größer und kleiner aus. Gibt es denn auch die Möglichkeit die Y-Achsen Legende automatisch an die Daten anzupassen?

Wenn ich die Werte für ein ganzes Jahr nehme muss die Achse schleißlich anders beschriftet werden wie nur für zwei Wochen.



VG

Hat sich erledigt. Einfach in den Einstellungen der Achsenformatierung die Maximumgrenze auf Auto stellen