<< Предыдущая

стр. 65
(из 106 стр.)

ОГЛАВЛЕНИЕ

Следующая >>

B2
; ;
1 С/ ; : i 'W:^ =PD'. 1 Е 1 - = FT--'i T1
'
Скпэд №1
1i
вара Ь; ; ^йин^^йэмерадния |Ц9иа (ру(1Наедйф
••q
•. . 2Й12 --r-^HSS
3
; :
• . S Кой;;ите^.. ^'го 12 штук! 4U| "˜25
4
Щ к. .р. | к
'. •. . .:..!•.;.'! 36451 46
5
1
" 7 Кон - .™пэ4щту«н 3
6
-. '. . г чЛЬ liU
[

9. _ _..._ . . . .L „ ;„„ _.. . . _ . "-,"v
ic
VI ! ;
"::' ; , • ! -.
H]
Ж1
и ^ шЁ
шш н ^ 3 ,f Ц
14 4 * *Л
Готово Cy™a=BQ4S ' SUM ^,




1 Лэнных по расположению

Запомните
:
Чтобы провесмконсолидацию до.нн,ых ПО расположен ию^жек.укажите верхнюю.
левую ячейку конечной области, где должны быть помещены консолидированные
данные, и выберите команду Консолидация (Consolidate) в меню Данные (Data),

Диапазоны, данные которых консолидируются и помешаются в указанный
конечный диапазон, называются исходными областями. Исходные области мо-
гут располагаться на любом листе или книге, на других открытых листах или
книгах, а также на листах Lotus 1-2-3.
Если во всех ведомостях количество упаковок Рыбные продукты приводится
в ячейке F3, то при консолидации в итоговой ведомости будут обобщены зна-
чения, хранящиеся в этой ячейке во всех ведомостях. Области консолидации

Рис. 18.15 Консолидация
Диалоговое окно Консолидация ^; ^ ' •: •- " " ' '
И ;
zJ-
Суммэ
• Ссылка \

. S ад^-- ,]=
• Ожок диапазонов :
J __Довавит> |
Г Цамтъ 1
v



№то1ъа»*1ъ в качестве имен -
• Г˜ подтсч Ёвраней строш
Г" эиачвч«1 аввогп стсийцл

Г" Создавать связи с исдодчьм! да**»*
СК Закрыть ^ •
Анализ данных в Excel 2003 357

можно задать либо трехмерными формулами, либо в поле Ссылка (Reference)
(рис. 18.15).
В диалоговом окне Консолидация в раскрывающемся списке Функция (Func-
tion) выберите итоговую функцию, которую следует использовать для обработ-
ки данных, например, Сумма (Sum), если будут суммироваться значения. Опи-
сание функций, приведенных в списке, дано в таблице 18.3.
Таблица 18.3. Итоговые функции, используемые при построении сводных таблиц
и сеодных диаграмм
Операция Назначение
Сумма (Sum) Вычисление суммы чисел, хранящихся в исходных
ячейках. Эта операция используется по умолчанию
для подведения итогов по числовым полям
Количество (Count) Количество записей или строк данных. Эта
операция используется по умолчанию для
подведения итогов по нечисловым полям
Среднее (Average) Вычисление среднего числа по данным,
хранящимся в исходных ячейках
Максимум (Мах) Определение максимального числа по данным,
хранящимся в исходных ячейках
Минимум (Min) Определение минимального числа по данным,
хранящимся в исходных ячейках
Произведение (Product) Вычисление произведение чисел, хранящихся
в исходных ячейках
Количество чисел (Count Nums) Количество записей или строк, содержащих числа
Смещенное отклонение (StdDev) Смешенная оценка стандартного отклонения
генеральной совокупности по выборке данных
Несмешенное отклонение Вычисление стандартного отклонения генеральной
совокупности по выборке данных, хранящихся
(StdDevp)
в исходных ячейках
Смещенная оценка дисперсии генеральной
Смещенная дисперсия (Var)
совокупности по выборке данных
Несмещенная оценка дисперсии генеральной
Несмешенная дисперсия (Varp)
совокупности по выборке данных

После установки курсора в поле Ссыпка (Reference) введите ссылку на пер-
вый диапазон данных, который консолидируется в указанный конечный диа-
пазон. Ссылку введите вручную или, если лист, содержащий новую исходную
область, является текущим, выделите на нем исходную область. Если исходные
области и область назначения находятся на разных листах, используйте имя
листа и имя или ссылку на диапазон. Чтобы убрать диалоговое окно Консолида-
ция (Consolidate) на время выбора исходной области, нажмите кнопку Свернуть
диалоговое окно (Collapse dialog) в правой части поля. Повторное нажатие на эту
кнопку восстанавливает окно.
Если исходные данные находятся в другой книге, которая в данный момент
закрыта, нажмите кнопку Обзор (Browse) и выберите книгу. Путь к выбранной
ссылке отобразится в поле Ссылка (Reference). Затем добавьте ссылку.
Нажмите кнопку Дoбaвumь(Add). Введенная ссылка отобразится в окне Спи-
сок диапазонов (АН references). Повторите эту операцию для всех консолидируе-
мых исходных областей.
Чтобы автоматически обновлять итоговую таблицу при изменении источни-
ков данных, установите флажок Создавать связи с исходными данными (Creole
3S8

links to source data). Связи нельзя использовать, если исходная область и область
назначения находятся на одном листе. После установки связей нельзя добав-
лять новые исходные области и изменять исходные области, уже входящие
в консолидацию.
При консолидации данных по расположению заголовки категорий исход-
ных областей не копируются автоматически в область назначения. Если в об-
ласти назначения требуется разместить заголовки, скопируйте или введите их
вручную.

Консолидация данных по категориям

Запомните
Чтобы провести консолидацию данных по расположению: *гчеек, укажите верхнюю
. .,-левую ячейку-конечной области, где дол^ьГбьяь^помещены^консолидир'ОВОнньй
данные, и выберите команду Консолидация (Consolidate) в меню Данные (Data).

Выберите из раскрывающегося списка Функция (Function) функцию, кото-
рую следует использовать для обработки данных (рис. 18.15). Используемые
итоговые функции приведены в таблице 18.3. Введите исходную область консо-
лидируемых данных в поле Ссылка (Reference). Убедитесь, что исходная область
имеет заголовок.
Нажмите кнопку Добавить (Add) для добавления диапазона к списку исход-
ных диапазонов консолидации. Повторите эту операцию для всех консолиди-
руемых исходных областей. В наборе флажков Использовать в качестве имен
(Use labels in) установите флажки, соответствующие расположению в исходной
области заголовков: в верхней строке, в левом столбце или в верхней строке
и в левом столбце одновременно.
Чтобы автоматически обновлять итоговую таблицу при изменении источни-
ков данных, установите флажок Создавать связи с исходными данными (Create
links to source data). Связи нельзя использовать, если исходная область и область
назначения находятся на одном листе. После установки связей нельзя добав-
лять новые исходные области и изменять исходные области, уже входящие
в консолидацию.

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

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

Создание сводной таблицы
В качестве примера рассмотрим создание сводной таблицы, позволяющей
на основе таблиц с исходными данными выполнить анализ продажи опреде-
ленных товаров в различных городах России. В книге, приведенной на
рис. 18.16, показана продажа нескольких моделей автомобилей: Волга, Жигули,
Ока в разных городах России: в Москве, Саратове и Туле. Каждый город пока-
зан на отдельном листе. Предполагается, что сводные таблицы составляются по
четырем месяцам: январь, февраль, март и апрель. Таблицы отформатированы
с использованием команды Автоформат (AutoFormat) в меню Формат (Format).
Выбран образец с подписью Простой (Simple).

Рис. 18.16 . Й1Х1
S Microsoft Excel - Кнмгаб
Исходный список для составления
сводной таблицы .




модель январь февраль март апрель




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

Запомните
. Чтобы создать сводную таблицу, выберите кома иду Сводная таблица (Plyot Table
and PivotChart Report) в меню Данные (Data). На экране отобразится диалоговое;
окно Мастер сводных таблиц и диаграмм — шаг 1 из 3 (PivotTabte and PivotCht
> 'Wbart--'Step ГоГЗ) (рис. 18.17).

Положением переключателя в группе Создашь таблицу на основе данных, на-
ходящихся: (Create Pivot table from data in:) установите переключатель в положе-
Глава 18
360

ние: в нескольких диапазонах консолидации (Multiple consolidation ranges), так как
источники данных для создания сводной таблицы, расположены на разных
листе Excel.
Назначение других положений переключателя:
• в списке или базе данных Microsoft Office Excel (Microsoft Office Excel list or
Database) — позволяет создать отчет сводной таблицы или сводной диа-
граммы по данным строк или столбцов с подписями, расположенных на
одном листе Excel;
• во внешнем источнике данных (External data source) — позволяет создать отчет
сводной таблицы или сводной диаграммы по данным внешнего файла или
базы данных, например, Microsoft Access, SQL Server, Paradox;
• в нескольких диапазонах консолидации — позволяет создать отчет сводной
таблицы или сводной диаграммы по данным строк или столбцов с подпи-
сями, расположенных в нескольких диапазонах консолидации;
• в другой сводной таблице или сводной диаграмме (Another PivotTable report or
Pivot Chart report) ˜ используется для создания отчета сводной таблицы
или сводной диаграммы по данным другого отчета сводной таблицы в ак-
тивной книге.

Рис. 18.17 Wactep сводных таблиц и диаграмм • шаг 1 из 3
Окно мастера сводных таблиц Создаю te6nntf.ua основе данных, негодящийся:


во внешнем исто'+шив данных
в несюльии диапазонах консолидации




Вид [задаваемого отчету
!
* вводная таблица- -
<˜ сводная диаграмма Косвенной га




<< Предыдущая

стр. 65
(из 106 стр.)

ОГЛАВЛЕНИЕ

Следующая >>