Как вставить вложенную функцию в Excel
Обновлено: 05.07.2024
Являются ли вложенные операторы ЕСЛИ злом? Они необходимы? Есть ли альтернативы? Ответ: Да! В этой подробной статье подробно рассматриваются вложенные формулы ЕСЛИ с множеством советов и несколькими альтернативами.
Функция ЕСЛИ — одна из наиболее часто используемых функций в Excel. ЕСЛИ — это простая функция, и люди любят ЕСЛИ, потому что она дает им возможность заставить Excel отвечать при вводе информации в электронную таблицу. С IF вы можете оживить свою таблицу.
Однако одна ЕСЛИ часто приводит к другой, и если вы объедините более пары ЕСЛИ, ваши формулы могут начать напоминать маленьких Франкенштейнов :)
Являются ли вложенные операторы ЕСЛИ злом? Нужны ли они иногда? Какие есть альтернативы?
Читайте дальше, чтобы узнать ответы на эти и другие вопросы.
1. Базовый ЕСЛИ
Функция ЕСЛИ выполняет проверку и выполняет различные действия в зависимости от того, является ли результат истинным или ложным.
Обратите внимание на квадратные скобки. это означает, что аргументы являются необязательными. Однако вы должны предоставить либо значение для true, либо значение для false.
Для иллюстрации здесь мы используем ЕСЛИ для проверки баллов и расчета "пройдено" для баллов не менее 65:
Ячейка D3 в этом примере содержит следующую формулу:
Что можно прочитать так: если оценка в C3 составляет не менее 65, вернуть "Пройдено".
Однако обратите внимание, что если оценка меньше 65, ЕСЛИ возвращает ЛОЖЬ, так как мы не указали значение, если оно ложно. Чтобы отобразить «Fail» для непроходных оценок, мы можем добавить «Fail» в качестве ложного аргумента следующим образом:
2. Что означает вложенность
Вложенность просто означает объединение формул одна внутри другой, так что одна формула обрабатывает результат другой. Например, вот формула, в которой функция СЕГОДНЯ вложена в функцию МЕСЯЦ:
Функция СЕГОДНЯ возвращает текущую дату внутри функции МЕСЯЦ. Функция МЕСЯЦ берет эту дату и возвращает текущий месяц. Даже в умеренно сложных формулах часто используется вложение, поэтому в более сложных формулах вложение встречается повсеместно.
3. Простой вложенный оператор ЕСЛИ
Вложенный ЕСЛИ – это еще два оператора ЕСЛИ в формуле, где один оператор ЕСЛИ появляется внутри другого.
Для иллюстрации ниже я расширил исходную формулу «годен/не годен» для обработки «неполных» результатов, добавив функцию ЕСЛИ и вложив один ЕСЛИ внутрь другого:
Сначала запускается внешний IF и проверяет, является ли C3 пустым. Если это так, внешний ЕСЛИ возвращает "Незавершенный", а внутренний ЕСЛИ никогда не выполняется.
Если оценка не пуста, внешний ЕСЛИ возвращает ЛОЖЬ, и выполняется исходная функция ЕСЛИ.
4. Вложенный ЕСЛИ для весов
Часто можно увидеть вложенные IF, настроенные для обработки "масштабов". например, чтобы назначить оценки, стоимость доставки, налоговые ставки или другие значения, которые варьируются по шкале с числовым вводом. Пока в шкале не слишком много уровней, вложенные ЕСЛИ здесь работают нормально, но вы должны поддерживать порядок в формуле, иначе ее будет трудно читать.
Хитрость заключается в том, чтобы определить направление (от высокого к низкому или от низкого к высокому), а затем соответствующим образом структурировать условия. Например, чтобы присвоить оценки в порядке «от низкого к высокому», мы можем представить необходимое решение в следующей таблице. Обратите внимание, что для "А" нет условия, потому что после выполнения всех остальных условий мы знаем, что оценка должна быть больше 95 и, следовательно, "А".
6. Используйте Evaluate для просмотра логического потока
В Windows вы можете использовать функцию оценки, чтобы посмотреть, как Excel шаг за шагом решает ваши формулы. Это отличный способ «увидеть» логическую последовательность более сложных формул и устранить неполадки, когда что-то работает не так, как вы ожидаете. На приведенном ниже экране показано, что окно Evaluate открыто и готово к работе. Каждый раз, когда вы нажимаете кнопку «Оценить», решается «следующий шаг» в формуле. Вы можете найти Оценить на вкладке Формулы на ленте (Alt M, V).
К сожалению, версия Excel для Mac не содержит функции оценки, но вы все равно можете использовать прием F9, описанный ниже.
7. Используйте F9, чтобы просмотреть результаты проверки
Когда вы выбираете выражение в строке формул и нажимаете клавишу F9, Excel решает только выбранную часть. Это мощный способ подтвердить, что на самом деле делает формула. На приведенном ниже экране я использую всплывающие окна для выбора различных частей формулы, а затем нажимаю F9, чтобы увидеть, что эта часть решена:
Используйте Control + Z (Command + Z) на Mac, чтобы отменить F9. Вы также можете нажать Esc, чтобы выйти из редактора формул без каких-либо изменений.
8. Знай свои пределы
Excel имеет ограничения на глубину вложения функций ЕСЛИ. До Excel 2007 в Excel было разрешено до 7 уровней вложенных ЕСЛИ. В Excel 2007+ Excel поддерживает до 64 уровней.
Однако тот факт, что вы можете вкладывать множество ЕСЛИ, не означает, что вы должны. Каждый дополнительный уровень, который вы добавляете, усложняет понимание формулы и устранение неполадок. Если вы обнаружите, что работаете с вложенным IF более чем на несколько уровней, вам, вероятно, следует использовать другой подход — альтернативы см. ниже.
9. Подбирайте скобки как профессионал
Одной из проблем с вложенными операторами ЕСЛИ является сопоставление или "балансировка" скобок. Когда скобки не совпадают, ваша формула не работает. К счастью, в Excel есть пара инструментов, которые помогут вам убедиться, что скобки «сбалансированы» при редактировании формул.
Во-первых, если у вас есть более одного набора скобок, они окрашиваются в цвет, чтобы открывающие скобки соответствовали закрывающим скобкам. Эти цвета чертовски сложно увидеть, но если присмотреться, они есть:
Во-вторых (и лучше), когда вы закрываете круглые скобки, Excel ненадолго выделяет совпадающую пару жирным шрифтом. Вы также можете щелкнуть формулу и использовать клавишу со стрелкой для перемещения по скобкам, и Excel на короткое время выделит обе скобки полужирным шрифтом, если есть совпадающая пара. Если совпадений нет, жирный шрифт не будет выделен.
К сожалению, выделение полужирным шрифтом доступно только в Windows. Если вы используете Excel на Mac для редактирования сложных формул, иногда имеет смысл скопировать и вставить формулу в хороший текстовый редактор (Text Wrangler бесплатен и превосходен), чтобы получить лучшие инструменты сопоставления скобок. Text Wrangler будет мигать при совпадении скобок, и вы можете использовать Command + B, чтобы выбрать весь текст, содержащийся в скобках. Вы можете вставить формулу обратно в Excel после того, как все исправите.
10. Используйте всплывающую подсказку для навигации и выбора
Когда дело доходит до навигации и редактирования вложенных IF, вам лучше всего подойдет подсказка на экране функций. С его помощью вы можете перемещаться и точно выбирать все аргументы во вложенном ЕСЛИ:
Вы можете увидеть, как я часто использую всплывающую подсказку в этом видео: Как построить вложенный IF.
11. Будьте осторожны с текстом и цифрами
Необходимо напомнить, что при работе с функцией ЕСЛИ убедитесь, что числа и текст правильно сопоставляются. Я часто вижу такие формулы ЕСЛИ:
Является ли результат теста в A1 действительно текстом, а не числом? Нет? Тогда не используйте кавычки вокруг числа. В противном случае логический тест вернет FALSE, даже если значение является проходным баллом, потому что "100" не равно 100. Если тестовый балл является числовым, используйте это:
12. Добавление разрывов строк упрощает чтение вложенных ЕСЛИ
Когда вы работаете с формулой, которая содержит много уровней вложенных ЕСЛИ, может быть сложно сделать все правильно. Поскольку Excel не заботится о «пробелах» в формулах (т. е. о дополнительных пробелах или разрывах строк), вы можете значительно улучшить читаемость вложенных операторов if, добавив разрывы строк.
Например, на приведенном ниже экране показана вложенная ЕСЛИ, которая вычисляет комиссионную ставку на основе количества продаж. Здесь вы можете увидеть типичную вложенную структуру IF, которую трудно расшифровать:
Однако, если я добавляю разрывы строк перед каждым "значением, если ложь", логика формулы становится ясной. Кроме того, формулу легче редактировать:
Вы можете добавить разрывы строк в Windows с помощью Alt + Enter, на Mac — с помощью Control + Option + Return.
13. Ограничение ЕСЛИ с помощью И и ИЛИ
Вложенные операторы ЕСЛИ — это мощный инструмент, но он быстро усложняется по мере добавления новых уровней. Один из способов избежать дополнительных уровней — использовать ЕСЛИ в сочетании с функциями И и ИЛИ. Эти функции возвращают простой результат ИСТИНА/ЛОЖЬ, который отлично работает внутри ЕСЛИ, поэтому вы можете использовать их для расширения логики одного ЕСЛИ.
Например, в приведенной ниже задаче мы хотим поставить "x" в столбце D, чтобы отметить строки, где цвет "красный" и размер "маленький".
Мы могли бы написать формулу с двумя вложенными ЕСЛИ следующим образом:
Однако, заменив тест функцией AND, мы можем упростить формулу:
Точно так же мы можем легко расширить эту формулу с помощью функции ИЛИ, чтобы проверить красный ИЛИ синий И маленький:
Все это можно сделать с помощью вложенных ЕСЛИ, но формула быстро усложнится.
14. Заменить вложенные ЕСЛИ на ВПР
Когда вложенный ЕСЛИ просто присваивает значения на основе одного входа, его можно легко заменить функцией ВПР. Например, этот вложенный ЕСЛИ присваивает числа пяти разным цветам:
Мы можем легко заменить его на этот (намного проще) ВПР:
В качестве бонуса функция ВПР сохраняет значения на листе (где их можно легко изменить), а не встраивает в формулу.
Несмотря на то, что в приведенной выше формуле используется точное соответствие, вы также можете легко использовать функцию ВПР для оценок.
15. Выберите ВЫБРАТЬ
Функция ВЫБОР может предоставить элегантное решение, когда вам нужно сопоставить простые последовательные числа (1,2,3 и т. д.) с произвольными значениями.
В приведенном ниже примере ВЫБОР используется для создания пользовательских сокращений дней недели:
Конечно, вы можете использовать длинный и сложный вложенный оператор IF, чтобы сделать то же самое, но, пожалуйста, не делайте этого :)
16. Используйте IFS вместо вложенных IF
Если вы используете Excel 2016 через Office 365, есть новая функция, которую вы можете использовать вместо вложенных ЕСЛИ: функция IFS. Функция IFS предоставляет специальную структуру для оценки нескольких условий без вложенности:
Формула, использованная выше, выглядит следующим образом:
Обратите внимание, что у нас всего одна пара скобок!
17. Максимум
Иногда вы можете использовать MAX или MIN очень умным способом, избегая оператора IF. Например, предположим, что у вас есть вычисление, результатом которого должно быть положительное число или нуль. Другими словами, если вычисление возвращает отрицательное число, вы просто хотите показать ноль.
Функция MAX дает вам умный способ сделать это без использования ЕСЛИ где-либо в поле зрения:
Этот метод возвращает результат вычисления, если он положительный, и ноль в противном случае.
Мне нравится эта конструкция, потому что она такая простая. Полный текст статьи см. в этой статье.
18. Перехватывать ошибки с помощью ЕСЛИОШИБКА
Классическое использование IF – это перехват ошибок и предоставление другого результата при возникновении ошибки, например:
Это некрасиво и избыточно, так как одна и та же формула используется дважды, и Excel должен вычислять один и тот же результат дважды, если нет ошибки.
В Excel 2007 была представлена функция ЕСЛИОШИБКА, которая позволяет более элегантно перехватывать ошибки:
Теперь, когда формула выдает ошибку, ЕСЛИОШИБКА просто возвращает введенное вами значение.
19. Используйте логическую логику
Иногда можно также избежать вложенных операторов IF, используя так называемую "логическую логику". Слово boolean относится к значениям TRUE/FALSE. Хотя Excel отображает слова ИСТИНА и ЛОЖЬ в ячейках, внутри Excel обрабатывает ИСТИНА как 1, а ЛОЖЬ как ноль. Вы можете использовать этот факт для написания умных и очень быстрых формул. Например, в приведенном выше примере ВПР у нас есть вложенная формула ЕСЛИ, которая выглядит следующим образом:
Используя логическую логику, вы можете переписать формулу следующим образом:
Каждое выражение выполняет тест, а затем результат теста умножается на "значение, если оно истинно". Поскольку тесты возвращают либо ИСТИНА, либо ЛОЖЬ (1 или 0), результаты ЛОЖЬ фактически компенсируют формулу.
Для числовых результатов логическая логика проста и чрезвычайно быстра, так как нет ветвления. С другой стороны, логическая логика может сбивать с толку людей, которые не привыкли ее видеть. Тем не менее, это отличная техника, о которой нужно знать.
Когда вам нужен вложенный ЕСЛИ?
С учетом всех этих возможностей избежать вложенных ЕСЛИ вы можете задаться вопросом, когда имеет смысл использовать вложенные ЕСЛИ?
Я думаю, что вложенные ЕСЛИ имеют смысл, когда вам нужно оценить несколько разных входных данных, чтобы принять решение.
Например, предположим, что вы хотите рассчитать статус счета "Оплачен", "Открыт", "Просрочен" и т. д. Для этого необходимо посмотреть срок действия счета и непогашенный остаток:< /p>
В этом случае вложенный ЕСЛИ является идеальным решением.
Ваши мысли?
А вы? Вы IF-стер? Избегаете ли вы вложенных ЕСЛИ? Являются ли вложенные ЕСЛИ злом? Поделитесь своими мыслями ниже.
Использование функции в качестве одного из аргументов в формуле, использующей функцию, называется вложением, и мы будем называть эту функцию вложенной функцией. Например, при вложении функций СРЗНАЧ и СУММ в аргументы функции ЕСЛИ следующая формула суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50. В противном случае возвращается 0.
Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
В формулу можно вложить до 64 уровней функций.
Нажмите на ячейку, в которую хотите ввести формулу.
Чтобы начать формулу с функцией, нажмите "Вставить функцию" в строке формул.
Excel вставит за вас знак равенства (=).
В поле Или выберите категорию выберите Все.
Если вы знакомы с категориями функций, вы также можете выбрать категорию.
Если вы не знаете, какую функцию использовать, вы можете ввести вопрос, описывающий, что вы хотите сделать, в поле "Найти функцию" (например, "добавить числа" возвращает функцию СУММ).
Чтобы ввести другую функцию в качестве аргумента, введите нужную функцию в поле аргумента.
Части формулы, отображаемые в диалоговом окне "Аргументы функции", отражают функцию, выбранную на предыдущем шаге.
Если вы нажали ЕСЛИ, в диалоговом окне Аргументы функции отобразятся аргументы функции ЕСЛИ. Чтобы вложить другую функцию, вы можете ввести ее в поле аргумента. Например, можно ввести СУММ(G2:G5) в поле Значение_если_истина функции ЕСЛИ.
Введите любые дополнительные аргументы, необходимые для завершения формулы.
Вместо того, чтобы вводить ссылки на ячейки, вы также можете выбрать ячейки, на которые хотите сослаться. Нажмите, чтобы свернуть диалоговое окно, выберите ячейки, на которые вы хотите сослаться, а затем нажмите, чтобы снова развернуть диалоговое окно.
Совет. Чтобы получить дополнительные сведения о функции и ее аргументах, щелкните Справка по этой функции.
После ввода аргументов формулы нажмите кнопку "ОК".
Нажмите на ячейку, в которую хотите ввести формулу.
Чтобы начать формулу с функцией, нажмите "Вставить функцию" в строке формул.
В диалоговом окне "Вставить функцию" в поле "Выбрать категорию" выберите "Все".
Если вы знакомы с категориями функций, вы также можете выбрать категорию.
Чтобы ввести другую функцию в качестве аргумента, введите функцию в поле аргумента в построителе формул или непосредственно в ячейку.
Введите любые дополнительные аргументы, необходимые для завершения формулы.
После ввода аргументов формулы нажмите клавишу ВВОД.
Примеры
Ниже показан пример использования вложенных функций ЕСЛИ для присвоения буквенной оценки числовому результату теста.
Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД.При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.
На следующем рисунке показана дневная цена закрытия Standard & Poor's 500 за сентябрь 2004 года. Возможный анализ состоит в том, чтобы увидеть, во сколько раз цена закрытия была выше средней за месяц. Поэтому вам необходимо рассчитать среднее значение, прежде чем вы сможете сравнить любую цену. Вставьте функцию СРЗНАЧ внутрь другой функции, чтобы сначала вычислить среднее значение.
Когда функция вложена в другую, сначала вычисляется внутренняя функция. Затем этот результат используется в качестве аргумента внешней функции.
Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, удовлетворяющих условию. Условием в этом случае является то, что любое отдельное значение в диапазоне больше, чем (>) среднее значение диапазона. Формула в ячейке D7: =СЧЁТЕСЛИ(B5:B25, " > " & СРЗНАЧ(B5:B25)) . Функция СРЗНАЧ вычисляется первой; затем вычисляется функция СЧЁТЕСЛИ, используя в качестве аргумента значение, возвращаемое вложенной функцией.
Вложенные функции лучше вводить напрямую. Диалоговое окно «Вставить функцию» не упрощает ввод вложенной функции. Попробуй. В этом примере вы используете функцию СРЗНАЧ, чтобы найти среднее значение наибольших значений из двух наборов чисел. Вложенной функцией в этом примере является MAX. Вы дважды вводите функцию МАКС внутри функции СРЗНАЧ. Выполните следующие действия:
Введите несколько разных чисел в один столбец.
Введите несколько разных чисел в другом столбце.
Нажмите на пустую ячейку, в которой вы хотите отобразить результат.
Введите =AVERAGE(, чтобы начать запись функции.
Щелкните первую ячейку во втором наборе чисел, нажмите кнопку мыши и перетащите курсор по всем ячейкам первого набора.
Адрес этого диапазона входит в функцию MAX.
Введите закрывающую скобку, чтобы завершить первую функцию MAX.
Еще раз введите MAX(.
Щелкните первую ячейку во втором наборе чисел, нажмите кнопку мыши и перетащите курсор по всем ячейкам второго набора.
Адрес этого диапазона входит в функцию MAX.
Введите закрывающую скобку, чтобы завершить вторую функцию MAX.
На этом функция СРЗНАЧ завершается.
При использовании вложенных функций перед внешней функцией ставится знак равенства (=), если она является началом формулы. Перед любыми вложенными функциями не ставится знак равенства.
Вы можете вкладывать функции до 64 уровней.
Об этой статье
Эта статья взята из книги:
Об авторе книги:
Кен Блуттман — опытный разработчик программного обеспечения и веб-приложений, специализирующийся на Excel/VBA и веб-приложениях, ориентированных на базы данных. Его последние проекты включают крупномасштабные облачные приложения и разработку мобильных приложений.
Формулы Excel могут быть как простыми, так и более сложными. Сложность формулы зависит от сложности результата, который вы хотите получить от своих данных. В этой главе объясняются ваши варианты по всему спектру.
Читайте Excel 2013 по запросу или более 24 000 других книг и видео в Safari Books Online. Начните бесплатную пробную версию сегодня.
Эта глава из книги
Эта глава из книги
Эта глава из книги
- Понимание формул
- Создание и редактирование формул
- Ссылки на ячейки
- Использовать абсолютные ссылки на ячейки
- Используйте смешанные и трехмерные ссылки на ячейки
- Название ячеек и диапазонов
- Ввод имен и управление ими
- Упрощение формулы с помощью диапазонов
- Отображение вычислений в строке состояния
- Расчет итогов с помощью автосуммы
- Выполнить однократные вычисления
- Преобразование формул и значений
- Исправить ошибки в расчетах
- Правильные формулы
- Аудит рабочего листа
- Найти циклические ссылки
- Выполнение вычислений с использованием функций
- Создание функций и вложенных функций
- Расчет нескольких результатов
- Использование констант и функций в именах
Введение
После того как вы введете данные на лист, вы захотите добавить формулы для выполнения расчетов. Microsoft Excel может помочь вам получить нужные результаты. Формулы могут быть как очень простыми, так и более сложными. Сложность формулы зависит от сложности результата, который вы хотите получить от своих данных. Например, если вы просто хотите подсчитать объем продаж за этот месяц, формула добавит ваш номер продаж и предоставит результат.Однако если вы хотите показать продажи в этом месяце на сумму более 100 долларов США с постоянными клиентами, вам потребуется немного больше времени для разработки формулы.
Поскольку Excel автоматически пересчитывает формулы, ваши листы остаются точными и актуальными независимо от того, как часто вы меняете данные. Использование абсолютных ссылок на ячейки привязывает формулы к конкретной ячейке. Excel предоставляет множество встроенных функций для добавления к вычислениям на листе. Такие функции, как СРЗНАЧ или СУММ, позволяют выполнять быстрый расчет по формуле.
Еще один способ упростить понимание формул – использовать в них диапазоны имен. Диапазоны имен — группа выбранных ячеек, называемых диапазоном, — могут помочь вам понять ваши более сложные формулы. Гораздо проще прочитать формулу, в которой используются диапазоны имен, чем посмотреть на формулу и попытаться ее расшифровать. Excel предлагает инструмент для аудита вашего рабочего листа. Глядя на «поток» вашей формулы, вы значительно уменьшаете количество ошибок в расчетах. Вы можете увидеть, как строится ваша формула, по одному уровню за раз с помощью ряда стрелок, которые указывают, откуда формула извлекает данные. По мере разработки формулы вы можете вносить в нее исправления.
Читайте также: