gvozdeff (gvozdeff) wrote,
gvozdeff
gvozdeff

Categories:

Office для кипятильников. Часть 4

Сегодня будем делать квартальный календарь. В Excel, разумеется. Я, кстати, на рабочем столе именно такой и использую. Сделал я его давно, но сегодня решил сделать заново, чтобы попутно прояснить множество полезных вещей. Да, кстати, вас еще не достало это окно справа «Приступая к работе»? Место ведь занимает. Лечится так: Вид -> Область задач. Совсем лечится так: Сервис -> Параметры -> Вид -> Область задач при запуске. Галку сбрасываем, больше не появится.

Для начала выделим семь ячеек от А1 до G1 и объеденим их: Формат -> Ячейки -> Выравнивание -> Объединение ячеек. Для объединения ячеек есть также специальная кнопка на панели форматирования. Вот только обратно разъединить с ее помощью не выйдет, для этого надо будет галку сбросить по указанному выше пути. Вставим туда формулу (без кавычек): «=СЕГОДНЯ()». Кстати, когда набираете в ячейке Excel какую-либо формулу, рекомендуется делать это строчными буквами. Если все сделано правильно, и Excel нашел такую функцию, он сигнализирует об этом, сам преобразуя ваше ключевое слово в верхний регистр. В ячейке появилась текущая дата. Теперь необходимо войти в форматирование ячеек: Формат -> Ячейки -> Число. Тут выберем нижнюю строку «(все форматы)» и в окошке «Тип» забьем «ММММ». Теперь в ячейке отображается текущий месяц. Следующую строку заполняем цифрами от 2 до 8. Теперь выделяем эти семь ячеек и тоже идем в меню «Формат», где присваиваем им формат «ДДД». Почему именно от 2 до 8? Все очень просто. При форматировании числа в один из форматов даты, Excel, как мы уже выяснили, меняет это число на некую дату, в соответствии со своими представлениями о порядочности. Так вот, число 2 он поменяет на 02.01.1900, а это был Понедельник, как мы все помним. Нам же только этого сейчас и надо. Вообще, можно поставить туда любую дату, которая приходилась на Понедельник, и тогда Формат «ДДД» отобразит ее, как «Пн». Но нам проще работать именно с 02.01.1900, поскольку на языке Excel это – наименьший из случавшихся в истории понедельников.

Теперь нам надо выяснить, на какой день недели приходится первое число текущего месяца. Выяснить это было бы довольно просто, но нам осложняет работу то, что необходимо вывести это первое число в определенной ячейке. Следовательно, будем пользоваться фунцией «ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)». У нее три аргумента. Первый – условие. Если условие выполняется, то функция выводит результат, записанный во втором аргументе. Если условие не выполняется, то она выводит третий аргумент. Аргументы в скобках, как мы помним, разделяются точкой с запятой. Вот простой пример: «=ЕСЛИ(2*2=4;”OK”;”Какая-то фигня”)». Условие «2*2=4» на данной планете, как правило, выполняется, поэтому, если случилась «Какая-то фигня», проверьте глобус. «ОК» и «Какая-то фигня» являются текстовыми величинами, поэтому в формулах они взяты в кавычки. Вернемся к календарю. Нужно построить такую конструкцию, которая определила бы первое число текущего месяца, его день недели, сравнила бы этот день недели с днем недели «Понедельник» и при положительном результате выдала бы в ячейку число «1». Чтобы получить текущий год, используем формулу «=ГОД(СЕГОДНЯ())». В качестве аргумента функции «ГОД()», которая выделяет из даты год, мы подставили «СЕГОДНЯ()», выдающее текущую дату. Месяц получаем аналогичным образом: «=МЕСЯЦ(СЕГОДНЯ()». Дата нам требуется не сегодняшняя, а первое число текущего месяца. Именно его мы будем обрабатывать функцией «ДЕНЬНЕД(дата_в_числовом_формате)», вычисляющей день недели. Чтобы получить дату в числовом формате из трех составляющих, надо воспользоваться функцией «ДАТА(год;месяц;число). У этой функции три аргумента: год, месяц, число. Вот и подставим их: «=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)». Следите за скобками, у каждой функции должно быть две скобки, одна – открывающая, другая – закрывающая. Теперь добавляем в эту формулу определение дня недели, для этого пишем «=ДЕНЬНЕД()», а потом в скобки копируем всю предыдущую конструкцию, вот так: «=ДЕНЬНЕД(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1))». Далее нам нужно сравнить полученный результат с днем недели, который указан во второй строке нашего календаря. Там у нас специально стоит не «Пн», а 02.01.1900. Его день недели выясняется той же функцией «=ДЕНЬНЕД(сюда_вставим_адрес_ячейки_с_сдатой)». Ячейка с датой у нас – А2, значит, выглядеть все это будет так: «=ДЕНЬНЕД(A2)», не забывайте, что буквы в адресах ячеек – латинские. Теперь их надо сравнить и вывести результат сравнения. Настала пора функции «ЕСЛИ». Упрощенно: «=ЕСЛИ(день_недели_совпадает;1;0). Стало быть, функция выдаст нам число 1 при совпадении и 0 при несовпадении дней недели. Теперь на место первого аргумента копируем сравнение двух предыдущих конструкций – день недели первого числа текущего месяца и день недели в ячейке А2 и ставим между ними знак «=». Если будет равно, функция выдаст 1, если не равно - 0. «=ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1))=ДЕНЬНЕД(A2);1;0)». Вообще, конечно, такую длинную вложенную конструкцию надо собирать постепенно. В области листа, которая не затронута нужными данными, надо последовательно вводить функцию за функцией, чтобы сразу видеть правильность результата. А потом копировать одну функцию на место аргумента другой и так далее. Теперь нам надо забить такими же функциями всю первую строку календаря. Только с маленьким отличием. Вторая формула должна ссылаться уже не на А2, а на В2. Как же этого добиться? А очень просто. Надо копировать всю ячейку и вставлять, при этом ссылка поменяется автоматически. Почему так происходит? Потому что формула фактически ссылается не на конкретную ячейку А2, а на ячейку, которая находится на одну ячейку выше данной, и при копировании Excel это учитывает. Есть способ копирования ячеек, после знакомства с которым не влюбиться в Excel не возможно. Просто наводите курсор на правый нижний угол ячейки (он примет форму черного крестика), прижимаете левую клавишу мыши и тащите этот крестик в нужном вам направлении (в данном случае – вправо). Итак, мы получили в первом ряду календаря шесть нулей и одну единицу. До календаря, вроде бы еще далеко. Как же мы будем определять, где находится второе число календаря? Очень просто, оно находится обычно сразу после первого. Значит, нам надо внести в нашу формулу еще одно условие. Если слева от числа находится 1, то выводить надо 2, а не выполнять всю предыдущую конструкцию. А вот если слева находится 0, тогда пусть выполняет. Выглядеть это дело будет так: «=ЕСЛИ(ячейка_слева>0;выводим_ячейка_слева+1;иначе_проверка_на_первое_число)». Тут будет одно исключение. У нашей крайней левой ячейки не существует ячейки, которую надо проверять на 0. Поэтому в ней все оставим, как было, чтобы не возникла ошибка обращения к несуществующей ячеке, а проверку начнем с ячейки B3. В ней в условие функции «ЕСЛИ» запишем «A3>0», во второй аргумент – «A3+1» и в третий всю нашу предыдущую длинную конструкцию. Должно в ячейке В3 получиться следующее: «=ЕСЛИ(A3>0;A3+1;ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1))=ДЕНЬНЕД(B2);1;0))». Вот эту ячейку теперь и копируем вправо до самого воскресенья. В смысле, не до самого воскресенья, а до того места, где в календаре воскресенье.
Но и это еще не все. У нас все формулы ссылаются на СЕГОДНЯ(), а должны бы ссылаться на ячейку, в которой у нас стоит это самое СЕГОДНЯ(). Нужно это для того, чтобы потом теми же формулами вывести предыдущий и последующий месяцы. Значит, во всех формулах надо заменить «СЕГОДНЯ()» ссылкой на ячейку. Ссылку будем делать «жесткую», или «абсолютную» в терминологии Excel. Чтобы она не смещалась при перетаскивании и все ячейки ссылались на одну и ту же. Абсолютная ссылка на A1 выглядит так: «$A$1». Выделим все семь ячеек первого ряда и выполним команду «Заменить» (Ctrl+H), подставив в «Найти» СЕГОДНЯ(), а в «Заменить на» $A$1.

Переходим ко второму ряду чисел. Тут все просто, в ячейке A4 надо сделать ссылку на воскресенье первой недели и прибавить единицу. Вот так: «=G3+1». Кстати, формула пишется следующим образом: сначала нажимете на «=», потом щелкаете мышкой на ячейке G3, потом пишете «+1» и жмете Enter. В B4 надо уже ставить ссылку на A4. Вот так: «=A4+1». Следующие пять ячеек заполняем перетаскиванием крестика, они все ссылаются на ячейку слева от себя и прибавляют к ней 1. Чтобы заполнить следующую строку, выделяем нашу вторую строку, все семь ее ячеек, прижимаем Ctrl и перетаскиваем ее на ряд ниже, все скопируется, как надо. Точнее, лучше использовать другой способ: выделить все семь ячеек, навести мышку на правый нижний угол выделения (до появления черного крестика) и потащить все это вниз на три строчки, все они заполнятся автоматически.

Осталось определиться с последним днем месяца. Он может появиться уже в конце четвертой строки (если текущим месяцем будет февраль, который начнется с понедельника). Значит, с этого места надо опять править ячейки. Условие должно быть таким: Если последний день месяца не меньше полученного в формуле числа, то выполняем все, что было в ячейке, а иначе ставим 0. Как определить последний день месяца? В Excel такой функции нет, поэтому придется оперировать имеющимися (свою писать не будем, это излишне). Мы просто возьмем первое число следующего месяца и отнимем от него единицу. Первое число следующего месяца берется так: «=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)». Из полученной даты надо вычесть единицу, выглядит это так: «=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)-1». Можете проверять все эти формулы, вводя их в свободные ячейки в свободную область листа где-нибудь далеко от календаря. Если сейчас идет Октябрь 2006, то последняя конструкция выдаст в ячейку 31.10.2006. Но нам вся дата не нужна, только число. Стало быть, надо всю предыдущую формулу засунуть аргументом в функцию «ДЕНЬ()», которая вычисляет день из заданной даты. Получаем: «=ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)-1)». Теперь идем в ячейку A7 нашего календаря, там у нас должно быть написано «=G6+1». Вот это «G6+1» должно проверяться на то, чтобы не быть больше последнего числа месяца, и в случае превышения не должно считаться и выводиться. Стало быть, надо написать следующее: «=ЕСЛИ(G6+1>ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)-1);0; G6+1). То есть, если оно будет больше, выдаст просто 0. Теперь скопируем все это в ячейку B7 и внесем правку, вместо адресации к ячейки H6, которая посчиталась при копировании, подставим A7. Делается это так: в строке формул выделяется двойным щелчком ссылка на ячейку H7, а потом щелкается мышкой по ячейке A6. Дальнейший ряд заполняем перетаскиванием. Следующий ряд чисел копируем с последнего. Ага, нашли ошибку. После того, как формула выдала 0, сразу пошли числа 1, 2, 3 и так далее. Ну правильно, они ведь не больше последнего числа месяца, а значит, условие соблюдено, и плюсование продолжается. Надо исправлять. Возвращаемся к редактированию A7, откуда пошла неправильная формула. Тут надо добавить условие, что если проверяемая ячейка содержит 0, то тут тоже пусть будет 0, без всяких заморочек. Правда, ячейка G6 не может содержать 0 ни при каких условиях, но мы же потом это будем копировать строкой ниже, так что пригодится. Итак, должно выйти: «=ЕСЛИ(G6=0;0;ЕСЛИ(G6+1>ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)-1);0; G6+1))». Теперь копируем всю полученную ячейку в B7 и снова правим все ссылки на H7 ссылками на A7. Заполняем весь ряд вправо от ячейки B7 перетаскиванием. И теперь перетаскиванием заполняем следующие ряды. Сколько всего должно быть рядов? Всего шесть, так как месяц из 31 дня, начинающийся с воскресенья (как раз Октябрь 2006), займет шесть недель в нашем календаре. Осталась одна правка – заменить «СЕГОДНЯ()» во всех конструкциях на $A$1, это мы уже умеем делать, к тому же, функция замены помнит, что мы заменяли в последний раз, даже вводить ничего не придется, просто выделить последние два ряда чисел, нажать Ctrl+H и «Заменить все».

Теперь настала пора форматировать. Сначала выберем общий фон и цвет шрифта. Я выбрал желтые цифры на черном фоне, ячейку с названием месяца подчеркнул двойной чертой, а названия дней недели сделал жирным шрифтом и тоже отчеркнул от самих чисел. Также неплохо бы все числа выравнять по центру. В общем, сейчас творческий момент, приведите все это в должный вид. Нулями потом займемся, пока считайте, что на этих местах будут даты располагаться. Главное, помните: если надо форматировать одинаково сразу несколько ячеек, выделяйте их все и форматируйте. Чтобы выделить весь ряд (или столбец), надо щелкнуть по его обозначению. Перетаскивая мышку по обозначениям столбиков или рядов, можно выделить их несколько. Чтобы выделить вообще все, надо щелкнуть по верхнему левому углу, где обозначения столбцов и рядов сходятся. Кстати, когда выделено несколько столбцов, удобно задавать им одинаковую ширину, просто перетаскивая линию, разделяющую обозначения между столбцами. А если надо задать ширину столбца автоматически (чтобы влезли все помещенные в него данные), надо щелкнуть дважды по линии, разделяющей обозначения столбцов.

Сейчас с нулями разберемся. Выделяйте все шесть недель и переходите в меню Формат –> Условное форматирование. Там выбираете «Условие 1», «значение», «равно», вписываете 0 и форматируете так, чтобы цвет текста был таким же, как цвет фона. Фон, естественно, выбираете такой же, какой вы уже задали для всех дат. А вот условие 2 мы сделаем посложнее. Выделяем одну ячейку A3. Жмем «А также>>» Ставим в условие «формула» и пишем в окне «=ДАТА(ГОД($A$1);МЕСЯЦ($A$1);A3)=СЕГОДНЯ()», форматируем это дело, помимо цвета шрифта и фона, скажем, рамочкой вокруг ячейки. Теперь заполняем форматы. Делается это так же, как и заполнение значений, но правой клавишей мыши. Наводим курсор на правый нижний угол ячейки A3, чтобы появился черный крестик, и тащим этот крестик вправо правой же клавишей, а в появившемся контекстном меню выбираем «Заполнить только форматы». И так же растаскиваем форматы вниз по всем неделям. В результате сегодняшнее число должно быть выделено рамочкой.

А теперь займемся двумя остальными месяцами. Вот это место сейчас понять невозможно, позже объясню. Надо выделить все числа, нажать Ctrl+H и заменить во всех случаях абсолютную ссылку $A$1 на смешанную ссылку $A1 (ссылка на столбец «жесткая», ссылка на строку «мягкая»). Кроме того, необходимо будет снять второе условия условного форматирования со всех ячеек. Далее выделим всю область нашего единственного месяца, нажмем Ctrl и перетащим его вниз, скопируем. И еще раз, чтобы получилось три одинаковых месяца. Средний оставим, как есть, а вот в ячейки, где написаны месяцы в верхней и нижней части календаря внесем небольшие изменения. Сейчас там написано «=СЕГОДНЯ()», а нам надо, чтоб там был предыдущий и последующий месяцы. Предыдущий месяц задается так: «=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())-1;1)» Соответственно, следующий месяц: «=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)».

Остался у нас один, вроде бы, косяк. При переходе с одного года на другой вся наша конструкция будет учитывать месяца только этого года, а это ошибка. Если сегодня январь 2007, то декабрь на календаре должен быть прошлогодний. Однако, Excel уже внес поправку за нас. Когда он выполняет функцию «ДАТА» и в месяце у него получается 13, он автоматически переключает год на следующий. А если 0 – на предыдущий.

Теперь вернем форматирование для обведения в рамочку текущего числа. Очевидно, что оно нужно только для одного месяца, текущего. Выделяем ячейку, в которую попал первый понедельник текущего месяца (даже если он нулевой) и пишем во втором условии форматирования «=ДАТА(ГОД($A$10);МЕСЯЦ($A$10);A12)=СЕГОДНЯ()», это у меня так попало, у вас могут быть другие ячейки, год и месяц должны браться из той ячейки, где написан месяц, а дата должна браться из самой условно форматируемой ячейки. Снова жмем там «Формат» и задаем параметры рамочки. Теперь растаскиваем форматы, заполняя ими весь текущий месяц.

Остался последний штришок. Надо, чтобы при открытии книга автоматически пересчитывалась. Для этого потребуется маленький макрос. Вообще, кто такие макросы. Это дурацкое слово, образованное, как и «баксы», от множественного числа слова в английском языке. А обозначаются им макрокоманды, то есть, некоторая последовательность действий пользователя, записанная, как программа. Скажем, делаете вы изо дня в день рутинную операцию с файлами. И не догадываетесь, что всю последовательность действий можно легко записать и назначить ее одной кнопке. Только надо заранее продумать последовательность действий, чтобы они в точности подходили ко всем обрабатываемым в будущем файлам. А записывается макрос так: Сервис -> Макрос -> Начать запись. Далее ему присваивается произвольное имя (без пробелов и желательно латиницей). Потом выполняется последовательность действий. У нас она сейчас будет очень простая. Сначала нажмем на F9, чтобы пересчитать лист, а затем на Ctrl+S, чтобы сохранить (имейте в виду, что книга должна быть уже сохранена, до начала записи макроса). Все, жмем кнопку «Остановить» на всплывшей панели. Теперь заходим в редактор, нажав Alt+F11, находим там наш свежеизготовленный макрос (в одном из модулей), читаем его текст:




Sub Название_макроса()

Calculate

ActiveWorkbook.Save

End Sub





Нам надо изменить первую строчку на «Sub Auto_Open()», и тогда эти действия будут выполняться автоматически при запуске Excel.

Вот, что у меня получилось:


Собственно, все. Ах, да, в эту хрень можно вставить картинку, сохранить ее в виде HTML и поместить на рабочий стол. Правда, в этом виде она не будет автоматически пересчитываться каждый день. Чтобы пересчитывалась, надо было делать не в Excel, а в VBScript или в JavaScript. Но нам ведь не это интересно было, а возможности Excel.


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


©2006stalicGvozdeff
Tags: excel, office
Subscribe
  • Post a new comment

    Error

    default userpic

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 16 comments