Оценка «3» ставится при выполнении 6 заданий.

 
Практическая работа Создание и технология работы с базами данных в MS Excel. Создание сводных таблиц

Время выполнения – 2 часа

Цель занятия: Изучить возможности программы для работы с большими таблицами,  связывание листов электронной книги, структурирование таблицы.

Вопросы для подготовки к работе:

1. Алгоритм работы с мастером функций.

2. Определение типа данных в ячейках.

3. Алгоритм создания сводных таблиц.

4. Редактирование и форматирование данных.

Литература: Теоретическая часть методических указаний,   [1.стр. с.262-271]

Порядок работы:

Задание 1.Создать базу данных по продажам, произвести расчеты в таблице, создать на основе исходной таблицы сводные таблицы

Когда данные становятся спискам?

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

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

Таким образом, база данных является множеством записей, состоящих из полей данных. В качестве примера построим базу, содержащую данные о продажи фирмы «ПермКом», осуществленных разными менеджерами за первый квартал, затем рассмотрим, как извлекать из неё нужную информацию и представлять графически.

Задание 1.Создание базы данных. Вычисление суммы продаж

1. В новой книге создайте таблицу, как показано на рис.31.

2. Дайте название листу Продажи. В ячейки А1, А2 введите текст, как указано на рисунке, отформатируйте его. Данные D2:E2 и данные столбца Сумма, руб. являются расчетными, заполним их позже.

3.  Значения курса доллара наберите в отдельных ячейках C4:C6.

4. Создайте структуру базы, или проще говоря, введите имена полей (столбцов). Перед строкой с названием следует оставлять пустую строку (7 строка). Это необходимо для последующих операций с базой.

5. Заполните базу данных, задайте форматы ячеек. Имейте в виду, что при вводе повторяющихся данных Excel предлагает подстановку ранее введенных данных по первым символам.

 

Рисунок 31

После заполнения вей таблицы, заполните столбец Сумма, руб. с учетом приведенного курса доллара. Для этого в ячейку E9 введите формулу:

 

и скопируйте ее вниз до конца таблицы. Установите «рублевый формат».

В ячейке D2вычислите общую сумму продаж в долларах:

- Выделите ячейку D2;

- Нажмите кнопку Автосумма на панели инструментов;

- Курсором мыши укажите данные столбца D;

- В строке формул сделайте поправку, указав максимально возможный диапазон ячеек D4:D16000; это делается с учетом, что вы еще будете вносить записи в базу, общее количество которых заранее не известно;

- Установите «долларовый» формат в ячейке D2.

- Аналогичным образом в ячейке Е2 вычислите общую сумму продаж в рублях и установите «рублевый» формат.

Задание 2. Выполните фильтрацию данных по указанным ниже параметрам. Создайте промежуточные итоги.

Наиболее частой задачей при работе с базами данных является поиск нужной информации. Эта задача решается с помощью автофильтра, который позволяет вывести на экран строки, содержащие только определенные значения (рис.32):

Выделите диапазон ячеек А8:Е8, содержащих заголовки полей (имена полей);

-Выберите команду: меню Данные\Фильтр\Автофильтр;

-

Рисунок 32

В таблице, в каждой из выделенных ячеек, появится кнопка автофильтра.

-Нажав на соответствующую кнопку автофильтра можно выбрать «нужное» в появившемся списке возможных значений. Можно, например, произвести фильтрацию по любому из полей: Менеджер, Кому и т.д. Для отмены фильтрации нажмите кнопку автофильтрации   и выберите в открывающемся списке Все.

1. Отфильтруйте данные о продажах менеджера Иванова. Результаты сохраните на следующем листе.

2. Отфильтруйте данные о продажах, размер которых превысил 100 долларов. Результаты сохраните на следующем листе.

Для работы с данными, содержащимися в отфильтрованных списках, используется функция ПРОМЕЖУТОЧНЫЕ ИТОГИ, которая игнорирует все скрытые строки и столбцы.

Формат функции: ПРОМЕЖУТОЧНЫЕ ИТОГИ (<число>;<диапазон>)

<число> - определяет тип вычислений (1 –усреднение; 4 и 5 –определение минимума и максимума; 9 –суммирование 0;

<диапазон> - определяет диапазон ячеек, над которыми будут выполнены вычисления.

Промежуточные итоги покажите в ячейках G8:H8 (рис.33). Для этого:

- В ячейку F8 введите текст Итоги;

- В ячейку G8, используя Мастер функций, введите функцию ПРОМЕЖУТОЧНЫЕ ИТОГИ из категории Математические;

- В появившемся окне функции сделайте следующие установки;

- В поле Номер функции введите 9 (суммирование)

 

Рисунок 33

 

- В поле Ссылка 1 введите диапазон ячеек D9:D16000, используя для этого однострочное поле ввода окна функции.

- По завершении ввода функции установите «долларовый «формат.

- Если вы все сделали правильно, в ячейке G8 будет записана формула:

 =ПРОМЕЖУТОЧНЫЕ ИТОГИ (9;D9:D16000)

Аналогично в ячейке H8 получим данные в «рублевом» эквиваленте.

Пока фильтрация не выполнена, результат в ячейках G8, H8 равны общей сумме продаж в ячейках D2:E2 соответственно. (рис. 3)

Предположим, что нам нужно определить общую сумму продаж, выполненных менеджером Ивановым И.И. Произведя фильтрацию в поле Менеджер и указав Иванов И.И., в базе отобразятся только две записи. Остальные будут скрыты (рис.34)

Рисунок 34

 

Таким образом функция ПРОМЕЖУТОЧНЫЕ ИТОГИ суммирует только видимые на экране записи, не включая в итоговый результат записи, скрытые фильтрацией.

1. Аналогично вы можете определить когда, кто и на какую сумму осуществил продажи и какой фирме. Для этого сделайте автофильтрацию по полю Кому, или Сумма и выберите интересующую вас информацию. По окончании работы на листе Продажи снимите Фильтр.

Задание 3 Выборка из базы данных. Подготовить информацию об объемах продаж, каждого менеджера за первый квартал, чтобы иметь возможность сравнить результаты их работы.

Работать с автофильтром легко и просто, но если требуется постоянно видеть отфильтрованный список, то удобнее сделать выборку из базы данных, которая будет автоматически «перевычисляться» при добавлении новой информации.

Для создания такой выборки будем использовать функцию БДСУММ для работы с базами данных. Эта функция выполняет операции выборочно, т.е. только с данными, соответствующими заданным критериям.

Формат функции:

БДСУММ (<диапазон>;<столбец>;<критерий>).

<диапазон> - диапазон базы данных;

<столбец> - номер столбца диапазона данных (поле), над которыми выполняется операция; при этом надо помнить, что первый столбец диапазона имеет номер 1.

<критерий> -диапазон, содержащий критерий выбора строк (записей) в базе данных, на которые распространяется действие функции.

1. Выберите для этого новый лист. Назовите его Менеджеры. Заполните диапазон ячеек A1:C4, как показано на рис.35.

2. Далее в ячейку А5 введите формулу для вычисления суммы продаж Иванова И.И. Для этого активизируйте ячейку А5 и с помощью мастера функций выберите БДСУММ из категории Работа с базой данных.

3. В появившемся окне функции задайте параметры:

- В поле База данных введите диапазон ячеек $C8:$D16000 листа Продажи;

- В поле Поле введите номер столбца с листа продаж, содержащий Сумма, $;

- В поле Критерий – диапазон ячеек А3:А4 листа Менеджеры (это блок критериев);

Если вы все делали правильно, то в ячейке А5 запишется результат всех продаж менеджера Иванова И.И.

4. Скопируйте формулу в ячейки D5:C5.

Добавьте новые записи в базу данных на листе Продажи и посмотрите, как будут меняться выборка на листе Менеджеры, как будут меняться общие и промежуточные итоги на листе

Рисунок 35

Задание 4. Создание сводной таблицы.

Сводная таблица - это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Для построения сводных таблиц используется команда меню Вставка/Сводные таблицы, при этом в меню добавляются команды Параметры и конструктор на панели Работа со сводными таблицами.

1. Активизируйте любую ячейку рабочего лист Продажи с исходными данными.

2. Выполните команду: Вставка\Сводная таблица, после чего будет запущен Мастер сводных таблиц (рис. 36).

3. Ответьте на вопрос Мастера сводных таблиц – выберите вид создаваемого отчета данных – сводная таблица и источник – диапазон значений.

4. Поместите таблицу на новый лист и определите структуру создаваемой таблицы с помощью области задач – Список полей сводной таблицы:

- Отметьте галочками нужные поля в команде Выберите поля для добавления в отчет: Кому, Менеджер.

- Перетащите мышью кнопку Кому в область строк.

- Перетащите мышью кнопку Менеджер в область столбцов.

- Перетащите мышью кнопку Сумма руб. в область данных.

Рисунок 36

Назовите рабочий лист Св-табл-1. Нажав кнопку Параметры можно отрегулировать структуру и внешний вид сводной таблицы. Итог ваших действий показан на рис.37

 

Рисунок 37

 

Изменение сводной таблицы

Изменение данных в исходной таблице не влечет автоматической реакции сводной таблицы. Однако хотелось бы, чтобы вводимые изменения в исходную таблицу Продажи отражались в сводной таблице.

Рассмотрим два варианта выполнения перерасчета:

1) изменение содержимого ячейки, 2) добавление записи в исходной таблице.

Первый вариант. После изменения содержимого одной или нескольких ячеек исходного листа Продажи вызовите рабочий лист Св-табл-1, где находится ваша сводная таблица, и щелкните на кнопке   Обновить данные панели инструментов Работа со Сводными таблицами/ Параметры

Второй вариант. После ввода новых строк в исходную таблицу Продажи выполните следующие действия:

- Вызовите лист сводной таблицы;

- Щелкните на кнопке Источник данных панели инструментов Работа со Сводными таблицами/ Параметры

- Измените диапазон данных в поле диапазон.

После этих операций все изменения, сделанные в исходном листе, будут отражены на листе сводной таблицы.