В реальной жизни не все ячейки могут быть заполнены. И бывают всевозможные ошибки и опечатки. Обычно такие нестыковки расчищают при наполнении хранилища данных. Однако, кое-что может проникнуть и на следующий уровень.
Создаём новую сводную таблицу. Перетаскиваем поле «Название магазина» в раздел Values два раза. Выбираем показатели Count и Count numbers. Первые столбцы это количество заполненных ячеек, вторые количество числовых значений в поле «Название магазина». Нули во вторых колонках говорят о том, что никаких чисел в этом поле не встречается (рис. 6.21). Обратим внимание, что оба показателя названы одинаково: Count of. Сразу же даём понятные названия колонкам, пока не забыли кто и где прячется.
Рис. 6.21. Количество значений
Задание. Создайте сводную таблицу с расчётом количества заполненных и числовых ячеек в поле «Название товара».
6.4. Обновление сводной таблицы
В процессе работы любого предприятия учётная база данных постоянно пополняется. Сотрудники могут даже вносить изменения и исправления в существующие записи. Поэтому требуется проводить повторный расчёт итоговых показателей в сводных таблицах. Это называется «Обновление».
Познакомимся с функцией обновления и заодно проверим, как работают наши счётчики. Используем наше предыдущую сводную таблицу со счётчиками. На рис. 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. Это будет самый быстрый способ обновления. Если такая операция нужна очень часто, то можно эту комбинацию даже запомнить. Она сама запомнится. Если нажимать её по сто раз на дню. А если она не нужна, то и запоминать необязательно.
Задание. Вызовите обновление сводной таблицы, нажав комбинацию клавиш.
Но и это ещё не всё! Нам дают возможность обновлять сводную таблицу при каждом открытии файла. Для этого выбираем в верхнем меню (рис. 6.26) следующий пункт:
Pivot Table Tools Analyze Pivot Table Options Options.
Рис. 6.26. Параметры сводной таблицы
Появляется диалоговое окно Pivot Table options (Настройка сводной таблицы), см. рис. 6.27. Открываем вкладку Data. Выбираем пункт
Refresh data when opening the file
Обновлять данные при открытии файла.
Рис. 6.27. Настройка автоматического обновления
Задание. Установите автоматическое обновление последней сводной таблицы. Прочитайте предупреждение. Выясните, что на самом деле будет обновляться при открытии файла.
7. Иерархия
В сводных таблицах можно организовать ИЕРАРХИЮ. По сути, это представление наших обобщённых показателей в виде дерева. В иерархии может быть несколько уровней. Можно сказать, что это вложенность уровней друг в друга.
С иерархией мы встречаемся и в обыной жизни. Например, в почтовом адресе.
Задание. Сделайте зарисовку схемы административного деления России в виде иерархии начиная от страны в целом и заканчивая районом города. Сколько уровней можно здесь указать?
В наших примерах иерархия образуется естественным образом. Фактически, у нас уже есть две иерархии: «города магазины» и «категории товары». В одном городе есть несколько магазинов. В рамках одной категории товаров есть несколько наименований. Количество уровней иерархии может быть гораздо больше.