Анализ данных в Excel. 2 часть. Лекция 4

1.

Анализ данных в Excel
2 часть
Лекция 4

2.

Содержание
• Сводные таблицы
• Построение Сводной таблицы
• Элементы сводной таблицы
• Полезные советы
• Импорт данных в Excel
• Адресация

3.

Сводные таблицы
Сводные таблицы — один из самых мощных инструментов анализа данных в Excel, который к
тому же относительно прост в использовании. Сводные таблицы могут решать огромное
количество задач по обработке данных — когда нужно проанализировать большое количество
строк «сырых» данных, группируя их по тем или иным параметрам.
Сводные таблицы легко менять и настраивать, за несколько секунд получая ответы на свои
вопросы без применения функций и формул.
При этом, в отличие от формул, сводные не пересчитываются автоматически, но достаточно
щёлкнуть правой кнопкой на сводной таблице и нажать «Обновить», чтобы в ней отразились
все изменения в исходных данных, которые происходили с предыдущего обновления.
Для сводной подходят любые таблицы и диапазоны, данные в которых можно обобщить и
сгруппировать. Если данные представлены в виде нескольких полей (столбцов), каждый из
которых — это определённая характеристика, параметр (сумма, дата, тип операции,
региональные параметры — например, город продажи или производства чего-либо, другие
характеристики, описывающие данные), а каждая строка — это одна запись с набором этих
характеристик (одна транзакция, операция, один человек, одно событие, один счёт и так
далее), то такие данные можно проанализировать, построив сводную таблицу.

4.

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

5.

Создание
• Создаётся сводная кнопкой на ленте инструментов во вкладке
«Вставка». Кнопка так и называется — «Сводная таблица»:

6.

Рекомендуемые сводные таблицы
В зависимости от версии может также быть опция
«Рекомендуемые сводные таблицы». Если вы выберете её, Excel
предложит несколько вариантов сводных, которые могут
подходить для ваших данных.
А на основе каких данных будет строиться сводная? Вы можете
заранее выделить всю таблицу либо же выделить любую её ячейку,
и в большинстве случаев Excel правильно выделит всю таблицу.

7.

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

8.

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

9.

Поля
Для настройки отчёта нужно
щёлкнуть на него, чтобы
справа отобразился список
полей, иначе говоря —
столбцов исходных данных
Перетащите те поля, по
которым нужно агрегировать
данные, в строки и столбцы (в
зависимости от того, как
хотите группировать)

10.

Элементы сводной таблицы
• Область строк – названия строк, которые расположены в крайнем левом
столбце. Это все уникальные значения выбранного поля (столбца). В области
строк может быть несколько полей, тогда таблица получается
многоуровневой. Здесь обычно размещают качественные переменные типа
названий продуктов, месяцев, регионов и т.д.
• Область столбцов – аналогично строкам показывает уникальные значения
выбранного поля, только по столбцам. Названия столбцов – это также
обычно качественный признак. Например, годы и месяцы, группы товаров.
• Область фильтра – используется, как ясно из названия, для фильтрации.
Например, в самом отчете показаны продукты по регионам. Нужно
ограничить сводную таблицу какой-то отраслью, определенным периодом
или менеджером. Тогда в область фильтров помещают поле фильтрации и
там уже в раскрывающемся списке выбирают нужное значение.

11.

Поля агрегация
• Можно добавлять несколько
полей только в строки или
столбцы — тогда, в
зависимости от их порядка,
данные в сводной будут
сгруппированы по одному
полю, а затем каждое значение
будет группироваться по
значениям второго поля

12.

Агрегирование
• Как вы понимаете, в этих сводных ещё
не хватает данных.
• Пока мы указывали, по каким полям
данные агрегируем, но не указывали,
какие поля агрегируются (что
суммируется, подсчитывается,
усредняется и так далее).
• Обычно агрегируются поля с числовыми
значениями (численность, количество,
выручка и так далее). В нашем примере
— это сумма операции (столбец
«Продажи»). Это поле нужно перенести
в значения. Те поля, что находятся в
значениях в сводной таблице, будут
агрегироваться (подсчитываться) в
контексте той группировки, которую вы
настроили в строках и столбцах.

13.

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

14.

Требования для
успешной работы
со сводными
таблицами
• Обязательным условием является
наличие названий над каждым
полем (столбцом), по которым эти
поля будут идентифицироваться.

15.

Полезные советы
• 1. Лучший формат для данных – это Таблица Excel.
• у каждого поля есть наименование и при добавлении новых строк они
автоматически включаются в сводную таблицу.
• 2. Избегайте повторения групп в виде столбцов.
• Например, все даты должны находиться в одном поле, а не разбиты по месяцам в
отдельных столбцах.
• 3. Уберите пропуски и пустые ячейки
• чтобы указанная строка не выпала из анализа.
• 4. Применяйте правильное форматирование к полям.
• Числа должны быть в числовом формате, даты должны быть датой.
• Это строго особенно при группировке и математической обработке. Но здесь
Excel вам поможет, т.к. сам неплохо определяет формат данных.

16.

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

17.

Обновление данных
Данные можно обновить командой
во вкладке
• Данные – Обновить все.
Сводная таблица занимает
много места в
оперативной памяти.
Чтобы расходовать
ресурсы компьютера
более экономно, работа
идет не напрямую с
источником, а с кэшем, где
находится моментальный
снимок исходных данных.

18.

Импорт данных в Excel
• В Excel можете импортировать данные из различных источников данных,
таких как база данных Microsoft Access, веб-страницы, текстовые файлы,
таблица SQL Server, куб анализа SQL Server, файл XML и т. д.
• Можно одновременно импортировать любое количество таблиц данных из
базы данных. При импорте нескольких таблиц из реляционной базы данных,
такой как Access, существующие связи между таблицами также будут
сохранены в Excel. При импорте данных можно создать сводную таблицу,
сводную диаграмму или отчет Power View на основе этих данных.
• Можно создать подключение к источнику данных или импортировать данные
в Excel. При импортировании данных в Excel, таблицы данных добавляются в
модель данных в Excel.

19.

Адресация
• Классическая и всем известная система адресации к ячейкам листа в
Excel представляет собой сочетание буквы столбца и номера строки морской бой или шахматы используют ту же идею для обозначения
клеток доски. Например третья сверху во втором столбце ячейка,,
будет иметь адрес B3. Иногда такой стиль ссылок еще называют
"стилем А1".
• В формулах адреса могут использоваться с разным типом ссылок:
относительными (просто B3), абсолютными ($B$3) и смешанного
закрепления ($B3 или B$3).
• Знак доллара указывает абсолютность. Что удобно в формулах при их
размножении, если надо применить значение одной и той же ячейки,
не меняя адрес. При размножении формулы относительный адрес
менятся.

20.

Стиль R1C1
• Существует еще и альтернативная малоизвестная система
адресации, называемая "стилем R1C1".
• В этой системе и строки и столбцы обозначаются цифрами.
• Адрес ячейки B3 в такой системе будет выглядеть
как R3C2 (R=row=строка, C=column=столбец).
• Мало кто использует этот режим осознанно.
• При проверке формул и поиске ошибок в таблицах иногда
гораздо удобнее использовать режим ссылок R1C1, потому что в
нем однотипные формулы выглядят не просто похоже, а
абсолютно одинаково.

21.

Заключение
• Сводные таблицы Excel – это один из самых простых
инструментов анализа данных.
• Теперь Вы можете быстро и эффективно анализировать большие
массивы данных, проводить вычисления, фильтрацию,
сортировку и др.
• Сводные таблицы станут Вашим повседневным инструментом.

22.

Использованные материалы. Материалы
для самостоятельного изучения
• Поручиков, М. А., Анализ данных: учеб. пособие / М.А.
Поручиков. – Самара: Изд-во Самарского университета, 2016. – 88
с.
• Data analysis with Excel.
https://www.tutorialspoint.com/excel_data_analysis/excel_data_anal
ysis_tutorial.pdf
• Статистический анализ в MS Excel - https://statanaliz.info