3.4. Консолидация данных

Консолидировать следующие данные, функции консолидации - сумма (без создания связи с исходными данными), минимум (создать связь с исходными данными). Данные для консолидации:

Продажи машин					Продажи запчастей
ФИО	Италия	Испания	США		ФИО	Италия	Испания	США
Иванов	603	460	351		Иванов	664	450	314
Петров	139	923	828		Петров	413	200	700
Сидоров	958	10	593		Сидоров	749	101	900

Теоретический материал для выполнения задания:

Консолидация позволяет свести воедино данные из нескольких таблиц. Например, результаты консолидации по приведенным в задании таблицам будет выглядеть следующим образом:

Всего продаж (по итогам продаж машин и запчастей )					Минимум продаж (из соответствующих значений в таблицах машин и запчастей )
	Италия	Испания	США			Италия	Испания	США
Иванов	1267	910	665		Иванов	603	450	314
Петров	552	1123	1528		Петров	139	200	700
Сидоров	1707	111	1493		Сидоров	749	10	593

3.5. Сводные таблицы

Создать сводную таблицу на основании следующих данных, и имеющую вид, представленный ниже.

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

Создать источник данных для построения сводной таблицы в виде таблицы Access и создать в Excel сводную таблицу на основании базы данных Access.

Данные для сводной таблицы

ФИО	Квартал	Изделие	Италия	Испания	США
Иванов	1 кв.	Сталь	100	400	300
Петров	1 кв.	Медь	200	900	800
Иванов	2 кв.	Медь	500	400	300
Петров	2 кв.	Медь	400	200	700
Сидоров	2 кв.	Медь	700	100	900
Иванов	3 кв.	Сталь	300	400	300
Сидоров	3 кв.	Сталь	900	100	500

Сводная таблица

		Медь		Медь	Сталь		Сталь	Общий итог
ФИО	Данные	1 кв.	2 кв.	Всего	1 кв.	3 кв.	Всего
Иванов	Сумма по Италии		500	500	100	300	400	900
	Сумма по Испании		400	400	400	400	800	1200
	Сумма по США		300	300	300	300	600	900
	Страны - среднее (вычисляемое поле)	0	400	400	267	333	600	1000
Петров	Сумма по Италии	200	400	600				600
	Сумма по Испании	900	200	1100				1100
	Сумма по США	800	700	1500				1500
	Страны - среднее (вычисляемое поле)	633	433	1067	0	0	0	1067
Сидоров	Сумма по Италии		700	700		900	900	1600
	Сумма по Испании		100	100		100	100	200
	Сумма по США		900	900		500	500	1400
	Страны - среднее (вычисляемое поле)	0	567	567	0	500	500	1067
Итог Сумма по Италии		200	1600	1800	100	1200	1300	3100
Итог Сумма по Испании		900	700	1600	400	500	900	2500
Итог Сумма по США		800	1900	2700	300	800	1100	3800
Итог Страны - среднее		633	1400	2033	267	833	1100	3133

Теоретический материал для выполнения задания:

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

Создание таблицы: Данные\Сводная таблица\В списке или базе данных Excel, указать диапазон с данными для консолидации, перетащить кнопку “ФИО” в поле “Строка”, затем кнопку “Изделие” в поле “Столбец”, затем кнопку “Квартал” в поле “Столбец”, кнопки “Италия”, “Испания” и “США” в поле “Данные”. Дважды щелкнуть по кнопкам в поле “Данные” и выбрать операцию “Сумма”, “Максимум”, “Минимум”, “Среднее” и т.д. Выбрать куда будет помещена сводная таблица - на новый или текущий лист. В результате будет построена сводная таблица следующего вида (название столбцов и строк таблицы отредактированы, для того чтобы “Медь” и “Сталь” были по центру надо в КМ: Параметры установить флажок “Объеденить ячейки заголовков”):

При изменении источника данных сводная таблица автоматически не обновляется. Для этого необходимо выделить таблицу и дать команду Данные\Обновить данные. Внешний вид сводной таблицы также можно легко изменять. Для этого достаточно прямо в сводной таблице перетащить поле “Квартал” на место строк, а поле “ФИО” на место столбцов. Чтобы изменить операции над данными (например, минимум вместо суммирования) надо выделить это поле (например, поле “Сумма по Италии”) и в КМ: Поле... установить новую операцию. Для более значительного изменения сводной таблицы можно вызвать мастер сводных таблиц. Для этого, выделив ячейку внутри сводной таблицы, дать команду Данные\Сводная таблица - появится мастер, где можно производить необходимые изменения. Или тоже самое в КМ: Мастера ...

В сводную таблицу можно добавить и собственное, вычисляемое поле (см. поле Страны - среднее в таблице). Для этого КМ:Формулы\ Вычисляемое поле и в окне “Формула” ввести формулу, используя кнопку “Добавить поле”. Например: = (Италия + 'Испания' + 'США') / 3.

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

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

Сводная таблица на основании данных, находящихся во внешнем источнике данных (таблица или запрос Access, лист или имя из других книг Excel, текстовых файлов и т.д.) строится также, как и обычная. Отбор исходных данных из внешней базы также, как в пункте “Использование внешних данных Microsoft Access в файлах Microsoft Excel”.