Как сделать часы в эксель
Как сделать аналоговые часы в Excel используя стандартные инструменты для визуализации данных и формулы? Для реализации данной задачи можно использовать кольцевую диаграмму так же как при построении популярного типа диаграмм в инфографике – спидометра. Аналоговые часы в Excel можно использовать в качестве таймера. При анализе с учетом разницы во времени в разных временных зонах крупнейших фондовых бирж: New York, London, Tokyo, Sydney, Moscow и т.п.
Пример как из диаграммы спидометр сделать аналоговые часы в Excel
Сначала составим Техническое Задание (ТЗ) для, того чтобы сделать часы в Excel. Как говориться в кулуарах программистов без ТЗ результат ХЗ. Чтобы создать стильные аналоговые часы в программе Excel нам понадобиться:
- С помощью формул подготовить входящие данные для показателей: часов, минут и секунд.
- Четыре диаграммы для циферблата и стрелок.
- Кнопки управления часами: Start и Stop.
- Код макроса обновления данных.
Формулы для перевода времени в секунды, минуты и часы
Приступим к реализации задачи по пунктам ТЗ. В первую очередь подготавливаем исходные данные. Сначала создаем значения для секундной стрелки. Нам необходимо перевести значение полной текущей даты и времени в секунды:
Далее указываем неизменяемую статическую долю диаграммы, которая будет служить стрелкой. Соответственно: чем больше доля, тем больше ширина стрелки. А также формула для вычисления размера третьей доли диаграммы, которая своим соотношением к первой доли будет определять положение стрелки и на сколько ее нужно перевести:
Сумма всех значений при каждом пересчете формул (F9) постоянно должна быть равна числу 60 так как секунды считаются по шестидесятеричной системе счисления (позиционная система счисления по целочисленному основанию 60).
Готовым данные для минутной диаграммы. Для этого нужно перевести текущую дату и время в минуты. На основе текущих значений секунд составляем формулу для определения размера доли диаграммы (от числа 60), которая будет перед минутной стрелкой:
Используя в формуле значение секунд позволит нам определять точное положение минутной стрелки в режиме онлайн. Также указываем размер второй неизменяемой доли 0,1 и вводим формулу для вычисления размера оставшейся доли от 60-ти:
Также сумма всех значений в диапазоне B7:B9 равна числу 60.
И наконец осталось подготовить данные для часовой стрелки. Здесь необходимо перевести текущее значение даты и времени в часы. Сложность заключается в том, что в аналоговых часах минуты и секунды рассчитывается по шестидесятеричной, а часы по двенадцатеричной системе счисления. Поэтому придется использовать более сложную формулу для точного вычисления положения часовой стрелки на циферблате часов:
12;ЧАС(ТДАТА())-12;ЧАС(ТДАТА()))+(B7/60)+(B2/3600)' >
Эту формулу достаточно легко прочитать. Если количество часов (получено функцией =ЧАС()) от текущей даты и времени (которые возвращает функция =ТДАТА()) составляет больше чем число 12, тогда переводим его в двенадцатеричную систему исчисления вычитая от этого количества число 12. А если меньше чем 12, тогда оставляем все как есть.
И последняя формула для часовой стрелки:
На этот раз сумма всех чисел в диапазоне B12:B14 равна числу 12. Данные подготовлены переходим непосредственно к рисованию диаграмм для аналоговых часов в Excel.
Кольцевая диаграмма для создания циферблата аналоговых часов
Аналоговые часы в Excel нарисуем или сконструируем из 4-х диаграмм. Сначала сделаем стильный циферблат. Для него нам не понадобятся исходные данные, поэтому переместите курсор Excel в любую пустую ячейку и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Кольцевая». После чего будет создана пустая диаграмма, но нам уже будет доступно дополнительное меню из которого выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные»:
В появившемся окне «Выбор источника данных» нажмите на кнопку «Добавить» для добавления первого ряда данных и нажмите ОК:
В результате мы создали рамку корпуса циферблата часов, переходим к делениям.
Далее создаем еще 2 рада данных. Для этого снова нажмите на кнопку «Добавить», но на этот раз в поле значение необходимо ввести массив из 12-ти единиц как показано ниже на рисунке:
Порядок расположения рядов должен быть такой же как на рисунке. При необходимости используйте стрелки (вверх и вниз) для управления расположением рядов на диаграмме.
Далее по прядку настраиваем все 3 ряда данных кольцевой диаграммы для дизайна циферблата. Выделите первый ряд двойным кликом левой кнопки мышки или выбрав инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Ряд1»-«Формат выделенного» и внесите следующие настройки в параметры ряда:
- «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Градиентная заливка»-«Цвета» (RGB коды цветов точек градиента: бирюзовый: 0;173;173, темно-сизый: 0;54;54) здесь же «Граница»-«Нет линий».
- «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Диаметр отверстия, в % от общего диаметра».
Не снимая выделения с «Ряд1» выберите инструмент для 3D оформления корпуса часов: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Стили фигур»-«Эффекты фигуры»-«Рельеф»-«Угол»:
Корпус аналоговых часов полностью готов!
Теперь делаем настройки для второго ряда. Он послужит нам главным циферблатом. Выделите второй ряд2 таким же способом как вы выделяли первый и внесите следующие настройки в параметры ряда:
- «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Сплошная заливка»-«Цвет»-Белый. Здесь же «ГРАНИЦА»-«Сплошная линия»-«Ширина»-0,25пт.
- «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Угол поворота первого сектора»-15 градусов.
Не снимая выделения со второго ряда данных добавим цифры на циферблат. Для этого нажмите на кнопку плюс возле диаграммы и из выпадающего меню отметьте опцию «Подписи данных» за одно снимите галочку с опции «Легенда»:
По отдельности выделяйте каждую подпись данных ряда и пока не снято выделения вводите в строку формул соответствующую цифру, а после для подтверждения нажимайте на клавишу Enter. Пока полностью не заполните весь циферблат всеми 12-ю цифрами.
Переходим к оформлению третьего ряда. Выделите Ряд3 на диаграмме и указываем только другой цвет заливки: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Сплошная заливка»-«Цвет»-RGB: 225;239;255. После чего также, как и для второго ряда добавляем метки «Подписи данных» таким же способом, как и в предыдущем примере описанном выше:
На этот раз при поочередном выделении каждой метки в строку формул вводим одну и туже внешнюю ссылку: =Часы!$D$1. Во всех диаграммах Excel всегда используются только внешние ссылки на листы. Слово перед восклицательным знаком «Часы» - это имя листа. В ячейке D1 на которую ссылается ссылка находиться функция =СИМВОЛ(149), которая возвращает символ «•» по коду 149. При желании можно ввести в ячейку D1 любой другой символ или функцию с другим кодом символа для отображения на часах.
Как сделать стрелки для аналоговых часов в Excel
Далее создаем стрелки для аналоговых часов в Excel. Для этого нам понадобиться создать еще 3 диаграммы. Чтобы создать первую стрелку выделите диапазон ячеек A2:B4 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Круговая»:
На диаграмме не видно второй доли «Ширина», но о том что она там присутствует свидетельствует легенда. Сейчас необходимо отформатировать диаграмму таким образом, чтобы была видна только лишь одна вторая доля, которая и послужит секундной стрелкой.
Сначала убираем цвет заливки и линию ряда диаграммы, щелкнув двойным щелчком мышки по ряду и выбрав инструмент: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Нет заливки» и здесь же «ГРАНИЦА»-«Нет линий»:
Все доли на диаграмме находятся на одном и том же ряду. Поэтому чтобы получить доступ ко второй (невидимой) доли следует воспользоваться легендой диаграммы. Выбираем мышкой только лишь один элемент легенды «Ширина», а затем двойным щелчком по нему вызываем: «Формат элемента легенды»-«ПАРАМЕТРЫ ЭЛЕМЕНТА ЭЛЕГЕНДЫ»-«ЗАЛИВКА»-«Цвет»-Красный и здесь же «ГРАНИЦА»-«Сплошная линия»-«Цвет»-Красный:
Теперь убираем все лишние элементы на диаграмме, нажав на кнопку плюс «+» с правого края и сняв все галочки из выпадающего меню:
Секундная стрелка – ГОТОВА! Далее таким же образом создаем часовую и минутную стрелку выполняя те же самые действия за исключением выбора цвета – черный. Кроме того, для всех троих диаграмм необходимо сделать прозрачный фон. Для этого делаем на каждой из них двойной щелчок по фоновой области диаграммы и выбираем инструмент: «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«ЗАЛИВКА»-«Нет заливки»:
Следующий шаг, как не сложно догадаться – это наложение стрелок на циферблат аналоговых часов в Excel:
Полезный совет! Для удобной работы с несколькими графическими объектам на рабочем листе Excel, хорошо использовать инструменты для выделения и расположения объектов из дополнительного меню: «ФОРМАТ»-«Упорядочение»-«Область выделения» и здесь же «Выровнять»-«По центру» и «Выровнять»-«По середине».
Создадим фигуру овал для последней детали часов, выбрав инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Овал»:
Придадим фигуре цвет и 3D дизайн гармоничный для наших аналоговых часов. Для этого сначала выберите инструмент из дополнительного меню: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Заливка фигуры»-Код цвета RGB: 0; 173; 173, а потом здесь же выберите «Эффекты фигуры»-«Затовка1»:
Выполняем финальные настройки по конструкции аналоговых часов в Excel:
- Накладываем фигуру на часы.
- Выделяем все элементы: «СРЕДСТВА РИСОВАНИЯ» или «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Упорядочение»-«Область выделения».
- Группируем: «ФОРМАТ»-«Упорядочение»-«Группировать».
И наслаждаемся готовым результатом!
Кнопки управления часами
Перед написанием кода макроса добавим на лист еще 2 элемента управления аналоговыми часами: Кнопка 1 – ButtonStart и Кнопка 2 – ButtonStop.
Для этого создадим две овальных фигуры, как и в предыдущий раз через меню: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Скругленный прямоугольник». Но на этот присвоим им оформление дизайна из коллекции предустановленных стилей Excel. Для кнопки ButtonStart выберите: «СРЕДСТВА РИСОВАНИЯ»-«Стили фигур»-«Средний эффект – Зеленый, Акцент 6». А для кнопки ButtonStop «Средний эффект – Оранжевый, Акцент 2»:
Макрос для автоматического перевода стрелок часов в Excel
Конструкция и дизайн аналоговых часов готов, осталось лишь запустить механизм. Если на данном этапе разработки мы будем периодически нажимать клавишу F9 или выбирать инструмент: «ФОРМУЛЫ»-«Вычисления»-«Пересчет» все формулы на листе будут автоматически пересчитываться и обновлять свои возвращаемые значения. Следовательно, будут переводиться стрелки часов. Значит нам нужен макрос, который будет выполнять данную функцию с периодичностью таймера в одну секунду.
Все готово для создания макросов в Excel, выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11. В появившемся окне редактора макросов «Microsoft Visual Basic for Applications» создайте модуль выбрав опцию из меню: «»
Двойным щелчком левой кнопкой мышки перейдите в модуль и вставьте в него ниже приведенный код сразу для двух макросов Start_Clock и Stop_Clock:
Sub Start_Clock()
ActiveSheet.Shapes( "ButtonStart" ).Shadow. Type = msoShadow30
ActiveSheet.Shapes( "ButtonStop" ).Shadow. Type = msoShadow21
Dim sh As Worksheet
Set sh = ActiveSheet
X:
VBA. DoEvents
If sh.Range( "N1" ).Value = "Stop" Then Exit Sub
Application.Calculate
GoTo X
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Range( "N1" ).Value = "Stop"
ActiveSheet.Shapes( "ButtonStart" ).Shadow. Type = msoShadow21
ActiveSheet.Shapes( "ButtonStop" ).Shadow. Type = msoShadow30
Осталось подключить макросы к соответственным нашим кнопкам для управления часами. Делаем щелчок правой кнопкой мышки по кнопке Start и из появившегося контекстного меню выбираем опцию «Назначить макрос». В появишвемя окне «Назначить макрос объекту» выбираем соответсвеное имя макроса «Start_Clock» из списка и нажимаем на кнопку ОК:
Такие же действия выполняем для кнопки Stop, только для нее следует из списка макросов выбрать имя Stop_Clock – соответственно.
Все ГОТОВО для запуска часов! Теперь при нажатии на кнопку Start будет выполняться код макроса Start_Clock, а при клике на кнопку Stop выполнится макрос Stop_Clock.
По такому же принципу можно создавать аналоговые часы самого разного дизайна. В программе Excel можно использовать много разных инструментов для оформления стиля диаграмм:
В новых версиях Excel ваше воображение ничем не ограничено. Более того присутствует много коллекций с предустановленными стилями оформления созданными разными дизайнерами в Microsoft, которые существенно сокращают время разработки визуализации данных. Научившись делать в Excel аналоговые часы, вы с легкостью сможете создать любую диаграмму типа спидометр для инфографики или визуализации данных в отчетах.
Биржевые часы в Excel с разницей во времени
Как сделать биржевые часы для текущего времени фондовых бирж на разных континентах мира в принципе уже понятно. Нужно лишь посчитать разницу во времени в Excel. Если надо сделать набор часов для крупнейших мировых бирж, которые находятся в городах на разных часовых поясах: NEW YORK, LONDON, TOKYO, SYDNEY, MOSCOW. В формулу часовой стрелки следует добавить операции для вычисления разницы во времени. Например, у Вас на компьютере отображается московское время тогда разница во времени города Москва и Нью-Йорк равна -7 часов. Формула вычисления количества часов для часовой стрелки приобретает следующий вид:
12;ЧАС(ТДАТА()-(7/24))-12;ЧАС(ТДАТА()-(7/24)))+(B12/60)+(B7/3600)' >
Как видно к функции возвращающей текущую дату и время =ТДАТА() добавилась операция вычитания -(7/24). Чтобы вычитать от значения даты и времени 7 часов нам нужно случала преобразовать данное число в формат времени разделив его на количество часов в сутках 24. О правилах математических операций со временем в Excel читайте: Как сложить время и число часов или минут в Excel
Сегодня темой поста будет вопрос одного из подписчиков: "Как вставить в ячейку таблицы время?" Вопрос требует некоторого пояснения. Нам требуются полноценные часы, которые будут отображать текущее время на компьютере.
Пришедшие поначалу в голову формулы СЕГОДНЯ() , ДАТА() и ВРЕМЯ() здесь не помогут, так как они будут оставлять "отпечатки" сегодняшних дней, дат, часов и секунд на момент ввода формулы. Также формулы не поддерживают автоматическое обновление.
Выход один - будем использовать макросы.
Пройдёмся по этапам создания нашей процедуры:
- Создадим новую книгу в Excel;
- На вкладке "Разработчик" жмём кнопку VisualBasic;
- Выполним команды Insert -> Module.
Появится пустое окно куда нужно поместить следующий макрос:
Dim varNextCall As Variant
'записываем в ячейку текущее время
Cells(1, 1).Value = Now
'записываем в varNextCall время, когда вызвать макрос в следующий раз (через 1 секунду)
varNextCall = TimeSerial(Hour(Now), Minute(Now), Second(Now) +1)
'уведомляем программу в вызове макроса
Application.Ontime varNextCall, "UpdateTime"
Мы создали макрос записи обновления времени, который поместит в ячейку А1 текущее время, его можно будет вызвать сочетанием Alt+F8 или щёлкнуть по кнопке "Макросы".
Следующим шагом будет сохранение книги. Обязательно сохранить книгу с поддержкой макросов (.xlsm)!
После выполнения макроса увидим вот такой результат:
В ячейке отображается дата и время (по компьютеру), в строчке формул также добавляются секунды для более удобного просмотра.
В этом примере есть также один минус - как только мы закроем книгу, работа макроса прекратится. Для того, чтобы избежать этого мы прикрутим к макросу автоматический старт (как и в случае с КУРСАМИ ВАЛЮТ).
- Щёлкаем кнопку VisualBasic;
- В области Project два раза кликаем строчку "Эта книга";
- В появившемся окне в поле "Object" выбираем "Workbook" и в поле "Procedure" название процедуры "Open";
- Между появившихся строчек вводим вызов нашего макроса - Call UpdateTime.
Макрос можно переместить в любую ячейку. Теперь даже при закрытии/открытии у нас будет точное время!
Буду рад всем оставленным отзывам и комментариям! Ссылка на файл примера ниже:
Если остались вопросы посмотрите обучающее видео:
Мой предыдущий пост EXCEL для чайников.1.ВПР содержал несколько грубых педагогических ошибок. Почитав комментарии и сделав выводы, я решил двигаться дальше, побольше, так сказать, разъясняя. Готовьтесь, пост будет еще длиннее.
Сегодня я расскажу вам о времени. Ученые и философы испокон веков спорят о его происхождении, и даже о его существовании. В Excel оно точно есть и работать с ним можно и нужно. Итак, что же такое время в Excel? Возьмем число 42997,635216. Что оно может означать? Человек, работающий с датами в Excel, сразу поймет подвох. Дело в том что это дата и время, в Excel, взятые на момент написания поста функцией =ТДАТА() в формате обычного числа. Про форматы поговорим отдельно, сначала нужно разобраться с представлением дат и времени: 42997 – это количество дней, начиная с 1 января 1900 года, (так что если вы историк то работать с датами до 1900 года придется как обычным текстом, без вычислительных выкрутасов), Стоит так же отметить, что сейчас работать с датами можно вплоть до 31 декабря 9999 г. (тут писатели фантасты печально вздохнули и полезли за калькуляторами). Дробная часть 0,635216 это время от целых суток, то есть 1 – это 24 часа. Давайте проверим за Excel, все ли правильно он посчитал: 0,635216*24=15,245184 (15 это часы); 0,245184*60=14,71104 (14 это минуты); 0,71104*60=42,6624 (43 это секунды, округляем до целого). Теперь переводим ячейку с числом 42992,57046 в формат даты и времени и получаем 19.09.17 15:14:43, хотя функция =ТДАТА() уже изрядно убежала за время нашего расчета. Поверьте, нет смысла проверять за Excel. Не нужно тратить на это драгоценное время, он все считает правильно, ошибка может быть только по другую сторону монитора. Многим это покажется смешным, но у меня есть знакомые, которые проверяют Excel на калькуляторе.
Понимание того что целая часть - это дни, а дробное - это часы, минуты, и секунды, очень важно. Функция =ТДАТА() прекрасна и опасна. Ведь она выполняет расчеты относительно текущего времени, и, если это не нужно и об этом забыть, цифры в таблице поплывут. Они будут плыть пока мы не закрепим дату. Нужно взять ячейку с формулой, войти в режим редактирования и нажать F9, либо копировать её и вставить обратно в туже ячейку как значение. Ход времени в таблице остановится, данные перестанут обновляться.
ТДАТА( ), СЕГОДНЯ( ) – текущее дата и время в первом случае, и дата без времени во втором.
ВРЕМЗНАЧ(“Текст”), ДАТАЗНАЧ(“Текст”) – переводит время либо дату из текста в числовой формат;
ДЕНЬНЕД(Дата, Тип) – выдает номер дня недели (наш тип недели 2, американской 1).
КОНМЕСЯЦА(Дата; Число_Месяцев) – это дата последнего дня месяца со смещением на нужное количество месяцев
ДАТАМЕС(Дата; Число_месяцев) – передвигает эту же дату на нужное количество месяцев вперед или назад
НОМНЕДЕЛИ(Дата; Тип) – номер недели с начала года (тип как в ДЕНЬНЕД)
РАБДЕНЬ(Дата; Количество; Праздники) – дата, которая будет или была через заданное количество дней (учитывая или нет праздники)Праздники задаются диапазоном ячеек
РАБДЕНЬ.МЕЖД(Дата; Количество; Выходной; Праздники) – то же самое, но с расширенной настройкой выходных данных. Можно задать строкой где 0-это рабочий день, 1-это выходной, на пример нормальная рабочая неделя выглядит так “0000011”
ЧИСТРАБДНИ (Дата1; Дата2; Праздники) – возвращает количество рабочих дней между 2 датами (с праздниками или без них)
ЧИСТРАБДНИ.МЕЖД (Дата1; Дата2; Выходной; Праздники) – то же самое, но с произвольным выбором выходных дней. (см. РАБДЕНЬ.МЕЖД)
ДНЕЙ360(Дата1; Дата2) – “Функция ДНЕЙ360 возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать месяцев по 30 дней). Эта функция используется для расчета платежей, если система бухгалтерского учета основана на двенадцати 30-дневных месяцах.” © - взял из справки, в бухгалтерии не силен, ничего добавить не могу, кроме того, что это как-то связано с расчетом равномерности платежей в течении года. В общем, эти бухгалтера даже в году умудрились спереть 5 дней.
ДОЛЯГОДА(Дата1; Дата2; Базис) – это доля года между двух дат. Базис равен 1, если хотите считать по фактическим датам. В противном случае данное значение варьируется от 0 до 3, выбирайте то, что нужно, согласно пояснениям, содержащимся в справке.
Теперь давайте разберемся, как это работает. В столбце А я напишу формулу, а в столбце В, С, D я напишу результат этой формулы в разных форматах, в столбце E напишу комментарии.
Время на работе (ч) =(C2-B2)*24 – тут мы вычитаем дату прихода из даты ухода, из суток переводим в часы, все просто.
Время на работе в рабочие дни (ч) =(ЧИСТРАБДНИ(B2;B2)*(C2-B2))*24 тут мы учитываем, был ли день рабочий. Я обнаружил, что если применить ЧИСТРАБДНИ с указанием одного дня эта функция в случае рабочего дня выдаст 1 и в случае выходного - 0, далее все как в формуле выше.
Опоздание (мин) =ЕСЛИ(ЧИСТРАБДНИ(B2;B2);ЕСЛИ(B2-ОКРУГЛВНИЗ(B2;0)<$K$1;"";ОКРУГЛ(((B2-ОКРУГЛВНИЗ(B2;0))-$K$1)*24*60;0));""). Также, как и выше, мы проверяем рабочий ли у нас день. Затем (это мое любимое) вычисляем время прихода сотрудника, без учета даты. Для этого я отнимаю из даты со временем значение той же даты со временем округленное вниз до целого значения с помощью функции ОКРУГЛВНИЗ. Выражение B2-ОКРУГЛВНИЗ(B2;0) у нас будет иметь значение 8:42 в формате времени, то есть время прихода сотрудника. В принципе мы могли бы написать =ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2)), это аналогичное решение, которое собирает время из значений часов, минут и секунд, но первое решение мне нравится больше. Затем сравниваем это время с временем начала рабочего дня, если оно меньше - оставляем ячейку пустой (“”), если же больше - считаем что сотрудник опоздал и высчитываем опоздание в минутах: из времени фактического прихода отнимаем время начала рабочего дня и умножаем на 24 и на 60, чтобы получить минуты, затем округляем полученный результат до целого значения. В случае же если день выходной, то логическое условие функции ЕСЛИ не выполняется и ячейка остается пустая.
Ранний уход (мин) =ЕСЛИ(ЧИСТРАБДНИ(C2;C2);ЕСЛИ(C2-ОКРУГЛВНИЗ(C2;0)>$K$2;"";ОКРУГЛ(($K$2-(C2-ОКРУГЛВНИЗ(C2;0)))*24*60;0));""). Тут все аналогично предыдущему, за исключением того что учитывается время ухода, которое должно быть больше времени окончания рабочего дня.
Вот и все что я хотел рассказать про время, пост получился длинноватым. Надеюсь, Вы меня поняли. Буду думать, о чем рассказать в следующий раз. Помните, сначала Вы работаете в Excel, потом Excel работает за Вас!
В профессиональной работе с электронными таблицами нередко приходится взаимодействовать с датой и временем. Без этого не получится обойтись. Поэтому научиться работать с данными этого типа сам Бог велел. Это поможет сэкономить огромное количество времени и предотвратить множество ошибок во время работы с электронными таблицами.
К сожалению, много новичков не знает, каким образом обрабатываются данные. Поэтому перед тем, как рассматривать этот класс операций, нужно провести более детальный ликбез.
Как представляется дата в Excel
Обработка информации о дате осуществляется, как о количестве суток с 0 января 1900 года. Да, вы не ошиблись. Действительно, с нулевого числа. Но это необходимо для того, чтобы была точка отсчета, чтобы уже 1 января считалось цифрой 1 и так далее. Максимально поддерживаемое значение, обозначающее дату – 2958465, что в свою очередь являет собой 31 декабря 9999 года.
Этот метод дает возможность использовать даты для расчетов и формул. Так, Excel дает возможность определить количество суток между датами. Схема проста: из одного числа вычитается второе, а потом полученное значение переводится в формат даты.
Для большей наглядности, вот таблица, где показаны даты с соответствующими им числовыми значениями.
Чтобы определить количество дней, которое прошло от даты А до даты B, необходимо от последней отнять первую. В нашем случае это формула =B3-B2 . После ее ввода результат оказывается следующий.
Важно обратить внимание, что значение представлено в днях, потому что мы выбрали для ячейки формат, отличающийся от даты. Если бы мы выбрали изначально формат «Дата», то результат был бы таким.
Важно обратить внимание на этот момент в своих расчетах.
То есть, для отображения правильного порядкового номера, полностью соответствующего дате, надо использовать какой-угодно формат, помимо даты. В свою очередь, для того, чтобы число превратить в дату, следует выставить соответствующий формат.
Как представляется время в Excel
То, как представляется время в Excel, немного отличается от даты. За основу берется день, а часы, минуты, секунды – это его дробные части. То есть, 24 часа – это 1, а любое более мелкое значение рассматривается, как ее доля. Так, 1 час – это 1/24 дня, 1 минута – 1/1140, а 1 секунда – 1/86400. Наименьшая доступная в Excel единица времени – 1 миллисекунда.
Аналогично датам, этот способ представления дает возможность осуществлять расчеты с временем. Правда, здесь одна вещь неудобна. После расчетов у нас получается часть суток, а не количество дней.
На скриншоте указаны значения в числовом формате и формате «Время».
Методика расчета времени аналогична дате. Нужно от более позднего времени отнять более раннее. В нашем случае это формула =B3-B2 .
Так как у ячейки B4 сперва был Общий формат, то по окончанию введения формулы он сразу меняется на «Время».
Excel в работе с временем выполняет обычные арифметические операции с числами, которые потом переводятся в знакомый нам временной формат.
Формат дат и времени
Насколько мы знаем, даты и время могут храниться в разных форматах. Поэтому нужно знать, как правильно вводить их, чтобы форматирование было правильным.
Конечно, можно использовать порядковый номер дня или часть суток при вводе даты и времени, но такой подход очень неудобный. Кроме этого, придется постоянно применять к ячейке определенный формат, что только усиливает дискомфорт.
Поэтому Excel дает возможность указывать время и дату разными способами. Если применить один из них, то программа сразу информацию конвертирует в соответствующее число и применяет к ячейке правильный формат.
В таблице ниже вы можете ознакомиться с перечнем способов ввода даты и времени, поддерживаемых Excel. В левой колонке перечислены возможные форматы, а в правой – как они будут отображаться в Excel после преобразования. Важно отметить, что если не указывается год, автоматически присваивается текущий, который выставлен в операционной системе.
На самом деле, способов отображения значительно больше. Но и этих достаточно. Также конкретный вариант записи даты может отличаться в зависимости от страны или региона, а также настроек операционной системы.
Произвольное форматирование
Во время работы с ячейками пользователь может сам определять, какой будет формат. Он может сделать так, чтобы отображалось только время, месяц день и так далее. Также есть возможность регулировать порядок формулирования даты, а также разделители.
Чтобы получить доступ к окну редактирования, необходимо открыть вкладку «Число», где найти опцию окно «Формат ячеек». В открывшемся диалоговом окне будет категория «Дата», в котором можно выбрать правильный формат дат.
Для применения определенного варианта форматирования к ячейке, необходимо выбрать нужный формат и кликнуть «ОК». После этого результат применится. Если не хватает форматов, которые предлагает Excel, то можно найти категорию «Все форматы». Там тоже есть множество вариантов.
Если никакой вариант не подходит, то всегда возможно создание своего собственного. Сделать это очень легко. Необходимо просто выбрать предустановленные форматы, как образец и выполнить такие действия:
Использование функций при работе с датами и временем
При работе с датами и временем пользователь может использовать более 20 самых разнообразных функций. И хотя кому-то этого количества может быть слишком много, все они могут использоваться для достижения определенных целей.
Чтобы получить доступ ко всем возможным функциям, необходимо перейти в категорию «Дата и время» группы «Библиотека функций». Мы же рассмотрим только некоторые основные функции, дающие возможность извлекать разные параметры из дат и времени.
Дает возможность получить год, который соответствует определенной дате. Как вы уже знаете, это значение может быть в пределах от 1900 до 9999.
В ячейке 1 видно дату, представленную в формате ДДДД ДД.ММ.ГГГГ чч:мм:cc. Это тот формат, который мы создали ранее. Давайте приведем в качестве примера формулу, которая определяет, сколько лет прошло между двумя датами.
При этом если более внимательно посмотреть, то окажется, что функция не вычислила полностью правильного результата. Причина кроется в том, что она использует только даты в своих вычислениях.
МЕСЯЦ()
С помощью этой функции можно выделить номер месяца, соответствующий определенной дате. Возвращает результат, колеблющийся в пределах от 1 до 12. Это число в свою очередь соответствует номеру месяца.
Аналогично предыдущим функциям, эта выдает номер дня, в определенной дате. Результат вычислений может колебаться от 1 до 31.
Как можно догадаться из названия, эта функция возвращает номер часа, который колеблется от 0 до 23.
МИНУТЫ()
СЕКУНДЫ()
Эта функция возвращает такие же значения, как и предыдущая, за тем лишь исключением, что ею возвращаются секунды.
ДЕНЬНЕД()
С помощью этой функции можно узнать номер дня недели, который используется в этой дате. Возможные значения от 1 до 7, но стоит учитывать, что отсчет начинается с воскресенья, а не понедельника, как у нас принято.
При этом с помощью второго аргумента эта функция позволяет настроить формат. Например, если передать значение 2 в качестве второго параметра, то можно настроить формат, при котором число 1 означает понедельник, а не воскресенье. Это значительно удобнее для отечественного пользователя.
Если во втором аргументе написать 2, то в нашем случае функция вернет значение 6, что соответствует субботе.
СЕГОДНЯ()
Эта функция очень проста: чтобы она работала, не требуется вводить никаких аргументов. Ею возвращается порядковый номер даты, которая выставлена на компьютере. Если ее применить к ячейке, для которой выставлен формат Общий, то автоматически он будет сконвертирован в формат «Дата».
ТДАТА()
Эта функция также не требует ввода аргументов. Действует таким же образом, как и предыдущая, только с датой и временем. Используется, если надо вставить в ячейку текущую дату и время, которые выставлены в компьютере. И точно так же, как и в предыдущей функции, при применении этой, ячейка автоматически конвертируется в формат даты и времени при условии, что до этого был выставлен формат «Общий».
Как предыдущая, так и эта функция при каждом пересчете листа автоматически изменяются, что дает возможность отображать самое актуальное время и дату.
Например, такая формула может определить нынешнее время.
= ТДАТА()-СЕГОДНЯ()
В этом случае формула определит долю суток в десятичном формате. Правда, придется к ячейке, в которую записывается формула, применить формат времени, если нужно отобразить именно время, а не число.
Эта функция имеет три аргумента, каждый из которых необходимо обязательно ввести. После расчетов этой функцией возвращается порядковый номер даты. Ячейка автоматически конвертируется в формат «Дата», если до этого у нее был «Общий» формат.
Аргумент «День» или «Месяц» можно делать как положительным, так и отрицательным. В первом случае дата увеличивается, а во втором – уменьшается.
Также можно использовать математические операции в аргументах функции ДАТА. Например, эта формула позволяет добавить 1 год 5 месяцев и 17 дней к дате, указанной в ячейке A1.
А такая формула дает возможность превратить текстовую строку в полноценную рабочую дату, которая может использоваться и в других функциях.
ВРЕМЯ()
Точно так же, как и функция ДАТА() , в этой функции есть три обязательных параметра – часы, минуты и секунды. После того, как ее использовать, в результирующей ячейке появится десятичное число, но сама ячейка будет отформатирована в формат «Время», если до этого у нее был формат «Общий».
По своему принципу работы у функции ВРЕМЯ() и ДАТА() очень много чего схожего. Поэтому особого внимания на ней акцентировать нет смысла.
Важно учесть, что эта функция не способна вернуть время, которое больше 23:59:59. Если получится больший, чем этот, результат, функция автоматически обнуляется.
Функции ДАТА() и ВРЕМЯ() могут применяться вместе.
На этом скриншоте ячейка D1, в которой использовались обе эти функции, имеет формат даты и времени.
Функции вычисления даты и времени
Всего есть 4 функции, позволяющие выполнять математические операции с датой и временем.
ДАТАМЕС()
С помощью этой функции можно узнать порядковый номер даты, отстающей на известное количество месяцев (или опережающей заданную). Эта функция принимает два аргумента: начальная дата и количество месяцев. Второй аргумент может быть как положительным, так и отрицательным. Первый вариант нужно указывать, если требуется вычислить будущую дату, а второй – если предыдущую.
КОНМЕСЯЦА()
Эта функция дает возможность определить порядковый номер последнего дня месяца, отстающего или опережающего заданную дату. Имеет такие же аргументы, как и предыдущая.
РАБДЕНЬ()
То же самое, что и функция ДАТАМЕС() , только отставание или опережение происходит на определенное количество рабочих дней. Синтаксис аналогичный.
Все эти три функции возвращают число. Чтобы увидеть дату, нужно сконвертировать ячейку в соответствующий формат.
ЧИСТРАБДНИ()
Это простая функция определяет количество рабочих дней между датой 1 и датой 2.
Читайте также: