Как автоматически заполнять даты в Excel
Обновлено: 06.07.2024
< бр />
В этом руководстве показано, как автоматически заполнять последовательные или непоследовательные даты для дней недели или всей недели.
Мы также предоставляем пошаговые инструкции по автоматическому заполнению дат с разницей в один месяц или год.
Другие учебные пособия, содержащие инструкции по автозаполнению различных типов данных, а также рост и линейные ряды, см. в нашем Информационном центре автозаполнения Excel.
Как автоматически заполнять последовательные даты
► Пример: 04.04.19, 05.04.19, 06.04.19, .
Автозаполнение последовательных дат очень просто. Существует несколько способов, и мы предлагаем пошаговые инструкции для самого простого из них. Затем мы обсудим два альтернативных метода с использованием разных функций Excel, которые некоторые могут предпочесть использовать.
-
Введите дату начала в первой ячейке и отформатируйте ячейку, чтобы она выглядела так, как вы хотите.
Другие методы автоматического заполнения последовательных дат
(1) Вместо использования ЛЕВОЙ кнопки мыши перетащите маркер заполнения с помощью ПРАВОЙ кнопки мыши, а затем выберите «Дни заполнения» в появившемся меню.
Как автоматически заполнять даты любым значением шага
► Пример: 04.04.19, 07.04.19, 10.04.19, .
Есть три способа автозаполнения каждой "n-й" даты. Мы перечислили их ниже от быстрого к медленному.
- Введите вначале ДВЕ даты и отформатируйте ячейки.
- Наведите указатель мыши на нижний правый край второй ячейки, пока не появится маркер заполнения.
- Удерживая нажатой ЛЕВУЮ кнопку мыши, проведите пальцем по строке или столбцу, чтобы выбрать ячейки для автозаполнения. Отпустите мышь.
- Введите дату начала и отформатируйте ячейку.
- Наведите указатель мыши на правый нижний угол ячейки, пока не появится маркер заполнения.
- Введите первую дату и нажмите Enter. Затем выберите эту ячейку и ячейки, которые нужно заполнить.
- Следующий шаг зависит от вашей версии Excel:
- Excel 2007 и более поздние версии: нажмите значок "Заливка" в разделе "Редактирование" на вкладке "Главная" ленты и выберите "Серии" в меню "Заливка".
Как автоматически заполнять последовательные даты рабочих дней
Следуйте приведенным ниже инструкциям, чтобы автоматически заполнять последовательные дни недели (без субботы и воскресенья). Компании находят это особенно полезным.
Автозаполнение дат рабочих дней любым значением шага
Эти инструкции показывают, как автоматически заполнять каждую "n-ю" дату дня недели. Разумеется, Excel пропускает все выходные дни.
- Введите первую дату и нажмите Enter. Затем выберите эту ячейку и ячейки, которые нужно заполнить.
- Следующий шаг зависит от вашей версии Excel:
- Excel 2007 и более поздние версии: нажмите значок «Заливка» в разделе «Редактирование» на вкладке «Главная» ленты и выберите «Серии» в меню «Заливка».
До недавнего времени существовал только один простой способ создания дат в Excel — функция автозаполнения. Введение новой функции динамического массива ПОСЛЕДОВАТЕЛЬНОСТЬ сделало возможным создание ряда дат с помощью формулы. В этом руководстве подробно рассматриваются оба метода, чтобы вы могли выбрать тот, который лучше всего подходит для вас.
Как заполнить ряд дат в Excel
Если вам нужно заполнить столбец датами в Excel, самый быстрый способ – воспользоваться функцией автозаполнения.
Автозаполнение ряда дат в Excel
Заполнить столбец или строку датами, которые увеличиваются на один день, очень просто:
- Введите начальную дату в первой ячейке.
- Выберите ячейку с начальной датой и перетащите маркер заполнения (небольшой зеленый квадрат в правом нижнем углу) вниз или вправо.
Excel немедленно создаст ряд дат в том же формате, что и первая дата, которую вы ввели вручную.
Заполните столбец днями недели, месяцами или годами
Чтобы создать последовательность рабочих дней, месяцев или лет, выполните одно из следующих действий:
- Заполните столбец последовательными датами, как описано выше. После этого нажмите кнопку Параметры автозаполнения и выберите нужный параметр, например Заполнить месяцы:
- Или вы можете ввести свое первое свидание, щелкнуть правой кнопкой мыши маркер заполнения, удерживать и перетаскивать столько ячеек, сколько необходимо. Когда вы отпустите кнопку мыши, появится всплывающее контекстное меню, позволяющее выбрать нужную опцию, в нашем случае Fill Years:
Заполнить ряд дат с шагом N дней
Чтобы автоматически сгенерировать серию дней, дней недели, месяцев или лет с определенным шагом, вам нужно сделать следующее:
- Введите начальную дату в первую ячейку.
- Выберите эту ячейку, щелкните правой кнопкой мыши маркер заполнения, перетащите его через столько ячеек, сколько необходимо, а затем отпустите.
- Во всплывающем меню выберите Серии (последний элемент).
- В диалоговом окне Серия выберите интересующую единицу даты и задайте значение шага.
- Нажмите "ОК".
Как создать последовательность дат в Excel с помощью формулы
В одном из предыдущих руководств мы рассмотрели, как использовать новую функцию динамического массива SEQUENCE для создания числовой последовательности. Поскольку внутри Excel даты хранятся в виде порядковых номеров, функция также может легко создавать ряды дат. Все, что вам нужно сделать, это правильно настроить аргументы, как описано в следующих примерах.
Примечание. Все обсуждаемые здесь формулы работают только в последних версиях Excel 365, поддерживающих динамические массивы. В преддинамических версиях Excel 2019, Excel 2016 и Excel 2013 используйте функцию автозаполнения, как показано в первой части этого руководства.
Создать серию дат в Excel
Чтобы сгенерировать последовательность дат в Excel, настройте следующие аргументы функции ПОСЛЕДОВАТЕЛЬНОСТЬ:
- Строки – количество строк для заполнения датами.
- Столбцы – количество столбцов для заполнения датами.
- Начало — начальная дата в формате, понятном Excel, например "01.08.2020" или "1 августа 2020". Чтобы избежать ошибок, вы можете указать дату с помощью функции DATE, например DATE(2020, 8, 1).
- Шаг — приращение для каждой последующей даты в последовательности.
Например, чтобы составить список из 10 дат, начиная с 1 августа 2020 года и увеличивая их на 1 день, используйте следующую формулу:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(10, 1, "01.08.2020", 1)
=ПОСЛЕДОВАТЕЛЬНОСТЬ(10, 1, ДАТА(2020, 8, 1), 1)
Кроме того, вы можете ввести количество дат (B1), дату начала (B2) и шаг (B3) в предопределенные ячейки и ссылаться на эти ячейки в своей формуле. Поскольку мы генерируем список, номер столбца (1) жестко запрограммирован:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(B1, 1, B2, B3)
Введите приведенную ниже формулу в самую верхнюю ячейку (в нашем случае A6), нажмите клавишу Enter, и результаты автоматически распределятся по указанному количеству строк и столбцов.
Примечание. В формате Общий по умолчанию результаты отображаются в виде серийных номеров. Чтобы они отображались правильно, обязательно примените формат даты ко всем ячейкам в диапазоне разлива.
Сделайте серию рабочих дней в Excel
Чтобы получить ряд рабочих дней, оберните ПОСЛЕДОВАТЕЛЬНОСТЬ в функцию РАБДЕНЬ или РАБДЕНЬ.МЕЖД следующим образом:
Поскольку функция РАБДЕНЬ прибавляет количество дней, указанное во втором аргументе, к дате начала, мы вычитаем из него 1, чтобы в результаты была включена сама дата начала.
Например, для создания последовательности рабочих дней, начиная с даты в B2, используется следующая формула:
Где B1 – размер последовательности.
- Если дата начала — суббота или воскресенье, серия начнется на следующий рабочий день.
- Функция РАБДЕНЬ в Excel предполагает, что суббота и воскресенье являются выходными. Чтобы настроить собственные выходные и праздничные дни, используйте функцию РАБДЕНЬ.МЕЖД.
Создать последовательность месяцев в Excel
Чтобы создать ряд дат с шагом в один месяц, можно использовать следующую общую формулу:
В этом случае целевой год указывается в 1-м аргументе, а день — в 3-м аргументе. Для 2-го аргумента функция ПОСЛЕДОВАТЕЛЬНОСТЬ возвращает порядковые номера от 1 до 12. На основе указанных выше параметров функция ДАТА создает ряд дат, как показано в левой части снимка экрана ниже:
=ДАТА(2020, ПОСЛЕДОВАТЕЛЬНОСТЬ(12), 1)
Чтобы отображались только названия месяцев, установите один из следующих настраиваемых форматов даты для диапазона разлива:
- ммм – краткая форма, например январь, февраль, март и т. д.
- мммм – полная форма, например январь, февраль, март и т. д.
В результате в ячейках появятся только названия месяцев, но базовые значения по-прежнему будут полными датами. В обеих сериях на снимке экрана ниже обратите внимание на выравнивание по правому краю по умолчанию, типичное для чисел и дат в Excel:
Чтобы создать последовательность дат, которая увеличивается на один месяц и начинается с определенной даты, используйте функцию SEQUENCE вместе с EDATE:
Функция ДАТАМЕС возвращает дату, которая является указанным числом месяцев до или после даты начала. А функция ПОСЛЕДОВАТЕЛЬНОСТЬ создает массив из 12 чисел (или столько, сколько вы укажете), чтобы заставить ДАТАМЕС двигаться вперед с шагом в один месяц. Обратите внимание, что для аргумента start установлено значение 0, поэтому в результаты включается дата начала.
С датой начала в ячейке B1 формула принимает следующий вид:
=ДАТА(B1, ПОСЛЕДОВАТЕЛЬНОСТЬ(12, 1, 0))
Примечание. После заполнения формулы не забудьте применить соответствующий формат даты к результатам, чтобы они отображались правильно.
Создайте последовательность лет в Excel
Чтобы увеличить последовательность дат по годам, используйте следующую общую формулу:
Где n — количество дат, которые вы хотите сгенерировать.
В этом случае функция ДАТА(год, месяц, день) создает дату следующим образом:
- Год возвращается функцией ПОСЛЕДОВАТЕЛЬНОСТЬ, которая настроена на создание n строк по 1 столбцу массива чисел, начиная со значения года из start_date< /эм>. Значения
- Месяц и день извлекаются непосредственно из даты начала.
Например, если вы введете дату начала в B1, следующая формула выведет серию из 10 дат с шагом в один год:
=ДАТА(ПОСЛЕДОВАТЕЛЬНОСТЬ(10, 1, ГОД(B1)), МЕСЯЦ(B1), ДЕНЬ(B1))
После форматирования в виде дат результаты будут выглядеть следующим образом:
Создать временную последовательность в Excel
Поскольку время хранится в Excel в виде десятичных чисел, представляющих часть дня, функция ПОСЛЕДОВАТЕЛЬНОСТЬ может напрямую работать со временем.
Предполагая, что время начала находится в B1, вы можете использовать одну из следующих формул для получения серии из 10 раз. Разница только в аргументе шаг. Поскольку в сутках 24 часа, используйте 1/24 для увеличения на час, 1/48 для увеличения на 30 минут и т. д.
Интервал 30 минут:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(10, 1, B1, 1/48)
=ПОСЛЕДОВАТЕЛЬНОСТЬ(10, 1, B1, 1/24)
=ПОСЛЕДОВАТЕЛЬНОСТЬ(10, 1, B1, 1/12)
На снимке экрана ниже показаны результаты:
Если вы не хотите вычислять шаг вручную, вы можете определить его с помощью функции ВРЕМЯ:
Для этого примера мы введем все переменные в отдельные ячейки, как показано на снимке экрана ниже. Затем вы можете использовать приведенную ниже формулу для создания временного ряда с любым размером шага приращения, который вы укажете в ячейках E2 (часы), E3 (минуты) и E4 (секунды):
=ПОСЛЕДОВАТЕЛЬНОСТЬ(B2, B3, B4, ВРЕМЯ(E2, E3, E4))
Как создать ежемесячный календарь в Excel
В этом последнем примере мы будем использовать функцию SEQUENCE вместе с DATEVALUE и WEEKDAY для создания ежемесячного календаря, который будет автоматически обновляться в соответствии с указанными вами годом и месяцем.
Формула в A5 выглядит следующим образом:
=SEQUENCE(6, 7, DATEVALUE("1/"&B2&"/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1, 1)
Как работает эта формула:
Вы используете функцию ПОСЛЕДОВАТЕЛЬНОСТЬ для создания массива из 6 строк (максимально возможное количество недель в месяце) и 7 столбцов (количество дней в неделе) с датами, увеличенными на 1 день. Следовательно, аргументы rows, columns и step не вызывают вопросов.
Самая сложная часть аргумента start. Мы не можем начать наш календарь с 1-го числа целевого месяца, потому что мы не знаем, какой сегодня день недели. Итак, мы используем следующую формулу, чтобы найти первое воскресенье перед 1-м числом указанного месяца и года:
DATEVALUE("1/"&B2&"/"&B1) – ДЕНЬ НЕДЕНЬ(DATEVALUE("1/"&B2&"/"&B1)) + 1
Первая функция DATEVALUE возвращает порядковый номер, который во внутренней системе Excel представляет 1-й день месяца в B2 и год в B1. В нашем случае это 44044, что соответствует 1 августа 2020 года. На данный момент у нас есть:
44044 – ДЕНЬ НЕДЕЛИ(ДАТАЗНАЧ("1/"&B2&"/"&B1)) + 1
Функция WEEKDAY возвращает день недели, соответствующий 1-му дню целевого месяца, в виде числа от 1 (воскресенье) до 7 (суббота). В нашем случае это 7, потому что 1 августа 2020 года — суббота. И наша формула сводится к:
44044 - 7 равно 4403, что соответствует субботе, 25 июля 2020 г. Поскольку нам нужно воскресенье, мы добавляем поправку +1.
Таким образом, мы получаем простую формулу, которая выводит массив серийных номеров, начинающихся с 4404:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(6, 7, 4404, 1)
Отформатируйте результаты как даты, и вы получите календарь, показанный на снимке экрана выше. Например, вы можете использовать один из следующих форматов даты:
- д-ммм-гг для отображения дат типа 1-авг-20
- ммм д для отображения месяца и дня, например 20 августа
- d для отображения только дня
Подождите, но мы стремимся создать ежемесячный календарь. Почему отображаются некоторые даты предыдущего и следующего месяца? Чтобы скрыть эти нерелевантные даты, настройте правило условного форматирования с помощью приведенной ниже формулы и примените белый цвет шрифта:
Где A5 — крайняя левая ячейка календаря, а B2 — целевой месяц.
Вот как вы можете создать последовательность дат в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Когда вы настраиваете электронную таблицу Microsoft Excel, содержащую информацию о датах, вы ускорите свою задачу, если позволите Excel выполнить утомительную работу по заполнению столбцов с отдельными записями дат. Функция автозаполнения Microsoft Excel удобна для любого процесса, который основан на создании одной ячейки с правильным форматированием и типом содержимого, а затем заполнении других ячеек последовательностью связанной информации. Вы можете выбрать параметры после применения автозаполнения для заполнения ячеек информацией о дате.
Нажмите заголовок столбца, чтобы выбрать место для информации о дате. На вкладке «Главная» на ленте Excel щелкните стрелку в правом нижнем углу раздела «Число». Выберите «Дата» из списка категорий числового формата, а затем примените нужный стиль даты. Нажмите кнопку "ОК", чтобы закрыть диалоговое окно "Формат ячеек".
Дважды щелкните начальную ячейку в верхней части столбца, чтобы выбрать ее. Введите свою первую дату в первую ячейку столбца. Нажмите клавишу «Ввод», чтобы подтвердить свои данные. Перетащите правый край заголовка столбца вправо, чтобы расширить столбец, если вы выбрали формат даты, включающий день недели.
Поместите курсор в правый нижний угол ячейки, содержащей начальную дату. Когда курсор примет вид черного знака плюса, перетащите этот маркер заполнения вниз, пока не дойдете до нижней части области, которую вы планируете заполнить данными.
Нажмите немаркированную кнопку автозаполнения, которая появляется ниже и справа от последней ячейки, на которую вы перетащили маркер заполнения. Откроется раскрывающееся меню автозаполнения, в котором можно выбрать один из семи вариантов. Если вы выберете переключатель «Заполнить серию», Excel заполнит ячейки датами, которые увеличиваются на один день, начиная с введенной вами даты.
Используйте автозаполнение, чтобы заполнить столбец названиями дней или месяцев, чтобы распространить форматирование из начальной ячейки без увеличения ее данных или увеличить данные без применения форматирования первой ячейки. Все эти параметры отображаются в меню автозаполнения.
Существует несколько вариантов автоматического создания последовательности дат или месяцев в Excel. Указав дату начала в первой ячейке, вы можете перетащить или дважды щелкнуть маркер заполнения в правом нижнем углу ячейки или использовать команду «Заполнить» на ленте.
Перетаскивание маркера заполнения
Первый вариант — перетащить маркер заполнения из начальной ячейки. Допустим, вы хотите заполнить даты с 21 января 2020 г. по 30 января 2020 г. в столбце А, начиная с ячейки А1.
<р>1. Сначала введите 21.01.2020 в начальную ячейку (A1) и поместите курсор в правый нижний угол ячейки. Когда вы это сделаете, появится дескриптор заполнения. <р>2. После этого вам нужно перетащить маркер заполнения вниз в зависимости от того, сколько дат вы хотите заполнить (в данном случае 10).Как видите, Excel распознает дату в ячейке A1 и автоматически заполняет каждую ячейку следующим днем на основе начальной даты.
Другие интервалы дат
Если вы хотите автоматически заполнить другой шаблон даты, введите первую дату в ячейку A1, а вторую — в ячейку A2. Затем выделите обе ячейки и перетащите маркер заполнения вниз к строке 10.
В этом случае функция автозаполнения распознает шаблон (каждый седьмой день) и заполняет ячейки датами вторника.
Двойной щелчок по маркеру заполнения
Еще один способ автоматического заполнения дат в Excel — дважды щелкнуть маркер заполнения. При этом Excel заполняет ячейки на основе соседних столбцов (непустые столбцы слева и справа от выбранного столбца).
Например, в столбце А ниже у вас есть данные по строке 8, а в столбце Б — по строке 6. И вы хотите автоматически заполнять даты, начиная с ячейки C2. В этом случае Excel заполняет даты до C8, поскольку столбец A заполняется до строки 8. Выберите C2 и дважды щелкните маркер заполнения в правом нижнем углу.
Excel автоматически распознает дату в ячейке C2 и заполняет последовательные даты до строки 8.
Автозаполнение дат с помощью команды «Заполнить» на ленте
Приведенный выше пример также можно выполнить с помощью команды «Заполнить» на ленте Excel.
Выберите диапазон ячеек, включая начальное значение, в которые вы хотите вставить даты (C2:C8). Затем на ленте выберите Главная > Заливка > Серии.
Во всплывающем окне оставьте значения по умолчанию, так как вам нужно заполнить столбец. Значение шага равно 1 (1 день).
Таким образом, вы получите тот же результат, что и при двойном щелчке: даты с 17.01.2021 по 23.01.2021 будут заполнены в ячейках C2:C8.
Автозаполнение месяцев с помощью маркера заполнения
Как показано в предыдущих примерах, Excel может распознавать даты и заполнять последовательные дни. Кроме того, когда Excel распознает дату, он может автоматически заполнять последовательность недель, месяцев или лет на основе начального значения. Если вы хотите автоматически заполнять определенную дату каждого месяца на основе начальной даты, вы можете использовать маркер заполнения перетаскиванием. Например, вы хотите, чтобы 21-е число каждого месяца в году заполнялось в столбце А. Давайте начнем с 21 января 2020 года в ячейке А1.
Затем перетащите маркер заполнения в строку 12, чтобы заполнить все 12 месяцев года.
Аналогично можно автоматически заполнять названия месяцев с января по декабрь. Введите январь в ячейку A1 и перетащите маркер заполнения вниз к строке 12.
Excel автоматически распознает названия месяцев и соответствующим образом заполняет ячейки.
Автозаполнение месяцев с помощью команды «Заполнить» на ленте
Как и в предыдущем примере, мы можем использовать команду заполнения на ленте, чтобы заполнить список месяцев. (1) Выберите диапазон ячеек, в которых вы хотите заполнить месяцы (C2: C9). Затем на ленте (2) выберите Главная > Заливка > Серии.
Во всплывающем окне введите Дата будет автоматически распознана, (1) выберите Месяц в разделе Дата и (2) нажмите OK.
Таким образом, вы получите тот же результат, что и при двойном щелчке: 21-е число каждого месяца заполняется ячейками A1:A12.
Автозаполнение дат в Google Таблицах
Функция автозаполнения доступна в Google Sheets, но имеет некоторые ограничения по сравнению с Excel:
<р>1. На ленте Google Таблиц нет команды «Заполнить», поэтому вам нужно будет использовать дескриптор заполнения. <р>2. В Google Таблицах, если вы дважды щелкните дескриптор заполнения, он заполнит строки в зависимости от количества строк в первом столбце слева. (Если вы используете автозаполнение в первом столбце, он просматривает первый столбец справа.)Давайте используем тот же пример, что и выше. Введите 27.01.2021 в ячейку C2, затем выберите эту ячейку, и в правом нижнем углу появится маркер заполнения.
Теперь дважды щелкните маркер заполнения или перетащите его вниз. Когда вы дважды щелкаете, автозаполнение заполняется до C6, поэтому, если вы хотите заполнить строку до 8, вам придется перетащить ее до конца.
Читайте также: