Excel voor beginners

[CorVerm]

Inhoudsopgave:

Alle hier onderstaande artikelen kun je ook als .pdf downloaden.
Excel voor beginners deel 1
Excel voor beginners deel 2
Excel voor beginners deel 3
Excel voor beginners deel 4
Excel voor beginners deel 5
Excel voor beginners deel 6
Excel voor beginners deel 7


Alle begin is moeilijk. Voor het werken met Excel is dat net zo, daarom zal ik m'n best doen om je, op een zo eenvoudig mogelijke wijze, wegwijs te maken in Excel. We gaan dat simpelweg doen aan de hand van voorbeelden die, spelenderwijs, enig inzicht geven in het werken met Excel.





De benamingen van de diverse werkbalken. Als er in het vervolg over een van deze werkbalken wordt gesproken weet je waar het over gaat.

Wat je moet weten om met Excel aan de slag te gaan:
Elke nieuwe werkmap wordt standaard geopend met drie werkbladen. Je kunt van werkblad wisselen door op een van de bladtabs te klikken die zich onderaan het documentvenster bevinden.

Het werken binnen een werkblad:
Een cel selecteer je door met de muis op een cel te klikken. Navigeren door het werkblad kun je doen met de pijltjestoetsen.
Tekst wordt links in de cel uitgelijnd, getallen rechts. (Excel moet onderscheid maken tussen tekst en getallen omdat het met getallen moet kunnen rekenen)
De celaanwijzer is te herkennen aan een dikke omranding van de geselecteerde cel met rechtsonder een klein blokje (vulgreep).
Invoer van gegevens worden bevestigd met een druk op Enter, de celaanwijzer zal zich dan een cel omlaag verplaatsen.
Een typefout kun je corrigeren door in de cel dubbel te klikken of in de formulebalk (waar de celinhoud ook wordt weergegeven) eenmaal te kikken op het punt waar de typefout staat. De cel selecteren en op de F2 toets te drukken geeft hetzelfde resultaat.
De inhoud van een cel wis je door op de Deletetoets te drukken. (wees daar dus voorzichtig mee)



De cursor in al z'n gedaanten.


De knoppen die in dit onderdeel zijn gebruikt:


Gebruikte operatoren:
+ optellen * vermenigvuldigen / delen


Excel berekent een formule van links naar rechts. Wil je dat Excel een andere rekenvolgorde hanteert dan moet dat deel van de formule tussen haakjes geplaatst worden.


Zowel de rekenkundige als vergelijkingsoperatoren komen gaandeweg ter sprake.

Dit zijn zo ongeveer de zaken die je echt moet weten om met Excel aan de slag te kunnen. Verder komen in elk onderdeel aanwijzingen te staan die, op dat moment, relevant zijn.

Tijd dus om aan de slag te gaan, we doen dat aan de hand van onderstaand model.

Het model dat we na gaan maken.

De groene cellen bevatten variabele gegevens, d.w.z. hier kun je opgeven wat de maten en de prijs van de tegel is, zo ook de oppervlakte van de kamer. Deze gegevens kunnen dus wisselen.
De oranje gekleurde cellen bevatten de constanten, dus gegevens die vastliggen. (al ben ik er niet helemaal zeker van of het BTW percentage nog wel klopt)
In de rode cellen komen de berekeningen te staan.

Maak om te beginnen bovenstaande model na. Klik in A1 en type de tekst, navigeer met een van de pijltjestoetsen naar de volgende cel of klik die weer aan om ook daar tekst of getallen in te voeren.
Zo je wilt kun je meteen de opmaak van het model overnemen. Laten we beginnen met het “vet” maken van een aantal teksten. Je kunt die cellen waarin je de tekst “vet” wilt maken met de muis en ingedrukte CTRL toets selecteren en daarna op de B (Bolt)knop drukken. Wat je ook kunt doen is cel A1 de opmaak Bolt geven en vervolgens te dubbelklikken op de kwast (opmaak kopiëren) in de werkbalk Standaard, waarna je eenmaal klikt op de andere cellen die je van een "vette" tekst wilt voorzien. Klaar? Druk een maal op de kwast om die te deactiveren of op de Esc toets.
Voor de randopmaak klikken we in A1 druk de Shifttoets in en klik op E8, klik op het pijltje naast de knop Randopmaak en kies voor Buitenrand. Dezelfde procedure geldt natuurlijk ook voor de andere delen waar je een rand omheen wilt zetten. De cellen die in kolom D van een rand zijn voorzien selecteer je ook (dus D5 selecteren, met de Shifttoets ingedrukt op D8 klikken) maar kies dan voor Alle randen. Zo ook de andere cellen van een randopmaak worden voorzien.
Het voorzien van cellen van een kleur gaat net zo. Selecteer die cellen en klik op het pijltje naast de knop Opvulkleur, klik op de gewenste kleur en de cellen staan er dan echt gekleurd op.
De cellen die in het model van een kleur zijn voorzien zijn bedoeld om onderscheid te maken tussen de verschillende “onderdelen” in het model. Alleen de cellen die in het rood zijn weergegeven kun je een bepaalde functie toekennen, zij bevatten de formules die nodig zijn voor de berekeningen. De boodschap is dus: van de rode cellen blijf je af. Een soort beveiliging dus. Nou zitten er in Excel heel andere methoden om een werkblad te beveiligen maar daarover later meer. Hoe dan ook, je hebt de cellen geselecteerd om die rood te kleuren, tegelijk kun je ze ook van een andere tekstkleur voorzien. Druk op het pijltje naast de knop tekstopmaak en klik op de gewenste kleur, in dit geval voor wit.



Tijdens het typen van wat langere tekst zul je zien dat de tekst doorloopt in de volgende cel. Op een eenvoudige manier is de tekst passend te maken. Zet de muisaanwijzer op de lijn tussen de kolomkoppen A en B, de cursor verandert “kolom verbreden” zoals in het (bovenstaand) overzicht is weergegeven. Sleep de cursor met ingedrukte linkermuisknop naar rechts of geef een dubbelklik, de kolom is aan de breedte van de tekst aangepast.



Hoe krijg ik dat 2tje zo klein half boven de m? Is niet zo moeilijk als het misschien wel lijkt. Je typt in cel E8 gewoon m2, laat de cel geselecteerd staan en veegt met de linkermuisknop ingedrukt over de 2 in de formulebalk. Klik met rechts op de cel en kies voor celeigenschappen, vervolgens voor Lettertype en vink de optie Superscript aan. Druk op de Entertoets en zie daar. Om de opmaak van E8 in E15 over te nemen passen we de truck met de kwast weer toe, je hoeft nu maar eenmaal op de kwast te drukken omdat het nu om de opmaak van een cel gaat.
Dan D7 die de opmaak valuta moet krijgen. Klik met rechts op de genoemde cel en kies voor celeigenschappen, vervolgens voor Valuta. In het rechtervenster kun je het aantal decimalen opgeven en het symbool, tegelijk kun je aangeven hoe je negatieve getallen weergegeven wilt zien. (Dat zal verderop van pas kunnen komen)



Voor de cellen D11 en D12 geldt een andere procedure om de % notatie, niet alleen weer te geven, maar juist ook om die cellen echt die notatie mee te geven zodat Excel er mee kan rekenen. Voordat je de gegevens in die cellen typt klik je eerst op de procentknop uit de werkbalk Opmaak. Dat doe je zo: selecteer beide cellen (eerst D11 en met ingedrukte Shifttoets ook D12), vervolgens druk je op de % knop. Pas daarna type je de waarden in de cellen.

Klaar met het maken van het model? Dan slaan we nu aan het rekenen, d.w.z.: Excel gaat voor ons rekenen. Om dat te bereiken is het nodig dat we Excel vertellen hoe en wat er uit te rekenen valt.
Als eerste typen we het = teken in de cel waarin de berekening moet komen, voor Excel het sein dat er gerekend moet worden. Iedere ingevoerde formule bevestigen we door op Enter te drukken.
We beginnen met D15. In deze cel berekenen we eerst de oppervlakte van de tegel en dat doen we zo: we typen de formule =D5*D6/10000 in de genoemde cel. We vermenigvuldigen dus de lengte met de breedte en delen dat door 10000 omdat we de maten die in cm opgegeven zijn willen omrekenen naar m2. In D16 komt de formule =D8/D15+(D8/D15*D11) te staan om het aantal tegels te berekenen die in 8 m2 gaan plus 15% extra om te versnijden. In D17 typen we een iets simpeler formule =D16*D7 om de totale netto prijs te berekenen. Om de BTW over dat bedrag te berekenen nemen we de formule =D17*D12 in cel D18, tenslotte berekenen we de totaalprijs in D19 met =D17+D18.
Deze foutmelding wordt gegeven als in cel D15 een nul genoteerd staat of als de cel leeg is.

Een andere manier voor het opbouwen van formules.
Uiteraard kun je de formules zoals die hierboven zijn neergezet gewoon overtypen, daar is niks mis mee. Maar je kunt ook zelf de formules opbouwen. Dat gaat zo: plaats de celaanwijzer in b.v. D16 en type het = teken om aan te geven dat er een formule komt. Klik op D8 en Excel neemt de celreferentie over. Type het / teken en klik op D15 waarna je het + teken typt gevold door het ( haakje open. Herhaal dat voor de rest van de formule (vergeet niet om de operatoren tussen de celreferenties te zetten) en de formule af te sluiten door een ) afsluitend haakje te typen en op Enter te drukken.

Uitleg formules:
Als we 10 bij10 optellen en dat vermenigvuldigen met 2 dan is de uitkomst 40. Dat kunnen we uit het hoofd wel uitrekenen, daar hebben we Excel dus niet voor nodig. Maart let op! Type in een willekeurige cel =10+10*2 en je zult zien dat Excel het niet met onze rekenkunst eens is, de uitkomst die Excel geeft is 30. Dat heeft alles te maken met de volgorde van berekenen in Excel. Om kort te gaan geef je zelf op wat de volgorde van berekenen moet zijn. Aangezien Excel het vermenigvuldigen ( en ook delen) voor laat gaan boven optellen en/of aftrekken gebruiken we haakjes om de rekenvolgorde te bepalen. In het voorbeeld berekent Excel eerst 2x10 en telt daar vervolgens 10 bij op, de uitkomst is dan inderdaad 30. Om Excel de goede rekenvolgorde toe te laten passen gaan we dat deel van de berekening dat eerst berekent moet worden tussen haakjes zetten. De formule komt er dan zo uit te zien: =(10+10)*2 en dan geeft Excel wel het goede antwoord. Zo ook bij de formule =D8/D15+(D8/D15*D11) die we in cel D16 hebben gezet. Als eerste wordt het aantal benodigde tegels berekent en daar wordt dan 15% bij opgeteld doormiddel van het gedeelte dat tussen haakjes staat.

Om de berekening, naar mijn idee, een beetje inzichtelijker te maken is voor de formule =D8/D15+(D8/D15*D11) gekozen. Hetzelfde resultaat krijg je door: =D8/D15*(1+D11) te gebruiken.
Je ziet hieronder nogmaals de afbeelding van het model. Een van de verschillen is dat het model niet meer zo strak in de linkerbovenhoek is gepositioneerd maar dat er nu wat ruimte aan de linker en bovenkant is te zien. Staat mooier en overzichtelijker. Hoe doe je dat? Klik op de kolomletter A met rechts, de kolom wordt geselecteerd en een uitklapmenu verschijnt, klik op Invoegen en ziedaar de kolommen verschuiven naar rechts. Hetzelfde doe je met Rij1, klik met rechts op 1 en kies ook daar voor Invoegen. Het mooie van Excel is dat de formules, die we ingevoerd hebben, zich aanpassen aan de nieuwe situatie.
Ook zijn alle “lijntjes” (rasterlijnen) verdwenen die in de vorige afbeelding nog wel zichtbaar waren, alleen de kaders die we zelf hebben aangebracht zijn nog zichtbaar. Hoe je dat doet? Ga naar het menu Extra en kies voor Opties, het tabblad Weergave staat voor, haal het vinkje weg bij Rasterlijnen.



Buiten optische veranderingen is er nog een verschil met de vorige afbeelding. Aan de rechterkant zijn een drietal nieuwe elementen toegevoegd die we ook in het model gaan zetten. Type de tekst over en voorzie de cellen van een kader. Klik op J2 en type het = teken, klik op J17 en sluit af met Enter. Hetzelfde doe je met J3 maar nu klik je op E20. Achter Budget vul je het bedrag in. Hé, mijn budget is niet toereikend en toch wil ik voor dat bedrag vloertegels leggen. Wat nu? Het tekort bij je tante gaan lenen? Nee, liever niet. Excel heeft een functie in huis die je helpt naar het zoeken van een oplossing. Let maar op.
Klik op J6 en daarna op het menu Extra, ga voor Doelzoeken. De cel die we hebben aangeklikt zie je terug achter Cel instellen en dat is inderdaad de bedoeling. Type achter Op waarde: 900, dat is immers het bedrag dat we te besteden hebben. Nu nog een celreferentie achter: Door wijzigen van cel. De vraag is nu wat je aan wilt passen, het formaat van de tegels of misschien het bedrag?



Laten we maar voor het laatste kiezen en dat doen we zo: klik op het pijltje in het vakje achter: Door wijzigen van cel. Het venster verkleind zich en nu kun je klikken op E8, klik op het vierkantje om het venster weer in oude staat te herstellen.



Klik op OK en in het volgende venster kun je zien dat Excel een oplossing heeft gevonden Met ook hier een klik op OK wordt de waarde in cel E8 (waar de stuksprijs in staat) direct aangepast en daarmee het hele model.





In het geval dat er in een cel alleen maar hekjes getoond worden kan dat twee oorzaken hebben, of de cel is te smal om de inhoud te tonen of de formule is niet goed samengesteld en geeft dus een (niet geheel zichtbare) foutwaarde. In beide gevallen weet je wat je te doen staat. Wat betreft de foutwaarden, daar komen we later op terug.
Persoonlijk zou ik geen tegels van 20x40 in m’n kamer willen leggen, als jij dat ook niet wilt kun je naar hartenlust de waarden in de groene cellen veranderen. De berekening wordt na iedere wijziging aangepast. Heb je meer te makken als € 900 dan kun je dat natuurlijk ook aanpassen. Hoe je bij een eventueel tekort naar een oplossing kunt zoeken weet je, dat mag geen probleem meer zijn.

Tip:
Maak in cel J8 zelf een berekening wat het tekort, of overschot, op je budget is. Kijk naar de afbeelding Celeigenschappen hoe je een eventueel negatief saldo op gepaste wijze weer kunt laten geven.

[CorVerm]

Ook deze keer gaan we aan de hand van een voorbeeld aan de slag om Excel beter te leren kennen. Ditmaal gaan we een model maken om de meterstanden bij te houden om zo inzicht te krijgen in ons stookgedrag. Hier is gekozen om de standen maandelijks in te vullen, maar als je eenmaal weet hoe je zo’n model maakt kun je er ook voor kiezen om er een wekelijks gebeuren van te maken.

Vooraf:
Een bereik kun je selecteren door een cel aan te klikken de Shifttoets in te drukken, en ingedrukt te houden, en daarna de cel aan te klikken die je als laatste in de selectie op wilt nemen. Je kunt ook met ingedrukte linkermuisknop over het bereik “vegen” van links boven naar rechtsonder of, zo het uitkomt, naar beneden of naar rechts.


Let op dat de cursor tijdens het “vegen” er zo uitziet.

Afzonderlijke (niet aaneengesloten) cellen selecteer je door de Ctrl-toets in te drukken en de te selecteren cellen aan te klikken.

In dit deel aandacht voor relatieve en absolute formules.

Gebruikte knoppen.



We gaan aan de slag.


Maak bovenstaand model na en neem ook de reeds ingevulde meterstanden over, je kunt na het invoeren van de formules gelijk zien of deze werken. Hoezo werken? Natuurlijk werken de formules.
Voordat we verder gaan, gaan we eerst op herhaling. Van de vorige keer weet je nog dat er een deel van het model bestemd is om gegevens in te voeren (variabelen) en een gedeelte om te berekenen (berekening). Ook hebben we het gehad over de opmaak van een model dus kun je op dit model de opmaak toepassen zoals het jou uitkomt. Vergeet niet dat in een aantal gevallen opmaak ook functioneel kan zijn.
Het model namaken vergt enig typewerk maar bij de maanden aangekomen neemt Excel ons wat werk uit handen. Type in B6 “januari” (zonder aanhalingstekens), rechtsonder in de celaanwijzer zie je een blokje (vulgreep), zet de cursor op de vulgreep (de cursor verandert in een soort plusje) en sleep, met ingedrukte muisknop, de vulgreep naar beneden. Excel vult de maanden vanzelf aan. Hoe dat in z’n werk gaat leggen we aan het eind uit.
Van de vorige keer weten we ook nog dat tekst in Excel links wordt uitgelijnd en getallen rechts. Nu zie je in de kolommen C, D en E dat de uitlijning zoals omschreven is klopt, alleen “oogt” het niet zo lekker.

We gaan daar wat aan doen. Selecteer de cellen C5 t/m E17 en klik op de knop Centreren om tekst en getallen netjes in het midden uit te lijnen. De betreffende cellen in de kolommen G t/m I is dat (in het voorbeeld natuurlijk) al gedaan, dat “oogt” toch wel mooier. Dus voor die cellen in de kolommen G t/m I die voor centreren in aanmerking komen kun je hetzelfde doen. Herinner je je ook nog hoe je een kolom kunt verbreden om de tekst passend te maken? Dat gaan we hier weer doen, alleen op een andere manier. In Excel zijn er meerdere wegen die tot het einddoel leiden, vaak kun je af met een simpele druk op een knop, vaak zijn er meerdere mogelijkheden en soms kun je het doel alleen bereiken op andere manieren. Maar die zullen we gaandeweg ook bespreken.





Allereerst kolom B. Klik op de kolomletter en ga naar het menu Opmaak, zak naar onder om de muisaanwijzer op Kolom te laten rusten. Een uitklapmenu is het resultaat, waar we twee mogelijkheden zien om de kolom(men) op breedte te maken. Klik op Breedte… , het venster Kolombreedte verschijnt. De kolommen C t/m E en G t/m I hebben de breedte van 5,75 meegekregen. (let even op dat je voor het typen van de komma NIET die van het numerieke deel van het toetsenbord gebruikt, je kunt die n.l. wel gebruiken bij het invoeren van decimale getallen in Excel) Wil je van te voren weten welke breedte de kolommen standaard hebben (gaat ook op voor rijhoogte) dan zet je de muisaanwijzer op de scheiding van twee kolommen of rijen en je houdt even de linkermuisknop ingedrukt, je krijgt meteen te zien wat de breedte of hoogte is. AutoAanpassen aan selectie: klik daar op om de geselecteerde kolom(men) aan de tekst aan te passen.

Als laatste noemen we D18. Je ziet dat de tekst daar overloopt in de E18, er is niks aan de hand zolang je in die cel niks invoert.

Excel heeft de mogelijkheid aan boord om cellen samen te voegen en dat gaan we in dit geval dan ook doen. Selecteer de cellen en klik op de knop Samenvoegen en centreren, de twee cellen worden samengevoegd en de tekst wordt gecentreerd. Om de tekst weer links uit te lijnen druk je op de knop Links uitlijnen. Beperk het gebruik van cellen samenvoegen in zeer grote mate, en al zeker voor cellen van waaruit een berekening moet worden gemaakt. Conclusie: cellen samenvoegen? Liever niet, maar soms staat het wat netter.

De gebruikte formules.
We willen resultaten zien dus gaan we formules invoeren. De ingevoerde meterstanden moeten van elkaar afgetrokken worden (duhu). Dus kunnen we in G6 de simpele formule =C6-C5 invoeren en aangezien we de formule ook voor de andere cellen in kolom C willen gebruiken gaan we de formule met de vulgreep, je weet wel dat blokje, naar beneden slepen. Het mooie is dat Excel de celadressen gewoon aanpast, daar hebben we dus geen omkijken naar.

Het resultaat van deze formule is niet zoals we dat willen zien. Zeg nou zelf, voor de cellen waar gegevens ingevoerd zijn is het resultaat zoals het moet zijn maar verder slaat het nergens op. De eerstvolgende cel geeft een negatief resultaat en alle daarop volgende cellen laten een nulwaarde zien. Zullen we de formule dan maar veranderen zodat we een fatsoenlijker overzicht krijgen? Doen we. In G6 zetten we dus de formule: =ALS(C6>0;C6-C5;"") Zo, dat is een betere oplossing. We werken nu met de ALS() functie, een van de logische functies die Excel aan boord heeft. Wat doet deze formule? Eerst test de formule of er in C6 een waarde staat die meer (>)dan nul is. Is dat het geval dan pas trekt Excel de waarde van C5 af van de waarde in C6. Is er in C6 niets ingevuld dan moet G6 leeg ("" is 2x dubbel aanhalingsteken) blijven. De ; (puntkomma) die je in de formule ziet heet in Excel termen argumentscheider. Als we de formule op een populaire wijze samenvatten dan gaat dat zo: als C6 meer is dan nul dan trek je C5 van C6 af en anders laat je de cel leeg. Duidelijk? OK dan. Wanneer je nu de formule naar beneden doorvoert blijven alle cellen dus leeg. Vul je een nieuwe stand in dan worden de waarden netjes van elkaar afgetrokken. Je weet nu wat je te doen staat om ook de cellen H6 en I6 van een formule te voorzien en naar beneden door te voeren.

Nou zijn wij natuurlijk niet lui maar wel voor ons gemak. Excel biedt ons ook hier een stukje gemak en dat scheelt ons werk. Selecteer de cellen G6 t/m I6, zoals je ziet is de vulgreep alleen in I6 aanwezig. “Pak” daar de vulgreep en trek die naar beneden zover als nodig. Laat de vulgreep los en, ziedaar, de formules in de drie kolommen zijn, in een moeite door, gekopieerd.

Natuurlijk willen we ook weten hoe het met het totaal verbruik staat. Nu kun je de optelling maken door = G6+G7+G8 … enz. te typen in G18 maar dat is een behoorlijk gedoe. Nee, Excel heeft ook hier een functie voor die bovendien ook nog eens allerhande voordelen biedt. Klik op G18 en type de formule =SOM(G6:G17) in, het totaal wordt netjes opgeteld.

Wat ook kan is G18 selecteren en op de knop met het ∑ (autosom) symbool klikken (niet op het pijltje naast het symbool). Excel komt met een voorstel hoe de som samengesteld moet worden, Excel “ziet” (in ons geval) 22 staan en “begrijpt” hoe de optelling moet verlopen. De suggestie die Excel doet wordt door een stippellijn weergegeven, en met een druk op Enter is het ingeven van de formule een feit. Ben je het niet eens met het voorstel dat Excel doet, dan kun je dat simpelweg wijzigen door, met ingedrukte muisknop, over de cellen te slepen die je wel in de som terug wilt zien.
Sleep de celaanwijzer middels de vulgreep naar I18 om de formule te kopiëren, de celverwijzingen worden weer automatisch aangepast. Het grote voordeel van de functie SOM() is dat bij het invoegen van een rij (maar dat komt niet altijd uit) of een of meer cel(len) in het bestaande model, de tussengevoegde cel(len) ook in de som worden opgenomen. Mocht het zo zijn dat er tussen de getallen tekst staat, dan wordt de tekst genegeerd en de getallen worden toch opgeteld. Wanneer je het plus teken gebruikt om een optelling te realiseren en er staat een cel met tekst tussen, leidt dat tot de foutmelding #WAARDE!.


De tarieven zijn bedacht. Kijk op de site van je energieleverancier voor de juiste bedragen.
Nu willen we ook wel weten wat een rondje stoken kost, evenals het bedrag dat we aan elektriciteit kwijt zijn. Maak het tabelletje na. In cel K6 zetten we de formule: =ALS(G6="";"";G6*$O$5) die we weer doorvoeren naar beneden. Meteen valt op dat er in deze formule $ (dollar) tekens staan, dat betekent dat de verwijzing naar O5 een absolute verwijzing is. In de vorige formule ontbreken de $ tekens, dus zijn dat relatieve formules. Relatieve formules passen zich, in relatieve afstand aan, tijdens het doorvoeren of kopiëren. Absolute formules (of het gedeelte van de formule dat absoluut is) doen dat niet maar houden de cel waarheen verwezen wordt “vast”. Concreet: het eerste deel van de formule in K6 is relatief, die zal zich bij het doorvoeren aanpassen. De celverwijzing naar O5 is absoluut. Dat kan ook niet anders, zou je O5 niet absoluut maken dan zal tijdens het doorvoeren van de formule naar beneden ook O5 veranderen in O6…enz., en dat komt de berekening uiteraard niet ten goede. Anders gezegd: daar komt dan geen pepernoot van terecht.
De formule in K6 kun je middels de vulgreep doorvoeren naar L6 en M6, het enige wat je moet doen is de celverwijzingen in L6 en M6 aanpassen naar $P$6 en $Q$6.
Inmiddels weet je hoe je de formules voor meerdere kolommen door moet voeren.


Aangezien de tarieven van gas en elektriciteit meestal met vier en soms vijf decimalen worden opgegeven zie je die in de berekening ook terug. Is niet zo’n fraai gezicht. Daar kun je op twee manieren wat aan doen, de tarieven (uiteraard) gewoon met het aantal vereiste decimalen invoeren en daarna met de knop Minder decimalen het aantal terugbrengen tot twee. Excel rekent toch met het aantal decimalen dat je hebt opgegeven. Of je brengt in de resultaatcellen het aantal decimalen terug tot twee. Nou ja, er is nog een optie: Celeigenschappen > tabblad getal > getal en het aantal decimalen instellen op twee.
De berekende bedragen zijn “kale” bedragen. Daar komen uiteraard de nodige heffingen e.d. nog bij.


Zo ziet het gehele model er uit.

Aangepaste lijst.
We hebben afgesproken dat we terug zullen komen op het aanvullen van de maandnamen. Dat heeft alles te maken met “Aangepaste lijst” die je terug kunt vinden onder het menu Extra > Opties. In het scherm Opties klik je op de tab Aangepaste lijst. In het linkerdeel van het venster zie je de bestaande aanvullijsten staan, in het rechtervenster kun je zelf een lijst samenstellen. Vul de gegevens in het venster in, na ieder item en druk je op Enter. Ben je klaar met het opstellen van de lijst dan klik je op de knop toevoegen. In het vervolg kun je volstaan door alleen het eerste item te typen en met de vulgreep aan te vullen.



Voor het doorvoeren van getallen heeft Excel iets speciaals in huis. Typ in een cel een getal en in de cel daaronder (of ernaast) het daarop volgende getal, selecteer beide cellen en pak de vulgreep, sleep die naar beneden of naar links en Excel telt de opvolgende getallen door.

Voor deze keer weer even genoeg voer denk ik.
In de volgende aflevering gaan we iets doen met het oog op het komende EK voetbal.

[CorVerm]

Excel voor beginners deel 3.

In het vorige deel hebben we beloofd om iets te gaan maken i.v.m. het EK voetbal, en hoe dan ook belofte maakt schuld. Eenmaal begonnen aan dit deel heb ik me pas gerealiseerd dat de stap die we maken ten opzichte van de vorige delen best wel groot is. In ieder geval zal ik m’n best doen om zo duidelijk mogelijk te zijn en met een zo helder mogelijke uitleg verder te gaan met waar we de vorige keer gebleven zijn.

Eerst weer wat theorie.
Is het je opgevallen dat tijdens het typen van een formule de formuleopbouw in de betreffende cel te zien is? Ook in de formulebalk kun je de voortgang zien van de formuleopbouw, alleen moet je dan wel even in de formulebalk klikken, anders zie je alleen de getypte tekst. Als je =ALS( hebt getypt verschijnt de volgorde van de formuleopbouw in beeld, het vet gedrukte deel is aan de “beurt”. Heb je de logische-test (de voorwaarde waaraan voldaan moet worden) getypt dan volg een ; waarna het volgende deel van de formule vet in beeld komt. Uiteindelijk sluit je de formule af met een haakje. Zo meteen in de praktijk meer daarover.

Excel heeft vijf Logische functies in huis, ALS(), EN(), NIET(), OF(), ONWAAR() en WAAR(). Deze functies kun je onderling combineren (nesten), waarbij opgemerkt dat je in een formule zeven maal kunt nesten.

In dit deel komen de nodige formules voorbij evenals de functie Voorwaardelijke opmaak. Zo goed als dit hele model bestaat uit voorwaarden waarvan hierboven een voorbeeld te zien is. Hier gaat het om een Logische test. Uiteraard komen we daar straks op terug.

Wanneer je tekst wilt weergeven middels een formule moet je de tekst altijd tussen aanhalingstekens plaatsen, aan het eind van dit deel zal daar een voorbeeld van te zien zijn. Gaat het erom een getal weer te geven middels een formule dan moet je dat zonder aanhalingstekens opnemen in de formule.

Ook een aantal statistische functies komt aan de beurt, zoals de functie AANTAL.ALS(). Deze functie “kijkt” hoe vaak een bepaalde waarde voorkomt in een bepaald bereik.
De functie SOM.ALS() telt de waarden op die aan een bepaalde voorwaarde voldoen.

Aan het werk dan maar.
Maak onderstaande model maar na op de uitslagen na, die komen later aan bod. Geef de cellen waar Nederland in staat de kleur oranje door ze te selecteren (met ingedrukte Ctrl-toets) en bij Opvulkleur de juiste kleur te kiezen.



Voor de opvulkleur in de kolommen J en K gaan we een heel ander verhaal vertellen. Daarvoor gebruiken we de functie Voorwaardelijk opmaak, we weten immers van te voren niet in welke kolom Nederland terecht komt.

Selecteer de cellen J4:K9, ga naar het menu Invoegen en kies daar voor Voorwaardelijke opmaak.



In het scherm Voorwaardelijke opmaak staat standaard “Celwaarde is” geselecteerd. Klik op het pijltje in het vakje waar “gelijk aan” staat en kies deze optie. Typ vervolgens Nederland (zonder het = teken of de aanhalingstekens) en klik op de knop Opmaak. In het scherm dat dan verschijnt kies je voor de tab Patronen en vervolgens voor oranje door op het betreffende vakje te klikken. Klik tweemaal op OK om de vensters te sluiten. Elk van deze cellen zal oranje kleuren als “Nederland” daarin verschijnt.
Maar wat nu? Als Nederland gelijk speelt zien we geen oranje cel(len) terug. Vandaar dat we een tweede voorwaarde toe gaan voegen.

Let op! Klik J4 aan, ga weer het menu Invoegen > Voorwaardelijke opmaak en klik in het scherm “Voorwaardelijke opmaak” op de knop toevoegen.



Kies nu voor “Formule is” en neem de formule over zoals je die ziet staan in de afbeelding, waarbij opgemerkt dat je nu wel alle tekens moet typen. Druk weer op de knop Opmaak en kies weer voor de opmaak zoals beschreven. Twee maal op OK klikken en ook dit is gefikst. Bijna dan, want we moeten de opmaak nog kopiëren naar de andere cellen.
Klik op de kwast Opmaak (je hebt nog steeds J4 geselecteerd staan) en klik op J5, houdt de muisknop ingedrukt en sleep de cursor (in de vorm van een kwast) tot aan J9. Nu hebben ook deze cellen de goede (voorwaardelijke) opmaak. Voor de cellen in kolom K geldt hetzelfde verhaal.
Bewust is hier gekozen voor een wat omslachtige manier van werken, het kan wat directer maar nu weet je hoe het vanaf de bodem in z’n werk gaat.

Je ziet dat alle uitslagen zijn ingevuld, voor het Nederlands elftal niet te hopen dat het zo afloopt als in dit voorbeeld, dus kun je deze uitslagen maar beter niet overnemen. Wat je wel moet doen is een kleine formule plaatsen in I4 (voor de duidelijkheid: ie4). In I4 komt: =ALS(OF(G4="";H4="");"";G4+H4), (zonder komma) deze formule telt het aantal doelpunten op die in de wedstrijd gevallen zijn. Je zou in dit geval kunnen volstaan met: =G4+H4, ’t is maar dat het aantal doelpunten van de wedstrijd bij elkaar opgeteld worden. ’t Is ook een mooie gelegenheid om te laten zien hoe je twee logische functies kunt gebruiken in een formule.


Door logische functies te nesten kun je meerdere voorwaarden (7X) gebruiken.

Het eerste deel van de formule kijkt of de cellen G4 en H4 leeg zijn, (="" ,je weet nog wel dat het hier 2X een dubbel aanhalingteken betreft) is dat het geval dan moet I4 ook leeg blijven. Pas als er G4 en H4 iets ingevuld is komt de som in I4 te staan. Let op de haakjes, om de OF() functie af te sluiten typ je een ) haakje. Door een ; te typen ga je verder met de ALS() functie. Goed, I5 is nog geselecteerd, druk op de Shift-toets en klik op I9, druk vervolgens op de toetsen Ctrl-D om de formule naar beneden te kopiëren. De truc met de vulgreep ken je al.



Als het even kan willen we ook wel weten wie er gewonnen, verloren of gelijk gespeeld heeft. Zet in J4 de formule: =ALS(G4="";"";ALS(G4=H4;"Gelijk";ALS(G4>H4;E4;F4))), een hele mond vol zo lijkt het. Eerst kijken we of G4 leeg is, in dat geval moet J4 ook leeg blijven. Daarna kijken we (nou ja, de formule natuurlijk) of de uitslag in G4 en H4 gelijk is, in dat geval moet er in J4 het woord Gelijk verschijnen. Is de uitslag in G4 meer dan (>) H4 dan komt in J4 de tekst uit E4 te staan en anders de tekst uit F4. Duidelijk zo?
In K4 zetten we de formule: =ALS(H4="";"";ALS(G4=H4;"Gelijk";ALS(H4>G4;E4;F4))). We selecteren J4 en K4 en “trekken” de formules met de vulgreep naar beneden tot en met J9 en K9.
We hebben nu driemaal de ALS() functie genest hetgeen betekent dat we de formule met drie haakjes moeten afsluiten. Het is altijd zo dat er, hoe dan ook, evenveel haakjes dicht als haakjes open in de formule gebruikt moeten worden.

We nemen de cellen L4:L9 nog onder de loep.


Ditmaal is =ALS(EN( aan de beurt. (Hebben we trouwens al verteld dat dit soort formules altijd beginnen met de functie ALS(). Nee? Bij deze dan.) Ditmaal doet het er wel degelijk toe of aan beide voorwaarden wordt voldaan. Want pas als er in de cellen J4 en K4 het woord gelijk staat moet er in L4 een 1 komen te staan en anders moet L4 leeg blijven.

Om te zien hoe de vlag er verder bij hangt maken we het onderstaande model na, uitgezonderd de getallen. Want die komen vanzelf voor de dag als er, uitslagen worden ingevuld. Maar eerst gaan we natuurlijk de nodige formules invoeren.



In N4 zetten we de formule: =SOM(O4;P4;Q4), hoewel de cellen O4, P4 en Q4 dan nog leeg zijn zal Excel een nul laten zien omdat Excel in dit geval een lege cel ook voor nul aanziet.

In O4 komt de formule: =AANTAL.ALS(J$4:J$9;M4).

Als eerste geef je het bereik op waar Excel moet zoeken, als tweede het criterium waarop Excel moet zoeken. Concreet gezegd moet er in het bereik J$4:J$9 gekeken worden hoe vaak daar de waarde uit M4 (Nederland) in voorkomt. De dollartekens voor de celverwijzing zijn nodig om tijdens het kopiëren van de formule het juiste bereik vast te houden. (Zie deel twee voor relatieve en absolute formules)
In P4 zetten we: =AANTAL.ALS(K$4:K$9;M4) zodat we ook kunnen zien hoe vaak Nederland heeft verloren. Je weet onderhand wel dat je de formules tot in rij negen moet kopiëren.

Dan is Q4 aan de beurt, we kijken daar hoe vaak er gelijk gespeeld is met: =SOM.ALS(E$4:E$9;M4;L$4:L$9)+SOM.ALS(F$4:F$9;M4;L$4:L$9)

Ook hier gaan we kijken of in het opgegeven bereik het opgegeven criterium voorkomt om, als dat zo is, in het optelbereik de som te maken. Concreet: als in E$4:E$9 Nederland (M4) voorkomt dan tel je het aantal enen op die in het bereik L$4:L$9 achter Nederland staan. Om alles compleet te maken moeten we ook in het bereik F$4;F$9 kijken of ook daar het gezochte land in voorkomt, om middels het + teken de twee uitkomsten bij elkaar op te tellen.

Om het aantal bij elkaar gespeelde punten te bepalen komt in S4 de formule: =(O4*3)+(Q4*1)

Dan hebben we het totaal aantal doelpunten nodig om te kunnen bepalen hoeveel er voor en tegen gescoord is met in T4: =SOM.ALS(E$4:E$9;M4;I$4:I$9)+SOM.ALS(F$4:F$9;M4;I$4:I$9)

Voor het aantal doelpunten voor gebruiken we in U4: =SOM.ALS(E$4:E$9;M4;G$4:G$9)+SOM.ALS(F$4:F$9;M4;H$4:H$9)

Om voor het aantal tegendoelpunten in V4 =T4-U4 te zetten.
Het saldo wordt berekent door =U4-V4 in W4 te zetten.
Niet vergeten om de formules te kopiëren.



Een aantal kolommen hoeven we niet meer te zien en dus gaan we die verbergen. Selecteer de kolommen I t/m L en kolom T door op de kolomletter te klikken met de Ctrl-toets ingedrukt, een maal geselecteerd klik je met rechts op een van de kolommen en kies je voor Verbergen.
Het geheel ziet er dan zo uit. Vind je het netter staan om tussen de uitslagen en de groep een vrije ruimte te zien, klik dan met rechts op kolomletter M en kies voor Invoegen. De formules passen zich vanzelf aan de nieuwe situatie aan.



Tenslotte zouden we in het groepsoverzicht ook de juiste rangschikking wel willen zien en dus gaan we daar wat aan doen. Geef eerst ook de cellen M4:M7 de voorwaardelijke opmaak voor Nederland.

In X4 komt de formule: =RANG(S4;S$4:S$7;0) die je kopieert tot in X7.

Aangezien we van het puntenaantal de Rang gaan bepalen nemen we voor getal S4, verw staat voor het bereik waaruit de Rang moet worden bepaald (S$4:S$7). Dan volgorde dat tussen vierkante haken staat, wat in dit geval betekent dat het een optioneel onderdeel van de formule betreft. Laat je de “volgorde” weg dus =RANG(S4;S$4:S$7) dan zal Excel altijd rangschikken op de hoogste volgorde, dus in het voorbeeld heeft Italië 7 punten en krijgt de rang 1 mee. Hier, in het voorbeeld, is wel volgorde gebruikt en geeft hetzelfde resultaat omdat er een nul als volgorde is opgegeven. Zou daar een 1 staan dan zou Italië rang 4 hebben, dus niet oplopend maar aflopend gerangschikt.
Zolang we nog geen uitslagen hebben ingevoerd staan alle gegevens in het groepsoverzicht op nul, hetgeen betekent dat de rangschikking in alle gevallen op 1 komt te staan. We kunnen dit ondervangen door de volgende formule te gebruiken: =ALS(N4=0;"";RANG(T4;T$4:T$7)). In kolom N komen het aantal gespeelde wedstrijden te staan, en dus in tegenstelling tot de andere kolommen (hangt af van winst, verlies of gelijkspel) altijd optellen. Onnodig te zeggen dat ook deze formule naar beneden moet worden gekopieerd. Je kunt er ook voor kiezen om tekst weer te geven zolang er niets te sorteren valt, dat doe je zo: =ALS(N4=0;"nog geen rang";RANG(S4;S$4:S$7)), m.a.w. is de waarde in N4 0 geef dan de tekst weer. In dit geval komt er dan: nog geen rang te staan.
Nu we de rang bepaald hebben kunnen we gaan sorteren. Selecteer M4:X7, ga in het menu Data naar Sorteren en kies bij “Sorteren op” voor Kolom X door op het pijltje te klikken.



Let even op dat de optie Oplopend is geselecteerd evenals de optie Geen veldnamenrij. Alhoewel Excel dat laatste ook wel in de gaten heeft kan het geen kwaad om dat toch even te controleren.

Het resultaat van de sorteeractie.

Rangschikken op alleen het aantal punten is een hachelijke zaak. Wat nu als er twee (of misschien wel meer landen) hetzelfde aantal punten behalen? Hier ligt een mooie uitdaging voor jou om dat vraagstuk op te lossen. Je kunt dan denken aan een som van punten en saldo, maak daar dan een aparte kolom voor en laat Excel daar dan op rangschikken. Nou ja, laat je creativiteit maar spreken.

In principe ben je nu in staat om een heel speelschema op te bouwen aan de hand van het bovenstaande. Houdt er rekening mee dat je de bereiken aanpast aan de nieuwe situatie. Maar oké, wij zijn de beroerdste niet en doen er een compleet schema bij. Veel plezier er mee, als je tenminste van voetballen houd.

Tenslotte nog een opmerking over de gebruikte formules.
In de formules hebben we steeds gebruik gemaakt van celverwijzingen, terwijl het ook mogelijk is om met bereiknamen te werken. Goed, het is er om te doen dat je met Excel kan leren werken dus moeten alle opties besproken worden. En dan begin je bij het begin. Even hebben we er aan gedacht om ook hier het Naam geven te bespreken maar dat is misschien wel teveel van het goede in een keer. Vandaar dat we in de volgende aflevering daar op terug komen.

[CorVerm]

Excel voor beginners deel 4.

Zoals we in deel drie hebben beloofd gaan we het in deze ronde hebben over het geven van namen aan (een) cel(len) of bereik(en). Het voordeel is dat namen gemakkelijk te gebruiken zijn in formules en het navigeren door een werkblad.
Tegelijk leggen we de functie VERT.ZOEKEN() uit omdat die functie in het te gebruiken model voorkomt. Verderop vind je de uitleg.


Eerst maar weer de theorie.
• Een naam mag uit niet meer dan 255 tekens bestaan. (zou je daar genoeg aan hebben?)
• Spaties zijn niet toegestaan. Je kunt wel een naam geven die uit meerdere woorden bestaat maar dan moet je een underscore (onderstrepingsteken) gebruiken. Zoals km_zakelijk, of de woorden aaneen typen.
• Evenmin kun je een celreferentie opgeven als naam. Soms krijg je een foutmelding na het invoeren van een naam omdat je een naam hebt gebruikt die binnen Excel is gereserveerd. Geef dan gewoon een andere naam.
• Wanneer je een naam in een formule gebruikt dan mag je die niet tussen aanhalingstekens plaatsen. (dit in tegenstelling tot het gebruik van tekst in een formule)



Je kunt op twee manieren een naam toekennen. Als eerste kun je een cel of een bereik selecteren om vervolgens in het naamvak te klikken en daar een naam voor de cel of bereik te typen (en op Enter te drukken), ten tweede kun je via het menu Invoegen > Naam een naam bepalen.



Eenmaal op de optie Naam geklikt verschijnt er een uitklapmenu met meerdere opties die we stuk voor stuk bespreken.



Definiëren:
Middels deze optie zie je het overzicht van alle toegekende namen. Wijzigen van het bereik van een bepaalde naam kun je doen onderin het venster in het vak “Verwijst naar”, sluit dan altijd het venster middels de knop OK. Inderdaad, zoals de venstertekst het al zegt, kun je hier ook een naam bepalen. Druk op de knop Toevoegen en typ een naam in het naamvak, vervolgens klik je op de knop met het pijltje rechtsonder in het venster en het scherm verkleint zich. Sleep met ingedrukte muisknop over het bereik dat je aan de opgegeven naam wilt geven, of klik op de cel die je van die naam wilt voorzien. Natuurlijk kun je het bereik of het celadres ook intypen.
Een naam kun je ook verwijderen (een naam per keer). Selecteer de naam die je wilt verwijderen en klik op de knop Verwijderen, sluit het venster door op de OK knop te drukken.



Plakken:
Selecteer een cel buiten het bereik van het model om middels deze optie de reeds toegekende namen in het werkblad te plakken. Zeker als het meerdere namen betreft is het wel handig om die in het werkblad zichtbaar te hebben. Klik op de knop Lijst plakken om de namen in het werkblad te plakken.
Een andere mogelijkheid is om een naam direct in een formule te plakken. Als je tijdens het typen van de formule aan het invoeren van de naam bent toegekomen haal je het venster Plakken voor de dag, selecteer de naam die je in de formule in wilt voegen en klik op OK.



Maken:
Excel maakt hier namen voor de kolomlabels die we vaak gebruiken. Ook in het model dat we hier als voorbeeld nemen gebruiken we kolomlabels. Kolomlabels zijn hier o.a. Beginstand, Eindstand, Van, Naar enz. (zie afbeelding van het model verderop) Selecteer een bereik inclusief de labels, zet een vinkje voor “Bovenste rij” en de kolomlabels vind je als namen terug in het namenoverzicht. Deze namen kunnen als naam gebruikt worden voor de onderliggende rijen, ook in formules.

Toepassen:
Deze optie is alleen te gebruiken als je middels de optie Naam maken inderdaad namen hebt gemaakt. Na de uitleg over het gebruik van namen aan de hand van onderstaand model zullen we dit apart toelichten.

De namen zoals die in het werkblad zijn geplakt middels de optie Naam plakken.
* Het afdrukbereik wordt door Excel zelf bepaald.





Door op het pijltje naast het naamvak te klikken zie je eveneens de toegekende namen. Wanneer je op een naam klikt wordt de cel of het bereik dat aan de naam gekoppeld is geselecteerd.

Uitleg over de functie VERT.ZOEKEN()


=VERT.ZOEKEN(H5;tarieven;3;WAAR)*H5)
Eerst dit: zoals je ziet hoef je de formules niet met hoofdletters te typen, als er geen fout(en) in de formule zit(ten) maakt Excel er zelf hoofdletters van.
Als eerste argument moeten we de zoekwaarde opgeven. De zoekwaarde geeft een waarde terug die gevonden wordt uit de rij die in de opgegeven kolom gevonden wordt. In ons geval is de zoekwaarde H5 (uit de kolom Kilometers zakelijk).
Het tweede argument is tabelmatrix. Geef hier het bereik op waarin de zoekwaarde gevonden moet worden of, zoals in ons geval, een bereiknaam. In dit voorbeeld is dat dus de naam tarieven.
Argument drie betreft de kolomindex_getal. Hier geef je aan in welke kolom er naar de juiste waarde moet worden gezocht. Hier betreft het kolom drie (kolom C dus), want we zijn op zoek naar het tarief dat bij een bepaalde kilometerstand hoort.
Het laatste argument in de functie is benaderen. Hier is het even opletten welk argument je gebruikt want hiermee valt of staat het resultaat. Je kunt het argument WAAR of ONWAAR opgeven. Voor WAAR kun je ook een 1 zetten en voor ONWAAR een 0. WAAR (of niets invullen) wil zeggen dat Excel zoekt naar de meest naastliggende waarde die lager is als de zoekwaarde, uiteraard is dat het geval als de juiste waarde niet kan worden gevonden. In het geval van ons voorbeeld moeten we wel kiezen voor WAAR omdat een exacte overeenkomst niet kan worden gevonden. Bij gebruik van het argument ONWAAR zoekt Excel altijd naar een exacte overeenkomst en als die niet wordt gevonden geeft Excel de foutmelding #NB. We komen daar straks op terug.



Behalve de gebruikte namen in het model zijn er ook een paar formules (onvermijdelijk) gebruikt en een zoekfunctie. Maar laten we beginnen met de werkbladen te voorzien van een naam. (deze naam heeft niets te maken met het naam geven in het werkblad). Dubbelklik op de tab Blad1 en typ daar Rittenregistratie in, zo ook met Blad2 maar geef die de naam Tarieven. Je kunt ook met rechts op de tab klikken en kiezen voor Naam wijzigen.

Eerst een formule. Typ in B6: =ALS(C5>0;C5;"") en kopieer de formule naar beneden. Zo,dan hoeven we de eindstand van de vorige rit niet over te typen als beginstand van de volgende rit.
We gaan de eerste naam geven in het werkblad, en wel voor het bereik F5:F16. Zoals je in het voorbeeld ziet is F5 geselecteerd en is er een pijltje naast de cel verschenen. Als je op het pijltje klikt zie je een keuzemenuutje en kun je kiezen uit Zakelijk of Privé. Zet in b.v. cel Z1 de tekst Zakelijk en in Z2 Privé, selecteer de beide cellen. Klik in het naamvak en typ daar de naam Zakelijk_Privé in en druk op Enter, of definieer de naam via het menu Invoegen > Naam > Definiëren. Ga vervolgens naar het menu Data en kies voor Valideren, in het venster Gegevensvalidatie kies je voor Lijst en onder Bron typ je = Zakelijk_Privé.



Nu moeten we er meteen bij vertellen dat het niet puur noodzakelijk is om een naam te geven, je kunt ook een bereik opgeven mits je dat doet (zoals nu in ons geval) in hetzelfde werkblad. Wil je de te valideren gegevens op een ander werkblad zetten dan zul je inderdaad wel een naam moeten geven omdat, in dat geval, een opgegeven bereik niet werkt.
In het geval je meer opties aan de lijst wilt toevoegen kun je de lijst uitbreiden door onderaan de lijst verder te typen. Je moet dan wel het bereik aanpassen (zie Naam bepalen), vergeet niet om dat venster af te sluiten met de OK knop.

In H5 zetten we de formule =ALS(OF(B5>0;C5>0);ALS(F5="zakelijk";C5-B5;"")), dus als zowel B5 als C5 leeg zijn dan komt er in H5 ook niks te staan en anders worden de waarden in B5 afgetrokken van C5.

=ALS(OF(B5>0;C5>0);ALS(F5="privé";C5-B5;"")) komt in L5 te staan en beide formules kopieer je naar beneden.

Aan het bereik H5:H16 is de naam km_gereden_zakelijk gegeven, puur om het gebruik van een naam te verduidelijken. Als je in H3 de formule =SOM(km_gereden_zakelijk) zet worden de waarden in het bereik met die naam netjes opgeteld.
Zet in J3 de formule =SOM(J5:J3000) en je hebt hetzelfde resultaat maar is niet echt duidelijk in de benaming.
Geef H3 de naam km_zakelijk en L3 de naam km_privé.
Met in L5 =km_zakelijk+km_privé tel je de beide waarden op om tot het totaal aantal gereden kilometers te komen.

Nu we dit deel van het model klaar hebben gaan we de tarieven die bij het aantal gereden kilometers horen erbij zoeken. Klik op de tab van het werkblad Tarieven en maak onderstaande tabel, selecteer de tabel en geef het de naam tarieven.



Met de functie VERT.ZOEKEN() zoeken we in de tabel “tarieven” de juiste bedragen op.

Zet in N5 de formule:
=ALS(F5="zakelijk";VERT.ZOEKEN(H5;tarieven;3;WAAR)*H5;"")
M.a.w. als in F5 de tekst zakelijk staat moet er gezocht worden, met als zoekwaarde H5 (gereden kilometers), in de tabel tarieven in kolom 3 (waarin de bijbehorende tarieven staan) met het argument WAAR. Het gevonden bedrag wordt vermenigvuldigd met H5 (*H5). Staat er in F5 een andere tekst, of als de cel leeg is, dan moet N5 leeg blijven (""). I n plaats van N5 leeg te laten kun je ook een tekst laten weergeven zoiets als: Niet zakelijk. Zet de tekst dan tussen de laatste aanhalingstekens.
In P5 komt de formule =ALS(F5="Privé";J5*VERT.ZOEKEN(J5;tarieven;3;WAAR);"") en beide formules kopieer je naar beneden.
Nu we de bedragen in beeld hebben kunnen we de totalen gaan optellen. In N3 komt =SOM(bedrag_zakelijk), in P3 =SOM(bedrag_privé) en in R3 =SOM(bedrag_zakelijk)+SOM(bedrag_privé). Merk het verschil op met de formule die we gebruiken om het totaal aantal kilometers op te tellen. Daar tellen we de som op van km_zakelijk en van km_privé ( de namen die we gegeven hebben aan L3 en N3), nu tellen we rechtstreeks de beide (naam) bereiken op.

Aan de hand van een eenvoudig voorbeeld lichten we de functie VERT.ZOEKEN() nog even toe.



In A2 typ je een naam om het adres erbij te zoeken. We doen dat met de formule (in C2)
=VERT.ZOEKEN(A2;E2:F6;2;ONWAAR). Het laatste argument wil dus zeggen dat er naar de exacte overeenkomst van de zoekwaarde moet worden gezocht. Maar wat als de zoekwaarde niet overeen komt? Zoals hierboven gezegd geeft Excel dan de foutmelding #NB.



De naam geert komt in de tabel niet voor vandaar de foutmelding. Aan de foutmelding kunnen we wat doen door de formule uit te breiden.



=ALS(ISNB(VERT.ZOEKEN(A2;E2:F6;2;ONWAAR));"komt niet voor";VERT.ZOEKEN(A2;E2:F6;2;ONWAAR))

Bovenstaande formule is een geheel, dus typ die gewoon aan elkaar.

De formule begint met de logische functie ALS() gevolgd door de functie ISNB(). De laatste functie kun je vertalen als is #NB (NB = niet beschikbaar).
Populair vertaald luidt de formule: =als( is niet beschikbaar(is niet beschikbaar waarde in de vorm van vert.zoeken)) als de gezochte waarde niet voorkomt dan ”komt niet voor”;is de gezochte waarde wel beschikbaar dan inderdaad wel vert.zoeken)).

Tenslotte, we hebben beloofd dat we het maken van een naam uit zullen leggen, dus doen we dat dan ook.



Dit deel van het model hebben we geselecteerd om van de tabellabels een naam te maken, dat wil zeggen van B4:H17. We zijn naar het menu Invoegen > Naam gegaan en hebben daar gekozen voor de optie Maken. Met als gevolg dat de tabellabels nu ook in het rijtje met namen staat.



We hebben deze actie ondernomen om het toepassen van een naam te kunnen toelichten dus daar gaan we dan.



Wederom via het menu Invoegen > Naam > Toepassen komen we in het scherm Naam gebruiken. In het voorbeeld is gekozen om de namen Beginstand, Eindstand en Kilometers_zakelijk te gebruiken. Dus selecteer die, laat de vinkjes staan en klik op OK. Het gevolg is dat er in elke rij van de kolom Kilometers zakelijk de formule:
=ALS(OF(Eindstand>0;Beginstand>0);ALS(F5="zakelijk";Eindstand-Beginstand;"")) komt te staan. Het aardige is dat Excel die gegevens uit de betreffende rij haalt die nodig zijn voor het berekenen van de formule. Dus zonder het nader opgeven van de celreferenties. Bovendien past Excel alle formules in het werkblad aan die refereren aan Kilometers_zakelijk.

Alle beloften hierboven gedaan zijn ingelost, dus aan jou om het e.e.a. verder uit te werken.
Veel succes.

[CorVerm]

Deel 5. Kasboek in Excel.

Deze keer gaan we een kasboek maken in Excel. De meeste functies die we gaan gebruiken ken je al van de vorige afleveringen, maar er zijn ook een aantal nieuwe of ook geneste functies bij.

Laten we beginnen om de tabs van de werkbladen van een andere naam te voorzien, in ieder geval twee van de drie tabs. Voor alle duidelijkheid: het is handig om Blad3 te verwijderen om daar straks geen “last” van te hebben. Dubbelklik (of klik met rechts op de tab om voor Naam wijzigen te kiezen) op de tab van Blad1 en geef het de naam jan (van januari), dubbelklik op de tab van Blad2 en geef die de naam totaal mee. Met het laatste blad gaan we beginnen om het kasboek op te zetten. Meteen spreken we af dat we direct de nodige formules invoeren zodat we daar geen omkijken meer naar hebben.



Neem het overzicht zoals hier is afgebeeld over. Uiteraard kun je andere inkomstenbronnen invullen als er een bij staat die niet van toepassing is op jouw situatie, of juist een inkomstenbron toevoegen. Selecteer de te gebruiken cellen in kolom C, klik daar met rechts op en kies voor Celeigenschappen. Daar aangekomen kies je voor Financieel met als opmaak twee decimalen en als symbool het Euroteken. Zet in C4 de formule =SOM(jan:dec!D10) en kopieer de formule naar beneden. Wat deze formule doet? We gaan, naast het werkblad totaal, nog twaalf andere werkbladen maken en dus raad je het al, deze formule telt van ieder werkblad de cellen D10 bij elkaar op. We krijgen zo een mooi totaaloverzicht van onze inkomsten en ook, onder het kopje uitgaven, van onze uitgaven. In C2 komt een geheel andere formule te staan namelijk: =SOM(VERSCHUIVING(C4;0;0;AANTALARG(C4:C100))). Een hele mond vol, dat wel. Aan de eerste functie (SOM) zie je dat het ook hier om een optelling gaat, in ieder geval van die cellen waar straks alle totalen van de werkbladen in komen te staan.


De functie VERSCHUIVING().

De tweede functie (VERSCHUIVING) geeft als resultaat een verwijzing naar een bereik met een opgegeven aantal rijen en kolommen van een cel of cellenbereik. Met andere woorden geef je een cel op waarvandaan je een rij, kolom of meerdere rijen en kolommen opschuift om een berekening uit te laten voeren.


De functie AANTALARG()

Aangezien we in dezelfde kolom blijven en we de functie AANTALARG gebruiken kunnen we voor rijen; kolommen; een nul ingeven. De functie AANTALARG telt het aantal niet lege cellen (ook cellen met daarin een formule tellen mee) om, in ons geval, de verschuiving als het ware te realiseren. Als bereik van het aantal argumenten hebben we het bereik C4:C100 opgegeven hetgeen zeer ruim bemeten is, maar goed beter teveel dan te weinig ruimte. Er is een andere optie om de som van kolom C te bepalen, weliswaar met dezelfde formule maar dan in een andere kolom gezet, bijvoorbeeld in K2. Zet daar dan de formule: =SOM(VERSCHUIVING(C4;0;0;AANTALARG(C:C))). In dit geval heb je geen celverwijzing nodig om het aantal argumenten goed te kunnen bepalen, simpelweg omdat in dit geval de optelling begint in C4 en het aantal argumenten die daaronder komen. In het geval je de som van de getallen bovenaan de kolom met bedragen wilt zien (staat wat netter) dan zul je gebruik moeten maken van de eerste formule om de eenvoudige reden dat er anders een kringverwijzing ontstaat en dus van een optelling geen sprake kan zijn. Het doel van deze formule is het variabel maken van een bereik.
Waarom deze formule gebruikt? Tenslotte zou je het ook met =SOM(C4:C100) toekunnen maar het gaat er hier om dat we inzicht krijgen in het gebeuren dat Excel heet.

We gaan verder met het opbouwen van het werkblad totaal. De uitgaven zijn nu aan bod, we maken verschil tussen maandelijkse en variabele uitgaven.



Neem de items uit het overzicht weer over. Zet in F5 de formule =SOM(jan:dec!J5) en kopieer die naar beneden. In F2 komt =SOM(VERSCHUIVING(E5;0;1;AANTALARG(F5:F100))). Hier zien we een iets andere opbouw van de formule als de formule die in C2 staat. In dit geval gaan we uit van de gegevens die in kolom E staan, aangenomen dat je voor het bedrag ook een omschrijving zet. Lijkt me overigens logisch. We gaan in deze formule dus uit van kolom E en verschuiven (SOM(VERSCHUIVING(E5;0;1;) een kolom naar rechts om daar de som te berekenen.
Met de formule =SOM(jan:dec!M14) in I5 berekenen we wat we in het lopende totaal aan abonnementen hebben betaald. Kopieer de formule naar beneden. In I2 zetten we de formule =SOM(VERSCHUIVING(I5;0;0;AANTALARG(I5:I100))). Tenslotte zet je in J2 =C2-F2-I2.

Nou ja, niet helemaal. Tenslotte moeten we twee bereiken van een Naam voorzien. Klik op E5 in het blad totaal, ga naar het menu Invoegen > Naam > Definiëren. Typ in het tekstveld (bovenaan in het venster) uitgaven_maand en neem de volgende formule over in het tekstvak Verwijst naar::. Je kunt de formule ook kopiëren en plakken in het tekstvak.

=VERSCHUIVING(Totaal!$E$5;0;0;AANTALARG(Totaal!$E:$E)-3)



Over de functie AANTALARG hebben we het al gehad maar in dit geval staat tussen de laatste twee haakjes -3. Immers de kopjes uit de cellen E2:E4 willen we (en hoeven we) niet mee laten doen. We selecteren nu H5 en gaan bovenstaande herhalen. Typ dan in het tekstvak onder Namen in werkmap: uitgaven, en onder Verwijst naar: =VERSCHUIVING(Totaal!$H$5;0;0;AANTALARG(Totaal!$H:$H)-3).


Overzicht van het blad totaal.

Zo, het maken van het totaaloverzicht zit er op dus gaan we verder met het maken van de werkbladen voor de maanden. Klik op de tab jan om het werkblad voor januari te kunnen maken. Overigens is dit werkblad de basis voor alle andere maanden maar daarover straks meer.



Eerst typen we in B2 de datum 1-1-2008 en drukken op Enter. Klik met rechts op de cel om naar Celeigenschappen te gaan en we kiezen daar voor Aangepast als weergave.



Typ onder Type viermaal een m en je krijgt als weergave de maand van de ingevoerde datum te zien. Het nut daarvan zien we terug als we met een formule gaan werken om het bedrag over te nemen van de vorige maand, maar dat zien we straks wel als we de bladen voor de andere maanden gaan maken.
Vervolgens nemen we de gegevens over uit de cellen B3:B6. In B9 typ je Datum, in D9 Bedrag, C8 krijgt als inhoud Inkomsten en in C9 komt Soort te staan. Voor C10 en volgende hebben we een formule in petto namelijk =ALS(Totaal!B4="";"";Totaal!B4). Op deze manier nemen we de gegevens van het blad totaal over, wat dus ook betekent dat wanneer je gegevens wilt wijzigen je dat altijd in het blad totaal moet doen. Onnodig te zeggen dat de formule naar beneden gekopieerd moet worden, toch? De formule die in D3 komt voeren we pas in als we de bladen voor de andere maanden gaan maken, dus komen we daar nog op terug. In D4 zetten we de formule =SOM(D10:D17) en in D5 komt =SOM(M:M), in D6 tenslotte =D3+D4-D5. Bij de bespreking van het blad jaar hebben we aangegeven dat je de cellen waarin de bedragen komen te staan de opmaak Financieel mee kunt geven. Voor D6 zou je de opmaak Valuta kunnen gebruiken, deze opmaak heeft als voordeel dat je aan kunt geven dat negatieve getallen in het rood moeten worden weergegeven. Zie je in een oogopslag dat de uitgaven de inkomsten overschrijden. Zo, hebben we meteen weer een mogelijkheid besproken die voorradig is binnen Excel.

Inkomsten zijn altijd welkom, daar zijn we het zonder meer over eens. Maar onvermijdelijk komen ook de (maandelijkse) uitgaven en daar gaan we nu mee aan de slag.



Neem de tekst uit de cellen F3, F4, F13, H4 en J4 over. Voor de invulling van de overige cellen in kolom F gaan we weer uit van het blad totaal. Selecteer het bereik F5:F12 en ga naar het menu Data > Valideren, neem de gegevens over zoals in de afbeelding is weergegeven. Let (nogmaals) op dat, wanneer je gegevens wilt wijzigen, je dat in het blad totaal moet doen.



Zoals gezegd (zie bespreking van het blad totaal) hebben we het bereik in het blad jaar variabel gemaakt en daar hebben we nu profijt van. Zeker als het gaat om de variabele uitgaven.


Selecteer eenvoudig het item dat je wilt gebruiken.

Selecteer F14 t/m, nou ja dat mag je zelf weten. Ga desnoods tot F100 dan hoef je niet om voldoende gevalideerde cellen verlegen te zitten. ’t Is maar hoe vaak je van jouw (zuur?) verdiende geld gaat shoppen. In ieder geval gaan we het opgegeven bereik weer valideren. Volg de procedure zoals omschreven maar typ nu onder Bron: =uitgaven. Straks zal ook blijken dat het hier gevalideerde bereik overeenkomt met de “vaste” gegevens die in kolom L komen te staan. Dat laatste geldt ook voor de vaste uitgaven.



We nemen eerst weer een aantal gegevens over met name uit de cellen L3, L4, L13 en M3. Zet in L5 de formule =ALS(F5="";"";F5) om de gegevens uit kolom F over te nemen, kopieer de formule tot in F12.
In L14 komt =ALS(Totaal!H5="";"";Totaal!H5) om weer de betreffende gegevens van het blad totaal over te nemen. Kopieer deze formule ruim naar beneden zodat, als je items toevoegt , er voldoende cellen beschikbaar zijn om die items weer te kunnen geven.


Overzicht van het blad januari.

Nu het blad voor januari klaar is gaan we dit werkblad kopiëren.



Klik met rechts op de tab jan en kies voor Blad verplaatsen of kopiëren, geef aan voor welk werkblad je de kopie wilt hebben en vergeet vooral niet om een vinkje te zetten bij Kopie maken.



Het gekopieerde blad krijgt de naam jan (2) mee dus dubbelklikken we op de tab om de naam te veranderen in feb.
Vergeet niet om de datum in B2 aan te passen in 1-2-2008, dat is wel belangrijk voor een goede werking. In het voorgaande hebben we gezegd dat er in D3 een formule moet komen te staan, en dat gaan we nu dan ook doen. In D3 dus de formule: =ALS(B2<=VANDAAG();jan!D6;0). Met andere woorden, als de datum in B2 minder of gelijk is aan de datum van vandaag geef dan de waarde uit D6 van het blad jan weer.
Van het blad feb maken we tien kopieën! Geef elke tab de goede (maand) naam, pas in de cellen B2 de datums aan en de formules in de cellen D3.
Om ook met de volgende functie in Excel kennis te maken kun je Zoeken en vervangen gebruiken om de formules in D3 te veranderen.



Klik D3 aan en druk op de toetsen Ctrl en H, in het scherm zoeken en vervangen……., nou ja, je ziet in de afbeelding hoe het moet. Uiteraard zoek je in het blad april naar mrt (of indien je daar de voorkeur aan geeft de volle maandnaam) om door april te laten vervangen. Niets staat je in de weg om de formules “handmatig” aan te passen door de cel te selecteren en op de F2 toets te drukken. Het is een heilig moeten dat je dezelfde namen opgeeft als de namen die je aan de tabs heb meegegeven.


Overzicht van het blad januari met een aantal ingevulde bedragen.

Tenslotte.
Wil je de werkbladen jan t/m dec van opmaak voorzien dan kan dat in een keer, m.a.w. je hoeft dus niet elk blad apart op te maken. Selecteer alle bladen (behalve het blad totaal) door op de tab van jan te klikken, de Shifttoets in te drukken en met een klik op de tab van dec hebben we van de werkbladen een groep gemaakt.



Op deze manier maak je alle bladen in een keer op. Om de groep op te heffen kun je op de tab van het blad jaar (dus in ieder geval op een tab van een blad dat niet in de groep zit) klikken, of met rechts op een van de gegroepeerde bladen en kiezen voor Groepering bladen opheffen.

[CorVerm]

Functies in Excel.

Laten we eerst even kijken wat een functie is. Een functie bestaat uit een aantal argumenten die voor een vaste opbouw van de formule zorgen en een goede uitkomst garanderen, mits van de juiste argumenten voorzien.

Het grote voordeel van functies is dat je tussentijds gegevens kunt veranderen, cel(len) of rij(en) kunt invoegen of verwijderen zonder de dat werking van de functie verloren gaat. Dit geldt uiteraard alleen als je dat doet binnen het bereik van de functie. Als er zich bijvoorbeeld een cel met tekst bevindt in een kolom met getallen dan zal, bij het gebruik van een functie, de tekst genegeerd worden. De functie SOM() bijvoorbeeld trekt zich niets aan van een cel met tekst tussen een reeks getallen, de tekst wordt genegeerd en de getallen gewoon opgeteld.
Aan de hand van enkele voorbeelden willen we duidelijk maken hoe functies werken.

Het is zo goed als onmogelijk om alle functies van Excel uit het hoofd te kennen, maar als je wilt weten welke functie je nodig hebt raadpleeg dan de helpfunctie. Functies toepassen in Excel maar je weet niet hoe? Daar heeft Excel een wizzard voor in huis die je begeleid bij het samenstellen van de functie.

Het gebruik van de wizzard Functie invoegen.

Met een klik op het pijltje naast het autosom symbool open je een pop-up menu waar je direct uit al een aantal (eenvoudige) functies kunt kiezen. Als de functie die je wilt gebruiken niet in het lijstje staat klik dan op Meer functies om het scherm Functie invoegen te laten tonen. In het zwart gemarkeerde tekstvak vul je de functienaam in en druk je op de knop Zoeken. In het voorbeeld is gezocht naar “zoeken” en dat levert alle functies op die iets met zoeken te maken hebben. Wij willen de functie VERT.ZOEKEN() gaan gebruiken dus hebben we die geselecteerd en natuurlijk ook op de knop zoeken geklikt.



Met een klik op OK opent de wizzard Functie invoegen, in de afbeelding al van alle argumenten en voorzien. We leggen uit hoe dat in z’n werk gaat.


Het venster functieargumenten met daarin de argumenten van de functie VERT.ZOEKEN().
Merk op dat het resultaat van de formule direct wordt weergegeven.

Als voorbeeld voor het toekennen van functieargumenten hebben we uit het onderstaande overzicht de functie VERT.ZOEKEN in B12 genomen. Als eerste argument dienen we de Zoekwaarde op te geven. Klik op het pijltje achter het tekstveld Zoekwaarde, een klein schermpje is het resultaat. Klik nu op de cel waarin de zoekwaarde staat (B11). Klik terug op het pijltje om het hele venster weer te tonen. Achter het tekstvak zie je meteen de waarde van Zoekwaarde. Doe hetzelfde voor de Tabelmatrix (het bereik) waarbinnen je wilt zoeken, met dit verschil dat je nu met de muis over het bereik moet “vegen”. Ook nu zie je achter het tekstvak (een deel) van de waarden van het te doorzoeken bereik. Kolomindex_getal zullen we even handmatig moeten opgeven. Met Kolomindex_getal geef je op in welke kolom er gezocht moet worden. In ons geval is dat kolom 2 aangezien de Zoekwaarde zich in kolom 1 van de Tabelmatrix bevindt. Als laatste argument kunnen we Benaderen opgeven. Kunnen, want dit argument is geen niet verplicht. Kortom, alle vetgedrukte argumenten zijn verplicht, de niet vetgedrukte niet. Toch hebben we hier de waarde ONWAAR opgegeven omdat deze benadering uitsluitend een exacte overeenkomst teruggeeft. Als Benaderen WAAR is of wordt weggelaten, dan wordt de exacte overeenkomst of een benadering van de opgegeven waarde teruggegeven. Wordt er geen exacte overeenkomst gevonden, dan wordt de volgende grootste waarde die kleiner is dan de zoekwaarde geretourneerd.

Als benaderen WAAR is, moeten de waarden in de eerste kolom van tabelmatrix in oplopende volgorde zijn gesorteerd. Als dit niet het geval is, retourneert VERT.ZOEKEN mogelijk niet de juiste waarde. Selecteer de tabel, klik in het menu Data op Sorteren en klik vervolgens op Oplopend om de waarden in oplopende volgorde te sorteren.


(Voor ONWAAR kun je ook een 0 opgeven, voor WAAR een 1)

Tot zover het gebruik van de wizzard Functie invoegen en de bespreking van de functie VERT.ZOEKEN().

Een aantal andere functies.



We beginnen met de uitleg over de functies die in het voorbeeld gebruikt zijn. In C9 staat de functie =GEMIDDELDE(C2:C8). Wat onmiddellijk opvalt is dat er in het opgegeven bereik zowel een lege cel als een cel met tekst voorkomt. De functie GEMIDDELDE() trekt zich niets van die tekst aan en geeft de juiste waarde terug. In G4 is dat, na het invoeren van de formule =(C2+C3+C4+C5+C6)/5, ook het geval. Met de formule bereken je de som van de getallen en deelt die dan door het aantal cellen waarin die getallen staan. Zo kom je ook aan het gemiddelde. Maar in I5, waarin de formule =(C2+C3+C4+C5+C6+C7+C8)/7 staat gaat het mis. Ook in die formule is de lege cel en de cel met tekst meegenomen met als resultaat de foutmelding WAARDE#. Waarmee gezegd is dat een functie inderdaad een heel groot voordeel heeft op een formule.

In E9 hebben we het gemiddelde uit C9 naar beneden afgerond met: =AFRONDEN.BENEDEN(C9;0,5). De functie AFRONDEN.BENEDEN(getal;significantie) heeft twee argumenten, getal is de waarde die u wilt afronden (in het voorbeeld dus C9), significantie is het veelvoud waarop u wilt afronden. We hebben ervoor gekozen af te ronden op tienden door als significantie 0,5 in te voeren. Voer je een 1 in dan wordt C9 afgerond op een heel getal. In het voorbeeld zal dat dan 11 zijn.

Wat zullen we moeilijk doen? Beide functies kunnen we ook gebruiken in dezelfde cel door de functies te nesten. Typen we in C9 =AFRONDEN.BENEDEN(GEMIDDELDE(C2:C6);0,5) dan hebben we het eindresultaat in een keer te pakken (zie F9). Het is wel zaak om de functies in de juiste volgorde te nesten. Je zult jezelf eerst af moeten vragen wat het doel is dat je met het nesten van functies wilt bereiken. In ons voorbeeld willen we het resultaat van de functie GEMIDDELDE() afronden naar beneden. Vandaar dat de eerst gebruikte functie AFRONDEN.BENEDEN() is, het eerste argument van deze functie is ‘getal’. Voor het bepalen van ‘getal’ gebruiken we de functie GEMIDDELDE(),de argumenten van deze functie zijn ‘getal1;[getal2]’ ofwel het bereik waarin de getallen staan. Dus de functie GEMIDDELDE() bepaalt voor de functie AFRONDEN.BENEDEN() het eerste argument.



Wat rest is het tweede argument voor het naar beneden afronden op te geven, significantie. (Volgens van Dale: statistisch verantwoorde conclusies toelatend) Let op hoe de haakjes in de formule geplaatst zijn, met het plaatsen van de haakjes (op de goede, of juist de verkeerde) plaats valt of staat het resultaat van de formule.

Nog een voorbeeld maar nu met de functies HORIZ.ZOEKEN() en AFRONDEN.BOVEN() met bovendien een vermenigvuldiging.



In dit voorbeeld hebben we de functie (horizontaal) HORIZ.ZOEKEN() gebruikt. Je kunt deze functie gebruiken wanneer de vergelijkingswaarden zich in de bovenste rij van een gegevenstabel bevinden en je een bepaald aantal rijen verder naar beneden wilt zoeken. Met rijen worden hier de rijen in de tabel bedoeld, dus niet de rijnummering van het werkblad.
De argumenten van HORIZ.ZOEKEN zijn:
(zoekwaarde;tabelmatrix;rij-index_getal;benaderen). Dezelfde argumenten dus als bij horizontaal zoeken, alleen geef je nu geen kolomletter maar een rijnummer op als index_getal. We geven de formules zoals gebruikt in het voorbeeld.
E15: =HORIZ.ZOEKEN(B15;D6:G11;B17;ONWAAR) (merk op dat er voor index_getal een celverwijzing is gebruikt)
F15: =E15*B3
G15: =AFRONDEN.BOVEN(F15;1)
I15: =AFRONDEN.BOVEN(HORIZ.ZOEKEN(B15;D6:G11;B17)*B3;1)



Ook voor deze functie geldt dat ONWAAR uitsluitend een exacte overeenkomst teruggeeft. Als Benaderen WAAR is of wordt weggelaten, dan wordt de exacte overeenkomst of een benadering van de opgegeven waarde teruggegeven. Als benaderen WAAR is, moeten de waarden in de eerste rij van tabelmatrix in oplopende volgorde zijn gesorteerd.

Tot slot.



Tot slot een aantal logische functies al of niet genest. De formules die in kolom G zijn afgebeeld staan in kolom C. Maak dit tabelletje na en speel eens met de waarden die in de kolommen C en D staan.

[CorVerm]

Database in Excel.

Zoals alle vorige afleveringen is ook deze aflevering weer gemaakt met Excel 2003. Wil je weten hoe het maken van een database in Excel 2007 in z’n werk gaat stuur me dan een PB via het forum. Zijn er meerdere geïnteresseerden dan plaats ik hier een handleiding en anders krijg je persoonlijk bericht.

In deze aflevering houden we ons bezig met de functie Database, voor zover je van een functie kunt spreken. De officiële benaming voor dit fenomeen heet in Excel 2003 Lijst, hoe dan ook, in een Lijst kun je werken met databasefuncties. Het voordeel van het werken met een database is dat je er snel enkele standaard berekeningen op los kunt laten terwijl het overzicht behouden blijft. Er zijn twee mogelijkheden om een database te maken, of van een bestaande tabel of van de grond af aan beginnen.

We gaan van start met het opbouwen van een database. Een kenmerk van een database is dat het kolomlabels bevat, vandaar dat we die eerst gaan typen. Denk van tevoren goed na wat je met de database wilt bereiken, dat is erg belangrijk voor een goed resultaat.



Klaar met het maken van de kolomlabels? Dan gaan we de functie database er op loslaten.
Selecteer een van de kolomlabels en ga naar het menu Data > Lijst > Lijst maken. Vergeet niet om een vinkje te zetten bij: De Lijst bevat kopteksten.



Klik op OK en de database is een feit. Meteen komt er onder de rij met kolomkoppen een lege rij in de opmaak van de database. Ook de werkbalk Lijst verschijnt in het werkblad die de nodige opties bevat om de database te kunnen bewerken. De( nog lege) cel in kolom A bevat een sterretje, als je daar met de cursor op gaat staan veranderd de cursor in een pijltje en met een linker muisklik worden de records geselecteerd . Records zijn de gegevens die in de databaserij staan. Wanneer je in de lege rij gegevens hebt getypt vult de database zich aan door onmiddellijk weer een lege rij in te voegen.


Werkbalk Lijst


Het menu Lijst van de werkbalk Lijst.

Het voornaamste onderdeel uit de werkbalk Lijst is, jazeker, Lijst. De opties van het menu Lijst zetten we even op een rij.
Invoegen > rij of kolom
Verwijderen > rij of kolom
Sorteren > op drie criteria
Formulier > gegevens invoegen, verwijderen, en zoeken.
Formaat van de lijst wijzigen > wijzigt het gegevensbereik.
Converteren naar bereik > maak van de database een gewone Lijst.

Nu zijn er twee zaken die opvallen in een database, het autofilter en de Totaalrij. Beiden zijn van groot nut om uit de database de juiste gegevens te halen. Of eigenlijk zijn er drie zaken die opvallen. Elke formule die je in een database zet wordt automatisch gekopieerd naar de volgende rij. In het voorbeeld staat in G2 de formule: =ALS(OF(D2>0;E2>0;F2>0);GEMIDDELDE(D2:F2);"") die in iedere rij (automatisch) is mee gekopieerd.



De Totaalrij invoegen.

Klik in de werkbalk op de betreffende knop. Een andere mogelijkheid is om via het menu Data > Lijst voor Totaalrij kiezen. Bij het aanklikken van een cel in de Totaalrij verschijnt er een pijltje in de cel waaronder zich een keuzemenu bevindt. In de onderstaande afbeelding kun je zien welke keuzes gemaakt zijn en zullen we die nader toelichten.



Te beginnen met kolom C. Aantal geeft het aantal records weer en dat kan handig zijn maar liever zien we het werkelijke aantal (unieke) klanten. Daar is een oplossing voor maar dat leggen we straks uit. Van kolom D hebben we de Som genomen, dus zijn de waarden in kolom D bij elkaar opgeteld. Met Max halen we uit kolom E de hoogste waarde en met Min de laagste waarde uit kolom F. In kolom G staat de hierboven genoemde formule en Excel snapt meteen dat we de som van deze getallen bij elkaar op willen tellen en doet dat dan ook geheel uit zichzelf.

Er valt toch nog het één en ander uit te leggen, we beginnen maar weer bij kolom C.



In plaats van het aantal records willen we zien hoeveel klanten we hebben, en dat gaat dus niet met Aantal. We moeten een truckje toepassen om wel te weten hoeveel unieke klanten we hebben. Klik twee cellen onder de Totaalrij in de kolom waaruit we het aantal unieke records willen halen. In die cel zetten we de volgende formule: {=SOM(ALS(INTERVAL(VERGELIJKEN(B2:B8;B2:B8;0);VERGELIJKEN(B2:B8;B2:B8;0))>0;1))}. De accolades mag je niet meetypen want die komen vanzelf rond de formule te staan omdat het hier een Matrixformule betreft en die bevestig je door op CTRL+Shift+Enter te drukken. De hier gebruikte formule haalt zowel tekst als getallen op. Inderdaad hebben we het hier over tekst, want getallen met een – (koppelteken) ertussen worden als tekst gezien door Excel. Een nieuwe rij toegevoegd aan de database ? Dan past de formule zich aan en de rij waarin de formule staat schuift mee naar beneden door.


Dit venster vertelt je dat cellen met gegevens die onder de
database staan naar beneden worden verplaatst.

De functie INTERVAL() geeft een getal dat gelijk is aan het aantal keer dat een waarde voorkomt, de eerstvolgende gelijke waarde geeft de functie een nul terug. De positie van de gevonden waarde wordt door de functie VERGELIJKEN() opgehaald en in dit verband fungeert deze functie als argument voor de functie INTERVAL(). Dit even terzijde, op zich heeft dit verhaal niets met de database op zich te maken. Helaas is het in Excel 2003 niet mogelijk om andere als de “voorgekookte” opties in de Totaalrij te gebruiken. In Excel 2007 kan dat wel.

We hebben nog meer uit te leggen en dat doen we dus ook.
De opties die je in de Totaalrij onder de pijltjes vindt zijn op zich geen functies. Misschien zijn die het best te omschrijven als aanwijzing over wat Excel moet doen met de gegevens uit de betreffende kolom. Wat is het geval? Excel gebruikt voor de diverse opties de functie SUBTOTAAL().
Voor kolom D hebben we Som uitgekozen maar als je in de formulebalk kijkt zie je de formule =SUBTOTAAL(109;D2:D8)staan. In het onderstaande lijstje kun je zien dat Functie_getal 109 de functie SOM() is.



Meteen wordt duidelijk waarom Excel ervoor kiest om voor een Functie _getal uit de “100 serie” te gaan. Verborgen waarden worden niet meegenomen als er gefilterd wordt op, bijvoorbeeld, een bepaalde klant.



Uit de afbeelding blijkt dat we hebben gefilterd op Klant (vandaar de blauwe kleur van het pijltje) en hebben gekozen voor klant Schaik. Uiteraard doet in kolom C de opdracht Totaal nu wel goed z’n werk, immers de verborgen waarden worden niet meegeteld.

Formulier.

Bij een database hoort ook een formulier. Zoals je hierboven in de afbeelding van de werkbalk Lijst kunt zien is de optie Formulier aanwezig in het menu Lijst van de werkbalk Lijst. Het nut van het formulier is dat je, in een grote database, gemakkelijk wijzigingen aan kunt aanbrengen, gegevens op kunt zoeken en wijzigen.



Klik jein de werkbalk op Formulier dan verschijnt het venster Formulier met het eerste record uit de database in beeld. Door op de knop Vorige of Volgende zoeken te klikken “loop” je door de database om de aanwezige records te bekijken. Wil je de klant die in “beeld” is verwijderen? Klik dan op de daarvoor bestemde knop en je bent ervan verlost. De database past zichzelf aan door de gehele databaserij te verwijderen. Een nieuwe klant toevoegen doe je door op de knop Nieuw te klikken, de velden zijn dan leeg en kun je vullen met gegevens. Met een klik op de knop Sluiten is de nieuwe klant aan de database toegevoegd. Zoeken binnen de database middels het formulier behoort ook tot de mogelijkheden. Klik op de knop Criteria en wederom zijn alle velden leeg. Nu ligt het er aan waarop je wilt zoeken, op klantnummer? Typ dat dan in in het veld Klantnr. Zoeken op klantnaam? Nou ja, je snapt het wel, hoeven we verder niet uit te leggen.

Een grafiek maken.

Een grafiek maken van de database is ook een peulenschil. Eveneens in de werkbalk Lijst klik je op de knop Wizzard Grafieken. Excel komt met een voorstel in welke vorm je de grafiek wilt zien, kies een vorm met de, voor jou beste weergave en klik op volgende. Zo doorwandel je de hele wizzard om tot een mooie maar vooral ook overzichtelijke grafiek te komen. Let er wel op dat, voordat je definitief de opdracht tot het maken van de grafiek geeft, je aangeeft of de grafiek in het bestaande of in een nieuw werkblad geplaatst moet worden. Alleen al over de instellingen binnen een grafiek valt heel veel te zeggen maar dat gaat hier iets te ver. Experimenteer met de instellingen, je komt daar best wel uit.