Це глава з книги Білла Джелу Гуру Excel розширюють горизонти: робіть неможливе з Microsoft Excel .

Завдання: підрахувати, скільки робочих днів потрапляє між двома датами. Функція Excel ЧІСТРАБДНІ прекрасно справляється із завданням, якщо у вас 5-денний робочий тиждень з суботою і неділею як вихідних. Ця замітка покаже вам, як виконати розрахунок робочого тижня будь-якої тривалості і будь-яким набором вихідних.

Прим. Багузіна. Слід зазначити, що, починаючи з версії 2010 в Excel з'явилася більш гнучка функція ЧІСТРАБДНІ.МЕЖД, яка дає можливість розрахунку для будь-якої тривалості робочого тижня і будь-якого (але постійного) набору вихідних днів. Ця функція буде описана в другій частині статті. Проте, підхід Білла Джелу залишається цікавим і сам по собі.

ЧІСТРАБДНІ обчислює кількість робочих днів між двома датами, включаючи початкову і кінцеву дати. Слід вказати більш ранню дату в якості першого аргументу, більш пізню дату в якості другого аргументу, і додатково визначити масив святкових днів в якості третьої аргументу (рис. 1). Формула в осередку C3: = ЧІСТРАБДНІ (A3; B3; $ E $ 2: $ E $ 11). 17.02.09 відняти 10.02.09 дає 8 календарних днів, мінус два вихідних і один святковий день. Разом, 5 робочих днів. Однак, якщо вам «пощастило» працювати з понеділка по суботу, стандартна формула не впорається із завданням.

Рис. 1. ЧІСТРАБДНІ передбачає, що робочий тиждень триває з понеділка по п'ятницю

Завантажити замітку в форматі Word або pdf , Приклади в форматі Excel

Для початку визначте діапазон, і назвіть його Свята. Для цього виділіть діапазон разом з заголовком і пройдіть по меню ФОРМУЛИ -> Створити з виділеного (рис. 2). Переконайтеся, що Ви вибрали в рядку вище. Натисніть Ok в вікні Створення імені з виділеного діапазону.

Рис. 2. Привласнення імені діапазону

Визначивши діапазон, знову введіть формулу в С3. Тепер вона набуде вигляду: = ЧІСТРАБДНІ (A3; B3; Свята)

Тепер перейдемо до розрахунку числа робочих днів при 6-денному робочому тижні (рис. 3). Формула в С3: = СУММПРОИЗВ (- (СЧЁТЕСЛІ (Свята; СТРОКА (ДВССИЛ (A3 & ":" & B3))) = 0); - (ДЕНЬНЕД (СТРОКА (ДВССИЛ (A3 & ":" & B3)); 2) < 7)). Хоча ця формула має справу з масивами, вона в кінцевому рахунку повертає одне значення, так що вам не потрібно використовувати Ctrl + Shift + Enter для введення цієї формули.

Рис. 3. Число робочих днів при 6-денному робочому тижні

Розглянемо роботу формули докладніше. Відносно всіх дат, що входять в діапазон від дати старту до дати закінчення (включно), формула перевіряє дві речі. По-перше, чи не є будь-яка дата святом. По-друге, які дати в діапазоні дат неділі.

Вам потрібен швидкий спосіб, щоб порівняти кожну дату, починаючи з А3, і закінчуючи В3 зі списком свят. В даному прикладі (в осередку С3), у вас тільки 8 днів, але в рядку 5, у вас більше 300 днів. Формула використовує той факт, що в Excel дати зберігаються у вигляді порядкових номерів. Хоча в осередку A3 відображається 20 сiчня 2009, Excel насправді зберігає дату як числове значення 39854.

Перша функція ДВССИЛ формує масив всіх дат, що входять в діапазон. Аргументи всередині ДВССИЛ зчіплюють дату (що зберігається як число) з осередку А3 з датою з B3, розділяючи їх двокрапкою. Проміжний результат обчислення можна побачити у вікні Обчислення формули (рис. 4). Фрагмент ДВССИЛ (A3 & ":" & B3) перетворився в $ 39854: $ 39861.

