Excel: Изчислете седмици - така работи с формула

Намерете правилната функция

В Microsoft Excel е налична функцията CALENDAR WEEK, с която можете да покажете календарната седмица за определена дата. Например, можете автоматично да покажете в коя седмица се провежда среща. Функцията за таблица CALENDAR WEEK в Excel също има своите клопки. Това е така, защото календарната седмица е определена по различен начин в Германия, Австрия и Швейцария, отколкото в съседните европейски страни или САЩ. Тази статия показва как все още можете да изчислите правилната календарна седмица и разликите между календарната седмица в региона DACH и други страни.

Функцията CALENDAR WEEK в Excel

Функцията CALENDAR WEEK също изчислява календарните седмици в европейските версии на Excel, използвайки американския стандарт. Първият ден от годината обикновено се определя за календарна седмица 1.

Синтаксисът за изчисляване на календарната седмица е:

= КАЛЕНДАРНА СЕДМИЦА (ДАТА)

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

Определете правилно календарните седмици в Excel - така работи

За да покажете правилната календарна седмица за Германия, Австрия или Швейцария, имате нужда от формула. Например, това трябва да бъде следното за клетка C4:

= TRIM ((A4-DATE (ГОДИНА (A4-ОСТАВА (A4-2,7) +3); 1; ОСТАВА (A4-2,7) -9)) / 7)

Формулата е сложна и обширна. За да обясним как работи, разглеждаме отделните компоненти на формулата поотделно:

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

Алтернативно: Използвайте функционалния аргумент WOCHENSTART

Друга възможност да работите с функцията CALENDAR WEEK в Microsoft Excel и да постигнете коректни резултати е да добавите функцията аргумент на функцията седмица във формулата. Подобно на формулата, показана по -горе, са необходими усилия за изграждането на формулата.

Аргументът на функцията WOCHENSTART може да прави разлика между двете различни системи за изчисляване на календарната седмица:

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

Ако използвате функционалния аргумент WOCHENSTART и функцията календарна седмица, винаги трябва да въвеждате следните числа като аргументи:

Понеделник	2 или 11
Вторник	12
Сряда	13
Четвъртък	14
Петък	15
Събота	16
Неделя	1 или 17

Свържете функцията CALENDAR WEEK с функционалния аргумент WOCHENSTART в 6 стъпки

За да свържете функцията за календарната седмица с функцията за започване на седмицата, направете следното:

1. Въведете дата в електронната си таблица на Excel.

2. Щракнете с левия бутон в клетката, в която искате да включите календарната седмица.

3. Преминете към раздела FORMULAS и щракнете с левия бутон на мишката върху „Insert function“.

   

4. Въведете CALENDAR WEEK в полето за търсене.

   

5. След като намерите функцията КАЛЕНДАРНА СЕДМИЦА, потвърдете избора си с OK.

6. Веднага щом сте в полето за въвеждане на функцията, въведете компонентите на формулата, както следва:

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

Календарната седмица е правилно изведена в съответствие с ISO 8601.

Как да изчислите началото и края на всяка календарна седмица в Excel

Календарните седмици играят решаваща роля в професионалното планиране. Назначенията често се планират или договарят за определена календарна седмица. В такъв случай трябва

1. Знайте и коя календарна седмица пада определена дата
2. Разберете коя дата започва или свършва определена календарна седмица.

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

Използвайки специална формула, можете лесно да определите началото и края на всяка календарна седмица. Формулата има следната структура:

= ТЕКСТ (ДАТА (номер на годината; 1; 7 * calweek-3-
СЕДМИЦА (ДАТА (номер на годината ;;); 3)); "DD.MM.YY")
& "-" & ТЕКСТ (ДАТА (номер на годината; 1; 7 * календарна седмица + 3-
СЕДМИЦА (ДАТА (номер на годината ;;); 3)); "DD.MM.YY")

Предавате два аргумента на формулата: С аргумента Номер на годината дефинирайте годината, в която се намира календарната седмица. Предавате номера на календарната седмица с аргумента Kalweek.

Резултатът от формулата е датата на понеделник на тази календарна седмица, последвана от тире и датата на неделята на календарната седмица.

В показаната работна книга следната формула в клетка D4 представя периода „09/30/13 - 10/6/13“ като резултат:

= ТЕКСТ (ДАТА (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 с помощта на лесни за разбиране формуляри.

Следващата фигура показва списък на стойностите на датата в колона А. В колона B има стойност, която показва колко седмици тази стойност на датата трябва да бъде отложена:

В колона C искате да използвате формула, която изчислява новата дата въз основа на датата в колона A и броя на седмиците в колона B. Ако броят на седмиците е стойност, по -голяма от нула, датата се премества в бъдещето, ако стойността е отрицателна, тя се премества в миналото.

Можете да постигнете тази цел, като умножите броя на седмиците със стойността 7 и я добавите към първоначалната дата. С описаната по -горе структура на таблицата формулата за клетка С2 изглежда така:

= A1 + 7 * B1

Формулата е приложима, защото Microsoft Excel съхранява стойностите на датата вътрешно като числа. Разстоянието от един ден до друг е 1. Следващата фигура показва използването на формулата в примерна таблица:

Заключение: Календарните седмици са от решаващо значение за професионалното управление на назначенията

Назначенията определят живота ни както в професионалния, така и в личния живот. Когато планирате срещи, календарните седмици стават все по -важни с увеличаване на интензивността на срещите, тъй като те могат да бъдат използвани за създаване на по -голяма яснота при планирането на срещи.

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

За правилното изчисляване на календарната седмица можете да използвате както функцията CALENDAR WEEK, така и аргумента WEEKSTART функция в Excel. Възможно е също така да настроите отделни формули и да ги адаптирате специално към вашата електронна таблица.