Лабораторная работа № 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) установите флажки:
Расположение(Seriesin): постолбцам
Тип(Type): дата
Единицы(Date Unit): месяц
Шаг (StepValue): 1.
5. Нажмите ОК.
СекцияЕдиницы (DATEUNIT) окнадиалога Прогрессия позволяет задать разные временные интервалы для создаваемогоряда дат. В поле Предельное значение (StopValue) можно задать конечную дату ряда.
Упражнение № 3
Вводряда дат с автозаполнением. Вы уже умеете вводить числовые и текстовые ряды спомощью автозаполнения. Для ввода ряда дат сделайте следующее:
1. В ячейку В5 введите 1.03.97.
2. В ячейку В6 введите 1.04.97.
3. С помощью мыши выделите эти двеячейки.
4. Установите указатель мыши намаркере автозаполнение (Autofill).
Указание.Если маркер не виден, выберите в меню Сервис команду Параметры (ToolsÝOptions). На вкладке Правка (Edit)установите флажок Разрешить перетаскивание ячеек (MoveSelectionAfterEnter).
Указательмыши должен принять вид жирного перекрестия.
5. Перетащите маркер заполнения, покане будет выделен весь диапозон, который Вы хотите заполнить датами.
6. Отпустите кнопку мыши.Автозаполнение проанализирует две выделенные ячейки, определит, что их значенияотличаются на 1 месяц и создаст ряд данных с месячным интервалом.
Задание1. Создайте ряды:
1) дат с однодневным интервалом,начиная с 1 января 1997 года;
2) дат с двухмесячным интервалом,начиная с 1 января 1996 года;
3) последовательность вида
1 янв 2 янв 3 янв ..... 7 янв
4)последовательность вида
/>/> дек 96 дек 97 дек98 ..... дек02
5) последовательность вида
/>/>/>/>/> 1 квартал 2квартал .......
6) янв фев мар апр
7) январь февраль .....
8) день1 день 2 день 3 ..... день 7
9) пн вт ср ..... вс
Упражнение № 4
Форматированиедат и времени. Введите дату 28 февраля 1997 года. Форматировать дату можно всоответствии с пятью встроенными формулами.Формат Вывод Д.М 28.2 Д.М.ГГ 28.2.97 ДД.ММ.ГГ 28.02.97 Д.ММММ 28 февраля Д.ММММ.ГГ 28 февраля 1997
Дляназначения формата выполните следующее:
1. Выделите ячейки, которые надоотформатировать. Например, ячейку D5, в которой введена дата 28февраля 1997 года.
2. В меню Формат (Format) выберите команду Ячейки (Cells).
3. В окне диалога Формат ячеек (FormatCells) щелкните по вкладке Число.
4. В списке Числовые форматы (Number) выберите категорию ДАТА.
5. В списке Тип выберите нужныйформат.
6. Щелкните по ОК.
/>Упражнение № 5
НазначьтеФормат времени (FormatTime)ячейке. Некоторые форматы используют 24-часовой формат, а некоторые 12-часовой.
1. Введите время в ячейку Е7 в виде13:52:32,44.
2. Изучите ФОРМАТЫ, которыепредлагает EXCEL
3. Стандарты форматов временипредставлены в таблице.
Формат
Вывод Ч: ММ 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
2. Выделите ячейку В7.
3. В меню Формат (Format) выберите команду Ячейки (Cells).
4. Щелкните по вкладке Число (Number).
5. В списке Числовые форматы (Category) найдите категорию Время(Time).
6. В списке Тип (Type)выберите нужный формат.
7. Щелкните по ОК.
Упражнение № 7
Арифметическиеоперации с датами. Введенные даты можно использовать в формулах и функцияхтакже как и любые другие значения.
1. Введите в ячейку А10 дату 14 мая1997 года.
2. В ячейке А2 подсчитайте дату,которая будет отстоять от заданной на 50 дней: = А1+50 (Enter).
3. Найдите число дней между датами:
n в ячейке А1 введена дата 14 мая 1997 года
n в ячейке А3 введена жата 31 октября 1997 года
4. В ячейку А4 введите:=(«31.10.97»)-(«14.05.97») или =А3-А1.
5. Найдите число недель между этимидатами: =((«31.10.97»)-(«14.05.97»))/7.
Функции дат и времени
1. =Сегодня ( ) — возвращает текущуюдату.
= Today ( )
2. =Время ( ) — (часы, минуты,секунды).
= Time ( )
3. ДЕНЬНЕД (десятичная_дата, тип).
Weekday ( )
Здесь десятичная_дата — это либовыражение даты, либо ячейка, которая содержит дату (например, 27.01.97) или А1,или 27 января 1997 года. Если используется текст, то его надо заключить вкавычки. Тип — тип представления результатов.
n если тип =1 или опущен, то функция возвращает число от1 до 7, где 1 — воскресенье, а 7 — суббота;
n если тип =2, то возвращается число дня так: 1 — понедельник, а 7 — воскресенье;
n если тип =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, то после даты, если
Врезультате получаем дату, которая отстоит от начальной на заданное количествомесяцев.
Пример 1. Найдите дату в ячейку В7, которая отстоит от даты вячейке А1 на 15 месяцев.
=КОНЕЦМЕСЯЦА (начальная_дата; число_месяцев) (EOMONTH(start_data; month)) — функция аналогична ДАТАМЕС, но только в этомслучае возвращаемое значение даты всегда является последним днем месяца.
Пример2. Найдите значение функции дляячейки А1 через 15 месяцев
=ДОЛЯГОДА
Этафункция вычисляет десятичное значение, которое представляет часть годаинтервалом между двумя заданными датами. Синтаксис:
=ДОЛЯГОДА (начальная_дата; конечная_дата; базис)
= YEARFRAC(start_data; end_data; basis)
Здесьбазис задает способ вычисления:
n базис =0 — задает способ 30/360, т.е. 30 дней в месяцеи 360 дней в году
n базис =1 — задает фактическое количество дней в месяцеи фактическое количество дней в году
n базис =2 — задает фактическое количество дней в месяцеи 360 дней в году
n базис =3 — задает фактическое количество дней в месяцеи 365 дней в году
n базис=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»})
ЗАДАНИЕ.Создайте бланк ТАБЕЛЯ учета рабочего времени на АПРЕЛЬ Текущего года.