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

Функции даты и времени предназначены для работы с данными, имеющими тип даты и времени.

Excel хранит даты в виде чисел от 1 до 2 958 465 (в версиях ранее Excel 97 - до 65 380). Числа эти - "номер по порядку" данного дня, отсчитанный от 1 января 1900 года. Порядковое число 1 соответствует 1 января 1900 года, число 2 — это 2 января 1900 года и т.д. Дата написания этой статьи - 06 марта 2011 имеет порядковый номер 40608.

Такая система позволяет использовать даты в формулах. Например, с ее помощью легко подсчитать количество дней между двумя датами. Как это ни удивительно, но в Excel есть дата с порядковым числом, равным нулю, — 0 января 1900 года. Эта несуществующая дата используется для представления временных данных, которые не связаны с какой-либо конкретной датой.

Чтобы отобразить числовое значение в виде даты, просто примените к данной ячейке формат даты.

В Excel существуют две системы дат. Microsoft Excel поддерживает систему дат 1900 и систему дат 1904. Эти системы отличаются начальной датой, от которой отсчитываются порядковые числа дат. В системе дат 1900 начальной датой является 1 января 1900 года, а в системе 1904— 1 января 1904 года. По умолчанию Excel для Windows использует систему дат 1900, a Excel для Macintosh— систему 1904. Чтобы обеспечить совместимость с файлами электронных таблиц Macintosh, Excel для Windows может поддерживать систему дат 1904. При открытии документа, подготовленного на другой аппаратной платформе, смена системы дат происходит автоматически. Например, при открытии в Excel для Windows рабочей книги, созданной в Excel для Macintosh; система дат 1904 будет установлена автоматически. Будьте внимательны, если используете совместно рабочие книги, в которых применяются разные системы дат. При связывании данных передается порядковое число даты, которое в рабочей книге переводится в дату в соответствии с установленной в ней системой дат.

Максимальная доступная дата — 31 декабря 9999 года (в версиях до Excel 97 - 31 декабря 2078 года).

Время Excel хранит так же в виде чисел, точнее в виде десятичных дробей чисел соответствующих дат. То есть к порядковому номеру даты добавляется дробная часть, соответствующая части суток. Например 06 марта 2011 года имеет порядковый номер 40608, а полдень 06 марта 2011 года (12:00) будет соответствовать числу 40608,5 так как 12 часов - это 0,5 от 24.

Максимальное значение времени, которое можно ввести в ячейку (без значения даты), составляет 9999:59:59 (без одной секунды 10 тыс. часов).

Список функций даты и времени:

* - функции, которые доступны в версиях Excel младше 2007 только после подключения надстройки Пакет Анализа (Analysis ToolPack)

** - недокументированная функция Excel

Создание формул, использующих данные в формате даты и времени, может оказаться несколько затруднительным, если не знать, как данные такого типа представлены в Excel. Владея подобной информацией, вы сможете свободно применить многие функции даты и времени, которые предлагаются программой. Настоящая глава предлагает краткий обзор числового представления данных в формате даты и времени их использования для создания простейших формул, вычисляющих интервал времени. Кроме того, будут рассмотрены встроенные функции даты и времени, в том числе и доступные после установки надстройки Analysis ToolPak. В Excel данные в формате даты и времени, введенные в ячейки рабочего листа, представлены не так, как обычная текстовая информация (подробнее о вводе чисел в. электронные таблицы см. главу 1 книги 2). Любая введенная информация, которая напоминает по виду один из форматов даты и времени, используемый в Excel, автоматически конвертируется для внутреннего применения программой в действительное число. Если ввести дату, это число будет отображать количество дней, прошедших с 1 января 1900 г. Указанная дата имеет порядковый номер 1; 2 Января 1900 г. — порядковый номер 2 и т. д. Если ввести время, это число будет представлять десятичную дробь, которая соответствует времени, прошедшему с полночи (число 0,00000000); таким образом, 12:00:00 (полдень) соответствует 0,50000000; а 23:00:00 — число 0,95833333 и т. д. До тех пор, пока вводимые данные отвечают имеющемуся формату даты и времени, программа будет переводить их в соответствующие числовые значения для даты и времени.

Если вы вводите данные в текстовом формате (впереди ставится апостроф) или импортируете их в рабочий лист в текстовом формате, вам самостоятельно придется позаботиться о переводе этих данных в числовые значения для даты и времени, чтобы в Дальнейшем можно было создавать формулы даты и времени, использующие эти данные. Изначально в Excel не установлено автоматическое распознавание европейского формата даты, в котором число предшествует месяцу и году, например, 6.11.1969 обозначает 6 ноября 1969 г., а не 11 июня 1969 г. При работе с электронными таблицами, в которых применяется европейская система записи дат, необходимо установить региональные настройки в Windows таким образом, чтобы формат Краткое представление даты (Short Date) в таких программах Windows, как Excel и Word, использовал форму краткой записи даты д/м/гг (день, месяц, год), а не установленную по умолчанию форму м/д/гг (месяц, день, год). Внести изменения в формат даты можно следующим образом. 1. Щелкните на кнопке Пуск панели задач, а затем — на элементе Панель управления. Откроется Панель управления в режиме Выбора категории (Category). 2. Щелкните на ссылке Дата, время, язык и региональные стандарты (Date, Time, Language and Regional Options). Откроется диалоговое окно Дата, время, язык и региональные стандарты. 3. В этом диалоговом окне щелкните на ссылке Изменение формата отображения чисел, даты и времени (Change the Format of Numbers, Dates and Times). Откроется диалоговое окно Язык и региональные стандарты (Regional and Language Options). Если Панель управления открывается в классическом виде (в котором доступ ко всем панелям осуществляется через их ярлыки), диалоговое окно Язык и региональные стандарты можно открыть гораздо проще, дважды щелкнув на его ярлыке на Панели управления. 4. Щелкните на кнопке Настройка (Customize), расположенной справа от поля с текущим установленным форматом. Как правило, вначале устанавливается формат Английский (English). Откроется диалоговое окно Настройка региональных параметров (Customize Regional Options). 5. В этом окне щелкните на вкладке Дата (Date). 6. Щелкните в поле со списком Краткий формат (Short Date Format) и введите в его текстовое поле новый формат для записи даты д/м/гг. Этот формат даты необходимо ввести вручную, поскольку в региональных установках для некоторых стран (например США) данный формат не включается автоматически в раскрывающийся список. После ввода вручную европейский формат записи даты становится элементом списка, и его можно использовать в дальнейшем. 7. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Настройка региональных параметров, а затем — на кнопке ОК, чтобы закрыть диалоговое окно Язык и региональные стандарты.

8. Закройте Панель управлений, щелкнув на кнопке Закрыть, расположенной в верхнем правом углу окна. Если вы изменили краткий формат даты в Панели управления, при следующем открытии Excel автоматически будет использоваться форма записи даты д/м/гг (так, например, 3/5/02 означает 3 мая 2002 года, а не 5 марта 2002 года). При работе с электронными таблицами, в которых применяется формат "месяц-день-год", не забудьте установить краткий формат даты в состояние м/д/гг в Windows 98 в диалоговом окне Свойства: язык и стандарты, а в Windows XP — в диалоговом окне Настройка региональных параметров. Также обязательно перезапустите Excel, чтобы произведенные изменения вступили в действие. Большинство формул даты, которые создаются в Excel, предназначены для вычисления количества дней или лет между двумя датами. В таких случаях строится простая формула, в которой из более ранней даты вычитается более поздняя.

Например, если в ячейке В11 находится дата 25.4.75, а в ячейке С11 — 3.6.02, для подсчета количества дней в интервале между указанными датами в ячейке D11 постройте следующую формулу: =С11-В11 В результате вычисления в ячейке D11 будет находиться число, равное количеству дней, прошедших с 25 апреля 1975 года по 3 июня 2002 года. Единственной проблемой является тот факт, что программа Excel применит формат даты, используемый для ячеек В11 и С11, к полученному результату, ивячейкеОП будет находиться значение 8.2.1927. Чтобы представить результат в виде целого числа, преобразуйте его в иной числовой формат.

Например, если использовать для ячейки D11 общий формат (просто нажав комбинацию клавиш (Ctrl+Shift+~)), результат будет представлен в более понятном виде: 9901. Если необходимо, чтобы результат отображал не количество дней, а число лет, разделяющих две даты, полученную разность следует поделить на число дней в году. Введите следующую формулу в ячейку Е11: =011/365.

Полученный результат 27,12603 можно округлить до 27, щелкнув на кнопке Уменьшение разрядности (Decrease Decimal), расположенной на панели инструментов Форматирование (Formatting) — вы должны добиться того, чтобы в ячейке оказалось необходимое значение. В некоторых электронных таблицах используются формулы, которые помогают вычислить промежуток времени между начальной и конечной датой. Предположим, существует таблица, в которой находится информация о времени начала и окончания работы почасовых служащих. Для начисления дневной и месячной зарплаты этим работникам необходимо вычислить количество часов и минут, прошедших за данный период времени. Чтобы определить длительность рабочего дня, необходимо построить формулу, в которой от времени окончания рабочего дня отнимается время его начала. Предположим, в ячейке В14 содержится время начала работы служащего, а в ячейке С14 — время окончания рабочего дня. Тогда для вычисления продолжительности рабочего дня необходимо в ячейке D14 создать следующую формулу: =С14-В14 В ячейке D14 будет находиться результат в формате времени, отображающий, какую часть от суток (24 часов) составляет рабочий день.

Если, например, в ячейке В14 находится значение 9:15 AM, а в ячейке С14 — 3:4 5 РМ, результатом формулы в ячейке D14 будет 6:30 AM Чтобы конвертировать полученный результат в формате времени в эквивалентное ему числовое значение, задайте для ячейки D14 общий формат ((Ctrl+Shift+~)). Тогда значение в ячейке примет вид 0,270833. Эта десятичная дробь показывает, какую часть от суток занимает рабочий день. Чтобы узнать, сколько длится рабочий день в часах, необходимо умножить полученную десятичную дробь на 24 (т. е. =D14*24), что после применения к ячейке общего формата ((Ctrl+Shift+~)) даст 6,5 часа.

Практическая работа №19

Тема: «Функции даты и времени в табличном процессоре Microsoft Office Excel 2003»

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

Основные теоретические сведения:

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

Представление даты и времени в Excel имеет одну особенность. Microsoft Excel хранит даты в виде последовательных чисел, с которыми можно производить вычисления: сравнивать, прибавлять, вычитать и т. д. По умолчанию дате 1 января 1900 года соответствует порядковый номер 1, а, например, 1 января 2008 года — 39448 (так как интервал между этими датами в днях равен 39 448). Microsoft Excel сохраняет время в виде десятичной дроби, так как время является частью даты. Поэтому в Excel, наряду с текстовым представлением даты и времени, существует и числовое представление.

Года Microsoft Excel интерпретирует следующим образом:

· Двухзначные значения года от 00 до 29 интерпретирует как годы с 2000 по 2029. Например, если вводится дата 28.05.19, Excel распознает ее как 28 мая 2019 г.

· Двухзначные значения года от 30 до 99 интерпретирует как годы с 1930 по 1999. Например, если вводится дата 28.05.98, Excel распознает ее как 28 мая 1998 г.

Функция СЕГОДНЯ() возвращает текущую дату компьютера в формате даты, а функция ТДАТА() – текущие дату и время компьютера. Определить номер дня недели поможет функция ДЕНЬНЕД(ДАТА_КАК_ЧИСЛО;ТИП), значение ТИП отсчёта недели может быть равно 1, 2 или 3, если этот отсчёт с Вс=1 до Сб=7 или с Пн=1 до Вс=7 или с Пн=0 до Вс=6 соответственно.

Определить номер месяца поможет функция МЕСЯЦ(ДАТА_КАК_ЧИСЛО). Функция ГОД(ДАТА_КАК_ЧИСЛО) возвращает год аргумента.

Рассчитать количество дней между двумя датами поможет функция ДНЕЙ360(НАЧАЛЬНАЯ_ДАТА;КОНЕЧНАЯ_ДАТА;МЕТОД).

ЗАДАНИЯ.

1. Откройте приложение Microsoft Excel.

2. На листе 1 в ячейку А1 установите текущую дату компьютера.

3. В ячейку А2 установите текущие дату и время компьютера.

4. Скопируйте значение ячейки А1 в ячейку В1. При необходимости увеличьте ширину столбца.

5. Установите числовой формат для ячейки В1 с нулевым числом десятичных знаков после запятой (Формат – Ячейки – Число). Это числовой формат сегодняшней даты.

6. В ячейку С1 введите значения даты вашего рождения и установите числовой формат с нулевым числом десятичных знаков для неё.

7. В ячейке D1 найдите количество дней между введёнными датами из ячейки С1 и В1.

8. В ячейке Е1 найдите количество ваших лет, разделив значение ячейки D1 на 365.

9. В ячейке С2 определите номер дня недели вашего дня рождения, используя значение ячейки С1 и ТИП 2.

10. В ячейке В2 определите номер текущего дня недели, используя значение ячейки В1 и ТИП 2.

11. В ячейке С3 определите месяц вашего дня рождения, используя значение ячейки С1.

12. В ячейке В3 определите месяц сегодняшней даты, используя значение ячейки В1.

13. Перейдите на Лист2.

14. В ячейку A1 введите следующую формулу, которая при помощи функции СЕГОДНЯ отображает текущую дату: ="Сегодня:"&ТЕКСТ(СЕГОДНЯ();"ДД ММММ ГГГГ"). В ячейку A2:A4 введите текст по образцу (см. рис.). В ячейки введите числа, которые соответствуют сегодняшнему дню, месяцу, году.

15. Для того чтобы из года (ячейка В2), месяца (ячейка ВЗ) и дня (ячейка В4) собрать дату, введите в ячейку В5 формулу =ДАТА(В2;ВЗ;В4) (Окно: Аргументы функции).

16. А чтобы из даты (ячейка В5) извлечь год, месяц и день, в ячейки В6, В7 и В8 введите соответственно следующие формулы: =Год:"&ГОД(В5), ="Месяц:"&МЕСЯЦ(В5), ="День:"&ДЕНЬ(В5).

17. Введите в ячейки C1 и С2 текст, а в ячейки D1 и D2 даты, выделив эти ячейки и измените тип данных. Для этого: Формат ячеек – Число – Дата – ЧЧ.ММ.ГГГГ (14.03.2001).

18. Предположим, вам надо посчитать продолжительность работы в днях между начальной (ячейка D1) и конечной датами (ячейка D2). Для этого можно ввести в ячейку D3 формулу =D2-D1+1. Но ведь нам надо знать количество дней в числовом формате. Тогда формула должна быть вот такой: =ТЕКСТ(D2-D1+1;"≠≠≠"). Здесь мы применяем автоматическое форматирование. А если мы хотим узнать, каким по порядку днем года является данная дата (ячейка D4) (см. как в образце)? Очень просто: в ячейку D5 введите формулу =D4-ДАТА(ГОД(04);1;0), которая находит разность между данной датой и 1 января того же года. В результате может получиться значение в виде даты, выделив ячейку D5 переведем в числовой формат. Формат – Ячеек – Число – Числовой тип и Знаков после запятой – 0.

19. Или вот такая задача: определить, является ли данная дата (ячейка D4) субботой или воскресеньем? Введите в ячейку D6 следующую формулу: =ЕСЛИ(ИЛИ(ДЕНЫНЕД (D4;2)=6;ДЕНЬНЕД(D4;2)=7);"Суббота или Воскресенье";»»). В ней применена функция ДЕНЬНЕД, которая возвращает номер дня недели заданной даты. Если указанная дата попала на субботу или воскресенье, то именно это и будет указано в ячейке, если нет, то не будет указано ничего.

20. Для определения текущего времени надо из текущих даты и времени, возвращаемых в числовом формате функцией ТДАТА, вычесть текущую дату (функция СЕГОДНЯ), а результат (ячейка В11) перевести в формат времени: =ТЕКСТ(ТДАТА()-СЕГОДНЯ();"ЧЧ:ММ:СС").

21. Введите в ячейки B12, B13, B14 значения часов, минут и секунд в соответствии с образцом (см. рис.). Чтобы из часов (В12), минут (В13) и секунд (В14) «собрать» время, введите в ячейку В15 формулу =ВРЕМЯ (В12;В13;В14). В ячейки A17 – A19, B17 – B19 введите данные в соответствии с образцом. А чтобы найти суммарный промежуток времени (В17, В18, В19), в ячейку В20 введите формулу =СУММ(В17:В19). Если суммарный промежуток времени, как для данных из диапазона D17:D19, больше 24 часов, то простого суммирования будет недостаточно. В этом случае результат надо отформатировать (D20), причем часовую составляющую заключить в квадратные скобки, как это сделано в формуле =ТЕКСТ(СУММ (D17:D19);«[Ч]:ММ»).

Дата добавления: 2014-10-17; просмотров: 1158; Нарушение авторских прав

Мы поможем в написании ваших работ!