Тема: Функції ДАТИ і ЧАСУ
Мета: Навчитися
використовувати текстові функції у
формулах Microsoft Excel, а також функції дати і часу.
Обладнання та методичне забезпечення: персональні комп’ютери, програмне забезпечення (Microsoft Excel), роздатковий дидактичний матеріал.
Література: 1. Наливайко Н. Я. Інформатика. Навч. посібник. - К. : Центр учбової літератури, 2011. –
576 с.
2.
Інформатика. Навч.
посібник. / Ю. В. Форкун, Н. А. Длугунович.
– Львів : «Новий Світ-2000», 2012. – 464 с.
3.
Стоцкий Ю. Office 2010. Самоучитель / Ю. Стоцкий,
А. Васильев, И. Телина.
– СПб. : Питер, 2011.
– 432 с.
4.
Лебедев А. Н. Windows 7 и Office 2010. Компьютер для начинающих / А. Н. Лебедев. – СПб. Питер, 2010. – 299 с.
5.
Леонтьев В. П.
Window 7.
Office 2010. Энциклопедические справочники. – М. : Олма Медиа
групп, 2010. –
768 с.
Зміст
1.Функції часу.
2. Функції дати.
Теоретичні відомості
Щоб
показати на аркуші Excel поточну дату, можна використати функцію СЕГОДНЯ (),
щоб отримати не тільки поточну дату, але і час, слід використовувати функцію
ТДАТА (). За замовчанням для першої функції заданий формат Дата в форматі
операційної системи, але, можна задати і формат з показом дати і часу (час буде
дорівнювати 00:00). Аналогічно і для другої
функції.
Рис. 11.4. Використання функції ТЕКСТ
З
використанням формату комірок для типу дата / час можна показати навіть назву
дня тижня і місяця (рис. 11.5).
Рис. 11.5. Використання різних функцій і форматів типу
дата / час
Раніше
було також показано, як об’єднати в один рядок дані текстові та дати / часу
(рис. 11.4), у цьому випадку можна використовувати всі формати дати / часу, що
показані на рис. 11.5.
Представлення
результату обчислення різниці між двома датами також залежить від формату
комірки. Під час використання формату Общий буде показана різниця у днях між датами (рис.
11.6).
Рис. 11.6. Використання різних форматів і функції РАЗНДАТ
в обчисленні різниці двох дат
Наприклад,
потрібно порахувати стаж працівника, як кількість цілих років + місяців + днів,
це завдання можна вирішити в системі Excel з використанням недокументованої
функції РАЗНДАТ (збережена для сумісності із старими версіями) (рис. 11.6.).
У
довідці Excel немає даних по цій функції, нижче наведений опис її синтаксису:
У
системі Excel є 4 функції для визначення кількості робочих днів в інтервалі і,
навпаки, дати, що відстає вперед або назад на задану кількість робочих днів:
ЧИСТРАБДНИ, ЧИСТРАБДНИ.МЕЖД і РАБДЕНЬ, РАБДЕНЬ.МЕЖД. Для прикладу наведемо
синтаксис 2-ої функції (в описі синтаксису у квадратних дужках – необов’язкові
аргументи):
У
цієї функції необов’язковий аргумент выходной може задаватися
числом з першого стовпця таблиці 11.1 або рядком другого стовпця цієї таблиці.
Необов’язковий
аргумент праздники
– набір з однієї або декількох дат, які необхідно виключити з календаря робочих
днів. Значення аргументу має бути діапазоном комірок, що містять дати, або
константою масиву, що включає порядкові значення, які представляють дати.
Порядок дат або значень може бути будь-яким. Приклад використання функції на
рис. 11.7.
Рис. 11.7. Використання функції ЧИСТРАБДНИ.МЕЖД
Таблиця 11.1.
Значення
аргументу Выходной для функцій ЧИСТРАБДНИ.МЕЖД і РАБДЕНЬ.МЕЖД
|
Номер вихідного дня |
Вихідні дні |
|
1 або відсутній |
Субота, неділя |
|
2 |
Неділя, понеділок |
|
3 |
Понеділок, вівторок |
|
4 |
Вівторок, середа |
|
5 |
Середа, четвер |
|
6 |
Четвер, п’ятниця |
|
7 |
П’ятниця, субота |
|
11 |
Тільки неділя |
|
12 |
Тільки понеділок |
|
13 |
Тільки вівторок |
|
14 |
Тільки середа |
|
15 |
Тільки четвер |
|
16 |
Тільки п’ятниця |
|
17 |
Тільки субота |
Функції дати і часу
У табличному редакторі Microsoft Excel усі дати, починаючи з 1 січня 1900 р.,
пронумеровані послідовністю натуральних чисел: 1.01.1900 = 1, 2.01.1900 = 2,
1.02.1900 = 32, 01.01.2000 = 36526, і т.д. Тому додавання до дати числа х означає збільшення дати на х днів, а різниця дат дає кількість днів
між цими датами, наприклад: 29.08.97 + 4 = 2.09.97, а 2.01.98 –
30.12.97 = 3.
Якщо цілим числам відповідають дати, то дробам – час
доби. Оскільки година – це 1/24 доби, то: 0,5 = 12:00, 0,25 = 6:00,
2,75 = 2.01.1900 18:00. Таким чином, дата і час розглядаються в Excel
як звичайне число, тільки подане в специфічному форматі. Отже, арифметичні
операції можуть виконуватись також даними типу дати і часу, наприклад: 1:14:30
+ 2:50:30 = 4:55:00. Не слід забувати, що якщо результат цього додавання
перевести в числовий формат, то одержимо час, виражений у добах: 4:05:00 =
0,170138889 доби. Результат у годинах одержимо, перемноживши останнє число
на
24: 0,170138889*24 = 4,083333333 години.
Від’ємні значення
не можуть бути подані у форматі дати і часу.
Тип
даних дата / час найбільш складний через наявність різних форм зовнішнього
представлення дати, часу або дати і часу одночасно. Формат дат і часу залежить
від настроювань Windows (Язык и региональные стандарты). Повний список
усіх стандартних форматів дати / времени можна
переглянути у вікні Формат ячеек (рис. 11.8), крім того можна визначити власні формати
представлення даних.
Рис. 11.8. Формат функції Час та функції Дата
Щоб додати до
електронної таблиці фунції Дати і Часу необхідно
відкрити вкладку Формули у групі Библиотека функцый та вибрати кнопку Дата або Время (рис.11.9).
Функція ДАТА(рік;місяць;день) – повертає ціле число, що представляє певну дату. Якщо до
введення цієї функції формат комірки був Общий, результат буде
відформатований як дата.
Рік – аргумент, який може мати від однієї до
чотирьох цифр. Microsoft Excel інтерпретує аргумент рік відповідно до
використовуваної системи дат. За замовчуванням Excel для Windows використовує
систему дат 1900, а Excel для «Макінтоша» – систему 1904.
Для системи дат 1900:
ð
Якщо аргумент рік знаходиться в
діапазоні від 0 (нуль) до 1899 (включно), Excel додає це значення до 1900 для
обчислення року. Наприклад, ДАТА(108;1;2) повертає 2 січня 2008 (1900+108) року.
ð
Якщо аргумент рік знаходиться в
діапазоні від 1900 до 9999 (включно), Excel використовує це значення як рік.
Наприклад, ДАТА(2008;1;2) повертає 2 січня 2008 року.
ð
Якщо значення аргументу менше 0 або
більше 9999, Excel повертає значення помилки #ЧИСЛО!.
Рис. 11.9. Функції Дати і часу у Excel
ð
Якщо аргумент рік знаходиться в
діапазоні від 4 до 1899 (включно), Excel додає це значення до 1900 для
обчислення року. Наприклад, ДАТА(108;1;2) повертає 2
січня 2008 (1900+108) року.
ð
Якщо аргумент рік знаходиться в
діапазоні від 1904 до 9999 (включно), Excel використовує це значення як рік.
Наприклад, ДАТА(2008;1;2) повертає 2 січня 2008 року.
ð
Якщо значення аргументу менше 4
або більше 9999 або знаходиться в діапазоні від 1900 до 1903(включно), Excel
повертає значення помилки #ЧИСЛО!
Місяць – число, що представляє місяць року.
Якщо значення аргументу більше 12, уведене число місяців відлічується від
першого місяця вказаного року. Наприклад, ДАТА(2008;14;2) повертає число, відповідне 2 лютого 2009 року.
День – число, що представляє день місяця. Якщо
значення аргументу більше числа днів у вказаному місяці, введене число днів
відлічується від першого дня місяця. Наприклад, ДАТА(2008;1;35) повертає число, відповідне
4 лютого 2008 року.
Примітки:
ð
Microsoft Excel зберігає дати як
цілі числа і може виконувати над ними обчислення. За замовчуванням порядковий
номер 1 січня 1900 року – 1, а 1 січня 2008 – 39448, оскільки інтервал в днях
рівний 39448. Microsoft Excel для «Макінтоша» за замовчуванням використовує різні системи дат.
ð
Функція ДАТА корисна в тих
формулах, де рік, місяць і день представлені формулами, а не константами.
Функція ЧАС(час_в_числовому_форматі) – повертає час, відповідний заданому часу в
числовому форматі. Час визначається як ціле число в інтервалі від 0 до 23.
Час_в_числовому_форматі – час, для якого потрібно виділити
години. Час може бути заданий текстовим рядком в лапках (наприклад
"18:45"), десятковим числом (наприклад 0,78125 –– відповідає
18:45) або результатом інших формул або функцій (наприклад
ВРЕМЗНАЧ("18:45")).
Примітки
Microsoft Excel для Windows і Excel для «Макінтоша»
використовують різні системи дат за замовчуванням. Час є дробовою частиною
значення дати і задається десятковим числом (наприклад, 12:00 представляється
як 0,5).
ХІД виконання
РОБОТИ:
1. В аркуші 1 (під назвою Об’єднання) у комірках А1, А2, А3 наберіть текст, що містить
відповідно ваше прізвище, ім’я, по-батькові. У комірці С2 виконайте об’єднання тексту вказаних комірок А1-А3.
2. В аркуші 2 (під
назвою Ювілеї) відповідно до рис. 11.11. складіть таблицю для розрахунку
дат «1000-денних ювілеїв»:
ð у комірку Д1 введіть
дату вашого народження;
ð в одну з комірок
введіть формулу Ф1, що обчислюється як вміст комірки, розташованої над нею,
плюс 1000;
ð за допомогою
маркера заповнення скопіюйте формулу Ф1 у всі інші комірки таблиці;
ð встановіть у лівому стовпці числовий формат, а в правому – формат дати.
3. В аркуші 3 (під назвою Прокат) заповніть
розрахунок вартості прокату устаткування, де:
Æ Т1 – довільні
найменування (не менше 6 позицій), Ч1 – довільні числові значення, ДЧ1,
ДЧ2 – довільні
значення дати і часу[1].
Æ Розрахункові
формули: Ф1=(ДЧ2-ДЧ1)*24; Ф2=Ч1*Ф1;
Æ Ф3 розраховується
як округлене до цілого числа значення ДЧ2 плюс 10. Тут
передбачається, що оплата за прокат повинна надійти не пізніше 10 днів після
повернення устаткування. Округлення здійснюється для того, щоб у процесі
нарахування пені враховувати тільки дні, а не години прострочення платежу.
Æ Дату Фактичної оплати Д1 у деяких
позиціях уведіть із перевищенням терміну, розрахованого в графі Сплатити до, в інших – без.
Æ Для розрахунку Пені ви повинні застосувати у формулі Ф4 функцію
ЕСЛИ так, щоб у процесі своєчасної оплати пеня дорівнювала нулю,
а у випадку прострочення – Ф2*(Д1-Ф3)*1%.
Æ Ф5=Ф2+Ф4, а Ф6, Ф7 і Ф8 розраховуються як суми значень у відповідних стовпцях.
3.
Виконайте закріплення областей так, щоб шапка
таблиці і найменування устаткування завжди залишалися в полі зору.
4.
Робочі аркуші обох таблиць відформатуйте
і збережіть у файлі на диску.
Контрольні питання:
1. Де в Excel розміщені
текстові функції та функції дати і часу?
2. Які найбільш
вживані текстові функції Excel?
3. Які найбільш
вживані функції датиі часу в Excel ви знаєте?
4. Як в Excel
представляються дата і час?
5. Які дії можна виконувати
з даними у форматі дати і часу?
6. Як
використовуються функції у формулах?
7. Які функції
Excel ви знаєте?
8. Чи можна
від’ємні значення подати у форматі дати і часу?
9. Як можна
пронумеровані усі дати у Microsoft Excel?
10.Як можна
закріпити області в Excel?