Работа в microsoft excel 2010 что если

13

ПЛАН-КОНСПЕКТ

учебного занятия кружка «Компьютер и информационные технологии»

Тема учебного занятия: «Решение задач с использованием логической функции ЕСЛИ в Microsoft Excel 2010».

Цели учебного занятия: научиться решать задачи с использованием логической функции ЕСЛИ в Microsoft Excel 2010.

Задачи учебного занятия:

Образовательная:

• познакомить обучающихся с работой логической функции ЕСЛИ в Microsoft Excel 2010;

• закрепить полученные знания в процессе выполнения практической работы;

• способствовать формированию у обучающихся логического и алгоритмического мышления;

• развивать познавательный интерес к учебной дисциплине и навыки ИКТ;

• развивать умения оперировать ранее полученными знаниями;

• выполнять в процессе учебной деятельности все требуемые этапы решения задач с помощью компьютера.

Воспитательная:

• формировать информационную культуру, умения и навыки самостоятельного овладения знаниями;

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

Тип учебного занятия: комбинированный.

Метод обучения: объяснительно-иллюстративный.

Методические приемы обучения: словесный, наглядный.

Формы работы: индивидуальная.

Оборудование: персональные компьютеры, доска, мел.

Программное обеспечение: операционная система Windows 7, пакет прикладных программ Microsoft Office 2010.

План занятия:

1. Организационный момент (3 минуты).

2. Актуализация базовых знаний (12 минут).

3. Изложение нового материала (25 минут).

4. Практическая работа (1 час 30 минут).

5. Подведение итогов учебного занятия. Рефлексия (5 минут).

Ход учебного занятия:

Организационный момент.

Учебное занятие начинается с приветствия обучающихся. Затем педагог дополнительного образования отмечает присутствующих в группе.

Актуализация базовых знаний.

Педагог дополнительного образования старается подвести обучающихся к новой теме и проводит опрос.

1. Что является минимальным элементом в табличном процессоре Excel? (Минимальным элементом табличного процессора Excel, является ячейка.)

2. С помощью какого средства Excel можно существенно упростить ввод данных, что это за средство? (Автозаполнение – это заполнение ячеек данными из определенных последовательностей, предусмотренных Excel.)

3. Что собой представляют формулы в Excel, и с какого знака начинается запись формул? (Начинается запись формулы со знака “=”. Формулы представляют собой выражения, описывающие вычисления в ячейках.)

4. Каким образом можно занести формулу в несколько ячеек, т.е. скопировать ее? (Нужно установить курсор на нижнем правом маркере ячейки (курсор должен принять вид маленького черного крестика) и протянуть его до последней ячейки.)

5. Назовите часто используемые функции? (Часто используются такие функции как: СУММ, СРЗНАЧ, МАКС, МИН.)

Педагог дополнительного образования предлагает обучающимся тестовые задания:

1.Электронная таблица – это …

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

б) приложение хранящее и обрабатывающее данные в прямоугольных таблицах.

в) приложение служащее для производства вычислений и решения математических задач.

г) все ответы верны

2.Активная ячейка в Excel — это:

а) ячейка с адресом А1; б) ячейка, выделенная рамкой; в) ячейка, в которую введены данные; г) нет правильного ответа

3.Ввод формулы в Excel начинается:

а) со знака равно; б) со знака скобки; в) с цифры; г) с буквы.

4.Адрес ячейки в Excel состоит из:

а) имени файла;

б) заданного набора символов;

в) имени столбца и номера строки, на пересечении которых находится ячейка;

г) номера строки и имени столбца, на пересечении которых находится ячейка.

5.Функция СУММ() в Excel относится к функциям:

а) математическим; б) статистическим; в) логическим; г) финансовым.

6.Мастер функций в Excel необходим для:

а) редактирования таблицы; б) для быстрого запуска программы;

в) сохранения информации; г) ввода необходимых функций.

7.Если в Excel сделать ячейку активной и нажать клавишу Delete, то:

а) удалится содержимое ячейки; б) очистится формат ячейки;

в) удалится ячейка; г) удалится имя ячейки.

8.Информация в таблице представлена в виде:

а) файлов; б) записей; в) текста, чисел, формул; г) все ответы верны

9.Укажите правильный адрес ячейки:

а) А6С

б) В67

в) 83С

г) В1Г

10.Наименьшим элементом электронной таблицы является …

а) ячейка; б) символ; в) столбец; г) строка

11.Диапазоном ячеек электронной таблицы называется …

а) множество всех заполненных ячеек таблицы;

б) множество всех пустых ячеек;

в) множество ячеек, образующих область прямоугольной формы;

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

12.Укажите недопустимую формулу для ячейки F1

а) =A1+B1*D1; б) =A1+B1/F1; в) =C1; г) допустимы все формулы

13.Укажите недопустимую формулу для записи в ячейку D1

а) =2A1+B2; б) =A1+B2+C3; в) =A1-C3; г) допустимы все формулы.

14.В электронной таблице выделена группа ячеек А1:В3. Сколько ячеек в этой группе?

а) 2; б) 3; в) 5; г) 6.

15.В электронной таблице нельзя удалить

а) строку; б) столбец; в) имя ячейки; г) содержимое ячейки.

Изложение нового материала.

Для выполнения практической работы необходимо знать следующее:

Логические функции

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

• Команда ЕСЛИ позволяет организовать разного рода разветвления. Формат её:

=ЕСЛИ(логическое_условие;когда_верно;когда_неверно)

В качестве логического условия выступают равенства и неравенства с использованием знаков (больше), = (больше или равно), (не равно).

Пример: =ЕСЛИ(C1D1*B5; «УРА!»; «УВЫ…») – если число в ячейке C1 больше чем произведение D1 и B5, то в нашей ячейке будет радость, а если меньше – разочарование.

В функцию ЕСЛИ может быть вложена другая функция ЕСЛИ, а в неё ещё одна – «и так семь раз».

Пример: =ЕСЛИ(C1100; «УРА!»; ЕСЛИ(Е1=1;G1;G2)) – если ячейка C1больше ста, то в нашей ячейке будет написано «УРА!», а если меньше либо равна – то в неё скопируется содержимое ячеек G1 (при Е1, равном 1) или G2 (при Е1, не равном 1).

• Команда И позволяет задать несколько условий, которые можно использовать в команде ЕСЛИ. Все условия в команде И должны быть выполнены, только тогда функция принимает значение Истина. Если хоть одно условие не выполнено, то значение её Ложь. Формат:

=И(логическое_условие_1;логическое_условие_2)

Всего логических условий может быть до 30 штук.

Пример совместного использования функций ЕСЛИ и И:

ЕСЛИ(И(Е11;G2= «УРА!»); «Угадал»; «Не угадал!») – если ячейка Е1 больше 1, а в G2 находится слово «УРА!», то в нашей ячейке окажется слово «Угадал» (Истина), если же какое-то из логических условий не выполнено (Ложь), получим «Не угадал».

• Команда ИЛИ тоже позволяет задать несколько условий, но иным образом. Если хоть одно из них выполнено, то функция принимает значение Истина. И только когда все заданные условия неверны, получим «Ложь». Формат такой же, как у функции И, использовать её совместно с ЕСЛИ можно точно так же.

• Команда НЕ инвертирует, переворачивает полученное значение: была Истина, станет Ложь, и наоборот.

Пример: ЕСЛИ(НЕ(C1D1*B5; «УРА!»; «УВЫ…»); «УРА!» появляется, когда C1 не больше D1*B5.

Практическая работа.

1. Решить задачу1 с использованием логической функции ЕСЛИ: «Если количество баллов, полученных при тестировании, не превышает 12, то это соответствует оценке «2»; оценке «3» соответствует количество баллов от 12 до 15; оценке «4» – от 16 до 20 баллов; оценке «5» – свыше 20 баллов. Создать таблицу по образцу (Приложение 1).

2. Произвести расчёты по формуле.

1. Запускаем редактор электронных таблиц Microsoft Excel 2010 и создаём в новой папке новую электронную книгу под своей фамилией. Для оформления таблицы выделяем диапазон ячеек, устанавливаем тип шрифта Times New Roman, размер шрифта – 12, начертание для заголовка – полужирный, для остального данных – обычный, на вкладке границы – внешние и внутренние. Создаём таблицу по образцу (Приложение 1. Задание для выполнения)

1. Рассчитываем оценку по следующей формуле:

Для второй строки:

=ЕСЛИ(B2B2B2=12);3; ЕСЛИ(И(B2B2=16);4;5)))

Остальные ячейки столбца «Оценка» заполняем автозаполнением.

Сохраним созданную электронную книгу в новой папке.

1. Решить задачу2 с использованием логической функции ЕСЛИ: «Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: К рублей за 1 КВт/ч и Х рублей за каждый КВт/ч сверх нормы, которая составляет 50 КВт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента.

Создать таблицу по образцу (Приложение 2)

1. Рассчитываем оценку по следующей формуле:

Для девятой строки:

=ЕСЛИ(B9

1. В ячейке «Итого» используем функцию СУММ.

Сохраним созданную электронную книгу в той же папке.

Подведение итогов учебного занятия. Рефлексия.

Педагог дополнительного образования спрашивает: «Цель нашего учебного занятия выполнена? Что нового вы узнали на занятии? Что было сложно сделать?»

Педагог дополнительного образования демонстрирует работы, выполненные обучающимися и предлагает обучающимся оценить себя самостоятельно.

Приложение 1

Задание для выполнения:

Образец выполненной практической работы:

Приложение 2

Задание для выполнения:

Образец выполненной практической работы:

Помогла ли Вам статья?

Как в Экселе использовать функцию ЕСЛИ

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

Функция ЕСЛИ описывает условие и результат, который получается при его выполнении или, наоборот, невыполнении. Это одна из базовых функций программы Excel, которую используют при создании отчетов: для бухгалтерии, списка товаров или имен, поиска ошибок в больших таблицах и так далее.  

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

Для примера мы создали таблицу с фамилиями и возрастом случайно выбранных людей. Предположим, что в третьем столбике напротив каждого человека надо проставить его «социальный статус» — ребенок это или взрослый. Это можно сделать с помощью функции ЕСЛИ.

Excel содержит множество мощных инструментов для выполнения сложных математических вычислений, например, Анализ «что если». Этот инструмент способен экспериментальным путем найти решение по Вашим исходным данным, даже если данные являются неполными. В этом уроке Вы узнаете, как использовать один из инструментов анализа «что если» под названием Подбор параметра.

Подбор параметра

Каждый раз при использовании формулы или функции в Excel Вы собираете исходные значения вместе, чтобы получить результат. Подбор параметра работает наоборот. Он позволяет, опираясь на конечный результат, вычислить исходное значение, которое даст такой результат. Далее мы приведем несколько примеров, чтобы показать, как работает Подбор параметра.

Как использовать Подбор параметра (пример 1):

Представьте, что Вы поступаете в определенное учебное заведение. На данный момент Вами набрано 65 баллов, а необходимо минимум 70 баллов, чтобы пройти отбор. К счастью, есть последнее задание, которое способно повысить количество Ваших баллов. В данной ситуации можно воспользоваться Подбором параметра, чтобы выяснить, какой балл необходимо получить за последнее задание, чтобы поступить в учебное заведение.

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

1. Выберите ячейку, значение которой необходимо получить. Каждый раз при использовании инструмента Подбор параметра, Вам необходимо выбирать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6).
2. На вкладке Данные выберите команду Анализ «что если», а затем в выпадающем меню нажмите Подбор параметра.
3. Появится диалоговое окно с тремя полями:
   • Установить в ячейке — ячейка, которая содержит требуемый результат. В нашем случае это ячейка B7 и мы уже выделили ее.
   • Значение — требуемый результат, т.е. результат, который должен получиться в ячейке B7. В нашем примере мы введем 70, поскольку нужно набрать минимум 70 баллов, чтобы поступить.
   • Изменяя значение ячейки — ячейка, куда Excel выведет результат. В нашем случае мы выберем ячейку B6, поскольку хотим узнать оценку, которую требуется получить на последнем задании.
4. Выполнив все шаги, нажмите ОК.
5. Excel вычислит результат и в диалоговом окне Результат подбора параметра сообщит решение, если оно есть. Нажмите ОК.
6. Результат появится в указанной ячейке. В нашем примере Подбор параметра установил, что требуется получить минимум 90 баллов за последнее задание, чтобы пройти дальше.

Как использовать Подбор параметра (пример 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

1. Выделите ячейку, значение которой необходимо изменить. В нашем случае мы выделим ячейку B4.
2. На вкладке Данные выберите команду Анализ «что если», а затем в выпадающем меню нажмите Подбор параметра.
3. Появится диалоговое окно с тремя полями:
   • Установить в ячейке — ячейка, которая содержит требуемый результат. В нашем примере ячейка B4 уже выделена.
   • Значение — требуемый результат. Мы введем 500, поскольку допустимо потратить $500.
   • Изменяя значение ячейки — ячейка, куда Excel выведет результат. Мы выделим ячейку B3, поскольку требуется вычислить количество гостей, которое можно пригласить, не превысив бюджет в $500.
4. Выполнив все пункты, нажмите ОК.
5. Диалоговое окно Результат подбора параметра сообщит, удалось ли найти решение. Нажмите OK.
6. Результат появится в указанной ячейке. В нашем случае Подбор параметра вычислил результат 18,62. Поскольку мы считаем количество гостей, то наш окончательный ответ должен быть целым числом. Мы можем округлить результат в большую или меньшую сторону. Округлив количество гостей в большую сторону, мы превысим заданный бюджет, значит, остановимся на 18-ти гостях.

Как видно из предыдущего примера, бывают ситуации, которые требуют целое число в качестве результата. Если Подбор параметра выдает десятичное значение, необходимо округлить его в большую или меньшую сторону в зависимости от ситуации.

Другие типы анализа «что если»

Для решения более сложных задач можно применить другие типы анализа «что если» — сценарии или таблицы данных. В отличие от Подбора параметра, который опирается на требуемый результат и работает в обратном направлении, эти инструменты позволяют анализировать множество значений и наблюдать, каким образом изменяется результат.

history 11 апреля 2013 г.

Группы статей
• стандартные функции

Функция ЕСЛИ()

, английский вариант IF(),

используется при проверке условий. Например, =ЕСЛИ(A1>100;»Бюджет проекта превышен»;»ОК!»). В зависимости от значения в ячейке

А1

результат формулы будет либо «Бюджет превышен» либо «ОК!».

Функция ЕСЛИ() относится к наиболее часто используемым функциям. Поэтому рассмотрим ее подробно.

Синтаксис функции

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

Функция имеет 3 аргумента.

Лог_выражение

— любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, выражение A1>100. Если в ячейке А1 содержится значение 102, то выражение A1>100 вернет ИСТИНА, т.к. 102>100. Если в ячейке А1 содержится значение 88, то выражение вернет ЛОЖЬ, т.к. неравенство 88>100 неверно. Полностью формула может выглядеть так =ЕСЛИ(A1>100;»Бюджет проекта превышен»;»ОК!») Если в ячейке

A1

содержится значение большее 100, то формула вернет текстовое значение

Бюджет проекта превышен,

а если меньше 100 или равное 100, то будет выведено

ОК!

Других значений кроме ИСТИНА или ЛОЖЬ логическое выражение вернуть не может. Любое число кроме 0 в первом аргументе будет преобразовано в ИСТИНА, ноль — в ЛОЖЬ. Если ошибочно будет указано текстовое значение, то функция вернет ошибку #ЗНАЧ!.

Примечание

: Ошибка #ЗНАЧ! может быть также из-за ошибок в выражениях указанных во втором и третье аргументе.

Второй аргумент —

Значение_если_истина

и третий аргумент

значение_если_ложь

Как было показано выше, если логическое выражение (первый аргумент функции) принимает значение ИСТИНА, то функция возвращает значение из второго аргумента

значение_если_истина.

Соответственно, если логическое выражение принимает значение ЛОЖЬ, то функция возвращает значение из третьего аргумента

значение_если_ложь

.

Второй и третий аргумент могут быть любым значением (число или текст) или являться выражением (формулой с функциями). Например формула =ЕСЛИ(A1>100;0;А1*1%) позволяет вычислять премию для проектов, в которых не было превышения бюджета (>100). Если бюджет превышен (в А1 введено значение 200 или любое другое более 100), то формула вернет 0, если в А1 введено значение менее 100, то будет вычислено значение премии А1*1% (т.е. если в А1 число 90, то формула вернет 0,9).

Если в качестве второго и третьего аргументов введено выражение, то оно может содержать и функции, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке

A1

содержится значение >100, то суммирование ведется по столбцу

B

, а если меньше или равно 100, то по столбцу

С

. Не забывайте, что при значении А1 = 100 в данном случае будет срабатывать выражение из аргумента

значение_если_ложь.

Аргумент

значение_если_ложь

можно опустить (не вводить), тогда при значении логического выражения =ЛОЖЬ будет выведено ЛОЖЬ. Например, если в А1 содержится 600, то формула =ЕСЛИ(A1>=1000;»Больше или равно 1000″) вернет просто ЛОЖЬ. Понятно, что при значении логического выражения =ИСТИНА будет выведено «Больше или равно 1000», т.е. второй аргумент функции ЕСЛИ() —

Значение_если_истина.

Вложенные ЕСЛИ (несколько условий)

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

значение_если_истина

и

значение_если_ложь

введите еще одно условие с функцией ЕСЛИ(). Например, для проверки в какой диапазон попадает значение из ячейки А8 запишите формулу

=ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2))

• Если значение из ячейки А8 менее 5, то будет выведено 1
• Если значение из ячейки А8 более 10, то будет выведено 3
• Если значение из ячейки А8 между 5 и 10 (включая 5 и 8), то будет выведено 2

Примечание

: В версии EXCEL 2007 можно использовать до 64 вложенных друг в друга функций ЕСЛИ(). Но, пожалуйста, не делайте так. Такой подход чреват ошибками.

Примечание

: Формулу =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2)) можно переписать как =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) или ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1)

или более сложный вариант =ЕСЛИ(A8<5;1;ЕСЛИ(И(5 < A8;A8<10);2;3)). Последний вариант позволяет последовательно перебирать диапазоны изменения А8, но требует знания работы логических функций.

Формула =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2)) работает следующим образом: сначала вычисляется логическое выражение A8<5. Если оно истинно, то выводится 1, вычисления завершены. Если оно ложно, т.е. А8>=5, то EXCEL переходит к вычислению выражения ЕСЛИ(A8>10;3;2). Опять сначала вычисляется логическое выражение A8>10. Если оно истинно, то выводится 3, вычисления завершены. Если оно ложно, то выводится 2, вычисления завершены.

Чтобы грамотно написать формулу с вложенными ЕСЛИ() следуйте следующим простым правилам:

1) Определитесь с диапазонами:

1.1) они не должны пересекаться. В примере выше — 3 диапазона и значение из А8 гарантированно попадет только в один из них. Было бы ошибкой написать формулу типа =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;ЕСЛИ(A8<3;2;4))). Области значений у выражений A8<5 и A8<3 пересекаются и крайне сложно безошибочно прописывать такие условия. Такая формула просто не верна. Очевидно, что в этом случае 4 диапазона: меньше 3; от 3 до 5; от 5 до 10 и больше 10. Как прописать такие условия написано ниже в этом разделе.

1.2.) диапазоны должны полностью охватывать нужную область значений. Очевидно, что в формуле =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2)) на интересует вся числовая ось от минус бесконечности до +бесконечности. И все 3 диапазона полностью покрывают эту область.

1.3.) Не забудьте ставить знак равно, чтобы при значениях А8 совпадающих с границами интервалов не было неправильных вычислений. Например, если в формуле =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) забыть указать знак = в логическом выражении A8>=5, то при А8=5 формула =ЕСЛИ(A8>5;ЕСЛИ(A8>10;3;2);1) вернет 1, тогда как исходная формула вернет 2.

Примечание:

В перечисленных выше случаях логические выражения вычисляются на основании одной переменной А8. Иногда нужно придумывать формулы с ЕСЛИ(), зависящие от 2-х и более ячеек: =ЕСЛИ(B8>=5;ЕСЛИ(A8>10;3;2);1). Это гораздо сложнее, но, конечно, возможно. Следуйте правилам 1.1)-1.3) и следите за диапазонами. Еще сложнее, если обе переменных являются зависимыми, т.е. например B8 вычисляется на основании А8. Написание таких формул требует предельной аккуратности и напрямую связано с корректностью формулирования самой задачи (возможно вы не продумали до конца как использовать такую сложную зависимость). Советую по возможности разбить вычисления на 2 или более формулы, чтобы проще контролировать результат вычисления.

=ЕСЛИ(A8<5;1;

ЕСЛИ(A8>10;3;2))

Для этого поставьте курсор перед вторым ЕСЛИ(), нажмите и удерживайте клавишу ALT, нажмите ENTER.

4) для отладки вложенных ЕСЛИ вместо реальных вычислений (V8*10%-37500) используйте простые значения — последовательности 1; 2; 3 … Это покажет какой блок вложенных ЕСЛИ() срабатывает.

5) В ряде случаев от вложенных ЕСЛИ можно отказаться. Рассмотрим пример определения размера проекта от его бюджета

=ЕСЛИ(A1>=100;»Очень крупный проект»;

ЕСЛИ(A1>=90;»Крупный проект»;

ЕСЛИ(A1>=50;»Средний проект»;»Малый проект «)))

Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы. Ниже приведено 2 эквивалентные формулы:

=ПРОСМОТР(A1;{0;50;90;100};{«Малый проект»;»Средний проект»;»Крупный проект»;»Очень крупный проект»})

=ВПР(A1;A3:B6;2)

Для функции ВПР() необходимо создать в диапазоне

A3:B6

таблицу значений:

Формула =ПРОСМОТР(A1;{0;50;90;100};{«Малый проект»;»Средний проект»;»Крупный проект»;»Очень крупный проект»}) выглядит необычно, но по сути второй аргумент {0;50;90;100}, как и третий аргумент {«Малый проект»;»Средний проект»;»Крупный проект»;»Очень крупный проект»} это запись содержимого 4-х ячеек. Вместо {0;50;90;100} можно записать ссылку на диапазон ячеек, содержащих 4 значения 0;50;90;100. Круглые скобки нужны, чтобы сообщить, что в функцию передается не одно значение, а целый массив.

Еще пример. Пусть требуется вывести разный текст (например «<0»; «=0» или «>0») в случае наличия в ячейке

А1

отрицательного значения, положительного значения или 0. Это можно сделать без вложенных ЕСЛИ():

=ПРОСМОТР(A1;{-1E+307;0;1E-307};{«<0″;»=0″;»>0″})

Примечание

: -1E+307 — это самое маленькое отрицательное число в EXCEL, 1E-307 — это самое маленькое положительное число. Таким образом задается 3 диапазона: от минус бесконечности до 0 (0 не включая); 0 (диапазон состоит только из одного значения 0); от 0 до +бесконечности (0 не включая).

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

=ПРОСМОТР(A24;{-1E+307;0;1E-307};A27:A29)

Диапазон A27:A29 должен, понятно, содержать формулы (см. файл примера).

Опущен третий аргумент

[значение_если_ложь]

Третий аргумент функции не обязателен. Если логическое условие не выполнено, т.е. ЛОЖЬ, а третий аргумент функции не указан, то функция вернет также значение ЛОЖЬ. Например:

=ЕСЛИ(A1>100;»Бюджет превышен»)

Если в ячейке

A1

содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.

Сложные логические выражения

В аргументе

Лог_выражение

можно использовать как простые сравнения типа F1>10, так и более сложные конструкции на основе функций И(), ИЛИ(), НЕ() и их комбинаций.

Основное правило

— логические функции и их комбинации возвращают только одно из 2х логических значений: ЛОЖЬ или ИСТИНА.

Приведем несколько полезных примеров.

Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.

Формула =ЕСЛИ(ИЛИ(A1=0;A2=0);0;2) проверяет равенство 0 хотя бы одного значения из А1 и А2.

Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1<=0;1;2)

Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

Т.к. значение ЛОЖЬ эквивалентно 0, то формулы =ЕСЛИ(0;»Бюджет превышен»;»ОК!») или (если в ячейке

A1

содержится значение 0) =ЕСЛИ(A1;»Бюджет превышен»;»ОК!») вернут

ОК!

Если в ячейке

A1

находится любое другое число кроме 0, то формула вернет

Бюджет превышен

. Такой подход удобен, когда проверяется равенство значения нулю значения ячейки.

Примечание

: чтобы убедиться, что логическое значение ЛОЖЬ соответствует 0 введите формулу =—А1=0. В А1 введите ЛОЖЬ. Формула вернет ИСТИНА. Отметим, что логическое значение ЛОЖЬ именно соответствует 0, но не равно 0, т.к. формула  =А1=0 вернет ЛОЖЬ, следовательно логическое значение ЛОЖЬ не равно 0. Железная логика!

Примечание

: Двойное отрицание «—» — это просто математическая операция, которая приводит к преобразованию логического выражения в числовое, но не приводит к изменению самого значения. Двойное отрицание можно заменить сложением с 0 или возведением в первую степень: =(А1+0)=0.

Связь функции ЕСЛИ() с другими функциями использующие условия

EXCEL содержит также другие функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета количества вхождений чисел в диапазоне ячеек используется функция СЧЁТЕСЛИ() , а для сложения значений, удовлетворяющих определенным условиям, используется функция СУММЕСЛИ() .

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

Пусть данные находятся в диапазоне

A6:A11

(см. файл примера)

Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;»>10″) . Аналогичный результат (23) можно получить с помощью

формулы массива

=СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо

ENTER

нужно нажать

CTRL+SHIFT+ENTER

)

Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек

A6:A11

=СЧЁТЕСЛИ(A6:A11;»>10″) . Аналогичный результат (2) можно получить с помощью

формулы массива

=СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))

Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))

Функции программы Microsoft Excel: оператор «ЕСЛИ»

​Смотрите также​ сопоставить цены на​ Таким образом, у​ практике. Рассмотрим подробно​ вычислить процент определенных​ ставят не слова,​Если​ написать три условия.​ языке) .​ подсчета и сложения​Если требуется вывести разный​ проверке условий. Например,​Следствием этого является возможность​и​

​ «ИЛИ». Он подразумевает,​ сотрудник не мужчина​

Общее определение и задачи

​ такой вид: «ЕСЛИ(B6=»жен.»;​Среди многих функций, с​ товар в разные​ нас получиться несколько​ на примерах.​ ответов к общему​ а знаками. Читайте​в ячейке стоит текст​ В формуле эти​Обзор ​ значений с использованием​ текст в случае​ =ЕСЛИ(A1>100;»Бюджет превышен»;»ОК!»). В​

​ альтернативной записи формулы​выражение A2>100 -​ что значение является​ (т.е. женщина), то​ «1000»; «0»)».​

Пример использования

​ которыми работает программа​ привозы, сравнить балансы​ функций ЕСЛИ в​Синтаксис оператора в Excel​

​ числу ответов.​ статью «Как объединить​ Excel​ условия напишутся через​Прежде чем приступить к​ условий. Ниже приведены​ наличия в ячейке​ зависимости от значения​ =И(A1>100;A2>100) в виде​ ИСТИНА), то формула​ истинным, если выполнено​ начинается проверка второго​Вводим данное выражение в​ Microsoft Excel, следует​ (бухгалтерские отчеты) за​ Excel.​ – строение функции,​Есть ещё функция​ данные теста в​, то формулу напишем​ точку с запятой.​ расширенному применению функции​ иллюстрирующие примеры.​А1​

​ в ячейке​ =(A1>100)*(A2>100)​ вернет ИСТИНА, а​ хотя бы одно​ условия. Если женщина​

​ самую верхнюю ячейку,​ выделить функцию «ЕСЛИ».​ несколько месяцев, успеваемость​Синтаксис будет выглядеть следующим​ необходимые для ее​ в Excel «СУММЕСЛИ».​ ячейках в Excel»​ такую.​Первое условие –​ ЕСЛИ, не помешает​Пусть данные находятся в​

​отрицательного значения, положительного​А1​Значение второй формулы​ если хотя бы​

Пример функции с несколькими условиями

​ из нескольких условий.​ относится к основному​ где должен выводиться​ Это один из​ учеников (студентов) разных​ образом:​ работы данные.​ Про эту функцию​ тут.​Нам нужно, чтобы​ «Лог_выражение». Мы написали​ вспомнить, о чем​ диапазоне​

​ значения или 0,​результат формулы будет​ будет =1 (ИСТИНА),​ в одной ячейке​Итак, предположим, что премия​ персоналу, то в​ результат. Перед выражением​ тех операторов, к​ классов, в разные​=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))​=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)​ смотрите в статье​Можно в Excel​ в ячейке B​ А1=2 – это​ идет речь. В​A6:A11​ то можно записать​

​ либо «Бюджет превышен»​ только если оба​ значение​ к 8 марта​ ячейку будет выводиться​ ставим знак «=».​ которым пользователи прибегают​ четверти и т.д.​Здесь оператор проверяет два​Разберем синтаксис функции:​ «Как посчитать в​ в функцию «ЕСЛИ»​ 4 было написано​ значит, если в​ этом видеоролике приведены​(см. файл примера)​ следующую формулу:​ либо «ОК!».​ аргумента истинны, т.е.​Другими словами, формула =И(ИСТИНА;ИСТИНА)​ в 100 рублей​

​ значение – «1000»,​После этого, жмем на​ чаще всего, при​Чтобы сравнить 2 таблицы​

​ параметра. Если первое​Логическое_выражение – ЧТО оператор​ Excel ячейки в​

Пример с выполнением двух условий одновременно

​ вписать несколько условий​ «100», если в​ ячейке А1 стоит​ некоторые примеры.​Подсчитаем сумму значений, которые​=ПРОСМОТР(A1;{-1E+307;0;1E-307};{«<>0»})​

​Функция ЕСЛИ()относится к наиболее​ равны 1. Только​ вернет ИСТИНА,​ положена только женщинам,​ а в обратном​ кнопку Enter. Теперь,​ выполнении задач в​ в Excel, можно​ условие истинно, то​ проверяет (текстовые либо​ определенных строках».​ логического выражения. Смотрите​ ячейке A 4​ цифра 2. Мы​Вложение функции в функцию​ больше 10 с​или, если требуется вместо​ часто используемым функциям.​ произведение 2-х единиц​а формулы =И(ИСТИНА;ЛОЖЬ)​ которые входят в​ случае – «500».​ чтобы данная формула​ приложении. Давайте разберемся,​ воспользоваться оператором СЧЕТЕСЛИ.​ формула возвращает первый​ числовые данные ячейки).​

​Можно в Excel​ такую формулу в​ будет стоять слово​ написали условие, по​

Пример использования оператора «ИЛИ»

​ЕСЛИ​ помощью функции СУММЕСЛИ(),​ текстовых значений вывести​ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])​ даст 1 (ИСТИНА),​ или =И(ЛОЖЬ;ИСТИНА) или​ число основного персонала.​

​ В виде формулы​ появилась и в​ что представляет собой​ Рассмотрим порядок применения​ аргумент – истину.​Значение_если_истина – ЧТО появится​ суммировать данные из​ статье «Функция «ЕСЛИ»​ «Да». В ячейке​ которому нужно искать​Функция СУММЕСЛИМН​ записав =СУММЕСЛИ(A6:A11;»>10″). Аналогичный​ формулы, можно использовать​Лог_выражение​ что совпадает с​ =И(ЛОЖЬ;ЛОЖЬ) или =И(ЛОЖЬ;ИСТИНА;ИСТИНА)​ В этом случае,​ это будет выглядеть​ нижних ячейках, просто​ функция «ЕСЛИ», и​

​ функции.​

​ Ложно – оператор​ в ячейке, когда​ таблицы по многим​ в Excel с​ В4 написали такую​ ячейки в столбце​Функция СЧЁТЕСЛИМН​ результат (23) можно​ ссылки на диапазон​   — любое значение или​ определением функции И().​ вернут ЛОЖЬ.​

​ если работник мужчина,​

lumpics.ru

Функция И() в MS EXCEL

​ следующим образом: «=ЕСЛИ(B6=»муж.»;»0″;​ становимся в правый​ как с ней​Для примера возьмем две​ проверяет второе условие.​ текст или число​ разным условиям. Например,​ несколькими условиями сравнения».​

Синтаксис функции

​ формулу.​

​ А (ячейки, в​​Функция СРЗНАЧЕСЛИМН​ получить с помощью​ ячеек (содержащих формулы)​

​ выражение, принимающее значения​
​Эквивалентность функции И() операции​​Функция воспринимает от 1​​ или относится к​​ ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))».​​ нижний угол заполненной​ работать.​ таблицы с техническими​​Примеры несколько условий функции​​ отвечают заданному условию​ в функции «ЕСЛИ»​Функция «ЕСЛИ» в Excel​=ЕСЛИ(A4=»Да»;100;»»)​ которых стоит цифра​Функция ЕСЛИОШИБКА​

​ формулы массива​=ПРОСМОТР(A24;{-1E+307;0;1E-307};A27:A29) (см. файл примера)​
​ ИСТИНА или ЛОЖЬ.​ умножения * часто​ до 255 проверяемых​ вспомогательному персоналу, то​

​Вставляем данное выражение в​ ячейки, жмем на​Скачать последнюю версию​ характеристиками разных кухонных​ ЕСЛИ в Excel:​ (правдивы).​ можно написать одно​ – примеры​Обратите внимание!​

Совместное использование с функцией ЕСЛИ()

​ «2»).​Часто применяемая,​=СУММ(ЕСЛИ(A6:A11>10;A6:A11))​Третий аргумент функции не​=ЕСЛИ(A1>=100;»Бюджет превышен»;»ОК!»)​ используется в формулах​ условий. Понятно, что​ величина его премии​
​ самую верхнюю ячейку​ кнопку мышки, и​

​ Excel​​ комбайнов. Мы задумали​​Таблица для анализа успеваемости.​​Значение,если_ложь – ЧТО появится​​ условие поиска данных​смотрите в следующих​В третьем условии​Второе условие –​логическая функция в Excel​

Сравнение с функцией ИЛИ()

​(для ввода формулы​ обязателен, если его​Т.е. если в​ с Условием И,​ 1 значение использовать​ будет равна нулю,​ столбца «Премия к​ проводим курсором до​«ЕСЛИ» является стандартной функцией​ выделение отличий цветом.​ Ученик получил 5​ в графе, когда​

Эквивалентность функции И() операции умножения *

​ для суммирования. В​ статьях.​ в формуле мы​ «Значение_если_истина» — здесь​ — функция «ЕСЛИ»​ в ячейку вместо​ опустить, то функция​

​ ячейке​ например, для того​ бессмысленно, для этого​ а иначе –​
​ 8 марта».​ самого низа таблицы.​ программы Microsoft Excel.​ Эту задачу в​ баллов – «отлично».​ текст или число​ функции «СУММЕСЛИМН» можно​В формулу можно​ поставили не «нуль»,​

​ мы напишем для​считает данные ячеек​ENTER​ вернет значение ЛОЖЬ​A1​ чтобы сложить только​ есть функция ЕСЛИ().​ 1000 рублей. В​​Как и в прошлый​​Таким образом, мы получили​
​ В её задачи​

Проверка множества однотипных условий

​ Excel решает условное​ 4 – «хорошо».​ НЕ отвечают заданному​​ написать в одной​​ поставить несколько функций​ а две двойные​ Excel, что нужно​ по условию, которое​
​нужно нажать​
​ (если условие не​содержится значение большее​ те значения, которые​ Чаще всего функцией​ виде формулы это​
​ раз, «протягиваем» формулу​
​ таблицу со столбцом,​ входит проверка выполнения​​ форматирование.​​ 3 – «удовлетворительно».​​ условию (лживы).​​ формуле до 127​

​ «ЕСЛИ» в Excel.​ кавычки – это​ написать в ячейке​ мы напишем в​CTRL+SHIFT+ENTER​ выполнено).​ или равное 100,​ больше 5​
​ И() на истинность​

​ выглядит так: «=ЕСЛИ(ИЛИ(B6=»муж.»;​ вниз.​ заполненным при помощи​
​ конкретного условия. В​

excel2.ru

Функция ЕСЛИ() в MS EXCEL

​Исходные данные (таблицы, с​ Оператор ЕСЛИ проверяет​Пример:​ условий. Подробнее об​ Наприме, такая формула.​ значит «Пусто» (оставить​​ В1, если в​​ формуле или пишет​)​=ЕСЛИ(A1>100;»Бюджет превышен»)​

​ то формула вернет​И​

Синтаксис функции

​ проверяется 2-5 условий.​

​ C6=»Вспомогательный персонал»); «0»;​​В функции «ЕСЛИ» можно​ функции «ЕСЛИ».​ случае, когда условие​
​ которыми будем работать):​
​ 2 условия: равенство​Оператор проверяет ячейку А1​​ этой функции, ее​​=ЕСЛИ(C4=10;5;ЕСЛИ(C4>=5;3;ЕСЛИ(C4>=0;0)))​ ячейку пустой). Скопировали​ ячейке А1 будет​ нужные слова в​Теперь подсчитаем количество вхождений​Если в ячейке​

​ ОК!, а если​меньше 10:​Сама по себе функция​
​ «1000»)». Записываем эту​
​ также использовать оператор​В функцию «ЕСЛИ» можно​​ выполнено (истина), то​​Выделяем первую таблицу. Условное​ значения в ячейке​ и сравнивает ее​​ применении и различных​​Эта формула взята​ формулу в ячейку​​ стоять цифра «2».​​ нужной ячейке. Например,​

Вложенные ЕСЛИ

​ чисел больше 10​A1​​ нет, то Бюджет​​=СУММПРОИЗВ((A1:A10>5)*(A1:A10​ И() имеет ограниченное​ формулу в соответствующую​ «И», который позволяет​ также вводить несколько​
​ в ячейку, где​ форматирование – создать​

​ 5 и 4.​ с 20. Это​ вариантах применения, читайте​

​ из примера составления​ В5.​

​ Мы написали, что​

​ такие условия -​ в диапазоне ячеек​​содержится значение 1,​​ превышен.​

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

​ «логическое_выражение». Когда содержимое​

​ в статье «Суммирование​ теста, анкеты в​Третий пример.​ если в ячейке​ если в ячейке​

​A6:A11​

Опущен третий аргумент [значение_если_ложь]

​ то вышеуказанная формула​В качестве аргументов функции,​ все значения в​ может вернуть только​«Протягиваем» результаты вниз.​ выполнение двух или​
​ случае, применяется вложение​
​ возвращается одно значение,​​ формулу для определения​​ добавили третье условие,​ графы больше 20,​ ячеек в Excel​

Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

​ Excel. Смотрите статью​В следующей формуле​
​ А1 будет стоять​
​ А стоит то-то,​=СЧЁТЕСЛИ(A6:A11;»>10″). Аналогичный результат​​ вернет значение ЛОЖЬ.​​ могут участвовать формулы,​
​ диапазоне​
​ значения ИСТИНА или​

​Как видим, функция «ЕСЛИ»​​ нескольких условий одновременно.​​ одного оператора «ЕСЛИ»​ а если не​ форматируемых ячеек:​ подразумевающее наличие в​ появляется истинная надпись​ по условию».​ «Как сделать тест​

Связь функции ЕСЛИ() с другими функциями использующие условия

​ поставили в третьем​ цифра «2», то​ а в ячейке​ (2) можно получить​Т.к. значение ЛОЖЬ эквивалентно​ например:​A6:A9​ ЛОЖЬ, чаще всего​ может оказаться для​Например, в нашем случае,​ в другой. При​ выполнено (ложь) –​

​В строку формул записываем:​ табеле успеваемости еще​ «больше 20». Нет​Очень много разных​ в Excel».​ условии знак «Тире».​ в ячейке В1​

​ В стоит то-то,​ с помощью формулы массива​​ 0, то формулы​​=ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10))​

​на превышение некоторого​ ее используют вместе​ пользователя хорошим помощником​ премия к 8​ выполнении условия, в​ другое.​ =СЧЕТЕСЛИ (сравниваемый диапазон;​
​ и «двоек». Принцип​
​ – «меньше или​ применений этих функций.​​Можно функцию «ЕСЛИ»​​ =ЕСЛИ(A5=»Да»;100;»-«)​​ нужно написать цифру​​ то в ячейке​

​=СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))​=ЕСЛИ(0;»Бюджет превышен»;»ОК!»)​Т.е. если в​​ граничного значения, например​​ с функцией ЕСЛИ():​ при работе с​ марта в размере​
​ ячейке отображается заданный​

​Синтаксис данной функции выглядит​ первая ячейка первой​ «срабатывания» оператора ЕСЛИ​ равно 20».​Еще одна логическая​ в Excel вставлять​В ячейку В6​ «6». Можно написать​
​ С напиши такое​

excel2.ru

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

​Теперь, когда принцип понятен,​​или (если в​ ячейке​ 100. Можно, конечно​=ЕСЛИ(И(A1>100;A2>100);»Бюджет превышен»;»В рамках​ данными в программе​ 1000 рублей выдаётся​ результат, если же​ следующим образом: «ЕСЛИ(логическое​ таблицы)=0. Сравниваемый диапазон​ тот же.​Внимание! Слова в формуле​ функция в Excel​ в другие формулы​ написали такую формулу.​ любое слово в​ слово, посчитай сумму​ с помощью функции​ ячейке​A1​ записать формулу​ бюджета»)​

​ Microsoft Excel. Она​

​ только женщинам, которые​ условие не выполнено,​ выражение; [значение если​ – это вторая​Когда нужно проверить несколько​ необходимо брать в​ – это функция​

Хотите узнать больше?

​ с другими функциями.​

​ =ЕСЛИ(A6=»%»;1;»нет») Здесь в​

​ этом условии, т.д.​

​ таких-то ячеек, т.д.​

​ ЕСЛИ() можно конструировать​

​A1​

support.office.com

Функция «ЕСЛИ» в Excel.

​содержится значение >100,​​=И(A6>100;A7>100;A8>100;A9>100)​Т.е. если в​​ позволяет отобразить результаты​ являются основным персоналом,​ то выводимый результат​ истина]; [значение если​ таблица.​ истинных условий, используется​ кавычки. Чтобы Excel​ в Excel «ЕСЛИОШИБКА».​=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B11;НАИМЕНЬШИЙ(ЕСЛИ($F$2=A2:A11;СТРОКА(B2:B11)-1;»»);​ третьем условии написали​Третье условие –​ Все это пишем​ и другие формулы​содержится значение 0)​ то суммирование ведется​но существует более​обеих​ соответствующие определенным условиям.​ а мужчины, и​ зависит уже от​ ложь])».​Чтобы вбить в формулу​
​ функция И. Суть​​ понял, что нужно​ Этой функцией мы​СТРОКА()-5));»»)​ слово «нет» в​ «Значение_если_ложь» — здесь​ в формуле. Функция​
​ с условиями. Например,​
​=ЕСЛИ(A1;»Бюджет превышен»;»ОК!»)​ по столбцу​ компактная формула, правда​ячейках​ Ничего особенно сложного​ представительницы женского пола,​ второго оператора.​Теперь давайте разберем конкретные​ диапазон, просто выделяем​
​ такова: ЕСЛИ а​ выводить текстовые значения.​
​ говорим Excel, что,​Эта формула для​ кавычках. Получилось так.​ мы напишем, что​ в Excel «ЕСЛИ»​
​ нахождение минимального значения​вернут ОК!​B​ которую нужно ввести​A1 A2​ в овладении принципами​ числящиеся вспомогательным персоналом,​Для примера возьмем все​ примеры, где используется​ его первую ячейку​ = 1 И​Еще один пример. Чтобы​
​ если будет ошибка,​ примера взята из​Четвертый пример.​ нужно писать, если​ пригодится в таблицах,​ среди чисел больше​Если в ячейке​, а если меньше,​ как формулу массива (см.​содержатся значения больше​ использования данной функцией​ не получают ничего.​ ту же таблицу​ формула с оператором​ и последнюю. «=​ а = 2​ получить допуск к​
​ не пиши ее,​ статьи «Выбрать сразу​В ячейку С1​ в ячейке А1​ чтобы посчитать определенные​ 10:​A1​ то по столбцу​ файл примера):​ 100, то выводится​ нет.​ Таким образом, чтобы​ с выплатами премии​ «ЕСЛИ».​ 0» означает команду​
​ ТОГДА значение в​ экзамену, студенты группы​ а оставь ячейку​
​ много данных из​ напишем формулу, где​ будет написано НЕ​
​ данные, в анкетах,​=МИН(ЕСЛИ(A6:A11>10;A6:A11))​находится любое другое​С​=И(A6:A9>100)​
​ Бюджет превышен, если​
​Автор: Максим Тютюшев​​ значение в ячейках​ к 8 марту.​​Имеем таблицу заработной платы.​ поиска точных (а​
​ ИНАЧЕ значение с.​ должны успешно сдать​ пустой. Например, если​ таблицы Excel» здесь.​ условия будут такие:​ цифра «2», а​ в тестах, т.д.​Примечание:​ число кроме 0,​
​.​
​(для ввода формулы​
​ хотя бы в​Функция И(), английский вариант​ колонки «Премия к​ Но, на этот​ Всем женщинам положена​ не приблизительных) значений.​Функция ИЛИ проверяет условие​ зачет. Результаты занесем​ ячейки, указанные в​
​ Этой формулой выбираем​
​ больше или равно​ другая цифра. Мы​Функция Excel «ЕСЛИ»​Мы стараемся как​
​ то формула вернет​В EXCEL 2007 в​ в ячейку вместо​ одной ячейке значение​ AND(), проверяет на​ 8 марта» было​
​ раз, согласно условиям,​
​ премия к 8​Выбираем формат и устанавливаем,​ 1 или условие​ в таблицу с​ формуле, пустые, то​ из таблицы товара​
​ (можно поставить меньше​ поставили «0» (нуль).​находится на закладке​ можно оперативнее обеспечивать​ Бюджет превышен. Такой​ качестве значений аргументов​ENTER​Функция ИЛИ() также может​ истинность условия и​ 1000, нужно соблюдение​ размер премии зависит​ марту в 1000​ как изменятся ячейки​ 2. Как только​
​ графами: список студентов,​
​ Excel выдает ошибку.​ и переносим в​ или равно).​ Это значит, если​ «Формулы» в разделе​ вас актуальными справочными​ подход удобен, когда​значение_если_истиназначение_если_ложь​нужно нажать​
​ вернуть только значения​ возвращает ИСТИНА если​ двух условий: пол​ от категории работника.​ рублей. В таблице​ при соблюдении формулы.​ хотя бы одно​ зачет, экзамен.​
​ О функции «ЕСЛИОШИБКА»​ лист заказа выбранные​​=ЕСЛИ(A1>=2;»Да»;»») Мы в​ в ячейке А1​
​ «Библиотека функций» -​ материалами на вашем​ проверяется равенство значения​можно для построения​
​CTRL+SHIFT+ENTER​
​ ИСТИНА или ЛОЖЬ,​ все условия истинны​ – женский, категория​ Женщины, имеющие статус​ имеется колонка, в​ Лучше сделать заливку​
​ условие истинно, то​Обратите внимание: оператор ЕСЛИ​ читайте в статье​ покупателем позиции.​
​ формуле написали, что,​
​ стоит НЕ цифра​
​ «Логические». Встаем на​ языке. Эта страница​ нулю.​ более сложных проверок​)​ но, в отличие​ или ЛОЖЬ если​ персонала – основной​ основного персонала получают​ которой указан пол​
​ цветом.​ результат будет истинным.​ должен проверить не​ «Функция «ЕСЛИОШИБКА» в​Ещё есть функция​ если в ячейке​ «2», то Excel​ ячейку В1, вызываем​ переведена автоматически, поэтому​EXCEL содержит также другие​ использовать до 64​В случае, если границы​ от И(), она​ хотя бы одно​ персонал. Во всех​ по 1000 рублей​ сотрудников. Таким образом,​Выделяем вторую таблицу. Условное​ Суть такова: ЕСЛИ​
​ цифровой тип данных,​ Excel».​ «СЧЁТЕСЛИ» в Excel​ А1 будет стоять​ должен написать в​ функцию «ЕСЛИ».​ ее текст может​
​ функции, которые можно​ вложенных друг в​ для каждого проверяемого​ возвращает ЛОЖЬ, только​ ложно.​ остальных случаях, значение​ премии, а вспомогательный​ нам нужно сделать​ форматирование – создать​ а = 1​ а текстовый. Поэтому​Логический оператор ЕСЛИ в​ — она считает​ число равное или​ ячйке В1 нуль.​Первый пример.​ содержать неточности и​ применять для анализа​
​ друга функций ЕСЛИ().​ значения разные, то​
​ если все ее​И(логическое_значение1; [логическое_значение2]; …)​ в этих ячейках​ персонал получает только​ так, чтобы в​ правило – использовать​ ИЛИ а =​ мы прописали в​ Excel применяется для​ количество ячеек по​ больше «2», то​Нажимаем кнопку «ОК».​Нам нужно выбрать​ грамматические ошибки. Для​ данных с использованием​=ЕСЛИ(A1>=100;»Бюджет превышен»;ЕСЛИ(A1>=90;»Крупный проект»;ЕСЛИ(A1>=50;»Средний​ границы можно ввести​

excel-office.ru

Функция ЕСЛИ в Excel с примерами нескольких условий

​ условия ложны. Чтобы​логическое_значение​ будет рано нулю.​ 500 рублей. Естественно,​ строчке со значением​ формулу. Применяем тот​ 2 ТОГДА значение​ формуле В2= «зач.».​ записи определенных условий.​ нашим условиям. Смотрите​

​ написать в ячейке​ Получилась такая формула.​ из столбца А​ нас важно, чтобы​ условий. Например, для​ проект»;»Малый проект «)))​

Синтаксис функции ЕСЛИ с одним условием

​ в соседний столбец​ сравнить эти функции​   — любое значение или​ Это записывается следующей​

​ что мужчинам этот​

​ «жен.» в колонке​

​ же оператор (СЧЕТЕСЛИ).​ в ИНАЧЕ значение​ В кавычки берем,​

​ Сопоставляются числа и/или​ статью «Функция «СЧЁТЕСЛИ​ С1 слово «Да».​ =ЕСЛИ(А1=2;6;0)​ числа «2» и​

​ эта статья была​ подсчета количества вхождений​Вышеуказанную формулу можно усложнять​ и организовать попарное​ составим, так называемую​

​ выражение, принимающее значения​

​ формулой: «=ЕСЛИ(И(B6=»жен.»; C6=»Основной​ вид выплат вообще​ «Пол» в соответствующую​Скачать все примеры функции​ с.​ чтобы программа правильно​ текст, функции, формулы​ в Excel». Эту​ Если число будет​

​Копируем формулу вниз​ написать в столбце​ вам полезна. Просим​ чисел в диапазоне​ и дальше, но​

​ сравнение списков с​ таблицу истинности для​ ИСТИНА или ЛОЖЬ.​ персонал»); «1000»; «0»)».​ не положен независимо​ ячейку колонки «Премия​ ЕСЛИ в Excel​Функции И и ИЛИ​

​ распознала текст.​ и т.д. Когда​ функция может посчитать​ меньше 2, то​ по столбцу В.​ В то значение,​ вас уделить пару​ ячеек используется функция​ есть другие подходы:​

​ помощью формулы массива:​

Функция ЕСЛИ в Excel с несколькими условиями

​ И() и ИЛИ().​Например, =И(A1>100;A2>100)​ Вставляем её в​ от категории.​ к 8 марта»​Здесь вместо первой и​ могут проверить до​​ значения отвечают заданным​ ячейки, в которых​ ячейка должна оставаться​

​ Получилось так.​ которое мы укажем​

​ секунд и сообщить,​

​ СЧЁТЕСЛИ(), а для​=ПРОСМОТР(A1;{0;50;90;100};{«Малый проект»;»Средний проект»;»Крупный проект»;»Бюджет​=И(A18:A21>B18:B21)​В математических вычислениях EXCEL​Т.е. если в​ ячейку.​Таким образом, первым условием​

​ выводилось значение «1000»,​ последней ячейки диапазона​

​ 30 условий.​Часто на практике одного​ параметрам, то появляется​ текст (например, «да»​ пустой. Скопировали формулу​Только в ячейке В2​ в формуле. Можно​ помогла ли она​ сложения значений, удовлетворяющих​

​ превышен»})​Вместо диапазона с границами​ интерпретирует значение ЛОЖЬ​обеих​Как и в прошлые​ является то, что​ а в строчках​

Расширение функционала с помощью операторов «И» и «ИЛИ»

​ мы вставили имя​Пример использования оператора И:​ условия для логической​ одна запись. Не​ — в анкете).​ по столбцу С.​ условия, прописанные в​ выбрать любые числа,​

​ вам, с помощью​ определенным условиям, используется​=ВПР(A1;A3:B6;2)​ можно также использовать​ как 0, а​ячейках​ разы, копируем значение​ если сотрудник мужчина,​ со значением «муж.»​ столбца, которое присвоили​Пример использования функции ИЛИ:​ функции мало. Когда​

​ отвечают – другая.​ Или ячейки с​ Получилось так.​

​ формуле, совпали, и​

​ слова, т.д.​

Как сравнить данные в двух таблицах

​ кнопок внизу страницы.​ функция СУММЕСЛИ().​Для функции ВПР() необходимо​ константу массива:​ ИСТИНА как 1.​A1 A2​ формулы на ячейки,​ то величина получаемой​ в колонках «Премия​ ему заранее. Можно​Пользователям часто приходится сравнить​ нужно учесть несколько​

​Логические функции – это​ числом или числами​Пятый пример.​ там стоит цифра​Появившееся диалоговое окно​

​ Для удобства также​Функция ЕСЛИ(), как альтернативный​ создать в диапазоне​=И(A18:A21>{9:25:29:39})​ В этом легко​содержатся значения больше​ расположенные ниже.​ премии равна нулю.​

​ к 8 марта»​ заполнять формулу любым​

​ две таблицы в​ вариантов принятия решений,​ очень простой и​ больше, например, 5.​Можно функцией «ЕСЛИ»​

​ «2».​ заполнили так.​ приводим ссылку на​ вариант, может быть​A3:B6​Функция ЕСЛИ(), английский вариант​

​ убедиться записав формулы​ 100 (т.е. выражение​В функции «ЕСЛИ» также​ Если же данное​ стояло значение «0».​ из способов. Но​ Excel на совпадения.​

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

​В формуле «ЕСЛИ» нужно​ оригинал (на английском​ также использована для​таблицу значений:​ IF(), используется при​

​ =ИСТИНА+0 и =ЛОЖЬ+0​ A1>100 — ИСТИНА​

​ может использоваться оператор​ значение ложно, и​ Наша функция примет​ с именем проще.​ Примеры из «жизни»:​ друг в друга.​ часто применяется в​ ячейки, но и​

exceltable.com

​ когда в нем​

Функция ЕСЛИ (IF) является одной из наиболее часто используемых функций в Excel. ЕСЛИ — простая функция, и люди любят ее, потому что она дает им возможность заставить Excel реагировать, когда информация вводится в электронную таблицу. С ЕСЛИ, вы можете оживить вашу таблицу.

Но использование одной функции ЕСЛИ часто приводит к использованию второй, и как только вы объединяете более пары ЕСЛИ, ваши формулы могут начать выглядеть как маленькие Франкенштейны 🙂

Являются ли вложенные ЕСЛИ опасными? Всегда ли они необходимы? Какие есть альтернативы?

Читайте дальше, чтобы узнать ответы на эти вопросы и многое другое …

1. Базовый ЕСЛИ

Прежде чем говорить о вложенном ЕСЛИ, давайте быстро рассмотрим базовую структуру:

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

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

Обратите внимание на квадратные скобки … это означает, что аргументы необязательны. Однако вы должны указать либо значение ИСТИНА, либо значение ЛОЖЬ.

Чтобы проиллюстрировать это, мы используем ЕСЛИ, чтобы проверить результаты и вернуть «Зачтено» для баллов не менее 65:

Ячейка D4 в примере содержит эту формулу:

= ЕСЛИ (С4 > = 65; «Зачтено»)

Что можно прочитать так: если количество баллов в ячейке C4 составляет не менее 65, вернуть «Зачтено».

Однако обратите внимание, что если оценка меньше 65, ЕСЛИ возвращает ЛОЖЬ, так как мы не указали «значение_если_ложь». Чтобы отобразить «Не зачтено» для непроходных оценок, мы можем добавить «Не зачтено» в качестве ложного аргумента следующим образом:

= ЕСЛИ (С3 > = 65; «Зачтено»; «Не зачтено»)

2. Что значит вложение

Вложенность означает объединение формул, одна внутри другой, так что одна формула обрабатывает результат другой. Например, вот формула, в которой функция СЕГОДНЯ (TODAY) вложена в функцию МЕСЯЦ (MONTH):

= МЕСЯЦ (СЕГОДНЯ ())

Функция СЕГОДНЯ (TODAY) возвращает текущую дату внутри функции МЕСЯЦ (MONTH). Функция МЕСЯЦ (MONTH) берет эту дату и возвращает текущий месяц. Даже в формулах средней сложности часто используются вложения, поэтому вы увидите их в более сложных формулах.

3. Простой вложенный ЕСЛИ (IF)

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

Чтобы проиллюстрировать это, ниже я расширил оригинальную формулу «Зачтено/Не зачтено», приведенную выше, для обработки «пустых» результатов, добавив функцию еще одну функцию ЕСЛИ:

= ЕСЛИ (С4 = «»; «Неявка»; ЕСЛИ (С4> = 65; «Зачтено»; «Не зачтено»))

Внешний ЕСЛИ запускается первым и проверяет, является ли ячейка C4 пустой. Если это так, внешний ЕСЛИ возвращает «Неявка», а внутренний ЕСЛИ никогда не запускается.

Если ячейка не пуста, внешний ЕСЛИ возвращает ЛОЖЬ, и запускается вторая функция ЕСЛИ.

4. Вложенный ЕСЛИ (IF) для шкал

Вам часто будут встречаться вложенные ЕСЛИ, настроенные для обработки «шкал» … например, для назначения оценок, стоимости доставки, налоговых ставок или других значений, которые изменяются в шкале с числовым вводом. Пока в шкале не слишком много уровней, вложенные ЕСЛИ работают нормально. Но нужно быть внимательными, иначе формула может стать не читабельной.

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

С четко понятными условиями мы можем ввести первый оператор ЕСЛИ:

= ЕСЛИ (С5 <64;»Неуд.»)

Мы позаботились о «Неуд.». Теперь, чтобы обработать «Удовл.», нам нужно добавить еще одно условие:

= ЕСЛИ (С5 <64; «Неуд.»; ЕСЛИ (С5 <73; «Удовл.»))

Обратите внимание, что я просто добавил еще один ЕСЛИ в первый для «ложного» результата. Чтобы расширить формулу для обработки оценки «Хорошо», мы повторяем процесс:

= ЕСЛИ (С5 <64; «Неуд.»; ЕСЛИ (С5 <73; «Удовл.»; ЕСЛИ (С5 <90; «Хорошо»)))

Мы обработали все оценки и дошли до последнего уровня «Отлично». Вместо добавления еще одного ЕСЛИ, просто добавьте итоговую оценку для ЛОЖЬ.

= ЕСЛИ (С5 <64; «Неуд.»; ЕСЛИ (С5 <73; «Удовл.»; ЕСЛИ (С5 <90; «Хорошо»; «Отлично»)))

Вот последняя вложенная формула ЕСЛИ в действии:

5. Логика вложенных ЕСЛИ

Многие формулы решаются изнутри, потому что «внутренние» функции или выражения должны решаться в первую очередь, чтобы остальная часть формулы работала.

Вложенные ЕСЛИ имеют свою логику, поскольку «внешние» ЕСЛИ действуют как ворота к «внутренним» ЕСЛИ. Это означает, что результаты внешних ЕСЛИ определяют, работают ли внутренние ЕСЛИ. Диаграмма ниже визуализирует логический ход формулы расчета оценок выше.

6. Используйте функцию «Вычислить формулу»

В Windows вы можете использовать функцию «Вычислить формулу», чтобы шаг за шагом посмотреть, как Excel решает ваши формулы. Это отличный способ «увидеть» логический поток более сложных формул и устранить неполадки, если что-то не работает. Кнопку «Вычислить формулу» можно найти на ленте на вкладке Формулы.

На экране ниже показано «окно Вычисление формулы», открытое и готовое к работе. Каждый раз, когда вы нажимаете кнопку «Вычислить», вычисляется «следующий шаг» в формуле.

К сожалению, версия Excel для Mac не содержит этой функции, но вы можете использовать прием, описанный ниже.

7. Используйте F9, чтобы определить результаты проверки

Когда вы выбираете выражение в строке формул и нажимаете клавишу F9, Excel решает только выбранную часть. Это крутой способ проверить, что на самом деле делает формула. В приведенном ниже примере я использую окна с подсказками для выбора различных частей формулы. Затем нажимаю F9, чтобы увидеть, результат вычисления этой части формулы:

Используйте Ctrl + Z (Command + Z на Mac), чтобы отменить F9. Вы также можете нажать Esc, чтобы выйти из редактора формул без каких-либо изменений.

8. Помни об ограничениях

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

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

9. Расставляй круглые скобки как профессионал

Одной из проблем с вложенными ЕСЛИ является сопоставление или «балансировка» скобок. Если круглые скобки стоят не там, где нужно или их меньше, чем требует формула, результат вы не получите. К счастью, Excel предоставляет несколько инструментов, которые помогут вам убедиться, что круглые скобки «сбалансированы» при редактировании формул.

Во-первых, если у вас несколько наборов скобок, круглые скобки имеют цветовую кодировку, поэтому открывающие скобки соответствуют закрывающим скобкам. Эти цвета нелегко рассмотреть, но при желании — можно:

Во-вторых, когда вы закроете круглые скобки, Excel выделит соответствующую пару. Вы также можете щелкнуть в формуле и использовать клавишу со стрелкой для перемещения по скобкам, и Excel будет выделять обе скобки, когда есть совпадающая пара. Если нет совпадений, вы не увидите жирного шрифта.

К сожалению, выделение шрифтом — это функция только для Windows. Если вы используете Excel на Mac для редактирования сложных формул, иногда имеет смысл скопировать и вставить формулу в хороший текстовый редактор, чтобы получить лучшие инструменты для сопоставления скобок. Вы можете вставить формулу обратно в Excel после того, как вы все исправите.

10. Используйте окно подсказки для навигации и выбора

Когда дело доходит до навигации и редактирования вложенных ЕСЛИ, окно подсказки — ваш лучший друг. С его помощью вы можете перемещаться и точно выбирать все аргументы во вложенном ЕСЛИ:

11. Будьте осторожны с текстом и цифрами

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

= ЕСЛИ (А1 = «100»; «Зачтено»; «Не зачтено»)

Является ли результат теста в А1 действительно текстом, а не числом? Нет? Тогда не используйте кавычки с числом. В противном случае логический тест вернет ЛОЖЬ, даже если значение является проходным баллом, потому что «100» не совпадает с 100. Если тестовый балл является числовым, используйте вот такую формулу:

= ЕСЛИ (А1 = 100; «Зачтено»; «Не зачтено»)

12. Добавляйте разрывы строк, чтобы облегчить чтение вложенных ЕСЛИ

Когда вы работаете с формулой, которая содержит много уровней вложенных ЕСЛИ, довольно сложно уследить за правильностью формулы. Поскольку Excel не беспокоят разрывы строк в формулах, вы можете значительно улучшить читаемость вложенных ЕСЛИ, добавив их.

Например, на приведенном ниже экране показан вложенный ЕСЛИ, который рассчитывает комиссионную ставку на основе суммы продажи. Здесь вы можете увидеть типичную вложенную ЕСЛИ-структуру, которую трудно расшифровать:

Однако, если я добавляю разрывы строк перед каждым «значением_если_ ложь», логика формулы легко читается. Кроме того, формулу легче редактировать:

Вы можете добавить разрывы строк в Windows с помощью Alt + Enter, на Mac — Control + Option + Return.

13. Уменьшите количество ЕСЛИ с И и ИЛИ

Вложенные ЕСЛИ — мощный инструмент, но формулы быстро становятся громоздкими, когда вы добавляете больше уровней. Один из способов избежать большего количества уровней — использовать ЕСЛИ в сочетании с функциями И (AND) и ИЛИ (OR). Эти функции возвращают простой результат ИСТИНА / ЛОЖЬ, который отлично работает внутри ЕСЛИ, поэтому вы можете использовать их для расширения логики одного ЕСЛИ.

Например, в приведенной ниже задаче мы хотим поставить «х» в столбце D, чтобы отметить строки, где цвет «красный», а размер «маленький».

Мы могли бы написать формулу с двумя вложенными ЕСЛИ, вот так:

= ЕСЛИ (В3 = «красный»; ЕСЛИ (С3 = «маленький»; «х»; «»); «»)

Однако, заменив одну проверку на функцию И, мы можем упростить формулу:

= ЕСЛИ (И (В3 = «красный»; С3 = «маленький»); «х»; «»)

Таким же образом, мы можем легко расширить эту формулу с помощью функции ИЛИ, чтобы проверить наличие красного ИЛИ синего И маленького:

= ЕСЛИ (И (ИЛИ (В3= «красный»; В3= «синий»);С3= «маленький»);»х»; «»)

Все то же самое можно сделать с помощью вложенных ЕСЛИ, но формула быстро станет сложной.

14. Замените вложенные ЕСЛИ на ВПР

Когда вложенный ЕСЛИ просто присваивает значения на основе одного значения, его можно легко заменить функцией ВПР (VLOOKUP). Например, этот вложенный ЕСЛИ присваивает номера пяти различным цветам:

= ЕСЛИ (F2 = «красный»; 100; ЕСЛИ (F2 = «синий»; 200; ЕСЛИ (F2 = «зеленый»; 300; ЕСЛИ (F2 = «оранжевый»; 400;500))))

Мы можем легко заменить все ЕСЛИ одним ВПР:

= ВПР (F2; В3:C7; 2; 0)

Хотя в приведенной выше формуле используется точное соответствие, вы можете легко использовать ВПР для приблизительного поиска.

15. Выберите ВЫБОР

Функция ВЫБОР (CHOOSE) может предоставить элегантное решение, когда вам необходимо отобразить простые последовательные числа (1,2,3 и т.д.) для произвольных значений.

В приведенном ниже примере ВЫБОР (CHOOSE) используется для создания пользовательских сокращений дней недели:

Конечно, вы можете использовать длинный и сложный вложенный ЕСЛИ, чтобы сделать то же самое, но, пожалуйста, не надо 🙂

16. Используйте ЕСЛИМН вместо вложенных ЕСЛИ

Если вы используете Excel 2016, у Office 365 есть новая функция, которую вы можете использовать вместо вложенных ЕСЛИ: функция ЕСЛИМН (IFS). Функция ЕСЛИМН (IFS) предоставляет специальную структуру для оценки нескольких условий без вложенности

Перепишем формулу из примера про оценки с использованием ЕСЛИМН:

= ЕСЛИМН (C5 <64; «Неуд.»; C5 <73; «Удовл.»; C5 <90; «Хорошо»; C5> = 90; «Отлично»)

Обратите внимание, в формуле всего одна пара скобок!

Что происходит, когда вы открываете электронную таблицу, которая использует функцию ЕСЛИМН (IFS) в более старой версии Excel? В Excel 2013 и 2010 (и я верю в Excel 2007, но не могу проверить) вы увидите «_xlfn» в ячейке. Ранее вычисленное значение все еще будет там, но если формула пересчитается, вы увидите ошибку #ИМЯ?.

17. Используйте МАКС

Иногда вы можете использовать МАКС (MAX) или МИН (MIN) очень интересным способом, избегая оператора ЕСЛИ (IF). Предположим, что у вас есть расчет, который должен привести к положительному числу или нулю. Другими словами, если вычисление возвращает отрицательное число, вы просто хотите показать ноль.

Функция МАКС дает вам способ сделать это без ЕСЛИ:

= МАКС (расчет; 0)

Этот метод возвращает результат вычисления, если положительный, и ноль в противном случае.

Я люблю эту конструкцию, потому что она очень проста.

18. Перехват ошибок с помощью ЕСЛИОШИБКА

Классическим использованием ЕСЛИ является перехват ошибок и предоставление другого результата при возникновении ошибки, например:

= ЕСЛИ (ЕОШИБКА (формула); значение_если_ошибка; формула)

Это уродливо и неудобно, так как одна и та же формула вводится дважды, и Excel должен вычислять одно и то же несколько раз, если ошибки нет.

В Excel 2007 была введена функция ЕСЛИОШИБКА (IFERROR), которая позволяет более элегантно отлавливать ошибки:

= ЕСЛИОШИБКА (формула; значение_если_ошибка)

Теперь, когда формула выдает ошибку, ЕСЛИОШИБКА просто возвращает указанное вами значение.

19. Используйте «логическую» логику

Вы также можете иногда избегать вложенных ЕСЛИ, используя так называемую «логическую логику». Слово логическое относится к значениям ИСТИНА / ЛОЖЬ. Хотя Excel отображает слова ИСТИНА и ЛОЖЬ в ячейках, внутренне Excel воспринимает ИСТИНА как 1, а ЛОЖЬ как ноль.

Вы можете использовать этот факт для написания умных и очень быстрых формул. Например, в приведенном выше примере с ВПР (VLOOKUP) у нас есть вложенная формула ЕСЛИ, которая выглядит следующим образом:

= ЕСЛИ (F2 = «красный»; 100; ЕСЛИ (F2 = «синий»; 200; ЕСЛИ (F2 = «зеленый»; 300; ЕСЛИ (F2 = «оранжевый»; 400;500))))

Используя логическую логику, вы можете переписать формулу следующим образом:

= (F2 = «красный») * 100 + (F2 = «синий») * 200 + (F2 = «зеленый») * 300+ (F2 = «оранжевый») * 400+ (F2 = «фиолетовый») * 500

Каждое выражение выполняет тест, а затем умножает результат теста на «значение, если оно истинно». Поскольку тесты возвращают значение ИСТИНА или ЛОЖЬ (1 или 0), результаты ЛОЖЬ фактически отменяют формулу.

Для числовых результатов логическая логика проста и чрезвычайно быстра, так как нет разветвлений. С другой стороны, логическая логика может сбивать с толку людей, которые не привыкли к ней. Тем не менее, это отличная техника, чтобы знать о ней.

Когда вам нужен вложенный ЕСЛИ?

Со всеми этими опциями для избежания вложенных ЕСЛИ, вы можете задаться вопросом: «А когда же его использовать?»

Я думаю, что вложенные ЕСЛИ имеют смысл, когда вам нужно оценить несколько различных входных данных для принятия решения.

Например, предположим, что вы хотите определить статус счета-фактуры «Оплачено», «Ожидание», «Просрочено» и т.д. Для этого необходимо посмотреть дату счета-фактуры и остаток задолженности:

В этом случае вложенный ЕСЛИ является идеальным решением.