Office: (Office 2010) Eindeutige Werte Zählen, nach mehreren Kriterien, einem Datum zuordnen

Hallo Zusammen,

seit Tagen sitze ich bei einer Formel fest die "eigentlich" nicht so schwer sein dürfte.

Mir liegt eine Tabelle vor, die sich tägl. füllt und bis zu 10.000 Zeilen am Monatsende erreicht.
Aufgelistet sind u.a. das Datum, Auftragsnummer, die einzelnen Art., Preis und Status des Auftrags.

Ich benötige eine Zusammenfassung die mir auf Tagesebene den Umsatz in Summe nennt und die Anzahl der Aufträge vom Status Versendet und Abgegeben.

Mit Summewenns bekomme ich einfach den Umsatz berechnet.
Bei der eindeutigen Anzahl der Aufträge, unter Ausschluss der doppelten, scheitere ich immer wieder.
In der Datei anbei seht Ihr eine Beispieltabelle und rechts davon, die Lösung die ich benötigen würde...

Ich wäre sehr Dankbar für Ideen!


Quihi

:)

 

Hallo Quihi,

schau mal hier:

http://www.ms-office-forum.net/forum...d.php?t=252062

 

Hallo Quihi,

und für den Fall, dass Du die Formeln nicht auf Deinen Fall angepasst bekommst, hier die Formeln für
G2:

PHP:

         =SUMMENPRODUKT((($D$2:$D$29="Abgegeben")+($D$2:$D$29="versendet"))*(WERT(TEXT($A$2:$A$29;"TT.MM.JJJJ"))=F2)*(1/ZÄHLENWENN($B$2:$B$29;$B$2:$B$29)))


 

Hallo,

m.E. ist das eine klassische Aufgabe für eine Pivot-Tabelle.

mfg

 

Hallo Zusammen,

erstmal Danke für das schnelle Feedback!

@Fennek11
Pivot nutzen wir aktuell, allerdings sind dadurch immer wieder Flüchtigkeitsfehler entstanden und wir haben viele Zwischenschritte.

@Superingo2
Die Formel ist schon super! Aber wie setze ich diese ein, wenn der gesamten Rohdatenbereich sich täglich erweitert?
Heisst z.B. gestern Daten bis Zeile 29 heute füge ich 40 hinzu. Wenn ich in der Formel die gesamte Spalte angebe statt bis 29, funktiert die Formel nicht. Kann ich hier einen dynamischen Bereich einsetzen? Würde dann nochmal googlen wie man den macht..

 

P.S. Auf der Seite Excelformeln habe ich mich bereits durchgearbeitet aber leider ohne Erfolg. Vielleicht habe ich es auch einfach nicht auf meine Daten übertragen bekommen. Ich weiss es leider nicht.

 

Hallo Quihi,

das ist kein Problem für mich.
Für G2:

PHP:

         {=SUMME((($D$2:INDEX($D:$D;MAX(WENN($A:$A<>"";ZEILE($A:$A))))="Abgegeben")+($D$2:INDEX($D:$D;MAX(WENN($A:$A<>"";ZEILE($A:$A))))="versendet"))*(WERT(TEXT($A$2:INDEX($A:$A;MAX(WENN($A:$A<>"";ZEILE($A:$A))));"TT.MM.JJJJ"))=F2)*(1/ZÄHLENWENN($B$2:INDEX($B:$B;MAX(WENN($A:$A<>"";ZEILE($A:$A))));$B$2:INDEX($B:$B;MAX(WENN($A:$A<>"";ZEILE($A:$A)))))))


 

Hallo Superingo2

erhalte bei der Formel 0 oder als Array #WERT!.
Muss ich etwas besonderes bei der Formel beachten?

LG Quihi

 

Hallo Quihi,

#WERT erhältst Du, wenn der Bereich von "WERT(TEXT($A$2:$A$29;"TT.MM.JJJJ"))" keinen Wert enthält. Also Bei der Text-Überschrift oder bei leeren Zellen.
Null erhältst Du, wenn keine Bedingung zutrifft.

Ach, ich vergass, es handelt sich um Matrixformeln!
Geschweifte Klammern nicht mit eintippen oder mitkopieren, sondern durch STRG+SHIFT+ENTER erzeugen. Das gilt bei jedem reinklicken in die Zellformel!

LG Ingo

 

Hallo Quihi,

anstatt meiner Aufblähung "WERT(TEXT.....;"TT.MM.JJJJ"))" habe ich nun erkannt, dass ABRUNDEN(...........;0) völlig ausreicht und dann auch kein Problem mit leeren Datensätzen hat.
Also besser:
G2:

PHP:

         =SUMMENPRODUKT((($D$2:$D$9999="Abgegeben")+($D$2:$D$9999="versendet"))*(ABRUNDEN($A$2:$A$9999;0)=F2)*(1/ZÄHLENWENN($B$2:$B$9999;$B$2:$B$9999)))


 

Hallo Quihi,

aber am Besten arbeitest Du mit 2 Hilfsspalte.
In Spalte E errechnest Du den Auftragszähler mit

PHP:

         =WENN(ZÄHLENWENN($B$2:B2;B2)=1;1;0)


 

Hallo Ingo,

ja SUMMENPRODUKT ist zwar sehr leistungsfähig, benötigt aber verständlicherweise auch seine Zeit.

Zumindest der Umsatz kann aber auch mit SUMMEWENNS ermittelt werden.

Code:

 

Hallo,

und mit einer Hilfsspalte können auch die Aufträge mit ZÄHLENWENNS gezählt werden.

Ja so leistungsfähig SUMMENPRODUKT auch ist, bei größeren Datenmengen nehme ich aber dann doch gerne die Hilfsspalte in Kauf, um so schneller die Ergebnisse zu bekommen.

 

Hallo Zusammen!

Vielen Dank für die Lösungswege! es sind schon sehr viele Datensätze und es ist nicht absehbar wie diese noch steigen. Da ich nicht ausschliessen kann, dass jemand die Rohdaten anders sortiert, ist der Lösungsweg von Superingo2 mit den zwei Hilfsspalten für mich am sichersten.
Werde aber auch Summenprodukt nochmal testen.
Danke nochmal!
LG
Quihi

 

Hallo WS-53,

schöne Idee mit mit größer und kleiner+1 zu arbeiten.
Hätte ich auch selber draufkommen müssen. Bin ich aber nicht.
Dadurch lässt sich eine Hilfsspalte einsparen.
Meinen Respekt!

LG Ingo