Microsoft Excel

Herbers Excel/VBA-Archiv

Formel-Fehler für Zuordnung Tage zu Jahr

Betrifft: Formel-Fehler für Zuordnung Tage zu Jahr von: Ricarda
Geschrieben am: 05.11.2020 11:48:02

Hallo zusammen,

ich möchte die Anzahl der Tage in einem Zeitraum bei der entsprechenden, dazu gehörigen Jahreszahl anzeigen lassen. Anbei meine erstellte Liste:https://www.herber.de/bbs/user/141332.xlsx.
Allerdings habe ich wohl einen Fehler in der Formel, da mir anstelle der Anzahl einfach nur eine 0 angezeigt wird.

Kann mir da vielleicht jemand helfen? Das Problem ist wahrscheinlich recht banal, aber ich komme nicht weiter.

Danke im Voraus. :)

Betrifft: AW: Formel-Fehler für Zuordnung Tage zu Jahr
von: MCO
Geschrieben am: 05.11.2020 12:15:10

Hallo Ricarda!

An sich ist alles richtig.
Allerdings hast du als Vergleichswerte JAHR(D3) angegeben, in D3 steht aber kein Datum sondern der Zahlenwert 2021. Damit kommt dann 1905 als Vergleichsjahr raus und das Ergebnis ist 0

Richiger sieht es so aus:




-BCDEF
3BeginnEnde202120222023
401.01.2105.10.223652780
508.11.2228.06.23054179



-BCDEF
3BeginnEnde202120222023
44419744839=SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($B4&":"&$C4)))=D$3)*1)=SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($B4&":"&$C4)))=E$3)*1)=SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($B4&":"&$C4)))=F$3)*1)
54487345105=SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($B5&":"&$C5)))=D$3)*1)=SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($B5&":"&$C5)))=E$3)*1)=SUMMENPRODUKT((JAHR(ZEILE(INDIREKT($B5&":"&$C5)))=F$3)*1)


Viel Erfolg

Gruß, MCO

Betrifft: AW: etwas einfacher mit INDEX() ...
von: neopa C
Geschrieben am: 05.11.2020 12:20:26

Hallo Ricarda,

... in D4: =SUMMENPRODUKT(--(JAHR(ZEILE(INDEX(A:A;$B4):INDEX(A:A;$C4)))=D$3)) und Formel nach rechts und unten ziehend kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: etwas einfacher mit INDEX() ...
von: Günther
Geschrieben am: 05.11.2020 12:30:22

Moin,
geht in deiner Version (natürlich) auch mit Power Query… Muster-Lösung auf Anfrage.
 
Gruß
Günther  |  mein Excel-Blog

Betrifft: AW: etwas einfacher mit INDEX() ...
von: Ricarda
Geschrieben am: 05.11.2020 12:45:37

Hallo Werner,

danke dafür! Es hat funktioniert. Allerdings verstehe ich nicht ganz, warum der Teil "A:A" etc. in der Formel ist. Was macht das?
Ich glaube nämlich, dass mir das zum Verhängnis wird, wenn ich eine neue Spalte einfüge.

Betrifft: AW: zu Deiner Zusatzfrage ...
von: neopa C
Geschrieben am: 05.11.2020 13:02:48

Hallo Ricarda,

... Datumswerte sind in Excel Ganzzahlen, die nur entsprechend formatiert werden können.

Mehr dazu sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=128

Die Zeilennummern einer (beliebigen) Spalte in Excel sind ja auch Ganzzahlen von 1:1048576

Die Zeilennummer 44140 eingegeben in eine Zelle und als Datum formatiert ergibt z.B. das heutige Datum (5.11.20) somit 44141 das morgige und so weiter. Mit ZEILE(A44140:A44141) können somit das Datum dieser beiden Tage als Matrix gelistet werden und natürlich auch mit ZEILE(B44140:B44141) oder ...

Wenn Du in Deiner Tabelle die Spalte A löschst, würden die Formeln für die Auswertung von 2021 einen Bezugsfehler ergeben (kannst Du aber verhindern, wenn Du in meiner Formel anstelle Spalte A z.B. die Spalte B oder C für ZEILE() nutzt, weil Du diese Spalten ja nicht löschen wirst, da ja damit auch Deine Beginn/Ende-Datumsangaben gelöscht würden). Aber neue Spalten einfügen, hat keinerlei Einfluss auf die Ergebnisse (probier es ruhig aus).

Gruß Werner
.. , - ...

Betrifft: AW: zu Deiner Zusatzfrage ...
von: Ricarda
Geschrieben am: 05.11.2020 18:17:29

Hallo Werner,

danke für die Erklärung. Das macht echt Sinn!
Ich habe es auch ausprobiert und es klappt.

Danke nochmal.

Gruß,

Ricarda

Betrifft: AW: bitteschön , ...
von: neopa C
Geschrieben am: 05.11.2020 18:50:40

Hallo Ricarda,

... und hier nur noch kurz zu Deinem anderen thread. In N10 hättest Du folgende Formel schreiben sollen: =$F5*N8 und diese ziehend nach rechts kopieren. Zu der Aufteilung auf entsprechende Zeiten müsstest Du schon angeben, nach welchen Kriterien/Bedingungen diese aufgeteilt werden sollen. Aber vielleicht hast Du ja Glück und ein anderer ExcelUser mit entsprechenden kaufmännischer Ausbildung hilft Dir da evtl. aus.

Gruß Werner
.. , - ...