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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[expert_bq id=»1570″]Если столбец, в котором требуется подсчитать различные значения, может содержать пустые ячейки, следует добавить функцию IF, которая будет проверять указанный диапазон на наличие пробелов в этом случае базовая формула Excel distinct, описанная выше, возвращает ошибку DIV 0. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Вот общая задача, которую все пользователи Excel должны выполнять один раз в то время. У вас есть список данных, и вам нужно узнать количество уникальных значений в этом списке. Как ты это делаешь? Проще, чем вы можете подумать ниже вы найдете несколько формул для подсчета уникальных значений различных типов.

Как посчитать количество ячеек в Excel

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

Если разница между прописными и строчными буквами критична для вас, самый простой способ подсчета — создать вспомогательный столбец со следующей формулой массива для определения повторяющихся и уникальных элементов:
[expert_bq id=»1570″]Единственное отличие состоит в том, что вы используете функцию СЧЁТЕСЛИ вместо СЧЁТЕСЛИ, которая позволяет вам указывать сразу несколько столбцов для проверки их уникальности. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Примечание. Поскольку логический элемент управления в операторе IF содержит массив, наше выражение немедленно становится формулой массива, которая требует ввода с помощью Ctrl + Shift + Enter. Поэтому SUMPRODUCT был заменен на SUM.
В нашем примере это выглядит так:

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

Помимо этого, у вас есть возможность задать условия И/ИЛИ. Только во втором случае придется использовать сразу несколько правил. Смотрим: вам необходимо найти клетки, в которых слова начинаются с буквы В или Р — пишете =СЧЕТЕСЛИ(А1:Е4;«В*»)+ СЧЕТЕСЛИ(А1:Е4;«Р*»).
[expert_bq id=»1570″]Если Вам нужно посчитать количество заполненных блоков в Excel, выделите диапазон ячеек в документе существуют различные способы, прочтите про них, перейдя по ссылке и в Строке состояния обратите внимание на пункт Количество. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Когда нужно подсчитать элементы с определённым значением, то есть соответствующие какому-то формату, применяется функция СЧЁТЕСЛИ(массив;критерий). Чтобы вам было понятнее, следует разобраться в терминах.

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

Рассмотренный выше пример не дает возможности сохранить результаты подсчета и отображать их в ячейке на листе. Более того, часто необходимо учитывать и пустые позиции. Для этого удобно пользоваться формулой ЧСТРОК.

[expert_bq id=»1570″]Как вы можете видеть на скриншоте выше, мы получили общее количество уникальных текстовых значений, исключая пустые ячейки, числа, логические выражения и ошибки ИСТИНА и ЛОЖЬ. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Все, что мы сделали, это использовали формулу ISTEXT (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит текст, и ЛОЖЬ, если нет. Он не будет считать пустые ячейки, но будет считать ячейки с пустой строкой («»).

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

Например, она может подсказать, когда выгодно продавать и покупать акции, если вы настроите нужный уровень цен. Для этого в формулу через точку с запятой добавляем условие — цена акции выросла до определенного значения — и нужную реакцию программы — подсказку «продавать».
[expert_bq id=»1570″]Также можно выделить значения, которые находятся в определенном интервале в условиях форматирования между , содержат нужный текст текст содержит , или задать сразу несколько условий. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Например, можно подсветить месяцы, когды вы тратили больше, чем зарабатывали, или задать цветовое кодирование для каждой категории расхода. В этом случае ячейка может быть зеленой, пока вы вписываетесь в бюджет, желтой, если на грани, и красной, когда вы вышли за лимит.

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

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

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

Если вам нужно пересчитать уникальные числа (с учетом первого вхождения) в диапазоне, с учетом некоторых ограничений, вы можете использовать формулу, основанную на СУММ и ЧАСТОТА, и одновременно применять критерии.