IF funkcija programmā Excel. Piemēri (ar vairākiem nosacījumiem)

Viena no galvenajām Excel izklājlapu priekšrocībām ir iespēja programmēt konkrēta dokumenta funkcionalitāti. Kā lielākā daļa cilvēku zina no skolas datorzinību stundām, viens no galvenajiem komponentiem, kas ļauj to īstenot praksē, ir loģiskie operatori. Viens no tiem ir IF operators, kas nodrošina noteiktu darbību izpildi, ja ir izpildīti noteikti nosacījumi. 

Piemēram, ja vērtība atbilst noteiktai vērtībai, šūnā tiek parādīta viena etiķete. Ja nē, tas ir savādāk. Apskatīsim šo efektīvo rīku praksē sīkāk.

IF funkcija programmā Excel (vispārīga informācija)

Jebkura programma, pat ja tā ir maza, obligāti satur darbību secību, ko sauc par algoritmu. Tas var izskatīties šādi:

1. Pārbaudiet, vai visā A kolonnā nav pāra skaitļu.
2. Ja tiek atrasts pāra skaitlis, pievienojiet šādas un tādas vērtības.
3. Ja pāra skaitlis nav atrasts, parādiet uzrakstu “nav atrasts”.
4. Pārbaudiet, vai iegūtais skaitlis ir pāra skaitlis. 
5. Ja jā, pievienojiet to visiem 1. punktā atlasītajiem pāra skaitļiem.

Un pat tad, ja šī ir tikai hipotētiska situācija, kas reālajā dzīvē, visticamāk, nebūs vajadzīga, jebkura uzdevuma izpilde obligāti nozīmē līdzīga algoritma klātbūtni. Pirms funkcijas izmantošanas JA, galvā jābūt skaidram priekšstatam par to, kādu rezultātu vēlies sasniegt. 

Funkcijas IF sintakse ar vienu nosacījumu

Jebkura funkcija programmā Excel tiek veikta, izmantojot formulu. Modeli, pēc kura dati jānodod funkcijai, sauc par sintaksi. Operatora gadījumā IF, formula būs šādā formātā.

=IF (loģiskā_izteiksme, vērtība_ja_patiesa, vērtība_ja_false)

Apskatīsim sintaksi sīkāk:

1. Būla izteiksme. Tas ir pats nosacījums, kura atbilstību vai neatbilstību Excel pārbauda. Var pārbaudīt gan skaitlisko, gan teksta informāciju.
2. Vērtība_ja_patiesa. Rezultāts, kas tiks parādīts šūnā, ja pārbaudāmie dati atbilst norādītajiem kritērijiem.
3. vērtība_ja_false. Rezultāts, kas tiek parādīts šūnā, ja pārbaudāmie dati neatbilst nosacījumam.

Šeit ir piemērs skaidrības labad.

Šeit funkcija salīdzina šūnu A1 ar skaitli 20. Šī ir sintakses pirmā rindkopa. Ja saturs ir lielāks par šo vērtību, šūnā, kurā tika uzrakstīta formula, tiek parādīta vērtība “lielāka par 20”. Ja situācija neatbilst šim nosacījumam – “mazāks vai vienāds ar 20”.

Ja vēlaties šūnā parādīt teksta vērtību, tā jāievieto pēdiņās.

Šeit ir cita situācija. Lai studentiem būtu tiesības kārtot eksāmenu sesiju, ir jānokārto ieskaites sesija. Skolēniem izdevās izcīnīt kredītpunktus visos priekšmetos, un nu palicis pēdējais, kas izrādījās noteicošais. Mūsu uzdevums ir noteikt, kuri no skolēniem tiek pielaisti eksāmeniem un kuri nē.

Tā kā mēs vēlamies pārbaudīt tekstu, nevis skaitli, pirmais arguments ir B2 = "cons.".

IF funkcijas sintakse ar vairākiem nosacījumiem

Bieži vien ar vienu kritēriju nepietiek, lai pārbaudītu vērtību. Ja jums ir jāapsver vairākas iespējas, varat ligzdot funkcijas IF viens otrā. Būs vairākas ligzdotas funkcijas.

Lai padarītu to skaidrāku, šeit ir sintakse.

=IF(loģiskā_izteiksme, vērtība_ja_patiesa, IF(loģiskā_izteiksme, vērtība_ja_patiesa, vērtība_ja_false))

Šajā gadījumā funkcija vienlaikus pārbaudīs divus kritērijus. Ja pirmais nosacījums ir patiess, tiek atgriezta vērtība, kas iegūta darbības rezultātā pirmajā argumentā. Ja nē, tiek pārbaudīta otrā kritērija atbilstība.

Šeit ir piemērs.

Un ar šādas formulas palīdzību (parādīta zemāk esošajā ekrānuzņēmumā) varat analizēt katra skolēna sniegumu.

Kā redzat, šeit tika pievienots vēl viens nosacījums, taču princips nav mainījies. Tātad jūs varat pārbaudīt vairākus kritērijus vienlaikus.

Kā paplašināt IF funkcionalitāti, izmantojot UN un OR operatorus

Ik pa laikam rodas situācija, ka nekavējoties jāpārbauda atbilstība vairākiem kritērijiem, nevis jāizmanto loģiski ligzdotie operatori, kā tas bija iepriekšējā piemērā. Lai to izdarītu, izmantojiet funkciju И vai funkcija OR atkarībā no tā, vai jāatbilst vairākiem kritērijiem vienlaikus vai vismaz vienam no tiem. Apskatīsim šos kritērijus sīkāk.

Funkcija IF ar nosacījumu UN

Dažreiz jums ir jāpārbauda izteiksme vienlaikus vairākiem nosacījumiem. Šim nolūkam tiek izmantota funkcija UN, kas ierakstīta funkcijas pirmajā argumentā IF. Tas darbojas šādi: ja a ir vienāds ar vienu un a ir vienāds ar 2, vērtība būs c.

Funkcija IF ar nosacījumu “OR”.

Funkcija VAI darbojas līdzīgi, taču šajā gadījumā ir patiess tikai viens no nosacījumiem. Iespēju robežās šādā veidā var pārbaudīt līdz pat 30 apstākļiem. 

Šeit ir daži funkciju izmantošanas veidi И и OR kā funkcijas arguments IF.

Datu salīdzināšana divās tabulās

Ik pa laikam ir iespējams salīdzināt divas līdzīgas tabulas. Piemēram, cilvēks strādā par grāmatvedi un viņam jāsalīdzina divi pārskati. Ir arī citi līdzīgi uzdevumi, piemēram, dažādu partiju preču izmaksu salīdzināšana, pēc tam studentu vērtējumi par dažādiem periodiem utt.

Lai salīdzinātu divas tabulas, izmantojiet funkciju COUNTIF. Apskatīsim to sīkāk.

Pieņemsim, ka mums ir divas tabulas ar divu virtuves kombainu specifikācijām. Un mums tie ir jāsalīdzina un jāizceļ atšķirības ar krāsu. To var izdarīt, izmantojot nosacījumu formatējumu un funkciju COUNTIF. 

Mūsu galds izskatās šādi.

Mēs izvēlamies diapazonu, kas atbilst pirmā virtuves kombaina tehniskajām īpašībām.

Pēc tam noklikšķiniet uz šādām izvēlnēm: Nosacījuma formatēšana – izveido kārtulu – izmantojiet formulu, lai noteiktu formatētās šūnas.

Formatēšanas formulas veidā mēs ierakstām funkciju =COUNTIF (salīdzināmais diapazons; pirmās tabulas pirmā šūna)=0. Kā salīdzināšanas diapazons tiek izmantota tabula ar otrā virtuves kombaina funkcijām.

Jums jāpārliecinās, ka adreses ir absolūtas (ar dolāra zīmi rindu un kolonnu nosaukumiem priekšā). Aiz formulas pievienojiet =0, lai programma Excel meklētu precīzas vērtības.

Pēc tam jums jāiestata šūnu formatējums. Lai to izdarītu, blakus paraugam jānoklikšķina uz pogas “Formatēt”. Mūsu gadījumā mēs izmantojam pildījumu, jo tas ir ērtākais šim nolūkam. Bet jūs varat izvēlēties jebkuru vēlamo formatējumu.

Mēs esam piešķīruši kolonnas nosaukumu kā diapazonu. Tas ir daudz ērtāk nekā manuāla diapazona ievadīšana.

SUMIF funkcija programmā Excel

Tagad pāriesim pie funkcijām IF, kas palīdzēs nomainīt divus algoritma punktus uzreiz. Pirmais ir SUMMSLEY, kas saskaita divus skaitļus, kas atbilst noteiktam nosacījumam. Piemēram, mēs saskaramies ar uzdevumu noteikt, cik daudz naudas būtu jāmaksā mēnesī visiem pārdevējiem. Šim nolūkam tas ir nepieciešams.

1. Pievienojiet rindu ar visu pārdevēju kopējiem ienākumiem un noklikšķiniet uz šūnas, kurā būs rezultāts pēc formulas ievadīšanas. 
2. Mēs atrodam fx pogu, kas atrodas blakus formulu rindai. Pēc tam parādīsies logs, kurā, izmantojot meklēšanu, varat atrast nepieciešamo funkciju. Pēc operatora izvēles jums jānoklikšķina uz pogas “OK”. Taču manuāla ievade vienmēr ir iespējama.
   

   

3. Pēc tam parādīsies logs funkciju argumentu ievadīšanai. Visas vērtības var norādīt attiecīgajos laukos, un diapazonu var ievadīt, izmantojot blakus esošo pogu.
   

   

4. Pirmais arguments ir diapazons. Šeit ievadiet šūnas, kuru atbilstību kritērijiem vēlaties pārbaudīt. Ja mēs runājam par mums, tad tie ir darbinieku amati. Ievadiet diapazonu D4:D18. Vai vienkārši atlasiet interesējošās šūnas.
5. Laukā “Kritēriji” ievadiet pozīciju. Mūsu gadījumā – “pārdevējs”. Kā summēšanas diapazonu mēs norādām tās šūnas, kurās ir norādītas darbinieku algas (tas tiek darīts gan manuāli, gan atlasot tos ar peli). Noklikšķiniet uz “OK”, un mēs saņemam visu darbinieku, kas ir pārdevēji, gatavās aprēķinātās algas.

Piekrītu, ka tas ir ļoti ērti. Vai ne?

SUMIFS funkcija programmā Excel

Šī funkcija ļauj noteikt vērtību summu, kas atbilst vairākiem nosacījumiem. Piemēram, mums tika dots uzdevums noteikt visu uzņēmuma dienvidu filiālē strādājošo vadītāju kopējo algu.

Pievienojiet rindu, kurā būs gala rezultāts, un ievietojiet formulu vajadzīgajā šūnā. Lai to izdarītu, noklikšķiniet uz funkcijas ikonas. Parādīsies logs, kurā jums jāatrod funkcija SUMMESLIMN. Pēc tam atlasiet to sarakstā, un tiek atvērts pazīstamais logs ar argumentiem. Taču šo argumentu skaits tagad ir atšķirīgs. Šī formula ļauj izmantot bezgalīgu skaitu kritēriju, bet minimālais argumentu skaits ir pieci. 

Argumentu ievades dialoglodziņā var norādīt tikai piecus. Ja nepieciešams vairāk kritēriju, tad tie būs jāievada manuāli pēc tādas pašas loģikas kā pirmie divi.

Apskatīsim galvenos argumentus sīkāk:

1. Summēšanas diapazons. Summējamās šūnas.
2. Nosacījuma diapazons 1 – diapazons, kurā tiks pārbaudīta atbilstība noteiktam kritērijam. 
3. 1. nosacījums ir pats nosacījums.
4. 2. kritēriju diapazons ir otrais diapazons, kas tiks pārbaudīts atbilstoši kritērijam.
5. 2. nosacījums ir otrais nosacījums.

Turpmākā loģika ir līdzīga. Rezultātā noteicām visu Dienvidu filiāles vadītāju darba samaksu.

COUNTIF funkcija programmā Excel

Ja jums ir jānosaka, cik šūnu atbilst noteiktam kritērijam, izmantojiet funkciju COUNTIF. Pieņemsim, ka mums ir jāsaprot, cik daudz pārdevēju strādā šajā organizācijā:

1. Vispirms pievienojiet rindu, kurā norādīts pārdevēju skaits. Pēc tam jums jānoklikšķina uz šūnas, kurā tiks parādīts rezultāts.
2. Pēc tam jums jānoklikšķina uz pogas “Ievietot funkciju”, kas atrodama cilnē “Formulas”. Parādīsies logs ar kategoriju sarakstu. Mums ir jāizvēlas vienums “Pilns alfabētiskais saraksts”. Sarakstā mūs interesē formula COUNTIF. Kad esam to atlasījuši, mums jānoklikšķina uz pogas “OK”.
   

   

3. Pēc tam mums ir šajā organizācijā nodarbināto pārdevēju skaits. Tas tika iegūts, saskaitot šūnu skaitu, kurās ir rakstīts vārds “pārdevējs”. Viss ir vienkārši. 

COUNTSLIM funkcija programmā Excel

Līdzīgi kā formula SUMMESLIMN, šī formula uzskaita šūnu skaitu, kas atbilst vairākiem nosacījumiem. Sintakse ir līdzīga, bet nedaudz atšķiras no formulas SUMMESLIMN:

1. Nosacījuma diapazons 1. Šis ir diapazons, kas tiks pārbaudīts saskaņā ar pirmo kritēriju.
2. Nosacījums 1. Tieši pirmais kritērijs.
3. Nosacījuma diapazons 2. Šis ir diapazons, kas tiks pārbaudīts atbilstoši otrajam kritērijam. 
4. 2. nosacījums.
5. Diapazona nosacījumi 3.

Un tā tālāk.

Tātad funkcija IF programmā Excel - ne vienīgais, ir vēl vairākas tās šķirnes, kas automātiski veic visbiežāk sastopamās darbības, kas ievērojami vienkāršo cilvēka dzīvi. 

Lielā mērā funkcijas dēļ IF Excel izklājlapas tiek uzskatītas par programmējamām. Tas ir vairāk nekā vienkāršs kalkulators. Ja tā padomā, tad funkcija IF ir jebkura veida programmēšanas stūrakmens.

Tātad, ja jūs iemācīsities strādāt ar lielu datu apjomu programmā Excel, būs daudz vieglāk apgūt programmēšanu. Pateicoties loģiskajiem operatoriem, šīm jomām tiešām ir daudz kopīga, lai gan Excel biežāk izmanto grāmatveži. Bet mehānisms darbam ar datiem lielākoties ir vienāds. 

Funkcija labajās rokās IF un tās variācijas ļauj pārvērst Excel lapu par pilnvērtīgu programmu, kas var darboties pēc sarežģītiem algoritmiem. Izpratne par funkcijas darbību IF ir pirmais solis ceļā uz makro apguvi — nākamais solis elastīgākā darbā ar izklājlapām. Bet tas jau ir profesionālāks līmenis.