Excel Bezug auf anderes Tabellenblatt durch Zelllink?
Hallo zusammen,
ich habe da folgende Frage, ich habe mir eine kleine Formel gebastelt, die mir die Summen innerhalb eines gewissen Datumsbereichs auf einem anderen Tabellenblatt einsammelt: Die Formel ist die folgende:
=SUMMEWENNS('2019'!$G:$G;'2019'!$A:$A;">="&DATUM($A22;SPALTE()-1;1);'2019'!$A:$A;"<="&DATUM($A22;SPALTE()-1;28))
Die Formel tut zur Veranschaulichung folgendes auf Tabellenblatt 2019 schauen ob in Spalte A das Datum in dem Bereich liegt und bildet dann die Summe der Spalte G in den betroffenen Bereichen.
Damit ich mir die Formel einfach runter/zur Seite ziehen kann hab ich bei Monat und Jahr mit einem Direktbezug bzw. einfach mit dem Spaltenindex gearbeitet.
Damit ich das ganze aber noch voll automatisieren kann würde ich gerne ebenso das '2019'! durch einen Link auf die Zelle A22, die für das aktuelle Datum genutzt wird austauschen, ist das irgendwie möglich?
Ergänzend noch eine weitere Frage, der maximal Monatstag, in dem Fall im Februar 28 bzw. 29, kann ich den nicht auch irgendwie automatisch generieren?, denn schließlich variiert der von Monat zu Monat und ich müsste ihn in jeder Formel erneut eingeben und im Falle des Februars sogar noch drauf achten ob Schaltjahr oder nicht. Denn wenn ich einfach immer Jahr;Monat;31 eingebe, kommt murks raus, keine Ahnung was dann Excel rechnet, aber dann werden noch weitere Tage zu raten gezogen, die gar nicht im Februar liegen.
Ich bedanke mich schonmal für Eure Inspiration.
MfG Gipfelstürmer
1 Antwort
Das Tabellenblatt für das jeweilige Jahr kannst du mit der Indirekt-Funktion verknüpfen. Also statt z.B. '2019'!$G:$G kannst du INDIREKT("'2019'!$G:$G") benutzen und die 2019 kannst du dann natürlich auch aus einer Zelle ziehen: INDIREKT("'" & A22 & "'!$G:$G")
Bei dem Enddatum würde ich nicht den "<=" Operator benutzen, sondern nur den "<" Operator, dann kannst du nämlich einfach den ersten Tag des nächsten Monats benutzen. Excel hat auch kein Problem damit, wenn du z.B. nach dem "01.13.2022" suchst. Das wird automatisch in "01.01.2023" umgewandelt.
Excel benutzt für Datumswerte intern Kommazahlen. Eine ganze Zahl ist jeweils ein Tag. Also wenn du z.B. HEUTE()+1 rechnest, kommt das Datum von morgen raus. Dementsprechend kannst du z.B. DATUM(2022, 3, 1) -1 rechnen und es kommt der letzte Februartag von 2022 raus.
Allgemein würde ich für sowas vermutlich eher die FILTER-Funktion benutzen und das Ergebnis davon summieren.
Etwa so:
=SUMME(FILTER(INDIREKT("'"&$A22&"'!G:G");MONAT(INDIREKT("'"&$A22&"'!A:A"))=SPALTE()-1))
Ja, das mit dem Tag 0 im Datum geht natürlich auch.
Filter finde ich halt praktisch. Einerseits kriegt man eine Liste zurück (also wenn du das SUMME außenrum weglässt, siehst du genau, welche Werte summiert werden), andererseits hat man halt mehr Freiheiten mit den Filterkriterien. Nicht nur größer, kleiner, gleich, sondern wie in meinem Beispiel auch Monat('2019'!A:A)=1 oder sowas.
Das mit dem Filter schaue ich mir mal an danke ich hab jetzt noch den Tipp mi eingeholt mit Datum(Jahr;Monat+1;0) zu arbeiten damit bekommt man wohl immer den höchsten Tag des Monats, probiere das gerade aus.
Ja an indirekt dachte ich auch schon, aber ich hab immer nur versucht 2019 da reinzubringen anstatt den ganzen Part im nachhinein betrachtet logisch, dass es nicht ging, danke dir schonmal.