Daten aufbereiten

5

5 Schlechter Datenaufbau

Eine Zeile zu löschen oder Daten in eine Zelle zu schreiben stellt normalerweise kein Problem dar. Schwierig wird es jedoch, wenn es sich um mehrere Hundert Zellen, beziehungsweise Zeilen und Spalten handelt. Dabei sollte man schon die Assistenten von Excel gut kennen.

5.1 Leerzeilen füllen

Wenn Sie Pivottabellen kennen, dann kennen Sie auch die Einstellung "Alle Elementnamen wiederholen". Somit werden die "Lücken" ausgefüllt und man die Spalte sortieren und filtern:

Abbildung 5.1 Alle Elementnamen wiederholen

Was tut man allerdings, wenn man so eine Liste in Excel erhält? Jede Zelle markieren und herunterziehen? Bis zur nächsten Überschrift? Das wäre zu mühsam und zu zeitaufwändig. Auch für dieses Problem gibt es mehrere Lösungen:

5.1.1 Assistenten

Man kann im ersten Schritt die gesamte Spalte mit den Leerzellen markieren. Der Assistent "Inhalte auswählen", den Sie in Start / Suchen und Auswählen / Inhalte auswählen finden, bietet die Möglichkeit nur die Leerzellen auszuwählen. Interessanterweise werden dabei nicht alle Leerzellen ausgewählt, sondern nur die Leerzeilen des verwendeten Bereichs:

Abbildung 5.2 Die leeren Zellen werden markiert.

Anschließend verschiebt man den Bildschirm so, dass die aktive Zelle ins Zentrum kommt.

Hinweis

Die Tastenkombination [Strg] + [Rückschritt] leistet hierfür gute Dienste.

Das heißt die oberste Zelle, die einen weißen Hintergrund hat, sollte sichtbar sein. Dort trägt man = und den Namen der Zelle darüber ein, beispielsweise

=C2

Abbildung 5.3 Ein Bezug auf die darüberliegende Zelle

Und dieser Bezug, das heißt: diese Formel, wird nicht mit [Enter], sondern mit [Strg] + [Enter] auf alle Zellen übertragen. Nun steht in jeder Zelle eine Formel. Die gesamte Spalte kann markiert, kopiert und mit "Inhalte einfügen als Werte" auf alle Zellen übertragen werden.

Abbildung 5.4 Das fertige Ergebnis

Analog zum Markieren kann auch gefiltert werden. Man kann alle leeren Zellen filtern, die aktive Zelle ist auch hier wieder die oberste Zelle. Nun kann mit einer Formel, beispielsweise

=C2

Bezug genommen werden. Diese Formel muss dann nach unten gezogen werden.

Oder: man markiert alle leeren, gefilterten Zellen, stellt über die aktive Zelle den Bezug auf die Zelle darüber her und bestätigt das Ergebnis mit [Strg] + [Enter].

Und analog zum ersten Verfahren kopiert und als Wert eingefügt werden.

5.1.2 Formeln

Natürlich kann man das Problem auch durch geschickte Formeln lösen. Wenn die Zelle der entsprechenden Spalte gefüllt ist, soll ihr Wert übernommen, das heißt: wiederholt, werden. Wenn die Zelle dagegen leer ist, soll der über der Formelzelle befindliche Wert verwendet werden. Und das Ergebnis wird nach unten gezogen und in Werte umgewandelt:

Abbildung 5.5 Eine WENN-Funktion genügt zur Lösung des Problems

5.1.3 Power Query

Und Power Query stellt den Assistenten "Ausfüllen / Nach unten" zur Verfügung. Einfacher geht es nicht. Allerdings müssen die Daten nach Power Query geladen werden:

Abbildung 5.6 Ausfüllen - einfacher geht es nicht

5.2 Leerzeilen entfernen

In Sinne einer Datenbank sollte in einer Liste jeder Datensatz gefüllt sein. Das heißt: in der jeder Zeile einer Datenbank sollte mindestens in einer Spalte ein Wert stehen. Ist dies nicht der Fall, liegen Leerzeilen vor. Während in intelligenten Tabellen dies kein großes Problem darstellt, kann es bei Bereichen zu enormen Schwierigkeiten beim Sortieren, Filtern, Erstellen einer Pivottabelle, Erstellen eines Diagramms, ... kommen. Leerzeilen sollten entfernt werden.

Abbildung 5.7 Leerzeilen sind unangenehm.

5.2.1 Assistenten

Filtern und Sortieren löst dieses Problem. Hierzu muss allerdings der gesamte Bereich markiert werden. Dies kann mit der Tastenkombination [Umschalt] + [Strg] + [Ende] erledigt werden, wenn der Cursor sich in der ersten Zelle befindet. Oder - wenn die Liste die einzige auf dem Tabellenblatt ist - kann man auch das ganze Blatt markieren.

Wird die Liste aufsteigend oder absteigend sortiert, stehen die Leerzeilen immer unterhalb des Bereichs.

Natürlich könnte man auch die Leerzeilen (über eine beliebige Spalte) filtern; anschließend die Zeilen markieren und löschen ([Strg] + [-]).

5.2.2 Formeln

Selbstverständlich kann mit Hilfe einer WENN-Funktion die Leerzeile ermittelt werden. Entweder über

=WENN(B2="";"x";"")

oder

=WENN(ISTLEER(B2);"x";"")

oder die ganze Zeile:

=WENN(TEXTKETTE(B2:F2)="";"x";"")

Hinweis

Leider kann man diese Formel nicht mit einem Doppelklick auf das Kästchen herunterziehen. Eben weil in der Liste Leerzeilen stehen. Mit [Strg] + [Ende] wird die letzte Zelle ermittelt. Trägt man nun in das Namensfeld den Bereich ein (beispielsweise A2:A35114), editiert die erste Zelle mit [F2], kann man anschließend mit [Strg] + [Enter] die Formel auf alle Zellen übertragen.

5.2.3 Power Query

Wurde die gesamte Liste in Power Query abgerufen, kann man dort die Leerzeilen - ebenso wie in Excel - durch Filtern eliminieren.

5.3 Zeilen mit bestimmten Daten eliminieren

Angenommen, ein System liefert eine Liste an Daten, bei denen bestimmte Datensätze entfernt werden sollen. Oder eine Person hat eine Liste angelegt, bei denen in - beispielsweise in Zwischenzeilen - Summen stehen. Oder andere Informationen, die nicht benötigt werden - ja, die sogar stören würden. Dann müssen Sie sich ein Merkmal suchen, anhand dessen Sie diese Zeilen identifizieren können.

Abbildung 5.8 Zwischenzeilen, die eliminiert werden sollen.

5.3.1 Assistenten

Hat man solche Zeilen erkannt, kann man sie durch Filtern herauslösen. Entweder leere Zellen oder bestimmte Informationen.

Beim Filtern stehen zwei Möglichkeiten zur Verfügung: entweder man filtert die überflüssigen Zeilen und löscht sie oder man lässt sich mit Hilfe des Filters nur die wichtigen Daten anzeigen, markiert sie und kopiert sie an eine andere Stelle.

Hinweis

Wird diese Aktion mehrmals benötigt, leistet der Spezialfilter gute Dienste.

Abbildung 5.9 Die Zwischenzeilen wurden eliminiert.

5.3.2 Formeln

Natürlich kann man mit einer oder mehreren WENN-Funktionen Zeilen, die nicht benötigt, kennzeichnen. Beispielsweise so:

=WENN(A1="";"x";"")

=WENN(TEXTVERKETTEN("";WAHR;A2:D2)="";"x";"")

=WENN(LINKS(A3;6)="Zahlen";"x";"")

Und danach kann man filtern.

5.3.3 Power Query

Die Power Query-Lösung gestaltet sich ähnlich wie die Lösungsansätze per Formel und per Filtern. Auch dort kann nach einer geeigneten Spalte Ausschau gehalten werden und über ein bestimmtes Kriterium gefiltert werden. Oder beispielsweise durch Zuhilfenahme einer benutzerdefinierten Funktion, beispielsweise, wenn in der ersten Spalte ODER in der zweiten Spalte kein Text vorhanden ist, dann kennzeichne die Hilfsspalte durch das Zeichen "x":

=if [Spalte1] = null or [Spalte2] = null then "x" else null

5.4 Transponieren

Manchmal werden Listen so unglücklich geliefert, dass Zeilen und Spalten vertauscht sind. Das heißt: Man möchte gerne die Überschrift als Zeilenbeschriftung und die die Nummerierung an der Seite als Überschrift haben. Das heißt: die Liste an der Diagonalen spiegeln. Der Begriff aus der Mathematik für das Vertauschen von Zeilen und Spalten einer Matrix heißt Transponieren.

Hinweis

Der Begriff "Transponieren" wurden nicht - wie viele fälschlicherweise glauben, der Musik entlehnt.

Abbildung 5.10 Die Liste muss transponiert werden.

5.4.1 Assistenten

Sicherlich kennen Sie die Excellösung mit dem Assistenten: Man kopiert die Liste (beispielsweise [Strg] + [A]), kopiert die Liste, wählt eine andere Zelle außerhalb des gewählten Bereichs und wählt nun den Befehl Inhalte einfügen / Transponieren:

Abbildung 5.11 Die Liste wird transponiert.

Der Befehl "transponieren" befindet sich auch im Kontextmenü:

Abbildung 5.12 Das Kontextmenü hilft beim Transponieren.

Hinweis

Im Dialog können Sie weitere Einstellungen vornehmen. Sollen beispielsweise die Formeln in Werte umgewandelt werden oder ohne Formate transponiert werden, oder ähnliches, kann die entsprechende Option gewählt werden.

5.4.2 Formeln

Die Formel zum Transponieren lautet MTRANS, also...