Пример 7.17. Цель: Составить таблицу истинности функции или для случая двух аргументов.
Обсуждение: В этом примере требуется найти значения функции ИЛИ(х;у) двух переменных х и у, когда эти переменные независимо друг от друга принимают логические значения.
Решение
Действия
В ячейки N2, N3, O1, P1 с клавиатуры вводим текстовые строки ИСТИНА и ЛОЖЬ (см. предыдущий рисунок), которые воспринимаются Excel как логические константы.
Выделяем диапазон О2:Р3.
Активизируем ячейку О2 (с помощью клавиш <Tab> или <Enter>).
Вводим в ячейку О2 формулу =ИЛИ($N2;O$1).
Нажимаем клавиши <Ctrl > + <Enter>.
Комментарии:
Здесь за один шаг в ячейки диапазона О2:Р3 скопирована формула =ИЛИ($N2;O$1), введенная поначалу в ячейку О2. Результат естественен для копирования зависимых ячеек со смешанными ссылками. Например, в ячейке Р3 находится формула =ИЛИ($N3;P$1).
Казалось бы эту задачу можно решить, введя в диапазон О2:Р3 какую-либо из следующих формул массивов: {=ИЛИ(N2:N3;O1:P1)}, {=ИЛИ(N2:N3+O1:P1)} или {=ИЛИ(N2:N3*O1:P1)}, - по аналогии с тем, как это мы раньше делали для арифметических функций двух переменных. Однако результат оказывается на первый взгляд неожиданным:
,
поскольку функция =ИЛИ(ЛОЖЬ;ЛОЖЬ) (обратите внимание на ячейку Р3) должна вернуть значение ЛОЖЬ, а между тем отображаемое значение ячейки Р3 есть ИСТИНА. Почему же получен неверный результат? Вспомним, что функция ИЛИ может зависеть от 30 аргументов. В рассматриваемом случае Excel воспринимает функцию =ИЛИ(N2:N3;O1:P1) как функцию четырех аргументов, каждый из которых записывается в свое индивидуальное поле. Среди этих четырех аргументов хотя бы один есть ИСТИНА. Поэтому Excel возвращает для каждой ячейки диапазона О2:Р3 одно и то же значение ИСТИНА. Как видим, Excel «работает» правильно, но мы получили результат, которого не ожидали. А посему совет: логическую функцию ИЛИ (а также логическую функцию И и арифметические функции типа СУММ, МАКС, МИН, СРЗНАЧ и некоторые другие, в синтаксисе которых допускается большое число аргументов) в формулах массивов нужно использовать с осторожностью.
На самом деле обсуждаемая в п.2 формула массива {=ИЛИ(N2:N3;O1:P1)} (или ее “ячеечный” вариант =ИЛИ(N2:N3;O1:P1)) решает другую задачу: в некоторой ячейке вывести значение ИСТИНА, если хотя бы одна из ячеек О1, Р1, N2, N3 содержит это значение, в противном случае вывести в ней значение ЛОЖЬ. Посмотрим, каковы будут результаты. В такой постановке задачи для случая, когда зависимой является ячейка Р3, имеем
,
или (для других входных данных):
.
Вывод: Формулы массивов, использующие логическую функцию ИЛИ (а также функцию И), могут оказаться полезными при анализе каких-то отдельных ситуаций, но еще раз подчеркнем, что в таком случае надо быть предельно внимательными. Более того, опыт показывает, что в формулах массивов во избежание ошибок лучше вообще не применять логические функции И и ИЛИ.
Продолжая анализ пп.2 и 3, любопытно попробовать найти ответ на вопрос: существует ли какой-либо вариант формулы массива, позволяющий решить задачу в исходной постановке? Ответ приведен ниже:
Совет: Проанализируйте самостоятельно, как «работает» формула массива {=ЕСЛИ(N2:N3+O1:P1>0;ИСТИНА;ЛОЖЬ)}.
Функция НЕ
Функция НЕ меняет на противоположное логическое значение своего аргумента. Обычно функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что одно значение не равно некоторому другому значению, и тогда функция НЕ возвращает значение ЛОЖЬ. Если же эти значения совпадают друг с другом, то функция НЕ возвращает значение ИСТИНА.
Иллюстративные примеры
=НЕ(ЛОЖЬ) возвращает значение ИСТИНА.
=НЕ(1+1=2) возвращает значение ЛОЖЬ.
=НЕ(А1<>А2) возвращает значение ЛОЖЬ, если, например, ячейка А1 содержит число 1, а ячейка А2 содержит число 2.
=НЕ(A13<>A14) возвращает значение ЛОЖЬ, если, например, ячейка А13 содержит текст ТЕКСТ, а ячейка А14 содержит текст ТЕКС.
=НЕ(A13=A14) возвращает значение ЛОЖЬ, если, например, ячейки А13 и А14 обе содержат одинаковый текст.
=НЕ(A11<>тип) возвращает значение ошибки #ИМЯ?, если, например, в ячейке А11 содержится число 0. Это происходит потому, что Ехсеl пытается сравнить число 0 с другим числом и ищет поименованную ячейку (с именем тип), в которой возможно тоже лежит какое-то число. Не найдя имя тип в списке имен, Ехсеl сообщает об ошибке.
=НЕ(ругайся) возвращает значение ошибки #ИМЯ?.
Если в ячейке G5 «лежит» текст ругайся, то функция =НЕ(G5) возвращает значение ошибки #ЗНАЧ!.
Функция ЕСЛИ
Функция ЕСЛИ используется для условной проверки значений и формул. Синтаксис: =ЕСЛИ(Лог_выражение;Значение_если_истина; Значение_если_ложь). Лог_выражение - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ. Значение_если_истина - это значение, которое возвращается, если Лог_выражение имеет значение ИСТИНА. Если Лог_выражение имеет значение ИСТИНА и Значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть формулой. Значение_если_ложь - это значение, которое возвращается, если Лог_выражение имеет значение ЛОЖЬ. Если Лог_выражение имеет значение ЛОЖЬ и Значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь также может быть формулой.
До 7 функций ЕСЛИ включительно могут быть вложены друг в друга в качестве значений аргументов Значение_если_истина и Значение_если_ложь для конструирования проверок сложных условий.
Все аргументы функции ЕСЛИ могут быть массивами. Если какой-либо аргумент функции ЕСЛИ является массивом, то при выполнении функции ЕСЛИ вычисляется каждый элемент массива.
Иллюстративные примеры
=ЕСЛИ(И(2+2=4; 2+3=5);1;0) возвращает значение 1.
=ЕСЛИ(И(2+2=4; 2+2=5);”друг”;”недруг”) возвращает значение недруг.
Если интервал B1:B3 содержит значения ИСТИНА, ЛОЖЬ, и ИСТИНА, то функция = ЕСЛИ(И(B1:B3);ИСТИНА;ЛОЖЬ) возвращает значение ЛОЖЬ.
= ЕСЛИ(ИЛИ(1+1=1;2+2=5);5;6) возвращает значение 5.
= ЕСЛИ(ИЛИ(1+1=3;2+2=5);5;6) возвращает значение 6.
Если ячейка B4 содержит число 99, то функция = ЕСЛИ(ИЛИ(1>B4; B4>100);”1”;”3”) возвращает значение 3.
= ЕСЛИ(НЕ(А1<>А2);0;1) возвращает значение 1, если, например, ячейка А1 содержит число 1, а ячейка А2 содержит число 2.
= ЕСЛИ(НЕ(A13=A14)”нетекст”;”текст”) возвращает значение текст, если, например, ячейки А13 и А14 обе содержат одинаковый текст.
Пример 7.18. Постановка задачи: Предположим, что нужно вывести в некоторой ячейке значение другой ячейки B4, если последняя содержит число строго между 1 и 100 и текстовое сообщение Значение вне интервала в противном случае.
Решение: Если, например, ячейка B4 содержит число 104, то формула =ЕСЛИ(И(1<B4; B4<100);B4;"Значение вне интервала"), введенная в некоторую другую ячейку, возвратит текст Значение вне интервала, а если ячейка B4 содержит число 50, то эта формула возвратит число 50.
Пример 7.19. Постановка задачи: В ячейках А1, А2, А3 находятся числа 10, 6, 8 соответственно. В ячейку В1 введена формула =ЕСЛИ(РАНГ(A1;A1:A3;1)<=2;СРЗНАЧ(A3;2);МИН(A3;8;1)). Какое значение в ней отобразится?
Решение
Третий аргумент функции РАНГ указывает на то, что мы должны преобразовать исходную последовательность чисел 10, 6, 8 в возрастающую, т.е. в следующую последовательность 6,8,10. Поскольку число 10 занимает третью позицию в отсортированном по возрастанию списке 6,8,10, то функция РАНГ(A1;A1:A3;1) возвращает значение 3 (т.е. третье место для числа 10). Неравенство 3 <=2 является ложным и потому функция РАНГ(A1;A1:A3;1)<=2 возвращает значение ЛОЖЬ. Тогда функция ЕСЛИ возвращает значение, соответствующее своему третьему аргументу, т.е. вычисляемое вложенной функцией МИН(A3;8;1). В ячейке А3 находится число 8. Минимум трех чисел 8, 8, 1 равняется 1. Следовательно, в ячейке В1 отобразится значение 1.
Пример 7.20. Постановка задачи: В ячейках А1, А2, А3 находятся числа 10, 6, 10 соответственно. В ячейку В1 введена формула =ЕСЛИ(РАНГ(A1;A1:A3;1)<=2;СРЗНАЧ(A3;2);МИН(A3;8;1)). Какое значение в ней отобразится?
Решение аналогично предыдущей задаче, только отсортированный по неубыванию список будет таким 6, 10, 10 и потому два одинаковых числа 10 занимают две вторые позиции в полученной неубывающей последовательности. Поэтому функция РАНГ(A1;A1:A3;1) возвращает значение 2. Неравенство 2 <=2 является истинным и потому функция РАНГ(A1;A1:A3;1)<=2 возвращает значение ИСТИНА. Тогда функция ЕСЛИ возвращает значение, соответствующее своему второму аргументу, т.е. вычисляемое вложенной функцией СРЗНАЧ(A3;2). В ячейке А3 находится число 10. Среднее арифметическое двух чисел 10 и 2 равно 6. Следовательно, в ячейке В1 отобразится значение 6.
Пример 7.21. Постановка задачи: В ячейках А1, А2, А3 находятся числа 3, 8, 11 соответственно. В диапазон В1:В3 введена формула массива {=ЕСЛИ(A1:A3>СРЗНАЧ(A1;A3);A1:A3;0)}. Какое значение отобразится в ячейке В2?
Решение
Функция СРЗНАЧ(A1;A3) возвращает значение (3+11)/2, т.е. число 7. Поскольку нас интересует результат, возникающий в ячейке В2 диапазона В1:В3, надо провести анализ лишь второго неравенства в группе неравенств A1:A3>СРЗНАЧ(A1;A3). В ячейке А2 находится число 8. Его нужно сравнить с числом 7. Неравенство 8>7 является истинным. Следовательно, второе возвращаемое значение вложенной логической формулы массива A1:A3>СРЗНАЧ(A1;A3) есть ИСТИНА. А это значит, что второе возвращаемое значение функции ЕСЛИ соответствует второй позиции диапазона A1:A3, т.е. ячейке А2, в которой “лежит” число 8. Следовательно, в ячейке В2 отобразится значение 8.
Пример 7.22. Постановка задачи: В ячейках А1, А2, А3, А4 находятся числа 1, 4, 5, 7 соответственно. В диапазон В1:В4 введена формула массива {=ЕСЛИ(A1:A4>ТРАНСП({5;2;3;10});9;A1:A4)}. Какое значение отобразится в ячейке В4?
Решение
Функция ТРАНСП({5;2;3;10}) возвращает вертикальный одномерный массив {5:2:3:10}. Поскольку нас интересует результат, возникающий в ячейке В4 диапазона В1:В4, надо провести анализ лишь четвертого неравенства в группе неравенств A1:A4>ТРАНСП({5;2;3;10}). В ячейке А4 находится число 7. Его нужно сравнить с числом 10. Неравенство 7>10 является ложным. Следовательно, четвертое возвращаемое значение вложенной логической формулы массива A1:A4>ТРАНСП({5;2;3;10}) есть ЛОЖЬ. А это значит, что четвертое возвращаемое значение функции ЕСЛИ соответствует третьему аргументу этой функции, т.е. диапазону A1:A4. В нем нас интересует четвертая позиция - ячейка А4. В ней “лежит” число 7. Следовательно, в ячейке В4 отобразится значение 7.
Пример 7.23. Постановка задачи: Ячейка В1 содержит число 5. Какое значение возвращает функция =В$1*ЕСЛИ(В$1<10%;10%; ЕСЛИ(В1>20%;20%;30%))?
Решение
Во вложенных формулах В$1<10% и В1>20% оператор % возвращает значения 0,1 и 0,2 соответственно. Поскольку ячейка В1 содержит число 5, то первая из этих формул В$1<10% возвращает значение ЛОЖЬ, а вторая В1>20% - ИСТИНА (ведь неравенство 5< 0,1 ложно, а неравенство 5>0,2 истинно). Поэтому внешняя функция ЕСЛИ возвращает значение, которое вычисляет внутренняя функция ЕСЛИ(В1>20%;20%;30%), а та в свою очередь возвращает значение, соответствующее ее второму аргументу, т.е. число 0,2 (а не 20%, т.к. символ % во вложенной функции действует как оператор, преобразующий число 20% в число 0,2). Поэтому функция, заданная в условии задачи, возвращает значение, равное 5*0,2. Окончательный ответ 1.
Пример 7.24. Постановка задачи: Предположим, что рабочий лист содержит в ячейках B2:B7 фактические расходы (в тыс. руб.) районной администрации за первое полугодие. Ячейки C2:C7 содержат бюджетные данные за то же время. Требуется записать формулы для проверки соответствия бюджету расходов каждого месяца, генерируя в ячейках D2:D7 тексты сообщений Бюджет превышен на или Имеется резерв в. В ячейках Е2:Е7 – указать соответствующее превышение бюджета или имеющийся резерв. В ячейках F2:F7 – указать, на сколько ежемесячные расходы превышают соответствующие бюджетные поступления (+ отвечает превышению, - отвечает резерву). Рассчитать следующие итоговые величины за полугодие: