Excel: вычисление недель - вот как это работает с формулой

Найдите подходящую функцию

В Microsoft Excel доступна функция КАЛЕНДАРНАЯ НЕДЕЛЯ, с помощью которой вы можете отображать календарную неделю для определенной даты. Например, вы можете автоматически отображать, на какой неделе назначена встреча. Табличная функция КАЛЕНДАРЬ НЕДЕЛЯ в Excel также имеет свои подводные камни. Это связано с тем, что календарная неделя в Германии, Австрии и Швейцарии определяется иначе, чем в соседних европейских странах или США. В этой статье показано, как можно рассчитать правильную календарную неделю и разницу между календарной неделей в регионе DACH и других странах.

Функция КАЛЕНДАРНАЯ НЕДЕЛЯ в Excel

Функция КАЛЕНДАРНАЯ НЕДЕЛЯ также рассчитывает календарные недели в европейских версиях Excel с использованием американского стандарта. Первый день года обычно назначается 1 календарной неделе.

Синтаксис для расчета календарной недели:

= КАЛЕНДАРНАЯ НЕДЕЛЯ (ДАТА)

Календарная неделя определяется по-разному в Германии, Австрии и Швейцарии. По сути, это неделя, которая как первая неделя года состоит как минимум из четырех дней. Если первое января выпадает на пятницу, субботу или воскресенье, табличная функция CALENDAR WEEK возвращает неверный результат для Европы.

Правильно определяйте календарные недели в Excel - вот как это работает

Чтобы отобразить правильную календарную неделю для Германии, Австрии или Швейцарии, вам понадобится формула. Например, для ячейки C4 это должно быть следующее:

= ОБРЕЗАТЬ ((A4-ДАТА (ГОД (A4-ОСТАЛОСЬ (A4-2,7) +3); 1; ОСТАЛОСЬ (A4-2,7) -9)) / 7)

Формула сложная и обширная. Чтобы объяснить, как это работает, мы рассмотрим отдельные компоненты формулы по отдельности:

• Функция ОТДЫХ (A4-2; 7) встречается в формуле несколько раз. Эта функция делит дату на 7 и возвращает полученный остаток.Эта функция возвращает значение 0 для понедельника, значение 1 для вторника и значение 6 для воскресенья.
• В формуле число 2 вычитается из значения даты. Причина этого в том, что базовой датой 01.01.1900 было воскресенье. После деления на 7 останется 1. По этой причине понедельник будет преобразован в число 2. Чтобы скорректировать счетчик дней на 0 для воскресенья, 1 для понедельника и т. Д., Число 2 вычитается из даты.
• На основе этого расчета дня недели часть (ГОД (A4-REST (A4-2; 7) +3) обеспечивает годовое значение, уменьшенное на 1 для первых трех дней года, при условии, что эти дни - пятница, суббота или воскресенье. действует.
• Это годовое значение включается в дату в функции ДАТА. Эта дата обычно формируется со вторым аргументом 1 для января.
• В качестве дневного значения перенесите описанную выше функцию REST в функцию DATE минус значение 9. Это обычно приводит к отрицательному дневному значению. Формула показывает, что дата, рассчитанная с помощью DATE, обычно приходится на декабрь.
• Однако для первых трех дней года это декабрь года, предшествующего предпоследнему, при условии, что эти три дня приходятся на пятницу, субботу или воскресенье.
• Вы вычитаете эту дату, рассчитанную функцией ДАТА, из даты в столбце A, делите разницу на 7 (для 7 дней недели). Все это вычисление используется в функции КОРОТКИЙ, чтобы получить только целые значения.

В качестве альтернативы: используйте аргумент функции WOCHENSTART

Еще одна возможность работать с функцией КАЛЕНДАРНАЯ НЕДЕЛЯ в Microsoft Excel и получать правильные результаты - это добавить в формулу начало недели аргумента функции. Как и в приведенной выше формуле, для ее построения требуются усилия.

Аргумент функции WOCHENSTART может различать две разные системы расчета календарной недели:

1. Система 1 (Американская система) - неделя 1 обозначается как неделя, включающая первое января.
2. Система 2 (Европейский стандарт согласно ISO 8601) - KW 1 - неделя, которая включает первый четверг года.

Если вы используете аргумент функции WOCHENSTART и функцию календарной недели, вы всегда должны вводить следующие числа в качестве аргументов:

понедельник	2 или 11
вторник	12
среда	13
четверг	14
Пятница	15
Суббота	16
Воскресенье	1 или 17

Свяжите функцию КАЛЕНДАРНАЯ НЕДЕЛЯ с аргументом функции WOCHENSTART за 6 шагов.

Чтобы связать функцию календарной недели с началом недели с аргументом функции, выполните следующие действия:

1. Введите дату в электронную таблицу Excel.

2. Щелкните левой кнопкой мыши ячейку, в которую вы хотите включить календарную неделю.

3. Перейдите на вкладку ФОРМУЛЫ и щелкните левой кнопкой мыши на «Вставить функцию».

   

4. Введите КАЛЕНДАРНАЯ НЕДЕЛЯ в поле поиска.

   

5. После того, как вы нашли функцию КАЛЕНДАРНАЯ НЕДЕЛЯ, подтвердите свой выбор с помощью OK.

6. Как только вы окажетесь в поле ввода функции, введите компоненты формулы следующим образом:

   • Serial_number = ячейка, содержащая дату, например A1.
   • Тип числа = числовое значение дня недели в соответствии с таблицей выше.
   

Календарная неделя выводится правильно в соответствии с ISO 8601.

Как рассчитать начало и конец любой календарной недели в Excel

Календарные недели играют решающую роль в профессиональном планировании. Встречи часто планируются или согласовываются на определенную календарную неделю. В этом случае вам нужно

1. Знайте, на какую календарную неделю выпадает определенная дата
2. Узнайте, в какой день начинается или заканчивается определенная календарная неделя.

Эта информация поможет вам, например, при планировании сроков производства в промышленной среде или при организации сроков поставки или выставления счетов.

По специальной формуле можно легко определить начало и конец любой календарной недели. Формула имеет следующую структуру:

= ТЕКСТ (ДАТА (номер года; 1; 7 * калнеделя-3-
WEEKDAY (DATE (номер года ;;); 3)); «ДД.ММ.ГГ»)
& "-" & ТЕКСТ (ДАТА (номер года; 1; 7 * календарная неделя + 3-
WEEKDAY (DATE (номер года;;); 3)); «ДД.ММ.ГГ»)

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

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

В показанной книге следующая формула в ячейке D4 возвращает период «30.09.13 - 06.10.13» в качестве результата:

= ТЕКСТ (ДАТА (C4; 1; 7 * B4-3-ДЕНЬ НЕДЕЛИ
(ДАТА (C4;;); 3)); «ДД.ММ.ГГ») & «-» & ТЕКСТ
(ДАТА (C4; 1; 7 * B4 + 3-ДЕНЬ НЕДЕЛИ (ДАТА (C4;;); 3));
«ДД.ММ.ГГ»)

Этот период составляет 40 календарную неделю из ячейки B4 в 2013 году из ячейки C4.

Результат формулы выводит текст, который нельзя использовать для продолжения вычислений. Чтобы уточнить выводимый период времени, вы можете отобразить результат таким образом, чтобы выводились также дни недели. Для этого измените числовой формат в двух ТЕКСТОВЫХ функциях формулы следующим образом:

= ТЕКСТ (ДАТА (C4; 1; 7 * B4-3-ДЕНЬ НЕДЕЛИ
(ДАТА (C4;;); 3)); «ДДД ДД.ММ.
ГГ ") &" - "& ТЕКСТ (ДАТА (C4; 1; 7 * B4 + 3-
WEEKDAY (DATE (C4;;); 3)); «ДДДДД.ММ.ГГ»)

После копирования формулы во все строки таблицы даты всегда выводятся с днями недели.

Как посчитать недели в Excel и найти правильную дату

Во многих планах недели играют роль как единое целое, например календарные недели или производственные недели. Недели также можно рассчитать и отобразить в Excel с помощью простых для понимания форм.

На следующем рисунке показан список значений даты в столбце A. В столбце B есть значение, указывающее, на сколько недель следует отложить это значение даты:

В столбце C вы хотите использовать формулу, которая вычисляет новую дату на основе даты в столбце A и количества недель в столбце B. Если количество недель больше нуля, дата смещается в будущее, если значение отрицательное - в прошлое.

Вы можете достичь этой цели, умножив количество недель на значение 7 и прибавив его к исходной дате. При описанной выше структуре таблицы формула для ячейки C2 выглядит следующим образом:

= A1 + 7 * B1

Формула применима, потому что Microsoft Excel хранит значения даты внутри как числа. Расстояние от одного дня до другого равно 1. На следующем рисунке показано использование формулы в примере таблицы:

Вывод: календарные недели имеют решающее значение для профессионального планирования встреч

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

Microsoft Excel может указать правильную календарную неделю для каждой даты. Решающим фактором здесь является то, что пользователи в Европе соблюдают европейскую систему дат согласно ISO 8601 при построении своих формул Excel. По умолчанию календарная неделя рассчитывается по системе США, что приводит к отклонениям. В США первый день года обычно присваивается 1 календарной неделе. Напротив, в Германии, Австрии и Швейцарии первая календарная неделя обычно определяется как неделя, которая, как первая неделя года, состоит как минимум из четырех дней. На практике это означает, что неделя 1 в европейской системе - это неделя, которая включает в себя первый четверг года.

Для правильного расчета календарной недели вы можете использовать как функцию КАЛЕНДАРНАЯ НЕДЕЛЯ, так и аргумент функции WEEKSTART в Excel. Также можно настроить отдельные формулы и адаптировать их специально для вашей электронной таблицы.