підказки

У серії "Поради та підказки в Excel" ми пропонуємо відповіді на запитання учасників, пов’язані з Excel, які не є поширеними в програмі наших курсів. Ми створили мозаїку відповідей, яка може допомогти вам працювати з Excel, і ви ...
Це п’ята частина, в якій ми маємо справу в основному з функціями часу, як розрахувати в Excel вік чи тривалість роботи тощо.

Ви вже стикалися з необхідністю обчислювати вік людини з точністю років, місяців чи навіть з точністю до доби? Або із завданням з’ясувати, чи повинен батько мати при собі свідоцтво про відвідування школи, оскільки його дитина досягла 18 років? Як розрахувати тривалість роботи на підприємстві? Скільки днів знадобиться для подорожі корабля з порту Азії до Європи?

В Excel ми можемо зробити це легко і важко, дуже точно і приблизно ... швидко і повільно 🙂

Функції дати в Excel

Серед майже п’ятисот функцій Excel має декілька функцій, які допоможуть вам розрахувати дати та час.
На зображенні ви можете побачити вгорі огляд з описом основних - ДЕНЬ, МІСЯЦЬ, РІК, ТИЖДЕНЬ та ISOWEEKNUM.
Перші три функції повертають значення дня, місяця чи року з дати. Результатом WEEKNUM є серійний номер тижня дати у вказаній клітинці. ISOWEEKNUM також повертає порядковий номер тижня дати у вказаній клітинці, але на відміну від WEEKNUM, результатом є номер тижня відповідно до міжнародного стандарту. Однак на практиці інші дві функції використовуються більше.

До речі - чи знали ви, що кількість тижнів у світі різниться?
Є країни, які вважають перший тиждень року першим тижнем 1 січня. У той же час є країни, які вважають першим тижнем року понеділок, вівторок, середу чи четвер. Або коли вони святкують Новий рік із собою десь у січні ... То який перший тиждень у році?
Міжнародна організація зі стандартизації представила ISO 8601 наприкінці 1980-х рр. Перший тиждень року ISO - це перший четвер григоріанського року, тиждень 4 січня.

Для чого це добре?
Наприклад, ... експортер повідомляє зовнішньоторговельного партнера, що товар буде готовий до відвантаження з вівторка, 23 тижня. Він замовляє вантажівку, корабель, літак, щоб перевезти товар на його склад. І нічого. Вантажівка чекає, вони не можуть знайти вантаж на складі. Телефонні дзвінки, лайка, нервозність. Після години пошуку інформація виявилася правильною, але новий працівник використав місцеве позначення тижня. Йому не спало на думку, що тиждень за кордоном можна було б позначити інакше (власний досвід автора з експортером). Можна.

Як розрахувати вік в Excel

Давайте розглянемо, як обчислити дату в Excel на конкретних прикладах.
Нас цікавить - здебільшого для різноманітних адміністративних завдань на роботі - скільки років людині (робітникові, дитині ...) до теперішнього "сьогодні". Ми зацікавлені в тому, як розрахувати вік в Excel? Кількість відпрацьованих років?
Це не складно, якщо ми знаємо правильні функції або знаємо, як Excel працює з датами.

Припустимо, що людей, яких ми збираємося дізнатись про вік, ми розташували в таблиці, як на малюнку.

Спосіб 1

Excel дозволяє нам з’ясувати просту різницю між двома датами скільки днів минуло між двома датами. Тому ми могли б записати поточну дату сьогодні в сусідній стовпець поруч із колонкою дати народження (тепер неправильно позначено як Вік). І ми напишемо в наступній колонці формула, за якою ми обчислюємо різницю між двома клітинками з датами в рядку поруч з. Ось як це роблять початківці, на самому початку розраховуючи на Excel.
Результат правильний, але ...

(Якщо в стовпці замість кількості днів з’являється дата, що не говорить, не впадайте у відчай. Проблема полягає лише у форматі комірки. Установіть виділення у верхньому вікні на вкладці Домашня сторінка на панелі Число на Загальне.)

Отриманий результат має один недолік - він буде дійсним лише на дату 7.2.2020. Отже, трохи вдосконалене "досконалість" розгляне формулу з функцією, яка полегшить обчислення.

Щоб не бути прив’язаним лише до однієї дати «сьогодні», яку ми ввели в стовпець D у попередньому способі обчислення, ми використаємо іншу функцію при обчисленні у формулі. Функція дати СЬОГОДНІ в Excel завжди використовується поточна дата з календаря комп’ютера. Тож нам зовсім не потрібно писати поточну дату в таблиці. Розрахунок відбуватиметься кожного разу, коли ми відкриваємо файл, до нової поточної "сьогоднішньої" дати. Хоча добре?

Функція виглядає трохи дивним під час написання, оскільки вона не має аргументів - вона не має аргументів у дужках. Формула з нею виглядає так у клітинці:

Всередині функції ми нічого не пишемо, немає місця. Тільки ліва та права дужки.
СЬОГОДНІ покаже нам результат у комірці у вигляді поточної дати.

У нашому прикладі ми оцінимо функцію, оскільки після її використання рішення виглядає так.
Замість дати введіть формулу різниці в першу комірку стовпця D (D7):

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

Ось так ми отримали кількість днів життя працівника. Якщо ми хочемо певну кількість років, нам все одно доведеться перетворити результат на роки. Як?
Оскільки кожен четвертий рік проникний, ми ділимо кількість отриманих днів життя на 365,25. Ми отримуємо досить точний вік усіх працівників.
Тоді вся формула в першій комірці виглядає так:

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

Ми можемо відображати результат лише протягом цілих років, використовуючи функцію INT (ціле число), яка забезпечує відображення лише цілочисельної частини. Це виглядало б так:

Примітка:
Якщо нам потрібно розрахувати вік для конкретного дня, наприклад станом на 31.12.2019, замість СЬОГОДНЯ ми напишемо конкретну дату у формі у формі ДАТА (2019; 12; 31).

Спосіб 2

Для розрахунку ми використаємо ще одну функцію дати Excel - YEARFRAC. Функція YEARFRAC має два обов’язкові аргументи (дата початку та закінчення) та необов’язковий аргумент відповідно до відповідної фінансової операції (не використовується зазвичай).

Ми проведемо обчислення в бічній колонці, щоб можна було порівняти результати. Перша комірка буде виглядати так:

Двічі клацніть квадратний маркер, щоб скопіювати його до решти стовпця.
Ми виявили різницю між двома датами за допомогою функції YEARFRAC, за допомогою якого ми обчислюємо різницю двох комірок з датами.

Порівнюючи два стовпці Вік, ми бачимо різницю. Вік, обчислений діленням і діленням на 365,25, не зовсім точний. Результат, отриманий функцією YEARFRAC, є точним із правильно введеними вхідними значеннями (у виняткових випадках підрахунку банківських років 30/360 з датою початку 29.2. Це може повернути неправильні результати).
Якщо нас цікавить лише кількість років, обох розрахунків достатньо.

Якщо нам потрібно обчислити різницю дат для конкретного дня, наприклад станом на 31.12.2019, навіть при використанні функції YEARFRAC замість TODAY (), ми пишемо конкретну дату у вигляді DATE (2019; 12; 31 ) всередині нього.

(Останній рядок показує формули, що використовуються в комірках у виділеній комірці у стовпці над ними.)

Як розрахувати тривалість трудових відносин

Ми розраховуємо тривалість трудових відносин в Excel, як і в попередньому випадку.
Погляньте на стіл.
Замість дати народження ми вводимо або копіюємо з іншої таблиці дати вступу працівників на роботу. Процедура та логіка створення формули подібні.

Ми проводимо обчислення за допомогою функції YEARFRAC у стовпці G.
Перша комірка буде виглядати так:

Двічі клацніть квадратний маркер у куті комірки, щоб скопіювати формулу в решту стовпця.

Якщо вам потрібні лише роки або вам не подобається кількість десяткових знаків, ви можете налаштувати її за допомогою інструментів зі стрілками на вкладці Домашня сторінка на панелі «Число» (Формат чисел). Додайте і відніміть кількість відображених десяткових знаків. Або ми можемо використовувати функцію INT, згадану вище, безпосередньо у формулі наступним чином:

Якщо нам потрібно підрахувати кількість років, відпрацьованих у конкретний день, наприклад 31.12.2019, замість TODAY () ми пишемо конкретну дату у формі DATE (2019; 12; 31) всередині функції, тому формула буде виглядати так:

Звичайно, ми також можемо розрахувати тривалість трудових відносин першим способом, описаним вище - відніманням двох дат і діленням різниці на 365,25. Результат буде менш точним для волосся, ніж функція YEARFRAC.

Для поціновувачів у Excel

Розрахунок віку чи періоду може мати різну форму залежно від вимог до відображення результату.
Формула простого відображення кількості років (переживань, відпрацьованих годин…) на сьогоднішній день може бути наступною:

де комірка С3 буде датою, з якої ми відлічуємо роки, тобто дата народження, дата початку роботи,…
Формула для більш точного подання розрахунку років, місяців і днів може виглядати так:

Кілька методів обчислення, включаючи проблеми із вживанням словацьких слів рік, роки, роки, ... навіть з англійською версією можна побачити на малюнку нижче. Якщо хтось хоче протестувати ці формули або використовувати їх на практиці, ви можете завантажити файл електронної таблиці Excel на свій комп’ютер, натиснувши кнопку в кінці статті.

При обчисленні за цією формулою я використовував менш відому функцію дат Excel DATEIF.
Ви можете знайти його більш детальний опис з 2019 року в довідці Excel (клавіша F1).

Висновок

Як видно з останньої таблиці, існує кілька рішень при обчисленні дати.
Для практичного використання я рекомендую - вибирайте ту, яку найкраще розумієте. Так що ви можете будь-коли легко інтерпретувати або коригувати це за потреби.

Ті самі принципи та формули можуть бути використані на практиці, наприклад, для визначення того, чи повинен батько подавати роботодавцю довідку про відвідування школи (у формулі ми використовуємо дату народження та СЬОГОДНІ або інші практичні завдання. Excel полегшить наше життя завдяки своїм функціям 🙂

Хочете дізнатися більше про роботу з Excel та функції часу? Приходьте на курс Excel, вивчайте та приміряйте приклади. Через два дні курсу Excel I або Excel II ви дізнаєтесь більше про роботу з цією електронною таблицею, вивчите комбінації клавіш, способи розрізнення типів електронних таблиць і коли їх використовувати, навчитеся створювати формули, використовувати функції, ... багато.
Ви будете знати, як розрахувати дату після Excel II, ми детальніше розглянемо функції часу в Excel III 🙂