Тема: Cоставление калькуляций стоимости заказов с помощью электронных таблиц MS Excel 2007

Лабораторная работа 3.

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

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

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

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

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

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

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

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

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

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

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

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

Шаг 2. Выбор Бикроста

Бикрост - материал используемый для покрытия крыш, измеряемый в рулонах и имеюший одинаковую длину в разных типах. Каждый тип определяет свое качество материала.

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

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

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

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

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

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

2. Выведите на листе Калькуляция панель Элементов управления Формы с помощью команды: в настройке панели быстрого доступа добавьте Элементы управления, рис. 2.3.

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

Нажмите кнопку Элементы управления формы. Выберите , указатель мышки превращается в крестик прицела. Совместите крестик с левым верхним углом ячейки С5 и с нажатой левой кнопкой мыши «натяните» прямоугольник на ячейку С5 (рис. 2.3).

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

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

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

· В контекстном меню выберите команду Формат объекта, в появившемся диалоговом окне Формат элемента управлениявыберите вкладку Элемент управления.

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

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

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

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

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

Рис. 2.5. Окно «Формат элемента управления» в свернутом виде

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

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

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

Рис. 2.6. Просмотр списка БиКрост

2.3. Установка цены выбранного БиКрост

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

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

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

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

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

· Итак, в поле Массив введем диапазон ячеек В2:В11 с ценами листа Бикрост

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

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

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