зміст
У професійній роботі з електронними таблицями нерідко взаємодіяти з датами та часом. Без нього не обійдешся. Тому сам Бог велів навчитися працювати з даними такого типу. Це заощадить вам величезну кількість часу та уникне багатьох помилок під час роботи з електронними таблицями.
На жаль, багато новачків не знають, як відбувається обробка даних. Тому, перш ніж розглядати цей клас операцій, необхідно провести більш детальну навчальну програму.
Як дата представлена в Excel
Інформація про дату обробляється як кількість днів з 0 січня 1900 року. Так, ви не помилилися. Дійсно, з нуля. Але це потрібно для того, щоб була точка відліку, щоб 1 січня вже вважалося числом 1 і так далі. Максимальне підтримуване значення дати становить 2958465, тобто 31 грудня 9999 року.
Цей спосіб дає можливість використовувати дати для розрахунків і формул. Отже, Excel дає можливість визначити кількість днів між датами. Схема проста: від одного числа віднімається секунда, а потім отримане значення перетворюється у формат дати.
Для більшої ясності ось таблиця, у якій відображено дати з відповідними числовими значеннями.
Щоб визначити кількість днів, що минули від дати А до дати Б, потрібно від останнього відняти перший. У нашому випадку це формула = B3-B2. Після його введення результат наступний.
Важливо зауважити, що значення вказано в днях, оскільки ми вибрали для клітинки інший формат, ніж дата. Якби ми спочатку вибрали формат «Дата», то результат був би таким.
Важливо звернути увагу на цей момент у своїх розрахунках.
Тобто для відображення правильного порядкового номера, який повністю відповідає даті, необхідно використовувати будь-який формат, відмінний від дати. У свою чергу, щоб перетворити число в дату, необхідно встановити відповідний формат.
Як відображається час в Excel
Спосіб представлення часу в Excel дещо відрізняється від дати. За основу береться доба, а години, хвилини, секунди - її дробові частини. Тобто 24 години дорівнює 1, а будь-яке менше значення вважається його часткою. Отже, 1 година дорівнює 1/24 доби, 1 хвилина дорівнює 1/1140, а 1 секунда дорівнює 1/86400. Найменшою одиницею часу, доступною в Excel, є 1 мілісекунда.
Подібно до дат, цей спосіб представлення дає можливість виконувати обчислення з часом. Правда, тут є одна незручність. Після розрахунків ми отримуємо частину доби, а не кількість днів.
На скріншоті показані значення в числовому форматі і форматі «Час».
Спосіб обчислення часу подібний до дати. Від більш пізнього необхідно відняти більш ранній час. У нашому випадку це формула = B3-B2.
Оскільки комірка B4 спочатку мала загальний формат, то в кінці введення формули вона відразу змінюється на «Час».
Excel при роботі з часом виконує звичайні арифметичні дії з числами, які потім переводяться в звичний для нас формат часу.
Формат дати й часу
Наскільки нам відомо, дати й час можуть зберігатися в різних форматах. Тому потрібно знати, як їх правильно вводити, щоб форматування було правильним.
Звичайно, при введенні дати і часу можна використовувати порядковий номер дня або частини доби, але такий підхід дуже незручний. Крім того, вам доведеться постійно застосовувати певний формат до клітини, що тільки посилює дискомфорт.
Тому Excel дозволяє вказувати час і дату різними способами. Якщо застосувати один із них, то програма негайно конвертує інформацію у відповідне число та застосовує до комірки правильний формат.
У таблиці нижче наведено список методів введення дати й часу, які підтримує Excel. У лівому стовпці наведено список можливих форматів, а в правому стовпці показано, як вони відображатимуться в Excel після перетворення. Важливо відзначити, що якщо рік не вказано, автоматично призначається поточний, який встановлено в операційній системі.
Насправді способів відображення набагато більше. Але цих достатньо. Крім того, конкретна опція запису дати може відрізнятися залежно від країни чи регіону, а також налаштувань операційної системи.
Спеціальне форматування
Під час роботи з комірками користувач може визначити, який формат буде. Він може зробити так, щоб показувався тільки час, місяць, день і так далі. Також можна налаштувати порядок формулювання дати, а також роздільники.
Для доступу до вікна редагування необхідно відкрити вкладку «Число», де знаходиться опція вікна «Формат клітинок». У діалоговому вікні, що відкриється, буде категорія «Дата», у якій можна вибрати правильний формат дати.
Якщо вибрати категорію «Час», то, відповідно, з'явиться список з варіантами відображення часу.
Щоб застосувати певний параметр форматування до комірки, необхідно вибрати потрібний формат і натиснути «ОК». Після цього буде застосований результат. Якщо форматів, які пропонує Excel, недостатньо, ви можете знайти категорію «Усі формати». Є також багато варіантів.
Якщо жоден варіант не підходить, то завжди можна створити свій. Зробити це дуже просто. Вам просто потрібно вибрати попередньо встановлені формати як зразок і виконати такі дії:
- Виберіть клітинку, формат якої потрібно змінити.
- Відкрийте діалогове вікно «Формат клітинок» і знайдіть вкладку «Число».
- Далі відкривається категорія «Всі формати», де знаходимо поле введення «ТИП». Там потрібно вказати код числового формату. Після введення натисніть «ОК».
- Після цих кроків у комірці відображатиметься інформація про дату й час у спеціальному форматі.
Використання функцій з датами та часом
При роботі з датами і часом користувач може використовувати більше 20 різних функцій. І хоча для когось ця сума може виявитися завеликою, всі вони можуть бути використані для досягнення певних цілей.
Щоб отримати доступ до всіх можливих функцій, необхідно перейти до категорії «Дата й час» групи «Бібліотека функцій». Ми розглянемо лише деякі основні функції, які дозволяють витягувати різні параметри з дат і часу.
YEAR ()
Надає можливість отримати рік, який відповідає певній даті. Як ви вже знаєте, це значення може бути від 1900 до 9999.
Комірка 1 показує дату у форматі ДДДД ДД.ММ.РРРР гг:хх:сс. Це формат, який ми створили раніше. Візьмемо для прикладу формулу, яка визначає, скільки років пройшло між двома датами.
При цьому, якщо придивитися уважніше, то виявиться, що функція обчислила не зовсім правильний результат. Причина в тому, що він використовує лише дати у своїх розрахунках.
MONTH ()
За допомогою цієї функції ви можете виділити номер місяця, який відповідає певній даті. Повертає результат у діапазоні від 1 до 12. Це число, у свою чергу, відповідає числу місяця.
ДЕНЬ()
Подібно до попередніх функцій, ця повертає номер дня в даній даті. Результат розрахунку може коливатися від 1 до 31.
ЧАС()
Як випливає з назви, ця функція повертає номер години в діапазоні від 0 до 23.
ХВИЛИН()
Функція, яка повертає кількість хвилин у певній клітинці. Можливі значення, які повертаються, від 0 до 59.
СЕКУНДИ()
Ця функція повертає ті ж значення, що й попередня, за винятком секунд.
ДЕНЬ()
За допомогою цієї функції ви можете дізнатися номер дня тижня, який використовується в цій даті. Можливі значення від 1 до 7, але майте на увазі, що відлік починається з неділі, а не з понеділка, як ми зазвичай робимо.
Однак, використовуючи другий аргумент, ця функція дозволяє налаштувати формат. Наприклад, якщо передати значення 2 як другий параметр, можна встановити формат так, щоб число 1 означало понеділок, а не неділю. Це набагато зручніше для вітчизняного користувача.
Якщо в другому аргументі написати 2, то в нашому випадку функція поверне значення 6, що відповідає суботі.
СЬОГОДНІ ()
Ця функція дуже проста: для її роботи не потрібні аргументи. Він повертає порядковий номер дати, встановленої на комп’ютері. Якщо його застосувати до комірки, для якої встановлено загальний формат, то він буде автоматично перетворений у формат «Дата».
ТАТА ()
Ця функція також не вимагає жодних аргументів. Працює так само, як і попередній, тільки з датою і часом. Використовується, якщо необхідно вставити в комірку поточні дату і час, які встановлені в комп'ютері. І так само, як і в попередній функції, при застосуванні цієї комірка автоматично перетворюється в формат дати і часу, якщо раніше був встановлений формат «Загальний».
І попередня функція, і ця функція автоматично змінюються щоразу, коли аркуш перераховується, що дозволяє відображати найновіші час і дату.
Наприклад, за такою формулою можна визначити поточний час.
=СЬОГОДНІ()-СЬОГОДНІ()
У цьому випадку формула визначить частку доби в десятковому форматі. Правда, вам доведеться застосувати формат часу до комірки, в якій записана формула, якщо ви хочете відображати саме час, а не число.
ДАТА()
Ця функція має три аргументи, кожен з яких необхідно ввести. Після обчислень ця функція повертає порядковий номер дати. Комірка автоматично перетворюється на формат «Дата», якщо раніше вона мала формат «Загальний».
Аргумент «День» або «Місяць» може бути позитивним або негативним. У першому випадку дата збільшується, а в другому – зменшується.
Ви також можете використовувати математичні операції в аргументах функції ДАТА. Наприклад, ця формула додає 1 рік, 5 місяців і 17 днів до дати в клітинці A1.
А ще така формула дозволяє перетворити текстовий рядок на повноцінну робочу дату, яку можна використовувати в інших функціях.
ЧАС()
Так само, як функція ДАТА(), ця функція має три обов’язкові параметри – години, хвилини та секунди. Після його використання в отриманій комірці з’явиться десяткове число, але сама комірка буде відформатована у форматі «Час», якщо раніше мала формат «Загальний».
За своїм принципом дії функція ЧАС() и ДАТА() багато схожих речей. Тому акцентувати увагу на цьому немає сенсу.
Важливо зауважити, що ця функція не може повертати час, більший за 23:59:59. Якщо результат більший, ніж цей, функція автоматично скидається на нуль.
Функції ДАТА() и ЧАС() можна застосовувати разом.
На цьому знімку екрана клітинка D1, у якій використовуються обидві ці функції, має формат дати й часу.
Функції обчислення дати та часу
Всього є 4 функції, які дозволяють виконувати математичні операції з датою і часом.
DATAMES()
За допомогою цієї функції можна дізнатися порядковий номер дати, що відстає від відомої кількості місяців (або випереджає задану). Ця функція приймає два аргументи: дату початку та кількість місяців. Другий аргумент може бути як позитивним, так і негативним. Перший параметр необхідно вказати, якщо ви хочете розрахувати майбутню дату, а другий – якщо попередню.
EOMY()
Ця функція дає змогу визначити порядковий номер останнього числа місяця, що відстає від даної дати або випереджає її. Має такі ж аргументи, як і попередній.
РОБОЧИЙ ДЕНЬ()
Те саме, що функція DATAMES(), тільки затримка або випередження відбувається на певну кількість робочих днів. Синтаксис аналогічний.
Усі три функції повертають число. Щоб побачити дату, потрібно перевести клітинку у відповідний формат.
CLEAR()
Ця проста функція визначає кількість робочих днів між датою 1 і датою 2.