IF Formula Excel - If Then -uttalanden

Ladda ner exempel på arbetsbok

Ladda ner exempelarbetsboken

Denna handledning visar hur du använder Excel IF -funktion i Excel för att skapa If Then -uttalanden.

IF Funktionsöversikt

IF -funktionen kontrollerar om ett villkor är uppfyllt. Om SANT gör en sak, om FALSKT gör en annan.

Om du vill använda funktionen IF Excel -kalkylblad väljer du en cell och skriver:

(Lägg märke till hur formelinmatningarna ser ut)

IF -funktion Syntax och ingångar:

1	= OM (logisk_test, värde_if_true, värde_if_falsk)

Logiktest - Logiskt uttryck. Exempel: A1> 4.

value_if_true - Värde eller beräkning som ska utföras om det logiska uttrycket är SANT.

value_if_false - Värde eller beräkning som ska utföras om det logiska uttrycket är FALSKT.

IF är en "villkorlig" funktion. Det betyder att du definierar ett logiskt test och det returnerar ett värde om testet utvärderas som sant och ett annat värde om det är falskt

Hur man använder IF -funktionen

Här är ett mycket grundläggande exempel så att du kan se vad jag menar. Prova att skriva följande i Excel:

1	= OM (2 + 2 = 4, "Det är sant", "Det är falskt!")

Eftersom 2 + 2 faktiskt är lika med 4, kommer Excel att returnera "Det är sant!". Om vi ​​använde detta:

1	= OM (2 + 2 = 5, "Det är sant", "Det är falskt!")

Nu returnerar Excel "Det är falskt!", Eftersom 2 + 2 inte är lika med 5.

Så här kan du använda IF i ett kalkylblad.

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

Du driver en sportbar och du anger individuella flikgränser för olika kunder. Du har skapat det här kalkylarket för att kontrollera om varje kund överskrider sin gräns, i så fall stänger du av dem tills de betalar sin flik.

Du kontrollerar om C4-D4 (deras nuvarande flikbelopp minus deras gräns) är större än 0. Detta är ditt logiska test. Om detta är sant, returnerar IF "Ja" - du bör stänga av dem. Om detta är falskt, returnerar IF "Nej" - du låter dem fortsätta dricka.

Vad IF kan återvända

Ovanstående returnerade vi en textsträng, "Ja" eller "Nej". Men du kan också returnera siffror eller till och med andra formler.

Låt oss säga att några av dina kunder har stora flikar. För att avskräcka detta kommer du att börja ta ut ränta på kunder som går över sin gräns.

Du kan använda IF för det:

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

Om fliken är högre än gränsen, returnera fliken multiplicerad med 0,03, vilket ger 3% av fliken. Annars returnerar du 0: de är inte över fliken, så du tar inte ut ränta.

Använda IF med AND

Du kan kombinera IF med Excel’s AND -funktion <>. Du använder detta i det logiska testet, vilket gör att du kan ange två eller flera villkor att testa. Excel returnerar bara SANT om ALLA tester är sanna.

Så du implementerade din ränta. Men några av dina stammisar klagar. De har alltid betalat sina flikar tidigare, varför slår du ner på dem nu? Du kommer på en lösning: du tar inte ut ränta till vissa betrodda kunder.

Du skapar en ny kolumn i kalkylarket för att identifiera betrodda kunder och uppdaterar ditt IF -uttalande med en OCH -funktion:

1	= OM (OCH (C4> D4, F4 = "Nej"), C4*0,03,0)

Låt oss titta på AND -delen separat:

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

Observera de två villkoren:

• C4> D4: kontrollera om de överskrider flikgränsen, som tidigare
• F4 = ”Nej”: det här är den nya biten som kontrollerar om de inte är en betrodd kund

Så nu returnerar vi bara räntan om kunden är över sin flik, OCH vi har "Nej" i den betrodda kundkolumnen. Dina stamgäster är glada igen.

Läs mer på huvudsidan för Excel OCH -funktionen <>.

Använda IF med OR

OR är en annan av Excels logiska funktioner. Liksom AND gör det det möjligt att definiera mer än ett villkor. Men till skillnad från AND kommer den att returnera SANT om NÅGON av de tester du definierar är sanna.

Kanske kunder som är över deras flik inte är den enda anledningen till att du skulle klippa av dem. Kanske ger du vissa människor ett tillfälligt förbud av andra skäl, spelande i lokalen kanske.

Så du lägger till en ny kolumn för att identifiera förbjudna kunder och uppdaterar din "Avstängning?" kolumn med ett ELLER -test:

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

Tittar bara på OR -delen:

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

Det finns två villkor:

• C4> D4: kontrollera om de överskrider flikgränsen
• F4 = ”Ja”: den nya delen, kontrollerar om de för närvarande är förbjudna

Detta kommer att utvärderas till sant om de är över deras flik, eller om det finns ett "Ja" i kolumn E. Som du kan se är Harry avstängd nu, även om han inte har överskridit flikgränsen.

Läs mer på huvudsidan för Excel ELLER -funktionen <>.

Använda IF med XOR

XOR är en annan logisk funktion, som returnerar “Exclusive Or”. Detta är lite mindre intuitivt än de tidigare vi har diskuterat.

I enkla fall definierar du två villkor och XOR returnerar:

• SANT om något av argumenten är sant (samma som ett normalt ELLER)
• FALSKT om båda argumenten är sanna
• FALSKT om båda argumenten är falska

Ett exempel kan göra detta tydligare. Tänk dig att du vill börja ge månatliga bonusar till din personal:

• Om de säljer över 800 dollar i mat eller över 800 dollar i drycker ger du dem en halv bonus
• Om de säljer över $ 800 i båda ger du dem en full bonus
• Om de säljer under 800 dollar i båda får de ingen bonus.

Du vet redan hur du ska träna om de får hela bonusen. Du skulle bara använda IF med AND, som beskrivits tidigare.

1	= OM (OCH (C4> 800, D4> 800), "Ja", "Nej")

Men hur skulle du räkna ut vem som får halv bonus? Det är där XOR kommer in:

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

Som du kan se var Woodys dryckesförsäljning över $ 800, men inte matförsäljning. Så han får halva bonusen. Det omvända gäller för Coach. Diane och Carla sålde mer än 800 dollar för båda, så att de inte får en halv bonus (båda argumenten är SANN), och Rebecca gjorde under tröskeln för båda (båda argumenten FALSK), så formeln returnerar "Nej" igen.

Läs mer på huvudsidan för Excel XOR -funktionen <>.

Använda IF med NOT

NOT är ännu en av Excel logiska funktioner, som ofta används med IF.

INTE vänder resultatet av ett logiskt test. Med andra ord kontrollerar den om ett villkor inte har uppfyllts.

Du kan använda den med IF så här:

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

Här har vi en tabell med data om några filmer från 1980 -talet. Vi vill identifiera filmer som släpptes 1985 eller senare, som inte regisserades av Steven Spielberg.

Eftersom NOT är kapslad inom en OCH -funktion kommer Excel att utvärdera det först. Resultatet används sedan som en del av OCH.

Läs mer på huvudsidan i Excel NOT -funktionen <>.

Kapslade IF -uttalanden

Du kan också returnera ett IF -uttalande i ditt IF -uttalande. Detta gör att du kan göra mer komplexa beräkningar.

Låt oss gå tillbaka till våra kunders bord. Tänk dig att du vill klassificera kunder utifrån deras skuldnivå till dig:

• $ 0: Inga
• Upp till $ 500: Låg
• $ 500 till $ 1000: Medium
• Över $ 1000: Högt

Du kan göra detta genom att "häcka" IF -uttalanden:

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

Det är lättare att förstå om du lägger IF -uttalandena på separata rader (ALT + ENTER på Windows, CTRL + COMMAND + ENTER på Mac):

12345

=OM (C4 = 0, "Ingen",OM (C4 <= 500, "Låg",OM (C4 <= 1000, "Medium",IF (C4> 1000, "High", "Okänt"))))

OM C4 är 0 returnerar vi “None”. Annars går vi till nästa IF -uttalande. OM C4 är lika med eller mindre än 500, returnerar vi “Låg”. Annars går vi vidare till nästa IF -uttalande … och så vidare.

Förenkla komplexa IF -uttalanden med hjälpkolumner

Om du har flera kapslade IF -uttalanden och du också använder logiska funktioner kan dina formler bli mycket svåra att läsa, testa och uppdatera.

Detta är särskilt viktigt att tänka på om andra människor kommer att använda kalkylbladet. Det som är vettigt i ditt huvud kanske inte är så uppenbart för andra.

Hjälpkolumner är en bra väg runt detta problem.

Du är en analytiker på finansavdelningen i ett stort företag. Du har blivit ombedd att skapa ett kalkylblad som kontrollerar om varje anställd är berättigad till företagspension.

Här är kriterierna:

Så om du är under 55 år måste du ha 30 års tjänst under ditt bälte för att vara berättigad. Om du är mellan 55 och 59 år behöver du 15 års tjänst. Och så vidare, upp till 65 år, där du är behörig oavsett hur länge du har arbetat där.

Du kan använda ett enda komplext IF -uttalande för att lösa detta problem:

1	= IF (OR (F4> = 65, AND (F4> = 62, G4> = 5), AND (F4> = 60, G4> = 10), AND (F4> = 55, G4> = 15), G4 > 30), "Kvalificerad", "Ej kvalificerad")

Oj! Lite svårt att få huvudet runt det, eller hur?

Ett bättre tillvägagångssätt kan vara att använda hjälparkolumner. Vi har fem logiska tester här, motsvarande varje rad i kriterietabellen. Detta är lättare att se om vi lägger till radbrytningar i formeln, som vi diskuterade tidigare:

12345678

= OM (ELLER(F4> = 65,OCH (F4> = 62, G4> = 5),OCH (F4> = 60, G4> = 10),OCH (F4> = 55, G4> = 15),G4> 30), "Kvalificerad", "Inte berättigad")

Så vi kan dela upp dessa fem tester i separata kolumner och sedan helt enkelt kontrollera om någon av dem är sanna:

Varje kolumn i tabellen från E till I innehåller alla våra kriterier separat. Sedan i J4 har vi följande formel:

1	= IF (COUNTIF (E4: I4, TRUE), "Kvalificerad", "Inte kvalificerad")

Här har vi en IF -sats och det logiska testet använder COUNTIF <> för att räkna antalet celler inom E4: I4 som innehåller TRUE.

Om COUNTIF inte hittar ett TRUE -värde returnerar det 0, vilket IF tolkar som FALSE, så IF returnerar "Eligible".

Om COUNTIF hittar några SANT -värden returnerar det antalet. OM tolkar alla andra tal än 0 som SANT, så returnerar det "Kvalificerat".

Att dela upp de logiska testerna på detta sätt gör formeln lättare att läsa, och om något går fel är det mycket lättare att upptäcka var felet är.

Använda gruppering för att dölja hjälpkolumner

Hjälpkolumner gör formeln lättare att hantera, men när du väl har fått dem på plats och du vet att de fungerar korrekt tar de ofta bara plats i kalkylarket utan att lägga till användbar information.

Du kan dölja kolumnerna, men det kan leda till problem eftersom dolda kolumner är svåra att upptäcka om du inte tittar noga på kolumnrubrikerna.

Ett bättre alternativ är gruppering.

Välj de kolumner du vill gruppera, i vårt fall E: I. Tryck sedan på ALT + SKIFT + HÖGERPIL i Windows eller KOMMANDO + SKIFT + K på Mac. Du kan också gå till fliken "Data" på menyfliksområdet och välja "Gruppera" i avsnittet "Kontur".

Du ser gruppen ovanför kolumnrubrikerna, så här:

Tryck sedan helt enkelt på knappen “-“ för att dölja kolumnerna:

IFS -funktionen

Kapslade IF -uttalanden är mycket användbara när du behöver utföra mer komplexa logiska jämförelser, och du måste göra det i en cell. De kan dock bli komplicerade när de blir längre, och de kan vara svåra att läsa och uppdatera på din skärm.

Från Excel 2022 och Excel 365 introducerade Microsoft en annan funktion, IFS, för att göra det lite enklare att hantera. Det kapslade IF -exemplet ovan kan uppnås med IFS så här:

1234567

= IFS (C4 = 0, "Ingen",C4 <= 500, "Låg",C4 <= 1000, "Medium",C4> 1000, "Hög",SANT, "Okänt",)

Du kan läsa allt om det på huvudsidan för Excel IFS -funktionen <>.

Använda IF med villkorlig formatering

Med Excel villkorlig formateringsfunktion kan du formatera en cell på olika sätt beroende på dess innehåll. Eftersom IF returnerar olika värden baserat på vårt logiska test, kanske vi vill använda villkorlig formatering för att göra dessa olika värden lättare att se.

Så låt oss gå tillbaka till vårt personalbonusbord från tidigare.

Vi återvänder ”Ja” eller ”Nej” beroende på vilken bonus vi vill ge. Detta berättar vad vi behöver veta, men informationen hoppar inte över oss. Låt oss försöka fixa det.

Så här gör du:

• Välj cellområdet som innehåller dina IF -uttalanden. I vårt fall är det E4: F8.
• Klicka på "Villkorlig formatering" i avsnittet "Stilar" på fliken "Hem" på menyfliksområdet.
• Klicka på "Markera celleregler" och sedan på "Lika med".
• Skriv "Ja" (eller vilket returvärde du behöver) i den första rutan och välj sedan den formatering du vill ha från den andra rutan. (Jag väljer grönt för detta).
• Upprepa för alla dina returvärden (jag ställer också in "No" -värden till rött)

Här är resultatet:

Använda IF i matrisformler

En matris är ett intervall av värden, och i Excel representeras matriser som kommaseparerade värden som är inneslutna i hängslen, till exempel:

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

Det fina med matriser är att de gör att du kan utföra en beräkning av varje värde i intervallet och sedan returnera resultatet. Till exempel tar SUMPRODUCT -funktionen två matriser, multiplicerar dem tillsammans och summerar resultaten.

Så denna formel:

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

… Returnerar 32. Varför? Låt oss arbeta igenom det:

12345

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

Vi kan föra in ett IF -uttalande i denna bild, så att var och en av dessa multiplikationer bara händer om ett logiskt test returnerar sant.

Ta till exempel dessa data:

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

Obs! I Excel 2022 och tidigare måste du trycka på CTRL + SKIFT + ENTER för att göra detta till en matrisformel.

Vi skulle sluta med något så här:

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

På engelska, om namnet i kolumn C är lika med det som finns i G2 ("Olivia"), DO multiplicera värdena i kolumnerna D och E för den raden. Annars multiplicera dem inte. Sammanfatta sedan alla resultat.

Du kan lära dig mer om denna formel på huvudsidan för SUMPRODUCT IF Formula <>.

OM i Google Kalkylark

IF -funktionen fungerar exakt samma i Google Kalkylark som i Excel:

Ytterligare anmärkningar

Använd IF -funktionen för att testa om ett villkor är SANT. Om villkoret är SANT, gör en sak. Om det är FALSKT gör du ett annat. Villkoret måste vara ett logiskt uttryck (ex: a1> 5), en referens till en cell som innehåller TRUE eller FALSE eller en array som innehåller alla logiska värden.

IF -funktionen kan bara testa ett tillstånd i taget. Du kan dock "häcka" andra logiska funktioner inom IF -villkoret för att testa flera villkor samtidigt:

= om (AND (a1> 0, a2> 0), TRUE, FALSE)
= if (OR (a1> 0, a2> 0), TRUE, FALSE)
= om (XOR (a1> 0, a2> 0), TRUE, FALSE)
ELLER Funktionstest om en eller flera villkoren är uppfyllda.
OCH Funktioner testar om Allt villkoren är uppfyllda.
XOR -funktioner testar om en och bara en villkor är uppfyllda.

Du kan också "häcka" en IF -funktion inom en IF -funktion:

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

Nu några konkreta exempel på hur IF -funktionen fungerar i praktiken:

1. Starta en ny arbetsbok.

2. I cell A1 anger du värdet 10 (och trycker på Retur)

3. Ange sedan följande formel i cell B1:

1	= OM (A1> 5, "STÖRRE ÄN 5", "MINDRE ÄN 5")

4. Skärmen ska se ut så här nu:

5. Om du har angett formeln korrekt ser du meddelandet "Större än 5" visas i cell B1.

6. Formeln du har angett i cell B1 utför testet "A1> 5", dvs det kontrollerar om värdet i cell A1 är större än 5. För närvarande är värdet i cell A1 10 - så villkoret är SANT och meddelandet “BIGGER THAN 5” visas

7. Om vi ​​nu ändrar värdet i cell A1 till 2:

Thtn meddelandet i cell B2 är nu "MINDRE ÄN 5" eftersom villkoret är FALSKT.

8. Du kan fortsätta ändra värdet i cell A1 och meddelandet i cell B2 kommer att anpassas därefter.

9. Naturligtvis finns det situationer där tillståndet kan ge busiga resultat:

• Vad händer om vi anger värde 5 i cell A1?

• Vad sägs om vi lämnar Cell A1 tomt?

• Vad sägs om vi lägger in lite text i Cell A1, t.ex. frasen DOG

Mer om Excel IF -funktionen

Vi kommer nu att titta närmare på IF -funktionen. Den kan användas för att analysera stora mängder data mycket enkelt.

Tänk dig att du är Area Sales Manager och har ett Sales Team. Du kan registrera den totala försäljningen som varje person gör i ett enkelt Excel -kalkylblad:

Antag att kriterierna för en bonus var att försäljningen av den personen översteg £ 40 000. Du kan bara "ögonkula" data och bestämma att endast Anton, Newton och Monique nådde målet.

Det här är ganska enkelt när du bara har en handfull namn. Men om du har flera finns det utrymme för fel. Lyckligtvis kan det göras mycket snabbare och säkrare med hjälp av Excel IF -funktion.

Skapa en ny arbetsbok och skriv in data enligt ovan. Skriv sedan i cell D4 följande formel:-

1	= OM (C4> 40000, "BONUSBETALNING", "INGEN BONUS")

så att du har:

Observera hur Excel visar strukturen för IF -formeln - vilket är en användbar hjälpmemoire.

Efter att ha skrivit in formeln trycker du på ENTER och du ser det utvärdera för första raden:

Formeln har utvärderats för Martin - eftersom han tjänade mindre än 40 000 pund har han inte rätt till någon bonus.

Och sedan drar vi formlerna ner genom att klicka i det nedre högra hörnet och dra ner kan vi avgöra om varje person har rätt till en bonus:

Och vi ser att Excel har bestämt vilka av säljarna som har rätt till en bonus.

Återgå till listan över alla funktioner i Excel

VBA IF -uttalanden

Du kan också använda If -uttalanden i VBA. Klicka på länken för att lära dig mer, men här är ett enkelt exempel:

1234567

Sub Test_IF ()If Range ("a1"). Värde <0 dåOmråde ("b1"). Värde = "Negativt"Avsluta omAvsluta om

Denna kod testar om ett cellvärde är negativt. Om så är fallet kommer det att skriva "negativt" i nästa cell.