Лабораторная работа 3.9. Сводные таблицы
Оценка 4.6

Лабораторная работа 3.9. Сводные таблицы

Оценка 4.6
doc
13.05.2020
Лабораторная работа 3.9. Сводные таблицы
184. Лабораторная работа 3.9. Сводные таблицы.doc

Лабораторная работа 3.9. Сводные таблицы

 

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

 

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

 

1) выручку для каждого магазина;

2) выручку от продажи товаров каждого наименования;

3) общую выручку по всем магазинам;

4) выручку от продажи ручек (шариковая, гелевая, перьевая) для каждого магазина;

5) общую выручку от продажи ручек.

 

Для решения задачи дополните исходную таблицу столбцом «Выручка» и воспользуйтесь сводной таблицей, которую разместите в том же файле на том же листе.

 

 

A

B

C

D

1

Магазин

Наименование

Кол-во

Цена

2

№ 1

Карандаш

70

4,50р.

3

№ 1

Тетрадь

55

12,00р.

4

№ 1

Шариковая ручка

45

5,50р.

5

№ 2

Карандаш

70

4,50р.

6

№ 2

Тетрадь

75

12,00р.

7

№ 2

Шариковая ручка

40

5,50р.

8

№ 2

Гелевая ручка

90

7,00р.

9

Канцлер

Перьевая ручка

25

15,00р.

10

Канцлер

Тетрадь

30

12,00р.

11

Канцлер

Шариковая ручка

55

5,50р.

12

Канцлер

Гелевая ручка

60

7,00р.

 

Рис. 3.46. Исходная таблица для задания 1

 

Выполнение:

 

1.1 В книгу Лабораторные.xls добавьте новый лист с названием Сводные таблицы. Внесите на этот лист исходные данные (рис. 3.46).

 

1.2 В ячейку Е1 введите заголовок столбца Выручка, в ячейку Е2 введите формулу =С2*D2 и скопируйте ее с помощью маркера заполнения в остальные ячейки столбца. Таблица примет вид (рис. 3.47).

 

 

A

B

C

D

E

1

Магазин

Наименование

Кол-во

Цена

Выручка

2

№ 1

Карандаш

70

4,50р.

315,00р.

3

№ 1

Тетрадь

55

12,00р.

660,00р.

4

№ 1

Шариковая ручка

45

5,50р.

247,50р.

5

№ 2

Карандаш

70

4,50р.

315,00р.

6

№ 2

Тетрадь

75

12,00р.

900,00р.

7

№ 2

Шариковая ручка

40

5,50р.

220,00р.

8

№ 2

Гелевая ручка

90

7,00р.

630,00р.

9

Канцлер

Перьевая ручка

25

15,00р.

375,00р.

10

Канцлер

Тетрадь

30

12,00р.

360,00р.

11

Канцлер

Шариковая ручка

55

5,50р.

302,50р.

12

Канцлер

Гелевая ручка

60

7,00р.

420,00р.

 

Рис. 3.47. Дополненная таблица для задания 1

 

1.3 Вызов мастера сводных таблиц: сделайте активной какую-либо ячейку таблицы (всю таблицу выделять необязательно); войдите в меню Данные / Сводная таблица… В результате появится окно мастера сводных таблиц.

 

1.4 Определение типа исходных данных для сводной таблицы: оставьте вариант, предлагаемый по умолчанию («В списке или базе данных Microsoft Excel»); [Далее >]. Этот вариант используется в ситуациях, когда сводная таблица создается на основе одной исходной таблицы данных.

 

1.5 Определение диапазона с исходными данными: оставьте вариант по умолчанию (А1:Е12, то есть вся таблица); [Далее >].

 

1.6 Определение макета сводной таблицыExcel 2000 для перехода в режим определения макета надо дополнительно нажать кнопку [Макет…]: зацепите мышью элемент с текстом Магазин и перетащите его на область Столбец; затем перетащите элемент Наименование на область Строка и элемент Выручка на область Данные (в результате макет сводной таблицы примет указанный на рис. 3.48 вид); [Далее >] ([OK] в Excel 2000).

 

 

Рис. 3.48. Макет сводной таблицы

 

R Для удаления ошибочно размещенного элемента макета следует зацепить его мышью и «стащить» с таблицы.

 

1.7 Размещение сводной таблицы: выберите вариант Существующий лист и щелкните мышью на ячейке F1 (эта ячейка станет левым верхним углом созданной сводной таблицы); нажмите Готово. На листе будет сформирована сводная таблица, и одновременно на экране появится панель Сводные таблицы.

 

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

 

1.8 Форматирование ячеек в области данных: сделайте активной одну из ячеек в области данных сводной таблицы;  (панель Сводные таблицы); в появившемся окне Вычисление поля сводной таблицы нажмите Формат…, выберите в списке вариант Денежный и установите число десятичных знаков равным «2»; [OK], [OK]. В результате сводная таблица примет следующий вид (рис. 3.49).

 

Сумма по полю Выручка

Магазин

 

 

 

Наименование

№ 1

№ 2

Канцлер

Общий итог

Гелевая ручка

 

630,00р.

420,00р.

1 050,00р.

Карандаш

315,00р.

315,00р.

 

630,00р.

Перьевая ручка

 

 

375,00р.

375,00р.

Тетрадь

660,00р.

900,00р.

360,00р.

1 920,00р.

Шариковая ручка

247,50р.

220,00р.

302,50р.

770,00р.

Общий итог

1 222,50р.

2 065,00р.

1 457,50р.

4 745,00р.

 

Рис. 3.49. Вид сводной таблицы

 

Теперь можно ответить на первые три вопроса задания:

1) выручка для каждого магазина указана в нижней строке;

2) выручка по каждому наименованию - в правом столбце;

3) общая выручка - в правой нижней ячейке.

 

Для ответа на последние два вопроса надо выполнить группировку данных.

 

1.9 Группировка данных и скрытие деталей в сводной таблице: в столбце «Наименование» сводной таблицы выделите три несмежные ячейки, соответствующие товарам «ручка» (шариковая, гелевая, перьевая), щелкнув на них мышью при нажатой клавише Ctrl; меню Данные / Группа и структура / Группировать…Excel 97 достаточно нажать кнопку  Æ  на панели Сводные таблицы). В результате в сводной таблице появится еще один столбец с заголовком Наименование 2, в котором всем товарам «ручка» будет соответствовать одна ячейка Группа1. Для большей наглядности ее содержимое следует заменить на «Ручки» (отредактировав текст этой ячейки обычным образом). Теперь осталось скрыть детали в столбце Наименование: щелкните на заголовке Наименование 2;  (панель Сводные таблицы). В результате сводная таблица примет вид (рис. 3.50).

 

Сумма по полю Выручка

 

Магазин

 

 

 

Наименование 2

Наименование

№ 1

№ 2

Канцлер

Общий итог

Ручки

 

247,50р.

850,00р.

1 097,50р.

2 195,00р.

Карандаш

 

315,00р.

315,00р.

 

630,00р.

Тетрадь

 

660,00р.

900,00р.

360,00р.

1 920,00р.

Общий итог

 

1 222,50р.

2 065,00р.

1 457,50р.

4 745,00р.

 

Рис. 3.50. Сводная таблица с выполненной группировкой данных

и скрытыми деталями

 

Строка «Ручки» полученного варианта сводной таблицы позволяет ответить на два последних вопроса упражнения.

 

1.10 Возврат к исходному виду сводной таблицы (отображение деталей и разгруппировка данных):

- отображение деталей: щелкните на заголовке Наименование 2;  ;

- разгруппировка данных: не снимая выделения со столбца Наименование 2, выполните команду Данные / Группа и структура / Разруппировать…Excel 97 достаточно нажать кнопку  Å  на панели Сводные таблицы).

Сохраните измененный файл.

 

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

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

 

1) количество товаров, проданных в каждом магазине;

2) общее количество проданных товаров определенного наименования;

3) общее количество проданных товаров;

4) количество ручек (шариковая, гелевая, перьевая), проданных в каждом магазине;

5) общее количество проданных ручек.

 

 


Лабораторная работа 3.9. Сводные таблицы

Лабораторная работа 3.9. Сводные таблицы

Карандаш 70 4,50р. 315,00р

Карандаш 70 4,50р. 315,00р

Форматирование ячеек в области данных : сделайте активной одну из ячеек в области данных сводной таблицы; (панель

Форматирование ячеек в области данных : сделайте активной одну из ячеек в области данных сводной таблицы; (панель

Возврат к исходному виду сводной таблицы (отображение деталей и разгруппировка данных) : - отображение деталей : щелкните на заголовке

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