Rechnungsvorlage mit Excel
Hinweis: Alle Maße und Zellenverweise sind nur Beispiele. Die angegebenen Seitenränder sind nicht DIN-konform. Bei Bedarf bitte anpassen.
Layout festlegen
Ich beginne die Erstellung meiner Rechnungsvorlage mit der Definition der Seitenränder (Registerkarte "Seitenlayout" ⇒ Gruppe "Seite einrichten").
Danach gehe ich zur Registerkarte "Ansicht" und klicke auf "Seitenlayout". Dann markiere ich alle Zellen, die auf eine Seite passen, und setze ihre Höhe und Breite auf jeweils 0,5 cm (Registerkarte "Start" ⇒ "Format" ⇒ "Zeilenhöhe" bzw. "Spaltenbreite"). So bin ich bei der Gestaltung meiner Rechnungsvorlage sehr flexibel. Danach markiere ich Zellen der ersten Seite und lege den Druckbereich fest (Registerkarte "Seitenlayout" ⇒ Gruppe "Seite einrichten").
Ich überlege, welche Felder an welcher Position ich haben möchte, verbinde dementsprechend die Zellen, wähle Füllfarbe und Rahmen aus und definiere Schriftgrößen und -farben.
Empfängeradresse-Fenster
Ab und zu versende ich meine Rechnungen per Post. Deshalb muss der Bereich der Absender- und Empfängeradresse so gewählt werden, dass er ins Fenster des Briefumschlages passt: Der Abstand vom Papierrand sollte oben 5 bis 5,5 cm, links 2 bis 2,5 cm betragen (die Höhe der Seitenränder beachten). Dieser Bereich sollte ca. 8 cm breit und 3,5 bis 4 cm hoch sein.
Empfängeradresse automatisch einfügen
Außerhalb des Druckbereichs aber so, dass man sie auf dem Bildschirm gut sehen kann (z.B. in der Zelle AM1), erstelle ich eine Auswahlliste (Auswahlliste erstellen) mit Daten der Spalte A ("Kunde") des Arbeitsblattes "Adressen" (s. Erfassung und Verwaltung von Kundendaten).
In die Zelle, die für die Empfängeradresse vorgesehen ist, trage ich folgende Formel ein:
=SVERWEIS($AM$1;Adressen!A:O;13;FALSCH)
Die Syntax der Funktion "SVerweis" hat folgende Argumente:
SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])
Das Suchkriterium ist in diesem Fall die Zelle mit der Auswalliste (AM1). Die Matrix ist der Bereich, in dem gesucht wird (Arbeitsblatt "Adressen", Spalten A bis O). Der Spaltenindex (13) ist die Spaltennummer aus welcher der Wert zurückgegeben wird (die 13. Spalte von A aus gezählt). Das Argument "Bereich_Verweis" gibt an, ob nach einer genauen oder einer ungefähren Entsprechung gesucht werden soll. Ist dieses Argument mit FALSCH angegeben, kann SVERWEIS nur nach einer genauen Entsprechung suchen.
Beim Auswählen eines Kunden aus der Auswahlliste der Zelle AM1 wird nun die Empfängeradresse automatisch eingefügt.
Ich werde meine Rechnungsvorlage so gestalten, dass alle wichtigen Rechnungsdaten automatisch per Knopfdruck (d. h. per Makro) erfasst werden. Dafür brauche ich noch zwei Angaben, die zwar nicht in der Rechnung erscheinen, enthalten aber wichtige Informationen über den jeweiligen Kunden: Kundensitz (Deutschland, EU-Land oder Drittland) und Kundentyp (Privatkunde, Direktkunde oder Agentur). Diese Daten brauche ich für Berechnung der Umsatzsteuer, Umsatzsteuervoranmeldung und Statistik. Sie werden auch mithilfe der Funktion "SVerweis" ausgegeben. Dafür habe ich Zellen AM2 und AM3 vorgesehen.
Ich trage in die Zelle AM2 folgende Formel ein: =SVERWEIS($AM$1;Adressen!A:O;11;FALSCH). Das bewirkt, dass in der Zelle AM2 der Kundensitz automatisch ausgegeben wird, wenn ich einen Kunden in der Zelle AM1 auswähle.
Die Formel in der Zelle AM3 lautet: =SVERWEIS($AM$1;Adressen!A:O;2;FALSCH). Sie gibt den Kundentyp zurück.
Ich trage in die Zelle AM2 folgende Formel ein: =SVERWEIS($AM$1;Adressen!A:O;11;FALSCH). Das bewirkt, dass in der Zelle AM2 der Kundensitz automatisch ausgegeben wird, wenn ich einen Kunden in der Zelle AM1 auswähle.
Die Formel in der Zelle AM3 lautet: =SVERWEIS($AM$1;Adressen!A:O;2;FALSCH). Sie gibt den Kundentyp zurück.
Sprachen der Rechnungsvorlage umschalten
Da ich Rechnungen manchmal auf Englisch und manchmal auf Deutsch erstelle, möchte ich meine Rechnungsvorlage so gestalten, dass ich zwischen diesen beiden Sprachen wechseln kann.
Zu diesem Zweck erstelle ich außerhalb des Druckbereichs (in der Zelle AL1) eine kleine Auswahlliste mit Angaben "Deutsch" und "Englisch" (Auswahlliste erstellen).
In allen auftragsunabhängigen Bereichen trage ich eine Formel nach folgendem Muster ein:
=WENN($AL$1="Deutsch";"Rechnung";"Invoice")
=WENN($AL$1="Deutsch";"Rechnungsnummer";"Invoice No.")
usw.
Nun kann ich alle allgemeinen Beschriftungen von Englisch auf Deutsch und umgekehrt umschalten.
Bei längeren mehrzeiligen Angaben wie z.B. eigene Adresse oder Bankverbindung empfiehlt sich die Verwendung von zusätzlichen Zellen. Wenn die Bankverbindung auf Deutsch sich in der Zelle AO1 befindet, und die gleichen Angaben auf Englisch in der Zelle AP1 stehen, so lautet die Formel:
=WENN(AL1="Deutsch";AO1;AP1)
Anmerkung: Um einen Zeilenumbruch innerhalb einer Zelle einzufügen, drückt man gleichzeitig Alt- und Enter-Taste.
Bilder per Dropdown auswählen
Wenn man für verschiedene Sprachvarianten verschiedene Logos hat, kann man sie auch mittels Dropdownliste auswählen.
Rechts unter der Rechnungsvorlage vergrößere ich zwei Zellen (AM60 und AM61), so dass in diese Zellen meine Bilddateien passen, und füge die Bilder in diese Zellen ein (Registerkarte "Einfügen" ⇒ Schaltfläche "Grafik" in der Gruppe "Illustrationen").
In den Zellen AN60 und AN61 trage ich Bildbeschriftungen "Englisch" und "Deutsch" ein.
Danach gehe ich zur Registerkarte "Formeln", klicke auf "Namensmanager" (Gruppe "Definierte Namen") und auf "Neu". Ich vergebe dem Bereich den Namen "Logo". Unter "Bezieht sich auf:" trage ich folgende Formel ein:
=INDIREKT("Rechnung!AM"&VERGLEICH(Rechnung!$AL$1;Rechnung!$AN:$AN;0))
"Rechnung!" ist der Name des Arbeitsblattes.
"AM" ist die Spalte mit zwei Bildern.
"$AL$1" ist die Zelle mit der Auswahlliste (Deutsch und Englisch).
"$AN:$AN" ist die Spalte mit den Bildbeschriftungen "Deutsch" und "Englisch".
Nun markiere ich die verbundenen Zellen, in denen das Logo stehen soll, klicke auf "Kopieren", gehe zur Registerkarte "Start", klicke auf "Einfügen" und wähle unter "Weitere Einfügeoptionen" die Schalfläche "Grafik" aus.
In der Bearbeitungsleiste verweise ich auf den Bereich "Logo", indem ich dort "=Logo" eintrage und die Eingabe mit der Enter-Taste beende. Wenn ich jetzt in der Zelle AL1 zwischen Englisch und Deutsch umschalte, wechselt sich entsprechend das Logo.
Auswahllisten
Um sich häufig wiederholende Daten schnell auswählen zu können, erstelle ich drei Auswahllisten (s. Auswahllisten erstellen):
· Dienstleistungsart (Übersetzen, Dolmetschen, Lektorat usw.)
· Abrechnungsmodus (Wörter, Zeilen, Stunden usw.)
· Angaben zum Zahlungsziel (7, 14 oder 30 Tage)
Wichtige Formeln
Rechnungsdatum: =HEUTE() gibt das aktuelle Datum zurück.
Der Nettobetrag wird ermittelt, indem die Anzahl der Zeilen mit dem Zeilenpreis multipliziert wird, z. B.: =N33*U33 (Zellenverweise sind Beispiele).
Summenformeln lauten z. B. =Y33+AC33 oder =SUMME(AG33:AK38).
Man kann auch den Zahlungstermin per Formel berechnen: Man summiert das Rechnungsdatum mit dem Zahlungsziel in Tagen.
Berechnung der Mehrwertsteuer
Wenn der Nettobetrag sich in der Zelle Y33 befindet, dann lautet die Formel für die Mehrwertsteuer: =RUNDEN(Y33*19/100;2).
Bei gewerblichen Kunden im EU-Ausland und bei Kunden in Drittländern (sowohl gewerblichen als auch privaten) wird keine Mehrwertsteuer erhoben. Ich verknüpfe deshalb diese Formel mit der Angabe in der Zelle AM2 (Kundensitz):
=WENN($AM$2="DE";RUNDEN(Y33*19/100;2);0)
Das heißt, dass bei Kunden aus einem Drittland oder aus dem EU-Ausland die Mehrwertsteuer 0 beträgt.
Wenn keine Mehrwertsteuer erhoben wird, weil der Sitz des Kunden außerhalb Deutschlands liegt, sollte auf der Rechnung der Grund dafür angegeben werden, und zwar in der deutschen oder englischen Sprache, je nach dem welche Sprachvariante gerade verwendet wird. Das kann man mit der Funktion "WENN-UND" erreichen.
Außerhalb des Druckbereichs trage ich folgende Sätze ein:
Zelle AM11: Steuerschuldnerschaft des Leistungsempfängers. (Deutsch / EU)
Zelle AM12: Reverse Charge. (Englisch / EU)
Zelle AM13: Leistungsempfänger im Drittlandsgebiet. (Deutsch / Drittland)
Zelle AM14: Place of performance is non-EU country. (Englisch / Drittland)
In die dafür vorgesehene Zelle der Rechnungsvorlage trage ich folgende Formel ein:
=WENN(UND($AM$2="Drittland";$AL$1="Englisch");AM14;
WENN(UND($AM$2="EU";$AL$1="Englisch");AM12;
WENN(UND($AM$2="Drittland";$AL$1="Deutsch");AM13;
WENN(UND($AM$2="EU";$AL$1="Deutsch");AM11;""))))
WENN(UND($AM$2="EU";$AL$1="Englisch");AM12;
WENN(UND($AM$2="Drittland";$AL$1="Deutsch");AM13;
WENN(UND($AM$2="EU";$AL$1="Deutsch");AM11;""))))
Diese Formel bewirkt, dass je nach Angaben in den Zellen AL1 und AM2 den richtigen Satz zurückgegeben wird.
Währungsbeträge richtig formatieren
Wenn man auf der Registerkarte "Start" auf das Euro-Zeichen klickt, werden Zahlen als Euro-Beträge formatiert. Wenn man aber Formeln hat, die Nullwerte zurückgeben, wird in diesen Zellen bei dieser Formatierung statt 0,00 € ein Strich angezeigt. Das möchte ich vermeiden.
Auf der Registerkarte "Start" kicke ich auf "Format" ⇒ "Zellen formatieren". In der Kategorie "Benutzerdefiniert" trage ich in das Feld "Typ" "0,00 €" ein und klicke auf "OK". Jetzt kann ich dieses Format für alle Zellen mit Euro-Beträgen wählen.
Nullwerte werden jetzt als "0,00 €" angezeigt. Ich gehe zur Registerkarte "Datei", "Optionen" ⇒ "Erweitert" und entferne das Häkchen bei "In Zellen mit Nullwerten eine Null anzeigen".
Alle Zellen mit Nullwerten sehen nun wie leere Zellen aus.
Namen vergeben
Später werde ich ein Makro erstellen, mit dem ich alle wichtigen Rechnungsdaten automatisch in einer Umsatz-Liste erfassen kann. Daten dieser Liste werden automatisch in Tabellen erfasst, die ich bei der Erstellung von Umsatzsteuervoranmeldungen und der Einnahmen-Überschuss-Rechnung verwende. Vorher werde ich jedoch den Zellen, deren Werte erfasst werden sollen, Namen vergeben.
Ein Name in Excel ist ein Wort oder eine Zeichenfolge, die für eine Zelle, einen Zellbereich, eine Formel oder eine Konstante steht. Durch die Verwendung von Namen können Formeln oder Makros einfacher verstanden und verwaltet werden. Wie man Namen am schnellsten vergibt ist hier beschrieben: Zellennamen definieren.
Eine andere Vorgehensweise wäre:
Hier habe ich die Zelle mit der Kundenliste (AM1) "Auftraggeber" genannt. Dieser Name gilt nur für das Arbeitsblatt "Rechnung":
Auf diese Weise definiere ich Namen für alle Zellen, die in der Liste der Umsätze berücksichtigt werden müssen, und zwar:
· Rechnungsnummer
· Rechnungsdatum
· Nettobetrag
· Umsatzsteuer
· Bruttobetrag
· Rechnungsbetrag
· Auftraggeber (Zelle AM1)
· Kundensitz (Zelle AM2)
· Kundentyp (Zelle AM3)
· Auftragsnummer
· Auftragsdatum
· Zahlungstermin
Ein Name in Excel ist ein Wort oder eine Zeichenfolge, die für eine Zelle, einen Zellbereich, eine Formel oder eine Konstante steht. Durch die Verwendung von Namen können Formeln oder Makros einfacher verstanden und verwaltet werden. Wie man Namen am schnellsten vergibt ist hier beschrieben: Zellennamen definieren.
Eine andere Vorgehensweise wäre:
- Auf die entsprechende Zelle klicken oder einen bestimmten Bereich markieren.
- Auf der Registerkarte "Formel" auf "Namen definieren" klicken (Gruppe "Definierte Namen").
- Den Namen eintragen. Die Namen dürfen keine Leerzeichen oder Sonderzeichen enthalten.
- Den Bereich auswählen: Hier wählt man aus, ob der Name für die gesamte Arbeitsmappe oder für ein bestimmtes Arbeitsblatt gelten soll. Beides hat Vorteile und Nachteile. Wenn der Name für die gesamte Arbeitsmappe gilt, braucht man in Formeln, die diesen Namen verwenden, den Arbeitsblattnamen nicht anzugeben. In diesem Fall darf der Name nur ein Mal in der gesamten Arbeitsmappe vorkommen (muss eindeutig sein). Deshalb muss man für Bereiche mit ähnlichen Daten immer neue Namen ausdenken.
Hier habe ich die Zelle mit der Kundenliste (AM1) "Auftraggeber" genannt. Dieser Name gilt nur für das Arbeitsblatt "Rechnung":
Auf diese Weise definiere ich Namen für alle Zellen, die in der Liste der Umsätze berücksichtigt werden müssen, und zwar:
· Rechnungsnummer
· Rechnungsdatum
· Nettobetrag
· Umsatzsteuer
· Bruttobetrag
· Rechnungsbetrag
· Auftraggeber (Zelle AM1)
· Kundensitz (Zelle AM2)
· Kundentyp (Zelle AM3)
· Auftragsnummer
· Auftragsdatum
· Zahlungstermin