Du bist hier::--Excel Formeln – Zellen in einer gefilterten Liste mit Bedingung zählen

Excel Formeln – Zellen in einer gefilterten Liste mit Bedingung zählen

[imgr=logo-470.png]logo-80.png?nocache=1309247896774[/imgr]In diesem Artikel werde ich ihnen zeigen, wie Sie in Excel die Anzahl an Zellen zählen können, die einen bestimmten Wert enthalten. Dabei sind aber auch noch einige Zeilen ausgeblendet, was die Arbeit erschwert.

Wenn Sie hören, dass die Aufgabe ist Zellen zu zählen, die einen bestimmten Wert haben, so werde Sie als erfahrener Excel Benutzer sicherlich an die Funktion ZÄHLENWENN denken. Eigentlich ist das auch gar keine schlechte Idee. Jedoch ist bei der Aufgabe noch vorgegeben, dass die Tabelle eine gefilterte Liste ist. Das bedeutet also, dass in der Tabelle nicht alle Werte angezeigt werden. So haben Sie beispielsweise 20 Einträge gemacht, durch die Filterung werden aber nur 5 angezeigt. Bei diesem Fall hilft ZÄHLENWENN nicht weiter, da es nicht unterscheiden kann, ob ein Eintrag sichtbar oder unsichtbar ist. Wie Sie dies aber trotzdem hinbekommen, erfahren Sie im folgenden Artikel.

Beginn:

Zu aller erst müssen Sie in die Tabelle ein paar Wertepaare eintragen. Diese bestehen aus einem Datum, einem Betrag und einer Markierung. Ich habe in meinem Beispiel nun 16 Wertepaare eingetragen, die alle unterschiedliche Zahlen bei Betrag und alle ein unterschiedliches Datum haben. Die Markierung ist der Wert, der später von den übriggebliebenen Wertepaaren gezählt werden soll. Dabei habe ich mit „X“, „Y“ und „Z“ drei verschiedene Bezeichnungen gewählt. „X“ wird später ausgelesen. Die Werte können zum Beispiel den Verlauf von vielen Aktien zeigen, die dann über die Markierung unterschieden werden. Haben Sie hier alle wichtigen Werte eingetragen, so lesen Sie nun im nächsten Abschnitt, wie Sie es schaffen, dass manche Zeilen ausgeblendet werden.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-werte-470.png?nocache=1308837901173

Wertepaare ausfiltern:

Schritt 1:

Nun werde ich damit beginnen, ihnen zu zeigen, wie Sie in der Tabelle nun einige Werte ausblenden können. Klicken Sie dazu in der Tabelle erst einmal die Zelle „Betrag“ an. Danach orientieren Sie sich nicht mehr am Danteblatt, sondern in der Zeile darüber. Hier fahren Sie mit ihrer Maus ganz nach rechts zu dem Punkt „Bearbeiten“. Dieser Punkt ist wiederrum dreigeteilt. Klicken Sie hier jetzt mit der linken Maustaste auf „Sortieren und Filtern“ woraufhin sich umgehend ein Dropdown Menü öffnen wird.

02-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-sortieren-und-filtern-470.png?nocache=1308837913165

Schritt 2:

Das Dropdown Menü besteht aus sechs Einträgen, von denen die letzen zwei ausgegraut sind. Da sich die ersten drei Einträge auf das Sortieren der Zellen beziehen, müssen Sie nun logischerweise den vierten Eintrag anklicken.

03-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-filtern-470.png?nocache=1308837924239

Schritt 3:

Nachdem Sie den Eintrag aus dem Dropdown Menü angeklickt haben, hat sich nun in der Tabelle im Datenblatt etwas verändert. Hier sind jetzt in der Zelle von „Datum“, „Betrag“ und „Markierung“ jeweils Pfeile erschienen, die angeben, dass hier nun etwas gefiltert werden kann. Jetzt haben Sie zwei Möglichkeiten. Entweder Sie filtern die Einträge nach dem Datum – zum Beispiel die neuesten 5 Einträge oder die erste 10 – oder Sie filtern Sie nach dem Betrag in Spalte B. Beides steht ihnen hier zur Auswahl. Lediglich nach der Markierung sollen Sie nicht filtern, da diese später ausgelesen wird. Je nachdem nach was Sie filtern möchten, klicken Sie nun auf den Pfeil in der entsprechenden Zelle, woraufhin sich auch hier wieder ein Dropdown Menü öffnen wird.

04-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-pfeil-470.png?nocache=1308837936397

Schritt 4:

Dieses Dropdown Menü hat wieder sechs Punkte. Da sich die ersten drei wieder auf das Sortieren beziehen, und die anderen zwei Punkte ausgegraut sind, bleibt ihnen nur noch der vierte Punkt übrig. Fahren Sie mit der Maus über diesen. Haben Sie das gemacht, so klappt sich an der rechten Seite ein weiteres Dropdown Menü aus. Hier können Sie jetzt einen beliebigen Filter auswählen. So bietet es sich zum Beispiel an, wenn Sie am Anfang in der Tabelle mehrere Zellen mit dem gleichen Wert ausgefüllt haben, dass Sie hier den Punkt „Ist gleich…“ auswählen. Dies würde – angewendet auf das Aktienbeispiel – dann den Namen der Aktie und das Datum ausgeben, wann sie diesen Wert erreicht hat. In meinem Beispiel war das aber nicht der Fall und ich wollte, dass die Einträge mit den höchsten Werten angezeigt werden, also habe ich hier „Top 10…“ ausgewählt. Sie können hier wie gesagt wählen, was Sie möchten.

05-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-top-10-470.png?nocache=1308837951246

Schritt 5:

In meinem Fall hat sich nun ein neues Fenster geöffnet. Es trägt den Titel „Top-10-AutoFilter“. Hier können Sie nun einstellen, wie viele von den Wertepaaren Sie anzeigen möchten die die höchsten Werte besitzen. Diese Zahl stellen Sie über das mittlere Feld im Fenster ein. Da mir 10 Werte zu viele waren, habe ich hier die Anzahl auf 5 umgestellt. Anschließend müssen Sie noch auf „OK“ klicken, um ihre Eingaben zu bestätigen.

06-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-5-ok-470.png?nocache=1308837964397

Schritt 6:

Nun haben sich in der Tabelle große Änderungen ergeben. Wie gerade eben eingestellt, sind nun nur noch die Wertepaare zu sehen, die die fünf größten Einträge unter dem Punkt „Betrag“ haben. Desweiteren ist bei der Zelle B1 nun neben „Betrag“ nicht mehr der Pfeil zu sehen, wie das in den anderen Zelle noch der Fall ist, sondern hier ist jetzt ein Filter zu sehen, was einfach bedeutet, dass die Wertepaare nach dem Betrag gefiltert sind. Im nächsten Abschnitt werden Sie nun erfahren, wie man jetzt die Anzahl an den angezeigten Wertepaaren bekommt, die eine bestimmte Markierung besitzen und wie man den Median anzeigen kann.

07-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-gefiltert-470.png?nocache=1308837987165

Anzahl an Zellen mit bestimmter Markierung anzeigen:

Nachdem nun einige Zeilen herausgefiltert wurden, werde ich ihnen nun zeigen, wie man die Anzahl an Zellen zählen kann, in denen ein bestimmter Wert in der Spalte „Markierung“ steht. Geben Sie dazu in die Zelle E1 nun die folgende Formel ein:

=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("C"&ZEILE(2:999)))*(C2:C999="X"))

Nachdem Sie die Formel eingegeben haben, wird ihnen auch umgehend das korrekte Ergebnis angezeigt, sofern Sie die gleichen Zellbezüge wie ich gewählt haben und in der Spalte C auch mal der Wert „X“ vorkommt. Haben Sie einen anderen Zellbezug für die Markierungsspalte als C gewählt, so ändern Sie hier bitte die entsprechenden Einträge ab. Möchten Sie nicht nach der Bezeichnung „X“ sondern einem treffenderen Wort suchen, wie zum Beispiel den Namen einer Aktie, so geben Sie diesen Wert in der Formel anstelle von „X“ ein. In meinem Beispiel wird nun in der Zelle also korrekterweise die Zahl „2“ ausgegeben, da von den Einträgen nur noch zwei übrig geblieben sind, die als Markierung den Wert „X“ tragen. Diese beiden Wertepaar befinden sich in den Zeilen 7 und 8.

08-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-anzahl-x-470.png?nocache=1308838011693

Median des Betrags anzeigen:

Nun wissen Sie, wie man die Anzahl an Zellen ausgeben kann, in denen der gesuchte Wert in der Markierungszeile auftritt. Jetzt werde ich ihnen zeigen, wie Sie von den übrig gebliebenen Werten in der Spalte Betrag den Median anzeigen können. Der Median ist nicht zu verwechseln mit dem Mittelwert. Stellen Sie sich es so vor, dass Sie eine Reihe an Zahlen haben. Bei dieser Reihe ist nun der Wert, der in der Mitte steht der Median. Haben Sie zum Beispiel die Zahlen 1, 2, 3, 4, 5, so ist der Median die Zahl 3. Um den Median in unserem Fall auszurechnen müssen Sie zuerst die Zelle E1 löschen. Hier tragen Sie nun folgendes ein:

=MEDIAN(WENN(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(1:999)))>0;B1:B999))

Wenn Sie diese Funktion eingeben, wird ihnen zuerst der Median von allen Zahlen der Spalte Betrag angezeigt, auch von den ausgeblendeten. Das liegt daran, dass die Formel eine Matrixformel ist. Daher müssen Sie nun folgende drei Schritte befolgen, damit die Formel korrekt ausgeführt wird. Klicken Sie zuerst die Zelle E1 erneut an. Haben Sie das gemacht, so klicken Sie nun in die Bearbeitungszeile der Formel. Abschließend drücken Sie auf ihrer Tastatur die Tasten STRG+SHIFT+Enter gleichzeitig. Nun erscheint in der Zelle der richtige Ausgabewert. In meinem Fall war das die Zahl 800, da meine Zahlenreihe aus 700, 705, 800, 850 und 900 bestand. Neben der richtigen Ausgabe in der Zelle hat sich auch die Formel selbst verändert. Sie sieht nun matrixtypisch so aus:

{=MEDIAN(WENN(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(1:999)))>0;B1:B999))}

Haben Sie einen anderen Zellbereich als ich gewählt, so ändern Sie die Formel wieder entsprechend ab.

09-excel-formeln-zelle-in-einer-gefilterte-liste-mit-bedingung-zaehlen-median-470.png?nocache=1308838038085

Nun wissen Sie, wie man mithilfe von zwei Formeln die Anzahl an Zellen in einem Bereich mit ausgeblendeten Zeilen anzeigen kann. Desweiteren können Sie auch dann den Median ausgeben. Auch wissen Sie jetzt, wie man es schafft in Excel ganz einfach Zeilen auszublenden, indem man einen Filter benutzt.

Von |2018-07-25T14:22:32+00:00Juni 29th, 2011|Kategorien: Tabellenkalkulation|0 Kommentare

Über den Autor:

Hinterlassen Sie einen Kommentar