Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1516to1520
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
Inhaltsverzeichnis

Geschachtelte Wenn Summen Max Funktion

Geschachtelte Wenn Summen Max Funktion
07.10.2016 13:39:20
Doddi
Hallo,
ich brauche Hilfe bei einer geschachtelten WENN Funktion.
Bisher hatte ich immer mehrere Hilfsspalten dafür möchte es aber gern in einer Spalte haben.
Täglich soll im Bereich 1 geschaut werden nach der/den höchsten Nr und die höchsten Nr die Menge summiert.
Danke
Datum Bereich NR Menge
05.09.2016 | 1 | 10 | 134
05.09.2016 | 1 | 20 | 136
05.09.2016 | 1 | 30 | 560
05.09.2016 | 2 | 30 | 565
05.09.2016 | 2 | 30 | 565
06.09.2016 | 1 | 10 | 1158
06.09.2016 | 1 | 20 | 1158
06.09.2016 | 1 | 20 | 1158
06.09.2016 | 1 | 30 | 200
06.09.2016 |2 | 30 | 200
07.09.2016 | 1 | 10 | 200
07.09.2016 | 1 | 10 | 2
07.09.2016 | 2 | 10 | 200
07.09.2016 | 1 | 20 | 200
07.09.2016 | 2 | 20 | 500
07.09.2016 | 1 | 20 | 40
07.09.2016 | 1 | 30 | 40
08.09.2016 | 2 | 10 | 860
08.09.2016 | 2 | 10 | 860
08.09.2016 | 2 | 10 | 860
08.09.2016 | 1 | 10 | 860
08.09.2016 | 1 | 30 | 12
09.09.2016 | 2 | 20 | 50
09.09.2016 | 2 | 30 | 20
09.09.2016 | 1 | 30 | 252
09.09.2016 | 1 | 30 | 648
09.09.2016 | 1 | 30 | 2558

Summe: 4270

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: und warum werden dann ...
07.10.2016 13:48:53
...
Hallo Doddi,
... die beiden vorletzten Werte in die Summierung einbezogen?
Gruß Werner
.. , - ...
AW: und warum werden dann ...
07.10.2016 14:00:51
Doddi
Ja, alle markierten Mengen sollten addiert werden
Alle Mengen, die die Größe Nr des Tages haben.
Es kann auch mal die 50 oder nur 20 vorkommen als höchste Nr (ist mir aber im Beispiel erst späer aufgefallen, dass es fehlt.)
wohl weil die....
07.10.2016 14:03:28
Rudi
...auch die höchste Nr. (30) haben.
Was haben viele nur gegen Hilfspalten?
Die werden i.d.R. schneller berechnet als Matrix(funktionalitäts)Formeln über große Bereiche.
Gruß
Rudi
AW: ohne Hifsspalte geht es aber schneller ...
07.10.2016 14:18:19
...
Hallo Rudi,
... wenn man ganz ohne Formeln arbeitet ;-)
Vorausgesetzt jedem Tag gibt es einen Wert für die gleich höchste Nummer (ansonsten wohl wirklich am schnellsten mit einer zusätzlichen Hilfsspalte mit einer Formel) dann einfach eine PIVOTauswertung .
Mit ca nur einen Dutzend Mausklicks hab ich Grundlage erstellt und jeden Tag dann mit einem Zusatzmausklick das gewünschte Ergebnis.
 ABCDEFGHIJ
1DatumBereichNRMenge  DatumBereichNRSumme von Menge
205.09.2016110134  05.09.2016130560
305.09.2016120136  06.09.2016130200
405.09.2016130560  07.09.201613040
505.09.2016230565  08.09.201613012
605.09.2016230565  09.09.20161303458
706.09.20161101158  Gesamtergebnis  4270
806.09.20161201158      
906.09.20161201158      
1006.09.2016130200      
1106.09.2016230200      
1207.09.2016110200      
1307.09.20161102      
1407.09.2016210200      
1507.09.2016120200      
1607.09.2016220500      
1707.09.201612040      
1807.09.201613040      
1908.09.2016210860      
2008.09.2016210860      
2108.09.2016210860      
2208.09.2016110860      
2308.09.201613012      
2409.09.201622050      
2509.09.201623020      
2609.09.2016130252      
2709.09.2016130648      
2809.09.20161302558      
29          


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: ohne Hifsspalte geht es aber schneller ...
07.10.2016 15:14:11
Rudi
Hallo,
jedem Tag gibt es einen Wert für die gleich höchste Nummer
was wohl nicht der Fall ist.
Mal 20, mal 30, mal 50.
Gruß
Rudi
AW: dann PIVOTauwertung + eine Formel ...
07.10.2016 15:42:56
...
Hallo Rudi,
... das ist wohl schneller als eine gesamte Hilfsspaltenformel. Die anderen Bereiche auch wieder ausgefiltert und das PIVOT-Gesamtergebnis deaktiviert.
Wenn die Daten so vorliegen sollten, dann so:
 ABCDEFGHIJK
1DatumBereichNRMenge DatumBereichNRSumme von Menge Ergebnis
205.09.2016120134 05.09.2016120830 5388
305.09.2016120136 06.09.20161101158  
405.09.2016120560   202316  
505.09.2016230565   30200  
605.09.2016230565 07.09.2016110202  
706.09.20161101158   20280  
806.09.20161201158 08.09.2016150872  
906.09.20161201158 09.09.2016110252  
1006.09.2016130200   303206  
1106.09.2016230200       
1207.09.2016110200       
1307.09.20161102       
1407.09.2016210200       
1507.09.2016120200       
1607.09.2016220500       
1707.09.201612040       
1807.09.201612040       
1908.09.2016250860       
2008.09.2016210860       
2108.09.2016210860       
2208.09.2016150860       
2308.09.201615012       
2409.09.201622050       
2509.09.201623020       
2609.09.2016110252       
2709.09.2016130648       
2809.09.20161302558       
29           

Formeln der Tabelle
ZelleFormel
K2=SUMMENPRODUKT((F2:F19<F3:F20)*I2:I19)+VERWEIS(9^9;I2:I19)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: als Formel: SUMMENPRODUKT() von AGGREGAT() ...
07.10.2016 14:39:21
AGGREGAT()
Hallo Doddi,
... für die gleichen Bedingung! wie vorhin für die PIVOTauswertung benannt, so:
=SUMMENPRODUKT((AGGREGAT(14;6;C$2:C$99/(A$2:A$99=A2:A99);1)=C$2:C$99)*(B$2:B$99=1)*(D$2:D$99))
Gruß Werner
.. , - ...
Ich setze mal hier, nicht oben fort, weil Pivot …
09.10.2016 04:14:00
Luc:-?
…hier nicht passt und ich Deine obige Fml mitverwendet habe, Werner (& Rudi & Doddi);
eigentlich ist diese Auswertung doch ein klassischer Fall für die gute alte DBSUMME! Statt der Hilfs­spalten wird einfach ein Kriterien­Bereich (Spalten konjunktiv, Zeilen disjunktiv verknüpft) fest­gelegt und dann geht's ratz-fatz.
Eine Variante ohne Hilfsspalten mit dualer MatrixFml und (unpublizierter Evaluierungs-)UDF habe ich zwar ebenfalls dargestellt, aber die hat mich ein Viel­faches an Zeit gekostet:
 ABCD
1
DatumBereichNR *geändtMenge05.09.201611013405.09.201611513605.09.201612056005.09.201623056505.09.201623056506.09.20161101 15806.09.20161201 15806.09.20161201 15806.09.201613020006.09.201623020007.09.201611020007.09.2016110207.09.201621020007.09.201612020007.09.201622050007.09.20161404007.09.20161304008.09.201621086008.09.201621086008.09.201621086008.09.201611086008.09.20161301209.09.20162205009.09.20162302009.09.201613025209.09.201613064809.09.20161302 558Summe Menge für Bereich1 und maxNrn/Tag:40DB-KriterienBereichSoll (4 270)DatumBereichNR 05.09.2016120 06.09.2016130duale MxFml:07.09.20161404 27008.09.2016130 09.09.2016130 Summe Menge für Bereich1 und maxNrn/Tag:9 316D29:=SUMMENPRODUKT((AGGREGAT(14;6;C2:C28/(A2:A28=A2:A28);1)=C2:C28)*(B2:B28=1)*(D2:D28))D30: {=SUMME((CellContIn(D2:D28;;;"fst")="Fett")*D2:D28)}A32:A36: {=DataSet(A2:A28;A2:A28;1;-1)}B32:B36: {=VSplit(GLÄTTEN(WIEDERHOLEN(" 1";ZEILEN(A32:A36)));;1;1)}C32[:C36]: {=MAX(C$2:C$28*(A$2:A$28=A32)*(B$2:B$28=B32))}D37:=DBSUMME(A1:D28;D1;A31:C36)D33:D34: {=WAHL({1;2};"duale MxFml:";SUMME(MMULT(WENN((C2:C28)*(B2:B28=1)*(A2:A28=            MTRANS(DataSet(A2:A28;A2:A28;1;-1)))=MTRANS(TransFor(A1:A5;"max(index((C2:C28)*            (B2:B28=1)*(A2:A28=DataSet(A2:A28,A2:A28,1,-1)),0,row(A#)))"));D2:D28;);1^H2:H6)))
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

🙈 🙉 🙊 Gruß + schöSo, Luc :-?
Besser informiert mit …
Anzeige
Ist leider wg nachträgl Ergänzung ein von mir …
09.10.2016 14:40:01
mir
…unbemerkter Fehler entstanden. Jetzt sind die ** aber per Format gesetzt, dann wird's auch richtig:
 ABCD
1
DatumBereichNR *Menge05.09.201611013405.09.20161 15*13605.09.20161 20*56005.09.201623056505.09.201623056506.09.20161101 15806.09.20161201 15806.09.20161201 15806.09.201613020006.09.201623020007.09.201611020007.09.2016110207.09.201621020007.09.201612020007.09.201622050007.09.20161 40*4007.09.20161304008.09.201621086008.09.201621086008.09.201621086008.09.201611086008.09.20161301209.09.20162205009.09.20162302009.09.201613025209.09.201613064809.09.20161302 558Summe Menge für Bereich1 und maxNrn/Tag:40DBKriterienSoll (4 270)DatumBereichNR* geändert05.09.2016120 06.09.2016130duale MxFml:07.09.20161404 27008.09.2016130 09.09.2016130benannt: 4 270Summe Menge für Bereich1 und maxNrn/Tag:4 270D29:=SUMMENPRODUKT((AGGREGAT(14;6;C2:C28/(A2:A28=A2:A28);1)=C2:C28)*(B2:B28=1)*(D2:D28))D30: {=SUMME((CellContIn(D2:D28;;;"fst")="Fett")*D2:D28)}A32:A36: {=DataSet(A2:A28;A2:A28;1;-1)}B32:B36: {=VSplit(GLÄTTEN(WIEDERHOLEN(" 1";ZEILEN(A32:A36)));;1;1)}C32[:C36]: {=MAX(C$2:C$28*(A$2:A$28=A32)*(B$2:B$28=B32))}D37:=DBSUMME(A1:D28;D1;A31:C36)D36:=DBSUMME(A1:D28;D1;DBKriterien)D33:D34: {=WAHL({1;2};"duale MxFml:";SUMME(MMULT(WENN((C2:C28)*(B2:B28=1)*(A2:A28=            MTRANS(DataSet(A2:A28;A2:A28;1;-1)))=MTRANS(TransFor(A1:A5;"max(index((C2:C28)*            (B2:B28=1)*(A2:A28=DataSet(A2:A28,A2:A28,1,-1)),0,row(A#)))"));D2:D28;);1^H2:H6)))
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DBSUMME verlangt übrigens zwingend Bereiche (auch benannt), Ausdrücke wdn nicht akzeptiert. Aber die Anlage der Kriterienliste ist ja nicht sonderlich aufwendig und ihr Bereich kann ja auch ausgeblendet oder -gelagert wdn. Damit sind dann (bzw waren von Anfang an) die kriterien­mäßig weniger flexiblen und ansonsten ähnlich beschränkten ~WENNs-Fktt eigent­lich ziemlich über­flüssig.
Luc :-?
Anzeige
AW: dem muss ich widersprechen, denn …
09.10.2016 15:53:37
...
Hallo Luc,
... eine PIVOTauswertung ist hier sehr wohl auch geeignet.
Mit der zusätzlichen SUMMENPRODUKT()-Formel (unten in K2) ergibt sich somit der gesuchte Ergebniswert, welchen Du mit Formeln und UDFs, Bereichsnamen und DB-SUMME() doch mE viel aufwendiger ermittelst.
Man muss in der Pivotauswertung nur das richtige Layout (Tabellen-) definieren und danach entsprechend die richtigen Auswertungsoptionen aktivieren/deaktivieren und richtig filtern. Auf jeden Fall ermittele ich damit schneller und flexibler (für einen anderen Bereich muss ledig anders gefiltert werden) das gewünschte Ergebnis.
Der Formelteil +(1-ZÄHLENWENN(F:F;"Gesamt"&"*"))*VERWEIS(9^9;I2:I19) in der Formel K2 ist nur dann notwendig, wenn bei der Pivotauswertung die Zeilengesamtauswertung sowohl aktiviert und deaktiviert werden soll und könnte somit bei stets aktivierter Zeilengesamtauswertung auch entfallen.
Eine reine Formelauswertung ohne {} ist natürlich auch möglich, aber dauert natürlich von der reinen Konstruktions- wie auch Auswertungszeitzeit entsprechend länger. Vorteil dieser wäre jedoch, dass diese auch bei geänderten Daten sofort das richtige Ergebnis anzeigt und z.B. über eine Dropdownzelle (hier z.B. in F22) auch leicht und schnell ein anderer Bereich ausgewertet werden kann.
Realisierbar wäre eine solche auch ohne UDFs und bereichsnamen. Zwei Varianten dazu zeig ich hier mal auf.
Variante 1: in zwei (kurzen) Hilfsspalten (hier G23:H29) mit je einer kleinen AGGREGAT()-Formel, die nach unten zu kopieren sind und dazu der ebenfalls kurzen auswertenden Matrixfunktion(alität)sformel in H18.
Variante 2: mit einer Hilfsspalte (langen; über den gesamten Datenbereich zu kopierenden Formel) in L2 und der einfachen SUMMEN()-Auswertungsformel in L1.
 ABCDEFGHIJKL
1DatumBereichNRMenge Pivotauswertung + Formel in H18  4270
205.09.2016110134 DatumBereichNRMengen  
305.09.2016115136 05.09.2016110134  
405.09.2016120560   15136 560
505.09.2016230565   20560  
605.09.20162306565 06.09.20161101158  
706.09.20161101158   202316  
806.09.20161201158   30200  
906.09.20161201158 07.09.2016110202  
1006.09.2016130200   20200 200
1106.09.2016230200   3040  
1207.09.2016110200   4040  
1307.09.20161102 08.09.2016110860  
1407.09.2016210200   3012  
1507.09.2016120200 09.09.20161303458  
1607.09.2016220500 Gesamt  9316  
1707.09.201614040       40
1807.09.201613040  Ergebnis:4270    
1908.09.2016210860        
2008.09.2016210860 reine Formelauswertung Variante 1:   
2108.09.2016210860 Bereich:Ergebnis:4270    
2208.09.2016110860 1      
2308.09.201613012  05.09.201620   12
2409.09.201622050  06.09.201630    
2509.09.201623020  07.09.201640    
2609.09.2016130252  08.09.201630   252
2709.09.2016130648  09.09.201630   648
2809.09.20161302558       2558
29         

Formeln der Tabelle
ZelleFormel
L1=SUMME(L2:L29)
L2=(AGGREGAT(14;6;C$1:C$99/(A$1:A$99=A2)/(B$1:B$99=F$22); 1)=C2)*(B2=F$22)*D2
H18=SUMMENPRODUKT((F3:F20<F4:F21)*I3:I20)+(1-ZÄHLENWENN(F:F;"Gesamt"&"*"))*VERWEIS(9^9;I2:I20)
H21=SUMMENPRODUKT(SUMMEWENNS(D:D;A:A;G23:G101;B:B;F22;C:C;H23:H101))

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
F22Liste 1;2 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
Du hast mich leider missverstanden, …
09.10.2016 21:11:14
Luc:-?
…Werner;
ich meinte nicht, dass eine PT-Auswertung hier nicht passt, sondern dass das Thema PT nicht zu meinen oben gemachten Ausführungen passt. Bei denen stand eindeutig die Xl-Fkt DBSUMME im Vordergrund (wie auch in der nachfolgd Korrektur), die duale MxFml war nur Zugabe (und ein weiteres Bsp für diesen MxFml-Typ!) und darauf bist Du gar nicht eingegangen, dafür aber auf meine duale MxFml, die ganz ohne Hilfsspalten aus­kommt, dafür aber eine 2.Zelle benötigt!
Schneller geht die Vor­be­reitung einer PT mE auch nicht und es wird ja auch nur diese Summe, nicht die Gesamt­Dar­stel­lung benötigt. Wdn die Daten des Krite­rien­Bereichs per Fmln geholt/erzeugt, ist der genauso aus­wer­tungs­flexibel.
Gruß, Luc :-?
Anzeige
AW: auch wenn dem so ist ...
10.10.2016 08:01:02
...
Hallo Luc,
... finde ich trotzdem die Lösung über Pivotauswertung einfacher als die über die DBSUMME(). Aber das ist sicherlich individuell begründet.
Deine Duale Matrixformel hab ich wirklich übersehen. Dazu komme ich aber momentan und die nächsten Tage wohl eher nicht.
Gruß Werner
.. , - ...
Dachte, Du beziehst Dich auch auf d.duale... owT
10.10.2016 14:16:58
Luc:-?
:-?

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige