Hilfe für Formel

Bild

Betrifft: Hilfe für Formel
von: eric
Geschrieben am: 10.08.2015 11:08:30

Hallo liebe Forumsmitglieder,
bei der nachstehenden Tabelle benötige ich Hilfe bzgl. einer Formel für die Spalte B - ich komme da irgendwie nicht weiter. Die Formel soll gruppenweise (die gefüllten zwischen den leeren Zellen) betrachten und mir den kleinsten Wert in die jeweils erste Zeile der Gruppe (gelb markiert) schreiben. Ist hier bereits ein Makro nötig oder gelingt es jemandem mit einer Formel?
https://www.herber.de/bbs/user/99462.xlsx
Beste Grüße und jetzt schon vielen Dank für Eure Hilfe.
Eric

Bild

Betrifft: AW:KKLEINSTE ?? oT
von: Bernd
Geschrieben am: 10.08.2015 11:21:00


Bild

Betrifft: AW: AW:KKLEINSTE ?? oT
von: eric
Geschrieben am: 10.08.2015 11:25:59
Hallo Bernd,
vielen Dank für die schnelle Reaktion! Dass es über die KKLEINSTE-Formel geht ist mir bewusst, jedoch ist die erste Spalte dynamisch aus einer Pivottabelle und Lage sowie Umfang der Gruppen in dieser Spalte ist vollkommen variabel (nach einer Aktualisierung). Ich hoffe, Du verstehst worin die Herausforderung liegt.
Besten Gruß.
Eric

Bild

Betrifft: AW: Hilfe für Formel
von: Rudi Maintaire
Geschrieben am: 10.08.2015 11:42:48
Hallo,
Martrixformel in B3:
{=WENN(A2="";MIN(WENN(A3:INDEX(A:A;MIN(WENN(A3:A102="";ZEILE(2:101);"")))>=0;A3:INDEX(A:A; MIN(WENN(A3:A102="";ZEILE(2:101);"")));""));"") }
für Gruppen mit max. 100 Elementen. Ansonsten A3:A102="";ZEILE(2:101) anpassen.
Gruß
Rudi

Bild

Betrifft: AW: Hilfe für Formel
von: eric
Geschrieben am: 10.08.2015 11:55:28
Danke Rudi, großes Kino mal wieder!!
Genau so hab ich es mir vorgestellt.
Beste Grüße.
Eric

Bild

Betrifft: mit AGGREGAT() einfacher, schneller, kürzer ...
von: ... neopa C
Geschrieben am: 10.08.2015 12:51:05
Hallo Eric,
... und dazu auch ohne den leidlichen MATRIXFormelabschluss!

 B
30,699116401

Formeln der Tabelle
ZelleFormel
B3=WENN((A2="")*(A3<>""); AGGREGAT(15;6;A3:INDEX(A:A;AGGREGAT(15;6;ZEILE(A3:A99)/(A3:A99=""); 1))/(A3:A99>0); 1); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Die gewählte Gruppengröße von 999-3 sollte auch ausreichend sein.
Meine zusätzliche kleine Bedingung: (A3<>"") ergibt bei mehreren Daten-Leerzeilen auch keinen 0-Ergebniswert.
Gruß Werner
.. , - ...

Bild

Betrifft: ich hab drauf gewartet. owT
von: Rudi Maintaire
Geschrieben am: 10.08.2015 12:57:06


Bild

Betrifft: AW: ich hab drauf gewartet. owT
von: eric
Geschrieben am: 10.08.2015 13:11:26
Hallo ihr beiden,
danke nochmals - funktioniert auch tadellos! Hier sind wirklich erfahrene User am Werk, das merkt man sofort. Mit zwei Lösungen hab ich nicht gerechnet, aber so ists mir lieb!
Habt einen schönen Tag.

Bild

Betrifft: ...und hier außer Konkurrenz die 3. u.kürzeste ...
von: Luc:-?
Geschrieben am: 10.08.2015 14:58:19
…Formel, Eric,
mit meiner neuesten UDF, allerdings per „leidiger“ MatrixFml und in einem separaten Block (nur zum Ergebnis­Vergleich!):

 CDE
2lfdNrGrpAnzWerteMinWert
3130,699116401
4211,228762131
5330,267797373
6431,140937477
7540,749202988
8630,177753345
974-13,67717923
1084-6,760334434
11931,038837153
12104-11,10990116
131130,664130777
141230,098409857
151350,931756103
161450,758969568
171530,433510372
18165-1,765333267
19MxFormeln:D3:D18: {=SubSet(2;;A2:A74)}
20 E3:E18: {=SubSet(5;;A2:A74)}

Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: da war aber auch noch zu beachten ....
von: ... neopa C
Geschrieben am: 10.08.2015 15:08:58
Hallo Luc,
... dass nur die kleinsten positiven Werte auszugeben sind.
Gruß Werner
.. , - ...

Bild

Betrifft: Das stand nur in der Datei und ist mir dann ...
von: Luc:-?
Geschrieben am: 10.08.2015 15:16:00
…untergegangen, Werner;
dann wäre es ggf etwas komplizierter… ;-)
Gut erholt?
Gruß, Luc :-?

Bild

Betrifft: oT: bei dieser Hitze, ...
von: ... neopa C
Geschrieben am: 10.08.2015 15:19:30
Hallo Luc,
... die wir seit Anfang August haben und diese Woche noch sein soll, bin ich spätestens nächste Woche wieder urlaubsreif.
Gruß Werner
.. , - ...

Bild

Betrifft: War auch in Kurzurlaub und könnte gleich ...
von: Luc:-?
Geschrieben am: 10.08.2015 15:29:42
…wieder, Werner… ;-]
Jetzt besser?

 CDEF
2lfdNrGrpAnzWerteMinWertposMinWert
3130,6991164010,699116401
4211,2287621311,228762131
5330,2677973730,267797373
6431,1409374771,140937477
7540,7492029880,749202988
8630,1777533450,177753345
974-13,677179230,927310398
1084-6,7603344340,477135446
11931,0388371531,038837153
12104-11,109901160,566459028
131130,6641307770,664130777
141230,0984098570,098409857
151350,9317561030,931756103
161450,7589695680,758969568
171530,4335103720,433510372
18165-1,7653332670,225052996
19MxFormeln:D3:D18: {=SubSet(2;;A2:A74)}
20 E3:E18: {=SubSet(5;;A2:A74)}
21F3:F18: {=SubSet(5;;WENN(A3:A74<0;99;A3:A74))}

Luc :-?

Bild

Betrifft: was ist, wenn alle Gruppenwerte negativ sind ? ...
von: ... neopa C
Geschrieben am: 10.08.2015 15:45:20
Hallo Luc,
... doch nicht etwa 99? Oder?
Es sollte dann mE auch eine Fehlermeldung kommen, wie mit meinem Formelvorschlag. Mit Rudis Formel käme da übrigens auch nicht ganz korrekt, teilweise ein 0 als Ergebnis (bei mehreren Werten).
Gruß Werner
.. , - ...

Bild

Betrifft: Ja, diese Frage habe ich erwartet, ...
von: Luc:-?
Geschrieben am: 10.08.2015 17:37:56
…Werner,
dann muss statt 99 etwas noch Signifikanteres verwendet wdn wie bspw 999,999999 und danach in einem umschließenden WENN-Konstrukt gefragt und ggf durch "" ersetzt wdn. Alternativ könnten solche Werte auch per benutzer­definiertem Format ausgeblendet wdn.
Allerdings ist das bei UDF SubSet-Verwendung nicht wirklich erforderlich, wenn 99 durch #NV ersetzt wird. Die so erzeugten Fehlerwerte wdn bei der Auswertung mit dieser Fml einfach weggelassen. Sind alle Werte einer Gruppe <0, entfällt folglich die ganze Gruppe! Es geschieht also genau das, was bei entsprd Argumen­tierung auch mit AGGREGAT möglich ist.
Luc :-?

Bild

Betrifft: Das sähe dann so aus, ...
von: Luc:-?
Geschrieben am: 10.08.2015 18:45:17
…wenn alle Werte der 3.Gruppe negativ wären:

 IJK
