Тема 6. ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ

Цель работы: овладеть навыками создания и обработки сводных таблиц в Excel.

ПОНЯТИЕ СВОДНОЙ ТАБЛИЦЫ

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

МАСТЕР СВОДНЫХ ТАБЛИЦ

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

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

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

На третьем шаге проектируют структуру сводной таблицы. Нужно указать данные, которые будут использоваться в качестве полей строк, полей столбцов и полей страниц, а затем указать данные, которые хотят подытожить в области данных сводной таблицы. В правой части диалогового окна содержатся поля исходного списка. Имена этих полей можно транспортировать в поля Фильтр отчета, Названия строк, Названия столбцов или Сумма значения макета сводной таблицы. Excel автоматически использует для подведения итогов функцию Сумма для числовых данных и функцию Кол-во значения для нечисловых данных.

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

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

ГРУППИРОВКА ЭЛЕМЕНТОВ В СВОДНОЙ ТАБЛИЦЕ

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

• группировка нескольких выделенных элементов в поле (Excel создает второе группировочное поле для замены сгруппированных элементов);

• автоматическая группировка чисел по интервалам (например, список элементов от 1 до 100 может быть сгруппирован по десяткам);

• автоматическая группировка дат и времени по интервалам (например, список месяцев может быть сгруппирован по кварталам или другим периодам; можно также создавать собственные интервалы для группировки).

Для выполнения группировки дат и времени по интервалам нужно:

• выделить один элемент, являющийся датой или временем, в поле, которое нужно сгруппировать;

• На вкладке Параметры - Группировать -Группировка по выделенному - в окне Группирование выбрать группировка по дням - количество дней - 7;

Выбранные для группировки элементы (дата и время) должны соответствовать форматам Excel.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1.Что такое сводная таблица?

2.Опишите последовательность работы с мастером сводных таблиц.

3.Каково назначение кнопок панели инструментов Сводные таблицы?

4.Перечислите способы группировки данных в сводной таблице.

5.Как выполнить группировку дат и времени по интервалам в сводной таблице?

ЗАДАНИЯ

1. Вызвать Excel для работы с электронной таблицей, созданной на предыдущих практических занятиях.

2. Создать новый лист Лист8. Присвоить ему имя Апрель.

3. На листе Апрель создать электронную таблицу (см. ниже, табл. 5) «Производство продукции с шифром 0103 в апреле», расположив ее в ячейках A2:D44.

Табл. 5

Название таблицы ввести в ячейку А1 и отцентрировать по ширине таблицы. Для столбца «День» установить формат ячеек - Дата. Данные столбцов: «Номер цеха» и «День» ввести, используя метод автозаполнения. Для расчета значений столбца «Стоимость продукции, руб.» использовать ссылку на ячейку листа Цех1, содержащую цену 1 т продукции с шифром 0103, используя абсолютную адресацию. Для Цеха2 и Цеха3 произвести подобные операции.

4. Создать сводную таблицу. Для этого:

Ø В качестве источника данных указать список (базу данных) Excel, или щелкнуть по какой-либо ячейке таблицы Апрель.

Ø Вставка - Сводная таблица - сводная таблица. В появившемся окне Создание сводной таблицы. Ввести или выделить интервал ячеек рабочего листа, содержащий данные, которые следует использовать для построения сводной таблицы - 'Апрель'!$А$2:$D$44. с помощью мастера сводных таблиц. Установите флажок куда следует поместить отчет сводной таблицы На новый лист.

Ø Для определения внешнего вида сводной таблицы выберем поля из списка полей сводной таблицы. Для этого перетащим в поле Название строк имя столбца «День», в поле Название столбцов имя столбца «Номер цеха», в поле Сумма значения имя столбца «Произведено продукции, т»

Ø На вкладке Конструктор - Макет отчета - Показать в форме структуры.

Ø Щелкните по любой ячейке в столбце День. На вкладке Параметры - Группировка по выделенному - в окне Группирование выбрать группировка по дням - количество дней - 7.

Ø Просмотреть созданную сводную таблицу на листе Лист9. Переименовать лист Лист9, присвоив ему имя Сводная таблица.

 

 

5. Разгруппировать данные, используя кнопку на вкладке Параметры , выделив одну из ячеек поля «День».

6. Выполнить пункт 5 другим способом:

· выделить первый интервал ячеек А5:А11;

· ПКМ (правая кнопка мыши), нажать кнопку

· выделить второй интервал ячеек А12:А18;

· ПКМ, нажать кнопку

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

8. Выполнить пункты 5, 6 для модифицированной сводной таблицы.

9. Перейти на лист Апрель и произвольно изменить несколько числовых данных в столбце «Произведено продукции, т».

10. Перейти на лист Сводная таблица и обновить данные в сводной таблице, нажав кнопку вкладки Параметры - Обновить .

11. Записать ЭТ в файл.

12.Выйти из Excel.

 

 

Предыдущая1234567Следующая

---

Не нашли, что искали? Воспользуйтесь поиском по сайту:

©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.