Пример 6. 4.

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

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 (протяните маркер заполнения). Для каждой из ячеек введите пользовательский формат. Например, для 81 выберите в меню " Формат/ Ячейки/ Число/ (все форма­ты)" и в поле ввода " Тип: " введите ДДЦ. Нажмите " ОК". Ото­бразится сокращенное название дня недели. Далее сделайте для С1 формат ДДДД, для D1 формат МММ, для Е1 формат мммм.

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

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

8. Благодаря тому, что даты представляют собой порядковые числа, с ними можно производить вычисления. Можно вычис­лить, сколько дней прошло между двумя датами, например =" 24/01/99" - " 19/12/97". Можно прибавлять к дате или вычи­тать из нее определенное количество дней.

9. Остановимся на проблеме отображения дат, находящихся на рубеже тысячелетий, в Excel 2000. Если пользователь ввел состав даты значение года, состоящее из двух цифр (или такая
дата получена Excel из внешней базы данных), то она интерпре­тируется так: двухзначные значения года от 00 до 29 интерпре­тируются как годы с 2000 по 2029; двухзначные значения года от 30 до 99 интерпретируются как годы с 1930 по 1999. Напри­мер, дата 02/03/29 будет воспринята как 02/03/2029, а дата 02/03/31 —как02/03/1931. Проверьте! >

ЗАДАЧА 6.8. Вычислите свой возраст в днях и неделях.

ЗАДАЧА 6.9. Производственное совещание проходит по вторникам и пятницам. Составьте их расписание на второй квартал 2001 г. в виде таблицы Дата-День недели. Превратите формулы в первом столбце таблицы в значения и замените вручную 1 мая на 30 апреля.

ЗАДАЧА 6.10. В ячейке А7 дата помещена в форме 981022 (т.е. 22 октября 1998 г.). Преобразовать ее в формат Excel двумя способами: с помощью текстовых функций и с помощью пункта меню " Данные/ Текст по столбцам" (дата останется в одном столбце, на втором шаге Мастера текстов укажите, что она в формате ГМД).

ЗАДАЧА 6.11. (только для Excel 97/2000 — условное форма­тирование). Введите в столбец А даты от 1 марта до 30 апреля 2000 г. Наложите на диапазон условный формат, чтобы даты, на которые приходятся суббота и воскресенье, отображались по­лужирным красным шрифтом.

Имеется функция для того, чтобы " собрать" дату из года, ме­сяца и дня: ДАТА(год, месяц, день). Прочитайте Справку об этой функции. Любопытно, что аргументами этой функции мо­гут быть числа, не входящие в допустимый диапазон. Например, формула =ДАТА(99, -2, 42) вернет дату11/11/98(проанализируйте, почему).

ЗАДАЧА 6.12. Выяснить, в какие месяцы заданного года тринадцатое число приходится на пятницу. Для этого спроектировать таблицу следующего вида (рис. 6.3).

Пользователь вводит год (остальная часть таблицы защите
от изменений). Если Вы работаете в Excel 97/2000, то выделяйте
ячейки с пятницей цветом (пункт меню" Формат/Условное
форматирование").

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

Упражнение 6, 3. Получение радов дат. Мы неоднократно использовали команду меню " Правка/ Заполнить/ Прогрессия Эту команду можно применять для получения рядов дат.

1. Получите даты с шагом два месяца для начальной даты 31/12/1999 и конечной даты 31/12/2000.

2. Получите рабочие дни для июля 2000 г.
ПРИМЕР 6.5 Автозаполненне и списки. Номера кварталов, названия дней и месяцев приходится вводить в рабочие листы довольно часто. В Excel эта утомительная процедура автоматизирована.

1. На новом рабочем листе поместите в ячейку А1 строку 1 кв.". Протащите маркер заполнения этой ячейки до Н1. Ячейки будут заполнены номерами кварталов.

J1 кв. |2 кв. |3кв. U кв. |1 кв. |2кв. |3кв. ukb. I Ц

¹ —•—--л- -..-.— " ".• '"..I------------------------ it.•.[-—.Л" ' -I, " 1T-I- L.rf т Г -------------------------------- 1.....-.•.у--.---.1..-„-||1ИЦ-.|_|_'- лг,.-..'..._! -.; ijt-i.., i,..-..-.'.'У*.-• '• I -..-* Ш^^^!

2. В ячейку A3 введите название месяца " январь". Протащите маркер заполнения этой ячейки до А18. Блок АЗ: А18 будет заполнен названиями месяцев.* За " декабрем" последует " января ". Откуда Excel взял эту последовательность? Откройте вкладку " Сервис/ Параметры/ Списки" и Вы увидите там четыре списка: полные и сокращенные названия месяцев и дней неделя.

Испытайте их: введите в ячейку ВЗ " ноя", в СЗ " вт", в D3 " четверг", выделите блок B3: D3 и протащите маркер заполнения блока вниз. Проверьте, что элементы списков можно начинать с прописной буквы, что можно целиком вводить их прописными буквами. >

В главе 9 мы научимся создавать пользовательский список.