6 Wiskundige en statistische analyses

6.1 Functies

Doelstellingen: het kunnen gebruiken van de ingebouwde rekenkundige, financiële, statistische, string-, datum- en logische functies

De werkmap Functies kan hier opgehaald worden. (Excel97-formaat)

6.1.1 Logische functies

Open de werkmap FUNCTIES.XLS en bekijk de oefeningen op werkblad 1 (logische functies).

Ga na of de waarde in de kolom links positief of negatief is.

Hiervoor zullen we gebruik maken van de logische ALS-functie.

formule in cel B2: ___________________________________________________

OEFENINGEN

1. Ga na tot welke categorie een waarde behoort. Indien de waarde in de kolom links lager is dan 70 dan moet "Te langzaam" verschijnen. Is de waarde hoger dan 120 dan verschijnt "Te snel", anders "OK".
   
2. Is de snelheid lager dan of gelijk aan 120 is er geen boete. Is de snelheid hoger dan 120, dan is de boete het aantal kilometers boven de 120 vermenigvuldigd met 150.
   
3. Staat er in de kolom links Assebroek of Sint-Kruis dan moet er naast Brugge komen anders komt er niets.

We kunnen hier ook gebruik maken van de OF-functie

=OF( logische uitdr1 ; logische uitdr 2 ; …)

met logische uitdr voorwaarden die WAAR of ONWAAR kunnen zijn

Opl: =ALS(OF(A29="Assebroek" ; A29="Sint-Kruis" ); "Brugge" ; "")

De daarbijhorende functie is de EN-functie

=EN( logische uitdr1 ; logische uitdr2 ; …)

Vb1. =EN(3>0;-2>0) geeft als oplossing ONWAAR

Vb2 =EN(3>0;-2<0) geeft als oplossing WAAR

Vb3: =OF(3>0;-2>0) geeft als uitdrukking WAAR

BESLUIT:

Bij de EN-functie moeten alle voorwaarden waar zijn opdat je als resultaat WAAR zou krijgen

Bij de OF functie volstaat het dat er één voorwaarde waar is om als resultaat WAAR te verkrijgen

6.1.2 Opzoekfuncties

Open de werkmap FUNCTIES.XLS en ga naar het 2de werkblad (Opzoekfuncties)

Er zijn 2 belangrijke zoekfuncties. Deze functies zoeken bepaalde waarden op in een tabel en geven de waarde uit de aangegeven cel.

=HORIZ.ZOEKEN( zoekwaarde ; tabel ; rij-index_getal )

=VERT.ZOEKEN( zoekwaarde ; tabel ; kolom-index_getal )

zoekwaarde: waarde die in de EERSTE rij/kolom van de tabel moet gezocht worden.

tabel: is het bereik waar je naar gegevens wilt gaan zoeken.

Let op: de tabel moet gesorteerd worden in oplopende volgorde.

rij-index_getal: is het rijnummer van de rij in de tabel waaruit je de gezochte waarde wilt ophalen.

kolom-index_getal: is het kolomnummer van de kolom in de tabel waaruit je de gezochte waarde wilt ophalen.

OEFENINGEN

1. Zoek de 5de dag van de week.
   
2. Zoek aan de hand van het ingegeven maandnummer de bijhorende maand.
   
3. Zoek d.m.v. het gegeven dagnr en de code voor de taal (Nl 2, En 3, Du 4 en Fr 5) de bijhorende dag
   
4. Bepaal het aantal rijen in de tabel A4:D8
   
5. Bepaal het aantal kolommen in de tabel A4:D8
   
6. Zoek wat er naast 130 staat
   

2 nieuwe functies: =RIJEN( tabel ) en =KOLOMMEN( tabel )

7. Bepaal de omzet onder het eerste jaartal en bepaal de grootste omzet

6.1.3 Datum- en tijdfuncties

een datum (en een tijd) op een pc is altijd een getal tussen 1900 en 2078

Open de werkmap FUNCTIES.XLS en ga naar het 3de werkblad (Datumfuncties)

De belangrijkste functies:

=NU() : levert de datum van vandaag

=WEEKDAG( getal ; type_getal) bepaalt de dag van de week in getalvorm

getal: is het getal waarmee de datum/tijd overeenkomt (vb nu() )

type_getal: is een getal die aangeeft hoe je het resultaat wilt zien

1 of niets: een getal van 1(zondag) tot en met 7(maandag)

2: een getal van 1(maandag) tot en met 7(zondag)

3: een getal van 0(maandag) tot en met 6(zondag)

vb. =weekdag( "98-01-01" ) ® 5 (donderdag)

=DATUMWAARDE( datum-tekst ) zet een datum om in een getal

vb. =datumwaarde("97-12-31") ® 35795

=NETTO.WERKDAGEN( begindatum ; einddatum ; vakantiedagen )

levert het aantal volledige werkdagen tussen begindatum en einddatum (weekeinden = vakantiedagen)

LET OP:

• begin- en einddatum zijn getallen.
• vakantiedagen is een optie: hier kan je een reeks data (getallen) die niet als werkdag mogen bekeken worden ( vb. Kerstdag, 21 juli,...)

vb. Netto werkdagen tussen 01/07/97 en 31/07/97 met uitsluiting van de Nationale feestdag op 21 juli levert 22 dagen op.

=JAAR( getal ) bepaalt het jaar van een datum (dus van een getal)

Vb. =jaar(nu()) . . . . . . . . . . . . . . . . . . . . .

Vb. =jaar("45-04-01") . . . . . . . . . . . . . . .

=MAAND( getal ) bepaalt de maand van een datum (dus van een getal)

vb. =maand(" 30-aug ") . . . . . . . . . .

=DAG( getal ) bepaalt de dag van een datum (dus van een getal)

vb. =dag( " 29 - febr -1996 " ) . . . . .

6.1.4 String- of tekenfuncties

Open de werkmap FUNCTIES en ga naar het werkblad Stringfuncties

String- of tekenfuncties zijn functies die betrekking hebben op karakters (tekst)

=LINKS( string ; n ) levert de eerste n karakters van een tekenreeks (string)

=RECHTS( string ; n ) levert de laatste n karakters van een tekenreeks (string)

=HOOFDLETTERS( string ) zet de string volledig om in hoofdletters

=KLEINE.LETTERS( string ) zet de string volledig om in kleine letters

=BEGINLETTERS( string ) zet eerste letter van elk woord om in een hoofdletter

6.1.5 Wiskundige functies

Open de werkmap FUNCTIES.XLS en ga naar het werkblad Wiskundige functies

OEFENINGEN

1. In een eerste oefening proberen we de Euclidische deling uit.

Bepaal in B5 het quotiënt van de cellen B3 en B4: ________________________

Bepaal in B6 de rest na deling: _______________________________________

2. Bepaal in cel B12 de absolute waarde van een getal: _______________________
3. Bepaal in cel B18 en B20 de omtrek en de oppervlakte van een cirkel met straal (B16)

cel B18: _________________________________________________________

cel B20: _________________________________________________________

4. Bepaal in cel B24, B25 en B26 de sinus, cosinus en tangens van een hoek. Let op de hoek moet uitgedrukt worden in radialen.
5. Bepaal in cel B32 de vierkantswortel en in cel B34 de 3^de machtswortel van een getal.

cel B24: _________________________________________________________

cel B25: _________________________________________________________

6.1.6 Statistische functies

Open de werkmap FUNCTIES.XLS en ga naar het werkblad Statistische functies

De belangrijkste functies zijn de volgende:

=MAX( bereik ) levert het grootste getal uit een bereik cellen.

=MIN( bereik ) levert het kleinste getal uit een bereik cellen.

=GEMIDDELDE( bereik ) levert het rekenkundig gemiddelde van een bereik cellen.

=AANTAL( bereik ) telt het aantal cellen dat getallen bevat

=AANTAL.ALS( bereik ; voorwaarde ) telt het aantal waarden dat aan een bepaalde voorwaarde voldoen

voorwaarde: kan een getal, een expressie,… zijn

