Разблокировка расширенных вычислений по сводной таблице Расширенные сводные расчеты позволяют получить средние значения, экстремумы, доли в итоговых показателях, пропорции внутри категорий и текущие показатели. Компактный набор данных из 160 поставок летом 2021 года по двум категориям ‑ зерновым и напиткам — позволяет легко отслеживать каждый результат. Овладение этими инструментами повышает гибкость и контроль при проведении сводного анализа.
Преобразование диапазона в таблицу Excel и размещение сводного холста Преобразуйте исходный диапазон в таблицу Excel, чтобы стабилизировать ссылки и упростить фильтрацию. Вставьте сводную таблицу на том же листе над данными, присвойте ей понятное название и отключите автоматическое изменение ширины столбцов. Зарезервируйте достаточно места, чтобы результаты и проверка достоверности могли удобно сочетаться.
Формирование сводной таблицы по категориям, регионам и городам Расположите сетку таким образом, чтобы категории товаров пересекались с регионами и городами. Приведите в порядок метки и ширину столбцов, чтобы было удобно просматривать. Эта структура определяет критерии, которые каждая сводная ячейка будет использовать при выборе и суммировании исходных строк.
Внутри сводной ячейки: Фильтрация на основе критериев, затем агрегация Для любой ячейки сводная таблица отфильтровывает исходные строки в соответствии с метками строк и столбцов, а затем применяет выбранную статистику. При двойном щелчке по значению создается подробный лист, в котором указаны точные строки, вносящие вклад. Например, Bremen grains - это восемь отфильтрованных строк, выручка по которым суммируется в этой ячейке.
Проверка расчетов с помощью детализации и ручных фильтров Отфильтруйте таблицу Excel по Бремену и зерну и просуммируйте выручку, чтобы отразить выбор сводной таблицы и подтвердить результат. Используйте тот же подход для промежуточных итогов, например, по напиткам в Западном регионе, чтобы увидеть идентичную логику на более широком уровне. Соответствие между ручными проверками и сводными результатами подтверждает правильность рабочего процесса выбора, а затем агрегирования.
Область Фильтров Также определяет набор данных Поля, размещенные в области Фильтров, дополнительно ограничивают количество строк, которые используются при каждом вычислении. Например, при выборе конкретных клиентов все значения ограничиваются этим подмножеством. Метки строк, столбцов и фильтры вместе определяют данные, лежащие в основе каждого результата.
Переход от суммы к среднему значению и другим агрегациям Измените операцию в настройках поля "Значение", чтобы вычислять средние значения вместо сумм, когда в вопросе используются типичные значения. Средние значения для бременского зерна или мюнхенских напитков соответствуют среднему значению отфильтрованных строк, что подтверждает правильность. Помимо средних значений, Excel предлагает значения количества, максимума, минимума, продукта и подсчета, а также дополнительные статистические показатели, доступные по мере необходимости.
Применение изменений агрегации непосредственно из сводной таблицы Щелкните правой кнопкой мыши на любом значении, чтобы переключить статистику на лету, не открывая отдельных диалоговых окон. Count возвращает количество совпадающих записей, в то время как Max и Min отображают крайние значения отфильтрованного набора. Product умножает все включенные значения, часто давая очень высокие результаты из-за сложения.
Обзор дополнительных расчетов: Доли, изменения и рейтинг Меню "Дополнительные вычисления" накладывает дополнительные вычисления на базовую статистику. Параметры сгруппированы по трем темам: доли в итоговых показателях, различия по сравнению с другими элементами и ранжирование или относительный вес. Измените значение на "Сумма", чтобы примеры были интуитивно понятны и фокусировали внимание на преобразовании, а не на базовой математике.
Процент от общей суммы с параллельными абсолютными значениями Дважды заполните поле выручка значениями, чтобы показать необработанные суммы и их процент от общей суммы. Для Bremen grains сводная таблица делит 1 208,40 на общие 94 251,42, чтобы отобразить 1,28%. Та же идея работает и с другими агрегациями: используя Count, Berlin beverages показывает 17 из 160 записей, что составляет около 10,5%.
Процент от общего количества столбцов и процент от общего количества строк Процент от общего значения по столбцу делится на промежуточный итог по столбцу, чтобы выявить вертикальную структуру. Процент от общего значения по строке делится на промежуточный итог по строке, чтобы выявить горизонтальную структуру. При переключении между ними одни и те же данные переформулируются для получения ответов на различные вопросы о соотношении.
Поделиться по отношению к выбранному элементу Доля отображает каждое значение по отношению к конкретному выбранному вами базовому элементу. При использовании дат в строках, если в качестве базового выбрать август, каждый месяц будет отображаться в виде доли августа в своем столбце. Альтернативные схемы размещения, такие как размещение значений в строках, изменяют отображение, но не основные вычисления.
Процентные показатели, различия, текущие итоги и ранжирование на основе родительских данных Процент родительской строки или столбца делит элемент на его непосредственный промежуточный итог, например, на день в соответствующем месяце или на долю зерна в июне. Разница от сообщает об абсолютном изменении по сравнению с другим элементом — Обратная сторона сравнивается с предыдущим элементом, рядом со следующим — и любые ошибки из-за несоответствия уровней могут быть скрыты в параметрах сводной таблицы; Разница в процентах от показывает то же изменение в процентах. Текущий итог накапливает значения в выбранном поле, а процент от текущего итога отражает совокупную долю; в табличной форме ежемесячный промежуточный итог указывает на завершение накопления. Rank присваивает порядковые позиции по значению от наименьшего к наибольшему или наоборот, в то время как Index вычисляет конкретный показатель относительной доли, который используется нечасто.