Live-Forum - Die aktuellen Beiträge
Datum
Titel
04.11.2025 16:13:59
AddIn erstellen und mit Passwort schützen
04.11.2025 15:31:37
Die Summendifferenz zwischen 01.01. und heute berechnen
04.11.2025 13:05:47
Frage Bed.Format mit Reihenfolge
04.11.2025 11:17:34
Datumsberechnung Konfirmation
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
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 Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suchmatrix mit Platzhalter

Forumthread: 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
Anzeige

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
Anzeige
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
.. , - ...
Anzeige
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
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
Anzeige
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
Anzeige
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
Anzeige
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
Anzeige
sorry für die Doppelten - owT
05.01.2017 15:09:52
Michael
;

Forumthreads zu verwandten Themen

SVERWEIS mit Platzhalter
Gerda

Hallo,
ich möchte in einer SVERWEIS-Formel mit Platzhalter arbeiten, was leider nicht funktioniert. Kann mir jemand sagen, wo mein Fehler liegt? Hier die Mustertabelle.
https://www.herber.de/bbs/user/85647.xlsx
Danke schon mal vorab
Gerda
04.06.2013 14:35:05
Thread-ID: 1316246
3 Beiträge
Suchen und ersetzen mit Platzhaltern
Christian
Hallo,
bin gerade dabei eine etwas größere Excel Datei zu erstellen. Dazu müsste ich Viele Zellen kopieren. Da Excel mir dabei immer die Felder in den Formeln anpasst, wollte ich diese mit dem $ versehen.
Habe daher versucht mit Suchen und ersetzen so etwas hinzubekommen: Suche nach $?4 un...
28.08.2015 13:44:56
Thread-ID: 1444876
3 Beiträge
suchen mit platzhalter (*) möglich ?
daniel
hallo !
ich wollte mal was testen.
in a1 steht "konrad".
nun habe ich in b1 geschrieben =WENN(A1="*r*";1;0)
zweck war, dass ich schauen wollte, ob in a1 ein "r" vorkommt.
leider klappt das mit den * nicht so ganz.
jetzt steht in b1 nur dann eine 1, wenn in a1 exakt "*r*" steht.<...
18.02.2004 12:50:52
Thread-ID: 384535
3 Beiträge
suchen und erstetzen mit platzhaltern
Alex

Hallo,
ich suche eine Möglichkeit in einem Excel-Dokument alle Einträge nach folgendem Schema zu ersetzen.
abc**F , wobei ** immer wechselende Zeichen darstellt, soll ersetzt werden durch
abc**P , wobei ** eben die gleichen Zeichen wie vorher enthalten soll.
Gibt es eine Möglich...
29.05.2008 12:03:39
Thread-ID: 981409
5 Beiträge
Dateisuche mit Platzhalter
Andy_69
Hallo zusammen,
Ich möchte bei meiner Suche ob die Datei vorhanden ist, zusätzlich ein Platzhalter "*" bei der Fz-Nummer im Pfad einbauen, da der Ordner zusätzlich noch Zeichen hinten dran haben kann.
Meine Idee war, ein Stern in folgender Zeile einzubinden, funktioniert aber so nicht:
P...
31.05.2022 10:09:49
Thread-ID: 1885529
5 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Suchmatrix mit Platzhaltern in Excel

Schritt-für-Schritt-Anleitung

  1. Datenstruktur vorbereiten: Stelle sicher, dass deine Rohdaten und die Referenztabelle klar strukturiert sind. Jede Spalte sollte ein Kriterium darstellen (z.B. Geschlecht, Alter, Wohnort, Straße).

  2. Formel zur Gruppierung einfügen: Verwende die folgende Formel, um die Gruppenzugehörigkeit zu bestimmen. Diese Formel nutzt den INDEX- und VERGLEICH-Funktionsaufruf und kann Platzhalter berücksichtigen:

    =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)))

  3. Matrixformel eingeben: Stelle sicher, dass du die Formel als Matrixformel eingibst, indem du Strg + Shift + Enter drückst.

  4. Platzhalter einsetzen: Wenn ein Kriterium nicht relevant ist, kannst du den Platzhalter * verwenden. Dies ermöglicht eine flexiblere Suche.

Häufige Fehler und Lösungen

  • Formel funktioniert nicht nach dem Kopieren: Stelle sicher, dass du die Matrixformel nach dem Kopieren immer mit Strg + Shift + Enter abschließt.

  • NV-Fehler: Wenn du auf einen NV-Fehler stößt, kannst du die WENNFEHLER-Funktion verwenden, um das Problem zu umgehen. Beispiel:

    =WENNFEHLER(INDEX(...); INDEX(...))

  • Langsame Berechnung: Bei großen Datenmengen kann es hilfreich sein, die automatische Berechnung auszuschalten und manuell zu berechnen, um die Performance zu verbessern.

Alternative Methoden

  • Hilfsspalten verwenden: Eine Möglichkeit, die Leistung zu verbessern, besteht darin, Hilfsspalten zu erstellen, in denen die relevanten Merkmale verkettet werden. Du kannst dann INDEX und VERGLEICH verwenden, um die Gruppenzugehörigkeit zu bestimmen.

  • VBA-Lösungen: Bei sehr großen Datenmengen ist eine VBA-Lösung oft schneller. Schreibe ein Makro, das die Gruppierung effizient durchführt.

Praktische Beispiele

  1. Beispiel für SVERWEIS mit Platzhaltern:

    =SVERWEIS(A1 & "*"; Ref!$A$1:$B$100; 2; FALSCH)

  2. Gruppierung basierend auf Alter und Geschlecht:

    Verwende die zuvor genannte Matrixformel, um Daten nach Geschlecht und Alter zu gruppieren. Füge Platzhalter hinzu, wo nötig.

Tipps für Profis

  • Kombination von Funktionen: Nutze eine Kombination aus WENN, VERGLEICH und INDEX, um komplexe Gruppierungen vorzunehmen.

  • Datenvalidierung: Achte darauf, dass die Daten in den Rohdaten und der Referenztabelle konsistent sind, um Fehler zu vermeiden.

  • Regelmäßige Updates: Halte deine Referenztabelle aktuell, um sicherzustellen, dass die Suchmatrix die neuesten Daten reflektiert.

FAQ: Häufige Fragen

1. Kann ich Platzhalter in XVERWEIS verwenden?
Ja, XVERWEIS unterstützt Platzhalter. Du kannst * für beliebige Zeichen verwenden.

2. Wie gehe ich mit mehreren Kriterien um?
Verwende die WENN-Funktion in Kombination mit INDEX und VERGLEICH, um mehrere Kriterien zu berücksichtigen.

3. Was tun, wenn die Formel zu langsam ist?
Überlege, ob du Hilfsspalten verwenden oder auf eine VBA-Lösung umsteigen möchtest, um die Performance zu verbessern.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige