Office: Excel SVerweis mit dynamischer Matrix in anderem Tabellenblatt

Hallo,
so wie's aussieht werde ich mich in Zukunft bei der Arbeit öfters mit Excel herumschlagen dürfen.
Soweit macht das arbeiten ja auch Spaß so lange es funktioniert, jetzt bin ich aber gerade an eine harte Nuss für mich gestoßen und kriege das einfach nicht elegant gelöst.
Wenn irgendwer eine elegante Lösung kennt die meine noch sehr beschränktes Excelwissen übersteigt würde ich mich freuen :)

Folgendes Problem:

In den Tabellenblättern Tab1 - Tab 100
habe ich (neben vielen anderen Daten) eine senkrechte Tabelle mit Namen(Substanz).
Die Namen sind jeweils in Spalte B


Im Tabellenblatt TabGesamt habe ich nun die Gesamtliste an Namen
und muss eine Übersicht generieren welche Substanz in welcher Fraktion(Tab1-Tab100) vorkommt.
In Spalte A steht hier die Gesamtliste.

Um mir automatisch die vorkommenden Substanzen anzeigen zu lassen habe ich folgendes geschrieben:

=SVERWEIS(A1;'Tab1'!B1:B10;1;FALSCH)

Das gibt mir soweit ganz zuverlässig an ob in der jeweiligen Fraktion eine der Substanzen aus der Liste A1 vorkommt.

Das funktioniert also soweit, ich habe nur das Problem dass die Matrix B1-Bx in jeder Tabelle anders groß ist, da ich je nachdem manchmal die ersten 10, 15 oder 25 Werte brauche. Das Entscheidet sich immer nach Auswertung der Daten und kann nicht so einfach mathematisch festgelegt werden.
Momentan bin ich gezwungen aufwändig händisch die Matrixgröße für jede Fraktion ( Tab1-Tab100) jeweils neu zu bestimmen und in jeder Formel zu verbessern.
Eine Vereinfachung wäre es schon wenn ich einfach die Matrixgröße irgendwo angeben könnte, so dass sich die Formeln je nach entsprechendem Tabellenblatt daran anpassen könnte.

Was ich gerne hätte wäre die Möglichkeit die Matrix variabel zu gestalten also B1:BX an einer Stelle festzulegen.
Es wäre klasse wenn ich z.B. in Tab1 in irgendeiner Zeile(z.B. C1) die Matrixgröße festelegen könnte = B1:B10, und dementsprechend in Tab2 Matrixgröße ist = B1:B15, Tab3 in Zeile C1 steht Matrixgröße ist = B1:B23

So wie ich das bisher probiert habe klappt das nur leider nicht. Irgendwie kann Excel so wie ich es bisher probiert habe nicht die Matrix auslesen. Anscheinend scheint es auch ein Problem zu sein, dass die Matrix in einem anderen Tabellenblatt hinterlegt ist.

Ich hab mal Versucht die die gleiche Berechnung im gleichen Tabellenblatt zu machen in der auch die Tabelle B ist.
Das hat sogar mit dieser Formel funktioniert:
Wobei ich hier jedoch die Matrix Größe nicht in als C1= B1:B10
sondern als C1= B1 und C2= B10 angegeben hatte

=SVERWEIS(A1;INDIREKT(C1):INDIREKT(C2);1;FALSCH)

Wenn ich die Gleiche Berechnung dann allerdings im Tabellenblatt TabGesamt mache:

=SVERWEIS(A1;INDIREKT('Tab1'!C1):INDIREKT(Tab1C2);1;FALSCH)
kriege ich nur einen #NV Fehler.


Was da jemand Rat oder eine Lösung wie ich das Problem elegant lösen kann?
Wäre wie gesagt klasse wenn ich die Matrixgröße an einer andern Stelle festlegen könnte.

Liebe Grüße
Hembo

 

Hallo!

Schreibe in C1 eine 1 und in C2 eine 10.
Dann sollte diese Formel den Bereich Tab1!B1:B10 auswerten (ungetestet):

=SVERWEIS(A1;INDEX(Tab1!B:B;Tab1!C1):INDEX(Tab1!B:B;Tab1!C2);1;FALSCH)

 

Hey vielen Dank für die schnelle Antwort.
Ich hab die Formel gleichmal ausprobiert.
Leider bekomme ich allerdings nun einen #NAME Fehler

 

Hallo Hembo,

warum vergibts Du für die Matrix aus der die Daten stammen nicht einfach Namen. Dann kannst Du ohne Anpassung dynamisch mit der Matrix arbeiten wenn du nur darauf achtest das beim ausweiten die Namen Grenzen mit verschoben werden. Dann kannst auf die Indirekt Adressierung verzichten.

Gruß
Bernd

 

Hallo!

#NAME! erscheint meistens dann, wenn ein Schreib- bzw. Tippfehler vorliegt.
Das solltest du also nochmals checken!

 

Hey Danke Schatzi,
es funktioniert, es hatte sich nur zwischen das zweite B:B ein leerzeichen eingeschlichen.
Das ist super vielen vielen Dank!.
Das macht alles einfacher, eleganter und hübscher :)

@ Ratzmer: Auch wenn das eigentliche Problem nun gelöst ist würde mich dein Lösungsansatz mal interessieren.
Man lernt immer dazu und es ist für's Verständnis verdammt wertvoll mehrere Lösungsansätze zu verstehen.
Magst du vielleicht deinen Lösungsansatz noch etwas weiter erklären?
Wie vergebe ich denn für eine Matrix einen Namen und verweise darauf im S Verweis?

 

H- und S-Verweis flexibel und dynamisch

Hallo Hembo,

der einfachheithaber habe ich Dir mal eine Exceldatei beigelegt die Du dir anschauen kannst. In dieser sind extensiv Namen vergeben. und die Tabelle=Matrix auf die per Sverweis zugegriffen werden soll erweitert sich wenn du Daten oder Spalten anfügst. Die Verweisformel im Blatt Sverweis schaut auf die Überschrift und holt aus der Matrix den passenden Wert zum vertikalen und horizontalen Suchbegriff.

Viel Spaß damit.

Gruß
Bernd

 

Hallo Hembo,

hast Du Dir mal die Mustertabelle zum SVerweis Thema angeschaut?

Gruß
Bernd

 

hi Bernd, du baust den SVERWEIS() in einen WVERWEIS()... ... warum...? hier geht doch viel einfacher..:

PHP:

=SVERWEIS($A2;Dymamische_Matrix!$A$1:$K$41;VERGLEICH(B$1;Head;0);0)

 

Hallo Hiob,

vielen Dank für den Tipp zur Verbesserung meiner Formel. Die Lösung hatte ich mal entwickelt um Tabelleninhalte einfacher mit verschobenen Überschriften wieder in mein Vorgabespalten Layout zu bringen.

Ich teste das mal. Sieht einfacher aus.

Habt das mal ausprobiert, die Funktion reagiert so wie meine. Super Vereinfachung und Erweiterung meines Pools. Vielen Dank.