2lfdNrGrpAnzWerteposMinWert
3130,699116401
4211,228762131
5431,140937477
6540,749202988
7630,177753345
8730,927310398
9830,477135446
10931,038837153
111030,566459028
121130,664130777
131230,098409857
141350,931756103
151450,758969568
161530,433510372
171640,225052996
18 #NV#NV
19I3[:I18]:=WENN(ISTNV(J3);"";WENN(ZEILE()=3;0;I2)+
20WENN(INDEX(SubSet(4;;A$3:A$74);ZEILE(1:16))<0;2;1)
21J3:J18: {=SubSet(2;;WENN(A3:A74<0;#NV;A3:A74))}
22K3:K18: {=SubSet(5;;WENN(A3:A74<0;#NV;A3:A74))}

Luc :-?

Bild

Betrifft: allerdings ...
von: der neopa C
Geschrieben am: 10.08.2015 19:51:58
Hallo Luc,
... in Zeile 18 dürfte kein #NV stehen sondern lediglich ="", denn da werden ja keine Daten ausgewertet.
Gruß Werner
.. , - ...

Bild

Betrifft: Das liegt daran, dass der MxFml-Bereich ...
von: Luc:-?
Geschrieben am: 11.08.2015 05:09:44
…diese Zeile mit einschließt (einschließen muss!), Werner,
aber nicht ebensoviel Werte von der MxFml geliefert wdn. In solchen Fällen reagiert Xl standardmäßig mit #NV. Diese (Fehler-)Anzeige kann dann auch nicht verhindert wdn, auch nicht mit WENNFEHLER!
Letztlich liegt das daran, dass SubSet Fehlerwerte bei der Bildung des Ergebnis­Vektors generell auslässt. Eine gewisse Rolle spielen sie nur dann, wenn sie als Gruppen­Trenner benutzt wdn, was hier aber unzweckmäßig wäre.
Wollte man die Ergebnisse von vornherein so anordnen, wie vom Fragesteller gewünscht, wäre eine etwas andere Vorgehensweise zweckmäßiger, um die FmlLänge relativ kurz zu halten. Allerdings könnte dann deine Fml kürzer u/o fktionaler sein.
Zu einigen Möglichkeiten von SubSet siehe diese Abbildung diverser Tests!
Morrn, Luc :-?

Bild

Betrifft: da ist es wohl eine mehrzellige Matrixformel? ...
von: ... neopa C
Geschrieben am: 11.08.2015 08:49:22
Hallo Luc,
... oder? Das wäre z.B. ein Grund, warum ich mehrzellige Matrixformeln meide.
Das es auf die Kürze eine Formel prinzipiell nicht ankommt, waren wir uns schon mal einig, oder? Wobei es manchmal durchaus interessant sein kann nach einer kürzeren Formel zu suchen. Die Kürze von UDF-Formeln wird aber erst durch die zusätzliche UDF gewährleistet und ist insofern als Vergleichskriterium mE auszuschließen.
Eine Formel hätte hier ja auch z.B. auch =MinPosWert lauten können, wenn man die eigentliche Auswertungsformel zuvor als Bereichsnamen definiert hätte. Das diese "Kürze" nicht gewertet werden kann, leuchtet ja jeden sofort ein.
Entscheidender sind also andere Kriterien. Dazu zählen mE als erstes natürlich die Korrektheit der Lösung und im Weiteren (nachfolgende Aufzählung ohne Einordnung einer Wertung) u.a. die Schnelligkeit einer Lösungsermittlung/-bereitstellung, Nachvollziehbarkeit der Lösung, die Effizienz der Lösung in Anwendung und in der Handhabung u.a. auch bzgl. nachträglichen Anpassungsanforderungen, und und und ...
Für die meisten Fragesteller in Foren ist das erste Lösungsangebot, welches im eigenen Schnelltest als die Lösung seines Problems erscheint die Lösung. Auch wenn es objektiv betrachtet nicht immer die beste ist, so ist zumindest für den Fragesteller für seine Fragestellung fast immer auch die günstigste. Denn auch die beste Lösung müsste wohl in der einen Arbeitsmappe meist mehrere tausende Male und noch mehr angewendet werden, bevor sie den "Zeitverlust" durch die frühere Bereitstellung des Erstlösungsangebotes von z.B. nur einer Minute wieder aufholen kann.
Natürlich sind wir uns sicherlich einig, dass - in Umkehrung eines bekannten Sprichworts - man schon nach der Taube auf dem Dach schielen sollte, auch wenn man den Spatz auf der Hand hat. ;-)
Übrigens was Du mit "fktionaler" meintest, erschließt sich mir momentan noch nicht.
Gruß Werner
.. , - ...

Bild

Betrifft: Deshalb ist das 'außer Konkurrenz' und nicht ...
von: Luc:-?
Geschrieben am: 11.08.2015 13:03:22
…wg der tatsächlich mehrzelligen MxFml, Werner;
Du kennst meine Meinung dazu, die sehr schön zum Ausdruck kommen würde, wenn ich die Einzelwerte per INDEX aus dem mehr­zelligen Ergebnis zeilenweise herausfiltern würde. Dann wären es lauter 1zellige MxFmln, was hier den 16fachen Rechenaufwand bedeuten würde (in analogen Fällen mit XlStandardFktt aber auch!).
Über „FmlKürze“ müssen wir in der Tat nicht diskutieren und funktionaler bezieht sich hier auf die Position des gewünschten Ergebnisses, die mit SubSet nur unter Einbeziehung der Werte­Zähler-Fml möglich wäre. Ich habe dafür auch 2 Fmln auf Basis der beiden unter­schiedlichen SubSet-Argumentierungen entwickelt, von denen die mE günstigere erste aber noch 2 andere UDFs erfordert. (Näheres dürfte hier wohl zu weit führen, zumal die UDF nicht unter diesem Aspekt entwickelt wurde…)
Mir ging's hierbei in 1.Linie um eine/n Demo/Test der UDF an einem PraxisBsp, der (aus meiner Sicht) erfolgreich verlaufen ist.
Und was Nachvollziehen und Akzeptanz/Adaption von angebotenen Fmln durch den Frager betrifft, ist deine Sicht schon des Öfteren bestätigt worden. Aber darum ging's mir hier ja nicht. Eher hat mich Rudis, an dich gerichtete Replik inspiriert… ;-)
Übrigens greift dein UDF-Argument nicht, wenn es sich nicht um eine ganz spezielle UDF für genau das jeweilige Problem handelt. SubSet ist eine universelle Fkt, die Analogien zu TEILERGEBNIS aufweist, aber generell fehlerwert-tolerant ist. Und XlStandard-Fktt sind nun mal auch mehr oder weniger universelle Pgmm!
Gruß, Luc :-?

Bild

Betrifft: AW: zu den Standardfunktionen ....
von: ... neopa C
Geschrieben am: 11.08.2015 16:10:35
Hallo Luc,
... diese stehen jeden in der jeweilig genutzten Excelversion zur Verfügung und sind auch meist ausreichend gut dokumentiert. Schon bei von MS mitgelieferten aber nicht aktivierten Add-Ins gibt es bei den meisten Nutzer Probleme in deren Aktivierung und Nutzung. Das wird wohl bei Deinen UDF´s nicht viel anders sein und werden Ist zwar sicherlich schade aber so ist nun mal die Realität. Zumindest wie ich sie beobachtet habe.
Gruß Werner
.. , - ...

Bild

Betrifft: Da irrst du teilweise, ...
von: Luc:-?
Geschrieben am: 11.08.2015 16:38:36
…Werner;
was ich hier zeige, ist ja kostenlos und hat idR nur eine stichwortartige Erläuterung (Erinnerungsstütze für mich). Du hast da schon mehr zu sehen bekommen, nämlich u.a. auch Bspp!
Eine umfangreiche Hilfe anzubieten (mit Bspp), lohnt sich nur für ein AddIn, das mehrere UDFs enthält. So etwas macht sehr viel Arbeit, wie du dir vorstellen kannst, und ist dann, ebenso wie XL, natürlich nicht kostenfrei.
Auch bei auf diversen WebSites angebotenen UDFs wirst du 1. nur weniger umfangreiche UDFs finden, bei denen 2. eine allgemeine Kurzbeschreibung ausreichend ist, abgesehen davon, dass man als VBA-Kundiger die meisten leicht nachvollziehen kann. Das alles ist bei SubSet (und sicher auch vielen StandardFktt) nicht der Fall.
Da du hier schon des Öfteren auf das AddIn MoreFunc verwiesen hast, wirst du dir das vorstellen können. Allerdings weiß ich nicht, ob das eine umfängliche Hilfe anbietet. Wenn ja, dann wahrscheinlich nicht nach den neuesten MS-Standards.
Luc :-?

Bild

Betrifft: AW: vielleicht, vielleicht auch nicht ...
von: ... neopa C
Geschrieben am: 11.08.2015 16:58:55
Hallo Luc,
...auf morefunc verweise ich nur dann und deshalb, weil es für diese spez. INDIREKT()-Wünsche meist keine Alternative (ohne VBA) gibt (wenn ich eine erkenne dann erweise ich nicht darauf, ich selbst nutze es nicht).
Natürlich ist mir klar, dass Hilfe zu zusätzlich angebotenen Tools meist mindestens genau so viel Arbeit macht wie das Tool selbst zu erstellen. Deshalb versuche ich ja vorrangig, auf das zurückzugreifen, was "Standard" ist und ohne Zusatzbemühungen jeden sofort zur Verfügung steht.
Gruß Werner
.. , - ...

Bild

Betrifft: ...Allerdings habe ich seit Jahren ein eigenes ...
von: Luc:-?
Geschrieben am: 11.08.2015 17:28:16
…AddIn in Anwendung, Werner,
das eine .chm-Hilfe enthält (genauer: sie enthält ein Pgm, mit dem die einzelnen HTML- und HilfsDateien erzeugt wdn können, die man dann selbst mit dem chm-Tool von MS zu einer Datei zusammenfassen muss). Die ist aber nie ganz fertig geworden, weil immer wieder Neues dazugekommen ist.
Luc :-?

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Hilfe für Formel"