Umarbeitung der Liste Bundestagsmitglieder
zur Kunden-Liste
Für verschiedene Zwecke setzen wir ja die Liste der Bundestags-Mitglieder in unseren Beiträgen und Aufgaben als Datenquelle ein. Diese Aufstellung soll in dieser Aufgabe zu einer KundenListe erweitert werden. – Um die Vielfalt der Möglichkeiten nicht einzuschränken, bleibt es bei einer Rohdaten-Form, also seitens der Kundennummern „gemischte” Daten ohne jegliche Formatierung.
Die Ausgangsdaten gibt es natürlich schon in verschiedenen Varianten. Sie laden bitte die einfachste Form hier herunter. Idealerweise speichern Sie die Mappe sofort unter einem anderen Namen, damit Sie nicht versehentlich eine vorhandene Datei überschreiben. Der Name könnte KundenListe.xlsx sein.
Vorarbeit
Eine solide Basis ist für die Arbeit mit Excel immer wichtig. Darum sind kleine Vorarbeiten und Kontrollen zu Beginn stets hilfreich. Wenn Sie sich die Aufstellung ansehen, wird Ihnen gewiss ein „Mangel” sofort auffallen: Es existieren keine Überschriften. Das sollte als erstes behoben werden. Fügen Sie also eine leere Zeile ein und geben Sie den Spalten einen Namen. Wir verwenden Name, Vorname, Fraktion.
Und es ist wichtig, dass die Konsistenz der Daten vorhanden ist. Es darf keine leere Zeile und soll keine einzige leere Zelle im Datenbereich geben. Bei wenigen Zeilen ist das ja noch gut überschaubar, bei über 600 Zeilen und fast 2.000 Zellen ist das einfach nur ermüdend und dadurch fehlerträchtig. Die Arbeit überlassen wir gerne Excel:
- StrgPos1, um zu A1 zu gelangen
- StrgA, damit der gesamte Tabellenbereich markiert wird
- Merken Sie sich die Zeilennummer der untersten Zeile
- Klicken Sie irgendwo hin, damit die Markierung mehrerer Zellen aufgehoben wird.
- StrgEnde, und vergleichen Sie die die jetzige Zeile mit der vorher gemerkten.
- Falls ein Unterschied besteht, prüfen Sie nach, ob in den Zeilen unterhalb der zuerst gemerkten bis zur jetzt markierten Daten existieren, die zur Aufstellung gehören. Erforderlichenfalls löschen Sie die Leerzeilen.
Damit haben Sie auf jeden Fall erreicht, dass dort alle Daten ohne Leerzeilen in einem zusammenhängenden „Block” stehen, was eine unabdingliche Voraussetzung ist. Bleibt noch die Prüfung auf einzelne leere Zellen, die unerwünscht sind und eine Auswertung verfälschen können:
- Geben Sie in irgend eine leere Zelle außerhalb des Daten-Bereichs beispielsweise folgende Formel ein:
=ZÄHLENWENN(A1:A643; "")
und das Ergebnis sollte eine Null sein. - Ist es eine höhere Zahl, dann sollten Sie die Zellen suchen und erforderlichenfalls mit einem Wert füllen. Das geht dann übrigens sehr schön über F5, Inhalte… und dann die Auswahl Leerzellen.
Bereinigung der Daten
Diese Daten-Tabelle, Stand Mitte 2015, enthält auch ausgeschiedene Mitglieder des Bundestages. Diese sind bei der Fraktionszugehörigkeit an letzter Stelle mit einem Stern gekennzeichnet. Da hier (der Übung halber) nur mit (zu jener Zeit) aktiven Parlamentariern gearbeitet werden soll, müssen diese ehemaligen Angehörigen des Bundestages aus der Liste entfernt werden. Wir gehend dabei so vor:
- Sorgen Sie dafür, dass eine beliebige Zelle im Daten-Bereich markiert ist
- Menü Daten, Schaltfläche Filtern
- In C1 auf ▼ Klicken und versuchsweise im Feld Textfilter einen * eingeben
- Der Lerneffekt: Alles wird ausgewählt, nichts gefiltert. Darum im nächsten Anlauf bitte ~* in das Textfeld eingeben; die Tilde (~) bewirkt, das der Stern nicht als Joker für ein beliebiges Zeichen gewertet wird sondern das Zeichen an sich sucht.
- Kontrollieren Sie nun, ob wirklich nur die ausgeschiedenen Mitglieder im gefilterten Bereich unterhalb der Überschrift sichtbar sind.
- Nun die Daten der „Sternchen”-Fraktionen löschen. Das geht ganz gut, wenn Sie auf den obersten Zeilenkopf der betroffenen Daten Klicken und die Markierung bis zur letzten Zeile nach unten ziehen. Dann Rechtsklick und im Kontextmenü den Punkt Zeile löschen wählen und Klicken. Achtung, die Zeilen müssen gelöscht werden, ein „normales” löschen würde nur die Zellen leeren und die Zeilen ohne Inhalt stehen lassen. – Je nach Excel-Version gibt es auch bequemere Wege, dieser ist aber in jedem Fall zielführend.
- Abschließend noch einmal auf die Schaltfläche Filtern Klicken, um den Filter zu deaktivieren und alle übrig gebliebenen Datensätze anzuzeigen.
Kundennummern kreieren
Eine Kundennummer gehört einfach dazu. Allein schon, um Kunden mit dem gleichen Namen (aber anderer Adresse) auseinander halten zu können. Und überhaupt, auch für einen SVERWEIS() bietet sich das einfach an. Natürlich könnten Sie einfach fortlaufende Nummern vergeben, aber das ist zu einfach 😎 . So ein wenig soll der Zufall eine Rolle spielen. Dazu kommt die Vorgabe, dass die tiefste Kundennummer 1010 sein soll, nach oben hin der vierstellige Bereich nicht überschritten werden soll. Aber es wäre schon gut, wenn keine allzu großen Lücken in der Reihenfolge wären.
„Überangebot” erstellen
Dazu stellen Sie erst einmal fest, wie viele Datensätze jetzt noch in der Liste sind. Wegen des Löschens sind es ja weniger geworden. In unserer Datenbank sind es 631 plus Überschrift. Wenn Sie da round about die Hälfte drauflegen, dann haben Sie erst einmal genügend Reserve. Aufgerundet sind das dann 1.000 Nummern. Wofür aber? Nun ja, wenn Sie per Zufallsfunktion genau 631 Kundennummern generieren würden, dann wären mit hoher Sicherheit Dubletten dabei. Und das darf natürlich nicht sein.
Damit auch einige Lücken in den Kundennummern sein werden, lassen Sie sich 1.000 Zufallszahlen aus dem Zahlenbereich 1.010 bis 2272 (entspricht 631*2+1010 als Startwert) generieren. Dazu erstellen Sie (erforderlichenfalls) ein neues, leeres Tabellenblatt und schreiben Sie in Zelle A1 diese Formel: =ZUFALLSBEREICH(1010; 631*2+1010). – Hinweis: Sollte bei dieser Formel eine Fehlermeldung erscheinen, dann hilft Ihnen gewiss dieser Beitrag weiter. Suchen Sie nach der Überschrift #NAME! – Fehler.
Diese Zelle kopieren Sie anschließend in die Zwischenablage. Gehen Sie nun zu A1000, StgShift↑ und direkt danach Enter. Sofort wird diese Formel in jede einzelne Zelle des markierten Bereichs kopiert. Lassen Sie den Bereich markiert oder markieren Sie ihn neu. Kopieren Sie nun diese 1.000 Zellen und fügen Sie den Inhalt Als Wert wieder ein. Dadurch wird verhindert, dass sich die Werte stetig (bei jeder Neuberechnung des Blattes) verändern können. Selbstredend können Sie auch einen beliebigen anderen Weg gehen, um die Formelergebnisse in einen unveränderbaren Wert umzuwandeln.
Nur Unikate
Im nächsten Schritt sollen die Dubletten entfernt werden, weil ja jede Kundennummer nur ein Mal vorkommen darf. Dazu irgendwo in die Daten Klicken, Menü DATEN und in der Gruppe Datentools Duplikate entfernen nutzen. Sofort nach der Bestätigung wird Ihnen mitgeteilt, wie viele Duplikate entfernt worden sind. Es könnte knapp geworden sein. Also prüfen Sie bitte nach, welches nun die letzte Datenzeile ist. Wenn sie unterhalb 631 liegt, dann versuchen Sie es noch einmal oder erhöhen Sie im kommenden Versuch die entsprechenden Werte.
Gezielte Auswahl
Prinzipiell könnten Sie nun ab Zeile 632 alles löschen und die verbleibenden Daten bei den Kundennummern im ersten Arbeitsblatt per copy/paste eintragen. Um der Sache aber ein wenig mehr Würze zu geben, sollen nur die tieferen (kleineren) Kundennummern verwendet werden, damit die Lücken möglichst klein sind. Also: die eben generierten Zahlen aufsteigend ordnen und davon die ersten 631 für die Weiterverwendung behalten.
Das führt auch prinzipiell zum Ziel hat aber den Nebeneffekt, dass die Nummerierung irgendwie zur alphabetischen Ordnung der Liste der Abgeordneten zugeordnet scheint. Das ist einfach zu auffällig, das „riecht” nach konstruiert. Aber so schlecht ist dieser Ansatz doch erst einmal gar nicht. Ergo: Doch durchführen?
Jein. Kürzen Sie die Liste nun erst einmal auf die niedrigsten 631 Kundennummern. Dann schreiben Sie in B1 (oder die nächste freie Spalte) diese Formel: =Zufallszahl() und anschließend ein Doppelklick auf das Ausfüllkästchen. Wie in ähnlicher Form gehabt kopieren Sie diese Spalte und fügen den Inhalt Als Wert an gleicher Stelle wieder ein. Jetzt sortieren Sie die beiden Spalten nach den Werten in der rechten Spalte je nach Geschmack auf- oder absteigend.
Ergänzung: Es gibt im Netz verschiedene Formel-Lösungen, welche meist die RANG()-Funktion einbinden. Bei Interesse sollten Sie danach suchen.
Endspurt
Jetzt ist es fast geschafft. Und es stellt sich die Frage, wohin mit den Kundennummern? Ins erste Blatt, klar. In Spalte D, weil es die erste freie Spalte ist? Ach nein, das sieht so nach „Gewollt und nicht gekonnt” aus. Also fügen Sie vor Spalte A eine neue Spalte ein und als Überschrift geben Sie KdNr. oder etwas nach Ihrem Geschmack ein. Anschließend kopieren Sie die eben neu sortierten Kundennummern in die neue Spalte A des ersten Blattes. Danach können Sie in Blatt 2 Spalte B löschen. Vorsichtshalber lassen Sie die Spalte A erst einmal bestehen, löschen kann man sie ja immer noch … Damit ist die gesamte Arbeit erledigt. Sie haben eine „saubere” KundenListe, nach Namen sortiert.
Diese Liste, aber ausschließlich diese bzw. dieses Blatt werden Sie an verschiedenen Stellen des Blogs als Basis verwenden. Darum ausnahmsweise etwas Redundanz: Die verschiedenen Stadien bis zu diesem Punkt können Sie in dieser Mappe nachvollziehen, das ausgegliederte Ziel, die endgültige Kundentabelle ist hier im Blog zum Download hinterlegt.
[NachObenLetzte Verweis=„ML: Umarbeitung BundestagsListe”]