Umsatz pro Kunde/ Jahr ermitteln und Kundenumsätze als Rangliste ausgeben

Question

Guten Abend

Ich habe noch eine Herausforderung, die ich ohne eure Hilfe nicht werde lösen können.
Meine Kundenumsatzliste mit 1345 Zeilen enthält jede ausgestellte Rechnung über 6 Jahre (2008 - 2013). Nun möchte ich mit Excel die Umsätze der einzelnen Rechnung pro Kunde pro Jahr zusammenzählen lassen z.B. 2012. Dies würde ich mit der Funktion Summenprodukt noch hinbekommen.
Allerdings soll mir Excel anschliessend eine Top 10 Kundenumsatzliste für 2012 mit den entsprechenden Umsätzen anzeigen. Dabei muss noch berücksichtigt werden, dass ein Kunde dummerweise 2 Kundennummern hat, die Umsätze beider Kundennummern müssen also noch zusammengezählt werden.

Spalte A: Rechnungsdatum
Spalte B: Kundennamen
Spalte C: Rechnungsbetrag

Ich hab' keine Ahnung, wie ich das anstellen soll. Geht das überhaupt nur in Excel oder braucht es hierfür einen VBA Code?

LG, Ela71

Answer 1

Hallo Ela,

In welcher Spalte steht die Kundennummer?


Gruß

Paul1

Answer 2

Hallo Paul1

Spalte D

Gruss, Ela71

Answer 3

Hallo Ela71,

Habe den Bereich für das Jahr 2012 ab Zeile2 in eine eigene Tabelle kopiert, die Überschriften in Zeile 1 kopiert.

Formel in E2 und runterkopieren und zwar nach Namen, da es bei einem Kunden verschiedene Kundennummern gibt:

=WENN(ZÄHLENWENN(B2:B12;B2)>1;"";SUMMEWENN(B:B;B2;C:C))

Anschließend Autofilter in erster Zeile:
Absteigend sortiert
und dann Top 10

siehe Beispiel Tabelle2
http://www.file-upload.net/download-8447917/Kunden-verschiedene-Nummern.xls.html

Nachdem es nur ein Konzept ist, wären die Zellbezüge anzupassen.

Gruß

Paul1

Answer 4

Hallo Paul1

Vielen Dank für deinen Lösungsansatz. Der ist super cool!
Wieder etwas gelernt!

Habe versucht, die Periode (2012) als weiteres Kriterium mit in die Formel einzubinden (bei summewenns), sodass nicht erst die Daten von 2012 kopiert werden müssten.
Es wird mir zwar der Wert "Wahr" zurückgegeben, den kann ich aber irgendwie nicht weiterverwenden. Ich meine mit z.B. wenn "Wahr" dann "tu dies" sonst "tu das".

Weiss jemand, ob man "Wahr" weiterverwenden kann?

LG, Ela71

Answer 5

Hallo Ela71,

Wenn die von Dir angestrebte Alternative gelänge, wäre es für mich ein Quantensprung, "Summewenns" kann ich leider nicht testen, da ich rückständigerweise noch mit Excel 2003 arbeite.

Das Problem ist aber die zweite Bedingung in die gegenständliche Formel einzubauen, um mit dem gleichen Effekt runterkopieren zu können, daher hatte ich die Spreu vom Weizen getrennt.

Gruß

Paul1

Answer 6

Hallo Ela71,

Probier Folgendes (etwas pflegeleichter):

Überschrift E1: "Zahlen 2012"

Formel in E2:

=SUMME(WENN(JAHR(A2:$A$15)=2012;D2;0))

und runterkopieren

Überschrift F1: "Auswertung 2012"

Formel in F2:

=WENN(ZÄHLENWENN(B2:B15;B2)>1;"";SUMMEWENN(B:B;B2;E:E))

und runterkopieren

Den Autofilter anwenden wie in Antwort 3 beschrieben

Die Spalte A (Datum) zeigt nach der Auswertung lediglich das letzte Rechnungsdatum des Kunden an (kann durchaus auch 2013 sein), ist aber für das Ergebnis belanglos, da die Umsätze nach Kundennamen und Jahr 2012 entscheidend sind.

Zellbezüge sind anzupassen!

Gruß

Paul1

Excel 2003

Answer 7

Nachtrag

Wenn die Rechnungsbeträge in Spalte C sind, dann folgende Formel:

=SUMME(WENN(JAHR(A2:$A$15)=2012;C2;0))

Gruß

Paul1

Answer 8

Hallo Ela71,

Habe es mir nochmals angeschaut und es sollte eine Gesamtlösung für beide Anfragen werden

Beispiel:

Bereich A1:C18
Rechnungsdatum Kundenname Rechnungsbetrag
31.12.2008 Berger 100
01.09.2009 Müller 50
02.10.2010 Meier 200
03.08.2011 Brunner 300
04.08.2011 Berger 8000
06.03.2012 Kramer 400
05.01.2013 Krames 200
08.02.2013 Bauer 250
09.03.2013 Berger 400
18.05.2013 Brunner 1000
19.05.2013 Wagner 2500
20.05.2013 Brunner 1000
21.05.2013 Brunner 500
22.05.2013 Wagner1 3000
23.05.2013 Müller 1000
24.05.2013 Müller 1000
25.05.2013 Wagner1 1000

E1: "Umsätze Kunden gesamt 2013 einzeln"
E2:
=SUMME(WENN(JAHR(A2:$A$18)=2013;C2;0))
und runterkopieren
F1: "Umsätze Kunden gesamt 2013 gruppiert"
F2:
=WENN(ZÄHLENWENN(B2:B21;B2)>1;"";SUMMEWENN(B:B;B2;E:E))
und runterkopieren
G1:"Feststellung Kunden neu 2013"
G2:
=WENN(ISTNV(INDEX($C$2:$C$18;VERGLEICH(B2;$B$2:$B$7;0)));"x";INDEX($C$2:$C$18;VERGLEICH(B2;$B$2:$B$7;0)))
und runterkopieren
H1:"Umsätze Kunden neu gruppiert 2013"
H2:
=WENN(G2="x";F2;"")

Summe: =SUMME(H2:H18)
Anzahl: =ANZAHL(H2:H18)

je nach Jahr könnten die Formeln in E2 (Jahr) und G2 (Index und Vergleichsbereich) flexibel angepasst werden.

Nachdem Du keine Möglichkeit hast eine Beispieldatei downzuloaden, habe ich es hiermit versucht verständlich zu beschreiben.

Gruß

Paul1

P.S.
bei Fragen schreibe es