Создаем реестр договоров. Часть 2
 | В предыдущей статье мы сформировали реестр документов (это были договора купли-продажи) и узнали, как из этого реестра можно быстро получить конкретный экземпляр договора. Причем основным инструментом для решения такой задачи был MS Excel 2010. Сегодня мы пойдем дальше и посмотрим, как с использованием реестра можно обработать целую группу договоров. Речь в данном случае пойдет о формировании и подготовке к печати сразу нескольких экземпляров документов с разными параметрами. О том, как это сделать в пакете MS Office 2010, где еще могут пригодиться рассмотренные нами инструменты в практике бухгалтера, мы поговорим в этой статье. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Первым делом подведем итог и определимся, — что мы сделали, чем располагаем и какую задачу собираемся решить. Итак, у нас есть бланк договора, форма которого изображена на рис. 1. Договор оформлен в формате программы Word. В этом документе серым цветом выделены те поля, которые представляют собой переменную информацию. К ним, например, относятся номер договора, дата его заключения, название предприятия, фамилия его руководителя, платежные реквизиты, адрес и т. д. Все эти данные для разных договоров мы записали в базу данных Excel, фрагмент которой показан на рис. 2. Каждая колонка в базе соответствует определенному полю в договоре. А одна строка базы содержит всю переменную информацию об одном конкретном экземпляре документа. Так, номер договора находится в колонке «С» с названием «№ дог.». В колонке «D» под заголовком «Дата» есть данные о дате заключения договора и т. д. И теперь наша задача будет такой: для каждой записи из базы данных напечатать свой экземпляр документа.
Решить такую проблему можно разными способами. Однако наиболее приемлемый из них, на мой взгляд, — это инструмент слияния MS Office 2010. Иногда этот инструмент называют созданием рассылок. Именно его мы и применим для решения нашей задачи.
Инструменты слияния MS Office 2010
О создании рассылок мы писали на страницах нашей газеты. Например, — в «Б & К» № 23(71) от 14.12.2009 г. Поэтому повторяться не стану — просто напомню вкратце основную идею слияния, и как она работает. Рассылка позволяет объединить в одно целое базу данных и текстовый документ. Базу данных обычно создают в программе Excel или Access, иногда используют адресную книгу Outlook. Сам документ создают в программе Word. При этом из текстового документа удаляют всю переменную информацию, а на ее место вставляют т.н. поля слияния, то есть ссылки на элементы базы данных. После чего при помощи инструментов слияния можно выбрать любую часть базы данных, для каждой записи получить индивидуальный экземпляр документа, а результат этой работы передать в MS Word или же сразу отправить его на печать.
Для работы с инструментами слияния в программе Word 2010 предусмотрен специальный раздел меню, который называется «Рассылки» (рис. 3). На нем собрано пять групп иконок: «Создание», «Начало слияния», «Составление документа и вставка полей», «Просмотр результатов», «Завершение». Каждая из них предназначена для определенного этапа формирования рассылки, а именно:
— группа «Создание» позволяет сразу получить пакет наклеек или конвертов;
— группа «Начало слияния» объединяет инструменты для работы с базой данных;
— группа «Составление документа и вставка полей» позволяет добавлять поля слияния в текст документа;
— группа «Просмотр результатов» предназначена для предварительного просмотра результатов слияния, поиска ошибок, а также для навигации по базе данных в процессе работы с рассылкой;
— группа «Завершение» формирует результат в виде отдельного документа Word. Здесь же можно отправить результат слияния прямо на печать или разослать его по электронной почте.
Для работы с реестром документов нам понадобятся инструменты практически из всех этих групп, кроме первой. Однако, прежде чем приступить непосредственно к процессу слияния, мы должны видоизменить наш реестр. Проблема заключается в способе формирования суммы прописью. При создании одного экземпляра договора (как это было в прошлый раз) мы решили проблему при помощи формул MS Excеl. Этих формул было много, все они располагались на отдельном листе. Таким способом можно получить число прописью для одной конкретной ячейки. При работе с одним экземпляром договора этого вполне достаточно, а вот для рассылок этот вариант не подходит. Для рассылок нам понадобится специальная колонка в базе данных, где для каждого(!) договора будет стоять соответствующая сумма прописью. Сделать это при помощи формул нельзя. Единственный вариант решения задачи — создать пользовательскую функцию. Эта функция в качестве аргумента должна брать число или содержимое ячейки. А результатом ее работы должна быть строка, где будет записана сумма прописью.
Функция для суммы прописью
Для того чтобы создать пользовательскую функцию, нам придется воспользоваться языком VBA (Visual Basic for Application), который входит в состав любого приложения MS Office. Есть Visual Basic и в программе Excel 2010. Учитывая, что программирование на VBA не является профильной задачей практикующего бухгалтера, мы поступим следующим образом. В этой части статьи я расскажу, как открыть редактор VBA в Excel 2010 (по умолчанию эта возможность отключена). Затем покажу, куда и каким способом можно вставить функцию. При этом в подробности алгоритма работы самой функции мы вдаваться не будем. Нам достаточно знать в общих чертах, как выглядит программа и как внедрить ее в документ MS Excel 2010. А полный текст функции мы выложим на сайте редакции, где вы сможете скачать его без каких-либо ограничений. Приступим.
Включаем панель «Разработчик»
Первое, что нужно сделать для работы с инструментом VBA, — это включить вкладку «Разработчик» в меню Excel 2010. Для этого делаем так.
1. Открываем программу Excel. Загружаем файл с базой данных договоров. В нашем примере она называется «Реестр_.xls» (рис. 2).
2. Вызываем меню «Файл → Параметры» В предложенном меню выбираем раздел «Настройка ленты». Откроется окно, как на рис. 4.
3. В группе параметров «Настройка ленты:» находим пункт «Разработчик» и включаем флажок слева от этого пункта (рис. 4).
4. В окне настроек нажимаем «ОК». В составе основного меню Excel 2010 появится пункт «Разработчик» (рис. 5).
Пишем функцию для суммы прописью
Теперь наша задача — воспользоваться редактором Visual Basic и вставить в документ с реестром модуль для записи числа прописью. Для этого вначале заходим на сайт www.bk.factor.ua и скачиваем файл с текстом программы по ссылке http://www.bk.factor.ua/ru/publications/27/8/1018.html . Выглядит программа приблизительно так.
‘ Функции для вычисления суммы прописью
‘
Dim Тысячи, Миллионы As Boolean
Dim Миллиарды, ВторойДесяток As Boolean
‘
Function СуммаПроп(Гривны)
‘ Вызов функции для получения числа прописью
Число = CStr(Fix(Гривны))
МужРод = Ложь
СуммаПроп = ЧислоПроп(Число, МужРод)
‘ Строку с заглавной буквы
СуммаПроп = UCase(Mid(СуммаПроп, 1, 1)) + Mid(СуммаПроп, 2)
…
If ЧислоПроп = "" Then ЧислоПроп = Часть(32)
End Function
‘
Function ЧислоПроп(Число, Optional МужРод = Истина)
‘ Присвоение значений массиву частей
…
‘ Для нулевой суммы пишем "Ноль"
If ЧислоПроп = "" Then ЧислоПроп = Часть(32)
End Function
‘
Private Function ЦифраСтр(Цифра, Место, Род) As String
‘ Если сотни или десятки миллиардов, то запомнить это
If (Цифра <> 0) And ((Место = 11) Or (Место = 12)) Then Миллиарды = Истина
‘ Если сотни или десятки миллионов, то запомнить это
If (Цифра <> 0) And ((Место = 8) Or (Место = 9)) Then Миллионы = Истина
‘ Если сотни или десятки тысяч, то запомнить это
…
End Function
Важно! Приведенный текст — только часть функции для записи числа прописью. Чтобы получить работоспособный вариант программы, ее нужно скачать с сайта редакции.
Теперь делаем так.
1. Открываем файл с текстом функции в любом редакторе. Для этого можно воспользоваться программой «Блокнот» или задействовать хорошо знакомый MS Word.
2. Нажимаем комбинацию «Ctrl+A» (выделить все).
3. По комбинации «Ctrl+C» копируем текст в буфер обмена.
4. Переходим в программу Excel, где сейчас открыт документ с базой данных «Реестр_.xls».
5. Щелкаем левой кнопкой на разделе «Разработчик» главного меню. Лента примет вид, как на рис. 5.
6. В группе «Код» щелкаем на иконке «Visual Basic» (рис. 5). Откроется окно, изображенное на рис. 6. В левой части окна показана структура VBA-проекта для текущей рабочей книги. В этот проект нам нужно добавить текст нашей функции.
7. Вызываем меню «Insert → Module». Откроется окно для добавления текста программы.
8. Нажимаем «Ctrl+V» (вставляем текст программы из буфера обмена).
9.Нажимаем кнопку «Сохранить» и закрываем редактор Visual Basic. Пользовательская функция добавлена.
Проверяем, что у нас получилось на самом деле.
1. Переходим в программу Excel. Открываем лист с базой данных договоров. В нашем примере он называется «Реестр». Сейчас в реестре заполнены колонки «A:W».
2. Становимся на ячейку «X1», вводим заголовок «СумПроп». В принципе название может быть любым. Главное, чтобы в базе не было колонок без заголовков.
3. В ячейку «X2» вводим формулу «=СуммаПроп(V2)» и нажимаем «Enter». В «X2» появится текст — запись суммы прописью.
4. Копируем формулу на всю высоту таблицы и сохраняем файл. Можно переходить к подготовке документа с полями слияния.
Формируем шаблон для слияния
В данный момент у нас есть готовый реестр договоров, где кроме условий договора и данных о контрагентах записаны и суммы каждого договора в виде прописи. Кроме того, есть текстовый образец договора в формате программы Word (файл «Договор.doc»). И наша задача — объединить все это в одно целое. Первым делом нам нужно подсоединить базу с реестром к документу «Договор.doc». Делаем так.
1. Открываем документ Word с текстом договора, как на рис. 1.
2. Переходим в меню «Рассылки». Лента примет вид, как на рис. 3.
3. В группе «Начало слияния» щелкаем на иконке «Выбрать получателей». Откроется меню из трех пунктов, как на рис. 7.
4. Из этого меню выбираем «Использовать существующий список…». Откроется стандартное окно Проводника.
5. В Проводнике указываем тип файла — «Файлы Excel (*.xlsx, *.xlsm, *.xlsb, *.xls)». В появившемся списке файлов выбираем «Реестр_.xls».
6. В окне Проводника нажимаем «ОК». Откроется окно «Выделить таблицу», изображенное на рис. 8. В нем перечислены все листы рабочей книги «Реестр_.xls».
7. Выбираем лист «Реестр$», включаем флажок «Первая строка данных содержит заголовки столбцов»
8. В окне «Выделить таблицу» нажимаем «ОК». Источник данных к документу подключен. Можно добавлять поля.
9. Ставим указатель мыши на фрагмент текста с номером договора. Удаляем этот номер, — здесь будет находиться соответствующее поле слияния из базы данных.
10. В группе «Составление документа и вставка полей» (рис. 3) щелкаем на иконке «Вставить поле слияния». Откроется меню, как на рис. 9. В нем перечислены все поля базы данных на листе «Реестр».
11. Из меню выбираем элемент «N_Дог».
12. Становимся на фрагмент документа, где должны быть дата договора, стираем ее.
13. Щелкаем на иконке «Вставить поле слияния», из предложенного списка выбираем «Дата». Заголовок документа готов.
14. Последовательно редактируем все фрагменты документа, где нужно вставить информацию из реестра и через иконку «Вставить поле слияния» добавляем соответствующие элементы в текст договора.
Генерируем рассылку
Итак, у нас есть договор в формате Word. Через поля слияния этот документ связан с базой данных «Реестр». Все, что остается сделать, — это сформировать рассылку. То есть сгенерировать один или сразу несколько договоров для разных контрагентов из нашей базы данных. Для решения задачи нужно воспользоваться инструментом «Найти и объединить». Он позволяет сформировать договор для одного контрагента из базы данных, для всех записей базы или для произвольного подмножества элементов, которые удовлетворяют заданному набору условий. Посмотрим, как это сделать практически.
Формирование документа для текущей записи реестра
Чтобы получить один экземпляр договора для конкретного контрагента из базы «Реестр», делаем так.
1. Открываем файл с договором в программе Word. Появится окно с предложением обновить поля слияния, используя базу «Реестр».
2. В этом окне нажимаем кнопку «Да». Текст договора с обновленными полями появится на экране.
3. Вызываем меню «Рассылки», щелкаем на кнопке «Посмотреть результаты» (группа «Просмотр результатов»). Вместо полей слияния Word подставит реальные значения из базы данных. В таком виде документ удобнее просматривать на экране.
4. При помощи навигационной панели (кнопки «Первая запись», «Последняя запись», «Предыдущая запись», «Следующая запись», рис. 3) находим нужного контрагента. Поскольку в режиме «Посмотреть результаты» мы сможем видеть содержимое полей прямо в тексте договора, решить такую задачу не составит труда.
5. Завершив поиск, в группе «Завершение» щелкаем на иконке «Найти и объединить». Откроется меню, изображенное на рис. 10. В нем предлагается записать результат слияния в отдельный документ, направить результат прямо на печать или организовать рассылку по электронной почте.
6. Выбираем вариант «Изменить отдельные документы…». Откроется окно «Составные новые документы», изображенное на рис. 11.
7. В этом окне выбираем вариант «текущую запись».
8. Нажимаем «ОК».
9. В окне «Составные новые документы» нажимаем «ОК». Word подставит в текст договора данные из текущей записи реестра и запишет результат работы в новый документ. Остается просмотреть его, при необходимости, откорректировать его и отправить на печать.
Формирование серии документов для всех записей реестра
Чтобы распечатать договоры для всех записей в базе «Реестр», делаем так:
1. Открываем файл с договором, подтверждаем обновление полей слияния по базе «Реестр».
2. Кнопкой «Посмотреть результаты» включаем отображение содержимого полей.
3. Щелкаем на иконке «Найти и объединить…» (группа «Завершение»). Откроется меню, как на рис. 10.
4. В нем выбираем вариант «Изменить отдельные документы…». Откроется окно «Составные новые документы» (рис. 11).
5. В этом окне выбираем вариант «все».
6. Нажимаем «ОК». Word последовательно просмотрит все записи базы данных и для каждой из них сформирует свой вариант договора. После чего результат этой работы запишет в новый документ.
Формирование документов с использованием фильтра
На практике часто приходится формировать документы для определенного подмножества записей из базы данных. Например, в пределах указанной даты, для некоторой группы контрагентов и т. д. Для решения таких задач при слиянии можно использовать фильтр. Работу с этим инструментам я предлагаю посмотреть на паре конкретных примеров.
Пример 1. Выбор документов для слияния по списку получателей
1. Открываем договор, обновляем поля слияния с базой данных.
2. Включаем режим отображения содержимого полей (кнопка «Посмотреть результаты»).
3. В группе «Начало слияния» щелкаем на иконке «Изменить список получателей» (рис. 3). Откроется окно «Получатели слияния», изображенное на рис. 12. В этом окне видны все записи нашего реестра.
4. Просматриваем список, оставляем «галочки» возле тех записей, для которых нужно выполнить слияние.
5. В окне «Получатели слияния» нажимаем «ОК».
6. Щелкаем на иконке «Найти и объединить».
7. Из предложенного меню (рис. 10) выбираем вариант «Изменить отдельные документы…». Откроется окно «Составные новые документы» (рис. 11).
8. В этом окне выбираем вариант «все».
9. Нажимаем «ОК». Word обработает все записи реестра, которые отмечены галочками в списке «Получатели слияния». Для каждой такой записи он сгенерирует отдельный документ.
Пример 2. Выбор документов для слияния по условиям фильтра
Этот режим слияния мы посмотрим на примере обработки документов для заданного диапазона дат. Предположим, мы решили распечатать договоры, которые подписаны до «03.09.2011» включительно. Для этого делаем так.
1. В программе Word открываем документ с бланком договора. Обновляем поля слияния с базой данных.
2. Включаем режим отображения содержимого полей.
3. В группе «Составление документа и вставка полей» (рис. 3) щелкаем на иконке «Изменить список получателей». Откроется окно «Получатели слияния», как на рис. 12.
4. В этом окне щелкаем на гиперссылке «Фильтр…». Откроется окно настроек параметров фильтра, изображенное на рис. 13). В нем есть три колонки для формирования условий («Поле:», «Сравнение:» и «Значение:»), а также область для логической операции. Эта операция позволяет объединить несколько условий в одно целое.
5. Щелкаем на значке выпадающего списка «Поле:». Откроется меню, где будут перечислены названия всех полей базы «Реестр».
6. Из этого меню выбираем «Дата».
7. Щелкаем на списке «Сравнение:». Из предложенных вариантов выбираем «меньше или равно».
8. В окошко «Значение:» вводим дату «03.09.2011».
9. В окне «Фильтр и сортировка» нажимаем «ОК», возвращаемся в окно «Получатели слияния».
10. В этом окне тоже нажимаем «ОК».
11. Щелкаем на иконке «Найти и объединить…», выбираем вариант «Изменить отдельные документы…». Откроется окно «Составные новые документы», как на рис. 11.
12. В нем выбираем вариант «все» и нажимаем «ОК». Word создаст рассылку из тех договоров, которые подписаны до «03.09.2011».
В окне «Фильтры и сортировка» можно указать несколько условий для отбора записей, объединив их операциями «И», «ИЛИ». Таким образом, например, можно выбрать из реестра документы для конкретных контрагентов за определенный промежуток времени или дополнить выборку ограничением на сумму договора.
Работа с серийными документами (рассылками) в сочетании с фильтрацией источника данных открывает очень гибкие возможности при работе с реестром документов. Применяя фильтры, вы сможете обработать практически любую группу документов, используя для них общие признаки.
Пару слов о форматировании. При создании рассылок формат полей слияния может отличаться от текста основного документа. При этом бывает так, что форматирование полей стандартными средствами Word не дает желаемого результата, и после каждого обновления документа параметры форматирования у полей пропадают (приобретают значения по умолчанию). Чтобы справиться с этой проблемой, нужно знать один нюанс.
Совет Чтобы сохранить форматирование в документах слияния, нужно использовать специальные коды полей.
Теперь подробнее. Вернемся к документу на рис. 1. Одно из полей слияния в этом документе — наименование контрагента. Мы решили изменить формат этого поля и хотим, чтобы при обновлении документа этот формат не менялся. Делаем так:
1. Ставим курсор на поле слияния (в данном случае — выделяем наименование предприятия).
2. Нажимаем комбинацию клавиш «Alt+F9». Вместо значений в документе появятся коды полей. В данном случае вместо названия фирмы мы увидим: «{MERGEFIELD Предприятие}».
3. Изменяем форматирование первой буквы этого поля. В нашем случае достаточно отформатировать только букву «М».
4. После названия поля добавляем ключ сохранения формата «\*Charformat». После этого поле для названия контрагента должно выглядеть так: «{MERGEFIELD Предприятие *Charformat}».
5. Снова нажимаем комбинацию клавиш «Alt+F9» — возвращаемся в режим отображения значений полей.
6. Выполняем описанные действия над всеми полями в документе. Форматирование полей после их обновления изменяться не будет.
И последний момент.
Важно! Не перемещайте (без веской на то причины) источник данных для слияния в другую папку. Дело в том, что MS Office запоминает местоположение базы данных. Переместив ее в другой каталог, придется заново подключать источник для слияния к документу. Кстати, сам документ можно переносить в другие папки без каких-либо ограничений.
Правда, есть и хорошая новость. При повторном подключении источника данных поля слияния заново внедрять в документ не придется. Достаточно указать путь к базе данных, все остальное Word сделает автоматически.
Вот, пожалуй, и все, что я хотел рассказать о работе с реестрами документов. Надеюсь, что материал этой статьи поможет вам упорядочить свой документооборот без дополнительных затрат и неоправданных усилий.
Успешной работы! Жду ваших писем, вопросов и предложений на bk@id.factor.ua , nictomkar@rambler.ru или my.karpenko@gmail.com.
