MINWENNS - Funktion

Moderator: ModerationP

MINWENNS - Funktion

Beitragvon RaiSta » 28. Sep 2021, 15:08

Hai Ihr Wissenden,

ich hab da mal ne Frage...:

Die Funktionen MINWENNS(), MAXWENNS(), etc. sind ja schon klasse, besonders, da sie etliche meiner Array-Funktionskonstrukte einfach lesbar ersetzen und dazu noch 'in native speed' laufen.

Aber, die Kriterienbereiche sind ja schon irgendwie begrenzt. Ich schaffe es jetzt noch nicht, in den Kriterienbereichen auf Teilmengen der Inhalte abzufragen.

Also: in meiner Tabelle habe ich Spalten, in deren einer so Sachen drin stehen wie "A1", "A2", "M1", "M23", "I2" und so weiter. Nun möchte ich in einer der genannten Funktionen z.B. das Minimum aller Werte im Wertebereich ermitteln, in denen die zugehörigen Kriterienzellen ein "A" enthalten. Und in einer anderen Zelle das Minimum der Werte, in denen die Kriterien ein "M" - oder ein "I" enthalten.

Gibt es eine schnelle Lösung dafür, ohne dass ich Hilfsspalten einführen muss oder doch wieder eine Array-Formel bastle?

Ich hoffe, meine Beschreibung ist ausreichend eindeutig. Und ich hoffe auf eine Lösung, in der ich die genannte Fragestellung ausweiten kann auf "irgendwo im Text der Kriterienspalte steht ein A" oder sogar auf "irgendwo von der 3. bis zur 7. Stelle der Texte in der Kriterienspalte ist ein A enthalten"

Gruß und Dank,
RaiSta
Wenn Sokrates als intelligent gilt, weil er "ich weiß, daß ich nichts weiß" erkannte, muß ich ein Genie sein.
RaiSta
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 822
Registriert: 02. Jul 2013, 01:07
Wohnort: Nahe dem ehemaligen Mittelpunkt - der EU

Re: MINWENNS - Funktion

Beitragvon shift-del » 28. Sep 2021, 16:53

Moin RaiSta

Alternativen zur Hilfsspalte:
AGGREGAT(15;...)
MIN(FILTER(...))
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 21675
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: MINWENNS - Funktion

Beitragvon RaiSta » 28. Sep 2021, 17:09

shift-del hat geschrieben:AGGREGAT(15;...)
MIN(FILTER(...))

Sowas Neumodisches ... kann ich nur zu Hause testen, aber leider nicht am Arbeitsplatz.
Vielleicht komme ich da ja dazu, denn die vielen dollen Excel-Goodies sind irgendwie schon verlockend.

Wobei - eigentlich wäre mir Prozessstabilität lieber. Gerade wieder stürzt mir XL reproduzierbar ab, wenn ich eine UDF aus meiner personl.xlsm aufrufen will, die schon viele Male problemlos ihren Dienst getan hat (nix Überwältigendes).

Aber, wenn ich nur das Makro per "Schritt" starten will (bin mir halt bei älteren Makros nicht sicher, dass auch wirklich nix tut, was ich momentan nicht will) - dann dauert es nen Moment, der VBA-Editor schaut gar nicht vorbei - und dann poppt die Meldung "Automatisierungsfehler" auf. Keine Ahnung, was das soll. XL ist dann in der Tonne und startet neu.
So'n Mist passiert mir in den letzten Monaten und Jahren zunehmend. Leider - dolle neue Funktionen, aber die Stabilität geht total den Bach runter. Manchmal mag ich einfach nicht mehr! Das war früher erheblich besser!

gefrusteter Gruß,
RaiSta
Wenn Sokrates als intelligent gilt, weil er "ich weiß, daß ich nichts weiß" erkannte, muß ich ein Genie sein.
RaiSta
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 822
Registriert: 02. Jul 2013, 01:07
Wohnort: Nahe dem ehemaligen Mittelpunkt - der EU

Re: MINWENNS - Funktion

Beitragvon steve1da » 28. Sep 2021, 18:01

Hola,

wer Minwenns() benutzen kann, kann auch Aggregat() verwenden :wink:

Gruß,
steve1da
Benutzeravatar
steve1da
Moderator
 
Beiträge: 31550
Registriert: 24. Sep 2003, 10:54

Re: MINWENNS - Funktion

