Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
26.04.2024 16:05:28
PivotChart-Stapeldiagramm erstellen
26.04.2024 15:34:42
Überlagernde Textbox-Ansicht
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1628to1632
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1628to1632
1628to1632
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
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

331 Forumthreads zu ähnlichen Themen

Info aus mehreren Spaltenüberschriften entnehmen
mopitz

Hallo,
Da mein Problem etwas komplex ist, füge ich eine Datei zur Veranschaulichung an.:
https://www.herber.de/bbs/user/56058.xls
Ich möchte in der ersten Tabelle zu den Spalte "Kunde"(kommt nur einmal vor) die Technologie reinschreiben lassen. Diese Info erhalte ich aus der "Tabell...
16.10.2008 13:34:19
Thread-ID: 1016847
7 Beiträge
Anzeige
mehrere Wenn abfragen in einer Formel
skippy

Hallo stehe im mom auf den schlauch
möchte gern folgende Abfrage machen krieg aber die Formel nicht hin
Wenn e7-d7>=10;2;0 wenn e7-d7>8;1,5;0 wenn e7-d7>6;1;0 wenn e7-d7>4;0,5;0
Wie muss ich die Formel schreiben, bekomme immer einen Syntaxfehler wenn ich daraus eine Fo...
01.01.2009 21:42:55
Thread-ID: 1036302
4 Beiträge
Filter - Kriterium in mehreren Spalten
walter

Hallo,
ich stöbere nun schon seit zwei Tagen in der Excel-Hilfe und in diesem Forum und finde keine Lösung für mein Problem:
Ich habe eine Liste, in der jeder Datensatz 5 Kategorien zugeteilt ist. Das Problem ist dabei, dass ein und dieselbe Kategorie in verschiedenen Spalten stehen kan...
15.01.2009 08:33:57
Thread-ID: 1039897
3 Beiträge
Anzeige
Auswahlliste über mehrere Spalten
Roland

Hallo zusammen,
folgendes Problem:
In Tabellenblatt 2 sind Kontaktdaten vorhanden.
Nun möchte ich im Tabellenblatt 1 im Feld "Name" eine Auswahlliste mit Bezug auf Tabellenblatt 2 erstellen. Das ist mit der Gültikeitsprüfung ja auch kein Problem.
Jetzt möchte ich, dass auch in d...
17.07.2009 10:49:06
Thread-ID: 1089312
19 Beiträge
Sverweis und suche suche über mehrere Spalten
Sabine

Hi
Folgendes Problem. In Tabelle1 Spalte A stehen werte, die in Tabelle 2 in Spalte B-F gesucht werden sollen und dann der wert aus Spalte X ausgegeben werden.
Mit Sverweis geht das nicht, aber wie dann?
30.07.2009 18:44:34
Thread-ID: 1092620
2 Beiträge
Zählenwenn in mehreren Ranges Highspeed
Jan

Hallo,
Ich muss in mehreren Bereichen überprüfen, ob eine der Zellen in den Bereichen einen Wert enthält. Da die Excelfunktion "Zählenwenn" nur einen Bereich unterstützt, habe ich mir folgende Funktion in VBA geschrieben.
Function ZÄHLE(Wert As String, ParamArray Rng() As Variant) As I...
03.10.2009 14:08:20
Thread-ID: 1106968
15 Beiträge
Bedingte Formel über mehrere Spalten/Zeilen?
Sebastian

Ich möchte, das ein bestimmter Bereich Rot wird wenn in einer Zelle ein "ja" steht.
z.B. sollen die Felder A10 bis G10 Rot werden wenn in der Spalte M10 ein ja steht.
Das geht ja auch mit der "Bedingten Formatierung". Dort hatte ich eingegebem:
Zellwert ist ungleich =$M$10="ja" (un...
22.10.2009 10:51:11
Thread-ID: 1111308
2 Beiträge
Formeln in mehreren Sheets ändern
Sven

Hallo,
ich habe eine Tabelle mit meheren Seiten
Persdaten
Stammdaten
23
24
25
usw.
jetzt habe ich gemerkt, dass ich in allen tabellen Formelfehler habe (außer in Persdaten)
jetzt möchte ich per Makro gern in allen Tabellenblättern diese Fehler beheben.
wi...
16.11.2009 02:44:50
Thread-ID: 1117475
2 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
6
2
3
2
2
18
1
14
1
1
2
2
2
5
2
4
3
5
8
4
3
3
3
11
13