Материал: 64
81
15. Создайте по данным таблицы круговую объёмную диаграмму в соответствии с представленным образцом:
16.Вставьте в рабочую книгу новый рабочий лист и присвойте ему имя
Диаграмма2.
17.Скопируйте на рабочий лист Диаграмма2 всю информацию с рабочего листа Итоги3.
18.Удалите на рабочем листе Диаграмма2 построенную ранее круговую объёмную диаграмму.
19.Создайте на рабочем листе Диаграмма2 круговую диаграмму суммарной стоимости товаров, поставленных каждого числа, с вторичной гистограммой, детализирующей поставки 8 мая 2012 г. (для решения задачи в таблице
спромежуточными итогами следует отобразить записи всех поставок товаров за указанную дату):
Технологии построения круговой диаграммы с вторичной гистограммой подробно рассмотрены в параграфе Создание комбинированных диаграмм
пособия.
82
20.Вставьте в рабочую книгу новый рабочий лист и присвойте ему имя
Диаграмма3.
21.Скопируйте на рабочий лист Диаграмма3 всю информацию с рабочего листа Сорт1.
22.С помощью автоматизированного подведения итогов рассчитайте на рабочем листе Диаграмма3 суммарные значения количества и стоимости каждого товара.
23.Создайте на рабочем листе Диаграмма3 комбинированную диаграмму, предусматривающую отображение суммарных значений стоимости (гистограмма) и количества (график с маркерами) товаров, поступивших в магазин:
83
Лабораторная работа № 6 Консолидация данных
1. Создайте на одном рабочем листе MS Excel таблицы с данными о продаже строительных материалов магазинами торговой фирмы по следующему образцу:
Магазин "Стройка" Магазин "Мастер"
Товар |
Количество |
Стоимость, руб. |
|
|
|
Линолеум |
1 000 |
280 000 |
|
|
|
Паркет |
850 |
365 500 |
|
|
|
Грунтовка |
600 |
177 600 |
|
|
|
Шпатлёвка |
189 |
105 840 |
|
|
|
Штукатурка |
800 |
164 800 |
|
|
|
Товар |
Количество |
Стоимость, руб. |
|
|
|
Линолеум |
1 250 |
350 000 |
|
|
|
Паркет |
900 |
387 000 |
|
|
|
Грунтовка |
750 |
222 000 |
|
|
|
Шпатлёвка |
200 |
112 000 |
|
|
|
Штукатурка |
700 |
144 200 |
|
|
|
Магазин "Сервис"
Товар |
Количество |
Стоимость, руб. |
|
|
|
Линолеум |
1 500 |
420 000 |
|
|
|
Паркет |
1 000 |
430 000 |
|
|
|
Грунтовка |
750 |
222 000 |
|
|
|
Шпатлёвка |
149 |
83 440 |
|
|
|
Штукатурка |
440 |
90 640 |
|
|
|
2.Присвойте рабочему листу имя Исходный.
3.Используя консолидацию данных по расположению, создайте на этом же рабочем листе таблицу суммарных значений количества и стоимости проданных товаров для всей фирмы (флажок Создавать связи с исходными данными в диалоговом окне Консолидация сбросьте). Скопируйте в консолидированную таблицу названия строк и столбцов любой из исходных таблиц:
Товар |
Количество |
Стоимость, руб. |
|
|
|
Линолеум |
3 750 |
1 050 000 |
|
|
|
Паркет |
2 750 |
1 182 500 |
|
|
|
Грунтовка |
2 100 |
621 600 |
|
|
|
Шпатлёвка |
538 |
301 280 |
|
|
|
Штукатурка |
1 940 |
399 640 |
|
|
|
84
4.Измените одно-два значения данных в исходных таблицах. Проанализируйте, изменилась ли информация в консолидированной таблице.
5.Восстановите значения данных в исходных таблицах.
6.Создайте рабочий лист с названием Фирма.
7.Используя консолидацию данных по категориям, получите на рабочем листе Фирма таблицу с максимальными значениями количества и стоимости проданных товаров для всех магазинов фирмы (установите при этом флажок
Создавать связи с исходными данными):
Товар |
Количество |
Стоимость, руб. |
|
|
|
Линолеум |
1 500 |
420 000 |
|
|
|
Паркет |
1 000 |
430 000 |
|
|
|
Грунтовка |
750 |
222 000 |
|
|
|
Шпатлёвка |
200 |
112 000 |
|
|
|
Штукатурка |
800 |
164 800 |
|
|
|
8.Измените одно два значения данных в исходных таблицах. Проанализируйте, изменилась ли информация в итоговой, консолидированной таблице.
9.Восстановите значения данных в исходных таблицах.
10.Скопируйте сведения о продаже товаров каждым магазином на отдельный рабочий лист. Присвойте каждому листу имя, соответствующее названию магазина.
11.Измените заголовки строк и столбцов в таблицах, расположенных на рабочих листах Стройка, Мастер, Сервис:
Магазин "Стройка" Магазин "Мастер"
Товар |
Кол-во, ед. |
Стоимость, руб. |
|
|
|
Лин. Ютекс |
1 000 |
280 000 |
|
|
|
Паркет |
850 |
365 500 |
|
|
|
Грунтовка |
600 |
177 600 |
|
|
|
Шп. базовая |
189 |
105 840 |
|
|
|
Штукатурка |
800 |
164 800 |
|
|
|
Товар |
Количество |
Ст-ть, руб. |
|
|
|
Лин. Таркетт |
1 250 |
350 000 |
|
|
|
Паркет |
900 |
387 000 |
|
|
|
Грунтовка |
750 |
222 000 |
|
|
|
Шп. ПВА |
200 |
112 000 |
|
|
|
Шт. Ротбанд |
700 |
144 200 |
|
|
|
85
Магазин "Сервис"
Товар |
Кол-во |
Стоим., руб. |
|
|
|
Лин. Синтерос |
1 500 |
420 000 |
|
|
|
Паркет |
1 000 |
430 000 |
|
|
|
Грунтовка |
750 |
222 000 |
|
|
|
Шп. KNAUF |
149 |
83 440 |
|
|
|
Шт. Старатели |
440 |
90 640 |
|
|
|
12.Создайте рабочий лист с названием Продажи.
13.Используя консолидацию данных, расположенных на рабочих листах Стройка, Мастер, Сервис, по положению, создайте на рабочем листе Продажи таблицу со средними значениями количества и стоимости проданных товаров по всем магазинам фирмы. Введите названия строк и столбцов консолидированной таблицы, предусмотрите вывод средних значений количества товаров с точностью до целых, стоимости – в финансовом формате с точностью до двух десятичных знаков после запятой:
Товар |
Кол-во, ед. |
Ст-ть, руб. |
|
|
|
Линолеум (в ассортименте) |
1 250 |
350 000,00 |
|
|
|
Паркет |
917 |
394 166,67 |
|
|
|
Грунтовка |
700 |
207 200,00 |
|
|
|
Шпатлёвка (в ассортименте) |
179 |
100 426,67 |
|
|
|
Штукатурка (в ассортименте) |
647 |
133 213,33 |
|
|
|
14. Измените данные, заголовки строк и столбцов в таблицах, расположенных на рабочих листах Стройка, Мастер, Сервис:
Магазин "Стройка"
Товар |
Количество |
Стоимость, руб. |
|
|
|
Лин. Ютекс |
1 000 |
280 000 |
|
|
|
Паркет |
850 |
365 500 |
|
|
|
Грунтовка |
600 |
177 600 |
|
|
|
Шп. базовая |
189 |
105 840 |
|
|
|
Лин. Таркетт |
300 |
84 000 |
|
|
|
Лин. Синтерос |
400 |
112 000 |
|
|
|
Шп. KNAUF |
100 |
56 000 |
|
|
|
Магазин "Мастер"
Товар |
Количество |
Стоимость, руб. |
|
|
|
Лин. Таркетт |
1 250 |
350 000 |
|
|
|
Паркет |
900 |
387 000 |
|
|
|
Грунтовка |
750 |
222 000 |
|
|
|
Шп. ПВА |
200 |
112 000 |
|
|
|
Шт. Ротбанд |
700 |
144 200 |
|
|
|
Лин. Ютекс |
500 |
140 000 |
|
|
|
Лин. Синтерос |
200 |
56 000 |
|
|
|