Programmeren met spreadsheets/Spreadsheets-basis

Spreadsheets: de basis

Een spreadsheet(bestand) bestaat uit één of meer rekenvellen (sheets). Elk vel bestaat uit een rechthoek van cellen, ingedeeld in rijen en kolommen. De positie van een cel in het vel geven we aan als <kolom><rij>, waarbij we voor kolommen opeenvolgende letters gebruiken, en voor rijen opeenvolgende getallen. Voorbeelden:

• de cel linksboven is A1
• de derde cel in de eerste rij is C1; dit is ook de eerste cel in de derde kolom.
• de vierde cel in de tweede kolom is B4.

In principe kun je meer dan 26 kolommen gebruiken: na A volgt AA, AB, AC, enzovoorts. Maar in de praktijk is het handiger om een spreadsheet niet te breed te maken, zodat deze nog op het scherm past. Je hebt dan aan enkelvoudige letters genoeg. Meestal laat je een spreadsheet "naar beneden groeien": dat werkt zowel handiger op het scherm als op papier.

Cellen

Een cel:

• is leeg; of
• bevat een eenvoudige waarde: getal of tekst; of
• bevat een formule.

Een datum (of tijd) is eigenlijk een bijzondere weergave van een getal: de cel bevat een getal, dat is de waarde waarmee gerekend wordt; op het scherm (en op papier) wordt dit getal als datum weergegeven (geformatteerd). Het getal wordt opgevat als het aantal dagen sinds de referentiedatum.

Vragen:

• wat is de referentiedatum?
• hoe kun je gebruik maken van de eigenschap dat een datum een geheel getal (volgnummer) is?
• kun je ook een datum voor die referentiedatum gebruiken?

Logische waarden (FALSE, TRUE) zijn bijzondere weergaven van de getallen 0 en 1. In een logische uitdrukking kan 0 of 1 gebruikt worden voor FALSE of TRUE. In een rekenkundige uitdrukking worden FALSE en TRUE als getallen 0 en 1 opgevat.

Let op: in Nederlandse varianten schrijf je FALSE als ONWAAR, en TRUE als WAAR. De logische functies FALSE() en TRUE() houden wel hun naam.

Opdrachten

• plaats het getal 1024 in cel A7
• plaats de tekst "Hallo Wereld" in cel C4
• plaats de datum "14 maart 2019" in cel B2

Weergeven van een waarde

Een cel met een getal kun je op verschillende manieren (formats) weergeven; als "normale" getallen, met of zonder decimale komma (of punt), als valuta (euro's, dollars, enz.), met of zonder punt (of komma) voor de duizendtallen, negatieve getallen tussen haakjes, enz. Bovendien kun je deze weergave rechts, links, of in het middel plaatsen; gewoonlijk staan getallen rechts in een cel.

Deze weergave verandert niets aan het getal in de cel: als je een getal met minder decimale weergeeft, verandert alleen de weergave op het scherm, niet het getal in de cel waarmee verder gerekend wordt.

Een tekst kun je links, rechts of in het midden plaatsen. Gewoonlijk staat een tekst links in een cel.

Een datum/tijd-waarde kun je ook op allerlei manieren weergeven.

Hierbij moet je vooral verdacht zijn op verwarring tussen de notaties die in Europa en de VS gebruikelijk zijn: 1/2/2019 betekent in de VS 2 januari 2019, elders 1 februari.

Let op!

Een cel met een tekst levert in een berekening de waarde 0 op. Dit kan erg misleidend zijn als die tekst een getal voorstelt. De misleiding is nog groter als die tekst bovendien rechts in de cel staat, zoals andere getallen.

Zie het voorbeeld:

Automatisch vullen Handig!

Soms wil je een hele reeks cellen met dezelfde waarden vullen, of met waarden die elkaar logisch opvolgen. Bijvoorbeeld: je wilt kolom A1,... vullen met de getallen 1, 2, 3, 4, ... Met behulp van de vulgreep kun je dit snel doen:

• vul de eerste waarden in, bijvoorbeeld A1: 1 en A2: 2;
• selecteer deze beide cellen
• je vindt de vulgreep rechts onderaan deze selectie
• klik en trek deze vulgreep naar beneden, zover als nodig is.

Formules

Een formule begint met een "="-teken. In een formule gebruik je de gebruikelijk rekenkundige operatoren, getallen, en verwijzingen naar cellen (adressen).

Verwijzingen

Er zijn twee soorten verwijzingen naar cellen:

• absolute verwijzingen: deze verwijzen altijd naar dezelfde cel (rij, kolom), ook als je de formule verplaatst of kopieert;
  • dit geef je aan door een $ te plaatsen voor de kolom-letter en/of voor het rij-getal; voorbeelden: $B2, B$2, $B$2
• relatieve verwijzingen: de afstand tussen de cel (rij, kolom) en de cel met de formule blijft constant, ook als je de formule verplaatst of kopieert.

We noemen dit ook wel absolute adressering en relatieve adressering.

Opdracht

1. plaats in cel A2 het getal 42
2. plaats in cel B2 de formule =A2 + 1
3. plaats in cel C2 de formule =$A$2 + 1
4. kopieer en plak de formule van B2 naar B3
5. kopieer en plak de formule van C2 naar C3

Verklaar het resultaat.

Functies

Je kunt in een formule functies gebruiken. Dit kunnen functies zijn die een enkele waarde als invoer ("parameter") hebben, bijvoorbeeld SQRT(A2) (voor de wortel van een getal).

Er zijn ook functies die een reeks waarden als invoer hebben, zoals een segment van een rij of van een kolom. Voorbeelden hiervan zijn: SUM(A2:A12), MAX(A2:E2) Deze functies vatten een reeks waarden samen tot een enkele waarde.

Gevorderd

• gebruik van draaitabellen (pivots)

Aanpak van "programmeren met spreadsheets"

• uitgangspunt: een programma beschrijft een proces - of liever, een klasse van processen;
• in een spreadsheet beschrijven we een enkel proces (een instantie);
• de stappen van het proces beschrijven we met opeenvolgende rijen ("de tijd gaat van boven naar beneden").
  • je kunt de rijen in de spreadsheet dan zien als een "uitgevouwen programma".
• elke rij hangt alleen af van de voorgaande rij: de toestand van het proces
  • soms geven we de invoer voor een stap in een aparte kolom: de rij hangt dan af van de toestand (vorige rij) en de invoer (in dezelfde rij).
  • soms gebruiken we twee rijen voor een enkele stap (bijvoorbeeld bij sorteren).
• in de meeste gevallen zijn de berekeningen in elke rij (of elk tweetal rijen) gelijk:
  • het algoritme bestaat dan uit een herhaling van dezelfde opdrachten;

Je kunt de spreadsheet in deze voorbeelden zien als een "uitgevouwen programma": omdat alle rijen hetzelfde kunnen we ook volstaan met een enkele beschrijving. En omdat we alleen de waarden uit de vorige regel gebruiken, kunnen we de "cellen" met deze waarden opnieuw gebruiken: dit is het idee van een variabele in programmeertalen als Python, Scratch of Java.

(Concurrent) assignment

Een spreadsheet heeft geen variabelen en geen toekenning (assignment): het is een functioneel programma, waarin je alleen de afhankelijkheden tussen de deelberekeningen weergeeft.

In de manier waarop wij spreadsheets gebruiken in de voorbeelden, als een proces met opeenvolgende stappen, is elke kolom een "variabele": de overeenkomstige cel bij stap n geeft aan hoe deze variabele in die stap uitgerekend wordt uit de waarden van de voorgaande rij. De toekenning van de variabelen in een rij kun je zien als een concurrent assignment. In een programmeertaal als Python zou je dit schrijven als: x, y, z = Ex, Ey, Ez.

Keuze

Een spreadsheet heeft veel kenmerken van een functionele programmeertaal. Dit betekent dat er geen assignment (toekenning) is: een cel heeft bij de uitvoering van een spreadsheet maar één waarde; een cel wordt niet, zoals een variabele in Python of Java, "hergebruikt".

Dit betekent dat we keuze uitdrukken in de vorm van formules (expressies) met een conditie; dit noemen we ook wel een conditionele expressie. Voor het bepalen van het maximum van twee getallen schrijven we dan: =IF(x >= y; x; y).

In Python schrijf je dit meestal als een conditioneel statement (IF-statement):

  if x >= y:
    max = x
  else:
    max = y

Overigens heeft Python ook een conditionele expressie: max = x if x >=y else y

Herhaling

De meeste voorbeelden die we geven bestaan uit een eenvoudig iteratief proces: na de initialisatie (eerste rij, n=0) volgt een reeks identieke stappen. Dit is een bepaalde klasse van algoritmen die zie goed leent voor het gebruik in spreadsheets.

Geen abstractie: geen eigen functies

Spreadsheets hebben geen mogelijkheden voor abstractie: je kunt een algoritme in een spreadsheet niet een naam geven.

Er zijn wel voorstellen geweest voor dergelijke uitbreidingen: deze zijn relatief eenvoudig toe te voegen, en hoeven het basismodel en de eenvoud van spreadsheets niet aan te tasten. (Zie voorstel Peyton-Jones et.al.)