На главную

Загрузить файлы
к статье
(202 Кб)

Опубликовано в методической газете для учителей "Информатика" № 7, 2009 г.

Полная версия для печати (226 Кб)

Проект «Машина времени»

Часть 1: Вечные календари без макросов

Часть 2: Дополнения к вечным календарям

Часть 3: Часы с цифровым интерфейсом

Часть 4: Часы с циферблатом и стрелками  

Часть 1

Планируем модули • Функции даты и времени • Календарь на месяц 
Эксперименты • Григорианский календарь на любой год • Юлианский календарь

Планируем модули

Машина времени пока еще остается выдумкой фантастов. Но всякая выдумка есть не что иное, как информационная модель, в большей или меньшей степени соответствующая реалиям. Компьютерное моделирование уже позволяет нам совершать невероятные путешествия в прошлое («Парк Юрского периода») и в будущее («Звездные войны»). Кроме того, осмелюсь утверждать, что машина времени для виртуальных странствий изобретена и всем известна довольно давно. Такие «машины» есть в каждом доме. Они бывают настенными, настольными, карманными, отрывными, перекидными, встроенными и т.д. Я думаю, вы уже догадались, о чем идет речь. В самом деле, именно обычный календарь на текущий год позволяет нам приоткрывать завесу на ближайшее будущее и возвращаться в не столь отдаленное прошлое.

Понятно, что основным «навигационным» прибором и устройством ввода-вывода реальной машины времени должен быть именно календарь, причем календарь на любой год возможного диапазона наших путешествий во времени, и чем больше этот диапазон, тем лучше.

Так как продолжительность календарного года и календарного месяца есть величина непостоянная, то отсчет времени для наших турне, вероятно, придется вести в сутках. А для этого необходимо знать в какой день того или иного года мы попадем, переместившись во времени на заданное количество суток, и на сколько дней необходимо переместиться в прошлое или в будущее, чтобы оказаться там в нужную дату.

Но, даже имея вышеуказанные возможности, мы рискуем заметно промахнуться, например, при путешествиях в не очень далекое прошлое. Допустим, мы решили предотвратить дуэль А.С.Пушкина с Дантесом и переместиться в 27 января 1837 года – дату этой трагедии, известую из исторических источников. Оказавшись в Петербурге в указанное время, мы обнаружили бы, что на календаре 15 января. Причина этого заключается в том, что даты до 1918 года в России официально принято указывать по юлианскому календарю, действовавшему в ту пору. Следовательно, для нашей машины времени необходимо иметь еще и юлианский календарь на любой год, а также возможность пересчета дат одного календаря в другой и обратно.

Для ориентировки «на местности» неплохо бы иметь и календарь знаменательных дат. Большинство из них привязаны к тому или иному конкретному дню, но есть и такие, которые называются переходящими. Главной из них является Пасха. Решив вопрос с ее расчетом, мы сможем без особых проблем пополнить наш арсенал и таким «устройством» ввода-вывода.

Решив глобальные проблемы, можно перейти к «мелочевке» – часам, минутам, секундам.

Итак, в общих чертах план проекта готов. Для его реализации воспользуемся приложением MS Excel.

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

Прежде всего, несколько слов об особенностях представления даты и времени в MS Excel. И то и другое хранится в виде кода – числа, целая часть которого соответствует порядковому номеру данного дня, считая с 1 января 1900 года, а дробная – доле суток с полуночи до указанного времени, так, что 1 сек. соответствует 1/(24*60*60)= 0,0000115740740740741. Используя формат ячейки «Дата» или «Время», можно получить в ней соответствующее отображение. Для работы с данными такого типа в электронных таблицах предусмотрен целый набор функций, которые можно разделить на несколько категорий:

• функции определения текущей даты и времени по системным часам;

• функции преобразования даты и времени в формат кода даты-времени;

• функции выделения из кода составных частей даты и времени без использования форматирования;

• функции вычисления на основе одной или двух дат.

Из последней категории в версиях, предшествующих MS Excel 2007 есть только две первые, но нам потребуется только первая. От применения же второй функции (вычисления количества дней между двумя датами) придется отказаться в силу приближенности ее результатов.

Таблица 1 – Функции даты-времени в Microsoft Excel

В приведенной таблице указаны эквиваленты функций английской версии, которые могут пригодиться при работе с макросами, хотя в этом проекте мы будем следовать принципу обращения к ним только в тех случаях, когда задача не может быть решена традиционными методами электронных таблиц. Полный список всех функций MS Excel и их перевода можно найти в папке установки MS Excel по адресу C:\Program Files\Microsoft Office\OFFICE11\1049\ FUNCS.XLS.

К рассмотренному списку добавим еще одну, которая прямо не присутствует в электронных таблицах, но есть в Visual Basic for Application (VBA) и может быть использована как альтернатива функциям времени на заключительном этапе нашего проекта. Это функция TIMER(), которая возвращает количество секунд и долей секунды, прошедших с полуночи до момента обращения к ней.

В данном проекте нам придется воспользоваться еще некоторыми функциями, прежде всего, логическими (для проверки условий), а также некоторыми текстовыми (для сервисного отображения результатов).

Календарь на месяц

Для начала реализуем календарь на месяц наподобие того, что есть в операционной системе. Остановимся подробнее на технологии.

Выделим лист и установим ширину колонок равной 2. Для дат отведем диапазон E8:J14 размером 7 х 6. По левому краю этого диапазона в ячейках D8:D14 проставим дни недели от понедельника до воскресенья, а еще левее в ячейках C8:C14 – порядковые номера дней недели от 1 до 7.

Над диапазоном дат в ячейке E7 укажем порядковый номер месяца, а в J7 – количество дней в этом месяце.

Год, на который создается календарь, занесем в ячейку G4.

Вариант 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, вправо до столбца J. В результате должно получиться так, как на следующем рисунке.

Чтобы избавиться от недопустимых дат в первой, пятой и шестой колонках нашего календаря, воспользуемся условным форматированием. Выделив диапазон E8:E14, выберем в меню «Формат – Условное форматирование…» и в диалоговом окне в первом списке оставим «значение», во втором выберем «меньше», а в третье впишем «1». Затем нажмем кнопку «Формат…» и на вкладке «Шрифт» следующего диалогового окна установим цвет «белый» (под цвет фона).

Рис. 3. Диалоговое окно условного форматирования.

То же самое в Excel 2007:

– на вкладке «Главная» нажать кнопку «Условное форматирование»;

– в ниспадающем меню выбрать «Правила выделения ячеек»;

– в дополнительном меню выбрать «Меньше…»;

– в диалоговом окне в первый список заносим «1»;

– во втором списке выбираем «Пользовательский формат…»;

– на вкладке «Шрифт» устанавливаем соответствующий цвет.

Для диапазонов 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), которая по номеру месяца в ячейке E7 будет выводить его название.

Настало время заметать следы нашего «преступления». В первую очередь скроем номера дней недели, для чего установим цвет текста в ячейках 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 годы.

Рис. 5. Пример оформления юлианского календаря на любой год.

Таким образом, для создания юлианского календаря на любой год нам потребуется совсем немного. Выберем в программном меню «Правка – Переместить/скопировать лист…» В появившемся диалоговом окне отметим флажок «Создавать копию» и нажмем кнопку «Ok». Выделим ячейку D7, в которой мы вычисляли соответствующий год базового периода для григорианского календаря, и отредактируем имеющуюся там формулу, заменив ее на следующую, полученную после небольших преобразований:

=ОСТАТ($G$4;28)+2004+ЕСЛИ($G$4<0;1;0)

Здесь $G$4 – ссылка на ячейку, в которую вводится год. Возможно, что в вашем проекте адреса ячеек назначения будут отличаться от указанных. В этом случае внесите необходимые коррективы.

Вот и все. Обещанный юлианский календарь готов. Дальнейшее его оформление может быть, например, таким, как на приведенном рисунке.

Для вычисления года от сотворения мира была использована формула:

=СЦЕПИТЬ("(";ГОД(СЕГОДНЯ()+109)+5509;" год от сотворения мира)"), учитывающая, что в этом случае начало года приходилось на 1 сентября по старому стилю. Что касается сообщения о текущей дате, то эту более длинную формулу, попробуйте сконструировать сами, воспользовавшись функциями СЦЕПИТЬ и ВЫБОР, а также функциями даты-времени.

Пускаясь в путешествие в прошлое, необходимо учитывать, что юлианский календарь был введен в 45 г. до н.э., причем нынешнее правило определения високосных лет в нем действует только с 8-го года н.э. Счет лет от Рождества Христова впервые было предложен 525 году. Да и начало нового года не всегда приходилось на 1 января.

Продолжение

© 2009 Государственное областное образовательное учреждение
"Россошанская школа-интернат для детей-сирот и детей, оставшихся без попечения родителей"