###
  • Програми
  • Безпека
  • Новини
  • Цікаве
  • Поради
  • Новини
  • Огляди

    • Як в екселі встановити поточну дату. Додавання часу та дати в Microsoft Excel. Як Excel насправді зберігає та обробляє дати та час

    19.11.2019 Огляди

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

    Група функцій дати та часу відповідає за обробку даних, представлених у форматі дати або часу. В даний час в Excel налічується понад 20 операторів, які входять до блоку формул. З виходом нових версій Excel їх кількість постійно збільшується.

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


    Крім того, Майстер функційможна активувати, виділівши комірку на аркуші та натиснувши комбінацію клавіш Shift+F3. Існує ще можливість переходу у вкладку Формулиде на стрічці в групі налаштувань інструментів "Бібліотека функцій"слід натиснути на кнопку "Вставити функцію".

    Є можливість переміщення до вікна аргументів конкретної формули групи "Дата і час"без активації головного вікна Майстра функцій. Для цього виконуємо переміщення у вкладку Формули. Клацаємо по кнопці "Дата і час". Вона розміщена на стрічці у групі інструментів "Бібліотека функцій". Активується список доступних операторів цієї категорії. Вибираємо той, який потрібний для виконання поставленого завдання. Після цього відбувається переміщення у вікно аргументів.

    ДАТА

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

    Його аргументами є «Рік», "Місяць"і «День». Особливістю обробки даних і те, що функція працює лише з тимчасовим відрізком не раніше 1900 року. Тому, якщо як аргумент у полі «Рік»задати, наприклад, 1898 рік, то оператор виведе в комірку некоректне значення. Природно, що як аргументи "Місяць"і «День»виступають числа відповідно від 1 до 12 і від 1 до 31. Як аргументи можуть виступати і посилання на комірки, де містяться відповідні дані.

    Для ручного введенняформули використовується наступний синтаксис:

    ДАТА (Рік; Місяць; День)

    Близькі до цієї функції за значенням оператори РІК, МІСЯЦЬі ДЕНЬ. Вони виводять у комірку значення, що відповідає своїй назві і мають єдиний однойменний аргумент.

    РОЗНДАТ

    Свого роду унікальною функцією є оператор РОЗНДАТ. Він обчислює різницю між двома датами. Його особливість полягає в тому, що цього оператора немає в переліку формул Майстри функцій, Отже, його значення завжди доводиться вводити не через графічний інтерфейс, а вручну, дотримуючись наступного синтаксису:

    РОЗНДАТ(поч_дата;кон_дата;одиниця)

    З контексту зрозуміло, що як аргументи "Початкова дата"і «Кінцева дата»виступають дати, різницю між якими потрібно обчислити. А ось як аргумент «Одиниця»виступає конкретна одиниця виміру цієї різниці:

    • Рік (y);
    • Місяць (m);
    • День (d);
    • Різниця у місяцях (YM);
    • Різниця у днях без урахування років (YD);
    • Різниця у днях без урахування місяців та років (MD).

    ЧИСТРАБДНІ

    На відміну від попереднього оператора формула ЧИСТРАБДНІпредставлена ​​у списку Майстри функцій. Її завданням є підрахунок кількості робочих днів між двома датами, які задано як аргументи. Крім того, є ще один аргумент - «Свята». Цей аргумент є необов'язковим. Він зазначає кількість святкових днів за досліджуваний період. Ці дні також віднімаються із загального розрахунку. Формула розраховує кількість усіх днів між двома датами, крім суботи, неділі та тих днів, які зазначені користувачем як святкові. Як аргументи можуть виступати як безпосередньо дати, так і посилання на осередки, в яких вони містяться.

    Синтаксис має такий вигляд:

    ЧИСТРАБДНІ(поч_дата;кон_дата;[свята])

    ТДАТА

    Оператор ТДАТАцікавий тим, що немає аргументів. Він у комірку виводить поточну датута час на комп'ютері. Слід зазначити, що це значення не оновлюватиметься автоматично. Воно залишиться фіксованим на момент створення функції до моменту її перерахунку. Для перерахунку достатньо виділити комірку, що містить функцію, встановити курсор у рядку формул і клацнути по кнопці Enterна клавіатурі. Крім того, періодичний перерахунок документа можна увімкнути в його налаштуваннях. Синтаксис ТДАТАтакий:

    СЬОГОДНІ

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

    СЬОГОДНІ()

    Ця функція, як і, як і попередня, для актуалізації вимагає перерахунку. Перерахунок виконується так само.

    ЧАС

    Основним завданням функції ЧАСє висновок задану комірку зазначеного за допомогою аргументів часу. Аргументами цієї функції є години, хвилини та секунди. Вони можуть бути задані як у вигляді числових значень, так і у вигляді посилань, що вказують на осередки, в яких зберігаються ці значення. Ця функція дуже схожа на оператор ДАТА, Тільки на відміну від нього виводить задані показники часу. Розмір аргументу «Годинник»може задаватися в діапазоні від 0 до 23, а аргументів хвилини та секунди – від 0 до 59. Синтаксис такий:

    ЧАС(Години;Хвилини;Секунди)

    Крім того, близькими до цього оператора можна назвати окремі функції ГОДИНУ, ХВИЛИНИі СЕКУНДИ. Вони виводять на екран величину відповідної назви показника часу, що задається єдиним однойменним аргументом.

    ДАТАЗНАЧ

    Функція ДАТАЗНАЧдуже специфічна. Вона призначена не для людей, а для програми. Її завданням є перетворення запису дати у звичайному вигляді на єдиний числове вираз, доступне для обчислень в Excel. Єдиним аргументом цієї функції виступає дата як текст. Причому як і у випадку з аргументом ДАТА, коректно обробляються лише значення після 1900 року. Синтаксис має такий вигляд:

    ДАТАЗНАЧ (дата_як_текст)

    ДЕНЬНЕД

    Завдання оператора ДЕНЬНЕД– виводити у вказану комірку значення дня тижня для заданої дати. Але формула виводить не текстову назву дня, яке порядковий номер. Причому точка відліку першого дня тижня задається у полі «Тип». Так, якщо задати в цьому полі значення «1», то першим днем ​​тижня вважатиметься неділя, якщо «2»- Понеділок і т.д. Але це не обов'язковий аргумент, якщо поле не заповнене, то вважається, що відлік йде від неділі. Другим аргументом є власне дата у числовому форматі, порядковий номер якого потрібно встановити. Синтаксис виглядає так:

    ДЕНЬНЕД(Дата_у_числовому_форматі;[Тип])

    НЕМИЖНІ

    Призначенням оператора НЕМИЖНІє вказівка ​​в заданій комірці номера тижня за вступною датою. Аргументами є власне дата і тип значення, що повертається. Якщо з першим аргументом все зрозуміло, другий вимагає додаткового пояснення. Справа в тому, що в багатьох країнах Європи за стандартами ISO 8601 першим тижнем року вважається той тиждень, на який припадає перший четвер. Якщо ви хочете застосувати цю системувідліку, то в полі типу потрібно поставити цифру «2». Якщо вам більше до душі звична система відліку, де першим тижнем року вважається та, на яку припадає 1 січня, то потрібно поставити цифру «1»або залишити поле незаповненим. Синтаксис у функції такої:

    НОМТИЖНІ(дата; [тип])

    ДОЛЯРОКУ

    Оператор ДОЛЯРОКУздійснює пайовий розрахунок відрізка року, укладеного між двома датами до всього року. Аргументами цієї функції є дві дати, є межами періоду. Крім того, ця функція має необов'язковий аргумент «Базис». У ньому вказується спосіб обчислення дня. За замовчуванням, якщо жодного значення не встановлено, береться американський спосіб розрахунку. У більшості випадків він якраз і підходить, тому найчастіше цей аргумент заповнювати взагалі не потрібно. Синтаксис набуває такого вигляду:

    ДОЛЯРОКУ(поч_дата;кон_дата;[базис])

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

    При роботі з документами в Microsoft Excelіноді виникає необхідність додати час або дату, або обидві ці величини. На жаль, не всі користувачі знають про спеціальні засоби, реалізовані в Екселі, які дозволяють швидко вирішити цю проблему. У цій статті розглянемо, як вставити час або дату Excel. Давайте розумітися. Поїхали!

    Для додавання даних використовують додатковий функціонал

    Щоб вирішити поставлене завдання, потрібно скористатися однією з двох функцій програми: «Поточна дата» чи «Сегодня». Зверніть увагу, що наведені інструменти не підходять для випадків, коли число має бути фіксованим, тобто не змінюватися щодня.

    Насамперед визначте осередок, з яким працюватимете. Перейдіть на вкладку «Формули». У блоці «Бібліотека функцій» натисніть «Дата та час». Потім виберіть «Сьогодні» та клацніть по кнопці «ОК». Після цього вибрана вами область міститиме поточне значення, яке змінюватиметься щодня. Те саме можна зробити за допомогою комбінації клавіш Ctrl+Shift+4, в деяких випадках Ctrl+Ж або ввести «=СЬОГОДНІ()» у спеціальне поле для формул.

    Щоб додати час у тому ж розділі стрічки інструментів, натисніть пункт «Час». Зверніть увагу, що всі значення будуть виставлені відповідно до інформації з календаря та годинника вашого комп'ютера.

    Встановлені вами значення будуть оновлюватися щоразу при відкритті книги, але, якщо необхідно оновити дані в процесі роботи з книгою, перейдіть на вкладку «Формули» та натисніть кнопку «Перелік», розташовану в блоці стрічки інструментів «Обчислення». Також можна використовувати кнопку "Параметри обчислень", потім потрібно вибрати пункт "Автоматично". Існує більш простий і швидкий варіант- скористатися кнопкою F9 на клавіатурі.

    Якщо потрібно, щоб у полі відображався лише годинник, відкрийте вікно «Формат комірок». У розділі "Числові формати" вкажіть "Час", а в пункті "Тип" натисніть на відповідний варіант відображення годинника. Альтернативний спосіб – скористатися комбінацією клавіш Ctrl+Shift+6.

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

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

    Види дат

    Excel розглядає два варіанти значення "Дата поточна". Перший - це фіксоване значення поточної дати і часу, що зберігається на персональному комп'ютерікористувача. Одного разу введене в робочий лист значення не змінюватиметься незалежно від зміни реальної дати і часу. Коли може знадобитися цей варіант? Відповідей безліч, наприклад, коли зберігаємо дату прийому співробітника працювати чи звільнення, вводимо дату надходження товару складу. Дані значення мають бути статичними, адже з часом вони не змінюються.

    Другий варіант значення "Дата поточна" - динамічний, змінний, оновлюваний. Найпоширеніше застосування цього варіанта – це значення дати та часу в кутку робочого аркуша, у складі напису "Сьогодні 14 травня 2017 р.". Даний варіант широко використовується у формулах, наприклад, для обчислення, скільки днів пройшло з певного числа. Кадровик таким чином може дізнатися, чи не закінчився випробувальний термін того чи іншого співробітника, а працівник складу перевірить, чи не закінчився термін придатності в партії товару.

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

    Розглянемо, як у Excel вставити ці два види дат.

    Дата фіксована, не оновлювана

    Значення Excel "Поточна дата" може бути отримане шляхом простого натискання клавіш Ctrl і ";". Просто встаньте на потрібній комірці в робочому аркуші і відразу, утримуючи клавішу Ctrl, натисніть знак крапки з комою. У осередку з'явиться дата поточна у короткому форматі, наприклад 14.05.2017.

    Для вставки часу виконайте схожу операцію: утримуючи клавішу Ctrl, натисніть і утримуйте Shift і натисніть точку з комою. Всі. Поточний час вставився у форматі "годинник:хвилини".

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

    Дата динамічна, оновлювана

    Дата, що поточна в оновлюваному вигляді, задається в Excel за допомогою формул. Варіанту два:

    • Формула "=СЬОГОДНІ()" дозволяє отримати значення поточної дати.
    • Формула "=ТДАТА()" дозволяє отримати значення поточної дати та часу.

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

    Щоб ввести дату Excel, використовуйте символи "/" або "-". Щоб ввести час, використовуйте ":" (двокрапка). Ви можете ввести дату та час в одному осередку.

    Примітка:На малюнках цієї статті представлені дати у форматі США та ряду інших країн, де спочатку йдуть місяці, а потім дні. Тип формату залежить від ваших регіональних. Тут ви можете отримати додаткову інформаціюпро формати дати та часу.

    РІК, МІСЯЦЬ, ДЕНЬ

    Щоб отримати рік дати, скористайтеся функцією РІК(YEAR):

    РІК(A1)
    =YEAR(A1)

    Примітка:Використовуйте функції МІСЯЦЬ(MONTH) та ДЕНЬ(DAY), щоб отримати місяць та день дати.

    Функція ДАТА

    Примітка:Функція ДАТА(DATE) приймає три аргументи: рік, місяць та день. Excel знає, що 6+2=8 – це серпень. Як відомо, у серпні 31 день, тому програма переходить до наступного місяця (23 серпня + 9 днів = 1 вересня).

    Поточна дата та час

    Щоб отримати поточну дату та час, скористайтеся функцією ТДАТА(NOW):

    ТДАТА()
    =NOW()

    Примітка:Використовуйте функцію СЬОГОДНІ(TODAY), щоб отримати лише поточну дату. Щоб отримати тільки поточний час, використовуйте наступну формулу (не забудьте застосувати до комірки формат часу):

    ТДАТА()-СЬОГОДНІ()
    =NOW()-TODAY()

    ГОДИННА, ХВИЛИНА, СЕКУНДА

    Щоб повернути годинник, використовуйте функцію ГОДИНУ(HOUR):

    ГОДИННИК(A1)
    =HOUR(A1)

    Примітка:Використовуйте функції ХВИЛИНИ(MINUTE) та СЕКУНДИ(SECOND), щоб повернути хвилини та секунди відповідно.

    Функція ЧАС

    Щоб додати годинник, хвилину та/або секунду, використовуйте функцію ЧАС(TIME):

    ЧАС(ГОДИНА(A1)+2;ХВИЛИНИ(A1)+10;СЕКУНДИ(A1)+70)
    =TIME(HOUR(A1)+2,MINUTE(A1)+10,SECOND(A1)+70)

    Примітка: Excel додасть 2 години, 10+1 = 11 хвилин та 70-60 = 10 секунд.

    Для роботи з датами Excel у розділі з функціями визначено категорію «Дата і час». Розглянемо найпоширеніші функції у цій категорії.

    Як Excel обробляє час

    Програма Excel "сприймає" дату і час як звичайне число. Електронна таблиця перетворює подібні дані, прирівнюючи добу одиниці. В результаті значення часу є частка від одиниці. Наприклад, 12.00 – це 0,5.

    Значення дати електронна таблиця перетворює на число, що дорівнює кількості днів від 1 січня 1900 року (так вирішили розробники) до заданої дати. Наприклад, під час перетворення дати 13.04.1987 виходить число 31880. Тобто від 1.01.1900 минуло 31 880 днів.

    Цей принцип є основою розрахунків тимчасових даних. Щоб знайти кількість днів між двома датами, достатньо від пізнішого тимчасового періоду відібрати раніше.

    Приклад функції ДАТА

    Побудова значення дати складає його з окремих елементів-чисел.

    Синтаксис: рік; місяць, день.

    Усі аргументи є обов'язковими. Їх можна задати числами чи посиланнями на осередки з відповідними числовими даними: для року – від 1900 до 9999; для місяця – від 1 до 12; на день – від 1 до 31.

    Якщо для аргументу «День» задати більше (ніж кількість днів у зазначеному місяці), то зайві дні перейдуть на наступний місяць. Наприклад, вказавши на грудень 32 дні, отримаємо в результаті 1 січня.

    Приклад використання функції:

    Задамо більша кількістьднів для червня:

    Приклади використання як аргументів посилань на комірки:

    Функція РОЗНДАТ в Excel

    Повертає різницю між двома датами.

    Аргументи:

    • початкова дата;
    • кінцева дата;
    • код, що означає одиниці підрахунку (дні, місяці, роки та ін.).

    Способи виміру інтервалів між заданими датами:

    • для відображення результату днями – «d»;
    • у місяцях - "m";
    • у роках - "y";
    • у місяцях без урахування років - "ym";
    • у днях без урахування місяців та років – «md»;
    • у днях без урахування років – «yd».

    У деяких версіях Excel при використанні останніх двох аргументів (md, yd) функція може видати помилкове значення. Найкраще застосовувати альтернативні формули.

    Приклади дії функції РОЗНДАТ:

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

    Функція РІК в Excel

    Повертає рік як ціле число (від 1900 до 9999), що відповідає заданій даті. У структурі функції лише один аргумент – дата у числовому форматі. Аргумент повинен бути введений за допомогою функції ДАТА або подавати результат обчислення інших формул.

    Приклад використання функції РІК:

    Функція МІСЯЦЬ в Excel: приклад

    Повертає місяць як ціле число (від 1 до 12) для заданої у числовому форматі дати. Аргумент – дата місяця, який потрібно відобразити, у числовому форматі. Дати текстового формату функція обробляє неправильно.

    Приклади використання функції МІСЯЦЬ:

    Приклади функцій ДЕНЬ, ДЕНЬНЕД і НОМНЕДІЛИ в Excel

    Повертає день як ціле число (від 1 до 31) для заданої у числовому форматі дати. Аргумент – дата дня, який потрібно знайти у числовому форматі.

    Щоб повернути порядковий номер дня тижня для цієї дати, можна застосувати функцію ДЕНЬНЕД:

    За умовчанням функція вважає неділю першим днем ​​тижня.

    Для відображення порядкового номера тижня для зазначеної дати застосовується функція НОМТИЖНЯ:

    Дата 24.05.2015 припадає на 22 тижні на рік. Тиждень починається з неділі (за умовчанням).

    Як другий аргумент зазначена цифра 2. Тому формула вважає, що тиждень починається з понеділка (другий день тижня).

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