Доверенности

Лаб. 4. Создание текстовых документов с помощью Excel на примере оформления генеральной доверенности на автомобиль

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

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

Доверенности

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

Рис. 7.1. Пример генеральной доверенности на право управления и распоряжения автомобилем

Данная доверенность содержит шесть абзацев:

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

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

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

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

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

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

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

Формирования дат

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

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

Дата цифрами в текстовом формате

На рис. 7.2. приведен пример визуального отображения даты, ссылка на ячейку в которую она введена, находится внутри текстовой функции СЦЕПИТЬ, введенной в ячейку С1. Этот пример похож на текстовое отображение числовых данных, приведенных в прошлой лабораторной работе.

Рис. 7.2. Пример отображения даты в текстовом формате

Ячейка В1 отформатирована как дата в формате Excel. В аргументе функции СЦЕПИТЬ задана ссылка на ячейку В1. Эта текстовая функция автоматически переводит дату в число, равное количеству дней, прошедших с 1 января 1900 года, от которой Excel ведет исчисление дат. В таком виде эта дата и будет попадать в текст. Естественно, в таком отображении, дата в текстовом документе будет не читаема. Поэтому необходимо разработать алгоритм отображения даты внутри текста в таком же виде, как она представлена в ячейке В1 на рис. 7.2.

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

Рис. 7.3. Формула отображения в текстовом виде формата даты

В формуле используются:

• текстовая функция СЦЕПИТЬ - для соединения отдельных элементов, составляющих дату и введенного текста в единую текстовую строку;
• функции дат - для деления даты отдельно на элементы: день, месяц и год;
• логическая функция ЕСЛИ.

Рассмотрим вторую строку введенной формулы. Первый аргумент функции ЕСЛИ проводит сравнение номера дня со значением 9 и если номер дня равен или меньше значения 9, то тогда во втором аргументе, с помощью функции СЦЕПИТЬ к значению номера дня слева добавляется значение " 0". Если условие первого аргумента не выполняется, то номер дня возвращается третьим аргументом функции ЕСЛИ без изменений.

С номером месяца производится та же операция. И в завершение, функция СЦЕПИТЬ, соединяет в единое целое полученные значения составляющих даты, вставив между ними точки и добавив в конце текст - " г.".

Написание даты прописью

Для написания прописью номера дня и года можно применить таблицу, показанную на рис. 7.4. Отличие текста написания номера дня от номера года зависит только в склонении, например, номер дня 11 пишется одиннадцатое, а такой же год - одиннадцатого.

В таблице ячейка С5 является входом, в которую вводится число (или ссылка на ячейку, содержащую это число), а ячейка D5 выходом, в которой это число уже пишется прописью.

В области ячеек С6: D7 выделяются отдельно десятки и единицы этого числа. Формулы в ячейках С8: С10 предназначены для написания прописью количества десятков, а в ячейках D8: D16 для написания прописью единиц. Таблица пишет прописью числа от единицы до тридцати девяти.

Рис. 7.4. Таблица написания прописью номера дня в месяце

После создания таблицы и тестирования, ее размеры минимизируются путем вложения формул. На рис. 7.5. приведена минимизированная таблица (модуль) написания номера года. Модуль расположен в ячейках С6: F6, в которой ячейка С6 является входом, а ячейка F6 выходом. Номер года пишется от 2001 до 2039, причем прописью определяется только число от 1 до 39, а текст две тысячи дополняется.

Рис. 7.5. Модуль написания прописью номера года

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

Рис. 7.6. Полученный модуль написания даты прописью

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

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

Метод 1 (строки 4: 6). Вычтя одну дату из другой и применив функции ГОД, МЕСЯЦ и ДЕНЬ, можно определить эти данные, но Excel анализирует дату, полученную в результате вычитания, и поэтому полученный результат не будет достоверным.

Метод 2 (строки 8: 10). Состоит в том, чтобы определить год, месяц и день отдельно в двух датах, а затем произвести операцию вычитания этих составляющих. Но при вычитании отдельно лет, месяцев и дней результат может носить отрицательный характер, а такого быть не может.

Рис. 7.7. Примеры определения количества дней, месяцев и лет в интервале между двумя датами

На основании второго метода попытаемся проанализировать и использовать полученные результаты. На рис. 7.8. приведен пример модуля определения количества лет, месяцев и дней. Модуль занимает диапазон ячеек В4: D6. В ячейки В4 и В5, являющимися входом модуля, вводятся даты. Выходы модуля:

• ячейка D4 - для вывода количества лет;
• ячейка D5 - для вывода количества месяцев;
• ячейка D6 - для вывода количества дней.

Рис. 7.8. Модуль расчета количества лет, месяцев и дней в интервале между датами

Формула в ячейке D4 работает по следующему алгоритму:

• Если результат вычитания месяцев получен отрицательный, то количество полученных в результате вычитания лет, нужно уменьшить на один год. А этот год, как компенсацию за вычитание, в виде числа 12 месяцев, следует добавить к количеству полученных в результате вычитания месяцев в ячейке D5.Если номер вычитаемого месяца меньше, то тогда года вычитаются без корректировки.
• Если же номера месяцев в обоих датах равны, то сравниваются дни и если результат отрицательный, то из полученного результата вычитания лет вычитается единица.

Похожий алгоритм заложен и в формулах для определения количества месяцев и дней в ячейках D5 и D6.

Формулы в ячейках С4 и С5 проводят дополнительный анализ количества календарных дней в месяце, находящемся в большей дате (ячейка В4):

Если месяц, даты введенной в ячейку В4, имеет значение 3, то необходимо дополнительно проверить, високосный это год или нет. Для анализа високосного года в дате, введенной в ячейку В4, в формуле в ячейке В5 применяется функция Excel ОСТАТ (рис. 7.9.). Если год високосный, то результат деления этого года на 4 будет равен нулю.

Если в результате деления получена дробная часть (т.е. год не високосный), то количество дней в месяце, предшествующем 3-му месяцу, будет равно 28. Если год високосный, то функция ОСТАТ выдаст значение 0. Тогда вторая функция ЕСЛИ при выполнении условия сравнения со значением 0 перейдет к своему второму аргументу, где указано значение 29 (дней в феврале).

Рис. 7.9. Панель функции ОСТАТ

Для того чтобы написать прописью определенные количество месяцев, дней и лет, необходимо соединить в единое целое (рис. 7.10.):

• модуль определения этих параметров - область ячеек В4: D6;
• модуль написания текста этих вычисленных параметров - область ячеек В9: D14.

Входами модуля написания текста являются ячейки В9; С9 и D9, в которые введены ссылки на соответствующие ячейки выхода модуля определения параметров.

Рис. 7.10. Модули написания прописью количества лет, месяцев и дней между двумя датами