Formule-controletools in Excel
Zoals we allemaal weten, wordt MS Excel voornamelijk gebruikt en beroemd om zijn functie, formules en macro's. Maar wat als we een probleem krijgen tijdens het schrijven van de formule, of als we niet het gewenste resultaat in een cel kunnen krijgen omdat we de functie niet correct hebben geformuleerd. Daarom biedt MS Excel veel ingebouwde tools voor het controleren van formules en het oplossen van problemen met formules.
De tools die we kunnen gebruiken voor audits en probleemoplossing voor formules in Excel zijn:
- Traceer precedenten
- Traceer afhankelijken
- Verwijder pijlen
- Formules weergeven
- Foutcontrole
- Evalueer de formule
Voorbeelden van controletools in Excel
We zullen elk van de bovenstaande controletools een voor een leren aan de hand van enkele voorbeelden in Excel.
# 1 - Precedenten traceren
Stel dat we de volgende formule in D2-cel hebben voor het berekenen van rente voor een FD-rekening bij een bank.
Als we de precedenten voor de formule willen controleren, kunnen we op F2 drukken om in de bewerkingsmodus te komen na het selecteren van de vereiste cel, zodat precedentencellen worden omrand met verschillende kleuren en in dezelfde kleur wordt celverwijzing geschreven.
We kunnen zien dat A2 in de formulecel met blauwe kleur is geschreven en dat met dezelfde kleur de A2-cel omrand is.
Op dezelfde manier,
B2-cel heeft een rode kleur.
C2-cel heeft een paarse kleur.
Deze manier is goed, maar we hebben een gemakkelijkere manier om precedenten voor de formulecel te controleren.
Om precedenten te traceren, kunnen we het 'Trace Precedents'- commando gebruiken in de ' Formula Auditing'- groep onder het 'Formulas'- tabblad.
We moeten de formulecel selecteren en vervolgens op het commando 'Trace Precedents' klikken . Dan zie je een pijl zoals hieronder weergegeven.
We kunnen zien dat precedentcellen zijn gemarkeerd met blauwe stippen.
# 2 - Verwijder pijlen
Om deze pijlen te verwijderen, kunnen we het gebruiken 'Verwijder Arrows' opdracht in het 'Formula Auditing' groep onder 'Formules' tab.
# 3 - Traceer afhankelijken
Deze opdracht wordt gebruikt om de cel te traceren, die afhankelijk is van de geselecteerde cel.
Laten we dit commando aan de hand van een voorbeeld gebruiken.
Stel dat we vier bedragen hebben waarin we kunnen investeren. We willen weten hoeveel rente we kunnen verdienen als we investeren.
We kunnen zien dat we in de bovenstaande afbeelding een formule hebben toegepast voor het berekenen van rente met Bedrag 1 en gespecificeerd rentepercentage en looptijd in het jaar.
We zullen de formule kopiëren en in de aangrenzende cellen plakken voor bedrag 2, bedrag 3 en bedrag 4. Het valt op dat we een absolute celverwijzing hebben gebruikt voor G2- en I2-cellen, omdat we deze verwijzingen niet willen wijzigen tijdens het kopiëren en plakken.
Als we nu willen controleren of welke cellen afhankelijk zijn van de G2-cel, gebruiken we de opdracht 'Trace Dependents' die beschikbaar is in de groep 'Formula Auditing' onder het tabblad 'Formules' .
Selecteer de G2-cel en klik op het commando 'Trace Dependents' .
In de bovenstaande afbeelding zien we de pijllijnen waar pijlen aangeven welke cellen afhankelijk zijn van de cellen.
Nu zullen we de pijllijnen verwijderen met de opdracht 'Pijlen verwijderen' .
# 4 - Formules weergeven
We kunnen deze opdracht gebruiken om formules weer te geven die in het Excel-blad zijn geschreven. De sneltoets voor deze opdracht is 'Ctrl + ~.'
Zie de onderstaande afbeelding waar we de formules in de cel kunnen zien.
We kunnen zien dat we in plaats van formuleresultaten de formule kunnen zien. Voor bedragen is het valutanotatie niet zichtbaar.
Om deze modus te deactiveren, drukt u nogmaals op 'Ctrl + ~' of klikt u op het commando 'Formules weergeven' .
# 5 - Foutcontrole
Deze opdracht wordt gebruikt om de fout in de opgegeven formule of functie te controleren.
Laten we een voorbeeld nemen om dit te begrijpen.
Zie de onderstaande afbeelding waar we een fout hebben in de functie die voor het resultaat is toegepast.
Om deze fout nu op te lossen, gebruiken we de opdracht 'Foutcontrole' .
Stappen zouden zijn:
Selecteer de cel waarin de formule of functie is geschreven en klik op 'Foutcontrole'.
Als we op de opdracht klikken, krijgen we het volgende dialoogvenster met de titel 'Foutcontrole'.
In het bovenstaande dialoogvenster is te zien dat er een ongeldige naamfout is. De formule bevat de niet-herkende tekst.
Als we de functie gebruiken of de formule voor de eerste keer hebben geconstrueerd, kunnen we op de knop 'Help bij deze fout' klikken, waardoor de helppagina voor de functie in de browser wordt geopend waar we alle gerelateerde informatie online en begrijp de oorzaak en vind alle mogelijke oplossingen.
Als we nu op deze knop klikken, komen we op de volgende pagina.
Op deze pagina maken we kennis met de fout die optreedt wanneer deze fout optreedt
- De formule verwijst naar een naam die niet is gedefinieerd. Dit betekent dat de functienaam of het benoemde bereik niet eerder is gedefinieerd.
- De formule heeft een typfout in de gedefinieerde naam. Het betekent dat er een typefout is.
Als we de functie eerder hebben gebruikt en de functie kennen, kunnen we op de knop 'Berekeningsstappen weergeven' klikken om te controleren hoe de evaluatie van de functie resulteert in een fout.
Als we op deze knop klikken, worden de volgende stappen weergegeven:
- Het volgende dialoogvenster wordt weergegeven als we op de knop 'Berekeningsstappen weergeven' klikken.
- Nadat u op de knop 'Evalueren' hebt geklikt, wordt de onderstreepte uitdrukking, dwz 'IIF' , geëvalueerd en krijgt u de volgende informatie zoals weergegeven in het dialoogvenster.
Zoals we in de bovenstaande afbeelding kunnen zien, wordt de uitdrukking 'IIF' als een fout beschouwd, namelijk '#NAAM?'. Nu werd de volgende uitdrukking of verwijzing, dwz B2, onderstreept. Als we op de knop 'Stap in' klikken, kunnen we de interne details van een stap ook controleren en naar buiten komen door op de knop 'Stap uit' te drukken.
- Nu klikken we op de knop 'Evalueren' om het resultaat van de onderstreepte uitdrukking te controleren. Na klikken krijgen we het volgende resultaat.
- Nadat we op de knop 'Evalueren' hebben geklikt, krijgen we het resultaat van de toegepaste functie.
- Als resultaat kregen we een foutmelding en toen we de functie stap voor stap analyseerden, kwamen we erachter dat er een fout in 'IIF' zit. Hiervoor kunnen we het commando 'Functie invoegen' gebruiken in de groep 'Functiebibliotheek' onder het tabblad 'Formules'.
Terwijl we de 'als' hebben getypt , hebben we een vergelijkbare functie in de lijst, we moeten de juiste functie kiezen.
Na het selecteren van de 'If'- functie, krijgen we het volgende dialoogvenster met tekstvakken voor argument, en we zullen alle details invullen.
Nadat we op 'Ok' hebben geklikt , krijgen we het resultaat in de cel. We kopiëren de functie voor alle studenten.
Dingen om te onthouden
- Als we het 'Show Formulas'-commando activeren, worden de datums ook in het cijferformaat weergegeven.
- Bij het evalueren van de formule kunnen we F9 ook gebruiken als snelkoppeling in Excel.
