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')
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)
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')
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', '')
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
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')
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 ()
Č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)
Č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')
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))
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'))))
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'}))
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.
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.
- Preštejte celice, ki vsebujejo napake
- Vsota, če je vrednost ena od mnogih
- COUNTIFS z več merili in logiko OR
- Dobite n -to največjo vrednost s kriteriji
- Seštejte zgornjih n vrednosti s kriteriji
- INDEX in MATCH z več merili
Več virov formule
Naslednje povezave vsebujejo podrobnejše informacije o Excelovih formulah:
- Kako zgraditi logične formule (video)
- 19 nasvetov za ugnezdene formule IF
- 30+ formul za pogojno oblikovanje
- Osnovna formula (plačano usposabljanje)