http://www.liacs.nl/~hoogeboo/praatjes/lapp-top/
Rekenen met Excel
Experimentele editie. Opmerkingen en suggesties welkom! (Welke informatie mist hier, welke ideeen kun je toevoegen?)
Open microsoft Excel, een bekend
voorbeeld van een spreadsheet (rekenblad).
Zo'n spreadsheet bestaat uit een
matrix van cellen die getallen, strings, of formules kunnen bevatten
(of geldbedragen, data, etc.).
Het spreadsheet rekent de waarden van de cellen uit op basis van de
formules. Omdat de formule in een cel naar een of meerdere andere vakjes
kan verwijzen kunnen er nuttige
(boekhoudkundige) overzichten mee bijgehouden worden.
Met een spreadsheet kan er gerekend worden zonder dat we echt
hoeven te programmeren, zowel de datastructuur (de opslag van de getallen)
als de berekeningen worden door Excel verzorgd.
Cellen zijn genumerd volgens kolom en rij. kolommen volgens letters,
rijen volgens cijfers. C4 staat in derde kolom, vierde rij.
Als je de cursor in een cel zet zie je de inhoud boven in beeld
(bij fx).
Indien de cel een formule bevat zie je daar de formule, in het rekenblad
zelf zie je
de waarde die daarmee berekend is.
Eerste Bijeenkomst
1. Driehoek van Pascal
Zet in cel A1 de waarde 1.Zet in cel B1 de formule =A1
Kopieer de formule naar C1 tm. N1 (of verder) -- door hem over een groter gebied 'te slepen' of met ctrl-c, ctrl-v (een blok selecteer je met de shift-toets).
Nu wordt de waarde 1 over de hele eerste rij overgedragen. als je in A1 de waarde 2.4 zet
Klik op cel C1. daar staat de formule =B1, niet =A1, dat komt omdat excel hier werkt met relatieve adressering. Bij het kopieren van formules worden de adressen opgehoogd. Absolute adressen kunnen ook, dan gebruik je een dollar bij de desbetreffende coordinaat $A$1, met als gevolg dat bij het kopieren deze verwijzing precies gelijk blijft. Je kunt ook gemengde vormen gebruiken,dus A$1 of $A1.
Zorg er op dezelfde manier voor dat de eerste kolom de waarde uit A1 naar beneden doorgeeft, zeg tot A18.
Zet nu in cel B2 de formule =A2+B1 deze telt de inhoud van de buren op.
Kopieer die formule naar het hele blok B2 tm. N18 (of verder).
De driehoek van Pascal ontstaat, op zijn kant.
2. Fraktal
Verander de waarde in cel B2 naar =A2+B1-2*A2*B1, en kopieer naar het blok dat je gebruikt (uitgezonderd de randen links en boven, zoals eerder).Wat doet deze formule met de waarden? Maak een tabelletje met de vier gevallen. (zie XOR gate) Vergelijk dit met het optellen van even/oneven getallen.
Welk patroon zie je? Sierpinski triangle
Het patroon wordt duidelijk door het gebruiken van voorwaardelijke opmaak. Je kunt de cellen bijvoorbeeld zwart maken als de inhoud ervan gelijk is aan 1. Het wordt nog mooier als je de cellen heel smal maakt, zie opmaak, kolom, breedte.
3. Alignment
Ook het algoritme van alignment kan in een spreadsheet worden gestopt. Reserveer een stuk van het rekenblad voor een matrix. Zet in de bovenste rij en de eerste kolom een reeks (DNA) letters die de twee strings voorstellen. Laat ruimte voor een nulde kolom en een nulde rij (net als in het algoritme).
stap 1.
Bepaal een formule die de letters met elkaar vergelijkt.
In iedere cel moet je de letter bovenaan de kolom vergelijken met
die vooraan de rij.
Je hebt hier zowel absolute als relatieve adressen nodig,bijvoorbeeld:
de cel in de eerste rij (absoluut) van dezelfde kolom (relatief).
Je kunt een vergelijking met twee uitkomsten opgeven met een
test die als volgt in excel gegeven wordt:
IF(test,waarde,onwaarde)
Bijvoorbeeld
IF(B1=C1,-3,1) wil zeggen,
als cel B1 dezelfde waarde heeft als cel C1
dan heeft de huidige cel waarde -3, anders is die waarde 1.
stap 2. Bepaal de formules die de matrix initialiseren, dwz. de nulde kolom en nulde rij vullen met de initiele waarden (het aantal posities maal de gap-penalty).
stap 3.
De formule die de letter boven de kolom vergelijkt met de letter
vooraan de rij levert de straf/beloning in de cel voor de (mis)match.
Die moet nu aangevuld worden met een maximum van de drie buurwaarden.
Het maximum van drie waardes wordt berekend door
MAX(w1,w2,w3)
spoiler: In cel E7 heb ik de formule =MAX(E6-1,D7-1,D6+ALS(E$3=$A7,2,-1))
stap 4. Als extraatje kun je drie cellen gebruiken om de waardes voor match, mismatch en gap-penalty in te zetten. Als de formules naar deze (absolute) adressen verwijzen kun je de drie variabelen naar keuze aanpassen.
stap 5.
Het is ook mogelijk de backtrace informatie door kleuren in de cellen
aan te geven. Als de waarde van een cel precies de match scheelt met de
linkerbovenbuur dan volgt de alignment de diagonaal (of moet dan nog getest
worden of de letters ook gelijk zijn?).
Iets vergelijkbaars bij mismatch.
Tweede Bijeenkomst
4. Cellulaire automaten
Bij een cellulaire automaat hangt de waarde van elke cel af van de waardes van de drie bovenburen. Hier nemen we even aan dat de cellen slechts de waardes 0 en 1 kunnen bevatten. De drie bovenburen van een cel hebben dan in principe acht mogelijke waarden. De cellulaire automaat werkt volgens een vast recept (of regel). Bij elk van de acht mogelijkheden is de waarde van de cel eronder vastgelegd.Bepaal eerst een formule die overeenkomt met een regel van een cellulaire automaat, en gebruik deze dan in een spreadsheet om een plaatje van het bijbehorende patroon te genereren.
Hieronder volgen drie voorbeelden van regels, namelijk 30, 90 en 62. Kies een regel, knutsel een formule voor die regel, en kijk naar het patroon dat ontstaat door één 1 midden in de bovenste rij te plaatsen. Is er regelmaat in het patroon, of juist chaos? Wat gebeurt er bij een ander beginpatroon?
Regel 30
Hier het recept van regel 30.| current pattern xyz | 111 | 110 | 101 | 100 | 011 | 010 | 001 | 000 |
| new state for center cell | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0 |
Bepaal een wiskundige formule die bovenstaand recept vastlegt. Dus, als de drie opeenvolgende waardes gelijk zijn aan x,y,z, geef dan regel 30 als functie van x,y,z. Hier heb je wel een kladbaadje voor nodig.
spoiler: ik gebruik
(1-x)*(y+z-y*z)+x*(1-y)*(1-z)
Je kunt ook een spreadsheet gebruiken om de regel te controleren.
Drie kolommen voor x,y,z, met alle acht mogelijkheden voor de
drie variabelen en een vierde kolom die de formule uitrekent.
In het eerste jaar van de studie wordt behandeld hoe je in dit
soort gevallen bij een waarheidstabel (de lijst met nullen en enen)
logische expressies kunt maken die het gewenste
resultaat geven, met behulp van Karnaugh diagrammen,
¬x ·(y + z) + x · ¬ y · ¬ z .
Opvallend in deze expressie is dat de logische +, de 'of',
verschilt van de rekenkundige: '1+1=1', oftewel 'waar of waar = waar'
simpele functies in excel
Je kunt excel simpele functies laten uitvoeren door de originelen en de functiewaarden naast elkaar in een tabel te zetten. Met de opdracht LOOKUP( orig, A1:A8, B1:B8 ) wordt ik kolom A1 tm. 8 gezocht naar de waarde orig. Het resultaat is dan de waarde die staat op de naastgelegen cel uit B1 tm. 8.Het is het makkelijkst om de acht configuraties 000, 001, ..., 110, 111 te vertalen naar de getallen 0, 1, ..., 6, 7 (alsof het binaire getallen zijn) en hiervan een tabel te maken met waardes uit de te gebruiken regel.
Regel 90
Alternatief, regel 90.| current pattern xyz | 111 | 110 | 101 | 100 | 011 | 010 | 001 | 000 |
| new state for center cell | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 0 |
Regel 62
Tenslotte, regel 62.| current pattern xyz | 111 | 110 | 101 | 100 | 011 | 010 | 001 | 000 |
| new state for center cell | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 0 |
Excel files
Ter inspiratie, or if all else fails ...driehoek pascal; resp. sierpinski
(1) alignment (alleen score); (2) met parameters; en nu ook de (3) uitgerekende alignment (backtrace, twee strings met gaps) 11.2'09 backtrace verbeterd
Alignment zet letters strings netjes langs tabel (Maarten, LappTop deelnemer 2013);
test formule 30;
project van bob
Gevonden op
internet:
alleen het nummer van de regel ingeven,
formule volgt dan automatisch.