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

— No Calculations(не проводить никаких расчётов);

— % of Grand Total(процент от итогового значения выбранного показателя по всей таблице);

— % of Column Total(процент от итога по столбцу);

— % of Row Total(процент от итога по строке).

Дальше по списку видим множество других вариантов.

Выбираем «процент по строке» и нажимаем ОК(рис. 6.17).

Рис. 6.17. Проценты по строке

Остаётся «навести красоту». Поработаем с оформлением таблицы и с форматом чисел в ячейках.

Делаем общий заголовок подлиннее и попонятнее, а заголовки строк и столбцов покороче.

Настроим формат вывода чисел. Сумма пусть будет целыми числами. Это мы уже с вами проделывали, тут сложностей не должно быть. Проценты делаем с точностью до десятой доли процента. Для формата процентов выбираем следующую настройку:

Value Field Settings — Number — Category — Number — Decimal Places — 1.

Что мы получили в результате? Оказывается, проценты вначале рассчитываются как числа, а потом выводятся как проценты (рис. 6.18). Другими словами, обещанный расчёт процентов не переводит результаты вычислений в проценты, а только использует экранное представление числа — формат вывода в виде процентов.

Рис. 6.18. Проценты в формате числа

Значит, для вывода вычисленных «процентов» надо выбрать формат вывода в процентах:

Value Field Settings — Number — Category — Percentage — Decimal Places — 1.

Теперь с правильной настройкой проценты выводятся с точностью до десятых долей процента (рис. 6.19). Обратим внимание, что в строке формул тоже указаны проценты, но со всеми многочисленными разрядами после запятой (в английском варианте — после точки, естественно).

Рис. 6.19. Проценты в формате процентов

Мы исправили заголовки столбцов, в которых выводятся наши показатели — суммы и проценты. Но это не просто отдельные ячейки на рабочем листе. Это элементы такого объекта, как сводная таблица. Обратим внимание на меню настройки сводной таблицы (рис. 6.20). Здесь в разделе Valuesтоже произошли изменения. Новые, исправленные заголовки указаны здесь как названия наших итоговых показателей.

Рис. 6.20. Названия показателей

Задание. Создайте сводную таблицу с подсчётом сумм и процентов по стоимости товаров.

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

— Count(Количество заполненных ячеек);

— Count numbers(Количество ячеек, в которых записаны числа).

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

Создаём новую сводную таблицу. Перетаскиваем поле «Название магазина» в раздел Valuesдва раза. Выбираем показатели Countи Count numbers. Первые столбцы — это количество заполненных ячеек, вторые — количество числовых значений в поле «Название магазина». Нули во вторых колонках говорят о том, что никаких чисел в этом поле не встречается (рис. 6.21). Обратим внимание, что оба показателя названы одинаково: Count of. Сразу же даём понятные названия колонкам, пока не забыли кто и где прячется.

Рис. 6.21. Количество значений

Задание. Создайте сводную таблицу с расчётом количества заполненных и числовых ячеек в поле «Название товара».

В процессе работы любого предприятия учётная база данных постоянно пополняется. Сотрудники могут даже вносить изменения и исправления в существующие записи. Поэтому требуется проводить повторный расчёт итоговых показателей в сводных таблицах. Это называется «Обновление».

Познакомимся с функцией обновления и заодно проверим, как работают наши счётчики. Используем наше предыдущую сводную таблицу со счётчиками. На рис. 6.22 показаны четыре шага нашего эксперимента.

Шаг 1. Перейдём в таблицу транзакций и рассмотрим поля «Название магазина», «Город» и «Категория товара». Нас интересуют первые две строки. Здесь указаны сведения по магазинам «Никитич» (г. Москва) и «Василиса» (г. Владивосток). Проданы были товары из категории «Крупы».

Шаг 2. Рассмотрим значения показателей в сводной таблице. Категория «Крупы», города Москва и Владивосток:

1105 — 0 — 931 — 0.

Шаг 3. Внесём изменения в таблицу транзакций. Сделаем в ней пару ошибок:

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

Шаг 4. Переходим к сводной таблице. Она пока что не изменилась. Щёлкаем по любой ячейке внутри сводной таблицы и выбираем в контекстном меню (рис. 6.23) пункт Refresh(Обновить). Сводная таблица действительно обновила значения показателей:

1104 — 0 — 931 — 1.

Что нам сообщают эти странные числа? Что количество заполненных полей уменьшилось. И что появилось одно числовое значение.

Рис. 6.22. Обновление итогов

Рис. 6.23. Вызов обновления

После проведения опыта нужно отменить все наши изменения в таблице транзакций. То есть «откатиться назад». Нажимаем кнопку отката (рис. 6.24). В выпадающем списке выделяем последние действия:

— Typing 2 (ввод двойки вместо названия магазина);

— Clear(удаление названия магазина);

— PivotTable Refresh(обновление сводной таблицы).

Убеждаемся, что мы отменили изменения и в таблице транзакций, и в сводной таблице.

Рис. 6.24. Откат изменений

Задание. Отмените последние изменения и убедитесь, что результат вас устраивает.

Обновить таблицу можно и по-другому, через верхнее меню (рис. 6.25). Это более длинный путь. Нужно выбрать таблицу, щёлкнув по любой ячейке внутри таблицы. В верхнем меню появится раздел инструментов по работе со сводными таблицами. Теперь выбираем следующий пункт:

Pivot Table Tools — Analyze — Data — Refresh

Настройка сводной таблицы — Анализ — Данные — Обновить.

Рис. 6.25. Обновление через меню

Задание. Вызовите обновление сводной таблицы через меню.

Всплывающая подсказка на рис. 6.25 говорит, что для обновления сводной таблицы можно нажать комбинацию клавиш Alt + F5. Это будет самый быстрый способ обновления. Если такая операция нужна очень часто, то можно эту комбинацию даже запомнить. Она сама запомнится. Если нажимать её по сто раз на дню. А если она не нужна, то и запоминать необязательно.