Файл: Лабораторная работа 1. Excel.doc

Оформить лист «ЖурналРегистрацииКУ» и ввести в него данные.

Оформить лист «КомандировочноеУдостоверение».

Организовать работу модуля формирования даты прописью.

Добавить элементы управления.

Записать два макроса: «Вывод на печать лицевой стороны КУ», «Вывод на печать обратной стороны КУ».

1. Журнал регистрации командировочных удостоверений (КУ)

Журнал регистрации КУ представляет табличную базу данных, или с позиции Excel – список. Поля базы данных расположите в любом, удобном для пользователя порядке, например, как показано на рис.1. и 2.

1. В ячейку А1 ввести наименование предприятия – ОАО «Зодиак». При проектировании зададимся условием, что разрабатываемое приложение будет содержать до 200 записей. Поэтому ограничьте вертикальное расположение таблицы 200-й строкой.

2. Ввод таблицы начинается со строки №3. Данные в столбцы A, B, C, D, E, F, G, I, J, L, M вводятся (согласно рис. 1, 2), столбцы H и K – рассчитываются.

3. В столбце Н «Дата и номер командировочного удостоверения», в ячейку Н4 может быть введена формула (1), которая соединяет первые элементы текста в столбце G – «Дата и номер приказа», и порядковый номер документа в журнале регистрации.

Рис.1. Левая область журнала регистрации командировочных удостоверений на рабочем листе ЖурналРегистрацииКУ

Рис.2. Правая область журнала регистрации командировочных удостоверений на рабочем листе ЖурналРегистрацииКУ

4. Для автоматического определения продолжительности командировки в ячейку К5 «Срок командировки» введите формулу (2), которая вычитает из даты прибытия дату выбытия и прибавляет значение 1.

2. Командировочное удостоверение

1. Создать форму командировочного удостоверения (рис. 3). При создании формы командировочного удостоверения на рабочем листе КомандировочноеУдостоверение, не следует пренебрегать объединением ячеек и переносом текста в ячейках с формулами, созданием границ различной толщины (линии ограничивающие границы ячеек), применением различных шрифтов и их размеров.

Рис. 3. Форма командировочного удостоверения

2. Ввести в ячейку L1 цифру 1, порядковый номер первого сотрудника с таблице «Журнал командировочных удостоверений». По значению порядкового номера, введенного в эту ячейку, будет осуществляться поиск всех данных в журнале регистрации для заполнения выведенного на экран монитора командировочного удостоверения на конкретного работника.

3. Функция, на которой базируется автоматизация заполнения командировочного удостоверения – функция поиска ВПР. В ячейку Е3 (Фамилия, имя, отчество) ввести формулу (3).

4. Остальные формулы (кроме формулы в ячейке D5) отличаются от описанной выше только номером столбца в третьем аргументе. Значения аргументов:

• 5 – ячейка F7 (пункт назначения);

• 6 – ячейка D9 (наименование предприятия);

• 11 – ячейка G12 (срок командировки);

• 12 – ячейка D13 (цель командировки);

• 7 – ячейка G15 (приказ от);

• 13 – ячейка I16 (паспорт серии);

• 8 – ячейка В11 (дата и номер командировки).

5. Ввод текста места работы и должности. Формула в ячейке D5 использует комбинацию функций СЦЕПИТЬ, ВПР, а также ссылку и ввод текстовых данных (4).

По ссылке на ячейку А1 листа ЖурналРегистрацииКУ осуществляется ввод текста наименования предприятия. Первая функция ВПР производит поиск должности работника, а вторая - наименования отдела, в котором он работает. Все определенные текстовые значения (предприятие, должность и наименование отдела) соединены функцией СЦЕПИТЬ с разделением между собой знаками запятых и пробелами.

6. Ввести обратную сторону командировочного удостоверения. Обратная сторона командировочного удостоверения расположена на том же рабочем листе «КомандировочноеУдостоверение», ниже области занимаемого лицевой стороной документа (рис. 4.).

Рис.4. Обратная сторона командировочного удостоверения

Модуль формирования даты прописью представлен на рис. 5.

Рис.5. Модуль написания даты прописью с формулами

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

1. В ячейку L25 «Вход модуля» в вести формулу (5). Это ячейка для ввода даты, которая будет отображена в виде текстовой строки. В ячейку входа модуля может быть введена дата с клавиатуры, указана ссылка на ячейку содержащей дату или формула, возвращающая эту дату.

2. В ячейку M25 «Промежуточные вычисления» ввести формулу (6):

3. В ячейку N25 «Выход модуля» ввести формулу (7). В этой ячейке, окончательно формируется текстовая запись введенной на вход даты. На выход модуля может быть указана ссылка, из ячейки в которой должен находиться текст даты.

4. В ячейку В25 вставить ссылку на ячейку N25.

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

Для того, чтобы создать элемент управления «Список» необходимо:

1. Для создания элемента управления «Список» используется панель инструментов Формы (рис. 6.), для активизации которой выполнить команду меню Вид/Панели инструментов – Формы.

2. Для внедрения элемента управления «Список» щелкните левой кнопкой мыши на панели «Формы» по указателю элемента управления «Список». Указатель мыши, после перевода его на поле рабочего листа, приобретет вид крестика. Выбрав место расположения элемента управления на листе, и нажав левую кнопку мыши, очертите контур будущего элемента управления (рис. 7.).

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

Рис.8. Диалоговое окно «Формат» элемента управления для элемента управления «Список»

4. Поместите курсор в поле «Формировать список по диапазону» и на рабочем листе ЖурналРегистрацииКУ выделите область ячеек $А$4:$А$200.

5. Выделенный в списке элемента управления порядковый номер удостоверения должен автоматически вводиться в ячейку L1, для чего поместите курсор в поле Помещать результат в ячейку и на листе КомандировочноеУдостоверение, выделите мышью ячейку L1. В области Возможен выбор активизируйте переключатель Только одного значения, после чего нажмите кнопку ОК. Следует заметить, что значение помещаемого результата в ячейку L1 будет зависеть от выбранного значения в списке, начиная сверху, независимо от самого значения указанного порядкового номера введенного в столбце А. Это следует учитывать при формировании порядковых номеров командировочного удостоверения.

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

Для того, чтобы создать элемент управления «Счетчик» необходимо:

1. Рядом с элементом Список поместите элемент управления Счетчик (рис. 9.). С помощью счетчика можно последовательно «перебирать» в любом направлении командировочные удостоверения, находящиеся в журнале регистрации с шагом изменения равным единице или другим, заданным пользователем.

2. Диалоговое окно «Формат элемента управления» (рис. 10.) для элемента управления «Счетчик», несколько отличается от предыдущего. В поле «Начальное значение» отображается значение, которое содержится в этот момент времени в ячейке L1.

3. В поле «Минимальное значение» введите начальный номер, с которого «Счетчик» начинает перебирать номера с шагом, значение которого будет введено в поле «Шаг изменения». При желании введите значение в поле «Максимальное значение», в противном случае Excel введет его самостоятельно.

Макрос - Вывод на печать бланка командировочного удостоверения

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

1. Выделить мышью область А1:J20 (лицевая сторона командировочного удостоверения).

2. Нажать на клавишу Ctrl и, удерживая ее, выделить область А22:J69 (обратная сторона).

3. Выполнить команду Файл/Область печати – Задать.

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

Макрос вывода документа на печать

Для того чтобы начать запись макроса, необходимо:

1. Выполнить команду Сервис/Макрос – Начать запись. Появится диалоговое окно «Запись макроса» (рис. 11.). В поле «Имя макроса» ввести – ПечатьЛицевойСтороны».

Рис.11. Диалоговое окно Запись макроса

2. После нажатия на кнопку ОК или клавишу Enter Excel начинает запись макроса, для чего фиксирует все действия пользователя, независимо от того, правильны ли они. Фиксация действий производится на языке кода VBA и будет осуществляться до остановки записи. Для остановки записи предназначена появляющаяся после начала записи кнопка Остановка (рис.13.).

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

3.1. Выполните команду Файл/Печать.

3.2. В появившемся диалоговом окне «Печать» в области «Печатать» введите номера страниц с 1-й по 1-ю и нажмите кнопку ОК.

3.3. Для остановки записи макроса нажмите кнопку «Остановка» (рис. 12) или выполните команду Сервис/Макрос/Остановка записи.

Рис.12. Кнопка Остановка

Для осуществления печати лицевой стороны удостоверения, необходимо:

1. Создать кнопку «Печать лицевой стороны». Для этого вызвать панель инструментов «Формы» (рис. 6), по команде Вид/Панели инструментов. Нажать на элемент «Кнопка», курсором мыши растянуть на свободном месте.

2. После создания кнопки, появиться окно «Назначить макрос объекту», в нем выбрать макрос для печати лицевой стороны «ПечатьЛицевойСтороны».

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