Rekenen met voorwaarden


Rekenen met voorwaarden: optellen met SOM.ALS()

Of u nu kostenplaatsen van een professionele boekhouding wilt interpreteren, offertes op grond van een criterium wilt optellen, of de voorraad van een artikel op verschillende locaties wilt totaliseren, u kunt zich veel sorteerwerk besparen als u de optelling met de functie SOM.ALS() maakt met het gemeenschappelijke criterium. In combinatie met de stringfunctie LINKS() kunt u uit een lang kostenplaatsnummer het groepsnummer en een subgroepsnummer destilleren. In deze tip demonstreren we hoe u een hoeveelheid gegevens verwerkt met gebruik van de genoemde functies.

1 Kostenplaatsen evalueren
Aan de hand van een tabel met journaalbedragen en kostenplaatsen laten we zien hoe u de functie SOM.ALS() kunt toepassen. In dit voorbeeld beperken we ons tot een kleine hoeveelheid gegevens: méér draagt niet bij tot een beter inzicht in de werkwijze. We gaan uit van een eenvoudig journaal. Behalve de datum registreren we het kasstuknummer, het kostenplaatsnummer en het bedrag. We maken ook geen onderscheid tussen debet en credit omdat ook dat niet bijdraagt tot een beter inzicht in de besproken techniek. Voor de evaluatie gebruiken we alleen de kolommen Kostenplaats en Bedrag.

We gebruiken de functie SOM.ALS() om de omzet op bijvoorbeeld de kostenplaats 3310 te totaliseren. Bij een complexere analysevariant verzamelen we op basis van het eerste cijfer van de kostenplaats alle bedragen voor een gehele kostenklasse. In andere berekeningen doen we hetzelfde voor decimale detailleringen (kostengroep en subgroep).

Excel tips trucs | Financiele Professionals

Afb. 1: Brongegevens voor de functie SOM.ALS()

We laten het aan uw eigen inventiviteit over voor welke taak u de functie SOM.ALS() in uw omstandigheden het beste kunt gebruiken. De werkwijze is steeds gelijk: wat hieronder volgt is probleemloos bruikbaar voor allerlei taken.

2 Eenvoudige evaluatie
De functie SOM.ALS() behoort tot de functiecategorie Wiskunde en trigonometrie. Deze functie telt alleen getallen op die voldoen aan het vermelde criterium; de syntaxis luidt:

=SOM.ALS(bereik;criterium;optelbereik)

De functie SOM.ALS()
Met SOM.ALS() kunt u uit een lijst waarden optellen die voldoen aan een als argument vermeld kenmerk. De vraag ‘Hoe groot is de som van alle bedragen op de kostenplaats 3310 in de tabel op afbeelding 1.’ kan dus worden beantwoord met de functie SOM.ALS().

• Het argument bereik vindt u in het voorbeeld terug in kolom C met de kostenplaatsnummers.
• In het voorbeeld vormt een kostenplaatsnummer het optelcriterium. Dit nummer kunt u in een cel schrijven waarnaar u verwijst in het tweede argument van de functie.
• Het optelbereik met de geboekte bedragen vindt u in het voorbeeld terug in kolom D.

Excel doorzoekt kolom C op het vermelde kostenplaatsnummer. Bij aantreffen van dat nummer telt Excel het ernaast staande bedrag uit kolom D op.

TIP
In de argumenten van de functie SOM.ALS() hoeft u zich niet te beperken tot één werkmap. De benodigde gegevens kunt u met een verwijzing ook aan een andere Excel-werkmap ontlenen.


In het afgebeelde voorbeeld staat het kostenplaatsnummer waarvoor u de bedragen wilt sommeren in cel B4 van het werkblad Eenvoudig. De formule luidt:

=SOM.ALS(Bron!C2:C26;Eenvoudig!$B$4;Bron!D2:D26)

U kunt de formule samenstellen op het palet van de module Functie plakken:

Excel tips trucs | Financiele Professionals

Afb. 2: Wizard Functie plakken voor SOM.ALS()

De volgende afbeelding toont het resultaat:

Excel tips trucs | Financiele Professionals

Afb. 3: Resultaat met de voorbeeldgegevens

In het criteriumargument van de functie SOM.ALS() kunt u logische operatoren toepassen. Als u bijvoorbeeld de totale omzet voor de kostenplaatsen 0000 tot en met 5999 wilt weten, gebruikt u de formule:
=SOM.ALS(C2:C26;"<6000";D2:D26)

PRAKTIJKTIP
Als u verschillende criteria tegelijk wilt evalueren, hebt u niet genoeg aan de functie SOM.ALS(). Wellicht kunt u dan de gewenste taak uitvoeren met de wizard Voorwaardelijke som. Deze invoegtoepassing (add-in) kunt u laden via het dialoogvenster van de opdracht Extra -> Invoegtoepassingen. Activeer het selectievakje voor de genoemde wizard. Daarna kunt u wizard starten via de opdracht Extra -> Wizard -> Voorwaardelijke som.


3 Complexe evaluatie
De hierboven evaluatie is erg eenvoudig. Voor een complexere evaluatie hebt u meer functies nodig dan alleen SOM.ALS().

Neem aan dat u voor een complexe evaluatie gebruik wilt maken van criteria uit drie kostencategorieën:

• Klasse;
• Groep;
• Subgroep.

In het voorbeeld hebben we viercijferige kostenplaatsnummers. Hierbij vormt het eerste cijfer de kostenklasse; de eerste twee cijfers de kostengroep en de eerste drie cijfers de kostensubgroep.

Klasse ontlenen aan het kostenplaatsnummer
De begincijfers van een getal kunt u extraheren met de functie LINKS().

De functie LINKS()
De functie LINKS() kunt u instellen met de wizard Functie plakken:

Excel tips trucs | Financiele Professionals
Afb. 4: Functiepalet voor de functie LINKS()

De syntaxis luidt:

LINKS(tekst;aantal_tekens)

• Het eerste argument vertegenwoordigt de tekenreeks (string) die moet worden verwerkt of de verwijzing naar de cel waar de tekenreeks zich bevindt.
• Via het tweede argument geeft u op hoeveel tekens u van de bronstring wilt gebruiken. Als u geen waarde opgeeft voor het argument aantal_tekens, levert de functie LINKS() het eerste (meest linkse) teken van het argument tekst.

Evaluatie
Als u de omzetevaluatie wilt uitbreiden van één kostenplaats naar categorieën kostenplaatsen, moet u aan de bestaande tabel kolommen toevoegen waarin de Klasse, Groep en Subgroep van de kostenplaats worden berekend.
Ga als volgt te werk:

Stap voor stap
1 Klik op de kop van de kolom waar een nieuwe kolom vóór wilt maken. Druk op de toetsencombinatie Ctrl–+.
2 Herhaal deze bewerking voor de andere twee extra kolommen.
3 Zet in de uitgebreide voorbeeldtabel de celwijzer in D2 en schrijf daar de formule die de klasse berekent uit de kostenplaats:
=LINKS(C2)

4 Schrijf in E2 de formule die de groep berekent uit de kostenplaats:
=LINKS(C2,2)

5 Schrijf in F2 de formule die de subgroep berekent uit de kostenplaats:
=LINKS(C2,3)

6 Voer de drie formules door tot het einde van de tabel: selecteer de cellen D2:F2 en dubbelklik op de doorvoerhendel van de selectie (rechtsonder aan de markering).

De afbeelding toont het resultaat:

Excel tips trucs | Financiele Professionals
Afb. 5: Uitgebreide journaaltabel

Let op
De tabel op het werkblad Bron(2) is niet helemaal een kopie van die op het werkblad Bron; bij wijze van voorbeeld zijn enkele records gewijzigd.

Met de uitgebreide tabel kunt u een complexere evaluatie uitvoeren. De formules verschillen maar weinig van die op het werkblad Bron – de verwijzing naar het criterium is een cel in een andere kolom.

Cel Formule
D4 =SOM.ALS(’Bron(2)’!$C$2:$C$26;Complex!B4; ’Bron(2)’!$G$2:$G$26)
D5 =SOM.ALS(’Bron(2)’!$C$2:$C$26;Complex!B5; ’Bron(2)’!$G$2:$G$26)
D6 =SOM.ALS(’Bron(2)’!$C$2:$C$26;Complex!B6; ’Bron(2)’!$G$2:$G$26)
D7 =SOM.ALS(’Bron(2)’!$C$2:$C$26;Complex!B7; ’Bron(2)’!$G$2:$G$26)

Tab. Formules met SOM.ALS()
De volgende afbeelding toont het resultaat:

Excel tips trucs | Financiele Professionals
Afb. 6: Omzettotalen voor brede en smalle categorieën kostenplaatsen

PRAKTIJKTIP
Analoog aan de functie LINKS() berekent de functie RECHTS() een of meer van de laatste tekens van een tekenreeks. Verder is er nog de tekstfunctie DEEL() die uit een bronreeks te beginnen op de vermelde positie een deelreeks van een opgegeven lengte ophaalt. Er zijn dus drie argumenten; de syntaxis luidt:


=DEEL(tekst;eerste_teken;aantal_tekens)

4 Boekingen tellen
Analoog aan het hierboven besproken voorwaardelijke optellen kunt u met Excel ook voorwaardelijk tellen met de functie AANTAL.ALS().

Excel tips trucs | Financiele Professionals
Afb. 7: Functiepalet van AANTAL.ALS()

De functie AANTAL.ALS() kunt u altijd gebruiken als u wilt weten hoeveel boekingen in een lijst voldoen aan een opgegeven voorwaarde. In ons voorbeeld luidt de vraag hoeveel journaalposten er zijn binnen de vermelde kostencategorie. De syntaxis van de functie luidt:

=AANTAL.ALS(bereik;criterium)
De argumenten bereik en criterium vervullen dezelfde rol als bij SOM.ALS(). Het derde argument van SOM.ALS() is hier overbodig omdat er geen bewerkingen hoeven plaats te vinden met waarden in de boekingen die aan het criterium voldoen – deze boekingen hoeven slechts te worden geteld.

De formules voor de complexe evaluatievariant zijn:

Cel Formule
B5 =LINKS(B4)
B6 =LINKS(B4;2)
B7 =LINKS(B4;3)
E4 =AANTAL.ALS(’Bron(2)’!$C$2:$C$26;Complex!B4)
E5 =AANTAL.ALS(’Bron(2)’!$C$2:$C$26;Complex!B5)
E6 =AANTAL.ALS(’Bron(2)’!$C$2:$C$26;Complex!B6)
E7 =AANTAL.ALS(’Bron(2)’!$C$2:$C$26;Complex!B7)
Tab. Formules met AANTAL.ALS()

PRAKTIJKTIP
De lege cellen in een bereik kunt u tellen met een functie die de syntaxis AANTAL.LEGE.CELLEN(bereik) heeft. Deze functie beschouwt een cel óók leeg als er een formule staat die een lege tekenreeks ("") oplevert; cellen met nulwaarden worden niet meegeteld.

Vind je vaste baan

Al 28 jaar is WR het Werving en Selectie bureau voor vaste banen.