Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1852to1856
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1852to1856
1852to1856
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mittelwert ohne Top10

Mittelwert ohne Top10
21.10.2021 08:05:41
Klaus
Moin Forum,
ich brauche mal Hilfe mit einer Matrixformel.
Gegeben: Eine unsortierte Liste mit Datum, Wert.
Gewünscht: Summe, Anzahl und Mittelwert pro Kalenderwoche: das ist kein Problem.
Außerdem gewünscht: Summe, Anzahl und Mittelwert pro Kalenderwoche AUSSER den Top10 der entsprechenden Woche - also nur die niedrigsten Werte.
Den INPUT kann ich nicht ändern, der kommt genau so aus dem System.
Im OUTPUT habe ich die Zielwerte schon manuell (in gelb) eingetragen.
Die Originaldatei hat natürlich mehrere zehntausend Einträge.
Das ganze müsste doch mit {KGRÖSSTE(,,ZEILE(A1:A10))} als Matrix zu lösen sein? Ich bekomm die Formel gerade aber nicht zusammen.
Musterdatei:
https://www.herber.de/bbs/user/148720.xlsx
Wer von den Formelprofis kann mir eben helfen? :-)
LG,
Klaus M.

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mittelwert ohne Top10
21.10.2021 08:28:46
SF
Hola,
ich hab im Input-Blatt in Spalte C die Kalenderwoche des Datums aus A ermittelt um es mir leichter zu machen :)
Als Matrixformel:

=SUMMEWENNS(INPUT!B:B;INPUT!C:C;E5;INPUT!B:B;"
Für die anderen dann analog.
Gruß,
steve1da
AW: Mittelwert ohne Top10
21.10.2021 08:51:51
Klaus
Oh, da hatte ich einen Knoten im Hirn. Ich wollte unbedingt eine Matrixformel mit 1:10 bauen - alles kleiner als kgrößte(10) ohne Matrixformel ist natürlich viel einfacher. Das bekomme ich umgesetzt, vielen Dank!
LG,
Klaus M.
AW: Mittelwert ohne Top10
21.10.2021 12:02:06
Klaus
Hmmm ...
es funktioniert, aber ich verstehe nicht warum! Hier meine Formel (die ein paar mehr Bedingungen erfüllt)

=SUMMEWENNS(All!AJ$9:AJ$100000,All!$O$9:$O$100000,$B$2,All!$P$9:$P$100000,$B$3,All!$AA$9:$AA$100000,">"&$V7,All!$AA$9:$AA$100000,"$V7) *(All!$AA9:$AA100000"&$V7,All!$AA$9:$AA$100000,"
nochmal gekürzt auf den relevanten Teil:

=SUMMEWENNS(All!AJ$9:AJ$100000,All!$AA$9:$AA$100000,">"&$V7,All!$AA$9:$AA$100000,"$V7) *(All!$AA9:$AA100000"&$V7,All!$AA$9:$AA$100000,"
Dieser Formelteil

KGRÖSSTE(WENN((All!$AA9:$AA100000=>$V7)*(All!$AA9:$AA100000
Ist doch eine Matrixformel - KGRÖSSTE aus Matrix-WENN mit per * verknüfpten Bedingungen.
DIe Formel funktioniert aber korrekt auch ohne {Matrix} Klammern.
Kann mir jemand erklären wieso, liegt das an meiner Excelversion?
LG,
Klaus M.
Anzeige
AW: Mittelwert ohne Top10
21.10.2021 12:12:45
SF
Hast du wirkilch Excel2016?
In O365 muss man nicht mehr mit Strg-Shift-Enter abschließen.
Tatsächlich 365 ....
21.10.2021 12:14:07
Klaus
Hallo SF,
tatsächlich - es ist Excel 365!
Danke :-)
LG,
Klaus M.
AW: Mittelwert ohne Top10
21.10.2021 08:40:04
RPP63
Nur als Ergänzung eine Lösung mit SUMMENPRODUKT und SEQUENZ (erst in aktuellen Versionen verfügbar):
 CDEFGHIJ
3       OHNE TOP 10
4  KWSummeAnzahlMittelwert Summe
506.09.202112.09.202136220,279595297,5958481117,776516117,77
613.09.202119.09.202137548,797384609,14662306394,179953394,17
720.09.202126.09.202138653,3159965811,2640689433,135729 
827.09.202103.10.202139344,036111447,81900251223,237476 

ZelleFormel
I5=SUMMENPRODUKT(KGRÖSSTE(INPUT!B$2:B$225*(ISOKALENDERWOCHE(INPUT!A$2:A$225)=E5);SEQUENZ(G5-10;;11)))

Gruß Ralf
Anzeige
AW: Mittelwert ohne Top10
21.10.2021 08:52:56
Klaus
Hallo Ralf,
auch dir danke - deine Lösung werde ich ausprobieren, da ich SEQUENZ() spannend finde (kenne ich noch nicht). Nutzen werde ich aber die Variante von SF, da sie ohne Matrix auskommt was mir lieber ist.
LG,
Klaus M.
AW: Mittelwert ohne Top10
21.10.2021 09:55:10
RPP63
SEQUENZ gibt es (leider) nur in Excel 365, 2021 und Online.
Die neuen Spill-Formeln sind allesamt spannend und benötigen keinen Abschluss per CSE
SEQUENZ()
ZUFALLSMATRIX()
FILTER()
SORTIEREN()
SORTIERENNACH()
EINDEUTIG()
Du kannst sie aber mal in Excel Online ausprobieren und wirst sie nicht mehr missen wollen.
Auch die Möglichkeit, in LET()-Formeln Variablen zu errechnen und sie mehrfach in der Formel ohne Neuberechnung zu verwenden (also quasi der Verzicht auf Hilfsspalten) ist imo ein must have.
Anzeige
mit banaler Logik (funktioniert auch mit Excel 4)
21.10.2021 12:25:30
WF
ohne Bezug auf das gesendete Beispiel.
{=(SUMME(A1:A999)-SUMME(KGRÖSSTE(A1:A999;ZEILE(1:10))))/(ANZAHL(A1:A999)-10)}
mit (überflüssiger) Index-Verlängerung für die {}-Gegner:
=(SUMME(A1:A999)-SUMME(INDEX(KGRÖSSTE(A1:A999;ZEILE(1:10));0)))/(ANZAHL(A1:A999)-10)
WF
AW: mit banaler Logik (funktioniert auch mit Excel 4)
21.10.2021 12:34:44
Klaus 

{SUMME(KGRÖSSTE(A1:A999;ZEILE(1:10)))}
Ja - ich wusste doch es ist irgendwas mit Zeile und Matrix :-) Bin etwas eingerostet, ist lange her.
Danke Walther!
LG,
Klaus M.
AW: Auswertung für KWen auch ohne Hilfsspalte ...
21.10.2021 12:38:22
neopa
Hallo Klaus,
... für Deine Excelversion als Formellösung z.B. so:
Arbeitsblatt mit dem Namen 'OUTPUT'
 EFGHIJKL
3 ALLE   OHNE TOP 10  
4KWSummeAnzahlMittelwert SummeAnzahlMittelwert
536220,279595297,595848105 117,7765162196,198764009
637548,7973837609,146623061 394,1799533507,883599066
738653,31599575811,26406889 433,1357292489,023661026
839344,0361105447,819002512 223,2374762346,565808125
9        

NameBezug
_Dat=INPUT!$A$2:INDEX(INPUT!$A:$A;_ANZ)
_DatWert=(_KW=OUTPUT!$E3)*(_Werte&ltAGGREGAT(14;6;_Werte/(_KW=OUTPUT!$E3);10))
_Werte=INPUT!$B$2:INDEX(INPUT!$B:$B;_ANZ)

ZelleFormel
J5=SUMMENPRODUKT(_DatWert;_Werte)
K5=SUMMENPRODUKT(_DatWert)
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Die durch das Tool von Gerd leider nicht gelisteten aber benutzten benannten Formeln: _ANZ und _KW, die Basis für die oben gelisteten benannten Formeln: _Dat, _DatWert und _Werte sind, müssen (in J5) noch wie folgt definiert werden:
_ANZ: =ANZAHL(INPUT!$A:$A)+1
_KW: =WENNFEHLER(KÜRZEN((_Dat-DATUM(JAHR(_Dat+3-REST(_Dat-2;7));1;REST(_Dat-2;7)-9))/7);0)
Gruß Werner
.. , - ...
Anzeige
AW: Auswertung für KWen auch ohne Hilfsspalte ...
21.10.2021 12:41:03
Klaus
Auch dir danke, Werner! Deine Lösung probier ich aber nicht mehr aus - meine Datei ist (mit Matrixformeln) funktional und bereits abgeliefert :-)
Die Lösung mit benannten Formeln ist aber sehr elegant (und verhindert Formelmonster).
LG,
Klaus M.

305 Forumthreads zu ähnlichen Themen

Pivot berechnetes Feld
Peter

Hallöchen zusammen,
entweder stelle ich mich zu dämlich an, oder ich kapiere die Pivot wirklich nicht.
Habe eine Tabelle, aus der ich mir die Daten ziehe.
In die Datenfelder der Pivot kommt die gesamte Anzahl von Datensätzen.
In die Spaltenfelder die Differenzierung eines bestim...
15.08.2008 10:34:52
Thread-ID: 1002088
4 Beiträge
Anzeige
Pivot Bericht nur Leer - Felder anzeigen
Karsten

Hallo Liebe Excel Freunde,
Ich möchte gern nach Aktualisierung einer Pivotabelle in einer bestimmen Spalte nur die Leerfelder anzeigen.
Mit dem Macro- Recorder funktioniert das nicht richtig, wenn man dies per Hand durchführt, muss man sich nach der Aktualisierung erst alle Felder anze...
13.01.2009 14:49:00
Thread-ID: 1039261
7 Beiträge
Pivot-Gruppierung: nur Teil anzeigen
Florian

Hallo Forumgemeinde!
hab mal wieder eine Frage an Euch:
Untenstehender Code generiert eine Pivottabelle, wobei Spalte 30 (in der Pivottabelle also ".PivotFields(30)") sich auf ein Datum bezieht, welches in der Tabelle nach jahr und Monat gruppiert wird. Wie lautet nun die Codezeile, um...
03.06.2009 08:37:35
Thread-ID: 1078762
3 Beiträge
Anzeige
Pivottabelle Spalten berechnen
karsten

Hallo Zusammen,
ich habe folg.Frage.:
Ich habe eine Tabelle mit 2 Spalten.
1.Spalte: DATUM
2.Spalte: CODE
DATUM CODE
01.01.2009 A
08.02.2009 M
23.02.2009 C
09.08.2009 F
Wie kann ich in einer Pivottabelle die Datumsangaben bereits in Monaten eingeteilt dar...
23.09.2009 11:15:14
Thread-ID: 1104846
10 Beiträge
Pivot - berechnetes Feld
heide_tr

hallo zusammen,
ich habe eine Tabelle mit 3 Spalten: Teilnehmer, Datum1, Datum2.
Die Teilnehmer können beliebig oft vorkommen (Zeilen), die beiden Datumsfelder sind nicht immer gefüllt.
Ich möchte in einer Pivottabelle je Teilnehmer die Anzahl von Datum1 und Datum2 ausweisen. Das fu...
26.11.2009 18:41:34
Thread-ID: 1120340
2 Beiträge
Pivot - berechnetes Feld
heide_tr

hallo zusammen,
ich habe eine Tabelle mit 3 Spalten: Teilnehmer, Datum1, Datum2.
Die Teilnehmer können beliebig oft vorkommen (Zeilen), die beiden Datumsfelder sind nicht immer gefüllt.
Ich möchte in einer Pivottabelle je Teilnehmer die Anzahl von Datum1 und Datum2 ausweisen. Das fu...
26.11.2009 18:29:44
Thread-ID: 1120341
2 Beiträge
Pivot - berechnetes Feld
heide_tr

hallo zusammen,
ich habe eine Tabelle mit 3 Spalten: Teilnehmer, Datum1, Datum2.
Die Teilnehmer können beliebig oft vorkommen (Zeilen), die beiden Datumsfelder sind nicht immer gefüllt.
Ich möchte in einer Pivottabelle je Teilnehmer die Anzahl von Datum1 und Datum2 ausweisen. Das fu...
27.11.2009 02:17:34
Thread-ID: 1120342
5 Beiträge
Pivot: berechnetes Feld klemmt
Mike


Hallo Cracks,
in einer CD-Auswertung soll eine durchschnittliche <b>Berechnung</b>
eingefügt werden -> Gespielt / Anzahl Titel (orange im Beispiel).
https://www.herber.de/bbs/user/66976.xls
Die Anzahl Titel lassen sich schnell bilden, doch mit diesem Feld ka...
04.01.2010 15:09:49
Thread-ID: 1127062
6 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
3
2
6
2
11
9
1
1
4
5
5
7
1
4
2
2
1
5
5
2
3
4
4
2
5