Funktionen OM – kapslade formler och hur du undviker fallgropar

Funktionen OM – kapslade formler och hur du undviker fallgropar

Med OM-funktionen kan du göra en logisk jämförelse mellan ett verkligt värde och det värde du förväntar dig genom att testa ett villkor och returnera resultatet SANT eller FALSKT.

  • =OM(Någonting är Sant, gör då så här, gör annars något annat)

Därför kan ett OM-uttryck ha två resultat Det första resultatet är för om jämförelsen är sann och det andra är för om jämförelsen är falsk.

OM-satser är mycket robusta, och utgör grunden för många kalkylbladsmodeller, men de är också orsaken till många kalkylbladsproblem. Under idealiska förhållanden ska en OM-sats gälla för minimala villkor, till exempel man/kvinna eller ja/nej/kanske, men ibland kanske du måste utvärdera mer komplexa scenarier som kräver kapsling* av mer än 3 OM-funktioner.

* "Kapsla" refererar till att koppla ihop flera funktioner i en och samma formel.

Använd funktionen OM, en av de logiska funktionerna, för att returnera ett värde om ett villkor är sant och ett annat värde om det är falskt.

Syntax

OM(logisk_test; värde_om_sant; [värde_om_falskt])

Till exempel:

  • =OM(A2>B2;"Över budget";"OK")

  • =OM(A2=B2,B4-A4,””)

Argumentnamn

Beskrivning

logiskt_test   

(obligatoriskt)

Det villkor du vill testa.

värde_om_sant   

(obligatoriskt)

Det värde som ska returneras om resultatet av logisk_test är SANT.

värde_om_falskt   

(valfritt)

Det värde som ska returneras om resultatet av logisk_test är FALSKT.

Anmärkningar

Även om du kan kapsla upp till 64 olika OM-funktioner i Excel är det inget vi rekommenderar. Varför?

  • Det krävs mycket tankearbete för att skapa flera OM-uttryck som är korrekta och se till att deras logik ger korrekta beräkningar genom alla villkor ända till slutet. Om du inte kapslar formlerna 100 % rätt kanske de fungerar i 75 % av fallen, men returnerar oväntade resultat i 25 %. Tyvärr är chansen liten att du ska lyckas upptäcka de 25 %.

  • Flera OM-uttryck kan bli otroligt svåra att hantera. Särskilt när du går tillbaka senare och försöker ta reda på vad det var du eller någon annan ville uppnå.

Om du märker att du har en OM-sats som verkar växa utan att slutet är i sikte, är det dags att lägga ifrån sig musen och tänka ut en ny strategi.

Vi tittar på hur du ska skapa en korrekt, komplex kapslad OM-sats med flera OM-funktioner, och när det är dags att förstå att du ska använda något annat verktyg i Excel-arsenalen.

Exempel

Följande är ett exempel på en relativt vanlig kapslad OM-sats för att konvertera provresultat till motsvarande elevbetyg (enligt amerikanska bokstavsbetyg, baserade på rena kunskapsfrågor).

Komplex kapslad OM-sats – formeln i E2 är =OM(B2>97;"A+";OM(B2>93;"A";OM(B2>89;"A-";OM(B2>87;"B+";OM(B2>83;"B";OM(B2>79;"B-";OM(B2>77;"C+";OM(B2>73;"C";OM(B2>69;"C-";OM(B2>57;"D+";OM(B2>53;"D";OM(B2>49;"D-";"F"))))))))))))
  • =OM(D2>89;"A";OM(D2>79;"B";OM(D2>69;"C";OM(D2>59;"D";"F"))))

    Den här komplexa kapslade OM-satsen följer en enkel logik:

  1. Om provresultatet (i cell D2) är över 89 får eleven betyget A

  2. Om provresultatet är över 79 får eleven betyget B

  3. Om provresultatet är över 69 får eleven betyget C

  4. Om provresultatet är över 59 får eleven betyget D

  5. Annars får eleven ett F (betyget E delas inte ut)

Det här exemplet är relativt tryggt, eftersom det (i skolsystemet i USA) inte är sannolikt att sambandet mellan provresultat och betyg ändras, så den kräver inte mycket underhåll. Men tänk om du vill kunna skilja på betygen och ge A+, A och A- och så vidare? Nu behöver din OM-sats med fyra villkor skrivas till 12 villkor! Så här skulle formeln se ut:

  • =OM(B2>97;"A+";OM(B2>93;"A";OM(B2>89;"A";OM(B2>87;"B+";OM(B2>83;"B";OM(B2>79;"B";OM(B2>77;"C+";OM(B2>73;"C";OM(B2>69;"C";OM(B2>57;"D+";OM(B2>53;"D";OM(B2>49;"D";"F"))))))))))))

Den är funktionellt korrekt och fungerar som förväntat, men den tar lång tid att skriva, och ännu längre tid att testa att den gör du vill. Ett annat uppenbart problem är att du har angett poäng och motsvarande betygsgrader för hand. Vad är oddsen för att du har skrivit fel någonstans? Tänk dig nu att göra detta 64 gånger, med ännu mer komplexa villkor! Visst är det möjligt, men vill du verkligen utsätta dig för den här typen av arbete och för fel som troligen uppstår, men som är mycket svåra att upptäcka?

Tips: Varje funktion i Excel kräver en inledande och en avslutande parentes (). Excel hjälper dig att ta reda på vad som ska sitta var genom att färglägga olika delar av formeln medan du redigerar den. Om du till exempel redigerar formeln ovan och flyttar markören över en avslutande parentes ")", får motsvarande inledande parentes samma färg. Detta kan vara särskilt användbart i komplexa kapslade formler när du försöker ta reda på om du har tillräckligt med matchande parenteser.

Ytterligare exempel

Följande är ett mycket vanligt exempel på hur man beräknar försäljningsprovision utifrån uppnådda intäktsnivåer.

Formeln i cell D9 är =OM(C9>15000;20%;OM(C9>12500;17,5%;OM(C9>10000;15%;OM(C9>7500;12,5%;,OM(C9>5000;10%;0)))))
  • =OM(C9>15000;20%;OM(C9>12500;17,5%;OM(C9>10000;15%;OM(C9>7500;12,5%;OM(C9>5000;10%;0)))

Den här formeln säger att OM (C9 är större än 15 000: returnera 20 %, OM (C9 är större än 12 500: returnera 17,5 %, och så vidare...)

Även om den är anmärkningsvärt lik det föregående betygsexemplet är den här formeln är ett bra exempel på hur svårt det kan vara att underhålla stora OM-satser – vad skulle du behöva göra om din organisation bestämmer sig för att lägga till nya kompensationsnivåer, och eventuellt även ändra befintliga intäkts- eller procentandelsvärden? Du skulle ha en hel hög med arbete på ditt bord!

Tips: Du kan infoga radbrytningar i formelfältet för att göra det lättare att läsa långa formler. Tryck på Alt + Retur före den text du vill radbryta till en ny rad.

Här är ett exempel på provisionsscenariot med logiken i oordning:

Formeln i cell D9 är i fel ordning: =OM(C9>5000;10%;OM(C9>7500;12,5%;OM(C9>10000;15%;OM(C9>12500;17,5%;,OM(C9>15000;20%;0)))))

Ser du felet? Jämför ordningen på intäktsjämförelsen med föregående exempel. Åt vilket håll går den här? Jadå, den går nedifrån och upp (5 000 kr till 15 000 kr), inte tvärtom. Men varför är det ett problem? Problemet är att formeln inte går vidare efter den första utvärderingen för ett värde som är större än 5 000 kr. Anta att du har 12 500 kr i intäkter – OM-satsen returnerar 10 %, eftersom intäkterna är större än 5 000 kr, och stannar där. Detta kan vara otroligt problematiskt, eftersom den här typen av fel i många situationer inte upptäcks förrän de redan orsakat verkliga problem. Men vad kan du då göra, nu när du vet att det finns allvarliga fallgropar med komplexa kapslade OM-uttryck? I de flesta fall kan du använda funktionen LETARAD i stället för att skapa en avancerad formel med OM-funktionen. Med LETARAD måste du först skapa en referenstabell:

Formeln i cell D2 är =LETARAD(C2;C5:D17;2;SANT)
  • =LETARAD(C2;C5:D17;2;SANT)

Den här formeln säger att vi ska söka efter värdet i C2 i området C5:C17. Om värdet hittas, returneras motsvarande värde från samma rad i kolumn D.

Formeln i cell C9 är =LETARAD(B9;B2:C6;2;SANT)
  • =LETARAD(B9;B2:C6;2;SANT)

På samma sätt letar den här formeln efter värdet i cell B9 i området B2:B22. Om värdet hittas, returneras motsvarande värde från samma rad i kolumn C.

Obs!: Båda dessa LETARAD-satser använder argumentet SANT i slutet av formlerna, vilket innebär att vi vill att de ska leta efter en ungefärlig matchning. Med andra ord kommer formeln att matcha de exakta värdena i uppslagstabellen samt alla värden som ligger mellan dem. I det här fallet måste uppslagstabellerna sorteras i stigande ordning, från minsta till största.

LETARAD behandlas mycket mer detaljerat här, men det är absolut mycket enklare än en 12-nivåers OM-sats med komplex kapsling! Det finns också andra, mindre uppenbara fördelar:

  • LETARAD-referenstabeller är öppna och lätta att se.

  • Tabellvärdena kan enkelt uppdateras, och du behöver aldrig röra formeln om villkoren ändras.

  • Om du inte vill att andra ska kunna se eller redigera referenstabellen är det bara att placera den i ett annat kalkylblad.

Visste du detta?

Nu finns en IFS-funktion, som kan ersätta flera kapslade OM-satser med en enda funktion. Så istället för vårt första betygsexempel, som hade fyra kapslade OM-funktioner:

  • =OM(D2>89;"A";OM(D2>79;"B";OM(D2>69;"C";OM(D2>59;"D";"F"))))

Den kan förenklas med en enda IFS-funktion:

  • =IFS(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";SANT;"F")

Funktionen IFS är bra eftersom du inte behöver bekymra dig om alla kapslade OM-satser och parenteser.

Obs!: Den här funktionen är endast tillgänglig om du har en Office 365-prenumeration. Om du har en prenumeration på Office 365 ska du kontrollera att du har den senaste versionen av Office.

Prova Office 365 eller den senaste versionen av Excel

Behöver du mer hjälp?

Du kan alltid fråga en expert i Excel Tech Community, få support i Answers-communityn eller föreslå en ny funktion eller förbättringar på Excel User Voice.

Mer information finns i

Se en video om hur du använder OM-funktionen

Funktionen IFS (Office 365, Excel 2016 och senare)

Funktionen ANTAL.OM beräknar värden baserat på ett enda villkor

Funktionen ANTAL.OMF beräknar värden baserat på flera villkor

Funktionen SUMMA.OM summerar värden baserat på ett enda villkor

Funktionen SUMMA.OMF summerar värden baserat på flera villkor

Funktionen OCH

Funktionen ELLER

Funktionen LETARAD

Översikt över formler i Excel

Så här undviker du felaktiga formler

Använd felkontroll om du vill hitta fel i formler

Logiska funktioner

Excel-funktioner (alfabetisk ordning)

Excel-funktioner (efter kategori)

Utöka dina kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×