Rheinwerk Computing < openbook > Rheinwerk Computing - Professionelle Bücher. Auch für Einsteiger.
Professionelle Bücher. Auch für Einsteiger.

Inhaltsverzeichnis
1 Einleitung
2 Basiswissen für die Arbeit mit Excel 2007
3 Aufbau von Kalkulationstabellen
4 Entwicklung von Berechnungen mit Formeln
5 Gestaltung von Tabellenblättern
6 Auswertungen und Was-wäre-wenn-Analysen
7 Optimierungen
8 Grafische Präsentation von Daten
9 Diagramme optimal einsetzen
10 Tabellen grafisch aufbereiten
11 Verteilungsvorbereitung
12 Ausdruck und E-Mail-Versand
13 Excel-Daten im Web
14 Gemeinsame Arbeit an Arbeitsmappen
15 Tabellenfunktionen
16 Informationen als Tabellen ordnen und verwalten
17 Datenabfragen und Datenauszüge
18 Pivot–Tabellen und -Diagramme
19 Arbeit mit externen Daten
20 Datenaustausch zwischen Anwendungen
21 Datenaustausch mit anderen Anwendungen
22 Routineaufgaben mit Makros automatisieren
23 Visual Basic für Applikationen
A Tastenkombinationen
Stichwort

Buch bestellen
Ihre Meinung?

Spacer
 <<   zurück
Excel 2007 - Das umfassende Handbuch von Helmut Vonhoegen
Buch: Excel 2007 - Das umfassende Handbuch

Excel 2007 - Das umfassende Handbuch

1012 S., 39,90 Euro
Rheinwerk Computing
ISBN 978-3-89842-864-4
gp 22 Routineaufgaben mit Makros automatisieren
  gp 22.1 Makros aufzeichnen
    gp 22.1.1 Vorbereitungen
    gp 22.1.2 Die Aufzeichung für den Zeitplan
    gp 22.1.3 Wie sieht die Aufzeichnung aus?
    gp 22.1.4 Die Arbeitsmappe mit dem Makro speichern
    gp 22.1.5 Das Makro ausführen
  gp 22.2 Makros in den Arbeitsablauf einbinden
  gp 22.3 Eine Tabelle per Makro kippen
  gp 22.4 Makros für Rechnungen
  gp 22.5 Makros von älteren Excel-Versionen


Rheinwerk Computing

22.4 Makros für Rechnungen  downtop

Häufig kann eine Aufgabe durch ein paar kleine Makros sehr erleichtert werden, auch wenn an den Aufzeichnungen noch kleine Korrekturen erforderlich sind. Ein Beispiel ist das Schreiben von Rechnungen. Zwar bietet Ihnen Excel 2007 dafür eine fertige Tabellenvorlage, die ganz gut aussieht. Das Problem ist nur, es sind zwar einige Anpassungsmöglichkeiten eingebaut, aber in der Praxis werden oft noch andere Änderungen notwendig. Deshalb wird hier gezeigt, wie Sie sich selbstständig eine solche Vorlage aufbauen können, die beliebig angepasst werden kann.

Ein halbautomatisches Rechnungsformular

Angenommen, Sie haben für Ihre Rechnungen ein Formular mit Rechnungskopf etc. als Mustervorlage aufgebaut, in der schon die wesentlichen Einträge für die Rechnung enthalten sind. Das leere Formular könnte etwa so aussehen:

Formular für Rechnungen

Wenn Sie mit einem derartigen Formular arbeiten, sind folgende Aktionen nötig, von denen sich ein Teil automatisieren lässt:

  • Adressat, Datum, Auftragsdatum und Rechnungsnummer werden eingetragen.
  • Die Rechnungspositionen mit Menge und Einzelpreis werden zeilenweise eingegeben.
  • Zum Abschluss kommen die Gesamtsumme, Ausweisung der Mehrwertsteuer, evtl. Zahlungsbedingungen etc.

Rheinwerk Computing

Ein Makro für das Tagesdatum  downtop

Für die Eingabe des Datums kann ein kleines Makro verwendet werden. Die Zelle E11 wird ausgewählt. Das Datum wird mit der Datumsfunktion =HEUTE() vom Systemdatum übernommen. Der Zellinhalt wird in die Zwischenablage kopiert und dann als Wert wieder eingefügt. (Würden Sie die Funktion HEUTE() in der Zelle stehen lassen, würde die Rechnung jedes Mal das Datum aktualisieren, wenn die Datei geöffnet wird.) Anschließend wird die Zelle E12 ausgewählt, sodass das Auftragsdatum eingegeben werden kann.

Aufzeichnung für das Datum

Obwohl das Makro so korrekt arbeitet, ist der von der Makroaufzeichnung erzeugte Code etwas umständlich. Eine kleine manuelle Korrektur vereinfacht das Makro. Zwischen den beiden .Select-Anweisungen genügt eine einzige Zeile, die mit der VBA-Funktion Date arbeitet:

ActiveCell.R1C1Formula = Date

Durch diese Anweisung wird immer das aktuelle Datum des Tages in die Zelle geschrieben, an dem das Makro aufgerufen wird, also bei der Rechnungserstellung.


Rheinwerk Computing

Ein Makro für die Rechnungspositionen  downtop

Manuell würde nach dem Eintrag des Datums etc. das Ausfüllen des eigentlichen Rechnungsteils so vor sich gehen, dass der Reihe nach die Positionsnummern, Mengen, Bezeichnungen und Einzelpreise eingetragen werden. Dann käme in jede Zelle, die einen Gesamtpreis erhalten soll, unter »Gesamt« die Formel

= Bx*Dx

wobei für x die jeweilige Zeilennummer steht. Der Eintrag der Formeln lässt sich wieder durch ein Makro erledigen, das aber erst aufgerufen wird, wenn alle Positionen erfasst sind. Dabei ist das Problem zu lösen, dass die Formel nur in Zeilen eingegeben werden sollte, in denen tatsächlich Zahlen für Menge und Einzelpreis stehen. Steht die Formel auch in einer leeren Zeile, wird der Wert 0 ausgegeben, der bei Rechnungen mit leeren Zeilen natürlich sehr störend wirkt. Als Formel wird deshalb gewählt

=WENN(ISTZAHL(Bx);Bx*Dx;" ")

mit der Bedeutung: Wenn Bx eine Zahl enthält, dann das Produkt Bx*Dx, sonst ein Leerzeichen (x ist wieder die Zeilennummer). Anschließend muss diese Formel in alle Zellen kopiert werden, die dafür in Frage kommen.

Als Ausgangspunkt für die Aufzeichnung soll die letzte Zeile dienen, in der eine Mengenangabe und ein Einzelpreis eingetragen wurden. Der Zellzeiger wird also zunächst in die Spalte E ein Stück weiter unten (z. B. E30) gesetzt. Am besten schreiben Sie sich in zwei Zellen für Menge und für Einzelpreis je einen Testwert, um zu sehen, ob alles richtig funktioniert. Dann wird die Makroaufzeichnung aktiviert. Als Namen kann »Gesamt« eingetragen werden.


Rheinwerk Computing

Formel-Makro aufzeichnen  downtop

In der Tabelle werden folgende Schritte vollzogen:

1 Eintrag der oben genannten Formel =WENN(...), wobei für das x die richtige Zeilennummer eingesetzt wird.

2 Anschließend wird der Bereich von der Zelle, in die die Formel eingetragen wurde, bis zu E17 (im Beispiel also E30:E17) von unten nach oben markiert, sodass die Zelle mit der Formel den Zellzeiger behält.

3 Der letzte Schritt besteht darin, die Formel in alle markierten Zellen zu kopieren. Hierzu dient der Befehl Start/Bearbeiten/Füllbereich mit der Option Oben. Dann wird die Aufzeichnung beendet.

Das aufgezeichnete Makro sieht so aus:

Makro zum Eintragen der Formeln

Das Makro schreibt zunächst die gewünschte Formel in die aktive Zelle, wobei die Formel etwas ungewöhnlich aussieht: Statt der erwarteten (und beim Aufzeichnen geschriebenen) Formel hat Visual Basic mit relativen Adressen in der R1C1-Schreibweise gearbeitet.


Zellverweise in Makros

In Visual Basic werden zwei Adressierungsarten verwendet: die R1C1-Schreibweise und die A1-Schreibweise, die bei der Arbeit mit Tabellen üblich ist. Die seltener verwendete R1C1-Schreibweise benennt die einzelnen Zellen nach ihrer Zeilen- und Spaltenposition (R steht für Row, C für Column), R3C2 bedeutet »3. Zeile 2. Spalte«, entspricht also B3. Da die A1-Adressen Zeichenketten sind, können Sie sie mit Operatoren für Zeichenketten verändern: Statt »A5« könnte also auch »A« & »5« stehen.

Die R1C1-Schreibweise gestattet es, besonders elegant relative Verweise wiederzugeben. RC ist immer die aktuelle Zelle, R(1)C ist eine Zeile tiefer, R(-1)C eine Zeile höher, RC(1) eine Spalte nach rechts, R(2)C(3) zwei Zeilen tiefer drei Spalten nach rechts usw.


Ein Blick auf die Aufzeichnung lehrt, dass das Makro nicht ganz so funktionieren kann, wie es soll. Es enthält für den Bereich, der ausgewählt wird, feste Adressen ("E17:E30") und für die Zelle, in der die anfängliche Formel steht, ebenfalls eine feste Adresse ("E30"). Hier schafft eine kleine Nachbesserung Abhilfe.

Zunächst wird eine neue Zeile eingefügt, die die aktuelle Zeilennummer (wo die Formel eingetragen wurde) ausliest. Diese Nummer wird auch benutzt, um die Zeilennummer festzulegen, in die der Zellzeiger nach der ganzen Aktion gesetzt werden soll.

azeile = Selection.Row 
nzeile = azeile + 2

Die Variable azeile (aktuelle Zeile) speichert die Zeilennummer der aktuellen Auswahl. Dann wird diese Zeilennummer in die Bereichsbezeichnungen so eingebaut, wie es die folgende Abbildung zeigt (dazu wurde in diesem Beispiel ein neues Makro mit dem Namen Gesamta geschrieben). Zusätzlich wird an das Makro noch eine Zeile angehängt, die den Zellzeiger zwei Zellen tiefer setzt:

Range("E" & nzeile).Select

In beiden Fällen wird die Tatsache genutzt, dass die Bereichsadressen in Visual Basic als Zeichenketten eingegeben werden, sodass sie auch aus Variablen zusammensetzbar sind. Die übrigen Befehle können aus der Aufzeichnung durch Kopieren und Einfügen übernommen werden.

Das überarbeitete Makro

Damit ist die Arbeit mit der Rechnung fast schon zu Ende. Es fehlen jetzt lediglich noch die Endsumme mit dem gesonderten Ausweis der Mehrwertsteuer und eventuelle zusätzliche Einträge.


Rheinwerk Computing

Makro für den Rechnungsabschluss  downtop

Um das Schlussmakro zu erstellen, ist es sinnvoll, direkt an das vorherige Makro anzuschließen. Hierfür wird der Zellzeiger wieder in die Position gebracht, in der er nach Ablauf des vorhergehenden Makros steht. Für die Aufzeichnungsart wird diesmal Relative Aufzeichnung gewählt, damit das Makro später beim Aufruf alle Aktionen relativ zu der letzten Markierung durchführt. Als Name wird »Abschluss« eingetragen. Nach dem Start der Aufzeichnung werden folgende Aktionen durchgeführt:

1 In die aktuelle Zelle (wenn Sie dem Beispiel gefolgt sind, E32) wird die Summe der Spalte eingetragen: =SUMME(E17:E30).

2 Der Zellzeiger wird um zwei Zellen nach links versetzt. Hierher kommt der Text »Summe Netto:«.

3 Anschließend rückt der Zellzeiger eine Zelle nach unten für den Eintrag »Mehrwertsteuer:«.

4 Nun wieder zwei Zellen nach links und dort die Formel: =E32*0,19.

5 Jetzt eine Zelle nach unten und die Formel =SUMME(E32:E33).

6 Wieder zwei Zellen nach links für den Eintrag »Summe brutto:«.

Hiermit ist das Makro »Abschluss« im Prinzip fertig gestellt. Sie können aber jetzt noch, bevor Sie die Aufzeichnung beenden, einige Formatierungen vornehmen: die Zellen mit den Texteinträgen (E32:E35) markieren und den Text rechtsbündig formatieren, Zellrahmen (immer nur unten) setzen, um Zwischenstriche zu erhalten usw.

Im Beispiel wurde lediglich die Textausrichtung vollzogen. Die Aufzeichnung sieht dann so aus:

Aufgezeichnetes Makro Abschluss

Auch hier ist eine kurze Nachbearbeitung erforderlich. Die zweite Zeile des Makros soll die Formel für die Nettosumme eintragen. Die eingetragene Formel lautet im Makro:

"=SUMME(R(-15)C:R(-2)C)"

würde also die Summe des Bereichs fünfzehn Zellen bis zwei Zellen oberhalb bilden. Benötigt wird aber die Summe von Zelle E17 bis zwei Zellen oberhalb. Das lässt sich einfach erreichen: die Zeile wird so korrigiert, dass die eingetragene Formel lautet:

"=SUMME(R17C:R(-2)C)"

Die relative Adresse R(-15)C wird also durch die absolute Adresse R17C ersetzt. Das überarbeitete Makro erhält den Namen »Abschlussa«.


Rheinwerk Computing

Zusammenfassen von Makros  downtop

Nun gibt es eigentlich keinen Grund, warum die beiden Makros »Gesamta« und »Abschlussa« getrennt sein müssten. Zwischen ihnen muss an der Rechnung ja nicht mehr gearbeitet werden.

Es spricht also nichts dagegen, sie miteinander zu verbinden. Hierzu werden die beiden Makros hintereinander kopiert, der Titel von »Gesamta« abgeändert in »Schluss«, das End Sub des ersten Makros und das Sub Abschluss() des zweiten Makros können gelöscht werden.

Stattdessen können Sie beide Makros aber auch in einem gemeinsamen zusammenfassen. Das Resultat sieht so aus:

Zwei Prozeduren werden verklammert.

Schließlich werden noch die Bedienungselemente eingerichtet: eine Schaltfläche mit der Beschriftung »Datum« und eine mit der Beschriftung »Abschluss«, die mit den entsprechenden Makros verknüpft werden.

Das fertige Rechnungsformular mit Beispieldaten


Rheinwerk Computing

Speichern des Formulars als Mustervorlage  downtop

Mit diesen Makros lässt sich die Arbeit mit dem Rechnungsformular leicht erledigen. Sobald Sie die Makros ausgiebig getestet haben, können Sie das Rechnungsformular als Mustervorlage speichern. Alle Einträge aus der Rechnung, die im Lauf der Makroaufzeichnungen vorgenommen wurden, werden entfernt, auch die Formate aus den rechtsbündig formatierten Textstellen werden gelöscht.

Schließlich ist dafür zu sorgen, dass die Zellen unter Einzel und Gesamt mit dem Währungsformat formatiert werden (am besten zunächst die ganzen Spalten D und E, dann die beiden Datumszellen mit einem Datumsformat und die Zelle für die Rechnungsnummer mit dem Zahlenformat »0«).

Dann sollten alle nicht benutzten Tabellen entfernt werden, sodass nur noch das Formular übrig bleibt. Die Arbeitsmappe wird mit Speichern unter mit dem Namen »Rechnungsformular« und dem Dateityp Excel-Vorlage mit Makros im Vorlagenordner gespeichert.


Rheinwerk Computing

Arbeit mit dem Rechnungsformular  toptop

Wenn Sie mit dem Rechnungsformular arbeiten wollen, wählen Sie über das Office-Menü den Befehl Neu und im Dialogfeld Neue Arbeitsmappe das gewünschte Formular. Anschließend haben Sie eine Arbeitsmappe mit dem Namen »Rechnungsformular1« zur Verfügung, in die Sie mit Hilfe der Makros zunächst das Tagesdatum und nach Abschluss der einzelnen Positionen die Schlussberechnungen einfügen können.

Die Rechnungsvorlage im Dialog Neue Arbeitsmappe

Wenn Sie noch weitere Veränderungen an der Mustervorlage vornehmen wollen, können Sie die Mustervorlage direkt aus dem Vorlagenordner öffnen, also nicht über Neu, sondern über Öffnen.



Ihr Kommentar

Wie hat Ihnen das <openbook> gefallen? Wir freuen uns immer über Ihre freundlichen und kritischen Rückmeldungen. >> Zum Feedback-Formular
 <<   zurück
  
  Zum Katalog
Zum Katalog: Excel 2007 - Das umfassende Handbuch
Excel 2007 -
Das umfassende
Handbuch

Jetzt bestellen


 Ihre Meinung?
Wie hat Ihnen das <openbook> gefallen?
Ihre Meinung

 Buchtipps
Zum Katalog: VBA mit Excel






 VBA mit Excel


Zum Katalog: Einstieg in VBA mit Excel






 Einstieg in VBA
 mit Excel


Zum Katalog: Excel 2010 im Controlling






 Excel 2010
 im Controlling


Zum Katalog: IT-Handbuch für Fachinformatiker






 IT-Handbuch für
 Fachinformatiker


Zum Katalog: Visual C# 2012






 Visual C# 2012


 Shopping
Versandkostenfrei bestellen in Deutschland und Österreich
InfoInfo




Copyright © Rheinwerk Verlag GmbH 2008
Für Ihren privaten Gebrauch dürfen Sie die Online-Version natürlich ausdrucken. Ansonsten unterliegt das <openbook> denselben Bestimmungen, wie die gebundene Ausgabe: Das Werk einschließlich aller seiner Teile ist urheberrechtlich geschützt. Alle Rechte vorbehalten einschließlich der Vervielfältigung, Übersetzung, Mikroverfilmung sowie Einspeicherung und Verarbeitung in elektronischen Systemen.


[Rheinwerk Computing]

Rheinwerk Verlag GmbH, Rheinwerkallee 4, 53227 Bonn, Tel.: 0228.42150.0, Fax 0228.42150.77, service@rheinwerk-verlag.de