IF-funksjon i Excel. Eksempler (med flere betingelser)

En av hovedfordelene med Excel-regneark er muligheten til å programmere funksjonaliteten til et bestemt dokument. Som de fleste vet fra skoletimer i informatikk, er en av hovedkomponentene som lar deg sette dette ut i livet, logiske operatorer. En av dem er IF-operatøren, som sørger for utførelse av visse handlinger når visse betingelser er oppfylt. 

For eksempel, hvis verdien samsvarer med en bestemt, vises én etikett i cellen. Hvis ikke, er det annerledes. La oss se på dette effektive verktøyet mer detaljert i praksis.

IF-funksjon i Excel (generell informasjon)

Ethvert program, selv om det er lite, inneholder nødvendigvis en sekvens av handlinger, som kalles en algoritme. Det kan se slik ut:

1. Sjekk hele kolonne A for partall.
2. Hvis et partall blir funnet, legg til slike og slike verdier.
3. Hvis et partall ikke blir funnet, vis inskripsjonen "ikke funnet".
4. Sjekk om det resulterende tallet er partall. 
5. Hvis ja, legg det til alle partallene som er valgt i avsnitt 1.

Og selv om dette bare er en hypotetisk situasjon, som neppe vil være nødvendig i det virkelige liv, innebærer utførelsen av en oppgave nødvendigvis tilstedeværelsen av en lignende algoritme. Før du bruker funksjonen HVIS, du må ha en klar idé i hodet om hvilket resultat du ønsker å oppnå. 

Syntaks for HVIS-funksjonen med én betingelse

Enhver funksjon i Excel utføres ved hjelp av en formel. Mønsteret som data må sendes til en funksjon etter kalles syntaks. Når det gjelder operatøren IF, vil formelen være i dette formatet.

=IF (logical_expression, value_if_true, value_if_false)

La oss se på syntaksen mer detaljert:

1. boolsk uttrykk. Dette er selve betingelsen, overholdelse eller manglende overholdelse som Excel kontrollerer. Både numerisk og tekstlig informasjon kan kontrolleres.
2. Verdi_hvis_sant. Resultatet som vil vises i cellen hvis dataene som kontrolleres oppfyller de angitte kriteriene.
3. verdi_hvis_falsk. Resultatet som vises i cellen hvis dataene som kontrolleres ikke samsvarer med betingelsen.

Her er et eksempel for klarhet.

Her sammenligner funksjonen celle A1 med tallet 20. Dette er første ledd i syntaksen. Hvis innholdet er større enn denne verdien, vises verdien "større enn 20" i cellen der formelen ble skrevet. Hvis situasjonen ikke samsvarer med denne betingelsen - "mindre enn eller lik 20".

Hvis du vil vise en tekstverdi i en celle, må du sette den i anførselstegn.

Her er en annen situasjon. For å være kvalifisert til å ta eksamen, må studentene bestå en prøveøkt. Studentene klarte å vinne studiepoeng i alle fag, og nå gjenstår den siste, noe som viste seg å være avgjørende. Vår oppgave er å finne ut hvilke av studentene som blir tatt opp til eksamen og hvilke som ikke blir det.

Siden vi ønsker å se etter tekst og ikke et tall, er det første argumentet B2=”cons.”.

IF-funksjonssyntaks med flere betingelser

Ofte er ikke ett kriterium nok til å kontrollere verdien mot. Hvis du trenger å vurdere mer enn ett alternativ, kan du neste funksjoner IF den ene inn i den andre. Det vil være flere nestede funksjoner.

For å gjøre det klarere, her er syntaksen.

=HVIS(logisk_uttrykk, verdi_hvis_sant, HVIS(logisk_uttrykk, verdi_hvis_sant, verdi_hvis_falsk))

I dette tilfellet vil funksjonen sjekke to kriterier samtidig. Hvis den første betingelsen er sann, returneres verdien oppnådd som et resultat av operasjonen i det første argumentet. Hvis ikke, kontrolleres det andre kriteriet for samsvar.

Her er et eksempel.

Og ved hjelp av en slik formel (vist i skjermbildet nedenfor), kan du analysere ytelsen til hver elev.

Som du kan se, ble det lagt til en betingelse til her, men prinsippet er ikke endret. Så du kan sjekke flere kriterier samtidig.

