Функцыя IF у Excel. Прыклады (з некалькімі ўмовамі)

Адным з галоўных пераваг электронных табліц Excel з'яўляецца магчымасць праграмаваць функцыянальнасць канкрэтнага дакумента. Як вядома большасці людзей са школьных урокаў інфарматыкі, адным з асноўных кампанентаў, якія дазваляюць рэалізаваць гэта на практыцы, з'яўляюцца лагічныя аператары. Адзін з іх - аператар IF, які прадугледжвае выкананне пэўных дзеянняў пры выкананні пэўных умоў. 

Напрыклад, калі значэнне супадае з пэўным, то ў вочку адлюстроўваецца адна пазнака. Калі не, то зусім іншае. Давайце больш падрабязна разгледзім гэта эфектыўны сродак на практыцы.

Функцыя IF у Excel (агульная інфармацыя)

Любая праграма, нават калі яна невялікая, абавязкова змяшчае паслядоўнасць дзеянняў, якая называецца алгарытмам. Гэта можа выглядаць так:

1. Праверце ўвесь слупок А на наяўнасць цотных лікаў.
2. Калі знойдзены цотны лік, складзеце такія-то значэння.
3. Калі цотная лічба не знойдзена, то вывесці надпіс «не знойдзена».
4. Праверце, ці цотны атрыманы лік. 
5. Калі так, то дадайце яго да ўсіх цотных лікаў, выбраных у параграфе 1.

І нават калі гэта толькі гіпатэтычная сітуацыя, якая наўрад ці спатрэбіцца ў рэальным жыцці, выкананне любой задачы абавязкова мае на ўвазе наяўнасць падобнага алгарытму. Перад выкарыстаннем функцыі КАЛІ, у галаве неабходна мець дакладнае ўяўленне аб тым, якога выніку вы хочаце дасягнуць. 

Сінтаксіс функцыі КАЛІ з адной умовай

Любая функцыя ў Excel выконваецца з дапамогай формулы. Шаблон, па якім дадзеныя павінны перадавацца ў функцыю, называецца сінтаксісам. У выпадку з аператарам IF, формула будзе ў такім фармаце.

=КАЛІ (лагічны_выраз, значэнне_калі_ісціна, значэнне_калі_хлусня)

Давайце паглядзім на сінтаксіс больш падрабязна:

1. Лагічны выраз. Гэта і ёсць сама ўмова, адпаведнасць або неадпаведнасць якога правярае Excel. Можна праверыць як лікавую, так і тэкставую інфармацыю.
2. Значэнне_калі_праўда. Вынік, які будзе адлюстроўвацца ў ячэйцы, калі правяраныя даныя адпавядаюць зададзеным крытэрам.
3. значэнне_калі_хлусня. Вынік, які адлюстроўваецца ў ячэйцы, калі даныя, якія правяраюцца, не адпавядаюць умове.

Вось прыклад для нагляднасці.

Тут функцыя параўноўвае ячэйку A1 з лікам 20. Гэта першы абзац сінтаксісу. Калі змест перавышае гэта значэнне, у ячэйцы, дзе была запісана формула, адлюстроўваецца значэнне «больш за 20». Калі сітуацыя не адпавядае гэтай умове – «менш або роўна 20».

Калі вы хочаце адлюстраваць тэкставае значэнне ў ячэйцы, вы павінны заключыць яго ў двукоссе.

Вось іншая сітуацыя. Для здачы экзаменацыйнай сесіі студэнты павінны здаць заліковую сесію. Школьнікі здолелі набраць залікі па ўсіх прадметах, і цяпер застаўся апошні, які аказаўся вырашальным. Наша задача — вызначыць, каго са студэнтаў дапускаюць да іспытаў, а каго — не.

Паколькі мы хочам праверыць тэкст, а не лік, першы аргумент B2=”мінус”.

Сінтаксіс функцыі IF з некалькімі ўмовамі

Часта аднаго крытэрыю недастаткова для праверкі значэння. Калі вам трэба разгледзець больш чым адзін варыянт, вы можаце ўкласці функцыі IF адно ў другое. Будзе некалькі ўкладзеных функцый.

Каб было больш зразумела, вось сінтаксіс.

=КАЛІ(лагічны_выраз, значэнне_калі_ісціна, КАЛІ(лагічны_выраз, значэнне_калі_ісціна, значэнне_калі_ісціна))

У гэтым выпадку функцыя будзе правяраць адразу два крытэрыі. Пры выкананні першай умовы вяртаецца значэнне, атрыманае ў выніку аперацыі ў першым аргументе. Калі няма, то другі крытэрый правяраецца на адпаведнасць.

Вось прыклад.

І з дапамогай такой формулы (паказана на скрыншоце ніжэй) можна прааналізаваць паспяховасць кожнага вучня.

Як бачым, тут дадалася яшчэ адна ўмова, але прынцып не змяніўся. Такім чынам, вы можаце праверыць адразу некалькі крытэрыяў.

