Axel Werner's OPEN SOURCE Knowledge Base

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Einzigartige Namen und Gültigkeitsprüfung mit Microsoft Excel ======

**Kennen Sie das Problem? Sie erstellen mit Microsoft Excel z.B. eine große Inventarisierungsliste Ihrer PCs und versuchen diese über einen größeren Zeitraum aktuell zu halten. Und irgend wann, auf ein mal finden Sie doppelte Seriennummern oder PC Namen, doppelte MAC Adressen oder ähnliches, obwohl diese Informationen in der gesamten Liste nur ein mal vorkommen dürfen? Ärgerlich! Da hat wohl jemand bei der Eingabe geschlampt und vergessen seine kopierten Einträge vollständig anzupassen. Leider macht Microsoft Excel den Benutzer nicht darauf aufmerksam dass ein solcher einzigartiger Eintrag eben einzigartig sein soll und nicht doppelt vorkommen darf. Jedoch kann man Excel mit relativ wenig Aufwand dazu bringen dies zu tun. Und sogar mehr... Die Möglichkeiten sind mannigfaltig. Lesen Sie hier mehr über die Funktion ZÄHLENWENN(), WENN() sowie über "Bedingte Formatierung"...**

Nehmen wir mal folgende Tabelle als Beispiel:

{{:it-artikel:windows:excel:tab1.png|}} 
 

===== Das Ziel =====
 
Wir wollen nun sicherstellen das...

  - PC Namen immer genau 5 Zeichen lang sind
  - MAC Adressen immer genau 6 Bytes (12 Stellen) lang sind
  - leere (nicht ausgefüllte Felder) keine Fehler oder Wirres Zeug anzeigen.
  - die Einträge der Spalte 1, 2 und 3 niemals doppelt vorkommen
  - Bei einer illegalen Eingabe sollen sich die betroffenen Zellen automatisch ROT verfärben. 


===== Wie erreichen wir das? =====

 
Am einfachsten in mehreren Schritten. Wir tasten uns Stück für Stück an unsere Gesamtlösung heran und entwickeln zunächst mal einfache Formeln für einzelne Teilbereiche.

 
==== Punkt 1 + 2  - PC Namen und MAC Adressen müssen immer eine bestimmte Länge haben ====

Hierfür kombiniere ich einfach die Funktionen LÄNGE() und WENN() um entweder den Wert WAHR oder FALSCH zu erhalten. Man könnte sich hier auch "HALLO" oder "DU DUMMI" anzeigen lassen, aber WAHR und FALSCH sind Werte welche wir später in kombination mit weiteren Funktionen gut auswerten können.

 
Dies könnte so aussehen:

 
<color green>**=WENN( LÄNGE(A14)=5 ; WAHR ; FALSCH )**</color>

 
Klartext:

 
Wenn die Länge in Zelle A14 genau 5 Zeichen lang ist, dann melde mir WAHR, andernfalls melde mir FALSCH.

{{:it-artikel:windows:excel:tab2.png|}}

Damit sind Punkt 1 und 2 gelöst. Mit Hilfe dieser Formel in einer separaten spalte eingebaut könnten wir uns auf diese Weise anzeigen lassen ob eine Eingabe die richtige Länge hat oder nicht. Aber das reicht mir nicht. Denn wir bekommen auch dann noch FALSCH angezeigt wenn wir z.b. noch gar keine Eingabe in Spalte ein gemacht haben. Und doppelte Einträge wie in Zeile 6 und 14 werden uns auch noch nicht als Fehler angezeigt.
 

==== Punkt 3 - tue nichts wenn keine Eingabe vorhanden ist ====


Alles was dir dazu brauchen haben wir bereits behandelt. Wir nutzen einfach wieder unsere Funktion WENN() und formulieren folgenden Ausdruck als Formel:

Wenn im Feld A14 nichts steht (leere Menge, "") dann zeige mir auch nichts, andernfalls zeige mir das Ergebnis unserer vorhin entwickelten Formel ob die Eingabe die korrekte Länge hat.

Wir packen also um unsere vorhandene Längen-Bestimmungsformel einfach nochmal eine WENN() funktion herum. Das sieht dann so aus:

**<color red>=WENN(A15="";"";</color>      <color green>WENN(LÄNGE(A15)=5;WAHR;FALSCH)         </color><color red>)</color>**



  * <color red>ROT == neu</color>
  * <color green>GRÜN == alt</color>

{{:it-artikel:windows:excel:tab3.png|}}

Wie man hier unschwer erkennen kann bleibt die Zelle B15 leer obwohl dort ebenfalls die Formel zur Anzeige der Länge eingetragen wurde. Es funktioniert also.

 

==== Punkt 4 - doppelte Einträge finden/anzeigen ====


Hierbei hilft uns eine weitere Excel Funktion in kombination mit WENN(). Die neue Funktion heisst ZÄHLENWENN() (englisch COUNTIF() . Der Name der Funktion klingt etwas komisch und unlogisch. Ist es aber nicht. Diese Funktion ZÄHLT für uns das Vorkommen eines bestimmen Zellen-Inhaltes WENN diese Zelle mit etwas befüllt ist. Andernfalls tue nichts.


Ein Beispiel:


**=ZÄHLENWENN(  A5:A16 ; A14)**


Klartext:


Zähle/Zeige mir wie oft innerhalb des Bereiches A5-A16 der Inhalt der Zelle A14 vorgekommen ist.

Damit können wir sehr einfach feststellen ob ein Eintrag "einzigartig", also genau 1 mal vorhanden ist,  oder dieser eben mehrfach vorkommt (>1 mal gefunden wurde). Wir nutzen dafür einfach wieder unser WENN().


**=WENN(   ZÄHLENWENN(  $A$5:$A$16  ;  A12) >1   ;  WAHR  ;  FALSCH  )**


Klartext:


Wenn Inhalt von A12 innerhalb des Bereiches von A5-A16 mehr als 1 mal vorkommt zeige WAHR an, andernfalls zeige FALSCH an.


{{:it-artikel:windows:excel:tab4.png|}} 


Damit wäre nun auch Punkt 4 gelöst.

 Aber wie nun alles miteinander kombinieren ?? Genau! mit WENN() . Dabei gibt es nun mehrere Möglichkeiten wie man die einzelnen Formeln hintereinander verschachtelt. Ich wähle einfach mal folgende Variante:


**<color red>=WENN(   A5=""; FALSCH ;   </color><color blue>WENN(  LÄNGE(A5)=5; </color><color green>WENN(   ZÄHLENWENN( $A$5:$A$15;  A5) >1;  WAHR;  FALSCH  )</color><color blue>;   WAHR )  </color><color red>     )</color>**


Das sieht sehr verwirrend aus und klingt erst mal unlogisch. Wird dann aber von nutzen sein wenn wir damit Punkt 5 lösen wollen. Ich habe dazu die LOGIK umgedreht. d.h. WAHR bedeutet nun SCHLECHT und FALSCH bedeutet nun GUT.

Klartext:

Wenn eine Zelle in Spalte A leer ist melde mir FALSCH zurück (alles ist gut!), andernfalls überprüfe die Länge der Zelle in Spalte A. Wenn diese 5 Zeichen lang ist prüfe ob dessen Inhalt in der Spalte A mehrfach vorkommt. Falls mehrfach gefunden melde WAHR zurück (SCHLECHT! Es dürfen ja keine doppelten Einträge vorkommen), andernfalls melde FALSCH (alles ok). Falls die Länge in Spalte A nicht =5 ist melde WAHR (SCHLECHT) zurück.

 
{{:it-artikel:windows:excel:tab5.png|}}
 

Wann immer nun also ein Eintrag uns nicht gefällt meldet Excel uns nun ein WAHR (logisch 1) zurück. Das können wir nun zur Lösung von Punkt 5 gut gebrauchen. 

==== Punkt 5 - Eingabezelle ROT färben wenn ein illegaler Eintrag gemacht wurde ====

Es ist nicht gerade übersichtlich wenn man für jedes Eingabefeld ein extra Anzeigefeld benötigt welches einem sagt ob  wir die Eingabe nun korrekt oder falsch vorgenommen haben. Viel eleganter wäre es doch wenn uns Excel bei einer falschen Eingabe gleich mit ROTER FARBE zu erkennen gäbe wenn wir einen Fehler gemacht haben. Das können wir mit Hilfe von "bedingter Formatierung" sehr elegant lösen.

Wir benötigen hierzu unsere letzte Formel.


Gehen Sie zur Einrichtung folgendermaßen vor:
  - Erste Eingabezelle markieren (A5)
  - Menü FORMAT / Bedingte Formatierung... auswählen \\ {{:it-artikel:windows:excel:tab6.png|}}
  - Bedingung auf "Formel ist" umstellen und unsere letzte Formel eingeben. Anschließend auf den Button FORMAT... klicken \\ {{:it-artikel:windows:excel:tab7.png|}}
  - Auf Registerkarte "Muster" umschalten, gewünschte Warnfarbe auswählen und mit OK bestätigen. \\ {{:it-artikel:windows:excel:tab8.png|}}
  - Bedingte Formatierung mit OK bestätigen. Wir haben nun für unsere erste Eingabezelle A5 (und nur für diese!) eine Bedingte Formatierung vorgenommen. Wir möchten diese Einstellung aber auch für unsere anderen Eingabezellen in der Spalte A übernehmen. Wie das ? Dazu...
  - Sicherstellen das unsere soeben formatierte Zelle A5 markiert ist
  - EIN MAL auf das Symbol "Format übertragen" klicken \\ {{:it-artikel:windows:excel:tab9.png|}}
  - nun mit der Maus in EINEM ZUG alle Zellen markieren welche das gleiche Format wie unsere A5 erhalten sollen. Nun sehen Sie dass auf ein mal alle "Illegalen Einträge" ROT hinterlegt werden. Die bedingte Formatierung ist damit abgeschlossen. \\ {{:it-artikel:windows:excel:tab10.png|}}


==== Was ist passiert? ====

 
Wir haben soeben den Zellen A5 - A18 eine ganz besondere Formatierung verpasst. Eine "bedingte Formatierung". Bedingt bedeutet, dass nun unsere zuvor entwickelte Formel darüber entscheidet ob die einzelnen Zellen nun ROT hinterlegt werden sollen  oder nicht. Wann immer unsere Formel für eine einzelne Zelle ein WAHR zurück meldet, wird Excel diese Zelle wie befohlen ROT färben. Bei einem FALSCH Ergebnis wird Excel die Zelle in ihrer vorherigen Farbe belassen.
 
Nun können wir auch wieder unsere hässliche Spalte B mit all en Anzeige-Feldern löschen. Diese brauchen wir nun nicht mehr, da wir diese bislang nur als Ersatz genutzt hatten. Mit wenig Änderungen an unserer Formel können wir diese nun auch noch für die Spalten IP Adresse und MAC Adresse nutzen. So muss nur die gewünschte Länge sowie die Adressen geändert werden und schon haben wir eine sehr einfache aber nützliche Eingabehilfe. Doppelte oder Illegale Fehleingaben durch unachtsamkeit sind somit "fast" nicht mehr möglich.

 
Viel Erfolg!

 
 --- //[[mail@awerner.homeip.net|Axel Werner]] 2009-03-19 20:15//
 
{{tag>windows office excel ZÄHLENWENN}}