1.3. Formules, adressen en functies¶
Met een formule in een cel kun je de waarde van die cel uitrekenen uit de waarden van andere cellen: dat is het basisidee van spreadsheets.
leerdoelen
leren omgaan met formules en celverwijzingen
1.3.1. Formules¶
Een formule begint met = gevolgd door de eigenlijke rekenformule.
In een formule kun je waarden, verwijzingen naar andere cellen, operatoren zoals +, -, * of /,
of functies gebruiken.
Voorbeelden van eenvoudige formules:
=A4+A5=SQRT(A5)=B3*0,21
Sommige functies gebruiken een reeks cellen als parameter.
Een reeks opeenvolgende cellen geef je aan als: “begincel” “:” “eindcel”,
bijvoorbeeld A1:A10
=SOM(A1:A10)=MIN(B2:F2)
We gebruiken in de opdrachten het volgende spreadsheet-voorbeeld:
Maak voor de volgende opdrachten een eigen kopie van dit voorbeeld, voor je eigen spreadsheetprogramma.
In je kopie van dit voorbeeld kun je de formules in de verschillende cellen bekijken: de formule van de actieve cel zie je ook in de formulebalk bovenin.
Wat is de formule in cel E2?
Wat is de formule in cel E3?
Wat zijn de verschillen/overeenkomsten tussen de formules in E2 en E3?
Via de formulebalk bovenin kun je de formule van de actieve cel aanpassen. Als je in de formulebalk klikt zie je de formule van de actieve cel, en bovendien zie je in de spreadsheet zelf de invoercellen van deze formule, met behulp van verschillende kleuren.
Opmerking In de formules zie je soms $-tekens staan.
Voor het begrijpen van de formule kun je die wegdenken.
Deze tekens zijn wel belangrijk als je een formule kopieert,
zie verderop bij absolute en relatieve celverwijzingen.
Opmerking Zolang de formulebalk actief is heeft het selecteren van cellen een andere betekenis: de geselecteerde cellen worden toegevoegd aan de formule. Je verlaat de formulebalk door een Return, of door te klikken op “Accept” (vinkje) of “Cancel (kruisje)”.
Opdrachten
invullen van de lege regel
invoegen van een nieuwe regel (met aanpassen van de sommatie-reeksen)
gebruik van een lege regel voor invoegen achteraf
Opmerking Als je een regel voor of na een reeks toevoegt blijft die reeks onveranderd; je moet formules met die reeks dan met de hand aanpassen. Bij het invoegen van een lege regel binnen een reeks wordt de reeks wel aangepast in de formule(s). Een handige oplossing is dan om een speciale lege regel aan het eind te gebruiken als onderdeel van de reeks, en nieuw regels steeds boven deze regel in te voegen.
1.3.2. Functies¶
In het voorbeeld hebben we ook een functie gebruikt voor het bepalen van de totaalprijs:
de formule in cel G7 is: =SOM(G2:G6).
SOM is hierin de functie voor het sommeren van de opgegeven reeks cellen.
Zoals eerder beschreven verwijst G2:G6 naar de cellen G2, G3, ..., G6.
Opmerking. We gebruiken hier de Nederlandse namen van de functies. Als je de engelstalige instellingen gebruikt, moet je in je spreadsheet ook de engelse namen gebruiken.
SOM(ofSUM) is een reductie-functie: hiermee vat je een reeks waarden samen in een enkel resultaat.andere voorbeelden van dergelijke reductiefuncties zijn:
count(aantal), avg (average, gemiddelde), min, max, product, and (voor logische waarden).
1.3.3. Absolute en relatieve celadressen¶
Vaak gebruik je dezelfde berekening in meerdere cellen,
denk bijvoorbeeld aan een kolom waarin je de BTW-waarde uitrekent van het bruto-bedrag,
of waarin je het bruto-bedrag en de BTW optelt tot het netto-bedrag.
Zoals je eerder gezien hebt verschillen de formules dan steeds een klein beetje:
de formule in G2 is: E2+F2, in G3: E3+F3.
Het zou veel werk zijn als je voor de berekeningen in G2, G3, G4, enz.,
steeds een nieuwe formule zou moeten invoeren.
Maar gelukkig werkt het kopiëren van formules, inclusief het werken met de vulgreep,
een stuk handiger.
Relatieve celadressen. In veel gevallen wil je bij het kopiëren van een formule, eventueel met de vulgreep,
de afstanden naar de andere cellen in de formule gelijk houden.
De berekening van het bruto-totaal in cel E2 is =C2*D2.
Bij het kopiëren hiervan naar E3 moet dit worden: =C3*D3;
enzovoorts, voor alle cellen daaronder.
Dat is ook precies wat er gebeurt als je deze formules kopieert:
=C2*D2in cel E2 zegt eigenlijk: vermenigvuldig de twee celwaarden links van deze cel.als je deze formule kopieert naar cel E3 wordt dit:
=C3*D3.
De celadressen in deze formules zijn relatieve adressen: bij kopiëren blijven de afstanden tussen de invoercellen van de berekening en de resultaatcel gelijk.
Absolute adressen. Het BTW-percentage is voor alle BTW-formules gelijk. Alle BTW-formules moeten naar dezelfde cel verwijzen: dit moet bij het kopiëren niet veranderen. Een dergelijke vaste verwijzing noemen we een absolute verwijzing.
We geven een absolute verwijzing aan door een $ voor de kolomletter en/of het rijnummer te plaatsen.
$D2- absolute kolom, relatieve rij;D$2- relatieve kolom, absolute rij;$D$2- absolute kolom en absolute rij (absolute celverwijzing).de formule voor de BTW-berekening in cel F2
=E2*$I$1.
Namen: benoemd bereik Een waarde die je met een absolute verwijzing aangeeft kun je ook een naam geven. In een formule gebruik je dan de naam in plaats van de celverwijzing. Hiermee maak je je formules beter leesbaar en voorkom je fouten door het vergeten van een $.
Opmerking: ga na dat we als formule voor de bruto-totaalprijs ook hadden kunnen gebruiken: =$C2*$D2
1.3.4. Opdrachten¶
selecteer cel D3 en vul cellen D4 en D5 met de vulgreep; wat is de formule in cel D3? wat is de formule in cel D5?
selecteer cel E3 en vul de cellen D4 en E5 met de vulgreep;
selecteer cel F3 en vul de cellen F4 en F5 met de vulgreep;
vul de volgende gegevens in:
(artikel), (aantal), (prijs/stuk)
(artikel), (aantal), (prijs/stuk)
tussenvoegen van een rij; kopiëren van de voorgaande rij; controleer de formules in de gekopieerde rij; en in de totaal-som. pas de rangnummers aan met de vulgreep.
verwijderen van een rij; controleer weer de formules van de laatste rij.
1.3.5. Algemeen concept: absolute en relatieve adressen¶
Het onderscheid tussen absolute en relatieve adressering kom je op veel meer plaatsen in de informatica tegen.
Het gebruik van relatieve adressen zorgt ervoor dat een berekening of formule gelijk blijft bij verplaatsing.
gebruik van relatieve padnamen in een hiërarchish filesysteem:
./x/yof../x/ygebruik van relatieve adressen in machinecode;
Het gebruik van absolute adressen zorgt ervoor dat je een (gedeeld) gegeven kunt vinden vanaf elke plek.
gebruik van absolute padnamen in een hiërarchisch filesysteem:
/var/www/html(begint met/)gebruik van absolute adressering in machinecode;