Sverweis Ausgabe mit 2 Spalten und UND Verknüpfung?

Question

Hallo Leute,

ich hab ein Problem zu einer Sverweis-Funktion.

Es handelt sich um 2 Tabellenblätter, die miteinander verknüpft sind.

Ich habe ein Suchkriterium in Tabellenblatt 2, das sich auf ein beliebiges Datum bezieht (zwischen 01. April 2011 - 31.März 2012).

In Tabellenblatt 1 befindet sich meine Matrix. Spalte A kennzeichnet die Tage (z.B. 01.April, 01. April,...24 x, dann 02. April, ... 24 x, 03. April 24 x und so weiter bis 31. März 2012), Spalte B kennzeichnet die Stundenwerte eines Tages (von 01:00:00 - 00:00:00, 24 Werte) und Spalte C beinhaltet Stromverbräuche in kWh zu den einzelnen Stundenwerten an den bestimmten Tagen.

Die Aufgabe: Die Stundenverbräuche möchte ich mir gerne per Sverweis anzeigen lassen, für den bestimmten Tag und die bestimmte Uhrzeit, also quasi eine UND verknüpfung im Sverweis..?

Meine funktion sieht zur Zeit so aus:

=WENN(B3=ZEIT(1;0;0);SVERWEIS($F$1;'Stromverbrauch vs PV Produktion'!C2:E8785;3;FALSCH))

Ich glaube, ich könnte die Funktion jetzt weiter spinnen und weitere WENN-Funktionen dazufügen, bis ich alle 24 Werte (für alle Stundenwerte) habe, aber das wird ein RIESENBAUM von einer Funktion. Gehts kürzer und einfacher?

Wenn mir noch einer erklären kann, wie ich hier Anhänge (Dateien) machen kann, dann könnte ich das auch mal posten, hab die Funktion hier auf der Seite leider noch nicht gefunden :(

Danke euch für die Hilfe!

Answer 1

Hallo,

dazu eignet sich SVERWEIS() nicht.

Anbei mein Vorschlag in verkürzter Form.

Download

Gruß
Rainer

Answer 2

Hallo noch mal,

falls Du auf SVERWEIS() bestehst, empfiehlt es sich Datum und Uhrzeit in einer Spalte unter zu bringen.

Siehe Beispiel

Gruß
Rainer

Answer 3

Hallo Rainer,

wie ich sehe kommt der Sverweis für mich nicht in Frage, da ich die beiden Werte trennen möchte, schon mal danke für die Summenprodukt-Formel, die ist klasse!

Die hat mein Problem auch fast schon gelöst, da die Werte problemlos dargestellt werden. Nur eine Sache verstehe ich noch nicht. In ein paar Zeilen werden Nullen dargestellt, obwohl die Werte in der Matrix (Spalte C, also die Ausgabewerte) keine Nullen enthalten. Bin da verwirrt und habs auch nochmal nachgecheckt und dachte, deine Formel meinen Wünschen angepasst zu haben. Hier mal meine Formel:

=SUMMENPRODUKT(('Stromverbrauch vs PV Produktion'!$C$2:$C$8785=A3)*('Stromverbrauch vs PV Produktion'!$D$2:$D$8785=B3)*'Stromverbrauch vs PV Produktion'!$E$2:$E$8785)

Wenn ich nun die Zeitwerte fortlaufend von B3 von 01:00:00, 02:00:00...etc. durch =Zeit(1;0;0) ersetze und runterziehe verschwinden ein paar Nullen.

Die Nullwerte entstehen bei den Werten:
19:00:00
22:00:00
00:00:00


Datum Zeit Stromverbrauch in kWh

25.03.2012 01:00:00 2,13
25.03.2012 02:00:00 2,28
25.03.2012 03:00:00 2,28
25.03.2012 04:00:00 2,23
25.03.2012 05:00:00 2,30
25.03.2012 06:00:00 2,60
25.03.2012 07:00:00 2,75
25.03.2012 08:00:00 2,53
25.03.2012 09:00:00 2,48
25.03.2012 10:00:00 2,43
25.03.2012 11:00:00 2,45
25.03.2012 12:00:00 2,38
25.03.2012 13:00:00 2,35
25.03.2012 14:00:00 2,25
25.03.2012 15:00:00 2,38
25.03.2012 16:00:00 1,98
25.03.2012 17:00:00 1,80
25.03.2012 18:00:00 1,75
25.03.2012 19:00:00 0,00
25.03.2012 20:00:00 1,88
25.03.2012 21:00:00 2,13
25.03.2012 22:00:00 0,00
25.03.2012 23:00:00 2,35
25.03.2012 00:00:00 0,00

Die Datumswerte habe ich mit dem Suchkriterium verknüpft, die somit automatisch übernommen werden.

Hast du dafür vielleicht ne Erklärung für die Nullen?

Answer 4

Hallo,

habe Dein Beispiel mal nachgebaut.
Kann aber den genannten Fehler nicht bestätigen.

Siehe hier

Falls der Fehler immer noch auftritt poste doch mal die betreffende Mappe, alles andere wäre Rätsel raten.

Gruß
Rainer

Answer 5

Kommando zurück

und sorry ich hatte Dich falsch verstanden, der Fehler existiert.

Es scheint, dass Excel nicht richtig mit dem Vergleich von Zeiten umgehen kann, vielleicht liegt auch ein Bug vor.

Da es sich bie Dir immer um volle Stunden handelt, kannst Du folgenden Trick anwenden.

=SUMMENPRODUKT(('Stromverbrauch vs PV Produktion'!C3:C8785=A3)*(ZEIT('Stromverbrauch vs PV Produktion'!D3:D8785*24;0;0)=B3)*'Stromverbrauch vs PV Produktion'!E3:E8785)

Bei mir funktioniert er.

Gruß
Rainer

Answer 6

Hallo Rainer,

das klappt bei allen Zeiten, leider bis auf den Stundenwert 00:00:00. Liegt wohl wahrscheinlich am 24 Std.-Wert, so dass man dann bestimmt laut deiner Formel eine WENN-Funktion einfügen muss? Also falls er diesen Wert findet, dann...

Ich habe das mal bei deinem Datumsblatt_3 probiert und bin auf den gleichen Fehler gestoßen.

Meine Formel zur Zeit:

=SUMMENPRODUKT(('Stromverbrauch vs PV Produktion'!$C$3:$C$8785=A3)*(ZEIT('Stromverbrauch vs PV Produktion'!$D$3:$D$8785*24;0;0)=B3)*'Stromverbrauch vs PV Produktion'!$E$3:$E$8785)

Hier mal für den 01.05.2011

Tag Zeit Stromverbrauch in kWh
01.05.2011 01:00:00 2,43
01.05.2011 02:00:00 2,33
01.05.2011 03:00:00 2,30
01.05.2011 04:00:00 2,28
01.05.2011 05:00:00 2,23
01.05.2011 06:00:00 2,28
01.05.2011 07:00:00 2,50
01.05.2011 08:00:00 2,48
01.05.2011 09:00:00 2,30
01.05.2011 10:00:00 2,23
01.05.2011 11:00:00 2,23
01.05.2011 12:00:00 2,18
01.05.2011 13:00:00 2,20
01.05.2011 14:00:00 2,28
01.05.2011 15:00:00 2,70
01.05.2011 16:00:00 3,53
01.05.2011 17:00:00 1,65
01.05.2011 18:00:00 1,65
01.05.2011 19:00:00 1,55
01.05.2011 20:00:00 1,60
01.05.2011 21:00:00 1,80
01.05.2011 22:00:00 2,28
01.05.2011 23:00:00 2,20
01.05.2011 00:00:00 0,00

Answer 7

Hallo,

diese Unsicherheit entsteht dadurch weil die Zeiten in Spalte C per Formel erzeugt werden und das Vergleichskriterium per Handeintragung geschieht.
Um diese Unsicherheiten auszuschließen, empfehle ich Dir, anstatt Uhrzeiten, Ganzzahlen zu verwenden und diesen das Zeitformat zu verpassen.
Siehe Anhang.

Download

Gruß
Rainer