Логические функции – ЧАСТЬ 1

Логические  функции  применяются  для  проверки  данных  на  соблюдение  каких-либо условий. Эти функции в терминах булевой  логики (по имени известного английского математика  Джорджа  Буля)   также  часто  называют  логическими  операторами.  Они повсеместно   встречаются  в  теории  множеств,  которую  изучают  на  технических   и математических  специальностях  в  высших  учебных  заведениях.  Логические  функции возвращают  одно  из  двух  значений:  ИСТИНА  (TRUE)  или  ЛОЖЬ (FALSE). В  этом разделе  мы  подробно  рассмотрим  только  некоторые  из  предоставляемых  программой функций. Полную информацию смотрите в приложении В, «Встроенные функции Excel».

Excel  предлагает  богатый  выбор  различных  логических  функций,  часть  из  которых включена в надстройку Пакет анализа (Analysis ToolPak). Как правило, к определению истинности заданного условия в них привлекаются условные выражения. И то и другое мы уже рассматривали.

СМОТРИ ТАКЖЕ

Дополнительную информацию см. в разделе «Установка Пакета анализа» главы 13 и в раз-

деле «Условные выражения» главы 12.

Функция ЕСЛИ

Функция ЕСЛИ (IF) возвращает определенное значение в зависимости  от выполнения логического выражения. Она имеет следующий синтаксис:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

СОВЕТ : –

Если  вам  приходится  достаточно  часто  проверять  выполнение  некоторых  условий  в  от-

дельных строках или столбцах таблицы с помощью функции ЕСЛИ (IF), а потом суммировать прошедшие тест значения, используя функцию СУММ, то вы, несомненно, обрадуетесь, узнав о наличии функции СУММЕСЛИ  (SUMIF). Она значительно упростит решение этой задачи,

поскольку  обрабатывает  содержимое  только  тех  ячеек  из  заданного  диапазона,  которые

удовлетворяют             определенному             критерию.             Так,             по             формуле

=СУММЕСЛИ(С12:С27;"Да";А12:А27)  будет  подсчитана  сумма  тех  ячеек   из  диапазона  • А12:А27,  напротив  которых  в  столбце  С  стоит  слово  «Да».  То  есть  не  нужно  создавать дополнительный столбец с формулами  ЕСЛИ — все необходимые расчеты выполняются в одной ячейке. Более  подробно функция СУММЕСЛИ рассматривается в разделе «Функции СУММЕСЛИ и СЧЕТЕСЛИ» главы 17.

Записанная таким образом формула =ЕСЛИ(А6<22;5;10) выдает значение 5,  если значение в ячейке Аб меньше 22; в противном случае получается число 10. В качестве аргументов функции ЕСЛИ       можно        использовать        и         другие         функции.        Например,        формула

=ЕСПИ(СУММ(А1:А10)>0;СУММ(А1:А10);0) возвращает сумму значений в диапазоне от А1 до

А10, если эта сумма больше 0; иначе результатом становится число 0.

Аргументами   функции   ЕСЛИ   вправе   быть   и   текстовые   значения.    Так,    по   формуле

=ЕСЛИ(СУММ(А1:А10)>0;СУММ(А1:А10);"")   возвращается   пустая    строка,    если    условие А1:А10>0 не выполняется; а на выходе формулы  =ЕСЛИ(А1="Тест";100;200) будет число 100, если в ячейке А1 содержится строка Тест, или 200 — если любое другое значение. Правда, при проверке совпадения двух строк символов не учитывается регистр.

Функции И, ИЛИ и НЕ

Три дополнительные логические функции — И (AND), ИЛИ (OR) и НЕ  (NOT) — позволяют составлять сложные условные выражения. Они работают так же, как логические операторы =, >, <, >=, <= и <> и могут сочетаться с ними. Число различных аргументов функций И и ИЛИ не  должно превышать 30. Это любые другие условные выражения, массивы  или ссылки на ячейки, содержащие логические значения.  Функция НЕ имеет только один аргумент.

Предположим, требуется создать формулу, которая возвращала бы  текстовое значение Сдал, если студент по результатам экзаменов набирает средний балл выше 75 и при этом не пропустил больше 5 занятий без уважительных причин. На листе, представленном на рис. 14.1, в ячейку Н4 мы ввели формулу =ЕСЛИ(И(64<5;Р4>75);"Сдал";"Не сдал"), а затем скопировали ее в диапазон Н4:Н8. Как видно из рисунка, только один студент не был   допущен   к   экзамену,   несмотря   на   похвальную   успеваемость,   поскольку   он отсутствовал на пяти  занятиях. Но это не послужило бы препятствием, если в формулу вместо функции И вставить функцию ИЛИ. Ведь у всех студентов средний балл выше 75.

Функция ИЛИ возвращает значение ИСТИНА, если выполняется хотя бы одно условие в логическом выражении, в отличие от функции И, которая возвращает значение ИСТИНА только в том случае, когда истинны все логические выражения.

Рис. 14.1. Функция И позволяет создавать сложные логические выражения

Функция  НЕ изменяет  значение  своего  аргумента  на  противоположное  и  чаще  всего используется в сочетании с другими логическими функциями. Она возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и, наоборот, значение ЛОЖЬ, если     аргумент     —     ИСТИНА.     Например,     по     формуле     =ЕСЛИ(НЕ(А1="Не пришел");"Допущен";"Не допущен"), чтобы студент был Допущен к  экзамену, достаточно наличия в ячейке А1 значения, не равного Не пришел.

Вложенные функции ЕСЛИ

Иногда невозможно сформировать условное выражение только с  помощью логических операторов и функций И, ИЛИ и НЕ. В этих  случаях на помощь приходят вложенные функции ЕСЛИ. Так, формула:

= ЕСЛИ(А1 = 100; "Всегда" ; ЕСЛИ(И (А1>=80 ; АК100) ; "Обычно"; ЕСПИ(И(А1>=60

;А1<80) ; "Иногда";"Увы!")))

читается следующим образом: «если значение в ячейке А1 равно 100,  возвратить текст Всегда; иначе, если значение больше или равно 80, но меньше 100, возвратить — Обычно; иначе, если это значение больше или  равно 60, но меньше 80, возвратить — Иногда; и наконец, когда ни одно из условий не выполняется, резюмировать строкой Увы!».

ПРИМЕЧАНИЕ

В одной формуле допускается до семи уровней вложения функции ЕСЛИ. Так что в Excel вполне  допустима  следующая  формула  =ЕСЛИ(А1=1;3;ЕСЛИ(А1=2;6;ЕСЛИ(А1=3;  5;ЕСЛИ (А1=4;6;ЕСЛИ(А1=5;8;ЕСЛИ(А1=6;7;ЕСЛИ(А1=7;0;1))))))). Но добавление в нее еще хотя бы одной функции ЕСЛИ равносильно ошибке.

Следует  быть  внимательным  при  преобразовании  данных  из  программ,  в  которых  допускается больше семи уровней вложения функции  ЕСЛИ,  таких как Lotus 1-2-3. В этом случае  необходимо  либо  разбить  сложную  формулу  на  несколько  более  простых,  либо подойти к задаче  с  другой стороны. Хотя бы воспользоваться функциями просмотра, о которых пойдет речь далее в разделе «Функции для списков и массивов».

Другое применение логических функций

Все  логические  функции,  рассматриваемые  в  этом  разделе,  применимы  не  только  в составе других формул, но и по отдельности.  И  хотя такие функции, как И, ИЛИ, НЕ, ЕОШИБКА (ISERROR), ЕНД (ISNA) и ЕССЫЛКА (ISREF), чаще всего употребляются

вместе с функцией ЕСЛИ, они могут образовывать самостоятельные формулы. Например, формула =И(А1>А2;А2<АЗ) возвращает значение  ИСТИНА, если значение в ячейке А1 больше, чем в ячейке А2, а значение в ячейке А2 меньше, чем в ячейке A3. Подобный тип формул удобен для присвоения ячейкам какого-либо диапазона логического  значения, с целью последующего проведения специфических операций,  таких как выборка из базы данных.

Информационные функции

Эти функции можно назвать системой внутреннего контроля Excel. Они  не выполняют никаких  вычислений,  а  служат  для  получения  информации  об  отдельных  элементах интерфейса программы. Опять-таки в данной части книги мы не в состоянии представить их все, но основным характерным представителям категории информационных функций уделим должное внимание.

СМОТРИ ТАКЖЕ г

Сведения обо всех встроенных функциях Excel см. в приложении В, «Встроенные функции

Excel».

Информационные  функции  позволяют  собирать  сведения  о  содержимом  ячеек,  об  их форматировании,  а  также  проверять  тип  значений  в  ячейках  для  задания  условий  в выражениях.

Функции ТИП и ТИП.ОШИБКИ

Функция  ТИП (TYPE) сообщает  тип  значения  аргумента:  содержит  ли  ячейка  текст, число ли это, логическая величина, массив или  «ошибка».  Результат представляется в виде кода: 1 — число (или  пусто), 2 — текст, 4 — логическое значение (ИСТИНА или ЛОЖЬ), 16 — ошибка, 64 — массив. Соответственно, если ячейка А1 содержит числовое значение 100, формула =ТИП(А1) возвратит число 1. Если же в ячейке А1 хранится текст Microsoft Excel, то по этой же формуле получится значение 2.

Аналогично, функция ТИП.ОШИБКИ (ERROR.TYPE) определяет  характер ошибки в заданной ячейке и возвращает ее код. Так, код 1 —  #ПУСТ0! (#NULL!), 2 — #ДЕЛ/0! (#DIV/0!), 3 #ЗНАЧ! (#VALUE!), 4 #ССЫЛКА! (#REF!), 5 #ИМЯ! (#NAME!), 6 –

#ЧИСЛ0! (#NUM!) и 7 #Н/Д (#N/A).

Для ячеек, содержащих любые другие значения, выдается результат в  виде кода #Н/Д. Например, если в ячейке А1 находится формула, возвращающая значение #ИМЯ!, то по формуле =ТИП.0ШИБКИ(А1) мы определим ее тип как 5. Если же в ячейке А1 хранится текст Microsoft Excel, выходным значением этой формулы будет #Н/Д.

Функция СЧИТАТЬПУСТОТЫ

Функция   СЧИТАТЬПУСТОТЫ   (COUNTBLANK)  просто   подсчитывает   количество пустых  ячеек  в  заданном  диапазоне.  Она  очень  проста  в  использовании;  собственно говоря, диапазон — это единственный аргумент этой функции. Но если вам покажется,

что   она   не   учитывает   с   виду   пустые   ячейки,   не   спешите   обвинять   функцию СЧИТАТЬПУСТОТЫ в неправильном выполнении вычислений. На самом деле в ячейках могут содержаться пробелы,  формулы, возвращающие «нулевые» текстовые значения, такие как =" "  или ="", или формулы, результат вычисления по которым равен нулю, соответственно, такие ячейки не пусты и не подлежат подсчету.

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий