Как сделать функцию если то в excel

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

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.

Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.

Возвращает логическое значение ИСТИНА, если все аргументы функции вернули истинное значение. Если хотя бы один аргумент возвращает значение ЛОЖЬ, то вся функция вернет данное значение.

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

Рассмотрим таблицу истинности данной функции:

И ИСТИНА ЛОЖЬ
ИСТИНА ИСТИНА ЛОЖЬ
ЛОЖЬ ЛОЖЬ ЛОЖЬ

Синтаксис: =И(Логическое_значение1; [Логическое_значение1];…)

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

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

пример использования функции И

Во втором примере функция никогда не вернет значение ИСТИНА, т.к. условие ее второго аргумента заранее неравно.

заведомо ложное условие аргумента функции И

Особые варианты функции

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

Если на месте логического выражения оставить пустоту, то результатом функции будет выполнение действия, отвечающего за ложное выполнение алгоритма. Причиной тому служит факт, что программа ассоциирует пустое место с нулём, что на логическом языке означает «ЛОЖЬ». Если оставить пустым одно из значений, отвечающих за выполнение в случае истины или лжи, то при его выборе результатом будет «0».

функция если

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

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

3. Не допускайте изменения ссылок при копировании формулы

Способ 1: Если вам просто нужно скопировать одну формулу, выберите ее в строке формул и скопируйте в буфер обмена. Затем вы можете вставить в новом месте. Результатом будет формула, идентичная оригиналу.

Способ 2: Чтобы скопировать группу формул в новое место, не затрагивая ссылки, вы можете использовать поиск и замену.

  1. Выберите формулы, которые вы хотите скопировать
  2. Затем найдите и замените знак равенства (=) в формулах символом хеша (#). Это преобразует формулы в текст.
  3. Теперь скопируйте и вставьте формулы в новое место.
  4. После этого проделайте обратную операцию. Найдите хэш (#) и замените на знак равенства (=). Это восстановит формулы в рабочее состояние.

Текстовые функции

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

Часто у вас бывает такое, что вы пишите какой-либо текст, а когда смотрите на монитор, то выясняется, что он был написан с зажатым CAPS LOCK? Так вот в Экселе есть такая формула, которая убирает прописные буквы, заменяя их строчными.

Для того, чтобы создать такую формулу, войдите в «Текстовые» и выберите функцию «СТРОЧН». Ну а дальше выделите ту ячейку, где у вас написан ваш текст заглавными буквами. Дальше жмем «ОК» и все готово! Текст в норме. То же самое можно делать и наоборот, но за это отвечает функция «ПРОПИСН».

Текстовые формулы

Вот еще некоторые текстовые формулы, которые вам могут пригодится:

  • РУБЛЬ — присваивает числу денежный формат, подставляя в конце фирменный знак российского рубля.
  • СЖПРОБЕЛ — Удаляет лишние пробелы из текста, если вдруг случайно было поставлено два и более.
  • ЮНИСИМВ — очень интересная функция, которая преобразует число в соответствующий ему символ Юникода.

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

Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.

Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.

Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.

Исходные данные (таблицы, с которыми будем работать):

Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:

В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

Скачать все примеры функции ЕСЛИ в Excel

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

Функции Excel 2016: 10 самых важных формул

course promo

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

Объединение текстовых значений

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

Избежать данного недочета можно добавляя пробелы, либо в конце текста каждой ячейки, что вряд ли можно назвать оптимальным решением, либо непосредственно в самой формуле, куда в любое место можно вставить набор символов в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;” “;E4). Впрочем, если вы объединяете большое количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать после адреса каждой ячейки.

Раскрывающиеся списки Excel

Раскрывающиеся списки Excel
Добавление раскрывающегося списка в вашу Excel-таблицу может значительно повысить удобство работы, а значит и эффективность.
Подробнее

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(” “;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

Применение: Данная опция часто используется для склеивания ФИО, когда отдельные составные части находятся в разных колонках и есть общая сводная колонка с полным именем человека.

excel функция ОБЪЕДИНИТЬ

Выполнение условия ИЛИ

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, <, =, так и поиск конкретного значения для ячейки, которое может быть текстовым. В частности, для поиска слова «Сдал» в конкретных ячейках будет использоваться формула =ИЛИ(D4= “Сдал”; E4= “Сдал”; F4= “Сдал”)

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

excel функция ИЛИ

Поиск и использование значения

По горизонтали

Используя функцию ГПР мы можем задать поиск по конкретной строке таблицы, а на выходе получить значение из другой ячейки того же столбца (на одну или несколько строк ниже), что соответствует заданному условию. Причем поиск задается либо на точное значение (используется оператор ЛОЖЬ), либо на приблизительное (с оператором ИСТИНА), что позволяет использовать интервалы. Синтаксис =ГПР(искомое_значение; таблица; номер_строки; интервальный_просмотр)

Читайте также:  Функция СУММПРОИЗВ в Excel с примерами ее использования

Применение: Для расчета бонуса конкретному сотруднику можно задать интервалы, начиная от которых действует тот или иной процент от прибыли. Скажем, формула =ГПР(E5;$D$1:$G$2;2;ИСТИНА) будет искать в первой строке таблицы из интервала D1:G2 значение, приблизительно схожее со значением из ячейки E5, а результатом формулы станет вывод ячейки со второй строки соответствующего столбца.

excel функция ГПР

По вертикали

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

То есть для формулы =ВПР(E4;$I$3:$J$6;2;ИСТИНА) будет сравниваться значение ячейки E4 с ячейками столбца I из таблицы интервала I3:J6, а значение будет выдаваться из соседней ячейки столбца J.

excel функция ВПР

Выполнение условия ЕСЛИ

При использовании данной функции задается конкретное условие, а следом два результата – один для случаев, если условия выполнено, а другое – наоборот. Скажем для сравнения денежных средств из двух колонок может использоваться такая формула =ЕСЛИ(C2>B2; “Превышение бюджета”; “В пределах бюджета”).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; “ДА”;ЕСЛИ(D4=2; “Нет”; “Возможно”)).

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

excel функция ЕСЛИ

Формула ранжирования

Для значения чисел можно использовать формулу РАНГ, которая выдаст величину каждого числа относительно других в заданном списке. При этом ранжирование может быть как от меньшего значения в сторону увеличения, так и обратно.

Для данной функции используется три параметра – непосредственно число, массив или ссылка на список чисел и порядок. При этом если порядок не указан или стоит значение 0, то ранг определяется в порядке убывание. Любое другое значение для порядка будет отсортировывать значения по возрастанию.

Применение: Для таблицы с доходами по месяцам можно добавить столбец с ранжированием, а в дальнейшем по этому столбцу сделать сортировку.

excel функция РАНГ

Максимум из выбранных значений

Простая, но очень полезная формула МАКС выдает наибольшее значение из списка значений. Сам список может состоять как из ячеек и/или их диапазона, так и вручную введенных чисел. Всего максимальное значение можно искать среди списка из 255 чисел.

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

excel функция МАКС

Минимум из выбранных значений

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

excel функция МИН

Среднее из выбранных значений

Для получения среднего арифметического из выбранного списка значений также есть своя формула. Однако написание ее в русском языке не столь очевидно. Звучит она как СРЗНАЧ, после чего в скобках указываются либо конкретные значения, либо ссылки на ячейки.

excel функция СРЗНАЧ

Сумма выбранных значений

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

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; “Да”; F5:F9)

excel функция СУММЕСЛИ

Примечание: Подробную информацию об использовании каждой функции Excel можно найти на официальном сайте Microsoft Office.

Что такое функции Excel и где они находятся

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

Как мы уже говорили, в программе функций много — около 10 категорий: есть математические, логические, текстовые. И специальные функции — финансовые, статистические и пр. Все функции лежат во вкладке «Формулы». Перейдя в нее, нужно нажать на кнопку «Вставить функцию» на панели инструментов, после чего запустится «Мастер функций».

Вставка функции

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

Если вы знаете, какая функция вам нужна, просто введите ее в строке формул, начиная со знака «=». Как только ввели, нажмите «Enter» для вычисления.

подсказки в Excel

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

Ниже рассмотрим основные и часто используемые формулы в Excel для экономистов: ЕСЛИ, СУММЕСЛИ, ВПР, СУММПРОИЗВ, СЧЁТ, СРЗНАЧ и МАКС/МИН.

Функция ЕСЛИ для сравнения данных

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

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

Так выглядит формула:
=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь])

  • Лог_выражение — это то, что нужно проверить или сравнить (числовые или текстовые данные в ячейках)
  • Значение_если_истина — это то, что появится в ячейке, если сравнение будет верным.
  • Значение_если_ложь — то, что появится в ячейке при неверном сравнении.

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

Пропишем формулу в столбце «Скидка» так:
=ЕСЛИ(B2=»женский»;20%;0)
И применим ко всем строкам. В ячейках, где равенство выполняется, увидим товары по скидке.

функция ЕСЛИ для текстовых значений

Так применяется функция ЕСЛИ для текстовых значений с одним условием

Функции СУММЕСЛИ и СУММЕСЛИМН

Еще одна полезная функция СУММЕСЛИ, которая позволяет просуммировать несколько числовых данных по определенному критерию. Состоит формула из 2-х частей:

  • СУММ — математическая функция сложения числовых значений. Записывается как =СУММ(ячейка/диапазон 1; ячейка/диапазон 2; …).
  • и функция ЕСЛИ, которую рассмотрели выше.

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

В формуле нужно прописать такие аргументы:

  • Выделить диапазон всех должностей сотрудников — в нашем случае B2:B10.
  • Прописываем критерий выбора через точку с запятой — “менеджер”.
  • Диапазон суммирования — это заработные платы. Указываем C2:C10.

И получаем в один клик общую сумму заработной платы менеджеров:

функции excel для экономистов

С помощью СУММЕСЛИ можно просуммировать ячейки, которые соответствуют определенному критерию

Важно! Функция СУММЕСЛИ чувствительна к правильности и точности написания критериев. Малейшая опечатка может дать неправильный результат. Это также касается названий ячеек. Формула выдаст ошибку, если написать диапазон ячеек кириллицей, а не латиницей.

Более сложный вариант этой формулы — функция СУММЕСЛИМН. По-сути, это выборочное суммирование данных, отобранных по нескольким критериям. В отличие от СУММЕСЛИ, можно использовать до 127 критериев отбора данных. Например, с помощью этой формулы легко рассчитать суммарную прибыль от поставок разных товаров сразу в несколько стран.

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

Функции ВПР и ГПР — поиск данных в большом диапазоне

Экономистам часто приходится обрабатывать огромные таблицы, чтобы получить необходимые данные для анализа. Или сводить две таблицы в одну, что тоже не редкость. Функция ВПР или, как ее еще называют, вертикальный просмотр (англ. вариант VLOOKUP) позволяет быстро найти и извлечь нужные данные в столбцах. Либо перенести данные из одной таблицы в соответствующие ячейки другой.

Синтаксис самой простой функции ВПР выглядит так:
= ВПР(искомое_значение; таблица; номер_столбца; [интервальный просмотр]).

Например, вам нужно быстро извлечь наименование товара по номеру в списке. С помощью функции ВПР это сделать очень просто:

Функции ВПР и ГПР

Функция ВПР позволяет быстро найти нужные данные и перенести их в выделенную ячейку.

В ячейке С1 мы указали номер товара. Потом выделили диапазон ячеек, где его искать (A1:B10) и написали номер столбца «2», в котором нужно взять данные. Нажали Enter и получили нужный товар в выделенной ячейке.

Есть менее популярная функция ГПР. Она работает также, как ВПР, но ищет данные в горизонтальных списках.

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

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

Функция СУММПРОИЗВ в Excel

Четвертая функция нашего списка — СУММПРОИЗВ или суммирование произведений. Поможет быстро справиться с любой экономической задачей, где есть массивы. Включает в себя возможности предыдущих формул ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН, а также позволяет провести расчеты в 255 массивах. Ее любят бухгалтеры и часто используют при расчетах заработной платы и других расходов.

Что в данном случае может быть произведениями? Как заданные диапазоны, так и целые массивы данных. Чтобы задать количество массивов, их нужно перечислить через точку с запятой, потом перемножить, а затем просуммировать. При этом, есть одно условие — массивы должны быть одинаковые по типу и длине.

Читайте также:  Как работать с макросами в Excel 2010 без программирования кода

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

Для этого используем функцию СУММПРОИЗВ и указываем 2 условия. Каждое из них берем в скобки, а между ними ставим «звездочку», которая в Excel читается как союз «и».

Запишем команду так: =СУММПРОИЗВ((A5:A11=A13)*(B5:B11=B13)*C5:C11), где

  • первое условие A5:A11=A13— диапазон поиска и наименование нужного товара
  • второе условие B5:B11=B13 — диапазон поиска и размер
  • C5:C11 — массив, из которого берется итоговая сумма

С помощью функции СУММПРОИЗВ мы узнали за пару минут, что в магазине за месяц продали футболок М-размера на 100 у.е.

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

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

19 советов по использованию вложенных формул ЕСЛИ

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

Но одна IF часто приводит к другой, и как только вы объедините более пары IF, ваши формулы могут начать выглядеть как маленькие Франкенштейны 🙂

Являются ли вложенные IF злом? Иногда они нужны? Какие есть альтернативы?

Прочтите, чтобы узнать ответы на эти и другие вопросы .

