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

Лабораторная работа 2

Тема. Обработка данных в электронных таблицах MS Excel 2007. Cоставление калькуляций стоимости заказов

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

Используя возможности электронных таблиц MSEXCEL можно создать документы, отвечающие на вопрос «Сколько это стоит?» почти мгновенно. Для более эффективной работы с данными в электронных таблицах MS Excel применяют элементы управления - списки, переключатели, счетчики, кнопки, календари и т.д., которые позволяют сформировать пользовательский интерфейс электронных таблиц. Экранные формы, интерактивные таблицы и диаграммы, автоматически меняющие свой вид и содержание с помощью Элементов управления (Формы), обеспечивают представление корпоративных данных в наиболее удобном для анализа виде.

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

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

Расчет будем проводить пошагово.

Шаг 1. Подготовка основного листа Калькуляции

На 1-ом шаге оформим лист MS Excel в соответствии с рис.2.1и назовем его Калькуляция.

Используя Мастер функции ( пиктограмма ) проставьте текущую дату в ячейку B2.

Введите текст заголовка, курс у.е. (ячейка A3).

Введите наименования комплектующих крыши в столбец А.

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

 

Рис.2.1. Расчетный лист Калькуляции

Шаг 2. Выбор Кровельного материала

Подготовка прайс-листа.

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

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

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

Установим на листе Калькуляция элемент управления Поле со списком.. Используя этот элемент, можно просматривать список имеющихся типов материала и выбирать нужный.

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

1. Если у Вас на панели отсутствует вкладка Разработчик, необходимо зайти в меню Пуск, кнопка , параметры Excel/ Основные/ показать вкладку “Разработчик” на ленте.

Рис.2.2. Установка вкладки “ Разработчик”

2. Выберите на листе Калькуляция команду Разработчик\ Вставить\Элементы управления формы. Появляется окно “ Элементы управления формы” ( рис. 2.3), из которого необходимо выбрать нужный элемент.

Рис.2.3. Выбор элемента управления формы.

3. Выберите значок “ Поле со списком” - , указатель мышки превращается в крестик прицела. Совместите крестик с левым верхним углом ячейки B6 и с нажатой левой кнопкой мыши «натяните» прямоугольник на ячейку B6.

4. Отпустите кнопку мыши. Элемент управления формы установится на листе Калькуляция.

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

· Щелкните правой кнопкой мыши на созданном элементе управления.

· Выберите команду Формат объекта\ Форматирование объекта\Элемент управления (рис.2.4)

Рис. 2.4.Окно «Форматирование объекта».

· В поле Форматировать список по диапазону нажмите кнопку и окно свернется как показано на рис. 2.5

Рис. 2.5. Окно «Форматирование объекта» в свернутом виде

· Откройте лист Кровельный материал и выделите ячейки столбца A, где расположены наименования типов Кровельного материала, включая ячейку А2 – «НЕТ».

· Нажав кнопку , окно диалога Форматирование объекта развернется до первоначального размера.

· В поле Связь с ячейкой нажмите кнопку и щелкните мышкой на ячейке F6.

Для чего же нужна эта ячейка F6? Через элемент управления Поле со списком мы не только будем просматривать список, но и выбирать нужный элемент этого списка. При этом номер выбранного элемента будет помещен в ячейку F6 листа Калькуляция. В дальнейшем это пригодится для установки цены выбранного материала.

· Разверните опять окно Форматирование объекта (кнопка . Значение поля Количество строк списка определяет количество строк, открывающегося Поля списка. Этот размер не может быть меньше размера списка-источника (лист Кровельный материал ). Если вы планируете в дальнейшем увеличить этот список, то введите здесь число «с запасом».

· После установки всех параметров в окне Форматирование объекта, нажмите кнопку ОК.

Таким образом, мы можем выбрать нужный Кровельный материал, рис. 2.6.

Рис. 2.6. Просмотр списка Кровельный материал

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

2.3. Установка цены выбранного типа Кровельного материала

Поместим цену выбранного типа Кровельного материала в ячейку С6 листа Калькуляция. Для этого необходимо выполнить следующую последовательность действий.

· Выделите ячейку C6 и выберите вкладку Формулы\Ссылки и массивы\ИНДЕКС (рис. 2.7)

Рис. 2.7. Выбор вкладки ИНДЕКС

· В появившемся окне Мастер функции выделите строку массив; номер строки; номер столбца. Нажмите ОК.

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

· Итак, в поле Массив введем диапазон ячеек В2:В20 с ценами листа Кровельный материал. (устанавливаем до В20 с запасом)

· В поле Номер_строки - укажем ячейку F6 листа Калькуляция (это номер выбранного вами Кровельного материала из списка, а значит и номер соответствующей цены типа Кровельного материала.

Рис. 2.8. Окно «Аргументы функции»

· Итак, параметры функции ИНДЕКС заданы, щелкните на кнопке ОК и в ячейке C6 появится цена выбранного вами типа Кровельного материала, а в ячейке F6 – порядковый номер этого типа Кровельного материала с листа Кровельный материал.

2.4.Установка формата «у.е.» в ячейке цены Кровельного материала

Щелкните правой кнопкой мыши на ячейку B3. В контекстном меню выберите команду Формат ячейки и в появившемся окне - вкладку Число.

В списке Числовые форматы выберите строку Все форматы.

В поле ввода Тип введите новую маску формата: # ##0,00”у.е.”. Завершите работу нажатием кнопки ОК.

Шаг 3. Выбор утеплителя и каркаса.

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

В соответствии с основным листом Excel Калькуляция будем выбирать утеплитель и каркас – с помощью элемента управления Поле со списком.

Предварительно подготовьте прайс-листы на имеющиеся в наличии типы минваты, утеплителя и каркаса. Назовите их соответственно Утеплитель и Каркас.

Шаг 4. Назначение Количества.

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

4.1. Подготовка нового столбца Количество.

Рис. 2.9. Столбец «Количество»

4.2. Установка элемента управления Счетчик

Откройте лист Калькуляция. Выберите на листе Калькуляция вкладку Разработчик\ Вставить\Элементы управления формы\Счетчик ) и установите Счетчик в ячейку D6 (примерно в четверть ширины ячейки).

Зададим параметры счетчика. Для этого щелкните правой кнопкой мышки на поле Счетчика (ячейка D6) и в контекстном меню выберите команду Формат объекта. Появится диалоговое окно Форматирование объекта введите значения полей с клавиатуры (параметры счетчика):

Рис. 2.10. Окно «Форматирование объекта»

Параметры задают интервал значений счетчика: начальное его значение и шаг изменения при каждом нажатии на стрелку.

Максимальное значение рекомендуется задавать больше числа строк в соответствующем листе материала (при необходимости количество типов материала можно будет увеличить без коррекции).

Нажмите кнопку ОК. В ячейке D6 листа Excel Калькуляция появится значение счетчика. Проверьте, как работает Счетчик: изменение номера счетчика будет происходить по нажатию той или иной стрелки элемента управления Счетчик.

Установите элемента управления Счетчик для утеплителя и каркаса.

Шаг 5. Расчет стоимости материала и общей стоимости.

Вычислим Cтоимость материала (рис. 2.11). Стоимость товара вычислим как цену умноженную на количество. Для этого введем в ячейку E6 = С6*D6.

Теперь вычислим общую стоимость заказа в ячейке E12 по формуле =СУММ(E6:E10).

Рис. 2.11. Столбец «Стоимость»

Мы научились работать с данными посредством 2-х элементов управления: Поле со списком и Счетчик. Используя Поле со списком можно взглянуть на весь список одним взглядом и сразу увидеть подходящий элемент; использование Счетчика удобно, когда список упорядочен (например, по цене) и можно мгновенно переместиться по списку в окрестности нужного объекта.

Шаг 6. Расчет стоимости гарантии

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