Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1696to1700
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
Alle "Müller" anzeigen
22.06.2019 10:34:08
christina
Guten Tag,
im Excelbeispiel sollen bei Eingabe von Müller alle Werte angezeigt werden, die mit Müller zu tun haben. Die Argumente mangelhafter Tabellenaufbau, Verstoß gegen Eindeutigkeit kenne ich und doch kommt diese Konstruktion immer wieder vor und sucht nach einer Lösung.
Kann man Index(Vergleich)) dazu bringen, alle Werte von Müller anzuzeigen.
Über Hilfen, Lösungsansätze würde ich mich sehr freuen. Dank im Voraus.
Herzliche Grüße
christina verena
https://www.herber.de/bbs/user/130534.xlsx

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Alle "Müller" anzeigen
22.06.2019 10:35:28
Hajo_Zi
Hallo Christina,
http://www.excelformeln.de/formeln.html?welcher=28

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.
Die Beiträge werden auch ignoriert, es erfolgt keine Antwort.
AW: verschiedene Möglichkeiten ...
22.06.2019 11:11:40
neopa
Hallo Christina,
... z.B. so:
in A8: =WENN(ZEILE(A2)&gtZÄHLENWENN(A$2:A$5;A7);"";A7)
und in B7: =WENNFEHLER(AGGREGAT(14;6;C$2:C$5/(A$2:A$5=A7);ZEILE(A1));"")
und beide nach unten kopieren.

