Excel. Лабораторная работа 3 Даты и время
Цель работы
Научиться использовать формулы и форматы для работы с датами и временем.
Задание
Ознакомьтесь с теоретическими сведениями, приведенными ниже.
Создайте новую книгу Excel и сохраните ее под именем вида «Excel. Л.р. 3 группа ФИО».
Все задания выполняется на одном листе Excel. В начале листа в качестве заголовка указывать номер и название работы, сведения о том, кто выполнил работу (ФИО, группа).
Нумеровать задания необязательно, но нужно подписывать содержимое ячеек. Ячейки с формулами закрасьте зеленым цветом, например:
Задание 1. Определите сегодняшнюю дату и текущее время. Вычислите:
- какая дата будет через 45 дней?
- сколько времени будет через 2 часа?
- сколько времени будет через 95 минут?
- какое число будет через 2 месяца?
- сколько лет осталось до 2100 г.?
- какое число будет через 14 рабочих дней?
- сколько дней прошло с начала календарного года?
- сколько из них было рабочих дней и выходных?
- сколько недель прошло с начала семестра?
Чтобы выполнить вычисления с рабочими днями, на отдельном листе выпишите все праздничные даты за этот год, и подставьте их в формулы.
Задание 2. Введите в отдельную ячейку дату рождения (свою или любую другую). Определите, какой это был день недели. Вычислите:
- сколько вам лет;
- сколько всего вы прожили дней;
- сколько всего вы прожили часов;
- сколько всего вы прожили минут.
Задание 3. С помощью автозаполнения и формул составьте таблицу, содержащую следующие столбцы:
1) номер месяца;
2) название месяца;
3) дата начала месяца в текущем году;
4) дата конца месяца в текущем году;
5) сколько в месяце было рабочих дней.
Теоретическая часть
Представление даты и времени
Дата и время представляются в компьютере одним числом – сколько дней прошло с 1 января 1900 г.
Примеры:
0,0 = 00:00:00
0,5 = 12:00:00
0,8 = 19:12:00
1,0 = 01.01.1900 г. 00:00:00 (или просто 01.01.1900)
1,6 = 01.01.1900 г. 14:24:00
32 = 01.02.1900 г.
366 = 31.12.1900г.
Соответственно, час = 1/24 ≈ 0,04167, минута 1/24/60 ≈ 0,000694, год = 365 (если не високосный) и т. д. Поскольку компьютер хранит дробные числа со многими знаками после запятой, то время сохраняется с точностью не до секунд, а до миллисекунд, и даже мельче.
Форматы даты и времени
В зависимости от формата ячейки дату и время можно отобразить по-разному (например, 01.09.2016; 1 сентября 2016; сен.2016; 2016/09/01 и др.).
Стандартные форматы представления даты и времени можно найти в списке на ленте на вкладке «Главная». Но это не полный список. Другие варианты отображения данных в ячейке можно выбрать через пункт «Другие числовые форматы…» или через правый щелчок – пункт меню «Формат ячеек…» – вкладка «Число».
В разделах «Дата» и «Время» можно выбрать один из списка наиболее распространенных форматов или задать свой в пункте «(все форматы)».
При этом используются следующие обозначения:
| Обозначение | Пояснение | Примеры |
| Д | День месяца одной или двумя цифрами | 1, 6, 12, 31 |
| ДД | День месяца всегда двумя цифрами | 01, 06, 12, 31 |
| ДДД | День недели (кратко) | пн, вт, ср, чт, пт, сб, вс |
| ДДДД | День недели (полностью) | понедельник, вторник |
| М | Номер месяца одной или двумя цифрами | 1, 2, 3, 4, 10, 11, 12 |
| ММ | Номер месяца всегда двумя цифрами | 01, 02, 03, 11, 12 |
| МММ | Краткое название месяца | янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек |
| ММММ | Полное название месяца | январь, февраль |
| ГГ | Год двумя цифрами | 98, 01, 16 |
| ГГГГ | Год четырьмя цифрами | 1998, 2001, 2016 |
| ч | Час одной или двумя цифрами в 24-часовом формате | 1, 2, 20, 23 |
| чч | Час всегда двумя цифрами в 24-часовом формате | 01, 02, 20, 23 |
| м | Минуты одной или двумя цифрами | 1, 5, 15, 40, 59 |
| мм | Минуты всегда двумя цифрами | 01, 05, 15, 40, 59 |
| с | Секунды одной или двумя цифрами | 1, 5, 15, 40, 59 |
| сс | Секунды всегда двумя цифрами | 01, 05, 15, 40, 59 |
| AM/PM | Часы будут выводиться в 12-часовом формате, утро обозначается AM, вечер PM | 10:00 PM 08:16 AM |
Символы точка (.), слеш (/), дефис (-), запятая (,), круглые скобки, пробелы, а также текст в кавычках отображаются «как есть».
Например:
Д МММ ГГГГ “г.” 1 сен 2016 г.
ДД/ММ/ГГ (ДДД) 01/09/16 (Чт)
ДДДД, ДД МММ четверг, 01 сен
ММММ ГГГГ сентябрь 2016
ММ-ГГ 09-16
Д.М 1.9
ДД.ММ.ГГ ч:мм 01.09.16 8:30
чч:мм:сс/ДДД 08:30:00/Чт
мм:сс 30:00
Таким образом, чтобы узнать день недели, как это требуется в задании, достаточно отобразить дату в формате «ДДДД».
Распространенная ошибка! Если сбить формат отображения даты, то Excel покажет ее «как есть» – числом дней. Например, 01.09.2015 = 42 248. Ровно столько дней прошло с 01.01.1900. В этом случае необходимо вернуть правильный формат ячеек.
Вычисления с датами и временем
Поскольку даты являются числами, с ними можно выполнять все арифметические операции – складывать, вычитать, умножать, делить и даже извлекать корень, хотя смысла в последнем действии нет.
Наиболее часто используемые действия:
– дата + число = какая дата будет через «число» дней
– дата1 – дата2 = сколько дней прошло между двумя датами
– время * 24 = сколько всего часов (сутки в часы)
– время * 24 * 60 = сколько всего минут (сутки в минуты)
– время * 24 * 60 * 60 = сколько всего секунд (сутки в секунды)
Например, формула
вычисляет, какое число будет через 50 дней после 1 сентября 2016 г.
Обратите внимание, поскольку дата 01.09.2016 не записана в отдельную ячейку, а набрана прямо в формуле, ее надо брать в кавычки.
Вычисление, сколько дней осталось до Нового Года, т.е. сколько дней между сегодняшней датой и 31 декабря:
Кроме того, Excel содержит ряд функций специально для работы с датами и временем. Их все можно найти на вкладке «Формулы» – группа «Библиотека функций» – кнопка меню «Дата и время».
Для каждой функции в справке по Office есть подробное описание с примерами. Основные функции записаны в таблице ниже.
| Функция | Пояснение | Пример | |
| ДЕНЬ(дата) | Номер дня месяца от 1 до 31 | =ДЕНЬ(“12.10.2016”) | 12 |
| ДЕНЬНЕД(дата; [тип]) | Номер дня недели: если тип = 1, то от 1 (вс) до 7 (сб) если тип = 2, то от 1 (пн) до 7 (вс) если тип = 3, то от 0 (пн) до 6 (вс) Если тип не указан, то он считается равным 1 | =ДЕНЬНЕД(“13.10.2016”) | 3 (вт) |
| =ДЕНЬНЕД(“13.10.2016”;2) | 2 (вт) | ||
| =ДЕНЬНЕД(“12.10.2016”;3) | 0 (пн) | ||
| МЕСЯЦ(дата) | Номер месяца от 1 до 12 | =МЕСЯЦ(“12.10.2016”) | 10 |
| ГОД(дата) | Номер года от 1900 до 9999 | =ГОД(“12.10.2016”) | 2016 |
| ЧАС(дата_время) | Извлекает час из даты и времени | =ЧАС(“14:15:07”) | 14 |
| МИНУТА(дата_время) | Аналогично минуты | =МИНУТА(“14:15:07”) | 15 |
| СЕКУНДА(дата_время) | Аналогично секунды | =СЕКУНДА(“14:15:07”) | 7 |
| ТДАТА() | Текущая дата и время (по системным настройкам) | =ТДАТА() | 17.10.2018 23:12 |
| СЕГОДНЯ() | Текущая дата без времени (время 00:00:00) | =СЕГОДНЯ() | 17.10.2018 |
| ДАТАМЕС(нач_дата; число_месяцев) | Какая будет дата через «число_месяцев» от «нач_дата» | =ДАТАМЕС("12.10.2016";1) | 12.11.2016 |
| =ДАТАМЕС("12.10.2016";6) | 12.04.2017 | ||
| КОНМЕСЯЦА(нач_дата; число_месяцев) | Дата конца месяца спустя «число_месяцев» от «нач_дата» (0 – конец того же месяца | =КОНМЕСЯЦА("12.10.2016";0) | 31.10.2016 |
| =КОНМЕСЯЦА("12.10.2016";2) | 31.12.2016 | ||
| =КОНМЕСЯЦА("12.10.2016";12) | 31.10.2016 | ||
| =КОНМЕСЯЦА("12.10.2016";-1) | 30.09.2016 | ||
| ДАТА(год;месяц;день) | Возвращает полную дату по номерам года, месяца и дня | =ДАТА(2016;10;11) | 11.10.2016 |
| РАБДЕНЬ(нач_дата; число_дней; праздники) | Вычисляет, какое будет число через указанное количество рабочих дней. Если необходимо, можно указать даты праздничных дней, либо этот аргумент пропускается. | =РАБДЕНЬ("17.04.2017"; 7) | 26.04.2017 |
| ЧИСТРАБДНИ(нач_дата; кон_дата; праздники) | Вычисляет, сколько рабочих дней между указанными датами (включая их). Если необходимо, можно указать даты праздничных дней, либо этот аргумент пропускается. | = ЧИСТРАБДНИ ("01.04.2017"; "20.04.2017") | 14 |
Функции можно комбинировать друг с другом.
Например, чтобы получить дату последнего дня (31 декабря) текущего года можно использовать такую формулу:
Функция СЕГОДНЯ дает нам сегодняшнюю дату, функция ГОД берет из нее только год (отбрасывает день и месяц), а функция ДАТА ставит для этого года 12 месяц и 31 число.
Контрольные вопросы
1. Как представляются даты в компьютере?
2. Как вычислить, какое число будет завтра?
3. Что означает запись «мм» в формате даты?
4. Как записать следующий формат даты: «Понедельник: 17 апр.»?
5. Как указать праздничные дни в формулах для вычислений с рабочими днями?