1. Базовая IF

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

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

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

Чтобы проиллюстрировать, здесь мы используем IF для проверки оценок и расчета «Успешно» для баллов не менее 65:

Базовая функция ЕСЛИ - возврат

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

Это можно прочесть так: если результат в C3 не менее 65, верните «Pass».

Обратите внимание, однако, что если оценка меньше, чем 65, IF возвращает FALSE, поскольку мы не предоставили значение, если false. Чтобы отобразить Fail для непроходных оценок, мы можем добавить Fail в качестве аргумента false, например:

Базовая функция ЕСЛИ - с добавленным значением false

Видео: Как строить логические формулы .

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

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

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

3. Простая вложенная IF

Вложенный IF — это всего лишь еще два оператора IF в формуле, где один оператор IF находится внутри другого.

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

Базовый вложенный IF

Внешний IF запускается первым и проверяет, не заполнен ли C3. Если это так, внешний IF возвращает «Incomplete», а внутренний IF никогда не выполняется.

Если счет не пусто , внешний IF возвращает FALSE, и выполняется исходная функция IF.

Изучите вложенные IF с четкое, краткое видео обучение .

4. Вложенная IF для весов.

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

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

Логический поток вложенного IF

6. Используйте Evaluate, чтобы следить за логическим процессом.

В Windows вы можете использовать Оценить функцию чтобы посмотреть, как Excel решает ваши формулы шаг за шагом. Это отличный способ «увидеть» логический поток более сложных формул и устранить неполадки, когда что-то работает не так, как вы ожидали. На приведенном ниже экране показано, что окно Evaluate открыто и готово к работе. Каждый раз, когда вы нажимаете кнопку «Оценить», вычисляется «следующий шаг» в формуле. Вы можете найти Evaluate на вкладке «Формулы» на ленте (Alt M, V).

Использование Evaluate для пошагового выполнения вложенной IF, которая присваивает оценки

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

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

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

Используя F9, проверьте вложенный IF, который присваивает оценки

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

8. Знайте свои пределы

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

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

9. Используйте круглые скобки как профессионал.

Одна из проблем с вложенными IF — это сопоставление или «балансирование» круглых скобок. Если круглые скобки указаны неправильно, ваша формула не работает. К счастью, E xcel предоставляет несколько инструментов, которые помогут вам убедиться, что скобки «сбалансированы» при редактировании формул.

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

Скобки в формулах совпадают по цвету, но их трудно увидеть

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

К сожалению, выделение жирным шрифтом доступно только в Windows. Если вы используете Excel на Mac для редактирования сложных формул, иногда имеет смысл скопировать и вставить формулу в хороший текстовый редактор ( Text Wrangler бесплатно и отлично), чтобы улучшить инструменты сопоставления скобок. Text Wrangler будет мигать при совпадении скобок, и вы можете использовать Command + B, чтобы выделить весь текст, содержащийся в скобках. Вы можете вставить формулу обратно в Excel после того, как все уладите.

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

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

Перемещение и выбор аргументов формулы с помощью подсказки на экране

Вы можете увидеть, как я часто использую окно подсказки на экране в этом видео: Как построить вложенный IF .

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

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

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

12. Добавьте разрывы строк, чтобы упростить чтение вложенных IF.

Когда вы работаете с формулой, содержащей много уровней вложенных IF, может быть сложно сохранить ясность. Поскольку Excel не заботится о «пробелах» в формулах (т.е. лишних пробелах или разрывах строк), вы можете значительно улучшить читаемость вложенных if, добавив разрывы строк.

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

Вложенные IF без разрывов строки трудно читать

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

Разрывы строк упрощают чтение вложенных IF

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

Видео: Как упростить чтение вложенных IF .

Читайте также:  Функция МВСД для определения скорости обращения денег в Excel

13. Ограничьте IF с помощью AND и OR.

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

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

ЕСЛИ с функцией И проще двух вложенных ЕСЛИ

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

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

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

Все это мог можно сделать с вложенными IF, но формула быстро станет более сложной.

Видео: ЕСЛИ это ИЛИ то

14. Замените вложенные IF на ВПР.

Когда вложенный IF просто присваивает значения на основе одного входа, его можно легко заменить на Функция ВПР . Например, этот вложенный IF присваивает номера пяти разным цветам:

Мы можем легко заменить его этой (намного более простой) ВПР:

Вложенный ЕСЛИ против ВПР

В качестве бонуса функция ВПР сохраняет значения на листе (где их можно легко изменить) вместо того, чтобы вставлять их в формулу.

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

Видео: Почему ВПР лучше вложенных IF

15. Выберите ВЫБРАТЬ.

Функция CHOOSE может предоставить элегантное решение, когда вам нужно сопоставить простые, последовательные числа (1,2,3 и т. Д.) С произвольными значениями.

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

Вложенное ЕСЛИ против функции ВЫБРАТЬ

Конечно ты мог используйте длинный и сложный вложенный IF, чтобы сделать то же самое, но, пожалуйста, не делайте этого 🙂

16. Используйте IFS вместо вложенных IF

Если вы используете Excel 2016 через Office 365, есть новая функция, которую вы можете использовать вместо вложенных IF: функция IFS. Функция IFS предоставляет специальную структуру для оценки нескольких условий. без гнездование:

Функция IFS - несколько условий без вложенности

Формула, использованная выше, выглядит так:

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

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

17. Макс.

Иногда вы можете использовать MAX или MIN очень умным способом, избегая оператора IF. Например, предположим, что у вас есть расчет, результатом которого должно быть положительное число или ноль. Другими словами, если вычисление возвращает отрицательное число, вы просто хотите показать ноль.

Функция MAX дает вам умный способ сделать это без IF в поле зрения:

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

Мне нравится эта конструкция, потому что в ней просто так просто . См. Эту статью для полной рецензии .

18. Отловить ошибки с помощью IFERROR

Классическое использование IF — это перехват ошибок и предоставление другого результата при возникновении ошибки, например:

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

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

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

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

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

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

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

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

Видео: Как использовать логическую логику в формулах Excel

Когда вам нужен вложенный IF?

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

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

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

Расчет статуса счета-фактуры с вложенной IF

В этом случае вложенный IF — прекрасное решение.

Твои мысли?

А вы? Вы IF-Ster? Вы избегаете вложенных IF? Являются ли вложенные IF злом? Поделитесь своими мыслями ниже.

Общая информация

Что же делает данная функция, для чего она нужна и какое значение имеет?

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

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

Справочный центр Windows описывает функционал этой возможности одной фразой: если это верно, то сделать это, если же не верно, то сделать иное.

Очевидно, что при таком значении функция имеет два результата.

Первый – получаемый в случае, когда сравнение верное, второй – когда сравнение неверное.

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

  • Изначальное условие, для проверки которого и применяется ЕСЛИ;
  • Правильное значение – то значение, которое будет возвращаться каждый раз, когда логические алгоритмы расценивают изначальное условия, как соответствующее истине.

Имеется и третья составляющая – ложное значение. Оно возвращается всегда, изначальное условие расценено логическими алгоритмами как ложное.

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

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

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

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

Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

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

Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().

Ошибка #ЗНАЧ может выводиться функцией ВПР(), если введенные значения аргументов функции некорректны и функция не может их обработать.

Казалось бы какие значения могут быть некорректными, если ВПР() необходимо просто сравнить одно значение с другим и присвоить ячейке данные из совпавших ячеек, но эта ошибка возникает.

Появляется ошибка #ЗНАЧ в функции ВПР() тогда, когда длина строки сравниваемой функцией слишком большая и не может быть обработана. Например, в Excel 2010 максимальная длина строки обрабатываемой функцией всего 255 символов, и если Вы будете сравнивать строки длиной 256 и более символов, то получите ошибку #ЗНАЧ.

Исправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.

Еще ошибка #ЗНАЧ может возникнуть если Вы пропустили(не указали) один из аргументов в функции.не хватает аргумента

Ошибки в формулах Excel

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

  • ### – ширины столбца недостаточно для отображения результата;
  • #ЗНАЧ! – использован недопустимый аргумент;
  • #ДЕЛ/0 – попытка разделить на ноль;
  • #ИМЯ? – программе не удалось распознать имя, которое было применено в выражении;
  • #Н/Д – значение в процессе расчета было недоступно;
  • #ССЫЛКА! – неверно указана ссылка на ячейку;
  • #ЧИСЛО! – неверные числовые значения.

6. Далеко идущие выводы

  1. Формула введенная вручную

формула excel

2. Формула с использованием данных, которые находятся в ячейках

формула excel

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

формула excel

4. Формула с использованием встроенной функции и данных, которые находятся в двух диапазонах

формула excel

Теперь вы можете:

  1. Перечислить и показать арифметические операторы для создания формулы
  2. Вводить вручную формулы
  3. Использовать встроенные функции
  4. Выбирать нужные функции из контекстного меню

На этом занятии вы познакомились с базой, на которой построены все возможности мощного вычислительного аппарата Excel.

Ссылка на основную публикацию