Excel, Accentueren formules

Tip & Truc met betrekking tot het accentueren van formules.


Een krachtig mechanisme binnen Excel is "voorwaardelijk opmaak". Met gebruikmaking van voorwaardelijke opmaak is het mogelijk om cellen met formules te identificeren en te accentueren. Hoe vaak komt het voor dat je per ongeluk een of meerdere cellen overschrijft met vaste waarden om er daarna achter te komen dat de inhoud van de cellen niet constant was, maar dat de cellen formules bevatten.

Een mechanisme om dit te voorkomen is om cellen met formules te beveiligen. Een ander middel is om relevante cellen in het werkblad herkenbaar te maken. Hierbij kan gebruik worden gemaakt van "voorwaardelijke opmaak" (beschikbaar vanaf Excel 97). De techniek maakt het mogelijk het werkblad zo in te stellen dat alle cellen met een formule afwijken van de standaardopmaak.

Voer hiervoor de volgende stappen uit:
  1. Kies voor: Invoegen -> Naam -> Definiëren
    In het dialoogscherm dat wordt getoond, voer de volgende naam in het tekstvak "Namen in werkmap":
    CelHeeftFormule
  2. Vul dan in het tekstvak " Verwijst naar:" de volgende formule in :
    =CEL.LEZEN(48;INDIRECT("rk";ONWAAR))
  3. Klik op Toevoegen, en dan OK.
  4. Selecteer de cellen die je wilt voorzien van de voorwaardelijke opmaak.
  5. Kies onder Opmaak -> Voorwaardelijke opmaak . . .
  6. In het getoonde dialoogscherm selecteer "Formule is" uit de lijst en voer vervolgens de volgende formule in:
    =CelHeeftFormule
  7. Selecteer onder Opmaak...
    de gewenste opmaak die de cellen moeten hebben indien deze een formule bevatten.
  8. Klik op OK.

Na deze bewerkingen zal iedere cel die een formule bevat in het bereik dat in stap 4 is geselecteerd worden getoond in de gespecificeerde opmaak.

Hoe werkt het eigenlijk ?

Het belangrijkste is het maken van de formule in stap 1. Deze formule staat niet - zoals een gebruikelijke standaardformule - in een cel, maar reageert wel als een formule door een waarde terug te geven. In het bovenstaand voorbeeld is de waarde WAAR of ONWAAR.

Hierbij wordt gebruik gemaakt van de functie CEL.LEZEN die onderdeel is van de XLM macro taal (voorloper van VBA). De functie kan niet rechtstreeks worden gebruikt in een werkblad. Door gebruik te maken van de parameter met de waarde 48 voor CEL.LEZEN wordt celinformatie terugontvangen (WAAR als de cel een formule bevat, ONWAAR als de cel geen formule bevat). Met de functie INDIRECT() maken we een verwijzing naar iedere cel in het geselecteerde bereik.

In de huidige Excel help zul je geen toelichting vinden over de functie CEL.LEZEN en als je de functie rechtstreeks in het werkblad zou gebruiken zou dit resulteren in de melding "Deze functie is ongeldig". Hé, dit is een andere reactie van Excel dan in het geval van het invoeren van een niet bestaande functie "#NAAM?". Dus wat is CEL.LEZEN eigenlijk ? Mensen die al geruime tijd ervaring hebben met Excel, zullen CEL.LEZEN herkennen als Excel 4.0 Macro functie.
Deze functies worden weliswaar nog steeds door de modernere versie van Excel herkend, maar zijn zelden nog zinvol in gebruik.

Wil je geen gebruik maken van functies uit het verleden, dan kan hetzelfde worden bereikt door het gebruik van een simpele VBA functie. Doe dan het volgende:

  1. Selecteer Extra -> Macro -> Visual Basic Editor
    In VBA moet de betreffende toe te voegen functie worden opgenomen in een bestaande of nieuw toe te voegen module.
  2. Indien er nog geen module bestaat kies dan voor Invoegen -> Module. Selecteer in het andere geval de bestaande module.
  3. Voeg de volgende functie in de code van de module toe:
    Function BevatFormule(C As C) As Boolean

       BevatFormule = C.HasFormula

    End Function
  4. Sluit hierna de editor en ga terug naar het Excel werkblad.
  5. Selecteer cel A1,
  6. Kies voor Voorwaardelijke Opmaak.
  7. Kies "Formule is"
  8. Voer de formule "=BevatFormule(A1)" in.
  9. Definieer de gewenste opmaak.
  10. Knip/Plak de betreffende opmaak naar de cellen in het gewenste bereik.

Alle cellen die een formule bevatten zullen nu in de gedefinieerde opmaak worden getoond.

N.B. Het is ook mogelijk om met behulp van selecteren speciaal cellen te selecteren die formules bevatten.




Heb je nog vragen, neem dan gerust contact met ons op.