Clever-Excel-Forum

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?

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

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

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

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.

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" 

Hi

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

StrammerMax

WillWissen

Elex

Josef B

chris-ka

Jockel

Elex