Factureren met Excel - deel 1

Op de volgende manier is VerkoopFactuur5.xls te downloaden en op uw systeem te installeren:

 

 

 

 

Let op: voor een goede werking van de factuurapplicatie is het belangrijk dat u het bestand eerst opslaat op uw harde schijf en daarna vanuit Excel opent.

 

Met een factuur die in Excel is opgebouwd, is meer mogelijk dan in een Word-factuur: adresgegevens ophalen in plaats van typen, factuurnummer bijhouden in plaats van opzoeken in de ordner, totaaloverzicht van alle facturen en factuurbedragen, ophalen van artikelomschrijvingen en prijzen, automatisch optellen en berekenen van kortingen en BTW. In dit artikel leert u hoe u een factuur (of een ander document) voorziet van een keuzelijst, waarmee u heel gemakkelijk alle adresgegevens van een relatie kunt ophalen. De factuur wordt automatisch berekend, u kunt kiezen uit betaalmogelijkheden en het BTW-percentage wordt automatisch berekend. In de hier beschreven sjabloon zijn ook de adresgegevens van uw relaties gemakkelijk bij te houden.

 

De te volgen stappen worden een voor een als een soort cursus beschreven, daar is alleen enige basiskennis van Excel voor vereist. De hier beschreven factuurapplicatie is compleet werkend te downloaden en te gebruiken, maar het wordt aangeraden dat u, ook als u deze kant-en-klare versie download, deze beschrijving toch leest. Op die manier krijgt u inzicht in de werking van de spreadsheet-factuur, wat van pas komt als u de factuur verder wilt uitbreiden of aanpassen. De gedownloade sjabloon plaatst u op uw vaste schijf in de map c:\Documenten en instellingen\uw naam\Toepassingsgegevens\Microsoft\Sjablonen\. In een Engelstalige omgeving heet de map Toepassingsgegevens Application Data.

 

In Excel 2002 bevindt zich reeds een factuursjabloon, dat in dit artikel verder wordt uitgebouwd en aangepast aan uw behoeften. Afbeelding 1 laat zien hoe het eindresultaat eruit komt te zien. Het betreft hier het ontwerp van een factuur voor een handel in kantoorartikelen, maar met enige fantasie is dat gemakkelijk aan te passen aan uw branche. Het spreadsheet zal bestaan uit twee delen, namelijk de factuur en het klantenbestand. Het eerste deel is er al en wordt aangepast. Voor het tweede deel wordt een extra tabblad aangemaakt. Vanuit dit klantenbestand worden de adresgegevens automatisch naar de factuur gekopieerd, zodat die niet elke keer opnieuw getypt hoeven te worden. Het geheel wordt opgezet als een sjabloon. In die vorm is de factuur telkens weer geschikt voor hergebruik; u kent het sjabloon-principe wellicht al van Word.

 

Doe eerst ervaring op met de werkwijze van de sjabloon, zoals die standaard in Excel 2002 zit. De werkwijze is als volgt: Kies Bestand >> Nieuw. In het Taakvenster Nieuwe werkmap kiest u Algemene sjablonen en opent u het tabblad Werkbladmodellen. Hier dubbelklikt u op de sjabloon Verkoopfactuur. De aanwijzer staat in het vak Naam. Hier noteert u de naam van uw klant. Druk op Tab; deaanwijzer gaat naar het vak Datum, waar u de factuurdatum invult. Druk weer op Tab en vul het Adres in. Daarna volgen Ordernr, Plaats, Provincie, Postcode, Contactpersoon, Telefoon en FOB. Als u nu weer op Tab drukt komt u bij de factuurregels: per regel vult u in het Aantal, een Beschrijving en een Stuksprijs. Excel vermenigvuldigt Aantal met Stuksprijs en zet dit totaal in de laatste kolom. Na de factuurregels tabt u naar Verzendkosten (waar u een bedrag kunt invullen) en Betaling; dit is een keuzelijst waar u kunt kiezen uit Contant, Rekening, Creditcard en Overig. Dan volgt BTW; als u hier 19 typt wordt dit veranderd in 19%. Tot slot kunt u in het vak Opmerkingen nog iets typen. Maak een afdruk en bekijk het resultaat.

 

De indeling van de factuur is op zich prima, alleen staan er velden op die niet ter zake zijn, zoals Provincie en Telefoon. De Postcode staat verkeerd en verder heeft FOB (Free On Board) in Nederland weinig betekenis. Op die plek kan beter het Debiteurnummer staan. Ordernr. moet worden Factuurnr. Als datumsuggestie zou de datum van vandaag reeds ingevuld kunnen zijn. Bij betaalwijze ontbreekt de optie Rembours. Verder ontbreken uw bedrijfsgegevens en logo. Ook zou het handig zijn als u de naam en adresgegevens van de klant uit een klantenbestand kon ophalen.

 

De factuur die nu op het scherm staat is een kopie van de oorspronkelijke sjabloon. Als u structureel zaken wilt wijzigen, moet u dat uiteraard doen in de sjabloon, niet in de kopie. Sluit daarom nu de kopie (zonder te bewaren) en open de sjabloon via: Bestand >> Openen. Klik achter Zoeken in en traceer de map c:\Program Files\Microsoft Office\Templates\1043\. Dubbelklik op Verkoopfactuur.xlt. Bewaar de sjabloon meteen onder een andere naam op uw eigen sjablonen-locatie: kies Bestand >> Opslaan als, zoek de map c:\Documenten en instellingen\uw naam\Toepassingsgegevens\Microsoft\Sjablonen\ op en bewaar de sjabloon daarin onder de naam Verkoopfactuur2.xlt.

 

In een Engelstalige omgeving heet de map Toepassingsgegevens Application Data. Met de cursor kunt u lang niet overal komen en veel opties in de menu's zijn lichtgrijs, dus niet actief. Dat komt doordat het werkblad is beveiligd. Om de beveiliging op te heffen kiest u Extra >> Beveiliging >> Blad beveiliging opheffen. Nu zijn de functies en knoppen wel actief.

 

Begin bovenaan en klik op Voeg hier de bedrijfsgegevens in. Vervang deze tekst door uw firmanaam. Druk op Alt+Enter om naar de volgende regel te gaan en typ uw adres; nogmaals Alt+Enter en u kunt postcode en woonplaats invullen. Of zet die twee gegevens achter elkaar en reserveer de derde regel voor telefoonnummer en faxnummer. Op de vierde regel noteert u uw bankrekeningnummer en gironummer. Selecteer de eerste regel en maak uw firmanaam vetter (met Ctrl+B) en iets groter (van 10 naar 12 punts).

 

Klik in een willekeurige andere cel om het resultaat te zien. Als uw firmanaam wegvalt, komt dat doordat onder de bankrekeninginformatie een of meer lege regels staan: klik in uw bedrijfsgegevens en plaats de aanwijzer in het formulevak achter het bankrekeningnummer. Druk daar op de Delete-toets, totdat het totale adresvak niet meer dan vier regels telt. Klik in een willekeurige andere cel om het resultaat te zien.

 

Op dezelfde wijze vervangt u onderaan de factuur de teksten Voeg hier uw leveringsvoorwaarden in en Voeg hier de afsluitende tekst in door toepasselijke teksten of verwijder ze. Bovenaan wilt u uw bedrijfslogo toevoegen: klik op cel L3 en verwijder de tekst. Als u een digitaal bestand van uw logo beschikbaar hebt, kiest u nu (terwijl de cursor nog in L3 staat) Invoegen >> Figuur >> Uit bestand. Is het logo te groot, klik er dan in, en pas de maat aan door de hoekpunten van het logo te verslepen. Sleep het logo vervolgens naar de juiste plek. Voeg indien van toepassing nog een logo of illustratie toe.

 

Het adresblok bevat enige irrelevante informatie. Klik op E15 en verwijder de tekst Provincie. Klik in G15 en verwijder de tekst Postcode. Klik in D15 en breidt de selectie uit tot en met H15; klik in de werkbalk op de knop Samenvoegen en centreren. Rechtsklik in de nu ontstane grote cel en kies Celeigenschappen. Op het tabblad Rand zorgt u voor een grijs bovenlijntje en onderlijntje. Klik in C16 en verwijder de tekst Telefoon. In cel L16 vervangt u FOB door Debiteurnr. De vier betaalwijzes in de keuzelijst van cel D38 vindt u in de cellen A2, A3, A4 en A5. Klik in A5 en type in het formulevak Rembours.

 

Het opbouwen van het klantenbestand (afbeelding 2) is een van de eenvoudigste onderdelen van de hele operatie. U kunt zelf een klantenbestand definiëren, adressen overnemen uit een bestaand (Excel-)bestand of het afgebeelde en te downloaden voorbeeld gebruiken.

 

 

De klantinformatie moet voorhanden zijn op een apart tabblad met de naam Klanten. Maak een werkblad aan door links onderin beeld te rechtsklikken op de tab Factuur; kies Invoegen >> Werkblad >> OK. Rechtsklik op de nieuwe tab Tab1 en kies Naam wijzigen: vervang de naam door Klanten en druk op Enter. Rechtsklik nu op de tab Factuur en kies Blad verplaatsen; klik in het gelijknamige dialoogvenster in de rubriek Voor blad op Klanten en sluit het venster via OK. Klik op de tab Klanten.

 

 

Om de adresgegevens te kunnen gebruiken op de factuur, zal gebruik gemaakt worden van formules. Omdat in formules bij voorkeur met namen wordt gewerkt, moet u de kolommen A tot en met H op het werkblad van het klantenbestand een naam geven: selecteer de 8 kolommen door boven in de eerste kolom in de kolomaanduiding A te klikken, de Shift-toets ingedrukt te houden en in de kolomaanduiding H van kolom H te klikken.

 

Kies Invoegen >> Naam >> Definiëren >> type Klanten en kies OK. Ook in de factuur krijgt een aantal cellen een naam (ook wel bereiknaam genoemd); zo is er een hulpcel nodig om straks een keuzelijst met klantnamen te kunnen maken. Klik op de tab Factuur en reserveer hiervoor de cel A1: klik in A1 en geef deze de naam Klantnummer via Invoegen >> Naam >> Definiëren.

 

Boven het adresvak komt een keuzelijst, waarmee de klant gekozen kan worden. Om te beginnen dient de werkbalk Formulieren te worden aangezet: Beeld >> Werkbalken >> Formulieren. Zijn de knoppen in deze werkbalk niet actief, dan hebt u de sjabloon nog niet van zijn beveiliging afgehaald (kies Extra >> Beveiliging >> Blad beveiliging opheffen). Klik in de werkbalk op de knop Keuzelijst met invoervak; de muisaanwijzer is nu een kruis. Met dat kruis tekent u onder de lijn met uw bedrijfsgegevens een vak. Een andere plaats mag ook; de lijst wordt toch niet afgedrukt. Sluit de werkbalk Formulieren vervolgens weer, die is niet meer nodig (klik op het kruisje boven in die werkbalk).

 

 

Vervolgens dient een aantal eigenschappen van de keuzelijst aangepast te worden: rechtsklik in de keuzelijst en kies uit het snelmenu de optie Besturingselementopmaken. Pas eventueel op het eerste tabblad Grootte de grootte van het vak aan. Zet op het tabblad Bescherming de optie Geblokkeerd uit. Op het tabblad Kenmerken dient de optie Object afdrukken uitgezet te worden; daarmee wordt voorkomen dat de lijst op de factuur wordt afgedrukt. Controleer dit straks eventueel in het afdrukvoorbeeld. Ten slotte volgen de belangrijkste instellingen: open het tabblad Besturingselement. Daar geeft u op welke informatie in de lijst moet worden getoond (in het vak Invoerbereik); tevens wordt hier de koppeling gelegd naar de cel Klantnummer door middel van

de functie Koppeling met cel.

 

