Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1668to1672
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
Inhaltsverzeichnis

KGRÖSSTE() mit Wildcard

KGRÖSSTE() mit Wildcard
14.01.2019 16:42:06
Georg
Hallo Excel Welt,
in der Recherche finde ich viele Beiträge die KGRÖSSTE() mit div. Bedingungen diskutieren, aber leider nixx zu dieser Formel in Verbindung mit einer Wildcard.
Genauer: ich steuere per Dropdown die jeweilige Bedingung in eine KGRÖSSTE()Formel, möchte aber als zusätzliche Option auch mit einer Wildcard (z.B."*") im Dropdown die Möglichkeit bieten den größten Wert der Tabelle überhaupt, also ohne ein eingrenzendes Kriterium zu ermitteln. Wenn im Dropdown "Nord", "Mitte", "Süd" und "*" steht, will ich mit "*" den größten Wert over all sehen können. Muss ja nicht "*" sein, aber irgendwie soll alles gezeigt werden ohne dass die Formel umgebaut werden muss.
Bei SUMMEWENNS() geht das mit dem * problemlos, aber nicht bei KGRÖSSTE(). Warum bloß.. ? :-((
Ich hab mal eine Datei angehängt, die das (hoffentlich) verdeutlicht.
https://www.herber.de/bbs/user/126749.xlsx
DANKE für alle Ideen!
Gruß Georg

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: das geht auch so nicht ...
14.01.2019 16:58:52
neopa
Hallo Georg,
... nachfolgende Formel braucht keinen klassischen Matrixformelabschluss und berechnet das von Dir gewünschte auf etwas andere Art und Weise:

=AGGREGAT(14;6;B4:B9/(A4:A9=WENN(C4="*";A4:A9;C4));1)

Gruß Werner
.. , - ...
uff - aber: Danke!
14.01.2019 17:20:09
Georg
Hallo Werner,
uiuiui - das ist mir ja noch nie untergekommen! Cool, hab ich gleich mal in der Hilfe gelesen. Ist ja eine interessante Funktion.
Leider versteh ich sie noch nicht wirklich. Ich werde es mir mal genauer anschauen.
Vielen Dank!
Georg
AW: bitte, und hierzu ergänzend ...
14.01.2019 18:27:49
neopa
Hallo Georg,
... doch die aktuelle Hilfedokumentation zur Funktion AGGREGAT() ist mE zumindest als etwas dürftig, teils auch als unzureichend zu bezeichnen. Diese Funktion gibt es schon seit der Version 2010. Da war sie in der deutschen Offline-Hilfe mE noch ein wenig besser beschrieben.
Mein Formelvorschlag hier basiert auf der ehemals als Matrixversion bezeichneten Funktionsweise (in der aktuellen Onlineversion nicht mehr so explizit benannt, sondern diese lediglich dem Argument "Bezug 2" zugeordnet). Diese ist jedoch (leider) nur für die Unterfunktionen 14 - 19 der Funktion anwendbar.
Diese Funktionsweise hat den Vorteil, dass durch die Möglichkeit der Ignorierung von Fehlern in einer auszuwertenden Matrix (infolge setzen einer 6 als 2. Argument) in Kombination mit einer bewussten Herbeiführens von Fehlerwerten im 3. Argument (meist durch Division durch 0) eine Matrixberechnung ohne den klassischen Matrixformelabschluss der Formel erfolgt. Dies oft auch etwas schneller als mit vergleichbarer klassischer Matrixformel.
In meinem Formelvorschlag hatte ich diesmal WENN() eingebaut, welche ich aber ansonsten in AGGREGAT()-Formeln nur spärlich einsetze.
Zum gleichen Ergebnis wie die bisher aufgezeigten Formeln würde auch folgende führen:
=AGGREGAT(14;6;B4:B9/((A4:A9=C4)+(C4="*"));1)
Gruß Werner
.. , - ...
Anzeige
AW: bitte, und hierzu ergänzend ...
14.01.2019 18:35:31
Georg
Hallo Werner,
ah, so verstehe ich es schon etwas besser. Gehe ich recht in der Annahme, dass das "/" nicht als Division zu werten ist, oder?
Das ist so ein Ding, da muss man mal ein bisschen mit rumspielen, um es zu begreifen. Z.B. auf deinen Bemerkungen zu den Fehlern werde ich etwas rumkauen. Nochmal wirklich danke für die Hinweise - so geht es immer wieder etwas weiter!
Gruß Georg
AW: die Division ist eine "Kernstück" ...
14.01.2019 19:11:18
neopa
Hallo Georg,
... der Funktionsweise der Formel, die ich oft auch als Matrixfunktion(alität)sformel.
Die notwendigen Bedingungen zur Matrixauswertung werden in derartigen Formeln als Divisor so definiert, dass sie eine Datenmatrix aus 1en und 0en ergeben. Dort wo eine unzutreffende Bedingung vorliegt, ergibt sich in der Teilmatrix ein FALSCH und wo die Bedingung zutreffend ist ein WAHR. Durch die Division werden bekanntlich aus WAHR und FALSCH eine 1 und eine 0. Und da eine Division durch 0 immer einen Fehlerwert ergibt und dieser aber durch das 2. Argument der Funktion (der 6) ignoriert wird, werden somit nur die zutreffenden Bedingungen (also die Divisionen durch 1) ausgewertet.
In meiner hier zuletzt eingestellten Formel wird das einfach nachvollziehbar, wenn Du z.B. mal in C4 "Mitte" auswählst und die Teilformel B4:B9/((A4:A9=C4)+(C4="*")) in der Eingabezeile markierst und durch Betätigung der Taste [F9] deren Teilmatrix betrachtest.
Gruß Werner
.. , - ...
Anzeige
Matrix(-funktionalitäts)formel
14.01.2019 19:49:49
{Boris}
Hi Werner,
wozu stets die Betonung darauf, dass die Formel keine klassische {Array}formel ist?
Da gibt es auch andere Funktionsbeispiele: SUMMENPRODUKT, VERWEIS - um nur 2 zu nennen.
Letztlich funktioniert das AGGREGAT(14;6...)-Konstrukt genau so wie die VERWEIS(2;1/...)-Variante - nur dass eben per Funktionsparameter ein paar mehr Einstellungen / Auswertungen möglich sind.
Ist ja in Summe pfiffig - nur kann die Funktion das eben - wie manche andere auch - von Haus aus.
VG, Boris
AW: Matrixfunktion(alität)sformel ....
14.01.2019 21:01:32
neopa
Hallo Boris,
... so bezeichne ich eine bestimmte Formeltypgruppe nicht erst seit gestern und heute sondern schon seit einigen Jahren. Und zwar alle Formeln, die auf Basis von dementsprechenden Funktionen definiert werden. Und zwar meine ich Funktionen, die auf Grund ihrer entsprechenden internen Programmierung nicht darauf angewiesen sind, erst durch einen spez. Formelabschluss in die Lage versetzt zu werden, eine Matrix unter bestimmten (mehr oder weniger komplexen) Bedingungen auszuwerten.
Dazu zählte und zähle ich alle ...WENNs()-Formeln und natürlich auch die spez. VERWEIS()- wie alle SUMMENPRODUKT()-Formeln weiterhin auch Formeln, die auf Basis von MMULT(), HÄUFIGKEIT() basieren. Aber u.a. auch alle Formeln, die mit Hilfe von INDEX() in die Lage versetzt werden, einen {}-Formelabschlusses nicht zu benötigen.
Mit der Excelversion 2010 ist die Funktion(sgruppe) AGGREGAT() hinzugekommen. Bereits allein oder auch in Kombination mit anderen Funktionen eingesetzt, sind mE einerseits die vielfältigsten Einsatzmöglichkeiten zu der bereits aufgeführten Funktionsgruppe hinzugekommen. Anderseits ist diese Funktion(sgruppe) leider noch nicht konsequent durch programmiert. So ist sie nur in den Unterfunktionen 14 - 19 in ihrer Matrixfunktionsweise einsetzbar, Die Funktion verliert in dieser Funktionsweise dann auch die Eigenschaft, für die sie ursprünglich eigentlich wohl "gedacht" war. Sie sollte mE wohl hauptsächlich Funktionsweisen von TEILERGEBNIS() erweitern. Aber auch auf das hatte ich auch schon vor einigen Jahren hingewiesen.
Trotzdem haben sich gerade durch AGGREGAT() einerseits neue vereinfachende wie andererseits auch komplexer Formellösungsmöglichkeiten ergeben, denen sich mittlerweile ein Großteil aus der Schar der aktuellen Excel-Formel-Fans angeschlossen haben.
Gruß Werner
.. , - ...
Anzeige
Eben, Werner (& Boris), alles Fktt, ... ;-)
15.01.2019 01:17:25
Luc:-?
…die so pgmmiert wurden, während sich die „Klassiker“ unter ihnen (wie SUMME) auf eine diesbzgl Unterstützung durch den FmlText-Interpreter verlassen, das diesem aber per MatrixFml-Form mitteilen müssen (bzw wohl neuerdings mussten).
Dass eine Fml neben einer solchen Fkt auch noch anderes enthalten kann, ist hierbei meist nicht das Entscheidende, denn eine Fml ohne Fktt würde keinen vglbaren Effekt haben.
Manche dieser Fktt können (bzw konnten → wir wdn sehen) auch nicht in jedem Fall auf die MatrixFml-Form verzichten, was iaR von ihrer Argumentierung abhängt, denn bestimmte Ausdrücke verlang(t)en das. Aber auch SUMME kann Matrizen in Form von Zell­Berei­chen (sogar diskontinuierlichen!) und Datenfeldern verarbeiten, nur nicht jeden letztere erzeugenden Ausdruck (TeilFml) als ihr Argu­ment. Nur wenige Xl-Fktt können auch das nicht (zB UND, ODER) wirklich. Datenfelder (aus Ausdrücken als HptArgument) entfallen auch für die meisten neueren Xl-Fktt (oft auch diskontinuierliche Bereiche), neben den ~WENNs-Fktt fallen bekanntlich auch RANG (sowie beide neueren Varianten), TEIL­ER­GEB­NIS (total) und AGGREGAT (FktsNrn 1-13) darunter, was du für letztere erwähnt hattest. Und das nur, um in diesen Fällen stets alle Arg2-Möglichkeiten anwenden zu können (bei TEILERGEBNIS war das in alter Form, 1-11, gänzlich unverständlich und ist nur für Neue­res 101-111 sinnvoll). Insofern wurde das in AGGREGAT einfach nur so beibehalten. In VBA wäre es ein Klacks, das anders zu regeln (was ich schon vor Jahren bewiesen hatte, mit und ohne MatrixFml-Form!).
Gruß, Luc :-?
Anzeige
danke
17.01.2019 08:49:58
Georg
Hallo ihr alle,
war zwei Tage auf Dienstreise. Daher erst jetzt ein DANKE Euch allen für eure vielen Erläuterungen!! :-)
Gruß Georg
AW: bitte, und hierzu ergänzend ...
15.01.2019 11:59:10
Daniel
"Gehe ich recht in der Annahme, dass das "/" nicht als Division zu werten ist, oder?"
doch, das "/" ist hier eine normale Division.
in Berechnungen werden die Boolschen Werte WAHR wie 1 und FALSCH wie 0 gewertet, dh das Ergebnis des Ausdrucks: ((A4:A9=C4)+(C4="*")) kann 0, 1 oder 2 sein.
bei der Division durch 0 entsteht ein Fehler und dieser wird von der Aggregatfunktion ignoriert, wenn der 2. Parameter = 6 gesetzt ist.
Alle anderen Berechnungsergebnisse gehen in die Auswertung mit ein.
da ansonsten nur der Divisor 1 vorkommt, werden die Werte des Dividenden (B4:B9) dann unverändert in die Auswertung entsprechend der Angabe im ersten Parameter der Aggregatfunktion übernommen.
Gruß Daniel
Anzeige
AW: KGRÖSSTE() mit Wildcard
14.01.2019 17:01:32
Daniel
Hi
KKGRÖSSTE verarbeitet grundsätzlich nur Zahlen und kann keine Bedingungsprüfung analog SummeWenns durchführen.
Du fügst hier diese Bedingungsprüfung über eine entsprechende Matrixformel ein und verwendest hierbei den Vergleich mit "=". Dieser kann jedoch keine Joker verarbeiten.
eigentlich müsstest du in deiner WENN-Funktion dann mit ODER abfragen, ob der Wert aus der Liste = C4 ist ODER in C4 ein "*" steht.
Allerdings kann in einer Matrixformel wiederum die ODER-Funktion nicht verwendet werden.
der Workaround wäre hier entweder eine einsprechende Schachtelung von WENNs,
=KGRÖSSTE(WENN(C4="*";B4:B9;WENN(A4:A9=C4;B4:B9));1)

oder die Addtion der Bedingungen
(Excel verwendet WAHR wie 1 und FALSCH wie 0, daher kann man solche Bedingungsprüfungen auch anstelle von UND und ODER mit * und + verknüpfen)
=KGRÖSSTE(WENN((A4:A9=C4)+(C4="*");B4:B9);1)
Gruß Daniel
Anzeige
danke - funzt!
14.01.2019 17:25:44
Georg
Hallo Daniel,
vielen Dank, die klappt klasse und ich verstehe was ich tu! Hat manchmal seine Vorteile.. Ich werde es mal in meiner echten Datei ausprobieren, da hab ich zwar deutlich mehr Kriterien, aber das Prinzip hab ich verstanden und letztlich wird es ja immer nach dem gleichen Muster gehen.
Vielen Dank auch Dir!
Georg
PS: auch an Werner - ihr wart super schnell! :-)))

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige