Как составить сводную таблицу и прочитать ответ, который она вам даст

1. Создайте один за четыре клика

Сделайте это в своем файле во время чтения. Он работает в любом списке, где первая строка — это заголовки, а каждая строка ниже — одна запись.

1. Щелкните любую ячейку внутри данных. Не выбирайте диапазон. Excel находит края за вас, и позволить ему это сделать — это тоже проверка: если предложенный им диапазон неправильный, в середине ваших данных появится пустая строка или столбец.
2. Вставлять, затем Сводная таблица.
3. Оставлять Новый рабочий лист выбрано. Нажмите OK.
4. В списке полей справа перетащите Регион в Строки коробка и Выручка в Значения коробка.

Вот что возвращает Эксель.

Row Labels    Sum of Revenue
East                    3040
North                   2495
South                   2670
West                    1685
Grand Total             9890

Общая сумма составляет 9890, что соответствует сумме, которую мы записали. Сводная точка читает каждую строку. Также проверьте один регион вручную: Восток — это порядки 1003, 1006, 1010 и 1013, поэтому 660 + 680 + 1100 + 600 = 3040. Вот и вся привычка проверки, и она стоит десять секунд.

Одна вещь уже заслуживает внимания. Регионы получились Восток, Север, Юг, Запад, в алфавитном порядке, а не в порядке, выбранном кем-либо. Поворотная точка сортирует свои метки в алфавитном порядке, пока вы не прикажете иначе, а диаграмма, построенная непосредственно на этой опорной точке, наследует алфавит. Сортировка рейтинга по собственному номеру это отдельный шаг, который вы должны сделать намеренно.

2. Строки — это список, Значения — число.

Перед пояснением: в источнике 16 строк, а в стержне 4. Скажите, что теперь означает одна строка этого стержня и куда пошли остальные 12 строк.

Сводная таблица отвечает на предложение вида "общий доход по регионам". Поле в строках за что. Поле в Значениях сколько?. Excel находит каждое отдельное значение в поле «Строки», присваивает каждому из них строку, затем складывает совпадающие исходные строки в эту строку и уменьшает стопку до одного числа.

Таким образом, одна сводная строка больше не является одним заказом. Это один регион. Это изменение называется grain стола, и это самая полезная вещь, которую нужно сказать вслух, прежде чем строить. Как только уровень детализации будет состоять из одной строки на регион, в результате больше не будет отдельного порядка: вы не можете спросить у этого центра, сколько стоил порядок 1010, потому что 1010 был свернут в Восток. Если вам нужно просмотреть отдельные заказы, значит уровень детализации, которое вы просили, было неправильным.

Если произнести предложение первым, вы также поймете, какое поле куда идет. «Доход по региону» помещает регион в строки. «Заказов на представителя» помещает представителя в ряды. Существует более длинная версия этой идеи в Сводная таблица — это вопрос, а не отчет, и это самое быстрое лекарство от взгляда на четыре пустые коробки.

Перетащите «Регион» наружу и «Продукт», и та же сводная таблица ответит на другой вопрос без перестройки.

Row Labels    Sum of Revenue
Chair                   3230
Desk                    5060
Lamp                    1600
Grand Total             9890

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

3. Функция, которую Excel выбирает за вас, и когда она неверна

Перед объяснением: бросьте ID заказа в «Ценности» вместо «Доход», а общая сумма Excel составляет 16 136. Скажите одним предложением, что измеряет это число.

Он ничего не измеряет. Excel суммировал идентификационные коды. Вот реальный результат.

Row Labels    Sum of OrderID
East                    4032
North                   5037
South                   4031
West                    3036
Grand Total            16136

5037 Норта — это 1001 + 1004 + 1007 + 1011 + 1014. Сложение номеров заказов дает число, и число в отчете считывается. Ничего не ошиблось, ничего не стало красным.

Правило, которому следует Excel, простое, и его стоит точно знать: если столбец полностью состоит из чисел, он использует сумма; если столбец содержит какой-либо текст или пробелы, он использует Считать. Столбец идентификатора полностью состоит из чисел, поэтому он суммируется. Excel не догадывается, что означает этот столбец, потому что не может; он смотрит только на то, какого типа являются значения.

Однострочный тест перед принятием любого поля значений: будет ли сложение двух из них вместе что-нибудь означать? Два дохода добавляют к большему доходу, поэтому Сумма прав. Два номера заказа ничего не добавляют, поэтому сумма неверна. Два почтовых индекса, два номера сотрудников, два года работы, два рейтинга из пяти — все это не выдерживает проверки. Есть более полный аккаунт Excel просто суммировал ваши идентификационные номера и ничего не сказал., включая четыре раза, когда это произошло в одной реальной сборке.

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

4. Второе поле: регион внизу, товар вверху.

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

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

Sum of Revenue   Chair    Desk    Lamp   Grand Total
East               680    1760     600          3040
North              935    1320     240          2495
South              850    1540     280          2670
West               765     440     480          1685
Grand Total       3230    5060    1600          9890

Чтобы убедиться, прочитайте одну ячейку: Восток и Стол равны 1760, что составляет порядок 1003 в 660 плюс порядок 1010 в 1100. Сумма обоих ребер по-прежнему равна 9890, по горизонтали и вниз.

Уровень детализации теперь составляет один ряд на регион. за продукт. Этот макет называется перекрестной таблицей, и это форма, в честь которой названа сводная таблица. Именно здесь обычно происходит настоящая находка, потому что два измерения одновременно показывают вам то, что скрывает одно измерение. Вот он Запад: 440 столов против 1540–1760 повсюду, и Запад — единственный регион, где офисный бизнес практически отсутствует.

Две вещи поменьше. Если в комбинации вообще нет строк, в сводной таблице отображается пустая ячейка, а не ноль, а пустое и ноль — это разные результаты. И более трех полей, сложенных в строки, создают таблицу, которую никто не читает; если вам нужно четыре, вам, вероятно, понадобятся две точки поворота.

5. Показать значения как: превращаем 3040 в 30,7%.

До пояснения: Восток — 3040 из 9890. Без калькулятора это примерно четверть или половина бизнеса?

Большинство людей не могут провести такое разделение в уме, и это является единственным аргументом в пользу этой функции. Щелкните правой кнопкой мыши любое число в сводной таблице, выберите Показать значения как, затем % от общей суммы. Тот же центр, тот же источник, другой ответ на вопрос.

Row Labels    Revenue    Share    Orders
East             3040    30.7%         4
North            2495    25.2%         5
South            2670    27.0%         4
West             1685    17.0%         3
Grand Total      9890   100.0%        16

Обратите внимание, что для этого потребовалось: доход дважды был перенесен в Ценности. Первая копия остается простой суммой, вторая копия получает значение «Показать значения как применительно к ней», а ID заказа в третий раз переходит в значение «Количество». Поле может появляться в «Значениях» столько раз, сколько вам нужно, каждый раз суммируясь по-разному, и именно так одна сводная таблица содержит рядом общую сумму, долю и количество.

Теперь находка выпадает. Компания Север разместила наибольшее количество заказов среди всех регионов (пять) и по-прежнему заняла третье место по выручке. Ее доход на заказ составляет 2495 ÷ 5 = 499, тогда как у Восток 3040 ÷ 4 = 760. Больше заказов, меньше заказов. Ни общая сумма, ни подсчет сами по себе этого не говорят; две колонки вместе делают.

Настоящее решение внутри функции «Показать значения как» заключается в том, какая сумма находится в знаменателе. % от общей суммы делится на 9,890. Вместо этого % от общего количества строк и % от общего количества столбцов делятся на край перекрестной таблицы, и в макете «Регион по продукту» они дают совершенно разные предложения: какая доля денег Востока пошла на столы или какая доля всех денег на столе пришла с Востока. Выбор знаменателя вот в чем весь навык, и это более важное решение, чем любой щелчок мышью.

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

6. Сгруппируйте даты по месяцам и месяцу, которого нет

Перед пояснением: заказы выполняются с января по май. Сгруппируйте их по месяцам и скажите, сколько строк вы ожидаете увидеть.

Поместите Дата заказа в Строки, и вы получите шестнадцать строк, по одной на дату, что вовсе не является сводкой. Щелкните правой кнопкой мыши любую дату в сводной таблице и выберите Группа, галочка Месяцыи нажмите ОК. Вот реальный результат.

Row Labels    Sum of Revenue
Jan                     2630
Feb                     2245
Mar                     2585
May                     2430
Grand Total             9890

Четыре ряда, а не пять. Апрельской линии нет, потому что апрельских заказов не было, а сводная таблица отображает только те значения, которые существуют в данных. Эйприл не набрал ноль баллов; Эйприл просто отсутствует, а отсутствие на странице выглядит совершенно как ничто.

Это имеет значение, как только кто-то читает колонку вниз. Глаз смотрит на январь, февраль, март, май и показывает четыре месяца подряд, поэтому майские 2430 сравниваются с мартовскими 2585 и представляют собой скромное падение на 6,0%. Правдивая фраза другая: продажи остановились на месяц, а затем вернулись примерно на мартовский уровень. Те же четыре цифры, две разные истории, и центральная точка не может сказать вам, какая из них правильная, потому что доказательства этого никогда не были подряд.

Исправление не является поворотной настройкой. Это календарь: небольшой список каждого месяца за период, с сопоставлением с ним итоговых показателей. Тогда месяц без продаж отображается как ноль, который вы видите, а не как пробел, который вы не можете увидеть. Если в ваших данных вообще возможен пробел, постройте календарь перед диаграммой.

Две полезные подробности о группировке. Если поставить галочку «Месяцы» и «Годы», то 2025 и 2026 годы будут разделены, а если забыть «Годы», то январь 2025 года и январь 2026 года незаметно сольются в одну строку «Январь». А группировка возможна только в том случае, если столбец действительно содержит даты; Если группа неактивна, ваши даты представляют собой текст, похожий на даты, что представляет собой другую проблему с другим решением.

7. Обновить: сводная таблица — это фотография, а не формула.

Перед пояснением: вы меняете одно число в исходной таблице. Что показывает точка поворота секунду спустя?

То же самое, что он показывал раньше. Сводная точка считывает источник один раз в частную копию, называемую сводным кешем, и каждый последующий перерасчет выполняется для этой копии. Редактирование источника его не трогает. Вот фактическая последовательность, выполненная в Эксель.

West in the pivot                        1685
Change order 1016 from 2 units to 20
Order 1016 revenue in the source          4400
West in the pivot, immediately            1685
Right-click, Refresh
West in the pivot                         5645

Проверьте окончательное число: Уэст был 1685 с порядком 1016 в 440, поэтому 1685 − 440 + 4400 = 5645. Средняя линия самая опасная. Пока вы не обновите сводную таблицу, она отображает правильную сводку данных, которых у вас больше нет, и нет визуальной разницы между новой сводной таблицей и устаревшей.

Это противоположно тому, как ведет себя формула, поэтому она цепляет людей. =SUM(A2:A17) обновляет мгновенные изменения A5. Поворот этого не делает. Щелкните правой кнопкой мыши и «Обновить» или «Анализ сводной таблицы», затем «Обновить все», и сделайте это рефлексом, который вы выполняете перед чтением, а не после того, как вам задали вопрос, в котором вы ошиблись.

Одна настройка устраняет большую часть риска. Щелкните правой кнопкой мыши сводную таблицу, выберите «Параметры сводной таблицы», вкладку «Данные», отметьте галочкой. Обновить данные при открытии файла. Любой, кто откроет книгу, увидит текущие числа, не зная о существовании этого раздела.

8. Счет, сумма и отдельный счет, который вы должны запросить.

Три резюме постоянно путаются, и разницу легче всего увидеть по одному столбцу.

уронить Представитель в Ценности. Представитель — это текст, поэтому Excel использует Считать, и вот что возвращается.

Row Labels     Count of Rep
East                      4
North                     5
South                     4
West                      3
Grand Total              16

Прочтите это внимательно. Там написано 5 для Норта, а у Норт ровно один представитель. Счет имеет значение строки, которые имеют значение, а не разные значения. Он ответил на вопрос «сколько повторений работает на севере», то есть 5, и это выглядит точно так же, как ответ на вопрос «сколько повторений работает на север», который равен 1.

Сводка, которая подсчитывает различные значения, Разное количество, а обычный пивот этого не предлагает. Я проверил это, а не предполагал: установка для поля значения значения Разное количество в обычной сводной таблице не удалась, а Excel отказывался устанавливать это свойство. Он становится доступным только в том случае, если сводная таблица построена на основе модели данных, что означает отметку. Добавьте эти данные в модель данных в диалоге во время создания. Этот флажок легко пропустить, и его нельзя будет впоследствии поставить галочкой; вместо этого вы перестраиваете ось.

Итак, практическое правило. Сумма по деньгам и количествам. Посчитайте, сколько записей. Разное количество для количества разных клиентов, представителей или продуктов. Если вы знаете, что вам это понадобится, установите флажок «Модель данных», пока вы все еще находитесь в диалоговом окне «Создание сводной таблицы».

Крайние случаи, которые тихо ломают ось

Шесть, каждая из которых стоила кому-то дня.

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

Новые строки добавлены в источник. Разворот, построенный на фиксированном диапазоне, например A1:H17 никогда не видит строку 18. Вместо этого создайте сводную таблицу на именованной таблице, созданной с помощью Ctrl+Т, и диапазон будет увеличиваться вместе с данными самостоятельно.

Конечные пробелы в метках. «Север» и «Север» — это две разные метки, поэтому в отчете по четырем регионам появляется пятый регион. Если в вашем списке строк больше записей, чем вы ожидали, прежде чем искать что-либо еще, найдите почти повторяющиеся записи.

Числа, хранящиеся в виде текста. Одно значение введено в виде текста, и сумма молча его пропускает. Сумма остается правдоподобной и короче ровно на одну строку. Если общая сумма близка, но неверна, заподозрите это, прежде чем подозревать разворот.

Объединенные ячейки в строке заголовка. Excel отказывается создавать сводную таблицу и сообщает, что имя поля недопустимо. Это означает, что ячейка заголовка пуста или объединена. Разъедините данные, дайте каждому столбцу реальный заголовок и повторите попытку.

Скопированная сводная таблица с общим кешем. Скопируйте и вставьте опорную точку, и копия обычно использует общий кеш оригинала, поэтому группировка дат по месяцам в одной автоматически группирует и другую. Если два центра продолжают меняться вместе, вот почему.