В этом руководстве вы узнаете, как подсчитывать уникальные значения в Excel с помощью формул (функции COUNTIF и SUMPRODUCT).

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

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

Для целей этого урока я назову диапазон A2: A10 как NAMES. В дальнейшем мы будем использовать этот именованный диапазон в формулах.

В этом наборе данных есть повторение в диапазоне NAMES. Чтобы получить количество уникальных имен из этого набора данных (A2: A10), мы можем использовать комбинацию функций COUNTIF и SUMPRODUCT, как показано ниже:

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

Как работает эта формула?

Давайте разберем эту формулу, чтобы лучше понять:

  • COUNTIF(NAMES,NAMES)
    • Эта часть формулы возвращает массив. В приведенном выше примере это будет {2; 2; 3; 1; 3; 1; 2; 3; 2}. Числа здесь показывают, сколько раз значение встречается в заданном диапазоне ячеек.
      Например, имя Боб, которое встречается в списке дважды, поэтому для Боба будет возвращено число 2. Точно так же Стив встречается трижды, и поэтому Стиву возвращается 3.
  • 1/COUNTIF(NAMES,NAMES)
    • Эта часть формулы вернет массив — {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
      Поскольку мы разделили 1 на массив, он возвращает этот массив.
      Например, первый элемент возвращенного выше массива был 2. Когда 1 делится на 2, возвращается 0,5.
  • SUMPRODUCT(1/COUNTIF(NAMES,NAMES))
    • SUMPRODUCT просто складывает все эти числа. Обратите внимание, что если Боб встречается в списке дважды, приведенный выше массив возвращает 0,5 везде, где имя Боба появляется в списке. Точно так же, поскольку Стив появляется в списке трижды, массив возвращает 0,33333333 всякий раз, когда появляется имя Стива. Когда мы складываем числа для каждого имени, он всегда будет возвращать 1. А если мы сложим все числа, он вернет общее количество уникальных имен в списке.

Эта формула работает до тех пор, пока в диапазоне не останется пустых ячеек. Но если у вас есть пустые ячейки, он вернет # DIV / 0! ошибка.

Как обращаться с пустыми ячейками?

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

Теперь, если мы используем ту же формулу, которую использовали выше, часть формулы COUNTIF возвращает массив {2; 0; 3; 1; 3; 1; 2; 3; 1}. Поскольку в ячейке A3 нет текста, ее счетчик возвращается как 0.

И поскольку мы делим 1 на весь этот массив, возвращается # DIV / 0! ошибка.

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

=SUMPRODUCT((1/COUNTIF(NAMES,NAMES&””)))

Одно изменение, которое мы внесли в эту формулу, — это часть критериев функции COUNTIF. Мы использовали NAMES& ”” вместо NAMES. Таким образом формула вернет количество пустых ячеек (раньше она возвращала 0 —  там, где была пустая ячейка).

ПРИМЕЧАНИЕ. Эта формула будет подсчитывать пустые ячейки как уникальное значение и возвращать его в результате.

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

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

=SUMPRODUCT((NAMES<>””)/COUNTIF(NAMES,NAMES&””))

В этой формуле вместо 1 в качестве числителя мы использовали NAMES <> ””. Это возвращает массив значений ИСТИНА и ЛОЖЬ. Он возвращает FALSE всякий раз, когда есть пустая ячейка. Поскольку в расчетах ИСТИНА равно 1, а ЛОЖЬ равно 0, пустые ячейки не учитываются, так как числитель равен 0 (ЛОЖЬ).

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

Как подсчитать уникальные значения в Excel, которые являются текстом

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

Вот формула, которая будет подсчитывать уникальные текстовые значения в Excel:

=SUMPRODUCT((ISTEXT(NAMES)/COUNTIF(NAMES,NAMES&””)))

Все, что мы сделали, это использовали формулу ISTEXT (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит текст, и ЛОЖЬ, если нет. Он не будет считать пустые ячейки, но будет считать ячейки с пустой строкой («»).

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

Вот формула, которая будет подсчитывать уникальные числовые значения в Excel

=SUMPRODUCT((ISNUMBER(NAMES))/COUNTIF(NAMES,NAMES&””))

Здесь мы используем ISNUMBER (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит числовой тип данных, и ЛОЖЬ, если нет. Пустые ячейки не учитываются.

Функция COUNTIF — подсчет уникальных значений в Excel