Excel

Kako uporabiti merila formule (50 primerov)

How Use Formula Criteria

Ena najpomembnejših veščin pri ustvarjanju uporabnih formul je ustvarjanje merila - del formule, ki odloča, kaj vključiti ali izključiti v izračunu. Ustvarjanje učinkovitih meril pa je lahko presenetljivo težavno, saj zahteva dobro razumevanje, kako Excel ravna s podatki. Če ste kdaj popoldne odpravljali težave s formulo, za katero se zdi, da bi morala 'delovati', veste, kaj mislim :)





Namen tega priročnika vam je pomagati pri oblikovanju formul, ki delujejo prvi čas.

Opomba: jezikovni jeziki bodo poudarili, da je 'kriterij' edninski in 'kriterij' množinski, vendar bom v obeh primerih uporabil 'merila', da bom stvari poenostavil.





Imena funkcij na temnem ozadju spodaj so povezave do več informacij.

Kaj naredijo merila?

Med drugim merila:

  • Neposreden logični tok z logiko IF/THEN
  • Omejite obdelavo samo na ujemajoče se vrednosti
  • Ustvarite pogojne vsote in štetja
  • Filtrirajte podatke, da izključite nepomembne podatke
  • Sprožite pravila pogojnega oblikovanja

Za lažjo predstavo si poglejmo tri primere meril v akciji.



Primer #1

Na spodnjem zaslonu F3 vsebuje to formulo:

 
= IF (E3>30,'Yes','No')

Primer meril formule #1

Prevod: Če je vrednost v E3 večja od 30, vrnite 'Da', drugače pa 'Ne'.

Tu so E3> 30 merila, ki se uporabljajo v IF, da se ugotovi, ali naj formula za vsak račun vrne 'Da' ali 'Ne'.

Primer #2

V naslednjem primeru D3 vsebuje to formulo:

 
= IF ( OR (B3='red',B3='green'),C3*1.1,C3)

Primer 2 meril formule - zvišajte ceno, če je rdeča ali zelena

pretvori dneve v mesece v

Prevod: če je B3 'rdeč' ali 'zelen', zvišajte ceno za 10%. V nasprotnem primeru vrnite prvotno ceno.

Primer #3

V tem primeru se funkcija SUMIFS uporablja za seštevanje vsote le, če je barva 'rdeča':

 
= SUMIFS (E3:E7,B3:B7,'red')

Primer meril formule #2 - SUMIF, ko je barva

Prevod: vrednosti vsote v E3: E7, če je vrednost v B3: B7 'rdeča'.

Osnove meril

Ta razdelek zajema gradnike meril formule in nekaj preprostih načinov za preverjanje, ali merila delujejo po pričakovanjih.

Kaj so merila?

Merila so logični izrazi, ki vrnejo TRUE ali FALSE, ali njihove numerične ekvivalente, 1 ali 0.

To je to.

Trik je v tem, da merila sestavite tako, da vrnejo TRUE le, če test ustreza vašim natančnim merilom. V vseh drugih primerih morajo merila vrniti vrednost FALSE ali nič. Če obvladate to idejo, imate temelje za izgradnjo in razumevanje številnih naprednih formul.

Logični operaterji

Merila pogosto uporabljajo logične operatorje, navedene v spodnji tabeli.

Operater Pomen Primer
= Enako = A1 = 10
Ni enako = A110
> Večji kot = A1> 100
< Manj kot = A1<100
> = Večji ali enak = A1> = 75
<= Manj ali enako = A1<=0

Logične operaterje je mogoče združiti na različne načine, kot je razvidno iz spodnjih primerov.

Logične funkcije

Excel ima več tako imenovanih 'logičnih funkcij', ki jih je mogoče uporabiti za izdelavo in uporabo pogojev. Spodnja tabela navaja ključne logične funkcije.

Funkcija Namen
ČE Preizkusite en pogoj neposrednega logičnega toka
IFS Preizkusite več pogojev neposrednega logičnega toka
NE Obratna merila ali rezultati
IN Preizkusite več pogojev, vrnite TRUE, če so vsi TRUE
ALI Preizkusite več pogojev, vrnite TRUE, če je vsaj eden TRUE
XOR Izključno ALI - vrnite TRUE, če eno ali drugo, ne oboje
NAPAKA Napake ujemanja in vrnitev alternativnih rezultatov

Več meril

Seveda obstaja veliko primerov, ko boste želeli uporabiti več meril. V preprostih situacijah lahko uporabite funkcije AND, OR in NOT. Tu je nekaj primerov:

 
= AND (A1>0,A1<10) // greater than 0 and less than 10 = OR (A1='red',A1='blue') // red or blue = NOT ( OR (A1='red',A1='blue')) // not red or blue = AND ( ISNUMBER (A1),A1>100) // number greater than 100

Nadomestni znaki

Excel ponuja tri 'nadomestne znake' za ujemanje besedila v formulah:

Znak Ime Namen
* Zvezdica Ujemajte nič ali več znakov
? Vprašaj Ujemite kateri koli znak
~ Oznaka naglasa Ujemanje dobesednega nadomestnega znaka

Nadomestne znake lahko uporabite samostojno ali v kombinaciji, da dobite različne vrste vedenja:

Uporaba Vedenje Se bo ujemalo
? Kateri koli znak 'A', 'B', 'c', 'z' itd.
?? Katera koli dva znaka 'AA', 'AZ', 'zz' itd.
??? Kateri koli trije znaki 'Jet', 'AAA', 'ccc' itd.
* Kateri koli liki 'jabolko', 'JABOLKO', 'A100' itd.
*th Konča se na 'th' 'kopel', 'četrti' itd.
c * Začne se z 'c' 'Mačka', 'CAB', 'cindy', 'sladkarija' itd.
? * Vsaj en znak 'a', 'b', 'ab', 'ABCD' itd.
??? - ?? 5 znakov z vezajem 'ABC-99', '100-ZT' itd.
* ~? Konča se pod vprašajem 'Halo?', 'Je kdo doma?' Itd.
*xyz* Vsebuje 'xyz' 'koda je XYZ', '100-XYZ', 'XyZ90' itd.

Tukaj je nekaj primerov uporabe nadomestnih znakov za merila v funkciji COUNTIFS.

 
= COUNTIFS (A1:A100,'*red*') // count cells that contain 'red' = COUNTIFS (A1:A100, 'www*') // count cells starting with 'www' = COUNTIFS (A1:A100,'?????') // count cells with 5 characters

Vse funkcije ne dovoljujejo nadomestnih znakov. Tu je seznam skupnih funkcij, ki opravljajo:

Upoštevajte, da je funkcija IF ne na tem seznamu. Če želite doseči nadomestno vedenje z IF, lahko kombinirate funkcije SEARCH in ISNUMBER, kot je opisano spodaj.

Merila za preskušanje

Klasičen način preizkušanja meril je, da jih zavijete v funkcijo IF. Na primer, če želimo preveriti 'rdeče' ali 'modro', lahko funkcijo OR zavijemo v IF, kot je ta:

 
= IF ( OR (B3='red',B3='blue'),'OK', '')

Merila formule - testiranje s funkcijo IF

Prevod: če je barva 'rdeča' ali 'modra', vrnite 'V redu'. V nasprotnem primeru ne vrnite ničesar.

Lahko pa tudi preskusite merila neposredno na delovnem listu kot formulo. Recimo, da želite obdelati vrednosti, ki so 80 in višje. Na spodnjem zaslonu C3 vsebuje to kopirano formulo.

 
=B3>=80

Merila formule - testiranje neposredno na delovnem listu

Prevod: vrednost v B3 je večja ali enaka 80.

Brez IF ali druge funkcije dobimo samo rezultat TRUE ali FALSE, vendar je dovolj, da preverimo, ali merila delujejo po pričakovanjih.

Pri preskušanju meril kot formule naj vas znak enakega (=) ne zavrže. Vse formule Excel se morajo začeti z znakom enakosti, zato jih je treba vključiti. Ko premaknete merila v drugo formulo, odstranite znak enakosti.

Drug način preverjanja meril je uporaba F9 za oceno veljavnih meril. Previdno izberite logični izraz in pritisnite F9. Excel bo takoj ocenil izraz in prikazal rezultat.

Video: Kako uporabiti F9 za odpravljanje napak v formuli .

Dodajanje meril v formule

Seveda v večini primerov ne želite vrniti TRUE ali FALSE v celico, želite vrniti neko drugo vrednost, ki temelji na merilih, ki vračajo TRUE ali FALSE. Če želite to narediti, preprosto odstranite znak enakosti in po potrebi dodajte merila v formulo.

V spodnjem primeru formula C3 vsebuje to formulo, ki uporablja zgornja merila kot logični test znotraj IF:

 
= IF (B3>=80,'Pass','Fail')

Dodajanje meril formuli

Prevod: če je vrednost v B3 večja ali enaka 80, vrnite 'Pass'. V nasprotnem primeru vrnite 'Neuspešno'.

Glej tudi: 23 nasvetov za formule ( video | Članek )

Primeri meril

Ta razdelek prikazuje primere, kako sestaviti merila za izpolnjevanje različnih nalog za različne vrste vsebin.

Prazno ali ne

Obstaja več načinov, kako lahko preverite, ali so celice prazne ali ne. Če želite vrniti TRUE, če je A1 prazno, lahko uporabite:

 
= ISBLANK (A1) =A1=''

Če želite obrniti logiko in preveriti, ali so celice prazne, lahko uporabite:

 
= NOT ( ISBLANK (A1)) =A1''

Drug način za preverjanje prazne celice je preverjanje številskih znakov:

 
= LEN (A1)=0

Če je število nič, je celica „prazna“. Ta formula je uporabna pri testiranju celic, ki lahko vsebujejo formule, ki vračajo prazne nize (''). ISBLANK (A1) vrne FALSE, če formula vrne prazen niz v A1, vendar LEN (A1) = 0 vrne TRUE.

Merila za besedilo

Če želite vrniti TRUE, če celica vsebuje 'rdečo', lahko uporabite:

 
=A1='red' 

Če želite obrniti logiko, lahko uporabite funkcijo NOT ali funkcijo not equator operator (), kot je ta:

 
= NOT (A1='red') =A1'red'

V vsakem primeru je besedilo IS zaprto z dvojnimi narekovaji (npr. 'Rdeče'). Če ne uporabljate narekovajev, bo Excel mislil, da se poskušate sklicevati na imenovani obseg ali funkcijo, in vrnil napako #NAME.

Merila za številke

Če želite preveriti, ali je A1 enako 5, lahko uporabite naslednja merila:

 
=A1=5 // TRUE if A1 equals 5

Tu je še nekaj primerov meril za preizkušanje številskih vrednosti:

 
=A1<100 // less than 100 =A1>=1 // greater than or equal to 0 =A10 // not equal to zero = AND (A1>0,A1<5) // greater than zero, less than 5 = MOD (A1,3)=0 // value is a multiple of 3

Številke obvestil NISO zaprte z dvojnimi narekovaji. Če številko vstavite v narekovaje, Excelu govorite, naj številko obravnava kot besedilo, zaradi česar bodo kriteriji neuporabni. Zapomnite si tudi to oblikovanje številk v Excelu vpliva le na prikaz in na noben način ne spreminja številskih podatkov. Pri oblikovanju meril za testiranje številk ne vključujte znakov dolarja ($), znakov odstotka (%) ali drugih informacij o oblikovanju.

Merila za datume

Datumi v Excelu so le številke, kar pomeni, da lahko na datum uporabljate običajne matematične operacije, če želite. Z datumi naročila v stolpcu A in datumi dobave v stolpcu B bo ta formula v stolpcu C čas dostave, daljši od 3 dni, označila kot 'pozen':

 
= IF ((B2-A2)>3,'Late','')

Excel ponuja tudi veliko število posebnih funkcij za delo z datumi. Na primer, če želite preveriti, ali je datum 'v prihodnosti', lahko uporabite funkcijo TODAY tako:

 
=A1> TODAY ()

Primer datuma meril formule - večji kot danes

Če želite preveriti, ali se datum pojavi v naslednjih 30 dneh, lahko formulo razširite na:

 
= AND (A1> TODAY (),A1<=( TODAY ()+30))

Prevod: ČE je A2 večji kot danes IN manjši ali enak danes + 30 dni, vrnite TRUE.

kako izrezati znake v

Tu je še nekaj primerov meril za datume, ob predpostavki, da A1 vsebuje veljaven datum:

 
= DAY (A1)>15 // greater than 15th = MONTH (A1)=6 // month is June = YEAR (A1) = 2019 // year is 2019 = WEEKDAY (A1)=2 // date is a Monday

Najvarnejši način za vstavljanje veljavnega datuma v merila je uporaba funkcije DATE, ki sprejme leto, mesec in dan kot ločene argumente. Tu je nekaj primerov:

 
=A1> DATE (2019,1,1) // after Jan. 1, 2019 = AND (A1>= DATE (2018,6,1),B4<= DATE (2018,8,31)) // Jun-Aug 2018

Merila za čas

Časi so delna števila v Excelu, zato lahko v nekaterih primerih uporabite preprosto matematiko za čas. Na primer, če želite preveriti, ali je čas v A1 po 12.00 (več kot 12 ur), lahko uporabite:

 
=A1>.5

To deluje, ker je 1 dan = 24 ur, torej pol dneva = 12 ur.

Za natančnejše delo ima Excel posebne funkcije za pridobivanje časa po komponentah. Na primer z uro 8:45 v celici A1:

 
= HOUR (A1) // returns 8 = MINUTE (A1) // returns 45 = SECOND (A1) // returns 0

Najvarnejši način za vstavljanje časa v merila je uporaba funkcije TIME. Tu je nekaj primerov:

 
=A1> TIME (9,15,0) // after 9:15 AM = AND (A1>= TIME (9,0,0),A1<= TIME (17,0,0)) // 9 AM to 5 PM

Merila za SUMIFS, COUNTIFS itd.

Merila za SUMIFS, COUNTIFS, AVERAGEIFS in podobne funkcije, ki temeljijo na razponu, sledijo nekoliko drugačnim pravilom. To je zato, ker so merila razdeljena na dva dela (obseg meril in merila), kar vpliva na skladnjo, kadar merila vključujejo operatorje.

Enostavna merila, ki temeljijo na enakosti, ne potrebujejo posebnega ravnanja. Operator equals (=) je impliciran, zato ga ni treba vključiti v merila:

 
= COUNTIFS (A1:A100,10) // count cells equal to 10 = COUNTIFS (A1:A100,'red') // count cells that equal 'red'

Ko pa dodamo operaterje, se stvari spremenijo:

 
= COUNTIFS (A1:A100,'>10') // count cells greater than 10 = COUNTIFS (A1:A100,'<0') // count cells less than zero

Opazite narekovaje ('') okoli meril? To je potrebno, če merila vključujejo operaterja v te funkcije.

Merila za vrste podatkov

Excel omogoča tri glavne vrste podatkov: besedilo, številke in logike. Datumi, časi, odstotki in ulomki so le številke oblikovanje številk uporabljajo za spremembo načina prikaza. Privzeto so številke poravnane v desno, besedilo poravnano v levo in logične vrednosti centrirane. Uporabnik pa lahko ročno preglasi poravnavo, zato to ni dober preskus tipa.

Excel ponuja tri funkcije, ki jih lahko uporabite za preverjanje podatkovnih tipov: ISTEXT, ISNUMBER in ISLOGICAL. Te funkcije vrnejo TRUE ali FALSE. Na spodnjem zaslonu celice D3, F3 in H3 vsebujejo te formule, prepisane navzdol:

 
= ISTEXT (B3) = ISNUMBER (B3) = ISLOGICAL (B3)

Merila formule - uporaba funkcij za testiranje podatkovnih tipov

Če želite uporabiti te funkcije kot merilo, postavite formulo na pravo mesto. Na primer, če želite preveriti, ali A1 vsebuje številko, lahko uporabite ISNUMBER kot logični test znotraj IF, kot je ta:

 
= IF ( ISNUMBER (B3),'OK','Invalid')

Opomba: Formule niso podatkovni tip, vendar lahko formule preverite s tipko Funkcija ISFORMULA :

 
= ISFORMULA (A1) // TRUE if A1 contains formula

Pridobivanje

Zgornji primeri prikazujejo osnove uporabe meril v formulah, obstaja veliko načinov, kako narediti merila bolj izpopolnjena. Ta razdelek obravnava nekaj tehnik.

Ustvarjanje spremenljivih meril

Pogosto je koristno spremeniti merila s sklicevanjem na celico na delovnem listu. Na spodnjem delovnem listu je na primer prehodni rezultat v celici E3, formula za določitev uspeha ali neuspeha pa izgleda tako:

 
= IF (B3>=$E,'Pass','Fail')

Spreminjanje meril - primer testnega rezultata

Dajanje prehodnega rezultata v celico E3 omogoča enostavno spreminjanje kadar koli brez urejanja formul. Upoštevajte, da je sklicevanje na $ E $ 3 absolutno da preprečite spremembe pri kopiranju formule.

Spreminjanje meril v COUNTIFS, SUMIFS itd.

Kot prej, če merila preizkušajo enakost, ni potrebno posebno ravnanje:

 
= COUNTIF (range,A1) // count cells equal to A1

Če pa merila vključujejo operaterje, jih boste morali uporabiti združevanje . Na primer za štetje celic večji kot A1, se morate pridružiti '>' k 'A1' tako:

 
= COUNTIF (range,'>'&A1)

Povezovanje teče najprej. Če A1 vsebuje številko 10, je to formula po združitvi:

 
= COUNTIF (range,'>10')

Upoštevajte, da je vzorec enak, kot je bilo razloženo prej - če merilo vključuje operatorje, mora biti navedeno v narekovajih ('').

Tu je še nekaj primerov uporabe združevanja v merilih:

 
= COUNTIF (range,'<'&B1) // count less than value in B1 = COUNTIF (range,''&'') // count not blank cells = COUNTIF (range,'*'&B1&'*') // count contains text in B1 = COUNTIF (range,'>'& TODAY ()) // count dates in future = COUNTIF (range,'<'& TODAY ()+7) // count up to 7 days from today

Vsebuje določeno besedilo

Ena težavna situacija je, ko želite preizkusiti, ali je celica vsebuje določeno besedilo. Za funkcije, ki podpirajo nadomestne znake (na primer COUNTIFS, SUMIFS itd.), To lahko storite z nadomestnimi znaki. Če želite na primer šteti celice, ki vsebujejo 'rdeče' kjer koli v celici s COUNTIFS, lahko uporabite takšno zvezdico:

 
= COUNTIFS (A1:A100,'*red*')

Vendar mnoge druge funkcije (na primer funkcija IF) ne podpirajo nadomestnih znakov. V tem primeru lahko združite ISNUMBER in SEARCH, da ustvarite merila, ki celico preverijo za delno ujemanje. Na spodnjem zaslonu D3 vsebuje to formulo:

 
= ISNUMBER ( SEARCH (C3,B3))

Merila formule - celica vsebuje določeno besedilo

Ta izraz lahko uporabite kot merilo znotraj IF, kot je ta

 
= IF ( ISNUMBER ( SEARCH ('red',A1)),'red', '')

Prevod: če je 'rdeče' kjer koli v A1, vrnite 'rdeče'.

To deluje, ker SEARCH vrne številski položaj, če je najdeno 'rdeče', ISNUMBER pa vrne TRUE. Če ne, SEARCH vrne napako, ISNUMBER pa vrne FALSE. Za več podrobnosti glej to stran .

Vgnezdeni IF -ji

Formule ugnezdenega IF se pogosto uporabljajo za preverjanje več meril in vrnitev več rezultatov. Na splošno je izziv zgraditi ugnezdene IF -je tako, da merila delujejo v pravem zaporedju. Na primer, tukaj je ugnezdena formula IF, ki črkovni oceni dodeli na podlagi številske ocene:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Primer ugnezdenega IF za dodeljevanje ocen

Upoštevajte, da najprej testiramo nizke ocene, nato pa postopno višje.

Več: 19 nasvetov za ugnezdene IF -je (z alternativami)

Konstante niza v merilih

Konstante niza so trdo kodirana polja s fiksnimi vrednostmi, kot je ta: {'A', 'B', 'C'}. Včasih se lahko uporabijo kot merila za ustvarjanje enostavnih logičnih meril ALI. Na spodnjem zaslonu na primer celica F4 vsebuje to formulo:

 
= SUM ( SUMIFS (C3:C7,B3:B7,{'red','gold'}))

Merila formule z konstantami matrike

Prevod: SUM prodaje, kjer je barva 'rdeča' ALI 'zlata'.

Ker za merila podamo dve vrednosti SUMIFS, vrne dva rezultata. Funkcija SUM nato vrne vsoto obeh rezultatov.

Merila formule enostavne matrike

Formule matrike so zapletena tema, vendar so lahko merila za enostavne formule matrike precej preprosta. Klasičen primer je uporaba funkcije IF za 'filtriranje' vrednosti, ki jih je treba izključiti, obdelava rezultata z drugo funkcijo.

Na spodnjem zaslonu je formula v G4:

 
{= MAX ( IF (regions=F4,totals))}

kjer so 'regije' imenovani obseg B3: B8 in „skupaj“ je imenovano območje D3: D8.

Opomba: to je matrična formula in jo je treba vnesti s tipkama control + shift + enter.

Rezultat je najvišja vrednost za vsako regijo.

Merila formule za preprosto formulo matrike

Za merila uporabljamo izraz:

 
regions=F4

To primerja vse vrednosti regije z 'West' iz F4 in vrne naslednji rezultat matrike v logičnem testu za IF:

 
{TRUEFALSETRUEFALSETRUEFALSE}

Končni niz, ki ga vrne IF, izgleda takole:

 
{10500FALSE12500FALSE11800FALSE}

V polje vstopijo le vrednosti, povezane z regijo 'West'. Vrednosti, povezane z regijo 'East', so FALSE.

Funkcija MAX nato vrne največjo vrednost v matriki, pri čemer ne upošteva vseh vrednosti FALSE.

Napredna merila formule

Spodaj so povezave do primerov naprednejših formul. Vsaka povezava ima posnetek zaslona in popolno razlago.

Več virov formule

Naslednje povezave vsebujejo podrobnejše informacije o Excelovih formulah:

Avtor Dave Bruns


^