Dokumenter formler i Excel som kode med N()-funksjonen

Cellemerknader er rotet i Excel-verdenen - de blokkerer utsikten din og kommer i veien for formler. Derfor bør du adoptere utviklermindsettet ved å gjemme merknader inne i formlene dine, slik at logikken din forblir ryddig, dataene synlige, og regnearkene profesjonelle.

N()-funksjonen: Din nye usynlige notatbok

Den enkleste måten å begynne å dokumentere Excel-formler som en programmerer på, er ved å bruke N()-funksjonen. Selv om dens primære oppgave er å konvertere ikke-numeriske verdier til tall, har den en skjult egenskap: fordi tekststrenger inne i denne funksjonen vurderes som null, kan du legge den til i enhver matematisk formel uten å endre sluttresultatet.

Anta at du beregner provisjonskolonnen i T_Comm-tabellen din, der seniorrepresentanter beregnes annerledes enn juniorrepresentanter. Med en skitten, udokumentert formel må alle som ser på arket omvendt konstruere matematikkene for å forstå forretningslogikken:

=IF([@Rank]="Senior",[@Profit]*0.2,[@Profit]*0.15)

Dette er hvor N()-funksjonen kommer inn. Ved å legge den til på slutten av beregningen din, oppretter du et rom for notater på vanlig engelsk som forblir skjult i regnearkets rutenett, men som fortsatt er synlige i formellinjen. Pakk alltid notatet ditt inn i doble anførselstegn—uten dem vil Excel behandle det som en ødelagt formel og returnere en #NAME?-feil:

=IF([@Rank]="Senior",[@Profit]*0.2,[@Profit]*0.15)
+N("Senior rate is 20%; Junior/Unknown is 15%")

Nå har du permanent knyttet forklaringen til beregningen, så svaret på enhver forespørsel om begrunnelsen finnes allerede i formelen.

Tekstproblemet: Hvordan kommentere uten å krasje formelen din

Nå, forestill deg at du legger til en Status-kolonne for å merke de ukjente rangene i T_Comm-tabellen din, slik at HR-teamet kan rette dem. Hvis du prøver å legge til en +N()-kommentar—som "Sjekk HR-profil"—til et tekstresultat, gir Excel en #VALUE!-feil fordi det ikke kan foreta en addisjon på et ord. For å dokumentere tekstbaserte formler, må du "lime" kommentaren på ved å bruke en usynlig tom streng.

Her er dobbelt-innpakningsmetoden som konverterer kommentaren din til et format som ikke vil bryte tekststrengen:

=IF([@Rank]="Unknown","Check HR Profile","Verified")
&T(N("Flagging for HR cleanup; rank must be Senior or Junior"))

Her konverterer N()-funksjonen teksten til null. Deretter ser T()-funksjonen på den nullen og—siden det ikke er tekst—returnerer en tom streng ("" ). Du legger egentlig til "ingenting" i statusmeldingen din, så cellen forblir uendret, men notatet ditt er trygt lagret i formellinjen.

I motsetning til den matematiske versjonen, der du brukte "+" for å legge til null, krever tekstbasert dokumentasjon sammenføyningsoperatoren (&), siden du limer sammen to tekststrenger (i stedet for å legge sammen tall). Ved å plassere sammenføyningsoperatoren i starten av en ny linje (ved å bruke Alt+Enter), signaliserer du til alle som leser formelen at logikken er avsluttet, og kommentaren begynner.

Dokumentering av logikk: +0*N()-syntaksen

Noen ganger returnerer ikke Excel-formelen din et tall eller tekststreng—den forteller deg i stedet om en betingelse er oppfylt.

I din T_Comm-tabell ønsker du denne gangen å legge til en Bonus-kolonne som markerer høyt fortjenesteposter for lønnsavdelingen. Hvis du legger til en kommentar direkte ved å bruke +N(), kan Excel bli forvirret fordi det behandler TRUE som 1 og FALSE som 0 i boolske scenarier. Som et resultat kan det å legge til en kommentar konvertere logikken din til et tall. Dette er spesielt problematisk hvis du bruker avmerkingsbokser, som krever TRUE- og FALSE-verdier for å fungere. Så for å holde logikken ren, må du multiplisere kommentaren med null:

=[@Profit]>300
+0*N("Profits over $300 qualify for a bonus")

Velg deretter kolonnen, og på Sett inn-fanen klikker du på «Avmerkingsboks».

Utover kommentarer: Navnelogikk med LET-funksjonen

Jeg hører deg tenke, «Hvorfor ikke bare bruke LET-funksjonen?» og det er et rettferdig spørsmål! Faktisk er LET gullstandarden for moderne Excel-dokumentasjon fordi den lar deg navngi logikken din mens du bygger den. Men det er en grunnleggende forskjell mellom en kommentar og en variabel.

Der N()-funksjonen lar deg fortelle formelen din med ekstern kontekst, lar LET deg definere formelens interne struktur. En variabel forteller deg hva en verdi representerer, mens en kommentar forteller deg hvorfor den verdien eksisterer i utgangspunktet.

Anta at du vil rydde opp i T_Comm-tabellen din ved å beregne en endelig utbetaling som inkluderer:

• En 15 % provisjon for junioransatte.
• En 20 % provisjon for senioransatte.
• En bonus på 50 dollar hvis noens fortjeneste overstiger 300 dollar.
• En $50-bonus for alle på seniornivå.

Ved å bruke LET kan du definere disse navngitte variablene før du i det hele tatt begynner med matematikken:

=LET(
JuniorRate, 0.15,
SeniorRate, 0.2,
BonusAmt, 50,
IsSenior, [@Rank]="Senior",
HighProfit, [@Profit]>300,
([@Profit]*IF(IsSenior, SeniorRate, JuniorRate))
+IF(HighProfit, BonusAmt, 0)
+IF(IsSenior, BonusAmt, 0)
)

Denne strukturen lar deg erstatte viktige tall og forvirrende cellereferanser med tydelig merkede variabler som SeniorRate og BonusAmt, noe som får formelen til å lese som en logisk fortelling. Som et resultat kan du oppdatere satsene på ett sted uten å ødelegge den videre beregningen, samtidig som du skiller "hva" variablene dine er fra "hvorfor" i dokumentasjonen din.

Sammendrag: Utviklerens guide til dokumentasjon av Excel-formler

For å oppsummere, her er en hurtigguide som kan hjelpe deg med å velge riktig dokumentasjonsstrategi basert på datatypen din:

Hvis du absolutt må legge til notater i regnearket ditt, men ikke vil ha den irriterende røde taggen i hjørnet av cellene, finnes det ett hemmelig triks til. Mens N() og LET er perfekte for å dokumentere logikken inne i en formel, trenger du noen ganger å gi instruksjoner til personen som bruker arket. Ved å bruke Datavalidering kan du lage hemmelige pop-up-notater som bare vises når noen velger en bestemt celle. Dette holder regnearket ditt ryddig og oversiktlig samtidig som du gir veiledning akkurat der det trengs.