13
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
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
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 33574827202
Url: https://administrator.de/contentid/33574827202
Ausgedruckt am: 21.11.2024 um 17:11 Uhr
13 Kommentare
Neuester Kommentar
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
Nur für die kleine Beispiel Matrix (Bereiche für deine Tabelle anpassen) Daten in der Tabelle sind nur fiktiv ...
Download Tabelle
pj
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
2
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"
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 Ich möchte, dass die Liste mit dem kleinsten Wert beginnt. Mit dem kleinsten meine ich, der Wert mit den wenigsten "g"
. 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 
.
, 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
Zelle J11
Zelle K11
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!
=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.Ich weiß nicht ganz was du meinst? Wie soll ich die Daten besser zuordnen als in dieser Tabelle? Wie meinst du das?
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.
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.
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?
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?
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.
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.
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.
