Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 2

Условное среднее позволяет наглядно представить возможную взаимосвязь статистических признаков. Особенно хорошо эта связь видна на фоне диаграммы разброса.

Построим диаграмму разброса «Цена — Количество» по конкретному выбранному товару в таблице транзакций.

Настраиваем масштаб по осям так, чтобы весь график был занят нашими точками (рис. 8.18).

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

Рис. 8.18. Диаграмма разброса

Задание. Постройте диаграмму разброса «Цена — Количество» для данных по одному товару.

Переходим к группировке.

Находим минимум и максимум для цены и для количества по одному товару.

Товар будет нашим фильтром (рис. 8.19).

Рис. 8.19. Максимум и минимум

Задание. Найдите наибольшее и наименьше значения по выбранному товару.

Найдём размер интервалов группировки. Пусть у нас будет 20 интервалов.

Вычисляем размер интервалов группировки (рис. 8.20).

Рис. 8.20. Интервалы группировки

Задание. Определите размер интервалов группировки.

Проведём группировку по двум измерениям.

Построим сводную таблицу «Цена —Количество».

Фильтр — «Товар».

Обобщённый показатель — число значений (рис. 8.21).

Рис. 8.21. Параметры сводной таблицы

Задание. Постройте описанную выше сводную таблицу.

Включаем фильтрацию по выбранному товару.

Настраиваем группировку данных с нашими интервалами.

Результат — таблица частот.

В каждой ячейке — количество попаданий транзакций в указанные интервалы по цене и количеству одновременно.

Внешний вид таблицы (рис. 8.22) показывает общую картину и напоминает диаграмму разброса.

Рис. 8.22. Таблица частот

Теперь находим условное среднее значение.

В качестве факторного признака (независимой переменной) выбираем цену товара. Располагаем по строкам сводной таблицы.

Результативный признак (зависимая переменная) — количество товара. Среднее значение количества — обобщённый показатель.

Дополнительно вводим второй обобщённый показатель — среднюю цену.

Фильтр по выбранному товару.

Проводим группировку по нашим интервалам.

Настраиваем формат вывода числовых значений.

Получаем столбец средних цен и среднего количества для каждого интервала группировки по ценам (рис. 8.23).

Рис. 8.23. Результаты группировки

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

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

Изменим тип диаграммы:

Chart Tools — Design — Type — Change Chart Type.

Выбираем комбинированную диаграмму:

Combo.

Для данных по транзакциям выбираем тип графика Scatter.

Для условного среднего — Scatter with Straight Lines.

Рис. 8.24. Комбинированная диаграмма

Задание. Нанесите линию условного среднего на диаграмму разброса и настройте комбинированную диаграмму.

Теперь займёмся оформлением графика.

Настроим масштаб по осям так, чтобы наши точки занимали весь график.

Настраиваем маркеры и тип линий.

Для диаграммы разброса возьмём маркеры помельче и побледнее.

Format Data Series — Series Options:

Marker — Marker Options — Fill — Solid Fill;

Marker Options — Built-in — Size = 3;

Color — Light Gray;

Border — No line.

Для линии условного среднего настроим ломаную линию и среднего размера маркеры.

Format Data Series — Series Options:

Line — Solid Line;

Color — Blue;

Marker Options — Built-in — Size = 7;

Marker — Solid Fill;

Color — Blue;

Border — No line.

Оформляем заголовки, получаем окончательный вид графика (рис. 8.25).

На графике можно видеть общий характер зависимости. Это почти прямая линия. Вокруг линии наблюдается случайный разброс значений — ориентировочно плюс-минус 1 кг.

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

Рис. 8.25. Условное среднее

Задание. Оформите график, как описано выше.

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

Задание. Просмотрите статью «Data Mining Algorithms (Analysis Services — Data Mining)» на сайте https://docs.microsoft.comи выясните, как можно использовать регрессионный анализ при управлении компанией. При необходимости переключитесь на страницу с переводом на русский язык.

1. Арьков В. Ю. Бизнес-аналитика в Excel. Сводные таблицы: Учебное пособие. — [б. м.]: Издательские решения, 2020. — 180 с.

Режим доступа:

https://ridero.ru/books/biznes-analitika_v_excel_svodnye_tablicy/

2. Арьков В. Ю. Статистический анализ взаимосвязи в Excel: Учебное пособие. — [б. м.]: Издательские решения, 2019. — 146 с.

Режим доступа:

https://ridero.ru/books/statisticheskii_analiz_vzaimosvyazi_v_excel/

3. Арьков В. Ю. Анализ рядов динамики в Excel: Учебное пособие — [б. м.]: Издательские решения, 2020. — 130 с.

Режим доступа:

https://ridero.ru/books/analiz_ryadov_dinamiki_v_excel/

4. ГОСТ Р ИСО 3534-1-2019. Статистические методы. Словарь и условные обозначения. Часть 1. Общие статистические термины и термины, используемые в теории вероятностей

5. ISO 3534—1:2006 (en). Statistics — Vocabulary and symbols — Part 1: General statistical terms and terms used in probability

6. Теория статистики: учебник / Р. А. Шмойлова, В. Г. Минашкин, Н. А. Садовникова, Е. Б. Шувалова; под ред. Р. А. Шмойловой. — М.: Финансы и статистика, 2014. — 656 с.

7. Винстон У. Л. Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel. — СПб.: Питер, 2018. — 864 с.

8. Александер М. Excel 2019. Библия пользователя. Исчерпывающее руководство. — СПб.: Диалектика, 2019. — 1136 с.

9. Джелен Б., Александер М. Сводные таблицы в Microsoft Excel 2013. — М.: Вильямс, 2017. — 448 с.