=MODUS( bereik ) levert de waarde die het meest voorkomt in een reeks waarden

=MEDIAAN ( bereik ) levert de mediaan (het midden) van een reeks waarden

=VAR( bereik ) bepaalt de variantie van een reeks waarden

De standaarddiviatie kan je berekenen door de vierkantswortel te nemen van de variantie

=DEV.KWAD( bereik of getal ) bepaalt de som van de kwadraten van de afwijking van de verschillende waarden t.o.v. het gemiddelde

Gedeeld door het totaal aantal gegevens levert dit de Gemiddelde kwadratische afwijking of variantie

=GEM.DEVIATIE( bereik of getal ) bepaalt het gemiddelde van de afwijking van de verschillende waarden t.o.v. het gemiddelde

m.a.w. de Gemiddelde absolute afwijking

OEFENINGEN

Op het werkblad Statistische functies vind je een statistisch onderzoek weer i.v.m. de schoenmaat van 85 leerlingen van het zesde jaar. Bepaal in de nodige centrummaten en spreidingsmaten.

6.1.7 Financiële functies

Open de werkmap FUNCTIES.XLS. en ga naar het werkblad Financiële functies

Bepaal in een eerste oefening de jaarlijkse lineaire afschrijving op 10 jaar en jaarlijkse boekwaarde.

Formule in cel B8: _______________________________________________________

Formule in cel C8: _______________________________________________________

Ook heeft Excel een aantal financiële functies die zeer praktisch zijn om bepaalde begrippen te laten berekenen uit de financiële algebra.

=BET ( intrestvoet ; aantal termijnen ; beginwaarde ; eindwaarde ; (1) )

levert de jaarlijkse termijn bij een annuïteit waarbij we ofwel de parameter beginwaarde ofwel de eindwaarde ingeven.

de laatste parameter wijst op postnumerando of prenumerando annuïteit. Wil je een prenumerando annuïtiet dan plaats je als laatste parameter een 1

Opmerking:

• Wees consequent in het gebruik van de eenheden waarmee u intrestvoet en aantal-termijnen opgeeft.
  Als u maandelijkse aflossingen verricht op een lening met een looptijd van vier jaar tegen een jaarrente van 12 procent, gebruikt u 12%/12 voor rente en 4*12 voor aantal-termijnen.
  Als u jaarlijkse aflossingen verricht op dezelfde lening, gebruikt u 12% voor rente en 4 voor aantal-termijnen.
  
  Je kan ook de formule gebruiken voor de gelijkwaardige maandelijkse rentevoet:
  Vb. een jaarlijkse rentevoet van 12% komt dan overeen met een gelijkwaardige maandelijkse rentevoet van 0,949%
  

• De termijnen, eindwaarden en beginwaarden moeten negatief ingegeven worden om positieve resultaten te krijgen Uitgezonderd bij NPER en RENTE waar de termijn negatief en de eindwaarde of beginwaarde positief moet ingevoerd worden.

=HW ( intrestvoet ; aantal termijnen ; termijn ; (ev eindwaarde) ; (1) )
levert de beginwaarde bij een annuïteit.

=TW ( intrestvoet ; aantal termijnen ; termijn ; (ev beginwaarde) ; (1) )
levert de eindwaarde bij een annuïteit.

=NPER ( intrestvoet ; termijn ; beginwaarde ; eindwaarde ; (1) )
levert het aantal termijnen bij een annuïteit . waarbij we ofwel de parameter beginwaarde ofwel de eindwaarde ingeven.

=RENTE ( intrestvoet ; termijn ; beginwaarde ; eindwaarde ; (1) )
levert de intrestvoet bij een annuïteit . waarbij we ofwel de parameter beginwaarde ofwel de eindwaarde ingeven.

In een derde oefening proberen we een Aflossingstabel op te stellen.

Bepaal in cel B68 het jaarlijkse termijn: _________________________________________

Bepaal in cel B69 de terugbetaalde som: ________________________________________

Bepaal in cel B70 de totale kredietlast: _________________________________________

Vanaf rij 74 maken we een aflossingstabel op:

