Summe ohne größte / kleinste 5 Werte

[StrammerMax]

Hallo zusammen,

habt ihr Eine Idee, wie man von einer dynamischen Anzahl an Werten in einer Spalte die Summe nehmen kann - dabei aber jeweils die größten und kleinsten 5 Werte nicht mir einbezieht?

[WillWissen]

Hi Max,

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Liste	ohne 5-kleinste	ohne 5-größte
2	1	6	15
3	2	7	14
4	3	8	13
5	4	9	12
6	5	10	11
7	6	11	10
8	7	12	9
9	8	13	8
10	9	14	7
11	10	15	6
12	11	16	5
13	12	17	4
14	13	18	3
15	14	19	2
16	15	20	1
17	16	195	120
18	17
19	18
20	19
21	20

Zelle	Formel
B2	=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$21);ZEILE(A6)));"")
C2	=WENNFEHLER(INDEX(A:A;AGGREGAT(14;6;ZEILE($A$2:$A$21);ZEILE(A6)));"")
B17	=SUMME(B2:B16)
C17	=SUMME(C2:C16)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Elex]

Hi

 ____|_A_|_B|_C_|
   1|  1|  |   |
   2|  2|  | 91|
   3|  3|  |   |
   4|  4|  | 13|
   5|  5|  |   |
   6|  6|  |   |
   7|  7|  |   |
   8|  8|  |   |
   9|  9|  |   |
  10| 10|  |   |
  11| 11|  |   |
  12| 12|  |   |

je nach dem wie man die Aufgabe versteht ohne die G/K 5.

C2    =SUMME(AGGREGAT(15;6;A:A;ZEILE(INDEX(A6:INDEX(A:A;ANZAHL(A:A));)));AGGREGAT(14;6;A:A;ZEILE(INDEX(A6:INDEX(A:A;ANZAHL(A:A));))))       mit strg+shift+enter

C4    =SUMME(AGGREGAT(14;6;AGGREGAT(15;6;A:A;ZEILE(INDEX(A6:INDEX(A:A;ANZAHL(A:A));)));ZEILE(INDEX(A6:INDEX(A:A;ANZAHL(A:A)-5);))))      mit strg+shift+enter

Gruß Elex

[Josef B]

Hallo

Summe ohne größte / kleinste 5 Werte mit folgender Array-Formel

{=SUMME(-AGGREGAT({14.15};6;A2:A99;ZEILE(B1:B5));A2:A99)}

Achtung, die Array-Klammern am Anfang und Ende der Formel nicht eingeben, sondern die Eingabe mit   [Strg] + [Shift] + [Enter] abschliessen.

Durch die Tastenkombination weiß Excel, dass hier mit Matrizen gerechnet werden soll (Matrixformel).

Gruss Sepp

[chris-ka]

Hi,

oder auch ohne fixe Zeilenvorgabe
zumindest ab Off. 2016 möglich wenn nicht schon in früheren Versionen

=SUMME(-AGGREGAT({14.15};6;A:A;ZEILE(1:5));A:A)

wiederum Formel mit strg+Umschalt+ Enter abschließen.

[Jockel]

Und alternativ mit Power Query..:

PHP-Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Liste", Int64.Type}}),
    #"Sortierte Zeilen" = Table.Sort(#"Geänderter Typ",{{"Liste", Order.Ascending}}),
    #"Entfernte oberste Zeilen" = Table.Skip(#"Sortierte Zeilen",5),
    #"Entfernte untere Zeilen" = Table.RemoveLastN(#"Entfernte oberste Zeilen",5),
    #"Berechnete Summe" = List.Sum(#"Entfernte untere Zeilen"[Liste])
in
    #"Berechnete Summe" 


[Elex]

Hi

AGGREGAT({14.15};   Ja das ist schick. :18: