Summenprodukt mit VERGLEICH in Excel
Schritt-für-Schritt-Anleitung
Um die Anzahl der Neukunden pro Monat mithilfe der SUMMENPRODUKT-Funktion in Excel zu ermitteln, kannst Du folgende Schritte ausführen:
-
Daten vorbereiten: Stelle sicher, dass Deine Daten in einer Tabelle vorliegen. Die Bestelldaten sollten in einer Spalte (z.B. Spalte C) und die Kundennamen in einer anderen (z.B. Spalte A) sein.
-
Formel eingeben: Nutze die folgende Formel, um die Neukunden pro Monat zu zählen:
=SUMMENPRODUKT((($A$3="Alle")+(DATEN_AE!$D$2:$D$662=$A$3))*(TEXT(DATEN_AE!$C$2:$C$662;"MMMJJ")=A9) *(VERGLEICH(DATEN_AE!$A$2:$A$662;DATEN_AE!$A$2:$A$662;0)=ZEILE(DATEN_AE!$1:$661)))
Diese Formel zählt die Adressen, die in einem bestimmten Monat zum ersten Mal mit einem Bestelldatum erscheinen.
-
Daten sortieren: Achte darauf, dass die Daten nach dem Bestelldatum aufsteigend sortiert sind. Dies ist entscheidend für die korrekte Funktion der VERGLEICH-Funktion.
-
Ergebnis überprüfen: Überprüfe die Ergebnisse in der Tabelle, um sicherzustellen, dass die Neukunden korrekt gezählt werden.
Häufige Fehler und Lösungen
-
Fehler: #NV: Dieser Fehler tritt auf, wenn der Suchwert in der VERGLEICH-Funktion nicht gefunden wird. Stelle sicher, dass Du den 3. Parameter auf 0 setzt, um eine exakte Übereinstimmung zu verlangen.
-
Problem mit Sortierung: Wenn die Werte nach der Sortierung nicht stimmen, überprüfe, ob die Daten tatsächlich chronologisch angeordnet sind. Die Formel funktioniert nur korrekt, wenn die Daten aufsteigend sortiert sind.
Alternative Methoden
Wenn Du die Neukunden unabhängig von der Sortierung ermitteln möchtest, kannst Du die EINDEUTIGE-Funktion in Kombination mit FILTER verwenden (verfügbar ab Excel 365):
=ANZAHL(EINDEUTIGE(FILTER(DATEN_AE!A:A;DATEN_AE!C:C=Kriterium)))
Diese Methode ermöglicht es, die eindeutigen Neukunden zu zählen, ohne dass eine Sortierung erforderlich ist.
Praktische Beispiele
Angenommen, Du hast folgende Daten in Deinem Excel-Blatt:
| Kunde |
Bestelldatum |
Monat |
| Kunde 1 |
01.01.2023 |
Jan23 |
| Kunde 2 |
15.01.2023 |
Jan23 |
| Kunde 1 |
20.02.2023 |
Feb23 |
| Kunde 3 |
10.01.2023 |
Jan23 |
Um die Neukunden für Januar zu zählen, würdest Du die Formel wie oben beschrieben einsetzen. Das Ergebnis sollte 2 sein, da Kunde 1 und Kunde 2 im Januar zum ersten Mal Bestellungen aufgegeben haben.
Tipps für Profis
-
Verwende benannte Bereiche: Wenn Du häufig auf bestimmte Datenbereiche zugreifst, kann es hilfreich sein, diese Bereiche zu benennen. Das macht Deine Formeln übersichtlicher und einfacher zu verwalten.
-
Nutze die Funktion TEXT: Die TEXT-Funktion hilft Dir, Datumswerte in ein bestimmtes Format umzuwandeln, was bei der Auswertung von Neukunden äußerst nützlich ist.
FAQ: Häufige Fragen
1. Muss ich die Daten immer sortieren?
Ja, um korrekte Ergebnisse mit der VERGLEICH-Funktion zu erhalten, ist eine aufsteigende Sortierung der Daten erforderlich.
2. Was tun, wenn ich einen #NV-Fehler bekomme?
Überprüfe, ob der 3. Parameter der VERGLEICH-Funktion korrekt auf 0 gesetzt ist und dass der Suchwert tatsächlich in der Datenmatrix vorhanden ist.
3. Kann ich die Formel anpassen, um doppelte Einträge zu vermeiden?
Ja, Du kannst die EINDEUTIGE-Funktion nutzen, um sicherzustellen, dass nur neue Kunden gezählt werden, die zum ersten Mal in einem bestimmten Monat erscheinen.