Номер недели в году в EXCEL
history 7 апреля 2013 г.
Существует 2 способа определения номера недели: в соответствии со стандартом ИСО и с помощью функции НОМНЕДЕЛИ() .
Рассмотрим 2 способа определения номера недели:
Используя функцию НОМНЕДЕЛИ()
Предполагается, что в ячейке А3 находится дата. Формула определяет номер недели, которой принадлежит эта дата.
Альтернативой использованию функции НОМНЕДЕЛИ() с типом 2 (как принято в РФ) является формула (см. файл примера ): =1+ЦЕЛОЕ((A3-(ДАТА(ГОД(A3);1;2)-ДЕНЬНЕД(ДАТА(ГОД(A3);1;0))))/7)
Альтернативой использованию функции НОМНЕДЕЛИ() с типом 1 является формула: =1+ЦЕЛОЕ((A3-(ДАТА(ГОД(A3);1;2)-ДЕНЬНЕД(ДАТА(ГОД(A3);1;1))))/7)
По стандарту ISO8601:2000 (ГОСТ ИСО 8601-2001)
По стандарту ISO8601:2000 все недели начинаются с понедельника и длятся 7 дней. Первой неделей считается та, на которую попадает первый четверг нового года (т.е. первая неделя должна содержать не менее 4 дней из нового года).
Формула для расчета номера недели по стандарту ИСО: =ЦЕЛОЕ((A3-ДАТА(ГОД(A3-ДЕНЬНЕД(A3-1)+4);1;3)+ ДЕНЬНЕД(ДАТА(ГОД(A3-ДЕНЬНЕД(A3-1)+4);1;3))+5)/7)
Следствием того, что все недели начинаются с понедельника, может получиться так, что в первую неделю попадут дни из прошлого года.
Номер недели по дате в Excel
Рассмотрим различные варианты получения номера недели по дате в Excel в зависимости от способа определения первого дня и способа нумерации.
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Задача определения номера недели всегда остается актуальной, так как неделя является одним из основных временных интервалов в жизни и бизнесе — понедельные отчеты, планы, телепрограммы и т.д.
Однако, несмотря на внешне кажущуюся простоту задачи, в расчете есть определенные тонкости, о которых мы далее подробно поговорим.
Особенности расчета: нумерация и определение первого дня
Во-первых, в зависимости от страны может отличаться способ определения первой недели года.
В России (согласно ГОСТ ИСО 8601-2001, действует с 2002 г.) первой неделей считается неделя, которая содержит первый четверг года, т.е. неделя содержащая 4 января (другими словами, 1 января это любой день от понедельника до четверга). В США используют другой вариант нумерации, к примеру, у них 1 января начинается новая календарная неделя, независимо от дня недели.
Во-вторых, первый день недели в разных странах также может отличаться.
Для жителей России и стран Европы начало недели начинается с понедельника, но, например, в странах Северной и Южной Америки неделя начинается с воскресенья, а в некоторых странах Ближнего Востока с субботы.
Таким образом, в зависимости от того, какой конкретно способ расчета нам нужен и будем выстраивать алгоритм.
Способ 1. Неделя содержит первый четверг года
Давайте начнем с более популярного варианта, а именно, посчитаем номер недели для российского календаря.
Как мы уже выяснили, в России первой неделя должна содержать первый четверг года.
Для расчета можно воспользоваться следующей формулой:
Алгоритм расчета:
Начиная с версии Excel 2013 добавлена стандартная функция НОМНЕДЕЛИ.ISO (по стандарту ИСО), которую можно применить вместо выше приведенной формулы.
Синтаксис и описание функции НОМНЕДЕЛИ.ISO (в английской версии WEEKNUM.ISO):
НОМНЕДЕЛИ.ISO(дата)
Возвращает номер недели в году по стандарту ISO для указанной даты.
Проверим работу функции на тех же датах, что и в предыдущем примере:
Результаты совпали, что и требовалось, теперь перейдем к другим вариантам календаря.
Способ 2. Первая неделя начинается 1 января
Для такого способа нумерации недель тоже есть вариант стандартной функции, причем он доступен и в более ранних версиях Excel.
Синтаксис и описание функции НОМНЕДЕЛИ (в английской версии WEEKNUM):
НОМНЕДЕЛИ.ISO(дата; [тип])
Возвращает номер недели года.
В качестве примера рассмотрим 2017 г. где 1 января приходится на воскресенье, а 2 января на понедельник.
Соответственно, получается, что в американском варианте расчета недели меняются между 7 и 8 января, а в российском между 8 и 9 января:
Также возможные и другие различные значения аргумента Тип:
Первая цифра типа означает способ определения первой недели года (1 — неделя начинается 1 января, 2 — содержит первый четверг года), а вторая цифра определяет с какого дня начинается неделя (1 — понедельник, 2 — вторник, 3 — среда и т.д.)
Спасибо за внимание!
Если у вас остались вопросы по теме статьи — пишите в комментариях.
Номер недели по дате функцией НОМНЕДЕЛИ
Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача, на первый взгляд, простая, но (как всегда) есть несколько весьма существенных нюансов.
Во-первых, в разных странах отсчет начала и конца самой недели – различаются. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье.
Способ 1. Стандарт ГОСТ ИСО 8601-2001 и функция НОМНЕДЕЛИ.ISO
Первой рабочей неделей года считается та, на которую выпадает первый четверг года (или 4 января, если хотите). Здесь логика проста. Первая неделя – это та, на которую пришлось больше трех дней (больше половины недели) из наступившего года. Некоторое неудобство в том, что в году получается когда 52, а когда 53 рабочих недели. Плюс ко всему 1 января может запросто оказаться 52 неделей предыдущего года.
Но именно этот вариант официально принят в России с 2002 года как государственный и остается им на данный момент (см. ГОСТ ИСО 8601-2001). Чтобы посчитать номер недели по дате по такой системе, можно использовать вот такую формулу:
В Excel 2013 функцию для расчета номера недели по ISO добавили в стандартный набор – она называется НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)
Способ 2. Неделя с 1-м января и функция НОМНЕДЕЛИ (WEEKNUM)
=НОМНЕДЕЛИ( Дата ; Тип_отсчета )
Как узнать номер недели в excel
Как в Excel быстро и легко получить номер недели с заданной даты? Обычно функция WEEKNUM может помочь вам решить эту задачу как можно быстрее.
Рассчитайте, чтобы получить номер недели от даты в году
По умолчанию функция WEEKNUM может помочь вам получить номер недели для любой заданной даты в году, общий синтаксис:
Число return_type указывает на начало недели:
| Return_type | Неделя начинается |
| 1 или 17 или опущено | воскресенье |
| 2 или 11 | понедельник |
| 12 | вторник |
| 13 | среда |
| 14 | четверг |
| 15 | пятница |
| 16 | суббота |
| 21 | Понедельник (система даты недели ISO) |
Советы: Для функции WEEKNUM используются две разные системы нумерации недель:
Все типы возврата, перечисленные выше, применимы к Системе 1, но тип возврата 21 используется в Системе 2.
Чтобы вычислить номер недели от даты в традиционной системе:
Чтобы получить номер недели с заданной даты, вы можете использовать следующую формулу:
Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите использовать, и все номера недель будут возвращены из дат:
Чтобы вычислить номер недели от даты в системе ISO:
Если вам нужно вернуть номер недели ISO для некоторых дат, примените следующую формулу:
И тогда вы получите следующий результат:
Рассчитайте, чтобы получить номер недели от даты в месяце
Иногда вам нужно получить номера недель в месяцах, а не в годах, в Excel нет прямой функции для этого. Но вы можете объединить ФУНКЦИИ WEEKNUM, DATE и MONTH вместе для решения этой задачи.
Вычислить номера недель месяца от даты (неделя, начинающаяся в воскресенье):
Введите или скопируйте приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:
И тогда вы получите результаты, как показано на следующем скриншоте:
Вычислить номера недель месяца по ISO от даты (неделя начинается с понедельника):
Чтобы получить номера недель месяца по ISO от даты, вы должны использовать следующую формулу:
И все номера недель месяца по ISO были извлечены, как показано на скриншоте ниже:
Используемые относительные функции:
Статьи с относительным номером недели:
Лучшие инструменты для работы в офисе
Сводные таблицы Excel
Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача на первый взгляд простая, но (как всегда) есть несколько весьма существенных нюансов.
Во-первых, в разных странах отсчет начала и конца самой недели – различается. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье. Во-вторых, вопрос – какую неделю считать первой? На сегодняшний момент существуют как минимум два основных варианта с разной логикой. Рассмотрим их последовательно.
Способ 1. Стандарт ГОСТ ИСО 8601-2001
Первой рабочей неделей года считается та, на которую выпадает первый четверг года (или 4 января, если хотите). Здесь логика проста. Первая неделя – это та, на которую пришлось больше трех дней (больше половины недели) из наступившего года. Некоторое неудобство в том, что в году получается когда 52, а когда 53 рабочих недели. Плюс ко всему 1 января может запросто оказаться 52-й неделей предыдущего года.
Но именно этот вариант официально принят в России с 2002 года как государственный и остается им на данный момент (см. ГОСТ ИСО 8601-2001). Чтобы посчитать номер недели по дате по такой системе, можно использовать вот такую формулу (предполагается, что дата в ячейке А1):
Этот вариант официально принят в России
Функцию для расчета номера недели по ISO добавили в стандартный набор
Способ 2. Неделя с 1-м января
В этом случае первой неделей года считается та, на которую попадает 1 января. Здесь также возникает сложность в том, что в году может оказаться 52 или 53 недели и, плюс ко всему, 1 января может выпасть на воскресенье, т.е. шесть последних дней года могут оказаться уже в неделе с номером 1, что затрудняет отчетность. Тем не менее, если такой способ нумерации вам нужен, то его можно реализовать функцией НОМНЕДЕЛИ (WEEKNUM).
Если у вас Excel не 2007 версии или новее, то сначала придется подключить надстройку Пакет анализа через меню Сервис → Надстройки (Tools → Add-Ins → Analysis ToolPak), чтобы к списку функций Excel в категории Дата и время добавилась функция НОМНЕДЕЛИ (WEEKNUM). В Excel 2007–2016 ничего специально подключать не надо – эта функция входит в стандартный набор.
Вставляем эту функцию в нужную ячейку и указываем в качестве аргумента ячейку с датой и точку отсчета недели (1 – с воскресенья, 2 – с понедельника).
В Excel 2007–2016 ничего специально подключать не надо
При всей внешней простоте этого решения тут есть две тонкости. Во-первых, если у того пользователя, который откроет ваш файл, не будет заранее подключена надстройка Пакет анализа или у него не Excel 2007–2016 – он увидит вместо номера недели ошибку #ЗНАЧ!. Excel сам не распознает эту функцию, нужно обязательно заранее подключить надстройку. Во-вторых, названия функций из надстроек Excel сам не переводит на другой язык при открытии книги в Excel с другой версией.
Т.е. если даже у пользователя и подключена надстройка, но версия Excel англоязычная, то он тоже увидит вместо номера недели ошибку #VALUE!, потому что нужно вручную исправить русское название функции НОМНЕДЕЛИ на английское WEEKNUM – только так она заработает. Поэтому использование этой функции из надстройки Пакет анализа хорошо только в тех случаях, когда предполагается, что никто, кроме вас, с файлом работать не будет.
