Microsoft Excel

Herbers Excel/VBA-Archiv

Zeile ermitteln für Matrix-Funktion

Betrifft: Zeile ermitteln für Matrix-Funktion von: Seppl
Geschrieben am: 26.10.2020 11:54:56

Hallo community,

nachdem mir hier im Forum beim letzten Excel-Problem so gut geholfen wurde probiere ich es gerne auch wieder in der Hoffnung, dass Ihr mir auch hier helfen könnt:

Ich habe in meiner Excel-Datei eine Menge an Spalten (ca.20), bei denen Matrix/Array-Funktionen hinterlegt sind. Ich verwende deswegen Matrix-Funktionen, da ich 3 Werte (aus den Spalten H, B und F) mit anderen 3 Spalten (J, F und K) einer anderen Datei vergleiche und dann einen Wert (hier im Beispiel: aus Spalte O) zurückgebe.

Da aufgrund der Matrix-Formeln die Performance drunter leidet, habe ich irgendwo in einem Forum gelesen, dass es viel helfen würde, die Ermittlung der Treffer-Zeile aus der zweiten Tabelle separat (in zusätzlicher Spalte zu berechnen) um dann in den 20 Spalten "nur" mit der Index-Funktion darauf zu verweisen. Hätte anscheinend den Vorteil, die Zeilenermittlung nicht jedesmal in allen 20 Spalten durchführen zu müssen, sondern nur einmal (klingt ja auch logisch).

Aber da fehlt es mir leider an Know-how. Meine erste Beispiel-Formel in Spalte K sieht wie folgt aus:

{=WENN(H5="";"";SVERWEIS("*"&H5&"*"&"*"&B5&"*"&"*"&F5&"*";WAHL({1.2.3}; [Tab2.xlsx]Cockpit!$J$6:$J$5000&[Tab2.xlsx]Cockpit!$F$6:$F$5000&[Tab2.xlsx]Cockpit!$K$6:$K$5000; [Tab2.xlsx]Cockpit!$O$6:$O$5000);2;0)) }

Meine beiden Fragen wären:
1. Wie muss die Formel in der zusätzlichen Spalte (Bsp: Z5) der ersten Tabelle aussehen, die den ermittelten Zeilenwert der zweiten Tabelle (Tab2) liefert?
2. Wie muss die Formel in einer der Spalten (z.B.: Spalte S) der ersten Tabellen aussehen, die mit dieser ermittelten Zeilennummer, den Wert aus der Spalte O der zweiten Tabelle zurückgibt?

Oder, es hat vielleicht von Euch jemand eine andere Idee, wie ich die Formel umstellen sollte, dass sie performanter arbeitet?

Würde mich riesig freuen, wenn sich einer von Euch "Profis" der Sache annimmt.

Schöne Grüße und danke schön vorab, Seppl

Betrifft: AW: Zeile ermitteln für Matrix-Funktion
von: Daniel
Geschrieben am: 26.10.2020 12:16:57

Hi

VERWEIS ist die Basis-Funktion zum SVerweis und liefert die Positionsnummer der Fundstelle als Ergebnis.
INDEX wählt dann aus einem Zellbereich über die Positiosnummer mit Zeile und Spalte die Zelle aus.

Dh ein SVerweis("x";A:D;4;0)
Läßt sich so mit Index und Vergleich darstellen:
Index(A:D;Vergleich(A:A;"x";0);4)
Oder etwas kürzer:
Index(D:D;Vergleich(A:A;"x";0))
Den Vergleich kannst du dann in eine eigene Zelle auslagern.

Gruß Daniel

Betrifft: Finde den Fehler u.korrigiere ihn, Daniel! owT
von: Luc:?
Geschrieben am: 26.10.2020 15:14:03

:-?

Betrifft: AW: Wann begreifst Du,
von: sulprobil
Geschrieben am: 26.10.2020 22:08:09

dass es nicht wichtig ist, wieviel Du weißt, sondern dass Du konkret im Thread beantwortest, was der Fragesteller fragt?

Betrifft: Wann begreifst du, dass das nur ein Hinweis ...
von: Luc:?
Geschrieben am: 27.10.2020 01:57:52

…auf einen (Danielschen) Lapsus, wie er jedem mal passieren kann, war und ich es deshalb ihm überlassen wollte, ihn zu korrigieren.
Ansonsten verbitte ich mir deine zweifelhaften Attacken, deren eigentlicher Grund (mir) immer offensichtlicher wird! Meinst du, das wirklich nötig zu haben…‽
Luc :-?

Betrifft: Allerdings kommt mir das eher wie eine ...
von: Luc:?
Geschrieben am: 28.10.2020 15:30:02

False Flag-Aktion vor, wenn man den AW-Stil berücksichtigt. Aber wenn der Inhaber des ggf missbrauchten Nicks das nicht moniert, war's er halt doch…
Luc :-?

Betrifft: Was Daniel meint:
von: lupo1
Geschrieben am: 27.10.2020 08:57:06

SVERWEIS ist zwar eine Funktion, die in der Normalfassung aus zwei Funktionen eine macht.

Müssen aber Suchdaten erst verbogen werden, scheitert sie, weil der Suchbereich dann auch verbogen werden muss, und das Ergebnis, welches im Suchbereich liegt, eben auch. Und das ist Unsinn, nicht erwünscht oder nicht möglich.

Daher trennt man Suche und Zugriff auf in VERGLEICH und INDEX. Dort kann man alles machen.

Einfachstes Beispiel: Die Rückgabe des linken statt rechten Werts im SVERWEIS: https://www.herber.de/excelformeln/pages/Sverweisfunktion_nach_links.html

Betrifft: VERWEIS ist natürlich die Grundform ...
von: Luc:?
Geschrieben am: 28.10.2020 15:34:38

…der anderen beiden VERWEIS-Fktt, Lupo & all,
aber das hatte Daniel wohl eher nicht gemeint, wie seine nachfolgenden Ausführungen zeigen, sondern eher VERGLEICH, der in Kombi mit INDEX das Gleiche liefern kann wie S- bzw WVERWEIS.
Gruß, Luc :-?

Beiträge aus dem Excel-Forum zum Thema "Zeile ermitteln für Matrix-Funktion"