Лабораторная работа 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% от общей стоимости товара. Будем использовать элемент управления Переключатель 
на панели Формы. Переключатель применяется, когда нужно сделать выбор одного из нескольких вариантов (число вариантов не велико).
