Определение итоговых столбцов

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

Общие сведения.

Лабораторная работа №10

Создание и применение сводных таблиц

Цели работы:

Ознакомиться с приёмами анализа данных с помощью сводных таблиц.

Задачи работы:

Научиться проектировать и создавать сводные таблицы.

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

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

Внимание! Режим Сводная таблица может быть применен и к таблице, и к запросу, и к форме. Но наиболее применим именно к запросу, который обычно объединяет данные из нескольких таблиц и эти данные нужно иметь возможность представить в удобном для анализа виде.

1.1 Создайте запроспо таблицам «Заказы», «Заказано», «Товары», «Клиенты», «Доставка».Из таблицы «Заказы» вставьте поле «Клиент», из таблицы «Клиенты» поля «Адрес», «Город», «ОбращатьсяК».

1.2. В верхнюю ячейку следующего (пустого) поля, используя Построитель выражений, вставьте выражение [Имя] & " " & [Фамилия] (из таблицы «Сотрудники»), нажав Shift+F2, в Области ввода вместо Выражение 1: введите Продавец:

Внимание! Если в выражении используются поля из какой-либо таблицы, то эта таблица обязательно должна присутствовать в бланке запроса.

1.3. Из таблицы «Заказы» добавьте поля «КодЗаказа», «ДатаНазначения», «ДатаИсполнения». Из таблицы «Доставка» - «Название». Из таблицы «Товары» поля «Марка» и «Цена». Из таблицы «Заказано» поля «Количество», «Скидка».

1.4. В следующее (пустое) поле введите выражение, вычисляющее Отпускную цену: CCur([Заказано]![Количество]*[Товары]![Цена]*(1-[Заказано]!
[Скидка])/100)*100. Нажав клавиши Shift+F2 в окне Область ввода вместо Выражение 1: введите ОтпускнаяЦена.

1.5. Из таблицы «Заказы» добавьте поле «Стоимость доставки».

1.6. Запустите запрос и сохраните его под именем «Счета». Если в Режиме таблицы в запросе появляются «лишние» поля, то в режиме Конструктора в Свойствах поля (правой кнопкой мыши по пустому полю) в строке «Вывод всех полей»нужновыбрать Нет.

1.7. Создание сводной таблицы. Откройте запрос «Счета» в Режиме таблицы. Щелкнув правой кнопкой по заголовку запроса, выберите из списка элемент Сводная таблица. На экране появится макет будущей таблицы, который состоит из четырех областей. Каждая область имеет надпись, показывающая, какие данные должны в ней помещаться. Если Список полей не виден на экране, нажмите кнопку Список полей на вкладке Конструктор в разделе Показать или скрыть (рис. 18).

Рис.18. Макет Сводной таблицы и список полей.

· Найдите в списке поле «Город» и переместите его в верхнюю область таблицы, которая называется областью фильтра. После этого вы сможете фильтровать данные в таблице по городам клиентов. В сводной таблице появляется еще одно измерение — глубина — благодаря которому, можно просматривать и анализировать данные по городам. Каждый срез таблицы будет отображать данные по выбранному городу. Добавьте в эту же область поля «Продавец», «КодЗаказа», «Скидки», «Дата исполнения по месяцам»;

· Выделите в списке полей поле "Марка" и перетащите его в крайнюю левую область. Эта область называется областью строк и будет содержать список всех товаров, на которые были выписаны счета.

· Чтобы определить столбцы таблицы, переместите поле «Дата назначения по месяцам» в область столбцов, которая имеет надпись «Перетащите сюда поля столбцов». В таблице появятся три столбца, которые будут содержать данные по годам — это самый верхний уровень группировки, — и один итоговой столбец «Общие итоги».

· Для того, чтобы определить, что будет отображаться в самой таблице, переместите в самую большую область таблицы — поле «Отпускная цена». Таблица приобретет вид, представленный на рисунке (рис. 19). По умолчанию в ней отображаются все данные по всем товарам и городам.

Рис. 19. Сводная таблица

·

Щелкните на заголовке столбца «Отпускная цена» и выберите на вкладке Конструктор в разделе Сервис Автовычисления/Сумма. Появится дополнительная строка для каждого товара, содержащая итоговое значение по каждому товару за год, а в итоговом столбце будет отображена итоговая цифра продаж каждого товара по всем годам (рис. 20).

Рис. 20. Сводная таблица с итоговыми значениями.

· Теперь можно скрыть детальные данные, оставив только итоговые. Нажмите кнопку Без подробностей на панели инструментов в разделе Показать или скрыть.

1.9. Фильтрация данных сводной таблицы. Сейчас в таблице отображаются данные о продажах по всем товарам и всем городам. С этой таблицей легко манипулировать, отображая в ней разные данные. Например, она может быть очень легко изменена, когда требуется посмотреть выборочно данные по отдельному городу, заказу или скидке на товары, т. е. по тем полям, которые отображаются в области фильтра, области строк и области столбцов. Щелкнув по кнопке справа от названия любого из этих полей, снимите флажок Все и выберите то поле, по которому нужен фильтр, например, Тюмень, и нажмите кнопку ОК. Теперь ваша таблица отображает данные только по Тюмени.

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

Создать диаграмму на основе сводной таблицы очень просто. Переключитесь в режим Сводная диаграмма, и на экране появится сводная диаграмма. Создайте сводную диаграмму по запросу «Счета». В область Перетащите сюда поля данных поместите любое поле (по которому нужно просмотреть данные), например, «ОтпускнаяЦена». Посмотрите, что изменилось в сводной таблице.

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