Долгие годы Excel остается одной из самых распространенных офисных программ в мире. Широкий набор функций и понятный принцип работы позволяют производить расчеты, собирать, структурировать и анализировать информацию, составлять отчеты и решать другие задачи, необходимые для работы. Главный инструмент для выполнения всех этих действий – функции (формулы) Excel. Иногда для выполнения поставленной задачи необходимо внести функцию в качестве одного из элементов в формуле. Такой прием называют функцией в функции или вложенной функцией.
Функция в Excel — что это и как с ней работать
Для начала стоит разобраться, что же такое функция и какие виды функций существуют в Excel. Функция (проще говоря, формула) Excel – конструкция, состоящая из вычислительных знаков, символов и введенных значений. Каждая функция записывается начиная со знака равенства (=), который означает, что символы, написанные после него – единая формула. Вычисление ведётся от знака равенства слева направо. С полным списком функций можно ознакомиться на соответствующей странице официального сайта Microsoft – компании, выпускающей программу.
Интересно! Как вставить формулу в Эксель для расчета
В зависимости от задачи и введенных данных, функции разделяют на 12 больших групп:
- Функции совместимости
В версиях Excel 2010 и младше функции этого типа были заменены на новые, более эффективные. Согласно информации с официального сайта Microsoft: «Их по-прежнему можно использовать для совместимости с более ранними версиями Excel», тем не менее, если в обратной совместимости необходимости нет, то производитель рекомендует воспользоваться более новыми формулами.
Некоторые функции этого типа:
СЦЕПИТЬ – соединение текстовых строк в одну.
ОКРВНИЗ – округление заданное число до ближайшего меньшего по модулю.
СТАНДОТКЛОН – оценка стандартного отклонения.
- Функции кубов
Этот вид функций отвечает за аналитику и визуализацию данных. Один из самых простых, но от этого не менее полезных примеров – составление сводных таблиц.
Некоторые функции этого типа:
КУБЗНАЧЕНИЕ – агрегированное значение из куба.
КУБПОРЭЛЕМЕНТ – n-ный или ранжированный элемент в множестве.
КУБЧИСЛОЭЛМНОЖ – число элементов в множестве.
Интересно! Учетная запись Microsoft — как создать и для чего нужна
- Функции для работы с базами данных
Формулы, позволяющие эффективно составлять базы данных и пользоваться ими.
Некоторые функции этого типа:
ДСРЗНАЧ – среднее значение выбранных записей БД.
БИЗВЛЕЧЬ – извлечение записи из БД по заданному значению.
БДСУММ – сумма чисел в поле для выбранных записей БД.
- Функции даты и времени
Важный набор функций, позволяющий работать с временными данными.
Некоторые функции этого типа:
ДАТА – заданная дата в числовом формате.
ДНЕЙ360 – расчет количества дней между двумя датами.
СЕКУНДЫ – преобразование даты в числовом формате в секунды.
- Инженерные функции
Этот тип функций необходим для инженерных и научных расчетов.
Некоторые функции этого типа:
ДЕЛЬТА – проверка равенства значений.
ДВ.В.ДЕС – перевод из двоичной системы исчисления в двоичную.
ПОПРОГ – проверка значений по пограничному значению.
- Финансовые функции
Финансовые функции существуют в Excel для упрощения и увеличения точности вычислений.
Некоторые функции этого типа:
АМОРУМ – величина амортизации для учетного периода.
ЭФФЕКТ – фактическая годовая процентная ставка.
ПЛТ – регулярный платеж годичной ренты.
- Информационные функции
Благодаря функциям этого типа пользователь получает логическое (например, выраженное словом ИСТИНА) значение, отражающее информацию о местоположении, содержимом или операционной среде ячеек.
Некоторые функции этого типа:
ИНФОРМ – информация об операционной среде.
ЕОШИБКА – ИСТИНА, если аргумент ссылается на значение ошибки.
ЕЧИСЛО – ИСТИНА, если аргумент ссылается на число.
- Логические функции
Такие функции анализируют содержимое ячеек и выдают значение ИСТИНА или ЛОЖЬ в зависимости от соответствия содержимого заданным условиям.
Некоторые функции этого типа:
ЕСЛИ (логическое_выражение; [значение_если_истина]; [значение_если_ложи]) – анализ на основе заданных значений.
НЕ – меняет значение аргумента на противоположное.
ИЛИ – ИСТИНА, если хотя бы одно заданное условие выполняется.
- Функции ссылки и поиска
Необходимы для быстрой и эффективной навигации по массе данных.
Некоторые функции этого типа:
ПОИСКПОЗ – поиск значения в заданной области.
ВЫБОР – выбор значения из ряда значений.
АДРЕС – ссылка на ячейку.
- Математические и тригонометрические функции
Соответственно своему названию, этот вид функций применяется для математических и тригонометрических вычислений.
Некоторые функции этого типа:
ГРАДУСЫ – перевод значения радиан в градусы.
НОД – наибольший общий делитель.
СУММ – сумма заданных значений.
- Статистические функции
Эти функции нужны для обработки и систематизации ячеек по значениям.
Некоторые функции этого типа:
СРЗНАЧ – среднее арифметическое заданных аргументов
СЧЁТЗ – количество значений в списке аргументов.
КВАДРОТКЛ – сумма квадратов отклонений.
- Текстовые функции
Этот тип функций служит для решения задач, связанных с текстовыми значениями.
Некоторые функции этого типа:
СИМВОЛ – поиск и вставка символа по коду.
СЦЕПИТЬ – объединение нескольких текстовых значений в одно.
СТРОЧН – замена всех заглавных букв на строчные.
Функция с одним условием в Excel
Остановимся поподробнее на логической функции ЕСЛИ. Используя эту функцию с одним условием, можно получить результат сравнения значений ячеек с заданным значением (собственно, условием).
Например, представим, что нам нужно выяснить, сколько человек живет на улице Яблочная из списка работников фирмы, которых попросили оставить свой домашний адрес. У нас есть два столбца: «Фамилия» и «Улица» .
Далее нужно задать функцию «=ЕСЛИ» и ввести необходимые данные: нужное название улицы и словесные выражения результатов ИСТИНА и ЛОЖЬ. Выглядит получившаяся функция как =ЕСЛИ(B2=«Яблочная», «ДА», «НЕТ»).
Полученный результат необходимо растянуть на все строчки.
Таким образом мы получили информацию о том, кто живет на улице Яблочная, то есть увидели результат анализа ячеек по одному условию.
Функция в Excel с двумя и более условиями
В функции ЕСЛИ можно использовать и больше условий. Добавим к предыдущей таблице еще один столбец, отражающий наличие личного автомобиля.
Теперь, чтобы узнать, кто из живущих на ул. Яблочная имеет машину, зададим функцию с двумя условиями: =ЕСЛИ(B2:B7=»Яблочная»;ЕСЛИ(C2:C7=»Есть»;»+»;»-«);»-«).
Благодаря такой громоздкой формуле, включающей в себя две функции ЕСЛИ, удалось узнать, какие строки отвечают обоим условиям.
Как работать с аргументами функции в Excel
Обратим внимание на аргументы функции Excel. Аргументами называют значения, которые пользователь вводит в готовую формулу. Это могут быть числа, текст, а также другие формулы, как мы видели в примерах выше. Как правило, в функциях с одним условием пользователю очевидно, какие аргументы внесены в формулу. Сложности могут возникнуть с функциями с большим количеством условий, потому что на первый взгляд сложно отделить один аргумент от другого. Для решения этой задачи в современных версиях Microsoft Excel есть справочные функции, с помощью которых можно узнать свойства аргумента кликнув его мышкой. Кроме того, визуально аргументы основной функции отличаются от аргументов вложенной цветом.
Итак, подводя итог, можно сказать, что вложенные функции Excel – тема на первый взгляд непростая, тем не менее, умение оперировать сложносоставными функциями может в будущем значительно упростить работу с объемными массивами данных.
