Beginnen met spreadsheets - deel 3

Download hierboven de in dit artikel beschreven voorbeelddocumenten.

 

 

Prijslijsten bestaan uit kolommen: bestelnummer, omschrijving, inkoopprijs, marge, BTW, verkoopprijs enzovoort. Zodra de BTW verandert, hoeft u maar een getal te wijzigen om een nieuwe prijslijst te genereren. Ook als u een actie wilt doen met verlaagde prijzen is dat met een druk op de knop geregeld. Van de prijslijst kunt u afdrukken maken voor intern gebruik (met inkoopprijs) en om mee te geven aan een klant (zonder inkoopprijs en margeberekening).

 

Open het werkblad Excelcursus05.xls. Dit is een voorbeeldprijslijst van een boekhandel met in kolom A de titels van enkele boeken en in kolom B de inkoopprijs exclusief BTW; voor boeken geldt het lage BTW-tarief van 6%. Dit percentage en het gewenste margepercentage staan in de cellen C2, C3 en C4. In kolom C komt het margebedrag en in kolom E de verkoopprijs inclusief BTW.

 

 

Merk op dat de formules in rij 8 onjuiste resultaten geven. Dat komt omdat geen rekening is gehouden met de absolute verwijzing naar de cellen met de percentages. De verwijzingen naar de cellen C2 en C3 in de formules van rij 7 is relatief en veranderen na het kopiëren naar rij 8 in C3 respectievelijk C4 en dat is fout.

 

 

 

Het resultaat ziet eruit zoals in het werkblad Excelcursus06.xls. Overigens mag u als doelbestemming in stap 2 ook alleen de eerste rij van het bestemmingsgebied selecteren, dus C8:C14.

 

U kunt een celverwijzing ook absoluut maken door een cel een naam te geven. U geeft cel C2 bijvoorbeeld de naam marge, cel C3 noemt u BTW-laag en cel C4 noemt u BTW-hoog:

 

 

Een naam moet aan een aantal voorwaarden voldoen. Zo moet de naam beginnen met een letter en mag hij geen spaties bevatten. U mag wel hoofd- en kleine letters gebruiken, maar Excel maakt geen onderscheid tussen beide. Gewone streepjes mogen niet, wel een underscore (onderstreep-streep).

 

 

 

Zie het werkblad ExcelCursus07.xls en controleer uw resultaten. Tip: tijdens het maken van formules, waarin de btw moet worden opgenomen, kunt u de naam BTW_­laag typen of de cel C3 aanwijzen. In beide gevallen komt de naam BTW_laag in de formule te staan.

 

Om een naam te verwijderen of een naam aan een andere cel toe te kennen kiest u Invoegen >> Naam >> Definiëren. Wilt u een celnaam toekennen, nadat alle formules al zijn ingevoerd, dan kunt u via het menu Invoegen >> Naam >> Toepassen alle celverwijzingen in de formules omzetten in celnamen.

 

Eenvoudige 'Mijnheer van Dalen wacht op Antwoord' formules kunt u gemakkelijk typen. Zodra het wat ingewikkelder wordt zijn de ingebouwde functies van Excel handiger. U hebt al gezien hoe u een reeks getallen kunt optellen door middel van de SOM() functie. Zo kent Excel er nog veel meer. Een aantal daarvan passeert hier de revue; als voorbeeld dient ExcelCursus07.xls.

 

Om in cel B16 de gemiddelde verkoopprijs van de boeken te berekenen gebruikt u de functie GEMIDDELDE():

 

 

Het dialoogvenster Functie invoegen vermeldt in het vak Categorie verschillende functie-categorieën waaruit u kunt kiezen. In het vak Functienaam/Selecteer een functie staan de functies van de gekozen categorie. Als de categorie Alles is geselecteerd, verschijnt een alfabetische lijst van alle functies. De functie GEMIDDELDE bevindt zich (onder andere) in de categorie Statistisch.

 

 

Op het scherm verschijnt het dialoogvenster Functieargumenten dat bij de gekozen functie hoort. In dit venster moet u aangeven op welke cellen de functie betrekking heeft. Omdat de formule (in cel B16) onder een reeks getallen staat, gaat Excel ervan uit dat deze getallen in de formule verwerkt moeten worden: B7:B15. Dit zijn de cellen met de inkoopprijs, en het gaat erom de gemiddelde verkoopprijs te berekenen.

 

 

Plaats de cursor in cel B16 en kijk in de formulebalk. Daar ziet u de complete formule staan:

 

=GEMIDDELDE(E7:E14)

 

Deze formule had u ook volledig kunnen typen, maar dan moet u wel weten hoe hij opgebouwd is. Via Functie invoegen is dat gemakkelijker, omdat u geholpen wordt met informatie en het venster met de Functieargumenten.

Een functie in Excel bestaat altijd uit minimaal twee delen: een functienaam (GEMIDDELDE) en één of meer argumenten (E4:E11). De verschillende argumenten worden gescheiden door een puntkomma. Aan de hand van de argumenten bepaalt Excel de uitkomst van de functie. Een argument is bijvoorbeeld een getal, een celverwijzing of een celbereik. Hier volgen enkele voorbeelden van argumenten:

 

 

In cel B17 moet de verkoopprijs van het duurste boek komen te staan en in cel B18 van het goedkoopste boek. Dit doet u met de functies MAX() en MIN(). De opbouw van de functie lijkt veel op die van de functie GEMIDDELDE:

 

 

Doe hetzelfde met de functie MIN() in cel B18.

 

 

Als u één van de laatst gebruikte functies nogmaals wilt toepassen, kunt u ook als volgt te werk gaan: typ een is-gelijkteken en klik op de lijstpijl van het vak Functies die links op de formulebalk is verschenen; selecteer de gewenste functie en maak de formule af. Voeg zelf in cel B19 nog de functie AANTAL() toe om het totale aantal boeken in de lijst te berekenen, en bereken in cel B20 het totale BTW-bedrag van alle boeken. Gebruik hiervoor de functie SOM() en kies het bereik D7:D14.

 

Het resultaat ziet er nu uit zoals in ExcelCursus08.xls. Voordat u de prijslijst gaat afdrukken moet u weten, of de lijst voor intern gebruik is of aan klanten moet worden gegeven. In het laatste geval moeten er namelijk twee kolommen niet worden afgedrukt: kolom B en C met respectievelijk de inkoopprijs en de marge:

 

 

Om de verborgen kolommen weer terug te halen selecteert u de kolommen A en D, u rechtsklikt op deze selectie en kiest Zichtbaar maken.

 

Via de Help-functie van Excel is altijd informatie op te vragen over een bepaalde functie. Klik daartoe in het venster Functie invoegen de functie aan waarover u meer informatie wilt en klik linksonder in het venster op Help-informatie over deze functie. Het Help-venster of de Office-assistent verschijnt met meer informatie over de desbetreffende functie.