Microsoft Excel

Herbers Excel/VBA-Archiv

KGRÖSSTE() mit Wildcard


Betrifft: KGRÖSSTE() mit Wildcard von: Georg
Geschrieben am: 14.01.2019 16:42:06

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

  

Betrifft: AW: das geht auch so nicht ... von: neopa C
Geschrieben am: 14.01.2019 16:58:52

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
.. , - ...


  

Betrifft: uff - aber: Danke! von: Georg
Geschrieben am: 14.01.2019 17:20:09

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


  

Betrifft: AW: bitte, und hierzu ergänzend ... von: neopa C
Geschrieben am: 14.01.2019 18:27:49

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
.. , - ...


  

Betrifft: AW: bitte, und hierzu ergänzend ... von: Georg
Geschrieben am: 14.01.2019 18:35:31

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


  

Betrifft: AW: die Division ist eine "Kernstück" ... von: neopa C
Geschrieben am: 14.01.2019 19:11:18

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
.. , - ...


  

Betrifft: Matrix(-funktionalitäts)formel von: {Boris}
Geschrieben am: 14.01.2019 19:49:49

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


  

Betrifft: AW: Matrixfunktion(alität)sformel .... von: neopa C
Geschrieben am: 14.01.2019 21:01:32

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
.. , - ...


  

Betrifft: Eben, Werner (& Boris), alles Fktt, ... ;-) von: Luc:-?
Geschrieben am: 15.01.2019 01:17:25

…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 :-?


  

Betrifft: danke von: Georg
Geschrieben am: 17.01.2019 08:49:58

Hallo ihr alle,
war zwei Tage auf Dienstreise. Daher erst jetzt ein DANKE Euch allen für eure vielen Erläuterungen!! :-)
Gruß Georg


  

Betrifft: AW: bitte, und hierzu ergänzend ... von: Daniel
Geschrieben am: 15.01.2019 11:59:10

"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


  

Betrifft: AW: KGRÖSSTE() mit Wildcard von: Daniel
Geschrieben am: 14.01.2019 17:01:32

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


  

Betrifft: danke - funzt! von: Georg
Geschrieben am: 14.01.2019 17:25:44

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! :-)))


Beiträge aus dem Excel-Forum zum Thema "KGRÖSSTE() mit Wildcard"