HANDLEIDING REKENBLAD EXCEL (OFFICE 2000)

Vrije Universiteit Brussel Faculteit Wetenschappen

HANDLEIDING REKENBLAD EXCEL (OFFICE 2000)

Prof. Dr. Frank De Proft

1 1. INLEIDING 1.1. Rekenbladen (spreadsheets) We zullen in de oefeningen gebruik maken van het spreadsheetprogramma Excel. Als je Excel opent dan krijg je een window te zien zoals hieronder staat afgebeeld. Het bijzondere van een spreadsheet programma is dat men in elke cel (bv. in de geselecteerde cel C3) niet alleen getallen, maar ook formules kan plaatsen die gebruik kunnen maken van getallen die elders in het rekenblad staan. Er zijn tevens ook functies in Excel aanwezig om reeksen van getallen te gaan analyseren of om er grafieken van te maken. Het is mede daarom dat rekenbladen vaak gebruik worden in wetenschappelijk onderzoek.

1.2. Het Excel document window Bij het openen van het programma Microsoft Excel komt men in het document window. We kunnen hierin verschillende zones onderscheiden : Titel bar

Format toolbar

Standard toolbar

Naam workbook

Rij hoofding

Hoofdmenu bar Name and formula bar

actieve cel celhoofding

Kolom hoofding

huidige sheet sheet tabs

Een werkboek dient bij voorkeur zodanig georganiseerd te zijn dat de inputdata, de bewerkingen en de resultaten (outputgegevens, grafieken) logisch gerangschikt zijn. Dit zal betekenen dat voor sommige toepassingen alles op hetzelfde sheet staat, maar dat bij andere toepassingen verschillende sheets gebruikt worden.

− 1−

2 Een cel kan diverse categorieen van gegevens bevatten : » Constante

bv. = 20 (let op het "=" teken in de eerste positie van de kolom)

» Variabele

bv. 100; of C32 (relatieve referentie naar cel C32); of AbsT (referentie naar de variabele AbsT)

» Formule

bv. =EXP(C32+AbsT) (let opnieuw op het "=" teken in de eerste positie van de kolom)

» Tekst

Hier kan elke willekeurige tekst staan.

1.3. Basisbewerkingen Start Excel applicatie Dubbel klik op het Excel icon of op het Excel document icon Creëren, openen en sluiten van een Excel document - File menu : New, Open, Close, Save - Toetsenbord commando's (shortcuts) : CNTR-N, CNTR-O, CNTR-S - Toolbar buttons :

New, Open, Save Beschrijving van de gebruikersinterface We geven hier als voorbeeld het gebruik van de name en formula bar. Je kan deze personaliseren door View/Toolbars/Customize te kiezen en de gewenste button naar een toolbar te slepen. Deze bevat

1

2

− 2−

3 1. Namebox : bevat de cel referentie of een naam (identifier) die je zelf aan de inhoud van de cel kan geven. Een naam ken je toe door de gewenste naam in de Namebox te typen en te bevestigen door return te drukken. Je kan ook een naam toekennen aan een cel door de cel te selecteren en dan Insert/name/define te kiezen. Namen hoeven ook niet beperkt te zijn tot letters, je kan ook woorden gebruiken, bv. GasConstante. Deze zijn niet gevoelig aan hoofdletters of kleine letters, dus GasConstante en GASCONSTANTE worden als één en dezelfde naam behandeld. Bekijken we als voorbeeld het volgende rekenblad. Wanneer we de cellen B3:E4 selecteren en Insert/Name/Create kiezen, dan verschijnt het volgende dialoogvenster :

Excel heeft text in de bovenste rij van het geselecteerde venster ontdekt en getalwaarden in de onderste rij en neemt terecht aan dat je de namen van de bovenste rij wil toekennen aan de getallen van de onderste. Je kan bevestigen door OK te klikken. Merk op dat we de naam "c" niet aan een getal kunnen toekennen omdat Excel deze letter reeds voor andere doeleinden gebruikt (dit is bv. ook het geval met "r"). Je kan echter een underscore toevoegen om als naam "c_" te creëren.

2. Formule bar : een formule, constante of een tekst. Hier zal komen te staan wat je in de cel typt. Echter een formule zal na bevestiging (Return/Enter) een waarde teruggeven in de cel.

− 3−

4 Invoeren van data ; verwijderen, kopiëren en verplaatsen Vullen van een celblok (Plus handle) Als de cursor de vorm van een Plus handle ( ) aanneemt, dan kan je de actieve cel gaan vullen. Een cel kan tekst, een waarde of een formule bevatten.

Bij het afdrukken wordt de gehele tekst weergegeven. Het "ankerpunt" voor deze tekst ligt bij cel A2. Dus om de tekst te selecteren gaat men naar A2. Formule :

De formule staat in het edit-venster van de formule bar. Merk op dat de waarde in de cel staat. Een formule begint steeds met een "=" teken. Edit cel (gehele cel) en in-cel editing (deel van de inhoud van de cel) Om de inhoud van een cel te veranderen wordt de cel en de inhoud geselecteerd (dit doe je door dubbel te klikken op de beschouwde cel). Daarna kan je de inhoud gaan veranderen. Met Edit/Clear of de backspace toets verwijder je de inhoud van de cel. Cut, copy, paste, verplaatsen 1. Je kan dit doen vanuit het Edit menu 2. Je kan gebruik maken van de buttons in de toolbar 3. Je kan ook werken m.b.v. drag and drop (wanneer je de CNTR toest ingedrukt houdt copieer je) (Arrow handle). De cursur verandert in de Arrow handle als je deze beweegt naar de rand van de geselecteerde cel (of een reeks van cellen). Men houdt − 4−

5 de linker muisknop ingedrukt en verplaatst de cel(len) naar de gewenste positie. Als men tegelijkertijd de CNTR toets ingedrukt houdt wordt de inhoud gecopiëerd. Men ziet dit aan het + teken rechts van de arrow handle. Met de Fill handle kan de inhoud van een geselecteerde cel in een list worden geëxpandeerd.

De cursor verandert in de fill handle als men deze op de

rechterbenedenhoek van de geselecteerde cel (of een groep van cellen) zet. Hij verandert dan in een vette +. Deze handle is uitermate handig als men de cellen wil vullen uitgaande van de inhoud van de geselecteerde cel. Wanneer je bv. de getallen 1 t.e.m. 20 in de cellen A1:A20 moet invoeren moet je dus niet alle getallen expliciet intypen ; het volstaat in cel A1 het getal 1 en in cel A2 het getal 2 in te voeren en met de Fill handle de cellen A3 t.e.m. A20 te vullen. Vul als oefening nu eens in cel C1 "January" in en gebruik nu eens de Fill handle. Je krijgt een verrassend resultaat. Excel kent inderdaad zgn. custom lists die op deze manier gekopieerd worden. Men kan zelf ook dergelijke lijsten definiëren en op deze wijze gebruiken (zie Tools/Options). Bij het gebruik van de Fill handle kan de inhoud van de cel een tekst, een waarde van een getal of een formule zijn. Bij het kopiëren zullen relatieve referenties in de formules zich aanpassen. Bekijken we even het volgende voorbeeld :

Zoals je kan zien staat in cel B1 de formule SQRT(A1) (dit betekent dat in cel B1 de vierkantswortel zal komen te staan van het getal in cel A1). Met de Fill handle kan je dan B2 en B3 vullen. Zoals je kan zien hebben de formules zich automatisch aangepast (zo staat in cel B3 bv. SQRT(A3)). Soms wil men dit echter niet. Je kan in dat geval absolute referenties gaan gebruiken ; het gaat hier (zie ook verder) om celreferenties met $ tekens. Zo is $C$3 een absolute referentie voor cel C3.

− 5−

6 Undo en repeat (Redo) Dit maakt de laatste handeling ongedaan of herhaalt deze. Je kan dit invoeren door in het Edit menu te gaan of de buttons

te gebruiken.

Merk op dat het bij Undo en Repeat steeds om de laatste handeling gaat. Dit kan typen zijn, maar ook het invoegen van een kolom cellen of het formatteren van een cel. Split optie

Deze optie is handig wanneer je te maken hebt met grote worksheets. Hierdoor kan men één of meerdere kolommen of rijen bevriezen, terwijl men de andere cellen op gebruikelijke wijze kan laten scrollen. De splitoptie staat onder Window/Split.

1.4. Spreadsheettechnieken Formules en operatoren Zoals gezegd kan je een cel vullen met een constante, een variabele of een formule. Een formule in een cel begint altijd met = ; bv. =20; =b1 of =temp (als deze identifier gedeclareerd is). Een voorbeeld is = A1+A2+A3 die de waarde teruggeeft van de som van de getallen die in de cellen A1, A2 en A3 staan. Let bij het schrijven van formules op de prioriteit van de operatoren ; deze is Negatie (−) Machtsverheffing (^) Vermenigvuldiging en deling (*,/) Optelling en aftrekking (+,−) − 6−

7 Deze volgorde kan opgeheven worden door het gebruik van haakjes. Zo zal 2*5+6 gelijk zijn als 16 terwijl 2*(5+6) als resultaat 22 zal opleveren. Merk op dat −3^2 als resultaat 9 oplevert en niet −9 (vermits negatie een hogere prioriteit heeft als machtsverheffing wordt deze uitdrukking geïnterpreteerd als het kwadraat van −3 en niet als het negatieve van het kwadraat van 3). Relatieve en absolute referenties / referentierange Relatieve referenties worden bij het expanderen van een formule aangepast, d.w.z. dat het rijnummer en de kolomletter worden mee veranderd. Bij het gebruik van absolute referentie gebeurt dit niet ! Relatieve referenties kunnen met het $ teken absoluut gemaakt worden, bv. A1; $A1; A$1 en $A$1. Overzicht van referenties : =A1

relatief naar kolom en rij index (rij en kolom mogen veranderen)

=A$1

Relatief naar kolom en absoluut naar rij (rij blijft constant, kolom mag veranderen)

=$A1

Absoluut naar kolom, relatief naar rij (kolom blijft constant, rij mag veranderen)

=$A$1 Absoluut naar één cel (Zowel de rij als de kolom blijven constant) Het $ teken kan je invoeren als je de formule intypt maar je kan ook door de F4 toets te gebruiken, waardoor je zeer snel in de formule relatieve naar absolute referenties kan omzetten. Zet hiertoe de cursor achter de celreferentie. Veelvuldig toepassen van F4 past de referentie naar wens aan. Wanneer in een formule een relatieve referentie nodig is, dan kan men, terwijl men de formule intypt, de cursor in de bewuste cel klikken en wordt de referentie in de formule overgenomen. Maakt een formule gebruik van een range van referenties (bv. zoals in onderstaand voorbeeld waarbij de getallen in cellen A1 t.e.m. A3 opgeteld worden), dan kan men

− 7−

8 volstaan met klikken in de beginreferentie (A1) en vervolgens met de linkermuisknop ingedrukt naar de eindreferentie gaat (A3) en de muis loslaten.

In het inputvenster ziet men dan de geselecteerde range (A1:A3), terwijl in de worksheet de cellen A1 t.e.m. A3 door een knipperende stippellijn worden gemarkeerd. Naming ranges Men kan ook een rij of een kolom een naam geven. De namen zijn te zien in het namevenster van het formulevenster

Je doet dit door rij(en) of kolom(men) te selecteren en naar Insert/Name.../Create te gaan. Als je Insert/Name.../Define kiest dan heb je meer keuzemogelijkheden. Je kan ook in het naamvenster de gewenste naam typen. Uiteraard moet je eerst de range selecteren waaraan je de naam wil toekennen. Je kan vervolgens deze identifiers die je aan een range toegekend hebt gebruiken in formules, bv. : AVERAGE(staal4). Formatteren Alle controle bij het formatteren (d.w.z. preciseren hoe de, meestal, numerieke data er in de cel moet uitzien) heb je door Format/Cell te kiezen. Wanneer je deze optie kiest krijg je het volgende dialoogvenster :

− 8−

9

Voor het formatteren van numerieke data kies je vervolgens de optie Number. Je kan vervolgens het aantal gewenste decimalen ingeven. Je kan ook kiezen voor bv. de wetenschappelijk notatie (ga na als oefening met een willekeurig decimaal getal). Het is wel belangrijk te weten dat formatteren de waarde van het opgeslagen getal niet verandert. Laat ons tenslotte even stilstaan bij precisie. Excel kan positieve getallen in de range van 9.99 999 999 999 999 × 10+307 tot 1 x 10−307. voorstellen. De range voor negatieve getallen is −9.99 999 999 999 999 × 10+307 tot −1 x 10−307. De range van waarden in Microsoft Excel is dus 10±308, terwijl een typische rekenmachine een range van 10±99 heeft. Je moet je er van bewust zijn dat de conversie van decimaal naar binair in afrondingsfouten kan resulteren. Veronderstel dat je twee complexe berekeningen zou uitvoeren en verwacht dat A99 en B99 dezelfde waarden zouden hebben. Omwille van afrondingsfouten echter kunnen deze getallen een klein beetje verschillen en geeft de formule =A99 − B99 niet exact nul, maar bv. 0.000 000 000 000 008 of 8E-15. Net zoals in de decimale notatie het getal 10/3 niet met een oneindige precisie als een reëel getal kan geschreven worden, zijn er reële getallen die binair niet exact kunnen weergegeven worden. Voeg als voorbeeld de getallen 27.05 en 26.1 in cellen A1 en A2 van een leeg rekenblad in. In A3 schrijf je de formule = A1-A2 en je ziet de waarde 0.95 zoals je had verwacht. We gaan nu kijken naar de werkelijke waarde die gestockeerd is in A3. Voeg systematisch het aanal cijfers na de komma op ; na een tijdje zie je de waarde 0.94999 ... verschijnen. Er is duidelijk een afrondingsfout gebeurd omdat het getal exact 0.95 zou moeten zijn. Zo zullen programmeurs zelden testen of twee getallen exact gelijk zijn maar zullen ze eerder testen of het verschil tussen de twee getallen kleiner is dan een bepaalde kleine waarde. − 9−

10 2. FUNCTIES 2.1. Wiskundige en goniometrische functies Concepten Microsoft Excel bevat ongeveer 300 rekenbladfuncties die kunnen onderverdeeld worden in 10 categorieën, waarvan de wiskundige en goniometrische functies (Math and Trig), ingenieursfuncties (Engineering), logische (Logical) en statistische (Statistical) functies het belangrijkst zijn voor ons. Een groot aantal van deze functies kan je vinden met de Paste function button :

.

Wanneer je deze button selecteert krijg je het volgende window :

Veronderstel dat je de waarde van ln(3) wenst uit te rekenen. We noemen 3 dan het "argument" van de functie. Hieronder zie je een formule die een functie gebruikt. De "MAX" functie geeft de waarde van het grootste argument terug : = MAX(A1, B1:B8,10) Deze functie zal het maximum teruggeven van de waarde in A1, één van de waarden in B1 tot en met B8 en het getal 10. Merk op dat de verschillende argumenten van de functie gescheiden worden door een komma.

Deze argumenten kunnen een

celreferentie, een referentierange of een constante zijn. Afhankelijk van de functie kan het aantal argumenten vastliggen, variabel of zelfs nul zijn. Beschouw de volgende voorbeelden : Geen argumenten

=PI() − 10−

11 Eén argument

=SQRT(A2) of =SQRT(A2/2)

Twee argumenten

=ROUND(A2,2)

Variabel aantal argumenten =SUM(A1:A10) of =SUM(A1:A10,B3,B4) Als het aantal toegelaten argumenten variabel is, dan is het maximum aantal 30 en het aantal karakters mag de 1024 niet overschrijden. Merk op dat een range zoals A1:A100 voor één argument telt en niet voor 100. Als een functie op zijn beurt als argument gebruikt worden spreken we van een geneste functie. Het "nesten" van functies kan tot op zeven niveau's uitgevoerd worden. Een aantal fouten kunnen optreden bij het gebruik van formules en functies. Wanneer dit gebeurt dan geeft Excel één van de volgende foutmeldingen : #DIV/0!

Deling door nul

#NAME?

Een formule bevat een ongedefinieerde variabele, of er is een spatie achter de naam van de functie

#N/A

Geen waarde voorhanden

#NULL!

Een resultaat heeft geen waarde

#NUM!

Numerieke overflow bv. een cel met SQRT(Z1) wanneer Z1 een negatieve waarde heeft

#REF!

Ongeldige celreferentie

#VALUE!

Ongeldige argument vb. een cel met =LN(Z1) wanneer Z1 tekst bevat

Tevens kan een cel geen formule bevatten die een referentie bevat naar zijn eigen celadres. Zo zal het bv. onmogelijk zijn om in cel A10 de formule =SUM(A1:A10) te plaatsen. Als je dit toch probeert, dan geeft Excel als foutmelding "Cannot resolve circular reference". Goniometrische functies Veel fysische problemen gebruiken goniometrische functies zoals SIN, COS, TAN of hun inversen ASIN, ACOS en ATAN. Het is belangrijk om te weten dat alle computertoepassingen, inclusief Excel, verwachten dat de hoek waarvan je de functie neemt, uitgedrukt staat in radialen. De inverse functies geven tevens de hoek terug in

− 11−

12 radialen. Voor de conversie tussen radialen en graden en omgekeerd kan je de Excel functies RADIANS en DEGREES gebruiken. Exponentiële functies We schetsen deze functies aan de hand van een aantal voorbeelden : =EXP(2)

geeft e2 terug

=LN(5)

geeft het natuurlijke logaritme van 5 terug

=LOG10(5), =LOG(5,10) en =LOG(5) geeft het logaritme met grondtal 10 van 5 terug LOG(8,2)

geeft 3 terug, het logaritme met grondtal 2 van 8.

Afrondingsfuncties We zagen reeds dat het formatteren van de inhoud van een cel de waarde van de inhoud van de cel niet verandert, enkel de waarde die getoond wordt is aangepast. Excel heeft echter een aantal functies die getallen afronden. We geven hier een aantal voorbeelden : ABS

geeft de absolute waarde terug =ABS(−12.55) geeft 12.55.

EVEN

Rond een nummer af tot op het dichtste even getal =EVEN(3.25) geeft 4.

ODD

Rond een nummer af tot op het dichtste oneven getal =ODD(4.25) geeft 5.

ROUND

Rond een getal af tot een gewenst aantal decimalen =ROUND(1.378,1) geeft 1.4 (één decimaal). =ROUND(123.56,−1) geeft 120 (dichtste tiental). =ROUND(123.56,0) geeft 124 (dichtste integer).

Arrayfuncties Arrayfuncties geven gewoonlijk meer dan één getal terug. Om een arrayfunctie te gebruiken moet je : i) selecteer je het geschikte aantal cellen voordat je de formule invoegt ii) Beëindig je de formule met

Shift + Ctrl + Enter

− 12−

13 Als voorbeeld bekijken we de vermenigvuldiging van twee matrices in onderstaand rekenblad :

De twee te vermenigvuldigen matrices zijn A en B, respectievelijk in de cellen A4:C6 en E4:G6. Vervolgens selecteer je I4:K6 en typ je =MMULT ; als eerste argument vul je A4:C6 in en als tweede E4:G6. Vervolgens beëindig je de formule met Shift + Ctrl + Enter

.

Merk op dat je in de Formule bar {=MMULT(A4:C6,E4:G6)} kan aflezen in I4 als actieve cel. Excel heeft zelf de {} toegevoegd, je moet dit als gebruiker nooit zelf invoegen. Enkele andere wiskundige functies SQRT

Geeft de vierkantswortel van een getal terug ; het argument dient positief te zijn. =SQRT(9) geeft 3.

GCD

Geeft de grootste gemene deler terug =GCD(9,18,24) geeft 3.

LCM

Geeft het kleinste gemeen veelvoud terug =LCM(9,18,24) geeft 72.

QUOTIENT

Geeft het geheel deel van een deling terug =QUOTIENT(28.9) geeft 3.

FACT

Geeft de faculteit terug van een getal. =FACT(4) geeft 24.

RAND

Geeft een random getal tussen 0 en 1 terug.

De formule

=RAND()*(b-a) +a geeft een random waarde tussen a en b. MDETERM MINVERSE MMULT

Geeft de determinant, de inverse van een matrix en het product van twee matrices terug.

− 13−

14 2.2. Beslissingsfuncties Wanneer je wil dat een formule verschillende waarden teruggeeft afhankelijk van de waarde van een "conditie" (voorwaarde) kan je gebruik maken van de "IF" functie. Als een eenvoudig voorbeeld beschouwen we een rekenblad waar de cellen A2:A21 de examencijfers van 20 studenten bevatten en je in de B kolom "Geslaagd" of "Niet geslaagd" wil zien verschijnen, afhankelijk van het feit of het cijfer van een student groter of gelijk is aan 50. Je kan dit bewerkstelligen door de formule =IF(A2>=50,"Geslaagd","Niet geslaagd") in de cel B2 in te voegen en naar onderen te copiëren. De algemene syntax voor een formule die gebruik maakt van de IF functie is : =IF(voorwaarde,waarde wanneer waar, waarde wanneer onwaar) Een voorwaarde heeft de vorm : uitdrukking 1 - vergelijkingsoperator - uitdrukking 2 Deze uitdrukkingen zijn alle mogelijke geldige Excel uitdrukkingen samengesteld uit celreferenties, constanten of functies (eigenlijk is een uitdrukking een formule zonder "=" teken). Zo zal de uitdrukking die test of cel A3 de waarde 5 heeft er uitzien als : A3=5. De mogelijke vergelijkingsoperatoren zijn : = > >= < <= <>

gelijk aan groter dan groter dan of gelijk aan kleiner dan kleiner dan of gelijk aan niet gelijk aan

Voorbeelden van IF formules zijn : a)

=IF(A2<0,"Negatief","Positief") Geeft de tekst "Negatief" terug als A2 kleiner is dan 0, anders wordt "Positief" teruggegeven.

b)

=IF(A10-B10<=−0.001,0,1) Geeft 0 terug als (A10-B10) kleiner of gelijk is aan 0, anders wordt 1 teruggegeven.

c)

=IF(ABS(A10-B10)<=EPSILON,A10,B10) − 14−

15 Geeft de waarde van A10 terug als de absolute waarde van (A10-B10) kleiner of gelijk is aan de variable EPSILON, anders wordt de waarde in B10 teruggegeven. IF functies kunnen "genest" worden, d.w.z. dat in een IF functie een andere IF functie kan gebruikt worden. We bekijken de volgende voorbeelden : a)

=IF(A1>10,IF(A1>100,"Groot","Middelmatig"),"Klein") Als de voorwaarde dat A1>10 niet voldaan is dan geeft de eerste IF klein terug. Wat gebeurt er nu als de voorwaarde waar is ? In dit geval gaat de tweede IF een rol spelen. Als A1>100 is, geeft de binnenste IF "Groot" terug, anders wordt "Middelmatig" teruggegeven.

b)

=IF(A1>10,IF(A1>50,"Groot","Middelmatig),IF(A1<0,"Negatief","Klein")) Hier zijn zowel de waarde die wordt teruggegeven als de eerste IF waar is als de waarde als ze onwaar is op zichzelf IF functies.

Je kan tot op zeven niveau's IF functies nesten, op voorwaarde dat het totaal aantal karakters in de cel niet groter wordt dan 256. Een formule kan ook geconstrueerd worden enkel gebruik makend van een conditie. Deze formules geven dan de booleaanse waarden TRUE of FALSE terug. Bekijk even het volgende voorbeeld :

De logische functies AND() en OR() functies hebben als syntax AND(logical1,logical2,...) en OR(logical1,logical2,...) respectievelijk. Ze kunnen tot 30 argumenten bevatten. De AND functie geeft de waarde TRUE terug als alle argumenten waar zijn. De OR functie geeft de waarde TRUE terug als één of meerdere argumenten waar zijn. Beschouw het volgende voorbeeld : =IF(OR(A2>0,B2>A2/2),3,6) Geeft de waarde 3 terug als ofwel A2>0 of B2>A2/2. Als geen van beide voorwaarden waar is wordt 6 teruggegeven.

− 15−

16 3. GRAFIEKEN 3.1. Tekenen van een grafiek Voor het tekenen van grafieken roep je de hulp in van de Chart Wizard door op de button te klikken, nadat je de tabel waarvan je een grafiek wil maken geselecteerd hebt. Via een 4-stappen proces kan je dan van de geselecteerde data een grafiek maken. Voor wetenschappelijk werk zal men over het algemeen gebruik maken van XY (scatter) grafieken, d.w.z. een grafiek waar een reeks van X-waarden tegen corresponderende Y-waarden worden uitgezet. Zoals eerder gezegd moet je dus allereerst de twee kolommen met data (de X en Y kolommen) selecteren. Stap 0 : Selecteer de data waarvan je de grafiek wil maken :

Stap 1 : Klik op de Chart Wizard button en selecteer het type grafiek waarmee je de data wil voorstellen. In het onderstaande voorbeeld is gekozen voor een XY (Scatter) grafiek met data markers en een vloeiende lijn.

Als je de knop "Press and hold to view sample" indrukt , dan zie je een voorbeeld van de grafische voorstelling:

− 16−

17

Je kan dan eventueel besluiten om te kiezen voor een ander grafiektype. Stap 2 (Data Range) : Hierin kan je de data range nog controleren en aanpassen als dit nodig is.

Merk op dat de celrange absoluut is en voorafgegaan wordt door de naam van het sheet (in dit geval grafiek1). Als meerdere dataranges geselecteerd worden zijn worden deze gescheiden door een "," teken en elke absolute celrange wordt voorafgegaan door de naam van de sheet gescheiden door het "!" teken. Stap 2 (Series) : Hier kan men het aantal reeksen per grafiek aanpassen (Add; Remove). Ook kan men de legende aanpassen, die standaard de naam krijgt zoals deze onder series staat. In dit geval ligt het voor de hand de naam "[A]obs" mee te geven. Als de grafiek klaar is kan men indien nodig de legende verder formatteren.

− 17−

18 Stap 3 : Grafiek opties Er zijn een groot aantal opties waarmee je de grafiek kan optuigen.

Titles TAB :

Hier kan je een naam geven aan de grafiek en namen geven aan de labels van de assen.

Legend TAB : Hiermee kan je aangeven of de legende moet afgebeeld worden en zo ja waar deze moet geplaatst worden. Stap 4 Hier moet je aangeven waar de grafiek komt te staan : op een nieuw rekenblad of op het actieve rekenblad.

Bij de laatste mogelijkheid heb je de data en de grafiek bij elkaar en kan je snel zien wat het effect is als de data veranderen. Eindresultaat Na het bevestigen met de Finish knop krijg je het object op het rekenblad te zien. Je kan het object verplaatsen, vergroten of verkleinen. Klikken naast het object "bevriest" het object. Klikken in het object maakt het opnieuw bewerkbaar. Elk element (assen, labels, agenda, achtergrond, grafieklijn, enz...) van het object kan je afzonderlijk formatteren. Je kan dit doen door met de rechtermuisknop op het element te klikken en vervolgens Format ... te kiezen.

− 18−

19 Tenslotte merken we op dat de koppeling tussen de grafiek en de data dynamisch is, d.w.z. dat bij verandering van de data de grafiek automatisch aangepast wordt. In het uitgewerkte voorbeeld zal de grafiek er als volgt uitzien :

Door het formatteren (en verplaatsen) van de afzonderlijke elementen van de grafiek kan je de grafiek aanpassen tot onderstaande grafiek (probeer dit eens als oefening) :

3.2. Tekenen van verschillende datasets in een grafiek (gemeenschappelijk XY assenstelsel) Het zal regelmatig voorkomen dat je in een grafiek de waarnemingen wil combineren met de volgens een bepaald model berekende waarden. Meestal is het echter zo dat de berekende waarden dan niet overeenkomen met de X-waarden van de waarnemingen. Om de reeksen toch te kunnen combineren kan je een tabel maken die er als volgt uitziet :

− 19−

20

Zoals je ziet staan de X-waarden in de eerste kolom en de waarden voor [A]obs en [A]calc in verschillende kolommen. Voor het tekenen van de grafiek selecteer je dan het ganse blok ; merk op dat nu ook de headers van de kolommen geselecteerd zijn. Je krijgt dan het volgende resultaat :

Stel dat we in de definitieve grafiek de berekende waarden als een curve willen voorstellen en de experimentele waarden als losse punten. We gaan hiervoor als volgt te werk : 1. Selecteer in de grafiek de reeks berekende punten door met de linkermuisknop op één van deze punten te klikken. In een apart window krijg je trouwens te zien om welk punt van welke reeks het gaat :

− 20−

21 2. Met de rechtermuisknop kan je dan de representatie van de reeks veranderen. Voor ons doel selecteren we dan Chart Type ...

We selecteren dan de optie met vloeiende lijnen :

en krijgen het volgende resultaat :

Zoals je kan zien heeft de grafiek die ontstaan is een X-as domein van [-5,15]. Stel dat we dit willen aanpassen naar [0,15]. Hiervoor selecteer je de X-as door op de linkermuisknop te klikken. Er verschijnt dan aan elke kant van de as een zwart blokje. Daarna kan je door op de rechtermuisknop te klikken het volgende format menu op het scherm krijgen :

− 21−

22

Je kan dan de minimum waarde van de X-as van -5 naar 0 veranderen. Het resultaat is dan :

3.3. Tekenen van een combinatiegrafiek (één X-as, twee Y-assen) Het gaat hier om grafieken waarbij de X-as gemeenschappelijk is, maar waarin de Yassen verschillende schaalwaarden hebben. We behandelen hier het voorbeeld van een combinatiegrafiek van de concentraties van stoffen A en B in een reactie. Stel dat stof B een intermediair is dat slechts in geringe hoeveelheden voorkomt. Om beide curven tot hun recht te laten komen in een grafiek zal de schaalwaarde voor [B] moeten verkleind worden t.o.v. die voor [A]. Beschouwen we de volgende reeks van datapunten :

We maken hier allereerst een standaardgrafiek van : − 22−

23

Zoals je ziet liggen de punten voor [B] bijna op de X-as t.g.v. het feit dat de waarden op dezelfde Y-schaal als de [A] datapunten geplot worden. We gaan nu een secundaire Y-as voor B invoeren. We selecteren hiervoor met de rechtermuisknop de [B] datareeks en kiezen de optie Format Data Series. Selecteer vervolgens de Axis TAB, en verander daarin Primairy axis in Secondary axis :

Je ziet dat de Y-as schaling wordt aangepast. Probeer vervolgens de grafiek om te vormen tot een grafiek die er als volgt uitziet :

− 23−

24 3.4. Analyse van data Behalve het afbeelden van data is het ook mogelijk data te fitten. Na het maken van je XY scatter grafiek selecteer je de data-reeks door op één van de data punten te klikken met de rechtermuisknop. Alle punten van de range worden geselecteerd en vervolgens klik je Add Trendline. Bij de analyse kan je uit verschillende types kiezen : Lineair

y = ax + b

Logaritmisch

y = c ln x + b

Polynoom

y = b + c1x + c2x2 + ... + c6x6

Power

y = c xb

Exponentiëel

y = c ebx

Op grond van de XY plot van de data kies je dan de geschikte fit. Bij options kan je vervolgens aangeven of de vergelijking van de gefitte lijn en/of de correlatiecoëfficiënt (r2) moeten worden afgebeeld. Ook kan je aangeven of je de fit wil extrapoleren (dus weergave buiten de datapunten). Deze optie heet bij Excel forward/backward forecast. Nadat alles bevestigd is wordt de trendline getekend. Hierna kan de trendline worden geselecteerd en vervolgens geformatteerd (dikte, kleur, ...). De LINEST functie Deze functie gebruikt de kleinste kwadratenmethode om een rechte te fitten aan een set van data en geeft een array terug die deze rechte beschrijft. De syntax van deze functie is : LINEST(range van Y waarden, range van X waarden, Constante, Statistiek) Als de waarde van Constante TRUE is of weggelaten wordt, dan wordt het intercept van de lijn berekend. Anders wordt het intercept gelijk gesteld aan nul en wordt de data gefit aan y = mx. Als de waarde van statistiek TRUE is, dan wordt de waarde van r2 gegeven tesamen met andere statistische gegevens.

− 24−

25 Merk op dat LINEST een array functie is en dat we volgende procedure moeten gebruiken : (i) We selecteren een range voor de output (ii) we voeren de functie in (iii) we drukken Ctrl + Shift + Enter We krijgen dan : Richtingscoëfficiënt Standaard afwijking richtingscoëfficiënt r2

Intercept Standaardafwijking intercept Standaardafwijking in schatting van y

− 25−