Кафедра строительной механики и вычислительной техники
Пермский Государственный Технический Университет
Строительный факультет
Кафедра строительной механики и вычислительной техники
СОВРЕМЕННЫЕ МЕТОДЫ СОСТАВЛЕНИЯ КАЛЬКУЛЯЦИЙ,
РАБОТА С БАЗАМИ ДАННЫХ
С ИСПОЛЬЗОВАНИЕМ ЭЛЕКТРОННЫХ ТАБЛИЦ ЕXCEL
Практикум
Для студентов строительных специальностей и для слушателей курсов повышения квалификации центра дополнительного образования работников строительной отрасли «СТРОЙНЭКСТ»
Пермь 2006г.
План УМД 2006\2007уч.г.
УДК 621.452.3
СОВРЕМЕННЫЕ МЕТОДЫ СОСТАВЛЕНИЯ КАЛЬКУЛЯЦИЙ,
РАБОТА С БАЗАМИ ДАННЫХ
С ИСПОЛЬЗОВАНИЕМ ЭЛЕКТРОННЫХ ТАБЛИЦ ЕXCEL
Практикум
Для студентов строительных специальностей и для слушателей курсов повышения квалификации центра дополнительного образования работников строительной отрасли «СТРОЙНЭКСТ»
Составитель: канд. техн. наук, доцент Т.Б. Пермякова
Приведены рекомендации по использованию электронных таблиц Excel для составления калькуляций и баз данных.
Рецензент канд. техн. наук Г.Г. Кашеварова
Издание стереотипное.
Утверждено на заседании кафедры СМиВТ
От 2006.
Лабораторная работа №1
Cоставление калькуляций заказов
С помощью электронных таблиц EXCEL.
Часто возникает необходимость быстро подсчитать стоимость какого-либо заказа. Это может быть, например, сборка компьютера заданной конфигурации, печать издания в типографии, смета на ремонт квартиры и т.п.
Используя возможности EXCEL можно создать документы, отвечающие на вопрос «Сколько это стоит?» почти мгновенно.
Пример. Составить калькуляцию для расчета цены на сборку персонального компьютера (ПК) заданной конфигурации. Конечный вид рабочего листа Excelпредставлен на рис.1. Столбец Е, о роли которого поговорим позже, может быть скрыт в дальнейшем.
Рис.1. Расчетный лист калькуляции на сборку компьютера заданной конфигурации
В качестве изменяемых параметров заказа (комплектующих ПК) будем рассматривать: тип процессора, объем оперативной памяти, тип винчестера, марку монитора, стоимость гарантии и доставки. В дальнейшем в приведенный перечень можно добавить печатающее устройство, мышку и другие комплектующие ПК.
Расчет будем проводить пошагово.
Шаг 1. Подготовка основного текста калькуляции
(лист Калькуляция)
На первом шаге оформим лист Excelв соответствии с рис.1и назовем его Калькуляция.
Введите текст заголовка, курс у.е. (ячейка D3).
Используя Мастер функции (пиктограмма ) проставьте текущую дату.
Введите наименования комплектующих ПК в столбец А.
Приведите в нужный порядок внешний вид основного рабочего листа Калькуляция: установите размеры, шрифты, тонирование, выравнивание ячеек и т.д.
Шаг 2. Выбор процессора
2.1.Подготовьте прайс-лист на имеющиеся в наличии типы процессоров. Прайс-лист оформляем в виде отдельного рабочего листа Excelс именем Процессоры, рис.2. При всех изменениях типов процессоров и их цен, при новых поступлениях будет удобно откорректировать только этот рабочий лист.
Рис.2. Рабочий листа Процессоры
2.2. Установка элемента управления Поле со списком
Установим на листе Калькуляция элемент управления Поле со списком 
. Используя этот элемент, можно просматривать список имеющихся процессоров и выбирать нужный.
Контрольное здание
Составить калькуляцию для расчета количества и стоимости материалов, используемых при ремонте жилого помещения. В таблице 1 приведены виды работ и используемые материалы:
Таблица 1.
| Вид работы | Используемый материал |
| 1.Заделка трещин и швов | штукатурная сухая смесь, шпаклевочная сухая смесь и т.д. |
| 2.Побелка | составы для побелки. |
| 3. Укладка плитки | плитка (вид, размеры), |
| 4. Оклейка обоями: | обои (моющиеся или не моющиеся, легкие или тяжелые), размеры рулонов (длинна, ширина) |
| 6.Установка сантехники | Кухня (Италия, Россия и т.д.) Ванна (Италия, Россия, Канада и т.д.) |
Один из вариантов рабочего листа Калькуляция представлен в приложении1.
В качестве изменяемых параметров заказа будем рассматривать виды предлагаемых материалов, их количество и стоимость, стоимость гарантии и доставки.
В приложениях 2,3 приведены еще две возможные учебные калькуляции. При желании можно составлять любые калькуляции, даже очень объемные.
 |
Приложение 1
Приложение 2
Приложение 3
Лабораторная работа №2
Создание авто фильтра
Наиболее частой задачей при работе с базами данных является поиск нужной информации. Эта задача в Microsoft Excel решается с помощью Авто фильтра. Автофильтр позволяет вывести на экран строки, содержащие только определенную информацию.
Для созданияАвтофильтра выполните следующие действия:
Ø выделите ячейки А8:Е8, содержащие имена полей;
Ø выберите команду: меню Данные\Фильтр\Автофильтр;
Ø 
в таблице, в каждой из выделенных ячеек, появятся кнопки автофильтра (рис.2).
Нажав на соответствующую кнопку автофильтра можно выбрать «нужное значение» в появившемся списке возможных значений (рис. 2).
Можно, например, произвести фильтрацию полюбому из полей: Менеджер, Комуи т.д.
Рис.2. Автофильтр
Для отмены фильтрации нажмите кнопку автофильтра и выберите в открывающемся списке строку Все.
Выборка из базы данных
Структура выборки
Работать с автофильтром легко и просто, но если требуется постоянно видеть отфильтрованный список, то удобнее сделать выборку из базы данных, которая будет автоматически «перевычисляться» при добавлении новой информации.
Для создания такой выборки будем использовать функцию БДСУММ для работы с базами данных (категория «Работа с базами данных»). Эта функция выполняет операции выборочно, т.е. только с данными, соответствующими заданным критериям.
Формат функции:
БДСУММ(<база данных>;<имя поля>;<критерий выбора>)
<база данных> -диапазон ячеек базы данных (в нашем случае – А8:Е100);
<имя поля> -определяетстолбец (поле)базы данных, над которым выполняется операция. Параметр <имя поля> задается как текст с названием столбца (поля) в двойных кавычках или как номер столбца. При этом надо помнить, что первый столбец диапазона базы данных имеет номер 1;
<критерий> -ссылка надиапазон ячеек, задающих критерий выбора строк (записей) в базе данных. Чтобы задать условия отбора для отдельного столбца, например для столбца «Менеджер», введите условия в ячейки, расположенные в смежных строках. Например, для следующего диапазона условий
| Менеджер |
| Иванов И.И. |
будут отобраны строки, содержащие Иванов И.И. в столбце Менеджер.
Пример 3.1. Подготовить информацию об объемах продаж, каждого менеджера за 1-ый квартал, чтобы иметь возможность сравнивать результаты их работы. Порядок выполнения
1. Выберите новый лист. Назовите его Менеджиры. Заполните блок ячеек А1:С4, как показано на рис.5.
2. В ячейку А5 введите формулу для вычисления суммы продаж Иванова И.И. Для этого активизируйте ячейку А5 и с помощью Мастера функций выберите функцию БДСУММ (из категории Работа с базой данных).В появившемся окне функции задайте параметры:
Ø в поле База_данных -введите диапазон ячеек $А8:$Е100 листа Продажи;
Ø в поле Имя поля - введите текст «Сумма,у.е.»;
Ø в поле Критерий – введите диапазон ячеек А3:А4 листа Менеджеры (это блок критериев);
Ø нажмите кнопку ОК.
Таким образом, в ячейке А5 записана формула:
А5= БДСУММ(Продажи!$A$8:$E$100;”Сумма,у.е.”;$A3:A4)
Если вы все проделали правильно, то в ячейке А5 запишется результат всех продаж менеджера Иванова И.И. Скопируйте формулу из ячейки А5 в ячейки В5:С5. Созданная выборка имеет вид, как показано на рис.5.
Теперь добавьте новые записи в базу данных на листе Продажи и посмотрите, как будет меняться выборка на листе Менеджеры, как будут меняться общие и промежуточные итоги на листе Продажи.
Применяя описанный выше способ, вы можете создавать любые выборки из базы данных.
Контрольное задание 1. Добавить новые записи в базу данных, ввести изменения о продажах на листе Продажии проанализировать изменения на листе Менеджеры.
Сводные таблицы
Сводная таблица – это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных
Создание сводных таблиц
Для построения сводных таблиц используетсяМастер сводных таблиц, пиктограмма 
,находящаяся напанели инструментов Сводные таблицы (рис.6).
Для установки на рабочем листе панели инструментов Сводные таблицывыберите команду: Меню Вид \Панели инструментов \активизируйте строку Сводные таблицы.
Рис.6.Панельинструментов Сводные таблицы
Другой способ установкиМастера сводных таблиц - меню Данные.
Пример 4.1. Составить сводную таблицу, показывающую суммарный приход от продаж менеджеров.
Порядок построения сводной таблицы:
Активизируйте любую ячейку базы данных листа Продажи(можно всю базу $A$8:$E$15). Щелкните на пиктограмме 
напанели инструментов Сводные таблицы (рис.6). После чего будет запущен Мастер сводных таблиц,состоящий из 3-х шагов.
Шаг 1.Ответьте на первый вопрос Мастера сводных таблиц - выберите тип данных для отчета. Щелкните на кнопке Далее.
Шаг 2.В поле Диапазон уже находится адрес вашей базы данных $A$8:$E$15 (рис.7). Если вы не хотите изменений, щелкните на кнопке Далее.
Рис.7. Установка адреса базы данных (шаг 2)
Шаг 3. Укажите место, куда следует поместить сводную таблицу. Как правило, сводную таблицу помещают на новый рабочий лист. Сделайте это. Нажав кнопку Параметры можно отрегулировать структуру и внешний вид сводной таблицы.
Используя кнопку Макет, определите структуру создаваемой таблицы, (рис.8):
Ø Перетащите мышью кнопку Кому в область строк.
Ø Перетащите мышью кнопку Менеджер в область столбцов.
Ø Перетащите мышью кнопку Сумма,у.е. в область данных.
Ø Нажмите кнопку ОК.
Рис. 8. Структура сводной таблицы
Итог ваших действий показан на рис.9 и будет размещен на новом рабочем листе, назовите этот лист Св-табл-1. Установите формат «у.е».
Рис.9. Сводная таблица на рабочем листе Св-табл-1
|
\ операция сумма Изменения в сводной таблице
Добавление нового элемента
В сводную таблицу всегда можно вставить или удалить какое либо поле. Добавим поле Дата в сводную таблицу на рис.10. Для этого выполните следующие дейстаия:
Ø выделите произвольную ячейку сводной таблицы;
Ø 
нажмите кнопку панели Сводные таблицы;
Ø щелкните по кнопке Макет;
Ø перетащите кнопку Дата в поле строки и продолжите диалог.
Excel добавит столбец Дата, как показано на рис.11.
Рис.11.Сводная таблица после добавления столбца Дата и форматирования
Изменение порядка столбцов
Для изменения порядка столбцов выделите любую ячейку вашей сводной таблицы и щелкните на кнопке Мастер сводных таблицна панели инструментов Сводные таблицы для открытия 3-го диалогового окна мастера сводных таблиц. Перетащите кнопки с именами полей так, как вы считаете нужным.
Редактирование диаграмм
Диаграмма, созданная Мастером, нуждается в доработке. Каждый элемент диаграммы можно изменить. Для этого существует несколько способов.
1. Щелкните на нужном элементе диаграммы левой кнопкой мышки. Вокруг элемента появится масштабная рамка с маркерами. Сейчас можно сделать некоторые изменения уже известными вам способами: изменить размеры и положение отдельного элемента диаграммы; откорректировать заголовок диаграммы и т.д.
Рис.12. Гистограмма сводной таблицы рабочего листа Св-табл-1
2. Если надо изменить вид или параметры диаграммы, диапазон данных диаграммы, активизируйте диаграмму, раскройте пункт меню Диаграмма и выберите соответствующую строку и вы окажетесь в одном из окон Мастера диаграмм.
3. Щелкните на каком либо элементе диаграммы правой кнопкой мыши и выберите в появившемся контекстном меню строку Формат <имя элемента>; в появившемся окне введите соответствующие параметры.
Литература.
1. Комягин В.Б. Компьютер для менеджера. Быстрый старт. М:.1998. Изд. Триумф. –383 с.
2. Комягин В.Б. Компьютер для менеджера 2. М:.1998. Изд. Триумф.–411с.
3. Попов А. EXCEL. Практическое руководство. М:. 2000. ДЕСС КОМ.–301с.
4. Вейсскопф Д. Excel 2000. Базовый курс. М:.2000. Изд. ЭНТРОП.-393 с.
Пермский Государственный Технический Университет
Строительный факультет
Кафедра строительной механики и вычислительной техники