Formule-controletools in Excel
Zoals we allemaal weten, wordt MS Excel voornamelijk gebruikt en populair vanwege 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.
U kunt deze Excel-sjabloon voor controletools hier downloaden - Excel-sjabloon voor controletools# 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 nu de precedenten voor de formule willen controleren, kunnen we op F2 drukken om in de bewerkingsmodus te komen nadat we de vereiste cel hebben geselecteerd, zodat precedentencellen worden omrand met verschillende kleuren en in dezelfde kleur wordt celverwijzing geschreven.
We kunnen zien dat A2 met blauwe kleur is geschreven in de formulecel en met dezelfde kleur, A2-cel is omrand.
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 hoeven alleen maar de formulecel te selecteren en vervolgens op het commando 'Trace Precedents' te 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
Dit commando 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 4 bedragen hebben die 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 terwijl kopiëren en plakken.
Als we nu willen controleren of welke cellen afhankelijk zijn van de G2-cel. Vervolgens 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 de valuta-indeling niet zichtbaar.
Om deze modus te deactiveren, drukt u nogmaals op 'Ctrl + ~' of we kunnen op het commando 'Formules weergeven' klikken .
# 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 het commando 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 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 kunnen zien en begrijpen 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 geeft deze de volgende informatie zoals weergegeven in het dialoogvenster.
Zoals we in de bovenstaande afbeelding kunnen zien, wordt de 'IIF'- expressie geëvalueerd als een fout die' #NAAM? 'Is. 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.
- We kregen als resultaat een foutmelding en terwijl we de functie stap voor stap analyseerden, kwamen we erachter dat er een fout in 'IIF' zit. Hiervoor kunnen we het 'Functie invoegen' commando gebruiken in 'Functiebibliotheek' groep onder ' Tabblad Formules.
Terwijl we de 'als' hebben getypt , hebben we een vergelijkbare functie in de lijst, we moeten de juiste functie kiezen.
Nadat we de functie 'If' hebben geselecteerd , krijgen we het volgende dialoogvenster met tekstvakken voor argument en vullen we alle details in.
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 een snelkoppeling in Excel.
