В этом руководстве вы узнаете, как подсчитывать уникальные значения в Excel с помощью формул (функции СЧЁТЕСЛИ и СУММПРОИЗВ).
Как подсчитать уникальные значения в Excel
Допустим, у нас есть набор данных, как показано ниже:
Для целей этого урока я назову диапазон A2: A10 как ИМЕНА. В дальнейшем мы будем использовать этот именованный диапазон в формулах.
См. Также: Как создавать именованные диапазоны в Excel.
В этом наборе данных есть повторение в диапазоне NAMES. Чтобы получить количество уникальных имен из этого набора данных (A2: A10), мы можем использовать комбинацию функций COUNTIF и SUMPRODUCT, как показано ниже:
= СУММПРОИЗВ (1 / СЧЁТЕСЛИ (ИМЕНА; ИМЕНА))
Как работает эта формула?
Давайте разберемся с этой формулой, чтобы лучше понять:
- СЧЁТЕСЛИ (ИМЕНА, ИМЕНА)
- Эта часть формулы возвращает массив. В приведенном выше примере это будет {2; 2; 3; 1; 3; 1; 2; 3; 2}. Числа здесь показывают, сколько раз значение встречается в заданном диапазоне ячеек.
Например, имя Боб, которое встречается в списке дважды, поэтому для Боба будет возвращено число 2. Точно так же Стив встречается трижды, и поэтому Стиву возвращается 3.
- Эта часть формулы возвращает массив. В приведенном выше примере это будет {2; 2; 3; 1; 3; 1; 2; 3; 2}. Числа здесь показывают, сколько раз значение встречается в заданном диапазоне ячеек.
- 1 / СЧЁТЕСЛИ (ИМЕНА, ИМЕНА)
- Эта часть формулы вернет массив - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
Поскольку мы разделили 1 на массив, он возвращает этот массив.
Например, первый элемент возвращенного выше массива был 2. Когда 1 делится на 2, возвращается 0,5.
- Эта часть формулы вернет массив - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
- СУММПРОИЗВ (1 / СЧЁТЕСЛИ (ИМЕНА, ИМЕНА))
- SUMPRODUCT просто складывает все эти числа. Обратите внимание, что если Боб встречается в списке дважды, приведенный выше массив возвращает 0,5 везде, где имя Боба появилось в списке. Точно так же, поскольку Стив появляется в списке трижды, массив возвращает 0,33333333 всякий раз, когда появляется имя Стива. Когда мы складываем числа для каждого имени, он всегда будет возвращать 1. А если мы сложим все числа, он вернет общее количество уникальных имен в списке.
Эта формула работает до тех пор, пока в диапазоне не останется пустых ячеек. Но если у вас есть пустые ячейки, он вернет # DIV / 0! ошибка.
Как обращаться с пустыми ячейками?
Давайте сначала поймем, почему он возвращает ошибку, если в диапазоне есть пустая ячейка. Предположим, у нас есть набор данных, как показано ниже (с пустой ячейкой A3):
Теперь, если мы используем ту же формулу, которую использовали выше, часть формулы СЧЁТЕСЛИ возвращает массив {2; 0; 3; 1; 3; 1; 2; 3; 1}. Поскольку в ячейке A3 нет текста, ее счетчик возвращается как 0.
И поскольку мы делим 1 на весь этот массив, он возвращает # DIV / 0! ошибка.
Чтобы обработать эту ошибку деления в случае пустых ячеек, используйте следующую формулу:
= СУММПРОИЗВ ((1 / СЧЁТЕСЛИ (ИМЕНА, ИМЕНА & ””)))
Одно изменение, которое мы внесли в эту формулу, - это часть критериев функции СЧЁТЕСЛИ. Мы использовали ИМЕНА & ”” вместо ИМЕНА. Таким образом формула вернет количество пустых ячеек (раньше она возвращала 0 там, где была пустая ячейка).
ПРИМЕЧАНИЕ. Эта формула будет подсчитывать пустые ячейки как уникальное значение и возвращать его в результате.
В приведенном выше примере результат должен быть 5, но он возвращает 6, поскольку пустая ячейка считается одним из уникальных значений.
Вот формула, которая обрабатывает пустые ячейки и не учитывает их в конечном результате:
= СУММПРОИЗВ ((ИМЕНА ””) / СЧЁТЕСЛИ (ИМЕНА, ИМЕНА & ””))
В этой формуле вместо 1 в качестве числителя мы использовали ИМЕНА «». Это возвращает массив значений ИСТИНА и ЛОЖЬ. Он возвращает FALSE всякий раз, когда есть пустая ячейка. Поскольку в расчетах ИСТИНА равно 1, а ЛОЖЬ равно 0, пустые ячейки не учитываются, поскольку числитель равен 0 (ЛОЖЬ).
Теперь, когда у нас готов базовый скелет формулы, мы можем пойти дальше и подсчитать различные типы данных.
Как подсчитать уникальные значения в Excel, которые являются текстом
Мы будем использовать ту же концепцию, о которой говорилось выше, для создания формулы, которая будет подсчитывать только уникальные текстовые значения.
Вот формула, которая будет подсчитывать уникальные текстовые значения в Excel:
= СУММПРОИЗВ ((ISTEXT (ИМЕНА) / СЧЁТЕСЛИ (ИМЕНА, ИМЕНА & ””)))
Все, что мы сделали, это использовали формулу ISTEXT (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит текст, и ЛОЖЬ, если нет. Он не будет подсчитывать пустые ячейки, но будет подсчитывать ячейки с пустой строкой («»).
Как подсчитать уникальные числовые значения в Excel
Вот формула, которая будет подсчитывать уникальные числовые значения в Excel
= СУММПРОИЗВ ((ЕЧИСЛО (ИМЕНА)) / СЧЁТЕСЛИ (ИМЕНА, ИМЕНА & ””))
Здесь мы используем ISNUMBER (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит числовой тип данных, и ЛОЖЬ, если нет. Пустые ячейки не учитываются.
