Автоматизация и моделирование бизнес-процессов в Excel

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

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

Доверенности

На рис. 7.1. показан пример генеральной доверенности на автомобиль, выполненной в текстовом редакторе после выполнения автоматизации и с уже внедренными объектами экспорта данных из Excel.

Рис. 7.1. Пример генеральной доверенности на право управления и распоряжения автомобилем

Данная доверенность содержит шесть абзацев:

• место и дата оформления;
• данные о том — кто доверяет;
• данные о том — кому доверяют;
• данные об автомобиле, документе, подтверждающем право собственности и учреждении, в котором зарегистрирован автомобиль;
• на какой срок выдана доверенность и дату, по которую она действительна;
• данные о том, что доверенность засвидетельствована.
• которые имеют данные, изменяемые при оформлении автомобиль на каждого нового клиента.

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

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

Новый созданный документ может сохраняться в электронном виде на жестком диске компьютера, а может и нет. Если клиент приходит с подобным заказом через месяц, то в 99 случаях из 100 документ будут готовить заново. Потому что считается, что хранение сотен почти одинаковых текстовых документов в электронном виде нецелесообразно.

Ошибки, которые могут быть внесены в процессе редактирования, путешествуют из документа в документ и не всегда бывают замечены. А если и бывают, то после исправления в одном файле, могут впоследствии всплыть в другом. Ведь неизвестно какой из файлов будет открыт для последующего редактирования — с исправленной ошибкой или оставшейся.

Если Вы заметили, то это очень напоминает описанную ранее работу специалиста по заполнению командировочных удостоверений или подготовке типового договора.

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

Формирования дат

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

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

Дата цифрами в текстовом формате

На рис. 7.2. приведен пример визуального отображения даты, ссылка на ячейку в которую она введена, находится внутри текстовой функции СЦЕПИТЬ, введенной в ячейку С1. Этот пример похож на текстовое отображение числовых данных, приведенных в прошлой главе.

Рис. 7.2. Пример отображения даты в текстовом формате

Ячейка В1 отформатирована как дата в формате Excel. В аргументе функции СЦЕПИТЬ задана ссылка на ячейку В1. Эта текстовая функция автоматически переводит дату в число, равное количеству дней, прошедших с 1 января 1900 года, от которой Excel ведет исчисление дат. В таком виде эта дата и будет попадать в текст. Естественно, в таком отображении, дата в текстовом документе будет не читаема. Поэтому необходимо разработать алгоритм отображения даты внутри текста в таком же виде, как она представлена в ячейке В1 на рис. 7.2.

На рис. 7.3. на примере формулы, введенной в ячейку С6 и отображенной в строке формул, приведен алгоритм перевода видимого визуально формата даты Excel в текст, сохраняющий визуально этот формат.

Рис. 7.3. Формула отображения в текстовом виде формата даты

В формуле используются:

• текстовая функция СЦЕПИТЬ — для соединения отдельных элементов, составляющих дату и введенного текста в единую текстовую строку;
• функции дат — для деления даты отдельно на элементы: день, месяц и год;
• логическая функция ЕСЛИ.

Рассмотрим вторую строку введенной формулы. Первый аргумент функции ЕСЛИ проводит сравнение номера дня со значением 9 и если номер дня равен или меньше значения 9, то тогда во втором аргументе, с помощью функции СЦЕПИТЬ к значению номера дня слева добавляется значение "0". Если условие первого аргумента не выполняется, то номер дня возвращается третьим аргументом функции ЕСЛИ без изменений.

С номером месяца производится та же операция. И в завершение, функция СЦЕПИТЬ, соединяет в единое целое полученные значения составляющих даты, вставив между ними точки и добавив в конце текст — "г.".

Написание даты прописью

Для написания прописью номера дня и года можно применить таблицу, показанную на рис. 7.4. Отличие текста написания номера дня от номера года зависит только в склонении, например, номер дня 11 пишется одиннадцатое, а такой же год — одиннадцатого.

В таблице ячейка С5 является входом, в которую вводится число (или ссылка на ячейку, содержащую это число), а ячейка D5 выходом, в которой это число уже пишется прописью.

В области ячеек С6:D7 выделяются отдельно десятки и единицы этого числа. Формулы в ячейках С8:С10 предназначены для написания прописью количества десятков, а в ячейках D8:D16 для написания прописью единиц. Таблица пишет прописью числа от единицы до тридцати девяти.

Рис. 7.4. Таблица написания прописью номера дня в месяце

После создания таблицы и тестирования, ее размеры минимизируются путем вложения формул. На рис. 7.5. приведена минимизированная таблица (модуль) написания номера года. Модуль расположен в ячейках С6:F6, в которой ячейка С6 является входом, а ячейка F6 выходом. Номер года пишется от 2001 до 2039, причем прописью определяется только число от 1 до 39, а текст две тысячи дополняется.

Рис. 7.5. Модуль написания прописью номера года

Если соединить все три модуля написания текста числа, месяца и года в единое целое, то получится один модуль написания даты прописью. Как пример, такой модуль показан на рис. 7.6.

Рис. 7.6. Полученный модуль написания даты прописью

Расчет полного количества лет, дней и месяцев в интервале указанных дат

На практике довольно часто приходится определять количество дней, месяцев и лет между двумя датами. При классическом использовании Excel возможны два метода их определения, которые показаны на рис. 7.7.

Метод 1 (строки 4:6). Вычтя одну дату из другой и применив функции ГОД, МЕСЯЦ и ДЕНЬ, можно определить эти данные, но Excel анализирует дату, полученную в результате вычитания, и поэтому полученный результат не будет достоверным.

Метод 2 (строки 8:10). Состоит в том, чтобы определить год, месяц и день отдельно в двух датах, а затем произвести операцию вычитания этих составляющих. Но при вычитании отдельно лет, месяцев и дней результат может носить отрицательный характер, а такого быть не может.

Рис. 7.7. Примеры определения количества дней, месяцев и лет в интервале между двумя датами

На основании второго метода попытаемся проанализировать и использовать полученные результаты. На рис. 7.8. приведен пример модуля определения количества лет, месяцев и дней. Модуль занимает диапазон ячеек В4:D6. В ячейки В4 и В5, являющимися входом модуля, вводятся даты. Выходы модуля:

• ячейка D4 — для вывода количества лет;
• ячейка D5 — для вывода количества месяцев;
• ячейка D6 — для вывода количества дней.

Рис. 7.8. Модуль расчета количества лет, месяцев и дней в интервале между датами

Формула в ячейке D4 работает по следующему алгоритму:

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

Похожий алгоритм заложен и в формулах для определения количества месяцев и дней в ячейках D5 и D6.

Формулы в ячейках С4 и С5 проводят дополнительный анализ количества календарных дней в месяце, находящемся в большей дате (ячейка В4):

Если месяц, даты введенной в ячейку В4, имеет значение 3, то необходимо дополнительно проверить, високосный это год или нет. Для анализа високосного года в дате, введенной в ячейку В4, в формуле в ячейке В5 применяется функция Excel ОСТАТ (рис. 7.9.). Если год високосный, то результат деления этого года на 4 будет равен нулю.

Если в результате деления получена дробная часть (т.е. год не високосный), то количество дней в месяце, предшествующем 3-му месяцу, будет равно 28. Если год високосный, то функция ОСТАТ выдаст значение 0. Тогда вторая функция ЕСЛИ при выполнении условия сравнения со значением 0 перейдет к своему второму аргументу, где указано значение 29 (дней в феврале).

Рис. 7.9. Панель функции ОСТАТ

Для того чтобы написать прописью определенные количество месяцев, дней и лет, необходимо соединить в единое целое (рис. 7.10.):

• модуль определения этих параметров — область ячеек В4:D6;
• модуль написания текста этих вычисленных параметров — область ячеек В9:D14.

Входами модуля написания текста являются ячейки В9; С9 и D9, в которые введены ссылки на соответствующие ячейки выхода модуля определения параметров.

Рис. 7.10. Модули написания прописью количества лет, месяцев и дней между двумя датами

Структура создаваемого приложения

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

Рис. 7.11. Структура потоков информации и содержания рабочих листов создаваемого приложения

Лист Реквизиты

На рабочем листе Реквизиты (рис. 7.12.) расположена база данных о клиентах и информация об автомобиле, которая необходима для автоматизированного создания доверенности. Только табличной базой данных ее можно назвать условно, потому что нарушены некоторые принципы формирования списка Excel.

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

На листе Реквизиты, для удобства группировки данных, заголовок содержит три строки (область А1:АF3). Данные первой строки проводят группировку по месту и дате заключения доверенности, данным по каждому клиенту и автомобилю. Во второй строке данные группируются более детализировано. Например, данные по клиенту — кто, удостоверяющий его документ и место проживания. По автомобилю — данные свидетельства о регистрации и данные об автомобиле. Из-за удобства этих группировок, отдельные ячейки всех трех строк заголовка объединены.

В четверной строке введены порядковые номера столбцов от 0 до 31.

Из-за того, что заголовок состоит из трех строк невозможно установить Автофильтр и применить диалоговое окно Форма. Это будет затруднять поиск необходимой информации и ввод данных.

Рис. 7.12. Рабочий лист Реквизиты с информацией о клиентах и автомобилях

Удобный просмотр данных можно осуществлять при помощи фильтрации. Это будет возможно при удалении трех верхних строк 1:3. Но удалять эти строки на созданном рабочем листе Реквизиты не совсем корректно. Для этого желательно создать точную копию листа, а после просмотра данных этот лист удалить. Но проведение этой операции вручную трудоемко. Для ускорения процесса последовательно запишем три макроса.

Макросы

Макрос СоздатьСписок

Для записи макроса СоздатьСписок выполните следующие действия:

• поместите курсор на ярлычок листа Реквизиты и, нажав клавишу Ctrl, создайте точную копию листа Реквизиты(2) перемещением указателя мыши направо;
• выделите строки 1:3 и выполнить команду Правка/Удалить;
• выполните команду Данные/Фильтр/Автофильтр;
• поместите табличный курсор в ячейку В2.

Записанный макрос после ввода комментариев будет выглядеть как на рис. 7.13. (кроме строки Макрос создания кнопки, которая будет рассмотрена далее). Поместите на листе Реквизиты кнопку и назначьте ей созданный макрос.

Вначале макрос создает копию листа Реквизиты. Цифра 6 указывает на то, что этот лист будет находиться шестым по счету слева направо.

Copy Before:=Sheets(6)

Рис. 7.13. Окно программ Редактора Visual Basic с макросами создания точной копии листа, создания и присвоения кнопки и удаления листа

Макрос УдалитьСписок

Если отсутствует потребность в созданной копии базы данных в виде списка, то для его удаления напишите макрос УдалитьСписок, выполнив последовательность действий:

• поместив курсор на ярлычок листа Реквизиты(2) нажмите правую кнопку мыши и, выделив команду Удалить, нажмите на левую кнопку мыши;
• в появившемся диалоговом окне Microsoft Excel с предупреждением о том, что отмена операции удаления выделенных листов будет невозможна, нажмите кнопку ОК.

Макрос СозданиеКнопки

Для создания кнопки, нажатие на которую вызовет выполнение макроса УдалитьСписок, запишите макрос СозданиеКнопки. При записи макроса предполагается, что панель Формы не активизирована, поэтому при записи макроса предполагается ее активизация. Действия:

• выполните команду Вид/Панели инструментов/Формы и с помощью панели Формы создайте и разместите кнопку на листе Реквизиты(2);
• в появившемся окне Назначить макрос (рис. 4.18.) назначьте макрос УдалитьСписок;
• закройте панель Формы.

Теперь при нажатии на созданную кнопку лист Реквизиты(2) вместе с созданной кнопкой будет удален или самоликвидирован.

После написания макроса, первую и четвертую строку кода, можете удалить, или перевести в разряд примечаний.

Введение выполнения макроса в другой макрос

Для того чтобы при создании листа Реквизиты(2) была создана кнопка удаления этого листа, нужно задать из макроса СозданиеСписка ссылку на выполнение макроса СозданиеКнопки. Для этого перейдите в Редакторе Visual Basic на макрос СозданиеКнопки, выделите имя макроса после оператора Sub и скопируйте в буфер обмена. Перейдите в макрос СозданиеСписка и произведите вставку в строке перед командой перехода на ячейку В2.

Обманите Excel

Как видите, для того чтобы воспользоваться Автофильтром, необходимо таблице задать параметры списка, для чего нужно проделать ряд не совсем удобных процедур. Это не совсем удобно. Попытайтесь обмануть Excel!

Для этого выполните следующее:

• выделите строку 4 и выполните команду Вставка/Строки;
• после вставки строки выделенной будет вставленная строка 4. Выполните команду Правка/Очистить/Все;
• скройте эту строку, выполнив команду Правда/Строка/Скрыть;
• установите Автофильтр.

Почему это произошло будет описано в следующих главах.

Рабочий лист Действия

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

Для этого на листе Действия (рис. 7.14.) с помощью панели Формы внедрите несколько элементов управления Флажок. С помощью диалогового окна Формат элемента управления свяжите этот элемент с ячейкой, расположенной правее этого элемента.

Рассмотрим второй элемент управления Флажок, производящий ввод в доверенность текста — Продать. При его создании он размещен и ограничен размерами ячейки С5. Этот элемент управления связан с ячейкой Е5. При активизации элемента Продать ячейка Е5 возвращает значение ИСТИНА. В ячейку С5 введите текст продать, который будет вводиться в доверенность при активизации этого элемента. В ячейке F5 находится формула:

=ЕСЛИ(E5;СЦЕПИТЬ(B5;", ");"")

в которой функция ЕСЛИ анализирует значение ячейки Е5. Если элемент Продать не активизирован, то содержимое ячейки Е5 равно значению ЛОЖЬ, и тогда третий аргумент функции ЕСЛИ ничего не возвращает. В противном случае во втором аргументе функции ЕСЛИ функция СЦЕПИТЬ соединяет, текст "продать", введенный в ячейку В5, с запятой и пробелом, введенными во второй аргумент функции СЦЕПИТЬ.

Задача формулы, введенной в ячейку С15 (показана в строке формул на рис. 7.14.), соединить текст выбранных действий в единую текстовую строку и при этом удалить два последних текстовых символа, которыми являются запятая и пробел. Для этого из всего текста с помощью функции ЛЕВСИМВ выбирается столько левых символов, сколько находится в объединенном функцией СЦЕПИТЬ тексте, определенном функцией ДЛСТР за минусом значения 2.

Рис. 7.14. Рабочий лист Действия

Макросы перехода

На рабочем листе Действия (рис. 7.14.) находится кнопка с текстом Переход на лист Ввод. Подобные кнопки созданы для удобства работы и при нажатии на них происходит переход на указанный рабочий лист. Можно сказать, что это аналог гиперссылки, рассмотренных в прошлой главе.

Для записи такого макроса достаточно после начала записи макроса активизировать лист Ввод и остановить запись.

На рис. 7.15. показаны макросы, написанные для перехода на другие рабочие листы.

Рис. 7.15. Окно программ Редактора Visual Basic с макросами перехода на другие рабочие листы

Рабочий лист Ввод