Gruß Werner
.. , - ...
AW: Alle "Müller" anzeigen
22.06.2019 14:38:43
Daniel
Hi
in B7 und nach unten ziehen:
=WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE($A$2:$A$4)/($A$2:$A$4=$A$7);ZEILE(A1)));"")
sieht ähnlich aus wie die Formel von Werner, funktioniert aber auch dann, wenn du nach Texten suchst und nicht nur mit Zahlenwerten.
Gruß Daniel
Anzeige
so ein Winz-Bereich geht einfacher
22.06.2019 15:31:07
WF
Hi,
=WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;{2;3;4}/($A$2:$A$4=$A$7);ZEILE(A1)));"")
WF
Danke mit Zusatzfrage
22.06.2019 16:41:45
christina
Hallo,
vielen Dank für alle Beiträge. Daran merke ich, dass die Auszeit recht lang war. Aggregat ist nicht verankert, eine Funktion, die sehr vielseitig zu sein scheint. Und deren Geheimnisse und Anwendungsbreite erkannt werden sollten.
Daher auch die Zusatzfrage: Gibt es auch Lösungen ohne die Funktion Aggregat. Vielleicht kann eine Lösung ohne einen rascheren Einstieg bewirken. Ich bin sehr gespannt.
Vielen Dank im Voraus.
Gruß
christina verena
bevorzuge ich auch
22.06.2019 16:57:19
WF
Hi,
{=WENNFEHLER(INDEX(C:C;KKLEINSTE(WENN(A$1:A$5=A$7;ZEILE(X$1:X$5));ZEILE(X1)));"")}
bzw.
{=WENNFEHLER(INDEX(C:C;KKLEINSTE(WENN(A$1:A$5=A$7;{1;2;3;4;5});ZEILE(X1)));"")}
WF
Anzeige
AW: geht auch ohne AGGREGAT() und ohne {} ...
22.06.2019 18:50:42
neopa
Hallo Christina,
... wenn wie im Beispiel nur Zahlenwerte auszuwerten sind, einfach:
in B7: =WENN(A7="";"";KGRÖSSTE(INDEX(C$2:C$5*(A$2:A$5=A7););ZEILE(A1)))
Wenn in C2:C5 Textwerte stehen sollten, dann
in B7 so: =WENN(A7="";"";INDEX(C:C;KGRÖSSTE(INDEX(ZEILE(C$2:C$5)*(A$2:A$5=A7););ZEILE(A1))))
Beide Formeln kommen wie Du siehst auch ohne {} aus.
Anders wird es, wenn Du die Reihenfolge der vorhandenen Datenwertlistung beibehalten willst.
Aber das war bisher nicht gefragt.
Am einfachsten wie flexibelsten ist aber für Deine Beispiel-Datenquelle eine Pivotauswertung. Dort einfach "Name" in den Berichtsfilter gezogen und "Abteilung" und "Kosten" in die Zeilenbeschriftung und als Berichtslayout das Tabellenformat wählen und Anzeige von Teil- und Gesamtergebnissen abwählen.
Abschließend noch ein Tipp. Wenn Du weiterhin auch Formellösungen einsetzen willst: Die Beschäftigung mit der Funktion AGGREGAT() lohnt sich.
Gruß Werner
.. , - ...
Anzeige
logischerweise will man die originäre Reihenfolge
22.06.2019 22:03:38
WF
.
AW: sehe ich zumindest teilweise anders ...
23.06.2019 09:13:32
neopa
Hallo WF,
... aber zunächst sorry dafür, dass mein Beitrag im thread von mir falsch zugeordnet war.
Doch hatte ich eindeutig nur Christina angesprochen und außerdem ihr auch die Entscheidung dazu überlassen.
Gruß Werner
.. , - ...
AW: Danke mit Zusatzfrage
22.06.2019 16:58:02
Daniel
Hi
Aggregat ist nicht weiteres, als die Erweiterung von Teilergebnis um ein paar weitere Funktionalitäten.
so lassen sich jetzt auch Fehlerwerte ignorieren und es sind weitere unterstützte Funktionen hinzugekommen.
Aggregat(15;...) ist die Funktion KKleinste.
Die Funktion lässt sich auch genauso gut mit KKleinste schreiben, allerdings muss man jetzt die Eingabe der Formel immer mit STRG+SHIFT+ENTER abschließen, weil sonst die Matrixformel nicht erkannt wird:
=WennFehler(Index(C:C;KKleinste(Wenn($A$2:$A$5=$A$7;Zeile($A$2:$A$5));Zeile(A1)));"")
das Aggregat wird hier gerne verwendet, weil es die Matrixformelnberechnung automatisch durchführt, ohne dass man STRG+SHIFT+ENTER drücken muss (zumindest für die Nummern ab 14).
auch lassen sich mehrere Bedingungen einfach definieren, in dem man durch den Wahrheitswert der Prüfung dividiert, dieser ist in Berchungen 1 bei WAHR und 0 bei FALSCH, und die Fehler, die durch die Division durch 0 entstehe, werden im Aggregat ignoriert, während man in anderen Funktionen solche Fehler vermeiden muss.
Gruß Daniel
Anzeige
AW: so ein Winz-Bereich geht einfacher
22.06.2019 17:00:46
Daniel
gehst du ernsthaft davon aus, dass jemand so eine Auswertung mit einer Datenquelle von 3 Zeilen macht?
Gruß Daniel
kommt vor
22.06.2019 17:06:18
WF
.
AW: kommt vor
22.06.2019 17:10:13
Daniel
naja, auch bei 3 Zeilen ist das ja nicht einfacher und auch nur minimal kürzer.
außerdem passt sich das nicht das einfügen, löschen oder verschieben von Zellbereichen an, und hat damit mehr Nach- als Vorteile.
Gruß Daniel
DANKE MIT ZUSATZFRAGE 2
23.06.2019 12:19:26
christina
Guten Tag,
für meine Aufgabe passt folgende Lösung am besten:
{=WENNFEHLER(INDEX(C:C;KKLEINSTE(WENN(A$1:A$5=A$7;ZEILE(X$1:X$5));ZEILE(X1)));"")}
Und ein wichtiger Grund ist auch, dass ich diese Formel fast verstehe, bis auf ein Detail.
Weshalb ist der Index(C:C und nicht eingeschränkt auf die auszuwertenden Zellen in C?
Ist das eine Bedingung von MS?
Vielen Dank für alle Beiträge und einen schönen Sonntag wünscht
christina verena
Anzeige
AW: DANKE MIT ZUSATZFRAGE 2
23.06.2019 12:46:26
Daniel
Hi
du kannst natürlich bei Index auch genau den auszuwertenden Bereich angeben, aber das bringt hier keinen Vorteil, sondern nur Nachteile.
das Problem ist folgendes:
die Zeilennummer, die du bei Index als 2. Parameter angeben musst, sind nicht die absoluten Zeilennummern der Exceltabelle, sondern die Positionsnummer innerhalb des angegebenen Zellbereichs.
Dh mit Index(A100:A200;100) bekommst du nicht die Zelle A100 sondern die Zelle A199.
Nur wenn der Zellbereich in Zeile 1 beginnt, sind Positions- und Zeilennummer der Tabelle gleich.
weil du von deiner KKleinste-Auswertung aber die absolute Zeilennummer der Tabelle als Ergebnis bekommst, lässt man eben den Zellebereich für Index bei Zeile 1 beginnen bzw nimmt gleich die ganze Spalte.
Ansonsten müsstest du immer noch Versatzwerte vom Ergebnis der KKleinste-Funktion subtrahieren, um von der Zeilennummer auf die Postitionsnummer umzurechnen und das wäre nicht nur in der Formel direkt aufwendiger, sondern du müsstest auch immer die Formel anpassen, wenn du nachträglich Zeilen einfügst, löschst oder den Zellbereich verschiebst
falls du mal irgendwo gehört haben solltest, dass man innerhalb von Matrixformeln keine ganzen Spalten als Parameter angeben sollte:
ja das ist richtig, bezieht sich aber bei singulären Matrixformeln nur auf Matrixteil der Formel und nicht auf die Gesamtformeln.
Der Matrixteil ist hier nur der Teil der Formel, der innerhalb von Kkleinste steht:
Gruß Daniel
Anzeige
AW: OK, kapiert. Danke
23.06.2019 13:01:21
christina
Hallo Daniel,
jetzt ist das Detail auch geklärt. Vielen Dank für Deine Erklärung.
Einen schönen Tag - egal ob mit oder ohne Excel.
Gruß
christina verena
noch ne Zusatz-Info
23.06.2019 13:48:40
WF
Hi,
die von Daniel ist OK.
Bei einer Bereichsbegrenzung werden oft Performancegründe angeführt, dass nicht zig tausend sinnlose Berechnungen durchgeführt werden.
Bei INDEX wird nichts berechnet - es wird nur die Zelle Nr. (Formelergebnis) abgegriffen.
Das ist so, als würdest Du =A73 oder =A42588 schreiben.
WF
Danke für die Zusatz-Info
23.06.2019 15:52:57
christina
Gruß
christina verena

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige