Anzahl Übernachtungen je Jahr und Monat berechnen

14.08.2025 16:40:02

Rainerexcel

Ich trage jede Buchung meiner Ferienwohnung in eine Zeile ein, mit Urlaubsbeginn und -ende sowie Anzahl Nächten in Spalten A, B und C.
Ich brauche die Anzahl Nächte je Jahr und Monat und komme bei Buchungen, die Monats- und Jahresgrenzen überschreiten nicht weiter. Würde mich über Tipps freuen. Die Beispieldatei hängt hier an (https://www.herber.de/bbs/user/178605.xlsx).

AW: Anzahl Übernachtungen je Jahr und Monat berechnen

14.08.2025 16:54:45

daniel

Hi Reiner
ersten wäre es nett, wenn du deine Anfrage nicht so unhöflich grußlos hier reinknallen würdest. Manche sind da etwas konservativ und ein höflicher Text kann sich positiv auf die Hilfsbereitschaft auswirken
zweitens wäre es gut, wenn du den Link ohne direktanschließende Zeichen hier einstellen würdest (in deinem Fall die Klammern und der Punkt)
alles was direkt an den Linktext anschließt, interpretiert die Forensoftware als zum Link gehörig und dann funktioniert der Link nicht.
Gruß Daniel

AW: Anzahl Übernachtungen je Jahr und Monat berechnen

14.08.2025 17:44:04

Yal

Hallo Rainer,

Kurz eine Definitionsfrage: ich gehe davon aus, dass eine Übernachtung immer auf dem Abendstag gelegt wird, sodass eine Übernachtung 31.1. zu 1.2. für Januar gilt.
Du musst deine Daten so transformieren, dass für eine Zeitspanne jede einzelne Tag aufgedröselt wird.

Es geht so:
- gehe auf die erste Zelle deiner Liste
- Menü "Einfügen", "Tabelle"
- Menü "Daten", "aus Tabelle/Bereich"
- Du bist im Power Query ("PQ") Editor.
- Markiere die 2 ersten Spalte "von" und "bis",
- Rechtsklick auf dem Spaltenkopf, "Typ ändern", "Datum" auswählen,
- Menü "Spalte hinzufügen", "benutzerdefinierte Spalte",
- benenne die Spalte "Datum" und füge die Formel
= {Number.From([von])..(Number.From([bis])-1)}
ein (Damit wird eine Liste von Tage erstellt. Funktioniert nicht mit Datum, daher Number.From())
- siehe rechts wie diesen neuen Verarbeitungsschritt in der Liste hinzugefügt wird. Man kann den Vorschau für jeden Schritt sehen.
- Rechtsklick auf dieser Spalte "Datum", "andere Spalten entfernen"
- Klick auf dem Doppelpfeil-Symbol, "Auf neue Zeilen ausweiten"
- Rechtsklick auf die Spaltenkopf, "Typ ändern", "Datum"
- Menü "Datei", "Schliessen & laden in...", "Pivottable-Bericht" wählen
- Du bist wieder in Excel klassisch
- Feld "Datum" aus der Pivotfeldliste ins Quadrant "Zeilen" verschieben,
- Es wird erkannt, dass ein Datum ist und Standardaggregation für Datum werden eingeführt. Unnötige zurück in die Feldliste,
- Feld "Datum" ins Quadrant "Werte" verschieben. Es ist kein Zahl, daher wird die Aggregationsform "Anzahl" automatisch gewählt.
Fertig

Falls Du mehr über Power Query erfahren möchtest: https://excelhero.de/power-query/power-query-ganz-einfach-erklaert

VG
Yal

Im Anhang ist...

14.08.2025 23:50:34

Case

Moin, :-)

... eine Möglichkeit (nur Beispiele) mit Power Query: ;-)
https://www.herber.de/bbs/user/178609.xlsx

Servus
Case

und jetzt noch ...

15.08.2025 02:35:26

Uduuh

Hallo,
eine Spalte mit den kumulierten Jahreswerten und Gesamt kumuliert. ;-)

Ich habe übrigens festgestellt, dass du der letzte warst, der 2019 das legendäre Exceltreffen veranstaltet hat. Das nächste sollte bei Beverly sein. Corona hat's getötet.
Schade! Dadurch ist man ist in Gegenden gekommen, in die man freiwillig nie gefahren wäre. ;-)

Gruß aus'm Pott
Udo

Man(n) könnte auch...

16.08.2025 12:25:14

Case

Moin Udo, :-)

... eine Kreuztabelle aus den Daten machen (per Power Query) - das bietet IMHO eine schnelle Übersicht: ;-)
In Power Query kannst du eigentlich alles auswerten - wenn (M)man(n) kann. ;-)

Userbild

Ja - die Burg. Steht immer noch - auch mit dem netten Getränkeautomaten im Keller. ;-)

Servus
Case

AW: Man(n) könnte auch...

16.08.2025 14:36:51

Rainerexcel

Hallo Case, diese Matrix hätte ich gerne. Wie bist Du da hingekommen? Gruss, Rainer.

AW: Man(n) könnte auch...

16.08.2025 17:43:52

Yal

Moin,

In den Fall hat Case wohl in PQ pivotiert, bevor es ausgegeben wird.

Der Weg geht über dieselbe Übung wie meine erste PQ-Lösung, dann Jahr ("=Date.Year([Datum])") und Monat extrahieren und eine Zusatzspalte "Wert" mit überall 1. Spalte "Datum" löschen. Dann Spalte Jahr markieren, Menü "Transformieren", "Spalte pivotieren", die Spalte "Wert" mit Summierung eintragen. Ausgeben, fertig.

VG
Yal

Im Anhang ist...

16.08.2025 23:03:52

Case

Moin Rainer, :-)

... eine Möglichkeit mit deiner Spalte Nächte und ohne diese Spalte: ;-)
https://www.herber.de/bbs/user/178619.xlsx

Servus
Case

AW: Im Anhang ist...

17.08.2025 01:14:06

Yal

Hallo Case,

Jahresumme könnte man auch so erzeugen:
List.Sum(List.Skip(Record.ToList(_), 1))

Die Spaltensumme würde ich als Ergebniszeile einfügen: rechtsklick auf Tabelle, "Tabelle", "Ergebniszeile". Dann kann mit VBA weiterhin auf DataBodyRange ohne diese Tabellenzeile, die eigentlich keine Tabellenzeile ist, extra behandeln zu müssen.

Aber wer solche Summen haben möchte, kann gleich mit Pivottable auswerten.

VG
Yal

Ich atme. Produktiver wird es heute nicht mehr...

17.08.2025 09:51:41

Case

:-)

So - jetzt bin ich wieder...

20.08.2025 10:55:23

Case

Moin Yal, :-)

… Produktiver - deshalb erst jetzt eine kurze Antwort. ;-)

Rainer hat mich gefragt, ob ich ihm meine Datei geben könnte. Habe ich gemacht. ;-)

Wenn du den M-Code anders schreiben würdest, dann antworte doch dem Themenersteller - mich interessiert das nicht. Nirgends in diesem Thema ging es um VBA oder DataBodyRange und ich schrieb, dass es zur schnellen Übersicht dienen kann ;-)

Vielleicht könntest du in Zukunft darauf verzichten mir zu schreiben? Danke. ;-)

Mein letztes (vielleicht vorletztes) Thema für dich. ;-)

Servus
Case

AW: So - jetzt bin ich wieder...

20.08.2025 13:07:30

Yal

Hallo Case,

es ist mir bewusst, dass die Stimmung mit dem man etwas schreibt nie interpretiert wird, wie es geschrieben wurde. Ich bedaure sehr, dass es bei Dir nicht so gut gemeint angekommen ist, wie ich es geschrieben habe.

Mir interessiert sehr den Ausstauch mit anderen Power Query Begeisterten und wenn ich etwas entdecke, das ich gut finde, kommuniziere es eben. Es war keinesfalls einen "Du hast es falsch oder nicht gut genug gemacht" gemeint. Noch einmal Sorry, wenn es so angekommen ist.

Aber ich werde deinen Wunsch nachgehen, meine Austausch über Power Query mit Dir zu reduzieren (schade), es sei denn, Du lässt es ausdrücklich zu.

Danke, dass Du mir deine Meinung geteilt hast. Sei zuversichtlich, dass ich Dir dafür keinesfalls verärgert oder nachtragend bin.

VG
Yal

AW: Man(n) könnte auch...

16.08.2025 18:47:53

Rainerexcel

Hi Yal, vielen Dank, aber ich brauche mehr Text. Sorry. Was genau soll in wann in PQ machen? Beste Grüsse, Rainer.

AW: Man(n) könnte auch...

16.08.2025 19:03:00

Yal

Hallo Rainer,

es fügt sich an der Beschreibung in
https://www.herber.de/forum/archiv/2016to2020/2017349_Anzahl_Uebernachtungen_je_Jahr_und_Monat_berechnen.html#2017352
Und zwar vor dem "Schließen & laden".
In den Stand hat man nur eine Spalte "Datum", darauf werden 3 benutzerdefinierte Spalten hinzugefügt, um Jahr und Monat aus den Datum zu isolieren, und einen 1 für jeden Eintrag (Spaltename "Wert").
Wir löschen "Datum" und pivotieren die Monaten. Die Werte werden dann in Spalten von Januar bis Dezember summiert, als um Zeilenüberschrift bleiben die Jahre.
Dann "Schließen & laden". Wenn das vorige Ergebnis bereits als Pivottable ausgegeben wurde, dann mit "Laden in..." (Rechtsklick auf die Abfrageliste in Excel) zu einer Tabelle-Ausgabe wechseln.

VG
Yal

AW: Man(n) könnte auch...

16.08.2025 21:25:11

Rainerexcel

Hallo Yal,

alles soweit gemacht nur beim vorletzten Schritt (pivotieren der Monate) erhalte ich folgende Fehlermeldung, wohl weil ich auch Monate mit 0 Nächten habe...

Expression.Error: Der Operator ".." kann nicht auf die Typen "Null" und "Null" angewendet werden.
Details:
Operator=..
Left=
Right=

Kannst Du bitte nochmal schauen? Vielen Dank, Rainer.

null Tage

17.08.2025 00:42:31

Yal

Hallo Rainer,

es ist eine Konstellation, die in deiner Testdaten nicht vorhanden war. Es entsteht wenn "von" und "bis" auf dieselbe Tag gelegt sind.
Die Erstellung einer Liste mit {Number.From([von]).. Number.From([bis])-1} führt zu einer null-Liste und anschliessend einen Eintrag "null".

Diese Einträge werden in dem Schritt "Erweiterte Tage" erzeugt (vorausgesetzt, die List-Spalte "Tage" heisst).
Diesen Schritt aktivieren, im Kopf der Spalte "Tage" auf dem Filter gehen und den Haken bei "(NULL)" rausnehmen. Dann sind alle Einträge mit null weggefiltert.

VG
Yal

AW: null Tage

17.08.2025 16:28:41

Rainerexcel

Moin, vielen Dank, habe ich alles so gemacht. Die "0" kamen durch 2 leere Zeilen ganz zum Schluss. Habe ich gelöscht.
Jetzt sieht in PQ alles richtig aus (s. Anhang), nur wenn ich Schliessen & Laden in Pivot Tabelle gehe, bekomme ich die Jahre als Spalten, kein Feld "Datum" und keine "Wert" oder Fehlermeldung. Wie bekomme ich diesen letzten Schritt jetzt wieder sauber hin? Gruss, Rainer.

Userbild

AW: null Tage

17.08.2025 16:48:21

Yal

Hallo Rainer,

es hat vielleicht etwas in meiner Erklärung gefällt:
Entweder die erste Variante und dann Auswertung in Pivottable,
oder die zweite und Ausgabe in einer fixen Tabelle.

Bei Pivottable ist es wichtig, dass nur ein Wertfeld vorhanden ist (oder zumindest so wenig wie möglich), also Monat in einer Spalte und Werte in einer separaten Spalte.

Beim fertigen, nicht änderbare (aber aktualisierbare) Tabelle ist jeder Monat eine Wertspalte, was für Pivottable-Gestaltung schwierig macht.

VG
Yal

AW: null Tage

17.08.2025 17:33:15

Rainerexcel

Hallo Yal,

darf ich Dich bitten, mir die Schritte für eine aktualisierbare Lösung (Pivot oder ähnliches) mit den Jahren in Spalten und den Monaten in den Zeilen und den Nächten als Werten zu schreiben - ich bin etwas verwirrt. Tausend Dank, Gruss, Rainer.

AW: null Tage

18.08.2025 16:04:33

Yal

Hallo Rainer,

nehme einfach die Beschreibung wie in https://www.herber.de/forum/archiv/2016to2020/2017349_Anzahl_Uebernachtungen_je_Jahr_und_Monat_berechnen.html#2017352
Es geht darin darum, die Zahlen so zu vorbereiten, dass diese mit einer Pivottable auswertbar werden.

Die Pivotgestaltung sieht dann so aus:
Wenn Du ein Feld, das als Datum geführt wird in das Quadrant "Zeilen" verschiebt, wird eine automatische Gruppierung stattfinden: Jahre, Quartal, Monat.
Verschiebe das entstehende "Monate(Datum)" ins Quadrant "Spalten", "Quartal(Datum)" zurück in die Feldliste und anschliessend "Datum" ins "Werte"-Quadrant, woraus "Anzahl von Datum" gemacht wird.

Es gibt keine einfachere Lösung. Ob "bessere" ist Ansichtssache. Ich mag einfach die Flexibilität von Pivottable. Es geht um Daten zu analysieren, nicht um Schönheitsbastelei.

Falls Du komplett neu aufbaust auf Basis der Beschriebung oben, füge noch dazu den Schritt zum Ausfiltern der null-Tagen rein, um zwar nach der Ausweitung der Datum List in je eine Zeile.

VG
Yal

AW: null Tage

18.08.2025 16:42:57

Rainerexcel

Habe es gelöst und eine aktualisierbare Tabelle gewählt. Klappt super und vielen Dank für die Hilfe und Tipps. Besten Gruss, Rainer.

Monate

17.08.2025 00:51:05

Yal

Die Extraktion der Monate führt zu Monatszahl: 1, 2, 3, ...

Schöner ist die Spalte Datum zu markieren, Menü "Spalte hinzufügen", "Datum", "Monat", "Name des Monats" (muss vor der Pivotierung erfolgen)

Da aber dein erster Datum im Dezember ist, ist die Reihenfolge krum. Auf dem Spaltenkopf von "Dezember" rechtsklicken, "Verschieben", "Ans Ende".
Dann anschliessend die "Formel" anschauen und die richtige Reihenfolge prüfen und notfalls direkt eintippen (Achtung: case sensitiv)

VG
Yal

AW: Anzahl Übernachtungen je Jahr und Monat berechnen

14.08.2025 18:30:24

Rainerexcel

Hallo Yal, das klappt hervorragend und ich was über Power Query gelernt. Vielen Dank.

tolle Lösung

14.08.2025 19:07:36

Uduuh

Hallo,
musste ich natürlich auch ausprobieren.

Ich war bei einer Formellösung mit Hilfstabelle
Vermietung!D2: =--TEXT((SEQUENZ(;B2-A2;A2;1));"JJJJMM") und runter kopieren.
Neues Blatt:
A1: Monat; B1: Anzahl
A2: =EINDEUTIG(--TEXT(SEQUENZ(MAX(Vermietung!B:B)-MIN(Vermietung!A:A)+1;;MIN(Vermietung!A:A);1);"JJJJMM"))
B2: =ZÄHLENWENN(Vermietung!D:ZZ;A2#)

Geht vllt. auch noch kompakter.

Gruß aus'm Pott
Udo

AW: tolle Lösung

14.08.2025 19:21:51

daniel

Hi

einfache Formellösung so:

=LET(x;E1;y;F1;
a;$A$2:$A$668;
b;$B$2:$B$668;
c;WENN(b&gty;y;b)-WENN(a&ltx;x;a);
SUMME(WENN(c&gt0;c;0)))

mit dem Startdatum des Auswertebereichs, in E1 und dem Enddatum des Auswertebereichs in F1

AW: tolle Lösung

15.08.2025 13:59:49

Rainerexcel

Moin Udo,

mir gefällt Deine Lösung, weil ich sie dynamisch auch für zukünftige Buchungen nutzen kann (im Gegensatz zu Power Query). Ich habe Deine Formel =--TEXT((SEQUENZ(;B2-A2;A2;1));"JJJJMM") als Matrixformel eingegeben und die beiden anderen wie beschrieben auch. Allerdings komme ich nicht auf die korrekte Anzahl Nächte - auch die Summe stimmt nicht. Ich habe die Datei hier angehangen. Kannst Du bitte nochmal drüber schauen?

https://www.herber.de/bbs/user/178611.xlsx
beste Grüsse, Rainer (übrigens nicht der aus 2019...)

AW: tolle Lösung

15.08.2025 14:26:02

Yal

Moin,

"dynamisch auch für zukünftige Buchungen nutzen kann (im Gegensatz zu Power Query)"

Ich kann diesen Satz nicht so stehen lassen ;-)
Eine PQ-Abfrage ist nicht eine einmalige Transformation, sondern eine Programmierung, die jederzeit neulaufen und die Daten aktualisieren kann: Rechtsklick auf das Ergebnis-Ausgabe (Tabelle oder Pivot) und "aktualisieren" anstoßen.

