Excel SVerweis mit dynamischer Matrix?
Guten Tag,
Ich möchte gerne einen SVerweis anlegen, der immer im gleichen Bereich, aber auf einem anderen Tabellenblatt sucht.
Beispiel:
=SVERWEIS(C2;'14025'!A58:B104;2;FALSCH)
Ich möchte gerne eine Spalte anlegen in der ich immer die Nummer des Tabellenblattes reinschreibe, und der SVerweis sich dann immer die Nummer aus dieser Spalte nimmt.
Also dass in der Nächten Zeile zum Beispiel soll der SVerweis das gleiche suchen aber auf dem Tabellenblatt '14617' und im gleichen Bereich wie oben.
Kann mir jemand weiter helfen?
hier ein Beispiel wie ich es haben möchte ohne immer in den Sverweis gehen zu müssen um den Namen des Tabellenblattes zu ändern. Sodass er sich den Namen des Tabellenblattes eine Spalte vorne dran zieht.
https://www.bilder-upload.eu/bild-3a2f9f-1576424566.jpg.html
4 Antworten
der (für mich) Einfachheit halber in einem kleineren Bereich und mit fixem Suchwort:
=SVERWEIS("total";INDIREKT(A1&"!"&"B2:E19");3;0)
(also mit weitaus weniger Gänsefüßchen) oder noch kürzer:
=SVERWEIS("total";INDIREKT(A1&"!B2:E19");3;0)
Aber besser durchschaubar ist m.E. diese Formel (sie hat auch in der Praxis noch einige weitere Vorteile):
=INDEX(INDIREKT(A1&"!D:D");VERGLEICH("total";INDIREKT(A1&"!B:B");0))
in Vergleich() hast du hier keinen SpaltenVERSATZ, sondern adressierst in Index() die Auslese-Spalte direkt ebenso wie die Suchbegriff-Spalte, also
=index(wo soll der Wert ausgelesen werden;Vergleich(was ist das Kriterium;wo soll es gesucht werden;WAHR/FALSCH))
Vergleich liefert eine ZellNr im Bereich, bei B:B ist die identisch mit der ZeilenNr.
Beide Formeln sollten aber von Blatt zu Blatt beliebig kopierbar sein, oder willst du die Ergebnisse in einer Liste auf EINEM Blatt zusammenstellen? (also eine Liste aller Total mit den entsprechenden Summen?
Dann sollte eine Liste deiner Blattnamen die Basis bilden und du kannst die Formel nach $-setzen der Zeilen des Bereichs (bei SV) nach unten kopieren. Die Index-Fo ist mit B:B bzw D:D von vornherein so angelegt, dass das nicht nötig ist: B:B etc bleiben beim nach-unten-kopieren B:B etc.
Makro zum Erstellen einer Blattliste:
Sub Blattliste_erstellen()
Dim Blatt
For Each Blatt In Worksheets
If Not IsEmpty(ActiveCell) Then MsgBox "Zielzelle(n) belegt, Abbruch": Exit Sub
ActiveCell = Blatt.Name
ActiveCell.Offset(1, 0).Activate
Next
End Sub
Nehme Deine Formel und ergänze um das andere Tabellenblatt
=SVERWEIS(C2;'14025'!A58:B104;2;FALSCH)
z..B.
=SVERWEIS(C2;'14025'!A58:B104;2;FALSCH)+SVERWEIS(C2;'Tabelle2'!A58:B104;2;FALSCH) usw.
Ich will die ja nicht zusammen rechnen. Sondern auf meinem 1 Tabellenblatt eine Übersicht erstellen. In Zeile 1 soll der Suchbegriff von Blatt 1 kommen. In Zeile 2 soll dann der Suchbegriff von Tabellenblatt 2 kommen. usw.. Der Name des Tabellenblattes steht dann jeweils in der gleichen Zeile zur Zuordnung..
Warum nutzt Du nicht anstelle des Wertes 14025 die Zelle A1 und kopierst die Formel runter ?
=SVERWEIS(C2;'A1'!A$1:B$999;2;FALSCH)
--- damit übernimmst Du den jeweiligen Wert der Spalte "A" als Suchbegriff
Das würde ich ja gerne machen, aber so wie du das schreibst nimmt er nicht den Wert aus A1 sondern den Namen "A1". Das ist ja genau das Problem was ich habe..
Deine Suche bezieht sich auf Zelle C2, die jedoch keinen Wert enthält.
So sollte es aussehen.
Klar hat C2 bei mir einen Wert. Da steht "Total" drin. Schau doch das Bild oben. Ich suche auf jeden Blatt nach dem Wert Total. Aber darum gehts ja nicht. Bekommst du es hin das Arbeitsblatt in der Matrix durch den Wert von "A1" zu ersetzen?
Gebe die Formel nochmal neu ein, sollte vor A1 ein Zeichen wie " oder ' stehen, entferne dies (vor A1 und danach)
Du kannst auch, anstelle von A1 einzugeben, die Zelle A1 per Mausklick wählen.
(Ich hoffe ich habe Dich richtig verstanden)
Diese Formel in Deinem Beispiel in B1 und runterkopieren sollte klappen:
=SVERWEIS($C$2;INDIREKT("'" &A1&"'!A58:B104");2;FALSCH)
Oder?
Und dazu will ich nicht immer in den Sverweis rein gehen und den Namen des Tabellenblattes ändern in jeder Zeile, sondern dass der Sverweis sich dann den Namen des Tabellenblattes in der gleichen Zeile z.B. 1 Spalte davor nimmt.
https://www.bilder-upload.eu/bild-3a2f9f-1576424566.jpg.html