Registriert seit: 24.02.2017
Version(en): 365; (2013)
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?
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
11.02.2020, 15:19
(Dieser Beitrag wurde zuletzt bearbeitet: 11.02.2020, 15:19 von 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 |
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Registriert seit: 16.08.2017
Version(en): 2007 / 2010 / Web
11.02.2020, 15:48
(Dieser Beitrag wurde zuletzt bearbeitet: 11.02.2020, 15:48 von 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
Registriert seit: 15.04.2014
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
Folgende(r) 1 Nutzer sagt Danke an Josef B für diesen Beitrag:1 Nutzer sagt Danke an Josef B für diesen Beitrag 28
• Jockel
Registriert seit: 14.04.2014
Version(en): Office 2013/2016/2019/365
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.
lg Chris
Feedback nicht vergessen.
3a2920576572206973742064656e20646120736f206e65756769657269672e
Folgende(r) 1 Nutzer sagt Danke an chris-ka für diesen Beitrag:1 Nutzer sagt Danke an chris-ka für diesen Beitrag 28
• Jockel
Registriert seit: 10.04.2014
Version(en): Office 2019
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"
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht
"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Registriert seit: 16.08.2017
Version(en): 2007 / 2010 / Web
Hi
AGGREGAT({14.15}; Ja das ist schick. :18: