Kan je maar niet genoeg van Excel krijgen? Ideaal, want ik heb nog een blogpost voor je klaarstaan. Sinds Microsoft in 2007 tabellen in Excel introduceerde kan je het dollarteken ($) niet meer gebruiken om rijen of kolommen te blokkeren. Voor rijen zorgt dat niet voor problemen, maar kolommen kan je schijnbaar niet meer blokkeren. In deze blogpost leg ik je uit hoe dat wel lukt.
Elke Excel-gebruiker weet wat absolute en relatieve verwijzing is en kent het dollarteken ($). Net als F4 de bekendste functietoets is. Als je een degelijke formule wil bouwen, blokkeer je best rijen of kolommen. Nieuwe Excel-gebruikers moeten even wennen aan het gebruik van het dollarteken. Maar eens je doorhebt wat de noodzaak ervan is, zal dat je Excel-oplossingen vooruit helpen.
Sinds de introductie van tabellen in Excel (2007) is de schrijfwijze van formules drastisch gewijzigd. Vroeger gebruikte je het dollarteken in een celverwijzing of om rijen en kolommen te blokkeren. Nu gaat dat niet meer. De vierkante haakjes ([, ]) en het @-teken zijn erbij gekomen en maken formules leesbaarder voor de eindgebruiker. Tabellen werken de formules nu via het @-teken automatisch bij op rijniveau maar op kolomniveau ontbreekt er een standaardoplossing. In deze blog leg ik uit hoe je kolommen via een alternatieve schrijfwijze kan blokkeren.
In de klassieke schrijfwijze van een formule kan een celverwijzing op verschillende manieren genoteerd worden.
- De cel A1 kan in een formule gebruikt worden met als schrijfwijze: A1 / A$1 / $A1 / $A$1
- De functietoets F4 wordt als hulpmiddel gebruikt om na het selecteren van een cel (of een gebied) de dollartekens op de correcte plaats te vermelden. Dat doe je door de toets meerdere keren in te drukken.
Om dit toe te lichten geef ik hieronder een voorbeeld van de oude schrijfwijze ten opzichte van de nieuwe schrijfwijze. Daarna zien we de oplossing om de blokkering op kolomniveau toe te passen als we met tabellen werken.
De cellen onder de gele kolomkoppen moeten de overeenkomstige cel onder de groene kolomkop vermenigvuldigen met een vaste factor voor de stad die op rijniveau vermeld wordt.
In deze oplossing moet cel C6 op kolomniveau geblokkeerd worden, wat $C6 als resultaat oplevert.
Daardoor kan de formule naar alle cellen in het doelbereik gekopieerd worden zonder verdere aanpassing.
Tip: Selecteer het volledige doelgebied (H6:K9) en begin de formule te typen. Eindig met CTRL+ENTER. Daardoor wordt je formule automatisch naar alle cellen in het doelgebied gekopieerd zonder dat de opmaak van de cellen aangepast wordt.
Als je een tabel op het gebied toepast en hetzelfde voorbeeld opnieuw wil uitwerken, zal je merken dat er geen directe mogelijkheid bestaat om kolom C te blokkeren.
De nieuwe schrijfwijze voor tabellen krijg je enkel door de gewenste cel aan te klikken.
Na het ingeven van de formule gaat Excel automatisch je formule tot in de laatste cel van de kolom kopiëren. Dat is een enorme verbetering ten opzichte van de klassieke werkwijze.
Een nadeel van deze nieuwe werkwijze ontstaat wanneer je de formule naar de aangrenzende kolommen wil kopiëren. Zoals je in onderstaand voorbeeld kan zien, schuift de verwijzing voor elke cel mee op terwijl de [@Factor]-verwijzing geblokkeerd had moeten zijn. Dat kan niet meer met een dollarteken opgelost worden.
Het gewenste resultaat is =[@Summer]*[@Factor] maar beide cellen hebben dezelfde verschuiving gevolgd.
Dat los je op door de verwijzing naar Factor op een andere manier in de formule te noteren.
n plaats van [@Factor] schrijf je tblSeason[@[factor]:[Factor]].
In deze schrijfwijze start je met de tabelnaam. Excel zal dat automatisch corrigeren als je de tabelnaam zou vergeten. Dan open je een vierkant haakje en gebruik je het @-teken voor de rijverwijzing (context). Daarna vermeld je de kolomnaam, gevolgd door een dubbelpunt (:) en voeg je de kolomnaam nog eens toe aan de formule.
Als je de formule nu naar rechts versleept, zal de verwijzing naar de kolom ‘Factor’ steeds geblokkeerd blijven terwijl de kolom Spring naar Summer, Fall, Winter zal veranderen.
Vond je dit een nuttige tip? Laat dan zeker een reactie achter of ontdek er meer tijdens een open Excel-opleiding. In februari 2020 kan je uitzonderlijk Excel en Power BI in Gent volgen!
