Excel. Module 8: Populaire formules en functies

(1)

Excel

Module 8: Populaire formules en

functies

(2)

Inhoud

1 LOGISCHE FUNCTIES 4

1.1 Inleiding 4

1.2 ALS (IF) 4

1.2.1 Bespreking van het functievenster 4

1.2.2 Case 1 5

1.2.2.1 Werkwijze 5

1.2.3 Case 2 6

1.2.3.1 Werkwijze 6

1.3 OF (OR) 7

1.3.2 Case 8

1.3.2.1 Werkwijze 8

1.4 EN (AND) 8

1.4.2 Case 9

1.4.2.1 Werkwijze 10

1.5 Logische functies nesten 10

1.5.1 Case 10

1.5.2 Werkwijze 11

1.6 ALS.FOUT (IFERROR) 14

1.6.2 Case 14

2 ZOEKFUNCTIES 16

2.1 VERT.ZOEKEN (VLOOKUP) 16

2.1.2 Case 1 17

2.1.3 Case 2 18

2.2 VERGELIJKEN (MATCH) 20

2.2.2 Case 21

2.3 INDEX 22

2.3.1 Case 22

3 WISKUNDIGE FUNCTIES 24

(3)

3.1.2 Case 25

3.2 SOMMEN.ALS (SUMIFS) 26

3.3 Case 27

4 STATISISCHE FUNCTIES 28

4.1 GEMIDDELDE.ALS (AVERGAIF) 28

4.1.2 Case 29

4.2 GEMIDDELDEN.ALS (AVERAGEIFS) 30

4.2.2 Case 31

4.3 AANTAL.ALS (COUNTIF) 32

4.3.2 Case 32

4.4 AANTALLEN.ALS (COUNTIFS) 34

4.4.2 Case 35

5 OEFENINGEN 36

5.1 Oefening 1 36

5.2 Oefening 2 36

5.3 Oefening 3 36

5.4 Oefening 4 37

5.5 Oefening 5 37

(4)

1 Logische functies

1.1 Inleiding

Excel beschikt over verschillende logische functies om de geweste cellen in uw spreadsheets te controleren. Afhankelijk van de uitkomst van de bewuste controle(s) kunt u Excel andere cellen inhoudelijk laten aansturen. Alles kan gecontroleerd worden: tekst, cijfers, datums, … De inhoud die u vervolgens elders laat verschijnen, kan een vast getal zijn, de uitkomst van een andere berekening of functie, tekst, …

1.2 ALS (IF)

Dit is wellicht de populairste logische functie. Via deze functie kunt u standaard één voorwaarde laten controleren. Afhankelijk hiervan bepaalt u wat Excel moet aanbieden in de resultaatcel.

1.2.1 Bespreking van het functievenster

Argument Interpretatie

Logische‐test

(Logical_test) In dit vak voert u de CONTROLE in die Excel moet uitvoeren.

Waarde‐als‐waar (Value_if_true)

Via dit vak bepaalt u welke uitkomst u wil indien de VOORWAARDE in het vak Logische‐test (Logical_test) OK is.

Waarde‐als‐onwaar (Value_if_false)

Via dit vak bepaalt u welke uitkomst u wil indien de VOORWAARDE in het vak Logische‐test (Logical_test) NIET OK is.

(5)

1.2.2 Case 1

Praktisch: We gebruiken het werkblad Speeding violation in voorbeeldbestand Speeding ticket.

In deze lijst staan in kolom B een aantal gemeten snelheden opgesomd. Zoals we allemaal weten, zijn er op onze Belgische wegen ook bepaalde snelheidsbeperkingen van toepassing. Deze staan vermeld in kolom C. We willen simpelweg het woord YES of NO in kolom D laten invullen afhankelijk van het feit of we over een snelheidsovertreding spreken of net niet.

1.2.2.1 Werkwijze

 Selecteer de eerste cel waar u een resultaat wil. In dit voorbeeld is dat cel D5.

 Activeer het tabblad Formules (Formulas).

 Selecteer de knop Logisch (Logical).

 In de lijst die nu getoond wordt, kiest u voor ALS (IF). Het functievenster verschijnt nu.

 Klik in het vak Logische‐test (Logical_test).

 Voer deze voorwaarde in:

B5>C5

 Klik nu in het vak Waarde‐als‐waar (Value_if_true).

 Typ hier “YES”.

 Klik tot slot in het vak Waarde‐als‐onwaar (Value_if_false).

 Typ hier “NO”. Het functievenster ziet er nu als volgt uit:

(6)

 Klik op OK om het resultaat in cel D5 op te nemen.

 Gebruik de vulgreep om deze functie ook op de onderliggende cellen toe te passen.

1.2.3 Case 2

Praktisch: We gebruiken het werkblad Matrix in voorbeeldbestand Speeding ticket.

In dit werkblad bekijken we verschillende scenario’s. We berekenen in het bereik D9:F20 de boete afhankelijk van de gemeten snelheid die in kolom C getoond wordt en de toegelaten snelheid die is opgenomen in rij 8.

Een eventuele boete bestaat uit 2 componenten:

o Het vaste bedrag dat in cel F4 is opgenomen.

o Plus € 1 extra per kilometer/uur die boven de snelheidsbeperking ligt.

 Selecteer de eerste cel waar u een resultaat wil. In dit voorbeeld is dat cel D9.

 Voer deze voorwaarde in:

$C9>D$8

 Typ hier $F$4+($C9‐D$8)*$F$5.

 Typ hier 0. Het functievenster ziet er nu als volgt uit:

(7)

 Klik op OK om het resultaat in cel D9 op te nemen.

 Kopieer vervolgens de formule naar alle cellen van het bereik D9:F20.

1.3 OF (OR)

Met deze functie kunt u tot maar liefst 255 controles laten uitvoeren! Indien één van deze controles als OK kan beschouwd worden, dan geeft deze functie de uitkomst WAAR (TRUE). Indien geen enkele van alle mogelijke controles OK is, dan geeft deze functie de uitkomst ONWAAR (FALSE). In vele gevallen wordt de functie OF (OR) als ondersteunende functie gebruikt in andere functies zoals de functie ALS (IF).

1.3.1 Bespreking van het functievenster

Logisch1

(Logical1) In dit vak voert u de EERSTE CONTROLE in die Excel moet uitvoeren.

Logisch2

(Logical2) In dit vak voert u de TWEEDE CONTROLE in die Excel moet uitvoeren.

Telkens u in het volgende vak klikt, krijgt u een extra vak aangeboden om een bijkomende controle in te bouwen. Deze methode kan u blijven herhalen met een maximum van 255 controles!

(8)

1.3.2 Case

Praktisch: We gebruiken het werkblad LastYear in voorbeeldbestand Managers.

We laten in dit werkblad twee controles uitvoeren:

o Is het resultaat in kolom D groter dan de target in kolom C?

o Komt de naam van de manager in kolom B overeen met de naam in referentiecel C2?

Indien één van deze voorwaarden OK is, willen we het woord WAAR (TRUE) in kolom E tonen. Indien aan geen enkele van de opgesomde voorwaarden voldaan wordt, dan tonen we het woord ONWAAR (FALSE) in kolom E.

 Selecteer de eerste cel waar u een resultaat wil. In dit voorbeeld is dat cel E6.

 In de lijst die nu getoond wordt, kiest u voor OF (OR). Het functievenster verschijnt nu.

 Klik in het vak Logisch1 (Logical1).

 Voeg deze eerste controle in:

D6>C6

 Klik nu in het vak Logisch2 (Logical2).

 Voeg hier deze controle in:

B6=$C$2

 Klik op OK om het resultaat in cel E6 op te nemen.

1.4 EN (AND)

Ook met deze functie kunt u tot maar liefst 255 controles laten uitvoeren! Enkel indien alle controles die in deze functie worden opgegeven als OK kunnen beschouwd worden, dan geeft deze functie de uitkomst WAAR (TRUE). In alle andere gevallen geeft deze functie de uitkomst ONWAAR (FALSE). Net zoals de functie OF (OR) wordt deze functie als een ondersteunende functie gebruikt in andere functies zoals de functie ALS (IF).

(9)

1.4.1 Bespreking van het functievenster

Logisch1

(Logical1) In dit vak voert u de EERSTE CONTROLE in die Excel moet uitvoeren.

Logisch2

(Logical2) In dit vak voert u de TWEEDE CONTROLE in die Excel moet uitvoeren.

Telkens u in het volgende vak klikt, krijgt u een extra vak aangeboden om een bijkomende controle in te bouwen. Deze methode kan u blijven herhalen met een maximum van 255 controles!

1.4.2 Case

Praktisch: We gebruiken het werkblad 2018 in voorbeeldbestand Managers.

o Is het resultaat in kolom D groter dan de target in kolom C?

o Komt de naam van de manager in kolom B overeen met de naam in referentiecel C2?

Indien al deze voorwaarden OK zijn, willen we het woord WAAR (TRUE) in kolom E tonen. Indien slechts één of geen enkele van de opgesomde voorwaarden voldaan wordt, dan tonen we het woord ONWAAR (FALSE) in kolom E.

(10)

 Selecteer de eerste cel waar u een resultaat wil. In dit voorbeeld is dat cel E6.

 In de lijst die nu getoond wordt, kiest u voor EN (AND). Het functievenster verschijnt nu.

 Voeg deze eerste controle in:

D6>C6

 Klik nu in het vak Logisch2 (Logical2).

 Voeg hier deze controle in:

B6=$C$2

 Klik op OK om het resultaat in cel E6 op te nemen.

1.5 Logische functies nesten

In de vorige hoofdstukken hebben we de populaire functies ALS (IF), OF (OR), EN (AND) apart besproken.

Vaak worden deze functies gecombineerd. Deze techniek noemen we ook “nesten”. De ALS (IF) functie wordt hierbij aangestuurd één of een combinaties van de functies OF (OR) en EN (AND).

1.5.1 Case

Praktisch: We gebruiken het werkblad Results in voorbeeldbestand Selling.

o Is de waarde van het veld Net sales groter dan deze in het veld Target?

o Is de waarde van het veld Sold units groter 100?

Indien al deze voorwaarden OK zijn, willen we in het veld Bonus (dat we nog moeten toevoegen) de waarde 500 opnemen in elke record. Is de combinatie van de voorwaarden niet mogelijk, dan zal de waarde in het veld Bonus helaas 0 zijn.

(11)

1.5.2 Werkwijze

 Selecteer een willekeurige cel in de lijst.

 Gebruik de toetsencombinatie CTRL + L.

 Wijzig de tabelnaam in Sales.

 Selecteer cel G1.

 Typ hier de veldnaam Bonus in en druk op ENTER.

 Pas eventueel de kolombreedte van kolom G aan.

 Klik in cel G2.

(12)

Omdat we meerdere condities wil controleren, schakelen we nu meteen over naar de functie EN (AND).

 Klik hiertoe op het keuzepijltje van het naamvak.

 Indien u in deze lijst de functie EN (AND) niet meteen vindt, kiest u voor Meer functions (More Functions). Via het venster dat u vervolgens krijgt, kunt u de logische EN (AND) functie selecteren.

 Selecteer de eerste waarde in het veld Net Sales.

 Typ de operator >.

 Selecteer de eerste waarde van het veld Target.

 Selecteer de eerste waarde van het veld Sold Units en vul aan met >100. Het functievenster ziet er nu als volgt uit:

(13)

 Klik zeker niet op OK maar, selecteer de functienaam ALS (IF) in de formulebalk.

 U keert nu terug naar het venster van de begeleidende functie ALS (IF). Dit is de huidige situatie:

 Typ hier 500.

 Typ hier 0.

 Bevestig tot slot via de knop OK.

(14)

1.6 ALS.FOUT (IFERROR)

Soms geeft een formule een foutmelding in plaats van het verwachte resultaat. Dit kan voorkomen als bepaalde broncellen niet of verkeerd ingevuld werden. Om dergelijke foutmelding te vermijden, kunt u gebruik maken van de functie ALS.FOUT. Deze functie vervangt nl. de foutmelding door een waarde of een andere bereking die u zelf kunt kiezen.

1.6.1 Bespreking van het functievenster

Waarde (Value)

In dit vak verwijst u naar de cel of plaatst u de formule dewelke een fout genereert.

Waarde_indien_fout (Value_if_error)

In dit vak voert u de waarde in die u in plaats van de foutmelding wil laten weergeven.

1.6.2 Case

Praktisch: We gebruiken het voorbeeldbestand Discount.

In dit voorbeeld laten we in kolom D het percentage van de korting opzoeken. Het verkochte aantal (zie kolom C) wordt gecontroleerd in de tabel aan de rechterzijde. Afhankelijk van het aantal verkochte stuks wordt het percentage opgepikt. We gebruikten hiervoor de functie VERT.ZOEKEN (VLOOKUP). Zoals u merkt bevat cel D9 een probleem omdat de referentietabel de schaal tussen 0 en 20 niet aanbiedt. We lossen dit op.

(15)

 Selecteer cel D5.

 Klik in de formulebalk en selecteer de volledige formule ZONDER het =‐teken.

 Gebruik de toetsencombinatie CTRL + X.

 Kies voor ALS.FOUT (IFERROR). Het functievenster verschijnt nu.

 Klik in het vak Waarde (Value).

 Gebruik de toetsencombinatie CTRL + V.

 Selecteer vervolgens het vak Waarde_indien_fout (Value_if_error).

 Typ in dit vak het cijfer 0.

 Klik tot slot op OK.

 Kopieer de formule in cel D5 naar de cellen D6:D10.

