Anzahl Kunden pro Anzahl Verkäufe in Excel ermitteln

Bei der Analyse von Verkaufsdaten sind nicht immer nur die absoluten Kunden- und Artikelumsätze interessant, sondern oft auch die Frage, wie viele Kunden haben wie oft in einem bestimmten Zeitraum bei Ihnen gekauft. Diese Zahlen geben Ihnen einen Überblick, ob Sie in erster Linie Kunden haben, die nur einmal kaufen, oder ob Sie viele Stammkunden mit mehreren Käufen haben.

In dem unteren Beispiel sehen Sie in den Spalten A bis C die Tabelle mit den Verkaufsdaten. Sie hat den Namen Umsatz. Rechts daneben sehen Sie die Anzahl der Kunden insgesamt sowie die Anzahl der Kunden pro Anzahl Käufe.

Excel-Funktionen Summenprodukt und Zählenwenn

Sie können die obigen Werte durch eine Kombination der Funktionen SUMMENPRODUKT() und ZÄHLENWENN() ermitteln.

Um die interessanten Zahlen zu ermitteln, gehen Sie bitte folgendermaßen vor:

  1. Aktivieren Sie die Zelle F1 und geben Sie die folgende Formel für die Ermittlung der Anzahl der Kunden ein:

    =SUMMENPRODUKT(1/ZÄHLENWENN(Umsatz[Kunde];Umsatz[Kunde]))

  2. Aktivieren Sie die Zelle F4 und geben Sie die folgende Formel ein, um zu errechnen, wie viel Kunden nur einmal gekauft haben

    =SUMMENPRODUKT(N(ZÄHLENWENN(Umsatz[Kunde];Umsatz[Kunde])=E4))/E4

  3. Klicken Sie mit der Maus in der Zelle F4 auf das Ausfüllkästchen am rechten, unteren Rand und kopieren Sie die Formel bis zur Zelle F8.
  4. Aktivieren Sie die Zelle F9, um die Anzahl der Kunden zu ermitteln die mehr als fünfmal gekauft haben. Geben Sie folgende Formel ein:

    =F1-SUMME(F4:F8)

Erläuterungen der einzelnen internen Zwischenschritte

Die Funktionsweise der ersten Formel sowie die einzelnen Schritte zum Ergebnis soll anhand des folgenden, verkürzten Beispiels erläutert werden:

Excel-Funktionen Summenprodukt und Zählenwenn

In der Spalte A befinden sich 3 verschiedene Kunden. Die Anzahl der Kunden können Sie durch die folgende Formel ermitteln:

=SUMMENPRODUKT(1/ZÄHLENWENN(A2:A8;A2:A8))

Im ersten Schritt wird die Funktion ZÄHLENWENN() eingesetzt, um für jeden Eintrag in der Spalte A zu ermitteln, wie oft der Kunde jeweils vorkommt (siehe Spalte B). Der Kunde 1 kommt einmal vor, der Kunde 2 zweimal und der Kunde 3 viermal.

Um zu ermitteln, wie viel unterschiedliche Kunden in der Liste vorhanden sind, wird pro Zeile ein Gewichtungsfaktor angegeben, so dass die Summe aller Gewichtungsfaktoren pro Kunde immer die Zahl 1 ergibt, weil er nur einmal gezählt werden darf. Hierzu werden im zweiten Schritt für alle Zahlen aus Spalte B die anteiligen Werte ermittelt (1/Wert aus Spalte B), d h. wenn ein Kunde z. B. zweimal in der Tabelle erscheint, wird jedes Vorkommen nur zur Hälfte gezählt.

Im dritten und letzten Schritt wird die Funktion SUMMENPRODUKT verwendet, um die Werte, die in Spalte C erscheinen, aufzuaddieren.

Das Endergebnis sind in diesem Beispiel 3 verschiedene Kunden. Mit den beiden Funktionen SUMMENPRODUKT() und ZÄHLENWENN() benötigen Sie nur eine einzige Formel ohne Hilfsspalten, um zum gewünschten Ergebnis zu kommen.

Um beispielsweise zu ermitteln, wie viele Kunden zweimal bei Ihnen gekauft haben, können Sie folgende Formel einsetzen:

=SUMMENPRODUKT(N(ZÄHLENWENN(E2:E8;E2:E8)=2))/2

Die Einzelschritte sehen folgendermaßen aus:

Excel-Funktionen Summenprodukt und Zählenwenn

Wie im ersten Beispiel wird im ersten Schritt wird die Funktion ZÄHLENWENN() eingesetzt, um für jeden Eintrag in der Spalte E zu ermitteln, wie oft der Kunde jeweils vorkommt (siehe Spalte F).

Im zweiten Schritt wird für jede Zeile geprüft, ob die Anzahl in Spalte F die Zahl 2 ist.

Im dritten Schritt wird die Funktion N() eingesetzt. Sie wandelt z.B. den Wahrheitswert Ja in die Zahl 1 und Nein in die Zahl 0 um (siehe Spalte H).

Im vierten Schritt werden die Werte aus Spalte H aufaddiert. Das Ergebnis ist 2.

Als letzten Schritt der Berechnung wird das Ergebnis der SUMMENPRODUKT-Funktion noch durch die angegebene Anzahl der Käufe dividiert (in unserem Beispiel 2 Käufe), d.h. ein einziger Kunde hat zweimal gekauft.

Hinweis

Die ersten beiden Formeln mit SUMMENPRODUKT() und ZÄHLENWENN errechnen die Werte nur dann, wenn in der Spalte Kunde der Tabelle Umsatz keine Leerzelle existiert. Falls eine leere Zelle existieren sollte, erscheint der Fehler #DIV/0!. Ändern Sie in diesem Falle die erste Formel folgendermaßen ab:

SUMMENPRODUKT((Umsatz[Kunde]<>””)/ZÄHLENWENN(Umsatz[Kunde];Umsatz[Kunde]&””))

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

3 Kommentare

  1. zeitersparnis

    Antworten
  2. Super – vielen Dank!

    Antworten
  3. Beispieldatei wäre hilfreicher

    Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Pin It on Pinterest

Share This