Кафедра строительной механики и вычислительной техники

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

Контрольное здание

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

Таблица 1.

Один из вариантов рабочего листа Калькуляция представлен в приложении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 с.

Пермский Государственный Технический Университет

Строительный факультет

Кафедра строительной механики и вычислительной техники