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

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

В ячейку С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. Контекстное меню при назначении макроса элементу управления