Excel-Makros - Praktisches Tutorial für Anfänger mit Beispielen

excel macros hands tutorial

In diesem praktischen Tutorial zu Excel-Makros wird anhand zahlreicher Beispiele erläutert, was ein Makro ist, wie VBA-Makros erstellt und verwendet werden:

Die meisten von uns in der Branche werden sicherlich bestimmte Aufgaben haben, die fast täglich wiederholt ausgeführt werden müssen. Stellen Sie sich nun vor, diese Aufgaben wären mit nur einem Klick erledigt. Klingt aufregend? Excel-Makros ist die Antwort darauf.

In diesem Tutorial erfahren Sie, was ein Makro ist. Aufzeichnen eines Makros unter Verwendung einer absoluten und relativen Referenz sowie einiger praktischer Beispiele.

=> Schauen Sie sich hier den VBA-Leitfaden für Anfänger an

Was du lernen wirst:

• Was sind Excel-Makros?
  • So aktivieren Sie Makros in Excel
  • Aktivieren der Registerkarte 'Entwickler'
  • Optionen der Registerkarte 'Entwickler'
  • So zeichnen Sie ein Makro auf
  • Speichern einer Excel-Arbeitsmappe mit Makro
  • Ein Makro ausführen
  • Zellreferenzierung
  • Zeichnen Sie das Excel-Makro mithilfe der relativen Referenz auf
  • Häufig gestellte Fragen
• Fazit
  • Literatur-Empfehlungen

Was sind Excel-Makros?

Ein Makro ist eine Reihe von Aktionen, die Sie ausführen können, um die gewünschte Aufgabe auszuführen.

Angenommen, Sie erstellen jeden Monat einen Bericht, in dem die Benutzerkonten mit dem überfälligen Betrag in Fettdruck und Rot gekennzeichnet werden müssen. Anschließend können Sie ein Makro erstellen und ausführen, das diese Formatierungsänderungen jedes Mal anwendet, wenn Sie dies wünschen.

So aktivieren Sie Makros in Excel

Auf der Registerkarte 'Entwickler' können wir mit Funktionen wie Makros und Add-Ins arbeiten und unseren eigenen VBA-Code schreiben, mit dem wir alles automatisieren können, was wir möchten. Diese Registerkarte ist standardmäßig ausgeblendet.

Führen Sie die folgenden Schritte aus, um die Registerkarte Entwickler einzublenden. Dies funktioniert auf allen Versionen von Excel für Windows (Excel 2007, 2010, 2013, 2016, 2019).

Hinweis: Dies ist ein einmaliger Prozess. Sobald Sie die Registerkarte 'Entwickler' aktiviert haben, wird sie für jede geöffnete Excel-Instanz immer in einem benutzerdefinierten Menüband angezeigt, es sei denn, Sie deaktivieren sie explizit.

Aktivieren der Registerkarte 'Entwickler'

# 1) Drücke den Datei Tab

#zwei) Klicken Optionen

#3) Klicke auf Band anpassen.

# 4) Aktivieren Sie unter Multifunktionsleiste anpassen Entwickler.

Sobald Sie die Registerkarte Entwickler aktiviert haben, wird sie in der Multifunktionsleistenliste angezeigt.

Optionen der Registerkarte 'Entwickler'

Nachfolgend sind die Optionen aufgeführt, die auf der Registerkarte Entwickler verfügbar sind.

• Visual Basic: Ermöglicht einem Editor das Schreiben oder Bearbeiten von VBA-Code. Kann auch mit Alt + F11 geöffnet werden.
• Makros: Gibt die Liste aller bereits aufgezeichneten Makros an und wird auch zum Aufzeichnen eines neuen verwendet. Alt + F8 öffnet direkt die Liste der Makros.
• Add-Ins: Ermöglicht das Einfügen eines Add-Ins und kann diese auch verwalten.
• Kontrollen: Hilft uns bei der Verwendung von Formularsteuerelementen und ActiveX-Steuerelementen. Anzeigen und Bearbeiten der Steuerelementeigenschaften. Hier wird der Design-Modus EIN / AUS gesteuert.
• XML: Hilft uns beim Importieren / Exportieren einer XML-Datendatei, beim Verwalten von XML-Erweiterungspaketen und beim Öffnen des Aufgabenbereichs XML-Quelle.

So zeichnen Sie ein Makro auf

Betrachten Sie ein Beispiel , dass Ihr Unternehmen über ein bestimmtes Tool verfügt, mit dem Arbeitszeittabellen für verschiedene Abteilungen in Excel erstellt werden. Sie als Manager sind dafür verantwortlich, das Blatt jede Woche zu überprüfen und an das Finanzteam zu senden.

Vor dem Senden werden Sie jedoch aufgefordert, folgende Formatierungen vorzunehmen:

1. Fügen Sie den Titel für jedes Blatt ein, das den Teamnamen und die Wochennummer enthält, markieren Sie ihn fett und den Hintergrund gelb.
2. Zeichne einen Rand
3. Fett die Spaltenüberschriften.
4. Benennen Sie den Blattnamen als Teamnamen um.

Anstatt dies jede Woche manuell zu tun, können Sie einfach ein Makro erstellen und alle diese Aktionen mit nur einem Klick ausführen.

Das Aufzeichnen von Makros ist ziemlich einfach. Navigieren Sie zur Registerkarte Entwickler und klicken Sie auf Makro aufnehmen.

Dies öffnet ein Fenster, in das Sie eintreten müssen.

# 1) Makroname: Der Name sollte keine Leerzeichen zwischen den Wörtern enthalten. Dies muss mit einem Alphabet oder einem Unterstrich beginnen.

# 2) Tastenkombination: Dies ist nützlich, wenn Sie ein Makro ausführen. Wenn Sie die Tastenkombination drücken, wird sie ausgeführt. Stellen Sie sicher, dass Sie einen Schlüssel angeben, der noch nicht vergeben ist. Andernfalls überschreibt das Makro diesen.

Beispielsweise, Wenn Sie Strg + S als Verknüpfung angeben, wird jedes Mal, wenn Sie Strg + S drücken, Ihr Makro ausgeführt, wodurch die Option zum Speichern von Dateien ignoriert wird. Daher wird empfohlen, Umschalt wie Strg + Umschalt + D hinzuzufügen

# 3) Makro speichern in: Dies hat 3 Optionen wie unten angegeben.

• Diese Arbeitsmappe: Alle erstellten Makros sind nur für die aktuelle Arbeitsmappe verfügbar. Wenn Sie ein neues Excel öffnen, ist das zuvor erstellte Makro nicht verfügbar und kann daher nicht verwendet werden.
• Persönliche Makro-Arbeitsmappe: Wenn Sie dies auswählen, wird das erstellte Makro gespeichert und angezeigt, wenn Sie ein neues Excel-Blatt öffnen.
• Neues Arbeitsbuch: Diese Option öffnet eine neue Arbeitsmappe und die in dieser Arbeitsmappe ausgeführten Aktionen werden aufgezeichnet.

# 4) Beschreibung: Dies beschreibt den Zweck des Makros. Es wird empfohlen, eine detaillierte Beschreibung anzugeben, damit jeder, der diese verwendet, genau weiß, wofür sie verwendet wird.

Sobald Sie die Details für die oben genannten Felder eingegeben haben, können Sie die in der Excel-Arbeitsmappe erforderlichen Aktionen ausführen und alles wird aufgezeichnet. Wenn Sie fertig sind, kehren Sie zur Registerkarte Entwickler zurück und klicken Sie auf Höre auf, aufzunehmen.

Speichern einer Excel-Arbeitsmappe mit Makro

Auswählen des Store-Makros in 'Diese Arbeitsmappe': Beachten Sie, dass Sie das Store-Makro während der Aufnahme als „Diese Arbeitsmappe“ ausgewählt haben. Sobald Sie fertig sind, speichern Sie die Datei. Während des Speicherns müssen Sie Excel Macro-Enabled Workbook auswählen. Sie müssen das Makro nicht explizit speichern. Es wird automatisch gespeichert.

Auswählen von Makro speichern in 'Persönliche Makro-Arbeitsmappe': Erwägen Sie nun, das Store-Makro während der Aufnahme als „Personal Macro Workbook“ auszuwählen. Sie müssen das Makro explizit speichern. Wenn Sie nur die Excel-Datei speichern und dann versuchen, die Datei zu schließen. Anschließend erhalten Sie einen Popup-Dialog wie unten gezeigt.

Hinweis: Wenn Sie dies nicht speichern, wird das Makro gelöscht.

Ein Makro ausführen

Nachdem wir mit dem Aufzeichnen und Speichern der Datei fertig sind, versuchen wir, sie auszuführen und die gewünschten Ergebnisse zu erzielen. Wir haben ein Makro mit allen erforderlichen Schritten im Beispiel für die Anwesenheitsliste aufgezeichnet und es als Diese Arbeitsmappe mit der Tastenkombination Strg + Umschalt + B gespeichert.

Wenn Sie also jede Woche ein neues Excel vom Software-Tool erhalten, müssen Sie nur diese Excel-Datei öffnen und die Tastenkombination (Strg + Umschalt + B) drücken. Alle Änderungen werden wie erwartet übernommen. Das resultierende Excel ist unten angegeben.

Hängte die Excel-Makro-Arbeitsmappe an

Hinweis:

1. Wenn Sie die Tastenkombination vergessen haben, können Sie zu Entwickler -> Makros gehen, das Makro auswählen und auf Optionen klicken.
2. Wenn das als persönliches Geschäft gespeicherte Makro auf der Registerkarte Makros nicht sichtbar ist. Gehen Sie zu Ansicht -> Einblenden. Daraufhin wird die Liste aller Makros angezeigt.

Zellreferenzierung

Es gibt zwei Möglichkeiten, ein Makro aufzunehmen, wie unten gezeigt.

1. Absolute Zellreferenzierung
2. Relative Zellreferenzierung

Absolute Zellreferenzierung: Absolute Referenzen verweisen immer auf die bestimmte Zelle, in der sie aufgezeichnet wurden. Beispielsweise: Wenn Sie einen Text in einer A10-Zelle aufzeichnen und diesen Makro das nächste Mal in einer anderen Arbeitsmappe verwenden, wird dieser Text in A10 platziert.

Betrachten Sie unser Beispiel für die Anwesenheitsliste. Wir möchten immer, dass der Titel in der ersten Zeile jedes Blattes steht. Wir möchten nicht, dass sich die Zellreferenz ändert, wenn sie in andere Blätter oder Arbeitsmappen kopiert wird. In diesem Fall ist die absolute Zellreferenzierung praktisch.

Relative Zellreferenzierung: Angenommen, Sie müssen die Schritte an verschiedenen Stellen im Arbeitsblatt wiederholen. Relative Referenzen sind praktisch, wenn Sie dieselbe Berechnung oder dieselben Schritte über mehrere Zeilen oder Spalten hinweg wiederholen müssen.

Beispiel: Angenommen, Sie haben eine Excel-Tabelle mit den vollständigen Namen, Telefonnummern und DOBs von 1000 Mitarbeitern. (Format ist wie unten gezeigt)

Ihr Manager erwartet von Ihnen Folgendes:

gefälschte E-Mail-Adresse, die ich verwenden kann

1. Vorname und Nachname trennen.
2. Fügen Sie der Telefonnummer das Beispiel für den Ländercode (+91) hinzu.
3. Zeigen Sie DOB in Form von TT-Mon-JJ, Beispiel: 10. Januar 87.

Da es 1000 Datensätze gibt, würde es einige Zeit dauern, dies manuell zu tun. Sie entscheiden sich also, ein Makro zu erstellen. Die Verwendung einer absoluten Referenz löst Ihr Problem jedoch nicht, da es über mehrere Zeilen und Spalten hinweg funktionieren soll. In diesem Fall ist die relative Referenz praktisch.

Zeichnen Sie das Excel-Makro mithilfe der relativen Referenz auf

Um mit einer relativen Referenz aufzunehmen, wählen Sie zunächst die Zelle aus, in der Sie die Aufnahme starten möchten.

Gehen Sie zu Entwickler -> klicken Sie auf Relative Referenz verwenden -> Makro aufzeichnen . Nehmen Sie alles auf, was Sie möchten, und klicken Sie auf Aufnahme beenden.

Befolgen Sie für das obige Beispiel diese Schritte.

1. Zuerst müssen wir eine Spalte neben Emp FullName einfügen und die Spaltenüberschrift in FirstName und LastName ändern.
2. Wählen B2-Zelle-> Gehen Sie zu Entwickler -> Relative Referenz verwenden -> Makro aufzeichnen .
3. Verwenden Sie Text Delimiter, um Vor- und Nachnamen zu trennen. Sobald Sie fertig sind, beenden Sie die Aufnahme.
4. Erstellen Sie auf ähnliche Weise zwei weitere Makros für Telefonnummer und Geburtsdatum.
5. Speicher die Datei.
6. Wählen Sie zum Ausführen alle Emp-Vollnamen aus, d. H. B3 bis zum letzten Emp, das B1001 ist, und führen Sie die 1 aus^stMakro.
7. Befolgen Sie ähnliche Schritte für Telefonnummer und Geburtsdatum. Das resultierende Excel wird unten gezeigt.

Angehängte Datei als Referenz

Häufig gestellte Fragen

F # 1) Was ist ein Beispiel für Makros in Excel?

Antworten: Ein Makro ist eine Reihe von Aktionen, die Sie ausführen können, um die gewünschte Aufgabe auszuführen.

Angenommen, Sie erstellen jeden Monat einen Bericht, in dem die Benutzerkonten mit dem überfälligen Betrag in Fettdruck und Rot gekennzeichnet werden müssen. Sie können ein Makro erstellen und ausführen, das diese Formatierungsänderungen jedes Mal mit nur einem Klick anwendet.

F # 2) Wo sind Makros in Excel?

Antworten: Alle aufgezeichneten Makros sind unter verfügbar Registerkarte Entwickler -> Makros

Wenn Sie kein persönliches Makro finden, gehen Sie zu Ansicht -> Einblenden .

F # 3) Welche Arten von Zellreferenzen gibt es in Excel?

Antworten:

• Absolut: Absolute Referenzen verweisen immer auf die bestimmte Zelle, in der sie aufgezeichnet wurden. Beispielsweise, Wenn Sie einen Text in einer D10-Zelle aufzeichnen, zeigt das Makro bei jeder Verwendung immer auf D10.
• Relativ: Diese sind praktisch, wenn Sie dieselbe Berechnung oder dieselben Schritte über mehrere Zeilen oder Spalten hinweg wiederholen müssen.

F # 4) Wie speichere ich ein Makro in Excel in allen Arbeitsmappen?

Antworten: Wählen Sie beim Aufzeichnen eines Makros unter Arbeitsmakro in die Option Persönliche Makro-Arbeitsmappe aus, damit Ihr Makro für alle Arbeitsmappen verfügbar ist. Wenn Sie die Option immer noch nicht sehen, gehen Sie zu Ansicht -> Einblenden .

Fazit

In diesem Tutorial haben wir Excel-Makros gelernt, die uns bei der Automatisierung der Routineaufgaben in Excel helfen.

Wir haben gesehen, was ein Makro ist? So aktivieren Sie die Anzeige des Makros in Excel. Wir haben auch anhand von Beispielen untersucht, wie ein Makro mithilfe der absoluten und relativen Zellreferenzierung aufgezeichnet wird.

=> Lesen Sie die Easy VBA Training Series durch

Literatur-Empfehlungen

• Excel VBA Tutorial - Einführung in VBA in Excel
• VBA-Datentypen - Numerische und nicht numerische Datentypen in VBA
• Über 35 häufig gestellte Fragen und Antworten zu Microsoft Excel-Vorstellungsgesprächen
• So automatisieren Sie sich wiederholende QS-Aufgaben mithilfe von Excel-Makros (Beispiele)
• So öffnen Sie eine XML-Datei in Excel, Chrome und MS Word
• Lesen oder Schreiben von Daten aus einer Excel-Tabelle in Selenium Web Driver
• Top 5 Dinge, die ein Tester für Excel haben muss (und die sich verändernden Perspektiven des Software-Testers)
• Arbeiten mit VBScript Excel-Objekten