Hvordan utvide IF-funksjonaliteten ved å bruke AND- og OR-operatorer

Fra tid til annen er det en situasjon å sjekke umiddelbart for samsvar med flere kriterier, og ikke bruke logiske nestede operatører, som i forrige eksempel. For å gjøre dette, bruk en av funksjonene И eller funksjon OR avhengig av om du må oppfylle flere kriterier samtidig eller minst ett av dem. La oss se nærmere på disse kriteriene.

HVIS-funksjon med OG-tilstand

Noen ganger må du sjekke et uttrykk for flere forhold samtidig. Til dette brukes OG-funksjonen, skrevet i det første argumentet til funksjonen IF. Det fungerer slik: hvis a er lik en og a er lik 2, vil verdien være c.

HVIS-funksjon med "ELLER"-tilstand

ELLER-funksjonen fungerer på lignende måte, men i dette tilfellet er bare én av betingelsene sann. Så mye som mulig kan opptil 30 forhold kontrolleres på denne måten. 

Her er noen måter å bruke funksjoner på И и OR som funksjonsargument IF.

Sammenligning av data i to tabeller

Fra tid til annen er det mulig å sammenligne to like tabeller. For eksempel jobber en person som regnskapsfører og må sammenligne to rapporter. Det er andre lignende oppgaver, for eksempel å sammenligne varekostnadene i forskjellige partier, deretter vurderinger av studenter for forskjellige perioder, og så videre.

For å sammenligne to tabeller, bruk funksjonen ANTALL.HVIS. La oss se på det mer detaljert.

La oss si at vi har to tabeller som inneholder spesifikasjonene til to matprosessorer. Og vi må sammenligne dem, og fremheve forskjellene med farge. Dette kan gjøres ved hjelp av betinget formatering og funksjonen ANTALL.HVIS. 

Bordet vårt ser slik ut.

Vi velger utvalget som tilsvarer de tekniske egenskapene til den første foodprosessoren.

Deretter klikker du på følgende menyer: Betinget formatering – lag en regel – bruk en formel for å bestemme de formaterte cellene.

I form av en formel for formatering skriver vi funksjonen =ANTALLHVIS (område for å sammenligne; første celle i første tabell)=0. Tabellen med funksjonene til den andre foodprosessoren brukes som et sammenligningsområde.

Du må sørge for at adressene er absolutte (med et dollartegn foran rad- og kolonnenavnene). Legg til =0 etter formelen slik at Excel ser etter eksakte verdier.

Etter det må du angi formateringen av cellene. For å gjøre dette, ved siden av prøven, må du klikke på "Format" -knappen. I vårt tilfelle bruker vi fyllet, fordi det er det mest praktiske for dette formålet. Men du kan velge hvilken som helst formatering du vil.

Vi har tildelt et kolonnenavn som et område. Dette er mye mer praktisk enn å angi rekkevidden manuelt.

SUMIF-funksjon i Excel

La oss nå gå videre til funksjonene IF, som vil bidra til å erstatte to punkter i algoritmen samtidig. Den første er SUMMESLEY, som legger til to tall som oppfyller en viss betingelse. For eksempel står vi overfor oppgaven med å bestemme hvor mye penger som skal betales per måned til alle selgere. For dette er det nødvendig.

1. Legg til en rad med den totale inntekten til alle selgere og klikk på cellen som skal inneholde resultatet etter å ha lagt inn formelen. 
2. Vi finner fx-knappen, som ligger ved siden av linjen for formler. Deretter vises et vindu hvor du kan finne den nødvendige funksjonen gjennom søket. Etter å ha valgt operatør, må du klikke på "OK"-knappen. Men manuell inntasting er alltid mulig.
   

   

3. Deretter vises et vindu for å legge inn funksjonsargumenter. Alle verdier kan spesifiseres i de tilsvarende feltene, og området kan angis via knappen ved siden av dem.
   

   

4. Det første argumentet er et område. Her skriver du inn cellene du vil sjekke for samsvar med kriteriene. Hvis vi snakker om oss, er dette stillingene til ansatte. Skriv inn området D4:D18. Eller bare velg cellene av interesse.
5. I feltet "Kriterier" skriver du inn stillingen. I tilfellet med oss ​​- "selger". Som summeringsområdet angir vi de cellene der lønnen til ansatte er oppført (dette gjøres både manuelt og velg dem med musen). Klikk "OK", og vi får ferdig beregnet lønn til alle ansatte som er selgere.

Enig i at det er veldig praktisk. Er det ikke?

SUMIFS-funksjon i Excel

Denne funksjonen lar deg bestemme summen av verdier som oppfyller flere betingelser. For eksempel fikk vi i oppgave å fastsette totallønnen til alle ledere som jobber i den sørlige grenen av selskapet.

Legg til en rad der det endelige resultatet blir, og sett inn formelen i ønsket celle. For å gjøre dette, klikk på funksjonsikonet. Et vindu vil dukke opp der du må finne funksjonen SUMMESLIMN. Deretter velger du den fra listen og det kjente vinduet med argumenter åpnes. Men antallet av disse argumentene er nå annerledes. Denne formelen gjør det mulig å bruke et uendelig antall kriterier, men minimum antall argumenter er fem. 

Bare fem kan spesifiseres gjennom argumentinntastingsdialogen. Hvis du trenger flere kriterier, må de legges inn manuelt i henhold til samme logikk som de to første.

La oss se på hovedargumentene mer detaljert:

1. Oppsummeringsområde. Celler som skal summeres.
2. Tilstandsområde 1 – området som vil bli kontrollert for samsvar med et bestemt kriterium. 
3. Tilstand 1 er selve tilstanden.
4. Kriterieområde 2 er det andre området som vil bli kontrollert mot kriteriet.
5. Tilstand 2 er den andre betingelsen.

Videre logikk er lik. Som et resultat bestemte vi lønnen til alle ledere i den sørlige grenen.

COUNTIF-funksjonen i Excel

Hvis du trenger å finne ut hvor mange celler som faller inn under et bestemt kriterium, bruk funksjonen COUNTIF. La oss si at vi må forstå hvor mange selgere som jobber i denne organisasjonen:

1. Først legger du til en linje som inneholder antall selgere. Etter det må du klikke på cellen der resultatet skal vises.
2. Etter det må du klikke på "Sett inn funksjon" -knappen, som du finner i kategorien "Formler". Et vindu vises med en liste over kategorier. Vi må velge elementet "Full alfabetisk liste". I listen er vi interessert i formelen COUNTIF. Etter at vi har valgt det, må vi klikke på "OK" -knappen.
   

   

3. Etter det har vi antall selgere ansatt i denne organisasjonen. Det ble oppnådd ved å telle antall celler der ordet "selger" er skrevet. Alt er enkelt. 

COUNTSLIM funksjon i Excel

Ligner formelen SUMMESLIMN, teller denne formelen antall celler som samsvarer med flere forhold. Syntaksen er lik, men litt forskjellig fra formelen SUMMESLIMN:

1. Tilstandsområde 1. Dette er området som skal testes mot det første kriteriet.
2. Tilstand 1. Direkte det første kriteriet.
3. Tilstandsområde 2. Dette er området som vil bli testet mot det andre kriteriet. 
4. Tilstand 2.
5. Rekkeviddeforhold 3.

Og så videre.

Så funksjonen IF i Excel – ikke den eneste, det er flere av dens varianter som automatisk utfører de vanligste handlingene, noe som i stor grad forenkler en persons liv. 

Mye på grunn av funksjonen IF Excel-regneark anses som programmerbare. Det er mer enn bare en enkel kalkulator. Hvis du tenker på det, så funksjonen IF er en hjørnestein i enhver form for programmering.

Så hvis du lærer å jobbe med store datamengder i Excel, vil det være mye lettere å lære programmering. Takket være logiske operatører har disse områdene virkelig mye til felles, selv om Excel oftere brukes av regnskapsførere. Men mekanismen for å jobbe med data er stort sett den samme. 

Funksjon i riktige hender IF og variantene lar deg gjøre et Excel-ark til et fullverdig program som kan handle på komplekse algoritmer. Forstå hvordan funksjonen fungerer IF er det første steget mot å lære makroer – neste steg i mer fleksibelt arbeid med regneark. Men dette er allerede et mer profesjonelt nivå.