Microsoft Excel

Herbers Excel/VBA-Archiv

2 Größter Wert ohne Bereich (KKGRÖSSTE)


Betrifft: 2 Größter Wert ohne Bereich (KKGRÖSSTE)
von: mctobler
Geschrieben am: 15.04.2019 11:48:48

Hallo,
ich suche aus einer Reihe Absoluter Zahlen den 2. größten Wert.
Leider geht dies mit KKGröste nicht, da ich keinen Bereich habe sondern Absolute Zahlen in der Formel

Vereinfacht ausgedrückt suche ich folgendes

=KGRÖSSTE((1;2;3);2)

Ich würde 2 als Ergebnis erwarten

Hinter den Zahlen stehen wiederrum Formeln und keine Zellbezüge. Eine Hilfsspalte kann ich leider nicht einfügen.

Mit MAX(1;2;3) bekomme ich leider nur den größten Wert (=3) aber nicht "2".

Leider verweisen immer alle Forenbeiträge auf KGRÖSSTE

Hat jemand eine Idee ohne VBA und Matrixformel.

Danke für die Hilfe

  

Betrifft: AW: 2 Größter Wert ohne Bereich (KKGRÖSSTE)
von: SF
Geschrieben am: 15.04.2019 12:02:33

Hola,

=KGRÖSSTE({25.9.33};2)
Gruß,
steve1da


  

Betrifft: AW: 2 Größter Wert ohne Bereich (KKGRÖSSTE)
von: mctobler
Geschrieben am: 15.04.2019 12:16:27

Danke Dir, aber das geht leider nicht, da ich jetzt keine Formeln mehr benützen kann um die Zahlen zu generieren. z.B.

=KGRÖSSTE({(Anzahl(A1:B1).ANZAHL(A2:B2).(anzahl(A3:B3)};2)

mit Max geht das aber durchaus =max(ANZAHL(A1:B1);ANZAHL(A2:B2);Anzahl(A3:B3))


  

Betrifft: AW: 2 Größter Wert ohne Bereich (KKGRÖSSTE)
von: Rudi Maintaire
Geschrieben am: 15.04.2019 14:22:18

Hallo,
KGRÖSSTE() erfordert eine Matrix, während MAX() Einzelwerte erlaubt.
Der Ausdruck in den {} ist eine Matrixkonstante. Formeln sind aber nicht konstant.
Ohne Hilfszellen oder eine UDF wirst du nicht weiterkommen.

Gruß
Rudi


  

Betrifft: AW: für 3 Bereiche geht es schon mit MAX & MIN owT
von: neopa C
Geschrieben am: 15.04.2019 15:17:20

Gruß Werner
.. , - ...


  

Betrifft: Das geht schon - mit WAHL, ...
von: Luc:-?
Geschrieben am: 16.04.2019 02:47:13

…Rudi & Werner,
aber selbst mit AGGREGAT nicht ohne singulare MatrixFml, falls das die Xl-Abo-Version nicht doch drauf hat. Hier mal Varianten für 3 Spalten und 5 Zeilen:
{=KGRÖSSTE(WAHL(ZEILE(1:5);ANZAHL(A1:C1);ANZAHL(A2:C2);ANZAHL(A3:C3);ANZAHL(A4:C4);ANZAHL(A5:C5));2) }
{=AGGREGAT(14;6;WAHL(ZEILE(1:5);ANZAHL(A1:C1);ANZAHL(A2:C2);ANZAHL(A3:C3);ANZAHL(A4:C4);ANZAHL(A5:C5) );2) }
Ohne MatrixFml geht's ansonsten nur mit MatrixKonstante in WAHL, denn ZEILE verlangt hier die MatrixFml-Form …
=KGRÖSSTE(WAHL({1.2.3.4.5};ANZAHL(A1:C1);ANZAHL(A2:C2);ANZAHL(A3:C3);ANZAHL(A4:C4);ANZAHL(A5:C5));2)
=AGGREGAT(14;6;WAHL({1.2.3.4.5};ANZAHL(A1:C1);ANZAHL(A2:C2);ANZAHL(A3:C3);ANZAHL(A4:C4);ANZAHL(A5:C5) );2)
…oder mit UDF, aber das ist VBA, weshalb ich die UDFs, falls im Archiv, nicht verlinke:
=KGRÖSSTE(Collect(;;ANZAHL(A1:C1);ANZAHL(A2:C2);ANZAHL(A3:C3);ANZAHL(A4:C4);ANZAHL(A5:C5));2)
=AggregateXk(-14;6;WAHL(ZEILE(1:5);ANZAHL(A1:C1);ANZAHL(A2:C2);ANZAHL(A3:C3);ANZAHL(A4:C4); ANZAHL(A5:C5));2)
Morhn, Luc :-?

„Der beste Beweis für intelligentes Leben im Universum ist, dass noch niemand versucht hat, Kontakt mit uns aufzunehmen.“ H.Lesch, 2018, Sonneberg
Deshalb Intelligenz steigern mit …



  

Betrifft: Doch vergessen; ...
von: Luc:-?
Geschrieben am: 16.04.2019 02:56:43

…ich will mal hoffen, McTobler,
dass dir klar ist, das mehrere gleiche Maxima dazu führen, dass der 2.-größte Wert nicht der wirkliche ist, sondern einfach nur der 2. in der von KGRÖSSTE hergestellten Reihenfolge. Den echten 2.-größten Wert zu finden, wäre deutlich aufwendiger.
Morhn, Luc :-?


  

Betrifft: AW: ja, WAHL() ist hierfür eine gute Wahl owT
von: neopa C
Geschrieben am: 16.04.2019 08:57:25

Gruß Werner
.. , - ...


  

Betrifft: AW: wobei, bei nur drei Auswertungsbereichen ...
von: neopa C
Geschrieben am: 16.04.2019 09:22:38

Hallo Luc,

... würde ich (m)eine Kombination aus lediglich MAX() und MIN() in einer Formel ohne {} vorziehen. (Der Fragesteller hat ja noch nicht auf meine entsprechende Gegenfrage geantwortet).

Arbeitsblatt mit dem Namen 'Tabelle4'
 ABCDE
17  2. größte Anzahl:3
26   3
3   2. größte Summe:27
418   27
5     
615    
74    
8     
95    
106    
11     
124    
132    
14     
153    
16     
17     
187    
19     

ZelleFormel
E1=MAX(MIN(ANZAHL(A1:A3);ANZAHL(A4:A7));MIN(ANZAHL(A8:A18);ANZAHL(A4:A7)))
E2{=KGRÖSSTE(WAHL(ZEILE(A1:A3);ANZAHL(A1:A3);ANZAHL(A4:A7);ANZAHL(A8:A18));2)}
E3=MAX(MIN(SUMME(A1:A3);SUMME(A4:A7));MIN(SUMME(A8:A18);SUMME(A4:A7)))
E4{=KGRÖSSTE(WAHL(ZEILE(A1:A3);SUMME(A1:A3);SUMME(A4:A7);SUMME(A8:A18));2)}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...


  

Betrifft: Das würde dann meiner Meinung entsprechen, ...
von: Luc:-?
Geschrieben am: 16.04.2019 14:37:17

…Werner,
dass man Fmln idR situationsbezogen gestalten kann/sollte/muss/darf (wie beliebt!), wobei du ja meist evtlen (späteren) Erweite­rungs­bedarf einbeziehst… ;-)
Das ist übrigens bei VBA-SubProzeduren ähnlich, nur sollte man von vornherein spätere Änderungen, soweit möglich, voraussehbar und sinnvoll, erleichtern. Bei UDFs als Dauer-Pgmmierungshilfsmittel können die EinsatzBedingungen eng definiert sein, während UDFs für den ZellFml-Einsatz im Rahmen ihrer HauptAufgabe mit einer breiteren Palette von möglichen Situationen fertig wdn müssen, so wie Du es gewöhnlich für Deine Fmln vorsiehst (und ggf darüber hinaus).
Gruß, Luc :-?


  

Betrifft: AW: nachgefragt ...
von: neopa C
Geschrieben am: 15.04.2019 12:54:11

Hallo,

... wie viele derartige Teilformelergebnisse willst Du denn ohne Hilfszellen max. auswerten? Sind die Auswertungsbereiche der Teilformeln gleich lang und liegen immer in einem konstanten Abstand zueinander?

Die Funktionen KGRÖSSTE() auch ist dazu wie andere Funktionen für unterschiedliche Auswertungsbereiche ungeeignet. Von "Haus aus" kann man das, was Du anstrebst, mE nur mit Schachtelungen von MAX() und MIN() realisieren. Wenn nur drei Teilformelergebnisse auszuwerten sind, wäre es auch noch unproblematisch. Für viele jedoch nicht vertretbar.

Gruß Werner
.. , - ...