Подсчет различных значений в сводной таблице Excel (простое пошаговое руководство)

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

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

В этом руководстве я покажу вам, как подсчитывать отдельные значения, а также уникальные значения в сводной таблице Excel. Но прежде чем я перейду к подсчету различных значений, важно понять разницу между «уникальным счетом» и «уникальным счетом».

Отличный счет против уникального счетчика

Хотя это может показаться одним и тем же, но это не так.

Ниже приведен пример набора данных с именами, и я отдельно перечислил уникальные и уникальные имена.

Уникальные значения / имена те, которые возникают только один раз. Это означает, что все повторяющиеся и повторяющиеся имена не уникальны. Уникальные имена перечислены в столбце C в приведенном выше наборе данных.

Отличные ценности / имена те, которые встречаются в наборе данных хотя бы один раз. Таким образом, если имя встречается три раза, оно все равно считается одним отдельным именем. Этого можно достичь, удалив повторяющиеся значения / имена и сохранив все разные. Отличительные имена перечислены в столбце B приведенного выше набора данных.

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

Подсчет различных значений в сводной таблице Excel

Предположим, у вас есть данные о продажах, как показано ниже:

Предположим, вы хотите найти ответ на следующие вопросы:

  1. Сколько торговых представителей работает в каждом регионе (это не что иное, как отдельное количество торговых представителей в каждом регионе)?
  2. Сколько торговых представителей продали принтер в2021-2022 гг.?

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

Если вы используете Excel 2013 или более поздние версии, в сводную таблицу есть встроенная функция, которая быстро дает вам точный счет. И если вы используете Excel 2010 или более ранние версии, вам придется изменить исходные данные, добавив вспомогательный столбец.

Полезное:  Как быстро выбрать удаленную ячейку или диапазон Excel

В этом руководстве рассматриваются следующие два метода:

  • Добавление вспомогательного столбца в исходный набор данных для подсчета уникальных значений (работает во всех версиях).
  • Добавление данных в модель данных и использование опции Distinct Count (доступно в Excel 2013 и более поздних версиях).

В этой статье Роджер показывает третий метод (который он называет методом Pivot the Pivot Table).

Давайте начнем!

Добавление вспомогательного столбца в набор данных

Примечание. Если вы используете Excel 2013 и более поздние версии, пропустите этот метод и перейдите к следующему (поскольку он использует встроенную функцию сводной таблицы — Отличный граф).

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

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

Предположим, у меня есть набор данных, как показано ниже:

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

= ЕСЛИ (СЧЁТЕСЛИ ($ C $ 2: C2, C2; $ B $ 2: B2, B2)> 1,0,1)

В приведенной выше формуле используется функция СЧЁТЕСЛИМН, чтобы подсчитать, сколько раз имя появляется в данном регионе. Также обратите внимание, что диапазон критериев — $ C $ 2: C2 и $ B $ 2: B2. Это означает, что он продолжает расширяться по мере того, как вы спускаетесь по столбцу.

Например, в ячейке E2 диапазоны критериев: $ C $ 2: C2 и $ B $ 2: B2, а в ячейке E3 эти диапазоны расширяются до $ C $ 2: C3 и $ B $ 2: B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

Поскольку мы хотим получить только отдельные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только отдельные имена, а не повторения.

Ниже показано, как будет выглядеть ваш набор данных после добавления вспомогательного столбца.

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

Ниже приведены шаги для этого:

  1. Выберите любую ячейку в наборе данных.
  2. Щелкните вкладку «Вставить«.
  3. Нажмите на сводную таблицу (или воспользуйтесь сочетанием клавиш — ALT + N + V)
  4. В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включают вспомогательный столбец) и выбран параметр «Новый рабочий лист».
  5. Щелкните ОК.
Полезное:  Как удалить листы в Excel (ярлыки + VBA)

Вышеупомянутые шаги позволят вставить новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «D Count» в область «Значения».

Вы получите сводную таблицу, как показано ниже:

Теперь вы можете изменить заголовок столбца с «Sum of D count» на «Sales Rep».

Недостатки использования вспомогательной колонки:

Хотя этот метод довольно прост, я должен выделить несколько недостатков, связанных с изменением исходных данных в сводной таблице:

  • Источник данных со вспомогательным столбцом не такой динамичный, как сводная таблица. Хотя с помощью сводной таблицы вы можете разрезать данные как угодно, когда вы используете вспомогательный столбец, вы теряете часть этой способности. Допустим, вы добавили вспомогательный столбец, чтобы получить количество отдельных торговых представителей в каждом регионе. Теперь, что, если вы также хотите получить отчетливое количество торговых представителей, продающих принтеры. Вам нужно будет вернуться к исходным данным и изменить формулу вспомогательного столбца (или добавить новый вспомогательный столбец).
  • Поскольку вы добавляете больше данных в источник сводной таблицы (которая также добавляется в сводный кеш), это может привести к увеличению размера файла Excel.
  • Мы используем формулу Excel, это может замедлить работу вашей книги Excel, если у вас есть тысячи строк данных.

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

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

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

Ниже приведены шаги, чтобы получить отчетливое значение счетчика в сводной таблице:

  1. Выберите любую ячейку в наборе данных.
  2. Щелкните вкладку Вставка.
  3. Нажмите на сводную таблицу (или воспользуйтесь сочетанием клавиш — ALT + N + V)
  4. В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно и «Новый рабочий лист» выбран.
  5. Установите флажок «Добавить эти данные в модель данных».
  6. Щелкните ОК.

Вышеупомянутые шаги позволят вставить новый лист с новой сводной таблицей.

Перетащите область в область «Строки» и «Торговый представитель» в область «Значения«. Вы получите сводную таблицу, как показано ниже:

В приведенной выше сводной таблице указано общее количество торговых представителей в каждом регионе (а не отдельное количество).

Чтобы получить точное количество в сводной таблице, выполните следующие действия:

  1. Щелкните правой кнопкой мыши любую ячейку в столбце «Количество торговых представителей».
  2. Нажмите на Настройки поля значений.
  3. В диалоговом окне «Параметры поля значения» выберите «Distinct Count» в качестве типа расчета (возможно, вам придется прокрутить список вниз, чтобы найти его).
  4. Щелкните ОК.
Полезное:  Как преобразовать серийные номера в даты в Excel (2 простых способа)

Вы заметите, что название столбца изменится с «Количество торговых представителей» на «Отличное количество торговых представителей». Вы можете изменить его на все, что захотите.

Некоторые вещи, которые вы знаете, добавляя данные в модель данных:

  • Если вы сохраните данные в модели данных, а затем откроете их в более старой версии Excel, появится предупреждение: «Некоторые функции сводной таблицы не будут сохранены». Вы можете не увидеть отдельное количество (и модель данных) при открытии в более старой версии, которая его не поддерживает.
  • Когда вы добавляете данные в модель данных и составляете сводную таблицу, в ней не будут отображаться параметры для добавления вычисляемых полей и вычисляемых столбцов.

Что делать, если вы хотите подсчитывать уникальные значения (а не отдельные значения)?

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

Помните: уникальные значения и разные значения — это не одно и то же. Щелкните здесь, чтобы узнать разницу.

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

В таких случаях вам нужно создать один из нескольких вспомогательных столбцов.

В этом случае поможет следующая формула:

ЕСЛИ (ЕСЛИ (СЧЁТЕСЛИ ($ C $ 2: $ C $ 1001; C2; $ B $ 2: $ B $ 1001; B2) / СЧЁТЕСЛИ ($ C $ 2: $ C $ 1001; C2) 1,0,1); 0)

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

Если имя встречается более чем в одном регионе, возвращается 0, иначе возвращается единица.

Формула также проверяет, повторяется ли имя в том же регионе или нет. Если имя повторяется, только первый экземпляр имени возвращает значение 1, а все остальные экземпляры возвращают 0.

Это может показаться немного сложным, но это опять же зависит от того, чего вы пытаетесь достичь.

Итак, если вы хотите подсчитать уникальные значения в сводной таблице, используйте вспомогательные столбцы, а если вы хотите подсчитать отдельные значения, вы можете использовать встроенные функции (в Excel 2013 и выше) или можете использовать вспомогательный столбец.

Vip Excel: cоветы по работе с Эксель, таблицы и формулы
Добавить комментарий