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

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

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

Создание приложений, автоматизирующих процесс формирования однотипных документов, позволяет и текстовый процессор Microsoft Word, который также имеет возможность создания приложений на основе языка Microsoft Basic of Application.

Следующее простое разрабатываемое приложение — возможность управления текстовыми данными, находящимися в Microsoft Word из Excel, на примере формирования типовых договоров.

Договор

Структура договора и его основные условия

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

Договор состоит из следующих частей:

1. Вводной части (преамбула);
2. Предмета договора, прав и обязанностей сторон;
3. Дополнительных условий договора;
4. Реквизитов сторон.

Рис. 6.1. Пример структуры упрощенного договора купли-продажи

Вводная часть (преамбула) договора

Эта часть содержит следующие основные положения:

1. Название договора (договор покупки-продажи, комиссии, поручения, и т.д.).
2. Дата подписания договора.
3. Место подписания договора или населенный пункт.
4. Полное название контрагентов, под которым они зарегистрированы в реестре государственной регистрации.
5. Название стороны по договору (например, "Продавец", "Покупатель", "Поставщик" и пр.)
6. Наименование должности, фамилии, имени, отчества лица, которое подписывает договор, а также название документа, из которого следуют его полномочия на подписание договора.

Предмет договора, права и обязанности сторон

Эта часть договора содержит существенные условия договора:

1. Обязательства и права первой стороны по договору;
2. Обязательства и права другой стороны по договору;
3. Срок выполнения сторонами своих обязательств;
4. Место выполнения обязательств каждой из сторон;
5. Способ выполнения обязательств каждой из сторон.

Дополнительные условия договора

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

1. Срок действия договора;
2. Ответственность сторон;
3. Способы обеспечения обязательств;
4. Условия досрочного расторжения договора в одностороннем порядке и порядок действий сторон при одностороннем расторжении договора;
5. Условия о конфиденциальности информации по договору;
6. Порядок решения споров между сторонами по договору.

Прочие условия договора

1. Определяют, какими законодательными (и прочими) документами, кроме договора, регулируются отношения сторон;
2. Условия о средствах и методах связи между сторонами;
3. Реквизиты сторон;
4. Количество экземпляров договора;
5. Порядок исправлений текста договора.
6. Подписи представителей сторон.

Как бывает на практике

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

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

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

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

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

Рис. 6.2. Последовательность формирования документа

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

Задача

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

В качестве примера предположим, что предприятие занимается только оптовой торговлей, то есть покупает и продает различный товар. Как правило, предприятию приходится часто подписывать договора на покупку/продажу партий товара. Это типовые договора купли — продажи. В эти типовые договора вносятся изменения только в отдельные пункты, а остальной текст остается неизменным.

Техническое задание

Необходимо создать своими силами приложение в Excel, которое позволило бы внести быстро и безошибочно изменения в типовой договор, созданный в текстовом редакторе Word.

Исходим из того, что в создаваемом приложении:

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

Рис. 6.3. Алгоритм работы создаваемого приложения по управлению текстовыми данными

Создание приложения в Excel

Создаваемое приложение включает в себя один файл Excel и два файла Word, которые поместите в одну папку (директорию), которой присвойте имя Договор купли продажи. Присвойте создаваемой рабочей книге Excel имя База.

База данных контрагентов

Табличная база данных с реквизитами контрагентов (список) расположена на рабочем листе Реквизиты (рис. 6.4.). Она должна быть составлена с учетом особенностей конкретного автоматизируемого документа, в частности, договора, и, например, должность подписывающего лица указывается в ней два раза с учетом склонения: текста директора, находящегося в преамбуле договора и Директор в разделе реквизитов.

Рис. 6.4. База данных контрагентов

В этот список вносятся все данные, которые необходимы для заполнения реквизитов договора:

• Предприятие (столбец В) — полное наименование предприятия;
• Адрес (столбец С) — почтовый адрес предприятия;
• Расчетный счет (столбец D) — расчетный счет предприятия;
• Банк (столбец E) — учреждение банка;
• МФО (столбец F) — МФО банка;
• Должность 1 (столбец G) — должность руководителя предприятия, подписывающего договор, которая указывается во вступительной части договора. Пишется с маленькой буквы и склоненная в соответствующем падеже;
• Должность 2 (столбец H) — должность руководителя предприятия в именительном падеже, которая указывается в реквизитах;
• Ф.И.О. 1 (столбец I) — полностью фамилия, имя и отчество руководителя предприятия, подписывающего договор, которая указывается во вступительной части договора. Пишется полностью в именительном падеже
• Ф.И.О. 2 (столбец J) — фамилия и инициалы руководителя предприятия, который подписывает договор;
• На основании (столбец K) — на основании какого документа этот руководитель подписывает договор. Первый руководитель — на основании устава, другие, например, на основании выданной им доверенности.

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

Пользователь при создании нового договора от имени своего предприятия, просматривает, — присутствует ли в списке организация, с которой заключается договор. Если отсутствует, то тогда необходимо в базу данных ввести ее реквизиты. И после этого в этой строке в столбец А ввести, например, значение 2 — покупатель, а в строке с реквизитами своей организации ввести в столбец А значение 1 (или наоборот).

Корректировка содержимого договора

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

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

Переменная часть договора находится на рабочем листе Договор (рис. 6.5.) и состоит из двух частей: области ввода данных (диапазон А1:I7) и области просмотра сформированного "переменного" текста (диапазон А8:С21).

Рис. 6.5. Рабочий лист "Договор"

Область ввода данных

Ячейки А1 и С1 предназначены для ввода значений 1 и 2, по которым определяются реквизиты предприятия из табличной базы данных на листе Реквизиты.

Диапазон ячеек С2:С7 предназначен для ввода переменных данных договора:

• С2 — сумма договора;
• C4 — место заключения договора;
• С5 — дата заключения договора;
• С6 — предмет покупки/продажи (партия товара, наименование или ссылка на Спецификации к договору);
• С7 — срок оплаты партии товара;
• С3 — формируется присваиваемый номер договора.

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

Дата заключения договора

В ячейку С5 могут вводиться следующие данные:

функция СЕГОДНЯ, автоматически возвращающая текущую дату, если предполагается, что договор будет подписываться в день его формирования на компьютере; ничего не введено, то есть дата затем записывается от руки при подписании договора; дата произвольная, известен срок подписания договора, и она вводится в ячейку С5 с клавиатуры.

Рассмотрим два первых случая. Для формирования текущей даты в ячейку С5 введите формулу:

=ЕСЛИ(B5;СЕГОДНЯ();0)

которая при значении ИСТИНА в ячейке В5 возвращает в ячейке С5 текущую дату. При значении ячейки В5 равном нулю (ничего не введено) или ЛОЖЬ возвращает значение 0.

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

Определение номера договор и сроков оплаты по нему

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

1. Номер договора и дата оплаты указывается в момент формирования на компьютере (создания);
2. Номер договора не указан, а дата оплаты указывается в момент создания;
3. Номер договора указывается в момент создания на компьютере, а дата оплаты в момент подписания;
4. Не указаны ни номер договора, ни дата оплаты.

Исходя из этого и созданы формулы в ячейках С3 и С7, которые в зависимости от значения от 1 до 4, введенных в ячейку В3, формируют одно из значений.

Формирование текста срока оплаты по договору

Срок оплаты покупателем, согласно условий договора, вводится (или формируется) в ячейку С7 и может быть:

• введена какая-либо дата оплаты с клавиатуры;
• дата не указана.

Для автоматизации определения даты оплаты введите в ячейку С7 формулу:

=ЕСЛИ(ИЛИ(B3=3;B3=4);"";ЕСЛИ(C5=0;"";C5+14))

которая в первом аргументе функции ЕСЛИ проверяет содержимое ячейки В3 на значение 3 или 4. При любом из двух этих значений логическая функция ИЛИ возвращает значение ИСТИНА и тогда в ячейке С7 ничего не указано (во втором аргументе функции ЕСЛИ находится ""). В противном случае при введенной дате в ячейку С5 (дата заключения договора) формула прибавляет к введенной дате значение 14 (оплатить в течение двух недель). Вместо значения 14 (дней) можно ввести ссылку на какую-либо ячейку, в которую будет вводиться это значение.

Генерация текста номера договора

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

=ЕСЛИ(И(B3<>2;B3<>4);ЕСЛИ(C5=0;"________";АА3);"б/н")

В первом аргументе первой функции ЕСЛИ находится функция И, которая возвращает значение ЛОЖЬ, если в ячейке В3 находятся значения 2 или 4. Тогда формула возвращает текст б/н, введенный в третий аргумент первой функции ЕСЛИ.

Если же формула переходит к вычислению второй функции ЕСЛИ, то тогда при отсутствии в ячейке С5 введенной даты заключения договора, второй аргумент второй функции ЕСЛИ возвращается значение: ____________ (прочерк).

При введенной в ячейку С5 дате заключения договора, вторая функция ЕСЛИ переходит к своему третьему аргументу, который содержит ссылку на ячейку АА3.

Формула в ячейке АА3:

=СЦЕПИТЬ(ПРАВСИМВ(ГОД(C5);2);СЦЕПИТЬ(ЕСЛИ(МЕСЯЦ(C5)<9;"0";"");МЕСЯЦ(C5));СЦЕПИТЬ(ЕСЛИ(ДЕНЬ(C5)<9;"0";"");ДЕНЬ(C5));"/";МАКС(Архив!A2:A1000)+1)

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

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

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

Формула с помощью функции СЦЕПИТЬ объединяет следующие элементы:

• два правых символа номера года даты, введенной в ячейку С5, которые выделяются с помощью функции ПРАВСИМВ;
• два символа номера месяца, причем если номер месяца имеет только одну цифру, то перед этой цифрой добавляется значение 0;
• аналогично происходит формирование символов номера дня в месяце;
• разделительный знак "/";
• порядковый номер в базе регистрации договоров, который определяется с помощью функции МАКС, к которому суммируется значение 1.

Создание элементов управления Переключатель

Как было написано выше, управление формулами в ячейках С3 (номер договора) и С7 (срок оплаты) производится с помощью значений 1, 2, 3 и 4, вводимых в ячейку В3. Но не совсем удобно — вводить с клавиатуры эти значения, которые нужно помнить, и, как следствие, возможен ввод ошибки.

Для выбора нужного значения, вводимого в ячейку В3, используя панель Формы (рис. 4.6.) создайте четыре элемента управления Переключатель (рис. 6.6.), активизация каждого из которых, будет изменять содержимое ячейки В3 от значения 1 до значения 4. Эти элементы управления создаются аналогично описанным в главе 3 (Счетчик и Список).

При создании переключателей, введите в них текст:

• Номер договора и дата оплаты;
• Только дата оплаты;
• Только номер договора;
• Нет номера договора и даты оплаты,

который будет отражать назначение каждого переключателя. Для ввода (редактирования) текста воспользуйтесь командой контекстного меню Изменить текст (рис. 6.9.)

Рис. 6.6. Фрагмент рабочего листа "Договор" с элементами управления

Для управления содержимым ячейки В3 выделите первый созданный элемент управления Переключатель и выполните команду Формат/Элемент управления или комбинацию клавиш [Ctrl+1], вызовите диалоговое окно Формат элемента управления (рис. 6.7.), в котором на вкладке Элемент управления в поле Связать с ячейкой укажите адрес ячейки В3.

Рис. 6.7. Диалоговое окно "Формат" элемента управления "Переключатель"

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

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

Для управления содержимым ячейки В5, значение которой управляет формулой, находящейся в ячейке С5 (дата заключения договора) создайте элемент управления Флажок (рис. 6.6.).

Для управления ячейкой В5 в диалоговом окне Формат элемента управления на вкладке Элемент управления в поле Связать с ячейкой укажите адрес ячейки В5. Если флажок активизирован, то ячейка В5 будет возвращать значение ИСТИНА, в противном случае ЛОЖЬ.

Назначение макросов элементам управления

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

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

Задача макроса НомерДатаОплатыДоговор (рис. 6.8.) — при активизации любого переключателя предусмотреть возможность ввода формул в ячейки С3 и С7. Для этого запишите макрос, выполнив следующую последовательность действий:

• предварительно введите формулы в ячейки С3 и С7;
• запустите макрос на запись;
• поместите табличный курсор в ячейку С3, нажмите последовательно клавиши F2 и Enter;
• поместите табличный курсор в ячейку С7 и повторите операцию;
• остановите запись макроса.

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

Ввод формулы заключения договора

Макрос ДатаЗаключенияДоговора (рис. 6.8.) вводит формулу в ячейку С5 при обращении к элементу управления Флажок и создается аналогично предыдущему.

Присвоение макросов элементам управления

Для присвоения макроса элементу управления, выделите его и нажатием на правую кнопку мыши и вызовите контекстное меню (рис. 6.9.), в котором выберите команду Назначить макрос. В появившемся диалоговом окне Назначить макрос объекту (рис. 4.18.) в поле Имя макроса выберите макрос и нажмите кнопку ОК.

Рис. 6.9. Контекстное меню при назначении макроса элементу управления

Область формирования элементов договора

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

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

• номер договора (ячейка В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, с помощью текстовой функции СЦЕПИТЬ соединяет текст, функции и ссылки (рис. 6.10.).

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

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

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

и так далее

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Реквизиты

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

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

Создание интерактивного текстового документа "Договор"

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

Текст, приведенный в данном договоре можно разделить на две составляющие:

• вводимый в текстовом редакторе Word (постоянный);
• экспортируемый из Excel (переменный).

К тексту, вводимому в редакторе Word, относится текст, который вводится непосредственно в Word и в каждом генерируемом договоре остается без изменений.

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

На рис. 6.1.с текстом упрощенного типового договора купли-продажи к первой составляющей относятся:

• текст "Договор покупки-продажи";
• наименования разделов договора;
• пункт 3.1. раздела договора "Дополнительные условия"

К тексту, экспортируемому из Excel, относятся:

• номер договора;
• место заключения договора;
• дата заключения договора;
• пункты разделов "Предмет договора" и "Порядок расчетов";
• реквизиты сторон.

Созданный файл с этим текстовым документом сохраните на жестком диске под именем Договор в папке Договора купли продажи.

Создание связи между Excel и Word

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

• перейдите на лист Договор книги База, выделите ячейку В8 и скопируйте в буфер обмена;
• перейдите в текстовый редактор Word файл Договор;
• курсором выделите место внедрения объекта — после символа №;
• клавишами Delete или Backspace удалите прочерк, после чего выполните команду Правка/Специальная вставка;
• в появившемся диалоговом окне Специальная вставка (рис. 6.18.) в списке Как выделите Лист Microsoft Excel Объект и активизируйте переключатель Связать. В верхней части диалогового окна указывается Источник — Лист Microsoft Excel Договор!R8C2, где Реквизиты — имя листа, а R8C2 — восьмая строка второй столбец листа. В области Результат будет указано: Вставка содержимого буфера обмена как объекта типа рисунок. Вставка связи устанавливает связь с файлом данных. Изменения в исходном файле будут автоматически отражаться в документе.;
• нажмите на кнопку ОК.

Рис. 6.18. Диалоговое окно Специальная вставка текстового процессора Word

Для выделения внедренного объекта установите на него курсор мыши и щелкните правой кнопкой мыши (рис. 6.19.). При двойном нажатии на кнопку мыши вы сразу же перейдете на лист Microsoft Excel, являющийся источником связи, причем будет выделена область, с которой установлена связь.

Рис. 6.19. Внедренный объект элемента связи с Excel

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

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

Шрифт экспортируемого текста и его величина задается в Excel.

Рис. 6.20. Внешний вид полученного текстового документа Договор

Создание базы заключенных договоров

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

1. Порядковый номер договора в базе данных (столбец А);
2. Присвоенный договора номер (столбец В);
3. Преамбула, которая состоит из:
   1. Место заключения (столбец С);
   2. Дата (столбец D);
   3. Кто, с кем, в чьем лице и на основании какого документа заключил этот договор (столбец E);
4. Предмет договора (столбец F);
5. Условия договора (столбец G);
6. Реквизиты Продавца (столбцы Н:Q) и Покупателя (столбцы R:AA).

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

Подпрограмма автоматизации операции регистрации договоров

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

Рис. 6.21. Последовательность операций для переноса данных регистрируемого договора в базу данных

Создание массива переносимых данных

На рис. 6.22. показан фрагмент рабочего листа Договор с массивом переносимых данных собранных в области ячеек ВА1:ВА26. В столбце ВВ, в качестве примера приведены значения, полученные с помощью этих ссылок.

Рис. 6.22. Массив переменных данных договоров, переносимых в базу данных

Подпрограмма переноса информации в базу данных

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

Механическая запись макроса

Последовательность записи макроса состоит из следующих действий:

• начните запись макроса, находясь на рабочем листе Договор;
• на листе Договор выделите диапазон ячеек ВА1:ВА26 и скопируйте в буфер обмена;
• перейдите на лист Архив, поместите табличный курсор в свободной строке в ячейку столбца В и выполните команду Правка/Специальная вставка;
• в диалоговом окне Специальная вставка активизируйте переключатель Значения, отметьте опцию Транспонировать, и нажмите кнопку ОК;
• перейдите на рабочий лист Договор, установите табличный курсор в ячейку А1 и остановите запись макроса.

После записи макроса создайте кнопку с текстом Внести в Архив и назначьте ей выполнение созданного макроса.

Редактирование макроса переноса данных

Редактирование макроса производится в Редакторе Visual Basic и заключается в дополнении текста кода VBA элементами, которые невозможно записать в механическом режиме.

Метод определения первой пустой строки рассматривался в прошлой главе и заключается в определении в столбце А активного рабочего листа (ActiveSheet) количества заполненных ячеек с помощью функции СЧЕТЗ (CountA), к которому добавляется значение 1. Полученный результат присваивается переменной Row. На этом же активном рабочем листе выделяется ячейка, имеющая адрес:

• строка равна значению переменной Row;
• столбец В, потому что во второй аргумент адреса равен значению 2.

Замените ссылку на ячейку столбца В после перехода на лист Архив строками кода VBA:

Row = Application.CountA(ActiveSheet.Range("A:A")) + 1

ActiveSheet.Cells(Row, 2).Select

В строке кода VBA вставки через диалоговое окно Специальная вставка оставьте только аргументы вставки значений и транспонирования, после которой добавьте две строки:

ActiveCell.Offset(0, -1).Select

ActiveCell.Value = Application.Max(ActiveSheet.Range("A:A")) + 1

Первая строка встречалась ранее. Ее задача -перемещение относительно активной ячейки по той же строке на один столбец влево.

Задача второй строки — присвоение содержимому активной ячейке, значения определенного функцией МАКС (Max) в диапазоне столбца А и увеличенного на единицу. Преимущества такой записи перед вводом формулы и последующей заменой ее на вычисленное значение очевидно — меньшее количество действий, а значит и большее быстродействие.

Рис. 6.23. Подпрограмма переноса данных на лист "Архив"

Просмотр предыдущих договоров из базы данных их регистрации

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

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

=ВПР($E$8;Архив!$A$1:$AA$1000;4;ЛОЖЬ)

Остальные строки можно очистить и затем скрыть. Для этого выделите строки 1:4, 6, 7 и выполните команду Правка/Очистить/Все и затем Формат/Строка/Скрыть.

Формула в ячейке С9 остается без изменений, а во всех остальных ячейках заменяются на аналогичные формулы, как в ячейке С5 для поиска данных на листе Архив.

Ячейка Е8 предназначена для ввода порядкового номера, по которому будет осуществляться поиск данных из рабочего листа Архив. Для управления этой ячейкой внедрите элементы управления Счетчик и Список, как показано на рис. 6.24.

Рис. 6.24. Рабочий лист "Просмотр"

Для просмотра предыдущих договоров в текстовом редакторе, создайте в папке Договор купли продажи текстовый файл Архивные договора и в него внедрите объекты для экспорта текстовых данных из Excel, как и при создании текстового документа Договор, только из рабочего листа Просмотр.

Перемещение по листам рабочей книги и открытие текстовых документов

Обратите внимание на правую часть рабочих листов Договор (рис. 6.5.) — ячейки Е12, Е16 и Е17, и Просмотр (6.24.), ячейки F5, F8 и F9. В эти ячейки введены гиперссылки. В двух нижних ячейках введены гиперссылки для перемещения по листам рабочей книги, а в верхних ячейках указана ссылка на текстовые файлы Договор и Архивные договора, соответственно.

Гиперссылками в Excel могут быть объекты или ячейки, при активизации которых происходит перемещение по рабочему листу на указанную позицию, переход на другой рабочий лист, открытие и/или переход на другие файлы Microsoft Office. Гиперссылка создается двумя способами:

• командный способ назначения гиперссылки;
• с помощью функции ГИПЕРССЫЛКА.

Командный способ назначения гиперссылки

Открытие текстового документа или переход к нему

Для создания гиперссылки открытия (или перехода к) текстового документа Договор выполните следующее:

• поместите табличный курсор в ячейку Е12. Предварительно в эту ячейку можете ввести текст, которым будет именоваться выполнение этого перехода. При отсутствии введенного текста, в ячейке после создания гиперссылки будет указано имя и расширение открываемого файла;
• выполните команду Вставка/Гиперссылка или нажмите комбинацию клавиш [Ctrl+K] после чего откроется диалоговое окно Добавить гиперссылку (рис. 6.25.);
• нажмите на кнопку Обзор, расположенную справа от поля Связать с файлом/URL, что откроет диалоговое окно Связать с файлом (рис. 6.26.), в котором укажите путь к файлу;
• при нажатии на кнопку ОК диалогового окна Связать с файлом, в поле Связать с файлом/URL, будет указан путь;
• в диалоговом окне Добавить гиперссылку нажмите на кнопку ОК.

Рис. 6.25. Диалоговое окно "Добавить гиперссылку"

Рис. 6.26. Диалоговое окно "Связать с файлом"

Перемещение по рабочей книге

Для перемещения по рабочей книге при помощи гиперссылок в диалоговом окне Добавить гиперссылку нажмите на кнопку Обзор, расположенную справа от поля Имя объекта в документе (рис. 6.25.). Это вызовет появление диалогового окна Просмотр книги Excel, в которой выберите либо рабочий лист с указанием ссылки на определенную ячейку, либо именованный диапазон.

Рис. 6.27. Диалоговое окно "Просмотр книги Excel"

Изменение гиперссылки

Для изменения гиперссылки, выделите объект/ячейку и выполните либо команду ввода гиперссылки, либо в контекстном меню выберите команду Гиперссылка/Изменить гиперссылку. Это вызовет появление диалогового окна Изменение гиперссылки (рис. 6.28.), с помощью которого введите необходимые изменения.

Рис. 6.28. Диалоговое окно "Изменение гиперссылки"

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

Использование функции ГИПЕРССЫЛКА

В Excel есть способ ввода гиперссылки с помощью одноименной функции (рис. 6.29.). Синтаксис функции:

ГИПЕРССЫЛКА (Адрес;имя)

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

Имя — текст перехода или численное значение, отображаемое в ячейке. Имя отображается синим цветом с линией подчеркивания. Если аргумент имя опущен, ячейка в качестве текста перехода отображает адрес.

Функция гиперссылки перехода к текстовому документу Договор:

=ГИПЕРССЫЛКА("D:\Самоучитель\Договор купли продажи\Договор.doc";"Договор")

Рис. 6.29. Панель функции ГИПЕРССЫЛКА

Итоги

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

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

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

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

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

Дополнение от 25.02.2005

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

Письмо от читателя

Большое спасибо за Ваши примеры по автоматизации и моделированию бизнес-процессов с помощью Excel. Я пока использую некоторые из примеров. Так как по роду деятельности я занимаюсь оформлением договоров то Ваш пример "Договор купли продажи" очень заманчив в практическом применении, что я и намерен в ближайшее время постараться реализовать. В данном примере в файле "База.xls" мной замечена неточность в формуле (ячейка AC1) формирования числа суммы договора. Формирование числа с цифрами в разряде до тысячи и до миллиона, а именно от ХХХХ001 до ХХХХ099 и от Х001ХХХ до Х099ХХХ соответственно, не формируются две первые цифры "00" и первая цифра "0" соответственно. Я попытался доработать Вашу формулу (ячейка AD1), но она получилась громоздкой. Так как я пока осваиваю "программирование" Excel и не все возможности стандартных функций мне известны прошу помочь улучшить формулу, т.к. мне нужно ещё увеличить разрядность числа, а Excel имеет ограничение по размеру формулы.

Исправление ошибки

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

Если рассмотреть простые формулы, показанные на рис. 6.13., и в качестве рассматриваемого числа в ячейку В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)))

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

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

Следующая задача — доработать формулу в ячейке В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)

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

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

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

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

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

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