Du bist hier::--Excel Formeln – Einträge in gefilterter Liste ohne Duplikate zählen

Excel Formeln – Einträge in gefilterter Liste ohne Duplikate zählen

[imgr=logo-470.png]logo-80.png?nocache=1309248176054[/imgr]In diesem Artikel zu Microsofts Excel möchte ich ihnen zeigen, wie man die Anzahl an gefilterten Einträgen ausgeben kann. Dabei wird aber auch auf Duplikate geachtet und der Eintrag wird nur ein Mal gezählt.

Hier habe ich ihnen schon gezeigt, wie man die Anzahl an Zeilen in einer gefilterten Liste zählen kann, wenn eine Bedingung auftritt. Dabei wurde aber nicht  beachtet, dass auch Duplikate vorkommen können. In diesem Fall wurden sie einfach doppelt oder dreifach gezählt, also ja nach auftreten. In diesem Artikel möchte ich ihnen aber nun zeigen, wie Sie es schaffen, dass auch darauf geachtet wird.

Beginn:

Zu aller erst müssen Sie in das Excel Datenblatt ihre Datensätze eintragen. Dabei besteht ein Datensatz jeweils aus einem Eintrag in der Spalte A und einem Eintrag in der Spalte B. Tragen Sie die Datensätze aber nicht sofort in die Zellen A1 und B1 ein, da diese dafür benutzt werden, die Bezeichnung der Werte darzustellen. Ich habe hierfür nun in die Zelle A1 „Spalte A“ geschrieben, in der Zelle B1 steht „Spalte B“. Sie wählen in ihrem Fall nun natürlich passendere Begriffe. Ihre Datensätze können Sie nun ab Zeile 2 eintragen. Ich habe hier nun sechs Datensätze eingetragen, von denen fünf in der Spalte A den Wert „x“ haben. Das ist später wichtig, da nach diesem Wert gefiltert wird. Dazu aber gleich mehr. Haben Sie ihre Datensätze nun alle eingetragen und ihre Tabelle sieht ungefähr so aus, wie meinem im Screenshot unten, dann haben Sie alles richtig gemacht und Sie können nun zum nächten Schritt weitergehen, in dem erklärt wird, wie man nun Datensätze herausfiltert.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-beginn-470.png?nocache=1308915766348

Filtern:

Nun werde ich ihnen Schritt für Schritt zeigen, wie man die angegeben Datensätze filtern kann. Dabei wird, wie Sie gleich sehen werden, so gefiltert, dass nur Einträge angezeigt werden, die in Spalte A den Wert „x“ haben.

Schritt 1:

Bevor Sie oben etwas einstellen, müssen Sie zuerst auf die Zelle A1 mit dem Inhalt „Spalte A“ klicken. Haben Sie das gemacht, so orientieren Sie sich nun im oberen Bereich des Fensters. Hier sehen Sie in dem Bereich über den Datenblatt ganz rechts den Punkt „Bearbeiten“. Dieser besteht nochmals aus drei weiteren Unterpunkten. Für Sie relevant ist nun der Punkt „Sortieren und Filtern“. Klicken Sie diesen jetzt mit der linken Maustaste an, woraufhin sich umgehend ein Dropdown Menü öffnen wird.

02-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-sortieren-und-filtern-470.png?nocache=1308915849694

Schritt 2:

Diese Dropdown Menü besteht aus sechs Einträgen. Von diesen sechs Einträgen sind die ersten drei nur für die Sortierfunktion belegt. Da Sie aber nicht sortieren möchten, sondern die Einträge filtern wollen, kommen die anderen drei in Frage. Von denen sind aber auch zwei ausgegraut, wodurch nur noch der Eintrag „Filtern“ übrigbleibt. Klicken Sie diesen nun mit der linken Maustaste an.

03-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-filtern-470.png?nocache=1308915881206

Schritt 3:

Die Änderung sehen Sie nun am Datenblatt. Vorher standen in den Zellen A1 und B1 einfach nur so die Wörter „Spalte A“ bzw. „Spalte B“. Jetzt sieht das Ganze ein wenig anders aus. Rechts von diesen Begriffen ist nun jeweils ein Pfeil erkennbar. Das bedeutet, dass man hier nun filtern kann. Um nun ins Menü zu kommen, in dem eingegeben werden kann, nach was gefiltert werden soll, müssen Sie einen Pfeil anklicken.

04-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-pfeil-470.png?nocache=1308915904447

Schritt 4:

 In unserem Fall ist das der Pfeil bei „Spalte A“, da wir anschließend nur noch Datensätze sehen wollen, die in Spalte A den Wert „x“ haben. Es wird also nach „x“ in Spalte A gefiltert. Um das einzugeben, klicken Sie aber erst auf den Pfeil. Nun öffnet sich wieder ein Dropdown Menü. Fahren Sie hier mit ihrer Maus über den Begriff „Textfilter…“ in der Mitte des Menüs. Anschießend fährt zur rechten Seite ein weiteres Menü aus. Hier klicken Sie nun „Ist gleich…“ an, woraufhin sich sofort ein neues Fenster öffnet.

05-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-ist-gleich-470.png?nocache=1308915925263

Schritt 5:

Dieses neue Fenster trägt den Titel „Benutzerdefinierter AutoFilter“. Es ist von Beginn an schon auf den Punkt „entspricht“ eingestellt, was für unsere Bedürfnisse auch genau der richtige ist. In der Auswahlzeile daneben können Sie nun das Zeichen oder das Wort eingeben, nach dem gefiltert werden soll. Um an eines zu kommen, welches in der Spalte A auf jeden Fall schon einmal vorgekommen ist, müssen Sie auf den Pfeil ganz rechts klicken. Daraufhin öffnet sich eine Liste der Zeichen oder Zeichenfolgen, die in der Spalte A aufgetreten sind. Da ich möchte, dass nur Einträge angezeigt werden, die in Spalte A den Wert „x“ haben, klicke ich hier dementsprechend auf „x“. Haben Sie ihren Wert auch ausgewählt, so schließt sich das Auswahlmenü sofort wieder.

06-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-buchstaben-470.png?nocache=1308915944950

Schritt 6:

Haben Sie den Wert angeklickt, ist er nun in der Zeile erschienen. Vergewissern Sie sich noch einmal, dass Sie wirklich nach diesem Wert filtern möchten und drücken Sie anschließend unten auf „OK“, woraufhin sich das Fenster umgehend schließen wird.

07-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-ok-470.png?nocache=1308915966454

Schritt 7:

Nun sehen Sie in ihrem Datenblatt die gefilterten Datensätze. In meinem Beispiel werden jetzt nur noch die Datensätze angezeigt, die in der Spalte A ein „x“ haben. Der Datensatz in der Zeile 4 und einem „y“ in der Spalte A wurde korrekterweise ausgeblendet. Aber auch der Pfeil neben „Spalte A“ hat sich verändert. Hier sehen Sie nun einen Filter, was logischerweise bedeutet, dass in dieser Spalte etwas gefiltert wurde. Haben Sie das auch so, dann hat ihre Filterung erfolgreich funktioniert und Sie können im nächsten Schritt weiterlesen, wie man nun die Anzahl an den verschiedenen Einträgen bekommen kann.

08-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-fertig-470.png?nocache=1308915987967

Anzahl an verschiedenen Datensätzen ohne Duplikate anzeigen:

Nachdem die Liste nun gefiltert ist, können Sie nun die Anzahl an den Datensätzen ausgeben, die nur einmal vorkommen. Dazu benötigen Sie selbstverständlich eine Formel. Ich empfehle ihnen dazu die Formel nicht in eine Zelle der Spalte A oder der Spalte B zu schreiben, da Sie sonst die Zellbezüge umändern müssten. Ich habe hierfür die Zelle D1 gewählt. Tragen Sie nun in ihre gewünschte Zelle diese Formel ein:

=SUMME(N(HÄUFIGKEIT(ZEILE(1:117);TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:100)))*VERGLEICH(B2:B100&"";B2:B100&"";))>0))-1  

Nachdem Sie die Formel angegeben haben, wird ihnen auch umgehend das korrekte Ergebnis angezeigt. Das ist aber nur der Fall, wenn Sie die gleichen Zellbezüge wie ich gewählt haben. Haben Sie andere genommen, so ändern Sie „B“, „B2:B100“ und nochmals „B2:B100“ einfach entsprechend um, dass die Zellbezüge eingegeben werden, die auf die Spalte verweisen, in der die nicht gefilterten und somit verschiedenen Werte stehen. In meinem Fall war hier der Ausgabewert korrekterweise die Zahl „3“, da mit „x“ und „a“, „x“ und „b“ und „x“ und „c“ drei verschiedene Wertepaare auftreten. Da „x“ und „a“ aber zwei Mal auftritt, wird es trotzdem korrekterweise nur ein Mal gezählt. Diese Formel bietet sich übrigens perfekt dafür an, wenn die gefilterten Werte in der Spalte A manchmal wechseln.

09-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-formel-470.png?nocache=1308916024214

Anzahl an verschiedenen Datensätzen ohne Duplikate anzeigen (bestimmter Wert):

Mit der Formel oben haben Sie gelernt, wie man die Anzahl an verschiedenen Datensätzen ohne Duplikate ausgibt. Diese Formel war gültig für wechselnde Werte in der Spalte A. Wissen Sie aber von Beginn an schon, nach welchem Wert in Spalte A gefiltert wird, so können Sie auch eine allgemeinere Formel benutzen, die auch kürzer ist. Löschen Sie dazu erst einmal wieder den Inhalt von D1 und geben Sie nun folgendes ein:

=RUNDEN(SUMME(WENN((B1:B100="")+(A1:A100<>"x");0;1/ZÄHLENWENN(B1:B100;B1:B100)));0)

Im Gegensatz zu der Formel von vorhin, ist diese Formel eine Matrixformel. Das bedeutet, dass Sie noch nicht das korrekte Ergebnis angezeigt bekommen, wenn Sie die Formel eingeben. Um daran zu kommen müssen Sie noch die drei Schritte befolgen, die bei einer Matrixformel immer von Nöten sind. Klicken Sie zuerst noch einmal in die Zelle mit der Formel, in meinem Fall also D1. Haben Sie das gemacht, so klicken Sie nun oben in die Bearbeitungszeile der Formel. Abschließend drücken Sie auf ihrer Tastatur noch gleichzeitig die Tasten STRG+SHIFT+Enter. Dadurch erscheint in der Zelle nun der richtige Ausgabewert. Neben der korrekten Ausgabe in der Zelle hat sich aber auch die Formel verändert. Sie sieht jetzt, wie es für eine Matrixformel typisch ist, so aus:

{=RUNDEN(SUMME(WENN((B1:B100="")+(A1:A100<>"x");0;1/ZÄHLENWENN(B1:B100;B1:B100)));0)}

Haben Sie andere Zellbezüge in der Tabelle als ich gewählt, so ändern Sie diese einfach in der Formel entsprechend ab. Der Zellbezug „A1:A100“ steht dabei für die Spalte mit den gefilterten also nun den gleichen Werten und „B1:B100“ steht für die verschiedenen Werte. Das „x“ in der Formel ist der Wert, nach dem die Spalte gefiltert wurde. Ändern Sie diesen also einfach so ab, wie Sie zu Beginn des Artikels gefiltert haben. Haben Sie alles richtig eingestellt, so wird diese Formel genauso wie die andere funktionieren, jedoch müssen Sie auch die Formel nochmals verändern, wenn nach etwas anderem gefiltert werden soll.

10-excel-formeln-eintraege-in-gefilterter-liste-ohne-duplikate-anzeigen-bekannt-470.png?nocache=1308916051911

Nun haben Sie zwei Wege kennengelernt, wie man die Anzahl an Datensätzen in einer Tabelle ohne Duplikate ausgeben kann. Filtern Sie zu Beginn die Spalten, so müssen Sie darauf achten, dass Sie sie so filtern, dass in der Spalte A nur gleiche Werte auftreten, und dass Sie also beispielsweise nicht nach den Top 10 filtern (nur bei Zahlen).

Von |2018-07-25T14:21:23+00:00Juli 1st, 2011|Kategorien: Tabellenkalkulation|0 Kommentare

Über den Autor:

Hinterlassen Sie einen Kommentar