Командировочное удостоверение1. Создание формы удостоверения При создании формы командировочного удостоверения на рабочем листе КомандировочноеУдостоверение, не следует пренебрегать объединением ячеек и переносом текста в ячейках с формулами, созданием границ различной толщины (линии ограничивающие границы ячеек), применением различных шрифтов и их размеров. Эти параметры можно ввести или изменить с помощью диалогового окна Формат ячеек, вызываемого командой Формат/Ячейки. Шрифту текста, формируемому в ячейках с формулами, желательно задать другой цвет, отличный от стандартного, например, синий. Это позволит в дальнейшем избежать возможных ошибок, обнаруживаемых после вывода документа на печать, из-за уменьшения объема просматриваемого изменяющегося текста с реквизитами этого документа, перед выводом его на печать, а при печати на цветном принтере придаст документу еще и достойный дизайн.
2. Формулы поиска информации Функция, на которой базируется автоматизация заполнения командировочного удостоверения - функция поиска ВПР. Рассмотрим формулу, введенную в ячейку Е3 (Фамилия, имя, отчество): =ВПР(L1;'ЖурналРегистрацииКУ'!A4:J200;2;ЛОЖЬ) Функция ВПР ищет значение, заданное в первом аргументе, в крайнем левом столбце диапазона, указанного во втором аргументе. Возвращает она значение из строки содержащей найденное в первом аргументе значение, но находящегося в столбце, который задан в третьем аргументе функции. Четвертый аргумент - логическое выражение ЛОЖЬ, задается для поиска точно соответствующей информации, введенной в первом аргументе. В первом аргументе функции ВПР введена ссылка на ячейку L1. По значению порядкового номера, введенного в эту ячейку, будет осуществляться поиск всех данных в журнале регистрации для заполнения выведенного на экран монитора командировочного удостоверения на конкретного работника. Остальные формулы (кроме формулы в ячейке D5) отличаются от описанной выше только номером столбца в третьем аргументе. Значения аргументов: · 5 - ячейка F7 (пункт назначения) · 6 - ячейка D9 (наименование предприятия) · 11 - ячейка G12 (срок командировки) · 12 - ячейка D13 (цель командировки) · 7 - ячейка G15 (приказ от) · 13 - ячейка I16 (паспорт серии) · 8 - ячейка В11 (дата и номер командировки) Формула в ячейке D5 использует комбинацию функций СЦЕПИТЬ, ВПР, а также ссылку и ввод текстовых данных. =СЦЕПИТЬ(ЖурналРегистрацииКУ!A1;",";ВПР(L1;ЖурналРегистрацииКУ!A4:J200;3;ЛОЖЬ);", "; ВПР(L1;ЖурналРегистрацииКУ!A4:J200;4;ЛОЖЬ)) По ссылке на ячейку А1 листа ЖурналРегистрацииКУ осуществляется ввод текста наименования предприятия. Первая функция ВПР производит поиск должности работника, а вторая - наименования отдела, в котором он работает. Все определенные текстовые значения (предприятие, должность и наименование отдела) соединены функцией СЦЕПИТЬ с разделением между собой знаками запятых и пробелами. 3. Обратная сторона командировочного удостоверения Обратная сторона командировочного удостоверения расположена на том же рабочем листе - КомандировочноеУдостоверение, ниже области занимаемого лицевой стороной документа. Деталь, на которой следует остановиться - это заполнение прописью даты выбытия. В строке формул видно, что в ячейке В25 находится ссылка на ячейку N25, которая является выходом модуля формирования даты прописью.
Модуль написания даты прописью с формулами показан на рисунке и формирует этот текст на основании даты в формате Excel, введенной в столбец I журнала регистрации командировочных удостоверений. В свою очередь поиск этой даты осуществляется формулой, введенной в ячейку L25 с помощью функции ВПР по порядковому номеру, введенному в ячейку L1. Модуль имеет три ячейки: · L25 - вход модуля. Это ячейка для ввода даты, которая будет отображена в виде текстовой строки. В ячейку входа модуля может быть введена дата с клавиатуры, указана ссылка на ячейку содержащей дату или формула, возвращающая эту дату; · M25 - ячейка проведения промежуточных вычислений; · N25 - выход модуля. В этой ячейке, окончательно формируется текстовая запись введенной на вход даты. На выход модуля может быть указана ссылка, из ячейки в которой должен находиться текст даты.
4. Создание элементов управления Созданный генератор командировочных удостоверений имеет недостаток - для просмотра на листе КомандировочноеУдостоверение записей, введенных в журнал регистрации, каждый раз необходимо вводить в ячейку L1 с клавиатуры порядковый номер этой записи в журнале. А это не совсем удобно. Для устранения этого недостатка на листе КомандировочноеУдостоверение, с правой стороны от формы командировочного удостоверения, создайте элементы управления, с помощью которых можно будет изменять значение, содержащееся в ячейке L1. Такими элементами управления могут являться Список и Счетчик Для создания элементов управления используется панель инструментов Формы, для активизации которой выполните команду Вид/Панели инструментов/Формы.
Для внедрения элемента управления Список щелкните левой кнопкой мыши на панели Формы по указателю элемента управления Список. Указатель мыши, после перевода его на поле рабочего листа, приобретет вид крестика. Выбрав место расположения элемента управления на листе, и нажав левую кнопку мыши, очертите контур будущего элемента управления.
Для управления с помощью элемента управления Список содержимым ячейки L1, поместите указатель мыши на элемент управления и нажмите правую кнопку мыши. В появившемся контекстном меню выделите команду Формат объекта, что вызовет появление диалогового окна Формат элемента управления, в котором перейдите на вкладку Элемент управления. Это окно вызывается также комбинацией клавиш [Ctrl+1].
Поле Формировать список по диапазону предназначено для ввода диапазона, по которому будет формироваться список по этому элементу управления. Применимо к нашему приложению в этом поле должен быть указан диапазон журнала регистрации командировочных удостоверений, в котором формируются порядковые номера записей, содержащих данные командировочных удостоверений. Для этого поместите курсор в поле Формировать список по диапазону и на рабочем листе ЖурналРегистрацииКУ выделите область ячеек в столбце А, в котором будут расположены порядковые номера записей командировочных удостоверений, например $А$4:$А$200. В этом поле можно указать не только область формирования порядковых номеров, но и любую другую область журнала регистраций, но тогда этот список не будет иметь желательную информационность, а будет отражать, например, населенные пункты назначения или место работы сотрудника. Выделенный в списке элемента управления порядковый номер удостоверения должен автоматически вводиться в ячейку L1, для чего поместите курсор в поле Помещать результат в ячейку и на листе КомандировочноеУдостоверение, выделите мышью ячейку L1. В области Возможен выбор активизируйте переключатель Только одного значения, после чего нажмите кнопку ОК. Следует заметить, что значение помещаемого результата в ячейку L1 будет зависеть от выбранного значения в списке, начиная сверху, независимо от самого значения указанного порядкового номера введенного в столбце А. Это следует учитывать при формировании порядковых номеров командировочного удостоверения. При выделении мышью значения порядкового номера в списке, например "3", это значение будет автоматически помещено в ячейку L1, в результате чего на экране монитора на листе КомандировочноеУдостоверение будет выведено командировочное удостоверение, имеющее порядковый номер записи 3 в журнале регистрации. Рядом с элементом Список поместите элемент управления Счетчик. С помощью счетчика можно последовательно "перебирать" в любом направлении командировочные удостоверения, находящиеся в журнале регистрации с шагом изменения равным единице или другим, заданным пользователем. Диалоговое окно Формат элемента управления для элемента управления Счетчик, несколько отличается от предыдущего. В поле Начальное значение отображается значение, которое содержится в этот момент времени в ячейке L1. В поле Минимальное значение введите начальный номер, с которого Счетчик начинает перебирать номера с шагом, значение которого будет введено в поле Шаг изменения. При желании введите значение в поле Максимальное значение, в противном случае Excel введет его самостоятельно.
5. Вывод на печать бланка командировочного удостоверения Для того чтобы при выводе на печать заполненного бланка командировочного удостоверения созданный документ был расположен на одном листе бумаги с двух сторон, задайте область печати, причем лицевой и обратной стороны отдельно. Для этого последовательно выполните следующие действия: · выделите мышью область А1:J20 (лицевая сторона командировочного удостоверения); · нажмите на клавишу Ctrl и, удерживая ее, выделите область А22:J69 (обратная сторона); · выполните команду Файл/Область печати/Задать. После этого с помощью команды Файл/Предварительный просмотр просмотрите, как разместится документ на бумаге и если необходимо, то произведите устранение недостатков, вызвав диалоговое окно Параметры страницы нажав на кнопку Страница в меню окна предварительного просмотра. Возможно два метода последовательного вывода пользователем на печать нескольких командировочных удостоверений, введенных предварительно в журнал регистрации. · 1-й метод. Вывод на печать одного командировочного удостоверения, печатая последовательно лицевую сторону, затем обратную. Для этого желательно, чтобы в принтере находился один лист бумаги. Тогда нажав на кнопку Печать на стандартной панели инструментов нужно лист, после вывода его из принтера с распечатанной лицевой стороной, перевернуть и "пропустить" через принтер с другой стороны. Далее повторить эту операцию с оставшимися командировочными удостоверениями. · 2-й метод заключается в том, чтобы отпечатать партию командировочных удостоверений с одной стороны, и затем пропустить эти листы бумаги через принтер с другой стороны. Недостаток этого метода заключается в том, что тогда каждый раз отправляя документ на печать нужно применять команду Файл/Печать, а после этого в диалоговом окне Печать отмечать номера печатаемых страниц, например, с 1-й по 1-ю, что тоже не совсем удобно. Недостаток обоих методов заключается в относительной трудоемкости выполнения операций, которая заключается в повторении ряда обязательных однотипных действий и, как следствие, возможности внесения ошибок. Пользователь должен повторять несколько одних и тех же действий, независимо от применяемого метода. В Справочной системе Microsoft Excel в разделе Автоматизация задач написано следующее: Справочная система Microsoft Excel "Если какое-то действие часто повторяется, его выполнение можно автоматизировать с помощью макроса. Макрос - это серия команд и функций. Их можно выполнять всякий раз, когда необходимо выполнить данную задачу. Перед тем как записать макрос, необходимо спланировать шаги и команды, которые он будет выполнять." 6. Запись макроса вывода документа на печать Чтобы писать макросы, не обязательно быть опытным (продвинутым) пользователем. Можно с помощью активизации средства записи макроса записать последовательность выполняемых действий. При этом программа записи автоматически преобразует последовательность действий пользователя в код на языке Visual Basic of Application (VBA), который является основой записи макроса. Принимаем за основу для автоматизации 2-й метод. Предполагаем, что необходимо отправить на печать лицевую сторону сформированных командировочных удостоверений с порядковыми номерами с 1-го по 3-й, после чего повторить ту же операцию с выводом на печать обратной стороны.. Исходя из этого однотипные действия 2-го метода по выводу на печать каждой стороны нужно повторить три раза. Значит было бы удобно отправлять документ на печать одним действием, например, нажатием на кнопку, после чего Excel должен самостоятельно вызвать диалоговое окно Печать, определить выводимые на печать страницы и нажать на кнопку ОК. Для того чтобы начать запись макроса, выполните команду Сервис/Макрос/Начать запись. Появится диалоговое окно Запись макроса. Если это первый записываемый макрос в открываемой книге, то тогда в поле Имя макроса будет указано его имя - Макрос1.
Многие пользователи не изменяют имя макроса и через некоторое время может возникнуть недоразумения - какую операцию выполняет этот макрос. Поэтому сразу измените его имя на ПечатьЛицевойСтороны. Имя макроса должно начинаться с заглавной буквы и между элементами текста имени не должно быть пробелов. В поле Сочетание клавиш введите букву на английском языке, например, "z". Это позволит сразу же после записи макроса, нажав на комбинацию клавиш [Ctrl+z] запустить макрос на автоматическое выполнение записанной операции. В поле Описание будет зафиксирована дата создания макроса и при желании в это поле с клавиатуры введите краткое описание задачи макроса, например, Предназначен для печати лицевой стороны командировочного удостоверения. В раскрывающемся списке Сохранить в находятся: · Личная книга макросов; · Новая книга; · Эта книга; это место, куда будет помещен и храниться записанный макрос. При создании этого и последующих приложений все макросы будут записываться в Этой книге, поэтому остальные варианты рассматриваться не будут.
После нажатия на кнопку ОК или клавишу Enter Excel начинает запись макроса, для чего фиксирует все действия пользователя, независимо от того, правильны ли они. Фиксация действий производится на языке кода VBA и будет осуществляться до остановки записи. Для остановки записи предназначена появляющаяся после начала записи кнопка Остановка.
Для записи кода действий, выполняемых макросом, последовательно выполните процедуры по выводу на печать лицевой стороны командировочного удостоверения: · выполните команду Файл/Печать; · в появившемся диалоговом окне Печать в области Печатать введите номера страниц с 1-й по 1-ю и нажмите кнопку ОК; · для остановки записи макроса нажмите кнопку Остановка или выполните команду Сервис/Макрос/Остановка записи. Для записи макроса печати оборотной стороны командировочного удостоверения нужно проделать аналогичную операцию по выводу на печать страницы 2. Просмотр записанного макроса заключается в просмотре непосредственно сгенерированного текста кода VBA при его записи. Для этого, выполнив команду Сервис/Макрос/Редактор Visual Basic или нажав комбинацию клавиш [Alt+F11], откройте приложение Microsoft Visual Basic.
При запуске Редактора Visual Basic в этом приложения присутствуют три основных окна: · окно проекта; · окно свойств; · окно программы. Окно проекта расположено слева вверху и содержит несколько папок. В нашем случае имеются папка Microsoft Excel Объекты, в которой находятся объекты, с которыми производятся действия: · Лист1 (КомандировочноеУдостоверение); · Лист2 (ЖурналРегистрацииКУ); · ЭтаКнига (в которой записаны эти макросы). Вторая папка - Модули, в которой находится элемент Модуль1, содержащий записанные макросы вывода на печать 1-й и 2-й страницы. При создании диалоговых окон и форм может находиться и третья папка - Формы, но в этой книге она рассмотрена не будет. Окно проекта предназначено для управления различными элементами проекта VBA и открывается с помощью команды Вид/Окно проекта.
Если обратиться к окну проекта, показанному на рисунке то в объекте Модули находится один элемент Модуль1. Это тот элемент, который создан автоматически при записи макросов вывода документов на печать. Окно свойств находится в левом нижнем углу. В окне свойств перечисляются характеристики элементов проекта. Для изменения свойств элемента, выделите его в окне проекта и тогда его характеристики отобразятся в окне свойств. Если выделить элемент Модуль1, то в окне свойств отобразится его имя: Модуль1. Для изменения имени модуля в окне свойств измените значение свойства Name, введя новое имя – Печать.
Окно программы самое большое и находится справа. Именно в нем происходит запись текста макроса на языке Visual Basic of Application. Окно программы имеет два режима просмотра: · представление полного модуля; · представление процедуры. Переключение между этими режимами осуществляется с помощью кнопок, находящимися в левом нижнем углу окна программы. В режиме представления процедуры отображается только один макрос. Для перехода к следующему макросу, его имя нужно выбрать в раскрывающемся списке в верхней части окна программы или воспользоваться клавишами [Ctrl +Page Up] и [Ctrl+Page Down]. В режиме представления в окне программы находятся все макросы, в записанной последовательности. Ознакомимся с кодом VBA макроса ПечатьЛицевойСтороны. Если окно программ закрыто, то для просмотра макроса в окне проекта активизируйте двойным нажатием на правую кнопку мыши элемент Печать объекта Модули. Текст макроса в окне программы содержит следующие элементы: · ключевые слова Sub и End Sub, означающие начало и конец текста макроса, шрифт которых выделен синим цветом · имя макроса, которое следует за ключевым словом Sub начала макроса; · комментарии, шрифт которых выделен зеленым цветом; · код VBA или непосредственно записанные действия, которые выполняет макрос. Комментарии. В нашем примере это те данные, которые вводились в диалоговое окно Запись макроса. Строка комментария предназначена для описания действий и начинается с апострофа. Удаление комментариев никоим образом не влияет на выполнение макроса. Более подробно комментарии будут рассмотрены далее. И, наконец, код записанных действий, выполняемых макросом: ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1 говорит о том, что с активного окна выделенного (активного) рабочего листа отправить на печать страницы с 1-й по 1-ю в количестве одной копии. Текст макроса, который начинается и заканчивается ключевыми словами Sub и End Sub, называется подпрограммой или процедурой, что одно и то же. 7. Запуск записанных макросов на выполнение из приложения Excel Запуск макросов на выполнение может осуществляться как из приложения Microsoft Visual Basic, так и из приложения Microsoft Excel. Из Excel выполнение макроса задается двумя способами: · используя комбинации назначенных клавиш при записи макроса [Ctrl+z] - печать лицевой стороны или [Ctrl+x], для печати обратной стороны командировочного удостоверения; · используя диалоговое окно Макрос.
Диалоговое окно Макрос вызывается командой Сервис/Макрос/Макросы или комбинацией клавиш [Alt+F8]. Для выполнения макроса выделите в списке имя макроса и нажмите кнопку Выполнить. С помощью этого же диалогового окна можно войти в выделенный в списке макрос, для чего нажмите на кнопку Войти. Это позволит открыть приложение Microsoft Visual Basic и в выбранном макросе будет выделена первая строка с ключевым кодом начала подпрограммы и его именем. Этот режим называется режимом отладки. Описанная выше последовательность действий пользователя для запуска макросов на выполнение не является самой оптимальной, требует выполнения нескольких действий или запоминания комбинации клавиш запуска макроса, и может вызвать ошибку в действиях пользователя. Для устранения этих недостатков внедрите на рабочем листе КомандировочноеУдостоверение элемент управления Кнопка, нажатие на которую будет активизировать выполнение макроса, назначенного этой кнопке. Создание кнопки осуществляется аналогично созданию элементов Счетчик и Список, описанных выше. Для этого, вызвав панель Формы, создайте элемент управления Кнопка, по завершению создания которой, появится диалоговое окно Назначить макрос объекту.
Для назначения кнопке макроса выделите в диалоговом окне Назначить макрос объекту нужный макрос и нажмите кнопку ОК. Теперь нажатие на созданную кнопку вызовет выполнение назначенного ей макроса.
Для того чтобы в дальнейшем не задумываться над тем, какие действия последуют за нажатием на кнопку, поместите указатель мыши на кнопку и щелкните правой кнопкой мыши, что вызовет появление контекстного меню, в котором выделите команду Изменить текст. На рисунке показаны созданные кнопки с измененным текстом для вывода на печать лицевой и обратной стороны командировочного удостоверения.
На рисунке показан рабочий лист КомандировочноеУдостоверение со всеми встроенными элементами управления.
|
