IF Formula Excel - If Then -udsagn

Download eksempel på projektmappe

Download eksemplet på projektmappe

Denne vejledning viser, hvordan du bruger Excel IF -funktion i Excel for at oprette If Then -erklæringer.

IF Funktionsoversigt

IF -funktionen kontrollerer, om en betingelse er opfyldt. Hvis SAND gør en ting, hvis FALSK gør en anden.

Hvis du vil bruge IF Excel -regnearksfunktionen, skal du markere en celle og skrive:

(Bemærk hvordan formelindgangene vises)

IF -funktion Syntaks og input:

1	= HVIS (logisk_test, værdi_if_sand, værdi_if_falsk)

logisk_test - Logisk udtryk. Eksempel: A1> 4.

value_if_true - Værdi eller beregning, der skal udføres, hvis det logiske udtryk er sandt.

værdi_ hvis_falsk - Værdi eller beregning, der skal udføres, hvis det logiske udtryk er FALSKT.

IF er en "betinget" funktion. Det betyder, at du definerer en logisk test, og den returnerer en værdi, hvis den test vurderes som sand, og en anden værdi, hvis den er falsk

Sådan bruges IF -funktionen

Her er et meget grundlæggende eksempel, så du kan se, hvad jeg mener. Prøv at skrive følgende i Excel:

1	= HVIS (2 + 2 = 4, "Det er sandt", "Det er falsk!")

Da 2 + 2 faktisk er lig med 4, returnerer Excel "Det er sandt!". Hvis vi brugte dette:

1	= HVIS (2 + 2 = 5, "Det er sandt", "Det er falsk!")

Nu returnerer Excel “Det er falsk!”, Fordi 2 + 2 ikke er lig med 5.

Sådan kan du bruge IF i et regneark.

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

Du driver en sportsbar, og du angiver individuelle fanebegrænsninger for forskellige kunder. Du har konfigureret dette regneark for at kontrollere, om hver kunde er over deres grænse, i hvilket tilfælde vil du afbryde dem, indtil de betaler deres fane.

Du kontrollerer, om C4-D4 (deres aktuelle fanebeløb minus deres grænse) er større end 0. Dette er din logiske test. Hvis dette er sandt, returnerer IF “Ja” - du skal afbryde dem. Hvis dette er forkert, returnerer IF “Nej” - du lader dem blive ved med at drikke.

Hvad IF kan returnere

Ovenfor returnerede vi en tekststreng, "Ja" eller "Nej". Men du kan også returnere tal eller endda andre formler.

Lad os sige, at nogle af dine kunder kører store faner. For at afskrække dette, vil du begynde at opkræve renter på kunder, der overskrider deres grænse.

Du kan bruge IF til det:

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

Hvis fanen er højere end grænsen, returneres fanen ganget med 0,03, hvilket returnerer 3% af fanen. Ellers skal du returnere 0: de er ikke over deres fane, så du vil ikke opkræve renter.

Brug af IF med AND

Du kan kombinere IF med Excel’s AND -funktion <>. Du bruger dette i den logiske test, som giver dig mulighed for at angive to eller flere betingelser, der skal testes. Excel returnerer kun SAND, hvis ALLE testene er sande.

Så du implementerede din rente. Men nogle af dine faste klager. De har altid betalt deres faner tidligere, hvorfor slår du ned på dem nu? Du finder en løsning: Du vil ikke opkræve renter til visse betroede kunder.

Du opretter en ny kolonne i dit regneark for at identificere betroede kunder og opdaterer din IF -erklæring med en OG -funktion:

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

Lad os se på AND -delen separat:

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

Bemærk de to betingelser:

• C4> D4: kontrollere, om de er over deres fanegrænse, som før
• F4 = ”Nej”: dette er den nye bit, der kontrollerer, om de ikke er en betroet kunde

Så nu returnerer vi kun renten, hvis kunden er over deres fane, OG vi har "Nej" i den betroede kundesøjle. Dine faste er glade igen.

Læs mere på hovedsiden for Excel OG -funktionen <>.

Brug af IF med OR

OR er en anden af ​​Excels logiske funktioner. Ligesom AND, giver det dig mulighed for at definere mere end én betingelse. Men i modsætning til AND, vil det returnere SANDT, hvis NOGEN af de test, du definerer, er sande.

Måske er kunder, der er over deres fane, ikke den eneste grund til, at du ville afbryde dem. Måske giver du nogle mennesker et midlertidigt forbud af andre årsager, måske spil på stedet.

Så du tilføjer en ny kolonne til at identificere forbudte kunder og opdaterer din "Afbrydelse?" kolonne med en OR -test:

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

Ser man bare på OR -delen:

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

Der er to betingelser:

• C4> D4: kontrollere, om de er over deres fanegrænse
• F4 = ”Ja”: den nye del, der kontrollerer, om de i øjeblikket er forbudt

Dette vurderes til sandt, hvis de er over deres fane, eller hvis der er et "Ja" i kolonne E. Som du kan se, er Harry afskåret nu, selvom han ikke er over sin fanegrænse.

Læs mere på hovedsiden for Excel ELLER -funktionen <>.

Brug af IF med XOR

XOR er en anden logisk funktion, som returnerer “Exclusive Or”. Dette er lidt mindre intuitivt end de tidligere, vi har diskuteret.

I enkle tilfælde definerer du to betingelser, og XOR vender tilbage:

• SAND hvis begge argumenter er sande (samme som en normal OR)
• FALSKT, hvis begge argumenter er sande
• FALSK hvis begge argumenter er falske

Et eksempel kan gøre dette tydeligere. Forestil dig, at du vil begynde at give månedlige bonusser til dine medarbejdere:

• Hvis de sælger over $ 800 i mad eller over $ 800 i drikkevarer, giver du dem en halv bonus
• Hvis de sælger over $ 800 i begge dele, giver du dem en fuld bonus
• Hvis de sælger under $ 800 i begge dele, får de ingen bonus.

Du ved allerede, hvordan du skal træne, hvis de får den fulde bonus. Du vil bare bruge IF med AND, som beskrevet tidligere.

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

Men hvordan finder du ud af, hvem der får den halve bonus? Det er her, XOR kommer ind:

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

Som du kan se, var Woodys drikkesalg over 800 dollars, men ikke salg af mad. Så han får den halve bonus. Det omvendte gælder for Coach. Diane og Carla solgte mere end $ 800 for begge, så de ikke får en halv bonus (begge argumenter er SAND), og Rebecca lavede under tærsklen for begge (begge argumenter FALSK), så formlen returnerer igen "Nej".

Læs mere på hovedsiden for Excel XOR -funktionen <>.

Brug af IF med NOT

NOT er endnu en af ​​Excels logiske funktioner, som meget almindeligt bruges med IF.

IKKE vender resultatet af en logisk test. Med andre ord kontrollerer den, om en betingelse ikke er opfyldt.

Du kan bruge den med IF på denne måde:

1	= HVIS (OG (C3> = 1985, IKKE (D3 = "Steven Spielberg")), "Se", "Se ikke")

Her har vi en tabel med data om nogle film fra 1980'erne. Vi vil identificere film udgivet på eller efter 1985, som ikke blev instrueret af Steven Spielberg.

Fordi NOT er indlejret i en OG -funktion, evaluerer Excel det først. Det vil derefter bruge resultatet som en del af AND.

Læs mere på hovedsiden i Excel NOT -funktionen <>.

Indlejrede IF -erklæringer

Du kan også returnere en IF -erklæring i din IF -erklæring. Dette giver dig mulighed for at foretage mere komplekse beregninger.

Lad os gå tilbage til vores kunders bord. Forestil dig, at du vil klassificere kunder baseret på deres gældsniveau til dig:

• $ 0: Ingen
• Op til $ 500: Lav
• $ 500 til $ 1000: Medium
• Over $ 1000: Høj

