Excel, spelen met bereiken (samenvoegen, doorsnijden)

Tip
Het is mogelijk om op basis van samengevoegde en doorsnijdingen van bereiken (die je al dan niet een naam hebt gegeven) berekeningen te maken. De werkwijze wordt hieronder geïllustreerd naar aanleiding van een aantal voorbeelden.

Stap 1: Voer de matrix met gegevens in.
Maak voor het voorbeeld een eigen werkmap met daarin onderin bijvoorbeeld de gegevens zoals weergegeven in onderstaande afbeelding.

Stap 2. Definieer per week (kolom) een bereik met naam.
Dit kan via het menu Invoegen -> Naam -> Definiëren. Noem het bereik InWeek_41. Zie hiervoor ook onderstaand voorbeeld. Doe dit voor elke kolom (InWeek_42, InWeek_43 . . . InWeek_46).

Stap 3. Definieer per persoon (regel) een bereik met naam.
Dit kan via het menu Invoegen -> Naam -> Definiëren. Noem het bereik net zo als de naam van de persoon. Zie hiervoor het hieronder gegeven voorbeeld. Doe dit voor elke persoon (regel).

Het resultaat van de voorbereiding is een matrix met gegevens over uren per week voor een bepaalde persoon. Met behulp van de gedefinieerde bereiken en het samenvoegen of doorsnijden van deze bereiken is het mogelijk gegevens uit de matrix op een natuurlijke manier uit te vragen.

Stap 4. Uitvragen van de matrix door het samenvoegen van bereiken.
Hieronder zullen meerdere voorbeelden worden gegeven van het uitvragen van de matrix.

Stap 4.1 Sommeer alle uren van alle personen die gemaakt zijn in week 41, 43 en 46.
Als de bereiken in de formule worden gescheiden door een ";", dan worden deze in de berekening als één bereik gezien (samengevoegd).
De formule voor stap 4.1 (resultaat = 40) ziet er dan als volgt uit:



=SOM(InWeek_41;InWeek_43;InWeek_46)

Stap 4.2 Hoeveel uren heeft J. Jansen besteed in week 43 ?
Er is hier niet sprake van het samenvoegen van bereiken, maar van een doorsnijding. Voor het doorsnijden van bereiken moeten we ze scheiden door een " " (spatie). De formule voor stap 4.2 (resultaat = 3) ziet er dan als volgt uit:



=(J_Jansen InWeek_43) of
=SOM(J_Jansen InWeek_43)

Stap 4.3 Hoeveel uren hebben J. Jansen, P. Pietersen en H. Hummel gemaakt in de weken 41 en 46 ?
Nu wordt het wat ingewikkelder, maar niet minder overzichtelijk. De formule die voor stap 4.3 (resultaat = 21) ziet er dan als volgt uit (denk om de haken):



=SOM((J_Jansen; P_Pietersen; H_Hummel) (InWeek_41;InWeek_46) )

Stap 5. Blijf dingen uitproberen
Denk naar aanleiding van bovenstaande voorbeelden eens na over eigen toepassingen. Het is niet alleen goed om krachtige complexe formules te maken, maar ook aandacht te besteden aan het overzichtelijk en beheersbaar houden van Excel-werkbladen. Het gebruik van naamgeving voor bereiken en zoals in deze voorbeelden het gebruikmaken van een soort "natuurlijke taal" in formules voor het berekenen van totalen kan hierbij een stap zijn in de goede richting.