Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1532to1536
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
1532to1536
1532to1536
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suchmatrix mit Platzhalter

Suchmatrix mit Platzhalter
04.01.2017 14:31:46
Chris
Hallo zusammen,
ich möchte über eine Referenztabellen mit 4 Kriterien eine Menge an Rohdaten Gruppieren. Mein bisheriger Ansatz war der Abgleich über eine Index Vergleich Formel, dass funktioniert auch ganz gut solange alle 4 Kriterien benötigt werden - hier kommt mein Problem ins Spiel:
Die Gruppierung benötigt nicht immer alle 4 Kriterien, teilweise auch nur 1. Gibt es hier die möglich in der Referenztabelle sprich der Suchmatrix einen Platzhalter vorzugeben, sodass der Verweis hier immer eine Übereinstimmung findet?
Jede mögliche Kombination in der Referenz aufzunehmen ist nicht möglich.
Vielen Dank im voraus & viele Grüße
Chris

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Beispieldatei?
04.01.2017 14:55:06
Michael
Hi Chris,
was meinst Du "gruppieren" genau? Filtern?
Kannst Du eine Beispieldatei hochladen?
Gruß,
Michael
AW: Beispieldatei?
04.01.2017 15:26:45
Chris
Hallo Michael,
ich habe Merkmale in den Rohdaten (je eine Spalte) wie Geschlecht, Alter, Wohnort und Straße. Daraus sollen Gruppen gebildet (neue Spalte) werden, sodass ich die Daten dann über z.B. Pivot-Tabellen weiterverarbeiten kann.
Gruppe1 = weiblich, 20-25 Jahre, Berlin, Straße-xy
Gruppe2 = männlich, 20-25 Jahre, Berlin, Straße-xy
Gruppe3 = weiblich, 26-30 Jahre, München, alle Straßen
Gruppe4 = Firma, restliche Kriterien sind nicht relevant
die Gruppe will ich in einer Spalte stehen haben, sodass ich die Daten dann über z.B. Pivot-Tabellen weiterverarbeiten kann. Hab über 150 Tsd. Zeile und div. Spalten an Daten... eine Bsp. Datei könnte ich heute Abend erstellen und Hochladen sofern meine Ausführung noch nicht alle Klarheiten beseitigt hat :)
Gruß
Chris
Anzeige
wäre schon recht,
04.01.2017 16:13:16
Michael
Chris,
weil dann gleich alles da steht, wo es hingehört, und Du hinterher nicht mehr groß anpassen mußt.
Gruß,
M.
AW: wäre schon recht,
04.01.2017 16:28:09
Daniel
Hi
da würde ich zunächst für jede Gruppe eine eigene Spalte bilden und dort mit einer Formel die Gruppenzugehörigkeit prüfen, z.B. Für Gruppe 1
=1*Und(Geschlecht="w";Alter>=20;Alter in der Zelle steht dann 1 oder 0, je nachdem ob die Bedingungen erfüllt sind.
in einer weitern Spalte kannst du dann per VERGLEICH(1;ZellenMitGruppen;0) die Gruppennummer bestimmen (dh die relative Nummer der Spalte, zu deren Gruppe der Datensatz gehört) und des dann für die Pivotauswertung verwenden.
gruß Daniel
Anzeige
Bsp Datei
04.01.2017 16:46:23
Chris
Hi,
@ Daniel: habe aktuell 77 Zeile an zu verwendenden Kombination, befürchte wenn es hier Änderungen gibt bekomme ich das in einer Formel in entsprechend vielen Hilfsspalten nicht mehr angepasst nach 3 Monaten :)
Hier der Versuch einer Beispieldatei, Original kann ich natürlich nicht hochladen
https://www.herber.de/bbs/user/110335.xlsx
Gruß
Chris
AW: Bsp Datei
04.01.2017 19:05:39
Daniel
Hi
probier mal diese Formel.
=INDEX(Ref!E:E;MAX((A6=Ref!$A$7:$A$16)*(B6=Ref!$B$7:$B$16)*(C6=Ref!$C$7:$C$16) *((D6=Ref!$D$7:$D$16)+(Ref!$D$7:$D$16="*"))*ZEILE(Ref!$E$7:$E$16)))
muss auch als Matrixformel eingegeben werden.
Wenn die Zuordnung nicht eindeutig ist, dh eine Person mehren Gruppen zugeordnet werden kann, dann wird die Gruppe mit der höchsten Zeilennummer verwendet.
überall dort, wo ein Merkmal egal sein kann, musst du die Abfrage so erweitern wie für Spalte D (Merkmal 4) dieser Faktor wird dann 1, wenn der Wert übereinstimmt oder in der Gruppenzuordung ein * steht.
das * entspricht einer UND-vernküpfung von Wahrheitswerten, das + einer Oder-Verknüpfung.
(in den Rohdaten darf dann natürlich kein * stehen, denn wenn beide Bedingungen wahr sind, wäre das Ergbnis nicht 1 sondern 2 und würde dein Ergebnis verfälschen.
Gruß Daniel
Anzeige
AW: günstger ist evtl eine Hilfsspaltenlösung ...
04.01.2017 19:30:37
...
Hallo Chris,
... Du schreibst in Deiner Datei, das Du mit 2x140.000 auszuwertenden Datensätzen rechnest. Für mich sind das Massendaten. Da könnte anstelle einer Matrixformellösung eine Hilfsspaltenlösung günstiger sein. Dazu verkette jeweils die 4 Merkmale in der Rohdatentabelle und in der Reftabelle mit einem zusätzlichen Trennzeichen in je einer Hilfsspalte. Sortiere die Reftabelle nach dem Ergebnis dieser Hilfsspalte und ermittele dann Deine zugehörige neue Gruppe mit INDEX() und VERGLEICH() auf Basis der Hilfsspaltenergebnisse.
Gruß Werner
.. , - ...
AW: günstger ist evtl eine Hilfsspaltenlösung ...
04.01.2017 19:43:18
Daniel
Hi
so wie ich das verstanden habe, ist die Referenzliste mit den Gruppenzuordnungen nur 77 Zeilen lang.
das könnte noch machbar sein.
Gruß Daniel
Anzeige
AW: wie ich es verstanden habe ...
04.01.2017 20:09:37
...
Hallo Daniel,
... müsste diese Matrixformel dann 28.000 mal eingesetzt werden.
Gruß Werner
.. , - ...
AW: wie ich es verstanden habe ...
05.01.2017 08:39:13
guenni
Hab Dir mal eine Referenztabellen für die Merkmale 3 und 4 und eine Sverweisformel gebaut, so dass Du in den Rohdaten mit einer Spalte auskommst
https://www.herber.de/bbs/user/110341.xlsx
Gruß,
Günther
AW: wie ich es verstanden habe ...
05.01.2017 09:28:41
Chris
vielen Dank für die Ideen.
Habe das ganze jetzt mit 3 Verschachtelungen Wennfehler mit Index/Vergleich gelöst.
Gruß
Chris
VBA - nur interessehalber
05.01.2017 15:07:49
Michael
Hi zusammen,
bei der Datenmenge würde ich persönlich eine VBA-Lösung vorziehen, weil ich mir vorstellen könnte, daß die Tabelle mit Formeln etwas träge werden würde: nicht umsonst hatte Chris die automatische Berechnung ausgeschaltet.
Ich habe mal 77 Refs erzeugt und mit 3200 Datenzeilen getestet: das sind nur ein paar Millisekunden:
https://www.herber.de/bbs/user/110351.xlsm
Hochgerechnet auf 140.000 sollte weniger als 1 Sekunde rauskommen - besser als eine träge Mappe.
Schöne Grüße,
Michael
Anzeige
VBA - nur interessehalber
05.01.2017 15:08:00
Michael
Hi zusammen,
bei der Datenmenge würde ich persönlich eine VBA-Lösung vorziehen, weil ich mir vorstellen könnte, daß die Tabelle mit Formeln etwas träge werden würde: nicht umsonst hatte Chris die automatische Berechnung ausgeschaltet.
Ich habe mal 77 Refs erzeugt und mit 3200 Datenzeilen getestet: das sind nur ein paar Millisekunden:
https://www.herber.de/bbs/user/110351.xlsm
Hochgerechnet auf 140.000 sollte weniger als 1 Sekunde rauskommen - besser als eine träge Mappe.
Schöne Grüße,
Michael
VBA - nur interessehalber
05.01.2017 15:08:03
Michael
Hi zusammen,
bei der Datenmenge würde ich persönlich eine VBA-Lösung vorziehen, weil ich mir vorstellen könnte, daß die Tabelle mit Formeln etwas träge werden würde: nicht umsonst hatte Chris die automatische Berechnung ausgeschaltet.
Ich habe mal 77 Refs erzeugt und mit 3200 Datenzeilen getestet: das sind nur ein paar Millisekunden:
https://www.herber.de/bbs/user/110351.xlsm
Hochgerechnet auf 140.000 sollte weniger als 1 Sekunde rauskommen - besser als eine träge Mappe.
Schöne Grüße,
Michael
Anzeige
AW: Suchmatrix mit Platzhalter
04.01.2017 17:39:15
Hans
Wie wäre es wenn du in der Matrixformel mit WENNFEHLER arbeitest. Also wenn es einen NV-Fehler gibt, soll er nur die ersten 3 Spalten durchsuchen. Bei mir spinnt deine und meine Matrixformel allerdings, da ich sie nicht kopieren kann bzw. nach dem Kopieren nicht richtig funktioniert. Schließe ich jede einzeln mit Strg+Shift+Enter ab, geht es komischerweise.
=WENNFEHLER(INDEX(Ref!$E$7:$E$16;VERGLEICH(A6&B6&C6&D6; Ref!$A$7:$A$16&Ref!$B$7:$B$16&Ref!$C$7:$C$16&Ref!$D$7:$D$16;0));INDEX(Ref!$E$7:$E$16; VERGLEICH(A6&B6&C6;Ref!$A$7:$A$16&Ref!$B$7:$B$16&Ref!$C$7:$C$16;0)))
Anzeige
Danke an alle
05.01.2017 09:03:52
Chris
hatte sogar den Wennfehler in meiner Ursprungsdatei drin, aber ihn so zu verwenden bin ich nicht darauf gekommen :)
Das Problem, dass sich Formeln nur einzeln aktualisieren lassen habe ich von Zeit zu Zeit - weiß allerdings nicht woran es liegt :/
vielen Danke an alle die mit gedacht haben
Gruß
Chris
VBA - nur interessehalber
05.01.2017 15:04:23
Michael
Hi zusammen,
bei der Datenmenge würde ich persönlich eine VBA-Lösung vorziehen, weil ich mir vorstellen könnte, daß die Tabelle mit Formeln etwas träge werden würde: nicht umsonst hatte Chris die automatische Berechnung ausgeschaltet.
Ich habe mal 77 Refs erzeugt und mit 3200 Datenzeilen getestet: das sind nur ein paar Millisekunden:
https://www.herber.de/bbs/user/110351.xlsm
Hochgerechnet auf 140.000 kommt vielleicht 1 Sekunde raus - nicht verkehrt für ein schnell formuliertes Makro.
Schöne Grüße,
Michael
Anzeige
AW: Suchmatrix mit Platzhalter
05.01.2017 15:05:36
Michael
Hi zusammen,
bei der Datenmenge würde ich persönlich eine VBA-Lösung vorziehen, weil ich mir vorstellen könnte, daß die Tabelle mit Formeln etwas träge werden würde: nicht umsonst hatte Chris die automatische Berechnung ausgeschaltet.
Ich habe mal 77 Refs erzeugt und mit 3200 Datenzeilen getestet: das sind nur ein paar Millisekunden:
https://www.herber.de/bbs/user/110351.xlsm
Hochgerechnet auf 140.000 kommt vielleicht 1 Sekunde raus - nicht verkehrt für ein schnell formuliertes Makro.
Schöne Grüße,
Michael
sorry für die Doppelten - owT
05.01.2017 15:09:52
Michael

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige