Тема 10. Создание форм в MS Excel с использованием элементов управления.

КРАТКАЯ СПРАВКА

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

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

Таким образом, формы создаются на рабочем листе MS Excel и оформляются элементами управления, которые вызываются командой Вид/Панели инструментов/Формы.

При настройке элементов управления часто используют функцию ИНДЕКС.

Эта функция возвращает значение или ссылку на значение из таблицы или диапазона. Функция ИНДЕКС имеет две синтаксические формы: ссылка и массив. Если первый аргумент функции ИНДЕКС является массивом констант, лучше использовать форму массива.

ИНДЕКС(массив;номер_строки;номер_столбца)

Массив   — диапазон ячеек или массив констант.

     Если массив содержит только одну строку или один столбец, аргумент «номер_строки» (или, соответственно, «номер_столбца») не является обязательным.

     Если массив занимает больше одной строки и больше одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, то функция ИНДЕКС возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».

Номер_строки   — номер строки в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.

Номер_столбца   — номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.

Замечания:

     Если используются оба аргумента — и «номер_строки», и «номер_столбца», — то функция ИНДЕКС возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.

     Если задать для аргумента «номер_строки» или «номер_столбца» значение 0, функция ИНДЕКС возвратит массив значений для целого столбца или, соответственно, целой строки. Чтобы использовать массив значений, введите функцию ИНДЕКС как формулу массива (Формула массива. Формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ВВОД.) в горизонтальный диапазон ячеек для строки и в вертикальный для столбца. Чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ВВОД.

     Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС возвращает значение ошибки #ССЫЛ!.

Например:

Дана таблица (рис.10.1.)

 

Рис.10.1. Таблица с данными

 

Для этих данных функция ИНДЕКС будет обозначать:

=ИНДЕКС(A2:B3;2;2)	Значение ячейки на пересечении второй строки и второго столбца в диапазоне (Груши)
=ИНДЕКС(A2:B3;2;1)	Значение ячейки на пересечении второй строки и первого столбца в диапазоне (Бананы)

 

 

 

 

 

Примеры выполненных  заданий:

 

Пример №1:

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

 

Рис.10.2.  Вид готовой формы.

 

Решение:

Разработку сметы следует начать с создания и оформления листов.

 

Оформим лист «Смета» (рис.10.3.):

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис.10.3. Лист «Смета»

 

Оформим лист «Холодильники» (рис.10.4.):

 

Наименование	Цена	Производитель
не заказывать	$0	нет
ARISTON OSK VE 160L	$325	Италия
ARISTON OSK VG 160L	$325	Италия
ARISTON OSK VU 160L	$337	Италия
ARISTON OK DF 290L	$457	Италия
ARISTON OK DF 290NFL	$559	Италия
ARISTON K DF 290VNF	$559	Италия
ARISTON K-RF 310D	$578	Италия
ARISTON OK-RF 3300VL	$604	Италия
ARISTON OK-RF 3300VL-DX	$604	Италия
ARISTON OK-RF 3300NFL	$742	Италия
BOSCH KUR 1506	$568	Германия
BOSCH GUL 1205	$630	Германия
BOSCH KIF 2040	$676	Германия

Рис.10.4. Лист «Холодильники»

 

Оформим лист «Духовки» (рис.10.5.):

 

Наименование	Цена	Производитель
не заказывать	$0	нет
ARDO HCOO EB2(BR)	$196	Италия
ARDO FSOO EB(VVH)	$209	Италия
ARISTON FS 41(VVH)	$216	Италия
ARISTON FS 41(BK)	$221	Италия
ARISTON HD 87C(VVH)	$488	Италия
BOSCH HEN 3560	$761	Германия
BOSCH HBN 8550	$1 936	Германия
FAGOR 2H 436	$397	Испания
KAISER EBb28TtKDpRL	$740	Германия
KAISER EB28TeKDSL	$472	Германия
KAISER EB28TeKDSprL	$478	Германия
KAISER EHK285TeKDW	$498	Германия
SIEMENS HB 28064 EU	$643	Германия
SIEMENS HB 28054 EU	$723	Германия
SIEMENS HB 49E64 EU	$1 095	Германия

Рис.10.5. Лист «Духовки»

 

Оформим лист «Посудомоечные машины» (рис.10.6.):

 

Наименование	Цена	Производитель
не заказывать	$0	нет
ARDO ME 5661	$213	Италия
ARDO LF 9212 A1	$351	Италия
ARISTON LSV 61(IX)	$390	Италия
ARISTON LSV 61(BR)	$391	Италия
ARISTON LSV 62(VVH)	$396	Италия
ARISTON KLS 43 SFL(VVH)	$437	Италия
ARDO LS 9209 X	$437	Италия
ARISTON K-CD 12 TX(BR)	$458	Италия
ARISTON K-CD 12 TX(BX)	$550	Италия

Рис.10.6. Лист «Посудомоечные машины»

 

Оформим лист «Стиральные машины» (рис.10.7.):

 

Наименование	Цена	Производитель
не заказывать	$0	нет
ARISTON K-CD 12 TX(BK)	$550	Италия
SIEMENS VVK 61420	$1 424	Германия

Рис.10.7. Лист «Стиральные машины»

 

На листе «Смета»  в ячейке В3 определить текущую дату с помощью функции  =СЕГОДНЯ(),  а в ячейку D3 ввести текущий курс доллара, например  28,8 рублей.

 

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

1. Необходимо отобразить панель инструментов «Форма» (Вид – Панели инструментов – Форма) и установить соответствующие элементы управления следующим образом: 

2. На лист «Смета» в строке 5 напротив Холодильник следует поместить элемент управления  Поле со списком (рис.10.8.), он будет использован для выбора марки холодильника из справочника, расположенного на листе «Холодильники».

 

 

 

 

 

 

Рис.10.8. Образец расположения элемента управления «Поле со списком»

 

Для настройки элемента управления следует выбрать команду контекстного меню Формат объекта

В окне «Формат элемента управления» выбрать вкладку Элемент управления и установить следующие параметры (рис.10.9.):

 

Рис.10.9.  Образец заполнения окна «Формат элементов управления»

 

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

3. В ячейку В5 ввести формулу вывода стоимости выбранной техники:  =ИНДЕКС(Холодильники!В2:В15;Е5)

В ячейки В7, В9  и  В11 ввести аналогичные формулы.

4. В ячейку В13 ввести формулу вывода общей стоимости выбранной техники:   =СУММА(В5;В7;В9;В11)

5. Установить 2 элемента  Переключатель напротив Гарантии  и сделать обрамление элементом Рамка (рис.10.10.)

 

         

Рис.10.10. Образец оформления элементов управления

 

Для настройки элемента управления Переключатель следует выбрать команду контекстного меню Формат объекта

В окне «Формат элемента управления» выбрать вкладку Элемент управления и установить следующие параметры (рис.10.11.):

 

Рис.10.11. Образец заполнения окна «Формат элемента управления»

 

Измените тексты переключателей и рамки на соответствующие образцу.

6. В ячейку В15 ввести формулу вычисления стоимости гарантии (гарантия 6 месяцев – бесплатная, а гарантия 1 год – 10% от стоимости комплекта):   =ЕСЛИ(Е15=1;0;В13*10%)

7. Установить элемент  Флажок напротив Доставки и изменить текст надписи на «Нужна» (рис.10.12.):

 

 

Рис.10.12. Образец элемента управления «Флажок»

Для настройки элемента управления Флажок  следует выбрать команду контекстного меню Формат объекта

В окне «Формат элемента управления» выбрать вкладку Элемент управления и установить следующие параметры (рис.10.13.):

 

Рис.10.13. Образец заполнения окна «Формат элемента управления»

 

8.  В ячейку В17 ввести формулу для вычисления надбавки за доставку (например 10$):   =ЕСЛИ(Е17=ИСТИНА;10;0)

9. Установить элемент  Счетчик напротив Скидки (рис.10.14.):

 

Рис.10.14. Элемент управления «Счетчик»

 

Для настройки элемента управления Счетчик  следует выбрать команду контекстного меню Формат объекта

В окне «Формат элемента управления» выбрать вкладку Элемент управления и установить следующие параметры (рис.10.15.):

Рис.10.15. Образец заполнения окна «Формат элемента управления»

 

10. В ячейку В19 ввести формулу для вычисления размера скидки в процентах:   =Е19/100

11. В ячейку В21 ввести формулу для вычисления общей стоимости (в долларах) выбранного комплекта техники: 

   =СУММ(В13;В15;В17)*(100%-В19)

12. В ячейку В23 ввести формулу для вычисления суммы в рублях:    =В21*D3

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

Для этого необходимо выделить любую ячейку столбца и выполнить команду  [Формат]  à  [Столбец]  à  [Скрыть]

Скачано с www.znanio.ru