Представляем пошаговое руководство как сделать последовательность в Excel, предназначенное как для начинающих, так и для и опытных пользователей. Вы научитесь создавать формулы для последовательных списков с помощью простых функций.
Хотя базовая формула ПОСЛЕД выглядит не очень интересно, в сочетании с другими функциями она выглядит совершенно иначе и представляет очень много возможностей. Узнайте, как легко создавать любые последовательности: от простых числовых рядов до сложных дат с помощью формул, функций и полезных советов.
Рассмотрим несколько примеров, как быстро и правильно сделать это.
- Возрастающая или убывающая последовательность случайных чисел
- Последовательность чисел с повторением значений
- Как сделать текстовую последовательность
- Как повторить ряд чисел несколько раз
- Как создать последовательность дат
- Перечень рабочих дней
- Как сделать список месяцев
- Как получить список лет
- Как создать временную последовательность
- Создаем календарь месяца
- Последовательность дней недели
- Последовательность определенных дней недели
- Как создать интервалы дат
Возрастающая или убывающая последовательность случайных чисел
Как вы, наверное, знаете, в Excel появилась специальная функция генерации случайных чисел СЛУЧМЕЖДУ, о которой мы говорили несколько статей назад.
Чтобы сгенерировать восходящий или нисходящий ряд случайных целых чисел, нам она понадобится для аргумента шаг в формуле ПОСЛЕД.
Например, чтобы создать список случайных чисел по возрастанию, который будет записан в таком количестве строк и столбцов, сколько указано в B1 и B2 соответственно, и начинается с целого числа в B3, формула последовательности выглядит следующим образом:
=ПОСЛЕД(B1; B2; B3; СЛУЧМЕЖДУ(1; 10))
В зависимости от того, хотите ли вы сделать шаг меньше или больше, укажите меньшее или большее число для второго аргумента функции СЛУЧМЕЖДУ.
Чтобы составить убывающую последовательность случайных чисел, аргумент шаг должен быть отрицательным. Поэтому перед функцией СЛУЧМЕЖДУ ставим знак минус:
=ПОСЛЕД(B1; B2; B3; -СЛУЧМЕЖДУ(1; 50))
Примечание. Поскольку функция СЛУЧМЕЖДУ является изменчивой, она будет генерировать новые случайные значения при каждом изменении вашего листа. В результате ваша набор случайных чисел будет постоянно меняться. Чтобы этого не произошло, вы можете использовать функцию Excel «Специальная вставка» > «Значения» , чтобы заменить формулы их значениями.
Последовательность чисел с повторением значений
Часто случается, что нужно создать числовые последовательности, в которой каждое значение будет повторяться несколько раз.
Предлагаем вам несколько формул, в которых каждое значение в прогрессии из 20 чисел повторяется дважды.
=ОКРВВЕРХ(ПОСЛЕД(20;1;1;1)/2;1)
=ОКРВВЕРХ(ПОСЛЕД(20;1;1;1)/2;1)
=ОКРВВЕРХ(СТРОКА()/2;1)
=ОКРВВЕРХ(СТРОКА(A201)/2;1)
Деление на 2 и округление полученного результата в большую сторону как раз и дает нам два одинаковых числа подряд. Если вам нужно другое количество повторов, просто замените в формулах двойку на нужное вам число.
Кроме того, здесь вы можете заметить, что есть альтернатива функции ПОСЛЕД. То есть, последовательность можно создать без этой новой функции, если у вас старая версия Excel.
Вы можете попробовать функцию СТРОКА.
Она возвращает номер либо текущей строки СТРОКА(), либо номер заданной строки СТРОКА(А201). В этом случае формулы нужно копировать вниз по столбцу на нужное число ячеек.
Чтобы не копировать формулу, вы можете вернуть сразу диапазон номеров строк. К примеру, вместо ОКРВВЕРХ(СТРОКА(A201)/2;1) вы можете записать формулу
=ОКРВВЕРХ(СТРОКА(A201:A220)/2;1)
Она заполнит значениями сразу все 20 ячеек вниз по столбцу, начиная с текущей.
Как сделать текстовую последовательность
Вы можете объединить две или более числовые последовательности в текстовые строки при помощи оператора & или функции СЦЕПИТЬ. При этом вы можете добавить в этот текст и другие символы, как показано в примере ниже.
=ПОСЛЕД(20;1;1;2)&"-"&ПОСЛЕД(20;1;2;2)
Как видите мы заполнили две прогресии чисел и затем объединили их попарно в текстовые строки.
Как повторить последовательность чисел несколько раз
Рассмотрим случай, когда необходимо создать последовательность цифр, которую затем нужно повторить несколько раз. Например: 1,2,3,1,2,3,1,2,3….
Вот два варианта формул:
=ОТБР(ОСТАТ((ПОСЛЕД(20;1;1;1)-1)/1;3)+1)
=ОТБР(ОСТАТ((СТРОКА(B1)-1)/1;3)+1)
В этих формулах число 3 обозначает, сколько элементов должно быть в вашем исходном наборе, который вы затем циклически будете повторять.
В первой формуле вы сразу указываете, сколько ячеек (20) нужно заполнить данными. Вторую формулу вы копируете вниз по столбцу на столько строк, сколько необходимо.
Как создать последовательность дат
В одной из предыдущих статей мы рассмотрели, как использовать новую функцию динамического массива ПОСЛЕД для образования числовой последовательности.
Поскольку внутри Excel даты хранятся в виде чисел, эта функция также может помочь нам получить и даты. Все, что вам нужно сделать, это правильно настроить аргументы, как описано в следующих примерах.
Примечание. Все обсуждаемые здесь формулы работают только в последних версиях Excel 365, поддерживающих динамические массивы. В преддинамических версиях Excel 2019, 2016 и 2013 используйте функцию автозаполнения.
Чтобы сгенерировать последовательность дат в Excel, установите следующие аргументы функции ПОСЛЕД:
ПОСЛЕД(строки; [столбцы]; [начало]; [шаг])
- Строки — количество строк, заполняемых датами.
- Столбцы — количество столбцов для заполнения датами.
- Начало — дата начала в формате, понятном Excel, например «01.06.2024» или «1 июнь 2024». Чтобы избежать ошибок, вы можете указать дату, используя функцию ДАТА, например ДАТА(2024; 6; 1).
- Шаг — на сколько изменяется каждая последующая дата.
Например, чтобы составить список из 10 дат, начиная с 1 июня 2024 года и увеличивая на 1 день, используйте формулу:
=ПОСЛЕД(10; 1; ДАТА(2024;6;1); 1)
Чтобы сделать формулу более универсальной вы можете ввести количество дат (B1), дату начала (B2) и шаг (B3) в заранее определенные ячейки и ссылаться на эти ячейки в своей формуле. Поскольку мы генерируем вертикальный список, количество столбцов (1) записано прямо в формуле:
=ПОСЛЕД(B1; 1; B2; B3)
Введите приведенную ниже формулу в самую верхнюю ячейку (в нашем случае A6), нажмите Enter, и результаты автоматически будут вписаны в нужные ячейки.
Примечание. При использовании формата Общий (по умолчанию) результаты будут отображаться в виде чисел. Чтобы они отображались правильно, обязательно примените формат даты ко всем ячейкам в диапазоне.
Последовательность рабочих дней
Чтобы получить перечисление только рабочих дней, оберните ПОСЛЕД в функцию РАБДЕНЬ примерно следующим образом:
РАБДЕНЬ( нач_дата -1; ПОСЛЕД( количество_дней ))
Поскольку функция РАБДЕНЬ добавляет к дате начала количество дней, указанное во втором аргументе, мы вычитаем из нее 1, чтобы сама дата начала была включена в результаты.
Например, чтобы создать последовательность рабочих дней, начиная с даты в B2, используется формула:
=РАБДЕНЬ(B2-1; ПОСЛЕД(B1))
Где B1 — сколько дней вам нужно.
Советы и примечания:
- Если дата начала — суббота или воскресенье, список дат начнется со следующего рабочего дня (как в примере выше).
- Функция Excel РАБДЕНЬ в данном случае предполагает, что суббота и воскресенье являются выходными. Чтобы настроить и выходные, и праздничные дни, используйте вместо этого функцию ЧИСТРАБДНИ.МЕЖД.
Как сделать список месяцев
Чтобы создать серию дат с шагом в один месяц, вы можете использовать эту общую формулу:
ДАТА( год ; ПОСЛЕД(12); день )
В этом случае вы указываете нужный вам год в первом аргументе функции ДАТА и день – в третьем. В качестве второго аргумента функция ПОСЛЕД возвращает числа от 1 до 12. На основе этих параметров функция ДАТА создает набор дат, как показано на скриншоте ниже.
=ДАТА(2024; ПОСЛЕД(12; 1; 0);1)
Чтобы отображались только названия месяцев, установите один из следующих пользовательских форматов даты:
- ммм — короткая форма, например, янв , фев , мар и т. д.
- мммм — полная форма, например , январь , февраль , март и т. д.
В результате в ячейках появятся только названия месяцев, но реальные значения по-прежнему будут обычными датами. Обратите внимание на выравнивание по правому краю в обеих формулах последовательности, типичное для чисел и дат в Excel:
Чтобы создать последовательность дат, которая увеличивается на один месяц и начинается с определенной даты, используйте функцию ПОСЛЕД вместе с ДАТАМЕС (EDATE в английской версии):
=ДАТАМЕС(начальная_дата ; ПОСЛЕД(12; 1; 0))
Функция ДАТАМЕС возвращает дату, которая находится через указанное количество месяцев до или после даты начала. А функция ПОСЛЕД создает массив из 12 чисел (или столько, сколько вы укажете), чтобы заставить ДАТАМЕС двигаться вперед с шагом в один месяц. Обратите внимание, что аргументу начало присвоено значение 0, поэтому дата начала включается в результаты.
Если дата начала указана в B1, формула принимает следующий вид:
=ДАТАМЕС(B1; ПОСЛЕД(12; 1; 0))
Примечание. После создания формулы не забудьте применить к результатам соответствующий формат даты , чтобы они отображались правильно.
Как получить список лет
Чтобы создать список лет, используйте следующую общую формулу:
=ДАТА(ПОСЛЕД(n ; 1 ; ГОД( начальная_дата )); МЕСЯЦ( начальная_дата ); ДЕНЬ( начальная_дата ))
Где n — количество дат, которые вы хотите сгенерировать.
В этом случае функция ДАТА(год; месяц; день) создает дату следующим образом:
- Год возвращается функцией ПОСЛЕД, которая создает массив чисел из n строк в одном столбце, начиная со значения года из параметра начальная_дата.
- Значения месяца и дня извлекаются непосредственно из даты начала.
Например, если вы введете дату начала в B1, следующая формула выведет серию из 10 дат с шагом в один год:
=ДАТА(ПОСЛЕД(10; 1; ГОД(B1)); МЕСЯЦ(B1); ДЕНЬ(B1))
После форматирования как даты результаты будут выглядеть следующим образом:
Как создать временную последовательность
Поскольку время хранится в Excel в виде десятичных дробей, представляющих долю дня, функция ПОСЛЕД может напрямую работать и со временем. Вот как в общем виде выглядит нужная нам формула:
=ПОСЛЕД( строки; столбцы; начало; шаг_времени)
Предполагая, что время начала находится в B1, вы можете использовать одну из следующих формул для создания серии из 10 временных отметок.
Особенность заключается только в аргументе шаг. Поскольку в сутках 24 часа, используйте 1/24 для увеличения на час, 1/48 для увеличения на 30 минут и так далее.
С разницей в 30 минут:
=ПОСЛЕД(10; 1; B1; 1/48)
с разницей в 1 час:
=ПОСЛЕД(10; 1; B1; 1/24)
с разницей в 2 часа:
=ПОСЛЕД(10; 1; B1; 1/12)
На скриншоте ниже показаны результаты:
Если вы не хотите заморачиваться с расчетом шага вручную, вы можете определить его с помощью функции ВРЕМЯ:
=ПОСЛЕД( строки; столбцы; начало; ВРЕМЯ( часы; минуты; секунды))
Создаем календарь месяца
В этом примере мы будем использовать функцию ПОСЛЕД вместе с ДАТАЗНАЧ и ДЕНЬНЕД, чтобы создать ежемесячный календарь, который будет автоматически обновляться в зависимости от указанных вами года и месяца.
Формула выглядит следующим образом:
=ПОСЛЕД(6;7;ДАТАЗНАЧ("1/"&B2&"/"&B1) - ДЕНЬНЕД(ДАТАЗНАЧ("1/"&B2&"/"&B1);2)+1; 1)
Отформатируйте результаты как даты, и вы получите календарь, показанный на скриншоте выше.
Если вы выполните инструкции, описанные выше, то увидите картину, которая несколько отличается от этого скриншота. Почему отображаются некоторые даты предыдущего и следующего месяца?
Чтобы скрыть эти ненужные даты, настройте правило условного форматирования для диапазона ячеек A5:G10 с помощью приведенной ниже формулы и примените белый цвет шрифта:
=МЕСЯЦ(A5)<>МЕСЯЦ(ДАТАЗНАЧ($B$2 & "1"))
Где A5 — самая левая ячейка календаря, а B2 — целевой месяц.
Подробные инструкции см. в разделе: Как изменить цвет ячейки в зависимости от значения.
Последовательность дней недели
Предположим, определенное событие происходит регулярно в один и тот же день недели. Сгенерируем список дат, которые соответствуют, например, каждому понедельнику.
Для этого нужно выбрать дату первого в нашем списке понедельника и использовать функцию ПОСЛЕД, чтобы добавлять к ней 7 дней на каждой новой временной отметке. Например:
=ДАТА(2024;6;3)+ПОСЛЕД(10;;0;7)
Результат вы можете видеть на скриншоте.
При помощи функции ДАТА мы указываем первый из интересующих нас понедельников. Далее функция ПОСЛЕД помогает циклически увеличить эту дату на 7 дней и показать 10 результатов.
Теперь чуть более сложная задача. Нужно получить список из 20 дней недели, кроме воскресенья.
Вот подходящая для этого формула:
=ДАТА(2024;6;2)+ПОСЛЕД(20;1;1;1)+ОКРВВЕРХ(ПОСЛЕД(20;1;1;1)/6;1)-1
Как и в предыдущем примере, указываем дату начала отсчета при помощи функции ДАТА и добавляем к ней элементы, которые созданы формулой ОКРВВЕРХ(ПОСЛЕД(20;1;1;1)/6;1)-1. В данном случае, это будут шесть нулей, затем шесть единиц, потом шесть двоек и так далее.
Еще один вариант последовательности дат, который может быть полезен, это перечисление нечетных рабочих дней недели (понедельник, среда, пятница), а также четных (вторник, четверг, суббота).
Список из 20 понедельников, сред и пятниц получаем при помощи формулы
=ДАТА(2024;6;2)+ПОСЛЕД(20;1;1;2)-ОКРВВЕРХ(ПОСЛЕД(20;1;0;-3)/3;3)/3
Список вторых, четвертых и шестых дней недели создаем аналогичной формулой:
=ДАТА(2024;6;2)+ПОСЛЕД(20;1;2;2)-ОКРВВЕРХ(ПОСЛЕД(20;1;0;-3)/3;3)/3
Чтобы получить список дней недели, вам может быть полезна эта статья: День недели в Excel: Полезные формулы и сокращения.
Последовательность определенных дней недели
Предположим, вам в определенный день недели каждого месяца нужно выполнить определенную работу или действие. Например, предоставить какую-то отчетность.
Формула, представленная ниже, позволяет создать последовательность дат, представляющих собой третий четверг каждого месяца.
=НАИМЕНЬШИЙ(ФИЛЬТР(ПОСЛЕД(52;1;$B$1;7); (МЕСЯЦ(ПОСЛЕД(52;1;$B$1;7))=СТРОКА(A1)));3)
Вот как это работает:
Число 3 в формуле означает, какой по счету четверг (или другой день недели) в месяце вы хотите получить.
Чтобы задать другой день недели, измените начальную дату. К примеру, указав 1 января 2024 года, вы получите третий понедельник каждого месяца.
Последовательность интервалов дат
Если вам потребуется создать список временных интервалов (например, еженедельные интервалы), вы можете найти пример такого решения ниже.
Для этого создаем две последовательности дат: первая из которых – понедельники, вторая – воскресенья. Для простоты начальную дату определяем как первый понедельник временных интервалов.
=ТЕКСТ(ПОСЛЕД(10;1;$B$1;7);"Д МММ ГГГГ")&" - "&ТЕКСТ((ПОСЛЕД(10;1;$B$1;7)+6);"Д МММ ГГГГ")
Поскольку дата в Excel хранится как число, записываем ее в виде текста в нужном нам формате при помощи функции ТЕКСТ. Более подробно об этом читайте здесь: Как преобразовать дату в текст.
Объединяем эти две последовательности дат в текстовые строки, как мы делали это ранее с обычными числами.
Надеюсь, эти примеры как создать последовательность в Excel будут вам полезны.
