Работа с датами

9/27/2011 Изучаем EXCEL 7.0 1

_______________________________________________________________________________

Лабораторная работа № 4

Тема: Работа с датами

Основной единицей измерения времени в Microsoft EXCEL являются сутки. Каждые сутки представляются последовательными значениями от 1 до 65380. Базовая дата, представляемая значением 1 - это воскресенье 1 января 1900 года. 65380 - это 31 декабря 2078 года. Когда вводится дата, то EXCEL хранит ее в виде десятичного числа, которое показывает количество дней от 1 января 1900 года до указанной даты. Время суток - это десятичная дробь, которая представляет часть суток от 12:00 ночи до заданного времени. Назначая десятичные значения дням, часам, минутам и секундам, EXCEL позволяет проводить сложные вычисления с датами и временем. Дату можно вводить в формате Д.М.ГГ.

Замечание. Дополнительный встроенный формат Д.М.ГГ Ч:ММ.

Упражнение № 1

Введите дату 1 января 1997 года.

  1. Выделите ячейку и введите 1.1.97 или 1-1-97.

  2. В ячейке появится 1.01.97.

  3. Выделите ячейку и введите 1 дек (ENTER).

Упражнение № 2я2

Ввод ряда дат.

Создайте упорядоченный ряд дат в строке или столбце. Это можно сделать с помощью команды Правка  Заполнить Прогрессия (Edit Fill Series). Пользуясь этой командой, можно создать ряды дат с шагом, кратным дню, неделе, месяцу или году (Day, Weekday, Month, Year).

Создайте в столбце А последовательность лат, начиная с 1 января 1997 года с интервалом 1 месяц до 1 декабря 1997.

  1. Введите в ячейку А1 1.1.97.

  2. Выделите диапозон от А1 до А12 (щелкните по ячейке А1, затем, удерживая Shift, щелкните по А12).

  3. В меню Правка (Edit) выберите команду Заполнить (Fill), а затем Прогрессия (Series).

  4. В окне диалога Прогрессия (Series) установите флажки:

Расположение (Series in): по столбцам

Тип (Type): дата

Единицы (Date Unit): месяц

Шаг (Step Value): 1.

  1. Нажмите ОК.

Секция Единицы (DATE UNIT) окна диалога Прогрессия позволяет задать разные временные интервалы для создаваемого ряда дат. В поле Предельное значение (Stop Value) можно задать конечную дату ряда.

Упражнение № 3

Ввод ряда дат с автозаполнением. Вы уже умеете вводить числовые и текстовые ряды с помощью автозаполнения. Для ввода ряда дат сделайте следующее:

  1. В ячейку В5 введите 1.03.97.

  2. В ячейку В6 введите 1.04.97.

  3. С помощью мыши выделите эти две ячейки.

  4. Установите указатель мыши на маркере автозаполнение (Autofill).

Указание. Если маркер не виден, выберите в меню Сервис команду Параметры (Tools Options). На вкладке Правка (Edit) установите флажок Разрешить перетаскивание ячеек (Move Selection After Enter).

Указатель мыши должен принять вид жирного перекрестия.

  1. Перетащите маркер заполнения, пока не будет выделен весь диапозон, который Вы хотите заполнить датами.

  2. Отпустите кнопку мыши. Автозаполнение проанализирует две выделенные ячейки, определит, что их значения отличаются на 1 месяц и создаст ряд данных с месячным интервалом.

Задание 1. Создайте ряды:

  1. дат с однодневным интервалом, начиная с 1 января 1997 года;

  2. дат с двухмесячным интервалом, начиная с 1 января 1996 года;

  3. последовательность вида

1 янв 2 янв 3 янв ..... 7 янв

4) последовательность вида

дек 96 дек 97 дек 98 ..... дек 02

  1. последовательность вида

1 квартал 2 квартал .......

6) янв фев мар апр

  1. январь февраль .....

  2. день 1 день 2 день 3 ..... день 7

  3. пн вт ср ..... вс

Упражнение № 4

Форматирование дат и времени. Введите дату 28 февраля 1997 года. Форматировать дату можно в соответствии с пятью встроенными формулами.

Формат

Вывод

Д.М

28.2

Д.М.ГГ

28.2.97

ДД.ММ.ГГ

28.02.97

Д.ММММ

28 февраля

Д.ММММ.ГГ

28 февраля 1997

Для назначения формата выполните следующее:

  1. Выделите ячейки, которые надо отформатировать. Например, ячейку D5, в которой введена дата 28 февраля 1997 года.

  2. В меню Формат (Format) выберите команду Ячейки (Cells).

  3. В окне диалога Формат ячеек (Format Cells) щелкните по вкладке Число.

  4. В списке Числовые форматы (Number) выберите категорию ДАТА.

  5. В списке Тип выберите нужный формат.

  6. Щелкните по ОК.

Упражнение № 5

Назначьте Формат времени (Format Time) ячейке. Некоторые форматы используют 24-часовой формат, а некоторые 12-часовой.

  1. Введите время в ячейку Е7 в виде 13:52:32,44.

  2. Изучите ФОРМАТЫ, которые предлагает EXCEL

  1. Стандарты форматов времени представлены в таблице.

Формат

Вывод

Ч:ММ

13:52

Ч:ММ РМ

1:52 РМ

Ч:ММ:СС

13:52:32

Ч:ММ:СС РМ

1:52:32 РМ

ММ:СС,О

52:32,4

[Ч]:ММ:СС

13:52:32

Д.М.ГГ Ч:ММ РМ

0.1:00 1:52 РМ

Д.М.ГГ Ч:ММ

0.1.00 13:52

  1. Выделите ячейку В7.

  2. В меню Формат (Format) выберите команду Ячейки (Cells).

  3. Щелкните по вкладке Число (Number).

  4. В списке Числовые форматы (Category) найдите категорию Время (Time).

  5. В списке Тип (Type) выберите нужный формат.

  6. Щелкните по ОК.

Упражнение № 7

Арифметические операции с датами. Введенные даты можно использовать в формулах и функциях также как и любые другие значения.

  1. Введите в ячейку А10 дату 14 мая 1997 года.

  2. В ячейке А2 подсчитайте дату, которая будет отстоять от заданной на 50 дней: = А1+50 (Enter).

  3. Найдите число дней между датами:

  • в ячейке А1 введена дата 14 мая 1997 года

  • в ячейке А3 введена жата 31 октября 1997 года

  1. В ячейку А4 введите: =(«31.10.97»)-(«14.05.97») или =А3-А1.

  2. Найдите число недель между этими датами: =((«31.10.97»)-(«14.05.97»))/7.

Функции дат и времени

  1. =Сегодня ( ) - возвращает текущую дату.

= Today ( )

  1. =Время ( ) - (часы, минуты, секунды).

= Time ( )

  1. ДЕНЬНЕД (десятичная_дата, тип).

Weekday ( )

Здесь десятичная_дата - это либо выражение даты, либо ячейка, которая содержит дату (например, 27.01.97) или А1, или 27 января 1997 года. Если используется текст, то его надо заключить в кавычки. Тип - тип представления результатов.

  • если тип =1 или опущен, то функция возвращает число от 1 до 7, где 1 - воскресенье, а 7 - суббота;

  • если тип =2, то возвращается число дня так: 1 - понедельник, а 7 - воскресенье;

  • если тип =3, то возвращается значение от 0 до 6, где 0 - это понедельник и 6 - воскресенье.

Упражнение № 8

В ячейке А1 введена дата 27.01.97. В ячейку А5 введите = ДЕНЬНЕД (А5,1) (можно использовать Мастер функций).

Вы можете создавать свой пользовательский формат.

Для этого воспользуйтесь советом, который приведен ниже:

Совет. Номер дня наглядно отображает день недели. Создайте свой пользовательский формат даты в виде ДДДД.

  1. выделите ячейку А1;

  2. в меню Формат выберите команду Ячейки(Format Cells);

  3. щелкните по корешку вкладки Число (Number);

  4. В списке Числовые форматы (Category) выберите категорию Все форматы (Customs);

  5. В поле Тип (Type) введите свой пользовательский формат в виде ДДДД;

  6. Щелкните по ОК. EXCEL сохранит в списке Тип новый формат для категории Все форматы. Дата в ячейке А1 будет представлена в виде ДДДД.

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

= ГОД (десятичная_дата)

= МЕСЯЦ (десятичная_дата)

= ДЕНЬ (десятичная_дата)

Аргумент десятична_дата - это или выражение даты или ячейка.

= ГОД (А1) (YEAR) возвратит 1997

= МЕСЯЦ (MONTH) (А1) возвратит 1

= ДЕНЬ (DAY)(А1) возвратит 27

Функция ДНЕЙ360 (DAYS360)

Определенные вычисления с датами являются обыденными при обращении ценных бумаг, использующих искусственный 360-дневный год, который содержит двенадцать 30-дневных месяцев. Возвращает количество дней между датами.

Синтаксис:

= ДНЕЙ360 (начальная_дата; конечная_дата;метод) (DAYS360 (start_data; end_data; method))

Аргументы начальная_дата и конечная_дата могут быть выражением даты или номером ячеек.

Упражнение № 9

Найдите в ячейке С1 количество дней между датами А1= 27.01.97

В1= 29.05.97

=ДНЕЙ360 («27.01.97»;»29.05.97») или =ДНЕЙ (А1;В1)

Метод = 1 - используется европейский метод вычислений. Это значит, если начальная дата =31, то используется 30, а если конечная дата равна 31, то используется первый день следующего месяца. Вместо 28 или 29 февраля используется 30 февраля.

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

Ряд специальных функций даты выполняет такие вычисления, связанные с датами погашения ценных бумаг, ведением платежной ведомости и планированием работ. Эти функции работают только в том случае, если установлена надстройка ПАКЕТ АНАЛИЗА.

Упражнение № 9

  1. В меню Сервис (Tools) выберите команду Надстройки (Add-Ins).

  2. В списке надстроек выберете Пакет Анализа.

  3. Нажмите ОК.

Функции ДАТАМЕС и КОНМЕСЯЦА

= ДАТАМЕС (начальная_дата; число месяцев) (EOMONTH (start_data; month))

Аргумент начальная_дата - это дата, от которой производится отчет.

Число_месяцев - целое число до или после начальной даты ( если >0, то после даты, если <0, то до даты).

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

Пример 1. Найдите дату в ячейку В7, которая отстоит от даты в ячейке А1 на 15 месяцев.

= КОНЕЦМЕСЯЦА (начальная_дата; число_месяцев) (EOMONTH (start_data; month)) - функция аналогична ДАТАМЕС, но только в этом случае возвращаемое значение даты всегда является последним днем месяца.

Пример 2. Найдите значение функции для ячейки А1 через 15 месяцев

= ДОЛЯГОДА

Эта функция вычисляет десятичное значение, которое представляет часть года интервалом между двумя заданными датами. Синтаксис:

= ДОЛЯГОДА (начальная_дата; конечная_дата; базис)

= YEARFRAC (start_data; end_data; basis)

Здесь базис задает способ вычисления:

  • базис =0 - задает способ 30/360, т.е. 30 дней в месяце и 360 дней в году

  • базис =1 - задает фактическое количество дней в месяце и фактическое количество дней в году

  • базис =2 - задает фактическое количество дней в месяце и 360 дней в году

  • базис =3 - задает фактическое количество дней в месяце и 365 дней в году

  • базис=4 - задает 30 дней в месяце и 360 дней в году

Упражнение № 11

Определите в ячейки Е1, Е2, Е3, Е4 долю года, которую составляет разница между датами в ячейке А1 и В1 (упражнение № 9) для разных базисов.

Функции РАБДЕНЬ и ЧИСТРАБДНИ.

Эти функции представляют интерес для каждого, кто ведет платежные ведомости или составляет графики работ. Обе функции возвращают значения, учитывая только рабочие дни между датами. Кроме того, можно указать учитывать ли праздники между датами.

= РАБДЕНЬ (начальная_дата; рабочие_дни; количество_дней_праздника)

= WORKDAY (start_data; days; holidays)

Пример 3. Найдите дату, которая будет отстоять на 40 рабочих дней от текущей, не учитывая праздники.

= РАБДЕНЬ (ТДАТА; 100)

Праздники можно задавать датами или массивом.

Пример 4. Найти дату, отстоящую от текущей на 100 рабочих дней и учесть праздники года (1,2 января, 8 марта, 1,2,9 мая, 12 июня, 7 ноября) в ячейку А14.

= РАБДЕНЬ (ТДАТА ( ); 100; {«1.01.97»; «2.01.97»; «8.03.97»; «1.05.97»; «2.05.97»; «9.05.9»; «12.06.97»; «7.11.97»})

= ЧИСТРАБДНИ (начальная_дата; конечная_дата; праздники)

= NETWORKDAY (start_data; end_data; holiday)

Упражнение № 12

Найти в ячейку А15 количество рабочих дней между 1.01.97 и 15.05.97.

= ЧИСТРАБДНИ («1.01.97»; «15.05.97»; {«1.01.97»; «2.01.97»; «1.05.97»; «2.05.97»; «9.05.97»})

ЗАДАНИЕ. Создайте бланк ТАБЕЛЯ учета рабочего времени на АПРЕЛЬ Текущего года.