Excel-Tipps: Jede Woche neu

Excel-Tipp 25: Daten anders aufbereiten

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
René Martin hat in seiner Arbeit als Trainer zahllose Excel-Tipps gesammelt. Mit diesen Tipps lassen sich typische Anwendungsaufgaben schneller und eleganter lösen oder Fallstricke vermeiden. Dieses Wissen gibt er Ihnen in kleinen Häppchen Woche für Woche weiter. Seien Sie gespannt, welchen kleinen Leckerbissen er in der neuen Woche für Sie bereithält. Und mehr als 10 Minuten brauchen Sie nicht zu investieren.
07:03
  Lesezeichen setzen

Transkript

Das Leben könnte so schön sein. Kennen Sie solche Sätze, die mit einem Seufzer beginnen oder beendet werden? Als Excel-Anwender, Excel-Trainer und auch Excel-Programmierer kenne ich solche Äußerungen zur Genüge. Auch ich, René Martin, bekomme regelmäßig Listen, die man so einfach nicht brauchen kann. Und manche dieser Listen sind so vertrackt, dass man sie einfach nicht mittels Sortieren oder Filtern die Daten erhält, die man gerne hätte. Also muss man ein bisschen tricksen. Ich zeige Ihnen mal so einen Fall, der mir vor einer Weile untergekommen ist und zeige Ihnen, wie ich es gelöst habe. Schauen Sie zu. Das ist doof. So hätte es ich gerne. Das ist doof bedeutet, eine Liste, die so aus einem System kommt, kann ich so nicht weiter verarbeiten. Sie sehen jeweils Vorname, Nachname, Straße, Postleitzahl, Ort, Vorname, Nachname, Straße, Postleitzahl, Ort, Vorname, Nachname und so weiter untereinander, da kann ich weder Sortieren, Filtern noch sonst irgendwas weiter bearbeiten. Die soll schon vernünftig in entsprechende Spalten eingetragen werden. Nun, sowas kann man natürlich programmieren oder so etwas kann man auch mit Formeln erzeugen. Dazu brauche ich eine Vorüberlegung. Ich hätte ganz gern die Bianca aus der Zelle A1, ich schreib sie mal hierhin, 1 an der Stelle. Nebendran hätte ich gerne Nummer 2, ihren Nachnamen, ihre Straße, Postleitzahl und den Ort. Eine Zeile tiefer, ich mach's ein bisschen kleiner, hätte ich ganz gerne den Fridolin, das heißt hier die Nummer 6, die Zeile Nummer 6, 7, 8, 9, 10, und dann geht's weiter wieder mit 11, 12, 13 und so weiter. Das heißt, so ein System brauche ich, diese Zahlen hätte ich ganz gerne. Nun, um die Zahlen zu generieren, könnten Sie, wissen Sie was, ich mache mal hier weiter, "=" einerseits die Funktion ZEILE verwenden. ZEILE liefert mir eine Zeilenummer. Das heißt, hier die 6 runtergezogen, die entsprechende Zeilennummer. Sie ahnen wahrscheinlich, worauf ich raus will. Rübergezogen, die gleiche Information. In die andere Richtung geht es auch mit der Funktion "SPALTE". SPALTE liefert mir die Spaltennummer, rübergezogen, eben diese Spalte, er zählt weiter, genau das brauche ich, runtergezogen, er zählt weiter. Jetzt muss man das Ganze nur noch geschickt zusammenzählen. Das heißt, Sie müssen nicht "=ZEILE()" schreiben, Sie können auch "=ZEILE(", auf eine Zelle klicken. Die Zelle A1 hat die Zeilenummer 1, rüberziehen, runterziehen, gleiches Spiel, er geht jetzt basierend von A1 weiter. Das heißt, wenn ich hier "=" die Zeilenummer verwende von A1, dann wird er mir beim Runterzählen, 2, 3, 4, ich brauche aber das Fünffache, das heißt, ich muss diese Zeilennummer mal 5 multiplizieren. Warum 5? Weil jeweils in Fünfer-Schritten, 1 plus 5 ist 6, plus 5 ist 11, plus 5 ist 16, in Fünfer-Schritten habe ich die Nummer. Sieht ja schon mal ziemlich gut aus. Das heißt, von der Nummer könnten wir jetzt hier hinten ein -4 abziehen und damit hätten wir ja schon mal die erste Spalte fertig. 1, 6, 11, 16. Klasse. Wenn ich rüberziehe, passiert natürlich noch nichts Aufregendes, da zählt er noch nicht weiter. Jetzt kommt meine zweite Funktion zur Hilfe. Jetzt addiere ich "+" noch die Spalteninformation. Man könnte natürlich jetzt zählen, Ich bin in Spalte L, das ist die zwölfte, -11 oder Sie machen auch hier wieder Spalte von dir da, Achtung, das ist die erste Spalte, ich brauche hier an der Stelle natürlich minus eine, also null Spalten dazu. "Enter". In der ersten Spalte passiert wieder nichts Aufregendes, das Gleiche wie vorhin, aber wenn ich es rüberziehe, Vergleich mit oben, genau die Information. Also, mit geschicktem Berechnen von Zeile und Spalte bekommen Sie die entsprechende Zellnummer zurück. Nun, wenn ich die weiß, kann ich das Ganze mit dem Text "A und das Ganze verketten. A1 runterziehen, rüberziehen, und damit habe ich den Zellnamen von der Zelle, die ich brauche. Na, und das kann ich jetzt wiederum mit der Funktion INDIREKT umwandeln in einen Bezug, "Enter" und das Ganze runterziehen, rüberziehen und Sie sehen, bei der Null natürlich weiter unten steht nichts mehr in der Liste, klappt hervorragend. So ähnlich habe ich es drüben auch gemacht, Ich möchte gerne mit der Funktion "BEREICH.VERSCHIEBEN" das ändern, das heißt, ich wechsle hier in die Zelle kopier mir nochmal diese Funktion von Zeile bis Spalte, minus eins hier raus, kopier das Ganze, "Esc", unterbreche die Eingabe, wechsle hier "=" in die Zelle, füge es ein, "Enter". habe ich wieder die Eins, runterziehen, rüberziehen. Um diese Eins baue ich nun mit der Funktion BEREICH.VERSCHIEBEN, BEREICH.VERSCHIEBEN eine Formel, die mir ausgehend von A1, absolut, Semikolon, zählt, wie viel Zeilen gehen wir nach unten und Achtung, ich möchte nicht eine Zeile anfangs, sondern ich möchte gern bei Null anfangen, das heißt, ich muss diese Zahl, Zeilen, nochmal -1, eins weniger rechnen, Also gehe mal bitte Null runter. Wie viel Spalten gehen wir nach rechts? Wir verschieben den Bereich um 0 Spalten nach rechts, Klammer zu. "Enter". Aha, und schon bekomme ich die Bianca. Und wenn ich das Ganze nun rüberziehe, sehen Sie das gleiche Ergebnis wie oben drüber, ich muss nur aufpassen, dass ich bei BEREICH.VERSCHIEBEN ausgehend von der Zelle 0 mache, dagegen bei der Funktion INDIREKT A und die Zeilenummer 1 zusammenbaue. Das heißt also, mit geschickten Zusammensetzen von Zeile, Spalte mit Funktionen BEREICH.VERSCHIEBEN oder INDIREKT bekommt man so ein Problem gelöst, mit reinen Formeln, wie Daten, die sehr schlecht aufgebaut sind, in eine vernünftige Form gebracht werden.