Microsoft Excel

Herbers Excel/VBA-Archiv

Index & Vergleich ergibt falsches Ergebnis | Herbers Excel-Forum


Betrifft: Index & Vergleich ergibt falsches Ergebnis von: KlausUlrich
Geschrieben am: 31.07.2012 13:42:34

Hallo,

ich habe von unserem Heizkostendienst eine Exceltabelle mit den Auslesewerten von Heizkostenverteilern bekommen. Darin sind für jedes Messgerät die letzten 15 oder 20 Monatswerte enthalten. In der zweiten Spalte steht also immer die Nr. des Geräts, in der Spalte rechts davon das Datum und noch eine Spalte rechts davon der Auslesewert. Beispiel: B2 = 123456789 C2 = 01.01.2010 D2 = 500 darunter dann B3 = 123456789 C3 = 01.02.2010 D3 = 600 usw.

Leider wurden die Dinger auf den falschen Stichtag programmiert. Anstelle dem 01.07. wurde der 01.01. programmiert. Immer am 01.01. werden daher in den Geräten die Einheiten auf Null zurückgesetzt. Um nun den Verbrauch vom 01.07. bis 01.07. des Folgejahres zu ermitteln, muss ich manuell die Werte vom 01.07. bis 01.01. Folgejahr ermitteln und dazu die Einheiten beginnend mit 0 (weil am 01.01. zurückgesetzt wird) bis zum 01.07. des Folgejahres addieren, damit ich so den Jahresverbrauch erhalte. Beispiel: Zähler Nr. 123456789 zeigt am 01.07.2009 den Wert 100 und am 01.01.2010 500 und am 01.07.2010 300. Dann muss ich 500 minus 100 rechnen (anteilig 2009) und 300 addieren (anteilig 2010) gibt 700 vom 01.07.09 - 01.07.10.

Das möchte ich mit einer Formel vereinfachen. Ich habe also für jede Einheit ein Tabellenblatt. Darin gibt es in der ersten Spalte die Zählernummern und in den beiden Folgespalten Anfangs- und Enddatum.
Beispiel: in A1 steht 123456789 in Spalte B1 steht 01.07.2009 und in Spalte C1 steht 01.01.2010
darunter A2 = 123456789, B2 = 01.01.2010 und C2 = 01.07.2010. Darunter kommen jeweils in 2 Zeilen die weiteren Zählernummern (immer 01.07.09 bis 01.01.10 und darunter 01.01.10 bis 01.07.10).

Ich möchte nun in Spalte D eine Formel verwenden, die zunächst über die Zählernummer und dann über das Datum den Zählerstand aus der anderen Datei ausliest. Dazu habe ich Index + Vergleich wie hier im Forum mehrfach zu finden ist, versucht. Doch leider klappt das nicht ganz. Anstelle des Wertes des Suchdatums erhalte ich immer den Wert des davorliegenden Monats. Beispiel: ich suche nach Zählernummer 123456789 und dem Wert vom 01.01.2010 (dieses Datum steht so in beiden Tabellen) und erhalte von diesem Zähler den Wert zum 01.12.2009 als Ergebnis. Dies ist bei allen Ergebnissen so.

Ich meine mich erinnern zu können, dass wenn ein Datum nicht vorhanden ist, der nächst kleinere Wert zurückgegeben wird. Aber das ist ja nicht der Fall. Es gibt ja diese Datumsangaben. Warum bekomme ich also den falschen Wert?

Danke für etwas Hilfe.

  

Betrifft: VERGLEICH(Suchwert;Matrix;0) von: {Boris}
Geschrieben am: 31.07.2012 13:54:31

Hi Klaus Ulrich,

die NULL am Ende der Vergleichfunktion ist ziemlich entscheidend ;-)

VG, Boris


  

Betrifft: AW: VERGLEICH(Suchwert;Matrix;0) von: KlausUlrich
Geschrieben am: 31.07.2012 14:04:42

Hallo Boris,

ich habe eine 0 hinten anhängen. Ist das falsch?

Generell gibt es in der Datentabelle jede Zählernummer mehrfach (klar bei 20 Monatswerten). Aber jedes Datum ist (je Zähler) nur einmal vorhanden.

Ohne diese 0 hinten bekomme ich das gleiche Ergebnis. Die Tabelle ist sortiert nach Zählernummer und danach nach dem Datum.

Gruß, Klaus


  

Betrifft: AW: VERGLEICH(Suchwert;Matrix;0) von: {Boris}
Geschrieben am: 31.07.2012 14:05:57

Hi Klaus Ulrich,

zeig mal die Datei.

VG, Boris


  

Betrifft: AW: VERGLEICH(Suchwert;Matrix;0) von: KlausUlrich
Geschrieben am: 31.07.2012 14:18:02

Hallo Boris,

habe eine Zip hochgeladen (wo die allerdings gelandet ist (bekam einen neuen Namen 81.....)
Sehen kann ich die nirgends. Kannst Du darauf zugreifen?

VG, Klaus


  

Betrifft: Upload von: Rudi Maintaire
Geschrieben am: 31.07.2012 14:20:35

Hallo,
du musst dem Link hier posten.
Also Datei nochmal hochladen, den Link kopieren und in eine Antwort einfügen.

Gruß
Rudi


  

Betrifft: AW: Upload von: KlausUlrich
Geschrieben am: 31.07.2012 14:22:26

Hallo Rudi,

einfach, wenn mans weiß oder liest, was da steht.......

https://www.herber.de/bbs/user/81220.zip


  

Betrifft: AW: Upload von: KlausUlrich
Geschrieben am: 31.07.2012 14:26:03

So, müsste zugänglich sein. Habe zwei Dateien in eine Zip gepackt. Die eine (Wohnhaus..........) enthält die Auslesedaten der Zähler, die andere "HKAbrechnung........." soll die Daten verarbeiten. Darin habe ich bisher nur im Sheet "Tattoo" versucht die Formel im Bereich F41:G54 anzuwenden. Davor stehen die Suchbegriffe (sieht man ja aus der Formel).

VG, Klaus


  

Betrifft: AW: Upload von: Rudi Maintaire
Geschrieben am: 31.07.2012 14:42:48

Hallo,

 ABCDEFG
39Lfd. Nr.RaumGeräte-Nr.Datum altDatum neuStand altStand neu
40       
411F4646610001.07.200901.01.20100147
422F4646610001.01.201001.07.2010147152
433Z4648799601.07.200901.01.201076283
444Z4648799601.01.201001.07.2010283331
455LA4648799701.07.200901.01.2010161405
466LA4648799701.01.201001.07.2010405447
477K4648799801.07.200901.01.201040163
488K4648799801.01.201001.07.2010163358
499T4648799901.07.200901.01.201061119
5010T4648799901.01.201001.07.2010119166
5111LA4648800101.07.200901.01.2010126161
5212LA4648800101.01.201001.07.2010161228
5313LA4648800301.07.200901.01.2010288641
5414LA4648800301.01.201001.07.2010641772

ZelleFormel
F41=SUMMENPRODUKT(('[Wohnhaus Ablesedaten Sanncompact 082011.xls]Export'!$B$2:$B$1553=$C41)*('[Wohnhaus Ablesedaten Sanncompact 082011.xls]Export'!$C$2:$C$1553=$D41)*'[Wohnhaus Ablesedaten Sanncompact 082011.xls]Export'!$D$2:$D$1553)
G41=SUMMENPRODUKT(('[Wohnhaus Ablesedaten Sanncompact 082011.xls]Export'!$B$2:$B$1553=$C41)*('[Wohnhaus Ablesedaten Sanncompact 082011.xls]Export'!$C$2:$C$1553=$E41)*'[Wohnhaus Ablesedaten Sanncompact 082011.xls]Export'!$D$2:$D$1553)


Gruß
Rudi


  

Betrifft: AW: Upload von: KlausUlrich
Geschrieben am: 31.07.2012 14:50:54

Hallo Rudi,

vielen Dank für Deine Hilfe. Man muss sich halt nur mit der Syntax auskennen (was bei mir leider (noch) nicht der Fall ist. Warum da "multipliziert" wird bzw. eben dieses "*" verwendet wird und das ganze Ding dann auch noch "Summenprodukt" genannt wird......... muss man eben erstmal verstehen.

Kannst Du mir erklären, warum meine Formel nicht den richtigen Wert gebracht hat? Nur zum Verständnis.

Danke!

VG, Klaus


  

Betrifft: AW: Upload von: Rudi Maintaire
Geschrieben am: 31.07.2012 15:01:32

Hallo,
Kannst Du mir erklären, warum meine Formel nicht den richtigen Wert gebracht hat? Nur zum Verständnis.

kann ich nicht, da das Archiv einen Fehler hat und in der Mappe keine Formeln mehr sind. Deshalb kenne ich die nicht.
Ich vermute, dass du das http://www.excelformeln.de/formeln.html?welcher=30 verwenden wolltest und die Formel nicht als Matrixformel eingegeben hast.

Gruß
Rudi


  

Betrifft: Erklärung von: Rudi Maintaire
Geschrieben am: 31.07.2012 15:07:19

Hallo,
jetzt hab ich's noch mal neu runter geladen.
Es muss Export!$B$1:$B$500 und Export $C$1:$C$500 heißen.

Gruß
Rudi


  

Betrifft: AW: Erklärung von: KlausUlrich
Geschrieben am: 01.08.2012 12:16:30

Hallo Rudi,

gestern hat es mir nicht mehr gelangt. Danke für die Erklärung. Welchen Grund hat es, dass das Weglassen der Zeile 1 (dort stehen ja eigentlich keine Daten, nur die Spaltenüberschriften) dazu führt, dass die Ergebnisse auch um eine Zeile verschoben werden? Die Überschriften braucht man doch eigentlich nicht um die Daten zu durchsuchen. Muss man immer mit der Zeile 1 beginnen, egal was da drin steht?

VG, Klaus


  

Betrifft: AW: Erklärung von: Rudi Maintaire
Geschrieben am: 01.08.2012 14:42:07

Hallo,
Vergleich(x;Suchmatrix;) liefert die Position von x innerhalb der Suchmatrix. INDEX(Ergebnismatrix;n) liefert das nte Element der Ergebnismatrix. Wenn also deine Ergebnismatrix gegenüber der Suchmatrix verschoben ist, bekommst du zwangsläufig ein falsches Ergebnis, da deine Ergebnismatrix (ganze Spalte) bei Zeile 1 beginnt, während die Suchmatrix mit Zeile 2 beginnt.

Einfaches Bsp.:

 ABCDEF
1xxxyyy    
2a1 c32
3b2 c43
4c3    
5d4    
6e5    
7f6    
8g7    
9h8    

ZelleFormel
E2=VERGLEICH(D2;A2:A9;)
F2=INDEX(B:B;E2)
E3=VERGLEICH(D3;A1:A9;)
F3=INDEX(B:B;E3)


Gruß
Rudi


  

Betrifft: AW: Erklärung von: KlausUlrich
Geschrieben am: 01.08.2012 14:46:28

Hallo Rudi,

danke für die Info. Langsam steige ich dahinter. Logik bleibt Logik, man muss sie nur erkennen.

Tausend Dank. Denke mal das hilft mir hier auch mit anderen Formeln weiter.

VG, Klaus


  

Betrifft: AW: Upload von: KlausUlrich
Geschrieben am: 31.07.2012 14:26:50

So, müsste zugänglich sein. Habe zwei Dateien in eine Zip gepackt. Die eine (Wohnhaus..........) enthält die Auslesedaten der Zähler, die andere "HKAbrechnung........." soll die Daten verarbeiten. Darin habe ich bisher nur im Sheet "Tattoo" versucht die Formel im Bereich F41:G54 anzuwenden. Davor stehen die Suchbegriffe (sieht man ja aus der Formel).
VG, Klaus


Beiträge aus den Excel-Beispielen zum Thema "Index & Vergleich ergibt falsches Ergebnis"