Работа с данными в электронной таблице Microsoft Excel

Предисловие

В электронной таблице Microsoft Excel можно работать не только с числами и текстами, но и с датами. Даты можно сравнивать между собой, складывать и вычитать, а также использовать в других вычислениях. Например, можно вычислить число дней между двумя датами, определить, какой день недели приходился на ту или иную дату, и т.п. Эта статья познакомит вас с соответствующими возможностями популярной офисной программы.

Ввод значений даты

Для того чтобы ввести в ячейку дату, следует указать номер дня, номер месяца и две последние цифры года через точку (12.12.87), дефис (12-12-87) или символ “/” (12/12/87). Можно вводить также первые три буквы названия месяца (12-дек-87
и т.п.; для даты в мае месяце необходимо написать слово май). Текущий год можно не указывать — он будет добавлен к введенной дате автоматически. При вводе значений даты происходит их автоматическое распознавание, и общий формат ячейки ¹ заменяется на встроенный формат даты. Так, если ввести, например, значение 12-12-87 или 12 дек 87, то в ячейке отобразится 12.12.87 ², а в строке формул для данной ячейки будет выведено: 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 вводятся в отдельные ячейки.

Решения, пожалуйста, присылайте в редакцию (можно решать не все задачи). Читателям, не имеющим доступа в Интернет, предлагаем в письме указать в соответствующих ячейках необходимые формулы. Фамилии всех приславших ответы будут опубликованы, а лучшие ответы мы поощрим.

¹ Если в ячейке не установлен какой-либо специальный формат (числовой, процентный, финансовый, формат дат и т.д.), то данные в ней выводятся в так называемом “общем” формате, используемом для отображения как текстовых, так и числовых значений различного типа.

² Если такой формат установлен в операционной системе Windows. Изменение установок осуществляется с помощью Панели управления (кнопка Пуск, пункт Настройка), пиктограмма Язык и стандарты, вкладка Дата, поле Краткий формат даты.