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

Достаточно выбрать пункт More Colors.

В диалоговом окне Colorвыбираем вкладку Custom. Это цвет, задаваемый пользователем.

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

Рис. 7.27. Настройка цвета в палитре RGB

Задание. Выберите индивидуальный цвет маркера на цветовом поле. Обратите внимание на автоматическое изменение яркости цветовых каналов.

Цветовая палитра RGB — это представление цвета любой точки экрана с помощью трёх цветовых каналов:

— Red — красный;

— Green — зелёный;

— Blue — синий.

Яркость каждого канала можно настроить, задавая целое число — в некоторых пределах.

Результат настройки цвета сразу демонстрируется на экране.

Задание. Определите опытным путём пределы изменения яркости каждого цветового канала. Оцените приблизительное количество возможных цветов в данной палитре.

Задание. Установите цвет маркера, задавая яркость каждого цветового канала.

Следующая и последняя группа инструментов:

Group — Группировка.

Здесь можно найти дополнительные настройки осей графика (рис. 7.28).

Нажимаем кнопку Axisи изучаем открывающиеся возможности:

Само простое и востребованное — это изображение оси «иксов» на графике:

Show Axis.

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

Рис. 7.28. Настройка осей

Задание. Включите изображение оси на графике.

Здесь же находятся средства удаления (очистки) спарклайнов:

Clear(рис. 7.29).

Мы можем удалить только выбранные графики:

Clear Selected Sparklines.

Или же можно удалить группы мини-графиков:

Clear Selected Sparkline Groups.

Мы уже познакомились с удалением диаграмм.

На этот раз обратим внимание на всплывающие подсказки.

Обычно они сообщают что-то нужное и полезное. Но только не в этот раз. Видимо, разработчикам пришлось их добавить «для отчёта», но не для дела. Или, как говорят, «для галочки».

Рис. 7.29. Очистка спарклайнов

Задание. Нажмите кнопку Clearи изучите всплывающие подсказки для каждого варианта очистки спарклайнов.

Сводные таблицы позволяют провести традиционную статистическую группировку данных. Этот стандартный метод обработки подробно описан в учебниках по предмету. Практические приёмы использования статистической группировки данных в пакете Excel мы уже разбирали в предыдущих работах [1—4].

В статистике с помощью группировки можно изучать форму кривой распределения. Кроме того, можно анализировать статистические зависимости с помощью условного среднего значения.

Задание. Изучите в учебнике «Теория статистики» раздел «Сводка и группировка данных».

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

При группировке числовых данных первый шаг — это определение количества интервалов группировки.

Для этого можно использовать правило Стёрджеса:

n = 1 +3,322 lg N .

В этой формуле использованы следующие обозначения:

n — количество интервалов группировки;

N — количество исходных данных (объём выборки).

Задание. Определите рекомендуемое количество интервалов для имеющего объёма данных в таблице транзакций.

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

Для этого мы используем сводную таблицу и стандартные способы агрегирования.

Построим новую сводную таблицу. Без измерений. С двумя мерами по полю «Количество товара в сделке».

В качестве итоговых показателей зададим минимальное и максимальное значения для поля «Количество».

Настраиваем формат вывода чисел — три знака после запятой.

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

Задание. Найдите наименьшее и наибольшее значения количества товара с помощью сводной таблицы.

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

(Max (x) — Min (x)) / n.

Сделаем всё это средствами Excel (рис. 8.2).

В качестве примера возьмём количество интервалов

n = 40.

Получаем размер интервала.

Округляем для удобства до 0,09.

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

GETPIVOTDATA

Рис. 8.2. Размер интервала

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

Переходим к обработке данных.

Создадим сводную таблицу по полю «Количество». В качестве итогового показателя установим число заполненных ячеек по тому же полю (рис. 8.3).

По сути, это будет счётчик числа значений.

Большинство значений встречаются один раз.

Переходим в последнюю строку таблицы и проверяем, чему равен общий итог Grand Total. Должно быть 10000 значений. Это число исходных данных в таблице транзакций.

Рис. 8.3. Число ячеек

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

Сгруппируем данные.

Щёлкаем правой кнопкой мыши по колонке значений «Количество». В контекстном меню выбираем пункт Group.

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

Вводим свой размер интервала группировки в строке By (рис. 8.4).

Нажимаем ОК.

Рис. 8.4. Параметры группировки

Задание. Проведите группировку данных.

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

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

Настроим отображение числовых значений итоговых показателей в виде процентов от общего итога (рис. 8.5).

Теперь в каждой строке выводятся относительные частоты в процентах.

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

Рис. 8.5. Относительные частоты

Задание. Настройте вывод относительных частот в процентах.

Построим график под названием гистограмма. Это столбиковая диаграмма относительных частот.

Гистограмма — это оценка формы кривой распределения.

Оценка, полученная по собранным данным.

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