Beitragvon RaiSta » 29. Sep 2021, 11:03

steve1da hat geschrieben:wer Minwenns() benutzen kann, kann auch Aggregat() verwenden :wink:


Habe nachgesehen - und muss tiefe Demut üben. Die Funktion gibt es tatsächlich auf meinem Rechner, ist mir bisher noch nicht aufgefallen und habe ich somit noch nicht verwendet.

Mal sehen, was die alles kann - scheint ja doch recht mächtig zu sein.

Gibt es diesbezüglich bekannte Einschränkungen,
* wo sie besser nicht angewandt wird, obwohl formal geeignet
* oder was beim Einsatz unbedingt beachtet werden muss, um typische Fehler zu vermeiden?

Gruß und Dank,
RaiSta
Wenn Sokrates als intelligent gilt, weil er "ich weiß, daß ich nichts weiß" erkannte, muß ich ein Genie sein.
RaiSta
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 822
Registriert: 02. Jul 2013, 01:07
Wohnort: Nahe dem ehemaligen Mittelpunkt - der EU

Beitragvon lupo1 » 29. Sep 2021, 11:19

Das Nichtkennen von AGGREGAT ist ja geradezu ein Schlag in neopas Gesicht.

AGGREGAT ist recht schnell und mittlerweile auch stabil (früher konnte es nicht viel mehr als 100.000 Datensätze).

Ich mag AGGREGAT nicht und habe es noch nie verwendet.
lupo1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 10142
Registriert: 25. Okt 2012, 13:38

Re: MINWENNS - Funktion

Beitragvon slowboarder » 29. Sep 2021, 11:51

Hi

Enthält "A" sollte einfach über Joker gehen: MinWenns(A:A;B:B;"*A*")

Enthält "M" oder "I" mit einer kleinen Matrix: Min(MinWenns(A:A;B:B;{"*M*";"*I*"}))

Gruß Daniel
Zuletzt geändert von slowboarder am 29. Sep 2021, 12:49, insgesamt 1-mal geändert.
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33

Re: MINWENNS - Funktion

Beitragvon slowboarder » 29. Sep 2021, 12:27

Aggregat ist zunächst einmal der Nachfolger von TEILERGEBNIS

die Hauptaufgabe ist weiterhin, Ausgeblendete Zeilen oder andere Zwischenergebnisse in der gleichen Spalte in der Berechnung nicht zu berücksichtigen.
Aggregat erweitert Teilergebnis dahingehend, dass es zusätzlich noch erlaubt, Fehlerwerte zu ignorieren, dh du kannst auch dann eine Summe über eine Spalte bilden, auch wenn diese neben Zahlen noch den #NV-Fehler enthält (in Summe bekommmst du dann den Fehler als Ergebnis)

zusätzlich wurden weitere Funktionen der Sammlung hinzugefügt.

Grundsätzlich ist es so, dass Aggregat NICHT Matrixformeltauglich ist.
Das liegt wahrscheinlich an der Herkunft aus Teilergebnis, wo das Ignorieren von ausgeblendeten Zeilen die wichtigste Funktion war und hier braucht man ja den Bezug zu Zelle, der mit einer Matrixberechnung verloren geht.

Eine Ausnahme bilden jedoch die neu hinzugekommen Funktionen (ab 14). Diese sind Matrixformeltauglich, was man nutzen kann, um Werte aus der Berechnung herauszunehmen, in dem man gezielt Fehlerwerte erzeugt - in der mit Hilfe der Divison durch 1 oder 0 (wobei man ausnutzt, dass in Berechnungen ein WAHR wie 1 und FALSCH wie 0 gewertet wird)

Die Einschänkungen sind
- es geht nicht mit allen Funktionalitäten, sondern erst ab Nr 14, weshalb nicht MIN und MAX verwenden kann, sondern KKleinste und KGrößte.
- Die Berechnung ist immer noch eine Matrixformel, auch wenn man diese nicht mit STRG+SHIFT+ENTER eingeben muss, weil Aggregat das automatisch erkennt, aber die Regeln der Matrixformelberechnung gelten trotzdem (keine ganzen Spalten)
dh technisch ist es also keine Matrixformel (was den Umgang etwas erleichtert), von der internen Berechnung hingegen schon.

nach meinen ersten Tests ist das Aggregat mit Bedingungprüfung dann auch nicht schneller als ein Max(Wenn()) als Matrixformel

der Hauptvorteil ist also der Wegfall des STRG+SHIFT+ENTER, wenn man Aggregat(14;6;...) anstelle von Max(Wenn(....)) verwendet.

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33

Re: MINWENNS - Funktion

Beitragvon RaiSta » 29. Sep 2021, 16:04

slowboarder hat geschrieben:Enthält "A" sollte einfach über Joker gehen: MinWenns(A:A;B:B;"*A*")
Uuups, das geht also auch? Da habe ich die xxxWENNS()-Funktionen bisher ja mächtig unterschätzt! Dabei dachte ich immer, solcherlei Aktivitäten müssten eigentlich im Kriterienbezug erledigt werden (... was halt nicht geht). Ok, damit erweitert sich für mich der Gebrauch der xxxWENNS()-Funktionen gerade massiv! Klasse!
Hier habe ich offenbar mit selbstgebauten Scheuklappen agiert... na ja.

Ich denke, dass das in meinem konkreten Anwendungsfall die Matrixformeln obsolet macht - muss das aber erst noch mal verfizieren. Vielen Dank für den Hinweis auf jeden Fall!

Gruß und Dank,
RaiSta
Wenn Sokrates als intelligent gilt, weil er "ich weiß, daß ich nichts weiß" erkannte, muß ich ein Genie sein.
RaiSta
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 822
Registriert: 02. Jul 2013, 01:07
Wohnort: Nahe dem ehemaligen Mittelpunkt - der EU

Re: MINWENNS - Funktion

Beitragvon RaiSta » 29. Sep 2021, 16:34

slowboarder hat geschrieben:Aggregat ist zunächst einmal der Nachfolger von TEILERGEBNIS

die Hauptaufgabe ist weiterhin, Ausgeblendete Zeilen oder andere Zwischenergebnisse in der gleichen Spalte in der Berechnung nicht zu berücksichtigen.
Aggregat erweitert Teilergebnis dahingehend, dass es zusätzlich noch erlaubt, Fehlerwerte zu ignorieren, dh du kannst auch dann eine Summe über eine Spalte bilden, auch wenn diese neben Zahlen noch den #NV-Fehler enthält (in Summe bekommmst du dann den Fehler als Ergebnis)

zusätzlich wurden weitere Funktionen der Sammlung hinzugefügt.

Grundsätzlich ist es so, dass Aggregat NICHT Matrixformeltauglich ist.
Das liegt wahrscheinlich an der Herkunft aus Teilergebnis, wo das Ignorieren von ausgeblendeten Zeilen die wichtigste Funktion war und hier braucht man ja den Bezug zu Zelle, der mit einer Matrixberechnung verloren geht.

Eine Ausnahme bilden jedoch die neu hinzugekommen Funktionen (ab 14). Diese sind Matrixformeltauglich, was man nutzen kann, um Werte aus der Berechnung herauszunehmen, in dem man gezielt Fehlerwerte erzeugt - in der mit Hilfe der Divison durch 1 oder 0 (wobei man ausnutzt, dass in Berechnungen ein WAHR wie 1 und FALSCH wie 0 gewertet wird)

Die Einschänkungen sind
- es geht nicht mit allen Funktionalitäten, sondern erst ab Nr 14, weshalb nicht MIN und MAX verwenden kann, sondern KKleinste und KGrößte.
- Die Berechnung ist immer noch eine Matrixformel, auch wenn man diese nicht mit STRG+SHIFT+ENTER eingeben muss, weil Aggregat das automatisch erkennt, aber die Regeln der Matrixformelberechnung gelten trotzdem (keine ganzen Spalten)
dh technisch ist es also keine Matrixformel (was den Umgang etwas erleichtert), von der internen Berechnung hingegen schon.

nach meinen ersten Tests ist das Aggregat mit Bedingungprüfung dann auch nicht schneller als ein Max(Wenn()) als Matrixformel

der Hauptvorteil ist also der Wegfall des STRG+SHIFT+ENTER, wenn man Aggregat(14;6;...) anstelle von Max(Wenn(....)) verwendet.

Uiuiui ... das muss ich jetzt erst mal verdauen!
Die Funktion TEILERGEBNIS() habe ich bisher sehr selten genutzt, von daher ist meine Anwendungserfahrung hier arg überschaubar. Aber, meiner Erinnerung nach wurde die doch in erster Linie in Verbindung mit den "intelligenten Tabellen" angewandt, oder? Und es ging doch eigentlich im Wesentlichen darum, dass bei der Berechnung der 'Endergebnisse' dann die zwischendrin stehenden Teilergebnisse nicht noch mal einbezogen wurden. Oder habe ich das wieder zu eingeschränkt verstanden?

Na ja, wenn ich hier eigentlich keinen echten Gewinn gegenüber den Matrixformeln habe, dann weiß ich nicht, ob es sich dann überhaupt lohnt, mich näher mit der Funktion zu beschäftigen - mal sehen.

Was ich als Frage aber hier mal anschließen möchte: Ich muss häufig Messdaten auswerten, die über einen gewissen Bereich von Werten gehen (z.B. Wellenlängen von Messungen von 350 ... 1000nm), von denen aber manche Formeln nur Ergebnisse liefern sollen, wenn die zugehörigen Wellenlängen in einem bestimmten Teilbereich liegen.

Dazu schreibe ich dann Matrixformeln der Struktur =SUMME( ([Wellenlänge]>[Untergrenze]) * ([Wellenlänge]<[Obergrenze]) * [Messwert]). Das selbe könnte auch funktionieren mit WENN-Formeln =SUMME(WENN(UND([Wellenlänge]>[Untergrenze] ; [Wellenlänge]<[Obergrenze]) ; [Messwert] ; "") - und dass sich dieser triviale Fall auch mit SUMMEWENNS() einfach formulieren ließe, ist mir schon klar - es geht mir nur um die Verwendung der Multiplikation im Vergleich zur WENN()-Formel
Welche Version hat denn welche Vorteile? Mir ist die erste Struktur halt geläufiger, weil häufiger verwendet. Aber vielleicht hat WENN hier Vorteile, die mir nicht bewusst sind?

Gruß und Dank,
RaiSta
Wenn Sokrates als intelligent gilt, weil er "ich weiß, daß ich nichts weiß" erkannte, muß ich ein Genie sein.
RaiSta
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 822
Registriert: 02. Jul 2013, 01:07
Wohnort: Nahe dem ehemaligen Mittelpunkt - der EU

Re: MINWENNS - Funktion

Beitragvon slowboarder » 29. Sep 2021, 19:12

Hi
Durch Multiplikation mit Wahrheitswerten nimmst du den Wert nicht aus der Auswertung raus, sondern setzt ihn auf 0.
Bei Summe ist das egal, aber für Mittelwert oder Min/Max geht die 0 in die Auswertung mit rein.
Über Wenn kannst du den Wert dann tatsächlich rausnehmen.

Beachte, dass du UND und ODER nicht in Matrixformeln verwenden darfst, weil sie in Excel keine Operatoren, sondern Aggregationsfunktionen sind, so wie Summe.
Kann also sein, dass du Wenn mit deinen Multiplikationen kombinieren muss, denn alternativ musst du mehrere Wenns dann schachteln.

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33

Re: MINWENNS - Funktion

Beitragvon RaiSta » 30. Sep 2021, 16:03

slowboarder hat geschrieben:Hi
Durch Multiplikation mit Wahrheitswerten nimmst du den Wert nicht aus der Auswertung raus, sondern setzt ihn auf 0.
Bei Summe ist das egal, aber für Mittelwert oder Min/Max geht die 0 in die Auswertung mit rein.
Das stimmt - deshalb habe ich dann den Mittelwert immer als Summe()/Anzahl umgesetzt - wobei die Anzahl auch ne Summe war, die aber dann statt mit dem Wert mit 1 multipliziert.
slowboarder hat geschrieben:Beachte, dass du UND und ODER nicht in Matrixformeln verwenden darfst, weil sie in Excel keine Operatoren, sondern Aggregationsfunktionen sind, so wie Summe.
Kann also sein, dass du Wenn mit deinen Multiplikationen kombinieren muss, denn alternativ musst du mehrere Wenns dann schachteln.

Ich glaube, dass mir das in grauer Vergangenheit schon mal auf die Füße gefallen ist und ich deshalb zu den Multiplikationen übergegangen bin.
Ich persönlich 'mag' die Array-Funktionalität sehr, weil damit vieles machbar ist, was ich sehr spannend finde. Was mich dann letztlich doch nach Möglichkeit davon Abstand nehmen lässt, ist, dass sie ggf. doch arge Verarbeitungszeit kosten, besonders, wenn sie sich über viele Zellen hinziehen oder an vielen Stellen auftreten bzw. eine Zelle mit Array-Konstrukt vom Ergebnis anderer Zellen abhängt, etc.
Was ich faszinierend finde - ok, hab mich mittlerweile seit ein paar Jahren dran gewöhnt... - ist, dass auch bei benannten Formeln automatisch der Array-Auswerteformalismus angewandt wird. Das finde ich letztlich doch 'schnuckelig'.

Insgesamt: Danke für Deinen Input,
ist für mich sehr wertvoll!
RaiSta
Wenn Sokrates als intelligent gilt, weil er "ich weiß, daß ich nichts weiß" erkannte, muß ich ein Genie sein.
RaiSta
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 822
Registriert: 02. Jul 2013, 01:07
Wohnort: Nahe dem ehemaligen Mittelpunkt - der EU

Re: MINWENNS - Funktion

Beitragvon RaiSta » 30. Sep 2021, 16:40

Noch mal zu Deiner Meldung:
slowboarder hat geschrieben:Enthält "A" sollte einfach über Joker gehen: MinWenns(A:A;B:B;"*A*")

Kann ich bei diesen Jokern auch die Fragen stellen:
  • bilde die Summe(oder was auch immer) über alle Werte, deren Kriterienzelle "A" gefolgt von einer Zahl enthält? Also alle "A0", "A1", "A7", "A32", ....?
  • kann ich die Frage auf Bereiche der Kriterienzellen einschränken (also befindet sich unter den ersten 5 Zeichen des Texts in <Kriterienzelle> ein "A"?)
  • kann ich auf "A[1..9]" und "A[01..09]" gleichzeitig prüfen lassen (also gewissermaßen die ODER-Verknüpfung von Zahlen ein- oder zweistellig nach dem "A")
Bisher kenne ich nur die Joker "?" und "*" - gibt's da noch mehr, das meiner Unwissenheit zum Opfer gefallen ist?

Gruß und Dank,
RaiSta
Wenn Sokrates als intelligent gilt, weil er "ich weiß, daß ich nichts weiß" erkannte, muß ich ein Genie sein.
RaiSta
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 822
Registriert: 02. Jul 2013, 01:07
Wohnort: Nahe dem ehemaligen Mittelpunkt - der EU

Re: MINWENNS - Funktion

Beitragvon HKindler » 30. Sep 2021, 17:13

Hi,

wollte gerade schreiben, dass das doch alles in der Hilfe steht. Zum Glück habe ich das kontrolliert und festgestellt, dass es bei MinWennS und MaxWennS nicht explizit erläutert wird. Aber es gibt dort auch einen Link auf SummeWennS und dort ist es erklärt.
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6280
Registriert: 04. Jul 2013, 09:02
Wohnort: Schwarzwald

Re: MINWENNS - Funktion

Beitragvon RaiSta » 30. Sep 2021, 17:29

HKindler hat geschrieben:Aber es gibt dort auch einen Link auf SummeWennS und dort ist es erklärt.

Hmmm, danke für den Link! Ich hatte zuvor auch die µsoft-Seite konsultiert, aber auch eine, die weniger Info bietet...
Jedoch ist dort, außer, dass die deutschen Version fehlerhaft ist:
µsoft-Hilfe hat geschrieben:Beispiel: =SUMMEWENNS(A2:A9;B2:B9;"= A*";C2:C9;"An?") addiert alle Instanzen mit Namen, die mit "Zu" beginnen und mit einem dritten Buchstaben enden, der variieren kann
nichts zu finden bezüglich Konstrukten wie "=A[1..9]" oder einer 'verODERten' Bedingung. Zumindest kann ich das dort nicht entnehmen.

... und: ach ja, gibt's dort auch einen Link zur englischen Version? Kann ich auf der Seite, zumindest hier auf meinem Firmen-Rechner, auch nicht sehen.

Gruß und Dank,
RaiSta
Wenn Sokrates als intelligent gilt, weil er "ich weiß, daß ich nichts weiß" erkannte, muß ich ein Genie sein.
RaiSta
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 822
Registriert: 02. Jul 2013, 01:07
Wohnort: Nahe dem ehemaligen Mittelpunkt - der EU

Nächste

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 23 Gäste