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

Inhaltsverzeichnis:

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

Video: Erfahren Sie, wie Sie Excel-Makros verwenden, um langwierige Aufgaben zu automatisieren
Video: Tutorial Microsoft Word: Shortcuts nutzen 2024, März
Erfahren Sie, wie Sie Excel-Makros verwenden, um langwierige Aufgaben zu automatisieren
Erfahren Sie, wie Sie Excel-Makros verwenden, um langwierige Aufgaben zu automatisieren
Anonim
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.
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 Funktion 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. Auf die gleiche Art und Weise, wie Javascript HTML auf einer Webseite manipulieren kann, kann ein Makro ein Dokument bearbeiten.

Makros sind unglaublich leistungsstark und können so ziemlich alles tun, was Ihre Vorstellungskraft auslösen kann. Als (sehr) kurze Liste von Funktionen können Sie ein Makro verwenden:

  • Anwenden von Stil und Formatierung
  • Daten und Text manipulieren.
  • Kommunizieren Sie 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.
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, rufen Sie Ansicht> Makros> Makro aufzeichnen auf.

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

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 Aufnahmemodus anzeigt. 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 Stopp-Symbol zeigt an, dass es sich im Makromodus befindet. Wenn Sie hier drücken, wird die Aufnahme gestoppt. Wenn Sie sich nicht im Aufnahmemodus befinden, ist dieses Symbol die Schaltfläche Aufnahmemakro, die Sie anstelle des Makros-Menüs verwenden können.
Der andere befindet sich in der rechten unteren Ecke. Das Stopp-Symbol zeigt an, dass es sich im Makromodus befindet. Wenn Sie hier drücken, wird die Aufnahme gestoppt. Wenn Sie sich nicht im Aufnahmemodus befinden, ist dieses Symbol die Schaltfläche Aufnahmemakro, die Sie anstelle des Makros-Menüs verwenden können.
Jetzt, da wir unser Makro aufzeichnen, wenden wir unsere zusammenfassenden Berechnungen an. Fügen Sie zuerst die Header hinzu.
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.):
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.
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.
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:
Jetzt möchten wir die zusammenfassenden Daten für das gesamte Blatt erhalten, daher wenden wir einige weitere Berechnungen an:
Beziehungsweise:
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) * Für alle Daten aus dem gleichen Grund wie oben berechnet.
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 eine 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ü.
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 eine 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:
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.
Und zum Schluss noch etwas Stil auf die Gesamtwerte.
Wenn alles fertig ist, sieht unser Datenblatt so aus:
Wenn alles fertig ist, sieht unser Datenblatt so aus:
Da wir mit den Ergebnissen zufrieden sind, stoppen Sie die Aufzeichnung des Makros.
Da wir mit den Ergebnissen zufrieden sind, stoppen Sie die Aufzeichnung des Makros.
Herzlichen Glückwunsch - Sie haben gerade ein Excel-Makro erstellt.
Herzlichen 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.

Image
Image

Wenn die Daten jetzt gelöscht sind (die Makros jedoch 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.
Wenn Sie die Datei als Vorlage gespeichert haben, schließen Sie Excel.

Verwenden eines Excel-Makros

Bevor wir überlegen, wie wir dieses neu aufgenommene Makro anwenden können, ist es wichtig, einige 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 Makros von vertrauenswürdigen Quellen ausführen.

Ö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 (dies ist die Quelle, aus der das Arbeitsblatt erstellt wurde, um unser Makro zu erstellen).
Als Nächstes importieren wir den neuesten Datensatz aus einer CSV (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.).
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.
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.
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.
Image
Image

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 aufgenommen haben. Wenn alles gesagt und getan ist, sollte es genau wie unser Original aussehen - außer bei anderen Daten.

Image
Image

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.
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 gleichen 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. Zeichnen Sie ein neues Makro mit dem Namen "LoadData" auf.
  3. Importieren Sie bei der Makroaufzeichnung die Datendatei wie gewohnt.
  4. Beenden Sie die Aufzeichnung des Makros, sobald die Daten importiert sind.
  5. Löschen Sie alle Zelldaten (markieren Sie alle und löschen Sie dann).
  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 mit etwas Code-Bearbeitung wirklich die Hände schmutzig machen wollten, können Sie diese Aktionen leicht zu einem einzigen Makro kombinieren, indem Sie den aus „LoadData“erzeugten Code kopieren und ihn am Anfang des Codes aus „FormatData“einfügen.
Wenn Sie sich mit etwas Code-Bearbeitung wirklich die Hände schmutzig machen wollten, können Sie diese Aktionen leicht zu einem einzigen Makro kombinieren, indem Sie den aus „LoadData“erzeugten Code kopieren und ihn 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 enthalten.

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

Empfohlen: