wie bekomme ich das automatisch?

Question

Hallo Profis, ich habe eine neue Wohnung bezogen, Probleme mit der Leuftfeuchtigkeit hatte ich nie, in dieser Kellerwohnung aber ziemlich arg an manchen Tagen.
Am Luftentfeuchter habe ich einen Energieverbrauchsmesser mit Durchzieh-Plombe montiert, damit ich diese bei evtl. Anschuldigungen diese Daten und Nachweise Anwaltsgerecht vorlegen kann.
Zudem habe ich eine Raumklimastation mit Datenlogger angeschaft, in jeden Raum der Wohnung sowie auf der Terrasse ist ein Sensor, die die Messwerte alle 10 min. auf die SD-Karte speichert.
Der Grund liegt darin, dass ich mitbekommen habe, dass die Vormieter bereits Schimmel hatten, mein Vermieter aber nichts mitgeteilt hat. 
Es heißt immer, wer nichts beweisen kann, verliert fast immer. Die wollen alle nur noch Provit aber keine Investitionen leisten.
Ich habe eine Tabelle mit Excel 2019 gemacht und alles soweit eingetragen was ich benötige. Aber bei der Schlussberechnung der einzelnen Tage fehlt mir die Kenntnis. Hier hoffe ich auf eure Hilfe und Unterstützung.
In der Berechnung (letztes Blatt rechts)

Datum der der Betriebszeiten Spalte B und L, Messwerte der (hellroten Felder) wie eingetragen, sollen automatisch in der Auswertung rausgesucht - eingetragen werden. Die Formel in Spalte Gesamtwert kWh kann angepasst werden. Ich bedanke mich schon mal ganz herzlich

DanKirpan · Answer

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

Glaskocher · Answer

Das sieht recht komplex aus, weil mehrere Vorgänge des selben Tages zusammengefasst werden müssen. Da die Anzahl der Vorgänge je Tag variiert ist es aufwändig, diese Daten zu kopieren, ohne die Formeln für jeden Wert neu anpassen zu müssen. Von Hand müßte man jeweils mit "=" beginnend und einem Klick auf die Quellzelle der Daten jeden Einzelwert übertragen. Zusätzlich machen die verbundenen Zeilen und dadurch entstehende größere/unterschiedliche Abstände der Zeilennummern eine Automatisierung per Kopie und Einfügen der Formeln das Ganze schwerer. Auch die nichtkontinuierlichen Betriebstage erschweren die Automatisierung deutlich.

Das könnte so, wie ich es sehe, ein Fall für Makros sein, von denen ich keine Ahnung habe.

Eine Abhilfe könnte sein, daß man den Bereich der Dateneingabe von dem der Druckaufbereitung trennt. Das bedeutet, daß jeder Tag in den Rohdaten eine eigene Zeile bekommt und daraus die Daten ins Druckformular übernommen werden.

wie bekomme ich das automatisch?

2 Antworten