Практическая работа 8. Сводные таблицы порядок построения сводной таблицы
 Скачать 440.45 Kb.
|
|
Практическая работа 8. Сводные таблицы. Создание и редактирование. 4 СВОДНЫЕ ТАБЛИЦЫ Порядок построения сводной таблицы Сводные таблицы – это таблицы, обобщающие информацию из других таблиц или списков, содержащихся на рабочих листах или во внешних источниках данных. При создании сводной таблицы с помощью Мастера сводных таблиц задаются вид исходных данных, форма сводной таблицы и вид вычислений над элементами таблицы. Для построения сводной таблицы мы должны иметь: Во-первых, источник данных, в качестве которого может выступать список данных, расположенный на рабочем листе и оформленный в соответствии с пра- вилами формирования списков. Соблюдение этих несложных правил оформления списков позволит избежать возможных ошибочных ситуаций при использовании команд обработки данных. Правила формирования списков: 1. Каждый столбец (поле) списка должен содержать однородную информацию. 2. Каждый столбец(поле) списка должен иметь уникальное название. 3. Список не должен содержать пустые строки и столбцы внутри себя. 4. Список должен быть отделен от других данных пустой строкой и столбцом или размещен на отдельном листе рабочей книги. 5. Если предполагается использовать фильтрацию, то лучше не размещать другие данные справа от списка, так как часть строк, возможно, будет скрыта (это относится, в первую очередь, к автофильтру и выполнению фильтрации на месте – для расширенного фильтра). Во-вторых, после подготовки или проверки списка необходимо внимательно прочитать или сформулировать для себя вопрос, т.е. желательно представить, в каком виде вы хотели бы получить результирующую таблицу. Это достаточно важный этап, т.к. названия полей, которые будут использованы при формирова- нии структуры таблицы, обычно явно или неявно содержатся в вопросе. В-третьих, необходимо выполнить действия по построению таблицы: 5 1. Установить указатель активной ячейки (рамку) внутри исходной таблицы (мышью или с помощью клавиатуры). 2. Выполнить команду Д АННЫЕ – С ВОДНАЯ ТАБЛИЦА и последовательно прой- ти шаги Мастера. Шаг 1: Установить источник – список Excel. Шаг 2: Проверить правильность указания диапазона исходных данных. Шаг 3: (для Excel-2000 – щелкнуть на кнопке Макет, чтобы открыть окно структуры таблицы). Сформировать структуру сводной таблицы, пе- ретащив мышью кнопки с названиями полей в области строк, столб- цов, данных или страниц. В область данных помещаются те поля, по которым проводятся вычисления. Для числовых данных по умолча- нию используется функция Сумма, а для других – Кол-во значений. Шаг 4: Указать местоположение новой таблицы – новый лист или адрес левой верхней ячейки свободной области на существующем листе. В результате выполненных действий получится новая таблица, которая содер- жит обобщенные данные только из тех полей исходной таблицы, которые были использованы при формировании её структуры. Замечание. Если рабочая книга уже содержит сводную таблицу, то перед третьим шагом появится дополнительный вопрос об источнике данных. Пока вы осваиваете сводные таблицы, можно отвечать «Нет», т.е. создавать независи- мую сводную таблицу. Если полученная таблица вас не устраивает, ее очень легко изменить для по- лучения нужного результата. Для этого достаточно по-другому разместить поля в макете таблицы. Рассмотрим простейший пример. Пусть имеется список товаров, хранящихся на складе, содержащий в том чис- ле поля «Снять с реализации» и «Стоимость» (см. табл. 1, приложение 1). Задание: получить таблицу, содержащую данные об общей стоимости това- ров, подлежащих списанию, уценке и годных к продаже. Исходя из вопроса, при построении таблицы будут использованы поля «Снять с реализации» и «Стоимость». Выполним действия в соответствии с приведенным ранее алгоритмом. При формировании макета таблицы поле «Снять с реализации» переместим в область «Строка», а поле «Стоимость» (подлежащее вычислению) – в область «Данные». Структура таблицы на третьем шаге Мастера (Макет) представлена на рис. 1. 6 Рис. 1. Формирование структуры сводной таблицы Далее укажем расположение таблицы «на новом листе» и получим небольшую сводную таблицу, представленную на рис. 2. Сумма по полю Стоимость Снять с реализации Всего Годен 12951 нет данных 3344 Снять 216 Уценить 12142 Общий итог 28653 Рис. 2. Простейшая сводная таблица Если необходимо получить таблицу с разбивкой по группам товаров, то доста- точно в область столбцов добавить поле «Группа товаров». Для этого, находясь в сводной таблице, выполните Д АННЫЕ – С ВОДНАЯ ТАБЛИЦА , сразу откроется тре- тий шаг Мастера. Перетащите поле «Группа товаров» в область столбцов и таб- лица примет вид, как на рис. 3. Перемещение полей можно выполнять непосредственно на рабочем листе, удерживая левую кнопку мыши. Сумма по полю Стоимость Группа товаров Снять с реализации 1 2 3 Общий итог Годен 2630 884 3514 Нет данных 374 4144 4518 Снять 6810 405 12556 19771 Уценить 234 616 850 Общий итог 9674 2279 16700 28653 Рис. 3. Сводная таблица с указанием групп товаров В Excel-2000 для управления полями на рабочем листе удобно использовать панель инструментов «Сводные таблицы», т.к. на ней отображаются все названия полей таблицы. 7 Можно переместить поле «Группа товаров» в область строк и оставить перед полем «Снять с реализации». В этом случае поле «Группа товаров» будет внеш- ним, а поле «Снять с реализации» – внутренним. Сумма по полю Стоимость Группа товаров Снять с реализации Всего 1 Годен 2630 Снять 6810 Уценить 234 1 Всего 9674 2 Годен 884 Нет данных 374 Снять 405 Уценить 616 2 Всего 2279 3 Нет данных 4144 Снять 12556 3 Всего 16700 Общий итог 28653 Рис. 4. Измененная сводная таблица Попробуйте поменять местами поля «Группа товаров» и «Снять с реализации» и оцените произошедшие изменения. В зависимости от того, как звучит поставленный вопрос, мы очень быстро из- меняем структуру сводной таблицы. Поэтому, если первоначально построенная таблица получилась не очень удобной и информативной, поэкспериментируйте с размещением полей и подберите наиболее подходящую структуру. Замечание: Если в области данных размещено более одного поля, а область столбцов не использовалась, то на рабочем листе в готовой сводной таблице можно переместить поле «Данные» в область столбцов. Перемещение поля «Данные» в область столбцов иногда делает таблицу более удобной для работы. Обратите внимание, что при просмотре макета таблицы поля по-прежнему находятся в области данных, т.е. такое перемещение возможно только непосредственно на рабочем листе! Сравните два варианта сводных отче- тов на рис. 5 (исходные данные для отчетов см. табл. 2, приложение 1). Фирма Данные Всего Евразия+ Сумма по полю Стоимость 15155 Сумма по полю Кол-во 488 Мангнолия Сумма по полю Стоимость 15150 Сумма по полю Кол-во 505 Скиф Сумма по полю Стоимость 29024 Сумма по полю Кол-во 942 Итог Сумма по полю Стоимость 59329 Итог Сумма по полю Кол-во 1935 Данные Фирма Сумма по полю Стоимость Сумма по полю Кол-во Евразия+ 15155 488 Мангнолия 15150 505 Скиф 29024 942 Общий итог 59329 1935 Рис. 5. Изменение положения поля данных Можно изменять положение поля данных, подбирая наиболее подходящий вариант расположения данных сводной таблицы. 8 Изменение суммирующей функции Для выполнения вычислений, отличающихся от принятых по умолчанию (Сумма и Количество значений), необходимо при формировании структуры таб- лицы дважды щелкнуть на кнопке поля, помещенного в область данных, и вы- брать другую функцию. В этом же диалоге можно изменить формат отображения числовых данных и, при необходимости, имя поля. Рис. 6. Изменение суммирующей функции Измените структуру существующей сводной таблицы, переместив в область данных поле «Цена ед.». Дважды щелкните на нем и укажите функцию «Макси- мум». Для изменения формата отображения данных используется кнопка «Формат». При этом открывается окно, аналогичное окну команды Ф ОРМАТ – Я ЧЕЙКИ /Ч ИСЛО . Получится таблица, показанная на рис. 7, и отображающая мак- симальную цену товаров в каждой группе по каждой категории. Максимум по полю Цена ед Группа товаров Снять с реализации 1 2 3 Общий итог Годен 90 9 90 Нет данных 11 74 74 Снять 44 9 87 87 Уценить 39 11 39 Общий итог 90 11 87 90 Рис. 7. Таблица с измененной суммирующей функцией Вывод исходных данных Двойной щелчок на элементе в области данных сводной таблицы выводит на новый лист список строк исходной таблицы, влияющих на данный элемент, т.е. «расшифровывает» сводное значение. Например, если дважды щелкнуть на значении «90» строки «Годен» в сводной таблице на рис. 7, то на новом листе, который добавится перед текущим, появится таблица, содержащая те строки исходной таблицы, от которых зависит данное значение (см. рис. 8). 9 № Группа товаров Код Товара Наименование Фирма Дата изготовления Годен до Снять с реализации Количество Цена ед Стоимость 19 1 15 шоколад ЮУК 10.06.02 10.12.02 Годен 3 90 270 2 1 11 монпасье КонФи 12.03.02 12.12.02 Годен 56 29 1624 3 1 12 печенье КонФи 19.12.01 12.12.02 Годен 23 32 736 Рис. 8. Исходные данные для значения «Годен»-«1» Обновление данных Если после построения сводной таблицы произошли изменения в исходном списке, не изменившие размер списка, то нет необходимости заново строить сводную таблицу. Достаточно обновить в ней данные командой Д АННЫЕ – О БНОВИТЬ ДАННЫЕ (указатель ячейки должен находиться в сводной таблице). Если изменилось количество строк исходного списка, то необходимо выпол- нить команду Д АННЫЕ – С ВОДНАЯ ТАБЛИЦА , вернуться на предыдущий шаг (кноп- ка Назад) и исправить адрес исходного диапазона данных. Можно присвоить ис- ходному списку имя и указать его в качестве исходного диапазона. При добавле- нии и удалении строк внутри именованного диапазона автоматически изменяется ссылка на него. Если исходному списку присвоить имя «База_данных», то при добавлении записей в список с помощью команды Д АННЫЕ – Ф ОРМА они автома- тически попадут в именованный диапазон и будут учтены при обновлении дан- ных. Однако, при обновлении данных в сводной таблице вы можете потерять фор- матирование, которое было применено к таблице после ее построения. Для сохра- нения установленного форматирования сводной таблицы при обновлении данных, должен быть установлен флажок «сохранять форматирование» в окне «П АРАМЕТ- РЫ СВОДНОЙ ТАБЛИЦЫ » (рис. 9). Рис. 9. Окно «П АРАМЕТРЫ СВОДНОЙ ТАБЛИЦЫ » Excel-97 10 Скрытие элементов поля Если в полученной сводной таблице необходимо отображать не все элементы поля, расположенного в области строк или столбцов, то «лишние» элементы мож- но скрыть. Порядок работы: — Excel-97 – дважды щелкнуть на кнопке с названием поля в сводной таблице. Откроется диалог «Вычисление поля сводной таблицы», в котором необходимо указать, какие элементы скрыть. — Excel-2000 – раскрыть список элементов поля и убрать флажки у элементов, которые не должны отображаться. Вид окна и таблицы с развернутым списком элементов представлены на рис. 10а) и 10б), на рис. 11 показана таблица после скрытия элемента «нет данных». Рис. 10. Скрытие элементов поля Сумма по полю Стоимость Группа товаров Снять с реализации 1 2 3 Общий итог Годен 2630 884 3514 Снять 6810 405 12556 19771 Уценить 234 616 850 Общий итог 9674 1905 12556 24135 Рис. 11. Скрытие элементов поля. В таблице скрыт элемент «Нет данных» Группировка данных Мастер сводных таблиц автоматически группирует элементы внутреннего по- ля для каждого заголовка внешнего поля (см. рис. 4). Однако имеются и дополни- тельные возможности для объединения элементов в группы. а) Окно для Excel-97 б) Скрытие элементов в Excel-2000 11 Группировка по дате Пусть в таблице имеется столбец, содержащий даты, например «Дата поступ- ления» (приложение 1, табл. 2). Мы хотели бы определить ежемесячную стои- мость поступивших товаров. Построим сводную таблицу, расположив поле «Дата поступления» в области строк, а поле «Стоимость» – в области данных. В сводной таблице будут присут- ствовать все уникальные значения данного поля, делая её очень громоздкой. Для того, чтобы получить ежемесячный или квартальный отчет, можно вы- полнить группировку элементов. Порядок работы: 1. Установите указатель ячейки на кнопку поля «Дата поступления» в сводной таблице. 2. Выполните Д АННЫЕ – Г РУППА И СТРУКТУРА – Г РУППИРОВАТЬ (рис. 12) и укажите способ группировки (по месяцам, кварталам, годам). Причем из таблицы можно взять данные только за определенный период, или сгруп- пировать с шагом в несколько дней, например, в семь дней (рис. 14). Рис. 12. Группирование по дате Замечания: 1. Если выдается сообщение о невозможности группировки, про- верьте: а) правильность ввода даты в исходном списке и обновите сводную таб- лицу командой Д АННЫЕ – О БНОВИТЬ ДАННЫЕ или заново постройте сводную таб- лицу, б) правильность указания поля группировки. 2. Если элементы поля сгруппированы, в него нельзя добавить вычисляемый элемент (см. раздел "Создание вычисляемых элементов и полей"). В этом случае необходимо разгруппировать поле, создать вычисляемый элемент, а затем снова выполнить группировку. В результате может получиться сводная таблица, представленная на рис. 13 (у вас могут получиться другие числа в поле «Всего» из-за различия в исходных данных). 12 Сумма по полю Стоимость Кварталы Дата поступления Всего Кв-л1 янв 21484 фев 17149 мар 9146 Кв-л4 окт 11550 Общий итог 59329 Рис. 13. Сводная таблица, сгруппированная по кварталам и месяцам Сумма по полю Кол-во Дата поступления Всего 03.01.02 - 09.01.02 379 17.01.02 - 23.01.02 219 24.01.02 - 30.01.02 66 >31.01.02 1271 Общий итог 1935 Рис. 14. Сводная таблица, сгруппированная в диапазоне дат с 1.01.02 по 31.01.02 с интервалом 7 дней Группировка произвольных элементов Группировка произвольных элементов выполняется в том случае, когда не- сколько элементов поля нужно объединить с целью получения дополнительных итогов по группе. Порядок работы. 1. Выделить элементы, объединяемые в группу. 2. Выполнить Д АННЫЕ – Г РУППА И СТРУКТУРА – Г РУППИРОВАТЬ . Ввести новое название группы вместо стандартного «Группа 1». Измените название нового поля. Двойной щелчок мыши на внешнем поле скрывает элементы внутреннего по- ля. Повторный двойной щелчок раскрывает список сгруппированных элементов. Группировка элементов в числовых интервалах Группирование данных может оказаться необходимым и для полей с число- выми данными, например, инвентарными номерами, номерами накладных, число- выми кодами. Числовые элементы группируются так же, как даты, но окно пара- метров группировки будет выглядеть, как на рис. 15. Рис. 15. Группирование в числовых интервалах По умолчанию используются мини- мальное и максимальное значения поля 13 и шаг 1. Установим шаг группирования равным 10 и тогда сводная таблица, отра- жающая количество товара каждого кода, сгруппированная по кодам с шагом 10 (исходные данные см. в табл. 1, приложения 1), примет вид, как на рис. 16. Сумма по полю Количество Код Товара Всего 10-19 265 20-29 237 30-39 234 Общий итог 736 Рис. 16. Группировка по кодам товара с шагом 10 Отмена группировки элементов Для удаления группы, т.е. восстановления первоначального представления элементов поля, выделите сгруппированный элемент и выполните команду Д АННЫЕ – Г РУППА И СТРУКТУРА – Р АЗГРУППИРОВАТЬ или щелкните кнопку (разгруппировать) на панели инструментов «С ВОДНЫЕ ТАБЛИЦЫ » (см. рис. 17). Рис. 17. Панель инструментов «С ВОДНЫЕ ТАБЛИЦЫ » (Excel-97) Использование поля страниц Если при построении сводной таблицы была использована область страниц, то поля, размещенные в ней, имеют вид раскрывающегося списка. Выбор элемента списка приводит к отображению данных, которые ему соответствуют. Это поле удобно использовать для того, чтобы сделать таблицу более компактной или из- бежать необходимости скрывать множество элементов поля. Рассмотрим пример: составить отчет для каждой фирмы о количестве това- ров, подлежащих списанию и уценке, а также годных к продаже (поле «Фирма» поместим в область страниц)(исходные данные см. в табл. 1, приложения 1). Сводная таблица может иметь вид, как на рис. 18. Рис. 18. Группировка произвольных элементов и использование поля страниц Если в страничном поле выбрать элемент «Все», то вы получите итоговую 14 сводную таблицу по данному полю. Выбор другого элемента приведет к отобра- жению соответствующих ему данных. В области страниц может находиться не- сколько полей. Несмотря на страничную организацию сводной таблицы, она хра- нится на одном листе рабочей книги. При наличии страничного поля в сводной таблице можно получить копии таб- лиц на отдельных листах книги для каждого значения выбранного поля страницы. Для этого необходимо отобразить панель инструментов «С ВОДНЫЕ ТАБЛИЦЫ » (рис. 17) и щелкнуть кнопку «Отобразить страницы». Новые листы добавятся перед листом с исходной сводной таблицей и получат имена в соответствии со значениями страничного поля. |