Autor: Klaus-Dieter Oppermann  --- Aus Excel Standard - Gruppe: Spezielle Menübefehle

Datenüberprüfung - Gültigkeit (2) - Listen und mehr

Autor: Klaus-Dieter Oppermann - Erstellt: ?      - Letzte Revision: 2011-02Gruppenthema: 2 Folgen 1 2 Sie sind in Folge:2
Zwei Bereiche der Funktion Gültigkeit hatte ich im ersten Beitrag ausgeklammert, weil sie sehr komplex sind. Es sind die Bereiche Listen und Benutzerdefiniert, die ich in diesem Beitrag behandeln werde. Als erstes sehen wir uns den Bereich Listen an.

- Listen - vorhandene verwenden

Manche(r) hat sich vielleicht schon gefragt, ob es nicht möglich ist, wenn schon die Eingabemöglichkeiten eingeschränkt werden, diese in Form einer Liste anzubieten. Natürlich geht das. Dazu ein einfaches Beispiel.

Über das Menü Daten wird die Funktion Gültigkeit aufgerufen.

Dort wählen wir unter Zulassen den Punkt Listen. Es gibt es nur eine Möglichkeit etwas einzugeben. Das ist das Feld mit dem Namen Quelle.

  A B C
1 Firma    
2 Alfreds Futterkiste    
3 Ana Trujillo Emparedados y helados    
4 Antonio Moreno Taquería    
5 Around the Horn    
6 Berglunds snabbköp    
7 Blondel père et fils    
8 Bottom-Dollar Markets    
9 Cactus Comidas para llevar    
10 Comércio Mineiro    
11 Ernst Handel    
12 Familia Arquibaldo    
13 FISSA Fabrica Inter. Salchichas S.A.    
14 Folies gourmandes    
15 Hungry Owl All-Night Grocers    
16 Laughing Bacchus Wine Cellars    

Datengültigkeit der Tabelle
Zelle Zulassen Daten Eingabe 1 Eingabe 2
C2 Liste   =$A$2:$A$16  


Wir wollen nun die Inhalte der Spalte C als Gültigkeit definieren. Dazu markieren wir zunächst die Zelle C2. Danach klicken wir in das Feld Quelle und markieren anschließend in der Spalte A die Zellen von Zeile 2 bis Zeile 16. Es steht nun im Feld Quelle dieser Eintrag: =$A$2:$A$16, der Listenbereich aus dem nun die Gültigkeiten definiert werden.

Da nur die Zelle C2 markiert war, bevor die Gültigkeit definiert wurde, haben wir sie auch nur für diese Zelle festgelegt. Das macht in den meisten Fällen sicher keinen Sinn. Deshalb wiederholen wir die Prozedur, markieren nun aber einen beliebig langen Bereich der Spalte C. Jetzt ist die Gültigkeit in "einem Schuß" für den gesamten Bereich festgelegt.

Sicher ahnen sie es schon, man könnte natürlich den Zellbezug auch von Hand eintragen. Wichtig: dabei auf jeden Fall die absoluten Bezüge (Dollarzeichen ($)) mit eingeben. Sonst haben sie in jeder Zelle eine andere Gültigkeit, weil bei relativen Bezügen (ohne Dollarzeichen), mit fortschreitender Zeilenzahl oben in der Liste die Begriffe fehlen, während unten Leerzeichen zugefügt werden.

Als nächstes versuchen sie einmal eine Gültigkeit aus einer Zeile zu erstellen. Dazu wieder einen Bereich markieren, in das Feld Quelle klicken und diesmal die Zeile mit den Überschriften selektieren. Auch daraus wird eine Gültigkeitsliste. Das bedeutet, der Ursprung einer solchen Liste kann in einer Spalte oder auch in einer Zeile stehen. Die Betonung liegt dabei bei einer. Bereiche aus mehr als einer Spalte oder Zelle verursachen eine Fehlermeldung, das geht nicht. Ebenso verursachen unterbrochene Markierungen in einer Spalte oder Zeile eine Fehlermeldung. Auch das ist nicht zulässig.

  A B C D E
1 Firma Kontaktperson Position Straße Ort
2 Alfreds Futterkiste Maria Anders Vertriebsmitarbeiterin Obere Str. 57 Berlin
3 Ana Trujillo Emparedados y helados Ana Trujillo Inhaberin Avda. de la Constitución 2222 México D.F.
4 Antonio Moreno Taquería Antonio Moreno Inhaber Mataderos 2312 México D.F.
5 Around the Horn Thomas Hardy Vertriebsmitarbeiter 120 Hanover Sq. London


Möglich ist dagegen die Verwendung eines benannten Bereichs. Um das auszuprobieren, markieren wir den Bereich der Zellen A2 bis A16 in unserer ersten Liste, gehen auf im Menü Einfügen auf Namen / Einfügen und schreiben in der Dialogbox ins Feld Namen in der Arbeitsmappe zum Beispiel den Namen Firmen. Wenn nun unter Quelle =Firmen eingetragen wird, stehen wieder die Begriffe als Gültigkeit zur Auswahl. Die zulezt beschriebene Möglichkeit ist sehr interessant, weil sie eineige weitere Möglichkeiten eröffnet. Dazu mehr in den folgenden Abschnitten.


- Listen - händisch füllen

Dazu schreiben sie in das Feld Quelle Haus; Garage; Garten hinein. Oder etwas anderes, was ihnen gerade einfällt. Nach der Bestätigung mit OK erscheinen die Begriffe in Form einer Auswahlliste, wenn man auf die kleine Schaltfläche drückt, die rechts neben der Zelle erscheint, wenn diese aktiviert wird.

Wichtig ist, die einzelnen Einträge durch ein Semikolon zu trennen. Warum, das sieht man, wenn in der nächsten Zeile Mein Haus; Deine Garage; Unser Garten eingetragen wird. Beim Öffnen der Auswahlliste erkennt man, dass das Semikolon in der Liste einen Zeilenumbruch erzeugt und damit die einzelnen Begriffe trennt.

- Listen - Listen von anderen Tabellenblättern

Normalerweise müssen sich die Listen, die die Werte für eine Gültigkeitsprüfung liefern, auf dem gleichen Tabellenblatt befinden, wie die Gültigkeitsprüfung selbst. Wenn allerdings ein benannter Bereich verwendet wird, ist es auch möglich, auf eine Liste von einem anderen Tabellenblatt zuzugreifen.

ACHTUNG: Leider gibt es bei dieser Vorgehensweise eine "Falle". Wenn man einen benannten Bereich verwendet, in dem es eine leere Zelle gibt, darf der Haken beim Kontrollkästchen leere Zellen ignorieren nicht gesetzt sein. Sonst ist es möglich, trotz der Gültigkeitsregel beliebige Eingaben vorzunehmen.

Die Beispielliste zeigt was gemeint ist:
  C
1 Position
2 Vertriebsmitarbeiterin
3 Inhaberin
4  
5 Vertriebsmitarbeiter
6 Einkaufsleitung
7 Marketingmanager
8 Buchhalterin
9 Vertriebsagent
10 Vertriebsassistent
11 Vertriebsmanager
12 Marketingassistentin
13 Buchhalter
14 Vertriebsagentassistent
15 Vertriebsassistentin
16 Marketingassistent
17 Vertriebsagentin
18 Vertriebsagentassistentin


Tipp: Wenn in die Leere(n) Zelle(n) ein Leerzeichen (Space) eingegeben wird, ist dieser Effekt aufgehoben.

- Listen - Gültigkeit in Abhängigkeit

Eine sehr häufige Frage in allen Excelforen ist die nach der Erstellung von Gültigkeiten in Abhängigkeit. Mit den oben vorgestellten techniken, lässt sich das gut realisieren. Wir erstellen eine Liste nach diesem Muster. (Die Farben dienen nur der Erläuterung, sie sind entbehrlich).

  A B C
1 Autos Städte Tiere
2 Audi Berlin Katze
3 BMW Braunschweig Hund
4 Citroen´ Bremen Vogel
5 Ford Celle Pferd
6 Opel Hamburg Hase
7 Mercedes Hannover Rind
8 VW Lüneburg  


Entsprechend der Farbmarkierung vergeben wir vier Namen:
  • Orange = Auswahl
  • Gelb = Autos
  • Grün = Städte
  • Blau = Tiere
Benannte Bereiche definieren, wie geht das?

Nun wird in einer beliebigen Zelle, ich habe A2 gewählt eine Gütigkeit für die erste Auswahl angelegt. Dazu wird in der Dialogbox Gültigkeitsprüfung unter Zulassen Liste ausgewählt. Bei Quelle wird der Bereichsnamen =Auswahl eingetragen. Das ist der Bereich, den ich Orange gekennzeichnet habe. Die Inhalte Autos, Städte und Tiere können nun ausgewählt werden.

Die Zweite Gültigkeit habe ich in Zelle B2 angelegt. Hier wird in der Dialogbox bei Zulassen Liste ausgewählt. Bei Quelle tragen wir =INDIREKT(A2) ein. Die Funktion Indirekt muss hier angewendet werden, weil bei einem direkten Bezug auf A2 nur der dort aktuell angezeigte Wert zur Verfügung steht, was uns nicht weiter helfen würde. Durch die Funktion Indirekt wird die Liste angezeigt, die sich hinter dem Namen verbirgt.

So sieht das Ergebnis aus:

  A B
1    
2 Städte Braunschweig
3    

Datengültigkeit der Tabelle
Zelle Zulassen Daten Eingabe 1 Eingabe 2
A2 Liste   =Auswahl  
B2 Liste   =INDIREKT(A2)  


Das lässt sich beliebig fortsetzen. Hier noch mal eine Stufe weiter:

  E F G H I J
1 Katze Hund Vogel Pferd Hase Rind
2 Perser Bernhardiner Amsel Araber Feld Holsteiner
3 Angora Schäferhund Fink Ponny Stall Rotbunt
4 Siam Pudel Star Shetland   Maremma
5 Wildkatze Dackel Meise Norweger    
6   Dogge Zeisig      


Die nächste Regel könnte man nun zum Beispiel in Zelle C2 anlegen. In der Dialogbox würde unter Quelle =INDIREKT(B2) eingetragen.
Als Übung kann nun mal jeder für sich versuchen, zu den Punkten Autos und Städte Untergliederungen einzubauen.


- Benutzerdefinierte Gültigkeiten

Dahinter verbirgt sich die Möglichkeit, ganz bestimmte Eingaben zu erzwingen. Dazu zunächst ein paar Beispiele:

  A B C
1      
2   Zellen mit Gültigkeitsregeln Das kann man eingeben
3   Das ist ein Test Alles außer XYZ und XXX
4   13-45-3416 12-21-1234
5   1234567890 1234567890
6   16.-18.12.06 10.-11.08.06
7   Das ist ein Test Wenn Zellen F2 bis I2 gefüllt sind - alles

Datengültigkeit der Tabelle
Zelle Zulassen Daten Eingabe 1 Eingabe 2
B3 Benutzerdefiniert   =UND(B3<>"XYZ";B3<>"XXX")  
B4 Benutzerdefiniert   =UND(LÄNGE(B4)=10;ISTZAHL(TEIL(B4;1;2)*1)=
WAHR;ISTZAHL(TEIL(B4;3;2)*1)=WAHR;ISTZAHL(TEIL(B4;7;4)*1)=WAHR)
 
B5 Benutzerdefiniert   =UND(LÄNGE(B5)=10;ISTZAHL(TEIL(B5;1;2)*1)=
WAHR;ISTZAHL(TEIL(B5;3;2)*1)=WAHR;ISTZAHL(TEIL(B5;7;4)*1)=WAHR)
 
B6 Benutzerdefiniert   =UND(ISTZAHL(LINKS(B6;2)*1)=WAHR;TEIL(B6;3;2)=
".-";ISTZAHL(TEIL(B6;5;2)*1)=WAHR;TEIL(B6;7;1)=".";ISTZAHL(TEIL(B6;8;2)*1)=
WAHR;TEIL(B6;10;1)=".";ISTZAHL(RECHTS(B6;2)*1)=WAHR)
 
B7 Benutzerdefiniert   =ANZAHLLEEREZELLEN(F2:I2)=0  


Dieser Bereich ermöglicht es also, auch die Eingabe bestimmter Kombinationen von Buchstaben und Zahlen zu erzwingen. Oder es kann die Kombination verschiedener (bestimmter) Eingaben erlaubt oder verboten werden, so wie im ersten Beispiel, wo außer YXZ oder XXX alles zulässig ist. Wenn man in diesem Beispiel ungleich (<>) durch gleich (=) ersetzt, sind nur diese Eingaben möglich. (Das wäre dann aber auch wieder mit einer definierten Liste möglich, so wie es im 1. Teil beschrieben ist).

Für das zweite und dritte Beispiel wird die gleiche Formel benutzt. Man kann also sehen, das man sehr genau definieren muss, was erlaubt ist. Wenn also die Minuszeichen (-) erforderlich sind, müsste das entsprechend mitdefiniert werden.

Hinweis: wenn im Rahmen einer solchen Formeldefinition als erstes Zeichen eine Zahl erwartet wird, löst eine Null an dieser Stelle eine Fehlermeldung aus, da Eingaben mit einer führenden Null von Excel als Text interpretiert werden.

Das vierte Beispiel stellt die Eingabe eines bestimmten, für Excel so nicht vorgesehenen Formats einer Datumseingabe dar.

Beispiel fünf zeigt, wie man Eingaben in einer bestimmten Zelle davon abhängig machen kann, dass andere Zellen bereits ausgefüllt sind.

Für die benutzerdefinierte Gültigkeitsregel gilt ganz besonders das, was ich im ersten Teil über die Fehlermeldungen in Verbindung mit den Gültigkeitsregeln geschrieben habe. Hier ist es ganz besonders wichtig, dem Anwender mitzuteilen, welche Eingaben erwartet werden. Es sei denn, er soll zum Wahnsinn getrieben werden. Was ich aber nicht hoffen will.

- Gültigkeit in Verbindung mit SVerweis

Die Kombination der Funktionen Gültigkeit und SVerweis bietet eine komfortable Möglichkeit, verschiedene Werte aus einer Liste auszulesen, ohne dafür immer neue SVerweis-Formeln zu schreiben, oder diese von Hand anzupassen. Dazu legen wir eine Liste nach dem unteren Muster an. In Spalte A befinden sich die Gültigkeitsregeln, die Ihre Inhalte aus der Liste beziehen, die auch die Matrix für den SVerweis liefert. (Spalten D bis J).

In Spalte B werden die Werte ausgegeben die der SVerweis findet. Da die Gültigkeit ihre Werte aus Spalte D bezieht, ist sicher gestellt, das der Nutzer nur Begriffe auswählen kann, die es in der Matrix auch gibt. Damit sind auch Probleme ausgeschlossen, die aus Tippfehlern resultieren.

In Celle C1 befindet sich eine weitere Gültigkeitsregel, die Ihre Werte aus den Überschriften der Spalten E bis J bezieht. Entsprechend der Monatsauswahl wird in Zelle C1 der ausgewählte Monat geschrieben, der für den SVerweis die Spalte des auszulesenden Wertes angibt.

In den Zellen mit den Überschriften und damit in Zelle C1 steht in Wahrheit aber nicht der Name des Monats, sondern ein Datum wie 01.01.2006, das durch die benutzerdefinierte Formatierung (MMMM) als Schriftzug mit dem Monatsnamen dargestellt wird. Das ist wichtig, da andernfalls die SVerweis-Formel nicht funktionieren würde.

Auf diese Weise passen sich die SVerweisformeln automatisch an den ausgewählten Monat an und liefern die gesuchten Werte.

  A B C D E F G H I J
1 Produkt Summen Februar Produkt Januar Februar März April Mai Juni
2 Meeresfrüchte 76,00 €   Milchprodukte 287,30 € 88,00 € 599,00 € 72,00 € 331,00 € 474,00 €
3 Süßwaren 592,00 €   Meeresfrüchte 248,19 € 76,00 € 490,00 € 309,00 € 173,00 € 294,00 €
4 Naturprodukte 484,00 €   Süßwaren 327,08 € 592,00 € 422,00 € 525,00 € 416,00 € 185,00 €
5 Gewürze 597,00 €   Getreideprodukte 141,75 € 172,00 € 412,00 € 271,00 € 574,00 € 91,00 €
6 Fleischprodukte 369,00 €   Naturprodukte 161,85 € 484,00 € 393,00 € 178,00 € 19,00 € 137,00 €
7 Getränke 328,00 €   Gewürze 276,75 € 597,00 € 357,00 € 418,00 € 488,00 € 30,00 €
8 Getreideprodukte 172,00 €   Fleischprodukte 324,04 € 369,00 € 347,00 € 498,00 € 299,00 € 196,00 €
9 Milchprodukte 88,00 €   Getränke 455,75 € 328,00 € 278,00 € 52,00 € 241,00 € 489,00 €

Formeln der Tabelle
Zelle Formel
B2 =SVERWEIS(A2;D$2:J$9;MONAT(C$1)+1;FALSCH)
B3 =SVERWEIS(A3;D$2:J$9;MONAT(C$1)+1;FALSCH)
B4 =SVERWEIS(A4;D$2:J$9;MONAT(C$1)+1;FALSCH)
B5 =SVERWEIS(A5;D$2:J$9;MONAT(C$1)+1;FALSCH)
B6 =SVERWEIS(A6;D$2:J$9;MONAT(C$1)+1;FALSCH)
B7 =SVERWEIS(A7;D$2:J$9;MONAT(C$1)+1;FALSCH)
B8 =SVERWEIS(A8;D$2:J$9;MONAT(C$1)+1;FALSCH)
B9 =SVERWEIS(A9;D$2:J$9;MONAT(C$1)+1;FALSCH)

Datengültigkeit der Tabelle
Zelle Zulassen Daten Eingabe 1 Eingabe 2
C1 Liste   =$E$1:$J$1  
A2 Liste   =$D$2:$D$9  
A3 Liste   =$D$2:$D$9  
A4 Liste   =$D$2:$D$9  
A5 Liste   =$D$2:$D$9  
A6 Liste   =$D$2:$D$9  
A7 Liste   =$D$2:$D$9  
A8 Liste   =$D$2:$D$9  
A9 Liste   =$D$2:$D$9  

Editiert am 11.02.2011
Der Vorschlag der Verbindung von SVerweis und Gültigkeit hat den Schönheitsfehler, dass die Monatsangaben duch eine Formatierung aus Datumeinträgen abgeleitet werden. Deshalb habe ich eine Alternative entwickelt, die mit reinen Texten arbeiten kann. Das gilt für die Spaltenüberschriften, wie für die Gültigkeitsregel. Die angepasste Formal in den Zellen B3 bis B9 lautet: =SVERWEIS(A2;D$2:J$9;(VERGLEICH(C$1;E$1:P$1;0)+1);FALSCH) (Formel in der Zelle B3, von dort einfach herunter ziehen).
Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Dieses Thema hat weitere Beiträge

Datenüberprüfung - Gültigkeit (1) - Grundlagen
Datenüberprüfung - Gültigkeit (2) - Listen und mehr

Weitere Artikel der Gruppe: Spezielle Menübefehle Aus Excel Standard
Nach oben