Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1652to1656
1652to1656
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kombination INDEX mit SUMMENPRODUKT

Forumthread: Kombination INDEX mit SUMMENPRODUKT

Kombination INDEX mit SUMMENPRODUKT
24.10.2018 23:14:29
Andi
Hallo zusammen :)
Ich hätte da mal eine Frage zur Kombination INDEX mit SUMMENPRODUKT.
Ich habe ein Tabellenblatt mit Daten in einer Matrix (in der Beispieldatei vereinfacht) und müsste auf einem zweiten Tabellenblatt anhand von Daten in einer Spalte die Daten aus der Matrix aus Tabellenblatt 1 per Formel verknüpfen.
Die Formel sieht wie folgt aus:
=INDEX(Tabelle1!$A$1:$J$10;SUMMENPRODUKT((Tabelle1!$A$1:$J$10=A1)*(ZEILE($1:$10)+1)); SUMMENPRODUKT((Tabelle1!$A$1:$J$10=A1)*(SPALTE($A:$J)+1)))
In der Matrix kommt nur der gesuchte Wert "Test", sowie der gesuchte Wert "500" vor. Seltsamerweise gibt die Formel aber bei der Suche nach dem Wert "3" in der Matrix (ist ja nicht vorhanden) den Wert "Test" zurück und ich komme einfach nicht darauf, woran das liegen könnte.
Hat vielleicht jemand einen Tipp?
Im Voraus vielen Dank.
Beispieldatei: https://www.herber.de/bbs/user/124892.xlsx
Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Kombination INDEX mit SUMMENPRODUKT
25.10.2018 00:53:16
{Boris}
Hi,
gib mal in D1 ein:
=INDEX(Tabelle1!B:B;0;0)
und kopier das runter.
Hier scheint die Zeile der Formel für INDEX(...;0;0) eine Rolle zu spielen.
VG, Boris
AW: Kombination INDEX mit SUMMENPRODUKT
25.10.2018 06:44:17
Andi
Hi,vielen Dank für die schnelle Antwort. Bin gerade im Auto unterwegs, schaue mir das nachher an und vermelde dann das Ergebnis.
Anzeige
AW: Kombination INDEX mit SUMMENPRODUKT
25.10.2018 07:10:07
Andi
Da bin ich wieder, hat mir keine Ruhe gelassen. Die INDEX-Formel wirft dann in der Tat auch "Test" aus. Aber warum ist die Zeile der Formel auf Blatt2 relevant für das Ergebnis, wenn die Matrix doch auf Blatt1 ist und in der Formel Zeile und Spalte "0" sind?
AW: Kombination INDEX mit SUMMENPRODUKT
25.10.2018 08:41:40
Luschi
Hallo Andi,
Dein Beispiel ist etwas unglücklich gewählt. Schreibe in Tabelle1!B3 mal was anderes z.B: Sonne und Du wirst sehen, was passiert.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: Kombination INDEX mit SUMMENPRODUKT
25.10.2018 08:02:13
Luschi
Hallo {Boris},
wahrscheinlich weiß M$ immer noch nicht, waß sie da in die Index-Funktion reingezaubert haben, denn in der Vba-Hilfe steht auch in der neuesten Fassung (Excel 2019) dieser Passus: 
Zeile    Erforderlich. Markiert die Zeile in der Matrix, aus der ein Wert zurückgegeben
werden soll. Wird "Zeile" nicht angegeben, muss "Spalte" angegeben werden.
Spalte   Optional. Markiert die Spalte in der Matrix, aus der ein Wert zurückgegeben werden
soll. Wird "Spalte" nicht angegeben, muss "Zeile" angegeben werden.

denn statt INDEX(...;0;0) kann man auch INDEX(...;;) schreiben und Excel meckert nicht.
Auch in definierten Namen ist diese Schreibweise ganz gut zu gebrauchen, ein Vba-Zugriff auf solche Namen ist allerdings nicht empfehlenswert, da dann nur Blödsinn oder Error herauskommt.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: Kombination INDEX mit SUMMENPRODUKT
25.10.2018 09:37:46
Andi
Hm, ich dachte, dass sich INDEX auf die Zeile bezieht, welche ich als Parameter angebe und nicht auf die Zeile, in der die Formel selbst steht.
Gibt es denn eine andere Möglichkeit, anhand der Daten in Blatt2 Spalte A einen Wert in der Matrix auf Blatt1 zu suchen und dann den Wert von Blatt1 eine Spalte rechts, eine Zeile darunter auszugeben?
Grüße
Andi
Anzeige
INDEX(Bereich;0;0)
25.10.2018 10:53:43
{Boris}
Hi Andi,
offensichtlich ist es so, dass sich die INDEX-Funktion in dem Moment, wo beide Parameter (Zeile und Spalte) mit Null belegt werden, tatsächlich auf die (in diesem Fall) Zeile bezieht, in der die Formel selbst steht.
Ist so nirgends dokumentiert, aber es scheint halt so zu sein.
Ob das nun ein Bug oder gewollt ist, vermag ich nicht zu sagen.
VG, Boris
Anzeige
AW: Kombination INDEX mit SUMMENPRODUKT
25.10.2018 10:58:44
{Boris}
Hi Andi,
zur Lösung: Prüfe vorher, ob das Suchkriterium in dem Bereich vorkommt. Falls nicht: Null, ansonsten Formelergebnis.
Formel für B1 in Tabelle2 somit:
=WENN(ZÄHLENWENN(Tabelle1!$A$1:$J$10;A1)=0;0;INDEX(Tabelle1!$A$1:$J$10; SUMMENPRODUKT((Tabelle1!$A$1:$J$10=A1)*(ZEILE($1:$10)+1));SUMMENPRODUKT((Tabelle1!$A$1:$J$10=A1) *(SPALTE($A:$J)+1))))
und runterkopieren.
VG, Boris
Anzeige
AW: Kombination INDEX mit SUMMENPRODUKT
25.10.2018 11:41:20
Andi
Klasse, funktioniert prima. Ich danke euch beiden :)
;
Anzeige
Anzeige

Infobox / Tutorial

Kombination von INDEX mit SUMMENPRODUKT in Excel

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Stelle sicher, dass Deine Daten in einer Matrix auf einem Tabellenblatt (z.B. Tabelle1) angeordnet sind. In einer zweiten Tabelle (z.B. Tabelle2) hast Du die Suchkriterien.

  2. Formel eingeben: Nutze die folgende Formel, um den Wert zu finden:

    =INDEX(Tabelle1!$A$1:$J$10; SUMMENPRODUKT((Tabelle1!$A$1:$J$10=A1)*(ZEILE($1:$10)+1)); SUMMENPRODUKT((Tabelle1!$A$1:$J$10=A1)*(SPALTE($A:$J)+1)))

  3. Fehlerbehandlung einbauen: Um sicherzustellen, dass bei nicht gefundenen Werten kein Fehler zurückgegeben wird, erweitere die Formel mit einer Fehlerüberprüfung:

    =WENN(ZÄHLENWENN(Tabelle1!$A$1:$J$10;A1)=0;0;INDEX(Tabelle1!$A$1:$J$10; SUMMENPRODUKT((Tabelle1!$A$1:$J$10=A1)*(ZEILE($1:$10)+1)); SUMMENPRODUKT((Tabelle1!$A$1:$J$10=A1)*(SPALTE($A:$J)+1))))

  4. Formel nach unten kopieren: Ziehe das Ausfüllkästchen der Zelle nach unten, um die Formel auf weitere Werte anzuwenden.

Häufige Fehler und Lösungen

  • Fehler: Falscher Rückgabewert: Wenn die Formel den falschen Wert zurückgibt, überprüfe die Zeile, in der die Formel steht. Die Verwendung von 0 für Zeile und Spalte kann dazu führen, dass Excel die aktuelle Zeile verwendet.

  • Lösung: Achte darauf, dass die Eingabewerte in der Matrix genau übereinstimmen. Nutze die Funktion ZÄHLENWENN, um sicherzustellen, dass das Suchkriterium vorhanden ist.

Alternative Methoden

  • Verwendung von SVERWEIS: Anstelle von INDEX und SUMMENPRODUKT kannst Du auch die SVERWEIS-Funktion verwenden, wenn Deine Daten in einer vertikalen Anordnung vorliegen.

    Beispiel:

    =SVERWEIS(A1; Tabelle1!$A$1:$J$10; 2; FALSCH)

  • Kombination mit WVERWEIS: Bei einer horizontalen Tabelle kannst Du WVERWEIS nutzen.

Praktische Beispiele

  • Beispiel 1: Angenommen, Du hast in Tabelle1 Werte wie "Test", "500" und "Sonne". Wenn Du in Tabelle2 nach "Test" suchst, gibt die Formel den entsprechenden Wert zurück.

  • Beispiel 2: Mit der oben genannten erweiterten Formel kannst Du sicherstellen, dass bei der Suche nach "NichtVorhanden" ein 0 zurückgegeben wird, anstatt einen Fehler.

Tipps für Profis

  • Definierte Namen: Verwende definierte Namen für Deine Bereiche, um die Formeln übersichtlicher zu gestalten. Das erleichtert die Wartung Deiner Excel-Dateien.

  • Formelüberprüfung: Nutze die Funktion "Formel auswerten" in Excel, um Schritt für Schritt zu sehen, wie Excel Deine Formel berechnet. Das hilft beim Debuggen von komplexen Formeln.

FAQ: Häufige Fragen

1. Warum gibt die INDEX-Formel den falschen Wert zurück? Wenn sowohl Zeile als auch Spalte auf 0 gesetzt sind, bezieht sich die Formel auf die Zeile, in der sie steht. Überprüfe die Eingabewerte.

2. Wie kann ich sicherstellen, dass meine Suchkriterien korrekt sind? Nutze die ZÄHLENWENN-Funktion, um zu prüfen, ob das Suchkriterium in der Matrix vorhanden ist, bevor Du die INDEX-Formel anwendest.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige