|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Проект “Машина времени” в электронных таблицахМашина времени пока еще остается выдумкой фантастов. Но всякая выдумка есть не что иное, как информационная модель, в большей или меньшей степени соответствующая реалиям. Компьютерное моделирование уже позволяет нам совершать невероятные путешествия в прошлое (“Парк Юрского периода”) и в будущее (“Звездные войны”). Кроме того, осмелюсь утверждать, что машина времени для виртуальных странствий изобретена и всем известна довольно давно. Такие “машины” есть в каждом доме. Они бывают настенными, настольными, карманными, отрывными, перекидными, встроенными и т.д. Я думаю, вы уже догадались, о чем идет речь. В самом деле, именно обычный календарь на текущий год позволяет нам приоткрывать завесу на ближайшее будущее и возвращаться в не столь отдаленное прошлое. Понятно, что основным “навигационным” прибором и устройством ввода-вывода реальной машины времени должен быть именно календарь, причем календарь на любой год возможного диапазона наших путешествий во времени, и чем больше этот диапазон, тем лучше. Так как продолжительность календарного года и календарного месяца есть величина непостоянная, то отсчет времени для наших турне, вероятно, придется вести в сутках. А для этого необходимо знать, в какой день того или иного года мы попадем, переместившись во времени на заданное количество суток, и на сколько дней необходимо переместиться в прошлое или в будущее, чтобы оказаться там в нужную дату. Но, даже имея вышеуказанные возможности, мы рискуем заметно промахнуться, например, при путешествиях в не очень далекое прошлое. Допустим, мы решили предотвратить дуэль А.С. Пушкина с Дантесом и переместиться в 27 января 1837 года — дату этой трагедии, известную из исторических источников. Оказавшись в Петербурге в указанное время, мы обнаружили бы, что на календаре 15 января. Причина этого заключается в том, что даты до 1918 года в России официально принято указывать по юлианскому календарю, действовавшему в ту пору. Следовательно, для нашей машины времени необходимо иметь еще и юлианский календарь на любой год, а также возможность пересчета дат одного календаря в другой и обратно. Для ориентировки “на местности” неплохо бы иметь и календарь знаменательных дат. Большинство из них привязаны к тому или иному конкретному дню, но есть и такие, которые называются переходящими. Главной из них является Пасха. Решив вопрос с ее расчетом, мы сможем без особых проблем пополнить наш арсенал и таким “устройством” ввода-вывода. Решив глобальные проблемы, можно перейти к “мелочовке” — часам, минутам, секундам. Итак, в общих чертах план проекта готов. Для его реализации воспользуемся приложением MS Excel. Функции даты и времени Прежде всего несколько слов об особенностях представления даты и времени в MS Excel. И то и другое хранится в виде кода — числа, целая часть которого соответствует порядковому номеру данного дня, считая с 1 января 1900 года, а дробная — доле суток с полуночи до указанного времени, так что 1 сек. соответствует 1/(24*60*60) = 0,0000115740740740741. Используя формат ячейки “Дата” или “Время”, можно получить в ней соответствующее отображение. Для работы с данными такого типа в электронных таблицах предусмотрен целый набор функций, которые можно разделить на несколько категорий: · функции определения текущей даты и времени по системным часам; · функции преобразования даты и времени в формат кода даты-времени; · функции выделения из кода составных частей даты и времени без использования форматирования; · функции вычисления на основе одной или двух дат. Из последней категории в версиях, предшествующих MS Excel-2007, есть только две первые, но нам потребуется только первая. От применения же второй функции (вычисления количества дней между двумя датами) придется отказаться в силу приближенности ее результатов. В приведенной на с. 22 таблице указаны эквиваленты функций английской версии, которые могут пригодиться при работе с макросами, хотя в этом проекте мы будем следовать принципу обращения к ним только в тех случаях, когда задача не может быть решена традиционными методами электронных таблиц. Полный список всех функций MS Excel и их перевода можно найти в папке установки MS Excel по адресу C:\Program Files\Microsoft Office\OFFICE11\1049\ FUNCS.XLS. К рассмотренному списку добавим еще одну, которая прямо не присутствует в электронных таблицах, но есть в Visual Basic for Application (VBA) и может быть использована как альтернатива функциям времени на заключительном этапе нашего проекта. Это функция TIMER(), которая возвращает количество секунд и долей секунды, прошедших с полуночи до момента обращения к ней. В данном проекте нам придется воспользоваться еще некоторыми функциями, прежде всего логическими (для проверки условий), а также некоторыми текстовыми (для сервисного отображения результатов). Таблица 1. Функции даты-времени в Microsoft Excel
Календарь на месяц Для начала реализуем календарь на месяц наподобие того, что есть в операционной системе. Остановимся подробнее на технологии. Выделим лист и установим ширину колонок равной 2. Для дат отведем диапазон E8:J14 размером 7 ґ 6. По левому краю этого диапазона в ячейках D8:D14 проставим дни недели от понедельника до воскресенья, а еще левее в ячейках C8:C14 — порядковые номера дней недели от 1 до 7. Над диапазоном дат в ячейке E7 укажем порядковый номер месяца, а в J7 — количество дней в этом месяце. Год, на который создается календарь, занесем в ячейку G4.
Рис. 1. Промежуточный вариант оформления календаря на месяц Вариант 1 Теперь можно заняться датами. В ячейку E8 введем формулу: =C8-ДЕНЬНЕД(ДАТА($G$4;E$7;1);3) — и, схватившись за маркер автозаполнения, протянем ее до 14-й строки. Здесь ДАТА($G$4;E&7;1) переводит в код дату первого дня выбранного месяца данного года. Функция ДЕНЬНЕД с параметром 3 находит номер дня недели этой даты, считая, что понедельнику соответствует 0, а воскресенью 6. Таким образом, в строке, соответствующей первому дню месяца, появится 1, а ниже — следующие даты. Далее введем в ячейку F8 формулу =E8+7 и автозаполнением растянем ее сначала вниз до 14-й строки, а потом, не снимая выделения с блока F5:F14, вправо до столбца :). В результате должно получиться так, как на рис. 2.
Рис. 2. Пока ничто не скрыто Чтобы избавиться от недопустимых дат в первой, пятой и шестой колонках нашего календаря, воспользуемся условным форматированием. Выделив диапазон E8:E14, выберем в меню “Формат — Условное форматирование…” и в диалоговом окне в первом списке оставим “значение”, во втором выберем “меньше”, а в третье впишем “1”. Затем нажмем кнопку “Формат…” и на вкладке “Шрифт” следующего диалогового окна установим цвет “белый” (под цвет фона). То же самое в Excel-2007: — на вкладке “Главная” нажать кнопку “Условное форматирование”; — в ниспадающем меню выбрать “Правила выделения ячеек”; — в дополнительном меню выбрать “Меньше…”; — в диалоговом окне в первый список заносим “1”; — во втором списке выбираем “Пользовательский формат…”; — на вкладке “Шрифт” устанавливаем соответствующий цвет.
Рис. 3. Диалоговое окно условного форматирования
Рис. 4. Панель форм Для диапазонов I8:I14 и J8:J14 аналогично выберем значение “больше”, а в качестве верхней границы укажем ячейку J7, в которую было занесено максимальное число дней в месяце. Вариант 2 Наш календарь начинает приобретать приемлемый вид. Если же кому-то такой способ покажется иллюзионистским трюком, можно предложить другой вариант: без условного форматирования, но с использованием логических функций. Он несколько сложнее, но также результативен. В этом случае в ячейку E8 заносим формулу: =ЕСЛИ(C8-ДЕНЬНЕД(ДАТА($G$4;E$7;1);3)>0; C8-ДЕНЬНЕД(ДАТА($G$4;E&7;1);3);"") — которая выводит положительный результат, а отрицательный заменяет на пустой текст. Так как для следующей колонки предыдущий вариант не годится, то значение в ячейке F8 получим по формуле =E14+1, а в последующих ячейках этой колонки результат находим путем увеличения на единицу даты предшествующей ячейки. В третьей и четвертой колонках можно применить уже использованный способ увеличения на 7 значения слева. Далее лучше всего ввести в ячейку I8 формулу =ЕСЛИ(G8+14>$J$7;"";G8+14) и “растянуть” ее на все оставшиеся ячейки. Усовершенствования В нашем календаре есть один существенный недостаток: кроме года и месяца, каждый раз необходимо вводить еще и максимальное количество дней в этом месяце. Попробуем его преодолеть. Воспользуемся тем, что последний день месяца предшествует первому дню следующего месяца, код которого можно определить по формуле =ДАТА($G$4;E7+1;1). Напомню, что $G$4 — это год, а E7 — месяц создаваемого календаря. Занеся формулу =ДЕНЬ(ДАТА($G$4;E7+1;1)-1) в ячейку J7, получим число дней в месяце. В Excel-2007 для этих целей допустимо применить и другую формулу: =ДЕНЬ(КОНМЕСЯЦА(ДАТА($G$4;E7;1);0)) Для ускорения ввода номера месяца можно воспользоваться счетчиком. Отобразим панель инструментов “Формы” и, кликнув на ней по счетчику, выведем его на рабочий лист, очертив при нажатой левой кнопке мыши габаритный прямоугольник. Не снимая выделения, поместим счетчик в нужное место и подгоним его размеры. Затем в контекстном меню выберем “Формат объекта” и в диалоговом окне “Элемент управления” установим: — минимальное значение — 1; — максимальное значение — 12; — связь с ячейкой — E7. Аналогичную процедуру можно проделать и для автоматизации ввода года. При необходимости вывода названия месяца можно поступить следующим образом. В ячейке A5 написать “январь” и автозаполнением протянуть до L5, а в ячейку F6 вписать формулу: =ВЫБОР(E7;A5;B5;C5;D5;E5;F5;G5;H5;I5;J5;K5;L5), —
кото- Настало время заметать следы нашего “преступления”. В первую очередь скроем номера дней недели, для чего установим цвет текста в ячейках C8:C14 белым. Существуют и другие приемы для этого. Можно было обойтись и вовсе без них и также не единственным способом. Предлагаю читателю поэкспериментировать. Для обеспечения более респектабельного вида придется скрыть содержимое ячеек, содержащих номер месяца, количество дней в нем и названий месяцев. Что касается художественного оформления, то тут необъятный простор для фантазий. Эксперименты А теперь попробуем протестировать наше творение. Кажется, все замечательно. Наше путешествие в будущее вплоть до 9999 года ничем не омрачено. Все сходится: количество дней в месяце надлежащее, дни недели приходятся на соответствующие даты, нет проблем и с високосными годами, последовательность дней нигде не нарушается. Жаль только, что более отдаленные перспективы скрыты от нас “железным занавесом”. А как насчет прошлого? Дойдя до 1900 года, мы обнаруживаем первое несоответствие: в феврале — 29 дней, а по григорианскому календарю должно быть 28. Об этой ошибке, унаследованной от Lotus-1-2-3, можно прочитать в статье “Ошибка високосного года в Microsoft Excel” в газете “Информатика” № 21/2008. Ее устранение нарушило бы заведенный в электронных таблицах порядок счета дней и могло бы привести в некоторых случаях к непредсказуемым последствиям. Так что смиримся, но сделаем для себя вывод, что дни недели в нашем календаре для января и февраля 1900 года не соответствуют минувшей действительности. Попробуем переступить порог “неудачного” 1900 года в сторону более далекого прошлого. Есть результат! Но стоит ли ему доверять? При внимательном анализе бросается в глаза, что 1899 год по нашему календарю закончился во вторник, а следующий за ним год начался в воскресенье. Невольно вспоминается фантастическая повесть братьев Стругацких “Понедельник начинается в субботу”. Далее все гладко с чередованием дней, но проверка февралей в годы, оканчивающиеся двумя нулями, выдает неприятные результаты. Годы 1700-, 1300-, 900-, 500-, 100-й у нас оказываются високосными, а 1600-, 1200-, 800- и 400-й, наоборот, невисокосными. Так что же, “все врут календари”, как утверждал Грибоедов, или… Вообще-то мы забыли, что формат даты определен в Excel только в диапазоне от 1 января 1900 года до 31 декабря 9999 года. Тогда почему же наш календарь не воспринимает даты после верхней границы и реагирует на даты до нижней границы? Дело в том, что при определении года по коду даты-времени Excel автоматически добавляет 1900, а при переводе в код — вычитает из дат больших, чем 1900. Таким образом, в электронных таблицах 2009 год хранится как 109-й. Попробуйте ввести в ячейки формулы =ДАТА(109;04;1) и =ДАТА(2009;04;1) и сравнить результаты… Значит, наш календарь на 1899 год фактически был календарем на 3799 год, а календари на 100-, 400-, 500-, 800-й и т.д. на 2000-, 2300-, 2400-й и 2700 годы соответственно. Тогда понятно расхождение с високосными годами. Уходя в прошлое, мы оказались в будущем. Так что же, прошлое для нас еще более недоступно, чем будущее? Григорианский календарь на любой год Если бы не повторялись природные явления, то, вероятно, не было бы и календарей. Периодичность — основа любого календаря. Вам, возможно, приходилось встречаться с фактом, когда календарь на один год полностью идентичен календарю на другой год. Для некоторых лет такие совпадения могут происходить через 6, 11, 17, чаще — через 28 лет, но полный период григорианского календаря составляет 400 лет. То есть календари, на все годы отстоящие друг от друга ровно на 400 лет, совпадают с гарантией в 100%. В диапазоне доступных для MS Excel дат таких промежутков достаточно, так что можете проверить. Значит, чтобы создать календарь на любой год, нам надо найти ему соответствующий из выбранного периода. Перед нами открываются необъятные перспективы как в прошлое, так и в будущее. “Железный занавес” времени пал! Выберем в качестве базового промежуток от 2000 до 2399 года — близкий нам по времени и удобный для расчетов. Тогда, чтобы для любого года найти ему соответствующий, необходимо: — вычесть из номера года 2000; — найти остаток от деления на 400 получившегося числа; — прибавить к результату 2000. Так как 2000 кратно 400, то вычитание 2000 не сказывается на остатке, поэтому от первого шага можно отказаться. На третьем шаге, учитывая особенности представления формата даты в MS Excel, можно прибавить не 2000, а 100. Результат от этого не изменится. Кроме того, следует учесть, что 1-му году н.э., согласно принятой системе счета, предшествовал не нулевой год, а 1 год до н.э. Теперь смело приступим к усовершенствованиям. Чтобы выполнить работу с наименьшими издержками, поступим следующим образом. Выделите ячейку G4 (если вы по своей инициативе объединяли ее с соседними, то, предварительно, откажитесь от этого) и, схватившись за границу выделения, переместите ее на место ячейки D7. Все формулы, связанные с этой ячейкой, сразу же отреагируют на ваши действия и автоматически изменят адрес ссылки, что избавит нас от многих дополнительных хлопот. Если вы использовали счетчик для ввода года, то теперь он также будет связан с адресом D7, но нам этого не надо, поэтому, воспользовавшись контекстным меню и выбрав “Формат объекта…”, восстановите на вкладке “Элемент управления” связь с ячейкой G4, куда мы по-прежнему будем вводить дату. Что касается ячейки D7, то (я думаю, вы уже догадались) в ней в дальнейшем будет вычисляться год базового периода, соответствующий заданному, по формуле: =ОСТАТ($G$4;400)+2000+ЕСЛИ($G$4<0;1;0) Отображение значения в этой ячейке можно скрыть, выбрав цвет шрифта в соответствии с фоном, но в дальнейшем нам еще придется вернуться по этому адресу. Дополнительно, чтобы предупредить возможность некорректного ввода дат, выделим ячейку G4 и выберем в программном меню “Данные — Проверка…”. На вкладке “Параметры” установим: — Тип данных: Целое число; — Значение: не равно; — Значение: 0. На вкладке “Сообщение об ошибке”: — Вид: Останов; — Заголовок: Предупреждение; — Сообщение: Недопустимое значение. Кроме того, для ячейки G4 через пункт меню “Вставка” можно добавить примечание: “Годы до н.э. вводите со знаком минус”. Основные проблемы по созданию вечного календаря средствами Microsoft Excel подробно рассмотрены и решены, но наша реализация обладает ограниченным обзором в пределах одного месяца. Однако теперь, на основе предложенной технологии, не составит особого труда сделать календарь на все двенадцать месяцев года. Причем формулу для вычисления количества дней в месяце можно вводить только для февраля, а для остальных указать конкретные значения. Предоставим эту возможность читателю. Диапазон дат, для которых может быть создан календарь предложенными средствами, превосходит 50 млрд. лет как в прошлое, так и в будущее. Однако следует иметь в виду, что даже самый точный из всех ныне используемых — григорианский календарь допускает ошибку в одни сутки за 3300 лет по сравнению с реальной продолжительностью года и суток. Да и сами год и сутки не остаются константами на больших временных промежутках. Так что наше творение отражает лишь современные общепринятые правила григорианского календаря и не учитывает (да и не может учитывать) возможных будущих поправок к нему. Например, предложения не считать високосными годы, которые делятся на 4000, что значительно увеличило бы точность календаря. Кроме того, григорианский календарь впервые был введен в 1582 году, но переход на него в некоторых странах произошел только в XX веке. Значит, пускаясь в путешествие в очень далекое прошлое или будущее, правильно соотносите теорию и минувшую или будущую реальность, воспринимайте его прежде всего как виртуальное и не заблудитесь во времени. Юлианский календарь Официально в нашей стране (да и в большинстве других) используется григорианский календарь, однако православная церковь продолжает ориентироваться на юлианский, да и в отечественной исторической литературе (и не только) даты до 1918 года, как правило, указываются в соответствии с ним. В связи с этим нередко приходится обращаться к юлианскому календарю, изучая те или иные исторические события и соотнося их с текущим моментом. Порой возникает необходимость узнать, а в какой день недели происходило рассматриваемое событие, чтобы представить себе более полную картину происходившего. Многие исторические факты связаны с такими событиями, как солнечные и лунные затмения, точные даты которых современная наука позволяет вычислять с очень большой точностью. Так, например, историческая битва князя Игоря с половцами произошла через десять дней после солнечного затмения (“солнце, стоящее яко месяц”), описанного в летописях и “Слове о полку Игореве” и датированного астрономами 1 мая 1185 года по старому стилю. На какой день недели пришлась дата 11 мая и какому дню соответствует он в григорианском календаре? Чтобы ответить на эти вопросы, желательно иметь юлианский календарь на этот год. Однако система счета времени в MS Excel, и прежде всего функция ДЕНЬНЕД, явившаяся основой наших построений, предназначена для григорианского календаря.
Тем не менее ситуация эта не тупиковая. Ведь многие так и не знают, в чем же различие этих двух календарей, кроме смещения дат, так как другое обстоятельство этих отличий — увеличение смещения — происходит раз в 100, а то и в 200 лет. Так, в последний раз разница с 12 до 13 дней накопилась в 1900 году, а следующее приращение до 14 дней произойдет только в 2100 году. Но в отличие от григорианского юлианский календарь имеет значительно меньший период повторяемости, который составляет 28 лет, поэтому на отрезке григорианского календаря в сто лет не так уж сложно найти промежуток, полностью соответствующий юлианскому календарю с учетом сдвига. Так, отследив дни начала года с 2001-го по 2028-й ПнВтСрЧт СбВсПнВт ЧтПтСбВс ВтСрЧтПт ВсПнВтСр ПтСбВсПн СрЧтПтСб, мы находим, что 14 января (Новый год по старому стилю) приходится на те же дни в промежутке с 2013-го по 2040 годы. Таким образом, для создания юлианского календаря на любой год нам потребуется совсем немного. Выберем в программном меню “Правка — Переместить/скопировать лист…”. В появившемся диалоговом окне отметим флажок “Создавать копию” и нажмем кнопку “Ok”. Выделим ячейку D7, в которой мы вычисляли соответствующий год базового периода для григорианского календаря, и отредактируем имеющуюся там формулу, заменив ее на следующую, полученную после небольших преобразований: =ОСТАТ($G$4;28)+2004+ЕСЛИ($G$4<0;1;0) Здесь $G$4 — ссылка на ячейку, в которую вводится год. Возможно, что в вашем проекте адреса ячеек назначения будут отличаться от указанных. В этом случае внесите необходимые коррективы. Вот и все. Обещанный юлианский календарь готов. Дальнейшее его оформление может быть, например, таким, как на рис. 5. Для вычисления года от сотворения мира была использована формула: =СЦЕПИТЬ("(";ГОД(СЕГОДНЯ()+109)+5509;" год от сотворения мира)"), учитывающая, что в этом случае начало года приходилось на 1 сентября по старому стилю. Что касается сообщения о текущей дате, то эту более длинную формулу попробуйте сконструировать сами, воспользовавшись функциями СЦЕПИТЬ и ВЫБОР, а также функциями даты-времени. Пускаясь в путешествие в прошлое, необходимо учитывать, что юлианский календарь был введен в 45 г. до н.э., причем нынешнее правило определения високосных лет в нем действует только с 8 года н.э. Счет лет от Рождества Христова впервые был предложен в 525 году. Да и начало нового года не всегда приходилось на 1 января. Калькулятор пересчета дат Хорошо бы проверить надежность работы наших календарей на большой дистанции. Переместимся во времени к отправной точке наших календарей — 1 году нашей эры. Да… Что-то неладно! По григорианскому календарю 1 января пришлось на понедельник, а по юлианскому — на субботу. Результат, скажем прямо, неожиданный. Ведь должны же совпадать календари хотя бы в начале. Значит, где-то нами допущена ошибка? Не будем торопиться с выводами. Существуют формулы для пересчета дат григорианского календаря в юлианский и наоборот. Их можно найти в Интернете, например, по адресу http://www.astronet.ru/db/msg/1182321/node4.html. Воспользуемся ими для создания еще одной, ранее заявленной части нашего проекта — калькулятора для пересчета любых дат. После тщательного перевода формул в формат MS Excel, оптимизации ввода и преобразования результатов к удобному для восприятия виду можно получить примерно такой облик нашего “навигационного прибора”.
Рис. 6. Калькулятор пересчета дат Здесь многие формулы для промежуточных вычислений пришлось скрыть с целью обеспечения приличного дизайна, так что если вы воспользуетесь готовым проектом, то для обнаружения ячеек, работающих в “подполье”, попробуйте выделить весь лист. А для вскрытия связей между ними отобразите панель “Зависимости” и, выделяя ячейки с входными и выходными данными, пощелкайте по кнопкам “Влияющие ячейки” и “Зависимые ячейки”. У вас должно получиться следующее (см. рис. 7). Введя дату “1 января 1 года”, получаем, что этому дню григорианского календаря соответствует 3 января по юлианскому календарю. Результат пока непонятный, но в юлианском календаре на этот год, действительно, на 3 января приходится понедельник, как это было у нас в григорианском календаре. Попробуем разобраться, в чем дело. Если помните, 400 григорианских лет ровно на трое суток короче такого же количества юлианских. В 2000 лет содержится 5 раз по 400, значит, разница между юлианским и григорианским календарями должна составлять 3 * 5 = 15 дней. Фактически же в наше время григорианский календарь опережает юлианский только на 13. Дело в том, что основным днем для расчета Пасхи является день весеннего равноденствия. За время полного периода повторяемости дат этого праздника равноденствие перекочевало с 19 марта в 1 году н.э. на 23 марта в 532 году. В григорианском календаре день весеннего равноденствия приходится на одну и ту же дату. В качестве такой даты при введении нового календаря папой Григорием XIII в 1582 г. решено было выбрать среднее значение первого пасхального периода, т.е. 21 марта. Основным аргументом такого решения явилось то, что, как было известно из документальных источников, во время исторического Первого вселенского Никейского собора христианской церкви в 325 г. день весеннего равноденствия приходился на 21 марта. Это было удобно еще по одной причине: величина смещения в этом случае для того времени составляла 10 суток. Так что фактически время совпадения календарей приходится не на I, а на III век, точнее, на даты от 1 марта 200 года до 28 февраля 300 года. В этом можно убедиться, если ввести в созданные нами построители календарей даты из этого диапазона. Значит, полученные нами ранее результаты не свидетельствуют об ошибках, а, наоборот, подтверждают правильность наших построений.
Рис. 7. То же, но с отображением зависимостей и скрытых данных Осталось — прошло Каждому из нас приходилось считать, сколько дней осталось до или прошло после определенной даты. Обычно такая задача не вызывает особых затруднений, когда речь идет о расчетах в пределах месяца, но чем дальше отстоят даты от текущего момента, тем затруднительнее становятся вычисления. Для их облегчения в электронных таблицах есть даже функция ДНЕЙ360. Но ее практическая применимость вызывает большие сомнения, так как принципы, заложенные в ее основу, не позволяют получить точный результат даже на интервалах, превосходящих один месяц. Тем не менее система счета времени, принятая в MS Excel, дает возможность получить необходимые данные простым вычитанием дат, если они попадают в промежуток от 1 января 1900 года до 31 декабря 9999 года. Например, чтобы узнать, сколько дней осталось до открытия зимней олимпиады в Сочи, достаточно воспользоваться формулой =ДАТА(2014;2;7)-СЕГОДНЯ(). А если даты лежат за указанными пределами? Вспомним наш опыт преодоления преград с использованием периодичности григорианского календаря. Он выручит нас и в этом случае. Если даты отстоят друг от друга больше, чем на период, то можно найти разницу между соответствующими им датами в базовом периоде (мы выбрали от 2000 до 2399 года) и прибавить количество дней в периоде, умноженное на число периодов между ними. Формулу для вычисления года в базовом периоде, соответствующем данному, позаимствуем из нашего проекта григорианского календаря (ячейка D7). Не трудно посчитать, что число дней в григорианском периоде составляет 146 097. Тогда число дней до нужной даты можно вычислить по формуле:
Здесь S19, R19, M19 — адреса ячеек, содержащих год, месяц и день интересуемой даты, а U19 — адрес ячейки, где вычисляется искомый год в базовом периоде. Интерфейс калькулятора числа дней до даты можно сделать примерно таким.
Рис. 8. Пример оформления модуля "Осталось - прошло" Было — будет Актуальной для наших путешествий во времени является и обратная только что рассмотренной задача определения даты, отстоящей от текущей на заданное число суток. Для ее решения сначала по формуле =СЕГОДНЯ()+ОСТАТ(P29;146097) найдем дату в базовом периоде, которая позволит нам определить день и месяц интересующей нас даты. Здесь P29 — адрес ячейки ввода количества дней до этой даты. Затем вычисляем год даты на основе формулы =ГОД(M29)+400*ЦЕЛОЕ(P29/146097), в которой M29 — адрес ячейки с предыдущей формулой. Вариант оформления решения показан на следующем рисунке.
Рис. 9. Пример оформления модуля "Было - будет" Переходящие даты Наш проект машины времени только выиграет, если будет содержать модуль для расчета так называемых “переходящих” дат календаря. Вам сразу станет понятно, о чем идет речь, как только я скажу, что основной из них является дата Пасхи, которой в разные годы соответствуют разные дни календаря. Другие переходящие даты связаны с этим днем и предшествуют Пасхе или отмечаются после нее через определенное число суток. Формулы для расчета даты Пасхи можно найти в Интернете, в том числе на уже указанной ранее странице портала Астронет. Расчет непосредственно не связан с представлением и функциями даты-времени в Microsoft Excel, которые используются только на этапе вывода результата, поэтому описание этой части проекта мы опускаем и доверяем выполнить самостоятельно пытливому читателю, пожелав ему терпения, аккуратности и внимания. Для пересчета дат из григорианского календаря в юлианский и обратно пригодится ранее выполненный модуль. Результаты своей работы можно оформить, например, так, как на рис. 10.
Рис. 10. Пример оформления модуля "Пасха" Для вычисления остальных переходящих дат необходимо определить их смещение относительно Пасхи. Сделать это можно на основе какого-либо православного календаря, например, обратившись по адресу http://www.abc-people.com/event/crush.php, и формулой расчета количества дней между двумя датами одного года: =ДАТА(;месяц1;день1)-ДАТА(;месяц2;день2) Прибавив соответствующие смещения к вычисленной дате Пасхи, найдем искомые дни в календаре на интересующий нас год. Вывод можно организовать с помощью форматирования ячеек или функций СЦЕПИТЬ и ВЫБОР. Попробуем на нашей “машине времени” перенестись в будущее на 10 000 лет. К этому времени разница между юлианским и григорианским календарями составит 88 дней, православная Масленица в 2010 году придется на даты с 17 по 23 мая, а Пасха должна отмечаться 11 июля по григорианскому календарю. Полагаю, что этот прогноз вряд ли сбудется, но он соответствует нашим современным представлениям и общепринятым правилам календарных исчислений.
Рис. 11. Модуль "Переходящие даты" Макроуровень Решив глобальные проблемы временных промежутков на мегауровне, обратимся к повседневному макро- и даже микроуровню, в котором актуальны такие понятия, как час, минута, секунда и ее доли. В электронных таблицах многие вопросы, касающиеся даты и времени, можно решить, используя возможности форматирования. В предыдущем материале этот потенциал почти не был задействован. Причина этого кроется в амбициозности наших планов, выходящих за пределы формата представления даты-времени в MS Excel. Что касается счета коротких промежутков, то здесь таких препятствий не предвидится, поэтому мы постараемся наверстать упущенное и использовать форматирование с максимальной выгодой. Предлагаемые далее модули нашего проекта, хотя и используют ресурсы электронных таблиц, являются нетрадиционными для этого приложения, в связи с чем нам придется обратиться к макросам. На многих такие слова, как “программирование” и “макрос”, действуют волшебным образом, внушая панический ужас и парализуя волю. Многие проблемы предыдущих модулей предлагаемого проекта можно было бы эффективно решить с помощью макросов в Visual Basic for Application, но я сознательно оберегал читателя от этого, затягивая в глубь темы и стараясь не растерять контингент. Постараюсь и далее не злоупотреблять программированием, а в необходимых случаях давать понятные объяснения, чтобы обеспечить доступность как можно большему числу последователей. VBA-минимум Не буду утомлять читателя описанием особенностей и технологии работы с макросами в интегрированном в Мicrosoft Office интерпретаторе языка VBA, ограничившись самым необходимым минимумом: Разрешить исполнение макросов: — в Excel-2003 выбрать в меню: “Сервис — Макрос — Безопасность” “Средний” или “Низкий”, после чего закрыть файл и открыть снова; — в Excel-2007 на панели “Предупреждение системы безопасности” нажать кнопку “Параметры…” и выбрать “Включить это содержимое”.
В окне кода: ‘ (апостроф) — часть строки после апострофа интерпретатором не читается (используется для отмены действия операторов и комментариев). Остальное по ходу дела. Часы Речь идет о часах, которые показывают время в реальном режиме. Существует возможность создания таких часов в MS Excel без использования макросов на основе импорта внешних данных с настройкой времени обращения к ним. Минимальный доступный интервал времени в этом случае составляет одну минуту, а сам импорт используется не как источник данных, а как генератор событий. Поработав с этим способом достаточно много, я не хочу предлагать его для этой цели, так как для генерирования событий с очень короткими интервалами лучше использовать (поверьте моему опыту) все-таки макрос. Но не будем торопиться. Для начала в ячейку В4
занесем формулу =ТДАТА() и
через меню “Формат — Ячейки…” на вкладке
“Число” выберем “Время — Тип: *13:30:55”. Выделите
любую другую ячейку и попробуйте нажимать
клавишу Чтобы избавиться от механических хлопот, воспользуемся небольшим макросом, для чего перейдем в окно редактора 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 и щелкнув мышкой, убеждаемся, что часы пошли.
Предупреждение: В случае проблем при
прерывании хода часов используйте кнопку Чтобы иметь возможность остановить ход часов, создадим еще одну короткую событийную процедуру, реагирующую на двойной клик, выбрав из списков в верхней части окна кода для объекта 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)) с аналогичной целью. Чтобы подключить звонок, достаточно в команду проверки условия в процедуре 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 сек. до Время, прошедшее с момента старта, вычислим в ячейке 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 дополним условиями проверки флага пуска, приведя их соответственно к виду:
В макросе 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 Часы со стрелками Воспользовавшись диаграммой, нам удалось заметно оживить ход времени в таймере и сделать его более наглядным. А ведь, несмотря на свою дешевизну, электронные часы не могут вытеснить давнишнее изобретение — часы с циферблатом и стрелками. Более того, многие из них, хоть и имеют числовую природу, рядятся под механические. Причина этого именно в обзорности и наглядности. Хотелось бы, чтобы и наши часы обрели такие качества. Но откуда взять стрелки в MS Excel и как заставить их вращаться? Вспомним, что в приложениях Microsoft Office есть панель рисования, с помощью которой можно создавать не только множество типовых автофигур, но и свои собственные экземпляры, воспользовавшись в меню “Автофигуры — Линии” инструментами “Кривая”, “Полилиния” или “Рисованная кривая”. Впрочем, достаточно будет стандартных — равнобедренного треугольника, трапеции или тех же стрелок. Однако, как нетрудно убедиться, центр вращения графических объектов в MS Office лежит в геометрическом центре габаритного прямоугольника, заключающего фигуру, что не годится для стрелки часов. На первый взгляд ситуация кажется безвыходной, но… Нам помогут две особенности технологии работы с графическими объектами в офисных приложениях. Во-первых, объекты можно группировать, а во-вторых, их заливки и контуры могут быть прозрачными. Это и позволит создать иллюзию эксцентрического вращения. Создадим вертикальную стрелку, выбрав в меню “Автофигуры — Основные фигуры” примитив “Трапеция”, которую развернем меньшим основанием вверх. Ниже нарисуем вертикальный отрезок. Выделим обе фигуры и через панель “Рисование — Выровнять/распределить” выберем “Выровнять по центру”. Попробуем их сгруппировать. Если положение центра группы нас не удовлетворяет, выполним отмену и сдвинем отрезок немного вверх или вниз, после чего опять повторим группировку. Добившись ситуации, когда центр габаритного прямоугольника группы окажется немного выше большего основания трапеции, выделим отрезок и сделаем его прозрачным, выбрав “Цвет линий: Нет линий”. Оставшиеся стрелки создадим аналогично или путем копирования и последующей трансформации только что созданной. Отградуировать циферблат предлагаю следующим способом. Создадим короткий отрезок, равный длине минутного деления, и его копию. Разнесем их в положения, соответствующие 12 и 6 часам, выровняем по центру и сгруппируем. Скопируем группу и, вставив, выберем в меню приложения “Формат — Объект…”, где на вкладке “Размер” диалогового окна установим угол поворота равным 6°. Повторим операцию вставки и поворота еще три раза: для углов 12, 18 и 24°. Вернемся к исходным делениям, которые будут соответствовать часам, поэтому сделаем их толще и длиннее. Все группы выделим, выровняем с помощью панели рисования по центру и посередине, а затем сгруппируем и скопируем. По аналогии с предыдущим случаем, выполним вставку группы с последующим поворотом на углы 30, 60, 90, 120 и 150°. Снова выделим все группы и приведем их к единому центру. Оцифровку лучше всего выполнить с привлечением панели WordArt, так как это позволит масштабировать наш циферблат. Перед завершением работы над циферблатом рекомендую произвести полную разгруппировку всех его компонентов, а затем объединить все элементы в единую группу. Это поможет избежать некоторых дальнейших проблем. Итак, все графические компоненты готовы. Определим их порядок: на переднем плане секундная стрелка, затем минутная, за ней часовая и, наконец, на заднем плане циферблат. Еще раз выполним центровку, теперь уже не только для циферблата, но и для стрелок. Наша подготовительная работа несколько затянулась. Понимаю ваше нетерпение, но долгожданный момент совсем близок. Воспользуемся первым вариантом часов, где в диапазоне B4:B7 рабочего листа были занесены формулы: =ТДАТА() =ЧАС(B4) =МИНУТЫ(B4) =СЕКУНДЫ(B4) Привяжем секундную стрелку к ячейке B7, для чего, выделив ее, определим в поле “Имя” левой части строки формул номер группы, под которым она зарегистрирована в рабочей книге. Перейдя в редактор VBA, добавим в цикл процедуры Clock() следующую строку, сделав поправку на номер группы: ActiveSheet.Shapes("Group 1").Rotation = Range("B7") * 6 Если все сделано правильно, то секундная стрелка вздрогнет и, перескочив в текущее положение, начнет ритмично отсчитывать время. Впору воскликнуть: “Ура, заработало!” Подключим остальные стрелки, минутную: ActiveSheet.Shapes("Group 4").Rotation = Range("B6") * 6 + Range("B7") \ 10 Затем часовую: ActiveSheet.Shapes("Group 7").Rotation = Range("B5") * 30 + Range("B6") \ 2 Дело сделано.
Часы перемещаемые После первых экспериментов с созданными часами сразу же выясняется, что, кроме относительной простоты их реализации, они обладают еще одним плюсом: наши часы разборные. Можно сместить циферблат или передвинуть стрелки, при этом каждая из них как живая продолжает свою, теперь уже малопонятную миссию. Но, к сожалению, это достоинство оказывается существенным недостатком, так как вернуть наш хронометр в исходное состояние не так-то просто. Нас может выручить уже знакомая последовательность действий по центровке элементов часов. Записав ее в виде макроса и отредактировав, получим следующую процедуру: Sub Сбор() With ActiveSheet.Shapes. Range(Array("Group 1", "Group 4", "Group 7", "Group 10")) .Align msoAlignCenters, False .Align msoAlignMiddles, False End With End Sub Подключим ее в подпрограмме Clock() оператором: Call Сбор Теперь любые попытки вывести часы из строя путем их разборки мгновенно пресекаются :). Более того, мы добились еще одного результата, достижение которого нами на этом этапе не планировалось: наши часы стали перемещаемыми. Справедливости ради стоит отметить, что такая перемещаемость не отличается “послушаемостью”. То есть смещение часов действительно происходит в сторону сдвига того или иного элемента, но в промежуточное положение, не соответствующее желаемому.
Для достижения более высокого уровня управляемости воспользуемся следующей идеей, которая хоть и усложнит наши хлопоты по программированию, тем не менее будет иметь положительные последствия и откроет нам новые перспективы. Обычно часы закрыты сверху стеклом. Создадим и мы такую “стеклянную крышку”, нарисовав круг и выполнив для него заливку с использованием прозрачности. Будем отслеживать в макросе координаты центра такой “крышки” и приводить в соответствующее положение остальные элементы часов. Реализуя замысел, объявим в зоне “(General)” переменные для координат центра так, чтобы они были доступны в других макросах. Dim x0, y0 As Single В теле процедуры Clock() присвоением (это допустимо в VBA) введем следующие переменные: сек_стрелка = "Group 1" мин_стрелка = "Group 4" час_стрелка = "Group 7" стекло = "Oval 10" циферблат = "Group 11" На этом этапе использование функций времени в ячейках рабочего листа не имеет особого смысла, поэтому сделаем все необходимое в макросе с применением их английских эквивалентов: t = Now() 'Определяем время ts = Second(t) 'секунды tm = Minute(t) 'минуты th = Hour(t) Mod 12 'часы Проверим состояние “крышки” часов: With ActiveSheet. Shapes(стекло) x = .Left 'абсцисса y = .Top 'ордината w = .Width 'ширина h = .Height 'высота End With Вычислим текущие координаты центра и углы поворота секундной, минутной, а также часовой стрелок: x0 = x + w / 2 y0 = y + h / 2 us = ts * 6 um = tm * 6 + ts \ 10 uh = th * 30 + tm \ 2 Смещение остальных узлов наших часов обеспечим вызовом процедуры Сдвиг(Объект, Угол): Call Сдвиг(сек_стрелка, us) Call Сдвиг(мин_стрелка, um) Call Сдвиг(час_стрелка, uh) Call Сдвиг(циферблат, 0) Сама процедура будет иметь вид: Sub Сдвиг(объект, ug) With ActiveSheet.Shapes(объект) 'Устанавливаем .Left = x0 - .Width / 2 'абсциссу .Top = y0 - .Height / 2 'ординату .Rotation = ug 'угол поворота End With End Sub Теперь нетрудно убедиться, что наш хронометр стал вполне послушен, но это еще не значит, что его нельзя испортить “физическими” воздействиями.
Часы трансформируемые Технология работы с графическими объектами в Microsoft Office допускает их произвольное масштабирование и вращение. Понятно, что такое “коварство” в ранее созданных процедурах не предусматривается, а жаль. Хотя, проверяя состояние “стеклянного” покрова часов, мы уже заставили макрос выполнять лишнюю работу, определяя не только его координаты, но и размеры. А ведь они до сих пор оставались неизменными. То есть нами уже сделан шаг в нужном направлении. Остается учесть угол поворота и изменение соотношения сторон габаритного прямоугольника. Такая трансформация неизбежно приведет к неравномерному распределению делений на циферблате, значит, и стрелки, не нарушая ритмичности и соответствуя делениям, должны смещаться на углы, величина которых зависит от положения. Тонкости математического аппарата соответствующих преобразований хоть и неочевидны, но не выходят за рамки школьного курса, а их описание не укладывается в канву нашего повествования. К тому же внимательный читатель наверняка заподозрит автора в дальнейшем нарушении данного обещания не злоупотреблять макросами. По этим причинам позвольте опустить подробности и предложить пытливым программистам решить обозначенные задачи самостоятельно.
Рис. 19. Часы перемещаемые и трансформируемые Преодолев все преграды, вы наверняка столкнетесь, если это уже не произошло, с некоторыми непривычными эпизодическими явлениями, которых до сих пор в MS Excel вам наблюдать не доводилось. Связаны они с дополнительной, довольно значительной нагрузкой, создаваемой процедурой Clock(), работающей почти в режиме зацикливания. Так, может оказаться, что отдельные панели приложения неактивны. В этом случае перейдите на другой лист и вернитесь. Если в Excel-2003 не появляются маркеры выделения, то для модификации остановите часы, трансформируйте их “стекло” и запустите снова. В Excel-2007 окно приложения может не
закрываться соответствующей кнопкой или часы не
перемещаться мышкой. В первом случае можно
остановить часы, воспользоваться аккордом Итак… Вооружившись замечательным приложением Microsoft Excel, мы прошли большой путь, дерзко покоряя время. Перед нами открылись необъятные просторы — от кратчайших мгновений до многих тысячелетий. Созданы реальные навигационные приборы для гипотетической машины времени, которые позволяют не заблудиться в путешествиях как в прошлое, так и в будущее. Мне известно несколько реализаций вечных календарей в виде отдельных приложений. Есть в моей коллекции даже один созданный в MS Excel на основе макроса, доступ к коду которого закрыт паролем. Но все они или ограничены коротким диапазоном времени, или начинают давать ошибки даже в пределах одного столетия. А вот календарей, созданных только на основе стандартных средств Excel или часов в электронных таблицах, тем более со стрелками, встречать не доводилось. Более того, еще год назад, столкнувшись с такими проблемами, я полагал, что это даже невозможно, и теперь с удовлетворением признаю, что я ошибался. Потенциал, заложенный в это приложение, поистине неисчерпаем. Календарная часть модулей, несмотря на некоторые отличия представления даты в OpenOffice.org Calc и электронных таблицах IBM Lotus Symphony, работает и в них. Только в последнем придется ограничиться ручным вводом данных, так как в офисе от IBM не предусмотрен счетчик как элемент управления. Что касается часов, то язык для записи макросов OpenOffice.org Basic, встроенный в приложения с открытым кодом, имеет заметные отличия от Microsoft Visual Basic for Application. Так что в этом случае макросы придется переработать, используя те же идеи. Следует отметить что в отличие от проприетарного MS Excel в open source-приложениях существует возможность изменения положения центра вращения фигур, а также присутствуют такие элементы управления, как поле даты и поле времени, чем также не грех воспользоваться. Представленные варианты исполнения модулей машины времени неидеальны, да и неполны. Они не содержат, например, возможности определения момента восхода, захода и долготы дня в зависимости от широты и времени года. Вне сферы внимания остались мусульманский, лунный, китайский, еврейский и другие календари. Далек от идеала модуль с часовыми поясами. Не реализованы возможности регулировки часов на опережение или отставание. Не учитывается, что из-за уменьшения угловой скорости вращения Земли продолжительность суток на временных промежутках в миллиарды лет заметно отличается. Пофантазировав, можно было бы предложить даже часы и календари для планет Солнечной системы, например, для Марса или Венеры, путешествия к которым — дело не столь отдаленного будущего. Тем не менее хочу выразить надежду, что предлагаемые разработки могут найти применение: — в быту; — в качестве наглядных пособий на уроках информатики, для демонстрации возможностей электронных таблиц; — на уроках географии и астрономии; — в исторических исследованиях и прогнозировании; — в качестве элективного курса или его составляющей; — во внеклассной работе при проведении соревнований и конкурсов; — в самостоятельных проектах учащихся на факультативной или кружковой основе; — в исследовании процессов реального времени; — в дальнейших творческих замыслах. И будут способствовать: — развитию творческих способностей; — глубокому освоению потенциала офисных приложений; — пробуждению интереса к информационным технологиям и инструментальным средствам программирования. Файлы с работающими примерами можно найти в Интернете по адресу: http://www.rosinka.vrn.ru/dinex/time1.htm или загрузить с портала газеты “Информатика”: http://inf.1september.ru/download/time_09.rar. Модули и макросы, код которых доступен, снабжены подробными комментариями. Их содержательная часть может несколько отличаться от описанной в данной статье, но это не значит, что вам были предложены нерабочие варианты. Понимая разочарование отдельных читателей по поводу своей “выходки” с трансформируемыми часами, хочу их утешить. Там же вы сможете найти и файл с заинтриговавшим вас работающим модулем. Источники 1. Ошибка високосного года в Microsoft Excel. / “Информатика” № 21/2008. 2. Создание календаря в Microsoft Excel. / “Информатика” № 18/2008. 3. Справочная система Microsoft Excel.
А.. Н.. Комаровский,
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
. В
ячейке В4 отображается текущее
время с точностью до секунды. Заставить идти наши
часы вручную можно и другими способами (например,
вводом данных), но каждый раз, чтобы обновить
время, нам придется выполнять те или иные
действия с помощью клавиатуры или мышки. Хоть и
просто, но не очень удобно.
(Reset).
+ F4 или контекстным
меню на панели задач, а во втором — клавишами
управления курсором.