Die Daten
In einem unserer Tipps sind für die Aufgabe 100 Namen und Geburtstage generiert worden. Diese Datei verwenden Sie nun, um von jeder Person das Alter zu berechnen und anschließend Altersgruppen farblich hervorzuheben. Da es in den Excel-Versionen bis 2003 (ohne Umwege) für die bedingte Formatierung nur 3 Möglichkeiten gibt, beschränken wir uns auch hier auf diese Anzahl von Gruppen. Das Prinzip ist bei den Folgeversionen gleich. Die Abbildungen stammen allerdings aus der Version 2010, sofern nicht etwas anderes vermerkt ist; die Vorgehensweise in Excel 2003 ist hier kurz erklärt …
Ausschnitt der Namensliste
Die Namensliste mit dem Geschlecht und den Geburtstagen haben Sie nun geladen. Um das Alter jeder Person zu berechnen, verwenden Sie die Excel-Funktion DATEDIF(). Ein kleiner Hinweis dazu: Diese Funktion ist zwar seit Generationen in Excel integriert, aber Sie finden sie nicht über den Funktionsassistenten. Über die F1 – Hilfe, hier in unserem Blog oder im Internet gibt es aber diverse Hilfestellungen und Erklärungen. Verwenden Sie diese Funktion in Zelle E2 und kopieren Sie diese dann nach unten, um für jeden Datensatz das Alter auszuweisen:
=DATEDIF(D2; HEUTE(); „y”)
Eine kurze Ausführung zu den Argumenten: D2 ist das frühe (ältere) Datum, der Geburtstag. HEUTE() wird als jüngeres Datum täglich aktualisiert, damit jeden Tag das Alter neu bestimmt werden kann. Und „y” (mit Anführungsstrichen und in Kleinschreibung!) sorgt dafür, dass die Differenz in (vollen) Jahren berechnet wird. Eine ausführliche Hilfe zur Funktion DATEDIF() bekommen Sie auch hier.
Das tagesaktuelle, berechnete Alter jeder einzelnen Person in der Liste steht nun in Spalte E. – Bleibt noch die Bestimmung der Altersgruppen und eine anschließende farbliche Kennzeichnung der Zeilen. Gruppe 1 soll bis einschließlich 24 Jahren gehen, Gruppe 2 von 25 bis 55 Jahren und Gruppe 3 alle älteren Personen. Welche farblichen Kennzeichnungen Sie verwenden, bleibt Ihnen überlassen, ich setze hier helle Hintergrundfarben ein, um nicht zu „bonbonfarben” zu wirken. Mein Ergebnis sieht dann so aus:
Mit Bedingter Formatierung fertig gestellte Alters-Liste
Noch ein Wort zu den Altersgruppen: Natürlich könnten Sie eine Hilfsspalte (beispielsweise Spalte F) erstellen, wo jede Gruppe berechnet wird. Dann wäre die bedingte Formatierung wirklich „ein Klacks”. Sie werden vielleicht aber auch den etwas schwierigeren Weg gehen und die bedingte Formatierung über eine entsprechende Formel gestalten.Das schildern wir Ihnen hier versionsabhängig.
Excel 2007
In Excel 2007 und den Folgeversionen markieren Sie anfangs den Bereich A2:E101, danach Klicken Sie auf den Ribbon (Schaltfläche im Menüband) Bedingte Formatierung und wählen dort Neue Regel… aus:
Schritt 1 in der Bedingten Formatierung
Sofort erscheint ein neues Fenster, welches sich so darstellt, wie in der folgenden Abbildung gezeigt. Wählen Sie die unterste Möglichkeit, denn Sie werden ja eine Formel zur Bestimmung der Gruppenzugehörigkeit verwenden:
Schritt 2 in der Bedingten Formatierung, „Formel” auswählen
Die erste Formel ist denkbar simpel, sie wird in das entsprechende Feld eingegeben:
Schritt 3 in der Bedingten Formatierung, Eingabe der Formel
Achten Sie bitte exakt auf die Schreibweise, die ist sehr wichtig! Für diese Gruppe 1 gilt ja, dass die Person jünger als 25 Jahre alt ist: =$E2<25. – Klicken Sie nun auf Formatieren… und wählen Sie eine passende Hintergrundfarbe aus:
Auswahl der Farbe für den Zell-Hintergrund
Das war’s auch schon. So werden alle der Vorgabe entsprechenden Zeilen im Datenbereich bis Spalte E in der Wunschfarbe hervorgehoben. Wenn das Ergebnis anders ist, als vorgesehen oder erwartet, dann kontrollieren Sie noch einmal alles Schritt für Schritt, entfernen Sie erforderlichenfalls die erstellte Formatierungsbedingung und beginnen Sie noch einmal ganz von vorne.
Prinzipiell geht das für die beiden anderen Altersgruppen gleichermaßen. Für die „Oldies” sollte Ihnen die Formel kein Problem bereiten, die Gruppe 2 allerdings ist nur etwas komplexer in der Erstellung der Formel. Ich stelle Ihnen hier einfach einmal meine Lösung vor, eine von vielen:
=UND($E2>24;$E2<=55)
Die „Übersetzung” der Formel lautet in etwa so: Durch das UND() müssen alle Bedingungen, welche innerhalb der Klammern stehen, erfüllt sein. Durch das Semikolon getrennt gibt es diese beiden Argumente: $E2>24 und $E2<=55. Die entscheidende Zelle des markierten Bereichs ist E2. Damit auch die Spalten A:D mit eingebunden werden, dort aber kein Vergleich auf Werte erfolgt, wird die Spalte E absolut festgelegt: $E2 . – Die erste Bedingung lautet also: $E2 muss größer sein als 24. UND $E2 muss kleiner oder gleich 55 sein. Das ist die zweite Bedingung. Wenn also beides zutrifft, wird die Zeile des markierten Bereichs entsprechend der ausgewählten Farbe markiert.
Eigentlich sind Sie mit Abschluss dieses Vorgangs, wenn also alle drei Formatierungen vorgenommen worden sind, auch schon fertig. Vielleicht aber soll noch eine kleine Auswertung der Personengruppen erfolgen? Mit den Excel-Funktionen ZÄHLENWENN() und ZÄHLENWENNS() ist das gut zu realisieren. Die schwierigere der Formeln habe ich Ihnen hier einmal bildlich dargestellt:
Die Funktion ZÄHLENWENNS() in der Anwendung
Zu den Funktionen ZÄHLENWENN() und ZÄHLENWENNS() finden Sie Näheres, wenn Sie auf die eben genannten Funktionsnamen Klicken (Statistik-Funktionen) und dann die entsprechende Funktion aufrufen. Zu ZÄHLENWENNS() noch der Hinweis, dass diese Funktion erst ab Excel 2007 zur Verfügung steht.
Fertig. Aufgabe gelöst. Es folgen hier noch einige Hinweise zum Lösungsweg, wenn Sie Excel 2003 verwenden. Es sind allerdings nur die markanten Abweichungen dargestellt.
Excel 2003
Sie beginnen im Menü Format und wählen dort die Bedingte Formatierung… :
Bedingte Formatierung in Excel 2003
Im folgenden Fenster ändern Sie die Bedingung 1 so ab, dass Formel ist die aktuelle Auswahl ist:
Bedingte Formatierung in Excel 2003, Schritt 2
Für die erste Bedingung geben Sie die Formel exakt so ein, wie im folgenden Bild dargestellt:
Bedingte Formatierung in Excel 2003, Formel erstellen
Danach auf Format… Klicken und die Hintergrundfarbe auswählen:
Bedingte Formatierung in Excel 2003, Farbe festlegen
Für die beiden anderen Bedingungen Klicken Sie jeweils vorher auf Hinzufügen » und gehen Sie mit den entsprechenden Formeln (siehe weiter oben) gleichermaßen vor.
Hinweis: Die Funktion ZÄHLENWENNS() gibt es in Excel 2003 noch nicht, eine Auswertung müsste in früheren Versionen als 2007 auf anderem Wege erfolgen.
[NachObenLetzte Verweis=„T&T: Altersgruppen farblich”]