Консолидация данных (с пояснениями)

ЛАБОРАТОРНАЯ РАБОТА № 8

1. Создайте на одном рабочем листе MS Excel таблицы с данными о продаже строительных материалов магазинами торговой фирмы по следующему образцу:

Магазин "Сервис"			Магазин "Мастер"
Товар	Количество	Стоимость, руб		Товар	Количество	Стоимость, руб
Линолеум				Линолеум
Паркет				Паркет
Грунтовка				Грунтовка
Шпатлевка				Шпатлевка
Штукатурка				Штукатурка
Итого:				Итого:
Магазин "Стройка"
Товар	Количество	Стоимость, руб
Линолеум
Паркет
Грунтовка
Шпатлевка
Штукатурка
Итого:

Рис1. Лист MS Excel с таблицами данных о продаже строительных материалов

2. Используя консолидацию данных (Данные®Консолидация…) по категориям, получите на этом же рабочем листе суммарные значения количества и стоимости проданных товаров для всей фирмы (флажок Создавать связи с исходными данными сбросьте):

	Количество	Стоимость, руб
Линолеум
Паркет
Грунтовка
Шпатлевка
Штукатурка
Итого:

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

б). Команды: Данные-Консолидация. В ДО Консолидация выбираем: Функция-сумма.

в). В поле Ссылка-Консолидация ссылка через кнопку Добавить вводим диапазоны данных (3 таблицы с данными о продаже строительных материалов).

г). Флажки Создавать связи с исходными данными и значения левого столбца сбрасываем.

д). Устанавливаем флажок: подписи верхней строки

е). ОК!

ж). Снова Данные®Консолидация

з). Устанавливаем второй флажок: значения левого столбца

е). ОК!

3. Измените одно-два значения данных в исходных таблицах. Проанализируйте, изменилась ли информация в итоговой, консолидированной таблице.

4. Восстановите значения данных в исходных таблицах.

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

6. Создайте рабочий лист с названием Фирма.

7. Используя консолидацию данных по категориям, получите на рабочем листе Фирма таблицу с максимальными значениями количества и стоимости проданных товаров для всех магазинов фирмы (установите при этом флажок Создавать связи с исходными данными):

Команды: Данные-Консолидация – Максимум.

Через кнопку Добавить вводим диапазоны данных (3 таблицы с данными о продаже строительных материалов).

Устанавливаем флажок: подписи верхней строки ОК!

Снова Данные®Консолидация. Устанавливаем второй флажок: значения левого столбца. ОК!

Опять Данные®Консолидация. Устанавливаем флажок Создавать связи с исходными данными.

	Количество	Стоимость, руб
Линолеум
Паркет
Грунтовка
Шпатлевка
Штукатурка
Итого:

Магазин "Сервис"	Магазин "Мастер"	Магазин "Стройка"
Линолеум	Лин. Синтерос	Линолеум	Лин. Таркетт	Линолеум	Лин. Ютекс
Шпатлевка	Шп. KNAUF	Шпатлевка	Шп. ПВА	Шпатлевка	Шп. базовая
Штукатурка	Шт. Старатели	Штукатурка	Шт. Ротбанд	Штукатурка	Гипсовая
Количество	Кол-во	Стоимость, руб	Ст-ть, руб	Количество	Кол-во, ед

8. Измените одно-два значения данных в исходных таблицах. Проанализируйте, изменилась ли информация в итоговой, консолидированной таблице.

9. Восстановите значения данных в исходных таблицах.

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

11. Создайте рабочий лист с названием Отчет о продажах.

12.. Используя консолидацию данных по положению, получите на рабочем листе Отчет о продажах таблицу со средними значениями количества и стоимости проданных товаров по всем магазинам фирмы. Введите заголовки строк и столбцов по предлагаемой схеме, предусмотрите вывод средних значений количества товаров до целых, стоимости – до двух десятичных знаков после запятой:

Товар	Кол-во, ед.	Стоим., руб
Линолеум (в ассортименте)		350000,00
Паркет		394166,67
Грунтовка		207200,00
Шпатлевка (в ассортименте)		100426,67
Штукатурка (в ассортименте)		133213,33
Итого:		1185006,67

Дата добавления: 2014-01-15