Рабочий лист Ввод достаточно многофункциональный. Его задачами являются:

• облегчение просмотра базы данных клиентов, потому что вертикально расположенные данные воспринимаются пользователем легче, чем расположенные горизонтально;
• позволяет облегчить, ускорить и уменьшить количество ошибок вводимых в доверенность за счет экспорта данных из базы данных клиентов;
• помещает данные о клиентах в создаваемой доверенности в лист Реквизиты;
• служит своеобразным буфером для разрыва связи между формулами разрабатываемого приложения, за счет чего обеспечивается уменьшение занимаемого объема на жестком диске и быстродействие проведения расчетов.

Создание листа Ввод

Для создания области заголовка, но расположенного вертикально на рабочем листе Реквизиты выделите диапазон ячеек А1:АF4 и скопируйте в буфер обмена. Перейдите на лист Ввод и, выделив ячейку D16, вызовите диалоговое окно Специальная вставка, в котором отметьте опцию Транспонировать, произведите вставку из буфера обмена. Для придания заголовкам внешнего вида потребуется корректировка линий границ ячеек.

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

Формула в ячейке Н30, показанная в строке формул на рис. 7.16., использует для поиска данных функцию ГПР. В качестве искомого значения для поиска используется порядковый номер столбцов, которые были введены на листе Реквизиты в строку 4, и при вставке на лист Ввод находятся в столбце G. Область поиска расположена в области листа Реквизиты ограниченной строками 4:1000, а в качестве номера строки, по которому выбирается запись, используется увеличенное на единицу значение, введенное в ячейку D9 и управляемое элементом управления Счетчик.

Рис. 7.16. Рабочий лист Ввод

Для последовательного просмотра данных листа Реквизиты можно воспользоваться элементом управления или для просмотра конкретной записи ввести в ячейку D9 порядковый номер записи на листе Реквизиты.

Обновление формул

На практике часты случаи неосторожного удаления введенных ранее формул. Для страховки запишите макрос (рис. 7.17.), вводящий формулы поиска в диапазон Н16:Н47 для чего выполните следующие действия:

• перед записью макроса предварительно введите формулу в ячейку Н16;
• после начала записи макроса выделите диапазон ячеек Н16:Н47 начиная с ячейки Н16;
• нажмите клавишу F2 (переход в режим редактирования активной ячейки), после чего нажмите комбинацию клавиш [Ctrl+Enter];
• остановите запись макроса.

Рис. 7.17. Макросы ОбновитьФормулы, Очистка и СкопироватьПункт

Для создания и последующей вставки формул создайте элемент управления Кнопка Обновить формулы поиска с назначенным ей макросом ОбновитьФормулы.

Элементы автоматизации ввода данных

Очистка области ввода данных

Для ввода данных в доверенность предназначен диапазон ячеек I16:I46. Но прежде чем вводить новые данные желательно произвести очистку этого диапазона. Для этого запишите макрос Очистка (рис. 7.27.). Запись макроса заключается в выделении этого диапазона ячеек, ячейки F13 и последующем выполнении команды Правка/Очистить/Содержимое или нажатии на клавишу Delete.

Для очистки содержимого предназначена кнопка Очистка.

Ввод данных

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

Этой кнопке назначен макрос СкопироватьПункт (рис. 7.17.). Этот макрос копирует выделенный диапазон ячеек, выделяет ячейку, находящуюся на одну ячейку правее от правой верхней ячейки выделенного диапазона и производит вставку значений, определенных формулами, находящимися в этом диапазоне.

Проверка содержимого двух областей на соответствие

Довольно часто на практике применяется автоматический контроль за выполнением пользователем проводимых операций. Контроль может быть самым разнообразным и, как правило, предназначен для повышения эффективности выполняемой работы.

В качестве примера рассмотрим соответствие содержащихся данных в диапазонах ячеек Н16:Н47 и I16:I47.

В ячейку J16 введите формулу:

=H16=I16

которая заключается в сравнении полученного результата формулы в ячейке Н16 и текстового содержания, введенного в ячейку I16. Если эти данные идентичны, то формула возвращает значение ИСТИНА, если есть различия, то значение — ЛОЖЬ. Если скопировать эту формулу в ячейки J17:J47 и объединить полученные результаты, заключив их в формулу в ячейке F13:

=ЕСЛИ(И(J16:J47);"Вводимые данные и данные базы данных соответствуют";"Соответствия НЕТ!")

то при наличии расхождений хоть в одной ячейке диапазона Н16:Н47 от ячеек диапазона I16:I47 формула сформирует запись: Соответствия НЕТ! и в противном случае: Вводимые данные и данные базы данных соответствуют.

Анализ расхождений проводит логическая функция И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА или возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. А функция ЕСЛИ в зависимости от полученного результата возвращает текст, помещенный во второй или третий аргументы.

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

Механическая запись макроса ПроверкаДанныхВвод производится в следующей последовательности:

• до начала записи формул введите формулы в ячейки J16 и F13;
• произведите запись ввода формул в диапазон J16:J47, затем в ячейку F13, в которой после этого замените формулу на определенной ею значение, произведите окраску в ней шрифта на красный цвет;
• после этого очистите содержимое диапазона J16:J47.

Сравните записанный макрос с макросом, приведенным на рис 17.18. Как видите, отличия существенны, и заключаются в присутствии инструкции Select Case и некотором изменении кода VBA.

Рис. 7.18. Макрос ПроверкаДанныхВвод

Внесенные изменения

Основное отличие отредактированного макроса (подпрограммы) заключается в том, что в процессе его выполнения не происходит выделение (активизация) никаких ячеек и диапазонов для ввода формул или очистки области. Об этом говорит отсутствие соответствующих операторов (Select) в тексте подпрограммы. Макрос производит ввод формул в ячейки, замену их на вычисленное значение, окраску шрифта и очистку диапазона не выделяя эти ячейки.

Какие это дает преимущества? Во первых, увеличение быстродействия в несколько раз, во вторых, лучшую читаемость и понятность текста кода и третьих, отсутствие мельтешения на экране монитора, вследствие перемещения табличного курсора по рабочему листу как это было ранее. Подпрограмма при помощи редактирования создается довольно быстро.

Опишем некоторые детали кода VBA макроса.

В ячейку F13 вводится формула, а затем при помощи строки:

Range("F13").Value = Range("F13")

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

Вторая деталь — это строка:

Range("J16:J47").ClearContents

при помощи которой производится очистка содержимого диапазона ячеек, не выделяя его.

Инструкция Select Case

Select Case выполняет одну из нескольких групп инструкций в зависимости от значения выражения.

Инструкция — это синтаксически завершенная конструкция, представляющая отдельное действие, описание или определение, и, как правило, занимает одну строку.

Выражение — это комбинация ключевых слов, операторов, переменных и констант, результатом которой является строка, число или объект.

Синтаксис инструкции Select Case содержит следующие элементы

Select Case выражение
[Case списокВыражений-n
[инструкции-n]] ...
[Case Else
[инструкции_else]]
End Select

выражение — обязательный аргумент. Это любое числовое выражение или строковое выражение.

списокВыражений-n - обязательный аргумент при наличии предложения Case. Это список с разделителями, состоящий из одной или нескольких форм следующего вида: выражение, выражение To выражение, Is операторСравнения выражение. Ключевое слово To задает диапазон значений. При использовании ключевого слова To перед ним должно находиться меньшее значение. Ключевое слово Is с операторами сравнения задает диапазон значений. Если ключевое слово Is не указано, оно вставляется по умолчанию.

инструкции-n - необязательный аргумент. Это одна или несколько инструкций, выполняемых в том случае, если выражение совпадает с любым компонентом списка списокВыражений-n.

инструкции_else - необязательный аргумент. Это одна или несколько инструкций, выполняемых в том случае, если выражение не совпадает ни с одним из предложений Case.

Удобство использования инструкции Select Case заключается в возможности выбора одного или нескольких возможных вариантов из всех предложенных. В нашем примере в зависимости от результата, полученного в ячейке F13, происходит изменение шрифта, которым пишется этот текст. Либо голубой, либо красный.

Рассмотрим выполнение Select Case на нашем примере:

Перенос данных из листа Ввод в лист Реквизиты

Следующая задача — перенести введенные данные диапазона ячеек I16:I47 листа Ввод в лист Реквизиты. Эту задачу выполняет макрос ПоместитьДанныеРеквизиты (рис. 7.19.).

Макрос выполняет следующие действия:

• копирование диапазона ячеек I16:I47 на листе Ввод;
• переходит на лист Реквизиты;
• производит поиск первой пустой строки, выделяет в ней ячейку в столбце В и производит транспонированную вставку значений;
• переходит на одну ячейку влево, определяет максимальный порядковый номер в столбце А, добавляет к нему единицу и вводит это значение в ячейку;
• возвращается на лист Ввод.

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

Рис. 7.19. Макрос переноса данных из листа Ввод в лист Реквизиты

Перенос данных из листа Ввод в лист Текст

Макрос ПереносДанныхТекст производит копирование диапазона ячеек I16:I47 на листе Ввод, переходит на лист Текст и производит вставку скопированного в диапазон ВВ1:ВВ32.

Рис. 7.20. Макрос переноса данных из листа Ввод в лист Текст

Рабочий лист Текст

Рабочий лист Текст предназначен для формирования текста доверенности.

Модули преобразования дат в текстовый формат

В столбцах АА:АD с первой строки по 19-ю расположены созданные ранее модули формирования текстовых форматов дат (рис. 7.21.). В ячейки входов этих модулей введены имена ячеек, в которых находятся даты, фигурирующие в тексте доверенности. Например, как видно на рис. 7.21. в ячейку АА1 введено имя П_02, которое присвоено ячейке ВВ2 (дата выдачи доверенности).

Рис. 7.21. Фрагмент рабочего листа Текст с модулями формирования текстовых форматов дат

Присвоение имен ячейкам рабочего листа

До этой главы при ссылках на ячейки (диапазоны) указывались их адреса (или место расположения их на рабочем листе). Это не совсем удобно. Иногда желательно, чтобы вместо загадочного адреса в формулах присутствовала информация о том, что же находится в этой ячейке. Excel позволяет присваивать имена отдельным ячейкам и диапазонам ячеек.

Присвойте имена ячейкам диапазона ВВ1:ВВ32, в которую производится вставка данных из рабочего листа Ввод,. Наиболее простой и эффективный способ присвоения имен каждой ячейке диапазона заключается в следующем:

• в ячейки диапазона ВА1:ВА31 введите текст имен от П 01 до П 32;
• выделите диапазон ячеек ВА1:ВВ32 и нажмите комбинацию клавиш [Ctrl+ Enter+F3];
• в появившемся диалоговом окне Создать имена Excel (рис. 7.22.), пытаясь "угадать" в какой области ячеек введен текст имен, отмечает опции В строке выше и В столбце слева в области По тексту. Флажок в опции В строке выше снимите, иначе ячейке ВВ1 не будет присвоено никакого имени, а диапазону ВА2:ВВ32 будет присвоено два имени — П_01 и текста, находящегося в ячейке ВВ1.

Рис. 7.22. Фрагмент рабочего листа Текст с присвоением имен ячейкам диапазона с данными и диалоговым окном Создать имена

Ячейкам ВВ1:ВВ32 будут последовательно присвоены имена от П_01 до П_32. Для того чтобы просмотреть имена и адреса ячеек, которым это имя присвоено, выполните команду Вставка/Имя/Присвоить в результате чего появится диалоговое окно Присвоение имени (рис. 7.23.). При выделении строки в области Имя в поле Формула будет указан адрес ячейки (или адрес диапазона), которой принадлежит это имя. Двух одинаковых имен на рабочем листе быть не может.

Присвоение имен ячейкам выхода модулей формирования дат

Для присвоения имен ячейкам выхода модулей формирования текста дат, поместите табличный курсор на нужную ячейку и выполните команду Вставка/Имя/Присвоить. В появившемся окне Присвоение имен в поле Имя введите имя ячейки, понятное пользователю.

Рис. 7.23. Диалоговое окно Присвоение имен

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

Для этого в нашей книге с помощью команды Вставка/Лист произведите вставку нового рабочего листа и, выделив ячейку С1, выполните команду Вставка/Имя/Вставить. В появившемся диалоговом окне Вставка имени (рис. 7.24.) нажмите кнопку Все имена.

Рис. 7.24. Диалоговое окно Вставка имени

На рабочем листе Имена в области С1:В38 (рис. 7.25.) будут введены:

• в столбце С — имена ячеек;
• в столбце D — рабочий лист и адреса ячеек, которым присвоены эти имена.

Рис. 7.25. Рабочий лист Имена с присвоенными именами

Формирование текста доверенности

Текст семи пунктов доверенности формируется в ячейках В2:G7 (рис. 7.26. и 7.27.).

Рис. 7.26. Фрагмент рабочего листа Текст с формулами текста доверенности

Рис. 7.27. Фрагмент рабочего листа Текст с текстом доверенности

Рассмотрим формулу, основанную на функции СЦЕПИТЬ, которая находится в ячейке В3. Она содержит:

текст "Я";

имя ячейки П_04, в которую введено фамилия, имя, отчество доручителя;

далее в скобках идут вперемежку ссылки на имена, присвоенные нами и после скобок имена, назначение которых легко проследить.

Сохранение переменного текста доверенности

Если подготовленный документ оформлен без ошибок, поместите его в табличную базу данных (рис. 7.28.).

Рис. 7.28. Табличная база данных с текстами оформленных доверенностей

Макрос сохранения текста доверенности ВводБазаДанных показан на рис. 7.29. Его задачей является следующее:

• скопировать область ячеек В2:G7 на листе Текст;
• перейти на лист БазаДанных и найти первую пустую строку, в которой выделяет ячейку в столбце В и производит транспонированную вставку значений скопированного диапазона;
• переходит на одну ячейку влево и производит вставку значения порядкового номера;
• возвращается на лист Текст.

Рис. 7.29. Окно программ с макросом переноса данных на рабочий лист БазаДанных

Просмотр созданных доверенностей из табличной базы данных

Для просмотра текста доверенностей, введенных в табличную базу данных, предназначен рабочий лист Просмотр (рис. 7.30.). Его функции такие же, как и одноименного листа для просмотра текста договоров, который описан в прошлой главе.

Создается этот лист копированием листа Текст, после чего в него вводятся следующие изменения:

• формулы, основанные на функции СЦЕПИТЬ, заменяются на формулы поиска информации на основе функции ВПР (см. строку формул на рис. 7.30.);
• вместо элементов управления Кнопка создайте элемент управления Счетчик, который управляет ячейкой I2;
• очистите формулы, введенные в правой стороне листа;
• создайте текстовый файл Просмотр, в который внедрите динамичные ссылки на лист Просмотр.

Задача этого листа — просмотр имеющихся в табличной базе данных на листе БазаДанных доверенностей.

Рис. 7.30. Лист Просмотр для просмотра доверенностей введенных в базу данных

Выводы

В этой главе описана автоматизация формирования текстового документа, но несколько в другом разрезе, как это было сделано в прошлой главе.

Особое внимание уделено процессу перевода дат из формата Excel в текстовый формат, расчету временного отрезка между двумя датами.

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