РАБОТА С ДАННЫМИ ТИПА ДАТА В ЭЛЕКТРОННОЙ ТАБЛИЦЕ MICROSOFT EXCEL (материал для учителя) Текст научной статьи по специальности «Экономика и бизнес»

Златополъский Дмитрий Михайлович

РАБОТА С ДАННЫМИ ТИПА ДАТА В ЭЛЕКТРОННОЙ ТАБЛИЦЕ MICROSOFT EXCEL

(материал для учителя)

ПРЕДИСЛОВИЕ

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

ВВОД ЗНАЧЕНИЙ ДАТЫ

Для того чтобы ввести в ячейку дату, следует указать номер дня, номер месяца и две последние цифры года через точку (12.12.87), дефис (12-12-87) или символ «/»(12/12/87).

...какой fefru HefeMi afcu%afUMC& Ha t&y Ими ttHtyO fiUfotf: ..

Можно вводить также первые три буквы названия месяца (12-дек-87 и т. п.; для даты в мае месяце необходимо написать слово май). Текущий год можно не указывать -он будет добавлен к введенной дате автоматически1. При вводе значений даты происходит их автоматическое распознавание, и общий формат ячейки2 заменяется на встроенный формат даты. Так, если ввести, например, значение 12-12-87 или 12 дек 87, то в ячейке отобразится 12.12.873, а в строке формул для данной ячейки будет выведено: 12.12.1987. Но если

.Hatucaû-b смо&о мал:.

1 Естественно, если дата на компьютере установлена правильно.

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

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

в ячейке указать 22.10.28, то в строке формул, вместо ожидаемой даты 22.10.1928, вы увидите другую - 22.10.2001. Дело в том, что, если при вводе даты указаны только две последние цифры года, Microsoft Excel версий 97 и 2000 добавит первые две цифры по следующим правилам:

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

ДЕЙСТВИЯ С ДАТАМИ

ОСНОВНЫЕ ФУНКЦИИ ДЛЯ РАБОТЫ С ДАТАМИ

В Excel имеется ряд функций для работы с датами. Рассмотрим некоторые из них.

'Hftefctfcaêvteftue & ягейках

- если введенное число лежит в интервале от 00 до 29, то оно интерпретируется как год с 2000 по 2029;

- если введенное число находится в интервале от 30 до 99, то оно интерпретируется как год с 1930 по 1999.

Таким образом, фирма Microsoft в свое время позаботилась о переходе в новое тысячелетие. Поэтому года с 1900 по 1929 следует указывать полностью.

По умолчанию, значения даты выравниваются в ячейке по правому краю. Если не происходит автоматического распознавания формата даты, то введенные значения интерпретируются как текст, который выравнивается в ячейке по левому краю.

ПРЕДСТАВЛЕНИЕ ДАТ В ЯЧЕЙКАХ

Формат представления даты в ячейке, отображаемый после ввода значения, может быть изменен с помощью меню: пункт Формат, подпункт Ячейки, вкладка Число, раздел Числовые форматы - Дата. Так, вместо значения 05.12.87 можно получить 5.12.87; 5 дек 87; 5 Декабрь, 1987

Как уже отмечалось, даты можно складывать и вычитать, сравнивать между собой. Можно также умножать и делить их на числа! Для того чтобы по-

помощью маркера заполнения распространим (скопируем) введенное значение на ячейки А2:А10 (в них появятся даты, в которых будут значения, соответствующие 2, 3, ..., 10 января 1900 года). Скопируем блок ячеек А1:А10 в В1:В10. Изменим формат представления данных в блоке В1:В10 на Основной (Формат-Ячейки-Число-Основной). Мы увидим, что в этом блоке появятся значения 1, 2, ..., 10. Итак, дата в Excel -количество дней, прошедших от 1 января 1900 года. Такая форма внутреннего представления дат позволяет выполнять над ними различные арифметические операции и операции сравнения.

нять, как это реализуется, необходимо разобраться, как хранятся даты в компьютере. Введем в ячейку А1 дату 1 января 1900 года (напомним, что в Excel 97/2000 для этого следует ввести 1-1-1900, а не 1-1-00). С

1. Функции ДЕНЬ, МЕСЯЦ и ГОД.

Эти функции возвращают, соответственно, номер дня в месяце, номер месяца в году и год для некоторой даты.

Их синтаксис: ДЕНЬ(да-та), МЕСЯЦ(дата) и ГОД(дата), где аргумент дата - адрес ячейки, содержащей дату, либо дата, заданная в общем или числовом формате (12345), либо как текст (например, "154-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.

2. Функция ДЕНЬНЕД.

Функция возвращает номер дня недели, соответствующий некоторой дате. Ее синтаксис: ДЕНЬНЕД(дата; тип), где дата - аргумент, аналогичный используемому в описанных выше функциях; тип - число, которое определяет вариант возвращаемых значений (см. таблицу 1).

^нщисс ЪВ'ЯЪ, МеСЯ-Щ и ТбЪ.

Примеры:

1. Если в ячейке А2 указана дата 26.10.49, то ДЕНЬНЕД(А2) равняется

4 (среда).

2. ДЕНЬНЕД(" 15.2.90") равняется

5 (четверг)

3. ДЕНЬНЕД(" 15.2.90"; 2) равняется 4 (четверг).

3. Функция СЕГОДНЯ. Функция возвращает

дату текущего дня, отслеживаемую компьютером. Ее синтаксис: СЕГОДНЯ () - без аргументов, но с обязательными скобками.

4. Функция ДАТА. Функция позволяет «собрать» дату из значений года, номера месяца и номера дня. Ее синтаксис: ДАТА(год; месяц; день), где год - это число от 1900 до 2078; месяц -

это число, представляющее номер месяца в году; день - это число, представляющее номер дня в месяце.

Например, ДАТА(45; 5; 9) есть 9 мая 1945 года.

Таблица 1

Тип Возвращаемые значения

1 или опущен от 1 (воскресенье) до 7 (суббота)

2 от 1 (понедельник) до 7 (воскресенье)

3 от 0 (понедельник) до 6 (воскресенье)

ЗАДАЧИ ДЛЯ УЧАЩИХСЯ 5. В ячейке В2 будет записана не-

которая дата. Получите в ячейках В3-В5, 1 В ячейке В2 получите значение соответственно, номер дня в месяце, но-

«9 май 45» (указанное значение ни в одну мер месяца и год этой даты. из ячеек не вводить).

2. В ячейке В2 получите значение «26 Октябрь, 1949» (указанное значение ни в одну из ячеек не вводить).

3. В ячейках В2 и В3 получите число 37135 (указанное число ни в одну из ячеек не вводить).

6. По дате, указанной в ячейке, определите номер дня недели, на который приходилась эта дата (понедельник - 1, вторник - 2, ... , воскресенье - 7).

7. В ячейке В2 будет записана некоторая дата. В ячейке В3 получите дату дня, который будет через 100 дней после указанной даты.

А В С

1

2 09.05.45

3

А В С

1

2 26.10.49

3

А В С

1

2 Введите дату ®

3 Число в этой дате:

4 Месяц в этой дате:

5 Год в этой дате:

6

А В С

1

2 01.сен.01

3 01.09.01

4

4. В ячейках А2 и А3 получите число 18197 (указанное число ни в одну из ячеек не вводить).

А В С

1

2 26 Октябрь, 1949

3 26.10.49

4

...палугите дату котаорий бурей гере^ 100 рНей пасле указанной рати.

А В С

1

2 Введите дату ®

3 Дата через 100 дней после указанной:

4

8. В ячейке В2 будет записана некоторая дата. В ячейке В3 получите дату дня, который был за 200 дней до указанной даты.

А В С

1

2 Введите дату ®

3 Дата за 200 дней до указанной:

4

9. В ячейке В2 получите дату текущего дня, в ячейке В4 - номер дня недели (понедельник - 1, вторник - 2, ..., воскресенье - 7), который будет через некоторое число дней после текущего дня (это число будет указано в ячейке В3):

А В C

1

2 Дата сегодняшнего дня:

3 Количество дней после сегодняшнего:

4 Искомый номер дня:

5

10. В ячейке В2 получите дату текущего дня, в ячейке В4 - номер дня недели (понедельник - 1, вторник - 2, ..., воскресенье - 7), который был за некоторое число дней до текущего дня (это число будет указано в ячейке В3):

А В C

1

2 Дата сегодняшнего дня:

3 Количество дней до сегодняшнего:

4 Искомый номер дня:

5

11. Для текущей даты вычислите:

а) порядковый номер дня с начала

