Практическая работа 8. Сводные таблицы порядок построения сводной таблицы
 Скачать 440.45 Kb.
|
|
Сортировка элементов Мастер сводных таблиц обычно сортирует элементы по возрастанию. Однако, можно воспользоваться обычными командами сортировки, чтобы расположить элементы поля в нужном порядке. Для этого необходимо: — выделить любой элемент сортируемого поля; — выполнить команду Д АННЫЕ – С ОРТИРОВКА При сортировке элементов в области данных необходимо выделить любую ячейку в сортируемом столбце (но не заголовок столбца) и выполнить команду сортировки или щелкнуть на соответствующей кнопке панели инструментов «Стандартная». Для сортировки, отличающейся от порядка «по возрастанию» или «по убыва- нию» используется сортировка с параметрами. В этом случае следует воспользо- ваться кнопкой Параметры в окне «С ОРТИРОВКА » и выбрать в списке «Сорти- ровка по первому ключу» список элементов, в соответствии с которым будет про- изводиться сортировка. Этот список – не что иное, как список автозаполнения, который может быть создан заранее командой С ЕРВИС – П АРАМЕТРЫ /С ПИСКИ Например, если был создан список автозаполнения «Лимоны, Апельсины, Мандарины», то после сортировки по возрастанию с параметрами эти элементы всегда будут следовать именно в таком порядке (сводная таблица на рис. 19 по- строена по табл. 2 приложения 1). Сумма по полю Стоимость Фирма Наименование Евразия+ Мангнолия Скиф Общий итог Лимоны 396 313,5 495 1204,5 Апельсины 725,7 1092,4 1721,5 3539,6 Мандарины 1018 616 1913,9 3547,9 Бананы 723 574,2 181,5 1478,7 Яблоки 352 910,8 2219,2 3482 Общий итог 3214,7 3506,9 6531,1 13252,7 Рис. 19. Сводная таблица, отсортированная по полю «Наименование» в соответствии с пользовательским списком Для создания особого порядка следования элементов можно просто перета- 15 щить его мышью в другое место. Указатель мыши нужно установить на рамку выделенной ячейки, чтобы он превратился в стрелку и, удерживая левую кнопку, перетащить элемент на новое место. Итоги По умолчанию Мастер сводных таблиц создает общие итоги для всех внешних полей и промежуточные итоги для всех внутренних полей таблицы, за исключе- нием самого внутреннего. Общие итоги создаются с той же суммирующей функцией, что и в поле дан- ных. Можно отменить вывод итогов по строкам и/или столбцам (например, если предполагается построение диаграммы по сводной таблице), если сбросить соот- ветствующие флажки в окне «П АРАМЕТРЫ СВОДНОЙ ТАБЛИЦЫ » (см. рис. 9). По умолчанию для промежуточных итогов также используется функция поля данных, но, в отличие от общих итогов, ее можно изменить в окне «В ЫЧИСЛЕНИЕ ПОЛЯ СВОДНОЙ ТАБЛИЦЫ » см. рис. 10а), которое открывается двойным щелчком на заголовке поля или командой П ОЛЕ из контекстного меню. Причем можно вы- брать одновременно несколько функций (в разумных пределах, конечно). По умолчанию итоги и промежуточные итоги включают только видимые объ- екты. Excel рассчитывает и отображает различные итоги и промежуточные итоги при скрытии и отображении различных объектов В вычисляемых полях подведение итогов данных всегда производится с по- мощью итоговой функции "Сумма". Изменение итоговой функции для вычисляе- мого поля невозможно. Иногда промежуточные итоги сильно перегружают сводную таблицу, тогда от них можно отказаться, установив переключатель в положение «нет». 16 ВЫЧИСЛЕНИЕ СООТНОШЕНИЙ МЕЖДУ ЭЛЕМЕНТАМИ СВОДНОЙ ТАБЛИЦЫ Кроме стандартных вычислений в области данных, в сводной таблице можно вычислять соотношение между элементами. Например, можно вычислить отличие от базового элемента, процентный вклад каждого значения в общий итог, полу- чить нарастающие итоги. Для этого в готовой сводной таблице необходимо вы- полнить следующие действия: — Выполните команду Д АННЫЕ – С ВОДНАЯ ТАБЛИЦА , чтобы открыть структу- ру сводной таблицы (макет). — Дважды щелкните на кнопке поля в области данных. Откроется диалог «П ОЛЕ СВОДНОЙ ТАБЛИЦЫ », в котором : а) укажите новое название поля, б) щелкните на кнопке Дополнительно, в) в списке «Дополнительные вычисления» выберите вид вычислений для эле- ментов (отличие, доля, доля от суммы по строке, нарастающий итог и т.д.) При необходимости укажите базовое поле (например, поле, расположенное в области «столбцы») и базовый элемент (тот, с которым будет производиться сравнение). Установите необходимый числовой формат (кнопка «Формат») и под- твердите действия. В область данных можно переместить ещё один экземпляр поля, по которому выполняются дополнительные вычисления, и оставить для него обычный вид вы- числений, чтобы получить в одной таблице два варианта отображения значений данного поля. Рассмотрим несколько примеров. Пример 1 Постройте сводную таблицу по табл. 2 приложения 1, отображающую стои- мость каждого наименования товаров, полученных от каждой фирмы. Расположи- те поле «Фирма» в области столбцов, «Наименование» – в области строк, «Стои- мость» – в область данных. Установите числовой формат с двумя десятичными знаками. Получится сводная таблица, как на рис. 20. Сумма по полю Стоимость Фирма Наименование Евразия+ Мангнолия Скиф Общий итог Апельсины 725,7 1092,4 1721,5 3539,6 Бананы 723 574,2 181,5 1478,7 Лимоны 396 313,5 495 1204,5 Мандарины 1018 616 1913,9 3547,9 Яблоки 352 910,8 2219,2 3482 Общий итог 3214,7 3506,9 6531,1 13252,7 Рис. 20. Сводная таблица для примера 1 17 Поместите ещё одно поле «Стоимость» в область данных и, следуя указанному выше алгоритму, установите для него дополнительное вычисление «Доля от суммы по строке». Если нет необходимости отображать числовые значения стоимости, можно ос- тавить в области данных только поле с дополнительным вычислением. Фирма Наименование Данные Евразия+ Мангнолия Скиф Общий итог Лимоны Сумма по полю Стоимость 1320 1089 1705 4114 Доля от суммы по строке 32,09% 26,47% 41,44% 100,00% Апельсины Сумма по полю Стоимость 2706 5089 5900 13695 Доля от суммы по строке 19,76% 37,16% 43,08% 100,00% Мандарины Сумма по полю Стоимость 6430 3520 7824 17774 Доля от суммы по строке 36,18% 19,80% 44,02% 100,00% Бананы Сумма по полю Стоимость 3027 2262 957 6246 Доля от суммы по строке 48,46% 36,22% 15,32% 100,00% Яблоки Сумма по полю Стоимость 1672 3190 12638 17500 Доля от суммы по строке 9,55% 18,23% 72,22% 100,00% Итог Сумма по полю Стоимость 15155 15150 29024 59329 Итог Доля от суммы по строке 25,54% 25,54% 48,92% 100,00% Рис. 21. Значения стоимости, выраженные в числах и процентах Доля от общей суммы Фирма Дата поступления Евразия+ Мангнолия Скиф Общий итог янв 11,33% 3,25% 21,63% 36,21% фев 14,22% 8,53% 6,16% 28,90% мар 0,00% 7,77% 7,65% 15,42% окт 0,00% 5,99% 13,48% 19,47% Общий итог 25,54% 25,54% 48,92% 100,00% Рис. 22. Значения стоимости в процентах от общего итога Проанализируйте полученную таблицу. Замените дополнительное вычисление на «Долю от суммы по столбцу». Проанализируйте полученные изменения. Пример 2 Постройте сводную таблицу по исходным данным табл. 2 приложения 1, ото- бражающую ежемесячную стоимость товара, полученного от каждой фирмы. Для этого поместите поле «Дата поступления» в области столбцов, а поле «Фирма» – в области строк. В полученной таблице выполните группировку по дате, сгруппировав данные по месяцам. Выполните подбор ширины столбцов (см. рис. 23). Сумма по полю Стоимость Дата поступления Фирма янв фев мар окт Общий итог Евразия+ 1339 1875,7 3214,7 Мангнолия 369 1078 989,6 1070,3 3506,9 Скиф 2277 877,2 1110,9 2266 6531,1 Общий итог 3985 3830,9 2100,5 3336,3 13252,7 Рис. 23. Сводная таблица для примера 2 18 Выполните дополнительные вычисления в поле «Стоимость» со следующими параметрами: дополнительные вычисления: «Отличие»; поле: «Дата поступления»; элемент: «Назад»; имя: «Отличие от предыдущего». Отличие от предыдущего Дата поступления Фирма янв фев мар окт Общий итог Евразия+ 536,7 -1875,7 0 Мангнолия 709 -88,4 80,7 Скиф -1399,8 233,7 1155,1 Общий итог -154,1 -1730,4 1235,8 Рис. 24. Значения стоимости по сравнению с предыдущим месяцем Сравните с таблицей на рис. 23. При таких вычислениях из значений февраля вычитаются значения января и т.д. Измените базовый элемент в дополнительных вычислениях, указав в поле «Элемент» вместо значения «назад» значение «ян- варь». В данном случае все элементы будут сравниваться со значением января. (Вспомните известную формулировку: «По сравнению с январем прирост (снижение) составил (о)…»!) Отличие от января Дата поступления Фирма янв фев мар окт Общий итог Евразия+ 1713 -8434 0 Мангнолия 3130 -453 -1054 Скиф -9178 884 3458 Общий итог -4335 -8003 2404 Рис. 25. Значения стоимости по сравнению с январем В таблицах на рис. 24 и рис. 25 столбец «Общий итог» по строкам теряет смысл, чтобы отменить его отображение в таблице, необходимо открыть окно «П АРАМЕТРЫ СВОДНОЙ ТАБЛИЦЫ » (команда Д АННЫЕ – С ВОДНАЯ ТАБЛИЦА , кнопка Параметры) и снять флажок «общая сумма по строкам» (см. рис. 9 на с. 9). Добавьте еще одно поле «Стоимость» в область данных и установите допол- нительное вычисление как «С нарастающим итогом в поле» «Дата поступления». Проанализируйте результат. 19 Для дополнительных вычислений применяются следующие виды операций: Отличие Значения ячеек области данных отображаются в виде разности с заданным элементом, указан- ным в списках «поле» и «элемент». Доля Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках «поле» и «элемент». Приведенное отличие Значения ячеек области данных отображаются в виде разности с заданным элементом, указан- ным в списках «поле» и «элемент», нормиро- ванной к значению этого элемента. С нарастающим итогом в поле Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы ко- торого будут отображаться в нарастающем ито- ге. Доля от суммы по строке В отчете сводной таблицы значения ячеек каж- дой строки отображаются в процентах от итого- вого значения по строке. В отчете сводной диа- граммы значения отображаются в процентах от итогового значения по категории. Доля от суммы по столбцу В отчете сводной таблицы значения ячеек каж- дого столбца отображаются в процентах от ито- гового значения по столбцу. В отчете сводной диаграммы значения отображаются в процентах от итогового значения по ряду. Доля от общей суммы В отчете сводной таблицы значения ячеек в об- ласти данных отображаются в процентах от об- щего итога по всем значениям в отчете. Индекс При определении значений ячеек области дан- ных используется следующий алгоритм: ((Значение в ячейке) x (Общий итог)) / ((Итог строки) x (Итог столбца)). 20 СОЗДАНИЕ ВЫЧИСЛЯЕМЫХ ЭЛЕМЕНТОВ И ПОЛЕЙ При анализе данных с помощью сводной таблицы иногда возникает необхо- димость получения дополнительного результата, используя отдельные элементы поля или поля сводной таблицы. Например, определить общее количество для двух фирм, не теряя других значений и не изменяя структуры таблицы. Рассмотрим простейший вариант создания вычисляемого элемента поля. 1. Постройте сводную таблицу, как на рис. 20 (до проведения дополнитель- ных вычислений). 2. Щелкните на названии любого элемента в области столбцов (например, «Скиф»). 3. Откройте контекстное меню и выберите команду Ф ОРМУЛЫ – В ЫЧИСЛЯЕМЫЙ ОБЪЕКТ 4. Укажите имя элемента «Восток» и создайте формулу для вычислений, используя доступные элементы полей и знаки арифметических операций. Формула может иметь, например, вид: =Скиф+ Мангнолия. В области столбцов появится еще один элемент с заданным Вами названием и вычислением. Общий итог по строкам после этой операции будет отображать сумму всех элементов, в том числе и вновь созданного, поэтому отображение об- щих итогов по строкам было отменено (рис. 26). Сумма по полю стоимость Фирма Наименование Евразия+ Мангнолия Скиф Восток Апельсины 725,7 1092,4 1721,5 2813,9 Бананы 723 574,2 181,5 755,7 Лимоны 396 313,5 495 808,5 Мандарины 1018 616 1913,9 2529,9 Яблоки 352 910,8 2219,2 3130 Общий итог 3214,7 3506,9 6531,1 10038 Рис. 26. Сводная таблица с вычисляемым элементом «Восток» В формулах, создаваемых для вычисляемых полей и вычисляемых элементов, можно использовать операторы и выражения, как в других формулах листа. Мож- но использовать константы и ссылаться на данные из отчета, но нельзя использо- вать ссылки на ячейки или имена. Также нельзя использовать функции, тре- бующие использования ссылок на ячейки, и функции обработки массивов. При создании вычисляемого элемента формула одинакова для всех полей строки, столбца и страницы по вертикали или по горизонтали в области данных сводной таблицы. Если установить рамку на ячейку, содержащую вычисляемый элемент поля, то созданная вами формула отобразится в строке формул. Впослед- ствии можно изменить формулу в отдельной ячейке или нескольких ячейках вы- числяемого поля. Чтобы изменить формулу: 1) выделите ячейку в строке или столбце вычисляемого элемента, в которой необходимо изменить формулу; 2) измените формулу в строке формул. 21 Например, если в сводной таблице рис. 26 создать вычисляемый элемент «Прогноз_Восток» с формулой =Восток*5%, то она первоначально будет одина- ковой для каждого наименования. Затем можно изменить формулу, например, для значения «Яблоки» ввести =Восток*10%. Происходит вычисление по формуле, заданной для каждого элемента, а затем полученные значения суммируются. Замечание. Если поля содержат вычисляемые элементы, нельзя изменять итоговую функцию. Итоги подводятся автоматически. Рассмотрим простейший вариант создания вычисляемого поля для определе- ния средней цены каждого наименования товара. 1. Постройте сводную таблицу, отражающую количество и стоимость каждого наименования товара (наименование расположите в области строк, а коли- чество и стоимость – в области данных). 2. В готовой сводной таблице на рабочем листе перетащите поле «Данные» в область столбцов. 3. Выделите поле «Сумма по полю стоимость» и выберите в контекстном ме- ню пункт «Ф ОРМУЛЫ – В ЫЧИСЛЯЕМОЕ ПОЛЕ ». Укажите имя поля «Средняя цена» и создайте формулу для вычислений, ис- пользуя доступные элементы полей и знаки арифметических операций (=Стоимость/Количество). В полученной таблице поле «Сумма по полю Средняя цена» можно переиме- новать, если слово «сумма» вводит вас в заблуждение (выделите ячейку и исполь- зуйте строку формул). Данные Наименование Сумма по полю Кол-во Сумма по полю Стоимость Сумма по полю Средняя_цена Лимоны 132 4114 31,17 Апельсины 531 13695 25,79 Мандарины 473 17774 37,58 Бананы 233 6246 26,81 Яблоки 566 17500 30,92 Общий итог 1935 59329 30,66 Рис. 27. Сводная таблица с вычисляемым полем «Средняя цена» Обратите внимание, какое значение отображается в строке «Общий итог» для поля «Средняя цена». Конечно, это не сумма значений, как для полей «Кол-во» и «Стоимость» и не среднее по данному полю. Для строки «Общий итог» работает формула, заданная для вычисляемого поля, т.е. =Стоимость/Количество. Эту особенность необходимо учитывать, если вы решили использовать при создании вычисляемого поля логические функции. 22 Добавим в сводную таблицу рис. 27 еще одно вычисляемое поле, например, «Заказ» с формулой, проверяющей количество товара: = ЕСЛИ('Кол-во' <300; 300; 100) Данные Наименование Сумма по полю Кол-во Сумма по полю Стоимость Сумма по полю Средняя цена Сумма по полю Заказ Лимоны 132 4114 31,17 300 Апельсины 531 13695 25,79 100 Мандарины 473 17774 37,58 100 Бананы 233 6246 26,81 300 Яблоки 566 17500 30,92 100 Общий итог 1935 59329 30,66 100 Рис. 28. Сводная таблица с логической функцией вычисляемого поля В результате имеем таблицу, как на рис. 28, в которой вместо ожидаемой сум- мы заказа в строке «Общий итог» видим результат работы функции ЕСЛИ для значений итоговой строки. В то же время, при добавлении вычисляемого элемента поля в сводную таб- лицу, подобную рис. 26, c аналогичной формулой, определяющей количество = ЕСЛИ('Евразия+' +Мангнолия +Скиф <400;300;200) получим правильную общую сумму заказа (см. рис. 29). Сумма по полю Кол-во Фирма Наименование Евразия+ Мангнолия Скиф Заказ Лимоны 44 33 55 300 Апельсины 123 187 221 200 Мандарины 164 88 221 200 Бананы 113 87 33 300 Яблоки 44 110 412 200 Общий итог 488 505 942 1200 Рис. 29. Вычисляемый элемент поля с логической функцией Рассмотрим еще один пример создания вычисляемых полей. Пусть исходный список содержит значения стоимости, включающие в том числе некоторую сумму налога, например 20%. Необходимо выделить сумму налога из общей стоимости. Во-первых, постройте сводную таблицу, отражающую общую стоимость для каждой фирмы. Во-вторых, выделите поле «Сумма по полю стоимость» и в контекстном меню укажите команду Ф ОРМУЛЫ – В ЫЧИСЛЯЕМОЕ ПОЛЕ , откроется окно «В СТАВКА ВЫ- ЧИСЛЯЕМОГО ПОЛЯ ». Введите имя поля – «Налог», формула – «=Стоимость*0,2/1,2» и подтвердите действие. Аналогично создайте вычисляемое поле «Итого без налога» с формулой «=Стоимость – Налог». Последовательное преобразование таблицы показано на рис. 30. 23 Сумма по полю Стоимость Фирма Всего Евразия+ 15155 Мангнолия 15150 Скиф 29024 Общий итог 59329 Фирма Данные Всего Евразия+ Сумма по полю Стоимость 15155 Сумма по полю Налог 2526 Мангнолия Сумма по полю Стоимость 15150 Сумма по полю Налог 2525 Скиф Сумма по полю Стоимость 29024 Сумма по полю Налог 4837 Итог Сумма по полю Стоимость 59329 Итог Сумма по полю Налог 9888 Данные Фирма Сумма по полю Стоимость Сумма по полю Налог Сумма по полю Итого без налога Евразия+ 15155 2526 12629 Мангнолия 15150 2525 12625 Скиф 29024 4837 24187 Общий итог 59329 9888 49441 Рис. 30. Добавление вычисляемых полей в сводную таблицу Можно расположить в области столбцов поле «Дата» и сгруппировать по ме- сяцам, тогда таблица примет вид, как на рис. 31. Дата поступления Фирма Данные янв фев мар окт Общий итог Евразия+ Итого Стоимость 6721 8434 15155 Итого Налог 1120 1406 0 0 2526 Итого без налога 5601 7028 0 0 12629 Мангнолия Итого Стоимость 1930 5060 4607 3553 15150 Итого Налог 322 843 768 592 2525 Итого без налога 1608 4217 3839 2961 12625 Скиф Итого Стоимость 12833 3655 4539 7997 29024 Итого Налог 2139 609 757 1333 4837 Итого без налога 10694 3046 3783 6664 24187 Итог Итого Стоимость 21484 17149 9146 11550 59329 Итог Итого Налог 3581 2858 1524 1925 9888 Итог Итого без налога 17903 14291 7622 9625 49441 Рис. 31. Ежемесячное представление данных для каждой фирмы |