Створюємо реєстр договорів. Частина 2
 | У попередній статті ми сформували реєстр документів (це були договори купівлі-продажу) та дізналися, як із цього реєстру можна швидко отримати конкретний примірник договору. Причому основним інструментом для вирішення такого завдання був MS Excel 2010. Сьогодні ми підемо далі та подивимося, як з використанням реєстру можна обробити цілу групу договорів. У цьому випадку йтиметься про формування та підготовку до друкування відразу декількох примірників документів з різними параметрами. Про те, як це зробити в пакеті MS Office 2010 та де ще можуть стати у пригоді розглянуті нами інструменти у практиці бухгалтера, поговоримо в цій статті. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Насамперед підіб’ємо підсумок та визначимося, що ми зробили, що маємо у своєму розпорядженні та яке завдання збираємося вирішити. Отже, у нас є бланк договору, форму якого зображено на рис. 1. Договір оформлено у форматі програми Word. У цьому документі сірим кольором виділено ті поля, які є змінною інформацією. До них, наприклад, належать номер договору, дата його укладення, назва підприємства, прізвище керівника, платіжні реквізити, адреса тощо. Усі ці дані для різних договорів ми записали до бази даних Excel, фрагмент якої зображено на рис. 2. Кожна колонка в базі відповідає певному полю в договорі. А один рядок бази містить всю змінну інформацію про один конкретний примірник документа. Так, номер договору знаходиться в колонці «С» з назвою «№ дог.». У колонці «D» під заголовком «Дата» є дані про дату укладення договору тощо. І тепер наше завдання буде таким: для кожного запису з бази даних надрукувати свій примірник документа.
Вирішити таку проблему можна різними способами. Однак найбільш прийнятний із них, на мій погляд, — це інструмент злиття MS Office 2010. Іноді цей інструмент називають створенням розсилок. Саме його ми і застосуємо для вирішення нашого завдання.
Інструменти злиття MS Office 2010
Про створення розсилок ми вже писали на сторінках нашого видання. Наприклад, у «Б & К», 2009, № 23. Тому не повторюватимуся, лише стисло нагадаю основну ідею злиття та принцип його роботи. Розсилка дозволяє об’єднати в одне ціле базу даних та текстовий документ. Базу даних зазвичай створюють у програмі 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 .