In kolom A de vervaldagnummers, in B het jaarlijkse termijn, in C: de aflossing, in D het intrestgedeelte en in E het schuldsaldo

• Invullen van de vervaldagnummers: slepen met de vulgreep
• Invoeren van de constante termijnen
• Bepalen van de eerste intrest: r₁ = beginwaarde A₀ * intrestvoet i
• Bepalen van de eerste aflossing: k₁ = termijn a – r₁
• Bepalen van de eerste schuldsaldo: 1^ste schuldsaldo = beginwaarde A₀ – k₁
• Bepalen van de tweede intrest: r₂ = 1^ste schuldsaldo * intrestvoet i
• Bepalen van de tweede aflossing: k₂ = termijn a – r₂
• Bepalen van de tweede schuldsaldo: 2^de schuldsaldo = 1^ste schuldsaldo – k₂

6.2 Data-Tabel

Doelstellingen: het effect van de resultaten zien indien je bepaalde waarden verandert in het werkblad

De werkmap Tabellen en doelzoeken kan hier opgehaald worden. (Excel97-formaat)

Voorbeeld 1

Het bepalen van enkelvoudige intrest.  (zie tabblad Datatabel).  Gegeven een kpaitaal van 150 000 BEF staat uit gedurende 10 jaar tegen enkelvoudige intrest van 5%. Wat is de intrest?

Indien je nu andere percentages of andere periodes wilt gebvruiken moet je iedere keer die cel veranderen en het resultaat opschrijven.  Excel kan dit voor jou doen:

We baseren ons hiervoor op het princiepe ALS dat verandert WAT gebeurt er dan?

Methode:

* Selecteer de cellen A7:B11

* Selecteer in Data de keuze Tabel :

* Geef als kolominvoercel B5 op (dit is de cel die moet aangepast worden met de nieuwe percentages)

* Klik op ok

Oefening: probeer nu hetzelfde voor een andere periode.

Voorbeeld 2

Wat nu als we tegelijkertijd de intrestvoeten en de periodes wensen te veranderen (zie tabblad Datatabel2)

* Selecteer de cellen B8:E11

* Selecteer in Data de keuze Tabel :

* Bij Rij-invoercel kies je B4 (periodes staan in een rij) en voor Kolom-invoercel kies je B5 (de intrestvoeten staan in een kolom)

* Klik op OK

6.3 Doelzoeker

Een andere methode om wijzigingen vlug waar te nemen is gebruik te maken van de doelzoeker.

We hernemen het vorige voorbeeld

Onderstel nu dat we graag 100 000 BEF intrest zouden willen hebben na 10 jaar.   Wat is dan de nieuwe intrestvoet?

Methode:

* Selecteer in Extra de keuze Doelzoeken :

Bij cel instellen vul je het celadres in van de cel (B8) die gewijzigd moet worden in een nieuwe waarde (100000).  Bij door wijzigen van cel vul je het celadres in van de cel die daardoor gewijzigd moet worden

In het volgende venster kan je dan de nieuwe waarden behouden (OK) of de oude waarden herstellen (annuleren)

Oefening (tabblad doelzoeker2) Hoeveel jaar moet men sparen tegen samengestelde intrest om een kapitaal van 1 000 000 BEF te laten groeien tot een eindkapitaal van 2 000 000 BEF als de intrestvoet 5% is?  (antw.  14,2067 jaar) (formule eindkap= beginkap(1+i)^n)

6.4 Scenariobeheer

We hernemen weer het vorige voorbeeld:

We zouden nu graag het effect zien van de verandering van een aantal parameters.( i, n en k)

Methode:

* Kies Extra en selecteer Scenariobeheer

* Klik op toevoegen

* Geef het scenariobeheer een gepaste naam (bv. parameterveranderingen) en vul de cellen in die je wenst te veranderen.

* Klik op OK zodat het venster Scenariowaarden verschijnt.  Links staan alle cellen die je wilt wijzigen

* Om het resultaat van een scenario te zien, klikt u in het venster Scenariobeheer op weergeven