Рис. 4. Робота фрагмента формули ДВССИЛ (A3 & ":" & B3)

Зазвичай аргумент ДВССИЛ це діапазон типу "А2: IU2". Однак, якщо ви коли-небудь використовували посилання на діапазон від першого стовпчика (A) до останнього стовпчика, ви бачили, що = СУММ (2: 2) еквівалентна = СУММ (А2: IV2) в Excel 2003 і = СУММ (А2: XFD2 ) в Excel 2007. Таким чином, можна посилатися на цілі рядки. Фрагмент СТРОКА ($ 39854: $ 39861) повертає масив з восьми чисел {39854; 39855; ...; 39861} (рис. 5).

Рис. 5. Фрагмент формули СТРОКА

Тепер ви можете порівняти цей масив з восьми чисел з діапазоном святкових дат. Фрагмент СЧЁТЕСЛІ (Свята; СТРОКА (ДВССИЛ (A3 & ":" & B3))) перетворився в СЧЁТЕСЛІ ({39814: 39832: 39860: 39958: 39997: 40063: 40098: 40128: 40143: 40172}; {39854: 39855: 39856: 39857: 39858: 39859: 39860: 39861}). Формула підраховує, скільки разів кожне свято (перший аргумент функції СЧЁТЕСЛІ) співпаде з одним зі значень з діапазону дат (другий аргумент; див. Рис. 6).

Рис. 6. Аргументи функції СЧЁТЕСЛІ

Функція СЧЁТЕСЛІ повертає 1, якщо свято знаходиться в діапазоні дат і 0, якщо - ні (рис. 7). Так як вас цікавлять не святкові дати, а робочі, ви порівнюєте результат функції СЧЁТЕСЛІ з нулем. {0, 0, 0, 0, 0, 0, 1, 0, 0} = 0 повертає {ІСТИНА; ІСТИНА; ІСТИНА; ІСТИНА; ІСТИНА; ІСТИНА; БРЕХНЯ; ІСТИНА; ІСТИНА;}. Операція мінус мінус перетворює масив логічних значень в числові: {1; 1; 1; 1; 1; 1; 0; 1; 1}.

Рис. 7. Фрагмент формули СЧЁТЕСЛІ = 0

Рис. 8 ілюструє тільки що описані етапи розрахунку. Стовпець Е - це значення, що повертаються фрагментом СТРОКА (ДВССИЛ (A3 & ":" & B3)). Стовпець F - СЧЁТЕСЛІ (Свята; {39854; 39855; ...; 39861}) - повертає 0, якщо дата з шпальти Е не збігається ні з одним зі свят, і 1 - якщо збігається. Наприклад, датою в Е3, не відповідає ні одне зі свят, СЧЁТЕСЛІ в F3 повертає 0. В той же час, в осередку F8, знаходиться дата - 39860 - виявлена ​​в списку святкових дат, так що СЧЁТЕСЛІ в F8 повертає 1.

Рис. 8. Додаткові стовпці, що ілюструють роботу формули

У стовпці G, перевіряється, чи рівний результат СЧЁТЕСЛІ одиниці. Якщо так, повертається ІСТИНА, інакше - БРЕХНЯ. У стовпці H введена формула -G, яка перетворює кожне значення ІСТИНА в 1, а БРЕХНЯ в 0. На рис. 3, осередки Н2: Н9 є віртуальні результати першої половини формули, яка знаходить дати - не свято.

Друга половина формули використовує функцію ДЕНЬНЕД, щоб знайти дати, які не є неділями. Функція ДЕНЬНЕД повертає порядковий номер дня тижня і має два аргументи, другий з яких визначає, який день тижня вважати першим (рис. 9).

Рис. 9. Значення другого аргументу функції ДЕНЬНЕД

На рис. 8 в стовпці До представлений віртуальний результат, який повертає функція ДЕНЬНЕД з другим аргументом рамним 2 (понеділок = 1). У стовпці L значення ДЕНЬНЕД порівнюється з числом 7. Будний день повертає значення ІСТИНА, неділя - БРЕХНЯ. У стовпці М логічні значення перетворюються в числові. І, нарешті, в стовпці N перемножуються значення з стовпців Н і М. У стовпці N значення 1 відповідає робочому дню, а значення 0 означає, що день святковий і / або недільний. Дія в стовпці N відповідає перемножування масивів, які є першим і другим аргументами функції СУММПРОИЗВ (рис. 10). Коли відповідні елементи обох масивів містять 1, у нас робочий день. Коли, яке значення першого масиву, або другого (або обох) дорівнює 0, результат 0. Кінцевий результат є сума нулів і одиниць. У нашому випадку значення в осередку С3 дорівнює 6.

Рис. 10. Аргументи функції СУММПРОИЗВ

Якщо у вас 7-денний робочий тиждень, вам залишається тільки виключити святкові дні. Формула спрощується: = СУММПРОИЗВ (- (СЧЁТЕСЛІ (Свята; СТРОКА (ДВССИЛ (A3 & ":" & B3))) = 0)).

Проблема стає складніше, якщо вихідні дні розташовані довільним чином. Наприклад, співробітник працює за сумісництвом в понеділок, середу і п'ятницю. Тепер та частина формули, що перевіряла неділі, повинна перевірити 3 певних дня тижня. Зверніть увагу, що Тіп_возврата 2 функції ДЕНЬНЕД ніколи не повертає 0. Ви можете використовувати його в якості першого аргументу в функцію ВИБІР, щоб вказати, які дні є робочими. За допомогою фрагмента формули = ВИБІР (ДЕНЬНЕД (деякі дати, 2), 1,0,1,0,1,0,0) ми отримаємо 1 для понеділка, середи і п'ятниці, і 0 - для інших днів тижня (рис. 11 ).

Рис. 11. Функція ВИБІР для довільного числа робочих днів

Оскільки функція ВИБІР зазвичай не повертає масив, ви повинні ввести наступну формулу, використовуючи поєднання клавіш Ctrl + Shift + введення: {= СУММПРОИЗВ (- (СЧЁТЕСЛІ (Свята; СТРОКА (ДВССИЛ (A3 & ":" & B3))) = 0); - (ВИБІР (ДЕНЬНЕД (СТРОКА (ДВССИЛ (A3 & ":" & B3)); 2); 1; 0; 1; 0; 1; 0; 0)))}

Резюме: ви познайомилися з концепцією створення величезного масиву всього з двох значень. Наприклад, = РЯДОК (ДВССИЛ ( «1: 10000")) створює масив 10 000 значень від 1 до 10 000. Ви можете використовувати цю концепцію, щоб перевірити великий масив дат, задавши лише початкову та кінцеву точку.

джерело

* * *

Починаючи з версії 2010 в Excel з'явилася більш гнучка функція ЧІСТРАБДНІ.МЕЖД, яка дає можливість розрахунку для будь-якої тривалості робочого тижня і будь-якого (але постійного) набору вихідних днів (рис. 12). У комірці С2 формула має вигляд: = ЧІСТРАБДНІ.МЕЖД (A2; B2; "0101011"; Свята).

Рис. 12. Функція ЧІСТРАБДНІ.МЕЖД

Синтаксис функції: ЧІСТРАБДНІ.МЕЖД (нач_дата; кон_дата; [вихідний]; [свята])

Аргумент Вихідний є необов'язковим. Вказує, які дні тижня є вихідними і не включаються в число робочих днів між початковою і кінцевою датою. Значення може здаватися номером вихідного дня (всього 17 значень, докладніше див. Довідку Excel) або рядком, що визначає, які дні є вихідними.

Строкові значення днів тижня включають сім знаків, кожен з яких позначає день тижня (починаючи з понеділка). Значення 1 представляє неробочі дні, а 0 - робочі дні. У рядку допустимо використовувати тільки знаки 1 і 0. При значенні 1111111 завжди повертається 0. Рядок слід взяти в лапки. У нашому випадку терміну "0101011" означає, що робочими є понеділок, середа та п'ятниця.