Лабораторная работа №2. Консолидация баз данных и сводные таблицы в Excel.

Этот режим позволяет отображать в форме только те записи, которые удовлетворяют заданному критерию.

Переход в режим поиска записей по критерию осуществляется выбором команд: ДАННЫЕ -> Форма -> Критерии (DATA-Form…-Criteria). Затем следует задать в полях условия поиска. При этом можно использовать следующие символы-заменители:

? – заменяет один символ,

* - заменяет любое количество символов,

< > <= >= = используются для записи условий.

После задания критерия нажатие кнопок Назад и Далее будет приводить к переходам только на те записи, которые удовлетворяют заданным условиям.

Для нашего примера таблицы (рис.1) при задании критериев как на рис.4 отобрана будет только 1-я запись (рис.3).

Рис.4

Для упорядочивания данных служит операция сортировки: ДАННЫЕ -> Сортировка… (DATA-Sort…)

Перед вызовом команды надо поставить курсор в одно из полей таблицы. После выбора команды ДАННЫЕ -> Сортировка (DATA-Sort…)откроется диалоговое окно рис.5.

Рис.5

Как видно, в этом окне можно задать три критерия сортировки. В полях Сортировать по (Sort by), Затем по (Then by), В последнюю очередь по (Then by) задаются названия сортируемых полей. Справа размещены переключатели направления сортировки: по возрастанию (Ascending) – от первой буквы алфавита к последней, от меньшего числа к большему, от более ранней даты к более поздней и по убыванию (Descending)– в обратном направлении. Переключатели Идентифицировать поля по подписям и Идентифицировать поля по обозначениям позволяют соответственно включать и не включать верхнюю строку таблицы в процессе сортировки. Кнопка Параметры… открывает диалоговое окно, позволяющее установить последовательность нестандартной сортировки.

Для базы, представленной на рис.1, зададим критерии сортировки (рис.6).

Рис.6

Сортировка будет осуществляться по полю Вид молока, затем Цены, и в последнюю очередь по Весу заказанных продукций. Результат сортировки представлен на рис.7.

Рис.7

Для отображения записей базы, удовлетворяющих задаваемым условиям, и перевода остальных в скрытое состояние, служит фильтрация данных: ДАННЫЕ->Фильтр->Автофильтр (Data-Filter-AutoFilter) или ДАННЫЕ->Фильтр->Расширенный фильтр… (Data-Filter-Advanced Filter…)

Рассмотрим сначала работу с Автофильтром. При выборе команды ДАННЫЕ->Фильтр->Автофильтр (Data-Filter-AutoFilter) в строке названий полей размещаются кнопки раскрывающихся списков (рис.8). С помощью этих кнопок в выпадающих окнах можно задавать критерии отбора строк (фильтрации). При задании критериев в нескольких полях они объединяются по принципу логического умножения.

Рис.8

Кроме выбора одного критерия, в выпадающем окне Автофильтра можно выбрать пункт Условие (Custom Filter…). В этом случае откроется окно Пользовательского фильтра, в котором можно задать более сложный критерий выбора для текущего поля. На рис.9 для поля Цена задан диапазон цен больше 80 и по 240 рублей.

Рис.9

Результат выполнения сложного условия фильтрации представлен на рис.10.

Рис.10

Кнопки полей, для которых выполнена фильтрация, выделяются синим цветом. Чтобы отменить Автофильтр, снимите флажок Автофильтр (Data-Filter-AutoFilter) (повторное выполнение команды ДАННЫЕ->Фильтр->Автофильтр).

Теперь рассмотрим использование расширенного фильтра (ДАННЫЕ->Фильтр->Расширенный фильтр…) (Data-Filter-Advanced Filter…)для более сложных критериев фильтрации.

Возможности расширенной фильтрации рассмотрим на примере решения следующей задачи.

На основе имеющейся базы (рис.1) нам надо сформировать таблицу из полей с записями, соответствующими условиям:

1. заказ оформлен в период 04.01.2000-30.05.2000 на продукцию Предприятие "Новые услуги" при цене молока более 50 рублей,

2. а также (или) заказ оформлен в период 02.02.2000-14.02.2000 на продукцию ОАО "Маршак", а также при цене молока менее 400 рублей,

3. а также (или) все заказы, оформленные в период 01.05.2000-30.05.2000,

4. а также (или) все заказы на продукцию ИП "Нежные услуги".

Для решения задачи необходимо выполнить следующие действия:

Создать область критерия. Для этого необходимо заполнить ячейки на Лист2, как показано на рис.11.

Рис.11

Здесь столбцы соответствуют критериям, отобранным для фильтрации. Каждая строка описывает соответствующее условие из четырех условий фильтрации, сформулированных выше. Условия, записываемые в одной строке, объединяются условием И. Условия, записанные в разных строках, объединяются условием ИЛИ.

Такую таблицу удобно расположить на отдельном листе. Мы так и сделаем. Создадим такую таблицу на Листе 2.

Далее на том же листе, где находится база (основная таблица рис. 1), справа или ниже ее заполняем ячейки именами полей таблицы, которая будет создана после фильтрации (№ п\п, Дата, Заказчик, Адрес заказчика, Ф.И.О. заказчика, Название, Производитель, Вид, Цена (руб.), Вес (кг), Дата оплаты, Дата отправки/получения). Мы сделаем это в области, расположенной справа от базы (рис. 12).

Рис.12

Устанавливаем курсор на любую ячейку базы данных и вызываем диалоговое окно Расширенный фильтр (ДАННЫЕ->Фильтр->Расширенный фильтр…) (Data-Filter-Advanced Filter…) (Рис. 13).

Рис.13

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

Нажимаем OK и получаем результат, представленный на рис.14.

Рис.14

Теперь перейдем к рассмотрению процесса получения Итогов по данным базы. Эту процедуру рассмотрим на примере решения конкретной задачи. Но прежде чем сформулировать эту задачу, внесем некоторые изменения в базу данных. После поля Вес, обозначающее количества молока в заказе, добавим поле Сумма заказа, обозначающее сумму заказа. В ячейки записей на пересечении с полем Сумма заказа вставим формулу произведения содержимого ячеек полей Цена и Вес. Для этого, предварительно выделив ячейку первой записи поля Сумма заказа, в поле ввода формулы введем формулу =I3*J3; в ячейку поля Сумма заказа второй записи введем формулу =I4*J4 и т.д. Для ускорения этого процесса после ввода формулы в ячейку первой записи можно выделить эту ячейку однократным нажатием и отпусканием левой кнопки мыши, затем поставить курсор мыши на правый нижний угол ячейки и нажав левую кнопку мыши тянуть ее вниз до конца таблицы, т.е тиражировать. Произойдет копирование формулы во все ячейки. В результате будем иметь таблицу как на рис.15, которая отличается от базовой таблицы, представленной на рис.1.

Рис.15

Теперь сформулируем задачу получения итогов по базе данных. Требуется получить результаты продаж молока в натуральном и денежном эквиваленте по каждому виду отдельно и вместе. Для решения данной задачи вначале отсортируем порядок записей по полю Производитель (ДАННЫЕ -> Сортировка…) (Data-Sort…). Результат представлен на рис.16.

Рис.16

Теперь вызываем команду Итоги… (ДАННЫЕ ->Итоги…) (Data-Subtotals…). Результатом станет появление диалогового окна (рис.17). Задаем значение полей как показано на рисунке. В поле При каждом изменении в: (At each change in) указываем имя поля по группам позиций которого требуется получение итогов. В поле Операция (Use function) указываем операцию над значениями ячеек. В нашем случае это сумма как для веса, так и для величин сумм заказов. В поле добавить итоги по: указываем те поля, для которых собственно требуется получение итогов. Это поле Вес и поле Сумма заказа (рис.17). Нажимаем OK и получаем результат, который представлен на рис.18.

Рис.17

Рис.18

Рис.18а

Слева появилась карта структуры базы данных. Кнопки со знаком минус (рис.18а) позволяют свернуть соответствующую ей группу данных, от которой остается только одна итоговая строка, а на кнопке появляется знак плюс (рис.19).

Рис.19

Столбец Вид (рис.19) указывает, что итоги получены по видам продукции (молока), и группировка данных произведены по видам продукции.

Контрольные вопросы:

1. Что такое сортировка?

2. Дайте определение фильтрации?

3. В чем состоит отличие фильтрации от сортировки?

4. Какие виды фильтрации вам известны?

5. Характеризуйте Автофильтр?

6. Для чего применяется Пользовательский фильтр?

7. Характеризуйте Расширенный фильтр?

8. Чем отличаются друг от друга Автофильтр, Пользовательский фильтр и Расширенный фильтр?

9. Как удалить Автофильтр?

10. Укажите на информационные потоки в ваших таблицах?

11. Какие символы можно использовать при поиске?

Лабораторная работа №2. Консолидация баз данных и сводные таблицы в Excel.

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

Начало работы:Открыть программу Word и создать файл «Отчет по лаборатории №2-Е»; Открыть программу Excel и создать файл «Лабораторная работа №2-Е».

Открыть Интернет для поиска значений команд по справочным системам.