Du kan gøre dette ved at "indlejre" IF -udsagn:

1	= HVIS (C4 = 0, "Ingen", HVIS (C4 <= 500, "Lav", HVIS (C4 <= 1000, "Medium", HVIS (C4> 1000, "Høj"))))

Det er lettere at forstå, hvis du sætter IF -sætningerne på separate linjer (ALT + ENTER på Windows, CTRL + COMMAND + ENTER på Mac'er):

12345

=HVIS (C4 = 0, "Ingen",HVIS (C4 <= 500, "Lav",HVIS (C4 <= 1000, "Medium",HVIS (C4> 1000, "Høj", "Ukendt"))))

HVIS C4 er 0, returnerer vi “Ingen”. Ellers går vi til den næste IF -erklæring. HVIS C4 er lig med eller mindre end 500, returnerer vi “Lav”. Ellers går vi videre til den næste IF -erklæring … og så videre.

Forenkling af komplekse IF -erklæringer med hjælperkolonner

Hvis du har flere indlejrede IF -sætninger, og du også kaster logiske funktioner ind, kan dine formler blive meget svære at læse, teste og opdatere.

Dette er især vigtigt at huske på, hvis andre mennesker vil bruge regnearket. Hvad der giver mening i dit hoved, er måske ikke så indlysende for andre.

Hjælperkolonner er en god vej omkring dette problem.

Du er analytiker i finansafdelingen i et stort selskab. Du er blevet bedt om at oprette et regneark, der kontrollerer, om hver medarbejder er berettiget til firmapension.

Her er kriterierne:

Så hvis du er under 55 år, skal du have 30 års tjeneste under dit bælte for at være berettiget. Hvis du er i alderen 55 til 59 år, har du brug for 15 års service. Og så videre op til 65 år, hvor du er berettiget, uanset hvor længe du har arbejdet der.

Du kan bruge en enkelt, kompleks IF -sætning til at løse dette problem:

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

Puha! Lidt svært at få hovedet omkring det, ikke sandt?

En bedre tilgang kan være at bruge hjælperkolonner. Vi har fem logiske tests her, svarende til hver række i kriterietabellen. Det er lettere at se, om vi tilføjer linjeskift til formlen, som vi diskuterede tidligere:

12345678

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

Så vi kan opdele disse fem tests i separate kolonner og derefter blot kontrollere, om en af ​​dem er sand:

Hver kolonne i tabellen fra E til I indeholder hvert af vores kriterier separat. Så i J4 har vi følgende formel:

1	= HVIS (COUNTIF (E4: I4, TRUE), "Kvalificeret", "Ikke kvalificeret")

Her har vi en IF -sætning, og den logiske test bruger COUNTIF <> til at tælle antallet af celler inden for E4: I4, der indeholder TRUE.

Hvis COUNTIF ikke finder en SAND værdi, returnerer den 0, som IF tolker som FALSK, så IF returnerer "Ikke kvalificeret".

Hvis COUNTIF finder nogle SANDE værdier, returnerer det antallet af dem. HVIS tolker ethvert andet tal end 0 som SAND, så det returnerer "Kvalificeret".

Opdelingen af ​​de logiske tests på denne måde gør formlen lettere at læse, og hvis noget går galt med det, er det meget lettere at se, hvor fejlen er.

Brug af gruppering til at skjule hjælperkolonner

Hjælperkolonner gør formlen lettere at administrere, men når du har fået dem på plads, og du ved, at de fungerer korrekt, fylder de ofte bare på dit regneark uden at tilføje nyttige oplysninger.

Du kan skjule kolonnerne, men dette kan føre til problemer, fordi skjulte kolonner er svære at opdage, medmindre du ser nøje på kolonneoverskrifterne.

En bedre mulighed er gruppering.

Vælg de kolonner, du vil gruppere, i vores tilfælde E: I. Tryk derefter på ALT + SKIFT + HØJRE PIL på Windows eller KOMMANDO + SKIFT + K på Mac. Du kan også gå til fanen "Data" på båndet og vælge "Gruppér" fra afsnittet "Oversigt".

Du vil se gruppen vist over kolonneoverskrifterne, sådan her:

Tryk derefter på knappen “-“ for at skjule kolonnerne:

IFS -funktionen

Indlejrede IF -sætninger er meget nyttige, når du skal udføre mere komplekse logiske sammenligninger, og du skal gøre det i en celle. De kan dog blive komplicerede, når de bliver længere, og de kan være svære at læse og opdatere på din skærm.

Fra Excel 2022 og Excel 365 introducerede Microsoft en anden funktion, IFS, for at gøre det lidt lettere at administrere. Det indlejrede IF -eksempel ovenfor kan opnås med IFS som dette:

1234567

= IFS (C4 = 0, "Ingen",C4 <= 500, "Lav",C4 <= 1000, "Medium",C4> 1000, "Høj",SAND, "Ukendt",)

Du kan læse alt om det på hovedsiden for Excel IFS -funktionen <>.

Brug af IF med betinget formatering

Excel's betingede formateringsfunktion giver dig mulighed for at formatere en celle på forskellige måder afhængigt af dens indhold. Da IF returnerer forskellige værdier baseret på vores logiske test, vil vi måske bruge betinget formatering til at gøre disse forskellige værdier lettere at se.

Så lad os gå tilbage til vores personalebonusbord fra tidligere.

Vi returnerer "Ja" eller "Nej" afhængigt af hvilken bonus vi vil give. Dette fortæller os, hvad vi har brug for at vide, men oplysningerne springer ikke ud over os. Lad os prøve at rette op på det.

Sådan gør du det:

• Vælg celleområdet, der indeholder dine IF -sætninger. I vores tilfælde er det E4: F8.
• Klik på "Betinget formatering" i sektionen "Styles" på fanen "Start" på båndet.
• Klik på "Fremhæv celleregler" og derefter "Lig med".
• Skriv "Ja" (eller hvilken returværdi du end har brug for) i den første boks, og vælg derefter den formatering, du ønsker, fra den anden boks. (Jeg vælger grønt til dette).
• Gentag for alle dine returværdier (jeg sætter også værdierne "Ingen" til rød)

Her er resultatet:

Brug af IF i matrixformler

En matrix er en række værdier, og i Excel er arrays repræsenteret som kommaadskilte værdier indesluttet i seler, såsom:

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

Arrays skønhed er, at de giver dig mulighed for at udføre en beregning af hver værdi i intervallet og derefter returnere resultatet. For eksempel tager SUMPRODUCT -funktionen to arrays, multiplicerer dem sammen og summerer resultaterne.

Så denne formel:

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

… Returnerer 32. Hvorfor? Lad os arbejde det igennem:

12345

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

Vi kan bringe en IF -sætning ind i dette billede, så hver af disse multiplikationer kun sker, hvis en logisk test returnerer sand.

Tag f.eks. Disse data:

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

Bemærk: I Excel 2022 og tidligere skal du trykke på CTRL + SKIFT + ENTER for at gøre dette til en matrixformel.

Vi ender med sådan noget:

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

På engelsk, hvis navnet i kolonne C er lig med det, der er i G2 ("Olivia"), skal du gange værdierne i kolonner D og E for den pågældende række. Ellers må du ikke gange dem. Saml derefter alle resultaterne.

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

HVIS i Google Sheets

IF -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:

Ekstra Noter

Brug IF -funktionen til at teste, om en tilstand er SAND. Hvis betingelsen er SAND, så gør en ting. Hvis det er FALSK, så gør en anden. Betingelsen skal være et logisk udtryk (f.eks. A1> 5), en henvisning til en celle, der indeholder SAND eller FALSK, eller en matrix, der indeholder alle logiske værdier.

IF -funktionen kan kun teste en tilstand ad gangen. Du kan dog "rede" andre logiske funktioner inden for IF -tilstanden for at teste flere forhold på én gang:

= hvis (OG (a1> 0, a2> 0), SAND, FALSK)
= hvis (ELLER (a1> 0, a2> 0), SAND, FALSK)
= hvis (XOR (a1> 0, a2> 0), SAND, FALSK)
ELLER Funktionstest, hvis en eller flere betingelser er opfyldt.
OG Funktionstest hvis alle betingelser er opfyldt.
XOR -funktioner test, hvis en og kun en betingelse er opfyldt.

Du kan også “rede” en IF -funktion inden for en IF -funktion:

1	= hvis (a1 <0, if (a2 <0, "Begge", "kun 1"), "kun én")

Nu nogle konkrete eksempler på, hvordan IF -funktionen fungerer i praksis:

1. Start en ny arbejdsbog.

2. Indtast værdien 10 i celle A1 (og tryk på Enter)

3. Indtast derefter følgende celle i celle B1:

1	= HVIS (A1> 5, "STØRRE END 5", "Mindre end 5")

4. Skærmen skal se sådan ud nu:

5. Hvis du har indtastet formlen korrekt, vil du se meddelelsen "Større end 5" vises i celle B1.

6. Den formel, du har indtastet i celle B1, udfører testen "A1> 5", dvs. den kontrollerer, om værdien i celle A1 er større end 5. I øjeblikket er værdien i celle A1 10 - så betingelsen er SAND og meddelelsen “BIGGER THAN 5” vises

7. Hvis vi nu ændrer værdien i celle A1 til 2:

Thtn beskeden i celle B2 er nu "MINDRE END 5", da betingelsen er FALSK.

8. Du kan blive ved med at ændre værdien i celle A1, og meddelelsen i celle B2 justeres derefter.

9. Selvfølgelig er der situationer, hvor tilstanden kan give uheldige resultater:

• Hvad sker der, hvis vi indtaster værdi 5 i celle A1?

• Hvad med, hvis vi lader celle A1 stå tomt?

• Hvad med, hvis vi lægger noget tekst i celle A1, f.eks. Sætningen HUND

Mere om Excel IF -funktionen

Vi vil nu se mere på IF -funktionen. Det kan bruges til at analysere store datamængder meget let.

Forestil dig, at du er Area Sales Manager og har et salgsteam. Du kan registrere det samlede salg, som hver person foretager i et enkelt Excel -regneark:

Antag, at kriterierne for en bonus var, at salg foretaget af denne person oversteg £ 40.000. Du kunne bare "øje-kugle" dataene og bestemme, at kun Anton, Newton og Monique nåede målet.

Dette er ganske let, når du bare har en håndfuld navne. Men hvis du har flere, er der mulighed for fejl. Heldigvis kan det gøres meget hurtigere og sikrere ved hjælp af Excels IF -funktion.

Opret en ny projektmappe, og indtast dataene som ovenfor. Indtast derefter i celle D4 følgende formel:-

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

så du har:

Bemærk, hvordan Excel viser strukturen i IF -formlen - hvilket er et nyttigt hjælpememoire.

Efter at have indtastet formlen skal du trykke på ENTER, og du kan se, at den evalueres for den første række:

Formlen er blevet evalueret for Martin - da han tjente mindre end £ 40.000, har han ikke ret til nogen bonus.

Og så trækker vi formlerne ned ved at klikke i nederste højre hjørne og trække ned, så kan vi afgøre, om hver person har ret til en bonus:

Og vi ser, at Excel har bestemt, hvem af sælgerne, der har ret til en bonus.

Tilbage til listen over alle funktioner i Excel

VBA IF -erklæringer

Du kan også bruge If -erklæringer i VBA. Klik på linket for at lære mere, men her er et enkelt eksempel:

1234567

Sub Test_IF ()Hvis område ("a1"). Værdi <0 derefterOmråde ("b1"). Værdi = "Negativ"Afslut HvisAfslut Hvis

Denne kode tester, om en celleværdi er negativ. I så fald vil det skrive "negativt" i den næste celle.