SVERWEIS mit verschiedenen Suchkriterien und unterschiedlichem Verweis?
Hallo, ich habe eine Tabelle, die mit den Stromzählerdaten eines Monats gefüllt ist. Sie hat 3 Spalten (A-C), die mit Datum, Uhrzeit und Zählerstand betitelt sind. Die Tabelle umfasst mehr als 40.000 Zeilen, da der Zählerstand jede Minute ausgeworfen wird. Ich möchte nun für jeden Tag dieses Monats den Zählerstand in Spalte H einfügen, der nach 9 Uhr als erstes in der Spalte C erscheint. Wenn ich "SVERWEIS" mit "WAHL" verwende, funktioniert dies nur bei Eingabe der exakten Uhrzeit. Sie variiert aber jeden Tag. Eine Formel, in der ich "WENN" mit "SVERWEIS" verknüpfe, funktioniert nicht. Was mache ich falsch? Welche Formel wäre richtig?
muss es unbedingt der Sverweis sein, der ist nun mittlerweile schon sehr ausgelutscht? welche Excel Version kommt denn bei dir zum Einsatz?
Nö, muss es nicht. Die Lösung von BerchGerch enthält genau das Ziel, das ich verfolge und sie enthält keinen Sverweis.
4 Antworten
Hallo,
ich habe es so hinbekommen:
Ich habe die (blau markierte) Hilfsspalte angelegt, in der ich einfach Datum und Uhrzeit addiert habe. Diese Spalte muss auf Zahl formatiert sein.
Um den Zählerstand ab 09:00 Uhr auszulesen, habe ich die folgende Formel verwendet:
{=INDEX(D:D;VERGLEICH(MIN(WENN(C:C>=SUMME(F3;0,375);C:C));C:C;0))}
Wichtig: Das ist eine Matrixformel. Sie wird ohne die geschweiften Klammern eingegeben. Die Eingabe muss dann mit Strg + Umschalt + Eingabe abgeschlossen werden, damit Excel die geschweiften Klammern automatisch einfügt und die Formel so als Matrixformel erkennt.
Hier noch ein paar Anmerkungen zur Formel in G3:
- C:C bezieht sich jeweils auf die (blau markierte Hilfs-)Spalte, in der sich das Suchkriterium, also die Zahl, die sich aus Datum und Uhrzeit ergibt, befindet.
- D:D bezieht sich auf die Spalte, die die Zählerstände enthält.
- >=SUMME(F3;0,375) steht für größer oder gleich 09:00 Uhr am Ablesetag, der Ablesetag steht hierbei jeweils in Spalte F.
Die Formel oben habe ich übrigens nach Recherche auf dieser Seite gefunden.
Eine Rückmeldung wäre nett.
Gruß, BerchGerch
Schöne Formel und vor allem weit zurück reichend von der Version. Vor allem die MIN WENN Kombi ist ne kreative Lösung. Und ja auf Herbers Excel hocken einigen Koryphäen. Da hab ich schon manches mal Lösungen gesucht.
Zur besseren Wartbarkeit würde ich tatsächlich statt 0,375 lieber 9/24 nehmen ist einfacher eintragbar ohne Taschenrechner. Und auch nach 2 Jahren besser erkennbar warum dieser Wert da drin steht.
hier noch eine Lösung wie es geht:
V1
=XVERWEIS(WAHR;((A2:A43=E2)*(B2:B43>9/24))=1;C2:C43;;1)
V2
=@FILTER($C$2:$C$43;($A$2:$A$43=E2)*($B$2:$B$43>9/24))
ich finde die beiden Varianten schön Kompakt insbesondere der Filter ist auch für den Laien sehr gut verständlich.
einzige Besonderheit das @ Zeichen gibt nur den ersten Datensatz aus, da der Filter hier mehrere finden würde.
Beide Varianten funktionieren erst ab Excel 2021
Vermutlich wäre das einfachste, wenn du in einer Hilfsspalte zum Datum die Uhrzeit 09:00:00 addierst und das Ergebnis als Vergleichskriterium nimmst. SVERWEIS sollte standardmäßig den ersten Eintrag wählen, der größer oder gleich diesem Wert ist. (Wenn du den Wert tatsächlich nach 09:00 Uhr haben willst, kannst du noch z. B. 0,0000001 addieren - das ist knapp 1/100 Sekunde, oder andere Tricks probieren.)
Intern werden Datum-Zeit-Angaben als "Fließkommazahl" (eine Form von Zahl mit Nachkommastellen) gespeichert. Der ganzzahlige Anteil (vor dem Komma) stellt den Tag dar, der Bruchteil die Uhrzeit. Wenn du eine Hilfsspalte nimmst, in der vom Zeitpunkt des Zählerstands 09:00:00 abgezogen wird und dann ABRUNDEN (oder GANZZAHL) angewendet wird, taucht hier das gewünschte Datum in derjenigen Zeile zum ersten Mal auf, wo 09:00:00 Uhr zum ersten Mal erreicht oder überschritten wurde.
Die Hilfsspalten kannst du dann durch WAHL überflüssig machen.
z.B. so mit einer Hilfsspalte
