Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Effizienter Index Vergleich mit mehreren Spalten

Effizienter Index Vergleich mit mehreren Spalten
09.06.2018 07:57:13
cg
Hallo zusammen,
das ist meiner ersten Eintrag im Forum, daher habe ich noch nicht herausgefunden wie ich mein Minimalbeispiel schön darstellen kann.
Aber zuerst zu meinem Problem.
Ich würde gerne den Buchstaben A in einer Matchingliste Abgleichen und mir dadurch eben einen Indexarray mit (hier im Beispiel mit M001, M004, M005,.. usw.) zurückgeben lassen. Diesen würden ich gerne dann direkt verwenden um in einer anderen Tabelle die Zeile mit den jeweilgen Indexen aufzuaddieren (hier im Beispiel 4+1+0).
Ich habe mich durch einige Foreneinträge mit Index-Vergleich oder Index und Kkleinste/KGrösste oder auch Index Funktionen mit Arrays gelesen. Diese haben mir jedoch alle nicht das gewünschte Ergebnisse geliefert, da Sie jeweils immer noch einen Index berücksichtigen und somit unpraktikabel sind.
A
A M001
B M002
C M003
A M004
A M005
A ...
M001 M003 M004 M005
4 3 1 0
Ich würde mich sehr über eine Antwort zur Lösung meines Problemes freuen.
Falls nötig ist VBA auch okay.
Viele Grüße
CG

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Effizienter Index Vergleich mit mehreren Spalten
09.06.2018 08:45:32
Hajo_Zi
warum 4 3 1 0?
Ich bin kein Formelmensch, aber diese Frage stellt sich mir schon.

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.
AW: Effizienter Index Vergleich mit mehreren Spalten
09.06.2018 10:12:30
cg
Hi Hajo, danke für die schnelle Antwort:
Ich habe das aus Excel rauskopiert. Vielleicht kannst du mir auch sagen wie ich das schöner darstellen kann.
Das sollte heißen unter: (steht in zwei Zeilen)
M001 steht 4
M003 steht 3
M004 steht 1
M005 steht 0
Als Auswertung erhalten dass wird M001+M004+M005 aufsummieren müssen.
Somit 4+1+0
Ist mein Problem verständlicher?
Anzeige
AW: Effizienter Index Vergleich mit mehreren Spalten
09.06.2018 10:13:58
Hajo_Zi
Gut Du möchtest meine Frage nicht beantworten.
Vielleicht sieht ein Formelmensch die Lösung?
Gruß Hajo
AW: Effizienter Index Vergleich mit mehreren Spalten
09.06.2018 11:27:04
cg
Das sind fiktive Werte Hajo, ist ja auch nur ein minimalbeispiel :)
AW: Effizienter Index Vergleich mit mehreren Spalten
09.06.2018 10:53:00
Robert
Hallo CG,
als einfachste Lösung würde mir eine mit einer Hilfsspalte einfallen. In Deiner ersten Liste kannst Du mit WVerweis neben der 2. Spalte (also neben M001, M002 usw) die Werte aus der zweiten Liste eintragen. Diese Werte kann man dann mit SummeWenn aufaddieren.
Gruß
Robert
AW: Effizienter Index Vergleich mit mehreren Spalten
09.06.2018 11:31:52
cg
Hi ja, wäre durchaus möglich.
Allerdings habe ich ca. 10000 Zeilen mit Werten und ca. 5000 Spalten mit Werten.
Daher wäre ein WVerweis nicht möglich wirklich effizient. Außerdem ist das kopieren/transponieren mit den Hilfsspalten vermutlich nicht die beste Wahl.
Würde mich daher über eine Alternativlösung freuen.
Vielleicht klappt das doch irgendwie mit Arrays?
Viele Grüße
Christian
Anzeige
AW: Effizienter Index Vergleich mit mehreren Spalten
09.06.2018 13:34:08
Robert
Hallo,
das verstehe ich. Wenn allerdings sichergestellt ist, das der Suchwert des WVerweis in der Suchmatrix enthalten ist und die Suchmatrix aufsteigend sortiert ist, kann man den WVerweis mit dem 4. Parameter "WAHR" statt "FALSCH" verwenden. Mit dem WAHR-Parameter sind die Verweis-Funktionen erstaunlich schnell, auch bei 10.000 WVerweis-Formeln über 5000 und mehr Spalten. Probiere es ruhig mal aus.
https://www.herber.de/bbs/user/122025.xlsx
Gruß
Robert
AW: so allerdings mit mE ungewollten Ergebnis, ...
09.06.2018 14:40:30
neopa
Hallo Robert,
... denn erfindet wegen des WAHR als 4. Parameters für die WVERWEIS()-Hilfsspaltenformel für z.B. M5002 den Wert für M5000. Das ist sicherlich so nicht gewollt.
Hatte eben das Thema auch vorgenommen und eine EIN-Formellösung (also ohne Hilfsspalte) gefunden. Diese allerdings nur unter der Voraussetzung, das der Suchvektor (bei Dir in Tabelle2!1:1) aufwärts sortiert gelistet ist)
Für Dein Beispiel erhalte ich als Ergebnis 2986803 und zwar mit meiner Formel:

=SUMMENPRODUKT(ZÄHLENWENN(Tabelle2!1:1;B1:B9000)*(A1:A9000=F1)*VERWEIS(B1:B9000;Tabelle2!1:1; Tabelle2!2:2))

Gruß Werner
.. , - ...
Anzeige
AW: so allerdings mit mE ungewollten Ergebnis, ...
09.06.2018 16:52:17
Robert
Hall Werner,
das ungewollte Ergebnis war ein Fehler meinerseits. Ich schrieb ja, dass es gewährleistet sein muss, dass der Suchbegriff in der Suchmatrix vorhanden ist. In meiner Testdatei war der Suchwert M5002 auch in der Suchmatrix vorhanden (ging bis M6000). Beim Upload stieß ich aber auf die 300kb-Grenze. Ich habe daher einige Daten gelöscht, um unter die 300 kb zu kommen. Leider habe ich dann nicht mehr kontrolliert, ob die Ergebnisse noch passen.
Wenn ich diesen Fehler korrigiere (M5002 also in die Suchmatrix aufnehme), komme ich zu dem selben Ergebnis wie Du. Bei meiner Kiste hier dauert die Neuberechnung, wenn ich den Suchwert in F1 ändere, bei Deiner Formel aber über 10 Sekunden (um das Ergebnis nicht zu verfälschen, habe ich dafür die WVerweise entfernt). Bei meinem Vorschlag mit der Hilfsspalte und den WVerweisen ist das neue Ergebnis bei Änderung von F1 sofort nach der Eingabe sichtbar.
Gruß
Robert
Anzeige
AW: dazu wäre jedoch anzumerken, dass ...
09.06.2018 18:49:41
neopa
Hallo Robert,
... meine Formel gewährleistet dagegen, dass wenn Suchbegriffe der Spalte B in der Suchmatrix nicht gefunden werden, diese diese auch unberücksichtigt bleiben. Auf meinen (10 Jahre alten) PC braucht es dafür ca. 8 Sekunden. Bei häufigen Änderungen wäre diese Zeit natürlich nicht vertretbar, bei seltenen Änderungen könnte man diese evtl. noch in Kauf nehmen.
Ein Prüfung der Suchbegriffe der Spalte B auf Vorhandensein in die Hilfsspaltenformel einzubauen scheitert mE dagegen. Ich musste jedenfalls die Berechnung nach einigen Minuten ohne Ergebnis abbrechen.
Bei einer derartigen Massendatenauswertung würde ich aber sowieso zu einer Aufbereitung der Daten mit Power Query tendieren.
Abschließend hierzu meinerseits: Auf evtl. Reaktionen Deinerseits oder von Christian kann ich nicht mehr reagieren, weil ich jetzt für eine gute Woche offline sein werde.
Wünsche Dir noch ein schönes WE
Gruß Werner
.. , - ...
Anzeige
AW: mit nur einer Formel möglich, allerdings ...
09.06.2018 14:53:25
neopa
Hallo Christian,
... unter der Voraussetzung, dass die Daten (aus Deinem Beispiel bei mir unten stehend in H1:K1) aufwärts sortiert gelistet sind.
Unter der zusätzlichen Voraussetzung das die auszuwertende Datensätze keine Leerzellen aufweisen, geht dies sogar mit nur einer Matrixfunktion(alität)sformel - siehe Formel E1, die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt. Dies Bedingung kann man erreichen, z.B. wenn man den Daten eine Überschrift zuweist und sie "Als Tabelle formatiert" und dann mit den Feldnamen dieser "intelligenten" Tabelle arbeitet- siehe E2.
Wenn jedoch Leerzeilen mit ausgewertet werden, dann bedarf es einer klassischen Matrixformel - siehe Formel E3.
In Roberts Beispiel hab ich meine Formel in E1 entsprechend angepasst.
 ABCDEFGHIJKLM
1TZText  9 Text:M001M003M004M005  
2AM004  9 Wert:3715  
3BM002           
4CM003  9        
5AM001           
6AM987           
7AM005           
8             
9             

Formeln der Tabelle
ZelleFormel
E1=SUMMENPRODUKT(ZÄHLENWENN(H1:K1;B2:B7)*(A2:A7="A")*VERWEIS(B2:B7;H1:K1;H2:K2))
E2=SUMMENPRODUKT(ZÄHLENWENN(H1:K1;TT_Tab[Text])*(TT_Tab[TZ]="A")*VERWEIS(TT_Tab[Text];H1:K1;H2:K2))
E4{=SUMME(ZÄHLENWENN(H1:Z1;B2:B99)*(A2:A99="A")*WENNFEHLER(VERWEIS(B2:B99;H1:Z1;H2:Z2); 0))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige

347 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige