В Excel тысячи функций, но большинство пользователей используют только базовые, такие как СУММ и СРЗНАЧ. Хотя эти функции подходят для простых задач, есть три функции, которые справляются с более сложными сценариями с гораздо меньшими усилиями. Функции ПОСЛЕДОВАТЕЛЬНОСТЬ, ПУСТЬ и ЛЯМБДА используются не так часто, но они решают специфические задачи, требующие неудобных обходных путей или длинных формул, которые сложно поддерживать.
Используя эти функции, вы можете создавать динамические, автономные решения, которые обновляются автоматически, вместо создания множества вспомогательных столбцов или копирования формул в десятки ячеек. Эти функции — одни из самых полезных, независимо от того, генерируете ли вы последовательные данные, управляете сложными вычислениями или создаёте многоразовые пользовательские функции. Функции Excel, которые могут сэкономить вам много работы.
Быстрые ссылки
4. Функция SEQUENCE: автоматическое создание данных
Создание динамических последовательностей чисел и дат
Функция SEQUENCE создаёт массивы серийных номеров без необходимости вручную вводить каждое значение. Нужен ли вам список идентификаторов сотрудников, номеров счетов или диапазонов дат, эта функция справится с ними без проблем.
Формула проста и понятна:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(строки, [столбцы], [начало], [шаг])
Давайте проанализируем параметры:
- ряды: Указывает нужное количество чисел по вертикали.
- столбцы: Управляет горизонтальным распределением — оставьте один столбец пустым.
- Начало: Указывает начальный номер, значение по умолчанию — 1.
- шаг: Задает шаг между числами, значение по умолчанию также равно 1.
При наличии набора данных о продажах функция SEQUENCE оказывается полезной для генерации справочных чисел. Например, следующая формула генерирует числа от 1 до 32.
=ПОСЛЕДОВАТЕЛЬНОСТЬ(32)
Аналогично, если вам нужно начать с 1001, вы можете использовать:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(32, 1, 1001)
Эта функция также полезна для последовательностей дат. Следующая формула сгенерирует двенадцать последовательных дат, начиная с 1 января. Это удобнее, чем вручную вводить даты для ежемесячных отчётов или графиков проектов.
=ПОСЛЕДОВАТЕЛЬНОСТЬ(12; 1; ДАТА(2025; 1; 1); 1)
Вы также можете создать только рабочие дни, объединив функции SEQUENCE и NUMBERS. Другая ДАТА в Excel, например WORKDAY, для более сложных сценариев планирования.
Большие массивы SEQUENCE могут замедлить работу электронных таблиц. Избегайте создания более 10,000 XNUMX значений одновременно, если это не является абсолютно необходимым. Если вам нужны большие наборы данных, рассмотрите возможность их разделения на более мелкие части или использования внешних источников данных.
3. Функция LET позволяет поддерживать сложные формулы.
Устраните повторяющиеся вычисления и улучшите читаемость.
Функция LET присваивает имена значениям в формуле. Это устраняет необходимость в повторных вычислениях и упрощает чтение. Вместо того, чтобы вводить одно и то же выражение несколько раз, можно определить его один раз и обращаться к нему по имени.
Структура предложения следует следующему шаблону:
=LET(name1, value1, [name2, value2, ...], calculation)
Вы можете определить несколько переменных, добавив дополнительные пары «имя-значение». В конечном итоге эти именованные переменные используются для получения результата.
Предположим, что у вас есть набор данных о продажах, и вы рассчитываете комиссию торгового представителя с учетом бонусов. Без LET вы бы записали:
=IF(G2*0.05>500, G2*0.05*1.1, G2*0.05)
Расчёт комиссии B2*0.05 появляется дважды. С LET всё ещё нагляднее:
=LET(комиссия; G2*0.05; ЕСЛИ(комиссия>500; комиссия*1.1; комиссия))
Он выполняет тот же расчёт, но устанавливает «комиссию» один раз в начале. Вам нужно изменить ставку комиссии только в одном месте.
Для комплексного анализа маржи прибыли метод LET оказывается более полезным. Следующий пример наглядно описывает каждый компонент.
=LET(revenue, G2, costs, L2, margin, (revenue-costs)/revenue, IF(margin>0.3, "High", IF(margin>0.15, "Medium", "Low")))
Эта формула рассчитывает рентабельность в процентах, а затем классифицирует её как высокую (выше 30%), среднюю (15–30%) или низкую (ниже 15%). Каждый компонент имеет понятное название, что упрощает логику.
Этот метод уменьшает сложность формулы вдвое. Упрощение возможности последующего исправления и изменения электронных таблиц.
2. Функция LAMBDA создает повторно используемые пользовательские функции.
Создавайте пользовательские функции для повторяющейся бизнес-логики
Функция ЛЯМБДА позволяет создавать пользовательские функции, которые можно многократно использовать в рабочей книге. Вместо того, чтобы копировать формулы повсюду, вы можете создать одну функцию, которая принимает входные данные и возвращает вычисляемые результаты.
Формула такова:
=LAMBDA(parameter1, [parameter2, ...], calculation)
Параметры действуют как заполнители: при вызове функции вы передаёте фактические значения, которые заменяют эти заполнители. В вычислении эти параметры используются для формирования выходных данных.
Предположим, вы часто рассчитываете взвешенные оценки эффективности. Вы можете создать лямбда-функцию следующего вида:
=ЛЯМБДА(продажи; квота; вес; (продажи/квота)*вес)
Функция создаёт многоразовую функцию, принимающую три входных параметра: фактические продажи, квоту продаж и весовой коэффициент. Функция возвращает взвешенный показатель эффективности, делящий продажи на квоту и умножающий на вес. Назовите эту функцию «PerformanceScore» с помощью диспетчера имён Excel.
Чтобы назвать свою LAMBDA-функцию, перейдите по ссылке Формулы > Управление именами > Новые.
Теперь вы можете вызвать эту функцию в любом месте вашей книги.
=ОценкаПроизводительности(B2, C2, 0.7)
Эта функция рассчитывает показатель эффективности с использованием предоставленных объема продаж, доли и весового коэффициента.
Для анализа регионов вы можете создать функцию, которая ранжирует регионы на основе дохода:
=LAMBDA(revenue, IF(revenue>100000, "High", IF(revenue>50000, "Medium", "Low")))
Эта функция классифицирует доход по трём уровням: высокий для сумм свыше 100,000 50,000 долларов США, средний для сумм от 100,000 50,000 до XNUMX XNUMX долларов США и низкий для сумм меньше XNUMX XNUMX долларов США. Вы можете назвать её «Доход» и использовать на всех рабочих листах следующим образом:
=Доход(J2)
Функция LAMBDA также работает с другими функциями, и Позволяет писать формулы на человеческом языке Использование описательных имен вместо неоднозначных ссылок на ячейки.
Вы можете организовать свои LAMBDA-функции в диспетчере имён, используя префиксы типа «fn_» для всех пользовательских функций (например, «fn_PerformanceScore»). Это упрощает их поиск и предотвращает конфликты с обычными именованными областями действия.
1. Я объединяю эти функции для создания эффективных решений.
Создание комплексных инструментов бизнес-анализа
Совместное использование функций SEQUENCE, LET и LAMBDA позволяет решать задачи, которые в противном случае потребовали бы использования множества вспомогательных столбцов или сложных формул массивов. Такое сочетание создаёт динамичные и удобные в поддержке решения.
Давайте рассмотрим создание инструмента прогнозирования продаж на основе данных о продажах. Следующая формула рассчитывает 12-месячный прогноз продаж для заданного начального объёма. Сначала определяются две ключевые переменные с помощью LET. В качестве базового значения продаж используется значение из ячейки G2.
=LET(базовая_продажа; G2; темп_роста; L2; ПроектЕжемесячно; LAMBDA(месяц; базовая_продажа * (1 + темп_роста)^месяц); ПроектЕжемесячно(ПОСЛЕДОВАТЕЛЬНОСТЬ(12)))
Затем вы берёте ежемесячный темп роста из L2, равный 0.04 (4%). Вы можете варьировать это значение для моделирования различных сценариев. Далее вы определяете небольшую функцию повторного использования ProjectMonthly. Эта функция рассчитывает прогнозируемый объём продаж на заданный месяц на основе базового уровня продаж и темпа роста.
Кроме того, он вызывает функцию ProjectMonthly и передаёт ей SEQUENCE(12). Это генерирует массив чисел от 1 до 12, и LAMBDA автоматически применяет свои вычисления к каждому числу в этой последовательности.
Вот удобный калькулятор вознаграждений, который рассчитывает вознаграждения на основе достижения целей.
=ЛЯМБДА(продажи;цель;LET(соотношение;продажи/цель;ЕСЛИ(соотношение>=1.2;продажи*0.08;ЕСЛИ(соотношение>=1;продажи*0.05;0))))
Начните с малого, а затем усложняйте.
Эти функции работают лучше всего при грамотном сочетании. Начните с простых приложений — используйте SEQUENCE для создания тестовых данных, LET для удаления дублирующихся вычислений и LAMBDA для часто используемых бизнес-правил. Освоив каждую функцию по отдельности, вы обнаружите естественные возможности для их комбинирования в более сложные решения.
Обучение несложное, но отдача колоссальна. Ваши электронные таблицы становятся более надёжными, их проще проверять и модифицировать при изменении бизнес-требований. Именно поэтому эти три функции особенно ценны для тех, кто регулярно работает с данными.
