Функция ЕСЛИ

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

До 7 функций ЕСЛИ включительно могут быть вложены друг в друга в качестве значений аргументов Значение_если_истина и Значение_если_ложь для конструирования проверок сложных условий.

Все аргументы функции ЕСЛИмогут быть массивами. Если какой-либо аргумент функции ЕСЛИ является массивом, то при выполнении функции ЕСЛИ вычисляется каждый элемент массива.

Иллюстративные примеры

I) =ЕСЛИ(И(2+2=4; 2+3=5); 1; 0) возвращает значение 1.

II) =ЕСЛИ(И(2+2=4; 2+2=5); ”друг”; ”недруг”) возвращает значение недруг.

III) Если интервал B1: B3 содержит значения ИСТИНА, ЛОЖЬ, и ИСТИНА, то функция = ЕСЛИ(И(B1: B3); ИСТИНА; ЛОЖЬ) возвращает значение ЛОЖЬ.

IV) = ЕСЛИ(ИЛИ(1+1=1; 2+2=5); 5; 6) возвращает значение 5.

V) = ЕСЛИ(ИЛИ(1+1=3; 2+2=5); 5; 6) возвращает значение 6.

VI) Если ячейка B4 содержит число 99, то функция
= ЕСЛИ(ИЛИ(1> B4; B4> 100); ”1”; ”3”) возвращает значение 3.

VII) = ЕСЛИ(НЕ(А1< > А2); 0; 1) возвращает значение 1, если, например, ячейка А1 содержит число 1, а ячейка А2 содержит число 2.

VIII)= ЕСЛИ(НЕ(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 – указать, на сколько ежемесячные расходы превышают соответствующие бюджетные поступления (+ отвечает превышению, - отвечает резерву). Рассчитать следующие итоговые величины за полугодие:

· Число месяцев, в течение которых расходы превышали бюджет.

· Общую сумму превышения расходов над бюджетом (только по тем месяцам, когда это происходило).

· Общую сумму резерва (только по тем месяцам, когда расходы были меньше бюджетных поступлений).

· Cуммарные расходы минус суммарный бюджет за полугодие.

Решение достигается в рамках построения таблицы и введения в ее ячейки формул, показанных ниже:

Справка