9Sep

Erfahren Sie, wie Sie mit Excel-Makros mühsame Aufgaben automatisieren

Eine der leistungsfähigeren, aber selten verwendeten Funktionen von Excel ist die Möglichkeit, sehr einfach automatisierte Aufgaben und benutzerdefinierte Logik in Makros zu erstellen. Makros bieten einen idealen Weg, um bei vorhersagbaren, sich wiederholenden Aufgaben Zeit zu sparen und Dokumentformate zu standardisieren - oft ohne eine einzige Zeile Code schreiben zu müssen.

Wenn Sie neugierig sind, was Makros sind oder wie sie tatsächlich erstellt werden, kein Problem - wir begleiten Sie durch den gesamten Prozess.

Hinweis: der gleiche Prozess sollte in den meisten Versionen von Microsoft Office funktionieren. Die Screenshots könnten etwas anders aussehen.

Was ist ein Makro?

Ein Microsoft Office-Makro( da diese Funktionalität für mehrere MS Office-Anwendungen gilt) ist einfach Visual Basic für Applikationen( VBA) -Code, der in einem Dokument gespeichert wird. Für eine vergleichbare Analogie, betrachten Sie ein Dokument als HTML und ein Makro als Javascript.Ähnlich wie JavaScript HTML auf einer Webseite manipulieren kann, kann ein Makro ein Dokument manipulieren.

Makros sind unglaublich leistungsstark und können so ziemlich alles, was Ihre Fantasie hervorbringen kann. Als( sehr) kurze Liste von Funktionen können Sie mit einem Makro tun:

  • Übernehmen Sie Stil und Formatierung.
  • Manipulieren von Daten und Text.
  • Kommunizieren mit Datenquellen( Datenbank, Textdateien usw.).
  • Erstellen Sie völlig neue Dokumente.
  • Beliebige Kombination in beliebiger Reihenfolge.

Erstellen eines Makros: Eine Erklärung anhand des Beispiels

Wir beginnen mit Ihrer CSV-Datei für den Garten. Nichts Besonderes hier, nur ein 10 × 20 Satz von Zahlen zwischen 0 und 100 mit einer Zeilen- und Spaltenüberschrift. Unser Ziel ist es, ein gut formatiertes, vorzeigbares Datenblatt zu erstellen, das zusammenfassende Summen für jede Zeile enthält.

Wie bereits erwähnt, handelt es sich bei einem Makro um VBA-Code, aber eines der schönen Dinge bei Excel ist, dass Sie sie mit Nullcodierung erstellen / aufzeichnen können - wie wir es hier tun werden.

Um ein Makro zu erstellen, gehen Sie zu View & gt;Makros & gt;Makro aufzeichnen

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

Sobald dies getan ist, alle Ihrer Aktionen aufgezeichnet werden - jede Zelle ändern, Scroll-Aktion, Fenstergröße ändern, Sie nennen es.

Es gibt ein paar Stellen, die angeben, dass Excel der Aufzeichnungsmodus ist. Eine davon ist, indem Sie das Macro-Menü anzeigen und beachten, dass Stop Recording die Option für Record Macro ersetzt hat.

Der andere ist in der unteren rechten Ecke. Das Symbol "Stopp" zeigt an, dass es sich im Makro-Modus befindet. Wenn Sie hier drücken, wird die Aufnahme gestoppt. Wenn Sie nicht im Aufnahmemodus ist, wird dieses Symbol die Schaltfläche Makro aufzeichnen, die Sie anstelle des Makros verwenden können.

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

Wenden Sie als nächstes die entsprechenden Formeln an:

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

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

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

Nun wollen wir die zusammenfassenden Daten für das gesamte Blatt erhalten, deshalb wenden wir ein paar mehr Berechnungen an:

Beziehungsweise:

  • = SUMME( L2: L21)
  • = MITTELWERT( B2: K21) * Dies muss über alle berechnet werdenDaten, weil der Durchschnitt der Zeilenmittelwerte nicht notwendigerweise dem Durchschnitt aller Werte entspricht.
  • = MIN( N2: N21)
  • = MAX( O2: O21)
  • = MEDIAN( B2: K21) * Berechnet über alle Daten aus dem gleichen Grund wie oben.

Nun, da die Berechnungen abgeschlossen sind, werden wir den Stil und die Formatierung anwenden.Übernehmen Sie zunächst die allgemeine Zahlenformatierung für alle Zellen, indem Sie Alle auswählen( entweder Strg + A oder die Zelle zwischen den Zeilen- und Spaltenüberschriften) auswählen und im Home-Menü das Symbol "Kommastil" auswählen.

Als Nächstes wenden Sie eine visuelle Formatierung sowohl auf die Zeilen- als auch auf die Spaltenüberschriften an:

  • Bold.
  • Zentriert.
  • Hintergrundfüllfarbe.

Und schließlich, wenden Sie einen Stil auf die Summen an.

Wenn alles fertig ist, sieht unser Datenblatt so aus:

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

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

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

Wählen Sie dazu alle Zellen aus und löschen Sie sie.

Wenn die Daten jetzt gelöscht sind( aber die Makros noch in der Excel-Datei enthalten sind), möchten wir die Datei als XLTM-Datei( Macro Enabled Template) speichern. Es ist wichtig zu beachten, dass wenn Sie dies als Standard-Vorlage( XLTX) -Datei speichern, und nicht von ausgeführt werden können. Alternativ können Sie die Datei als Legacy-Vorlagedatei( XLT-Datei) speichern, sodass Makros ausgeführt werden können.

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

Verwenden eines Excel-Makros

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

  • Makros können bösartig sein.
  • Siehe den obigen Punkt.

VBA-Code ist tatsächlich ziemlich mächtig und kann Dateien außerhalb des Bereichs des aktuellen Dokuments bearbeiten. Beispielsweise könnte ein Makro zufällige Dateien in Ihrem Ordner Eigene Dateien ändern oder löschen. Daher ist es wichtig sicherzustellen, dass nur Makros von vertrauenswürdigen Quellen ausführen.

Um unser Datenformatmakro zu verwenden, öffnen Sie die oben erstellte Excel-Vorlagendatei. Wenn Sie dies unter der Annahme tun, dass die Standardsicherheitseinstellungen aktiviert sind, 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 werden wir den neuesten Datensatz aus einer CSV importieren( dies ist die Quelle des Arbeitsblatts, das zur Erstellung unseres Makros verwendet wurde).

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

Sobald unsere Daten importiert sind, gehen Sie einfach zum Macros-Menü( unter der Ansicht-Registerkarte) und wählen Sie View Macros.

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

Sobald Sie laufen, sehen Sie vielleicht, wie der Cursor für einige Momente herumspringt, aber Sie werden sehen, dass die Daten genau so wie bearbeitet werden, so wie wir sie aufgezeichnet haben. Wenn alles gesagt und getan ist, sollte es genau wie unser Original aussehen - außer mit anderen Daten.

Blick unter die Haube: Was macht ein Makro

Wie bereits erwähnt, wird ein Makro von Visual Basic für Applikationen( VBA) -Code gesteuert. Wenn Sie ein Makro "aufzeichnen", übersetzt Excel tatsächlich alles, was Sie tun, in seine jeweiligen VBA-Anweisungen. Um es einfach auszudrücken - Sie müssen keinen Code schreiben, weil Excel den Code für Sie schreibt.

Um den Code anzuzeigen, der unser Makro ausführt, klicken Sie im Dialogfeld Makros auf die Schaltfläche Bearbeiten.

Das sich öffnende Fenster zeigt den Quellcode an, der bei der Erstellung des Makros aus unseren Aktionen aufgezeichnet wurde. Natürlich können Sie diesen Code bearbeiten oder sogar neue Makros innerhalb des Codefensters erstellen. Während die in diesem Artikel verwendete Aufzeichnungsaktion wahrscheinlich den meisten Anforderungen entspricht, würden Sie bei höher angepassten Aktionen oder bedingten Aktionen den Quellcode bearbeiten müssen.

Nehmen wir unser Beispiel einen Schritt weiter. ..

Nehmen wir an, unsere Quelldatendatei, data.csv, wird von einem automatisierten Prozess erstellt, der die Datei immer am selben Ort speichert( zB C: \ Data \ data.csv ist immer am meisten)jüngste Daten).Der Prozess des Öffnens dieser Datei und des Importierens kann auch leicht in ein Makro umgewandelt werden:

  1. Öffnen Sie die Excel-Vorlagendatei, die unser Makro "FormatData" enthält.
  2. Zeichnen Sie ein neues Makro namens "LoadData" auf.
  3. Importieren Sie die Datendatei bei der Makroaufnahme wie gewohnt.
  4. Sobald die Daten importiert sind, beenden Sie die Aufzeichnung des Makros.
  5. Alle Zellendaten löschen( alle auswählen und dann löschen).
  6. Speichern Sie die aktualisierte Vorlage( denken Sie daran, ein Makro-fähiges Vorlagenformat zu verwenden).

Wenn das Template einmal geöffnet ist, wird es zwei Makros geben - eines, das unsere Daten lädt und das andere, das es formatiert.

Wenn Sie mit ein wenig Code-Bearbeitung wirklich Ihre Hände schmutzig machen wollten, könnten 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

Für Ihre Bequemlichkeit haben wir sowohl die Excel-Vorlage, die in diesem Artikel produziert wurde, als auch eine Beispieldatei für Sie, mit der Sie herumspielen können, aufgenommen.

Laden Sie die Excel-Makrovorlage von How-To Geek

herunter