Малоизвестная или недооцененная возможность. Помещение сводных таблиц в их собственные формы

Как вы уже поняли, сводная таблица — это режим необычного представления таблицы (или запроса). Когда вы закрываете окно после построения сводной таблицы, программа Access предлагает сохранить сделанные вами "изменения макета" (layout changes). В этот момент у вас есть два варианта: выбрать Да для сохранения структуры сводной таблицы с основной таблицей или запросом для просмотра в дальнейшем или выбрать Нет для того, чтобы отбросить все сделанное. Если вы выбрали Нет, то при переключе­нии в режим Сводная таблица в следующий раз вы начнете снова с чистого листа и при­дется собирать сводную таблицу с пуля.

Р ис. 9.17. В представленной сводной таблице группы стран подразделяются на штаты, а группы категорий товаров — на отдельные изделия. В сводной таблице также отображаются два итоговых поля: общий доход (TotalRevenu) и общее количество проданных товаров (OrderQty Sum)

Если вы истинный ценитель сводных таблиц, то, возможно, вам захочется сохранить для одной и той же таблицы или запроса два разных варианта сводной таблицы. В этом слу­чае вы смогли бы легко взглянуть на ваши данные под разными углами. К сожалению, каждая таблица или запрос могут вмещать только одну сводную таблицу. Что же делать предприимчивому разработчику БД?

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

Для создания формы сводной таблицы выполните следующие действия.

1. Выберите в области переходов таблицу или запрос, данные которых вы хотите ис­пользовать в сводной таблице.

2. Выберите на ленте Создание → Формы → Другие формы → Сводная таблица

(Create → Forms → More Forms → PivotTable).

3. На экране появится стандартное окно конструктора сводной таблицы.

4. Перетащите поля для создания сводной таблицы.

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

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

Скрытие и отображение подробностей

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

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

Самый легкий способ раскрыть или свернуть данные — использование кнопок +/-, ко­торые выводятся рядом с заголовками строк и столбцов (рис. 9.18). Этот метод позволяет раскрыть или свернуть все группы в конкретной строке или столбце.

Если вы хотите сосредоточиться на конкретных данных, можно раскрыть единственную ячейку. В данном случае просто щелкните ячейку правой кнопкой мыши и выберите коман­ду С подробностями (Show Details). Например, используя этот метод, можно раскрыть ячейку, в которой отображаются продажи одежды в Австралии (вместо объема продаж оде­жды во всех странах или объема всех продаж в Австралии).

Фильтрация в сводных таблицах

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

Существует несколько способов фильтрации. Два самых быстрых варианта фильтрации — выбор элементов, которые вы хотите видеть, из списка. Далее перечислены эти варианты.

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

Рис. 9.18. Используйте кнопку со знаком "плюс" (+) для отображения подробностей свернутой группы и кнопку со знаком "минус" (-) для свертывания развернутой группы. В данной сводной таблице все группы товаров свернуты за исключением группы Accessories. Также свернута страна Австралия, поэтому отображаются только итоги (а не распределение по регионам)

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

■ Можно отбирать записи, используя другие поля в исходной таблице. Просто перетащите их из списка полей сводной таблицы в область над сводной таблицей Перетащите сюда поля фильтра (Drop Filter Fields Here). После того как поле фильтра вставлено, рядом с его заголовком появляется раскрывающийся список. Щелкните кнопкой мыши стрелку для отображения списка всех значений и сбросьте флажок, расположенный рядом со значениями, которые вы не хотите видеть.

Программа Access позволяет отобрать максимальные и минимальные значения вычис­ляемых величин в любой группе. Например, можно применить этот метод для того, чтобы скрыть плохо продаваемые товары. Для этого щелкните правой кнопкой мыши заголовок поля ProductName и выберите вариант из подменю Показать верхние и нижние элементы (Show Top/Bottom Items). Возможно, вы хотите увидеть конкретное число товаров (наилучшие или наихудшие 1, 10, 25 и т. д.) или процентное выражение (наилучший 1%, наилучшие 10% и т. д.). На рис. 9.20 показан пример.

Примечание

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

Фильтрацию верхних/нижних элементов легко применять, но если у вас несколько уровней группировки, нужно тщательно выбирать место применения фильтрации. Напри­мер, рассмотрим сводную таблицу, показанную на рис. 9.20, в которой продажи разделены по категориям и названиям товаров. Если применить фильтр "верхние/нижние" к полю

ProductName, вы увидите 1% наилучших товаров из всех изделий. Но если однопроцент­ный фильтр применить к полю ProductCategory, вы увидите наилучший 1% из всех катего­рий. Другими словами, вы сосредоточитесь на категориях, имеющих максимальные объемы продаж, а не на самых ходовых товарах.

Р ис. 9.20. Сводная таблица сокращена до пяти процентов самых популярных товаров. Если есть категории, не содержащие товары из этого диапазона, эти категории не будут отображаться вообще

Для того чтобы понять разницу, представьте, что произойдет, если в категорию Components входит большое количество медленно продаваемых изделий, которые, будучи просум­мированы, представляют внушительную величину. Когда вы примените фильтр к полю ProductCategory, то увидите все товары в этой категории с высокой торговой эффектив­ностью. Если же применить фильтр к полю ProductName, вы сосредоточитесь на самых по­пулярных товарах и категориях, их содержащих. В данном случае центром внимания станет категория Clothing с несколькими самыми ходовыми товарами.

Подсказка

Есть опасность затянуть на себе узел, применив слишком много условий фильтрации одновре­менно. Если вы забыли, какие условия отбора задали, их можно отключить все сразу, выбрав на ленте Работа со сводными таблицами | Конструктор → Фильтр и сортировка → Автофильтр (PivotTable Tools | Design → Filter & Sort → AutoFilter).

Сводные диаграммы

П рограмма Access позволяет создавать диаграммы, основанные на данных сводной табли­цы. На самом деле с каждым представлением сводной таблицы связано представление сводной диаграммы. Для переключения из режима сводной таблицы на диаграмму, ото­бражающую ваши результаты в графической форме, выберите на лепте Работа со свод­ными таблицами | Конструктор → Режим → Сводная диаграмма (PivotTable Tools | Design → View → PivotChart View) или воспользуйтесь кнопками режимов в правом нижнем углу окна программы.

Рис. 9.21. На этой сводной диаграмме показана сводная таблица, разделенная по категориям товаров в строках и на страны в столбцах. Каждая категория строк отображается как группа расположенных рядом столбцов. Можно переместить указатель мыши над столбцом и увидеть всплывающую подсказку с дополнительными сведениями о нем. В этом примере выбранный в данный момент столбец (самый высокий), отображающий объем продаж велосипедов в США, превышает объемы продаж во всех группах

В случае сводной таблицы товаров из примера, описанного ранее в этой главе, сводная диаграмма позволяет легко увидеть группы, выделяющиеся объемами продаж. Нужно про­сто найти самые высокие столбцы на диаграмме, показанной на рис. 9.21.

Подсказка

Выберите на ленте Работа со сводными таблицами | Конструктор → Показать или скрыть → Легенда (PivotChart Tools | Design → Show/Hide → Legend) для вывода на экран блока с условными обозначениями ваших групп (legend box).

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

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

Подсказка

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

Выбор типа диаграммы

Еще одно ограничение, связанное со сводными диаграммами, — малое число вариантов ви­зуализации данных. Изменить тип диаграммы можно, щелкнув диаграмму правой кнопкой мыши и выбрав команду Изменить тип диаграммы (Change Chart Type). На экране появля­ется коллекция разных вариантов. Но большинство диаграмм в этой коллекции, начиная от круговых и заканчивая графиками, не могут сформировать приличного отображения ваших данных с большим количеством групп. В действительности стоит опробовать только три приемлемых варианта.

• Гистограмма с накоплением (stacked bar or column chart) формирует столбец для каждой группы и затем делит его для представления подгрупп (рис. 9.22).

• Нормированная гистограмма с накоплением очень похожа на обычную гистограмму с накоплением, за исключением того, что каждый прямоугольник растянут на всю высоту диаграммы. В этом случае вы действительно сможете сравнивать подгруппы (рис. 9.23).

Рис. 9.22. В гистограмме с накоплением группа каждой строки — отдельный прямоугольник. Затем прямоугольник делится на группы столбцов таблицы. В данном примере это означает, что у вас есть один прямоугольник для каждой страны и отдельные области прямоугольника представляют объемы продаж в разных категориях товаров для этой страны. Гистограмма с накоплением облегчает сравнение разных категорий. Очевидно, что велосипеды лучше всего продаются во всех странах

Р ис. 9.23. В нормированной гистограмме с накоплением нельзя определить страну с максимальным объемом продаж, но можно сравнить распределение продаж по категориям. Например, можно установить, в какой стране максимальная выручка от продаж велосипедов. (В данном примере, такой страной оказывается Австралия, но, как ни странно, в других странах похожая картина)

■ Объемная гистограмма в основном похожа на обычную гистограмму. Она просто позво­ляет расположить столбцы по ширине и глубине более логично (рис. 9.24).

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

Подсказка

Для поворота объемной диаграммы щелкните кнопкой мыши в свободном месте диаграммы. Затем выберите на ленте Работа со сводными таблицами | Конструктор → Сервис → Стра­ница свойств для отображения окна Свойства (Properties). Перейдите на вкладку Объем (3D View), содержащую множество ползунков, которые можно передвигать для получения разных ракурсов представления данных.

Печать сводной диаграммы

Если вы хотите напечатать сводную диаграмму, используйте последовательность команд Office → Печать (или кнопка Office → Предварительный просмотр для того, чтобы сначала внимательно посмотреть на то, как будут выглядеть ваши результаты).

Если у вас нет цветного принтера, визуальное разделение групп может оказаться труд­ной задачей. Можно выбрать определенные цвета для всех групп, но это потребует дополни­тельных усилий. Вот как это делается.

1. Щелкните кнопкой мыши конкретную группу где-нибудь на диаграмме (например, группу Bikes в столбце Australia).

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

столбце Australia (Австралия), группа Bikes будет выделена во всех странах, а именно ее вы и хотите изменить.

3. Выберите на ленте Работа со сводными таблицами | Конструктор → Сервис → Страница свойств (PivotChart Tools | Design → Tools → Property Sheet) для вывода на экран окна Свойства.

4. Выберите вкладку Границы и заливка (Border/Fill). На ней вы найдете параметры, позволяющие задать толщину и цвет границ вокруг столбца и цвет (или узор) для заполнения внутренней области столбца.

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

Часть III

Отчеты

Глава 10. Создание отчетов

Глава 11. Проектирование сложных отчетов

Глава 10

Создание отчетов

Для создания твердой копии ваших тщательно сохраняемых в Access данных есть множество причин. Имея хорошую распечатку, вы можете:

■ брать с собой информацию без транспортировки своего компьютера. Например, можно захватить с собой инвентарный список, когда идете за покупками;

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

■ просмотреть подробности вне офиса. Например, можно искать ошибки в пригородной электричке по дороге домой;

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

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

Отчеты — это специализированные объекты БД, похожие во многом на таблицы и запро­сы. В результате вы можете подготовить нужное вам количество отчетов и хранить их под рукой неограниченно долго. Жизнь не покажется медом, если ограничиваться только лис­том данных. Например, если вы пользуетесь БД о куклах-болванчиках, у вас может возник­нуть желание напечатать инвентарный список кукол с указанием имени куклы и сведений об изготовителе и отдельный список с ценами для составления сметы расходов. Переключа­ясь многократно между этими двумя выводами на печать и листом данных, вы должны бу­дете каждый раз вручную изменять порядок столбцов и скрывать их. У отчетов нет этих проблем, поскольку каждый из них сохраняется как отдельный объект БД. Итак, если вы хотите напечатать вашу инвентарную опись, то просто запустите отчет DollInventory. Если нужны подробности сметы, выполните отчет DollPrices.

Примечание

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

к оторые вам когда-либо понадобятся, и затем сохранить каждую из них как отдельный объект БД.

Р ис. 10.1. Обычные распечатки, как всем известно, плохи при обработке больших объемов данных в одном столбце. Посмотрите на поле Description в таблице Dolls. У всех записей под описание отводится область одного размера. Если объем данных больше доступного пространства (как в случае куклы Эдгара Алана По), информация в конце обрезается. Если данных меньше (как в случае куклы Джеймса Джойса), вы будете любоваться зияющими пустотами

Рис. 10.2. В типичном отчете вы задаете ширину столбцов, а высота каждой строки зависит от количества информации в записи. Это означает, что каждая строка достаточно велика для того, чтобы вместить весь текст из поля Description. Самое замечательное в том, что не нужно задавать специальные параметры для обеспечения подобного поведения. Отчеты делают это автоматически

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

Базовые сведения об отчетах

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

Примечание

Как вы увидите, простой способ создания отчета всегда помещает данные в табличную струк­туру (со строками и столбцами). Вы научитесь избавляться от этого дизайна в главе 11.

Создание простого отчета

Создание простого отчета требует двух шагов и еще несколько понадобится для его тонкой настройки. Если хотите опробовать этот метод самостоятельно, откройте БД Boutique Fudge (включенную в загружаемое содержимое для данной главы, описанное в разд. "Примеры" во введении) или собственную БД и выполните следующие действия.

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

В данном примере применяется таблица Products из БД Boutique Fudge. Можно также создать отчет на базе запроса. Об этом приеме см. дополнительную информацию в при­мечании. "На профессиональном уровне. Выполнение тяжелой работы с помощью запро­са " далее в этом разделе).

2. Выберите на ленте Создание → Отчеты → Отчет (Create → Reports → Report).

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

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

Примечание

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

3. Измените ширину столбцов, увеличивая ее или уменьшая до тех пор, пока не добьетесь нужного баланса.

Для изменения ширины столбца сначала щелкните кнопкой мыши его заголовок, чтобы выбрать столбец. (Вокруг столбца появится точечная линия.) Далее переместите мышь к правому краю ячейки с заголовком столбца так, чтобы указатель превратился в двуна­правленную стрелку. И, наконец, с нажатой кнопкой мыши перетащите границу столбца влево (для сужения) или вправо (для расширения). На рис. 10.3 показан этот процесс.

Р ис. 10.3. Перетащите край столбца для получения нужной ширины. Черный прямоугольник показывает новую ширину. Когда вы отпустите кнопку мыши, программа Access изменит ширину столбца и, соответственно, передвинет все последующие столбцы. Для того чтобы последний столбец не ушел за пределы окна, возможно, после расширения некоторых столбцов другие придется немного сузить

Примечание

На правой стороне отчета может появиться точечная линия, обозначающая край страницы. Вы можете изменить ширину столбца так, что он выйдет за край страницы. Это сделать имеет смысл, если у вас десятки столбцов и единственный способ справиться с ними — распечатать таблицу, занимающую две страницы по ширине. Тем не менее обычно лучше разместить все поля на одной странице, повернув ее набок с помощью альбомной ориентации (см. разд. "Изменение макета страницы" главы 3), если нужно расположить дополнительные столбцы.

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

Для перемещения столбца щелкните кнопкой мыши его заголовок и затем перетащите столбец с нажатой кнопкой мыши в новое место.

Подсказка

Столбцы можно передвигать и с помощью клавиатуры. Просто щелкните кнопкой мыши для выбора нужного столбца, а затем используйте клавиши <?> или <→> для перепрыгивания с од­ного места на другое.

5. При желании можно откорректировать форматирование, сменив шрифты, цвета и границы.

Самый быстрый способ изменения форматирования вашего отчета — выбор подходящей части (с помощью щелчка мышью) и использование кнопок на ленте Работа с макетами отчетов | Формат → Шрифт (Report Layout Tools | Formatting → Font). С помощью это­го метода можно изменить способ отображения заголовков, имен столбцов и данных. Дополнительную информацию о нем можно найти в разд. "Форматирование столбцов и заголовков столбцов" далее в этой главе.

6. Добавьте завершающий штрих.

Сейчас самое время изменить заголовки, вставить эмблему и номера страниц. Вы узнаете как добавлять эти элементы в разд. "Создание пустого отчета" далее в этой главе.

7. Для печати отчета можно выбрать последовательность: кнопка Office → Печать (Office → Print).

Отрегулировать параметры печати можно и в режиме Предварительный просмотр (выберите Office → Печать → Предварительный просмотр, как описано в разд. "Печать, предварительный просмотр и экспорт отчета" далее в этой главе).

8. Сохраните ваш отчет для дальнейшего использования.

Сохранить отчет можно в любое время, нажав комбинацию клавиш <Ctrl>+<S>. Если закрыть вкладку отчета без сохранения, программа Access сообщит о необходимости со­хранения отчета. В любом случае вам придется задать имя отчета.

Возможно создание отчетов с тем же именем, что и таблицы или другие объекты БД. На­пример, можно создать отчет Products, отображающий информацию из таблицы Products. Но на практике лучше выбирать для отчета более точное имя (например, Products By Category (товары по категориям), ProductListForDealers (список товаров для дилеров) и Top50Products (50 самых популярных товаров)). Отчет, приведенный на рис. 10.2, назван ProductCatalog (каталог товаров),