Создаем реестр договоров

Важно! В реестре нужно указать все варианты переменной информации точно в таком виде, в каком он встречается в договоре.

Например, название должности фигурирует в базе данных дважды. Один раз — с маленькой буквы в родительном падеже. Именно в таком виде должность покупателя записана в первом абзаце документа. Второй раз в реестре название должности указано в именительном падеже и с большой буквы (поле «Должность2»). В таком виде этот элемент используется в завершающей подписи в конце договора.

Таблица 1. Поля реестра документов

Создав базу данных, заполняем реестр сведениями о контрагентах (например, как показано на рис. 2). Исходная информация для нашего примера готова. Можно начинать работу над текстом договора.

В настоящий момент текст документа представлен в формате программы Word. Этот документ нам еще пригодится — мы воспользуемся им для организации рассылки. А пока наша задача скромнее. Я хочу из имеющейся базы данных распечатать один конкретный договор. Выглядеть это будет так. В рабочей колонке «Пр» я поставлю символ «*» — признак печати договора для соответствующего контрагента. После этого на отдельном листе я хочу получить текст договора, куда Excel автоматически подставит все атрибуты контрагента из соответствующей строки. Чтобы реализовать такой алгоритм на практике, мы вначале должны перенести на отдельный лист Excel текст документа. А затем сделать подстановку значений из базы функциями «ВПР()». Посмотрим, как это реализовать в Excel 2010.

Формируем Бланк договора в MS Excel

Вначале перенесем данные из программы Word на лист MS Excel. Для этого делаем так:

1. Открываем документ с реестром, добавляем новый рабочий лист.

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

3. Вводим название листа (например, «Дог»).

4. Вызываем «Файл печать», откроется окно предварительного просмотра.

5. Нажимаем «Esc» — закрываем окно предварительного просмотра. Это действие мы сделали только для того, чтобы на листе появилась пунктирная линия для обозначения правой границы печатного листа.

6. Через буфер обмена переносим текст из программы «Word» в ячейки Excel. Данные нужно переносить построчно, причем вставлять текст я предлагаю исключительно в ячейки колонки «А». При этом нужно следить за тем, чтобы строки документа не выходили за правую границу печатного листа (находились в пределах области печати).

7. Текст с подписями в конце документа сразу размещаем в отдельных колонках. Слово «Директор» (подпись от продавца) ставим в ячейку «A24». Ячейку «B24» оставляем пустой — это место для самой подписи. Фамилию директора от предприятия-продавца (В. М. Петров) пишем в ячейку «C24».

8. Аналогичным образом оформляем область с подписями от покупателя. В нашем примере текст «от Покупателя» записан в ячейку «E23». Слово «Ген. директор» находится в ячейке «E24», а фамилия «В.И. Авилов» занимает ячейку «G24».

9. Подбираем ширину колонок, ориентируясь на значения в области подписей завершающей части договора. То есть ширина колонки «А» должна быть такой, чтобы в ней поместилось слово «Директор». Ширина колонок «B» и «F» должна быть достаточной для физических подписей сторон и т. д.

10. В последнюю очередь ставим в текст документа данные о наименовании товара, объеме продаж, единице измерения, цене и сумме договора. В примере на рис. 3 я разместил эти значения в ячейках начиная с «D7» и до «D12». Такой выбор был сделан исключительно исходя из сложившейся ширины колонок на рабочем листе. Ведь пока эти данные нам нужны только для того, чтобы оценить реальный размер документа и заранее подогнать его под ширину страницы.

11. Через окно форматирования включаем для ячеек «B24» и «F24» нижнюю границу.

12. Выделяем ячейки «A1:G1», форматируем заголовок (строка «ДОГОВОР…») по центру выделения.

13. Последовательно выделяем все названия пунктов первого уровня и форматируем их по центру выделения относительно колонок «A:G».

14. Выделяем заголовки третьего уровня (п.п. 1.1.1, 1.1.2 и т. д.).

15. Вызываем окно «Формат ячеек», на закладке «Выравнивание» выбираем формат «по левому краю (отступ)» и вводим значение отступа «2».

16. Выделяем полужирным шрифтом ключевые элементы нашего договора. Внешний вид документа в первом приближении показан на рис. 3.

Сейчас мы создали на рабочем листе «Дог» только шаблон — заготовку в виде текста договора купли-продажи. Теперь наша задача дополнить его формулами, чтобы подставить в документ данные по конкретному контрагенту. Напомню, что строку с параметрами контрагента, для которого формируется документ, мы обозначим в колонке «Пр» листа «Реестр» символом «*».

Для решения задачи мы воспользуемся функцией «ВПР()». Как работает эта функция, мы посмотрим на примере третей строки документа с номером договора «6а-09/11» (ячейка «С4»). Сейчас в заголовке документа (рис. 1) находится текст «ДОГОВОР № 1 от 9/1/2011». Вместо номера «1» мы должны подставить значение из базы данных (лист «Реестр») из колонки «D». При этом номер строки мы определим из условия, что в колонке «A» она отмечена символом «*». На рис. 2 этот символ находится в ячейке «A4». Формула для подстановки номера договора будет выглядеть так: «=ВПР("*";Реестр!A:X;3)». В формуле три параметра. Вначале идет искомый текст (в данном случае — это символ «*»). Затем блок, из которого «ВПР()» сделает подстановку. В нашем случае функция будет просматривать первую слева колонку блока, т. е. колонку «А». Как только в одной из ячеек этой колонки она обнаружит символ «*», функция вернет в качестве результата значение по той же строке, но со смещением на 3 колонки вправо. Для примера на рис. 2 (символ «*» находится в ячейке «A4») результатом работы формулы будет значение «6а-09/11».

В принципе формулу можно оставить как есть. Но если в колонке «A» базы данных не будет ни одного символа «*», функция «ВПР()» отработает с ошибкой: результатом ее работы будет значение «Н/А». На рабочем листе смотрится оно, прямо скажем, нелепо. Чтобы усовершенствовать формулу, мы можем проанализировать ошибку, воспользовавшись функциями «ЕОШИБКА()» и «ЕСЛИ()». С их помощью мы проверим результат поиска символа «*» функцией «ВПР». Если поиск не успешен, в качестве результата мы вернем в ячейку слово «нет». В противном случае в ячейку запишем результат поиска. Тогда выражение для подстановки номера договора в документ будет выглядеть так: «="ДОГОВОР № " & ЕСЛИ(ЕОШИБКА(ВПР("*";Реестр!A:X;3));" Нет ";ВПР("*";Реестр!A:X;3))». Посмотрим на работу этой формулы подробнее. Функция «ВПР("*";Реестр!A:X;3)» находит в базе значение номера договора для контрагента, отмеченного символом «*» в колонке «А». Функция «ЕОШИБКА()» анализирует результат поиска. Если поиск успешен, функция вернет значение «ЛОЖЬ» (нет ошибки). В противном случае она вернет значение «ИСТИНА». Этот результат проверяется функцией «ЕСЛИ()». Когда поиск успешен и результат «ЕОШИБКА (ВПР("*";Реестр!A:X;3))» равен «ЛОЖЬ», функция «ЕСЛИ()» возвращает в ячейку значение «ВПР("*";Реестр!A:X;3))». Иначе в ячейку будет записано слово «нет».

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

Таблица 2. Формулы для подстановки значений в текст договора

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

СУММА ПРОПИСЬЮ В ДОКУМЕНТЕ Excel

Алгоритм записи числа прописью мы реализуем при помощи встроенных функций MS Excel. Все расчеты оформим на отдельном листе (например, «Проп»). Дальше делаем так:

1. Переходим на лист «Проп».

2. В ячейку «A1» печатаем слово «Сумма». В ячейку «D1» вводим выражение «=ВПР("*"; Реестр!$A:$X;22)». Эта формула вставляет на рабочий лист сумму договора, который отмечен символом «*» в базе на листе «Реестр». Ссылку на сумму договора мы поставили, теперь разберемся с алгоритмом.

Чтобы написать число прописью, нам нужно каждую цифру этого числа разнести по отдельным ячейкам. Для этого мы будем последовательно делить исходное число на «10» и каждый раз отбрасывать дробную часть при помощи функции «ОТБР()». В результате мы сможем определить количество единиц, десятков, сотен, тысяч и т. п. в исходном числе. Завершив такую обработку числа, мы проанализируем его поразрядно и напишем результат в виде прописи. Посмотрим, как это выглядит на практике. Начнем с завершающей части таблицы:

1. Оставаясь на листе «Проп», заполняем блок «A32:G41» значениями, как на рис. 4. Это будут текстовые заготовки для представления прописью.

2. В ячейку «B11» вводим формулу: «=ОТБР(D1)». Результат ее работы — целая часть числа из ячейки «D1», т. е. целая часть от суммы по договору.

3. Начиная с «A12» заполняем лист формулами в соответствии с табл. 3 (см. с. 30).

В результате работы этих формул в ячейках «B12:B27» мы получим исходное число, разделенное на разряды, а в блоке «E12:E27» — текстовое название каждого разряда нашей суммы.

Таблица 3. Формулы для обработки целой части суммы договора

До настоящего момента речь шла только о целой части числа, т. е. о гривнях. Переходим к копейкам. Для этого в ячейку «A29» вводим формулу: «=ОКРУГЛ(100*(D1-B11);0)». Переводить в текстовое представление мы ее не будем. При формировании прописи к этому значению мы просто добавим текст «коп.».

Итак, все данные для формирования числа прописью у нас есть. Остается объединить информацию и получить окончательный результат. Делаем так:

1. Становимся на ячейку «A10» (рис. 4), вводим формулу «=СЦЕПИТЬ(ЕСЛИ(A29=0;"00";A29);" коп.")». Это количество копеек в сумме по договору.

2. В ячейку «A9» вводим формулу «=СЦЕПИТЬ(ЕСЛИ(B25=0;"";E25); ЕСЛИ(B26=0;"";ЕСЛИ(C27<20; ЕСЛИ(C27<16;ЕСЛИ(C27<10;E26;D27);F27);E26));ЕСЛИ(B27=0;"";ЕСЛИ(НЕ(B26=1);E27;""));F28)». Это количество гривень прописью.

3. Формулы в ячейках «A6», «A7», «A8» по сути такие же, но только обрабатывают они разряды сотен, тысяч и миллионов гривень в исходном числе. А сами выражения в этих ячейках выглядят, как в табл. 4 (см. с. 32).

Таблица 4. Формулы для формирования суммы прописью

4. В ячейку «A4» вводим: «=СЦЕПИТЬ(A6;A7;A8;A9;A10)». Это сумма прописью с маленькой буквы.

5. В «A3» вводим выражение «=ПОДСТАВИТЬ (A4;СИМВОЛ(КОДСИМВ(A4));ПРОПНАЧ(СИМВОЛ (КОДСИМВ (A4)));1)». Это сумма числа прописью с заглавной буквы.

Результат из ячейки «A3» мы и вернем в бланк договора. Для этого делаем так:

1. Переходим на лист «Дог».

2. Становимся на ячейку «E11».

3. Вводим формулу: «=ЕСЛИ(ЕОШИБКА(Проп!A3);" нет ";"("&Проп!A3&").")». Бланк договора готов.