Jan 05, 2024, updated at 16:32:41 (UTC)

1313

Excel Formel gesucht (index,vergleich,filter)

Hallo zusammen,

ich habe folgende Tabelle (siehe Bild) und möchte gerne alle Werte zu einer Suche angezeigt bekommen.
Angenommen ich suche in der ersten Zeile nach dem Wert "Zucchini", möchte ich alle Ergebnisse mit einem "g" gelistet bekommen. Also nur die Werte "Mais" und "Rote Bete".

Ich habe schon verschiedene Formeln mit Filter, Index und Vergleich getestet. Ich komme jedoch nicht zu einem Ergebnis.

Kann mir hier jemand weiterhelfen?

Vielen Dank

Please also mark the comments that contributed to the solution of the article

Content-Key: 33574827202

Url: https://administrator.de/contentid/33574827202

Printed on: April 27, 2024 at 22:04 o'clock

13 Comments

Latest comment

Im Prinzip sind deine Daten pivotiert, kann Excel eine Art UNPIOVT in eine andere Tabelle machen? Dann hättest du eine Zurdnungstabelle mit Spalte A, Zeile 1 als Spalte B und der Unterscheidung g / s. Da könnte man dann ganz hervorragend suchen.

Ich weiß nicht ganz was du meinst? Wie soll ich die Daten besser zuordnen als in dieser Tabelle? Wie meinst du das?

Mit ner Matrixformel kein Problem, muss zwingend mit STRG+SHIFT+ENTER abgeschlossen werden und dann nach unten ziehen ...

Nur für die kleine Beispiel Matrix (Bereiche für deine Tabelle anpassen) Daten in der Tabelle sind nur fiktiv ...

=WENNFEHLER(INDEX($B$1:$G$1;1;KKLEINSTE(WENN(BEREICH.VERSCHIEBEN($B$1:$G$1;VERGLEICH($H$11;$A$2:$A$7;0);0)="g";SPALTE($B$1:$G$1)-1);ZEILE(A1)));"")  

Download Tabelle

pj

Hi pjumper,

perfekt das hat bestens funktioniert.

Ich würde die Formel gerne noch modifizieren. Jedoch gelingt mir auch das nicht.
Ich möchte, dass die Liste mit dem kleinsten Wert beginnt. Mit dem kleinsten meine ich, der Wert mit den wenigsten "g"

Zitat von @joe2017:
Ich möchte, dass die Liste mit dem kleinsten Wert beginnt. Mit dem kleinsten meine ich, der Wert mit den wenigsten "g"

Hast ja das ganze Wochenende Zeit für die Hausaufgabe

. Mit Zählenwenn die g's jeder Reihe zählen und die Matrix danach aufsteigend sortieren.

https://we.tl/t-s80CDNxNfC

Also die Sortierung an sich bekomme ich schon hin. Ich wollte aber keine extra Spalte, sondern alles in einer Formel machen.

Verstehe erst mal die obigen Formeln vollständig dann bekommst du diesen Schritt auch selbst hin. Sonst lernst du hier ja nüscht wenn wir dir hier alles bis ins Kleinste vorbeten. Das obige Aufschlüsseln auf eine separate Spalte sollte nur dem "Gedanken-Verständnis" dienen nur noch logisch miteinander verbinden

, viel Spaß dabei. Wir geben dir mal das ganze Wochenende Zeit dafür

Also nachdem ich erstmal nur den kleinsten Wert benötige würde ich folgende Formel nutzen:

=INDEX(I:I;VERGLEICH(MIN(J:J);J:J;0))

Schön wäre jedoch wenn ich die Formel ohne die Zwischentabelle (I und J) direkte nutzen könnte.
Quasi alles in einer Formel. Zu Broccoli alle Werte mit einem "g" suchen, und den Wert ausgeben, der die kleinste Anzahl von "g" besitzt. Ich bekomme das alle mit den Zwischentabellen hin. Jedoch nicht ohne. Ich habe bereits versucht die Formeln miteinander zu Verbinden, jedoch kann das ja nicht funktionieren, weill ich die Formeln in der Spalte I und J ja nach unten ziehen muss.

Folgende Formeln verwende ich:
Zelle I11

{=WENNFEHLER(INDEX($B$1:$G$1;1;KKLEINSTE(WENN(BEREICH.VERSCHIEBEN($B$1:$G$1;VERGLEICH($H$11;$A$2:$A$7;0);0)="g";SPALTE($B$1:$G$1)-1);ZEILE(A1)));"")}  

Zelle J11

{=WENNFEHLER(ZÄHLENWENN(BEREICH.VERSCHIEBEN($B$1:$G$1;VERGLEICH((WENNFEHLER(INDEX($B$1:$G$1;1;KKLEINSTE(WENN(BEREICH.VERSCHIEBEN($B$1:$G$1;VERGLEICH($H$11;$A$2:$A$7;0);0)="g";SPALTE($B$1:$G$1)-1);ZEILE(A1)));""));$A$2:$A$7;0);0);"g");"")}  

Zelle K11

=SORTIERENNACH(I11:I29;J11:J29)

Ich habe bereits versucht in der Fornel in Zelle K11 di I11:I29 durch die erste Formel und J11:J29 durch die Zweite Formel zu ersetzten. Aber das funktioniert ja nicht, da die Formeln nach unten kopiert werden.

Mir genüglt also lediglich der Wert mit den kleinsten "g" passend zu H11 (Broccoli)
Ich verzweifel daran!

Zitat von @joe2017:

Ich weiß nicht ganz was du meinst? Wie soll ich die Daten besser zuordnen als in dieser Tabelle? Wie meinst du das?

Das, was du hast, ist eigentlich keine Daten-Tabelle sondern eine Matrix oder ein Pivot. Ich komme aus der Datenbank-Welt, da wäre das eine Zuordnungstabelle wie bei einer n:m-Beziehung. Eine Spalte hätte die Werte aus Spalte A, eine Spalte hätte die Werte aus Zeile 1 und eine Spalte wäre s oder g. Für leere Zellen gäbe es gar keine Einträge in der Tabelle.

Wenn die Excel-Lösung wie von @10138557388 angeregt nicht zum Ziel führt, kann man noch gucken ob es UNPIVOT oder etwas vergleichbares in Excel gibt um die von mir beschriebene Tabelle zu erzeugen, die kann man dann filtern.

Aber so wie du das jetzt beschreibst würde in Spalte A und B das gleiche stehen.
Ich muss ja irgendwie logisch die "g" oder "s" zu allen vorhandenen und zukünftigen Sorten zuordnen können.
Deshalb dachte ich mir, wäre das die einfachste Methode.

Der Inhalt ist identisch, die Paarungen sind aber eindeutig. Mal als Beispiel deine ersten vier Paarungen ergäben:
Aubergine Broccoli g
Aubergine Erbesen s
Broccoli Aubergine g
Broccoli Buschbohnen g
Broccoli Erbsen g
Buschbohnen Broccoli g
Buschbohnen Erbsen s
Erbsen Aubergine s
Erbsen Broccoli g
Erbsen Buschbohnen s

Natürlich sind die Paarungen jeweils noch doppelt, liegt aber daran das sie in deiner Matrix auch doppelt enthalten sind.

Oha! Das bedeutet, ich müsste für jede neue Sorte dutzende neue Einträge machen!
Kann Excel anhand einer Liste eine solche Auflistung automatisch generieren? Also von meiner Tabelle in diese Auflistung?

Wäre das Ganze am Ende einfacher für Excel zu berechnen?

Zitat von @joe2017:

Oha! Das bedeutet, ich müsste für jede neue Sorte dutzende neue Einträge machen!

Eigentlich nicht. Du müsstest jetzt in deiner Matix eine neue Zeile und eine neue Spalte anlegen und dann g oder s eintragen. Das ist nicht wirklich anders als einmal eine Vorlage Liste zu kopieren und dann das zu behalten, was man braucht.

Aber darum geht es mir gar nicht. Es geht darum zu zeigen, wie man Daten abbilden kann. Du nutzt eine Matrix, ein Pivot aus dem was ich als normalisierte Tabelle wie in einer relationalen Datenbank dargestellt habe. Man könnte aus meiner Version, in einer Datenbank, deine Ansicht erstellen und auch den umgekehrten Weg gehen, aus deiner Version meine Version zu machen.

In meiner Version wäre die Suche, wie du sie haben willst, meiner Meinung nach gut umsetzbar. Daher dachte ich das man vielleicht auch in Excel von deiner Variante in meine Variante konvertieren kann, z.B. auf einem 2ten Tabellenblatt mit Formeln, um dann dort zu suchen. Ich erwarte nicht, das du dein ganzes Konzept änderst. Es ging nur darum a) zu verstehen, was du hast, b) was es noch gibt und c), wie du dir das zu Nutze machen kannst.

Kann Excel anhand einer Liste eine solche Auflistung automatisch generieren? Also von meiner Tabelle in diese Auflistung?

Möglicherweise, ich gehe davon aus. Bin aber in Excel nie mit dieser Aufgabe befasst gewesen daher meine Stichworte Pivot / Unpivot aus der Datenbankwelt. Damit findest du z.B. sowas:
https://www.indeed.com/career-advice/career-development/how-to-unpivot-d ...

Du solltest dir aber natürlich auch Gedanken machen ob das Ergebnis dir überhaupt hilft, deine ursprüngliche Anforderung für die Suche umzusetzen.

German Question Microsoft Office

Hotly discussed

End of availability for classic Teams clientDani