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

Задание. Найдите «Правила дорожного движения РФ» и прочитайте раздел «Сигналы светофора». Выясните, что сообщают водителям и пешеходам разные сигналы светофора.

Построим наш первый светофор.

Выделяем ячейки сводной таблицы. Наводим курсор на первый набор значков в разделе Shapes. Читаем всплывающую подсказку (рис. 6.42). Нам открыто сообщают, что это три сигнала светофора. Значки самые простые, без обрамления.

Рис. 6.42. Значки в стиле «Светофор»

Щёлкаем по этой кнопке и получаем значки в каждой ячейке.

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

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

Столбцы значков и значений должны быть на некотором расстоянии друг от друга. Тогда мы сможем их визуально разделить и различить (рис. 6.43).

Рис. 6.43. Сводная таблица в стиле «Светофор».

Задание. Выберите условное форматирование в стиле «Светофор». Подберите ширину столбцов и формат содержимого ячеек.

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

Выделяем диапазон «отформатированных» ячеек. Выбираем в верхнем меню пункт

Home —Styles — Conditional Formatting — Manage Rules.

На экране появляется диалоговое окно

Conditional Formatting Rules Manager(рис. 6.44).

Рис. 6.44. Менеджер правил форматирования

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

Show Formatting Rules for — This PivotTable.

В списке правил форматирования видим только одно правило:

Rule (applied in order shown) — Icon Set;

Format — вариант форматирования;

Applies to — список полей.

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

Итак, мы видим правило. Но не указано, как это правило будет работать.

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

Edit Rule.

На экране появляется диалоговое окно

Edit Formatting Rule(рис. 6.45).

Нам сообщают, как именно работает данное правило:

Display each icon according to these rules.

Зеленый значок выводится в случае, когда числовое значение больше или равно 67%.

Жёлтый — для значений от 33% до 67%

Красный — когда меньше 33%.

Рис. 6.45. Редактирование правила

Задание. Вызовите редактор правил и запишите в отчёт параметры своего правила форматирования.

Теперь разберёмся с пороговыми значениями.

В нашем правиле указано два порога: 33% и 67%.

Скорее всего, это проценты от всего интервала значений (рис. 6.46).

Рис. 6.46. Пороговые значения

Будем считать, что программа определяет наименьшее и наибольшее значения — среди всех значений, которые попали в выделенный диапазон ячеек. Затем каждое значение пересчитывается в проценты — в пределах этого интервала значений. Минимальное значение соответствует 0%. Максимальное значение — это 100%. Любое текущее значение превращается в условное число PROC — это расстояние от нижней границы значений, выраженное в процентах.

Скопируем в буфер значения итоговых показателей из сводной таблицы. Вставим их из буфера на свободном месте листа КАК ЗНАЧЕНИЯ. Поскольку здесь есть итоги по федеральным округам, удалим эти строки из дальнейших расчётов (рис. 6.47).

Находим минимальное и максимальное значения с помощью функций

MIN

MAX.

Определяем, какое значение соответствует порогу в 33%:

=J8+ (J9-J8) *0.33

Соответственно, порог в 67% равен:

=J8+ (J9-J8) *0.67

Рис. 6.47. Порог срабатывания

Задание. Вычислите пороговые значения для правил 33% и 67% по своей таблице.

Попробуем изменить правила. Например, мы хотим срабатывание на значениях 700 и 1600.

Пересчитаем эти значения в проценты от интервала «минимум-максимум».

Пороговое значение 700 в процентах выражается так:

= (J14-J8) / (J9-J8).

Пороговое значение 1600 выражается так:

= (J15-J8) / (J9-J8).

Чтобы вывести на экран значения в процентах, настроим формат вывода ячеек на экран в процентах:

Format Cells — Number — Category — Percentage — Decimal places — 2.

Вызываем редактирование правила форматирования.

Задаём наши собственные пороги срабатывания:

9.56% и 82.76% (рис. 6.48).

Рис. 6.48. Настройка правила форматирования

После настройки правила нажимаем кнопки

ОК — Apply — OK.

Рассматриваем изменения в таблице (рис. 6.49).

Действительно, теперь выделение цветными значками изменилось. Красными значками выделены значения меньше 700. Зелёными — больше 1600.

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

Рис. 6.49. Изменения в форматировании

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

Но и это ещё не всё! Нам предлагают более изощрённый инструмент под названием

Percentile.

Соответствующее русское название — ПРОЦЕНТИЛЬ или ПЕРЦЕНТИЛЬ. Вообще-то, процентиль — это процентная точка распределения. Другими словами, это значение случайной величины при заданной вероятности, которую выразили в процентах.

Расставим наши значения в порядке возрастания.

Для этого вначале расположим числа в одну колонку — через копирование и вставку из буфера.

Выделяем столбец ячеек.

Вызываем сортировку в верхнем меню:

Data —Sort & Filter — Sort Smallest to Largest(рис. 6.50)

Рис. 6.50. Сортировка по возрастанию

После сортировки мы получаем колонку из 24 значений. Числа расставлены по возрастанию — как мы и просили.

Слева добавим пару колонок.

Заполним одну колонку порядковыми номерами от 1 до 24.

В следующей колонке найдём значение процентиля для каждого нашего числа:

=K8/$K$31.

Установим формат вывода на экран в процентах.

Таблица процентилей готова (рис. 6.51).

Рис. 6.51. Вычисление процентилей

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

Допустим, мы хотим выделить значками только 3 наименьших и 3 наибольших значения. Находим значения в нашей табличке. Получаем пороги срабатывания 13% и 90% — в процентилях.

Щёлкаем по сводной таблице.

Вызываем редактор правил форматирования и задаём свои пороги срабатывания (рис. 6.52).

Рис. 6.52. Настройка порогов в процентилях

Нажимаем кнопки

OK — Apply — OK.

Любуемся полученными результатами (рис. 6.53).

В нашей сводной таблице находим три красных и три зелёных значка. Остальные — жёлтые.

Вот как работают процентили — это процент выделенных значений. Процент по количеству ячеек, а не по величине.