Excel voor besluitvorming

naast formules is een andere manier om wiskundige berekeningen in Excel uit te voeren door middel van functies. Statistische functies passen een wiskundig proces toe op een groep cellen in een werkblad. De functie SOM wordt bijvoorbeeld gebruikt om de waarden in een celbereik toe te voegen. Een lijst van algemeen gebruikte statistische functies is weergegeven in Tabel 2.4. Functies zijn efficiënter dan formules wanneer u een wiskundig proces toepast op een groep cellen. Als u een formule gebruikt om de waarden in een celbereik toe te voegen, moet u elke cellocatie één voor één aan de formule toevoegen. Dit kan zeer tijdrovend zijn als u de waarden in een paar honderd cellocaties moet toevoegen. Wanneer u echter een functie gebruikt, kunt u alle cellen markeren die waarden bevatten die u in slechts één stap wilt optellen. Deze sectie toont een verscheidenheid aan statistische functies die we zullen toevoegen aan de persoonlijke budget-werkmap. Naast het demonstreren van functies, bespreekt deze sectie ook het percentage van de totale berekeningen en het gebruik van absolute referenties.

Tabel 2.4 Gebruikte Statistische Functies

de SUM-functie wordt gebruikt wanneer u totalen moet berekenen voor een celbereik of een groep geselecteerde cellen op een werkblad. Met betrekking tot het werkblad met Begrotingsdetails gebruiken we de SUM-functie om de totalen in rij 12 te berekenen. Het is belangrijk op te merken dat er verschillende methoden zijn om een functie aan een werkblad toe te voegen, die gedurende de rest van dit hoofdstuk zullen worden gedemonstreerd. Het volgende illustreert hoe een functie aan een werkblad kan worden toegevoegd door het in een cellocatie te typen:

1. klik op de Budget Detail werkblad tab om het werkblad te openen.
2. klik op cel C12.
3. typ een gelijkteken =.
4. Typ de functienaam SUM.
5. typ een open haakje (.
6. klik op cel C3 en sleep naar cel C11. Dit plaatst het bereik C3: C11 in de functie.
7. typ een sluithaakje ).
8. druk op de ENTER-toets. De functie berekent het totaal voor de kolom maandelijkse uitgaven, die $ 1.496 is.

figuur 2.11 toont het uiterlijk van de SUM-functie die is toegevoegd aan het werkblad met Begrotingsdetails voordat u op de toets invoeren drukt.

zoals weergegeven in Figuur 2.11, werd de SUM-functie toegevoegd aan cel C12. Deze functie is echter ook nodig om de totalen in de jaarlijkse uitgaven en LY uitgaven kolommen te berekenen. De functie kan in deze cellocaties wegens het relatieve verwijzen worden gekopieerd en geplakt. Relatieve verwijzingen dienen hetzelfde doel voor functies als voor formules. Het volgende laat zien hoe de totale rij is voltooid:

1. klik op cel C12 in het werkblad Budgetdetail.
2. klik op de knop Kopiëren in het tabblad Home van het lint.
3. Markeer cellen D12 en E12.
4. klik op de knop Plakken in het tabblad Home van het lint. Dit plakt de SUM-functie in cellen D12 en E12 en berekent de totalen voor deze kolommen.
5. klik op cel F11.
6. klik op de knop Kopiëren in het tabblad Home van het lint.
7. klik op cel F12 en klik vervolgens op de plakken knop in de Home tab van het lint. Omdat we nu totalen in rij 12 hebben, kunnen we de procentveranderingsformule in deze rij plakken.

figuur 2.12 toont de uitvoer van de SUM-functie die is toegevoegd aan cellen C12, D12 en E12. Bovendien werd de procentuele veranderingsformule gekopieerd en geplakt in cel F12. Merk op dat deze versie van de begroting is van plan een 1,7% daling van de uitgaven in vergelijking met vorig jaar.

Absolute referenties (percentage van totalen berekenen)

gegevensbestand: ga verder met CH2 Personal Budget.

aangezien totalen zijn toegevoegd aan Rij 12 van het werkblad Begrotingsdetails, kan een percentage van de totale berekening worden toegevoegd aan kolom B die begint in cel B3. Het percentage van de totale berekening toont het percentage voor elke waarde in de kolom jaarlijkse uitgaven ten opzichte van het totaal in cel D12. Nadat de formule is gemaakt, moet u de functie relatieve verwijzing van Excel echter uitschakelen voordat u de formule kopieert en plakt naar de rest van de cellocaties in de kolom. Het uitschakelen van Excel ‘ s relatieve verwijzing functie wordt bereikt door middel van een absolute referentie. De volgende stappen leggen uit hoe dit wordt gedaan:

1. klik op cel B3 in het werkblad Budgetdetail.
2. typ een gelijkteken =.
3. klik op cel D3.
4. typ een schuine streep /.
5. klik op cel D12.
6. druk op de ENTER-toets. U zult zien dat huishoudelijke voorzieningen 16,7% van het jaarlijkse Uitgavenbudget vertegenwoordigen (zie figuur 2.14).

te berekenen Figuur 2.14 toont de voltooide formule die het percentage berekent dat huishoudelijke nutsbedrijven jaarlijkse uitgaven vertegenwoordigen aan de totale jaarlijkse uitgaven voor de begroting (zie cel B3). Normaal gesproken zouden we deze formule kopiëren en in het bereik B4:B11 plakken. Vanwege relatieve verwijzingen nemen beide celverwijzingen echter met één rij toe naarmate de formule in de cellen onder B3 wordt geplakt. Dit is prima voor de eerste celverwijzing in de formule (D3), maar niet voor de tweede celverwijzing (D12). Figuur 2.15 illustreert wat er gebeurt als we de formule in het bereik B4:B12 in zijn huidige toestand plakken. Merk op dat Excel de foutcode #DIV/0 produceert. Dit betekent dat Excel probeert een getal te delen door nul, wat onmogelijk is. Kijkend naar de formule in cel B4, zie je dat de eerste celverwijzing werd veranderd van D3 naar D4. Dit is prima omdat we nu de jaarlijkse uitgaven voor verzekeringen willen delen door de totale jaarlijkse uitgaven in cel D12. Excel heeft echter ook de D12-celverwijzing naar D13 gewijzigd. Omdat de cellocatie D13 leeg is, produceert de formule de foutcode #DIV/0.

om de deling door nul fout weergegeven in Figuur 2.15 te elimineren moeten we een absolute verwijzing naar cel D12 in de formule toevoegen. Een absolute referentie voorkomt dat relatieve verwijzingen een celverwijzing in een formule veranderen. Dit wordt ook wel aangeduid als het vergrendelen van een cel. Het volgende legt uit hoe dit wordt bereikt:

1. Dubbelklik op cel B3.
2. plaats de muisaanwijzer voor D12 en klik op. De knipperende cursor moet voor de D staan in de celverwijzing D12.
3. druk op de F4-toets. U ziet een dollarteken ( $ ) toegevoegd voor de kolomletter D en het rijnummer 12. U kunt ook de dollartekens voor de kolomletter en het rijnummer typen.
4. druk op de ENTER-toets.
5. klik op cel B3.
6. klik op de knop Kopiëren in het tabblad Home van het lint.
7. markeer het bereik B4: B11.
8. klik op de knop Plakken in het tabblad Home van het lint.

figuur 2.16 toont het percentage van de totale formule met een absolute referentie toegevoegd aan D12. Merk op dat in cel B4 de celverwijzing D12 blijft in plaats van D13, zoals weergegeven in Figuur 2.15. Ook zult u zien dat de percentages worden berekend in de rest van de cellen in de kolom en dat de verdeel-door-nul-fout nu is geëlimineerd.

de telfunctie

gegevensbestand: ga verder met CH2 Personal Budget.

de volgende functie die we aan het werkblad Budgetdetail zullen toevoegen, is de COUNT-functie. De telfunctie wordt gebruikt om te bepalen hoeveel cellen in een bereik een numerieke ingang bevatten. De functie tellen werkt niet voor het tellen van tekst of andere niet-numerieke vermeldingen. Voor het werkblad met Begrotingsdetails gebruiken we de COUNT-functie om het aantal items te tellen dat is gepland in de kolom jaarlijkse uitgaven (kolom D). In het volgende wordt uitgelegd hoe de COUNT-functie aan het werkblad wordt toegevoegd met behulp van de functielijst:

1. klik op cel D13 in het werkblad Budgetdetail.
2. typ een gelijkteken =.
3. Typ de letter C.
4. klik op de pijl-omlaag op de schuifbalk van de functielijst (zie figuur 2.17) en zoek het aantal woorden.
5. Dubbelklik op het aantal woorden in de functielijst.
6. markeer het bereik D3:D11.
7. u kunt een sluithaakje typen) en druk vervolgens op de ENTER-toets, of druk gewoon op de ENTER-toets en Excel zal de functie voor u sluiten. De functie produceert een output van 9 omdat er 9 items gepland zijn op het werkblad.

Figuur 2.17 toont de functie keuzelijst die verschijnt na het voltooien van de stappen 2 en 3 voor de functie tellen. De functielijst biedt een alternatieve methode voor het toevoegen van een functie aan een werkblad.

toe te voegen figuur 2.18 toont de uitvoer van de telfunctie na het indrukken van de ENTER-toets. De functie telt het aantal cellen in het bereik D3:D11 die een numerieke waarde bevatten. Het resultaat van 9 geeft aan dat er 9 categorieën zijn gepland voor deze begroting.

de GEMIDDELDE functie

de volgende functie die we aan het werkblad Begrotingsdetail zullen toevoegen, is de GEMIDDELDE functie. Deze functie wordt gebruikt om het rekenkundig gemiddelde voor een groep getallen te berekenen. Voor het werkblad Budgetdetail gebruiken we de functie om het gemiddelde van de waarden in de kolom jaarlijkse uitgaven te berekenen. We zullen dit aan het werkblad toevoegen met behulp van de functiebibliotheek. De volgende stappen leggen uit hoe dit wordt bereikt:

1. klik op cel D14 in het werkblad Budgetdetail.
2. klik op het tabblad Formules op het lint.
3. klik op de knop Meer functies in de commandogroep functiebibliotheek.
4. plaats de muisaanwijzer boven de statistische optie uit de keuzelijst met opties.
5. klik op de gemiddelde functienaam uit de lijst met functies die in het menu verschijnen (zie figuur 2.19). Hiermee opent u het dialoogvenster Functieargumenten.
6. klik op de knop in het dialoogvenster Functieargumenten (zie figuur 2.20).
7. markeer het bereik D3:D11.
8. klik op het dialoogvenster uitvouwen knop in het dialoogvenster Functieargumenten (zie figuur 2.21). U kunt ook op de invoeren toets drukken om hetzelfde resultaat te krijgen.
9. klik op de knop OK in het dialoogvenster Functieargumenten. Dit voegt de GEMIDDELDE functie toe aan het werkblad.

figuur 2.19 illustreert hoe een functie is geselecteerd uit de functiebibliotheek op het tabblad Formules van het lint.

figuur 2.20 toont het dialoogvenster Functieargumenten. Dit verschijnt nadat een functie is geselecteerd uit de functiebibliotheek. Het Dialoogvenster samenvouwen knop wordt gebruikt om het dialoogvenster te verbergen, zodat een celbereik op het werkblad kan worden gemarkeerd en vervolgens aan de functie kan worden toegevoegd.

figuur 2.21 laat zien hoe een celbereik kan worden geselecteerd uit het dialoogvenster Functieargumenten nadat het is ingeklapt.

Figuur 2.22 toont het dialoogvenster Functieargumenten nadat het celbereik is gedefinieerd voor de GEMIDDELDE functie. Het dialoogvenster toont het resultaat van de functie voordat deze aan de cellocatie wordt toegevoegd. Hiermee kunt u de functie-uitvoer beoordelen om te bepalen of het zinvol is voordat u deze toevoegt aan het werkblad.

figuur 2.23 toont de voltooide GEMIDDELDE functie in het werkblad Budgetdetail. De output van de functie laat zien dat we gemiddeld $1.994 verwachten te besteden voor elk van de categorieën in kolom A van het budget. Deze gemiddelde uitgavenberekening per categorie kan worden gebruikt als een indicator om te bepalen welke categorieën meer of minder kosten dan de gemiddelde begrote uitgaven dollars.

de MAX-en MINFUNCTIES

gegevensbestand: ga verder met CH2 Personal Budget.

de laatste twee statistische functies die we aan het Begrotingsdetailwerkblad zullen toevoegen, zijn de MAX-en MIN-functies. Deze functies identificeren de hoogste en laagste waarden in een reeks cellen. In de volgende stappen wordt uitgelegd hoe u deze functies aan het werkblad Budgetdetail kunt toevoegen:

1. klik op cel D15 in het werkblad Budgetdetail.
2. typ een gelijkteken =.
3. typ het woord MIN.
4. typ een open haakje (.
5. markeer het bereik D3:D11.
6. typ een sluithaakje) en druk op de ENTER-toets, of druk gewoon op de ENTER-toets en Excel zal de functie voor u sluiten. De MIN-functie produceert een output van $1.200, wat de laagste waarde is in de kolom jaarlijkse uitgaven (zie figuur 2.24).
7. klik op cel D16.
8. typ een gelijkteken =.
9. typ het woord MAX.
10. typ een open haakje (.
11. markeer het bereik D3:D11.
12. typ een sluithaakje) en druk op de ENTER-toets, of druk gewoon op de ENTER-toets en Excel zal de functie voor u sluiten. De MAX-functie produceert een output van $3.500. Dit is de hoogste waarde in de kolom jaarlijkse uitgaven (zie figuur 2.25).

formules kopiëren en plakken (plakken zonder formaten)

gegevensbestand: ga verder met CH2 Personal Budget.

zoals weergegeven in Figuur 2.25, zijn de functies COUNT, AVERAGE, MIN en MAX een samenvatting van de gegevens in de kolom jaarlijkse uitgaven. U zult ook merken dat er ruimte is om deze functies te kopiëren en te plakken onder de kolom Ly besteden. Dit stelt ons in staat om te vergelijken wat we vorig jaar hebben uitgegeven en wat we van plan zijn dit jaar te besteden. Normaal gesproken kopiëren en plakken we deze functies gewoon in het bereik E13:E16. Echter, je hebt misschien gemerkt dat de dubbele lijn stijl grens die werd gebruikt rond de omtrek van het bereik B13:E16. Als we het reguliere plakken commando gebruiken, zou de dubbele regel aan de rechterkant van het bereik E13:E16 worden vervangen door een enkele regel. Daarom gaan we een van de speciale commando ‘ s plakken gebruiken om alleen de functies te plakken zonder een van de opmaakbehandelingen. Dit wordt bereikt door middel van de volgende stappen:

1. markeer het bereik D13: D16 in het werkblad Budgetdetail.
2. klik op de knop Kopiëren in het tabblad Home van het lint.
3. klik op cel E13.
4. klik op de pijl-omlaag onder de knop Plakken op het tabblad Home van het lint.
5. klik op de formules optie in de vervolgkeuzelijst met knoppen (zie figuur 2.26).

figuur 2.26 toont de lijst met knoppen die verschijnen wanneer u op de pijl-omlaag onder de knop plakken klikt op het tabblad Home van het lint. Een ding om op te merken over deze opties is dat u ze kunt bekijken voordat u een selectie door te slepen met de muisaanwijzer over de opties. Zoals getoond in de figuur, wanneer de muisaanwijzer over de knop formules wordt geplaatst, kunt u zien hoe de functies zullen verschijnen voordat u een selectie maakt. Merk op dat de dubbele lijn rand niet verandert wanneer deze optie wordt bekeken. Dat is de reden waarom deze selectie wordt gemaakt in plaats van de normale plakken optie.