Mit dem Tool „Angebote und Rechnungen erstellen und verwalten“ können sowohl Angebote als auch Rechnungen professionell erzeugt und die Daten zur späteren Verwendung archiviert werden.
Für Selbständige und freiberuflich Tätige reicht es in vielen Fällen, ein einfaches Rechnungsausgangsbuch zu führen, welches nicht an eine Finanzbuchhaltung geknüpft ist. Wenn dies bei Ihnen zutrifft, dann wird Ihnen das nachfolgend näher erläuterte Tool gute Dienste erweisen. Das Programm besteht aus folgenden Tabellenblättern:
- Tabellenblatt Erfassen: Dieses Tabellenblatt ist Grundlage zur Erfassung von Angeboten und Rechnungen. Gleichzeitig befindet sich auf diesem Tabellenblatt der Button zum Verbuchen der erfassten Daten.
- Tabellenblatt geb.Rechnung: In diesem Tabellen-blatt werden die gebuchten Rechnungen aufgelistet.
- Tabellenblatt geb.Angebot: Im Tabellenblatt "geb.Angebot" werden wie im Blatt geb.Rechnung, die erstellten Angebote festgehalten.
- Tabellenblatt Offene.RG: Dieses Tabellenblatt dient zur Auflistung der offenen Rechnungen, das heißt es werden alle Rechnungen in dieses Blatt extrahiert, bei denen noch kein Geldeingang verzeichnet wurde.
In diesem Beitrag zeigen wir Ihnen anhand einer Schritt für Schritt-Anleitung, wie das Tool aufgebaut wird und wie es im Grundsatz funktioniert.
Sehen Sie sich als erstes das Blatt Erfassen etwas näher an, siehe Bild 1. Bevor Sie das Erfassungsblatt verwenden können, müssen Sie die Firmenbezeich-nung, die Anschrift sowie die Telefonnummern in der Kopfleiste und die Kontenbezeichnungen in der Fußleiste auf Ihre Wünsche hin abändern. In den Zellen A12, A13, A14, A16 und B16 wird die Kundenanschrift erfasst. Diese Felder sind mit gelber Farbe hinterlegt und mit einem Zellrahmen versehen. Sobald Sie jedoch einen Kundennamen in das Namensfeld eintragen, wird die Zellefarbe sowie die Zellum-randung automatisch entfernt. Dieser Effekt wird mit der bedingten Formatierung erzeugt.. Markieren Sie die Zelle A12 und tragen in das sich öffnende Dialogfeld folgende Bedingung ein: Zellwert gleich =“Anrede“ ein und wählen unter Format als Muster eine gelbe Hintergrundfarbe und aktivieren auf der Registerkarte Rahmen die Zellumrandung. Diese Einstellung nehmen Sie bitte auch in allen anderen Adressfeldern mit den entsprechenden Bedingungen vor.
Bild 1: Erfassungsblatt für Angebote und Rechnungen
Wenn Sie nach Fertigstellung der Rechnung oder des Angebots auf den Button Buchen klicken, wird die Rechnung verbucht und diese Adressfelder werden wieder mit den Standardeinträgen Anrede, Name / Firma, Straße, Hs-Nr, Plz und Ort überschrieben, somit werden durch die Funktion Bedingte Formatierung die zuvor gewählten Formatierungen wieder hergestellt. Den Code zum Setzen der Standardadressfeldinhalte sehen Sie später detailliert in Listing 5.
In Zelle F20 im Erfassungsblatt wird das Rechnungsdatum erfasst. Dieses können Sie, müssen Sie jedoch nicht manuell eingeben, da es mit einem Makro, siehe Listing 1, in diese Zelle automatisch eingetragen wird. Durch das Ereignis Worksheet_Activate erfolgt die Ausführung dieses Codes immer dann, wenn das Tabellenblatt "Erfassen" aktiviert wird.
Listing 1: automatische Erfassung des Rechnungsdatums
Private Sub Worksheet_Activate()
'Datum bei Aktivierung des Tabellenblattes Erfassung automatisch aktualisieren
Sheets("Erfassung").Range("F20").Value = Date
End Sub
Der Vorteil, das aktuelle Datum über ein Makro und nicht mit der Funktion =Heute() einzutragen besteht darin, dass das Datum jederzeit beliebig manuell abgeändert werden kann, ohne die erfasst Formel zu überschreiben. Da es sich beim Ereignis Worksheet_Acitvate um ein Ereignis für das Tabellenblatt Erfassen handelt, müssen Sie den Code aus Listing 1 im VBA-Editor auch zwingend in diesem Arbeitsblatt erfassen.
In den Zellen C25 bis D25 befindet sich ein DropDown-Element, aus dem zwischen Angebot und Rechnung gewählt werden kann. Damit in diesem DropDown-Feld diese Auswahlmöglichkeit zur Verfügung steht, muss dieses beim Start der Arbeitsmappe zuerst initialisiert werden. Tragen Sie dazu im Modul DieseArbeitsmappe im VBA-Editor das Makro aus Listing 2 ein.
Listing 2: Initialisieren des DropDown-Feldes und Aktu-alisierung des Datums
Private Sub Workbook_Open()
'Initialisierung der Combobox
With Sheets("Erfassung").ComboBox1
.AddItem "Rechnung"
.AddItem "Angebot"
End With
'Datum bei Aktivierung des Tabellenblattes Erfassung automatisch aktualisieren
Sheets("Erfassung").Range("F20").Value = Date
End Sub
Das Ereignis Workbook_Open bewirkt, dass das Makro bei jedem Start der Arbeitsmappe ausgeführt wird. Mit dem Befehl AddItem werden die Einträge Rechnung und Angebot zum DropDown-Feld hinzugefügt. Darüber hinaus wird bei jedem Start der Arbeitsmappe das aktuelle Datum in die Zelle F20 eingetragen.
In Zelle B28 befindet sich die Rechnungs- beziehungsweise Angebotsnummer, die ausgehend von den bereits verbuchten Rechnungen beziehungsweise Angeboten automatisch erzeugt wird. Angebote und Rechnungen haben unterschiedliche Nummernkreise. Damit bei Auswahl von Rechnung oder Angebot aus dem DropDown-Feld die richtige Belegnummer erzeugt wird, tragen Sie im VBA-Editor zum Tabellenblatt Erfassen den VBA-Code aus Listing 3 ein:
Listing 3: Angebots- bzw. Rechnungsnummer erzeugen
Private Sub ComboBox1_Change()
'Aufurf der Prozedur zum Erzeugen von Angebots- bzw.
'Rechnungsnummer
belegnummer
End Sub
Auf die Ermittlung dieser Belegnummern wird in der Folge dieses Artikels noch näher eingegangen. Ab Zelle B35 werden die einzelnen Auftragsdaten erfasst. Die Vorlage ist derzeit zur Abrechnung von Leistungshonoraren aufgebaut. Selbstverständlich können Sie diese Vorlage jederzeit an Ihre individuellen Bedürfnisse anpassen, beispielsweise wenn Sie zusätzlich ein Feld zu Erfassung von Mengenangaben oder Ähnlichem benötigen.
Die Ermittlung des Nettorechnungsbetrages erfolgt in Zelle F58, indem mit der Funktion =SUMME(F35:F57) die einzelnen Werte einfach addiert werden. In Zelle E59 können Sie über ein DropDown-Feld aus den Umsatzsteuersätzen 0, 7 und 19 Prozent wählen. Dieses DropDown-Feld wurde mit Hilfe der Gültigkeitsprüfung erzeugt. Gehen Sie dazu wie folgt vor. Markieren Sie die Zelle E59 und öffnen über den Menüpunkt Daten / Datentools / Datenüberprüfung das Dialogfeld zur Erfassung der Gültigkeitskriterien, siehe Bild 2.
Bild 2: Gültigkeitsprüfung zur Erfassung des Umsatzsteuersatzes
Wählen Sie im Feld Zulassen den Eintrag Liste und geben Sie als Quelle die Zahlen 19; 7; 0 jeweils durch ein Semikolon getrennt ein und klicken auf OK. Anschließend steht Ihnen die Auswahl der eingegebenen Umsatzsteuersätze über ein DropDown-Menü zur Verfügung. Damit dieser Wert in Zelle E59 nicht zu sehen ist, formatieren Sie die Schrift in dieser Zelle in der Farbe weiß. Damit Sie jedoch sofort erkennen können, wo sich die Zelle zum Auswählen des Umsatzsteuersatzes befindet, wurde ein Textfeld und ein Pfeil eingefügt. Damit diese beiden Elemente jedoch nur am Bildschirm zu sehen sind und nicht ausgedruckt werden, gehen Sie bitte wie folgt vor: Markieren Sie zuerst das Element und rufen über das Menü Format / Textfeld formatieren beziehungsweise Format /AutoForm formatieren das entsprechende Dialogfenster auf. In der Registerkarte Eigenschaften entfernen Sie den Haken bei Objekt drucken, damit diese Elemente nicht ausgedruckt werden. Um den Umsatzsteuersatz innerhalb des Textes in Zelle D59 anzuzeigen, geben Sie dort bitte folgende Funktion ein: ="zzgl. MwSt " &E59& " %"
Die Rechnungsvorlage ist jetzt weitgehend fertig. Was noch fehlt, ist der Button zum Starten des Buchungsvorganges. Fügen Sie dazu einen Button im Bereich der Zellen F63 und F64 ein und beschriften diesen mit dem Text Buchen. Wichtig ist außerdem, dass die Eigenschaft PrintObjekt auf den Wert False gesetzt wird, damit beim Ausdruck des Rechnungsblattes dieser Button ebenfalls nicht gedruckt wird. Weisen Sie diesem Button den Code aus Listing 4 zu. Nach einem Klick darauf wird die Verbuchung der Rechnung im Tabellenblatt "geb.Rechnung" beziehungsweise die Verbuchung des Angebots im Tabellenblatt geb.Angebot angestoßen.
Listing 4: Start des Buchungsvorgangs
Public Sub CommandButton1_Click()
'Dimensionierung der Variablen
Dim strAntwort As String
'Prüfen, ob der Rechnungsbetrag größer Null ist und Ausgabe eines
'entsprechenden Hinweises
If Sheets("Erfassung").Range("F61") <> 0 Then
buchen
Else
strAntwort = MsgBox("Der Rechnungsbetrag lautet 0,00 €!" & Chr(13) & _
"Möchten Sie diese Rechnung wirklich verbuchen", vbYesNo)
If strAntwort = vbYes Then
buchen
Else
Exit Sub
End If
End If
End Sub
Zu Beginn des Makros wird geprüft, ob sich in Zelle F61 ein Wert größer Null befindet. Ist dies der Fall, dann wird der Buchungsvorgang fortgesetzt, indem die Prozedur buchen, siehe Listing 5 ausgeführt wird. Im anderen Fall wird eine MessageBox eingeblendet, welche darauf hinweist, dass der Rechnungsbetrag auf 0,00 Euro lautet. Hier können Sie entweder mit Ja bestätigen und den Buchungsvorgang fortsetzten oder mit Nein abbrechen. In diesem Fall beendet der Befehl Exit Sub das Makro an dieser Stelle, ohne dass weitere Aktionen ausgeführt werden. Sehen Sie sich nun das Makro im Listing 5 zur Verbuchung der Angebots- und Rechnungsdaten näher an.
Listing 5: Verbuchung von Angeboten und Rechnungen
Listing 5: Verbuchung von Angeboten und Rechnungen
Public Sub buchen()
'Dimensionierung der Variablen
Dim lngLetzte As Long
Dim strObjblatt As String
‘Bildschirmaktualisierung wird aktiviert
Application.ScreenUpdating = False
'Prüfung, ob im DropDown-Feld der Eintrag Rechnung oder Angebot ausgewählt ist
If Sheets("Erfassung").ComboBox1.Value = "Rechnung" Then
'Zuweisen des Tabellenblattes geb.Rechnung
strObjblatt = "geb.Rechnung"
Else
'Zuweisen des Tabellenblattes geb.Angebot
strObjblatt = "geb.Angebot"
End If
'Prüfen, ob in Zelle B28 eine Belegnummer eingetragen ist
If Sheets("Erfassung").Range("B28").Value <> "" Then
'####Ermittlung der letzten Zeile in Spalte A des Tabellenblattes "strObjblatt"
lngLetzte = Sheets(strObjblatt).Cells(65536, 1).End(xlUp).Row + 1
'####Daten in Buchungsblatt "strObjblatt" übertragen
Sheets(strObjblatt).Cells(lngLetzte, 1).Value = Sheets("Erfassung").Range("F20").Value 'Datum
Sheets(strObjblatt).Cells(lngLetzte, 2).Value = Sheets("Erfassung").Range("B28").Value 'RG-Nr.
Sheets(strObjblatt).Cells(lngLetzte, 3).Value = Sheets("Erfassung").Range("A13").Value 'Name
Sheets(strObjblatt).Cells(lngLetzte, 4).Value = Sheets("Erfassung").Range("F58").Value 'Netto
Sheets(strObjblatt).Cells(lngLetzte, 5).Value = Sheets("Erfassung").Range("F61").Value 'Brutto
If Sheets("Erfassung").ComboBox1.Value = "Rechnung"
Then
Sheets(strObjblatt).Cells(lngLetzte, 6).Value = "JA" 'KZ RE offen setzen
End If
'Löschen der Anschrift und ersetzen durch die Vorga-ben
Sheets("Erfassung").Range("A12").Value = "Anrede"
Sheets("Erfassung").Range("A13").Value = "Name / Firma"
Sheets("Erfassung").Range("A14").Value = "Straße, Hs-
Nr"
Sheets("Erfassung").Range("A16").Value = "Plz"
Sheets("Erfassung").Range("B16").Value = "Ort"
'Löschen der Objektnummer
Sheets("Erfassung").Range("B28").ClearContents
'Löschen aller Aufträge Position 1-23
Sheets("Erfassung").Range("B35:F57").Select
Selection.ClearContents
Range("B35").Select
'Aufruf der Prozedur "belegnummer"
belegnummer
Else
'belegnummer erzeugen
belegnummer
'Hinweis Fenster, bezüglich Buchung ohne Angebots- bzw.
Rechnungsnummer
MsgBox "Eine Buchung ohne Angebots- bzw. Rechnungsnummer kann nicht erfolgen!" & Chr(13) & _
"Es wurde eine neue RG-Nr. erzeugt!" & Chr(13) & Chr(13) & _
"Bitte starten Sie den Buchungsvorgang erneut!"
Exit Sub
End If
'Bestätigung der ordnungsgemäßen Verbuchung
MsgBox "Buchungsvorgang erfolgreich abgeschlossen!", vbInformation, "Hinweis"
'Bildschirmaktualisierung wird aktiviert
Application.ScreenUpdating = True
End Sub
Zu Beginn des Makros werden die Variablen dimensioniert. Mit dem Befehl Application. ScreenUpdating = False die Bildschirmaktualisierung deaktiviert, um ein Flackern des Bildschirms zu vermeiden. Anschließend erfolgt über eine If-Then-Abfrage die Prüfung, ob im ersten DropDown-Feld im Blatt Erfassen der Eintrag Rechnung oder Angebot ausgewählt wurde. Der ausgewählte Eintrag wird der Variable strObjektblatt zugewiesen. Dies ist notwendig, damit die Verbuchung der einzelnen Werte im entsprechenden Tabellenblatt erfolgt.
Anschließend wird geprüft, ob sich in Zelle B28 eine Rechnungsnummer befindet. Ist dies der Fall, erfolgt die Ausführung der Then-Bedingung und der Buchungsvorgang wird gestartet. Der Befehl lngLetzte = Sheets(strObjblatt) . Cells(65536,1). End (xlUp).Row + 1 bewirkt, dass die erste freie Zeile im Tabellenblatt geb.Rechnung beziehungsweise geb.Angebot ermittelt wird, indem die erste Spalte durchsucht und die entsprechende Zeilennummer in der Variable lngLetzte abgelegt wird.
Anschließend werden die Daten im Tabellenblatt geb.Rechnung beziehungsweise geb.Angebot verbucht. Die Verbuchung erfolgt, indem die einzelnen Werte wie Datum, RG-Nr., Name, Nettobetrag und Bruttobetrag in das entsprechende Buchungsdatenblatt übertragen werden. Das Ergebnis der Verbuchung von Angeboten sehen Sie in Bild 3.
Bild 3: gebuchte Angebote
Zudem wird für jeden gebuchten Rechnungsda-tensatz das Kennzeichen JA in die Spalte „RE offen?“ gesetzt, siehe Bild 4. Das bedeutet, dass der Geldeingang noch nicht erfolgt ist. Erst wenn der Geldeingang erfolgt ist, ist dieses Kennzeichen manuell auf den Wert NEIN zu setzen.
Nachdem die Verbuchung von Angebot und Rechnung erledigt ist, werden im Tabellenblatt Erfassung die eingegebenen Werte wieder auf die Stan-dardvorgaben zurückgesetzt. So werden in die Zellen A12 bis B16 die Standardwerte für die Anschrift eingetragen. In Zelle B28 wird die Objektnummer gelöscht und die Zellen B35 bis F57 in denen die einzelnen Leistungsbeschreibungen eingegeben wurden, werden ebenfalls geleert. Im Anschluss an die verschiedenen Löschaktionen wird eine neue Rechnungs- beziehungsweise Angebotsnummer erzeugt, indem die Prozedur belegnummer aufgerufen wird. Dazu später mehr. Jetzt aber noch mal zurück zur If-Then-Abfrage am Anfang des Codes, in der geprüft wird, ob sich in Zelle B28 eine Belegnummer befindet. Sollte diese Zelle keinen Eintrag aufweisen, wird die Else-Bedingung ausgeführt. In dieser wird zuerst die Prozedur belegnummer aufgerufen, um eine neue Belegnummer zu erzeugen. Anschließend öffnet sich ein Hinweisfenster, welches darauf hinweist, dass eine Verbuchung ohne gültige Angebots- beziehungsweise Rechnungsnummer nicht vorgenommen werden kann. Sie werden aufgefordert, den Buchungsvorgang erneut zu starten. Danach wird das Makro mit der Funktion Exit Sub vorzeitig beendet.
Der Befehl Application .ScreenUpdating = True bewirkt, dass die Bildschirmaktualisierung wieder aktiviert wird. In Listing 6 sehen Sie, wie die Belegnummer (An-gebots- beziehungsweise Rechnungsnummer) automatisch generiert wird.
Listing 6: Ermittlung der Belegnummern
Nachdem die Verbuchung von Angebot und Rechnung erledigt ist, werden im Tabellenblatt Erfassung die eingegebenen Werte wieder auf die Stan-dardvorgaben zurückgesetzt. So werden in die Zellen A12 bis B16 die Standardwerte für die Anschrift eingetragen. In Zelle B28 wird die Objektnummer gelöscht und die Zellen B35 bis F57 in denen die einzelnen Leistungsbeschreibungen eingegeben wurden, werden ebenfalls geleert. Im Anschluss an die verschiedenen Löschaktionen wird eine neue Rechnungs- beziehungsweise Angebotsnummer erzeugt, indem die Prozedur belegnummer aufgerufen wird. Dazu später mehr. Jetzt aber noch mal zurück zur If-Then-Abfrage am Anfang des Codes, in der geprüft wird, ob sich in Zelle B28 eine Belegnummer befindet. Sollte diese Zelle keinen Eintrag aufweisen, wird die Else-Bedingung ausgeführt. In dieser wird zuerst die Prozedur belegnummer aufgerufen, um eine neue Belegnummer zu erzeugen. Anschließend öffnet sich ein Hinweisfenster, welches darauf hinweist, dass eine Verbuchung ohne gültige Angebots- beziehungsweise Rechnungsnummer nicht vorgenommen werden kann. Sie werden aufgefordert, den Buchungsvorgang erneut zu starten. Danach wird das Makro mit der Funktion Exit Sub vorzeitig beendet.
Der Befehl Application .ScreenUpdating = True bewirkt, dass die Bildschirmaktualisierung wieder aktiviert wird. In Listing 6 sehen Sie, wie die Belegnummer (An-gebots- beziehungsweise Rechnungsnummer) automatisch generiert wird.
Listing 6: Ermittlung der Belegnummern
Public Sub belegnummer()
'Dimensionieren der Variablen
Dim strObjblatt As String
Dim lngLetzte As Long
Dim intPos1 As Integer
Dim intJahr As Integer
Dim intLfdnr As Integer
'Prüfen ob Belegnummer für Rechnung oder für Angebot erzeugt werden soll
If Sheets("Erfassung").ComboBox1.Value = "Rechnung" Then
strObjblatt = "geb.Rechnung"
Else
strObjblatt = "geb.Angebot"
End If
'letzte Zeile ermitteln
lngLetzte = Sheets(strObjblatt).Cells(65536,
1).End(xlUp).Row
'Auswerten der letzten Belegnummer (Angebot bzw. Rechnung)
intPos1 = InStr(Sheets(strObjblatt).Cells(lngLetzte, 2),
"/")
intLfdnr = For-mat(Left(Sheets(strObjblatt).Cells(lngLetzte,2), intPos1 - 1), "00")
intJahr = Mid(Sheets(strObjblatt).Cells(lngLetzte, 2),
intPos1 + 1, intPos1 + 1)
'Neu generierte Belegnummer in das Tabellenblatt "Erfas-sung" schreiben
Sheets("Erfassung").Range("B28").Value = Format(intLfdnr +1, "00") & "/" & intJahr
'Formatierung der Belegnummer als String
Sheets("Erfassung").Range("B28").NumberFormat = "@"
End Sub
Das Makro analysiert die letzte gebuchte Belegnummer im Tabellenblatt geb.Rechnung beziehungsweise geb.Angebot und erzeugt daraus die neue fortlaufende Belegnummer, welche im Blatt Erfassen in Zelle B28 eingetragen wird. Es gilt zu beachten, dass die Belegnummer einen Schrägstrich (Slash) enthalten muss, damit das Makro belegnummer ordnungsgemäß funktionieren kann.
In Bild 4 sehen Sie einen Auszug aus dem Tabellenblatt geb.Rechnung. Ähnlich wie bei der Verbuchung der Angebote werden auch bei der Verbuchung der Rechnungen die Felder Datum, Rechnungsnummer, Name sowie Netto- und Bruttobetrag aus dem Erfassungsblatt ausgelesen und in das Tabellenblatt geb.Rechnung übernommen. Zusätzlich ist zu diesen Daten eine Hilfsspalte notwendig. Mit dieser Hilfsspalte werden alle Rechnungsbeträge, die mit einem JA im Feld Re offen? gekennzeichnet sind in dieser Spalte mit einer laufenden Nummer versehen. Diese Hilfsspalte ist Grundlage zur Anzeige aller offenen Rechnungen, das heißt es werden alle Rechnungen im Tabellenblatt Offene RG. angezeigt, die in Spalte F mit einem JA gekennzeichnet sind.
In Bild 4 sehen Sie einen Auszug aus dem Tabellenblatt geb.Rechnung. Ähnlich wie bei der Verbuchung der Angebote werden auch bei der Verbuchung der Rechnungen die Felder Datum, Rechnungsnummer, Name sowie Netto- und Bruttobetrag aus dem Erfassungsblatt ausgelesen und in das Tabellenblatt geb.Rechnung übernommen. Zusätzlich ist zu diesen Daten eine Hilfsspalte notwendig. Mit dieser Hilfsspalte werden alle Rechnungsbeträge, die mit einem JA im Feld Re offen? gekennzeichnet sind in dieser Spalte mit einer laufenden Nummer versehen. Diese Hilfsspalte ist Grundlage zur Anzeige aller offenen Rechnungen, das heißt es werden alle Rechnungen im Tabellenblatt Offene RG. angezeigt, die in Spalte F mit einem JA gekennzeichnet sind.
Bild 4: gebuchte Rechnungen im Tabellenblatt geb.Rechnung
Die laufende Nummer in Spalte G wird mit folgender Funktion ermittelt: =WENN(F4<>"";WENN(F4="JA" ;ZÄHLENWENN ($F$4:$F4;"JA"));"")
Tragen Sie diese Funktion in die Zelle G4, also in die erste Zeile der Hilfsspalte ein und kopieren diese mit dem Autoausfüllkästchen beispielsweise bis in die Zeile 1000 nach unten. Benötigen Sie mehr oder reichen weniger Zeilen, so kopieren Sie diese Formel entsprechend oft.
Mit dieser Funktion wird zuerst geprüft, ob sich in Zelle F4 ein Wert befindet oder ob diese leer ist. Ist in Zelle F4 ein Wert enthalten, dann wird mit Hilfe von ZÄHLENWENN() ermittelt, wie oft das Kennzeichen JA im entsprechenden Bereich enthalten ist. Durch das Kopieren der Formel beispielsweise in Zeile G5 wird die Funktion wie folgt angepasst: =WENN(F5<>"";WENN(F5="JA"; ZÄHLENWENN($F$4:$F5;"JA"));""). Entscheidend für eine korrekte Arbeitsweise der Funktion ist, dass die Angabe der Matrix, welche die Funktion ZÄHLENWENN() durchsuchen soll, teilweise absolute und teilweise relative Bezugsangaben enthält.
Nachdem die Formeln in die entsprechenden Zellen kopiert wurden, können Sie die Hilfsspalte G komplett ausblenden, da diese zum „Betrieb“ des Rechnungsbuches nicht weiter benötigt wird. Mit der Funktion Gültigkeitsprüfung wird in den Zellen F4 bis F1000 sichergestellt, dass nur die Werte JA und NEIN eingegeben werden können. Um die Gültigkeitsprüfung einzurichten, gehen Sie wie folgt vor. Starten Sie zuerst die Gültigkeitsprüfung und wählen anschließend bitte aus dem Feld Zulassen den Eintrag Liste aus. Im Feld Quelle erfassen Sie, wie in Bild 5 zu sehen, die zulässigen Werte JA und NEIN. Sobald Sie nun eine Zelle im Bereich F4 bis F1000 anklicken, erscheint neben der selektierten Zelle ein DropDown-Menü, in welchem Sie die Einträge JA beziehungsweise NEIN auswählen können.
Nachdem die Formeln in die entsprechenden Zellen kopiert wurden, können Sie die Hilfsspalte G komplett ausblenden, da diese zum „Betrieb“ des Rechnungsbuches nicht weiter benötigt wird. Mit der Funktion Gültigkeitsprüfung wird in den Zellen F4 bis F1000 sichergestellt, dass nur die Werte JA und NEIN eingegeben werden können. Um die Gültigkeitsprüfung einzurichten, gehen Sie wie folgt vor. Starten Sie zuerst die Gültigkeitsprüfung und wählen anschließend bitte aus dem Feld Zulassen den Eintrag Liste aus. Im Feld Quelle erfassen Sie, wie in Bild 5 zu sehen, die zulässigen Werte JA und NEIN. Sobald Sie nun eine Zelle im Bereich F4 bis F1000 anklicken, erscheint neben der selektierten Zelle ein DropDown-Menü, in welchem Sie die Einträge JA beziehungsweise NEIN auswählen können.
Bild 5: Gültigkeitsprüfung im Tabellenblatt geb.Rechnung
Da nun die Hilfsspalte zur Ermittlung der offenen Rechnungen funktioniert, sehen Sie sich das Tabellenblatt Offene RG. näher an. In diesem Tabellenblatt werden alle Rechnungen in fortlaufender Reihenfolge angezeigt, die im Tabellenblatt geb.Rechnung in der Spalte F, also im Feld RE offen? mit einem JA gekennzeichnet sind, siehe Bild 6.
Bild 6: Zusammenstellung aller offenen Rechnungen
Die entsprechenden Werte werden mittels folgender Funktion aus der Tabelle geb.Rechnung in das Tabellenblatt Offene RG. übernommen: =WENN(ISTNV (INDEX(geb.Rechnung! $A$4:$G$1000; VERGLEICH(ZEILE()-5; geb.Rechnung! $G$4:$G$1000;0); SPALTE()-1));""; INDEX (geb.Rechnung!$A$4:$G$1000; VERGLEICH (ZEILE()-5; geb.Rechnung! $G$4:$G$1000;0) ;SPALTE()-1))
Tragen Sie diese Funktion im Tabellenblatt Offene RG in Zelle A6 ein, damit das Rechnungsdatum übernommen wird. Zur Übernahme der Rechnungsnummer in Zelle B6 ist folgende leicht modifizierte Funktion notwendig:
=WENN(ISTNV(INDEX(geb.Rechnung!$ A$4:$G$1000; VERGLEICH(ZEILE()-5 ;geb.Rechnung! $G$4:$G$1000; 0);SPALTE()-2)); "";INDEX (geb.Rechnung! $A$4:$G$1000; VERGLEICH(ZEILE()-5; geb.Rechnung! $G$4:$G$1000;0); SPALTE()-2))
Wie Sie sehen, sind die beiden Funktionen bis auf eine Ausnahme identisch. Die Ausnahme besteht in der relativen Angabe des Spaltenbezuges, ausgehend von der Position im Tabellenblatt Offene RG in Bezug auf die Position im Tabellenblatt geb.Rechnung. Für die Spalten C (Summe Netto) und Spalte D (Summe Brutto) ist diese Spaltenberichtigung analog durchzuführen. Die Funktion für die Spalte C lautet demnach wie folgt:
=WENN(ISTNV (INDEX(geb.Rechnung!$A$4:$G$1000 ;VERGLEICH(ZEILE()-5;geb.Rechnung! $G$4:$G$1000;0) ;SPALTE()+1)) ;"";INDEX(geb.Rechnung!$A$4:$G$1000; VERGLEICH(ZEILE()-5; geb.Rechnung! $G$4:$G$1000;0) ; SPALTE()+1))
In Spalte D tragen Sie bitte diese Funktion ein:
=WENN(ISTNV(INDEX( geb.Rechnung!$A$4:$G$1000; VERGLEICH(ZEILE()-5; geb.Rechnung! $G$4:$G$1000;0); SPALTE()+1));""; INDEX(geb.Rechnung!$A$4:$G$1000; VERGLEICH(ZEILE()-5; geb.Rechnung! $G$4:$G$1000; 0) ;SPALTE()+1))
Kopieren Sie nun diese Funktionen wiederum beispielsweise bis zur Zeile 1000 nach unten. Selbstverständlich können Sie die Anzahl der Zeilen selbst bestimmen. Damit die Summe der offenen Rechnungen in Zelle D3 gebildet wird, tragen Sie dort bitte diese Formel ein: =SUMME(D6:D1000).
Da das Programm in der Grundstruktur nun fertig ist, können Sie sich daran machen, es für Ihre Bedürfnisse entsprechend anzupassen und gegebenenfalls zu erweitern.
Anfrage per E-Mail
+49(0) 151 164 55 914
Anfrage per Kontaktformular
Hilfe-Forum