(16)

2 Zoekfuncties

Er zijn tal van manieren in Excel om informatie op te zoeken. Excel biedt u filters, slicers, enz… Hiernaast kunt u ook gebruik maken van de wereldberoemde functie VERT.ZOEKEN (VLOOKUP).

In tegenstelling tot een filter of slicer waarmee u één of enkele gegevensrijen opzoekt in een lijst of tabel, zal deze functie één waarde als uitkomst geven. We bespreken in dit hoofdstuk zowel vertikaal zoeken alsook enkele andere populaire zoekfuncties.

2.1 VERT.ZOEKEN (VLOOKUP)

Met deze functie kunt u een bepaalde parameter in de eerste kolom van een lijst of tabel laten controleren. Indien Excel deze parameter in de kolom vindt, wordt een bijhorende waarde uit diezelfde rij als uitkomst aangeboden.

2.1.1 Bespreking van het functievenster

Zoekwaarde (Lookup_value)

In dit vak verwijst u naar de cel dewelke de parameter bevat die u in een tabel of lijst wil laten controleren.

Tabelmatrix (Table_array)

In dit vak verwijst u naar de tabel of lijst waar de zoekactie moet uitgevoerd worden. Deze tabel of lijst bevat ook het resultaat dat u zoekt.

(17)

Kolomindex_getal (Col_index_num)

In dit vak verwijst u naar de kolompositie van de kolom die het resultaat bevat. Dit moet altijd een cijfer zijn. De kolom met de zoekwaarde is kolom 1, de volgende kolom 2 enz…

Benaderen (Range_lookup)

In dit vak bepaalt u of deze functie exact moet zoeken of eerder bij benadering. Voor een exacte opzoeking voert u hier een 0 in. De zoektactie bij benadering specifieert u met het cijfer 1.

2.1.2 Case 1

Praktisch: We gebruiken het voorbeeldbestand Training.

De werkmap Training beschikt over twee werkbladen. Het werkblad Planning toont een tabel met alle opleidingen die gepland werden. Via het werkblad Search willen we een zoeksysteem opzetten. Als we nl. in het werkblad Search via cel C2 een code invoeren die aan een training werd toegewezen, dan dient alle gelinkte info van de bewuste opleiding in de onderliggende cellen getoond te worden.

In dit voorbeeld passen we de zoekmethode EXACTE MATCH toe.

Vooraleer we aan de slag gaan, bespreken we nog even de voorwaarden om vertikaal zoeken correct te kunnen gebruiken.

o De parameter die u wil laten controleren (cel C2 van het werkblad Search) moet dezelfde opmaak hebben als de referentiewaarde in de tabel.

o Vertikaal zoeken zal de parameter (cel C2 van het werkblad Search) altijd zoeken in de eerste kolom van de door u aangeduide tabel of lijst (veld ID in de tabel Source). Plaats de zoekkolom daarom uiterst links in de tabel of lijst.

o De zoekkolom mag geen dubbele waarden bevatten. Waak er over dat deze kolom (veld ID in de tabel Source) altijd is opgevuld met unieke waarden.

o U past ook best een oplopende sortering toe in de zoekkolom.

 Selecteer cel C5 van het werkblad Search.

 Selecteer de knop Zoeken en verwijzen (Lookup & Reference).

 In de lijst die nu getoond wordt, kiest u voor VERT.ZOEKEN (VLOOKUP). Het functievenster verschijnt nu.

 Klik in het vak Zoekwaarde (Lookup_value) en voer hier het celadres C2 in.

(18)

 Plaats hier de verwijzing naar alle gegevensrijen van de brontabel Source:

Source[#gegevens]

OF

Source[#data]

 Klik in het vak Kolomindex_getal (Col_index_num) en voer hier het cijfer 2 in.

 Selecteer vervolgens het vak Benaderen (Range_lookup).

 Plaats in dit vak de waarde 0. Hiermee laat u Excel de exacte match uitvoeren. Het functievenster ziet er nu als volgt uit:

 U kunt in de cellen C7, C9, C11, C13, C15 en C17 van het werkblad Search dezelfde werkwijze toepassen. U hoeft enkel een ander Kolomindex_getal (Col_index_num) in te voeren.

 Bewaar deze werkmap met de bestandsnaam Training solution 1.

2.1.3 Case 2

Praktisch: We gebruiken het voorbeeldbestand Bonus.

In de tabel aan de linkerzijde (tabel SalesResults) zijn alle verkoopsresultaten opgesomd. Afhankelijk van het resultaat dat elke sales rep boekte, zal hij of zij recht hebben op een bonus. Hiertoe gebruiken we de referentie tabel aan de rechterzijde (tabel Bonus). Een sales rep die een sales resultaat € 10.471 boekte, zal dus recht hebben op een bonus van € 1.500.

In dit voorbeeld passen we de zoekmethode BIJ BENADERING toe.

(19)

 Selecteer cel G2.

 In de lijst die nu getoond wordt, kiest u voor VERT.ZOEKEN (VLOOKUP). Het functievenster verschijnt nu.

 Klik in het vak Zoekwaarde (Lookup_value) en selecteer de eerste waarde in het veld Sales.

 Selecteer vervolgens het vak Tabelmatrix (Table array).

 Plaats hier de verwijzing naar alle gegevensrijen van de brontabel Bonus:

Bonus[#gegevens]

OF

Bonus[#data]

 Klik in het vak Kolomindex_getal (Col_index_num) en voer hier het cijfer 2 in.

 Selecteer vervolgens het vak Benaderen (Range_lookup).

 Plaats in dit vak de waarde 1. Hiermee laat u Excel schaalgewijs zoeken. Het functievenster ziet er nu als volgt uit:

(20)

2.2 VERGELIJKEN (MATCH)

Met de functie vergelijken kunt u de positie van een waarde in een kolom of rij laten weergeven. Het resultaat is altijd een cijfer! Deze functie wordt daarom vaak als een “supporterende” functie gebruikt om andere zoekfuncties efficiënter toe te passen.

2.2.1 Bespreking van het functievenster

Zoekwaarde

(Lookup_value) In dit vak verwijst u naar de waarde die u wil laten controleren.

Zoeken‐matrix (Lookup_array)

In dit vak bepaalt u in welke rij of kolom Excel de zoekwaarde moet controleren.

Criteriumtype_getal (Match_type)

In dit vak plaatst u een 0, een 1 of de waarde ‐1.

0: de zoekkolom of ‐rij mag willekeurig gesorteerd zijn. Er wordt naar een exacte overeenkomst gezocht

1: de zoekkolom of ‐rij moet oplopend gesorteerd zijn. Er wordt naar een waarde gezocht die kleiner of gelijk is.

‐1: de zoekkolom of ‐rij moet aflopend gesorteerd zijn. Er wordt naar een waarde gezocht die groter of gelijk is.

(21)

2.2.2 Case

Praktisch: We gebruiken het voorbeeldbestand Test.

De getoonde tabel (tabelnaam Selling) bevat een aantal facturen die werden ingeboekt. We beschouwen cel F1 als parametercel. In deze cel voeren we steeds het factuurnummer in van hetwelke we het rijnummer in deze tabel willen acherhalen. Dit resultaat tonen we in cel E2.

 Selecteer cel E2.

 In de lijst die nu getoond wordt, kiest u voor VERGELIJKEN (MATCH). Het functievenster verschijnt nu.

 Klik in het vak Zoekwaarde (Lookup_value) en selecteer de cel F1.

 Selecteer vervolgens het vak Zoeken‐matrix (Lookup_array).

 Plaats hier de verwijzing naar het veld Invoice N° in de tabel Selling:

Selling[Invoice N°]

Klik in het vak Criteriumtype_getal (Match_type) en voer hier het cijfer 0 in. Het functievenster ziet er nu als volgt uit:

(22)

2.3 INDEX

Soms komt het voor dat lijsten of tabellen niet altijd georganiseerd zijn om bijvoorbeeld de functie vertikaal zoeken toe te passen. In dergelijke situaties kunt u gebruik maken van de functie index. Deze functie is heel gebruiksvriendelijk. U duidt nl. aan in welke rij en kolom u een resultaat zoekt. De waarde die zich in de cel bevindt op het snijpunt van deze rij en kolom zal als resultaat aangeboden worden.

2.3.1 Case

Praktisch: We gebruiken het voorbeeldbestand InvoiceList.

Het factuurnummer dat in cel F1 ingevoerd wordt, gebruiken we als contole in het veld Invoice N°. Op basis hiervan willen we het bijhorende factuurbedrag in het veld Invoice amount opzoeken. We bieden het resultaat aan in cel F3. Dit zou bijvoorbeeld nooit lukken met de functie VERT.ZOEKEN (VLOOKUP).

2.3.2 Bespreking van het functievenster

Matrix

(Array) In dit vak voert u het bereik in waar u de zoekactie wil laten uitvoeren.

Rij_getal (Row_num)

In dit vak verwijst u naar het rijnummer (binnen het aangeduide bereik) waar Excel moet zoeken. De eerste rij van uw selectie is dus 1, de volgende 2, enz.

Kolom_getal (Column_num)

In dit vak verwijst u naar het kolomnummer (binnen het aangeduide bereik) waar Excel moet zoeken. De eerste kolom van uw selectie is dus 1, de volgende 2, enz.

(23)

 Selecteer cel F3.

 In de lijst die nu getoond wordt, kiest u voor INDEX. In het venster dat nu verschijnt, klikt u op OK.

 Klik in het vak Matrix (Array) en voer deze verwijzing in:

Selling[#gegevens]

OF

Selling[#data]

 Selecteer vervolgens het vak Kolom_getal (Column_num).

 Voer hier de waarde 2 in.

 Klik nu in het vak Rij_getal (Row_num).

 Selecteer dan het keuzepijltje in het naamvak:

 Selecteer via deze keuzelijst de functie VERGELIJKEN (MATCH).

 Klik in het vak Zoekwaarde (Lookup_value) en selecteer de cel F1.

 Selecteer vervolgens het vak Zoeken‐matrix (Lookup_array).

 Plaats hier de verwijzing naar het veld Invoice N° in de tabel Selling:

Selling[Invoice N°]

 Selecteer het vak Criteriumtype_getal (Match_type) en voer hier het cijfer 0 in.

(24)

3 Wiskundige functies

In dit hoofdstuk bespreken we twee varianten op de populaire functie SOM (SUM). We bekijken nl. hoe we een totaal voorwaardelijk kunnen berekenen.

3.1 SOM.ALS (SUMIF)

Met deze functie kunt u de cijfers uit een bepaalde kolom of veld laten optellen op basis van één voorwaarde die u in een andere kolom of veld binnen dezelfde tabel of lijst laat controleren.

3.1.1 Bespreking van het functievenster

Bereik (Range)

In dit vak verwijst u naar de kolom of het veld waar u een controle wil laten uitvoeren.

Criterium

(Criteria) In dit vak voert u de paramter(cel) in die u wil laten controleren.

Optelbereik (Sum_range)

In dit vak verwijst u naar de kolom of het veld waarvan Excel de cijfers dient op te pikken om het totaal te berekenen.

(25)

3.1.2 Case

Praktisch: We gebruiken het voorbeeldbestand Budget.

Op het eerste werkblad hebben we de tabel Budget geplaatst. Op basis van deze ruwe data willen we een resultaat krijgen in cel C4 van het werkblad 1 condition. Telkens we de plaatsnaam in cel B4 wijzigen, dient Excel het gebudgeteerde cijfer voor deze regio te berekenen.

 Selecteer cel C4 van het werkblad 1 condition.

 Selecteer de knop Wiskunde en trigonometrie (Math & Trig).

 In de lijst die nu getoond wordt, kiest u voor SOM.ALS (SUMIF).

 Klik in het vak Bereik (Range) en voer deze verwijzing in:

Budget[City]

 Selecteer vervolgens het vak Criterium (Criteria).

 Plaats hier het celadres B4.

 Klik nu in het vak Rij_getal (Row_num).

 Klik in het vak Optelbereik (Sum_range) en voer deze verwijzing in:

Budget[Budget value].

(26)

3.2 SOMMEN.ALS (SUMIFS)

Met deze functie kunt u de cijfers uit een bepaalde kolom of veld laten optellen op basis van meerdere voorwaarden die u in andere kolommen of velden binnen dezelfde tabel of lijst laat controleren.

3.2.1 Bespreking van het functievenster

Optelbereik (Sum_range)

Criteriumbereik1 (Criteria_range1)

In dit vak verwijst u naar de eerste kolom of het eerste veld waar u een controle wil laten uitvoeren.

Criteria1 In dit vak voert u de paramter(cel) in die u in het eerste bereik wil laten controleren.

Telkens u in het laaste vak van dit venster klikt, biedt Excel u een extra criteriumbereik en criteriumvak aan.

(27)

3.3 Case

Praktisch: We gebruiken het voorbeeldbestand Budget.

We werken verder met het bestand Budget. Op basis van deze ruwe data willen we een resultaat krijgen in cel E4 van het werkblad # conditions. Telkens we de parameters in de cellen B4, C4 en D4 wijzigen, dient Excel het gebudgeteerde cijfer voor deze setting te berekenen.

 Selecteer cel E4 van het werkblad # conditions.

 Selecteer de knop Wiskunde en trigonometrie (Math & Trig).

 In de lijst die nu getoond wordt, kiest u voor SOMMEN.ALS (SUMIFS).

 Selecteer het vak Optelbereik (Sum_range) en voer deze verwijzing in:

Budget[Budget value]

 Klik in het vak Criteriumbereik1 (Criteria_range1) en voer deze verwijzing in:

Budget[City]

 Selecteer vervolgens het vak Criteria1.

 Plaats hier het celadres B4.

Budget[Ledger]

 Selecteer nu het vak Criteria2.

 Verwijs in dit vak naar het celadres C4.

Budget[Quarter]

 Plaats hier het celadres D4.

(28)

4 Statisische functies

In het vorige hoofdstuk hebben we de voorwaardelijke som besproken. In dit hoofdstuk bespreken we dezelfde technieken om het gemiddelde of een aantal te berekenen op basis van één of meerdere voorwaarden.

4.1 GEMIDDELDE.ALS (AVERGAIF)

Met deze functie kunt u het gemiddelde van de cijfers uit een bepaalde kolom of veld laten laten berekenen op basis van één voorwaarde die u in een andere kolom of veld binnen dezelfde tabel of lijst laat controleren.

4.1.1 Bespreking van het functievenster

Bereik (Range)

Criteria In dit vak voert u de paramter(cel) in die u wil laten controleren.

Gemiddelde_bereik (Average_range)

In dit vak verwijst u naar de kolom of het veld waarvan Excel de cijfers dient op te pikken om het gemiddelde te berekenen.

(29)

4.1.2 Case

Praktisch: We gebruiken het voorbeeldbestand Worldwide results.

Het werkblad Results bevat de tabel Selling. Op basis van deze gegevens willen we op werkblad 1 de gemiddelde omzet berekenen voor de regio die in cel B6 wordt opgegeven.

 Selecteer cel C6 van het werkblad 1.

 Selecteer de knop Meer functies (More Functions).

 Kies in dit menu voor Statistisch (Statistical). Er wordt nu een submenu getoond.

 Selecteer de functie GEMIDDELDE.ALS (AVERAGEIF).

Selling[City]

 Verwijs vanuit het vak Criteria naar cel B6.

 Selecteer vervolgens het vak Gemiddelde_bereik (Average_range).

 Voer deze verwijzing in:

Selling[Turnover]

(30)

4.2 GEMIDDELDEN.ALS (AVERAGEIFS)

Met deze functie kunt u het gemiddelde van de cijfers uit een bepaalde kolom of veld laten laten berekenen op basis van meedere voorwaarden die u in andere kolommen of velden binnen dezelfde tabel of lijst laat controleren.

4.2.1 Bespreking van het functievenster

Gemiddelde_bereik (Average_range)

Criteria1 In voert u de paramter(cel) in die u in het eerste bereik wil laten controleren.

(31)

4.2.2 Case

Het werkblad Results bevat de tabel Selling. Op basis van deze gegevens willen we op werkblad 2 de gemiddelde omzet berekenen voor de parameters die in de cellen B6, C6 en D6 worden opgegeven. Een aandachtspunt is de omvang van de omzet. We willen nl. steeds een gemiddelde berekenen met een omzet die groter is dan de parameter Turnover ref.

 Selecteer cel E6 van het werkblad 2.

 Selecteer de functie GEMIDDELDEN.ALS (AVERAGEIFS).

 Klik in het vak Gemiddelde_bereik (Average_range) en voer deze verwijzing in:

 Verwijs vanuit het vak Criteriumbereik1 (Criteria_range1) naar het veld City:

Selling[City]

 Verwijs naar cel B6.

 Klik in het vak Criteriumbereik2 (Criteria_range2) en verwijs naar het veld Product:

Selling[Product]

 Verwijs naar cel C6.

 Selecteer het vak Criteriumbereik3 (Criteria_range3) en verwijs naar het veld Turnover:

 Typ deze verwijzing in het vak Criteria3:

">"&D6

(32)

4.3 AANTAL.ALS (COUNTIF)

Met deze functie kunt u op basis van één voorwaarde berekenen hoe vaak een bepaalde waarde voorkomt in een kolom of veld van een tabel of lijst.

4.3.1 Bespreking van het functievenster

Bereik (Range)

Criterium

(Criteria) In dit vak voert u de paramter(cel) in die u wil laten controleren.

4.3.2 Case

Het werkblad Results bevat de tabel Selling. Op basis van deze gegevens willen we op werkblad 3 berekenen hoe vaak een stad voorkomt in het veld City.

(33)

 Selecteer cel C6 van het werkblad 3.

 Selecteer de functie AANTAL.ALS (COUNTIF).

Selling[City]

 Verwijs vanuit het vak Criterium (Criteria) naar cel B6.

(34)

4.4 AANTALLEN.ALS (COUNTIFS)

Met deze functie kunt u berekenen hoe vaak een bepaalde waarde voorkomt in een kolom of veld van een tabel of lijst rekening houdend met meerdere voorwaarden die u in andere kolommen of velden laat controleren.

4.4.1 Bespreking van het functievenster

Criteria1 In voert u de paramter(cel) in die u in het eerste bereik wil laten controleren.

(35)

4.4.2 Case

Het werkblad Results bevat de tabel Selling. Op basis van deze gegevens willen we op werkblad 4 berekenen hoeveel omzetten > € 10.000 gerealiseerd werden voor het opgegeven product in de aangeduide regio.

 Selecteer cel E6 van het werkblad 4.

 Selecteer de functie AANTALLEN.ALS (COUNTIFS).

 Verwijs vanuit het vak Criteriumbereik1 (Criteria_range1) naar het veld City:

Selling[City]

 Verwijs naar cel B6.

 Klik in het vak Criteriumbereik2 (Criteria_range2) en verwijs naar het veld Product:

Selling[Product]

 Verwijs naar cel C6.

 Selecteer het vak Criteriumbereik3 (Criteria_range3) en verwijs naar het veld Turnover:

 Typ deze verwijzing in het vak Criteria3:

">"&D6

Opmerking

Om de vermelde functies toe te passen, hebben we in alle voorgaande cases steeds dynamische verwijzingen gebruikt zoals bv. Selling[Turnover]. U mag natuurlijk ook altijd

(36)

5 Oefeningen

5.1 Oefening 1

Open het bestand Fee calculator. Bereken in het veld Fee het extra loon dat elke medewerker kan ontvangen. Dit zijn de voorwaarden: enkel medewerkers afkomstig uit “DENMARK” of “NORWAY” die de functie “MANAGER” of ”ADMINISTRATIVE” bekleden krijgen € 1.000 extra. Alle andere medewerkers krijgen helaas niets. Bereken dit op een dynamische manier.

5.2 Oefening 2

Open het bestand Car expenses. Plaats op het werkblad Search in cel E9 één formule die u naar rechts kunt kopiëren. Deze formule dient alle kosten weer te geven die aan één auto gelinkt zijn. Dit dient te gebeuren op basis van de geselecteerde nummerplaat in cel C6. Alle gegevens van het wagenpark zijn terug te vinden op het werkblad General overview. Maak in cel E6 van het werkblad Search ook een formule aan die als resultaat steeds het merk van de wagen vermeld voorafgegaan door de tekst “This is the cost of your”. Dit is een voorbeeld:

5.3 Oefening 3

Open het bestand Members. Elke persoon die in dit overzicht is opgenomen, dient een bedrag te betalen voor de aangekochte outfit. Deze outfit bestaat uit de kost voor de kledij + de kost van de schoenen. De lijst vermeld zowel de kledij‐ als de schoemaat van elk individu. Aan de rechterkant van het werkblad staan de referentietabellen met de richtprijzen voor de kledij en de schoenen. Maak in cel G2 één formule aan die voor elke persoon het totaal te betalen bedrag berekent.

(37)

5.4 Oefening 4

Open het bestand Missing info. Het werkblad Products toont de productlijst met alle noodzakelijke info.

Plaats in cel B2 van het werkblad MissingInfo een formule die de productnaam van product 38 opzoekt in het vorige werkblad. Bouw deze formule zodanig op dat u deze formule zowel naar omlaag als naar rechts kunt kopiëren. Eén formule moet dus volstaan om alle info die op het tweede werkblad ontbreekt aan te vullen volgens de brontabel op het eerste werkblad. Indien een Product‐ID niet kan gevonden worden, laat u de melding “not listed” verschijnen. Dit is een voorbeeld:

5.5 Oefening 5

Open het bestand Customers. Bereken in cel N7 van het werkblad Analysis de totale omzet. Dit dient te gebeuren volgens de parametercellen E7, E9, J7 en J9. De brontabel kunt u terugvinden op het werkblad SourceData. Dit is een voorbeeld:

(38)