Erfahren Sie, wie Sie Excel-Makros verwenden, um langwierige Aufgaben zu automatisieren

Eine der leistungsfähigeren, aber selten genutzten Funktionen von Excel ist die Möglichkeit, sehr einfach automatisierte Aufgaben und benutzerdefinierte Logik in Makros zu erstellen. Makros bieten eine ideale Möglichkeit, um bei vorhersehbaren, sich wiederholenden Aufgaben Zeit zu sparen und Dokumentformate zu standardisieren - häufig, ohne eine einzige Codezeile schreiben zu müssen.

Wenn Sie neugierig sind, was Makros sind oder wie Sie sie erstellen, ist das kein Problem - wir werden Sie durch den gesamten Prozess führen.

Hinweis: Der gleiche Vorgang sollte in den meisten Versionen von Microsoft Office funktionieren. Die Screenshots können etwas anders aussehen.

Was ist ein Makro??

Ein Microsoft Office-Makro (da diese Funktionalität für mehrere MS Office-Anwendungen gilt) ist einfach VBA-Code (Visual Basic for Applications), der in einem Dokument gespeichert wird. Stellen Sie sich für eine vergleichbare Analogie ein Dokument als HTML und ein Makro als Javascript vor. Ein Makro kann ein Dokument auf die gleiche Weise manipulieren, dass JavaScript HTML auf einer Webseite manipulieren kann.

Makros sind unglaublich mächtig und können so ziemlich alles tun, was Ihre Vorstellungskraft heraufbeschwören kann. Als (sehr) kurze Liste von Funktionen können Sie ein Makro ausführen:

• Stil und Formatierung anwenden.
• Daten und Text manipulieren.
• Kommunikation mit Datenquellen (Datenbank, Textdateien usw.).
• Erstellen Sie völlig neue Dokumente.
• Jede Kombination in beliebiger Reihenfolge der oben genannten.

Erstellen eines Makros: Eine Erklärung anhand eines Beispiels

Wir beginnen mit Ihrer CSV-Datei für Gartensorten. Nichts Besonderes hier, nur ein 10 × 20-Satz von Zahlen zwischen 0 und 100 mit einem Zeilen- und einem Spaltenkopf. Unser Ziel ist es, ein gut formatiertes, präsentierbares Datenblatt mit einer Summensumme für jede Zeile zu erstellen.

Wie bereits erwähnt, ist ein Makro VBA-Code, aber eines der schönen Dinge in Excel ist, dass Sie sie mit der erforderlichen Null-Codierung erstellen / aufzeichnen können - wie wir es hier tun werden.

Um ein Makro zu erstellen, wählen Sie Ansicht> Makros> Makro aufzeichnen.

Weisen Sie dem Makro einen Namen (keine Leerzeichen) zu und klicken Sie auf OK.

Sobald dies erledigt ist, alles Ihre Aktionen werden aufgezeichnet - jede Zellenänderung, Bildlaufaktion, Größenänderung des Fensters, Sie nennen es.

Es gibt einige Stellen, an denen Excel den Aufzeichnungsmodus angibt. Sehen Sie sich das Makro-Menü an und stellen Sie fest, dass die Aufnahme-Aufnahme die Option für Aufnahmemakro ersetzt hat.

Der andere befindet sich in der rechten unteren Ecke. Das Symbol "Stopp" zeigt an, dass es sich im Makromodus befindet. Wenn Sie hier drücken, wird die Aufnahme gestoppt. Wenn Sie sich nicht im Aufnahmemodus befinden, wird die Schaltfläche "Makro aufzeichnen" angezeigt..

Jetzt, da wir unser Makro aufzeichnen, wenden wir unsere zusammenfassenden Berechnungen an. Fügen Sie zuerst die Header hinzu.

Wenden Sie anschließend die entsprechenden Formeln an (bzw.):

• = SUMME (B2: K2)
• = DURCHSCHNITT (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Markieren Sie nun alle Berechnungszellen und ziehen Sie die Länge aller unserer Datenzeilen, um die Berechnungen auf jede Zeile anzuwenden.

Sobald dies geschehen ist, sollte jede Zeile ihre jeweiligen Zusammenfassungen anzeigen.

Jetzt möchten wir die zusammenfassenden Daten für das gesamte Blatt erhalten, daher wenden wir einige weitere Berechnungen an:

Beziehungsweise:

• = SUMME (L2: L21)
• = DURCHSCHNITT (B2: K21) *Dies muss für alle Daten berechnet werden, da der Durchschnitt der Zeilendurchschnitte nicht notwendigerweise dem Durchschnitt aller Werte entspricht.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Berechnet für alle Daten aus demselben Grund wie oben.

Nachdem die Berechnungen abgeschlossen sind, werden wir den Stil und die Formatierung anwenden. Wenden Sie zunächst die allgemeine Zahlenformatierung für alle Zellen an, indem Sie Alle auswählen (entweder Strg + A oder klicken Sie auf die Zelle zwischen den Zeilen- und Spaltenüberschriften) und wählen Sie das Symbol „Komma-Stil“ im Hauptmenü.

Wenden Sie als Nächstes einige visuelle Formatierungen auf die Zeilen- und Spaltenüberschriften an:

• Fett gedruckt.
• Zentriert.
• Hintergrundfüllfarbe.

Und zum Schluss noch etwas Stil auf die Gesamtwerte.

Wenn alles fertig ist, sieht unser Datenblatt so aus:

Da wir mit den Ergebnissen zufrieden sind, stoppen Sie die Aufzeichnung des Makros.

Glückwunsch - Sie haben gerade ein Excel-Makro erstellt.

Um unser neu aufgezeichnetes Makro verwenden zu können, müssen wir unsere Excel-Arbeitsmappe in einem makroaktivierten Dateiformat speichern. Bevor wir dies tun, müssen wir jedoch zunächst alle vorhandenen Daten löschen, damit sie nicht in unsere Vorlage eingebettet werden (die Idee ist, jedes Mal, wenn wir diese Vorlage verwenden, werden die aktuellsten Daten importiert.).

Markieren Sie dazu alle Zellen und löschen Sie sie.

Wenn die Daten jetzt gelöscht sind (aber die Makros immer noch in der Excel-Datei enthalten sind), möchten wir die Datei als Makro-Vorlagendatei (XLTM-Datei) speichern. Es ist wichtig zu beachten, dass Makros automatisch erstellt werden, wenn Sie diese Datei als XLTX-Standarddatei speichern nicht davon laufen können. Alternativ können Sie die Datei als Legacy-Vorlagendatei (XLT-Datei) speichern, wodurch Makros ausgeführt werden können.

Wenn Sie die Datei als Vorlage gespeichert haben, schließen Sie Excel.

Verwenden eines Excel-Makros

Bevor wir uns überlegen, wie wir dieses neu aufgenommene Makro anwenden können, ist es wichtig, ein paar Punkte zu Makros im Allgemeinen zu behandeln:

• Makros können schädlich sein.
• Siehe den obigen Punkt.

VBA-Code ist sehr leistungsfähig und kann Dateien außerhalb des Gültigkeitsbereichs des aktuellen Dokuments bearbeiten. Ein Makro kann beispielsweise zufällige Dateien in Ihrem Ordner "Eigene Dateien" ändern oder löschen. Daher ist es wichtig, dass Sie sich vergewissern nur Ausführen von Makros aus vertrauenswürdigen Quellen.

Öffnen Sie die oben erstellte Excel-Vorlagendatei, um unser Datenformat-Makro zu verwenden. Wenn Sie dies tun, vorausgesetzt, Sie haben Standardsicherheitseinstellungen aktiviert, wird oben in der Arbeitsmappe eine Warnung angezeigt, die besagt, dass Makros deaktiviert sind. Da wir einem von uns erstellten Makro vertrauen, klicken Sie auf die Schaltfläche "Inhalt aktivieren".

Als Nächstes importieren wir den neuesten Datensatz aus einer CSV-Datei (dies ist die Quelle, aus der das Arbeitsblatt erstellt wurde, um unser Makro zu erstellen.).

Um den Import der CSV-Datei abzuschließen, müssen Sie möglicherweise einige Optionen festlegen, damit Excel sie richtig interpretieren kann (z. B. Trennzeichen, vorhandene Header usw.)..

Sobald unsere Daten importiert wurden, wechseln Sie einfach zum Menü Makros (auf der Registerkarte Ansicht) und wählen Sie Makros anzeigen.

Im daraufhin angezeigten Dialogfeld sehen Sie das Makro "FormatData", das wir oben aufgezeichnet haben. Wählen Sie es aus und klicken Sie auf Ausführen.

Nach dem Laufen kann es vorkommen, dass der Cursor für einige Momente herumspringt. In diesem Fall werden jedoch die Daten manipuliert genau wie wir es aufgezeichnet haben. Wenn alles gesagt und getan ist, sollte es genau wie unser Original aussehen - außer bei anderen Daten.

Blick unter die Haube: Was macht ein Makro?

Wie bereits einige Male erwähnt, wird ein Makro durch VBA-Code (Visual Basic für Applikationen) gesteuert. Wenn Sie ein Makro „aufzeichnen“, übersetzt Excel eigentlich alles, was Sie tun, in die entsprechenden VBA-Anweisungen. Einfach ausgedrückt: Sie müssen keinen Code schreiben, da Excel den Code für Sie schreibt.

Um den Code anzuzeigen, durch den unser Makro ausgeführt wird, klicken Sie im Dialogfeld "Makros" auf die Schaltfläche "Bearbeiten".

In dem sich öffnenden Fenster wird der Quellcode angezeigt, der beim Erstellen des Makros aus unseren Aktionen aufgezeichnet wurde. Natürlich können Sie diesen Code bearbeiten oder sogar neue Makros vollständig im Codefenster erstellen. Während die in diesem Artikel verwendete Aufzeichnungsaktion wahrscheinlich die meisten Anforderungen erfüllt, müssen Sie bei stärker angepassten Aktionen oder bedingten Aktionen den Quellcode bearbeiten.

Unser Beispiel noch einen Schritt weiter…

Angenommen, unsere Quellendatendatei "data.csv" wird hypothetisch durch einen automatisierten Prozess erstellt, der die Datei immer am selben Speicherort speichert (z. B. ist C: \ Data \ data.csv immer die aktuellsten Daten). Der Vorgang des Öffnens und Importierens dieser Datei kann leicht auch in ein Makro vorgenommen werden:

1. Öffnen Sie die Excel-Vorlagendatei, die unser Makro "FormatData" enthält.
2. Zeichne ein neues Makro mit dem Namen "LoadData" auf..
3. Importieren Sie bei der Makroaufnahme die Datendatei wie gewohnt.
4. Beenden Sie die Aufzeichnung des Makros, sobald die Daten importiert sind.
5. Alle Zelldaten löschen (Alles auswählen, dann löschen).
6. Speichern Sie die aktualisierte Vorlage (denken Sie daran, ein makroaktiviertes Vorlagenformat zu verwenden.).

Sobald dies geschehen ist, werden beim Öffnen der Vorlage zwei Makros angezeigt - einer, der unsere Daten lädt, und der andere, der sie formatiert.

Wenn Sie sich wirklich mit etwas Code-Bearbeitung die Hände schmutzig machen wollten, können Sie diese Aktionen leicht zu einem einzigen Makro zusammenfassen, indem Sie den aus "LoadData" erzeugten Code kopieren und am Anfang des Codes aus "FormatData" einfügen.

Laden Sie diese Vorlage herunter

Zu Ihrer Bequemlichkeit haben wir sowohl die in diesem Artikel erstellte Excel-Vorlage als auch eine Beispieldatendatei für Sie zum Mitspielen hinzugefügt.

Laden Sie die Excel-Makrovorlage von How-To-Geek herunter