Як пашырыць функцыянальнасць КАЛІ з дапамогай аператараў І і АБО

Час ад часу ўзнікае сітуацыя правяраць адразу на адпаведнасць некалькім крытэрам, а не выкарыстоўваць лагічныя ўкладзеныя аператары, як у папярэднім прыкладзе. Для гэтага выкарыстоўвайце альбо функцыю И або функцыі OR у залежнасці ад таго, ці трэба адпавядаць адразу некалькім крытэрам або хаця б аднаму з іх. Давайце больш падрабязна разгледзім гэтыя крытэрыі.

Функцыя КАЛІ з умовай І

Часам трэба праверыць выраз на некалькі ўмоў адначасова. Для гэтага выкарыстоўваецца функцыя І, прапісаная ў першым аргументе функцыі IF. Гэта працуе так: калі a роўна адзінцы, а a роўна 2, значэнне будзе c.

Функцыя IF з умовай «АБО».

Функцыя АБО працуе аналагічным чынам, але ў гэтым выпадку выконваецца толькі адно з умоў. Наколькі гэта магчыма, такім чынам можна праверыць да 30 умоў. 

Вось некалькі спосабаў выкарыстання функцый И и OR як аргумент функцыі IF.

Параўнанне даных дзвюх табліц

Час ад часу можна параўнаць дзве падобныя табліцы. Напрыклад, чалавек працуе бухгалтарам і яму трэба параўнаць дзве справаздачы. Ёсць і іншыя падобныя задачы, напрыклад, параўнанне кошту тавараў розных партый, затым, ацэнкі студэнтаў за розныя перыяды і г.д.

Каб параўнаць дзве табліцы, выкарыстоўвайце функцыю COUNTIF. Разгледзім яго падрабязней.

Дапусцім, у нас ёсць дзве табліцы з характарыстыкамі двух кухонных камбайнаў. І нам трэба іх параўноўваць, і колерам вылучаць адрозненні. Гэта можна зрабіць з дапамогай умоўнага фарматавання і функцыі COUNTIF. 

Наша табліца выглядае так.

Выбіраем асартымент, адпаведны тэхнічным характарыстыках першага кухоннага камбайна.

Пасля гэтага націсніце наступныя меню: Умоўнае фарматаванне – стварыць правіла – выкарыстоўваць формулу для вызначэння адфарматаваных вочак.

У выглядзе формулы для фарматавання запісваем функцыю = COUNTIF (дыяпазон для параўнання; першая ячэйка першай табліцы)=0. У якасці параўнальнага дыяпазону выкарыстоўваецца табліца з характарыстыкамі другога кухоннага камбайна.

Вы павінны пераканацца, што адрасы з'яўляюцца абсалютнымі (са знакам даляра перад імёнамі радкоў і слупкоў). Дадайце =0 пасля формулы, каб Excel шукаў дакладныя значэнні.

Пасля гэтага трэба задаць фарматаванне вочак. Для гэтага побач з узорам трэба націснуць на кнопку «Фарматаваць». У нашым выпадку мы выкарыстоўваем заліванне, таму што гэта найбольш зручна для гэтай мэты. Але вы можаце выбраць любое фарматаванне, якое хочаце.

Мы прызначылі імя слупка ў якасці дыяпазону. Гэта значна зручней, чым ўводзіць дыяпазон ўручную.

Функцыя SUMIF у Excel

Зараз пяройдзем да функцый IF, які дапаможа замяніць адразу два пункты алгарытму. Першы з іх САМСЛІ, які складае два лікі, якія адпавядаюць пэўнай умове. Напрыклад, перад намі стаіць задача вызначыць, колькі грошай трэба плаціць у месяц усім прадаўцам. Для гэтага неабходна.

1. Дадайце радок з агульным прыбыткам усіх прадаўцоў і клікніце па ячэйцы, якая будзе ўтрымліваць вынік пасля ўводу формулы. 
2. Знаходзім кнопку fx, якая знаходзіцца побач з радком для формул. Далей з'явіцца акно, у якім праз пошук можна знайсці патрэбную функцыю. Пасля выбару аператара неабходна націснуць кнопку «ОК». Але ручной увод заўсёды магчымы.
   

   

3. Далей з'явіцца акно для ўводу аргументаў функцыі. Усе значэння можна ўказаць у адпаведных палях, а дыяпазон ўвесці праз кнопку побач з імі.
   

   

4. Першы аргумент - гэта дыяпазон. Тут вы ўводзіце вочкі, якія хочаце праверыць на адпаведнасць крытэрыям. Калі казаць пра нас, то гэта пасады супрацоўнікаў. Увядзіце дыяпазон D4:D18. Ці проста выберыце цікавыя ячэйкі.
5. У полі «Крытэрыі» увядзіце пасаду. У нашым выпадку – “прадавец”. У якасці дыяпазону падсумоўвання пазначаем тыя ячэйкі, дзе пазначаны заробкі супрацоўнікаў (робім гэта як уручную, так і выдзяляем іх мышкай). Націскаем «ОК», і атрымліваем гатовую налічаную заработную плату ўсіх супрацоўнікаў, якія з'яўляюцца прадаўцамі.

Пагадзіцеся, што гэта вельмі зручна. Так?

Функцыя SUMIFS у Excel

Гэтая функцыя дазваляе вызначыць суму значэнняў, якія адпавядаюць некалькім умовам. Напрыклад, перад намі была пастаўлена задача вызначыць агульную заработную плату ўсіх кіраўнікоў, якія працуюць у паўднёвым філіяле прадпрыемства.

Дадайце радок, дзе будзе канчатковы вынік, і ўстаўце формулу ў патрэбнае вочка. Для гэтага націсніце на значок функцыі. З'явіцца акно, у якім трэба знайсці функцыю САММЕСЛІМН. Далей выбіраем яго з спісу і адкрываецца знаёмае акно з аргументамі. Але колькасць гэтых аргументаў цяпер іншая. Гэтая формула дае магчымасць выкарыстоўваць бясконцую колькасць крытэраў, але мінімальная колькасць аргументаў - пяць. 

У дыялогавым акне ўводу аргументаў можна ўказаць толькі пяць. Калі вам трэба больш крытэрыяў, то іх трэба будзе ўвесці ўручную па той жа логіцы, што і першыя два.

Разгледзім асноўныя аргументы падрабязней:

1. Дыяпазон сумавання. Ячэйкі для сумавання.
2. Дыяпазон умоў 1 – дыяпазон, які будзе правярацца на адпаведнасць пэўнаму крытэру. 
3. Умова 1 - гэта сама ўмова.
4. Дыяпазон крытэрыяў 2 - гэта другі дыяпазон, які будзе правярацца па крытэрыю.
5. Умова 2 - гэта другая ўмова.

Далейшая логіка падобная. У выніку мы вызначылі аплату працы ўсіх кіраўнікоў Паўднёвага філіяла.

Функцыя COUNTIF у Excel

Калі вам трэба вызначыць, колькі клетак падпадае пад пэўны крытэр, скарыстайцеся функцыяй COUNTIF. Дапусцім, нам трэба зразумець, колькі прадаўцоў працуе ў гэтай арганізацыі:

1. Спачатку дадайце радок з колькасцю прадаўцоў. Пасля гэтага трэба націснуць на вочка, дзе будзе адлюстроўвацца вынік.
2. Пасля гэтага трэба націснуць на кнопку «Уставіць функцыю», якая знаходзіцца ва ўкладцы «Формулы». З'явіцца акно са спісам катэгорый. Нам неабходна выбраць пункт «Поўны алфавітны спіс». У спісе нас цікавіць формула COUNTIF. Пасля таго, як мы выбіраем яго, нам трэба націснуць кнопку «ОК».
   

   

3. Пасля гэтага мы маем колькасць прадаўцоў, занятых у гэтай арганізацыі. Яе атрымалі шляхам падліку колькасці вочак, у якіх напісана слова «прадавец». Усё проста. 

Функцыя COUNTSLIM у Excel

Падобна формуле САММЕСЛІМН, гэтая формула падлічвае колькасць вочак, якія адпавядаюць некалькім умовам. Сінтаксіс падобны, але трохі адрозніваецца ад формулы САММЕСЛІМН:

1. Дыяпазон умоў 1. Гэта дыяпазон, які будзе правераны па першым крытэрыі.
2. Умова 1. Непасрэдна першы крытэр.
3. Дыяпазон умоў 2. Гэта дыяпазон, які будзе правераны па другім крытэрыі. 
4. Умова 2.
5. Умовы дыяпазону 3.

І гэтак далей.

Такім чынам, функцыя IF у Excel – не адзіны, ёсць яшчэ некалькі яго разнавіднасцяў, якія аўтаматычна выконваюць самыя звычайныя дзеянні, што значна спрашчае жыццё чалавека. 

Шмат у чым дзякуючы функцыі IF Табліцы Excel лічацца праграмуемымі. Гэта больш, чым проста калькулятар. Калі падумаць, то функцыя IF з'яўляецца краевугольным каменем любога віду праграмавання.

Такім чынам, калі вы навучыцеся працаваць з вялікімі аб'ёмамі дадзеных у Excel, навучыцца праграмаваць будзе нашмат лягчэй. Дзякуючы лагічным аператарам гэтыя вобласці сапраўды маюць шмат агульнага, хоць Excel часцей выкарыстоўваюць бухгалтары. Але механізм працы з дадзенымі шмат у чым аднолькавы. 

Функцыя ў правільных руках IF і яго разнавіднасці дазваляюць ператварыць ліст Excel ў паўнавартасную праграму, якая можа дзейнічаць па складаным алгарытмам. Разуменне таго, як працуе функцыя IF гэта першы крок да вывучэння макрасаў - наступны крок у больш гнуткай працы з электроннымі табліцамі. Але гэта ўжо больш прафесійны ўзровень.