Создание списка последовательных дат
Вы можете быстро создать список дат в последовательном порядке с помощью маркера заполнения 
или команды Заполнить.
Использование маркера заполнения
-
Выделите ячейку, содержащую первую дату. Перетащите маркер заполнения по смежным ячейкам, которые нужно заполнить последовательными датами.
-
Щелкните в нижнем правом углу ячейки маркер заполнения
и, удерживая, перетащите его, чтобы заполнить остальные ячейки в ряду. Маркер заполнения можно перетаскивать вверх, вниз и по горизонтали в пределах листа. -
Использование команды «Заполнить»
-
Выделите ячейку с первой датой. Затем выделите диапазон ячеек, которые нужно заполнить.
-
Выберите Заполнить > Прогрессия > Единицы. Выберите нужную единицу.
Совет: Даты можно сортировать так же, как любые другие данные. По умолчанию даты сортируются от самой ранней до самой поздней.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Автоматическое заполнение ячеек листа данными
Использование функций автозаполнения и мгновенного заполнения
Нужна дополнительная помощь?
Date lists are an important part of numerous Excel models. Excel can automatically recognize date-time values, format the cells, and generate a date list quickly with AutoFill or specific functions. However, date-time functions are focused on a generating a single input, just like most other Excel functions. In September 2018, Microsoft has introduced a new the concept of dynamic arrays and “spill” behavior to overcome this limitation.
The SEQUENCE function also came with this update. This function essentially generates an array of a number sequence. For example, you can generate numbers from 1 to 10 that increase by 1 at each step. Let’s see how the SEQUENCE function can help generate a date list dynamically.
Download Workbook
At the time of writing this article, Microsoft announced that this feature is currently only available to a number of select users. When it’s ready, the feature is planned to be released for Office 365 users.
SEQUENCE Function Basics
The function can generate an array of sequential numbers. You can define how many rows or columns of numbers are to be generated, the start value, and the increment of each step.
=SEQUENCE(rows,[columns],[start],[step])
|
rows |
The number of rows to be returned. |
|
[columns] |
Optional. The number of columns to be returned. The default value is 1. |
|
[start] |
Optional. Starting value. The default value is 1. |
|
[step] |
Optional. The increment of each step between values. The default value is 1. |
Date-time values in Excel
Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 0.5. As a result, adding or subtracting a number from a date value means adding or removing days from a date value.
Generating a list of days
In Excel, days are essentially numbers. All we need to do is format the number that returns the SEQUENCE function to generate the date list. However, without setting a [start] value, the SEQUENCE function starts from 1. For this, you can use a cell that already has a date value or a formula that returns a date value like TODAY(), NOW() or DATE().
We used the TODAY function in our example with 7 rows and a single column to generate 7 days starting from today (the date this article was written).
After entering the formula, the dates will be generated automatically. Remember to format generated values to display them as dates instead of numbers.
Generating a list of months
Generating a months list work in a similar way. However, since months have different day counts, generating dates by constant increments won’t work.
The DATE function has 3 arguments, the second being month. If you set 1 for month, the DATE function generates a date on January. As a result, if you enter an array of numbers from 1 to 12, you will get dates from January to December.
=DATE(2019,SEQUENCE(12),1)
The formula generates first day of each month in 2019. Apply custom number formatting «mmmm» to show only the month names.
Generating a list of years
Generating years works similarly to months. The only difference is that you need set a [start] value to avoid starting the years from the 1900s. Before setting the [start] argument, you also need to set [columns]. Here, we set this to 1 to generate a list in a single column. If you want to generate a list through columns, set rows to 1.
=DATE(SEQUENCE(10,1,2019),1,1)
This formula will generate first days of 10 consecutive years starting from 2019.
Bonus: Calendar
You can also generate a calendar using the SEQUENCE function. By using both rows and [columns] arguments, you can make the DATE function spread through multiple rows and columns just like a calendar.
We start our calendar from April 1st. To generate only the days of a month, a 7-column, 5-row range is populated.
=SEQUENCE(5,7,DATE(2019,4,1))
Note: Here, we also used conditional formatting to hide the days in May. The formula is =MONTH(H5)<>4.
Создадим последовательности дат и времен различных видов:
01.01.09, 01.02.09, 01.03.09, …, янв, апр, июл, …, пн, вт, ср, …, 1 кв., 2 кв.,…, 09:00, 10:00, 11:00, … и пр.
Т.к. каждому значению даты соответствует определенное число (см. статью
Как EXCEL хранит дату и время
), то подходы для формирования последовательностей, изложенные в статье
Числовые последовательности
, применимы и для дат. Однако, имеются и свои особенности.
Последовательность
01.01.09, 01.02.09, 01.03.09
(первые дни месяцев) можно сформировать формулой
=ДАТАМЕС(B2;СТРОКА(A1))
, в ячейке
B2
должна находиться дата — первый элемент последовательности (
01.01.09
).
Эту же последовательность можно сформировать используя правую клавишу мыши. Пусть в ячейку
A2
введено значение
01.01.09
. Выделим ячейку
A2
. Удерживая правую клавишу мыши, скопируем
Маркером заполнения
, значение из
A2
в ячейки ниже. После того, как отпустим правую клавишу мыши, появится контекстное меню, в котором нужно выбрать пункт
Заполнить по месяцам
.
Изменив формат ячеек, содержащих последовательность
01.01.09, 01.02.09, 01.03.09,
на
МММ
(см. статью
Пользовательский формат дат
) получим последовательность
янв, фев, мар, ..
.
Эту же последовательность можно ввести используя список автозаполения
(введите
янв
, затем
Маркером заполнения
скопируйте вниз).
Ячейки будут содержать не даты, как в предыдущем случае, а текстовые значения.
Аналогичным образом можно сформировать последовательность дней недели
пн, вт, ср, …
Последовательность кварталов
1 кв., 2 кв.,…
можно сформировать используя идеи из статьи
Текстовые последовательности
.
Используя инструмент
Прогрессия
, можно создать последовательности только рабочих дней. А также с шагом месяц и год.
Последовательность первых месяцев кварталов
янв, апр, июл, окт, янв, …
можно создать введя в две ячейки первые два элемента последовательности (
янв, апр
), затем (предварительно выделив их) скопировать вниз
маркером заполнения
. Ячейки будут содержать текстовые значения. Чтобы ячейки содержали даты, используйте формулу
=ДАТАМЕС($G$16;(СТРОКА(A2)-СТРОКА($A$1))*3)
Предполагается, что последовательность начинается с ячейки
G16
, формулу нужно ввести в ячейку
G17
(см.
файл примера
).
Временную последовательность
09:00, 10:00, 11:00, …
можно сформировать используя
Маркер заполнения
. Пусть в ячейку
A2
введено значение
09
:
00
. Выделим ячейку
A2
. Скопируем
Маркером заполнения
, значение из
A2
в ячейки ниже. Последовательность будет сформирована.
Если требуется сформировать временную последовательность с шагом 15 минут (
09:00, 09:15, 09:30, …
), то можно использовать формулу
=B15+1/24/60*15
(Предполагается, что последовательность начинается с ячейки
B15
, формулу нужно ввести в
B16
). Формула вернет результат в формате даты.
Другая формула
=ТЕКСТ(B15+1/24/60*15;»чч:мм»)
вернет результат в текстовом формате.
СОВЕТ:
О текстовых последовательностях вида
первый, второй, …, 1), 2), 3), …
можно прочитать в статье
Текстовые последовательности
. О числовых последовательностях вида
1, 2, 3, … 1, 3, 5, 7, … I, II, III, IV, …
можно прочитать в статье
Числовые последовательности
.
Содержание
- Последовательности дат и времен в EXCEL
- Как заполнить даты в excel. Создание списка последовательных дат.
- Использование автозаполнения
- Использование формул
- Дополнительные сведения
- List.Dates: список дат в Power Query для календаря
- Список с фиксированной датой
- Динамический столбец дат в Power Query
- Заключение
Последовательности дат и времен в EXCEL
history 10 апреля 2013 г.
Создадим последовательности дат и времен различных видов: 01.01.09, 01.02.09, 01.03.09, . янв, апр, июл, . пн, вт, ср, . 1 кв., 2 кв. 09:00, 10:00, 11:00, . и пр.
Т.к. каждому значению даты соответствует определенное число (см. статью Как EXCEL хранит дату и время ), то подходы для формирования последовательностей, изложенные в статье Числовые последовательности , применимы и для дат. Однако, имеются и свои особенности.
Последовательность 01.01.09, 01.02.09, 01.03.09 (первые дни месяцев) можно сформировать формулой =ДАТАМЕС(B2;СТРОКА(A1)) , в ячейке B2 должна находиться дата — первый элемент последовательности ( 01.01.09 ).
Эту же последовательность можно сформировать используя правую клавишу мыши. Пусть в ячейку A2 введено значение 01.01.09 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем Маркером заполнения , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши, появится контекстное меню, в котором нужно выбрать пункт Заполнить по месяцам .
Изменив формат ячеек, содержащих последовательность 01.01.09, 01.02.09, 01.03.09, на МММ (см. статью Пользовательский формат дат ) получим последовательность янв, фев, мар, .. .
Эту же последовательность можно ввести используя список автозаполения Кнопка Офис/ Параметры Excel/ Основные/ Основные параметры работы с Excel/ Изменить списки (введите янв , затем Маркером заполнения скопируйте вниз).
Ячейки будут содержать не даты, как в предыдущем случае, а текстовые значения.
Аналогичным образом можно сформировать последовательность дней недели пн, вт, ср, .
Последовательность кварталов 1 кв., 2 кв. можно сформировать используя идеи из статьи Текстовые последовательности .
Используя инструмент Прогрессия , можно создать последовательности только рабочих дней. А также с шагом месяц и год.
Последовательность первых месяцев кварталов янв, апр, июл, окт, янв, . можно создать введя в две ячейки первые два элемента последовательности ( янв, апр ), затем (предварительно выделив их) скопировать вниз маркером заполнения . Ячейки будут содержать текстовые значения. Чтобы ячейки содержали даты, используйте формулу =ДАТАМЕС($G$16;(СТРОКА(A2)-СТРОКА($A$1))*3) Предполагается, что последовательность начинается с ячейки G16 , формулу нужно ввести в ячейку G17 (см. файл примера ).
Временную последовательность 09:00, 10:00, 11:00, . можно сформировать используя Маркер заполнения . Пусть в ячейку A2 введено значение 09 : 00 . Выделим ячейку A2 . Скопируем Маркером заполнения , значение из A2 в ячейки ниже. Последовательность будет сформирована.
Если требуется сформировать временную последовательность с шагом 15 минут ( 09:00, 09:15, 09:30, . ), то можно использовать формулу =B15+1/24/60*15 (Предполагается, что последовательность начинается с ячейки B15 , формулу нужно ввести в B16 ). Формула вернет результат в формате даты.
Другая формула =ТЕКСТ(B15+1/24/60*15;»чч:мм») вернет результат в текстовом формате.
СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О числовых последовательностях вида 1, 2, 3, . 1, 3, 5, 7, . I, II, III, IV, . можно прочитать в статье Числовые последовательности .
Источник
Как заполнить даты в excel. Создание списка последовательных дат.
Если вы работаете с основанными на времени данными, вам, возможно, понадобится вставить некоторое количество дат в таблицу. Например, в приложении для отслеживания продаж вы можете ввести ряд дат для квартала, убедившись, конечно, в том, что были пропущены те даты, которые пришлись на выходные дни.
Использование автозаполнения
Наиболее эффективный способ ввести несколько дат, который не требует никаких формул, — использование функции автоматического заполнения. Введите первую дату, а затем перетащите маркер заполнения ячейки, нажимая при этом правую кнопку мыши. Отпустите кнопку мыши и выберите команду контекстного меню Заполнить по месяцам (рис. 108.1).
В некоторых ситуациях необходимо ввести две даты, прежде чем использовать функцию автозаполнения. Например, если вам требуется ввести даты, которые представляют собой идущие подряд понедельники, вы должны ввести первые две даты из серии. Затем выберите обе даты, щелкните правой кнопкой мыши и выберите в контекстном меню пункт Заполнить по дням.
Использование формул
Еще один способ ввести ряд дат основан на использовании формул. Преимущество формулы (а не функции автозаполнения) состоит в том, что вы можете изменить первую дату, а все остальные обновятся автоматически. Введите начальную дату в ячейку, а затем используйте формулы (копируя вниз по столбцу) для создания дополнительных дат. В следующих примерах предположим, что вы ввели первую дату из серии в ячейку А1, а формулу — в ячейку А2. Затем можно скопировать эту формулу вниз по столбцу столько раз, сколько это необходимо.
Для генерации серии дат, разделенных семью днями, используйте такую формулу: =A1+7 .
Для создания серии дат, разделенных одним месяцем, используйте следующую формулу: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;ДЕНЬ(A1))
Для создания серии дат, разделенных одним годом, используйте эту формулу: =ДАТА(ГОД(A1)+1;МЕСЯЦ(A1);ДЕНЬ(A1)
Для создания серии, включающей только будние дни (все дни, кроме субботы и воскресенья), используйте следующую формулу. Она предполагает, что дата в ячейке А1 не является выходным днем: =ЕСЛИ(ДЕНЬНЕД(A1)=6;A1+3;A1+1) . Эта формула возвращает серийный номер даты, так что вам нужно отформатировать ячейки для отображения дат.
Можно быстро создать список дат в последовательности с помощью Маркера заполнения или команду заполнить .
Выделите на листе ячейку, в которой необходимо начать список последовательных дат, и введите первый день или дату.
Например введите Понедельник или 6/15/08 .
Чтобы создать список дат, выполните действия, описанные ниже.
Совет: Как и любые другие данные, даты можно сортировать. По умолчанию даты сортируются в порядке от наиболее ранней до наиболее поздней.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте Excel User Voice .
Отказ от ответственности относительно машинного перевода . Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.
Источник
List.Dates: список дат в Power Query для календаря
Power Query позволяет делать множество операций с датами. В частности, мы можем создавать списки дат между фиксированными датами или список дат от начальной даты по сегодняшний/вчерашний день. Попробуем это сделать!
Список с фиксированной датой
Пример: создадим список дат за весь 2023 год.
Сначала создадим пустой запрос. Для этого выбираем в Excel вкладку Данные – Получить данные – Из других источников – Пустой запрос. Также можно создать пустой запрос из редактора Power Query с помощью команд Создать источник – Другие источники – Пустой запрос:
Чтобы приступить к созданию списка дат, на вкладке «Просмотр» щелкаете по иконке «Расширенный редактор».
Удаляем данные — Источник = «», которые записаны по умолчанию:
Чтобы получить список дат, нам нужно задать начальную и конечную дату. Создадим переменную start_date. В ней зададим дату начала, с которой будет начинаться наш список. В этом нам поможет оператор #date. Внутри оператора пропишем год (2023), месяц (1)и день (1):
Аналогично, создадим вторую переменную end_date для даты окончания нашего списка. Внутри оператора #date пропишем год (2023), месяц (12) и день (31) — #date(2023,12,31).
Теперь можно приступать непосредственно к созданию списка дат. Создадим следующую переменную dates_list.
Для генерации списка дат будем использовать функцию List.Dates, которая состоит из трёх аргументов: начальная дата, количество элементов и шаг между элементами.
Разберем подробно записанную нами функцию:
List.Dates( start_date, Duration.Days(end_date — start_date) + 1,
#duration(1, 0, 0, 0))
start_date – первая дата в списке, это созданная нами первая переменная
Duration.Days (end_date — start_date)+1) – количество дней в списке. У нас есть две даты – конечная end_date и начальная start_date, вычитанием получим количество дней. Если мы просто вычислим разницу между двумя датами, то получим duration – это специальный тип для временной длительности в Power Query. Для функции List.Dates такой тип не подходит, т.к. в качестве второго аргумента требуется указать число, а не длительность. Обойти этот нюанс нам поможет функция, способная преобразовать длительность в число, Duration.Days, аргументом которой и является duration. И конечно же надо не забыть прибавить 1, т.к вычитая из даты конечной дату начальную мы теряем дату старта.
#duration(1, 0, 0, 0) – шаг между датами. Задаем шаг с помощью оператора #duration, где указываем 1 день, 0 часов, 0 минут, 0 секунд.
Теперь результат переменной dates_list оставляем в качестве результата работы всего алгоритма. Для этого в алгоритме после in дописываем dates_list. Для подтверждения написанного кода нажимаем «Готово»:
Как результат получаем список дат за весь 2023 год:
Теперь список нужно превратить в таблицу, чтобы мы имели все атрибуты и возможности таблицы. Для этого выбираем в специальной закладке по работе со списками вкладку Преобразование – Преобразовать – в таблицу.
Осталось только переименовать столбец и поменять тип данных. Задаем столбцу новое имя «Список дат», щелкаем по пиктограмме с типом данных и выбираем тип данных – Дата:
Загружаем список дат в Excel выбрав команду «Закрыть и загрузить» на вкладке «Главная».
Динамический столбец дат в Power Query
Бывают ситуации, когда нужно получить список дат по сегодняшний или вчерашний день. Power Query поможет решить и эту задачу.
Пример: создадим список из дат с 01.06.2022 по текущую дату.
Создадим пустой запрос, как показано в примере выше. В расширенном редакторе создадим переменную dates_list и сразу запишем функцию List.Dates. Зададим конечную и начальную даты непосредственно в самой функции без создания промежуточных переменных.
Разберем все составляющие формулы подробнее:
dates_list = List.Dates( #date(2022, 6, 1), Number.From(DateTime.LocalNow() — #datetime(2022, 6, 1, 0, 0, 0)), #duration(1, 0, 0, 0))
#date(2022, 6, 1) — начало списка 2022.06.01, год 2022, месяцев 6, день 1.
Number.From(DateTime.LocalNow() — #datetime(2022, 6, 1, 0, 0, 0)) — это количество элементов в списке. Во втором аргументе мы из сегодняшнего дня вычитаем 1 июня 2022, чтобы получить нужное количество дней. Number.From помогает преобразовать в число длительность дней между сегодняшней датой и 1 июня 2022 года. DateTime.LocalNow() – будет всегда выдавать нам текущую дату. #datetime(2022, 6, 1, 0, 0, 0) будет задавать начальную дату, которую следует вычесть, где год 2022, месяцев 6, дней 1, 0 часов, 0 минут, 0 секунд).
#duration(1, 0, 0, 0) — шаг между датами. Задаем шаг с помощью оператора #duration, где указываем 1 день, 0 часов, 0 минут, 0 секунд.
Результат переменной dates_list оставляем в качестве результата работы всего алгоритма. Для этого в алгоритме после in дописываем dates_list. Для подтверждения нажимаем «Готово».
Преобразуем наш список в таблицу: вкладка Преобразование – Преобразовать – в таблицу.
Переименовываем столбец и задаем тап данных Дата.
Осталось загрузить нашу таблицу с датами в Excel командой «Закрыть и загрузить» на вкладке «Главная».
Теперь каждый день ваш список будет актуальным до текущей даты, главное не забывайте обновлять запрос!
Если бы вам требовалось получить список по вчерашнюю дату, в расширенном редакторе нужно было бы написать вот такую функцию:
Когда вы вычислите количество дней между текущей и начальной датами с помощью конструкции Number.From(DateTime.LocalNow() — #datetime(2022, 6, 1, 0, 0, 0)) , вам нужно из этого количества вычесть один день, чтобы получить «вчера».
Заключение
В этой статье мы разобрали, как создать список между двумя заданными датами. Такой список уникальных дат может понадобиться при работе с Power Pivot в Excel или с календарём в Power BI.
Также, мы рассмотрели варианты создания списков по текущую и вчерашнюю дату. Созданные в Power Query списки дают вам возможность иметь под рукой всегда актуальный и легко обновляемый календарь — как в Excel, так и в Power BI.
Источник
Если вы работаете с основанными на времени данными, вам, возможно, понадобится вставить некоторое количество дат в таблицу. Например, в приложении для отслеживания продаж вы можете ввести ряд дат для квартала, убедившись, конечно, в том, что были пропущены те даты, которые пришлись на выходные дни.
Использование автозаполнения
Наиболее эффективный способ ввести несколько дат, который не требует никаких формул, — использование функции автоматического заполнения. Введите первую дату, а затем перетащите маркер заполнения ячейки, нажимая при этом правую кнопку мыши. Отпустите кнопку мыши и выберите команду контекстного меню Заполнить по месяцам
(рис. 108.1).
В некоторых ситуациях необходимо ввести две даты, прежде чем использовать функцию автозаполнения. Например, если вам требуется ввести даты, которые представляют собой идущие подряд понедельники, вы должны ввести первые две даты из серии. Затем выберите обе даты, щелкните правой кнопкой мыши и выберите в контекстном меню пункт Заполнить по дням.
Использование формул
Еще один способ ввести ряд дат основан на использовании формул. Преимущество формулы (а не функции автозаполнения) состоит в том, что вы можете изменить первую дату, а все остальные обновятся автоматически. Введите начальную дату в ячейку, а затем используйте формулы (копируя вниз по столбцу) для создания дополнительных дат. В следующих примерах предположим, что вы ввели первую дату из серии в ячейку А1, а формулу — в ячейку А2. Затем можно скопировать эту формулу вниз по столбцу столько раз, сколько это необходимо.
Для генерации серии дат, разделенных семью днями, используйте такую формулу: =A1+7 .
Для создания серии дат, разделенных одним месяцем, используйте следующую формулу: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;ДЕНЬ(A1))
Для создания серии дат, разделенных одним годом, используйте эту формулу: =ДАТА(ГОД(A1)+1;МЕСЯЦ(A1);ДЕНЬ(A1)
Для создания серии, включающей только будние дни (все дни, кроме субботы и воскресенья), используйте следующую формулу. Она предполагает, что дата в ячейке А1 не является выходным днем: =ЕСЛИ(ДЕНЬНЕД(A1)=6;A1+3;A1+1) . Эта формула возвращает серийный номер даты, так что вам нужно отформатировать ячейки для отображения дат.
Можно быстро создать список дат в последовательности с помощью Маркера заполнения
или команду заполнить
.
Выделите на листе ячейку, в которой необходимо начать список последовательных дат, и введите первый день или дату.
Например введите Понедельник
или 6/15/08
.
Чтобы создать список дат, выполните действия, описанные ниже.
Совет:
Как и любые другие данные, даты можно сортировать. По умолчанию даты сортируются в порядке от наиболее ранней до наиболее поздней.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте Excel User Voice .
Примечания:
-
Отказ от ответственности относительно машинного перевода
. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.
Загрузка…