17.2 Komplexe Abfragen mit Spezialfiltern 

Eine Tabelle nach einfachen Kriterien mit Hilfe der Funktion Filtern auf die Daten zu reduzieren, die für die weitere Verarbeitung benötigt werden, ist kein Umstand. Ein ganz großer Teil der Abfragen an eine Tabelle lässt sich auf diese Weise mit ein paar Mausklicks erledigen. Wenn dies nicht ausreicht, und das ist schon der Fall, wenn Sie wie in unserem Beispiel den Bestand mit dem Mindestbestand vergleichen wollen, muss der Dialog Spezialfilter herangezogen werden. Er wird aus der Gruppe Daten/Sortieren und Filtern mit Erweitert aufgerufen.

17.2.1 Tabelle und Kriterienbereich 

Dazu wird ein spezieller Zellbereich für die Festlegung von Abfragekriterien benötigt. Dieser Kriterienbereich muss mindestens zwei Zeilen umfassen. In der ersten Zeile befinden sich Spaltennamen. Die zweite Zeile oder weitere Zeilen darunter sind für die Eingabe von Kriterien reserviert.

Auch bei dieser Funktion muss normalerweise der Bereich der Tabelle nicht manuell ausgewählt oder benannt werden. Excel markiert den entsprechenden Bereich nach den oben beschriebenen Regeln, wenn der Zellzeiger irgendwo in die Tabelle gestellt worden ist. Notwendig ist aber der angesprochene Tabellenbereich, in dem die Kriterien abgelegt werden, die über die Auswahl der Datensätze entscheiden. Die Spaltennamen müssen dabei buchstabengetreu mit den Spaltennamen der Tabelle übereinstimmen, mit Ausnahme zusätzlicher Namen für berechnete Kriterien, von denen noch die Rede sein wird. Das erreichen Sie am einfachsten, indem Sie die Namenszeile aus der Tabelle in die erste Zeile des Kriterienbereichs kopieren.

Es ist dabei nicht notwendig, dass alle Feldnamen im Kriterienbereich auftauchen, Sie können einzelne Feldnamen weglassen. Für kombinierte Kriterien kann ein Spaltenname sogar mehrfach benutzt werden. Achten Sie darauf, den Kriterienbereich im Tabellenblatt so zu platzieren, dass er nicht unbeabsichtigt von Änderungen wie Einfügen oder Löschen von Spalten oder Zeilen zerstört werden kann.

Wird der Kriterienbereich innerhalb des Tabellenblatts angelegt, in dem sich die Tabelle befindet, ist es am sichersten, dafür neue Zeilen oberhalb der Tabelle einzufügen. Die Tabelle kann dann ungehindert nach unten wachsen, ohne dass Gefahr besteht, mit dem Kriterienbereich zu kollidieren. Damit auch bei Änderungen der Spaltenanzahl in der Tabelle nichts passieren kann, ist es sinnvoll, den Kriterienbereich so weit nach rechts zu rücken, dass keine gemeinsamen Spalten mit der Tabelle vorkommen. Die noch bessere Lösung ist, den Kriterienbereich gleich auf ein eigenes Blatt zu legen.

Wenn Sie den Kriterienbereich im Dialogfeld Spezialfilter festlegen, vergibt Excel automatisch den Namen »Suchkriterien« für diesen Bereich. Über oder das Namenfeld kann der Bereich dann schnell angesprungen werden. Sie können natürlich auch einen anderen Namen vergeben.

Kriterienbereich über dem Listenbereich

17.2.2 Datenextrakte im Ausgabebereich 

Wenn Sie die Funktion Spezialfilter verwenden, haben Sie die Möglichkeit, die gefilterten Daten in einen anderen Bereich des Tabellenblatts zu kopieren, anstatt sie in der Tabelle selbst zu filtern. Dies ist immer dann sinnvoll, wenn Sie über längere Zeit mit einem solchen Filterextrakt weiterarbeiten wollen, unabhängig von der originalen Tabelle selbst.

Leider ist es nicht möglich, den Extrakt gleich in ein anderes Blatt der Arbeitsmappe zu filtern oder gar in eine andere Arbeitsmappe. Sie müssen, um so etwas zu erreichen, umgekehrt verfahren. Sie aktivieren das Blatt für den Ausgabebereich und rufen von dort den Befehl Daten/Sortieren und Filtern/Erweitert auf. Sie müssen dann aber den Tabellenbereich in dem anderen Blatt ausdrücklich markieren, ebenso den Kriterienbereich.

Festlegen des Ausgabebereichs 

Wenn Sie mit einem Ausgabebereich arbeiten wollen, sind eventuell einige Vorbereitungen nötig, bevor Sie den Dialog Spezialfilter aufrufen können. Sie haben drei Möglichkeiten:

• Sie geben nur die linke obere Eckzelle des Ausgabebereichs an. Excel kopiert alle Spalten samt Beschriftung in den Ausgabebereich.

• Sie geben eine Zeile mit Spaltennamen als Ausgabebereich an.

• Sie markieren einen Bereich für die gesamte Ausgabe, der als erste Zeile Spaltennamen enthält.

Werden Spaltennamen für den Ausgabebereich angegeben, ist es wieder am einfachsten, die Spaltennamen oder auch die ganze Spaltennamenszeile aus der Tabelle in den Ausgabebereich zu kopieren. Nehmen Sie dafür möglichst einen freien Bereich unterhalb der Tabelle. Wenn Sie immer mit demselben Ausgabebereich arbeiten, sollten Sie einen Namen dafür vergeben. Sie können aber auch einzelne Spaltennamen weglassen oder die Reihenfolge der Spaltennamen ändern.

Beispiel für eine Filterung mit Ausgabebereich

17.2.3 Bestandsprüfung mit Spezialfilter 

Die Arbeitsweise des Spezialfilters soll zunächst an einem Problem erprobt werden, das schon angesprochen wurde. Das Programm soll die Datensätze der Weine herausfiltern, bei denen der Lagerbestand den Mindestbestand unterschreitet. In dem Fall ist ja normalerweise eine Nachbestellung notwendig.

2 Sind die notwendigen Daten im Kriterienbereich eingetragen, sollten Sie nicht vergessen, den Zellzeiger zunächst wieder in die Tabelle zu rücken. Nun kann der Befehl Daten/Sortieren und Filtern/Erweitert aufgerufen werden.

4 Das Feld Listenbereich wird normalerweise von Excel automatisch ausgefüllt, wenn Sie den Zellzeiger korrekt innerhalb der Tabelle platziert haben. Stattdessen können Sie hier aber auch den Bereich ausdrücklich markieren oder einen Bereichsnamen mit einfügen.

5 Entscheidend ist der korrekte Bezug auf den Kriterienbereich. Markieren Sie den Bereich oder fügen Sie den Bezug oder Namen ein, falls ein Name vergeben worden ist.

6 Das Kontrollfeld Keine Duplikate kann verwendet werden, um Kopien von Datensätzen auszublenden oder aus dem Extrakt auszuschließen. Duplikate können zustande kommen, wenn Datensätze irrtümlich zweimal eingegeben worden sind. Manchmal kommt es aber auch vor, dass gleiche Datensätze tatsächlich mehrfach vorkommen, weil ein Unterscheidungskriterium fehlt; denken Sie nur an das berühmte Meier-Müller-Schmidt-Problem.

7 Wenn Sie das Dialogfeld bestätigen, filtert Excel alle Datensätze aus der gesamten Tabelle heraus, die die im Kriterienbereich abgelegten Kriterien erfüllen. Die nächste Abbildung zeigt die Datensätze der Weine an, die nachbestellt werden müssen.

Weine mit zu geringem Bestand

17.2.4 Gefilterte Daten an eine andere Stelle kopieren 

Sollen die gefilterten Daten in einen anderen Tabellenbereich übertragen werden, wählen Sie im Dialogfeld die Option An eine andere Stelle kopieren. Dann kann unter Kopieren nach der entsprechende Bezug oder Name eingetragen werden.

Dialogfeld mit Angabe des Ausgabebereichs

Werden die gefilterten Daten an eine andere Stelle der Arbeitsmappe kopiert, entstehen Datenextrakte. Die so gewonnene kleinere Tabelle kann dann wieder selbst Gegenstand von Abfragen sein.

Wird nur eine Zelle oder eine Zeile mit Spaltennamen als Ausgabebereich markiert, werden alle Datensätze dorthin kopiert, die das Kriterium erfüllen. Daten, die unterhalb der Spaltennamenzeile des Ausgabebereichs stehen – etwa die Daten eines vorhergehenden Extrakts –, werden vorher gelöscht.

Die Bereichsangaben im Dialogfeld Spezialfilter werden von Excel übrigens immer so lange in der Arbeitsmappe beibehalten, bis sie wieder geändert werden. Wenn Sie den Befehl also mehrfach verwenden, muss nur dann etwas geändert werden, wenn sich die Bereiche im Tabellenblatt geändert haben.

17.2.5 Welche Auswahlkriterien sind möglich? 

Mit Hilfe eines Auswahlkriteriums legen Sie fest, welche Daten Excel aus einer Tabelle ausfiltern soll. Das Kriterium wird immer positiv formuliert, und zwar als die Bedingung, die ein Datensatz erfüllen muss, wenn er den Filter passieren soll; das Kriterium »Farbe = rot« lässt alle roten Weine durch den Filter durch, alle nicht-roten Weine bleiben im Filter hängen.

Die einfachste Form eines Kriteriums im Kriterienbereich ist eines für eine Spalte. Entweder wird unter dem Spaltennamen ein konstanter Wert als Kriterium angegeben oder die Kombination von einem der logischen Operatoren mit einem konstanten Wert. Das entspricht der Schreibweise, die Sie schon im Dialogfeld der benutzerdefinierten Filter kennen gelernt haben.

17.2.6 Kombinierte Kriterien 

Wenn Sie nicht nur in einem, sondern in mehreren Feldern der ersten Zeile des Kriterienbereichs eine Eintragung vornehmen, behandelt das Programm dies als ein kombiniertes Kriterium. Gesucht wird in einem solchen Fall ein Datensatz, in dem es bei allen benutzten Feldern einen genau entsprechenden Inhalt gibt.

Wenn Sie also im Kriterienbereich unter Anbaugebiet »Mosel« eintragen und unter Farbe »rot«, sucht das Programm die Rotweine von der Mosel, also die Datensätze, die beide Kriterien gleichzeitig erfüllen. Das entspricht einer logischen UND-Funktion:

=UND(Anbaugebiet="Mosel";Farbe="rot")

Achten Sie vor der Eingabe eines neuen Kriteriums immer darauf, dass nicht Eintragungen von alten Abfragen stehen geblieben sind, die dann als ungewolltes Zusatzkriterium wirken.

Mehrere Kriterien gleichzeitig

Müssen zwei Bedingungen bei derselben Spalte erfüllt sein, können Sie Spaltennamen auch mehrfach verwenden.

sucht z. B. die Datensätze, in denen der Bestand zwischen den beiden angegebenen Werten liegt.

17.2.7 Alternative Kriterien 

Kriterien können auch alternativ verwendet werden. In diesem Fall werden die Kriterien untereinander in eine Spalte des Kriterienbereichs geschrieben.

Alternative Kriterien

Zum Beispiel können Sie unter dem Spaltennamen Farbe im Kriterienbereich »rot« und »rosé« untereinander eintragen. Dann werden alle Weine gesucht, die eine der beiden Farben aufweisen. Für diesen Fall erweitern Sie den Kriterienbereich einfach um eine zusätzliche Zeile.

Wollen Sie eine Abfrage nach Weinen formulieren, die entweder aus Italien kommen oder aus Rheinhessen, müssen Sie die Kriterien auf verschiedene Zeilen verteilen, weil sie sonst von Excel als UND-Verbindungen verstanden werden:

UND- und ODER-Kombinationen können natürlich auch gemischt werden:

Hier müssen die Weine in jedem Fall rot sein, können aber aus Italien oder Rheinhessen kommen.

Bei vergleichenden Suchkriterien kann der Inhalt einer Zelle in der Tabelle mit einer Zeichenfolge, einer Zahl, einem Wahrheitswert oder auch einem Fehlerwert verglichen werden.

Wird als Kriterium nur das Gleichheitszeichen eingetragen, werden alle Datensätze gesucht, die in dem kritischen Feld keinen Eintrag vorweisen, also leer sind. <> dagegen sucht alle Datensätze, die in der betreffenden Spalte über irgendeinen Eintrag verfügen, egal welcher Art.

Zeichenfolgen als Kriterium 

Wird nur eine Zeichenfolge als Kriterium verwendet, werden jeweils die Datensätze gesucht, in denen Übereinstimmung zwischen dem Feld in der Tabelle und dem Eintrag im Kriterienbereich besteht. Das Kriterium

ist also eine verkürzte Schreibweise für

Statt der Übereinstimmung mit einer Zeichenfolge kann auch die Stellung in Bezug auf die alphabetische Reihenfolge als Kriterium benutzt werden.

schließt die Weißweine aus, die Farben »rot« und »rosé« dagegen ein. Die Bedingung

dagegen wird durch »rot«, »rosé« und auch »weiß« erfüllt.

wählt alle Weine außer den französischen aus.

Gerade bei Zeichenfolgen, bei Namen und Bezeichnungen steht der Suchende aber oft vor dem Problem, dass ihm gerade die exakte Schreibweise nicht bekannt ist. In so einem Fall helfen Stellvertreterzeichen:

Hier einige Beispiel für solche ungefähren Suchkriterien:

Numerische Werte als Kriterium 

Wenn Sie in einem Feld des Kriterienbereichs eine Zahl eintragen, muss der Wert dieser Zahl mit dem Wert in dem gesuchten Datensatz exakt übereinstimmen. Nicht übereinstimmen muss das Format. Wenn also im Kriterienbereich eine Zahl im Standardformat eingegeben wird, in der Tabelle aber diese Spalte mit dem Währungszeichen formatiert ist, hat das für die Suchoperation keine Bedeutung.

Probleme kann es allerdings geben, wenn die Zahl in der Tabelle in der Anzeige gerundet ist, intern aber mit mehreren Nachkommastellen geführt wird. Das kommt z. B. häufig vor, wenn die Zahl das Ergebnis einer Division ist. Wenn im Kriterienbereich 3,33 steht und in der Tabelle als Ergebnis von 10/3 angezeigt wird, wird der Datensatz nicht gefunden. In solch einem Fall kann ein berechnetes Suchkriterium mit einer Formel wie

=RUNDEN(F9;2)=3,33

helfen. Ansonsten können Sie beim Vergleich mit Zahlen mit den schon aufgeführten Vergleichsoperatoren arbeiten.

17.2.8 Suchen mit berechneten Kriterien 

Die einfachen Vergleiche, die in den letzten Abschnitten behandelt worden sind, reichen nicht immer aus, um die Daten herauszufiltern, die für eine bestimmte Problemstellung von Bedeutung sind.

Oft müssen dazu z. B. Werte verschiedener Spalten verglichen werden. Oder Werte müssen zunächst mit einem Faktor multipliziert werden; das Ergebnis soll dann mit einem Wert außerhalb der Tabelle verglichen werden.

Für all diese Situationen können in Excel berechnete Kriterien verwendet werden. Oben haben Sie bereits das Beispiel mit der Berechnung der Differenz zwischen Lagerbestand und Mindestbestand kennen gelernt.

Für berechnete Kriterien gelten einige besondere Regeln:

• Das berechnete Kriterium erfordert einen eigenen Spaltennamen, der sonst in der Tabelle nicht vorkommt.

• Die Berechnungsformel muss immer eine logische Formel sein, die also WAHR oder FALSCH als Ergebnis liefert.

• Die Formel muss sich auf mindestens ein Feld in der Tabelle beziehen, und zwar entweder über die Adresse der ersten Zelle in der betreffenden Spalte oder über einen Spaltennamen.

Wird ein Zellbezug auf den ersten Wert einer Spalte benutzt, zeigt die Formel im Kriterienbereich den entsprechenden Wahrheitswert an. Ist das Kriterium in der ersten Zeile der Tabelle erfüllt, erscheint also WAHR, sonst FALSCH. Beachten Sie, dass der Zellbezug in diesem Fall relativ sein muss.

Bei einem berechneten Kriterium können Sie auch komplexe logische Vergleiche mit den Funktionen UND() oder ODER() formulieren oder auch mit anderen Tabellenfunktionen arbeiten.

=F6>Mittelwert($F$6:$F$25)

sucht etwa alle Weine, von denen überdurchschnittlich viele Flaschen auf Lager liegen.

Wird in einem berechneten Kriterium Bezug auf einen Wert außerhalb der Tabelle genommen, etwa auf eine bestimmte Konstante, muss der Bezug absolut gesetzt werden.

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.