На этом шаге мы рассмотрим автоматизацию процесса формирования составляющих договора.

Эта область объединяет элементы базы данных с введенными в нее реквизитами предприятий на рабочем листе Реквизиты и введенные данные в верхнюю часть рабочего листа Договор.

В свою очередь область формирования можно разделить на составляющие:

номер договора (ячейка В8);
преамбулу (диапазон ячеек А9:С11);
изменяющиеся пункты договора (объединенные ячейки А12:С12 и А13:С13);
реквизиты сторон (диапазон ячеек А15:С21).

Рассмотрим более детально каждую составляющую.

Номер договора, дата и место его заключения

В ячейке В8 находится формула номера договора:

  =ЕСЛИ(ИЛИ(B3=1;B3=3);C3;"__________")

в которой, если в ячейку В3 введено значение 1 или 3, то формула возвращает значение указанное в ячейке С3, в противном случае возвращается значение "____" (подчеркивающая линия).

Формула в ячейке А9 определяет место заключения договора:

=C4

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

Формула в ячейке С9 указывает дату заключения договора:

  =ЕСЛИ(C5=0;"<___>_________ 200 г.";AC5)

Если дата не введена в ячейку С5, то тогда формула возвращает текстовое значение "<___>_________ 200 г.". Если введена, то тогда возвращает эту дату из ячейки AC5, которая является выходом модуля преобразования даты из формата Excel в текстовый. Модуль преобразования даты находится в диапазоне ячеек АА5:АС5 и с его входа - ячейки АА5 - задана ссылка на ячейку С5, а выходом является ячейка АС5.

Преамбула договора

В объединенной ячейке А11:С11 формируется преамбула договора. Формула, находящаяся в ячейке А11, с помощью текстовой функции СЦЕПИТЬ соединяет текст, функции и ссылки (рисунок 1).

Рис.1. Формула формирования текста на основе функции СЦЕПИТЬ

В формуле последовательно происходит соединение следующих элементов:

ссылка на ячейку А16 в области реквизитов, в которой уже определено наименование предприятия-продавца;
текст ", в лице ";
функция ВПР, производящая поиск наименования должности, внесенной в базу данных на листе Реквизиты;
" " - текст (пробел), соединяющий наименование должности и фамилии, имени и отчества руководителя предприятия-продавца;
функция ВПР - производит поиск полностью фамилии, имени и отчества руководителя предприятия, который подписывает договор;
текст ", который действует на основании ";
функция ВПР - для поиска наименования документа, на основании которого руководитель подписывает договор;
текст ", в дальнейшем ";
ссылка на ячейку A15, в которой происходит поиск предприятия-покупателя товара;
текст ", с одной стороны и ";
ссылка на ячейку C16 - наименование организации Покупателя;
текст ", в лице ";
функция ВПР - производит поиск наименование должности, внесенное в базу данных на рабочем листе Реквизиты;
" " - текст (пробел), соединяющий наименование должности и фамилии, имени и отчества руководителя предприятия-покупателя;
и так далее.

Пункты договора

Формула в объединенной ячейке А12:С12 (рисунок 2) соединяет в единое текст, который указывает, кто и что именно собирается сделать.

Рис.2. Формула в объединенной ячейке А12:С12

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

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

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

На рисунке 3 представлен пример представления чисел, находящихся в строке 1, после указания ссылок на эти ячейки, помещенных в функцию СЦЕПИТЬ (строка 2).

Рис.3. Пример представления числа в текстовом формате

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

Для того чтобы сумма внутри текста выглядела в якобы числовом формате, необходимо этому числу пройти соответствующую обработку по алгоритму, показанному на рисунке 4. Формулы преобразования показаны на рисунке 5.

Рис.4. Алгоритм преобразования числа из числового формата в текстовый формат

Рис.5. Рабочий лист с формулами преобразования суммы числового формата в текстовый формат

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

Рассмотрим обработку целой части числа, которая производится в ячейках В4:В6 (рисунок 5). Формулы каждой из этих ячеек основаны на использовании функции ОТБР, выделяют из числа разряды: миллионы, тысячи и единицы соответственно. В ячейке В5 производится выделение разрядов тысяч.

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

Преобразование дробной части несколько сложнее.

В ячейке В8 с помощью функции ДЛСТР определяется количество символов в анализируемом числе, в которые включены цифры целой и дробной части числа, а также запятая.

В ячейке В9 формула производит с помощью функции НАЙТИ поиск запятой (делителя числа на целую и дробную части) в тексте числа. Если эта функция не находит символ запятой, указанный в первом аргументе функции, то тогда она возвращает значение ошибки #ЗНАЧ!, что не позволяет использовать полученный результат в дальнейших вычислениях. С целью устранения этого недостатка в первом аргументе функции ЕСЛИ вложена функция ЕОШИБКА (рисунок 6), контролирующая функцию НАЙТИ.

Рис.6. Панель функции ЕОШИБКА

При полученном значении ошибки возвращает значение ИСТИНА, по которому функция ЕСЛИ возвращает значение ноль.

Задача формулы в целом - определить номер позиции запятой в числе, начиная с левой стороны. Если запятая отсутствует, то возвращается значение 0.

Содержимое ячейки В10 предназначено при отсутствии разделительного знака, а, как следствие, и самой дробной части, сформировать ее. Поэтому если формула в ячейке В9 возвращает значение ноль, то формула в ячейке В10 возвращает текст ",00".

Ячейка В11 предназначена для определения количества знаков после запятой - один или два. Если в дробной части содержится два символа, то эта часть остается без изменений. Если же после запятой имеется только один знак, а это возможно только при наличии десятичного символа, то в дробную часть надо добавить символ 0, для чего предназначена формула в ячейке В13.

Формула в ячейке В12 выделяет существующую дробную часть вместе с запятой.

Ячейка В14 предназначена при необходимости для присоединения недостающего знака в дробной части.

Ячейка В15 является заключающей в формировании дробной части числа.

Полностью все число соединяется в единое целое в ячейке В17 с помощью текстовой функции СЦЕПИТЬ.

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

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

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

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

Для внедрения созданного модуля в разрабатываемое приложение скопируйте его область D3:F3 перейдите на лист Договор и, выделив ячейку АА1, произведите вставку. Из ячейки АА1 (вход модуля) задайте ссылку на ячейку С2, в которую вводится сумма договора. А ссылка на выход модуля задается из ячейки А13 (рисунок 1).

Реквизиты

Область формирования реквизитов на рабочем листе Договор находится в области А15:С21 и показана на рисунке 9.

Рис.9. Реквизиты сторон подписывающих договор

Область формирования реквизитов разделена на две части, формирование которых зависит от значений, введенных в ячейки А1 и С1.

Замечание. В данном примере в файле База.xls есть неточность в формуле (ячейка AC1) формирования числа суммы договора. Формирование числа с цифрами в разряде до тысячи и до миллиона, а именно от ХХХХ001 до ХХХХ099 и от Х001ХХХ до Х099ХХХ соответственно, не формируются две первые цифры "00" и первая цифра "0" соответственно,то есть Excel имеет ограничение по размеру формулы.

Если рассмотреть простые формулы, показанные на рисунке 5, и в качестве рассматриваемого числа в ячейку В1 ввести 1001001, то можно обнаружить, что в ячейке В17 будет получен результат "1 1 1,00". Исчезли десятки и сотни тысяч и десятки и сотни единиц.

Для исправления ошибок в тысячах введем следующую формулу в ячейку J5:

  =ЕСЛИ(B4="";B5;ЕСЛИ(ДЛСТР(B5)=1;СЦЕПИТЬ("00";B5);
    ЕСЛИ(ДЛСТР(B5)=2;СЦЕПИТЬ("0";B5);B5)))

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

Похожая формула находится и в ячейке J6, которая проверяет разрядность единиц:

  =ЕСЛИ(И(B5="";B4="");B6;ЕСЛИ(ДЛСТР(B6)=1;СЦЕПИТЬ("00";B6);
    ЕСЛИ(ДЛСТР(B6)=2;СЦЕПИТЬ("0";B6);B6)))

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

Рис.10. Рабочий лист преобразования суммы числового формата в текстовый формат

Следующая задача - доработать формулу в ячейке В17.

В находившейся в ней формуле:

  =СЦЕПИТЬ(B4;" ";B5;" ";B6;B15)

нужно заменить ссылки В5 на ячейку J5, и В6 на ячейку J6.

  =СЦЕПИТЬ(B4;" ";J5;" ";J6;B15)

После чего указанные ссылки заменить на формулы находящиеся в ячейках J5 и J6. Получена формула:

  =СЦЕПИТЬ(B4;" ";ЕСЛИ(B4="";B5;ЕСЛИ(ДЛСТР(B5)=1;СЦЕПИТЬ("00";B5);
    ЕСЛИ(ДЛСТР(B5)=2;СЦЕПИТЬ("0";B5);B5)));" ";ЕСЛИ(И(B5="";B4="");B6;
    ЕСЛИ(ДЛСТР(B6)=1;СЦЕПИТЬ("00";B6);ЕСЛИ(ДЛСТР(B6)=2;СЦЕПИТЬ("0";B6);B6)));B15)

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

Единственная рекомендация - оставить на месте вход и выход модуля.

На рисунке 11 находится вновь созданный модуль, который расположен в области D8:Н8.

Рис.11. Новый модуль трансформации с числовым примером

Для замены модуля в файле "База" скопировать область Е8:Н8 вновь полученного модуля и, перейдя на лист Договор книги База, произвести вставку в ячейку АВ1.

Рис.12. Новый модуль трансформации с формулами

Исправленные модули можно взять здесь.

На следующем шаге мы рассмотрим создание интерактивного текстового документа Договор.

Предыдущий шаг Содержание Следующий шаг