В мир информатики # 101 (1–15 января).
Microsoft Excel углубленно
Работа с датами в электронной таблице Microsoft Excel
Предисловие
В электронной таблице Microsoft Excel можно
работать не только с числами и текстами, но и с
датами. Даты можно сравнивать между собой,
складывать и вычитать, а также использовать в
других вычислениях. Например, можно вычислить
число дней между двумя датами, определить, какой
день недели приходился на ту или иную дату, и т.п.
Эта статья познакомит вас с соответствующими
возможностями популярной офисной программы.
Ввод значений даты
Для того чтобы ввести в ячейку дату,
следует указать номер дня, номер месяца и две
последние цифры года через точку (12.12.87), дефис
(12-12-87) или символ “/” (12/12/87). Можно вводить также
первые три буквы названия месяца (12-дек-87
и т.п.; для даты в мае месяце необходимо написать
слово май). Текущий год можно не указывать —
он будет добавлен к введенной дате
автоматически. При вводе значений даты
происходит их автоматическое распознавание, и
общий формат ячейки1 заменяется на
встроенный формат даты. Так, если ввести,
например, значение 12-12-87 или 12 дек 87, то в
ячейке отобразится 12.12.872, а в строке формул
для данной ячейки будет выведено: 12.12.1987. Но если в
ячейке указать 22.10.28, то в строке формул вместо
ожидаемой даты 22.10.1928 вы увидите другую — 22.10.2028.
Дело в том, что, если при вводе даты указаны
только две последние цифры года, Microsoft Excel добавит
первые две цифры по следующим правилам:
— если введенное число лежит в
интервале от 00 до 29, то оно интерпретируется как
год с 2000-го по 2029-й;
— если введенное число лежит в
интервале от 30 до 99, то оно интерпретируется как
год с 1930-го по 1999-й.
Таким образом фирма Microsoft в свое время
позаботилась о переходе в третье тысячелетие.
Поэтому года с 1900-го по 1929-й следует указывать
полностью.
По умолчанию значения даты
выравниваются в ячейке по правому краю. Если не
происходит автоматического распознавания
формата даты, то введенные значения
интерпретируются как текст, который
выравнивается в ячейке по левому краю.
Представление дат в ячейках
Формат представления даты в ячейке,
отображаемый после ввода значения, может быть
изменен с помощью меню, пункт Формат,
подпункт Ячейки, вкладка Число, раздел
Числовые форматы, пункт Дата. Так, вместо
значения 05.12.87 можно получить 5.12.87; 5 дек 87; 5
Декабрь, 1987 и другие представления одной и той же
даты. При этом значение даты, отображаемое в
строке формул, не меняется (оно не зависит от
формата ее представления в ячейке).
Действия с датами
Как уже отмечалось, даты можно
складывать и вычитать, сравнивать между собой.
Можно также умножать и делить их на числа! Для
того чтобы понять, как это реализуется,
необходимо разобраться, как хранятся даты в
компьютере. Введем в ячейку A1 дату “1 января 1900
года” (напомним, что для этого следует ввести
1-1-1900, а не 1-1-00). С помощью маркера заполнения
распространим (скопируем) введенное значение на
ячейки A2:A10 (в них появятся даты, в которых будут
значения, соответствующие 2, 3, …, 10 января 1900
года). Скопируем блок ячеек A1:A10 в ячейки B1:B10.
Изменим формат представления данных в блоке B1:B10
на Общий (Формат | Ячейки | Число | Общий).
Мы увидим, что в этом блоке появятся значения 1, 2,
…, 10. Из этого можно сделать важный вывод: дата в
Excel — это количество дней, прошедших от 1 января
1900 года. Такая форма внутреннего представления
дат и позволяет выполнять над ними различные
арифметические операции и операции сравнения.
Основные функции для работы с датами
В Excel имеется ряд функций для работы с
датами. Рассмотрим некоторые из них.
I. Функции ДЕНЬ, МЕСЯЦ и ГОД
Эти функции возвращают соответственно
номер дня в месяце, номер месяца в году и год для
некоторой даты.
Их синтаксис: ДЕНЬ(дата), МЕСЯЦ(дата) и
ГОД(дата), — где аргумент дата — адрес ячейки,
содержащей дату, либо дата, заданная в общем или
числовом формате (12345), либо как текст (например,
“15-4-93” или “15-Апр-1993”).
День возвращается как целое число в
диапазоне от 1 до 31. Месяц определяется как целое
в интервале от 1 (Январь) до 12 (Декабрь). Значение
года возвращается как целое число в интервале
1900–9999.
Примеры
1. Если в ячейке А2 указана дата 26.10.49, то
ДЕНЬ(А2) равняется 26, МЕСЯЦ(А2) равняется 10, ГОД(А2)
равняется 1949.
2. ДЕНЬ("4-Янв") равняется 4,
МЕСЯЦ("4-Янв") равняется 1.
3. ДЕНЬ("15-Апр-1993") равняется 15,
МЕСЯЦ("15-Апр-1993") равняется 4,
ГОД("15-Апр-1993") равняется 1993.
4. ДЕНЬ("11.8.93") равняется 11,
МЕСЯЦ("11.8.93") равняется 8, ГОД("11.8.93")
равняется 1993.
II. Функция ДЕНЬНЕД
Функция возвращает номер дня недели,
соответствующий некоторой дате. Ее синтаксис:
ДЕНЬНЕД(дата; тип),
— где дата — аргумент, аналогичный
используемому в описанных выше функциях;
тип — число, которое определяет
вариант возвращаемых значений:
Примеры
1. Если в ячейке А2 указана дата 26.10.49, то
ДЕНЬНЕД(А2) равняется 3 (Среда).
2. ДЕНЬНЕД("15.2.90") равняется 5
(Четверг).
3. ДЕНЬНЕД("15.2.90"; 2) равняется 4
(Четверг).
III. Функция СЕГОДНЯ
Функция возвращает дату текущего дня,
отслеживаемую компьютером.
Ее синтаксис: СЕГОДНЯ () — без
аргументов, но с обязательными скобками.
IV. Функция ДАТА
Функция позволяет “собрать” дату из
значений года, номера месяца и номера дня. Ее
синтаксис: ДАТА(год; месяц; день), где:
— год — это число от 1900 до 2078;
— месяц — это число,
представляющее номер месяца в году;
— день — это число, представляющее
номер дня в месяце.
Например, ДАТА(45; 5; 9) есть 9 мая 1945 года.
Задачи для самостоятельной работы
1. В ячейках В2 и В3 получить число 37 135
(указанное число ни в одну из ячеек не вводить):
2. В ячейках А2 и А3 получить число 18 197
(указанное число ни в одну из ячеек не вводить):
3. В ячейке В2 будет записана некоторая
дата. Получить в ячейках В3:В5 соответственно
номер дня в месяце, номер месяца и год этой даты:
4. Оформите лист для определения года,
номера месяца и номера дня рождения:
Искомые значения должны быть получены
в ячейках В3:В5.
5. Сотрудники отдела кадров обычно
подсчитывают стаж работы на предприятии
следующим образом. Выписывается текущая дата в
виде 2007, 27 сентября, а под ней — дата начала
работы работника на этом предприятии в
аналогичном виде. Затем попарно вычитаются
значения года, номера месяцев и номера дней в
месяце. Например, если работник начал работать на
предприятии 19 мая 2003 года, то его стаж работы
составляет 4 года 8 месяцев и 6 дней. Оформите лист
для расчета стажа работы по описанной методике с
использованием данных типа Дата. Принять, что
номер месяца и номер текущего дня больше
соответствующих значений момента поступления на
работу.
6. По дате, указанной в ячейке,
определить номер дня недели, на который
приходилась эта дата (понедельник — 1, вторник —
2, …, воскресенье — 7).
7. Научный сотрудник забыл точную дату
конференции, на которой ему необходимо
присутствовать, но помнит, что она должна
начаться в четверг в период с 1 по 8 февраля 2008
года. Помогите ему определить точную дату начала
конференции.
8. В ячейке В5 будет записана некоторая
дата.
В ячейке В3 получить дату дня, который будет через
100 дней после указанной даты:
9. В ячейке В2 будет записана некоторая
дата.
В ячейке В3 получить дату дня, который был за 200
дней до указанной даты.
10. В ячейке В2 получить дату текущего
дня, в ячейке В4 — номер дня недели (понедельник —
1, вторник — 2, …, воскресенье — 7), который будет
через некоторое число дней после текущего дня
(это число будет указано в ячейке В3):
11. В ячейке В2 получить дату текущего
дня, в ячейке В4 — номер дня недели (понедельник —
1, вторник — 2, …, воскресенье — 7), который был за
некоторое число дней до текущего дня (это число
будет указано в ячейке В3):
12. В ячейке В2 и В3 будут указаны даты
двух событий. Определить, сколько дней прошло
между этими событиями.
13. Определите свой возраст в днях и
неделях.
14. Для текущей даты вычислить:
а) порядковый номер дня с начала года;
б) сколько дней осталось до конца года.
15. Определить, сколько дней длится
первое полугодие года и сколько — второе.
16. В ячейке В2 указана дата некоторого
события, произошедшего в первой половине ХХ века.
Необходимо в ячейке В3 получить дату дня, до
которого от 1 января 1900 года прошло в 2 раза больше
дней, чем от 1 января 1900 года до дня данного
события.
17. В ячейке В2 запишите дату вашего
рождения, а в ячейке В3 получите дату текущего
дня. Определить дату того дня, когда число дней
вашей жизни станет в 2 раза больше, чем число
прожитых дней до текущего дня. Дату получить в
формате вида “12 Апрель, 2017”.
18. Известна дата рождения Пети.
Определить дату рождения Коли, если известно, что
число дней, прожитых им до текущего дня, в 2 раза
меньше, чем число дней, прожитых Петей.
19. В ячейке В2 запишите дату вашего
рождения, а в ячейке В3 получите дату текущего
дня. Определить номера дней недели (понедельник
— 1, вторник — 2, …, воскресенье — 7), которые
будут, когда число дней вашей жизни станет в 2, 3, 4
и 5 раз больше, чем число прожитых дней до
текущего дня.
20. После того, как в ячейки В2 и В3 будут
введены даты двух событий, определить, какое
событие произошло раньше.
21. Производственное совещание
проходит по вторникам и пятницам. Составьте их
расписание на февраль 2008 года в виде:
— где в строке 2 в ячейках,
соответствующих вторникам и пятницам, должен
быть указан какой-нибудь символ (“С”, “+” или
т.п.).
22. На листе представлены сведения о
дате рождения учеников класса:
В диапазоне ячеек С3:С27 поставить знак
“+” для тех учеников, дата рождения которых:
а) приходится на среду;
б) приходится на 10-е число месяца;
в) приходится на август.
23. В ячейке В2 указана дата
некоторого события. В ячейке В3 получить дату
дня, который будет через 3 года после этого
события.
24. В ячейке В2 указана дата
некоторого события. В ячейке В3 получить дату
дня, который был за 5 месяцев до этого события.
25. В ячейке В2 указана дата
некоторого события. В ячейке В3 получить дату
дня, который будет через n лет, m месяцев и k
дней после этого события. Значения n, m и k
вводятся в отдельные ячейки.
Решения, пожалуйста, присылайте в редакцию
(можно решать не все задачи). Читателям, не
имеющим доступа в Интернет, предлагаем в письме
указать в соответствующих ячейках необходимые
формулы. Фамилии всех приславших ответы будут
опубликованы, а лучшие ответы мы поощрим.
1 Если в ячейке не установлен
какой-либо специальный формат (числовой,
процентный, финансовый, формат дат и т.д.), то
данные в ней выводятся в так называемом “общем”
формате, используемом для отображения как
текстовых, так и числовых значений различного
типа.
2 Если такой формат установлен в
операционной системе Windows. Изменение установок
осуществляется с помощью Панели управления
(кнопка Пуск, пункт Настройка), пиктограмма Язык и
стандарты, вкладка Дата, поле Краткий формат
даты. |