Weeknum excel как пользоваться
Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача, на первый взгляд, простая, но (как всегда) есть несколько весьма существенных нюансов.
Во-первых, в разных странах отсчет начала и конца самой недели – различаются. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье.
Во-вторых, вопрос - какую неделю считать первой? На сегодняшний момент существуют как минимум два основных варианта с разной логикой. Рассмотрим их последовательно.
Способ 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 года. Синтаксис этой функции таков:
Возвращает номер, который указывает, где неделя падает числно в течение года.
Синтаксис
выражения. WeekNum (Arg1, Arg2)
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Variant | Serial_num - дата в течение недели. Даты должны быть введены с помощью функции DATE или в качестве результатов других формул или функций. Например, используйте DATE (2008,5,23) для 23-го дня мая 2008 г. Проблемы могут возникнуть, если даты вписались в виде текста. |
Arg2 | Необязательный | Variant | Return_type — число, определя которое определяет, в какой день начинается неделя. По умолчанию используется значение 1. |
Возвращаемое значение
64-разрядное число с плавающей запятой двойной точности.
Примечания
Функция WeekNum считает неделю, содержащую 1 января, первой неделей года. Однако существует европейский стандарт, который определяет первую неделю как первую неделю, при этом большинство дней (четыре или более) упадет в новом году. Это означает, что в течение нескольких лет в первую неделю января функция WeekNum возвращает некорректные номера в соответствии с европейским стандартом.
В следующей таблице представлены возможные значения для Arg2.
Return_type | Начинается неделя |
---|---|
1 | Неделя начинается в воскресенье. Будни про номером от 1 до 7. |
2 | Неделя начинается в понедельник. Будни про номером от 1 до 7. |
Microsoft Excel хранит даты как последовательные серийные номера, чтобы их можно было использовать в расчетах. По умолчанию 1 января 1900 является серийным номером 1, а 1 января 2008 г. — серийным номером 39448, так как после 1 января 1900 г. он составляет 39 448 дней. Microsoft Excel Macintosh использует другую систему дат по умолчанию.
Visual Basic для приложений (VBA) вычисляет серийные даты иначе, чем Excel. В VBA серийный номер 1 — 31 декабря 1899 г., а не 1 января 1900 г.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.
Описание
WEEKNUM в Excel возвращает номер недели указанной даты в году, который начинает отсчет недель с 1 января. По умолчанию WEEKNUM функция подсчета неделя начинается в воскресенье.
Синтаксис и аргументы
Синтаксис формулы
WEEKNUM ( serial_num , [return_type] ) |
аргументы
- Serial_num : Необходимые. Дата в формате серийного номера, используемая для расчета номера недели в году.
- Return_type : Необязательный. Число определяло тот день, с которого начинается неделя. По умолчанию он опущен или 1, что означает начало недели в воскресенье.
Система
WEEKNUM Функция может использоваться в двух системах.
Система 1 Функция считает начало недели 1 января, что означает, что 1 января каждого года является первой неделей.
Система 2 Функция подсчитывает начало недели в первый четверг года, например, 1 - это не первая неделя, а среда. Однако 1/2020/1 - первая неделя, так как это первый четверг в 2 году.
Возвращаемое значение
Функция WEEKNUM возвращает номер недели (1-54).
Замечания
Серийная дата - это то, как Excel хранит даты внутри, и она представляет количество дней с 1 января 1900 года. Например, 1 января 1900 года - это серийный номер 1, 1 января 2020 года - серийный номер 43831, потому что прошло 43831 день после января. 1, 1900 г.
Ошибка
Использование и примеры
Вот список дат в диапазоне B3: B6, чтобы получить их номер недели, а неделя начинается с воскресенья, используйте следующую формулу:
Нажмите Enter и перетащите маркер заполнения в ячейку B6.
Относительные функции:
Excel Date функция
Объедините числа года, месяца и дня из отдельных ячеек в действительную дату.
Excel DATEDIF функция
Если вы хотите рассчитать количество лет, месяцев, дней или недель между двумя заданными датами, DATEDIF функция в Excel может вам помочь.
Excel DATEVALUE функция
Функция ДАТАЗНАЧ может помочь вам преобразовать дату, сохраненную в текстовом формате, в серийный номер, который Excel может распознать как дату, а затем отформатировать номер в соответствующий формат даты.
Функция ДЕНЬ в Excel
С помощью функции ДЕНЬ вы можете быстро получить день в виде числа от 1 до 31 в зависимости от заданных дат.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Как в Excel быстро и легко получить номер недели с заданной даты? Обычно функция WEEKNUM может помочь вам решить эту задачу как можно быстрее.
Рассчитайте, чтобы получить номер недели от даты в году
По умолчанию функция WEEKNUM может помочь вам получить номер недели для любой заданной даты в году, общий синтаксис:
- date : Дата, с которой вы хотите получить номер недели.
- return_type : Необязательный. Число, определяющее день начала недели. Номер по умолчанию - 1. (указывает, что неделя начинается с воскресенья).
- DATE : Эта функция используется для объединения чисел года, месяца и дня из отдельных ячеек в действительную дату.
Число return_type указывает на начало недели:
Советы: Для функции WEEKNUM используются две разные системы нумерации недель:
- Система 1: Неделя, содержащая 1 января, считается 1-й неделей года. В этой системе неделя традиционно начинается в воскресенье.
- Система 2: (Система даты недели ISO) В этой системе неделя начинается в понедельник, а номер недели 1 устанавливается на первую неделю года, которая содержит четверг.
Все типы возврата, перечисленные выше, применимы к Системе 1, но тип возврата 21 используется в Системе 2.
Чтобы вычислить номер недели от даты в традиционной системе:
Чтобы получить номер недели с заданной даты, вы можете использовать следующую формулу:
Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите использовать, и все номера недель будут возвращены из дат:
Чтобы вычислить номер недели от даты в системе ISO:
Если вам нужно вернуть номер недели ISO для некоторых дат, примените следующую формулу:
И тогда вы получите следующий результат:
Рассчитайте, чтобы получить номер недели от даты в месяце
Иногда вам нужно получить номера недель в месяцах, а не в годах, в Excel нет прямой функции для этого. Но вы можете объединить ФУНКЦИИ WEEKNUM, DATE и MONTH вместе для решения этой задачи.
Вычислить номера недель месяца от даты (неделя, начинающаяся в воскресенье):
Введите или скопируйте приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:
И тогда вы получите результаты, как показано на следующем скриншоте:
Вычислить номера недель месяца по ISO от даты (неделя начинается с понедельника):
Чтобы получить номера недель месяца по ISO от даты, вы должны использовать следующую формулу:
И все номера недель месяца по ISO были извлечены, как показано на скриншоте ниже:
Используемые относительные функции:
- WEEKNUM :
- Эта функция используется для извлечения номера недели из заданной даты.
Статьи с относительным номером недели:
- Преобразование номера недели в дату или наоборот в Excel
- Есть ли способ получить номер недели по заданной дате или извлечь диапазон дат из определенного номера недели и года в Excel?
- Получить месяц из номера недели и года в Excel
- Если у вас есть два столбца в Excel, один из которых содержит номера лет, а другой - номера недель, как показано ниже, как вы можете получить месяцы непосредственно из них в следующем столбце?
- Подсчитайте / рассчитайте недели / месяцы / годы между двумя датами в Excel
- Вы когда-нибудь задумывались о том, сколько недель, месяцев или лет между двумя заданными датами в Excel?
- Добавить / вычесть количество недель до даты в Excel
- В этом руководстве вы узнаете, как добавить n недель к текущему моменту или вычесть n недель из данных в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Суммировать значения по номеру недели или дню недели в Excel
В этом руководстве шаг за шагом демонстрируются формулы, которые помогут вам суммировать значения в определенном диапазоне на основе указанного номера недели или дня недели в Excel.
Как суммировать значения по номеру недели в Excel?
Как показано на снимке экрана ниже, для суммирования значений в столбце «Сумма» на основе дат, на которые выпадает данный номер недели, мы предоставляем две формулы.
Общие формулы
=SUMIFS( value_range, helper_column_range, week_number)
=SUMPRODUCT(--(WEEKNUM(date_range+0,1)=week_number),value_range)
аргументы
Как пользоваться этой формулой?
Использование формулы СУММЕСЛИМН и вспомогательного столбца
Для использования SUMIFS формула, сначала вам нужно применить WEEKNUM функция для создания вспомогательного столбца для вычисления соответствующих номеров недель для дат в столбце Date.
=WEEKNUM (serial_num, [return_type])
Функция WEEKNUM возвращает номер недели указанной даты в году.
1. В этом случае я выбираю ячейку (F5) рядом с первым значением в столбце «Сумма».
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ, чтобы получить номер недели первого свидания. Выберите ячейку результата и перетащите ее дескриптор автозаполнения вниз, чтобы получить другие номера недель. Смотрите скриншот:
=WEEKNUM(C5)
3. Выберите ячейку для вывода общей суммы, введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите эту ячейку результата, перетащите ее дескриптор автозаполнения вниз, чтобы получить другие результаты.
=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)
Используя формулу СУММПРОИЗВ
Вам не нужно создавать вспомогательный столбец при использовании формулы СУММПРОФУКТ для решения этой проблемы.
1. Выберите пустую ячейку, введите в нее формулу ниже и нажмите Enter ключ.
=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)
2. Выберите ячейку результата, перетащите ее Ручка автозаполнения вниз, чтобы получить другие результаты в соответствии с вашими потребностями.
Пояснение формулы
=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)
После вычисления номеров недель с помощью функции WEEKNUM функция СУММЕСЛИМН суммирует значения в диапазоне E5: E14, если номера недель в вспомогательном столбце F5: F14 соответствуют данной неделе в H6.
=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)
WEEKNUM (5 канадских долларов: 14 канадских долларов + 0,1): Функция WEEKNUM вычисляет номер каждой недели дат в диапазоне C5: C14 и возвращает массив, подобный этому: ;
=G6: Здесь сравнивает номер каждой недели в массиве с заданным номером недели в G6 и возвращает новый массив: ;
- : Эти два знака минус преобразуют «ИСТИНА» и «ЛОЖЬ» в единицы и нули. Здесь вы получите новый массив как ;
SUMPRODUCT(): Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат 1428 + 2010 = 3438.
Как суммировать значения по дням недели в Excel?
Как показано на снимке экрана ниже, чтобы суммировать значения в столбце «Сумма» по определенному дню недели в Excel, вы можете применить функцию СУММПРОИЗВ вместе с функцией ДЕНЬНЕД.
В этом случае, поскольку нам нужно суммировать общую сумму для каждого дня недели, перед применением формулы мы создаем вспомогательный столбец с номерами от 1 до 7, чтобы отдельно указать с понедельника по воскресенье.
Общая формула
=SUMPRODUCT((WEEKDAY(date_range,2)=weekday_number)*value_range)
аргументы
Номер_буднего дня: Номер дня недели, используемый в качестве критерия для суммирования значений. Это может быть ссылка на ячейку или число от 1 до 7.
Как пользоваться этой формулой?
1. Выберите ячейку рядом с ячейкой с номером 1. Здесь я выбираю I6.
2. Скопируйте или введите в нее формулу ниже и нажмите Enter ключ. Выберите ячейку результата, а затем перетащите ее дескриптор автозаполнения вниз, чтобы получить результаты для других дней недели.
=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)
Примечание: Если вам просто нужно суммировать значения на основе одного указанного дня недели, например пятницы, вы можете напрямую ввести число 5 в формулу следующим образом.
=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=5)*$E$5:$E$14)
Пояснение формулы
=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)
WEEKDAY (5 канадских долларов: 14,2 канадских долларов): Функция WEEKDAY возвращает целые числа от 1 до 7 для представления дня недели с понедельника по воскресенье в диапазоне C5: C14: ;
=H6: Здесь каждый день недели в массиве сравнивается с заданным номером дня недели в H6 и возвращается новый массив: ;
* $ E $ 5: $ E $ 14: Здесь ИСТИНА и Ложь преобразуются в единицы и нули, а затем умножаются на значения в E1: E0 для возврата массива: ;
SUMPRODUCT : Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат 2288 + 1325 = 3613.
Связанные функции
Функция ЧИСЛО НЕДЕЛ в Excel
Функция СУММЕСЛИМН в Excel возвращает номер недели указанной даты в году, который начинает отсчет недель с 1 января.
Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов, а затем получения суммы произведений.
Функция ДЕНЬ НЕДЕЛИ в Excel
Функция Excel EOMONTH возвращает целое число от 1 до 7, представляющее день недели для заданной даты в Excel.
Родственные формулы
Сумма значений по месяцам (с годом или без)
В этом руководстве подробно показаны две формулы, которые помогут суммировать значения по определенному месяцу и году или суммировать значения по определенному месяцу без учета года в Excel.
Сумма значений по неделям заданной даты
В этом руководстве рассказывается, как суммировать значения по неделям, на которые приходится заданная дата.
Суммируйте каждые n строк или столбцов в диапазоне
В этом руководстве объясняются две формулы на основе функций СУММ и СМЕЩЕНИЕ для суммирования каждых n строк или столбцов в указанном диапазоне в Excel.
Суммируйте каждую n-ю строку или столбец в диапазоне
В этом руководстве показано, как создать формулу на основе функций СУММПРОИЗВ, МОД и СТОЛБЕЦ для суммирования каждой n-й строки или столбца в диапазоне в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Читайте также: