Обманчивая простота функции ПОСЛЕД (SEQUENCE)
Необходимое предисловие
Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в Excel в Office 365. Если вы ещё с не ознакомились (кому лень читать - там есть видео), то очень советую сделать это сейчас, чтобы понимать о чём, собственно, идёт речь и как заполучить все эти радости в вашем Excel.
Обновление Office 365, которое подарило Microsoft Excel новый вычислительный движок с поддержкой динамических массивов, также добавило к нашему арсеналу 7 новых функций, заточенных специально для работы с массивами. Про три самых важных функции: СОРТ (SORT), ФИЛЬТР (FILTER) и УНИК (UNIQUE) я уже писал, а в этой статье хотел разобрать ещё одну весьма приятную обнову - функцию ПОСЛЕД (SEQUENCE).
Обманчивая простота
Эта функция предназначена для совершенно, на первый взгляд, банальной задачи - генерации числовых последовательностей (рядов, арифметических прогрессий) с определённым шагом и от заданного стартового значения. Синтаксис функции предельно прост:
=ПОСЛЕД(строки; [столбцы]; [начало]; [шаг])
где
- строки - число строк в диапазоне, который надо заполнить числовой последовательностью - это единственный обязательный аргумент;
- столбцы - число столбцов в диапазоне (если нужно сгенерить двумерный массив)
- начало - стартовое значение (если не задано, то 1)
- шаг - шаг изменения ряда (если не задан, то 1)
Последние два аргумента могут быть и дробными, и отрицательными числами при необходимости.
Да, я знаю - выглядит это все как-то скучновато, но не доверяйте первому обманчивому впечатлению. У этой функции есть масса практических вариантов применения - давайте рассмотрим несколько случаев, чтобы вы ухватили идею.
Пример 1. Посимвольный разбор текста
Уверен, вы не раз встречались при заполнении бланков и всевозможных анкет с необходимостью "распатронить" текст на отдельные символы по отдельным ячейкам. С нашей функцией ПОСЛЕД эта задача решается в два счёта:
В английской версии это будет
=MID(A1;SEQUENCE(1;LEN(A1));1)
Логика тут простая:
- Сначала функция ПОСЛЕД формирует простой числовой ряд 1, 2, 3 ... N, где N - длина исходного текста, определяемая функцией ДЛСТР (LEN).
- Затем функция ПСТР (MID) выдёргивает из исходного текста символы по очереди, используя сгенерированную последовательность как порядковый номер извлекаемого символа.
Для аналогичного разбора даты по ячейкам, придется добавить ещё функцию ТЕКСТ (TEXT) для преобразования даты в текст - иначе на выходе мы получим код даты, а не её саму в привычном виде. Ну, а длину в этом случае мы знаем заранее - 10 символов:
Пример 2. Сумма каждой N-ой ячейки
Классическая задача про суммирование ячеек с заданным шагом, несколько способов решения которой я уже описывал. С помощью нашей новой функции ПОСЛЕД решается просто и изящно:
Функция ПОСЛЕД (SEQUENCE) здесь генерит числовую последовательность номеров строк, откуда нам нужно взять данные: 5, 9, 13, 17, 21.
Затем функция ИНДЕКС (INDEX) вытаскивает значения по этим номерам из столбца А, и затем мы их суммируем с помощью СУММ (SUM).
Пример 3. Трансформация столбца в таблицу
Самый красивый пример - преобразование столбца с данными в двумерную таблицу одной короткой формулой:
Здесь функция ПОСЛЕД сначала формирует двумерную последовательность, где каждое число - это, по сути, номер строки из нашей таблицы данных, откуда нужно взять значение для данной ячейки:
Чтобы не привязываться к жёстко прописанному количеству блоков (людей) в таблице - мы используем функцию СЧЁТЗ (COUNTA), чтобы вычислить их количество.
А затем мы извлекаем данные из левой таблицы по номеру строки с помощью функции ИНДЕКС (INDEX). (Если вы с ней ещё, не дай бог, не знакомы, то обязательно посмотрите эту статью).
Ну, и для полной картины, преобразование исхдной таблицы в динамическую "умную" даёт нам возможность не думать про её размеры - при добавлении новых данных к ней в будущем они автоматически попадут и в результаты.
Красота!
Пример 4. Многоуровневая нумерация
Это пример на перезапускающиеся числовые последовательности. В реальной жизни такое часто встречается, например, во вложенной нумерации пунктов 1.1 - 1.2 - 1.3 и т.д.
Допустим, что нам необходимо создать шаблон для заполнения посменного расписания. Причем количество смен и количество сотрудников в смене - величины переменные и должны браться из соответствующих ячеек:
Для столбца с номером смены нам нужна последовательность с повторениями каждого числа ряда (номер смены) заданное количество раз (число сотрудников в смене). Это можно легко реализовать формулой:
В английской версии это, соответственно:
=ROUNDUP(SEQUENCE(B2*B4)/B4;0)
Здесь:
- Функция ПОСЛЕД сначала генерирует числовую последовательность 1, 2, 3 .. 15 (общее число строк, равное произведению количества смен на количество людей в каждой смене)
- Мы делим эти числа на число людей в каждой смене и получаем последовательность дробных чисел: 0.2, 0.4, 0.6, 0.8, 1, 1.2 и т.д.
- Затем мы округляем эти дробные числа до ближайшего целого функцией ОКРУГЛВВЕРХ (ROUNDUP).
Для столбца с номерами сотрудников внутри смены нам нужна формула чуть сложнее - с перезапускающейся каждые 5 ячеек заново последовательностью:
Здесь мы сначала вычисляем остаток от деления нашей последовательности 1,2,3...15 на 5 с помощью функции ОСТАТ (MOD):
А затем заменяем нули на 5 - значение из ячейки B4 с помощью функции ЕСЛИ (IF).
Дополнительно, можно подтянуть к нашему графику еще и имена сотрудников из списка с помощью уже знакомой нам функции ИНДЕКС (INDEX):
Заключение и выводы
Надеюсь, я донёс до вас мысль, что эта функция не такая бессмысленная, как многим кажется на первый взгляд, а разобранные примеры станут достаточным импульсом для самостоятельных ваших экспериментов в этом направлении. Интересными найденными вариантами применения можно делиться тут же в комментариях, если что.
Если же вы пока ещё не получили обновление, которое добавляет в ваш Excel динамические массивы и эти новые функции, то когда придет время - вы будете готовы :)
Ссылки по теме
- Динамические массивы - тихая революция в Excel
- Функции СОРТ, ФИЛЬТР и УНИК в последнем обновлении Excel
- Функция ПРОСМОТРХ - наследник ВПР
Будет ли целесообразно заменить выражение
=ЕСЛИ(ОСТАТ({1:2:3:4:5:6:7:8:9:10:11:12:13:14:15};5)=0;5;ОСТАТ({1:2:3:4:5:6:7:8:9:10:11:12:13:14:15};5))=ЕСЛИОШИБКА(1/(1/ОСТАТ({1:2:3:4:5:6:7:8:9:10:11:12:13:14:15};5));5)P.S. К сожалению, у меня пока нет доступа к новым динамическим формулам...
=ОСТАТ(ПОСЛЕД(B2*B4)-1;B4)+1
зачем знак #. (Как он работает в принципе)
Искал в сети - ничего не нашел
Заранее спасибо)