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

Рис. 6.9. Числовое значение вместо логического

Задание. Преобразуйте поле «Претензия» в числовое значение.

Посмотрим, много ли у нас появилось единичек. То есть претензий покупателей.

Щёлкаем по кнопке со стрелочкой в правой части заголовка столбца «Претензии». Снимаем выбор ноликов и оставляем выбор единичек (рис. 6.10).

Рис. 6.10. Выбор единиц (фактов претензий)

Нажимаем ОКи получаем фрагмент нашей таблицы транзакций. На экран выведены только те строки, в которых поле «Претензия» принимает значение 1. Там, где строки без претензий скрыты, можно увидеть двойную сплошную разделительную линию между номерами строк (рис. 6.11). Например, между строками 1 и 84. А вот между соседними строками 126 и 127 — одинарная сплошная.

Рис. 6.11. Строки с претензиями

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

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

Среднее значение = 1.

Всего было выделено 32 ячейки.

Минимальное и максимальное значения = 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. Выделение зелёным цветом