Klik in het vak Invoerbereik en open het andere werkblad, door links onder in beeld op de tab Klanten te klikken. In het vak Invoerbereik ziet u nu Klanten! Staan. Terwijl het dialoogvenster Besturingselement opmaken op het scherm staat, selecteert u de kolom met Bedrijfsnamen, dus de cellen D2:D100. U kunt dit bereik vergroten als u meer klanten hebt. Selecteer in ieder geval ook alvast een paar lege regels voor nieuwe klanten. Al deze namen komen in de lijst te staan, ook de lege regels. Zodra het klantenbestand wordt uitgebreid verschijnen de nieuwe klanten automatisch in de keuzelijst, zonder dat u steeds het invoerbereik hoeft aan te passen.

 

In het vak Koppeling met cel typt u de naam van de cel waar het nummer van het geselecteerde item moet komen, in ons geval hulpcel A1 met de naam Klantnummer. Type in dit vak Klantnummer en sluit het dialoogvenster Object opmaken via OK. Zodra in de keuzelijst met klantnamen een keuze is gemaakt, wordt in deze cel het volgnummer van die keuze genoteerd. Controleer of dit werkt: open de keuzelijst door middel van het pijltje rechts in het vak en kies een van de namen. Controleer de inhoud van cel A1; de inhoud is af te lezen in het formulevak, nadat u in cel A1 hebt geklikt. Door middel van dat volgnummer in cel A1 kan de koppeling naar het adresgebied worden gelegd zodat de bij die klant behorende adresgegevens in het adresgebied worden geplaatst.

 

Aan de hand van het volgnummer in cel A1 worden de bijbehorende adresgegevens automatisch uit het klantenbestand opgehaald en in het adresgebied van de factuur geplaatst. Daarvoor worden in de cellen van het adresgebied formules geplaatst. Om ervoor te zorgen dat u niet de formules, maar de uitkomsten van die formules te zien krijgt, moet u de celeigenschappen van het adresgebied aanpassen: selecteer D13 tot en met D15, rechtsklik in de geselecteerde cellen, kies Celeigenschappen en wijzig op het tabblad Getal de Categorie in Standaard. Zet tevens op het tabblad Bescherming de optie Geblokkeerd uit. Sluit het dialoogvenster Celeigenschappen met OK.

 

In cel M16 wordt via een formule het debiteurnummer opgehaald uit het klantenbestand, dus ook van die cel stelt u de eigenschappen in op Standaard en niet-Geblokkeerd. Ook cel A1 moet op niet-Geblokkeerd staan: rechtsklik in A1, kies Celeigenschappen en vink op het tabblad

 

De syntaxis van de functie is:

 

 

De zoekwaarde is de waarde die u wilt zoeken in de eerste kolom van de tabel; in dit voorbeeld is dat de inhoud van de cel Klantnummer. De variabele tabel is het bereik of de tabel waarin de zoekwaarde, maar ook de aanvullende gegevens staan; in het voorbeeld is dat het klantenbestand op het tabblad Klanten. De kolomindex is het nummer van de kolom in de tabel waaruit de bij de zoekwaarde behorende informatie opgehaald moet worden; in dit voorbeeld is dat steeds een kolom met aanvullende adresgegevens. In de praktijk komt dat op het volgende neer. Om de bedrijfsnaam in het adresvak van de factuur te plaatsen, wordt in de cel D13 achter het kopje Naam de volgende formule geplaatst:

 

 

In gewoon Nederlands uitgelegd, beschrijft de functie het volgende: als in de keuzelijst de vijfde klant is geselecteerd, krijgt hulpcel A1 het klantnummer 5. De Vert.Zoeken-functie in de cel achter het kopje Naam gaat nu in de eerste kolom van de tabel Klanten (op het tabblad Klanten) verticaal op zoek naar de rij waarin het klantnummer gelijk is aan 5. Vervolgens haalt de functie uit de gevonden rij de bedrijfsnaam op door vier kolommen verderop de inhoud van de cel in de kolom Bedrijfsnaam te lezen (immers, de kolomindex is 4 en gerekend vanaf de kolom met de Klantnummers is dat de kolom Bedrijfsnaam). Het resultaat is dat de bedrijfsnaam in de cel achter Naam verschijnt. Typ bovenstaande formule in de cel naast het kopje Naam of bouw de formule zelf op met Functie invoegen (klik op de knop Functie invoegen links van het formulevak in de werkbalk). Test of na een andere klantkeuze inderdaad de juiste klantnaam verschijnt. In dat geval kunnen soortgelijke formules in de andere adrescellen worden genoteerd. Het volgende overzicht geeft aan hoe de gegevens door middel van formules worden opgehaald:

 

Celnaam	Functie
Bedrijf	=VERT.ZOEKEN(Klantnummer;Klanten;4)
Adres	=VERT.ZOEKEN(Klantnummer;Klanten;5)
Huisnummer	=VERT.ZOEKEN(Klantnummer;Klanten;6)
PC	=VERT.ZOEKEN(Klantnummer;Klanten;7)
Woonplaats	=VERT.ZOEKEN(Klantnummer;Klanten;8)
Debiteurnummer(M16)	=VERT.ZOEKEN(Klantnummer;Klanten;2)

 

Omdat Adres en Huisnummer in dezelfde cel (D14) moeten komen te staan, gescheiden door een spatie, noteert u in D14:

 

=VERT.ZOEKEN (Klantnummer;Klanten;5) & " " & VERT.ZOEKEN (Klantnummer;Klanten;6)

 

Postcode en Woonplaats worden in cel D15 samengevoegd met twee spaties ertussen:

 

=VERT.ZOEKEN (Klantnummer;Klanten;7) & " " & VERT.ZOEKEN (Klantnummer;Klanten;8)

 

De sjabloon is nu klaar, dus kunt u hem beveiligen, opslaan en sluiten. Beveiligen is nodig om cellen, waar niets ingevuld hoeft te worden te blokkeren: kies Extra >> Beveiliging >> Blad beveiligen, vink in het dialoogvenster Blad beveiligen de optie Ontgrendelde cellen selecteren aan en sluit het venster met OK. Kies Bestand >> Opslaan en vervolgens Bestand >> Sluiten. In de praktijk werkt de sjabloon als volgt: kies Bestand >> Nieuw en open in het Taakvenster via Algemene sjablonen het dialoogvenster Sjablonen.

 

Op het tabblad Algemeen dubbelklikt u op de verkoopfactuur. Selecteer in het keuzevak de klant voor wie de factuur bestemd is. Diens adresgegevens en debiteurnummer worden automatisch ingevuld (werkt dit niet en ziet u een melding over het opheffen van de beveiliging, dan zijn blijkbaar een of meer cellen nog geblokkeerd: cellen A1, D13, D14, D15, D16 en M16 mogen niet geblokkeerd zijn). Vul het factuurnummer in, typ de factuurregels, bepaald de verzendkosten en typ het BTW-percentage. Selecteer de af te drukken cellen (B2:N54), kies Bestand >> Afdrukbereik en druk de factuur af via Bestand >> Afdrukken. Sluit de factuur.

 

Als u adreswijzigingen van klanten moet doorvoeren of nieuwe klanten moet toevoegen, moet u dat in het sjabloonbestand doen, en niet in een kopie daarvan. Het sjabloonbestand opent u via: Bestand >> Openen. Klik achter Zoeken in en traceer de map c:\Documenten en instellingen\uw naam\Toepassingsgegevens\Microsoft\Sjablonen\. In een Engelstalige omgeving heet de map Toepassingsgegevens Application Data. Dubbelklik op de verkoopfactuur.

 

U ziet hoe eenvoudig het is om met Excel een factuur te produceren, als u over een goede factuursjabloon beschikt. Ook het bijhouden van de adresgegevens van uw relaties is eenvoudig. Op basis van dit sjabloon is uiteraard nog veel meer mogelijk, zo kunt u denken aan macro's die het factuurnummer voor u bijhouden, het ophalen van artikelgegevens, het bijhouden van de factuurnummers en factuurbedragen in een aparte lijst enzovoort. Daar komen we later op terug.