Как подсчитать различные и уникальные значения в Excel таблицах

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

 

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

 

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

1. Уникальные значения-это значения, которые появляются в списке только один раз.

1. Различные значения-это все разные значения в списке, т. е. уникальные значения плюс 1 st вхождений повторяющихся значений.

На следующем снимке экрана показана разница:

 

 

А теперь давайте посмотрим, как вы можете рассчитывать уникальные и отличные значения в Excel, используя формулы и функции сводной таблицы. В этой статье мы затронем следующие темы:

 

Как подсчитать уникальные значения в Excel; Подсчет уникальных значений в столбце; Подсчет уникальных текстовых значений; Считайте уникальные числа; Подсчет уникальных значений с учетом регистра; Как подсчитать различные значения в Excel; Подсчитывайте различные значения, игнорируя пустые ячейки; Формула для подсчета различных текстовых значений; Формула для подсчета различных чисел; Подсчет различных значений с учетом регистра; Как подсчитать уникальные и различные строки в Excel; Автоматическое подсчет различных значений в сводной таблице; Как подсчитать уникальные значения в Excel;

 

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

 

Подсчет уникальных значений в столбце

 

Предположим, у вас есть столбец имен в вашем листе Excel, и вам нужно подсчитать уникальные имена в этом столбце. Решение заключается в использовании функции SUM в сочетании с IF и COUNTIF:

 

=SUM(IF(COUNTIF(range, range)=1,1,0))

 

Примечание. Это формула массива, поэтому не забудьте нажать Ctrl + Shift + Enter, чтобы завершить его. Как только вы сделаете это, Excel автоматически заключит формулу в {фигурные скобки}, как на скриншоте ниже. Ни в коем случае не следует вводить фигурные скобки вручную, это не будет работать.

 

В этом примере мы подсчитываем уникальные имена в диапазоне A2:A10, поэтому наша формула принимает следующую форму:

 

=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))

 

Подсчет уникальных значений в Excel

 

 

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

 

Как работает формула уникальных значений Excel count

 

Как вы видите, в нашей уникальной формуле значений используются 3 различных функции-SUM, IF и COUNTIF. Глядя изнутри наружу, вот что делает каждая функция:

1. Функция COUNTIF подсчитывает, сколько раз каждое отдельное значение появляется в указанном диапазоне. В этом примере COUNTIF(A2:A10,A2:A10) возвращает массив {1;2;2;1;2;2;2;1;2}.
2. Функция IF оценивает каждое значение в массиве, возвращаемом COUNTIF, сохраняет все 1 (уникальные значения) и заменяет все другие значения нулями. Таким образом, функция IF(COUNTIF(A2:A10,A2:A10)=1,1,0)становится IF(1;2;2;1;2;2;2;1;2) = 1,1,0,тем, что превращается в массив {1;0;0;1;0;0;0;1;0}, где 1-уникальное значение, а 0-повторяющееся значение.
3. Наконец, функция SUM суммирует значения в массиве, возвращаемом IF, и выводит общее количество уникальных значений, что именно то, что мы хотели.

Совет. Чтобы узнать, к чему относится определенная часть формулы уникальных значений Excel, выберите эту часть в строке формул и нажмите клавишу F9.

 

Подсчет уникальных текстовых значений в Excel

 

Если ваш список Excel содержит как числовые, так и текстовые значения, и вы хотите считать только уникальные текстовые значения, добавьте функцию ISTEXT в Формулу массива, описанную выше:

 

=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))

 

Как известно, функция Excel ISTEXT возвращает TRUE, если вычисленное значение является текстом, в противном случае-FALSE. Поскольку звездочка (*) работает как оператор AND в формулах массива , функция IF возвращает 1 только в том случае, если значение является одновременно текстовым и уникальным, 0 в противном случае. А после того, как функция SUM сложит все единицы, вы получите количество уникальных текстовых значений в указанном диапазоне.

 

Не забудьте нажать Ctrl + Shift + Enter, чтобы правильно ввести формулу массива, и вы получите результат, аналогичный этому:

 

 

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

 

Подсчет уникальных числовых значений в Excel

 

Чтобы подсчитать уникальные числа в списке данных, используйте формулу массива, как мы только что использовали для подсчета уникальных текстовых значений, с единственной разницей, что вы вставляете ISNUMBER вместо ISTEXT в свою формулу уникальных значений:

 

=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))

 

 

Подсчет уникальных числовых значений в Excel Примечание. Поскольку Microsoft Excel хранит даты и время в виде серийных номеров, они также учитываются.

 

Подсчет уникальных значений с учетом регистра в Excel

 

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

 

=IF(SUM((--EXACT($A$2:$A$10,A2)))=1,"Unique","Dupe")

 

А затем используйте простую функцию COUNTIF для подсчета уникальных значений:

 

=COUNTIF(B2:B10, "unique")

 

 

Подсчет уникальных значений с учетом регистра в Excel

 

Подсчитайте различные значения в Excel (уникальные и 1-й повторяющиеся вхождения)

 

Чтобы получить количество различных значений в списке, используйте следующую формулу:

 

=SUM(1/COUNTIF(range, range))

 

Помните, что это формула массива, и поэтому вы должны нажать сочетание клавиш Ctrl + Shift + Enter вместо обычного нажатия клавиши Enter.

 

Кроме того, вы можете использовать функцию SUMPRODUCT и завершить формулу обычным способом, нажав клавишу Enter:

 

=SUMPRODUCT(1/COUNTIF(range, range))

 

Например, чтобы подсчитать различные значения в диапазоне A2:A10, вы можете пойти с любым из них:

 

=SUM(1/COUNTIF(A2:A10,A2:A10))

 

Или

 

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

 

 

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

 

Как работает формула Excel distinct

 

Как вы уже знаете, мы используем функцию COUNTIF, чтобы узнать, сколько раз каждое отдельное значение появляется в указанном диапазоне. В приведенном выше примере результатом функции COUNTIF является следующий массив: {2;2;3;1;2;2;3;1;3}.

 

После этого выполняется ряд операций деления, где каждое значение массива используется в качестве делителя с 1 в качестве дивиденда. Это превращает все повторяющиеся значения в дробные числа, соответствующие числу повторяющихся вхождений. Например, если значение отображается в списке 2 раза, оно генерирует 2 элемента в массиве со значением 0,5 (1/2=0,5). И если значение появляется 3 раза, он производит 3 элемента в массиве со значением 0.3(3). В нашем примере результатом 1/COUNTIF(A2:A10,A2:A10)) является массив {0.5;0.5;0.3(3);1;0.5;0.5;0.3(3);1;0.3(3)}.

 

Пока что это не имеет особого смысла? Это потому, что мы еще не применили функцию SUM / SUMPRODUCT. Когда одна из этих функций суммирует значения в массиве, сумма всех дробных чисел для каждого отдельного элемента всегда дает 1, независимо от того, сколько вхождений этого элемента существует в списке. И поскольку все уникальные значения отображаются в массиве как 1 (1/1=1), конечный результат, возвращаемый формулой, является общим числом всех различных значений в списке.

 

Формулы для подсчета различных значений различных типов

 

Как и в случае с подсчетом уникальных значений в Excel, можно использовать варианты базовой формулы Excel count distinct для обработки конкретных типов значений, таких как числа, текст и регистрозависимые значения.

 

Пожалуйста, помните, что все приведенные ниже формулы являются формулами массива и требуют нажатия клавиш Ctrl + Shift + Enter.

 

Подсчитывайте различные значения, игнорируя пустые ячейки

 

Если столбец, в котором требуется подсчитать различные значения, может содержать пустые ячейки, следует добавить функцию IF, которая будет проверять указанный диапазон на наличие пробелов (в этом случае базовая формула Excel distinct, описанная выше, возвращает ошибку #DIV/0):

 

=SUM(IF(range<>"",1/COUNTIF(range, range), 0))

 

Например, для подсчета различных значений в диапазоне A2:A10 используйте следующую формулу массива:

 

=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))

 

 

Формула для подсчета различных значений игнорирует пустые ячейки

 

Формула для подсчета различных текстовых значений

 

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

 

Как вы можете легко догадаться, мы просто встроим функцию ISTEXT в нашу формулу Excel count distinct:

 

=SUM(IF(ISTEXT(range),1/COUNTIF(range, range),""))

 

И вот вам реальный пример формулы:

 

=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))

 

Формула для подсчета различных чисел

 

Для подсчета различных числовых значений (чисел, дат и времени) используйте функцию ISNUMBER:

 

=SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),""))

 

Например, чтобы посчитать все различные числа в диапазоне A2:A10, используйте следующую формулу:

 

=SUM(IF(ISNUMBER(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))

 

Подсчет различных значений с учетом регистра в Excel

 

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

 

=IF(SUM((--EXACT($A$2:$A2,$A2)))=1,"Distinct","")

 

Как вы помните, все формулы массива в Excel требуют нажатия клавиш Ctrl + Shift + Enter.

 

После того, как приведенная выше формула будет завершена, вы можете рассчитывать" отличные " значения с помощью обычной формулы COUNTIF, такой как эта:

 

=COUNTIF(B2:B10, "distinct")

 

 

Подсчет различных значений с учетом регистра в Excel

 

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

 

=SUM(IFERROR(1/IF($A$2:$A$10<>"", FREQUENCY(IF(EXACT($A$2:$A$10, TRANSPOSE($A$2:$A$10)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), ""), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0))

 

Подсчет уникальных и отличных строк в Excel

 

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

 

Например, для подсчета уникальных или различных имен на основе значений в Столбцах A( имя) и B (фамилия) используйте одну из следующих формул:

 

Формула для подсчета уникальных строк:

 

=SUM(IF(COUNTIFS(A2:A10,A2:A10, B2:B10,B2:B10)=1,1,0))

 

Формула для подсчета различных строк:

 

=SUM(1/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))

 

 

Подсчет уникальных и отчетливых строк в Excel

 

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

 

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

 

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

 

 

Число различных объектов в сводной таблице

 

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

 

1. Выберите данные для включения в сводную таблицу, перейдите на вкладку Вставка, группа таблицы и нажмите кнопку Сводная таблица.

 

2. В диалоговом окне создать сводную таблицу выберите, следует ли разместить сводную таблицу на новом или существующем листе, и обязательно установите флажок добавить эти данные в модель данных.

 

 

Установите флажок "добавить эти данные в модель данных".

 

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

 

4. Переместите поле, для которого требуется вычислить число различных объектов (поле элемента в данном примере) , в область значений, щелкните его и в раскрывающемся меню выберите параметры значения поля... :

 

 

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

 

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

 

 

Прокрутите вниз до самого последнего варианта и выберите Distinct Count.

 

- Готово! Вновь созданная сводная таблица будет отображать отдельный счетчик, как показано на самом первом скриншоте в этом разделе.

 

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

 

Именно так вы считаете различные и уникальные значения в Excel.

 

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

 

Источник перевода: https://www.ablebits.com/office-addins-blog/2016/04/07/how-to-count-distinct-and-unique-values-in-excel/