года;

б) сколько осталось дней до конца года и сколько до конца месяца.

В обоих заданиях следует считать, что номер года и количество дней в месяце известны.

12. В ячейках В2 и В3 будут указаны даты двух событий. Определите, сколько дней прошло между этими событиями.

13. В ячейке В2 указана дата некоторого события, произошедшего в первой половине XX века. Необходимо в ячейке ВЗ получить дату дня, до которого от 1 января 1900 года прошло в 2 раза больше дней, чем от 1 января 1900 года до дня данного события.

14. В ячейке В2 запишите дату Вашего рождения, а в ячейке В3 получите дату текущего дня. Определите дату того дня, когда число дней Вашей жизни станет в

2 раза больше, чем число прожитых дней до текущего дня. Дату получите в формате вида 12 Апрель, 2017.

15. Известна дата рождения Пети. Определите дату рождения Коли, если известно, что число дней, прожитых им до текущего дня, в 2 раз меньше, чем число дней, прожитых Петей.

16. В ячейке В2 запишите дату вашего рождения, а в ячейке В3 получите дату текущего дня. Определите номера дней недели (понедельник - 1, вторник -2, ..., воскресенье - 7), которые будут, когда число дней Вашей жизни станет в 2, 3, 4 и 5 раз больше, чем число прожитых дней до текущего дня.

17. В ячейке В2 и В3 будут указаны даты двух событий. Определите, какое событие произошло раньше.

18. На листе представлены сведения о дате рождения учеников класса.

В диапазоне ячеек С3-С27 поставьте знак «+» для тех учеников, дата рождения которых:

а) приходится на среду;

б) приходится на 10-е число месяца;

в) приходится на август.

A В C

1

2 №№ Фамилия, имя Дата рождения:

3 1 Азаров Павел

4 2 Бордянская Анна

5 3 Войлуков Кирилл

27 25 Ющенко Мария

28

.......—^¿и,

19. Для исходных данных, представленных в условии задачи 16, определите количество учеников, которые родились после некоторой даты, указываемой в ячейке С28.

20. В ячейке В2 будет указана дата некоторого события. Необходимо в ячейке В3 получить дату дня, который будет через 3 года после этого события.

21. В ячейке В2 будет указана дата некоторого события. Необходимо в ячейке В3 получить дату дня, который был за 5 месяцев до этого события.

22. В ячейке В2 будет указана дата некоторого события. Необходимо в ячейке В3

получить дату дня, который будет через п лет, т месяцев и к дней после этого события. Значения п, т и к вводятся в отдельные ячейки.

23. В ячейке В2 введите дату Вашего рождения. Необходимо в ячейках Э4:013 получить номер дня недели (понедельник - 1, вторник - 2, ... , воскресенье - 7), который будет в день Вашего рождения через 1 год, 2 года, ..., 10 лет.

А В с Б Т

1

2 Дата рождения:

3

4 Номер дня недели в день рождения через 1 год:

5 Номер дня недели в день рождения через 2 года:

6 Номер дня недели в день рождения через 3 года:

13 Номер дня недели в день рождения через 10 лет:

' &гейке '2 Мефшбе 'Вашею роф^Аия.

Указания по выполнению:

1. Принять, что задача может решаться в любом году, а не только в текущем, номер которого известен.

2. Значение в ячейке Б4 следует получить с помощью формулы, которую затем распространить (скопировать) на остальные ячейки.

После решения ответить на вопрос: «Почему полученные номера дней недели иногда увеличиваются на 2?»

© Наши авторы: 2002. Оиг аи1Иогэ> 2002,

Златополъский Дмитрий Михайлович, доцент Московского городского университета,

учителъ гимназии № 1530 г. Москвы.