Hallo,
vorweg die folgenden Formeln gehen davon aus das dein Format konstant ist (Abstand zwischen den Tagen in Spalte B & L je 5 Zeilen, zu übernehmender kWh-Verbauch 3 Zeilen unterhalb des zugehörigen Datum) und es nur die beiden Bereiche gibt.
Ich habe in meiner Testtabelle benannte Bereiche (Strg+F3) genutzt um die Formeln beim austesten einfacher anpassen zu können, du solltest aber problemlos auch den jeweiligen Bezug verwenden können. Bei mir sind:
- erste_Spalte : $B$5:$B$40
- zweite_Spalte: $L$5:$L$40
- erste_Verbrauch: $G$5:$G$40
- zweite_Verbrauch: $Q5:$Q$40
Die Formeln sind:
- kleinstes Datum in V5: =MIN(erste_Spalte;zweite_Spalte)
- nachfolgende Datumswerte in V6 (+runter kopieren): =WENN(UND(V5<>"";V5<>0;MIN(WENN(erste_Spalte<=V5;"";erste_Spalte);WENN(zweite_Spalte<=V5;"";zweite_Spalte))>0);MIN(WENN(erste_Spalte<=V5;"";erste_Spalte);WENN(zweite_Spalte<=V5;"";zweite_Spalte));"")
Jeder Wert kleiner als das Datum der Vorgängerzeile wird aussortiert und anschließend der kleinste Wert ausgegeben, die umschließende Wenn() fängt die Fälle ab nachdem das letzte Datum bereits gefunden ist.
- Falls du nicht unbedingt die Zwischenwerte aufgelistet brauchst, könntest du dies verwenden um direkt die Gesamtsumme des Tages zu erhalten: {=SUMME(BEREICH.VERSCHIEBEN(erste_Verbrauch;3;0)*(V5=erste_Spalte);BEREICH.VERSCHIEBEN(zweite_Verbrauch;3;0)*(V5=zweite_Spalte))}
- Matrixformel, die geschweiften Klammern {} nicht mit eingeben, sondern die Eingabe über Strg+Enter abschließen
Über Bereich.Verschieben wird der Verbrauchsbereich so verschoben, das Tag und kWh-Stunde in derselben Zeile liegen. Zeilenweise werden die kWh-Stunden, je nachdem ob der Tag dem gesuchten Datum entspricht, mit WAHR(=1) oder FALSCH(=0) multipliziert und anschließend addiert.
Falls du auf den Zwischenwerten besteht wird es komplizierter den richtigen zugehörigen Wert in deinem Format zu finden:
- Formel in W5:
- =WENNFEHLER(WAHL(2+VORZEICHEN(SUCHEN("#####";WECHSELN(WECHSELN(GLÄTTEN(TEXTKETTE(erste_Spalte&" ";zweite_Spalte&" "));$V5;"#####";SPALTE(A1));" ";""))-(5*ANZAHL(erste_Spalte)));INDEX(erste_Verbrauch;3+SUCHEN("#####";WECHSELN(WECHSELN(GLÄTTEN(TEXTKETTE(erste_Spalte&" ";zweite_Spalte&" "));$V5;"#####";SPALTE(A1));" ";"")));"N/A";INDEX(zweite_Verbrauch;3+SUCHEN("#####";WECHSELN(WECHSELN(GLÄTTEN(TEXTKETTE(erste_Spalte&" ";zweite_Spalte&" "));$V5;"#####";SPALTE(A1));" ";""))-(5*ANZAHL(erste_Spalte))));"")
Die üblichen Verweisfunktionen (Index()+Vergleich(); XVerweis()) können meines Wissens nach weder mit mehreren Bereichen umgehen, noch das n.te Auftreten eines Wertes finden. Wechseln() kann das n.te Auftreten feststellen, benötigt jedoch einen Textwert, daher wird Textkette() verwendet um die Datumswerte der Spalten in einen solchen zu überführen. Die zusätzlichen Leerzeichen sind nötig um den Fall abzufangen das das Ende des einen Datumswertes und der Anfang des nachfolgenden Datumswertes einen anderen Datumswert ergeben.
Über Wechseln() wird nun das n-te Auftreten des Datums in ##### umgewandelt, anschließend die Leerzeichen entfernt, und über Suchen() die Position und damit die Zeile ermittelt. (Es entspricht hier der Zeile da Excel Datumswerte als fortlaufende Zahlen speichert und diese bis zum 13.10.2173 5-stellig sind, zufälligerweise der Zeilenabstand zwischen den Datumswerten in deiner Tabelle) Index() wandelt die Zeile schließlich in den jeweiligen kHw-Wert um.
Die Kombination aus Wahl() + Vorzeichen(2+) ist hierbei lediglich eine Alternative zu einer klassischen Wenn() um zu bestimmen ob der betreffende Wert in Spalte 1 oder 2 zu finden ist