Hast du Fragen zu Excel oder bist du an einem Hands-on Training interessiert? Retour aus der Webinar-Serie der Office Nerds und der Office Schulungen sowie interessanten Kundenprojekten melde ich mich mit weiterem Office Nerd Know-How zurück. Letztens durfte ich für einen unserer Kunden einen spannenden Ansatz verfolgen, welchen ich gerne im folgenden Blogbeitrag mit dir teile.
Komplexes Arbeiten – Datenerfassung Excel
Die Mitarbeitenden des Kunden erfassen in einer Excel-Datei ganz viele Datensätze als einzelne Zeilen. Ziel war es, dass der Benutzer durch die Datenerfassung «geleitet» wird, Auswahlmöglichkeiten zur Verfügung stehen und die Werte validiert werden. Notabene ohne Eingabemasken und auch ganz ohne Visual Basic for Applications (VBA) rundeten den Auftrag ab.
«Das wird komplex» war mein erster Gedanke. Nach einem Brainstorming wie ich es Umsetzen könnte, war klar, dass ich die Funktion «Datenüberprüfung» auf das allerletzte Ausreizen muss und kreativ vorgehen muss, ohne alles in einer Eingabemaske mit VBA zu programmieren (das wäre ja leicht gewesen). Am fiktiven Beispiel „Internet Geschwindigkeitstest“ möchte ich dir zeigen, wie ich Schritt für Schritt bis zum finalen Ergebnis vorgegangen bin. Im Beispiel werden Messungen an verschiedenen Orten in der Schweiz protokolliert. Nachfolgend siehst du die bereits gesammelten Daten:
Spätestens bei der Auswertung der Daten wirst du feststellen, dass bei der Eingabe einige Fehler passiert sind. Bei 10 Datensätze ist dies schnell korrigiert – bei mehreren 100’000 Einträgen wird dies eine längere, mühselige Angelegenheit.
Tipp: Formeln, Datenüberprüfungen und weitere Zellenanpassungen lassen sich am einfachsten in einer Tabelle übernehmen. Wenn Du deine Daten «als Tabelle formatiert» (Im Menüband unter Start zu finden) kannst du eine neue Zeile hinzufügen und alles wird korrekt übernommen.
Schauen wir uns die fehlerhaften Eingaben genauer an:
1. Anstatt «Luzern» wurde «Luzzern» geschrieben
2. Gemessener Messwert soll eingetragen werden und nicht ein etwaiger Wert
3. Ich bezweifle, dass die Messung dermassen hoch war
Prüfen wir das Pivot-Diagramm dieser Tabelle:
Gut ersichtlich ist, dass der Kanton Luzern («Luzzern») 2 Mal aufgelistet wird. Der angeblich gemessene Wert in Zug macht das Pivot-Diagramm nicht mehr aussagekräftig, da die restlichen Werte «verschwinden».
Excel ist ein Tabellenkalkulationsprogramm, welches einige Funktionen hat, die auch ganz leicht in abgewandelter Form genutzt werden können.
Falscheingaben und Formatfehler eliminieren
Mit der Funktion «Datenüberprüfung» kannst du Daten und Werte die der Benutzer eingegeben hat, einschränken. Du eliminierst dadurch sofort Falscheingaben und Formatfehler. Der Umgang mit Tabellen wird damit wesentlich einfacher und der Vorteil darin liegt, dass die Eingaben geprüft sind.
Wie erwähnt will ich sicherstellen, dass die Messwerte einheitlich eingetragen werden. Nachfolgend zeige ich dir, wie du eine solche Datenüberprüfung erstellen kannst:
1. Gewünschte Zellen auswählen:
2. [Daten] [Datenüberprüfung]
3. Als zugelassenen Wert wählst du je nach Einsatz den Typ aus. In unserem Beispiel möchte ich nur ganze Zahlen erlauben
4. Operation auswählen (grösser, kleiner, zwischen, ungleich, etc)
5. Je nach Operation kannst du noch Werte definieren
Tipp: Immer wenn du diesen Pfeil siehst, kannst du dort Zellen, Spalten, Zeilen, Bereiche von den Tabellenblättern referenzieren oder gar Formeln eingegeben:
6. Mit [OK] bestätigen
Da unser Beispiel bereits schon Daten beinhaltet werden Fehler in der Datenüberprüfung angezeigt und können bei Bedarf korrigiert werden:
Datenüberprüfung Messwerte
Ab jetzt werden die Eingaben für den Messwert geprüft, ob sie eine ganze Zahl zwischen 0 und 2000 ist.
Excel kann aber noch weit aus mehr mit solchen Datenüberprüfungen. Ich zeige dir nun wie du eine Liste mit mehreren Werten mithilfe der Datenüberprüfung gegenprüfen kannst:
1. Erstelle ein neues Tabellenblatt und füge deine Liste ein:
2. Nun markierst du in der Tabelle deine Zellen welche bei der Eingabe überprüft werden sollen
3. Du wählst im Menüband «Daten»
4. Und dann unsere «Datenüberprüfung»
5. Als zugelassenen Wert wählst du «Liste»
6. Zum Schluss definierst du die «Quelle» für die Liste
In unserem Beispiel sind die Daten für die Liste in dem Arbeitsblatt «Liste_Kantone»:
7. Optional kannst du noch entsprechende Meldungen definieren
8. Mit «OK» bestätigen und ab sofort werden die Daten bei Eingabe überprüft
Fortan werden die Eingaben der Kantone mit der statischen Liste abgeglichen.
Statische Liste in dynamische Liste umwandeln
Wir haben nun eine statische Liste. Wenn nun ein Wert dieser statischen Liste hinzugefügt wird, müssen alle Datenüberprüfungen erweitert werden (da diese absolut auf die Liste referenzieren). Du ahnst es: Das passt mir gar nicht. Aus diesem Grund will ich, dass sich die statische Liste automatisch erweitert und dadurch zu einer dynamischen Liste wird.
Ich zeige dir nun, wie du eine statische Liste in eine dynamische Liste umwandelst:
Die schnelle, einfache Lösung besteht aus einer Formel in der Datenüberprüfung:
=BEREICH.VERSCHIEBEN(BEZUG; ZEILEN; SPALTEN; HÖHE; BREITE)
- BEZUG: Der Startpunkt, der als Zellbezug oder Zellbereich bereitgestellt wird
- ZEILEN: Die Anzahl der Zeilen, die unterhalb des Startbezugs verschoben werden sollen
- SPALTEN: Die Anzahl der Spalten, die rechts vom Startbezug verschoben werden sollen
- HÖHE: [optional] Die Höhe in Zeilen des zurückgegebenen Zellbereichs
- BREITE: [optional] Die Breite in den Spalten des zurückgegebenen Zellbereichs
- BEREICH.VERSCHIEBEN: gibt einen Bezug zurück, der gegenüber dem angegebenen Bezug versetzt ist. Der zurückgegebene Bezug kann eine einzelne Zelle oder ein Zellbereich sein.
Da sich unsere Liste vom Beispiel nur in eine Richtung vergrössern kann (es ist eine eindimensionale Liste), ist das Argument Höhe das Massgebende. Das Argument Höhe soll sich also immer der Anzahl Einträge in der Liste automatisch anpassen und diese Anforderung kriegen wir mit folgender Formel hin:
=ANZAHL2(WERT1; WERT2;…)
- WERT1: Ein Element, eine Zellreferenz oder ein Zellbereich
- WERT2: [optional] Ein Element, eine Zellreferenz oder ein Zellbereich
Mit der Funktion ANZAHL2 kannst du die Anzahl der Zellen ausgeben lassen, die Zahlen, Text, logische Werte, Fehlerwerte und leeren Text («») enthalten. ANZAHL2 zählt jedoch keine leeren Zellen!
Für unser Beispiel ergibt sich also folgende Formel:
=BEREICH.VERSCHIEBEN(Liste_Kantone!$A$2:$A$4;0;0;ANZAHL2(Liste_Kantone!A:A)-1)
In unserem Beispiel gehen wir nun wie folgt vor:
1. Zelle mit bestehender Datenüberprüfung in Tabellenblatt «Tabelle1» auswählen
2. [Daten] und dann [Datenüberprüfung]
3. Formel einfügen und «Änderungen auf alle Zellen mit den gleichen Einstellungen anwenden:
4. Mit [OK] bestätigen und schon haben wir eine dynamische Datenüberprüfung
Tipp: Wenn du in deiner bestehenden Arbeitsmappe bereits Datenüberprüfungen auf mehreren Zellen definiert hast, kannst du eine Zelle mit einer Datenüberprüfung anwählen und bearbeiten. Mit «Änderungen auf alle Zellen mit den gleichen Einstellungen anwenden» übernimmst du automatisch die Anpassungen auf die anderen Zellen mit gleicher Datenüberprüfung.
Als Ergebnis haben wir nun eine Datenüberprüfung, welche sich dynamisch anhand der Werte in der Liste anpasst und somit die erste Automatisierung ohne VBA.
Datenüberprüfung in Abhängigkeit zu einer Auswahl setzen
Aus dem vorhergehenden Beispiel haben wir bereits eine Datenüberprüfung auf den Kantonen. Nun will ich die Orte entsprechend in Abhängigkeit setzen. Dazu gehen wir wie folgt vor:
1. Neues Tabellenblatt «Liste_Orte» erstellen
2. Anschliessend tragen wir in Zeile 1 die Abhängigkeit ein (im Beispiel entsprechend nun die Kantone)
3. Pro Spalte anschliessend die Orte welche zu dem Kanton zur Auswahl stehen sollen
4. Nun markieren wir jede Spalte
5. Über [Einfügen]
6. Dann [Tabelle] erstellen wir eine intelligente Tabelle
7. Aktivieren «Tabelle hat Überschriften»
8. Und mit [OK] bestätigen
–Schritt 4 – 8 wiederholen wir pro Spalte, so dass du am Schluss folgendes Bild hast:
9. Nun vergeben wir jeder Tabelle noch einen eindeutigen Namen unter [Tabellenentwurf]:
10. Zum Schluss setzen wir die Datenüberprüfung auf die Spalte «Orte» im Tabellenblatt «Tabelle1»:
11. Als Typ wählen wir «Liste»
12. Zum Schluss noch die Formel INDIREKT(A1) einfügen
13. Mit [OK] bestätigen
Ab sofort haben wir bei den Orten nur noch jene zur Auswahl welche im Kanton der Spalte A sind:
Solche Abhängigkeiten mit Datenüberprüfungen kannst du bei Bedarf immer wieder nutzen und verknüpfen.
Tipp: Datenüberprüfungen sollen vor dem Eintragen der Daten definiert werden.
Pflichtfelder definieren
In unserem Beispiel mit den Messungen soll sichergestellt sein, dass bei einer Messung immer die komplette Adresse des Messpunktes vorhanden ist. Daraus resultiert dass der Kanton, Ort und die Adresse sogenannte Pflichtfelder sind (diese müssen ausgefüllt sein, ansonsten soll die Eingabe des Messpunktes nicht möglich sein):
Eingabe beim Messpunkt nur zulassen wenn die roten Zellen bereits ausgefüllt sind.
Langsam kam ich ins Grübeln wie ich das den nun wieder ohne VBA umsetzen könnte. Ich griff nochmals zu den Datenüberprüfungen und verwendete eine Formel die super geeignet ist:
1. Zellen auswählen für die neue Datenüberprüfung:
2. [Daten] [Datenüberprüfung]
3. «Benutzer definiert» auswählen und Haken «Leere Zeilen ignorieren» entfernen
4. Einfügen der Formel:
=UND(NICHT(ISTLEER(A2));NICHT(ISTLEER(B2)))
- ISTLEER: Mit der ISTLEER Funktion kannst du ermitteln, ob eine Zelle leer ist. Sie gibt WAHR zurück, wenn eine Zelle leer ist und FALSCH, wenn eine Zelle nicht leer ist.
- NICHT: Mit dieser Funktion kannst du überprüfen, ob es stimmt, dass in einer abgefragten Zelle ein bestimmter Wert NICHT vorhanden ist.
- UND: Sind beide Bedingungen erfüllt, wird dir der Wert WAHR angezeigt. Sobald allerdings auch nur eine der beiden Bedingungen nicht mehr erfüllt ist, wird dir der Wert FALSCH angezeigt.
5. [OK] bestätigen, optional kannst du unter «Fehlermeldung» noch eine Meldung an den Benutzer eingeben, dass er weiss das zuerst Kanton und Ort ausgefüllt sein muss.
Abschluss
Jetzt kannst du die Pivot-Tabelle aktualisieren und dann sieht es sauber aus:
Welche Themen stellen dich vor Herausforderungen? Buche dein Microsoft Office Training bei mir.
Microsoft Office Training entdecken
Oder melde dich unverbindlich via Kontaktformular bei uns.
