Что такое вложенная функция в табличных редакторах

Использование функций и вложенных функций в формулах Excel

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

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

1. Structure. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающую круглую скобку, аргументы функции, разделенные запятыми, и закрывающая круглая скобка.

3. аргументы. Аргументы могут быть числами, текстом, логическими значениями, такими как Истина или ложь, массивами, значениями ошибок, например #N/a или ссылками на ячейки. Используемый аргумент должен возвращать значение, допустимое для данного аргумента. В качестве аргументов также используются константы, формулы и другие функции.

4. всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.

Примечание: Вам не нужно вводить функции во все прописные буквы, например = «ОКРУГЛИТЬ», так как Excel автоматически заполнит ввод имени функции после нажатия кнопки «Добавить». Если вы неправильно наводите имя функции, например = СУМА (a1: A10), а не = сумм (a1: A10), Excel вернет #NAME? Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

Ввод функций Excel

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

Для упрощения создания и редактирования формул, а также для минимизации ошибок ввода и синтаксиса используйте Автозаполнение формул. После ввода знака = (знак равенства) и начальных букв функции Excel отобразит динамический раскрывающийся список допустимых функций, аргументов и имен, соответствующих этим буквам. Затем вы можете выбрать один из раскрывающегося списка, и приложение Excel введет его автоматически.

Вложение функций Excel

1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник

Как в офисе.

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

Пример задачи с использование нескольких функцией ЕСЛИ: У нас есть отчет по продажам продавцов. План продаж считается по количеству проданных единиц товара и составляет от 18 до 20шт. Каждый сотрудник получает оплату 2000 рублей. За каждую дополнительно проданную единицу товара сверх плана сотрудник получает 25 рублей, а за невыполнение плана — штрафуется по 50 рублей за единицу (например, продал 16 шт, недобрав 2 шт до минимального плана 18 шт, следовательно он получает 2000 рублей минус 2*50 рублей то есть 1900 рублей). Нам необходимо рассчитать оплату для каждого сотрудника.

Для удобства вынесем все условия в отдельную таблицу, чтобы при необходимости мы могли быстро их менять если, например, поменяется оплата или план

Нам требуется заполнить столбец «Оплата». У нас несколько условий, поэтому одним если обойтись не получится. Вот как мы будет рассматривать эти условия

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

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

Откроется диалоговое окно функции если.

Лог_выражение — это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 20 (то есть больше плана) или нет. Продажи Алексея у нас в ячейке B3 — кликните мышкой на B3 и это выражение появится в этом поле (можно просто прописать B3, но удобнее выбирать). Далее нам необходимо проверить эти продажи Алексея с планом. Напишем B3> и выберем ячейку с планом G2, У нас должно получиться выражение B3>G2

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

Должно получиться вот так B3>$G$2 или так B3>G$2

Обратите внимание, Excel автоматически вычисляет выражение и отображает его справа. В нашем случае Excel вычислил, что выражение — ИСТИНА, то есть действительно Алексей сделал продаж в количестве 35 шт, что больше верхней границе плана 20 шт. Обратите внимание, что если бы у Алексея было бы недовыполнение плана, то формулу мы бы все равно писали точно так же — гипотетически рассуждая, как если бы у Алексея был бы выполнен план.

Поехали дальше, переходим ко второму аргументу функции.

Значение_если_истина. Переведите курс в это поле. Тут нам необходимо рассчитать оплату сотруднику, если он выполнил план. Стандартная оплата 2000 (F4) плюс так как мы рассматриваем ситуацию когда план перевыполнен, нужно прибавить переработку. Для этого вычтем из всего продаж план и получим количество продаж сверх нормы (B3-G2) и умножим их на оплату за перевыполнение 25 рублей (F5). В итоге получаем следующий расчет 2000+(35-20)*25 то есть F4+(B3-G2)*F5

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

Переходим к следующему полю.

Значение_если_ложь. Итак, выше мы рассчитали оплату сотруднику, если план продаж выполнен (то есть если наше выражение в первом поле верно — возвращает истину). Если нет, то осталось два варианта: либо сотрудник точно выполнил план продаж (от 18 до 20шт), либо недовыполнил. Снова два условия, потребуется вложенная ЕСЛИ. Будет легче, если мы сначала проверим условие невыполнение плана, а оставшийся вариант будет выполнение плана.

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

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

Заполняем поле лог_выражение, как вы помните мы проверяем условие о невыполнение плана сотрудником. То есть прописываем (выбираем) B3 =ЕСЛИ(B3>$G$2;$F$4+(B3-$G$2)*$F$5;ЕСЛИ(B3

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

Источник

Использование вложенных функций в формуле

Использование функции в качестве одного из аргументов в формуле, использующей функцию, называется вложенным, и мы будем называть ее вложенной функцией. Например, при вложении функций СНВП и СУММ в аргументы функции ЕСЛИ следующая формула суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50. В противном случае она возвращает значение 0.

Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

В формулу можно вложить до 64 уровней функций.

Щелкните ячейку, в которую нужно ввести формулу.

Чтобы начать формулу с функции, щелкните Вставить функцию в .

Знак равенства ( =) будет вставлен автоматически.

В поле Категория выберите пункт Все.

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

Если вы не знаете, какую функцию использовать, можно ввести вопрос, описывающий необходимые действия, в поле Поиск функции (например, при вводе «добавить числа» возвращается функция СУММ).

Чтобы ввести другую функцию в качестве аргумента, введите функцию в поле этого аргумента.

Части формулы, отображенные в диалоговом окне Аргументы функции, отображают функцию, выбранную на предыдущем шаге.

Если щелкнуть элемент ЕСЛИ, в диалоговом окне Аргументы функции отображаются аргументы для функции ЕСЛИ. Чтобы вложить другую функцию, можно ввести ее в поле аргумента. Например, можно ввести СУММ(G2:G5) в поле Значение_если_истина функции ЕСЛИ.

Введите дополнительные аргументы, необходимые для завершения формулы.

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

Совет: Для получения дополнительных сведений о функции и ее аргументах щелкните ссылку Справка по этой функции.

После ввода всех аргументов формулы нажмите кнопку ОК.

Щелкните ячейку, в которую нужно ввести формулу.

Чтобы начать формулу с функции, щелкните Вставить функцию в .

В диалоговом окне Вставка функции в поле Выбрать категорию выберите все.

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

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

Введите дополнительные аргументы, необходимые для завершения формулы.

Завершив ввод аргументов формулы, нажмите ввод.

Примеры

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

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Источник

Вложение функций И, ИЛИ и IF в Excel

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010, 2007; Excel для Office 365, Excel Online и Excel для Mac.

Создайте заявление Excel IF

При использовании функций IF, AND и OR одно или все условия должны быть истинными, чтобы функция возвращала ИСТИННЫЙ ответ. Если нет, функция возвращает FALSE в качестве значения.

Для функции ИЛИ (см. Строку 2 на рисунке ниже), если одно из этих условий выполняется, функция возвращает значение ИСТИНА. Для функции AND (см. Строку 3) все три условия должны быть истинными, чтобы функция возвращала значение TRUE.

На изображении ниже строки с 4 по 6 содержат формулы, в которых функции AND и OR вложены в функцию IF.

В этом примере три условия проверяются по формулам в строках 2 и 3:

Изменить вывод формулы

Во всех формулах в строках 4–6 функции «И» и «ИЛИ» идентичны своим аналогам в строках 2 и 3 в том, что они проверяют данные в ячейках с А2 по А4, чтобы определить, удовлетворяет ли это требуемому условию.

Функция IF используется для управления выводом формулы на основе того, что введено для второго и третьего аргументов функции. Примерами этого вывода могут быть текст, отображаемый в строке 4, число, отображаемое в строке 5, вывод из формулы или пустая ячейка.

С другой стороны, формула IF / OR в четвертой строке возвращает текстовую инструкцию Data Correct по одной из двух причин:

Значение OR вернуло значение TRUE — значение в ячейке A3 не равно 75.

Затем функция IF использовала этот результат для возврата аргумента Value_if_false: Data Correct.

Источник

Вложенные функции Excel

При изучении программы Excel сталкиваешься с тем, что необходимо отработать навыки работы с вложенными функциями. Когда учащиеся идут в ВУЗы, им недостаточно того минимального набора знаний, предусмотренного основной программой, поэтому в 10 математическом классе у нас идет углубленный курс изучения Excel. Функция – это специальная, заранее подготовленная формула, которая выполняет операции над заданными значениями и возвращает результат. Значение, над которыми функция выполняет операции, является аргументом. В качестве аргументов могут выступать числа, текст, логические значения или ссылки. Так же аргументы могут быть представлены константами или формулами. Формулы в свою очередь могут содержать другие функции, т.е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной функцией.

Математические, логические и текстовые функции я изучаю следующим образом: сначала объясняю способ вложения функции в другие функции. Рассматриваем ограничение по использованию аргументов отдельных математических функций, возможность максимального вложения в формуле до 7 функций. А далее идет самостоятельная работа учащихся. У каждого учащегося на столе лист с несколькими вариантами заданий. Одновременно все делают разные задания, а затем смещаются вкруговую по номеру задания. Таким образом, исключается списывание у соседа, но за урок каждый сделает все задания, отработав навык работы с функциями. У меня на столе лежат варианты ответов на каждое задание, поэтому учащиеся могут быстро проверить правильность введения формул. Я предлагаю следующие задания:

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

Составить прототип игры «Угадай число» в диапазоне от 10 до 20, используя функции ЕСЛИ и И. Расширьте диапазон до 50. В качестве ключевых слов используйте МАЛО, УГАДАЛ, МНОГО.

Используя функцию ЕСЛИ рассчитать остаток денег семьи после покупок в 1000, 5000, 25000, 30000 тенге. Первоначальный бюджет равен 27000 тенге. В соответствии со значениями расходов вывести надписи ЕЩЕ ПОШИКУЕМ, ДО КОНЦА МЕСЯЦА ДОТЯНЕМ, ХВАТИТ ТОЛЬКО НА ХЛЕБ, ДЕНЕГ НЕ ХВАТИТ.

Проверьте свое физическое состояние после измерения температуры. В соответствии со значениями до 37°, 37°, >37°, вывести надписи Я ЗДОРОВ, Я ПРИБОЛЕЛ, Я БОЛЕН.

С помощью логических функций проверьте срок хранения продукта в зависимости от температуры: 0-10°, 11-20°, от 21° и выше. Выведите соответствующие надписи: 1 МЕСЯЦ, 15 СУТОК, НЕ БОЛЕЕ СУТОК.

Выясните, к какому типу гор относятся возвышенности в 123, 456, 549, 1058, 2495 м, гора Белуха – 4506 м над уровнем океана, если учесть, что более 2000 м –ВЫСОКИЕ ГОРЫ, от 800 до 2000 м – СРЕДНЕВЫСОТНЫЕ ГОРЫ, от 500 до 800 м – НИЗКИЕ ГОРЫ, от 200 до 500 м – ВОЗВЫШЕННЫЕ НИЗМЕННОСТИ, до 200 м – НИЗМЕННОСТИ.

Математические функции.

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

4sin 3 x + 4sin 2 x
-p/2 + √((р/2) 2 + 2)

(х + 6)(3х + х 7 )
2ln(y + √(y 2 + 4)

√(12х 3 + tgП)
log₆х – (5х + 13,6) 3 +3/8

х + 7/8 – √sin П
(а 5 + 7а)(а – 1) 3

P 1/2 + (р – 56)(р + arctg 12)
√(f 3 – 15) * (f + 6) 5

-3 cos x + x / √(x+1)
Вычислите значение выражения (0,265*58) 2 – 98,3598. Округлите получившееся число с точностью до 2 знаков.

Вычислите значение выражения 25,58 + √47,589*4,23. Округлите получившееся число до ближайшего меньшего целого.
(16х / (х 2 –16)) 2 + sin x

Найти значение выражения 15*36,25 +21,3^3и вычислить остаток от деления этого числа на 5.
(а + 4) 2 *( sin 2 x – 1,54)

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

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

Задание для самостоятельной работы.

Далее можно дать волю фантазии: из различных ячеек выбрать слова и составить свои Ф.И.О., фамилию с инициалами, самостоятельно должны подобрать формулы и выполнить подсчет символов в своей фамилии, поменять символы на строчные, найти номер позиции символа, вырезать символы справа или слева, проверить совпадение символов в 2-х разных ячейках. Обязательно даю пример с трехзначным числом, в котором необходимо поменять местами цифры. Сначала решаем его через изученные математические функции (получается примерно так: ОСТАТ(A1;10)*100+ЦЕЛОЕ(ОСТАТ(A1;100)–ОСТАТ(A1;10))+ЦЕЛОЕ(A1/100)), а затем ребята самостоятельно ищут формулу, которая переводит число в текст и решают этот же пример с текстовыми функциями. Обычно за этим следует вопрос «А почему не рассказали об этом способе раньше?»

Источник