Excel tips & trucs | Finance Professionals

Autodoorvoeren

Velen maken in Excel graag gebruik van de handige functie AutoDoorvoeren. Van de bijna onuitputtelijke mogelijkheden van deze functie zijn echter maar weinigen op de hoogte. Daarom geven we hier een overzicht van mogelijke combinaties.

Buurcellen combineren
Door buurcellen te combineren ontstaan er interessante nieuwe mogelijkheden voor de functie AutoDoorvoeren.

Voorbeeld 1
Selecteer de inhoud van de cellen B5:C5 en sleep het gemarkeerde gebied omlaag met de greep rechtsonder aan de cel C5. Het resultaat is een tabel waarin de boekingen precies gelijk zijn aan de inhoud van het uitgangsbereik.

Voorbeeld 2
Als u naast een cel met een getal ook een lege cel markeert (E5:F5), wordt in de uitbreiding het getal automatisch opgehoogd; er ontstaat een volgnummerreeks. Bij deze bewerking is het niet nodig de Ctrl-toets ingedrukt te houden.

Voorbeeld 3 en 4
Deze voorbeelden demonstreren dat een reeks volgnummers ook kan ontstaan uit een combinatie van een tekstcel met een numerieke cel. Selecteer het bereik H5:I5 (c.q. K5:L5) en versleep de vulgreep. Er ontstaat een volgnummerreeks waarbij de tekst ernaast gehandhaafd blijft.

Voorbeeld 5
Als u uitgaat van een tabel waarin op twee opvolgende rijen diagonaal twee getallen staan (1 en 6) en u breidt de selectie N5:O5 uit, wordt de waarde in O6 vervangen door een lege cel, zoals in voorbeeld 2.

Voorbeeld 6
Anders is het resultaat als u het selectieblok vier cellen groot maakt (Q5:R6). Als u nu de vulgreep sleept, ontstaan er twee verticale reeksen waarin de getallen steeds gescheiden zijn door een lege cel.

Gelijke uitgangssituatie – Verschillende resultaten
In de voorbeelden 1 tot en met 4 ziet u in de cellen B5, D5, F5 en H5 waarden die er hetzelfde uitzien, maar na de bewerking met de vulgreep blijken de resultaten te verschillen.

In kolom B neemt de waarde van de eerste posities (door Excel opgevat als getal!) toe als u de vulgreep sleept. In kolom D blijven de boekingen gelijk als u tijdens het slepen Ctrl ingedrukt houdt.

In kolom F daarentegen blijven de boekingen gelijk als u tijdens het uitbreiden géén extra toets indrukt. Het verschil in resultaat in de kolommen B en D is een gevolg van een verschillende celopmaak van de broncellen.

In de cellen B5 en D5 hebben we de waarden geschreven met spaties (1 123 234), wat tot gevolg heeft dat Excel de celinhoud in hoofdzaak beschouwt als tekst. In de cellen F5 en H5 hebben we de waarden ingevoerd als getal (1123234) en ze vervolgens voorzien van het zelfgedefinieerde aangepaste type getalopmaak: # ### ###. Omdat we alle celinhouden gecentreerd hebben, lijkt de opmaak van alle boekingen op het eerste gezicht hetzelfde.

Met deze opmaak neemt de waarde van de laatste posities in kolom H toe als u bij het slepen Ctrl ingedrukt houdt.

Volgnummers tussen haakjes

Vaak is het wenselijk een reeks getallen tussen haakjes te plaatsen; bijvoorbeeld (1).

Als u in een cel de waarde (1) schrijft, geeft Excel dat standaard weer als -1. U kunt dit voorkomen door de invoer met een voorafgaande apostrof ’ expliciet tot tekst te verklaren. Dit heeft het nadeel dat u met de functie AutoDoorvoeren – zowel met als zonder Ctrl – steeds dezelfde waarde genereert.

U kunt het gewenste resultaat bereiken door uit te gaan van gewone getallen en die door een aangepaste (zelfgedefinieerde) opmaak het bedoelde uiterlijk te geven. In dit geval luidt de opmaakstring (#). Nu kunt u de gewenste startwaarde gewoon als getal invoeren en vervolgens in combinatie met Ctrl de vulgreep naar beneden slepen.

Alfabetische volgnummers
Als u letters wilt gebruiken om kolommen of rijen van een tabel aan te duiden, kunt u daar de functie AutoDoorvoeren niet zonder meer gebruiken.

Daarvoor is de volgende oplossing beschikbaar:
=TEKEN(RIJ(A1)+64)

Deze formule resulteert in de letter “A”. Door te slepen wordt de rest van het alfabet weergegeven. Het nadeel is echter dat u meestal niet meer weet hoe deze constructie eruitziet als u haar het hardst nodig hebt. Bovendien moet u voor kleine letters een andere formule gebruiken:
=TEKEN(RIJ(A1)+96

De kalenderweek berekenen

Hier volgt een uiterst korte, maar functionele Visual Basics-functie waarmee de kalenderweek van een willekeurige datum correct kan worden berekend. De functie wordt geprogrammeerd in de VBA-editor en kan het best in de persoonlijke werkmap (Persnlk.xls) worden opgeslagen.

Stap voor stap
1 Neem een willekeurige macro op (bijvoorbeeld een nieuwe map openen). Kies onder Extra -> Macro -> Nieuwe macro opnemen onder Macro opslaan in de ingang Persoonlijke Macrowerkmap.
2 Stop de opname na de eerste opgenomen opdracht en sluit Excel af. Beantwoord de vraag of de wijzigingen moeten worden opgeslagen, met Ja, aangezien de vraag betrekking heeft op deze persoonlijke werkmap.
3 Start Excel opnieuw en kies Venster -> Zichtbaar maken. Maak het bestand Persnlk.xls zichtbaar.
4 Activeer met Alt+F11 de macro-editor. Verwijder de opgenomen macro en voer in plaats daarvan de volgende functie in (zie afbeelding 1):

Function KWeek(d As Date)
Dim t As Long
t=DateSerial(Year(d + (8 – Weekday(d)) Mod 7 – 3), 1, 1)
KWeek=((d – t – 3 + (Weekday(t) + 1 ) Mod 7))\7 + 1
End Function

Nadat u weer naar het Excel-venster hebt teruggeschakeld, kunt u de werkmap Persnlk.xls verbergen en Excel beëindigen (vergeet niet de wijzigingen op te slaan!). Vanaf dit moment is de functie in alle werkmappen beschikbaar.

• Voer een datumwaarde in (bijvoorbeeld in cel A1):
A1: 1.1.1999
• Bij de functie moet ook de werkmap worden opgegeven waarin de functie is opgeslagen:
A2: =PERSNLK.XLS!KWeek(A1)
• Het resultaat: de kalenderweek van de datum in cel A1 wordt correct aangegeven als 53.

Deelberekeningen maken voor formules

Omvangrijke formules met meerdere in elkaar geschoven functies zijn lastig te bewerken. De foutcontrole geeft weliswaar een waarschuwing wanneer er een haakje, een leesteken of een aanhalingsteken ontbreekt, en biedt vaak ook suggesties voor de correctie, maar als de formule syntactisch correct, maar inhoudelijk fout is, verschijnt er in de cel niet meer dan een korte, kernachtige foutmelding zoals #GETAL!, #VERW! of #WAARDE!.

Als u gemakkelijker naar fouten wilt kunnen zoeken, zou u eigenlijk een bijzonder nuttige techniek moeten kennen, namelijk het maken van deelberekeningen voor een formule. Het is mogelijk elk afzonderlijk deel van een formule apart door te rekenen en zo de plaats van de fout langzaam steeds nader te bepalen. De techniek zal hier aan de hand van een voorbeeld worden gedemonstreerd. Voer in een leeg werkblad de volgende berekening in, die afhankelijk van de grootte van een bedrag een korting berekent (5% van de waarde indien deze groter is dan 50.000, anders 3%):

A1 Bedrag:
B1 50.000
A2 Korting:
B2 =ALS(B1>50000;B1>0,05;B100,03)

Voer vervolgens in cel B1 een tekst in, bijvoorbeeld geen bedrag. De formulecel B2 reageert daarop met de foutmelding #WAARDE!. Met behulp van deelberekeningen kunt u achterhalen aan welk deel van de formule deze fout te wijten is:

Stap voor stap
1 Selecteer cel B2. Selecteer vervolgens in de formulebalk het deel van de formule dat de voorwaarde voorstelt (B1>50000).
2 Druk op de functietoets F9.
3 De deelformule wordt berekend; het resultaat WAAR geeft aan dat aan de voorwaarde is voldaan. Druk op de Esc-toets.
4 Selecteer nu (opnieuw in de formulebalk) het tweede argument (B1*0,05) en druk weer op F9 (zie figuur 1). U hebt de fout nu gevonden, want deze berekening kan niet worden uitgevoerd.

Getallen bij invoer vermenigvuldigen en delen in Excel

De invoer van getallen in een werkblad kan soms veel werk opleveren, bijvoorbeeld als u meerdere kolommen met heel veel decimalen moet invoeren. Excel heeft echter een optie die de invoer vereenvoudigt:
Stap voor stap:
1 Selecteer Extra -> Opties.
2 Open het tabblad Bewerken en activeer de optie Vast aantal decimalen. De standaardwaarde van deze optie is 2.

Ieder getal dat u invoert wordt door het aantal decimalen gedeeld dat u in het dialoogvenster Opties hebt ingesteld. Het getal 2 wordt 0,02, 333 wordt 3,33 en 1234 verandert Excel in 12,34.

Een ander probleem bij de invoer van gegevens laat zich op deze manier oplossen: als u getallen moet invoeren die veel posities links van de komma innemen, kunt u de opmaak op dezelfde wijze als hierboven beschreven instellen. Om bijvoorbeeld alle gegevens automatisch met 100 te vermenigvuldigen selecteert u Extra -> Opties en opent u het tabblad Bewerken. Verander de waarde nu in -2

Negatieve decimalen vermenigvuldigen de invoer

Nu kunt u duizend- en miljoentallen met veel nullen sneller invoeren. De waarde -3 vermenigvuldigt de invoer met 1000, -6 verandert de gegevens in miljoenen.

Bekijk finance vacatures

Koppelingen in Excel opsporen.

Koppelingen in Excel opsporen en verwijderen is echt zo’n weetje. WR is hier goed in. Weetjes. Wij helpen je verder. Voor werving en selectie is WR het bureau voor specialistische functies. Koppelingen in Excel WR Werving. Wij zijn sterk in specialistische functies. WR ondersteunt daarom ook Excel.
Koppelingen maken het u gemakkelijk. Als u bijvoorbeeld bij het dagelijkse werk vaak gegevens nodig heeft uit andere Excel-werkmappen werkt dit gemakkelijk. (In de bronmap het bereik kopiëren en vervolgens op de doellocatie invoegen met Plakken speciaal -> Koppeling plakken.)

Soms bevat een werkmap koppelingen die verwijzen naar gegevens die u niet meer nodig hebt. Ze zijn dan hinderlijk. Dit is hinderlijk omdat Excel iedere keer bij het openen van het bestand blijft vragen of de koppelingen moeten worden bijgewerkt. In de onderstaande tips laten we zien hoe u (overbodige) koppelingen kunt vinden en er desgewenst van verlost kunt worden.

1 Formuleweergavemodus

Standaard toont Excel in de formulebalk de inhoud van een cel: een waarde of een formule, hetzij met functies en operatoren hetzij een verwijzing in de vorm van een adres of een bereiknaam. Een koppeling is een speciaal type verwijzing; de syntaxis luidt:
=[Map1.xls]Blad1!$A$1

Als de werkmap met de verwijzingsbron op het moment dat u de formulebalk raadpleegt niet geopend is, voegt Excel het pad naar de werkmaplocatie toe aan de verwijzing:

=’C:\Mijn documenten\[Map1.xls]Blad1′!$A$1

Een niet-geselecteerde cel met een koppeling toont alleen de waarde van de celinhoud (het resultaat van de koppeling). Normaal gesproken kunt u dus jammer genoeg niet zien hoe een waarde tot stand komt. Excel heeft echter een weergavemodus waarin de cellen niet de berekende waarden, maar de achterliggende formules tonen.

Ga als volgt te werk om formuleweergavemodus in te schakelen:

Stap voor stap

1 Selecteer de opdracht Extra -> Opties.
2 Open het tabblad Weergave en activeer in de categorie Vensteropties het selectievakje Formules.
3 Bevestig de nieuwe instelling met OK.
Excel toont van alle cellen met een formule niet de resulterende waarde maar de volledige formule. De meeste kolommen worden daardoor veel breder.

Op analoge wijze kunt u ook terugschakelen naar de normale weergave.

1 Maak koppelingen zichtbaar

TIP
Deze opdracht heeft alleen effect op het actieve werkblad. Je moet de procedure dus herhalen voor elk werkblad. Overal waarin je naar koppelingen wil zoeken.

2 Koppelingen documenteren

Met behulp van een macro kunt u een opsomming van de koppelingen documenteren op een nieuw werkblad. Ga als volgt te werk om zo’n macro te maken:

Stap voor stap

1 Druk op Alt+F11 om de Excel-ontwikkelomgeving te starten. Selecteer de opdracht Invoegen „Ã Module.
2 Selecteer de opdracht Invoegen „Ã Procedure, activeer het keuzerondje Sub, geef de macro de naam KoppelingenDocumenteren en klik op OK. Neem de volgende broncode op in de nieuwe macro:

Sub KoppelingenDocumenteren()
Dim bereik As Range
Dim cel As Range
Dim blad As Worksheet
Dim werkblad As Worksheet
Set werkblad = Sheets.Add
i = 1
For Each blad In ActiveWorkbook.Worksheets
blad.Activate
Set bereik = blad.Range(Cells(1, 1), Selection.SpecialCells(xlCellTypeLastCell))
For Each cel In bereik
If ((InStr(cel.Formula, “=”) > 0) And (InStr(cel.Formula, “\”) > 0)) Then
werkblad.Cells(i, 1) = cel.Address
werkblad.Cells(i, 2).Value = ” ” & cel.Formula
werkblad.Cells(i, 3) = cel.Value
i = i + 1
End If
Next
Next
End Sub

3 Maak via de werkset Besturingselementen een opdrachtknop en wijs daaraan via het snelmenu de macro toe.

De macro maakt een nieuw werkblad en noteert daar een overzicht van alle koppelingen (in de hele werkmap): celadres van de koppeling, verwijzing naar de koppelingsbron en de waarde van de koppeling.

3 Koppelingen verwijderen met een macro

De koppelingen in een werkmap kunt u wel bekijken in het dialoogvenster van de opdracht Bewerken -> Koppelingen, maar daar is geen mogelijkheid de koppelingen te verwijderen uit de werkmap. De hieronder afgedrukte macro vervangt alle koppelingen in een werkmap door hun waarden.
Macrocode
Neem de volgende macrocode (zoals in paragraaf 2 beschreven) op in de werkmap met koppelingen – of in het opstartbestand Persnl.xls als u dat altijd geopend houdt.

Sub KoppelingenVervangen()
Dim cel As Range
Dim werkblad As Worksheet
For Each werkblad In ActiveWorkbook.Worksheets
werkblad.Activate
For Each cel In werkblad.UsedRange
If InStr(cel.Formula, “„Ã”) > 0 Then cel.Value = cel.Value
Next cel
Next werkblad
End Sub

Houd in gedachten dat deze macro een koppeling alleen verwijdert als het bestand met de brongegevens gesloten is.

De laatste waarde in een kolom bepalen in Excel

Bij grote werkbladen is het handig als u de laatste waarde in een kolom kunt bepalen en in een andere cel kunt neerzetten. Dit kan op twee manieren: met een formule of met een Visual Basic-macro.

1. De laatste waarde van een kolom bepalen met een formule

Zet eerst in kolom A in een nieuw werkblad een aantal getallen of teksten. Zet daarna de muiswijzer op een lege cel, bijvoorbeeld in D2 en voer daar de volgende functie in:

=INDEX(A:A;AANTALARG(A:A))

Let op: bij deze methode mogen er geen lege cellen voor de laatste gevulde cel in de kolom voorkomen.

2. De laatste waarde van een kolom bepalen met een macro

Om de laatste waarde van een kolom inclusief het exacte celadres te bepalen maakt u een macro. Daarbij gaat u als volgt te werk:

1. Schakel met de toetsencombinatie Alt+F11 over naar de ontwikkelomgeving van Excel.
2. Kies in het menu Invoegen de opdracht Module.
3. Schrijf in het codevenster de volgende macro:

Sub LaatsteCelInKolomBepalen()
Sheets(“Blad1”).Activate
MsgBox Range(“A65536″).End(xlUp).Value & Chr(13) & _ ” in cel ” & Range(“A65536”).End(xlUp).Address
End Sub

Rekenen met ‘circa’-waarden

Bij het instellen van berekeningen zijn vaak nog niet alle cijfers voor een berekening beschikbaar. Veel gebruikers werken dan met ‘circa’-waarden om alvast een overzicht te hebben van de som van een reeks getallen. Om definitieve getallen te onderscheiden van geschatte waarden wordt vaak de term ‘circa’ in de cel toegevoegd. Als dan vervolgens de berekening wordt uitgevoerd, herkent Excel de invoer niet als getal, maar als tekst en wordt daardoor het getal bij de berekening genegeerd.

Met een kleine truc kunt ervoor zorgen dat u met ‘circa’-waarden kunt rekenen. Daartoe moet u een aangepaste getalnotatie maken.

Stap voor stap
1 Kies Opmaak -> Celeigenschappen. Activeer het tabblad Getal.
2 Klik onder Categorie op Aangepast en geef een type op als (bijvoorbeeld) in afbeelding 1. Sluit het dialoogvenster door op OK te klikken.

De notatie is nu beschikbaar in de werkmap (zie afbeelding 2) en kan met de knop Opmaak kopiëren/plakken op de werkbalk Opmaak worden gekopieerd.

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).

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)

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 evaluatie hierboven 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:

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

Klik op de kop van de kolom waar een nieuwe kolom vóór wilt maken. Druk op de toetsencombinatie Ctrl–+.
Herhaal deze bewerking voor de andere twee extra kolommen.
Zet in de uitgebreide voorbeeldtabel de celwijzer in D2 en schrijf daar de formule die de klasse berekent uit de kostenplaats: =LINKS(C2)
Schrijf in E2 de formule die de groep berekent uit de kostenplaats: =LINKS(C2,2)
Schrijf in F2 de formule die de subgroep berekent uit de kostenplaats: =LINKS(C2,3)
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).

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:

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().

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.