Werte zuordnen

Werte zuordnen

08.01.2025 11:25:20

ichdu32

Hallo Gemeinde,

ich habe dieses Problem hier schon einmal gestellt und von {Boris} eine gut funktionierende Lösung erhalten. Allerdings hat sich im Verlauf herausgestellt, dass es noch komplizierter ist.

Ausgang: Tabelle_1 (Spalten [Nadel], [Werte], [Kategorie]) & Tabelle_2 (Spalten [Heuhaufen], [Kategorie], [Ziel])

Ziel: Wenn der Wert aus [Nadel] in [Heuhaufen] gefunden wird und der Wert aus [Kategorie] in Tabelle_1 & Tabelle_2 gleich ist, soll der Wert aus [Werte] in [Ziel] ausgegeben werden.

Probleme:
- Sowohl in [Heuhaufen] als auch in [Nadel] können doppelte Werte vorkommen (daher die Unterteilung durch [Kategorie])
- Sowohl in [Heuhaufen] als auch in [Nadel] können die Werte sehr lang werden. Ich habe mich in [Nadel] auf 250 Zeichen begrenzt
- Sowohl in [Heuhaufen] als auch in [Nadel] bestehen die Werte aus Zahlen, Buchstanben und Sonderzeichen
- Beide Tabellen leben und werden durch neue Werte immer wieder erweitert

Beispieldatei: https://www.herber.de/bbs/user/174748.xlsx

Ich möchte mich bereits im Vorraus für eure mühen herzlich bedanken!

AW: Werte zuordnen

08.01.2025 14:16:50

Peter

Hi.
Leider unterwegs daher erstmal keine Hilfsdatei von mir an dich.

Aber ich versuche trotzdem zu helfen. Für genau solche Fälle ist =Filter() da! in kurz
=Filter(rückgabespalte;suchkriterien)
Wobei die Suchkriterien wie folgt aufgebaut sind (suchspalte=bedingung) das verkettet man dann mit * (UND bedingung) oder + (Oder bedingung)

Kompliziert. erstmal ja. (Microsoft bericht lesen dann wird es hoffentlich klarer)

Das in deiner Tabelle
=FILTER(Tabelle_1[Werte];(Tabelle_1[Nadel]=[@Heuhaufen])*(Tabelle_1[Kategorie]=[@Kategorie]))

zack fertig!

Am rande gesagt richtig richtig gute Frage stellung, top erklärt mit einer richtig guten Hilfsdatei.!
Hoffentlich konnte ich helfen.

(erklärung Tabelle1_1[werte] das ist die rückgabe dann die () sind suchbedingungen das * sagt das alle () wahr sein müssen und @ bedeutet in der spalte.

AW: Werte zuordnen

08.01.2025 15:46:42

{Boris}

Hi,

G2:
=WENNFEHLER(LET(x;FILTER(Tabelle_1[Nadel];Tabelle_1[Kategorie]=[@Kategorie]);y;FILTER(Tabelle_1[Werte];Tabelle_1[Kategorie]=[@Kategorie]);INDEX(FILTER(y;NACHZEILE(x;LAMBDA(a;ISTZAHL(SUCHEN(a;[@Heuhaufen])))));1));"")

Wenn es immer nur maximal 1 Treffer sein kann, kann man sich am Ende den INDEX um das Filterergebnis sparen. Aber er schadet auch nicht.

VG, Boris

Da hab ich zu kompliziert gedacht...

08.01.2025 16:01:33

{Boris}

Hi,

...die erste Formel (mit LAMBDA) funktioniert, ist aber unnötig komplex.

Nimm diese hier für G2:
=WENNFEHLER(LET(x;FILTER(Tabelle_1[[Werte]:[Kategorie]];ZÄHLENWENNS([@Heuhaufen];"*"&Tabelle_1[Nadel]&"*"));FILTER(INDEX(x;;1);INDEX(x;;2)=[@Kategorie]));"")

VG, Boris

Und wenn es mehrere Treffer geben kann...

08.01.2025 16:04:08

{Boris}

Hi,

...dann besser am Ende noch INDEX drumwickeln.

=WENNFEHLER(LET(x;FILTER(Tabelle_1[[Werte]:[Kategorie]];ZÄHLENWENN([@Heuhaufen];"*"&Tabelle_1[Nadel]&"*"));INDEX(FILTER(INDEX(x;;1);INDEX(x;;2)=[@Kategorie]);1));"")

VG, Boris

AW: Und wenn es mehrere Treffer geben kann...

08.01.2025 23:22:49

Peter

Moin Boris,
interessante Lösung! (Muss sagen, dass ich meine aber einfacher zu lesen finde ;) )
Muss aber bei meiner ergänzen, dass ich das Wildcardzeichen "*" noch in die Formel ergänzen hätte sollen, dass habe ich vorhin überlesen. Blöd.

Ich wollte aber mal so frei sein, den Leuten deine Formel aufzudröseln. Ich hoffe, dass dies keine Problem ist. Denn die ist schon Highlevel (habe unten auch noch ein kleinen Änderungsvorschlag, da bitte stellungnehmen boris ob ich einen Fall vergessen habe)

Formel
=WENNFEHLER(
LET(x;
FILTER(Tabelle_1[[Werte]:[Kategorie]];ZÄHLENWENN([@Heuhaufen];"*"&Tabelle_1[Nadel]&"*"));
INDEX(FILTER(INDEX(x;;1);INDEX(x;;2)=[@Kategorie]);1));"")

Boris Formel besteht aus drei Teilen:
Wennfehler - Suchwerte nicht gefunden behandeln
Filter - Alle übereinstimmungen mit einer Suchbedingung auflisten
Index - Zweite Suchbedingung angeben.

D.h. Konkret fangen wir mit dem Kern an. Dem Filter
Für die Leute die meine Filter erklärung gelesen haben, Boris notation weicht gerade bei der Rückgabematrix ab. Boris gibt mehrere Werte zurück

1.ALL
Aufgedröselt:
FILTER(Tabelle_1[[Werte]:[Kategorie]];ZÄHLENWENN([@Heuhaufen];"*"&Tabelle_1[Nadel]&"*"));

1.1(Rückgabe)
Tabelle_1[[Werte]:[Kategorie]]
-> dies gibt alle Spalten der Tabelle_1 zurück die zwischen Werte und Kategorie liegen (einschließelich). Die die Bedingungen erfüllen.
-> Die Bedingungen findet sich dahinter

1.2(Bedingunge)
ZÄHLENWENN([@Heuhaufen];"*"&Tabelle_1[Nadel]&"*")
Das Herzstück von Boris & obendrein ziemlich schlau.
Boris nutzt die Wildcard "*" aus. das bedeutet es kann beliebiger Text vor einem Suchstring stehen. Klingt trivial, aber man muss verstehen, dass Platzhalter nicht überall unterstützt werden, bzw. nicht sonderlich intuitiv.
Ich muss ausholen, denn Zählenwenn klingt im bereich der Bedingungen komisch.

Wie versprochen ausgeholt.
Was wollen wir?
WIr wollen alle Reihen finden in denen der Text von Häuhaufen steht.

Dh.
Suchwert "Heu"

Neuheu -> bedingung gefunden | WAHR | Reihe 1
Stroh -> nicht gefunden | FALSCH | Reihe 2
Heu -> bedingungen gefunden | WAHR | Reihe 3

Diese logik klingt erstmal trivial, besonders wenn man die funktion Suchen kennt.
=Suchen("Heu";"Neuheu") = Wahr
=Suchen("Heu";"Stroh") = #WERT
-> Ausprobieren!

Das problem ist, dass wir über mehrere Werte suchen und da werden die Bedingungen der Formeln in Arrays gepackt, d.h. wenn wir
=Suchen("heu";{Neuheu;Stroh;Heu}) kommt die Rückgabe {1;#Wert;1}. Damit kann Filter nicht arbeiten, denn Filter braucht immer ein WAHR oder ein Falsch in der Rückgabe also {WAHR;FALSCH;WAHR} oder in binär {1;0;1} denn was filter macht ist, er überprüft erst und Fillter dann die relativen Reihen bei denen die Rückgabe wahr ist (in unserem fall die Reihe 1 & 3).

Wenn wir intuitiv arbeiten würden dann müssten wir folgende Bedingung in Excel schreiben.
Bedingung: WENNFEHLER(SUCHEN(Tabelle_2[@Heuhaufen];Tabelle_1[Nadel]);0) man merkt aber, dass ist nicht mehr Intuitiv.
Boris hat daher sich entschiedend das Leben weiter zu machen. Er verwendet =Zählenwenn(suchwerte), das perfide? Zählenwenn funktioniert wie suchen, nur hat Zählenwenn die Rückgabe 0 wenn ein Wert nicht gefunden wird. Das ist wichtig, da im normalen nutzungsfall am ende der Bereich zusammengezählt wird. Doch nicht in der Filterfunktion dort wird {1;0;1} ausgewertet.

Viel Blabla. Das was herauskommt bei dem Schritt ist:
Vier44 Anton
Drei33 Berta

-> also die Werte der Spalten werte bis Kategorie (also werte & kategorie) die mit der Suchbedingung übereinstimmt
=FILTER(Tabelle_1[[Werte]:[Kategorie]];ZÄHLENWENN(Tabelle_2[@Heuhaufen];"*"&Tabelle_1[Nadel]&"*")) oder identisch
=FILTER(Tabelle_1[[Werte]:[Kategorie]];WENNFEHLER(SUCHEN(Tabelle_2[@Heuhaufen];Tabelle_1[Nadel]);0))

Und nun muss schritt zwei her denn Anton / Berta sind ja teil der Kategorie. Ein zweites Suchkriterium
INDEX(FILTER(INDEX(x;;1);INDEX(x;;2)=[@Kategorie]);1));"")

x ist
Vier44 Anton
Drei33 Berta
das hat boris mittels der Namensgebendenfunktion Let erreicht

Boris will nun Spalte 2 nach der Suchkategorie filtern und als rückgabe die erste Spalte haben.
D.h. XVERWEIS([@Kategorie];INDEX(x;;2);INDEX(x;;1);"")) das ist äquivalent zu INDEX(FILTER(INDEX(x;;1);INDEX(x;;2)=[@Kategorie]);1));"")
@boris hat es einen grund warum du index anstelle von Xverweis nutzt?

3 wennfehler()
Das verhindert den Fall, dass eine Reihe keine Übereinstimmung hat und wechselt diese mit #

Verbesserungsvorschlag

08.01.2025 23:25:52

Peter

moin boris, wenn du mehrere Treffer vermeiden möchtest dann würde ich dir empfehlen am ende einen Xverweis anstelle einer Filterfunktion zu nutzen, ist in meinen Augen besser zu lesen.

AUS
=WENNFEHLER(LET(x;
FILTER(Tabelle_1[[Werte]:[Kategorie]];ZÄHLENWENN([@Heuhaufen];"*"&Tabelle_1[Nadel]&"*"));
INDEX(FILTER(INDEX(x;;1);INDEX(x;;2)=[@Kategorie]);1));"")

WIRD
=WENNFEHLER(
LET(
x;FILTER(Tabelle_1[[Werte]:[Kategorie]];ZÄHLENWENN([@Heuhaufen];"*"&Tabelle_1[Nadel]&"*"));
XVERWEIS([@Kategorie];INDEX(x;;2);INDEX(x;;1);""));"")

Klar - der XVERWEIS reicht hier aus...oT

09.01.2025 17:33:05

{Boris}

VG, Boris

AW: Und wenn es mehrere Treffer geben kann...

09.01.2025 13:16:33

ichdu32

Hallo {Boris},

erneut vielen Dank für deine Hilfe. Diese Formel funktioniert genau wie ich es brauche und dazu scheint sie wesentlich performanter zu sein, auch bei größeren Datenmengen.
Gut lesbar und damit nachvollziebar ist sie für mich auch.

Noch einmal, vielen Dank!

AW: Werte zuordnen

08.01.2025 14:38:45

peter

Der Peter aus der Zukunft 5 Minuten später ist unzufrieden wie er beantwortet hat. Damit andere die das zu Gesicht bekommen verstehen was ich mache.
Falls sich jemand interessiert ich habe genau zu der Frage eine Große beispielliste zu hause auf dem Rechner, die kann ich die tage in den thread hängen.

'Kern
=Filter() das ist eine recht neue Funktion. Sie ist wie ein Xverweis nur kann sie mehrere Rückgabewerte geben. + andere Syntax.
Aller anfang macht die übersetzung. In der Dok steht

=filter(matrix;einschließen;wennleer)
Was bedeutet Matrix? Matrix sind mehrere Spalten und Zellen, der name wurde gewählt um zu zeigen. "SCHAU ich kann viele Rückgabewerte haben".
Wir wollen aber nur einen Haben, der liegt in einer Spalte also kommt anstelle Matrix unsere Rückgabespalte.

hier Tabelle[Werte] oder A2:A40 TIpp falls [] eine unbekannte notation ist schaut euch LIstObjekt tutorials an (oder probiert über strg + t euch das selber anzueignen. sprengt hier den rahmen)
-
Durchatmen.

Attribut einschließen.
Das ist im kern wie UND()/ODER() man schreibt nur das und oder in die mitte.

1.Das geht gut in Menschlicher sprache.
Sagt einfach laut: Ich moechte die werte haben bei denen heu = stroh ist und die kategorie des heu gleich der Kategorie des Strohs.
2.Jetzt übersetzt ihr die Heu=stroh in die Tabellen spalten
Tabelle1[Heu]=Tabelle2[Stroh] und Tabelle1[Kategorie]=Tabelle2[Kategorie].
3. Jetzt klammern um die Bedingung und das und wird zu *
(Tabelle1[Heu]=Tabelle2[Stroh]) * (Tabelle1[Kategorie]=Tabelle2[Kategorie])
Zusatz wer oder in seinen sätzen nutzt ersetzt oder mit einem + ;)

ihr könnt das mal spaeshalber irgendwo in excel eingeben dann werdet ihr die gefiltertete Liste haben. Dort könntet ihr jetzt euren Xverweis nutzen.
Doch das wäre zuviel aufwand deswegen sind wir schlau.

Da ein Listobjekt vorliegt und unsere suchwerte rechts stehen können wir in der Tabelle wo wir sind @ vor die Name der Spalte schreiben. Das sorgt dafuer, dass nur die aktuelle Reihe sortiert wird. (schaut euch die Farben an, dann ergeben die worte Sinn). Falls keine farben leuchten dann kann es daran liegen, dass ihr die formel nicht im ListObjekt eingegeben habt.

A:
Filter(tabelle1[wert];(Tabelle1[Heu]=Tabelle2[@Stroh]) * (Tabelle1[Kategorie]=Tabelle2[@Kategorie]))
Done.

jetzt ersetzen wir noch die nervigen NichtGefunden Fehler (hier Calc)
B:
Wennfehler(Filter(tabelle1[wert];(Tabelle1[Heu]=Tabelle2[@Stroh]) * (Tabelle1[Kategorie]=Tabelle2[@Kategorie]));"") - also blanke zellen.

und wir sind durch.

AW: Werte zuordnen

08.01.2025 14:42:41

PeterDerSverweis

ach und wenn das jemand mit sverweis macht.

https://www.tabstr.de/datenanalyse/sverweis/
hier ist ein fantastisches Tutorial!

AW: Werte zuordnen

08.01.2025 23:39:47

PeterKorrektur

Korrektur nachdem ich Boris lösungsvorschlag gesehen habe ist mri aufgefallen, das ein Teil der Aufgabe fehlt (keine Wildcards),
den behebe ich schnell

Filter(tabelle1[wert];(Tabelle1[Heu]=Tabelle2[@Stroh]) * (Tabelle1[Kategorie]=Tabelle2[@Kategorie]))
Done.

=WENNFEHLER(
FILTER(Tabelle_1[Werte];
(WENNFEHLER(SUCHEN(Tabelle_1[Nadel];[@Heuhaufen]);0))*(Tabelle_1[Kategorie]=[@Kategorie]));"")

Hier kann man sehen, dass die formel identisch zu der von Boris ist (& meinen vorherigen Fehler sehen)
https://www.herber.de/bbs/user/174764.xlsx

Forumthread: Werte zuordnen

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Werte zuordnen

Forumthread: Werte zuordnen

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

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread