Die 5 wichtigsten Excel-Makros für den Alltag

Wenn Sie bisher noch keine Excel-Makros benutzen, erstellen, aktivieren oder auch mal deaktivieren, dann verzichten Sie auf eines der stärksten Feature von Microsoft Excel. Makros sparen Ihnen Zeit und ersparen es Ihnen die immer gleichen, stupiden Excel-Aufgaben abarbeiten zu müssen. Keine Angst: Sie müssen kein Programmierer sein oder die Grundlagen von Visual Basic Applications (VBA) beherrschen, um ein eigenes Excel-Makro zu erstellen oder zu aktivieren. Mit Excel ist das genauso einfach wie das manuelle Eintippen aller verwendeten Befehle. Wir zeigen Ihnen im Folgenden, wie Sie die fünf häufigsten Aufgaben in Excel mit Makros automatisieren.

Makro-Grundlagen

Makros müssen „aufgenommen“ werden. Um das zu tun, klicken Sie auf „Makro aufzeichnen“ im Reiter „Ansicht, Makros“. Im neu geöffneten Fenster geben Sie nun die folgenden Informationen an und klicken anschließend auf OK.

Makroname – Das erste Zeichen muss ein Buchstabe sein, gefolgt von einer Reihe von Buchstaben, Zahlen und Unterstrichen Ihrer Wahl. Andere Zeichen werden nicht akzeptiert.

Tastenkombination – Strg+J und Strg+M sind frei verfügbar. Wenn Sie eine andere Tastenkombination wählen, überschreiben Sie damit das Makro, das dieser Kombination zuvor zugewiesen war.

Makro speichern in – Makros, die unter „Diese Arbeitsmappe“ oder „Neue Arbeitsmappe“ gespeichert werden, funktionieren auch nur in diesen speziellen Arbeitsmappen. Um das Makro in allen Arbeitsmappen und Tabellenblättern nutzen zu können, wählen Sie als Speicherort „Persönliche Makroarbeitsmappe“.

Beschreibung – Legen Sie hier bei Bedarf eine Beschreibung der Makro-Funktion fest.

Weil Makros dazu dienen, wiederkehrende Aufgaben automatisiert auszuführen, sollten Sie auf möglichst viele verschiedene Arbeitsmappen anwendbar sein. Daher können Sie nicht im Vorfeld die Zelladressen festlegen (C1, D5, usw.), es sei denn, alle Ihre Tabellen sind identisch aufgebaut – also alle Zahlen in allen Spalten und Reihen stimmen immer überein. Damit das Makro in verschiedenen Tabellen mit ähnlichen Daten funktioniert, müssen Sie stattdessen bei der Eingabe die Pfeiltasten verwenden (damit spielen die Zelladressen keine Rolle) und stets in der Position A1 beginnen.

1. Organisieren, formatieren und Daten importieren

Daten aus anderen Programmen liegen oft im Format TSV (Tabulatoren-getrennt) oder CSV vor (Komma- oder Trennzeichen-getrennt). Stellen Sie sich vor, Sie bekommen von dieser Art von Daten zwei Dutzend pro Monat zugesendet und müssen sie nun verwalten, unerwünschte Daten daraus löschen und anschließend nach Kunden- oder Firmenname sortieren. Manuell dauert eine solche Arbeit gut und gerne mehrere Stunden. Ein Makro schafft das innerhalb von Sekunden.

Öffnen Sie zunächst die entsprechende CSV-Arbeitsmappe, folgen Sie der Anleitung oben zum Anlegen eines neuen Makros und zeichnen Sie dann die folgenden Tasteneingaben auf.

1. Setzen Sie Ihren Cursor in die Zelle A1. Halten Sie Strg gedrückt und markieren Sie die Buchstaben über den Spalten, die Sie eliminieren möchten. Lassen Sie Strg dann wieder los und wählen Sie in der Menüleiste Start -> Löschen -> Blattspalten löschen.

2. Halten Sie Strg wieder gedrückt und markieren Sie die restlichen, vorhandenen Spalten (in unserem Beispiel A und B). Wählen Sie dann unter Start -> Format -> Spaltenbreite und setzen Sie die Spaltenbreite auf 42. Bestätigen Sie mit OK.

3. Markieren Sie alle Daten in Ihrem Tabellenblatt

Wählen Sie nun Start -> Sortieren & Filtern -> Benutzerdefiniertes Sortieren. Im neu geöffneten Fenster unter Spalte wählen Sie die Option „Sortieren nach Spalte A“ (wenn sich hierin die Firmennamen Ihrer Kunden befinden). Bei „Sortieren nach“ stellen Sie „Werte“ ein, bei „Reihenfolge“ dann noch „A bis Z“.

4. Klicken Sie in der grünen Leiste unten links auf das Quadrat-Symbol, um die Aufnahme des Makros zu stoppen. Speichern Sie die Arbeitsmappe als Excel-Datei. Öffnen Sie dann wieder die CSV-Datei, wechseln Sie auf den Reiter „Ansicht“ -> „Makros“, wählen Sie dort Ihr eben angelegtes Makro aus der Liste aus und klicken Sie auf „Ausführen“. Damit wird Ihr gesamtes Arbeitsblatt in Sekunden organisiert und sortiert.

2. Namen aus einer Spalte in zwei Spalten aufteilen

Oft erhalten Sie Listen, in denen etwa die Namen der Kunden als Vor- und Nachname in einer einzigen Spalte stehen. Für viele weitere Verarbeitungszwecke benötigen Sie den Vornamen aber getrennt vom Nachnamen. Mit dem passenden Makro schaffen Sie nicht nur das automatisiert und in Sekundenschnelle, sondern sortieren sie gleich noch, passen die Spaltenbreite an und lassen sich die Anzahl der Listeneinträge anzeigen. Öffnen Sie also die entsprechende Excel-Liste, benennen und definieren Sie Ihr Makro wie oben beschrieben und nehmen Sie dann folgende Tastenreihenfolge auf:

1. Begeben Sie den Cursor in die erste Spalte, markieren Sie alle dort befindlichen Einträge mit Strg + A. Wählen Sie in der Menüleiste Daten -> Text in Spalten. Im neu geöffneten Fenster klicken Sie auf „Getrennt“, dann unten auf „Weiter“. Auf der zweiten Seite wählen Sie in der Liste das Zeichen aus, mit dem Vor- und Nachname in Ihrer Liste getrennt werden. Wenn das beispielsweise Leerzeichen sind, dann setzen Sie also ein Häkchen bei „Leerzeichen“ und klicken unten auf „Weiter“. Auf der letzten Seite klicken Sie nun nur noch auf „Fertig stellen“.

2. Markieren Sie wieder die Daten in der ersten Spalte mit Strg + A. Klicken Sie dann unter „Start“ auf „Sortieren & Filtern“ -> „Benutzerdefiniertes Sortieren“. Im Feld „Sortieren nach“ wählen Sie die Option „Spalte B“ (Nachnamen). Klicken Sie dann oben auf „Ebene hinzufügen“ und wählen Sie „Spalte A“ (Vornamen) im Feld „Dann nach“. In den Feldern „Sortieren nach“ und „Reihenfolge“ lassen Sie die Standards „Werte“ und „A bis Z“ stehen. Dann klicken Sie auf OK.

3. Begeben Sie sich wieder in Zeile A1. Drücken Sie dann Shift+Pfeiltaste rechts, um die Zeilen A1 und B1 zu markieren. Klicken Sie auf „Format“ -> „Spaltenbreite“ und setzen Sie die Spaltenbreite auf 15. Klicken Sie auf OK.

4. Gehen Sie zurück in Zeile A1. Wählen Sie zwei Mal hintereinander „Start“ -> „Einfügen“ -> „Blattzeilen einfügen“. Tippen Sie in die leere Zeile A1 „Namen insgesamt“. Benutzen Sie die rechte Pfeiltaste, um ins Feld B1 zu navigieren, wo Sie diese Formel eingeben: „=ANZAHL2(markieren Sie mit Strg und Pfeiltaste unten den Bereich der Liste, in dem sich Namen befinden)“. Bestätigen Sie die Eingabe mit Enter – Sie wissen nun, wie viele Namenseinträge sich insgesamt in Ihrer Liste befinden.

5. Stoppen Sie die Makroaufzeichnung und speichern Sie das Makro unter einem einprägsamen Namen ab.

3. Spalten aufteilen und für zweite Vornamen aufbereiten

In Excel ist es einfach eine Spalte mit Namen in zwei separate Namensspalten aufzuteilen. Doch was, wenn viele Einträge in Ihrer Liste noch einen zweiten Vornamen führen? Das folgende Makro extrahiert zweite Vornamen, Initialen, etc., und vereint sie wieder mit den Vornamen. So entsteht eine Spalte mit einem oder mehreren Vornamen und einer separaten Nachnamenspalte. Öffnen Sie eine passende Liste mit erstem und zweitem Vornamen und Nachnamen, benennen und definieren Sie Ihr Makro und zeichnen Sie folgende Tasteneingaben auf:

1. Markieren Sie zunächst alle Daten in Ihrer Namensspalte mit Strg + A. Wählen Sie dann unter „Daten“ -> „Text in Spalten“. Im neuen Fenster klicken Sie auf „Getrennt“ -> „Weiter“, setzen dann ein Häkchen bei der Option „Leerzeichen“ -> „Weiter“ und klicken im letzten Fenster auf „Fertig stellen“. Sie haben nun eine Namenstabelle mit 3 Spalten.

2. Markieren Sie nun alle Daten in Ihren drei Spalten und wählen Sie „Start“ -> „Sortieren & Filtern“ -> „Benutzerdefiniertes Sortieren“ -> „Sortieren nach Spalte C“. Drücken Sie OK

3.  Drücken Sie Strg + Pos1 und dann zweimal die Pfeiltaste nach rechts. Anschließend drücken Sie auf die Ende-Taste, zweimal auf die Pfeiltaste nach unten und anschließend einmal auf die Pfeiltaste rechts – das wählt die erste leere Zelle in Spalte C und anschließend die angrenzende Zelle in Spalte D aus. Geben Sie dort „STOP“ ein, drücken die Pfeiltaste nach oben, Ende, Pfeiltaste nach unten, Pfeiltaste nach oben. Geben Sie diese Formel ein: =A1&” “&B1 und drücken Enter und dann Pfeiltaste nach oben.

Benutzen Sie die Pfeiltasten zur Navigation, so funktioniert das Makro auf allen ähnlichen Arbeitsmappen.

4. Drücken Sie Strg + C und dann die Pfeiltaste nach unten. Halten Sie Shift gedrückt und drücken auf Ende, Pfeiltaste nach unten, Pfeiltaste nach oben und Enter (das kopiert die Formel). Drücken Sie einmal auf die Pfeiltaste nach oben, halten Shift gedrückt und drücken auf Ende, dann die Pfeiltaste nach unten und abschließend die Pfeiltaste nach oben (das hebt den Bereich bis zum STOP hervor).

5. Drücken Sie Strg + C, wählen Sie Einfügeoptionen -> Werte. Drücken Sie auf Esc, Strg + Pos1 und zweimal die Pfeiltaste nach rechts. Halten Sie Shift gedrückt, drücken auf Ende, Pfeiltaste nach unten, Strg + C, Pfeiltaste nach rechts und Eingabe um die Nachnamen zu kopieren. Drücken Sie die Pfeiltaste nach rechts und dann Shift + Pfeiltaste nach rechts.

Kopieren und fügen Sie die Formel ein, um zusätzliche Spalten zu entfernen.

6. Wählen Sie Löschen -> Blattspalten löschen aus. Drücken Sie Strg + Pos1. Speichern Sie das Dokument ab, öffnen das vorherige Dokument und führen das Makro aus.

4. Sich wiederholender Text

Falls Sie die gleiche Information 10x pro Tag eingeben, sollten Sie sich dafür ein Makro zulegen. Selbst wenn diese Information kurz ist, gibt ein Makro diese in Sekunden ein und garantiert dazu absolute Genauigkeit. Diese Makro fügt die Informationen Ihrer Firma oben in Ihren Arbeitsmappen ein und fügt das aktuelle Datum hinzu. Öffnen Sie dafür eine neue Arbeitsmappe, benennen und definieren Sie Ihr Makro und führen dann diese Eingaben durch.

1. Drücken Sie Strg + Pos1. Halten Sie Shift gedrückt und drücken zudem zweimal auf die Pfeiltaste nach rechts. Wählen Sie Start -> Format -> Zellen formatieren -> Datum. Wählen Sie ein Datumsformat aus der Liste und klicken auf OK. Drücken Sie die Pfeiltaste nach unten zweimal.

2.Geben Sie die sich wiederholende Information ein und drücken die Eingabetaste am Ende jeder Zeile. Drücken Sie die Pfeiltaste nach unten. Wählen Sie Entwicklertools-> Aufzeichnung beenden. Löschen Sie alles, trennen Sie die Zellen A1-A3 voneinander und führen das Makro aus. Speichern Sie abschließend die Arbeitsmappe.

Erstellen Sie eine Kopfzeile mit dem aktuellen Datum, um monotones Tippen zu vermeiden.

5. Löschen Sie leere Zellen

Eine mit leeren Zellen gefüllte Arbeitsmappe ist nicht zu managen, sortieren und zu berechnen. Der erste Schritt ist es dem Makro aufzutragen nur die Daten auszuwählen, dann die leeren Reihen auszuwählen und zu löschen. Sobald das erledigt ist, können Sie Ihre Daten einfach organisieren.

Öffnen Sie eine Datei mit leeren Zellen, benennen und definieren Sie Ihr Makro und nehmen dann diese Eingaben auf.

1. Drücken Sie Strg + Pos1. Anmerkung: Strg + A wählt nicht alle Daten aus, wenn leere Reihen in der Datei sind, aber dieses Makro schon.

2. Wählen Sie Start –> Einfügen -> Blattzeilen. Drücken Sie auf Ende, Pfeiltaste nach unten, Pfeiltaste nach rechts, Ende und Pfeiltaste nach oben. Drücken Sie Strg + Pos1, Shift + Pfeiltaste nach rechts, Strg + Shift + Pfeiltaste nach rechts. Dadurch wird der Datenbereich ordnungsgemäß ausgewählt.

Entfernen Sie leere Zellen.

3. Wählen Sie Start -> Suchen und Auswählen -> Inhalte auswählen (oder drücken Sie Strg + G, Alt + S). Klicken Sie auf Leerzellen -> OK und alle Leerzellen erscheinen in grau. Wählen Sie dann Löschen -> Zellen löschen -> Zellen nach oben verschieben –> OK und die Leerzellen verschwinden. Drücken Sie Strg + Pos1, wählen Löschen –> Blattzeilen löschen, um die zusätzliche Spalte, die wir eingefügt haben zu löschen.

4. Aufzeichnung beenden. Machen Sie alle Schritte rückgängig und führen das Makro aus. Speichern Sie die Arbeitsmappe.

So rechnen Sie mit Excel-Funktionen

Excel-Tipp: Pivot-Tabellen in Excel erstellen – so geht’s

Video-Kurs “Excel Makros: Automatisiere deine Arbeit” bei Udemy ansehen