Excel: Розрахунок тижнів - ось як це працює з формулою

Знайдіть потрібну функцію

У Microsoft Excel доступна функція КАЛЕНДАРНИЙ ТИЖДЕНЬ, за допомогою якої можна відобразити календарний тиждень на певну дату. Наприклад, ви можете автоматично відобразити, на якому тижні відбувається зустріч. Функція таблиці CALENDAR WEEK у Excel також має свої підводні камені. Це тому, що календарний тиждень у Німеччині, Австрії та Швейцарії визначається інакше, ніж у сусідніх європейських країнах або США. У цій статті показано, як все ще можна обчислити правильний календарний тиждень та відмінності між календарним тижнем у регіоні DACH та інших країнах.

Функція тижня календаря в Excel

Функція «КАЛЕНДАРНИЙ ТИЖДЕНЬ» також розраховує календарні тижні в європейських версіях Excel за американським стандартом. Перший день року зазвичай відноситься до календарного тижня 1.

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

= КАЛЕНДАРНИЙ ТИЖДЕНЬ (ДАТА)

У Німеччині, Австрії та Швейцарії календарний тиждень визначається по -різному. В основному це тиждень, який, як і перший тиждень року, складається щонайменше з чотирьох днів. Якщо перше січня припадає на п’ятницю, суботу чи неділю, функція таблиці КАЛЕНДАРНИЙ ТИЖДЕНЬ повертає неправильний результат для Європи.

Правильно визначайте календарні тижні в Excel - ось як це працює

Щоб відобразити правильний календарний тиждень для Німеччини, Австрії чи Швейцарії, вам потрібна формула. Наприклад, це має бути наступним для комірки С4:

= TRIM ((A4-DATE (РІК (A4-REMAIN (A4-2,7) +3); 1; REMAIN (A4-2,7) -9)) / 7)

Формула складна і обширна. Щоб пояснити, як це працює, ми розглянемо окремі компоненти формули окремо:

• Функція REST (A4-2; 7) з'являється у формулі кілька разів. Ця функція ділить дату на 7 і повертає отриманий залишок. Ця функція повертає значення 0 для понеділків, значення 1 для вівторків до значення 6 для неділі.
• У формулі число 2 віднімається від значення дати. Причиною цього є те, що базовою датою 1.01.1900 була неділя. Ділиться на 7 результатів на залишок 1. З цієї причини понеділок буде вирішено на число 2. Щоб виправити кількість днів до 0 у неділю, 1 у понеділок тощо, число 2 віднімається від дати.
• Виходячи з цього розрахунку за тиждень, частина (РІК (A4-REST (A4-2; 7) +3) забезпечує річну величину, зменшену на 1 за перші три дні року, за умови, що ці дні є п’ятницею, суботою чи неділею акти.
• Це річне значення включається до дати в функції DATE. Ця дата зазвичай формується з другим аргументом 1 для січня місяця.
• Як щоденне значення, перенесіть функцію REST, описану вище, до функції DATE мінус значення 9. Це зазвичай призводить до від'ємного добового значення. Формула показує, що дата, обчислена за DATE, зазвичай є груднем.
• Перші три дні року, однак, це грудень року передостаннього за умови, що ці три дні - п’ятниця, субота чи неділя.
• Ви віднімаєте цю дату, обчислену функцією ДАТА, від дати у стовпці А, ділите різницю на 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-
ТИЖНИК (ДАТА (номер року ;;); 3)); "DD.MM.YY")
& "-" & ТЕКСТ (ДАТА (номер року; 1; 7 * календарний тиждень + 3-
ТИДЕНЬ (ДАТА (номер року ;;); 3)); "DD.MM.YY")

Ви передаєте формулі два аргументи: З аргументом Номер року визначте рік, в якому проходить календарний тиждень. Ви передаєте номер календарного тижня з аргументом Kalweek.

Результат формули - це дата понеділка цього календарного тижня, за яким слідує дефіс і дата неділі календарного тижня.

У наведеній робочій книзі наступна формула в комірці D4 містить результат "30.09.13 - 10.10.2016" як результат:

= ТЕКСТ (ДАТА (C4; 1; 7 * B4-3-ТИЖНИК
(ДАТА (C4 ;;); 3)); "DD.MM.YY") & "-" & ТЕКСТ
(ДАТА (C4; 1; 7 * B4 + 3-ТИДЕНЬ (ДАТА (C4 ;;); 3));
"DD.MM.YY")

Цей період - календарний тиждень 40 з комірки В4 у 2013 році з комірки С4.

Результат формули виводить текст, який не можна використовувати для продовження обчислення. Для того, щоб уточнити період часу, який виводиться, можна відобразити результат таким чином, щоб також виводилися дні тижня. Для цього змініть числовий формат у двох функціях TEXT формули таким чином:

= ТЕКСТ (ДАТА (C4; 1; 7 * B4-3-ТИЖНИК
(ДАТА (C4 ;;); 3)); “DDD DD.MM.
YY “) &“ - “& TEXT (ДАТА (C4; 1; 7 * B4 + 3-
ТИДЕНЬ (ДАТА (C4 ;;); 3)); "DDDDD.MM.YY")

Після копіювання формули у всі рядки таблиці дати завжди виводяться з днями тижня.

Як розрахувати тижні в Excel і знайти правильну дату

У багатьох планах тижні відіграють роль одиниці, наприклад, календарні тижні або тижні виробництва. Тижні також можна обчислити та відобразити в Excel за допомогою простих для розуміння форм.

Наступний малюнок показує список значень дати у стовпці А. У стовпці В є значення, яке вказує на скільки тижнів це значення дати слід відкласти:

У стовпці С потрібно використати формулу, яка обчислює нову дату на основі дати у стовпці А та кількості тижнів у стовпці В. Якщо кількість тижнів є значенням більше нуля, дата зміщується на майбутнє, якщо значення від’ємне - на минуле.

Ви можете досягти цієї мети, помноживши кількість тижнів на значення 7 і додавши її до початкової дати. З описаною вище структурою таблиці формула для комірки С2 виглядає так:

= A1 + 7 * B1

Формула застосовна, оскільки Microsoft Excel зберігає значення дати всередині як числа. Відстань від одного дня до іншого становить 1. Наступний малюнок показує використання формули у прикладі таблиці:

Висновок: Календарні тижні мають вирішальне значення для професійного управління зустрічами

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

Microsoft Excel може вказати правильний календарний тиждень для кожної дати. Вирішальним фактором тут є те, що користувачі в Європі дотримуються європейської системи дати відповідно до ISO 8601 при складанні своїх формул Excel. За замовчуванням календарний тиждень розраховується за американською системою, що призводить до відхилень. У США перший день року зазвичай віднесено до календарного тижня 1. У Німеччині, Австрії та Швейцарії, навпаки, перший календарний тиждень зазвичай визначається як тиждень, який як перший тиждень року складається щонайменше з чотирьох днів. На практиці це означає, що 1 тиждень у європейській системі - це тиждень, що включає перший четвер року.

Для правильного обчислення календарного тижня можна використовувати як функцію КАЛЕНДАРНИЙ ТИЖДЕНЬ, так і аргумент функції ТИЖНЯЧИЙ ЗАПУСК у Excel. Також можна налаштувати окремі формули та адаптувати їх спеціально до електронної таблиці.