Создание отчета сводной таблицы

Лабораторная работа №1

Создание отчета сводной таблицы

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

 

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

 

Создание сводной таблицы для анализа данных листа

 

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

 

Данные о расходах семьи	Соответствующая сводная таблица

Рисунок 1 – пример сводной таблицы

 

Рисунок 2 – пример сводной диаграммы

 

 

Перед началом работы необходимо помнить следующие особенности работы со сводными таблицами:

¾ данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов;

¾ таблицы – это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список «Поля сводной таблицы», в противном случае необходимо вручную обновлять диапазон исходных данных или использовать формулу динамического именованного диапазона;

¾ все данные в столбце должны иметь один и тот же тип, например, не следует вводить даты и текст в одном столбце;

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

 

Создание сводной таблицы

Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные варианты ориентации и изменить порядок полей для получения нужных результатов. Инструкция по созданию сводных таблиц с помощью инструмента «Рекомендуемые сводные таблицы» и в ручном режиме приведена ниже в таблице 1.

 

Таблица 1 – инструкция по созданию сводных таблиц

Рекомендуемые сводные таблицы

Создание сводной таблицы вручную

1. Щелкните ячейку в диапазоне исходных данных и таблицы. 2. Выберите Вставка > Таблицы > Рекомендуемые сводные таблицы. 3. Excel проанализирует данные и предоставит несколько вариантов, как в этом примере: 4. Выберите сводную таблицу, которая вам понравилась, и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.

1. Щелкните ячейку в диапазоне исходных данных и таблицы. 2. Выберите Вставка > Таблицы > Сводная таблица. Если вы используете Excel для Mac 2011 или более ранней версии, кнопка "Сводная таблица" находится на вкладке Данные в группе Анализ. 3. Появится диалоговое окно Создание сводной таблицы, в котором указан ваш диапазон или имя таблицы. В этом случае мы используем таблицу "таблица_СемейныеРасходы". 4. В разделе Укажите, куда следует поместить отчет сводной таблицы выберите На новый лист или На существующий лист. При выборе варианта На существующий лист вам потребуется указать лист и ячейку. 5. Если вы хотите включить несколько таблиц или источников данных в сводную таблицу, установите флажок Добавить эти сведения в модель данных. 6. Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.

 

 

Значения в сводной таблице

По умолчанию поля сводной таблицы, которые находятся в области Значения, обобщаются с помощью функции СУММ. Если Excel считает данные текстом, к ним применяется функция СЧЁТ. Именно почему так важно не использовать разные типы данных для полей значений. Вы можете изменить функцию, которая по умолчанию используется для вычислений. Для этого щелкните стрелку справа от имени поля и выберите Параметры полей значений (Рисунок 4).

 

Рисунок 4 – Пункт меню «Параметры полей значений»

 

Затем в окне «Параметры поля значений» (Рисунок 5) измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, "Сумма по полю имя_поля"), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.

 

Рисунок 6 – Окно параметры поля значений

 

Дополнительные вычисления. Значения также можно выводить в процентах от значения поля. В приведенном ниже примере (Рисунок 7) мы изменили сумму расходов на % от общей суммы.

 

Рисунок 7 – пример использования дополнительных вычислений

 

Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления (Рисунок 8).

 

Рисунок 8 – Вкладка «Дополнительные вычисления»

 

Для отображения значения как результата вычисления и как процента просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

 

 

Обновление сводных таблиц

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

 

Рисунок 9 – Инструмент «Обновить» группы «Работа со сводными таблицами»

 

Для удаления сводной таблицы просто выделите ее и нажмите клавишу DELETE. Это не повлияет на другие данные и сводные таблицы или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист.

 

 

Создание сводной диаграммы

 

Увидеть полную картину данных непросто, когда они представлены в виде огромной сводной таблицы или большого количества сложных данных, включающих текст и числа с заголовками столбцов, следующим образом (Рисунок 10):

 

Рисунок 10 – Пример сложной таблицы

 

 

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

 

Рисунок 11 – Интерактивные элементы управления сводной диаграммы

 

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

 

1. Выберите любую ячейку данных.

2. На вкладке Вставка в группе Диаграммы выберите Рекомендуемые диаграммы (Рисунок 12).

3.

Рисунок 12 – Группа инструментов «Диаграммы»

 

4. На вкладке Рекомендуемые диаграммы выберите любую диаграмму со значком сводной диаграммы в верхнем углу. В области просмотра отобразится предварительный вид сводной диаграммы (Рисунок 13).

Рисунок 13 – Окно «Вставка диаграммы»

 

5. Выбрав подходящую сводную диаграмму, нажмите ОК.

 

ПРИМЕЧАНИЕ: Если вам не удается найти нужную сводную диаграмму, на вкладке Вставка выберите Сводная диаграмма вместо Рекомендуемые диаграммы.

 

6. В появившейся сводной диаграмме нажмите любой интерактивный элемент управления, а затем выберите необходимые параметры сортировки или фильтрации (Рисунок 14).

 

Рисунок 14 – параметры фильтрации сводной диаграммы

Чтобы создать сводную диаграмму для существующей сводной таблицы нажмите в любом месте сводной таблицы, чтобы отобразить на ленте раздел "Работа со сводными таблицами" (Рисунок 15).

 

Рисунок 15 – группа инструментов «Работа со сводными таблицами»

 

1. Выберите Анализ > Сводная диаграмма (Рисунок 16).

 

Рисунок 16 – Инструмент «Сводная диаграмма»

 

2. В диалоговом окне Вставка диаграммы (Рисунок 17) выберите нужный тип и подтип диаграммы. Вы можете использовать любой тип, кроме точечной, пузырьковой и биржевой диаграммы.

 

Рисунок 17 – Окно «Вставка диаграммы»

 

 

 

Сортировка данных

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

В сводной таблице щелкните стрелку списка Названия строк или Названия столбцов.

1. Щелкните ячейку в строке или столбце, которые требуется отсортировать.

2. Щелкните стрелку на вкладке Названия строк или Названия столбцов, а затем выберите нужный параметр (рисунок 47).

 

Рисунок 47 – выбор параметров сортировки

 

3. Чтобы отсортировать данные в порядке возрастания или убывания, нажмите кнопки Сортировка от А до Я или Сортировка от Я до А (рисунок 48).

Рисунок 48 – Выбор параметров сортировки данных таблицы

 

Текстовые элементы будут сортироваться в алфавитном порядке, числа — от минимального к максимальному или наоборот, а значения даты и времени — от старых к новым или от новых к старым.

 

Группировка полей

1. В сводной таблице щелкните правой кнопкой мыши любое числовое поле или поле даты и времени и выберите команду Группировать.

2. В поля начиная с и по введите в зависимости от типа данных указанные ниже значения (Рисунок 53).

 

Рисунок 53 – параметры группировки полей

 

Для группировки числовых полей: минимальное и максимальное числа.

Для группировки значений даты и времени: начальную и завершающую дату или время.

Значение в поле начиная с должно быть больше или позже значения в поле по.

 

3. В поле с шагом введите указанные ниже значения.

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

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

 

Использование срезов

 

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

 

Рисунок 60 – пример среза

 

 

1. Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами (рисунок 61).

 

Рисунок 61 – вкладка «Работа со сводными таблицами»

 

2. Если вы используете Excel 2016 или 2013, на вкладке Анализ нажмите кнопку Вставить срез (рисунок 62).

Рисунок 62 – инструмент «Вставить срез» в MS Excel 2013-2016

 

Если вы используете Excel 2010 или 2007, щелкните Параметры > Вставить срез > Вставить срез (рисунок 63).

 

Рисунок 62 – инструмент «Вставить срез» в MS Excel 2007-2010

 

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

4. Нажмите кнопку ОК.

Для каждого поля, выбранного в диалоговом окне Вставка срезов, отобразится срез.

5. В каждом срезе выберите элементы, которые нужно отобразить в сводной таблице.

Чтобы выбрать более одного элемента, щелкните каждый из них, удерживая нажатой клавишу CTRL.

 

Отключение среза

1. Щелкните в любом месте отчета сводной таблицы, от которого нужно отключить срез.

Появится раздел Работа со сводными таблицы со вкладками Параметры и Конструктор.

2. На вкладке Параметры в группе Сортировка и фильтр щелкните стрелку Вставить срез, а затем выберите пункт Подключения к срезам.

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

 

Форматирование среза

1. Выберите срез, который необходимо отформатировать.

Откроется окно Инструменты для среза со вкладкой Параметры.

2. На вкладке Параметры в группе Стили срезов выберите необходимый стиль.

Чтобы просмотреть все доступные стили, нажмите кнопку Дополнительные кнопки .

 

Использование срезов в разных сводных таблицах

Если в одном отчете содержатся несколько сводных таблиц (например, при работе с отчетом по бизнес-аналитике), может потребоваться применить один фильтр к нескольким или всем сводным таблицам. В этом случае вместо создания нескольких фильтров можно использовать один срез с несколькими таблицами.

При использовании среза с несколькими таблицами создается связь с другой сводной таблицей, которая и содержит нужный срез. Все изменения общего среза мгновенно отражаются в сводных таблицах, связанных с этим срезом. Например, если в сводной таблице 1 для фильтрации по определенной стране используется срез "Страна", в сводной таблице 2, которая также использует этот срез, будут отображены данные для той же страны.

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

 

 

Работа с формулами

 

Задание

Изучить теоретическую часть. Используя файлы «L_1 Исходные данные.xlsx» и «L_1_База данных.accdb» применить приведенные в теоретической части инструменты и приемы для обработки, анализа и представления данных. Итоговые и промежуточные результаты применения различных инструментов и приемов (создания сводных таблиц, диаграмм, срезов и т.д.) занести в отчет по лабораторной работе, и сохранить исходном файле MS Excel.

 

Контрольные вопросы:

1. Для чего предназначены сводные таблицы?

2. Опишите основные шаги создания сводных таблиц в MS Excel?

3. Для чего предназначена функция подключения внешнего источника?

4. Как подключить внешний источник данных к документу MS Excel?

5. Для чего необходимо форматировать таблицы?

6. Как можно изменить макет сводной таблицы?

7. Как можно изменить стиль сводной таблицы?

8. Что такое сортировка данных?

9. Что такое фильтрация данных?

10. В чем различие сортировки и фильтрации данных?

11. Можно ли сортировать данных в сводных таблицах? Если да, как?

12. Что такое группировка данных?

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

14. Как создать срез сводной таблицы?

15. Какие параметры форматирования можно настроить для среза сводной таблицы?

16. Что такое условное форматирование ячеек MS Excel?

17. Назовите параметры условного форматирования ячеек MS Exсуд,

18. Что такое вычисляемый объект?

19. Как скопировать часть сводной таблицы без привязки к источнику данных?

20. Какие средства MS Excel следует применить для поиска повторяющихся ячеек таблицы?

 

Лабораторная работа №1

Создание отчета сводной таблицы

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

 

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