Man kann PQ auf einer Tabelle in eigener Exceldatei setzen, aber auch auf externe Dateien oder Datenbanken.
Die Frage ist, wie diese "zukünftige Buchungen" kommen:
- werden sie am Ende der Tabelle hinzugefügt, erweitert sich die Tabelle automatisch und die Aktualisierung nimmt die Buchungen auf alle Fälle mit.
- werde diese in separaten Datei übermittelt, z.B. in einer CSV-Datei als Export eines anderen Datensystem, sollte man die Power Query Abfrage auf diese eine Datei (deren neue Version dieselben Namen haben soll) oder auf alle Dateien in einem Verzeichnis. Siehe Excelheros Tutorial.
- werden diese aus einer Datenbank gelesen, könnte es sich lohnen, die PQ-Abfrage direkt auf die Datenbank zu setzen (PQ hat übrigens eine Optimierungsmechanismus, das die Abfrage so umgestaltet, dass die meiste Arbeit auf dem DB-Engine abgelagert wird und den Datentransfer maximal reduziert wird. Eine mit PQ simulierte "select * from tab where Month(Date) = 1", was in PQ mehrere Schritten bedeutet, wird auf dem DB gefiltert und nur die relevante Daten geliefert.)

Man kann auch jeden Monat prüfen, ob die Formel oder deren Eingangsparameter vollständig sind. Das Prüfen, ob alle Formel in einem Bereich weiterhin konsistent sind, hat sich mit den Spill-Formel stark reduziert, aber nicht auf null.
Dann die Übergabe eines Formel-Gewerkes an Kunden oder Vertretung (Praktikant?) ist ein großer Spass: "ich habe was angepasst (auf das Original) und es tut nicht mehr"...

VG
Yal

AW: tolle Lösung

15.08.2025 16:06:02

Uduuh

Hallo,
keine Matrixformel! Einfach so eingeben. Matrix kann XL365 von sich aus.

https://www.herber.de/bbs/user/178613.xlsx

Gruß aus'm Pott
Udo

AW: tolle Lösung

16.08.2025 01:27:22

Rainerexcel

Guten Abend,

wenn ich die Formel =--TEXT((SEQUENZ(;B2-A2;A2;1));"JJJJMM") in Vermietung!D2 reinkopiere erhalte ich als Fehlermeldung "Überlauf!" und wenn ich es runterkopiere, werden auch keine Werte in den Spalten rechts davon angezeigt so wie in Deinem Beispiel. Was mache ich falsch? Besten Gruss, Rainer.

AW: tolle Lösung

16.08.2025 01:35:25

BoskoBiati

Hi,

alle Zellen rechts von der Formel leeren!

Gruß

Edgar

AW: tolle Lösung

16.08.2025 01:53:19

Uduuh

Hallo,
wenn du die Formel in D2 schreibst wird die Tabelle automatisch auf Spalte D erweitert. Das musst du im Tabellenentwurf wieder auf A1:C668 korrigieren.
Oder du schreibst die Formel in E2. Dann wird die Tabelle wegen der leeren Spalte nicht erweitert.

Gruß aus'm Pott
Udo

AW: tolle Lösung

16.08.2025 11:00:36

Rainerexcel

Moin Udo,

mein Fehler. Die Tabelle mit Anreise- und Abreisedaten war noch als Tabelle formatiert (von der Power Query Übung). Alles hat nun geklappt. Vielen Dank.

Profis googlen so etwas... owT

16.08.2025 01:54:52

SF

AW: Anzahl Übernachtungen je Jahr und Monat berechnen

14.08.2025 17:23:44

Rainerexcel

hier der korrekte Link: https://www.herber.de/bbs/user/178605.xlsx zur Beispieldatei

Anzahl Übernachtungen je Jahr und Monat berechnen

Forumthread: Anzahl Übernachtungen je Jahr und Monat berechnen

34
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Anzahl Übernachtungen je Jahr und Monat berechnen

Forumthread: Anzahl Übernachtungen je Jahr und Monat berechnen

34 Beiträge zum Forumthread Beiträge zu diesem Forumthread

34
Beiträge zum Forumthread

Beiträge zu diesem Forumthread