 |
Рабата в MS Excel с данными в формате дата
|
|
|
|
Microsoft Excel хранит даты в виде последовательных чисел. По умолчанию дате 1 января 1900 года соответствует порядковый номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами в днях равен 39 448. Microsoft Excel сохраняет время в виде десятичной дроби (время является частью даты).
Поскольку даты и значения времени представляются числами, их можно складывать и вычитать, а также использовать в других вычислениях. При использовании основного формата для ячеек, содержащих дату и время, можно отобразить дату в виде числа или время в виде дробной части числа с десятичной точкой.
Поскольку правила, которые задают способ интерпретации дат в разных вычислительных программах, довольно сложны, следует быть предельно конкретным при вводе дат. Это обеспечит наивысшую точность в вычислении дат.
Системы дат 1900 и 1904
Microsoft Excel поддерживает две системы дат: 1900 и 1904. По умолчанию Microsoft Excel для Windows использует систему дат 1900. По умолчанию Microsoft Excel для Macintosh использует систему дат 1904. Чтобы изменить систему дат, в меню Сервис выберите команду Параметры, перейдите к вкладке Правка и установите или снимите флажок система дат 1904.
Пример работы с датами
Выяснить дату, отстоящую от текущего числа на определённое количество лет, месяцев и дней.
Рис.2.15. Пример работы с датами
Обратите внимание: в ячейке D34 для получения текущей даты записана функция СЕГОДНЯ ().
После выполнения задания научиться выполнять поиск чисел, текстов, дат при помощи условного форматирования – Условное форматирование (Формат – Условное форматирование …).
При расчетах учитывать:
нерабочие дни - суббота, воскресенье;
Праздничные дни - 01.01.12, 07.01.12, 08.03.12, 24.04.12, 09.05.12, 3.07.12, 07.11.12, 25.12.12.
|
|
|
Пример работы с текстом
Получить ФИО гражданина, если известны его полные имя, фамилия и отчество.
Рис. 2.16. Пример работы с текстом
Задание по вариантам
Вариант 1
Исходные данные:
Сотрудник работает в неделю один раз по 2 часа (в один и тот же день недели) в 1 квартале 2012 года.
Определить день недели работы сотрудника и вручную ввести первую дату работы.
Расчетные данные:
1) вывести календарь работы сотрудника (использовать функцию);
2) определить фонд рабочего времени сотрудника за каждый месяц;
3) определить фонд рабочего времени за 1 квартал 2012 года (при расчетах учесть праздничные дни);
4) условным форматированием найти определенные (2) даты работы сотрудника и пометить их цветом;
5) ввести в разные ячейки свои данные: фамилия, имя, отчество; объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 2
Исходные данные:
Оформить ведомость для работников некоторого производственного отдела, включающую следующие разделы:
§ фамилия И. О.,
§ начало работы (в формате день.месяц.год),
§ стаж работы,
§ коэффициент надбавки за стаж.
Расчетные данные:
Добавить необходимую формулу для столбца:
1) «стаж работы», учитывающую разность дат настоящего момента времени и дату, указанную в столбце «начало работы» (в годах);
2) «коэффициент надбавки за стаж» с целью получения необходимого значения коэффициента в данном столбце. Формула должна учитывать информацию, полученную в столбце «стаж работы»:
· менее года — надбавка 0,
· от 1 года до 5 лет — 0,1,
· от 5 до 10 лет — 0,15,
· свыше 10 лет — 0,2;
3) условным форматированием найти стаж >20 лет.
4) ввести в разные ячейки свои данные: фамилия, имя, отчество; объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 3
Исходные данные:
Оформить таблицу отчета о продаже товаров, в котором должны быть следующие столбцы:
|
|
|
§ наименование товара (заполняется вручную),
o дата поступления (заполняется вручную в формате день.месяц.год),
o дата реализации (заполняется вручную(в формате день.месяц.год),
§ срок реализации,
§ характеристика спроса на товар.
Расчетные данные:
Добавить необходимые формулы для столбцов:
1. «срок реализации», учитывающую значения, помещенные в столбцы «дата поступления», «дата реализации»;
2. «характеристика спроса на товар», которая формируется с учетом значений столбца «дата реализации» и учитывает следующие значения:
• до 3-х дней— товар пользуется спросом,
• от 3 до 30 — нормальная реализация,
• от 30 до 60 дней — замедленная реализация товара,
• свыше 60 дней — товар залежался.
3. Условным форматированием выделить товар, который пользуется спросом.
4. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информация о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 4
Исходные данные:
Сформировать ведомость учета продуктов питания, включающую разделы:
· наименование (заполняется вручную),
· дата выпуска (заполняется вручную),
· срок годности (в днях) (заполняется вручную),
· дата окончания годности,
· признак (на текущий день).
Расчетные данные:
1.Добавить необходимые формулы для столбцов:
«дата окончания годности» – в расчетах использовать данные столбцов «дата выпуска» и «срок годности»;
2. «признак» – заполнить одним из текстов: «годен», «осталось менее месяца», «срок годности истек».
3. Условным форматированием отметить признаки «срок годности истек».
4. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 5
Исходные данные:
Создать таблицу для расчета данных по 1 кварталу 2012 года.
Расчетные данные:
1. Рассчитать, сколько календарных дней в 1 квартале 2012.
2. Рассчитать, сколько рабочих дней в 1 квартале 2012 г. (без учета праздников).
3. Рассчитать, сколько рабочих дней в 1 квартале 2012 г. (с учетом праздников).
4. Определить, какой день недели сегодня.
5. Сколько рабочих дней прошло с начала квартала.
6. Сколько рабочих дней осталось до конца квартала.
|
|
|
7. С какого дня недели начинается каждый месяц 1 квартала.
8. Каким днем недели заканчивается 1 квартал 2012.
9. Условным форматированием отметить 2 даты по выбору.
10. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 6
Исходные данные:
В текущем году необходимо выполнить запланированный проект. Работа над проектом составляет 74 дня. Составить таблицу планирования со столбцами:
1. Работа над проектом, в днях (заполняется вручную).
2. Начало работы (заполняется вручную).
3. Окончание работы /календарные дни.
4. Окончание работы / рабочие дни без учета праздничных дней.
5. Окончание работы /рабочие дни с учетом праздничных дней.
6. Сдача проекта.
7. Номер месяца.
8. День недели.
9. До конца года осталось дней.
10. Условным форматированием отметить признаки 2 числа по выбору.
11. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Расчетные данные:
Добавить необходимые формулы для столбцов:
| Номер столбца | Результат расчета |
| 3 | Определите дату, которая наступит через 74 календарных дня |
| 4 | Определите дату, которая наступит через 74 рабочих дня (без учета праздничных дней) |
| 5 | Определите дату, которая наступит через 74 рабочих дня, (с учетом праздничных дней) |
| 6 | Следующий день после п. 5. |
| 7 | Определите номер месяца сдачи проекта |
| 8 | Определите день недели сдачи проекта |
| 9 | Определите, сколько дней останется до конца года после сдачи проекта |
Вариант 7
Исходные данные:
Создать таблицу для расчета данных по 2 кварталу 2012 года.
Расчетные данные:
1. Рассчитать, сколько дней в 2 квартале 2012г.
2. Рассчитать, сколько рабочих дней во 2 квартале 2012 г. (без учета праздников).
3. Рассчитать, сколько рабочих дней во 2 квартале 2012 г. (с учетом праздников).
4. Определить фонд рабочего времени 2 квартала (рабочий день 8 часов).
5. Определить потери рабочего времени в праздники.
6. Определить фонд рабочего времени каждого месяца во 2 квартале (рабочий день 8 часов).
|
|
|
7. Определить потери рабочего времени в праздники по каждому месяцу во 2 квартале.
8. Условным форматированием отметить признаки 2 числа по выбору.
9. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 8
Расчетные данные:
1. Сколько календарных дней между 1 июля 2012 г. и 28 декабря 2012 г.?
2. Определить день недели для дат: 1 июля 2012 г., 28 декабря 2012 г.
3. Сколько рабочих дней (исключая праздники) между 1 июля 2012 г. и 28 декабря 2012 г.?
4. Сколько рабочих дней (включая праздники) между 1 июля 2012 г. и 28 декабря 2012 г.?
5. Определите день недели для каждой праздничной даты, которая находится на данном временном промежутке.
6. Условным форматированием отметить 1 июля 2012 г. и 28 декабря 2012 г.
7. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 9
Исходные данные:
Проект состоит из трех действий: Действия № 1, № 2 и № 3.
Действие № 2 начинается на следующий день после завершения Действия № 1. Действие № 3 начинается на следующий день после завершения Действия № 2.
Создайте электронную таблицу, которая в качестве входящих данных будет принимать:
· Дату начала проекта.
· Длительность (в днях) каждого действия, и будет возвращать расчетные даты.
Расчетные данные:
1. Определите даты начала Действий № 2 и № 3.
2. Даты завершения Действий № 2 и № 3 (праздничные дни не учитывать, выходные – суббота и воскресенье).
3. Определите дату сдачи проекта – следующий день после завершения последнего действия.
4. Определите дни недели начала каждого действия и даты сдачи проекта.
5. Определите номер месяца даты сдачи проекта.
6. Условным форматированием отметить 2 даты по выбору.
7. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 10
Исходные данные:
Создать таблицу для расчета данных по 4 кварталу 2012 года.
Расчетные данные:
1. Рассчитать, сколько календарных дней в 4 квартале 2012 года.
2. Рассчитать, сколько рабочих дней в 4 квартале 2012 г. (без учета праздников).
3. Рассчитать, сколько рабочих дней в 4 квартале 2012 г. (с учетом праздников).
4. Определить, какой день недели сегодня.
5. Сколько рабочих дней прошло с начала квартала?
6. Сколько рабочих дней осталось до конца квартала?
7. С какого дня недели начинается каждый месяц 4 квартала 2012 г.?
8. Каким днем недели заканчивается 4 квартал 2012 г.?
9. Условным форматированием отметить 2 даты по выбору.
|
|
|
10. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 11
Исходные данные:
Сотрудник работает в неделю два дня по 2 часа (например, каждые вторник и пятницу) в 1 квартале 2012 года.
Определить день недели работы сотрудника и вручную ввести первую дату работы.
Расчетные данные:
1) вывести календарь работы сотрудника (использовать функцию);
2) определить фонд рабочего времени сотрудника за каждый месяц;
3) определить фонд рабочего времени за 1 квартал 2012 года (при расчетах учесть праздничные дни);
4) условным форматированием найти определенные (2) даты работы сотрудника и пометить их цветом.
5) ввести в разные ячейки свои данные: фамилия, имя, отчество; объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 12
Расчетные данные:
1. Сколько календарных дней в осеннем семестре?
2. Определить день недели для дат: 1 сентября 2012 г., 28 декабря 2012 г.
3. Сколько рабочих дней (исключая праздники) в осеннем семестре 2012 г.?
4. Сколько рабочих дней (включая праздники) в осеннем семестре 2012 г.?
5. Определите день недели для каждой праздничной даты, которая находится на данном временном промежутке.
6. Условным форматированием отметить 1 сентября 2012 г. и 28 декабря 2012 г.
7. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 13
Расчетные данные:
1. Сколько календарных дней в весеннем семестре?
2. Определить день недели для дат: 1 апреля 2013 г., 1 мая 2013 г.
3. Сколько рабочих дней (исключая праздники) в осеннем семестре 2013 г.?
4. Сколько рабочих дней (включая праздники) в осеннем семестре 2013 г.?
5. Определите день недели для каждой праздничной даты, которая находится на данном временном промежутке.
6. Условным форматированием отметить 1 апреля 2013 г. и 1 мая 2013 г.
7. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 14
Исходные данные:
Сотрудник работает в неделю три дня по 2 часа (например, каждые вторник, среду и пятницу) в 1 квартале 2012 года.
Определить день недели работы сотрудника и вручную ввести первую дату работы.
Расчетные данные:
1) вывести календарь работы сотрудника (использовать функцию);
2) определить фонд рабочего времени сотрудника за каждый месяц;
3) определить фонд рабочего времени за 1 квартал 2012 года (при расчетах учесть праздничные дни);
4) условным форматированием найти определенные (2) даты работы сотрудника и пометить их цветом;
5) ввести в разные ячейки свои данные: фамилия, имя, отчество; объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Вариант 15
Исходные данные:
Создать таблицу для расчета данных по 3 кварталу 2012 года.
Расчетные данные:
1. Рассчитать, сколько календарных дней в 3 квартале 2012 г.
2. Рассчитать, сколько рабочих дней в 3 квартале 2012 г. (без учета праздников).
3. Рассчитать, сколько рабочих дней в 3 квартале 2012 г. (с учетом праздников).
4. Определить, какой день недели сегодня.
5. Сколько рабочих дней прошло с начала квартала?
6. Сколько рабочих дней осталось до конца квартала?
7. С какого дня недели начинается каждый месяц 3 квартала 2012 г.?
8. Каким днем недели заканчивается 3 квартал 2012 г.?
9. Условным форматированием отметить 2 даты по выбору.
10. Ввести в разные ячейки свои данные: фамилия, имя, отчество. Объединить информацию о себе в одну ячейку по образцу: фамилия и инициалы.
Задание 7
|
|
|
