Excel SVerweis mit dynamischer Matrix?


15.12.2019, 16:44

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.

Woher ich das weiß:eigene Erfahrung

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..

1

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

1
@iAtomic

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

1
@KHSchindelar

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..

1

Diese Formel in Deinem Beispiel in B1 und runterkopieren sollte klappen:

=SVERWEIS($C$2;INDIREKT("'" &A1&"'!A58:B104");2;FALSCH)

Oder?

Woher ich das weiß:Beruf – IT-Administrator (i.R.)

Deine Suche bezieht sich auf Zelle C2, die jedoch keinen Wert enthält.

So sollte es aussehen.

Woher ich das weiß:eigene Erfahrung
 - (Excel, excel-formel, Sverweis)  - (Excel, excel-formel, Sverweis)

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?

1
@iAtomic

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)

1

Was möchtest Du wissen?