|
Проект «Машина времени»
Часть 1: Вечные календари без макросов
Часть 2: Дополнения к вечным календарям
Часть 3: Часы
с цифровым интерфейсом
Часть 4: Часы с
циферблатом и стрелками
Часть 3
Макроуровень • VBA-минимум
• Часы •
Время часовых поясов
Часы
с будильником и звонками по расписанию •
Секундомер • Таймер обратного отсчета
Макроуровень
Решив глобальные проблемы временных промежутков на мегауровне,
обратимся к повседневному макро- и даже микроуровню, в котором
актуальны такие понятия, как час, минута, секунда и ее доли. В
электронных таблицах многие вопросы, касающиеся даты и времени,
можно решить, используя возможности форматирования. В предыдущем
материале этот потенциал почти не был задействован. Причина этого
кроется в амбициозности наших планов, выходящих за пределы формата
представления даты-времени в MS Excel. Что касается счета коротких
промежутков, то здесь таких препятствий не предвидится, поэтому мы
постараемся наверстать упущенное и использовать форматирование с
максимальной выгодой.
Предлагаемые далее модули нашего проекта, хотя и используют ресурсы
электронных таблиц, являются нетрадиционными для этого приложения, в
связи с чем нам придется обратиться к макросам. На многих такие
слова, как «программирование» и «макрос», действуют волшебным образом,
внушая панический ужас и парализуя волю. Многие проблемы предыдущих
модулей предлагаемого проекта можно было бы эффективно решить с
помощью макросов в Visual Basic for Application , но я сознательно
оберегал читателя от этого, затягивая в глубь темы и стараясь не растерять
контингент. Постараюсь и далее не злоупотреблять программированием,
а в необходимых случаях давать понятные объяснения, чтобы обеспечить
доступность как можно большему числу последователей.
VBA-минимум
Не буду утомлять читателя описанием особенностей и технологии работы
с макросами в интегрированном в Мicrosoft Office интерпретаторе
языка VBA, ограничившись самым необходимым минимумом:
Разрешить исполнение макросов:
– в Excel 2003 выбрать в меню: «Сервис - Макрос – Безопасность»
«Средний» или «Низкий», после чего закрыть файл и открыть снова;
– в Excel 2007 на панели «Предупреждение системы безопасности»
нажать кнопку «Параметры…» и выбрать «Включить это содержимое».
Ctrl+Break – прервать исполнение макроса.
Alt+F11 – переключение в редактор VBA и обратно.
В редакторе VBA:
Ctrl+R – вызов окна проводника проекта (если не отображается);
F7 – вызов окна кода (если не отображается).
На панели инструментов:
(Run) – выполнить макрос;
(Break) – прервать исполнение макроса;
(Reset) – восстановить начальное состояние после прерывания.
В окне кода:
' (апостроф) – часть строки после апострофа интерпретатором не
читается (используется для отмены действия операторов и
комментариев).
Остальное по ходу дела.
Часы
Речь идет о часах, которые показывают время в реальном режиме.
Существует возможность создания таких часов в MS Excel без
использования макросов на основе импорта внешних данных с настройкой
времени обращения к ним. Минимальный доступный интервал времени в
этом случае составляет одну минуту, а сам импорт используется не как
источник данных, а как генератор событий. Поработав с этим способом
достаточно много, я не хочу предлагать его для этой цели, так как
для генерирования событий с очень короткими интервалами лучше
использовать (поверьте моему опыту) все-таки макрос. Но, не будем
торопиться.
Для начала в ячейку В4 занесем формулу =ТДАТА() и через меню «Формат
– Ячейки…» на вкладке «Число» выберем «Время – Тип: *13:30:55».
Выделите любую другую ячейку и попробуйте нажимать клавишу «Delete».
В ячейке В4 отображается текущее время с точностью до секунды.
Заставить идти наши часы вручную можно и другими способами
(например, вводом данных), но каждый раз, чтобы обновить время, нам
придется выполнять те или иные действия с помощью клавиатуры или
мышки. Хоть и просто, но не очень удобно.
Чтобы избавиться от механических хлопот, воспользуемся небольшим
макросом, для чего перейдем в окно редактора VBA. Двойным щелчком по
значку «Лист1» в проводнике проекта откроем окно кода для этого
листа и введем (комментарии можно не набирать):
Dim st
As Boolean 'Флаг хода часов
Sub Clock()
Do While ActiveSheet.Name = Worksheets(1).Name
'Пока активен Лист1
If Not st
Then Exit Sub
'Если флаг не установлен, то выход из процедуры
Range("A1") = "." 'В ячейку A1 поставить точку
DoEvents 'буквально- делай событие
Loop
End Sub
Ниже создадим событийную процедуру, позволяющую запускать
подпрограмму Clock() щелчком мыши в любой ячейке. Для этого в
верхней части окна кода в списке объектов (пока у нас там видно «(General)»)
выберем «Worksheet» и рядом в списке процедур выберем «SelectionChange».
Появившуюся заготовку дополним, чтобы получилось:
Private Sub Worksheet_SelectionChange(ByVal Target
As Range)
st =
True 'Ход часов разрешить
Clock
'Вызвать процедуру
End Sub
Перейдя в окно Excel и щелкнув мышкой, убеждаемся, что часы пошли.
Предупреждение:
В случае проблем при прерывании хода часов используйте кнопку (Reset)
.
Чтобы иметь возможность остановить ход часов, создадим еще одну
короткую событийную процедуру, реагирующую на двойной клик, выбрав
из списков в верхней части окна кода для объекта Worksheet процедуру
BeforeDoubleClick и вставив в нее единственную строку:
st = False
'Ход часов запретить
В строке Range("A1") = "." процедуры Clock() правую часть можно
заменить на Now() (эквивалент функции ТДАТА()) и отформатировать
соответствующую ячейку, как это было сделано для В4. В этом случае
там также будет отображаться текущее время.
Если выделить ячейку В4, то в строке формул будет заметно, как от
частого обновления «вибрирует» ее содержимое. Да и в строке
состояния наблюдается такое же явление. Уменьшить частоту обновления
можно добавлением в процедуру Clock() дополнительного условия, чтобы
получилось:
If Int(Timer) = Timer
Then
'Если целое число секунд
Range("A1") = "."
End If
Использование функций ЧАС(В4), МИНУТЫ(В4) и СЕКУНДЫ(В4) позволяет
выделить из кода времени соответствующие части для работы с ними и
создания собственных форматов.
Функцию =ТДАТА() можно ввести в произвольную ячейку, к которой затем
применить любые форматы даты-времени, а с помощью форматирования
символов, границ и заливок добиться желаемого дизайна ваших
электронных часов.
Время часовых поясов
В наших странствиях в прошлое или будущее мы можем оказаться не
только в другом времени, но и в другом месте, а для этого нам
потребуется ориентировка по местному, точнее, по поясному времени.
Для этих целей придется построить еще один модуль проектируемой
машины времени.
Создадим счетчик и привяжем его к ячейке N3. Минимальное значение
установим равным 0, а максимальное – 24. Рядом в ячейке M3 поместим
формулу =N3-12.
Диапазон A4:Y4 заполним значениями от -12 до 12 с шагом 1.
Введем в ячейку A5 формулу:
=ТДАТА()+ЗНАК(A4)*ВРЕМЯ(ABS(A4);;)-ВРЕМЯ($N$3;;)+0,5
и растянем ее вправо до столбца Y. Для этого диапазона выберем
формат «Время: *13:30:55».
Ниже в ячейку A6 запишем ссылку =A5 на вышестоящую ячейку и
автозаполнением размножим ее до ячейки Y6. В этом диапазоне
установим формат «Дата: 14 мар».
Рис. 12. Фрагмент листа «Время часовых поясов».
Вооружившись картой часовых поясов, разместим в нижележащих ячейках
списки столиц и городов, находящихся в соответствующих зонах.
Дальнейшее оформление и усовершенствование зависит от вашего вкуса и
потребностей. Установив счетчиком номер своего часового пояса, можно
в режиме реального времени отслеживать, который сейчас час в том или
ином населенном пункте, и как новый день или Новый год шествует по
стране и нашей планете.
Часы с будильником и звонками по расписанию
Без таких существенных дополнений часть проекта, посвященная часам,
была бы неполной. Для начала решим вопрос со звонком. Найдя в
Интернете или записав с помощью микрофона подходящий звук,
преобразуйте его в формат wav. Затем выберите в программном меню
приложения MS Excel «Вставка – Объект…» и на вкладке «Новый»
диалогового окна выберите в списке «Тип объекта: Звукозапись». В
меню окна появившегося приложения «Звукозапись» перейдите: «Правка –
Вставить файл…» и укажите на файл с подобранным звуком. Закрыв окно
«Звукозапись» и выполнив двойной клик на появившемся значке с
изображением громкоговорителя, убедитесь, что звук воспроизводится.
Обратите внимание, что в строке формул в это время отображается
=ВНЕДРИТЬ("SoundRec";""), а в зоне «Имя» левой части этой строки –
«Объект 1» (у вас номер объекта может быть другим).
Перейдя в редактор VBA, дополним код нашего рабочего листа еще одним
макросом:
Sub Zvon()
Sheets("Лист1").Activate
Sheets("Лист1").Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
End Sub
Ваш рабочий лист может называться иначе, а номер объекта быть иным.
В этом случае внесите необходимые коррективы. Если перед внедрением
звука включить запись макросов, то по ее завершению текст этой
процедуры можно обнаружить в окне кода модуля, перейдя туда с
помощью проводника проекта. Тогда достаточно скопировать нужные
строки.
Теперь займемся самими часами. Объединим ячейки диапазона B8:K32 и
впишем сюда формулу =ТДАТА() или в процедуре Clock() отредактируем
строку, чтобы получилось:
Range("B8") = Now
В этом диапазоне установим формат ячейки:
– Время: 13:30;
– Выравнивание:
• по горизонтали: по правому краю;
• по вертикали: по центру;
– Шрифт:
• начертание: полужирный;
• размер: 160.
Объединив диапазон L14:N22, введем в него формулу для отдельного
отображения секунд:
=ТЕКСТ(СЕКУНДЫ(ТДАТА());"00")
Здесь выберем формат:
– Общий;
– Выравнивание:
• по левому краю;
– Шрифт:
• размер: 100.
Для установки времени звонка будильника создадим с помощью панели
«Формы» два счетчика для выбора нужных часа и минут, настроив
необходимые ограничения и связав их с ячейками G36 и I36. В ячейку
I35 введем формулу =ВРЕМЯ(G36;I36;) для перевода выбранного времени
в формат представления времени в Excel. Левее в H35 запишем
=ВРЕМЯ(ЧАС(B8);МИНУТЫ(B8);СЕКУНДЫ(B8)) с аналогичной целью. В ячейке
G35 это значение мы будем сравнивать с выбранным с помощью формулы:
=ЕСЛИ(H35=I35;1;0).
Чтобы подключить звонок, достаточно в команду проверки условия в
процедуре Clock() добавить еще одно условие:
If Range("G35") = 1
Then Zvon
Заставить звонить наши часы по расписанию (например, уроков) теперь
совсем несложно. Отведем для расписания диапазон от Q10 до…, скажем,
Q33. В эти ячейки будем вводить время звонков в принятом формате
времени, отделяя двоеточием часы от минут. Условие в ячейке G35
придется преобразовать к виду:
=ЕСЛИ(ИЛИ(H35=I35;H35=Q10;H35=Q11;…;H35=Q33);1;0)
Вместо многоточия придется вставить соответствующие равенства. При
необходимости расширьте или сократите диапазон. Значения времени в
нем будут выделяться, если воспользоваться условным форматированием.
Значок громкоговорителя можно уменьшить и, отправив его через
контекстное меню на задний план, спрятать за счетчик.
Задача почти решена, за исключением небольшого нюанса. На будильнике
обычно есть кнопка для отключения звонка. Конечно, можно выключить
звук на компьютере, но… Давайте доведем дело до конца.
Рис. 13. Часы с будильником и расписанием звонков
Создадим с помощью панели «Формы» кнопку под часами, обратив
внимание на ее имя в левой части строки формул.
Дополним в редакторе VBA код к нашему листу еще одним макросом
следующего содержания, сделав поправку на ваш номер кнопки:
Sub Vkl()
ActiveSheet.Shapes("Кнопка 2").Select
If Range("K36") = 0 Then
Range("K36") = 1
Selection.Characters.Text = "Выкл."
Range("G2").Select
Else
Range("K36") = 0
Selection.Characters.Text = "Вкл."
Range("G2").Select
End If
End Sub
Эта процедура привязывает кнопку к ячейке K36, меняя в ней при
нажатии значения 0 (выключено) на 1 (включено) и наоборот, а также
трансформирует надпись на самой кнопке. Ячейка G2 выделяется только
для того, чтобы снять выделение с кнопки. Вместо нее можно
использовать любую другую в пределах окна. Воспользовавшись
контекстным меню, привяжем к кнопке только что созданный макрос Vkl().
Осталось только дополнить условие вызова процедуры Zvon() в
подпрограмме Clock() так, чтобы получилось:
If Range("G35") = 1
And Range("K36") = 1
Then Zvon
Секундомер
Учет времени, прошедшего с момента нажатия кнопки, обычно ведется
секундомерами или таймерами прямого отсчета. Преобразовать часы в
секундомер совсем несложно. Скопируем на страницу кода нового листа
макросы Clock() и Vkl(). Процедуру генератора событий отредактируем,
приведя к виду:
Sub Таймер()
Range("D7") = Now
Do While Range("F4") = 1
Range("D6") = Now
DoEvents
Loop
End Sub
Здесь в ячейке D7 будем фиксировать момент нажатия кнопки, а в D6
отображать текущее время. В F4 – флаг нажатия кнопки «Пуск».
В макрос Vkl() перед строкой с оператором Else добавим Call Таймер,
а все адреса ячеек заменим на F4.
Создадим на рабочем листе кнопку и привяжем ее к этому макросу, не
забыв исправить в нем номер кнопки на номер только что появившейся.
Ограничим диапазон нашего счетчика от 0, 01 сек. до 1 часа. При
необходимости его не трудно расширить, внеся небольшие коррективы в
используемые формулы.
Время, прошедшее с момента старта, вычислим в ячейке D8 как разность
D6 и D7, установив для нее формат времени из списка «(все форматы):
"мм:сс"». Чтобы добиться большей точности, в ячейке D9 сделаем
ссылку на предыдущую и выберем для нее формат времени "30:55,2".
Собственный формат отображения получим, если в ячейку D10 введем
формулу:
=ТЕКСТ(МИНУТЫ(D8);"0")&":"&ТЕКСТ(СЕКУНДЫ(D8);"00")
Ею мы воспользовались неспроста. Уже на этом этапе, запуская
счетчик, можно убедиться, что функция СЕКУНДЫ использует правило
округления до ближайшего целого, что создает дополнительные проблемы
при получения сотых долей секунды. По этой причине нам придется в
ячейке D12 создать свою функцию для нахождения секунд, которая будет
работать по правилу отбрасывания дробной части:
=ОСТАТ(ЦЕЛОЕ(D8*3600*24);60)
Выше, в ячейке D11 найдем количество прошедших минут: =МИНУТЫ(D8)
А ниже, в D13 – число сотых долей секунды:
=ОСТАТ(ЦЕЛОЕ(D8*3600*24*100);100)
Предложенные формулы для секунд и их сотых долей в заявленном
диапазоне будут работать нормально, но при его расширении в одну или
другую сторону, могут вызывать переполнение. В этом случае лучше
воспользоваться более общими формулами:
=ОСТАТ(ЦЕЛОЕ((D8-ЦЕЛОЕ(D8))*3600*24);60)
=ОСТАТ(ЦЕЛОЕ((D8-ЦЕЛОЕ(D8)-ВРЕМЯ(ЧАС(D8);МИНУТЫ(D8);D12))*3600*24*100);100)
Отображение значения истекшего времени с точностью до одной сотой
секунды даст нам формула, которую запишем в ячейку D14:
=ТЕКСТ(D11;"0")&":"&ТЕКСТ(D12;"00"&","&ТЕКСТ(D13;"00"))
Возможен другой вариант таймера прямого отсчета с такой же
точностью, но более короткими формулами. Для его реализации в
процедуре Таймер() заменим функцию Now() на Timer(). Установим в
столбце D формат ячеек «Общий». Число минут, прошедших с момента
нажатия кнопки, вычислим с помощью функции:
=ЦЕЛОЕ(D8/60)
Количество секунд:
=ЦЕЛОЕ(D8)-D11*60
А сотые доли секунды найдем как:
=ЦЕЛОЕ((D8-ЦЕЛОЕ(D8))*100)
Формула для визуализации времени в ячейке D14 останется прежней.
Теперь, щелкая по кнопке «Пуск», можете определить, например, за
какое время вы выполняете двойной клик. Найдется масса других
ситуаций, где будет полезен только что созданный секундомер.
Рис. 14. Таймер прямого отсчета.
Таймер обратного отсчета
Мы уже научили часы подавать сигнал в установленный момент, но
нередко требуется отмерить определенный промежуток с указанием
времени, оставшегося до его истечения. Такая ситуация возникает,
например, при проведении различных конкурсов или тестировании.
Вдохновившись последними успехами, смело приступим к штурму и этой
преграды.
За основу возьмем первый вариант секундомера, ограничившись
точностью в одну секунду, так как обычно в таймерах обратного
отсчета большей не требуется.
Ввод минут и секунд для задания интервала времени организуем с
помощью счетчиков (по аналогии с часами), привязав их к ячейкам B4 и
D4 соответственно и ограничив значения от 0 до 59.
Величину заданного промежутка в формате времени Excel найдем в
ячейке D11:
=ВРЕМЯ(;B4;D4)
А ниже определим время остановки, для чего к времени старта из
ячейки D7 добавим значение интервала из D11. Тогда оставшееся время
вычислим в D13 как разность конечного (из D12) и текущего (из D6).
Для его отображения можно выбрать формат времени как с точностью до
секунд, так и до десятых долей секунды или и создать
пользовательский.
Большую наглядность течению времени придаст динамичная круговая
диаграмма соотношения оставшегося и прошедшего времени. Выделив
ячейки D8 и D13 с этими значениями, выберем с помощью мастера тип
диаграммы: круговую левую в верхнем ряду. В параметрах диаграммы
уберем все данные и снимем флажки. В формате области диаграммы
установим заливку прозрачную, а рамку невидимую. В формате рядов
данных границу лучше всего сделать невидимой, а цвета заливки
секторов подобрать по вкусу. Размер и размещение выберите сами.
Такая диаграмма появляется и изменяется только во время действия
таймера, но, во избежание проблем, выполним еще некоторые
усовершенствования.
Формулы в ячейках D8 и D13 дополним условиями проверки флага пуска,
приведя их соответственно к виду:
=ЕСЛИ(F4=0;0;D6-D7)
=ЕСЛИ(F4=0;0;D12-D6)
В макросе Vkl() заменим «Стоп» на «Сброс». Скорректируем в процедуре
Таймер() условие цикла:
Do While Range("F4") = 1
And Range("D12") > Now
Добавим в ее конец еще две строки:
Range("D7") = ""
If Range("F4") = 1
Then Vkl
Рис. 15. Таймер обратного отсчета.
При желании наш таймер можно оснастить гонгом, для чего придется
подобрать нужный звук и вставить его так, как мы это делали при
создании будильника, не забыв дополнить страницу кода
соответствующим макросом Gong(). Тогда включить его после истечения
заданного интервала времени можно будет путем вставки еще одной
строки в процедуру Таймер():
If Range("D12") <= Now
Then Gong
Продолжение
 
|
