Autor: Klaus-Dieter Oppermann --- Aus Excel Standard - Gruppe: Spezielle Menübefehle
Weitere Artikel der Gruppe: Spezielle Menübefehle Aus Excel Standard
Nach oben
ToDo
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:1Zwei 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.
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.
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.
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 | ||||||||||
|
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.
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:
Tipp: Wenn in die Leere(n) Zelle(n) ein Leerzeichen (Space) eingegeben wird, ist dieser Effekt aufgehoben.
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).
Entsprechend der Farbmarkierung vergeben wir vier Namen:
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:
Das lässt sich beliebig fortsetzen. Hier noch mal eine Stufe weiter:
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.
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
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 | |||||||||||||||
|
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:
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.
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 | ||||||||||||||||||||||||||||||
|
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.
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).
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.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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).
Dieses Thema hat weitere Beiträge
Datenüberprüfung - Gültigkeit (2) - Listen und mehr
Datenüberprüfung - Gültigkeit (1) - Grundlagen
Datenüberprüfung - Gültigkeit (2) - Listen und mehr
Datenüberprüfung - Gültigkeit (1) - Grundlagen
Weitere Artikel der Gruppe: Spezielle Menübefehle Aus Excel Standard
Nach oben
Google Werbung
