Anand verfügt über ein Arbeitsblatt, das Verkaufsinformationen enthält, die aus Kundennamen (Spalte A) und Kaufdatum (Spalte B) bestehen. Er muss die Liste filtern, damit er Kunden sehen kann, die innerhalb eines bestimmten calendar Monats genau X-mal gekauft haben, wobei X ein Wert wie 1, 2 usw. ist. Er fragt sich, ob dies der Fall ist ist eine Möglichkeit, dies mithilfe der integrierten Filterung zu tun, oder ein anderer Ansatz ist besser.

Es gibt tatsächlich verschiedene Möglichkeiten, wie Sie dieses Problem angehen können.

Excel bietet eine Reihe verschiedener Tools, mit denen ein Ergebnis abgeleitet werden kann. Ich werde mich jedoch nur auf einige konzentrieren. Beide Ansätze erfordern Hilfsspalten für Ihre Filterung.

Der erste Ansatz verwendet nur eine einzige Hilfsspalte. Angenommen, Ihre Daten beginnen in Zeile 2 und belegen nicht mehr als 99 Zeilen. Geben Sie in Zelle C2 Folgendes ein:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,">="& DATE(YEAR(B2),MONTH(B2),1),$B$2:$B$100,"<"& DATE(YEAR(B2),MONTH(B2)+1,1))

Dies ist eine einzelne Formel; Es wird gezählt, wie viele Käufe die Person in Spalte A innerhalb des durch das Datum in Spalte B angegebenen Monats getätigt hat. Kopieren Sie die Formel so viele Zeilen nach unten, wie für Ihre Daten erforderlich sind. Zeigen Sie bei ausgewählter Zelle in Ihren Daten die Registerkarte Daten des Menübands an und klicken Sie in der Gruppe Sortieren und Filtern auf das Filterwerkzeug. Excel zeigt die Filtersteuerelemente oben in jeder Spalte Ihrer Daten an.

Klicken Sie auf das Steuerelement oben in der Spalte Datum, um die Filterpalette anzuzeigen, und wählen Sie dann den Monat aus, auf den Sie abzielen möchten. (Deaktivieren Sie im Bereich „Jahre“ am unteren Rand der Palette das Kontrollkästchen „Alle auswählen“ und wählen Sie dann ein Jahr und schließlich einen Monat aus.) Klicken Sie auf das Steuerelement oben in der Hilfsspalte, um die Anzahl der gewünschten Verkäufe in der gefilterten Spalte auszuwählen aufführen. (Siehe Abbildung 1.)

image

Abbildung 1. Ihre gefilterten Daten.

Beachten Sie, dass das Ergebnis mehrere Vorkommen jedes Namens für den Zielmonat anzeigt. Wenn Sie also diejenigen anzeigen, die in einem bestimmten Monat dreimal gekauft haben, werden im Zielmonat drei Zeilen für einen bestimmten Namen angezeigt.

Dies ist in Ordnung für eine relativ niedrige X-Rate für einen Monat. Wenn Sie jedoch diejenigen sehen möchten, die 50 Mal im Monat gekauft haben, wird dies schnell unhandlich. Stattdessen benötigen Sie eine Möglichkeit, sich auf eine einzelne Instanz eines bestimmten Namens in der gefilterten Liste zu beschränken.

Dies bringt uns zu einer Lösung, die zwei Hilfsspalten erfordert, die ich Count und Tally nennen werde. Wenn Ihre erste Zelle in der Spalte Anzahl C4 ist, geben Sie dies in diese Zelle ein:

=IF(EOMONTH(B4,0)<>EOMONTH($B$1,0),0,IF(A4=A3,C3+1,1))

Kopieren Sie diese Formel so weit wie nötig für Ihre Daten. Es wird der Wert 0 zurückgegeben, es sei denn, Monat und Jahr stimmen mit Monat und Jahr in Zelle B1 überein. (Das Datum in Zelle B1 sollte innerhalb Ihres Zielmonats liegen. Es kann ein beliebiges Datum innerhalb dieses Monats sein.) Wenn Monat und Jahr übereinstimmen, erhöht sich die Anzahl für jeden Verkauf für denselben Kunden innerhalb dieses Monats.

Wenn die erste Zelle der Tally-Spalte Zelle D4 ist, platzieren Sie diese Formel in dieser Zelle:

=IF(C4>=C5,C4,0)

Kopieren Sie diese Formel auch in die Spalte. Die Formel behält für jeden Kunden nur den höchsten Wert aus der Spalte Anzahl bei und setzt alle anderen Werte auf 0.

Zeigen Sie nun mit einer beliebigen Zelle in Ihren Daten die Registerkarte Daten des Menübands an und klicken Sie auf das Filterwerkzeug. Excel zeigt die Filtersteuerelemente oben in jeder Spalte Ihrer Daten an. (Siehe Abbildung 2.)

image

Abbildung 2. Ein Beispieldatensatz.

Sie sind jetzt bereit, die gewünschten Informationen zu erhalten. Sortieren Sie die Daten zuerst nach Spalte A (Spalte Name) und dann nach Spalte B (Spalte Datum). Sie können dann das Dropdown-Filtersteuerelement oben in Spalte D (Spalte Tally) verwenden, um auszuwählen, wie viele Verkäufe Sie für den gewünschten Monat wünschen (der in Zelle B1 angegeben ist). Wenn Sie angeben, dass nur der Wert 1 angezeigt werden soll, haben Sie die Namen aller Personen, die nur einmal im Monat gekauft haben. Wenn Sie 2 auswählen, sind die angezeigten Namen diejenigen, die zweimal gekauft haben usw.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9692) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.