Расчеты с датой и временем

Даты.

Пример 1.

Элементарные операции с датами.

• 1. Для того чтобы понять, как хранятся даты в ячейках Excel, введем в ячейку А1 дату 1 января 1900 года. В Excel 5.0/7.0 нужно ввести 1/1/00, в Excel 97/2000 нужно ввести 1/1/1900 (дата 1/1/00 будет воспринята как 1/1/2000 — фирма Microsoft позаботилась о грядущем переходе в новое тысячелетие). Далее расположим в А2: А10 даты 2/1/1900, 3/1/1900 … (для этого воспользуйтесь пунктом меню «Правка/Заполнить/Прогрессия»), Скопируем блок А1: А10 в В1: В10. А теперь наложим на блок В1: В10 формат «Основной». Это можно сделать так: Ctrl+Shift+~ либо выбрать в меню «Формат/Ячейки/Число/Основной». В В1: В10 хранится арифметическая прогрессия 1,2, …, 10. Итак, дата в Excel — количество дней, прошедших от 1 января 1900 г. Удалим блок А1: В10 (выделим его и нажмем клавишу Del).
• 2. Введем в ячейку А1 формулу =СЕГОДНЯ (). (Найдем эту функцию в Мастере функций в категории «Дата и время»). Отображается сегодняшнее число. Наложим на ячейку А1 формат «Общий». Мы увидим количество дней, прошедших от начала XX века до сегодняшнего дня. Вернем ячейке прежний формат.
• 3. Наложим на А1 по очереди различные форматы даты, доступные в списке «Формат/Ячейки/Число/Дата и время» .
• 4. Испытаем некоторые функции работы с датами. Введите в А2 формулу =ГОД (А1), в A3 формулу =МЕСЯЦ (А1), в А4 формулу =ДЕНЬ (А1). Однако, к своему удивлению, мы увидим какие-то странные даты. Например, если текущий год 1998, то в А2 отобразится 20/06/05. Дело в том, что, когда мы удалили содержимое ячеек А1: А10, остался автоматически наложенный на эти ячейки формат даты. Исправим положение. Выделите А2: А4 и наложите на них формат «Основной». Блок надо было очищать иначе: нужно было после выделения блока выбрать в меню пункт «Правка/Очистить/Все». Тогда в ячейках исчезнет и наложенный на них формат.
• 5. Иногда требуется получить порядковый номер дня недели. Для этого в ячейку А5 введите формулу =ДЕНЬНЕД (А1,2) (выясним в Справке назначение второго аргумента этой функции).
• 6. А как получить название дня недели или название месяца? Скопируйте А1 в В1: Е1 (протянем маркер заполнения). Для каждой из ячеек введите пользовательский формат. Например, для В1 выберите в меню «Формат/Ячейки/Число/(все форматы)» и в поле ввода «Тип:» введите ДДД. Нажмите «ОК». Отобразится сокращенное название дня недели. Далее сделайте для С1 формат ДДДД, для D1 формат МММ, для Е1 формат ММММ.

Такой способ не всегда удобен. Иногда хотелось бы получить в ячейке текстовую строку с названием дня или месяца. Для этого используется функция ТЕКСТ (значение, форматная строка). Получим в блоке В2: В5 те же результаты, что и в В1: Е1. Введем в ячейку В2 формулу =ТЕКСТ (А1," ДДД"). В ячейке будет отображено сокращенное название дня недели, причем выровненное по левому краю. Теперь продемонстрируем метод копирования формул, когда по каким-либо причинам мы не хотим использовать абсолютную адресацию. Превратим формулу в ячейке В2 в текстовую строку: для этого нажмем функциональную клавишу F2 и предварим формулу одиночной кавычкой: `=ТЕКСТ (А1," ДДД"). Скопируем эту строку в блок ВЗ: В5 (протяните маркер заполнения). Исправьте в формулах форматные строки на «ДДДД», «МММ», «ММММ». Заодно удаляйте кавычку перед знаком равенства — текст превратится в формулу.

7. Две последние цифры года можно получить по формуле =ТЕКСТ (А1," ГГ"). Но при этом получится текстовая строка. Чтобы получить число, нужно набрать формулу =ЗНАЧЕН (ТЕКСТ (А1," ГГ")). Проверьте.

Пример 2. Получение рядов дат. Мы неоднократно использовали команду меню «Правка/Заполнить/Прогрессия». Эту команду можно применять для получения рядов дат.

Дополнительные функции для работы с датами

Некоторые полезные функции доступны, если установить надстройку «Пакет анализа», а затем подключить эту надстройку с помощью команды меню «Сервис/Надстройки» .

Наиболее полезной из новых функций является функция КОНМЕСЯЦА (начальная_дата, число_месяцев).

Число_месяцев — это число месяцев до или после начальной_даты. Положительное значение аргумента число_месяцев означает будущую дату; отрицательное значение означает прошедшую дату. Функция возвращает последнюю дату месяца, который отстоит на заданное число месяцев от текущей даты.

Пример 3.

Допустим, в А4 записана дата 3 апреля 2000 г. Тогда функция КОНМЕСЯЦА (А4, -2) вернет число 36 585. Наложим на него формат даты: Ctrl+1/Число/Дата/, выберем образец 16/04/97 (в Excel 2000 образец другой: 14/03/99), получим 29/02/00.

Функция ЧИСТРАБДНИ (начальная дата, конечная дата, праздники) возвращает количество рабочих дней между начальной и конечной датами включительно. Рабочими считаются все дни, за исключением выходных и праздничных дней.

Праздники — это необязательный аргумент, являющийся множеством из одной или нескольких дат в числовом формате, которые должны быть исключены из числа рабочих дней, например государственные праздники.

Проще всего этой функцией пользоваться, если ее аргументы — имена ячеек, последний аргумент — имя блока. Если же надо в качестве аргумента ввести дату как строковую константу, то преобразуйте ее функцией ДАТАЗНАЧ (например, ДАТАЗНАЧ («23/06/99») — эта функция вернет порядковый номер даты).

Время.

Пример 4.

• 1. Время вводится в ячейку в формате чч: мм:сс. Например, 15.56:48, т. е. 15 ч. 56 мин. 48 с. Секунды можно и не вводить (15:24). Можно не вводить и минуты, но двоеточие должно присутствовать обязательно (18:).
• 2. Теперь изучим внутренний формат представления времени. Введем в ячейку 12, т. е. 12 ч. Отобразится 12:00. Наложим общий формат (клавиши Ctrl+Shift+~). В ячейке — число 0.5, т. е. половина суток. Введите 9, 18: и наложите общий формат. Ясно, что внутреннее представление времени — это доля суток.

В ячейку можно ввести дату и время. Например, введите 12/02 15:. В ячейке отобразится 12/02/99 15:00 (если текущий год 1999). Наложите общий формат. Вы получите число 36 203.625, т. е. количество дней от 01/01/1900 и доля суток.

Пример 5.

Сколько часов прошло с 6 ч. 14 апреля до 15 ч. 16 апреля? Введем в ячейку D1 дату и время 14/4 6, а в ячейку D2 — 16/4 15:. В D3 поместим формулу = D2 — D1 и наложим формат [ч].

Ответ: 57 ч. Можно было выбрать в списке форматов [ч]: м: с. Ответ был бы выведен в форме 57:00:00. Аналогичный прием (заключение форматного символа в квадратные скобки) подходит для минут и секунд между двумя моментами времени.

Использование встроенных функций

Программу Excel можно настраивать в соответствии с индивидуальными запросами очень широкого круга пользователей. Каждый пользователь программы Excel, определив круг наиболее часто используемых функций, может организовать работу с ними наиболее удобным для себя образом. С другой стороны, те функции программы, которые никогда не используются, можно вообще убрать из конфигурации, чтобы сэкономить ресурсы компьютера и повысить эффективность обработки.