На этом шаге мы рассмотрим некоторые способы автоматического назначения различных параметров.

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

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

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

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

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

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

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

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

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

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

Исходя из этого и созданы формулы в ячейках С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, используя панель Формы (рисунок 1) создайте четыре элемента управления Переключатель (рисунок 2), активизация каждого из которых, будет изменять содержимое ячейки В3 от значения 1 до значения 4.

Рис.1. Панель инструментов Формы

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

Эти элементы управления создаются аналогично описанным на 232 шаге (Счетчик и Список).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис.6. Диалоговое окно Назначить макрос объекту

На следующем шаге мы рассмотрим область формирования элементов договора.

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