Excel top5 formules: #2 ALS functie

Wat ALS…?

Zemanta Related Posts ThumbnailDeze keer in de reeks ‘Excel top5 formules’: de ALS functie. Een paar weken geleden heb ik jullie het fenomeen Verticaal zoeken uitgelegd. Hierbij ben ik begonnen met het maken van een Excelrapportage. Op deze rapportage gaan we nu verder borduren met behulp van de handige formules =ALS.FOUT en =ALS. Deze formules zal ik uitleggen hoe ze precies werken en daarbij een voorbeeld geven hoe je ze kunt toepassen om overzichtelijke en dynamische rapportages te maken.

ALS.FOUT

In de vorige post over Verticaal Zoeken ben ik geëindigd met onderstaand overzicht tot en met de kosten. Ik heb hier 2 berekende variabelen aan toegevoegd, die interessant zijn om als KPI te meten: het conversiepercentage (# transacties / # bezoeken) en de kosten/opbrengst verhouding.

Als je deze variabelen berekent met simpele excelformules (=cel-x/cel-y), dan krijg je in alle cellen waar de formule geen goed antwoord op krijgt een fout-waarde, zoals #N/B (niet bekend) of #DEEL/0 (proberen te delen door 0). Dit staat niet zo netjes:

overzicht zonder als

Dit is gemakkelijk op te lossen door de formule =ALS.FOUT te gebruiken. Deze formule wordt door Office help omschreven als: “ALS.FOUT geeft een opgegeven waarde als resultaat, indien een formule een foutwaarde retourneert. Indien geen fout wordt geretourneerd, wordt het resultaat van de formule weergegeven.”

Dit ziet er in formuletaal zo uit: ALS.FOUT(waarde;waarde_indien_fout)

  • De waarde is het argument dat op fouten gecontroleerd wordt. Dit kan een enkele cel zijn, maar ook – zoals in mijn voorbeeld – het resultaat van een andere formule;
  • De waarde_indien_fout is de waarde die moet worden getoond als de formule een foutwaarde als resultaat geeft, zoals #N/B, #WAARDE!, #VERW!, #DEEL/0!, #GETAL!, #NAAM? of #LEEG!. (Voor ieder van deze foutmeldingen verwijs ik jullie graag naar de Office Help of laat een berichtje achter als je er niet uitkomt.). De waarde_indien_fout kan een getal zijn, maar ook een tekst.

Om bovenstaande rapportage overzichtelijker te maken wil ik alle foutwaardes vervangen door niets. Dit vul ik in als =ALS.FOUT(M12/M7;””). M12/M7 was mijn oorspronkelijke formule (# transacties gedeeld door het # bezoeken). De “;” scheidt de formule: alles voor de ; is de waarde. Hetgeen achter de ; de waarde_indien_fout. Om niets terug te geven in de cel type je 2 dubbele aanhalingstekens.

Omdat we nog geen gegevens hebben van de maanden april tot en met december zijn de cellen nu leeg. Er staat wel een formule in, dus zodra de gegevens aangevuld worden waarop de formule gebaseerd is, zullen de berekende variabelen ook automatisch gevuld worden.

overzicht met als.fout

ALS

Dat was even een opwarmertje :-). Nu op naar het echte werk! Ik laat jullie nu een manier (één van de vele) zien waarbij de ALS functie uitkomst biedt. Natuurlijk ben je naast de cijfers per maand ook benieuwd naar de cumulatieve waarden (over de maanden heen). Dit kun je relatief simpel doen door een totaalkolom te gebruiken in de draaitabel of een =SOM functie te gebruiken en het bereik (J:U) te selecteren.

Maar dan kun je niet meer makkelijk kijken wat de cumulatieve waarde was tot en met bijvoorbeeld februari. Daarnaast kunnen je berekende variabelen vertekend worden doordat sommige waarden al wel zijn ingevuld voor de maand, maar andere nog niet. Zo zijn bijvoorbeeld de kosten van affiliates vaak pas later bekend (deze hebben vaak een cookietijd van 30 dagen). Je wilt dus graag in eigen hand houden tot en met welke maand je de waarden cumuleert.

Om dit te bereiken, pakken we hiervoor even een klein zijstraatje. Om de cumulatieve waarden namelijk dynamisch te maken gaan we gebruik maken van een Lijst. Een lijst maak je door in Excel ergens de waarden jan, feb, mrt etc te typen en dit bereik te selecteren.Type nu linksboven naast de formulebalk het woord ‘maand’:

lijst maken

In de rapportage zorg je er nu voor dat de kolomnamen overeenkomen met de maanden van je lijst. 01 wordt dus jan, 02 wordt feb etcetera. Kies een cel in je rapportage uit waar je een dropdown wilt maken (in mijn voorbeeld rechts bovenin) en ga nu in Excel naar ‘gegevens’ > ‘gegevensvalidatie’. Kies hier bij Toestaan voor ‘Lijst’ en geef bij Bron =maand in en klik op OK. Et voilá: een mooie dropdown met alle waarden uit je lijst.lijst maken voor maand Wat we nu willen doen is een kolom toevoegen aan de rapportage met de cumulatieve waarden. Deze cumulatieve waarde moet afhankelijk zijn van de gekozen maand uit de lijst. Kies je hier uit de dropdown ‘feb’, dan moeten enkel de kolommen J en K opgeteld worden. Wordt ‘mrt’ geselecteerd, dan moeten de kolommen J t/m L opgeteld worden etcetera. Dit kun je dus doen met de ALS-functie.

De functie ALS geeft een bepaalde waarde als resultaat als de opgegeven voorwaarde WAAR is en een andere waarde als deze ONWAAR is. Oftewel:

ALS(logische_test, [waarde_als_waar], [waarde_als_onwaar])

  • De logische-test is een vergelijking. Je vergelijkt een waarde uit een cel of het resultaat uit een formule met een andere cel. De uitkomst van deze vergelijking kan WAAR of ONWAAR zijn. In mijn voorbeeld vergelijk ik een waarde uit de tabel (jan – dec) met de waarde uit de lijst;
  • Bij de waarde-als-waar vul je in wat de functie met doen als de waarden overeenkomen. Als ik in de lijst ‘feb’ heb geselecteerd en het kolomlabel feb aangeeft, dan moeten de kolommen J:K gesommeerd worden;
  • De waarde-als-onwaar wordt getoond als er geen match is met de waarde uit de lijst en uit kolomlabels. In de formule geef ik deze waarde als niets in: “”. Als er geen match is, dan wordt er niets gesommeerd.

Om deze formule in kolom V te zetten, maak je 12 ALS-formules in 1! De formule hieronder lees je als volgt: ALS lijst=jan dan SOM(J), anders ALS lijst=feb dan SOM(J:K) anders ALS lijst=mrt dan SOM(J:L) anders etcetera:cumulatief mbv functie alsIn dit voorbeeld heb ik nu ‘feb’ geselecteerd. In de cumulatieve kolom zie je nu dat de waarden uit kolom J en K zijn gesommeerd. Als je nu ‘mrt’ selecteert, sommeert Excel de waarden uit kolom J tot en met L.

Visualisaties

Om je rapportage te verduidelijken zijn visualisaties key! Lang niet iedereen kan wijs worden uit een Excelsheet met honderden cijfers. Want wat is nu belangrijk? Het beste is dus om een aantal belangrijke statistieken in grafieken te zetten. Om niet elke maand nieuwe grafieken te maken of het bereik aan te hoeven passen, raad ik aan het totale bereik te selecteren (jan t/m dec). Alleen zijn nu je grafieken niet meer duidelijk:

grafieken alle maanden

Er is nog geen data van april tot en met december, dus deze assen mogen verborgen worden. Bij de grafieken met een lijngrafiek lijkt het zelfs alsof alles ineenstort. Vanaf april staat alles op 0. Dit los je op door de kolommen die nog geen data bevatten te groeperen. Selecteer de kolommen M tot en met U en klik op groeperen.

groeperen

Je ziet nu dat er een – boven de kolom komt te staan. Als je hierop klikt worden deze kolommen ingeklapt. Nu zijn enkel de relevante kolommen zichtbaar en zijn automatisch je grafieken aangepast!

rapportage

P.S. heb je vragen over de ALS-functie in Excel of andere rapportage vragen, neem dan gerust contact met me op of reageer op deze post En…stay tuned voor nog meer handige excelformules!

Lees ook mijn andere blogposts

  • Excel top5 formules: #3 INDIRECT Als ALS en VERT.ZOEKEN niet toereikend zijn Nu hoor ik je denken: "is this even possible?" Ja, it is. Met Verticaal zoeken en de ALS functies kom je een eind in Excel, maar je kunt het […]
  • Browse door je A/B-test resultaten Don't judge the book by it's cover... Bij KRAS.nl zijn we nu ruim 2 jaar aan het A/B-testen. Eén van de belangrijkste dingen die ik geleerd heb bij het beoordelen van een A/B-test is […]
  • Campaign tagging, eventtracking en virtual pageviews Google Analytics meet niet alles! Tenminste niet automatisch. Met de standaard trackingcode van Google Analytics worden alle unieke URL's gemeten. Daarnaast wordt een aantal media […]
  • Starten met Google Analytics First things first... Zonder data kunnen we niet analyseren en rapporteren, dus laten we beginnen bij het begin: statistieken verzamelen. Het meest gebruikte webanalysepakket is Google […]

One thought on “Excel top5 formules: #2 ALS functie

    1. Hoi Gerard,
      Hier zijn niet specifieke rules voor. Met een lijngrafiek kun je vaak een betere vergelijking maken met bijvoorbeeld het vorige jaar – dan zie je de ontwikkeling beter dan bij een staafgrafiek. Staafgrafieken en samengestelde staafgrafieken zijn vaak alleen duidelijk als je niet te veel datapunten hebt. Deze zijn daarentegen wel wat sexier dan een simpel lijntje. Allemaal lijngrafieken wordt ook teveel een eenheidsworst en dan kijkt er ook niemand meer naar :-). Je moet het een beetje spannend houden, maar bovenal duidelijk.

  1. Hallo Annemarie,

    fijne site zeg, goede tips. Kan alleen niet vinden hoe ik met mijn Excel “uitdaging” om moet gaan, hopelijk kun je me op weg helpen. 😉

    Alvast bedankt,

    grt
    Sandy

    ===
    zit nu al een tijdje te puzzelen om 2 formules samen te voegen.
    Afzonderlijk werken ze van elkaar en doen ze wat ik wil.

    Formule 1:
    =ALS(VERT.ZOEKEN($A3;BESTELD;9;0)>0;”JA”)
    In plaats van te zien hoeveel items ik besteld heb, wil ik het woordje “ja” zien om aan te geven dat het besteld is. Punt.

    Formule 2:
    =ALS.FOUT(VERT.ZOEKEN($A15;BESTELD;9;0);””)
    Als Excel geen bestelling kan vinden, wordt er #N/B gegeven als ik niet deze formule gebruik. In plaats van deze #N/B wil ik gewoon een lege cel. Dat krijg ik met deze formule.

    Maar nu wil ik deze twee formules combineren.
    Ik wil namelijk alleen “JA” zien als het besteld is, en ik wil een lege cel als het niet besteld is.

    1. Hoi Sandy,

      Je bent er al bijna. Je kunt de formules als volgt combineren:
      =ALS.FOUT(ALS(VERT.ZOEKEN($A3;BESTELD;9;0)>0;”JA”;””);””)

      Hiermee zeg je dat als het bestelde > 0 is dat er dan een JA moet komen te staan. Als het bestelde <= 0 is, dan komt er niets te staan. Maar ook als de uitkomst uit het verticaal zoeken een foutwaarde (zoals #N/B) oplevert, dat er dan niets getoond wordt. Succes! Groetjes, Annemarie

  2. Beste Annemarie,

    Graag wil ik een ALS vergelijking gebruiken voor de volgende drie mogenlijkheden in de kolom: een datum, nee of nvt.
    Als er een datum staat in de kolom wil ik dat de ALS vergelijking “ja” als uitkomst heeft, als er nee of nvt staat wil ik dat er resp “nee” of “nvt” te staan komt.
    De laatste twee lukken al maar de eerste nog niet.
    Ongeacht welke datum er in de kolom staat, er moet altijd “ja” uit de vergelijking komen. Is dit mogelijk en zo ja hoe?

    Met vriendelijke groeten,
    Sanne Goedhart

    1. Sorry Sanne, voor de nogal late reactie. Mijn blog houd ik niet meer zo goed bij :-). Waarschijnlijk ben je er via een andere weg al wel achter je antwoord gekomen, maar in excel bestaat geen formule om eenvoudig te checken of er een datum in een cel staat. Als de opties in de kolom enkel zijn ‘datum, nee of nvt’ en je hebt ‘nee’ en ‘nvt’ kunnen definieren, dan is automatisch de rest ‘ja’: =als([cel]=”nee”;”nee”;als([cel]=”nvt”;”nvt”;”ja”)).

  3. Dag Annemarie.
    Heldere uitleg. Ik ben met een afstudeerscriptie bezig en wil graag een stapje verder gaan:
    Ik heb een tabel met gegevens waar ik een bellendiagram (bubbeldiagram) bij heb gemaakt.
    Maar er staan 70 rijen in. Het diagram wordt dus onoverzichtelijk, temeer daar de locatie van de bubbel maar ook de grootte van de bubbel belangrijk is.
    Ik wil per rij graag aangeven of ik (automatisch) de gewenste bubbel wil tonen. Dus per rij een J/N keuze toevoegen. Bij ‘J’ verschijnt dan automatisch een bubbel in het diagram. Kan dat worden gerealiseerd met ALS(cel=”J”; toon bubbel in grafiek; toon niets). ZO ja, hoe doe ik dat dan (Excel 2010)?
    Graag advies.

    1. Hallo Sander,
      Ik denk dat de makkelijkste oplossing is om een nieuwe (kopie) draaitabel te maken en dan een filter te zetten op de rij variabele, waarbij je enkel de rijen selecteert die je in een grafiek wilt tonen. Hier kun je dan een draaigrafiek van tonen. Een ALS functie bij visualisatie werkt niet (voor zover ik weet).

      Succes!
      Annemarie

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *