Лагічныя функцыі ў Excel. Усё пра прымяненне лагічных функцый у Excel

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

Яны прызначаны для праверкі адпаведнасці значэнняў зададзеным крытэрам. Калі такое супадзенне ёсць, то ў ячэйку, дзе яно запісана, уносіцца значэнне «ІСЦІНА», у выпадку неадпаведнасці – «ХЛУСНЯ». Сёння мы больш падрабязна разгледзім такія пытанні, як структура лагічных функцый, сфера іх выкарыстання.

Спіс лагічных функцый у Excel

Існуе велізарная колькасць лагічных функцый, але найбольш часта выкарыстоўваюцца наступныя:

1. TRUE,
2. ХЛУСНІЦЬ
3. IF
4. КАЛІПАМЫЛКА
5. OR
6. И
7. НЕ
8. ЕАШЫБКА
9. ІЗБАЛАНКА

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

Аператары TRUE і FALSE

Агульным для абедзвюх гэтых функцый з'яўляецца тое, што яны вяртаюць толькі адно значэнне. Сфера іх выкарыстання - выкарыстанне ў якасці кампанента іншых функцый. Як можна зразумець з назвы аператараў, функцый TRUE, и ХЛУСНІЦЬ вяртаюць значэнні TRUE, и ХЛУСНІЦЬ адпаведна.

НЕ аператар

Гэтая функцыя выкарыстоўваецца з адным аргументам і запісвае супрацьлеглае значэнне ў ячэйку. Калі вы прапусціце гэты аператар TRUE,, тады ён вернецца ХЛУСНІЦЬ і, адпаведна, праўдзівае адваротнае зацвярджэнне. Такім чынам, вынік апрацоўкі дадзеных гэтым аператарам цалкам залежыць ад таго, якія параметры яму перадаваць.

Сінтаксіс гэтага аператара наступны: =НЕ(ісціна або ілжыва).

Аператары І і АБО

Гэтыя два аператары неабходныя для таго, каб перадаць сувязь умоў выразу адзін з адным. Функцыя И выкарыстоўваецца, каб паказаць, што два крытэрыі павінны адпавядаць аднаму нумару або тэксту адначасова. Гэтая функцыя вяртае значэнне TRUE, толькі пры ўмове, што ўсе крытэрыі даюць гэта значэнне адначасова. Калі хаця б адзін крытэрый не выконваецца, уся паслядоўнасць вяртае значэнне ХЛУСНІЦЬ.

Спосаб стварэння аператара І вельмі просты: =І(аргумент1; аргумент2; …). Максімальная колькасць аргументаў, якія можа выкарыстоўваць гэтая функцыя - 255. Сінтаксіс аператара OR падобныя, але механіка працы трохі адрозніваецца. Калі адна з функцый са спісу дае вынік TRUE,, то гэты лік будзе вернуты як цэлая лагічная паслядоўнасць.

Аператары IF і ISERROR

Гэтыя дзве функцыі маюць вельмі важнае прызначэнне - яны непасрэдна задаюць крытэрый, на адпаведнасць якому трэба правяраць пэўны выраз. Для больш глыбокага разумення таго, як працуе аператар КАЛІПАМЫЛКА, вы павінны спачатку апісаць функцыю IF. Яго агульная структура крыху больш складаная, чым папярэднія: =КАЛІ(лагічны_выраз, значэнне_калі_ісціна, значэнне_калі_хлусня).

Задача гэтага аператара - ствараць самыя складаныя канструкцыі. Ён правярае, ці выкананы крытэрыі. Калі так, то аператар вернецца TRUE,, калі не - ХЛУСНІЦЬ. Але аператар часта выкарыстоўваецца ў спалучэнні з іншымі. Напрыклад, калі ён выкарыстоўваецца як аргумент функцыі НЕ, то, адпаведна, агульная сума будзе аўтаматычна заменена на супрацьлеглую. Гэта значыць, калі ёсць супадзенне з крытэрам, то значэнне будзе вернута ХЛУСНІЦЬ. Гэта галоўная перавага лагічных функцый: іх можна камбінаваць у самых мудрагелістых формах.

Далей схема ўскладняецца. Калі па гэтым крытэры мы атрымаем вынік «TRUE», то вы можаце ўказаць тэкст, лік, якое будзе адлюстроўвацца, або функцыю, якая будзе вылічвацца. Падобным чынам вы можаце ўсталяваць вынік, які будзе адлюстроўвацца, калі вынік быў вернуты пасля апрацоўкі даных. ХЛУСНІЦЬ.

Структура аператара КАЛІПАМЫЛКА даволі падобныя, але ўсё ж некалькі адрозніваюцца. Змяшчае два неабходныя аргументы:

1. Сэнс. Правяраецца сам выраз. Калі гэта аказваецца праўдай, то гэта значэнне вяртаецца.
2. Значэнне, калі памылка. Гэта тэкст, лік або функцыя, якія будуць паказаны або выкананы, калі вынік праверкі першага аргумента быў ХЛУСНЯ.

сінтаксіс: =КАЛІПАМЫЛКА(значэнне;значэнне_калі_памылка).

Аператары ISERROW і ISEMPLAND

Першая функцыя з вышэйзгаданага змяшчае толькі адно значэнне і мае наступны сінтаксіс: =ISERROR(значэнне). Задача гэтага аператара - праверыць, наколькі добра запоўненыя вочкі (адна або ўвесь дыяпазон). Калі высвятляецца, што запаўненне было няправільным, ён вяртае сапраўдны вынік. Калі ўсё добра - хлусня. Можа выкарыстоўвацца непасрэдна ў якасці крытэрыю для іншай функцыі.

Excel можа правяраць спасылкі на наяўнасць наступных тыпаў памылак:

• #ІМЯ?;
• #N/A;
• #DEL/0!;
• #НУМАР!;
• #SO;
• #ПУСТЫ!;
• #СПАСЫЛКА!.

функцыя ІЗБАЛАНКА Увогуле, гэта неверагодна проста. Ён змяшчае толькі адзін параметр, які з'яўляецца ячэйкай/дыяпазонам для праверкі. Калі ёсць ячэйка, у якой няма ні тэксту, ні лічбаў, ні недрукаваных сімвалаў, то вяртаецца вынік TRUE,. Адпаведна, калі дадзеныя ёсць ва ўсіх вочках дыяпазону, то карыстальнік атрымлівае вынік ХЛУСНІЦЬ.

Табліца-памятка “Лагічныя функцыі ў Excel”

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

Лагічныя функцыі і прыклады рашэння задач

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

Заданне 1. Дапусцім, у нас засталася частка тавару пасля пэўнага часу продажу. Яго неабходна пераацаніць па наступных правілах: калі за 8 месяцаў яго не ўдалося прадаць, падзеліце яго кошт на 2 разы. Спачатку давайце створым дыяпазон, які апісвае зыходныя дадзеныя. Выглядае гэта так.

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

Вы можаце ўбачыць гэта ў радку формул на скрыншоце. Зараз давайце зробім некаторыя ўдакладненні. Лагічны выраз, які паказаны на скрыншоце (гэта значыць C2>=8), азначае, што тавар павінен знаходзіцца на складзе да 8 месяцаў уключна. Выкарыстоўваючы арыфметычныя аператары >=, мы вызначаем правіла больш або роўна. Пасля таго, як мы напісалі гэта ўмова, функцыя верне адно з двух значэнняў: «TRUE» або «FALSE». Калі формула адпавядае крытэру, то значэнне пасля пераацэнкі запісваецца ў вочка (ну ці перадаецца ў якасці аргумента іншай функцыі, усё залежыць ад зададзеных карыстальнікам параметраў), дзеліцца на два (для гэтага мы падзялілі цана на момант паступлення на склад на два) . Калі пасля гэтага выяўляецца, што тавар быў на складзе менш за 8 месяцаў, то вяртаецца тое ж значэнне, якое змяшчаецца ў вочку.

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

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

Заданне 2. Давайце прыменім гэты крытэрый да прадукту, які ёсць у наяўнасці. Дапусцім, калі пасля зробленай вышэй ўцэнкі яго кошт стала менш за 300 рублёў, або калі ён прастаяў без продажу больш за 10 месяцаў, то яго проста здымаюць з продажу. Формула наступная.

Давайце прааналізуем гэта. Мы выкарыстоўвалі функцыю ў якасці крытэрыю OR. Трэба прадугледзець такую ​​відэлец. Калі ў ячэйцы D2 знаходзіцца лік 10, то ў адпаведным радку слупка E аўтаматычна выводзіцца значэнне «спісана». Калі ні адзін з іх не выкананы, то проста вяртаецца пустая клетка.

Заданне 3. Скажам, у нас ёсць выбарка студэнтаў, якія спрабуюць паступіць у сярэднюю школу. Для гэтага ім неабходна здаць экзамены па некалькіх прадметах, паказаных на скрыншоце ніжэй. Для паступлення ў гэту навучальную ўстанову яны павінны набраць 12 балаў. Пры гэтым важная ўмова — бал па матэматыцы павінен быць не ніжэй за 4 балы. Стаіць задача аўтаматызаваць апрацоўку гэтых даных, а таксама скласці справаздачу аб тым, хто з студэнтаў паступіў, а хто не. Для гэтага зробім вось такую ​​табліцу.

Такім чынам, наша задача - прымусіць праграму падлічыць, колькі балаў будзе ў суме, паглядзець на прахадны вынік і правесці параўнанне. Пасля гэтых аперацый функцыя павінна змясціць вынік у ячэйку, у якую ён змяшчаецца. Тут магчымыя два варыянты: «прынята» або «не». Каб рэалізаваць гэтую задачу, увядзіце падобную формулу (проста падстаўце свае значэнні): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

З лагічнай функцыяй И мы можам пераканацца, што выконваюцца адразу дзве ўмовы. У дадзеным выпадку мы выкарыстоўвалі функцыю SUM падлічыць агульны бал. У якасці першай умовы (у першым аргументе функцыі І) мы паказалі формулу B3>=4. У гэтай графе змяшчаецца адзнака па матэматыцы, якая не павінна быць ніжэй за 4 балы.

Мы бачым шырокае прымяненне функцыі IF пры рабоце з электроннымі табліцамі. Вось чаму гэта самая папулярная лагічная функцыя, якую вам трэба ведаць у першую чаргу.

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

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

Давайце скарыстаемся табліцай, якую мы ўжо намалявалі. Для вырашэння гэтай задачы неабходна ў якасці ўмовы задаць правіла, што вочка D2 павінна быць менш сярэдняга арыфметычнага ўсяго асартыменту тавару. Калі правіла пацвярджаецца, то ў вочку, дзе прапісана гэтая формула, усталёўваецца значэнне «спісана». Калі крытэрый не выконваецца, усталёўваецца пустое значэнне. Для вяртання сярэдняга арыфметычнага існуе функцыя СЯРЭДНЯЯ.

Заданне 5. Дапусцім, нам трэба падлічыць сярэднія продажы розных тавараў у розных крамах адной маркі. Давайце зробім такую ​​табліцу.

Наша задача - вызначыць сярэдняе для ўсіх значэнняў, якое адпавядае пэўным характарыстыкам. Для гэтага выкарыстоўваем спецыяльную функцыю, якой не было ў спісе вышэй. Ён дазваляе сумяшчаць дзве функцыі СЯРЭДНЯЯ и IF. І яна пазваніла БЕЗДУШЧЫЯ. Змяшчае тры аргументы:

1. Дыяпазон для праверкі.
2. Умова, якую трэба праверыць.
3. Асерадненне дыяпазону.

У выніку атрымліваецца наступная формула (на скрыншоце).

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