Как в экселе сделать строку с первыми днями недель

Обновлено: 04.07.2024

Особенности расчета: нумерация и определение первого дня

Во-первых, в зависимости от страны может отличаться способ определения первой недели года.
В России (согласно ГОСТ ИСО 8601-2001, действует с 2002 г.) первой неделей считается неделя, которая содержит первый четверг года, т.е. неделя содержащая 4 января (другими словами, 1 января это любой день от понедельника до четверга). В США используют другой вариант нумерации, к примеру, у них 1 января начинается новая календарная неделя, независимо от дня недели.

Во-вторых, первый день недели в разных странах также может отличаться.
Для жителей России и стран Европы начало недели начинается с понедельника, но, например, в странах Северной и Южной Америки неделя начинается с воскресенья, а в некоторых странах Ближнего Востока с субботы.

Таким образом, в зависимости от того, какой конкретно способ расчета нам нужен и будем выстраивать алгоритм.

Способ 1. Неделя содержит первый четверг года

Давайте начнем с более популярного варианта, а именно, посчитаем номер недели для российского календаря.

Как мы уже выяснили, в России первой неделя должна содержать первый четверг года.
Для расчета можно воспользоваться следующей формулой:

Алгоритм расчета:

К вычисляемой дате (напомню, что дата в Excel хранится в числовом виде) прибавляем 3 дня и вычитаем номер дня недели (1 для понедельника, 2 для вторника и т.д.); Этим шагом мы добьемся, что для понедельника, вторника и среды вычисляемая дата увеличится, а для остальных дней (четверга, пятницы, субботы и воскресенья) уменьшится, что и позволит добиться включения первого четверга года в первую неделю.
Далее возьмем остаток от деления на 365,25 (средняя длительность года с учетом високосного года); Этой операцией мы получим количество дней в текущем году для вычисляемой даты.
Делим полученный результат делим на 7; Переводим дни в недели.
Прибавляем 1 и берем целую часть числа.

Начиная с версии Excel 2013 добавлена стандартная функция НОМНЕДЕЛИ.ISO (по стандарту ИСО), которую можно применить вместо выше приведенной формулы.
Синтаксис и описание функции НОМНЕДЕЛИ.ISO (в английской версии WEEKNUM.ISO):

НОМНЕДЕЛИ.ISO(дата)
Возвращает номер недели в году по стандарту ISO для указанной даты.

Проверим работу функции на тех же датах, что и в предыдущем примере:

Результаты совпали, что и требовалось, теперь перейдем к другим вариантам календаря.

Способ 2. Первая неделя начинается 1 января

Для такого способа нумерации недель тоже есть вариант стандартной функции, причем он доступен и в более ранних версиях Excel.
Синтаксис и описание функции НОМНЕДЕЛИ (в английской версии WEEKNUM):

НОМНЕДЕЛИ.ISO(дата; [тип])
Возвращает номер недели года.

В качестве примера рассмотрим 2017 г. где 1 января приходится на воскресенье, а 2 января на понедельник.
Соответственно, получается, что в американском варианте расчета недели меняются между 7 и 8 января, а в российском между 8 и 9 января:

Также возможные и другие различные значения аргумента Тип:

Спасибо за внимание!
Если у вас остались вопросы по теме статьи — пишите в комментариях.

Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача, на первый взгляд, простая, но (как всегда) есть несколько весьма существенных нюансов.

Во-первых, в разных странах отсчет начала и конца самой недели – различаются. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье.

Во-вторых, вопрос - какую неделю считать первой? На сегодняшний момент существуют как минимум два основных варианта с разной логикой. Рассмотрим их последовательно.

Способ 1. Стандарт ГОСТ ИСО 8601-2001 и функция НОМНЕДЕЛИ.ISO

Первой рабочей неделей года считается та, на которую выпадает первый четверг года (или 4 января, если хотите). Здесь логика проста. Первая неделя – это та, на которую пришлось больше трех дней (больше половины недели) из наступившего года. Некоторое неудобство в том, что в году получается когда 52, а когда 53 рабочих недели. Плюс ко всему 1 января может запросто оказаться 52 неделей предыдущего года.

Но именно этот вариант официально принят в России с 2002 года как государственный и остается им на данный момент (см. ГОСТ ИСО 8601-2001). Чтобы посчитать номер недели по дате по такой системе, можно использовать вот такую формулу:

В Excel 2013 функцию для расчета номера недели по ISO добавили в стандартный набор – она называется НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)

Способ 2. Неделя с 1-м января и функция НОМНЕДЕЛИ (WEEKNUM)

В этом случае первой неделей года считается та, на которую попадает 1 января. Здесь также возникает сложность в том, что в году может оказаться 52 или 53 недели и, плюс ко всему, 1 января может выпасть на воскресенье, т.е. шесть последних дней года могут оказаться уже в неделе с номером 1, что затрудняет отчетность. Тем не менее, если такой способ нумерации вам нужен, то его можно реализовать функцией НОМНЕДЕЛИ (WEEKNUM) , появившейся в Excel начиная с 2007 года. Синтаксис этой функции таков:

Т.к. каждому значению даты соответствует определенное число (см. статью Как 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 дата автозаполнения обычно должна использоваться в нашей повседневной работе, но в этом случае я расскажу о том, как заполнять даты по неделям. Другими словами, я определяю дату начала как 3 (вторник) и заполняю ее в ячейке A1, и мне нужно заполнить 2016/1/3 (вторник) в ячейке A8, 2016/2/2016 (вторник) в ячейке A3,… и т. д.

Дата автозаполнения еженедельно / ежемесячно / ежегодно с Kutools for Excel

Дата автозаполнения еженедельно формулой

Чтобы заполнять дату еженедельно, вы можете применить простую формулу.

1. Выберите ячейку и введите дату начала. В нашем случае мы набираем 3/1/2016 в ячейке A1. Смотрите скриншот:

2. Затем в следующей ячейке A2 введите эту формулу = A1 + 7 , и нажмите Enter ключ, чтобы получить второе свидание. Смотрите скриншот:

И теперь вы можете перетащить дескриптор автозаполнения ячейки A2 вниз, чтобы заполнять даты еженедельно по мере необходимости. Смотрите скриншот:

Дата автозаполнения еженедельно / ежемесячно / ежегодно с Kutools for Excel

Если у вас есть Kutools for Excel, С его Вставить порядковый номер Утилита, вы можете не только заполнять дату еженедельно, вы также можете указать дату окончания при заполнении ячеек.

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Введите дату начала в ячейку, возьмите, например, A1, затем выберите эту дату и щелкните Главная вкладку и перейдите, чтобы указать эту дату как Общие форматирование из списка в Число группы на Главная таб. Смотрите скриншот:

2. Затем нажмите Kutools > Вставить > Вставить порядковый номер. Смотрите скриншот:

3. В появившемся диалоговом окне щелкните НОВИНКИ кнопку, чтобы создать новую последовательность. Смотрите скриншот:

4. Затем введите дату начала (числовую серию) в поле Стартовый номер текстовое поле и введите 7 в текстовое поле Increment и 5 в Кол-во цифр текстовое окно. Смотрите скриншот:

5. Нажмите Добавить чтобы завершить создание новой последовательности, затем выберите диапазон ячеек, которые вы хотите заполнить по неделям, и нажмите Диапазон заполнения. Смотрите скриншот:

Наконечник: Вы можете закрыть Вставить порядковый номер диалоговое окно, как вам нужно, после еженедельной вставки дат.

6. Не снимая выделения с дат, нажмите Главная > Поле числового формата > Краткая дата для форматирования число как формат даты.

Наконечник:

1. Со вставкой порядкового номера, вы указываете дату окончания при вставке дат. Например, я указываю дату окончания 6/18/2016 , который может отображаться как 42539 . И когда вы вставляете даты, они заканчиваются на 6/18/2016 .

2. И вы также можете сбросить порядковый номер, просто нужно нажать Сбросить, последовательность будет сброшена, и число заполнится с начального числа.

При работе в программе Excel иногда ставится задача, чтобы после ввода конкретной даты в ячейку выводился день недели, который ей соответствует. Естественно, решить данную задачу посредством такого мощного табличного процессора, как Эксель, возможно, причем несколькими способами. Давайте посмотрим, какие варианты существуют для выполнения данной операции.

Отображение дня недели в Экселе

Существуют несколько способов отобразить день недели по введенной дате, начиная от форматирования ячеек и заканчивая применением функций. Давайте взглянем на все существующие варианты выполнения указанной операции в Экселе, чтобы пользователь мог выбрать самый лучший из них для конкретной ситуации.

Способ 1: применение форматирования

Прежде всего, посмотрим, как при помощи форматирования ячеек можно отобразить день недели по введенной дате. Данный вариант подразумевает преобразование даты в указанное значение, а не сохранение отображения обоих этих видов данных на листе.

Вводим любую дату, содержащую данные о числе, месяце и годе, в ячейку на листе.

В поле «Тип» окна форматирования вместо значения «ДДДД» можно также ввести выражение:

В этом случае на листе будет отображено сокращенное наименование дня недели.

Способ 2: использование функции ТЕКСТ

Но способ, который был представлен выше, предусматривает преобразование даты в день недели. Существует ли вариант, чтобы оба эти значения выводились на листе? То есть, если в одной ячейке мы вводим дату, то в другой должен выводиться день недели. Да, такой вариант существует. Его можно осуществить при помощи формулы ТЕКСТ. В этом случае нужное нам значение будет выводиться в указанную ячейку в текстовом формате.

«Вставить функцию»

В поле «Значение» нам нужно указать адрес той ячейки, в которой содержится дата. Для этого устанавливаем курсор в указанное поле и левой кнопкой мыши щелкаем по этой ячейке на листе. Адрес тут же отобразится.

В поле «Формат» в зависимости от того, какое мы хотим иметь представление дня недели полное или сокращенное, вводим выражение «дддд» или «ддд» без кавычек.

Более того, если в ячейке изменить значение даты, то соответственно автоматически изменится и день недели. Таким образом, меняя дату можно узнавать на какой день недели она будет приходиться.

Способ 3: применение функции ДЕНЬНЕД

Имеется ещё один оператор, который может выводить день недели по заданной дате. Это функция ДЕНЬНЕД. Правда, она выводит не название дня недели, а его номер. При этом пользователь может устанавливать с какого дня (с воскресенья или с понедельника) нумерация будет отсчитываться.

«Вставить функцию»

В поле «Дата в числовом формате» вводим конкретную дату или адрес ячейки на листе, в которой она содержится.

В поле «Тип» задается число от 1 до 3, которое определяет, как именно будут нумероваться дни недели. При установке числа «1» нумерация будет происходить, начиная с воскресенья, причем этому дню недели будет присвоен порядковый номер «1». При установке значения «2» нумерация будет выполняться, начиная с понедельника. Этому дню недели будет дан порядковый номер «1». При установке значения «3» нумерация так же будет происходить с понедельника, но в этом случае понедельнику будет присвоен порядковый номер «0».

Аргумент «Тип» не является обязательным. Но, если его опустить, то считается, что значение аргумента равно «1», то есть, неделя начинается с воскресенья. Так принято в англоязычных странах, но нам данный вариант не подходит. Поэтому в поле «Тип» ставим значение «2».

Как и с предыдущей функцией, при изменении даты автоматически изменяется номер дня недели в ячейке, в которой установлен оператор.

Как видим, в Экселе существуют три основных варианта представления даты в виде дня недели. Все они относительно просты и не требуют от пользователя наличия каких-то специфических умений. Один из них заключается в применении специальных форматов, а два других для достижения указанных целей используют встроенные функции. Учитывая, что механизм и способ отображения данных в каждом описанном случае существенно отличаются, пользователь должен сам выбрать, какой из указанных вариантов в конкретной ситуации ему подходит более всего.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Читайте также: