договор в эксель с формулами

Создание формы заполнения документов в Excel

Видео инструкция

Подготовка

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

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

Автоматизация формы

Дата договора

В Excel существует множество функций по работе с датами. Если дата договора всегда текущая, то можно использовать функцию =СЕГОДНЯ(). При необходимости можно настроить, чтобы дата рассчитывалась как следующий рабочий день =РАБДЕНЬ(СЕГОДНЯ();1).

Для вставки даты вы можете использовать календарь из программы:

Сумма договора

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

Срок прописью

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

В диалоговом окне сформируйте нужную вам формулу.

Заполнение заказчика

Данные заказчика идеально подгружать из внутренней системы заказчика 1С или другой CRM. Если такой возможности нет, то для начала можно упростить жизнь путем создания справочника правовых форм (ООО, ОАО и т.д.) Тогда нам потребуется просто выбрать из списка нужную форму и указать наименование. Остальное мы сделаем на формулах.

Итак создадим отдельный лист и разместим там таблицу со списком правовых форм.

Таблицу можно отформатировать как табличный диапазон, так с ней будет проще работать далее.

Теперь на форме создадим динамичный выпадающий список с данными правовых форм.

Результат выглядит следующим образом:

Склонение ФИО подписанта

Сейчас посмотрим как автоматически склонять ФИО подписанта. Для этого в программе есть соответствующая функция, которую можно вставить из меню:

Программа умеет склонять 90% русских ФИО по всем падежам.

Склонение должности и других фраз

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

Итоговая форма

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

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

Источник

Учет договоров в Excel с напоминаниями

Шаблон журнала учета договоров

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

Реестр учета договоров в Excel (образец)

Проверка данных в Excel

Цветовое выделение просроченных договоров

Текущий статус договора

Идем в ячейку А2 и вводим формулу:

=ЕСЛИ(СЕГОДНЯ()-C2>1;»Договор истек»;»Договор действует»)

Работает по такому же принципу, если дата окончания договора больше текущей хоть на 1 день, выводится сообщение «Договор истек».
Копируем формулу на другие ячейки столбца А, вводим данные, убеждаемся, что все работает.
Учет договоров в Excel с напоминаниями
Итак, просроченные договоры у нас будут подсвечиваться кирпичным цветом, в первом столбце будет выводится надпись «Договор истек«.

Получившийся файл можно скачать по ссылке.

Учет договоров в 1С

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

1С:Бухгалтерия 8

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

1С:Договоры 8

Отдельный специализированный продукт, подробное описание и стоимость можно посмотреть здесь

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

Источник

Программа формирования договоров по шаблону

Программа предназначена для формирования (заполнения) договоров купли-продажи.

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

Создаваемые документы получают имена файлов (и имена листов) согласно номеру договора из 8 столбца таблицы:

Договор КП № 1825.xls
Договор КП № 1820.xls
Договор КП № 1823.xls

Ознакомьтесь также с универсальной надстройкой формирования документов по шаблонам,
которая может делать всё тоже самое, что и эта программа,
только в качестве шаблонов могут выступать, помимо документов Word, ещё текстовые файлы, и книги Excel.

По вышеприведённой ссылке программа заполнения документов Word из Excel доступна для бесплатного скачивания.

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

В качестве параметров задаётся диапазон дат, для которого будет производиться формирование договоров.

Шаблон договора купли-продажи расположен на отдельном листе.

Все поля шаблона заполняются формулами, большая часть из которых задана через диспетчер имён.

Фамилии, адреса и прочие реквизиты также зависят от даты договора
(см. формулы в фиолетовых ячейках)

В сведениях о пакете ЦБ использована пользовательская функция «Сумма прописью» для вывода текстового представления количества и стоимости ценных бумаг.

Для определения типа ценной бумаги (Облигация, Акция привилегированная, Акция обыкновенная именная, Опцион) применена пользовательская функция ТипЦБ:

Есть возможность также распечатать или просмотреть заполненный договор без формирования отдельного файла.

Для этого необходимо ввести номер строки интересующей записи в базе данных,
и на листе ШАБЛОН вы увидите договор, заполненный данными из выбранной строки

Комментарии

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

PS: Даты куда удобнее вводить напрямую в ячейку без всяких календарей.
Просто вводите дату в формате DD-MM-YY (например, 12-07-50) на дополнительной цифровой клавиатуре.
Excel автоматически распознает данный текст как дату, и отобразит его в ячейке соответствии с региональными настройками (например, как 12.07.1950)

Здравствуйте, администратор сайта ExcelVBA.ru

Если вам известно, подскажите пожалуйста как в Элементе управления календарь 11, сделать раскрывающееся поле для года (там есть день, месяц, год), так вот с днём и месяцем порядок, а вот выпадающий список для выбора года, содержит всего 5 строчек для выбора. А часто нужно вписывать даты рождения типо 1950г. и очень неудобно там накручивать до нужного. В общем не могу узнать как сделать выпадающий список для выбора года в ЭУ Календарь 11 execel 2003 ДЛИННЕЕ.
Если вдруг у вас есть минутка, помогите. Зарание благодарен.

Источник

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

Глава 06
Автоматизация формирования текстовых документов с помощью Excel на примере типового договора купли-продажи

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=СЦЕПИТЬ(ПРАВСИМВ(ГОД(C5);2);СЦЕПИТЬ(ЕСЛИ(МЕСЯЦ(C5) _________ 200 г.»;AC5)

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

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

Формула в объединенной ячейке А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 производится выделение разрядов тысяч.

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

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

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

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

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

Формула в ячейке В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, относится текст, который вводится непосредственно в Word и в каждом генерируемом договоре остается без изменений.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дополнение

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

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

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

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

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

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

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

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

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

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

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

В находившейся в ней формуле:
=СЦЕПИТЬ(B4;» «;B5;» «;B6;B15)
нужно заменить ссылки В5 на ячейку J5, и В6 на ячейку J6.

После чего указанные ссылки заменить на формулы находящиеся в ячейках 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.

© Интернет-проект «Корпоративный менеджмент», 1998–2021

Источник