Бизнес-аналитика. Сводные таблицы. Часть 2 - [7]
Минимальное и максимальное значения = 1.
То есть все выделенные значения = 1.
Сумма значений = 32.
Это сумма 32 единичек.
Рис. 6.12. Количество претензий
Задание. Определите количество претензий с помощью строки состояния.
Мы посмотрели на итоговые показатели с выбором единичек. Выбираем все возможные значения:
Select All.
Нажимаем ОК.
Выделяем столбец претензий и смотрим на итоги в строке состояния.
Минимальное значение = 0.
Максимальное значение = 1.
Сумма = 32.
Это говорит о том, что у нас по-прежнему всего 32 единички, а остальные нули.
Рис. 6.13. Итоги по всем значениям
Задание. Выберите все значения по претензиям и рассмотрите итоговые показатели в строке состояния.
Чтобы данные больше не пересчитывались, скопируем столбец в буфер и вставим как значения.
Удалим вспомогательный столбец случайных чисел. Он нам больше не понадобится.
Теперь в нашем столбце «Претензии» находятся только числовые значения (рис. 6.14). Никаких формул и никакого пересчёта таблицы.
Рис. 6.14. Значения вместо формул
Задание. Избавьтесь от формул и вспомогательного столбца.
Наконец-то наши исходные данные по претензиям готовы для анализа. Построим сводную таблицу, чтобы найти сумму по полю «Претензии». Изучаем список полей в разделе
PivotTable Fields.
Поле «Претензии» отсутствует (рис. 6.15). Но ведь оно было в таблице транзакций. Мы только что его создали…
Рис. 6.15. Поле «Претензии» отсутствует
Задание. Вставьте сводную таблицу и проверьте наличие поля «Претензии».
Надо бы нам восстановить справедливость. Щёлкаем правой кнопкой по макету сводной таблицы (здесь пока только очертания, а содержимого нет). Просим обновить — нажимаем
Refresh.
Свершилось чудо: в списке полей появились наши «Претензии» (рис. 6.16).
Рис. 6.16. Поле «Претензии» появилось
Организуем нашу сводную таблицу.
— строки — ФО;
— столбцы — годы;
— значения — количество претензий.
По умолчанию итоговый показатель — это сумма. Соответственно, сумма единиц даёт нам количество претензий.
Настраиваем заголовки (рис. 6.17).
Рис. 6.17. Сводка по претензиям
Задание. Постройте сводную таблицу «ФО — Год — Количество претензий».
Таблица для опытов готова.
Организуем условное форматирование. Выделяем итоговые показатели и вызываем в верхнем меню
Home —Styles —Conditional Formatting — Highlight Cell Rules — Greater Than.
Выбираем пороговое значение 5.
Оставляем тревожный красный цвет (рис. 6.18).
Рис. 6.18. Условное форматирование
Задание. Выделите красным цветом самые проблемные значения.
Посмотрим на нашу работу со стороны. Вызываем меню печати:
File — Print.
Два числовых значения в таблице выделены тёмно-красным и внутри ячеек установлен светло-красный фон (рис. 6.19).
Рис. 6.19. Предварительный просмотр
Задание. Ознакомьтесь с видом сводной таблицы в режиме предварительного просмотра.
Мы выделили самые проблемные ФО. Теперь высветим самые успешные. Снова выделяем наши ячейки с показателями и настраиваем второе правило:
Insert —Styles — Conditional Formatting — Highlight Cell Rules — Less Than.
Получаем таблицу с двумя видами цветных ячеек (рис. 6.20).
Рис. 6.20. Выделение зелёным цветом
Задание. Настройте условное форматирование самых благополучных регионов.
Мы выделили лучшие и худшие значение. Можно также отметить что-то среднее. Для этого у нас есть вариант
Insert —Styles — Conditional Formatting — Highlight Cell Rules — Between.
Работает аналогично предыдущим.
Задание. Настройте выделение цветом для ячеек с промежуточными значениями.
После всех экспериментов мы можем удалить (очистить) все правила условного форматирования. Для этого выбираем
Insert —Styles — Conditional Formatting — Clear Rules — Clear Rules from This PivotTable.
Задание. Удалите условное форматирование в сводной таблице.
6.2. Выделение «лучших» и «худших»
Мы познакомились с форматирование на основе конкретного порогового значения. Есть ситуации, когда имеется точное значение, например, прожиточный минимум —это точное, заранее известное число.
В других случаях точное, абсолютное значение будет неудобно в использовании. Мы можем выделить «лучшие» и «худшие» значения по более простым и универсальным правилам. Это указанное количество или процент верхних или нижних значений:
Insert — Styles — Conditional Formatting — Top/Bottom Rules.
Рис. 6.21. Выделение лучших и худших
Возьмём, к примеру, первый пункт:
Top 10 Items.
Судя по названию, здесь нас ожидает десятка лучших. Но это не так. На самом деле, здесь можно установить любое количество значений, которые нас интересуют. Например, мы попросим выделить два лучших значения (рис. 6.22).
Рис. 6.22. Два наибольших значения
Задание. Выделите два лучших значения.
Вроде с десяткой передовиков всё просто и понятно. НО здесь есть одна особенность. А что произойдёт, если у нас несколько значений будут равны между собой?
На новом листе проведём небольшой опыт. Расставим несколько чисел. Некоторые из них будут одинаковыми.
Выделяем диапазон ячеек и меняем количество лучших (рис. 6.23). Выбираем тройку лидеров, а получаем пятёрку.
Рис. 6.23. Форматирование одинаковых значений
Задание. Сформируйте тестовую таблицу с несколькими одинаковыми значениями. Обратите внимание на количество выделенных ячеек.
