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

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

Фильтрация

Получившаяся таблица – двумерная, т.к. показывает данные в двух разрезах: ФИО сотрудников и время (годы). Можно добавить еще одно измерение, например, посмотреть продажу в разрезе получателей заказов.

Для того чтобы добавить поле Получатель в окно Список полей, вернитесь в окно Конструктора запросов (нажмите кнопку Вид).

В окне бланка конструктора запросов из таблицы Заказы добавьте поле НазваниеПолучателя и вернитесь в режим Сводная Таблица.

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

Появится окно со списком всех получателей, которым были отгружены товары. Слева в каждой строке стоит флажок.

Сбросьте флажок Все в первой строке, после чего будут сброшены все флажки. Установите флажок только у одного получателя, например Blondel pere et fils, и нажмите кнопку . Данные в сводной таблице изменятся, а под наименованием фильтра появилось имя выбранного получателя.

 

Рис. 5.19. Установка фильтра в сводной таблице

 

Окно со списком получателей позволяет сделать множественный выбор, то есть выбрать сразу несколько получателей. Так, чтобы добавить еще одного получателя, например Bon app', снова щелкните по стрелке справа от названия фильтра Получатели и в появившемся окне со списком всех получателей установите флажок у Bon app' и нажмите .

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

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

Данные сводной таблице сортируются как и в Excel. Сейчас они отсортированы по сотрудникам, но если выделить область данных таблицы (щелкнув по заголовку Сумма “Сумма”), а затем щелкнуть кнопку или , таблица будет отсортирована по объемам продаж.

Другой способ фильтрации данных в таблице – по первому столбцу. Сначала удалите все установленные фильтры, нажав на панели инструментов кнопку Автофильтр. Затем выделите столбец Сотрудники, щелкнув на его заголовке. На панели инструментов нажмите кнопку Показать верхние и нижние элементы. Затем выберите в списке элемент Показать только верхний элемент и далее 5. В результате будут отобраны только пять сотрудников с наибольшим значением в столбце общие итоги. В заголовке столбца ФИО появился значок фильтра.

 

Рис. 5.20. Установка фильтра по максимальному объему продаж

 

АВС-анализ

Покажем список товаров, доля продаж которых составляет 80 % от общего объема. Сначала удалите все установленные фильтры, нажав на панели инструментов кнопку Автофильтр.

Для того чтобы добавить поле Марка в окно Список полей, вернитесь в окно Конструктора запросов (нажмите кнопку Вид).

В окне бланка конструктора запросов нажмите на панели инструментов кнопку и в появившемся диалогом окне Добавление таблицы выберите таблицу Товары и нажмите кнопку . Добавьте в бланк запроса из таблицы Товары поле Марка. Так как поле Цена присутствует в двух таблицах, следует указать, что значение поля Цена в вычисляемом поле будет браться из таблицы Заказано

Сумма: [Заказано]![Цена]*[Количество]

Теперь вернитесь в режим Сводная Таблица.

Удалите столбец Сотрудники: щелкните на его заголовке и нажмите клавишу Delete. Найдите в окне Список полей сводной таблицы поле Марка и перетащите его мышью в область заголовков строк – вертикальную область у левого края формы.

На панели инструментов на-жмите кнопку Показать верх-ние и нижние элементы. Затем выберите в списке элемент Пока-зать только верхний элемент и далее Другое...

Появится окно Свойства с раскрытой вкладкой Фильтр и группировка. Установите в поле со списком Отображать значение первые. В поле Элементы введите 80 и отметьте флажок %.

Вы увидите список из 37 товаров (из 77 наименований), отгрузка которых составляет 80,5 % от общего объема (11074406,20 / 13755956,30).

 

 

Рис. 5.21. АВС-анализ

 

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

Детализация

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

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

Щелкните маркер развертывания в столбце Кв4, и таблица развернется по месяцам. Чтобы раскрыть данные сразу по всем кварталам, выделите заголовок Кварталы (он находится рядом с заголовком Годы). При этом окажутся выделенными заголовки сразу всех кварталов 1996 г. Теперь на панели инструментов нажмите кнопку Развернуть. Обратите внимание, что кроме данных по каждому месяцу выводятся также промежуточные итоги за квартал и за год.

Чтобы снова свернуть таблицу, щелкните на заголовке Годы. Будут выделены все заголовки, содержащие год. Нажмите кнопку Свернуть слева от кнопки Развернуть.

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

Снова сверните строку (сокройте детальные данные), щелкнув на кнопке в той же строке.

Аналогично можно развернуть любой столбец. Щелкните на значке в заголовке 1996 г. Будут показаны детальные данные этого столбца (все продажи за 1996 г.) для всех товаров.

Еще две кнопки на панели инструментов позволяют манипулировать уровнем детализации данных в таблице: Скрыть подробности и Показать подробности. Их можно использовать, когда требуется развернуть или свернуть данные сразу по всем строкам или столбцам. Нужно выделить соответствующий заголовок (для столбца) или столбец в области строк и щелкнуть на одной из этих кнопок.

Отчет должен содержать: тему и цель работы, задание к работе, распечатку по каждому запросу, бланк Конструктора запроса, результат его работы.

 

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

1. Как вводятся в запрос вычисляемые поля?

2. Почему добавленное в бланк запроса вычисляемое поле не отображается в режиме таблицы?

3. Можно ли использовать результат вычисляемого поля в последующих вычислениях в этом же запросе, например, рассчитать НДС на основе вычисленной суммы продаж и если да, то как это сделать?

4. Как избежать ошибки при использовании в бланке запроса полей с совпадающими именами в нескольких таблицах?

5. Как изменить в вычисляемом поле формат результата, например, изменить числовой формат на денежный?

6. Какие встроенные функции Access вы знаете? Какой инструмент Access осуществляет к ним доступ?

7. Можно ли в вычисляемых полях использовать встроенные функции Access?

8. Каков обязательный порядок следования полей в итоговом запросе?

9. Для каких типов данных в итоговых запросах можно применять функцию Count?

10. К каким полям итогового запроса можно задавать условия отбора?

11. Какие возможности предоставляет режим сводных таблиц?

12. В каком режиме сохраняется сводная таблица? Как вернуть ей требуемый вид?

13. Как добавить новые поля в сводную таблицу?

14. Как добавить вычисляемое поле, например, для подсчета количества, в область итогов сводной таблицы?

15. Как отобрать товары, продажи которых составляют 1 % от общего итога?

Тестовые задания

1. Каким символом отделяется в вычисляемом поле имя от вычисления?

а) =

б):

в)!

г) #

2. Какими символами отделяется название таблицы от названия поля?

а):

б)!

в).

г) &

3. В какие символы Access автоматически заключает имена полей?

а) ()

б) []

в) {}

г) ##

4. В какой строке бланка запроса вводится вычисляемое поле?

а) Имя таблицы.

б) Поля.

в) Вывод на экран.

г) Построитель выражений.

5. Какое имя Access автоматически присваивает вычисляемым полям?

а) Поле1.

б) Выражение1.

в) Формула1.

г) Вычисление1.

6. Какие операции допустимы над полем Дата/Время?

а) из даты вычесть дату.

б) к дате прибавить дату.

в) из даты вычисть число.

г) к дате прибавить число.

7. Если к дате, хранимой в поле типа Дата/Время прибавить дробное число...

а) операция не будет выполнена;

б) операция будет выполнена, но результат не имеет смысла;

в) дробная часть будет отброшена и в результате получится дата, отстоящая от исходной на число дней, равное второму слагаемому;

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

8. Каков будет результат операции «Дата+Дата»?

а) дата.

б) числовой.

в) не определен.

г) Null.

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

а) Min.

б) First.

в) Last.

г) Sum.

10. В таблице содержатся сведения о начисленной заработной плате по каждому сотруднику за каждый месяц в течение года. Какая функция позволит вывести среднее значение заработной платы по каждому сотруднику

а) Count.

б) First.

в) Avg.

г) Sum.

Формы

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

 

6.1. Общая характеристика экранных форм [8]

 

Формами называются настраиваемые диалоговые окна, хранящиеся в базе данных в виде объектов специального типа. Формы содержат элементы управления, с помощью которых осуществляется доступ к данным в таблицах, что является, во многих случаях, более удобным способом просмотра и изменения данных. Элементы управления бывают либо присоединенные, когда они связаны с определенным полем в определенной таблице (запросе), либо свободные – находящиеся в форме для отображения дополнительной информации. Свободный элемент может содержать результат вычислений, основанный на информации, отображаемой другими элементами управления. Например, свободный элемент может перемножать значения полей Цена и Количество и отображать получаемый результат – Сумму.

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

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

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

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

 

6.2. Задание для самостоятельной работы

 

1. Построить формы, рассмотренные в примере.

2. Самостоятельно построить:

- создать простые формы в столбец и ленточные для таблиц, содержащих справочную информацию;

- создать составную форму, где в качестве главной и подчиненной формы будут использованы данные таблиц;

- создать составную форму, где в главной форме будут отображены данные из таблицы, а в подчиненной – из запроса;

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

Варианты заданий

Вариант 1

1) Создать формы «в столбец» на основе таблиц Сотрудники, Поставщики, Товары и ленточную форму на основе таблицы Доставка.

2) Создать составную форму для таблиц Товары и Поставки.

3) Создать составную форму на основе таблицы Поставщики и запроса, в котором выводятся суммы поставок каждого поставщика (запрос содержит групповые операции).

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

Вариант 2

1) Создать формы «в столбец» на основе таблиц Сотрудники, Товары и ленточную форму на основе таблицы Типы.

2) Создать составную форму для таблиц Типы и Товары.

3) Создать составную форму, где в главной форме будет отображены данные из таблицы Товары, а в подчиненной – из запроса объединяющего поля из таблиц Продажи и Товары и содержащего поле, вычисляющее сумму реализации.

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

Вариант 3

1) Создать формы «в столбец» на основе таблиц Квартиры, Сотрудники и ленточную форму для таблицы Продавцы.

2) Создать составную форму для таблиц Покупатели и Осмотры.

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

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

Вариант 4

1) Создать форму «в столбец» для таблицы Рекламодатели и ленточную для таблицы Виды реклам.

2) Создать составную форму для таблиц Печатные издания и Расценки.

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

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

Вариант 5

1) Создать форму «в столбец» для таблицы Претенденты и ленточную для таблицы Отбор.

2) Создать составную форму для таблиц Работодатели и Вакансии.

3) Создать составную форму, где в главной форме будет отображены данные из таблицы Сотрудники, а в подчиненной – из запроса, в котором рассчитывается процент комиссионного сбора в размере 1,5 % от будущего оклада претендента.

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

Вариант 6

1) Создать форму «в столбец» для таблиц Мастера, Клиенты и ленточную форму для таблицы Услуги.

2) Создать составную форму для таблиц Услуги и Заказы.

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

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

Вариант 7

1) Создать форму «в столбец» для таблиц Сотрудники, Клиенты и ленточную для таблицы Расценки.

2) Создать составную форму для таблиц Виды работ и Расценки.

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

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

Вариант 8

1) Создать форму «в столбец» для таблицы Сотрудники и ленточную для таблиц Отделы и Должности.

2) Создать составную форму для таблиц Учебные заведения и курсы.

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

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

Вариант 9

1) Создать ленточную для таблицы Расценки и форму «в столбец» для таблицы Арендаторы, Помещения.

2) Создать составную форму для таблиц Арендаторы и Договора.

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

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

Вариант 10

1) Создать формы «в столбец» на основе таблиц Сотрудники, Клиенты и ленточную форму на основе таблицы Продукция.

2) Создать составную форму для таблиц Отгрузка и Оплата.

3) Создать составную форму, на основе таблицы Отгрузка и запроса, в котором выводятся суммы отгруженной продукции.

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

Вариант 11

1) Создать форму «в столбец» на основе таблицы Поставщики, и ленточную форму на основе таблиц Изделия и Выпуск.

2) Создать составные формы для таблиц Изделия и Комплектующие, а также Поставщики и Материалы.

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

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

Вариант 12

1) Создать формы «в столбец» на основе таблицы Изделия и ленточную форму на основе таблицы Виды брака.

2) Создать составную форму для таблиц Изделия и Выпуск.

3) Создать составную форму, где в главной форме будут отображены данные из таблицы Выпуск, а в подчиненной – из запроса, в котором объединяются данные из таблицы Брак и Виды.

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

Вариант 13

1) Создать формы «в столбец» на основе таблицы Клиенты, Сотрудники и ленточную форму на основе таблицы Маршруты.

2) Создать составную форму для таблиц Маршруты и Автотранспорт.

3) Создать составную форму, где в главной форме будут отображены данные из таблицы Путевой лист, а подчиненной – из запроса, в котором объединяются данные из таблицы Перевозимые грузы и Маршруты.

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

Вариант 14

1) Создать ленточную форму на основе таблицы Вознаграждения и формы «в столбец» на основе таблицы Сотрудники, Клиенты, Анкеты.

2) Создать составную форму для таблиц Клиенты и Анкеты.

3) Создать составную форму, где в главной форме будут отображены данные из таблицы Клиенты, а в подчиненной – из запроса, в котором объединяются данные из таблицы Анкета и Вознаграждения.

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

Вариант 15

1) Создать ленточную форму на основе таблицы Вакансии и форму «в столбец» на основе таблицы Претенденты.

2) Создать составную форму для таблиц Вакансии и Претенденты.

3) Создать составную форму, где в главной форме будут отображены данные из таблицы Вакансии, а в подчиненной – из запроса, в котором объединяются данные из таблицы Отборочный тур и претендент.

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

 

6.3. Пример выполнения задания

 

Простая форма

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

 

 

Рис. 6.1. Автоформа: в столбец

 

6.3.2. Составная форма, основанная на двух таблицах

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

Для создания составной формы нажмите на вкладке Формы кнопку , затем выберите в появившемся списке Мастер форм и нажмите кнопку (рис. 6.2).

 

Рис. 6.2. Создание составной формы с помощью Мастера

 

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

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

 

 

Рис. 6.3. Выбор таблицы-источника для главной формы

 

Затем перенесите из списка Доступные поля в список Выбранные поля те поля, которые вы хотите видеть в главной форме, например, поля: Название, Адрес и Телефон. Для этого следует в списке Доступные поля выделить имя поля, например, Название и нажать кнопку для переноса в список Выбранные поля (рис. 6.4).

Если поле выбрано ошибочно, следует нажать кнопку для удаления поля из списка Выбранные поля.

 

Рис. 6.4. Выбор полей для главной формы

 

После того, как были выбраны все поля для главной формы, следует выбрать поля, которые будут отображаться в подчиненной форме. Выберите в списке Таблицы и запросы в качестве источника данных ту таблицу, данные из которой будут отображаться в подчиненной форме, т.е. таблицу Товары (рис. 6.5).

 

 

Рис. 6.5. Выбор таблицы-источника для подчиненной формы

 

Затем перенесите из списка Доступные поля в список Выбранные поля те поля, которые вы хотите видеть в подчиненной форме, например, поля: КодТипа, Марка, Цена, НаСкладе (рис. 6.6).

 

Рис. 6.6. Выбор полей для подчиненной формы

 

Нажмите кнопку и на следующем шаге мастера, в диалоговом окне, выберите переключатель Подчиненные формы. В поле образца сразу показано, как будут выглядеть будущие формы (рис. 6.7).

 

 

Рис. 6.7. Выбор вида представления формы

 

Нажмите кнопку и следуйте указаниям мастера. На следующем шаге выберите стиль для оформления подчиненной формы, рекомендуемый стиль – табличный (рис. 6.8).

 

Рис. 6.8. Выбор внешнего вида подчиненной формы

 

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

 

 

Рис. 6.9. Выбор стиля формы

 

Нажмите кнопку и либо оставьте предлагаемые по умолчанию имена форм, либо введите новые. Введите новое имя для главной формы Поставщики товаров (рис. 6.10).

 

Рис. 6.10. Задание имени формы

 

Нажмите кнопку . На экране появится форма Поставщики товаров (рис. 6.11), в которой будут отображаться выбранные поля из таблицы Поставщики. Подчиненная форма будет содержать только те товары, которые поставляет поставщик, указанный в главной форме.

 

 

Рис. 6.11. Составная форма Поставщики товаров