IF Formula Excel - If Then -utsagn

Last ned eksempel på arbeidsbok

Last ned eksemplet på arbeidsbok

Denne opplæringen viser hvordan du bruker Excel IF -funksjon i Excel for å lage If Then -setninger.

IF Funksjon Oversikt

IF -funksjonen sjekker om en betingelse er oppfylt. Hvis SANN gjør en ting, hvis FALSK gjør en annen.

For å bruke IF Excel -regnearkfunksjonen, velg en celle og skriv:

(Legg merke til hvordan formelinngangene vises)

IF -funksjon Syntaks og innganger:

1	= HVIS (logisk_test, verdi_if_sann, verdi_if_falsk)

logisk_test - Logisk uttrykk. Eksempel: A1> 4.

value_if_true - Verdi eller beregning som skal utføres hvis det logiske uttrykket er SANT.

verdi_ hvis_falsk - Verdi eller beregning som skal utføres hvis det logiske uttrykket er FALSKT.

IF er en "betinget" funksjon. Dette betyr at du definerer en logisk test, og den returnerer én verdi hvis den testen vurderes som sann, og en annen verdi hvis den er usann

Hvordan bruke IF -funksjonen

Her er et veldig grunnleggende eksempel, slik at du kan se hva jeg mener. Prøv å skrive følgende i Excel:

1	= HVIS (2 + 2 = 4, "Det er sant", "Det er usant!")

Siden 2 + 2 faktisk er lik 4, vil Excel returnere "Det er sant!". Hvis vi brukte dette:

1	= HVIS (2 + 2 = 5, "Det er sant", "Det er usant!")

Nå vil Excel returnere "It's false!", Fordi 2 + 2 ikke er lik 5.

Slik kan du bruke IF i et regneark.

1	= HVIS (C4-D4> 0, C4-D4,0)

Du driver en sportsbar og angir individuelle fanegrenser for forskjellige kunder. Du har satt opp dette regnearket for å sjekke om hver kunde er over sin grense. I så fall vil du kutte dem til de betaler fanen.

Du sjekker om C4-D4 (deres nåværende fanemengde minus grensen) er større enn 0. Dette er din logiske test. Hvis dette er sant, returnerer IF “Ja” - du bør kutte dem. Hvis dette er feil, returnerer IF "Nei" - du lar dem fortsette å drikke.

Hva IF kan returnere

Ovenfor returnerte vi en tekststreng, "Ja" eller "Nei". Men du kan også returnere tall eller andre formler.

La oss si at noen av kundene dine har store faner. For å motvirke dette, kommer du til å begynne å kreve renter på kunder som går over grensen.

Du kan bruke IF til det:

1	= HVIS (C4> D4, C4*0,03,0)

Hvis fanen er høyere enn grensen, returnerer du fanen multiplisert med 0,03, som gir 3% av fanen. Ellers returnerer du 0: de er ikke over fanen, så du vil ikke belaste renter.

Bruke IF med AND

Du kan kombinere IF med Excel’s AND -funksjon <>. Du bruker dette i den logiske testen, som lar deg angi to eller flere betingelser for å teste. Excel returnerer bare SANN hvis ALLE testene er sanne.

Så du implementerte renten din. Men noen av dine faste klager. De har alltid betalt fanene sine tidligere, hvorfor slår du ned på dem nå? Du kommer med en løsning: du vil ikke belaste renter til visse pålitelige kunder.

Du lager en ny kolonne i regnearket for å identifisere klarerte kunder, og oppdaterer IF -setningen med en OG -funksjon:

1	= HVIS (OG (C4> D4, F4 = "Nei"), C4*0,03,0)

La oss se på AND -delen separat:

1	OG (C4> D4, F4 = "Nei")

Legg merke til de to betingelsene:

• C4> D4: sjekk om de er over tabulatorgrensen, som før
• F4 = ”Nei”: dette er den nye biten, som sjekker om de ikke er en klarert kunde

Så nå returnerer vi bare renten hvis kunden er over fanen, OG vi har "Nei" i den pålitelige kundekolonnen. Dine faste er glade igjen.

Les mer på hovedsiden for Excel OG funksjon <>.

Bruke IF med OR

OR er en annen av Excel logiske funksjoner. I likhet med AND, kan du definere mer enn én betingelse. Men i motsetning til AND, vil den returnere SANN hvis NOEN av testene du definerer er sanne.

Kanskje kunder som er over fanen, ikke er den eneste grunnen til at du vil kutte dem. Kanskje du gir noen mennesker et midlertidig forbud av andre årsaker, kanskje gambling på stedet.

Så du legger til en ny kolonne for å identifisere forbudte kunder, og oppdaterer "Cut off?" kolonne med en ELLER -test:

1	= HVIS (ELLER (C4> D4, E4 = "Ja"), "Ja", "Nei")

Ser bare på OR -delen:

1	ELLER (C4> D4, E4 = "Ja")

Det er to betingelser:

• C4> D4: sjekker om de er over fanen
• F4 = ”Ja”: den nye delen, og sjekker om de for øyeblikket er utestengt

Dette vil evaluere til sant hvis de er over fanen, eller hvis det er et "Ja" i kolonne E. Som du kan se, er Harry avskåret nå, selv om han ikke er over fanen.

Les mer på hovedsiden for Excel ELLER -funksjonen <>.

Bruker IF med XOR

XOR er en annen logisk funksjon, som returnerer “Exclusive Or”. Dette er litt mindre intuitivt enn de tidligere vi har diskutert.

I enkle tilfeller definerer du to betingelser, og XOR vil returnere:

• SANN hvis begge argumentene er sanne (samme som en vanlig OR)
• FALSK hvis begge argumentene er sanne
• FALSK hvis begge argumentene er usanne

Et eksempel kan gjøre dette tydeligere. Tenk deg at du vil begynne å gi månedlige bonuser til personalet ditt:

• Hvis de selger over $ 800 i mat eller over $ 800 i drikkevarer, gir du dem en halv bonus
• Hvis de selger over $ 800 i begge deler, gir du dem en full bonus
• Hvis de selger under $ 800 i begge deler, får de ingen bonus.

Du vet allerede hvordan du skal trene hvis de får full bonus. Du vil bare bruke IF med AND, som beskrevet tidligere.

1	= HVIS (OG (C4> 800, D4> 800), "Ja", "Nei")

Men hvordan ville du finne ut hvem som får halv bonus? Det er her XOR kommer inn:

1	= HVIS (XOR (C4> = 800, D4> = 800), "Ja", "Nei")

Som du kan se, var Woodys drikkesalg over 800 dollar, men ikke matsalg. Så han får halv bonus. Det motsatte gjelder for Coach. Diane og Carla solgte mer enn $ 800 for begge, så de får ikke en halv bonus (begge argumentene er SANN), og Rebecca laget under terskelen for begge (begge argumentene FALSK), så formelen returnerer "Nei" igjen.

Les mer på hovedsiden for Excel XOR -funksjonen <>.

Bruker IF med NOT

NOT er enda en av Excel logiske funksjoner, som er veldig vanlig med IF.

IKKE reverserer utfallet av en logisk test. Med andre ord sjekker den om en betingelse ikke er oppfylt.

Du kan bruke den med IF slik:

1	= IF (AND (C3> = 1985, NOT (D3 = "Steven Spielberg")), "Watch", "Don't Watch")

Her har vi en tabell med data om noen filmer fra 1980 -tallet. Vi ønsker å identifisere filmer utgitt på eller etter 1985, som ikke ble regissert av Steven Spielberg.

Fordi NOT er nestet i en OG -funksjon, vil Excel vurdere det først. Det vil da bruke resultatet som en del av AND.

Les mer på hovedsiden i Excel NOT -funksjonen <>.

Nestede IF -erklæringer

Du kan også returnere en IF -setning i IF -setningen. Dette gjør at du kan gjøre mer komplekse beregninger.

La oss gå tilbake til kundens bord. Tenk deg at du vil klassifisere kunder basert på gjeldsnivået til deg:

• $ 0: Ingen
• Opptil $ 500: Lav
• $ 500 til $ 1000: Middels
• Over $ 1000: Høy

Du kan gjøre dette ved å "hekke" IF -setninger:

1	= IF (C4 = 0, "None", IF (C4 <= 500, "Low", IF (C4 <= 1000, "Medium", IF (C4> 1000, "High"))))

Det er lettere å forstå hvis du legger IF -setningene på separate linjer (ALT + ENTER på Windows, CTRL + COMMAND + ENTER på Mac):

12345

=HVIS (C4 = 0, "Ingen",HVIS (C4 <= 500, "Lav",IF (C4 <= 1000, "Medium",IF (C4> 1000, "High", "Unknown"))))

HVIS C4 er 0, returnerer vi “Ingen”. Ellers går vi til neste IF -setning. HVIS C4 er lik eller mindre enn 500, returnerer vi “Lav”. Ellers går vi videre til neste IF -setning … og så videre.

Forenkle komplekse IF -setninger med hjelperkolonner

Hvis du har flere nestede IF -setninger, og du også kaster inn logiske funksjoner, kan formlene dine bli svært vanskelige å lese, teste og oppdatere.

Dette er spesielt viktig å huske på hvis andre mennesker bruker regnearket. Det som gir mening i hodet ditt, er kanskje ikke så åpenbart for andre.

Hjelpekolonner er en fin måte å løse dette problemet på.

Du er analytiker i finansavdelingen til et stort selskap. Du har blitt bedt om å lage et regneark som kontrollerer om hver ansatt er kvalifisert for firmapensjon.

Her er kriteriene:

Så hvis du er under 55 år, må du ha 30 års tjeneste under beltet for å være kvalifisert. Hvis du er i alderen 55 til 59 år, trenger du 15 års tjeneste. Og så videre, opp til 65 år, hvor du er kvalifisert uansett hvor lenge du har jobbet der.

Du kan bruke en enkelt, kompleks IF -setning for å løse dette problemet:

1	= HVIS (ELLER (F4> = 65, OG (F4> = 62, G4> = 5), OG (F4> = 60, G4> = 10), OG (F4> = 55, G4> = 15), G4 > 30), "Kvalifisert", "Ikke kvalifisert")

Puh! Litt vanskelig å få hodet rundt det, ikke sant?

En bedre tilnærming kan være å bruke hjelperkolonner. Vi har fem logiske tester her, tilsvarende hver rad i kriterietabellen. Dette er lettere å se om vi legger til linjeskift i formelen, som vi diskuterte tidligere:

12345678

= HVIS (ELLER(F4> = 65,OG (F4> = 62, G4> = 5),OG (F4> = 60, G4> = 10),OG (F4> = 55, G4> = 15),G4> 30), "Kvalifisert", "Ikke kvalifisert")

Så vi kan dele disse fem testene i separate kolonner, og deretter bare kontrollere om noen av dem er sanne:

Hver kolonne i tabellen fra E til I inneholder hvert av våre kriterier separat. Så i J4 har vi følgende formel:

1	= HVIS (COUNTIF (E4: I4, TRUE), "Kvalifisert", "Ikke kvalifisert")

Her har vi en IF -setning, og den logiske testen bruker COUNTIF <> for å telle antall celler i E4: I4 som inneholder TRUE.

Hvis COUNTIF ikke finner en SANN verdi, returnerer den 0, som IF tolker som FALSK, så IF returnerer "Ikke kvalifisert".

Hvis COUNTIF finner noen SANDE verdier, returnerer det tallet på dem. HVIS tolker et annet tall enn 0 som SANN, returnerer det "Kvalifisert".

Å dele de logiske testene på denne måten gjør formelen lettere å lese, og hvis noe går galt med det, er det mye lettere å se hvor feilen er.

Bruke gruppering til å skjule hjelperkolonner

Hjelpekolonner gjør formelen lettere å administrere, men når du har fått dem på plass og du vet at de fungerer som de skal, tar de ofte plass i regnearket uten å legge til nyttig informasjon.

Du kan skjule kolonnene, men dette kan føre til problemer fordi skjulte kolonner er vanskelige å oppdage, med mindre du ser nøye på kolonneoverskriftene.

Et bedre alternativ er gruppering.

Velg kolonnene du vil gruppere, i vårt tilfelle E: I. Trykk deretter ALT + SKIFT + HØYRE PIL på Windows, eller KOMMANDO + SKIFT + K på Mac. Du kan også gå til "Data" -fanen på båndet og velge "Gruppe" fra "Oversikt" -delen.

Du vil se gruppen som vises over kolonneoverskriftene, slik:

Trykk deretter på "-"-knappen for å skjule kolonnene:

IFS -funksjonen

Nestede IF -setninger er veldig nyttige når du trenger å utføre mer komplekse logiske sammenligninger, og du må gjøre det i en celle. Imidlertid kan de bli kompliserte etter hvert som de blir lengre, og de kan være vanskelige å lese og oppdatere på skjermen.

Fra Excel 2022 og Excel 365 introduserte Microsoft en annen funksjon, IFS, for å gjøre dette litt enklere å administrere. Det nestede IF -eksemplet ovenfor kan oppnås med IFS slik:

1234567

= IFS (C4 = 0, "Ingen",C4 <= 500, "Lav",C4 <= 1000, "Medium",C4> 1000, "Høy",SANN, "Ukjent",)

Du kan lese alt om det på hovedsiden for Excel IFS -funksjonen <>.

Bruke IF med betinget formatering

Med Excels betingede formateringsfunksjon kan du formatere en celle på forskjellige måter avhengig av innholdet. Siden IF returnerer forskjellige verdier basert på vår logiske test, vil vi kanskje bruke betinget formatering for å gjøre disse forskjellige verdiene lettere å se.

Så la oss gå tilbake til personalets bonusbord fra tidligere.

Vi returnerer "Ja" eller "Nei" avhengig av hvilken bonus vi vil gi. Dette forteller oss hva vi trenger å vite, men informasjonen hopper ikke ut på oss. La oss prøve å fikse det.

Slik gjør du det:

• Velg celleområdet som inneholder IF -setningene. I vårt tilfelle er det E4: F8.
• Klikk på "Betinget formatering" i delen "Stiler" på "Hjem" -fanen på båndet.
• Klikk "Marker celleregler" og deretter "Lik med".
• Skriv "Ja" (eller hvilken returverdi du trenger) i den første boksen, og velg deretter formateringen du vil ha fra den andre boksen. (Jeg vil velge grønt for dette).
• Gjenta for alle returverdiene dine (jeg setter også "Nei" -verdier til rødt)

Her er resultatet:

Bruke IF i matriseformler

En matrise er et verdiområde, og i Excel er matriser representert som kommaadskilte verdier omsluttet av seler, for eksempel:

1	{1,2,3,4,5}

Det fine med arrays er at de lar deg utføre en beregning av hver verdi i området, og deretter returnere resultatet. For eksempel tar SUMPRODUCT -funksjonen to matriser, multipliserer dem sammen og summerer resultatene.

Så denne formelen:

1	= SUMPRODUCT ({1,2,3}, {4,5,6})

… Returnerer 32. Hvorfor? La oss jobbe med det:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Vi kan bringe en IF -setning inn i dette bildet, slik at hver av disse multiplikasjonene bare skjer hvis en logisk test returnerer true.

Ta for eksempel disse dataene:

https://www.automateexcel.com/excel/wp-content/uploads 2020/07/SUMPRODUCT-Example-Range.png "nei"> 1 = SUMPRODUCT (HVIS ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))

Merk: I Excel 2022 og tidligere må du trykke CTRL + SKIFT + ENTER for å gjøre dette til en matriseformel.

Vi vil ende opp med noe slikt:

https://www.automateexcel.com/excel/wp-content/uploads 2020/07/SUMPRODUCTS-IF-Results-Table.png "nei"> 1 $ C $ 2: $ C $ 10 = $ G2

På engelsk, hvis navnet i kolonne C er lik det som er i G2 ("Olivia"), DO multipliser verdiene i kolonnene D og E for den raden. Ellers må du ikke multiplisere dem. Deretter summerer du alle resultatene.

Du kan lære mer om denne formelen på hovedsiden for SUMPRODUCT IF Formula <>.

HVIS i Google Regneark

IF -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel:

Ytterligere merknader

Bruk IF -funksjonen til å teste om en tilstand er SANN. Hvis tilstanden er SANN, gjør en ting. Hvis det er FALSK, gjør en annen. Tilstanden må være et logisk uttrykk (eks: a1> 5), en referanse til en celle som inneholder TRUE eller FALSE, eller en matrise som inneholder alle logiske verdier.

