Håndtering av flere forhold i en IF-formel. Denne artikkelen sammenligner de tre forskjellige metodene.
Når du trenger å gjøre en betinget beregning, er IF-funksjonen svaret. Hvis da ellers. I den følgende figuren beregner en enkel IF en bonus hvis salget ditt var $ 20 000 eller mer.
Men hva skjer når to betingelser må oppfylles? De fleste mennesker hekker en IF-uttalelse i en annen, slik:
Men dette går ut av hånden hvis du har mange betingelser som må oppfylles. AND-funksjonen vil forkorte og forenkle formelen. = OG (Test, Test, Test, Test) vil bare være sant hvis alle de logiske testene er sanne. Følgende eksempel viser en kortere formel med de samme resultatene.
Hvis du liker AND, kan du finne en bruk for OR og NOT. = ELLER (Test, Test, Test, Test) vil være sant hvis noen av de logiske testene er sanne. IKKE vil reversere et svar. =NOT(True)er falsk. =NOT(False)er sant. Hvis du noen gang må gjøre noe fancy som en NAND, kan du IKKE gjøre (OG (Test, Test, Test, Test)).
Forsiktighet
Selv om Excel 2013 introduserte XOR som en eksklusiv eller, fungerer den ikke slik regnskapsførere forventer. =XOR(True,False,True,True)er sant av grunner som er for kompliserte til å forklare her. XOR teller virkelig om du har et oddetall sanne verdier. Merkelig. Virkelig rart.
Bonus Tips
Bruke boolsk logikk
Jeg dekker alltid IF i seminarene mine. Og jeg spør alltid hvordan folk ville løse problemet med to forhold. Resultatene er ofte de samme; 70-80% av menneskene bruker nestet IF og 20-30% bruker AND. Bare en gang i Virginia tilbød en kvinne fra Price Waterhouse denne formelen:
Det fungerer. Det gir samme svar som de andre formlene. Beregn bonus .02 * B4. Men multipliser deretter den bonusen med logiske tester i parentes. Når du tvinger Excel til å multiplisere et tall med sant eller usant, blir sant 1 og usant blir 0. Ethvert tall ganger 1 er seg selv. Ethvert antall ganger 0 er 0. Å multiplisere bonusen med vilkårene sikrer at bare rader som oppfyller begge vilkår blir betalt.
Det er kult. Det fungerer. Men det virker forvirrende når du først ser det. Vitsen min på seminaret er alltid: "Hvis du forlater jobben din neste måned og du hater dine medarbeidere, begynn å bruke denne formelen."
Se på video
- Den enkleste IF-funksjonen er
=IF(Logical Test,Formula if True, Formula if False) - Men hva skal du gjøre hvis du må teste to forhold?
- Mange mennesker vil gjøre det
=IF(Test 1, IF(Test 2, Formula if True, False), False) - Dette blir uhåndterlig hvis 3, 5, 17 forhold!
- Bruk i stedet
=IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) - Hvis du liker AND, bør du vurdere ELLER, IKKE for andre situasjoner
- NAND kan gjøres med NOT (AND ())
- NOR kan gjøres med NOT (OR ())
- Vær forsiktig når du bruker XOR, ettersom resultatene ikke er det du forventer
Videoutskrift
Lær Excel fra podcast, episode 2025 - Flere forhold i IF!
Jeg podcaster hele denne boken, “i” øverst til høyre tar deg til spillelisten for alle disse podcastene!
Ok, vi skal begynne med verdens mest enkle sak om IF, den fiktive salgsdirektøren kommer til oss og sier "Hei, denne måneden får alle som har mer enn $ 20000 i salg en bonus på 2%." OK, så IF-funksjonen har tre deler: En logisk test som går ut på å si "Er B4> $ 20000?" Komma, hva skal jeg gjøre hvis det er SANT? Hvis det er SANT, .02 * B4, komma, hva gjør det hvis det er FALSE? Vel, hvis du ikke har tjent $ 20000, er ingen bonus for deg null, ok. Ctrl + Enter for å kopiere den ned, og du ser at vi bare har bonusen på rader som var over $ 20000, denne er nær, men fortsatt ingen bonus, ok. Nå har det aldri vært en bonusplan så enkelt, ikke sant, det er alltid flere regler, så her må vi sjekke for å se om inntektene> $ 20000 OG om bruttofortjenesteprosenten> 50%.
Greit, og hvis du vet hvordan du skal løse dette, tenk på hvordan du skal løse dette, greit, og jeg kommer til å forutsi at mange av dere sier “Vel, vi skal begynne med en IF-uttalelse, og senere med en annen IF-uttalelse som denne, de nestede IF-ene. " Og så er det noen få av dere som sier "Vel, la oss gjøre et IF-utsagn, og så rett inn i parentes går vi inn i en annen funksjon som heter AND." Og så er det en måte å gjøre dette uten IF-er i det hele tatt. Ok, så jeg vil se på det gjennom disse, så her er den første, vanligste måten, den nestede IF, OK, her er hvordan folk flest ville gjort dette.
= HVIS, gjør den første testen, se om inntektene i B4> $ 20000, hvis det er, gjør en annen IF, se om bruttofortjenesteprosenten> .05? Hvis det er sant, så er begge forholdene SANTE, vi kan gjøre 0,02 * B4, ellers ingen bonus. OK, men vi er ikke ferdige, lukk innvendige parenteser, komma, og hvis den første testen ikke var sant, ingen bonus, Ctrl + Enter for å kopiere ned. Du ser at bare radene som er over $ 20000 og over bruttofortjenesteprosenten på 50% får bonusen. Greit nå, på dagen, pleide det å være denne fryktelige grensen, hvor du ikke kunne hekke mer enn 7 IF-uttalelser. Det var en smertefull dag, spesielt hvis du bare sakte tilførte forhold i løpet av månedene, og du til slutt hadde en som hadde 7 og du trengte å legge til en åttende. Greit, vel i dag kan du gå til 32, jeg tror ikke du skulle gå til 32,men hvis du bare sårt trenger å gå fra 7 til 8, så er dette en god ting, ok. Så dette er den nestede IF-utsagnstilnærmingen, når jeg gjør mine live-seminarer, gjør omtrent halvparten av rommet dette, men det er en mye, mye bedre vei å gå.
= IF og deretter umiddelbart gå inn i en funksjon kalt AND, så inne i AND setter vi alle testene: Er inntektene> 20000, komma, er en brutto profittprosent> .5. Hvis det var flere tester, fortsett å sette komma med tilleggstestene, og lukk slutten, alt til slutt må være SANT for at slutten skal være SANT. Så hvis vi kommer til dette punktet hvis slutten er SANN, 0,02 * inntekt, ellers 0, er det en kortere formel, det er lettere å komme inn, du får de samme resultatene, livet er flott.
Ok, ut av alle seminarene jeg har gjort de siste 15 årene, har det bare gått en gang noen som har slått meg med denne sprø formelen. Hun sa "Se, vi skal bare gjøre = 0,02 * inntektene, slik." Greit, beregne bonusen. Jeg er som "Whoa, vent på, det blir dyrt, du kommer til å gi bonusen til alle." Hun er som "Vent, jeg var ikke ferdig, ganger, og så vil vi i parentes sette hver tilstand, så inntektene> 20000 ganger, i parentes bruttofortjeneste prosent> .5." OK, og her er hva som skjer er at vi beregner bonusen, og deretter vurderes disse til SANN eller FALSK. Og når vi tvinger Excel til å multiplisere et SANT eller FALSK ganger et tall, blir SANT 1, alt * 1 er seg selv, FALSE blir 0! Ok, så det vi har her er 2% * inntektene * 1 * 0, hva som helst * 0 = 0, ikke sant,slik at bonusen fjernes. Dette er boolsk logikk, SANT * SANT, 1 * 1 = 1, hvis enten de er FALSE eller alle er FALSE, vil det evaluere til 0, og vi får nøyaktig det samme resultatet. Tror jeg at du bør bytte til denne? Nei, det er forvirrende, med mindre du forlater jobben din neste uke og du hater dine kolleger, så bytt gjerne til dette, ok.
Hvis du liker AND-funksjonen, er det andre funksjoner eller kontroller for å se om noen av betingelsene er SANTE, så dette eller dette eller dette, vil det returnere SANT. NOT kommer til å reversere SANT til FALSE og FALSE til SANT, noe som er nyttig når du prøver å gjøre de boolske konseptene NAND eller NOR. NAND står for ikke-og, det er SANN når minst én tilstand er FALSJ, ok. Så hvis ingen av dem er SANTE, er det flott, hvis noen få av dem er SANTE, er det bra, men så snart alle er SANTE, betaler vi ikke. NOR står for ikke-eller, det betyr at ingen av forholdene er SANTE - hvis dette skjer, eller dette skjer, eller dette skjer, ingen bonus for deg. Og så XOR, vær nå forsiktig med denne, den ble introdusert i Excel 2013, og den gjør ikke det vi som regnskapsførere synes den skal gjøre.Eksklusivt - eller betyr at bare en av testene er SANN, og den fungerer når det er to forhold. Men for elektroingeniører gjør de dette i par, så det gir ikke resultatene du kanskje tror.
OK, så her er Test 1, Test 2, Test 3, Test 4, tre av dem er SANTE, og XOR sier "Er akkurat en av disse SANNE?" Og når vi gjør dette XOR, står det "Ja, akkurat en av dem som er SANNE." og det er fordi Excel-funksjonen dupliserer driften av en veldig vanlig chip som brukes i elektroteknikk. Jeg vet, den er sjokkerende, ikke sant? Du tror at Excel bare er for regnskapsførere, men ingeniører bruker også Excel også, og de tilsynelatende la til XOR for dem og ikke for regnskapsførere. Så måten dette blir evaluert på, mens de ser på de to første, “Er en av disse 2 SANNE? Ja!" Greit, så vi får det SANTE, og så tar de svaret fra XOR på dette og sammenligner det med SANT, “Er en av disse 2 SANNE? Nei, to av dem er SANNE, så det blir FALSK! " Så tar de svaret og XOR det med det siste,så de gjør dette parvis, ikke sant? “Er en av disse 2 SANNE? Ja!" OK, så det er slik vi får det. Det viser seg at det det faktisk gjør, elektroteknikken er at dette teller hvis et merkelig antall innganger er SANT. Ikke nødvendigvis nyttig for regnskapsførere, som forventer at det skal gjøre hva XOR på engelsk betyr.
Greit, mange gode tips i denne boka, nyttige ting, og til og med denne forferdelige diskusjonen om NAND og XOR og sånne ting. Kjøp boka, så har du alle disse tipsene i håndflaten din. Sammendrag fra i dag: Enkleste IF-funksjon, = HVIS logisk test, hva skal jeg gjøre hvis den er SANN, hva du skal gjøre hvis den er FALSK, men hvis du har to forhold, hekker mange IF-setningene, men bare forestill deg om du hadde 3 , 5 eller 17 forhold å hekke. OG vil løse det, forkort det litt, så hvis du liker OG, det er også ELLER IKKE, du kan gjøre NAND, du kan gjøre NOR, men vær forsiktig når du bruker den nye Excel 2013 XOR, resultatene er kanskje ikke det du forventer.
Greit hei, jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2025.xlsx
