Практическая работа №6 Связывание рабочих листов, консолидация таблиц. Создание, редактирование, форматирование сводной таблицы

Практическая работа №6

Связывание рабочих листов, консолидация таблиц. Создание, редактирование, форматирование сводной таблицы.

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

Источниками данных для сводных таблиц являются списки или базы данных, созданные на листах Excel, или другие сводные таблицы а также внешние данные. (Например, базы данных, созданные в Microsoft Access)

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

Сводная таблица создается с помощью мастера сводных таблиц, используемого для размещения и объединения анализируемых данных. Чтобы начать создание сводной таблицы, надо выбрать команду «Сводная таблица» в меню «Данные». (Если сводная таблица создается на основе списка на листе Excel, то предварительно можно отметить любую ячейку внутри списка. Это позволит Excel автоматически определить область, которую занимает список)

Подведение итогов в сводной таблице производится с помощью итоговой функции (например, «Сумма», «Кол-во значений» или «Среднее». В таблицу можно автоматически поместить промежуточные или общие итоги, а также добавить формулы в вычисляемые поля или элементы полей. 

ЗАДАНИЕ:

1.     Откройте книгу «ПР_№5_ФИО».

2.     Перейдите на лист «Полный ассортимент».

3.     На листе Полный ассортимент выделите диапазон B8:H28 и в меню Данные выберите команду Сводные таблицы, откроется диалоговое окно Мастер сводных таблиц.

На первом и втором шаге нажмите Далее

На третьем шаге перетащите мышью кнопку Товар в поле Столбец, кнопку Название в поле Строка, кнопку Сумма в поле Данные.

На четвертом шаге постройте сводную таблицу на новом листе Отчет в ячейке C8.

4.     Ваш рабочий лист должен выглядеть так:

5.     Добавьте количество проданных товаров каждого вида:

ü Щелкните мышью в любой из ячеек сводной таблицы и перетащите кнопку Количество в поле Данные;

ü щелкните правой кнопкой мыши в ячейке D10 и выберите команду Параметры поля, откроется диалоговое окно Вычисление поля сводной таблицы, в поле имя наберите Выручка вместо Сумма по полю Сумма и нажмите ОК;

ü аналогичным образом в ячейке D11 наберите Продано всего.

6.     Поместите поле на отдельной странице

ü сделайте активной сводную таблицу и перетащите кнопку Товар из ячейки E8 в поле страницы.

ü ваш рабочий лист выглядит так:

7.     Перенесите кнопку из области страниц в область строк

ü кнопка Товар перенесена из области страниц в область строк, и теперь все данные расположены на одной странице.

8.     Скройте, а затем покажите детали сводной таблицы:

-         в столбце Товар щелкните дважды на заголовке Ксерокс, данные по этому виду товара сжались;

-         повторным двойным щелчком в этой ячейке сводная таблица вернется к прежнему виду.

9.     Скройте данные:

ü Перетащите кнопку Товар в область страниц;

ü дважды щелкните на кнопке Товар, откроется окно Вычисление сводной таблицы;

ü в окне списка Скрыть элементы выделите строку Факс и ОК;

ü в списке кнопки Товар теперь находятся только названия страниц Все им Ксерокс;

ü аналогичным образом покажите скрытые данные.

10. Покажите все данные о продаже профессиональных ксероксов

ü дважды щелкните в ячейке E15, появится лист, на котором отображены все данные о продаже профессиональных ксероксов.

11. Измените функцию подведения итогов:

ü перетащите кнопку Товар в ячейку F8;

ü щелкните правой кнопкой мыши в ячейке D11 по словам Продано всего, в поле имя введите Продано Минимум, в поле Операция выберите Минимум, ОК

ü добавьте максимальные значения количества проданных товаров, после чего ваша сводная таблица должна выглядеть так:

12. Удалите общие итоги:

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

13. Отформатируйте сводную таблицу:

ü На панели инструментов Сводные таблицы нажмите кнопку Формат отчета и выберите автоформат Отчет 6;

ü отформатируйте числа в столбце Выручка, для этого выделите диапазон ячеек E9:E21, из контекстного меню выберите Формат ячеек и задайте формат — Тыс.тенге.

ü ваша сводная таблица должна выглядеть так:

14. Постройте диаграмму сводной таблицы:

ü перетащите кнопки полей Выручка и Продано Максимум из области сводной таблицы.

ü выделите сводную таблицу через команду Сводная таблица—Выделить—Таблица целиком на панели инструментов Сводные таблицы.

ü нажмите кнопку Мастер диаграмм на панели инструментов Сводные таблицы;

ü измените тип диаграммы на объемный вариант обычной линейчатой диаграммы;

ü поместите диаграмму на отдельном листе Диаграмма 1.