IF -funksjonen kan bare teste en tilstand om gangen. Du kan imidlertid "hekke" andre logiske funksjoner i IF -tilstanden for å teste flere forhold samtidig:

= hvis (AND (a1> 0, a2> 0), TRUE, FALSE)
= hvis (ELLER (a1> 0, a2> 0), SANN, FALSK)
= hvis (XOR (a1> 0, a2> 0), TRUE, FALSE)
ELLER Funksjonstest hvis en eller fler betingelsene er oppfylt.
OG Funksjoner tester om alle betingelsene er oppfylt.
XOR Funksjoner tester hvis en og bare en betingelsen er oppfylt.

Du kan også "hekke" en IF -funksjon i en IF -funksjon:

1	= if (a1 <0, if (a2 <0, "Both", "only 1"), "only one")

Nå for noen konkrete eksempler på hvordan IF -funksjonen fungerer i praksis:

1. Start en ny arbeidsbok.

2. Skriv inn verdien 10 i celle A1 (og trykk Enter)

3. Skriv deretter inn følgende formel i celle B1:

1	= HVIS (A1> 5, "STØRRE ENN 5", "Mindre enn 5")

4. Skjermen skal se slik ut nå:

5. Hvis du har angitt formelen riktig, vil du se meldingen "Større enn 5" vises i celle B1.

6. Formelen du har angitt i celle B1 utfører testen "A1> 5", dvs. den sjekker om verdien i celle A1 er større enn 5. For øyeblikket er verdien i celle A1 10 - så betingelsen er SANN og meldingen “BIGGER THAN 5” vises

7. Hvis vi nå endrer verdien i celle A1 til 2:

Thtn meldingen i celle B2 er nå “MINDRE ENN 5” ettersom tilstanden er FALSK.

8. Du kan fortsette å endre verdien i celle A1, og meldingen i celle B2 vil justeres deretter.

9. Selvfølgelig er det situasjoner der tilstanden kan gi rampete resultater:

• Hva skjer hvis vi angir verdi 5 i celle A1?

• Hva om vi lar celle A1 stå tomt?

• Hva om vi legger litt tekst i celle A1, for eksempel uttrykket DOG

Mer om Excel IF -funksjonen

Vi vil nå se nærmere på IF -funksjonen. Den kan brukes til å analysere store datamengder veldig enkelt.

Tenk deg at du er områdesjef og har et salgsteam. Du kan registrere det totale salget som hver person gjør i et enkelt Excel -regneark:

Anta at kriteriene for en bonus var at salget foretatt av personen oversteg £ 40 000. Du kan bare "øye-ball" dataene og bestemme at bare Anton, Newton og Monique nådde målet.

Dette er ganske enkelt når du bare har en håndfull navn. Men hvis du har flere, er det rom for feil. Heldigvis kan det gjøres mye raskere og sikrere ved å bruke Excels IF -funksjon.

Sett opp en ny arbeidsbok og skriv inn dataene som beskrevet ovenfor. Skriv inn følgende formel i celle D4:-

1	= HVIS (C4> 40000, "BONUS BETALINGSBETALT", "INGEN BONUS")

slik at du har:

Legg merke til hvordan Excel viser strukturen til IF -formelen - som er et nyttig hjelpeminne.

Etter å ha skrevet formelen trykker du ENTER og du ser den evaluere for første rad:

Formelen er evaluert for Martin - ettersom han tjente mindre enn 40 000 pund, har han ikke krav på noen bonus.

Og så drar vi formlene ned ved å klikke nederst i høyre hjørne og dra nedover, så kan vi avgjøre om hver person har rett til en bonus:

Og vi ser at Excel har bestemt hvem av selgerne som har krav på bonus.

Gå tilbake til listen over alle funksjoner i Excel

VBA IF -erklæringer

Du kan også bruke If -setninger i VBA. Klikk på lenken for å lære mer, men her er et enkelt eksempel:

1234567

Sub Test_IF ()Hvis område ("a1"). Verdi <0 daOmråde ("b1"). Verdi = "Negativ"Slutt omSlutt om

Denne koden vil teste om en celleverdi er negativ. I så fall vil den skrive "negativ" i den neste cellen.