Das Clever-Excel-Forum.de - Treffen
... 14.-16. September 2018 im Allgäu ...

Summenprodukt mit Jahresabfrage
#1
Hi zusammen,

wie kann ich in die Formel V10-V76 noch die Auswahl des Jahres einbauen, daß also nur die gezählt werden, deren Abschlußdatum z.B. im Jahr 2012 (U1) liegt:

Datenbank
ABCDLMSTUVW
1NRWE- DatumAbschlußAbschluß- Datummit "fertig"BearbeitungsdauerAuswertung2012
21000100.01.19001,0012.03.2010abgeschlossen5746,9
31000200.01.19001,0015.02.2010abgeschlossen5743,3Mittelwert30,9
41000300.01.19001,0015.02.2012abgeschlossen5847,6Maximal- Dauer5847,6
51000418.12.20091,0026.02.2012abgeschlossen111,3Summe:687
61000516.02.20101,0025.02.2010abgeschlossen1,3Anzahl%
71000618.12.20091,0010.03.2013abgeschlossen165,3unbekannt34,36681222707424E-03
81000710.01.20101,0013.03.2010abgeschlossen5,9in Bearbeitung81,16448326055313E-02
91000801.03.20101,0017.03.2014abgeschlossen208fertig0
101000904.03.20101,0017.03.2014abgeschlossen207,6<=4Wo4810,700145560407569
111001025.01.20101,0019.03.2010abgeschlossen4,6<=5Wo243,49344978165939E-02
121001119.01.20101,0022.03.2012abgeschlossen110,3<=6Wo284,07569141193595E-02
131001221.01.20101,0022.03.2010abgeschlossen5,6<=7Wo182,62008733624454E-02

verwendete Formeln
Zelle Formel Bereich N/A
V3=MITTELWERT($M$2:$M$694)
V4=MAX($M$2:$M$694)
V5=SUMME(V7:V76)
V8=SUMMENPRODUKT(($L$2:$L$694="offen")*(1=1))
V9=SUMMENPRODUKT(($L$2:$L$694="fertig")*(1=1))
V10=SUMMENPRODUKT(($M$2:$M$694<=T10)*($M$2:$M$694<>"")*(1=1))
L2:L13=WENN(C2="";"";WENN(C2=1;"abgeschlossen";WENN(C2=0,875;"fertig";"offen")))
M2:M13=P2
V11:V13=SUMMENPRODUKT(($M$2:$M$694>T10)*($M$2:$M$694<=T11))
W7:W13=WENN(V7/$V$5>0;V7/$V$5;"")
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.19 einschl. 64 Bit

Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#2
Hallo,

ungetestet:

=SUMMENPRODUKT(($M$2:$M$694<=T10)*($M$2:$M$694<>"")*(JAHR($D$2:$D$694=U1)*(1=1)))
Gruß
Peter
to top
#3
Hi Peter,

Zitat:ungetestet: =SUMMENPRODUKT(($M$2:$M$694<=T10)*($M$2:$M$694<>"")*(JAHR($D$2:$D$694=U1)*(1=1)))

Die Jahr-Klammern sind falsch ;-)

(JAHR($D$2:$D$694)=U1)
to top
#4
Hallo Boris,

stimmt, danke.
Gruß
Peter
to top
#5
Hallo,


wie wäre es mit MITTELWERTWENNS:

Arbeitsblatt mit dem Namen 'Tabelle2'
 ABCDEFGHIJ
1NR 8D ReportWE- DatumAbschlußAbschluß- Datummit "fertig"BearbeitungsdauerAuswertung 2012 
21000100.01.1900112.03.2010abgeschlossen5746,9    
31000200.01.1900115.02.2010abgeschlossen5743,3  Mittelwert2023,06667
41000300.01.1900115.02.2012abgeschlossen5847,6  Maximal- Dauer5847,6
51000418.12.2009126.02.2012abgeschlossen111,3  Summe:687
61000516.02.2010125.02.2010abgeschlossen1,3   Anzahl
71000618.12.2009110.03.2013abgeschlossen165,3  unbekannt3
81000710.01.2010113.03.2010abgeschlossen5,9  in Bearbeitung8
91000801.03.2010117.03.2014abgeschlossen208  fertig0
101000904.03.2010117.03.2014abgeschlossen207,6<=4Wo481
111001025.01.2010119.03.2010abgeschlossen4,6<=5Wo24
121001119.01.2010122.03.2012abgeschlossen110,3<=6Wo28
131001221.01.2010122.03.2010abgeschlossen5,6<=7Wo18

ZelleFormel
J3=MITTELWERTWENNS($F$2:$F$13;$D$2:$D$13;">=1.1."&I1;$D$2:$D$13;"<=31.12."&I1)
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#6
Hi Peter und Boris,

danke.
Die Formel hatte ich auch schon, aber durch kopieren nach unten wurden die Zahlen immer größer.
Ich hatte übersehen, daß ja ab V11 nach unten eine andere Formel verwendet war. Nun passt es!

Könnte man das auch per Pivot machen? Wie bekäme ich da den Vergleich mit den Wochen rein?

Hier die Datei:

.xlsb   Bearbeitungsdauer.xlsb (Größe: 177,4 KB / Downloads: 5)
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#7
Hallo Ralf,

anbei mein Lösungsvorschlag mit Pivot.
Dazu habe ich in der Datenbank die Spalten G und R mit Formeln versehen.

LG Ingo


.xlsb   Bearbeitungsdauer.xlsb (Größe: 144,44 KB / Downloads: 5)
to top
#8
Hallo,

hier mal eine Formellösung ohne SUMMENPRODUKT:

Arbeitsblatt mit dem Namen 'Datenbank'
 STUVWXYZAAABAC
10<=2Wo5337,86% <=4Wo48369,80%
11> 2 -3Wo1812,86% <=5Wo243,47%
12<=4Wo1410,00% <=6Wo294,19%
13<=5Wo75,00% <=7Wo192,75%

ZelleFormel
V10=ZÄHLENWENNS(Datenbank!$M$2:$M$694;"<="&T10;Datenbank!$M$2:$M$694;"<>"&"";Datenbank!$D$2:$D$694;">=1.1."&$V$1;Datenbank!$D$2:$D$694;"<=31.12."&$V$1)
V11=ZÄHLENWENNS(Datenbank!$M$2:$M$694;"<="&T11;Datenbank!$M$2:$M$694;">"&T10;Datenbank!$M$2:$M$694;"<>"&"";Datenbank!$D$2:$D$694;">=1.1."&$V$1;Datenbank!$D$2:$D$694;"<=31.12."&$V$1)
W10=WENN(V10>0;V10/$V$5;"")
AB10=ZÄHLENWENNS($M$2:$M$694;"<="&Z10;$M$2:$M$694;"<>"&"")
AB11=ZÄHLENWENNS($M$2:$M$694;"<="&Z11;$M$2:$M$694;"<>"&"")-SUMME($AB$10:AB10)
AC10=WENN(AB10>0;AB10/$AB$5;"")
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#9
Hi Ingo,

(13.11.2014, 11:12)Superingo schrieb: anbei mein Lösungsvorschlag mit Pivot.
Dazu habe ich in der Datenbank die Spalten G und R mit Formeln versehen.

das sieht sehr gut aus mit Pivot. Ich habe sie mir dann noch erweitert, indem ich die Jahre noch in die Spaltenbeschriftung gezogen habe.

Danke!
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#10
Hi Edgar,

(13.11.2014, 11:56)BoskoBiati schrieb: hier mal eine Formellösung ohne SUMMENPRODUKT:

danke, diese Formeln probiere ich morgen auch aus.

Ist das SUMMEWENNS resourcenschonender als das SUMMENPRODUKT?
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste