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

Сумма = 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.

Задание. Удалите условное форматирование в сводной таблице.

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

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

Insert — Styles — Conditional Formatting — Top/Bottom Rules.

Рис. 6.21. Выделение лучших и худших

Возьмём, к примеру, первый пункт:

Top 10 Items.

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

Рис. 6.22. Два наибольших значения

Задание. Выделите два лучших значения.

Вроде с десяткой передовиков всё просто и понятно. НО здесь есть одна особенность. А что произойдёт, если у нас несколько значений будут равны между собой?

На новом листе проведём небольшой опыт. Расставим несколько чисел. Некоторые из них будут одинаковыми.

Выделяем диапазон ячеек и меняем количество лучших (рис. 6.23). Выбираем тройку лидеров, а получаем пятёрку.

Рис. 6.23. Форматирование одинаковых значений

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

Получается, что указанное количество относится не к ячейкам, а к значениям в этих ячейках.

Посмотрим на эту ситуацию с другой стороны. Сделаем попроще — расставим числа по порядку, в одну строку.

Укажем номера ячеек. На это раз будем нижние значения.

Посмотрим, сколько чисел выберет программа:

Insert —Styles — Conditional Formatting — Top/Bottom Rules — Bottom 10 Items.

Можно сказать, что поведение программы не очень предсказуемое (рис. 6.24).

Рис. 6.24. «Пятёрка» лучших — это семёрка

Задание. Проверьте, сколько ячеек выделяется, задавая разное количество одинаковых значений.

Слово RANKв меню Bottom 10 Itemsнамекает на порядковый номер, или «ранг». Чтобы определить ранг значения, нужно расставить их по возрастанию и пронумеровать. Сделать «перекличку», как на уроке физкультуры.

Наша настройка одновременно затрагивает и количество ячеек, и их ранги. Это совсем не очевидное поведение.

Есть у программистов такое правило: если ничего не помогает, читайте инструкцию. Вот и попробуем почитать.

Нажимаем на кнопку с вопросительным знаком (рис. 6.24).

В браузере открывается соответствующая страничка справочной системы:

https://support.office.com.

Получаем страницу справки на тему:

Use conditional formatting to highlight information.

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

Попробуем узнать что-нибудь полезное.

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

Общее описание инструментов достаточно простое и понятное. Можно сориентироваться и начинать работать.