В этом руководстве вы узнаете, как подсчитывать уникальные значения в Excel с помощью формул и как это делать в сводной таблице. Мы также рассмотрим несколько примеров подсчета уникальных текстовых и числовых значений, в том числе с учетом регистра.
При работе с большим набором данных в Excel вам часто может потребоваться знать, сколько повторяющихся записей находится в таблице и сколько уникальных записей.
Если вы регулярно посещаете этот блог, вы уже знаете формулу Excel для подсчета дубликатов. Сегодня мы собираемся изучить различные способы подсчета уникальных значений в Excel. Но для ясности давайте сначала определим термины.
- Уникальные значения — это те, которые появляются в списке только один раз.
- Различные — это все, что есть в списке без учета повторений, т.е уникальные плюс первое появление повторяющихся.
Следующий рисунок иллюстрирует эту разницу:
Теперь давайте посмотрим, как их вычислить с помощью формул и функций сводной таблицы.
Ниже вы найдете несколько примеров для подсчета уникальных данных разных типов.
Предположим, у вас есть столбец имен на листе Excel, и вам нужно подсчитать, сколько их не дубликатов. Самое простое решение — использовать функцию СУММ в сочетании с ЕСЛИ и СЧЁТЕСЛИ :
= СУММ (ЕСЛИ (СЧЁТЕСЛИ (диапазон; диапазон) = 1,1,0))
Примечание. Это формула массива, поэтому обязательно нажмите Ctrl + Shift + Enter, чтобы ввести ее правильно. Как только это будет сделано, Excel автоматически заключит все выражение в {фигурные скобки}, как показано на снимке экрана ниже. Фигурные скобки ни в коем случае нельзя вводить вручную, не получится.
В этом примере мы подсчитываем уникальные имена в диапазоне A2: A10, поэтому наше выражение выглядит так:
{= СУММ (ЕСЛИ (СЧЁТЕСЛИ (A2: A10; A2: A10) = 1, 1, 0))}
Этот метод подходит как для текстовых, так и для цифровых данных. Обратной стороной является то, что, будучи уникальным, он будет пересчитывать любой контент, включая ошибки.
Позже в этом руководстве мы обсудим несколько других подходов для подсчета уникальных значений разных типов. И поскольку в основном это вариации этой базовой формулы, имеет смысл присмотреться к ней поближе. Если вы понимаете, как это работает, вы можете настроить его для своих данных. Если кого-то не интересуют технические детали, можете сразу перейти к следующему примеру.
Как работает формула подсчета уникальных значений?
Как видите, здесь используются 3 разные функции: СУММ, ЕСЛИ и СЧЁТЕСЛИ. Посмотрим, что делает каждый из них:
- Функция СЧЁТЕСЛИ подсчитывает, сколько раз каждое отдельное значение появляется в анализируемом диапазоне.
В этом примере СЧЁТЕСЛИ (A2: A10; A2: A10) возвращает матрицу {3: 2: 2: 1: 1: 2: 3: 2: 3}.
- Функция ЕСЛИ оценивает каждый элемент в этом массиве, сохраняет все единицы (то есть уникальные) и заменяет все остальные цифры нулями.
Затем функция ЕСЛИ (СЧЁТЕСЛИ (A2: A10; A2: A10) = 1; 1; 0) преобразуется в ЕСЛИ ({3: 2: 2: 1: 1: 2: 3: 2: 3}) = 1,1, 0).
А затем он превращается в массив чисел {0: 0: 0: 1: 1: 0: 0: 0: 0}. Здесь 1 означает уникальное значение, а 0 означает, что оно встречается более 1 раза.
- Наконец, функция СУММ складывает числа в этот последний массив и возвращает общее количество уникальных значений. Что нам нужно.
Совет. Чтобы увидеть, как определенная часть выражения дает результаты, выберите эту часть в строке формул и нажмите функциональную клавишу F9.
Если ваш список содержит как числа, так и текст, и вы хотите подсчитывать только уникальные текстовые строки, добавьте функцию ETEXT () к приведенной выше формуле массива:
{= СУММ (ЕСЛИ (ETEXT (A2: A10) * СЧЁТЕСЛИ (A2: A10; A2: A10) = 1; 1; 0))}
Функция ETEXT возвращает TRUE, если исследуемая ячейка является текстовой, и FALSE в противном случае. Поскольку звездочка (*) в формулах массива работает как оператор И, функция ЕСЛИ возвращает 1 только в том случае, если она считается как текстовой, так и уникальной, в противном случае мы получаем 0. И после того, как функция СУММ сложит все числа, вы получите количество уникальные текстовые значения в указанном диапазоне.
Не забудьте нажать Ctrl + Shift + Enter, чтобы правильно ввести формулу массива, и вы получите следующий результат:
Как вы можете видеть на скриншоте выше, мы получили общее количество уникальных текстовых значений, исключая пустые ячейки, числа, логические выражения и ошибки ИСТИНА и ЛОЖЬ.
Чтобы подсчитать уникальные числа в списке данных, используйте формулу массива, как мы только что сделали для подсчета текстовых данных. Разница в том, что вы используете ISNUMBER вместо ETEXT:
{= СУММ (ЕСЛИ (ЕЧИСЛО (A2: A10) * СЧЁТЕСЛИ (A2: A10; A2: A10) = 1; 1; 0))}
Вы можете увидеть пример и результат на скриншоте чуть выше.
Примечание. Поскольку Microsoft Excel хранит дату и время в виде чисел, они также участвуют в вычислениях.
Если разница между прописными и строчными буквами критична для вас, самый простой способ подсчета — создать вспомогательный столбец со следующей формулой массива для определения повторяющихся и уникальных элементов:
{= ЕСЛИ (СУММ ((- ТОЧНЫЙ ($ A $ 2: $ A $ 10, A2))) = 1; «Уникальный»; «Двойной»)}
А затем используйте простую функцию СЧЁТЕСЛИ для подсчета уникальных значений:
= СЧЁТЕСЛИ (B2: B10; «Уникальный»)
Теперь посмотрим, как можно подсчитать количество значений, которые появляются хотя бы один раз, то есть так называемых разных значений.
Используйте следующее общее выражение:
{= СУММ (1 / СЧЁТЕСЛИ (диапазон; диапазон))}
Помните, что это формула массива, поэтому вам следует нажать Ctrl + Shift + Enter вместо обычного Enter.
В качестве альтернативы вы можете использовать функцию СУММПРОИЗВ и написать формулу обычным способом:
= СУММПРОИЗВ (1 / СЧЁТЕСЛИ (интервал; интервал))
Например, чтобы подсчитать различные значения в диапазоне A2: A10, вы можете использовать выражение:
{= СУММ (1 / СЧЁТЕСЛИ (A2: A10; A2: A10))}
или
= СУММПРОИЗВ (1 / СЧЁТЕСЛИ (A2: A10; A2: A10))
Этот метод подходит не только для подсчета в столбце, но и для диапазона данных. Например, у нас есть два столбца для имен. Итак, давайте сделаем это:
{= СУММПРОИЗВ (1 / СЧЁТЕСЛИ (A2: B10; A2: B10))}
Этот способ подходит для текста, чисел, дат.
Единственное ограничение — диапазон должен быть непрерывным и не содержать пустых ячеек или ошибок.
Если у вас есть пустые ячейки в диапазоне данных, вы можете изменить:
{= СУММПРОИЗВ (1 / СЧЁТЕСЛИ (A2: A10; A2: A10&»»))}
Тогда пустая ячейка будет включена в расчет и будет засчитана.
Как это работает?
Как вы уже знаете, мы используем функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз каждый отдельный элемент встречается в указанном диапазоне. В приведенном выше примере результатом функции СЧЁТЕСЛИ является числовой массив: {3: 2: 2: 1: 3: 2: 1: 2: 3}.
Затем выполняется серия операций деления, в которых одна делится на каждую цифру этой матрицы. Это преобразует все неуникальные значения в дробные числа, соответствующие количеству повторений. Например, если число или текст появляется в списке 2 раза, в массиве создаются 2 элемента, равных 0,5 (1/2 = 0,5). А если он встречается 3 раза, то в массиве создаются 3 элемента из 0,333333.
В нашем примере результатом вычисления выражения 1 / COUNTIF (A2: A10; A2: A10) является массив {0,3333333333333333: 0,5: 0,5: 1: 0,333333333333333: 0,5: 1: 0,5: 0,333333333333333}.
Все еще не совсем ясно? Это потому, что мы еще не применили функцию СУММ / СУММПРОИЗВ. Когда одна из этих функций добавляет числа в массив, сумма всех дробных чисел для любого отдельного элемента всегда дает 1, независимо от того, сколько раз оно встречается. И поскольку все уникальные элементы отображаются в массиве как единицы (1/1 = 1), конечный результат — это сумма всех значений, которые встречаются.
Как и в случае с подсчетом уникальных значений в Excel, вы можете использовать универсальные параметры формулы для обработки чисел, текста или чувствительности к регистру.
Помните, что все следующие выражения являются формулами массива и требуют нажатия Ctrl + Shift + Enter.
Если столбец, который вы хотите подсчитать, может содержать пустые ячейки, вам следует добавить функцию ЕСЛИ к уже знакомой формуле массива. Он проверит ячейки на наличие пробелов (в этом случае базовая формула Excel, описанная выше, вернет ошибку # DIV / 0):
= СУММ (ЕСЛИ (диапазон «»; 1 / СЧЁТЕСЛИ (диапазон; диапазон); 0))
Вот как, например, можно подсчитать количество отдельных значений, игнорируя пустые ячейки:
Мы используем:
{= СУММ (ЕСЛИ (A2: A10 «»; 1 / СЧЁТЕСЛИ (A2: A10; A2: A10), 0))}
Как видите, наш список состоит из трех имен.
Чтобы подсчитать несколько числовых значений (числа, даты и время), используйте функцию ЕЧИСЛО:
= СУММ (ЕСЛИ (ЕЧИСЛО (диапазон); 1 / СЧЁТЕСЛИ (диапазон; диапазон); «»))
Посчитаем, сколько разных чисел находится в диапазоне A2: A10:
{= СУММ (ЕСЛИ (ЕЧИСЛО (A2: A10), 1 / СЧЁТЕСЛИ (A2: A10, A2: A10);»»))}
Вы можете увидеть результат ниже.
Это довольно простое и элегантное решение, но оно намного медленнее, чем выражения, использующие функцию ЧАСТОТА для подсчета уникальных значений. Если у вас большие наборы данных, мы рекомендуем перейти на частотную формулу.
А вот еще один способ считать числа:
= СУММ (- (ЧАСТОТА (диапазон; диапазон)> 0))
Применяется к следующему примеру:
= СУММ (- (ЧАСТОТА (A2: A10; A2: A10)> 0))
Как видите, записи, содержащие буквы, здесь игнорируются.
Посмотрим, как это работает пошагово.
Функция ЧАСТОТА возвращает массив цифр, которые соответствуют диапазонам, указанным доступными числами. В этом случае мы сравниваем один и тот же набор чисел для массива данных и для массива диапазонов.
В результате FREQUENCY () возвращает массив, который является счетчиком для каждого числового значения в массиве данных.
Это работает, потому что FREQUENCY () возвращает ноль для всех чисел, которые ранее появлялись в списке. Ноль также возвращается для текстовых данных. Таким образом, получившийся массив выглядит так:
{3: 0: 0: 2: 0: 0}
Как видите, обрабатываются только числа. Ячейки A7: A10 игнорируются, поскольку в них есть текст. А функция ЧАСТОТА () работает только с числами.
Теперь давайте проверим каждое из этих чисел на наличие условия «больше нуля».
У нас есть:
{ИСТИНА: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ}
Теперь установите TRUE и FALSE соответственно на 1 и 0. Мы делаем это с двойным отрицанием. Проще говоря, это двойной минус, который не меняет величину числа, но позволяет по возможности получать действительные числа:
{1: 0: 0: 1: 0: 0}
А теперь функция СУММ складывает все, и мы получаем результат: 2.
Примечание. Вы можете легко использовать СУММПРОИЗВ вместо функции СУММ.
Чтобы подсчитать отдельные текстовые записи в столбце, мы будем использовать тот же подход, что и для исключения пустых ячеек.
Как нетрудно догадаться, мы просто добавим функцию ETEXT и проверку состояния:
= СУММ (ЕСЛИ (ETEXT (диапазон); 1 / СЧЁТЕСЛИ (диапазон; диапазон); «»))
Рассчитываем количество отдельных символьных значений следующим образом:
{= СУММ (ЕСЛИ (ETEXT (A2: A10), 1 / СЧЁТЕСЛИ (A2: A10, A2: A10);»»))}
Не забывайте, что это формула массива.
Если в вашей таблице нет пустых ячеек и ошибок, вы можете применить формулу, которая использует несколько функций: ЧАСТОТА, ПОИСК, СТРОКА и СУММПРОИЗВ.
В целом это выглядит так:
= СУММПРОИЗВ (- (ЧАСТОТА (ПОИСК (диапазон; диапазон; 0); СТРОКА (диапазон) — СТРОКА (диапазон_первый_ячейка) +1)> 0))
Предположим, у вас есть список имен сотрудников с указанием часов, в течение которых они работали над проектом, и вы хотите знать, сколько людей было задействовано. Глядя на данные, можно увидеть, что названия повторяются. И вы хотите посчитать всех, кто хоть раз попадал в этот список.
Применяем формулу массива:
{= СУММПРОИЗВ (- (ЧАСТОТА (ПОИСК (A2: A10; A2: A10,0); СТРОКА (A2: A10) -ЛИНИЯ (A2) +1)> 0))}
это сложнее, чем использование функции ЧАСТОТА () для подсчета разных чисел. Это потому, что FREQUENCY () не работает с текстом. Следовательно, MATCH преобразует имена в номера элементов, которые FREQUENCY () может обрабатывать().
Если какая-либо из ячеек в диапазоне пуста, вам нужно использовать более сложную формулу массива, которая включает функцию ЕСЛИ:
{= SUM (IF (FREQUENCY (IF (data «»; SEARCH (data; data; 0))); STRING (data) -LINE (data_first_cell) +1); 1))}
Примечание. Поскольку логический элемент управления в операторе IF содержит массив, наше выражение немедленно становится формулой массива, которая требует ввода с помощью Ctrl + Shift + Enter. Поэтому SUMPRODUCT был заменен на SUM.
В нашем примере это выглядит так:
{= СУММ (ЕСЛИ (ЧАСТОТА (ЕСЛИ (A2: A10 «», ПОИСК (A2: A10; A2: A10,0)), СТРОКА (A2: A10) -ЛИНИЯ (A2) +1), 1))}
Теперь этот расчет может быть «нарушен» только наличием ячеек с ошибками в исследуемом диапазоне.
Предположим, мы хотим пересчитать, сколько товаров заказал конкретный клиент.
В решении этой проблемы вам может помочь этот вариант:
{= СУММПРОИЗВ ((($ A $ 2: $ A $ 18 = E2)) / COUNTIF ($ A $ 2: $ A $ 18; $ A $ 2: $ A $ 18 & «»; $ B $ 2: $ B $ 18; $ B $ 2: $ B $ 18&»»))}
Введите его в пустую ячейку, куда вы хотите вставить результат, например F2. Затем нажмите одновременно Shift + Ctrl + Enter, чтобы получить правильный результат.
Поясним: здесь A2: A18 — это список покупателей с учетом того, какая область расчетов ограничена, B2: B18 — это список товаров, в которых вы хотите посчитать уникальные значения, E2 содержит критерий, на основании которого расчет ограничен только конкретным клиентом.
Второй способ.
Для уникальных значений в диапазоне с критериями можно использовать формулу массива, основанную на функции ЧАСТОТА.
{= СУММ (- (FREQUENCY (IF (критерий; MATCH (диапазон; диапазон; 0)); STRING (диапазон) -STRING (диапазон_первый_ячейка) +1)> 0))}
Применительно к нашему примеру:
{= СУММ (- (ЧАСТОТА (ЕСЛИ (LA2: A10 = E2; ПОИСК (B2: B10; B2: B10,0)); СТРОКА (B2: B10) — СТРОКА (B2) +1)> 0))}
На основе ограничений IF () функция ПОИСКПОЗ определяет порядковый номер только для строк, которые соответствуют критериям.
Если какая-либо из ячеек в диапазоне критериев пуста, вам необходимо изменить расчет, добавив дополнительный SE для обработки пустых ячеек. В противном случае они будут переданы функции ПОИСКПОЗ, которая в ответ сгенерирует сообщение об ошибке.
Вот что произошло после корректировки:
{= СУММ (- (ЧАСТОТА (ЕСЛИ (B2: B10 «»; ЕСЛИ (A2: A10 = E2; ПОИСК (B2: B10; B2: B10,0))); СТРОКА (B2: B10) -СТРОКА (B2) +1)> 0))}
То есть мы выполняем все действия и вычисления, если мы встретили непустую ячейку в столбце B: IF (B2: B10 «»….
Если у вас есть два критерия, вы можете расширить логику формулы, добавив еще один вложенный SE.
Мы объясняем. Определяем, сколько единиц товара было в первой партии покупателя.
Отметим критерии в G2 и G3.
В целом это выглядит так:
{= СУММ (- (ЧАСТОТА (ЕСЛИ (критерий1, ЕСЛИ (критерий2, ПОИСКПОЗ (диапазон, диапазон, 0)))), СТРОКА (диапазон) — СТРОКА (диапазон_первый_элемент) +1)> 0))}
Подставляем сюда реальные данные и получаем результат:
{= СУММ (- (ЧАСТОТА (ЕСЛИ (LA2: A10 = G2; ЕСЛИ (C2: C10 = G3; ПОИСК (B2: B10; B2: B10,0))); СТРОКА (B2: B10) -СТРОКА (B2) +1)> 0))}
У первого лота 2 товарных наименования, хотя есть 3 локации.
Если вам нужно пересчитать уникальные числа (с учетом первого вхождения) в диапазоне, с учетом некоторых ограничений, вы можете использовать формулу, основанную на СУММ и ЧАСТОТА, и одновременно применять критерии.
{= СУММ (- (ЧАСТОТА (ЕСЛИ (критерий, диапазон), диапазон)> 0))}
Предположим, у нас есть список сотрудников и количество отработанных часов в день. Необходимо посчитать, сколько человек проработали хотя бы один раз менее 8 часов, то есть неполную смену.
Вот наша матричная формула:
{= СУММ (- (ЧАСТОТА (ЕСЛИ (B2: B10 0))}
Как видите, таких случаев 3, но они связаны с двумя сотрудниками.
Подобно подсчету уникальных значений, самый простой способ подсчета различных значений с учетом регистра — это добавить вспомогательный столбец формулы массива, который идентифицирует нужные элементы, включая повторяющиеся первые вхождения.
Подход в основном такой же, как тот, который мы использовали для подсчета уникальных значений с учетом регистра, с одним небольшим изменением:
{= ЕСЛИ (СУММ ((- ТОЧНЫЙ ($ A $ 2: $ A2, $ A2))) = 1; «Уникальный»;»»)}
Как вы помните, все формулы массива в Excel требуют нажатия Ctrl + Shift + Enter.
Заметив это выражение, вы можете подсчитать «различные» значения, используя обычную функцию СЧЁТЕСЛИ, например:
= СЧЁТЕСЛИ (B2: B10; «Уникальный»)
Если вы не можете добавить вспомогательный столбец на свой рабочий лист, вы можете использовать следующую более сложную формулу массива для подсчета различных значений с учетом регистра без создания дополнительного столбца:
{= СУММ (ЕСЛИОШИБКА (1 / IF ($ A $ 2: $ A $ 10 «»; FREQUENCY (IF (MATCH ($ A $ 2: $ A $ 10; TRANSPOSE ($ A $ 2: $ A $ 10)); ПОИСК (СТРОКА ($ A $ 2: $ A $ 10); СТРОКА ($ A $ 2: $ A $ 10)); «»); ПОИСК (СТРОКА ($ A $ 2: $ A $ 10)); LINE ($ A $ 2: $ A $ 10))); 0); 0))}
Как видите, обе формулы дают одинаковые результаты.
Подсчет уникальных / различных строк в Excel аналогичен пересчету уникальных и различных значений. Единственное отличие состоит в том, что вы используете функцию СЧЁТЕСЛИ вместо СЧЁТЕСЛИ, которая позволяет вам указывать сразу несколько столбцов для проверки их уникальности.
Например, чтобы подсчитать уникальные строки на основе столбцов A (Имя) и B (Фамилия), используйте один из следующих вариантов:
Для уникальных строк:
{= СУММ (ЕСЛИ (СЧЁТЕСЛИМН (A3: A11; A3: A11; B3: B11; B3: B11) = 1; 1; 0))}
Для разных строк:
{= СУММ (1 / СЧЁТЕСЛИ (A3: A11; A3: A11; B3: B11; B3: B11))}
Конечно, вы не ограничены двумя столбцами. Функция СЧЁТЕСЛИ может обрабатывать до 127 пар диапазон / критерий.
Вот общая задача, которую все пользователи Excel должны время от времени выполнять. У вас есть список данных (например, названия продуктов), и вам нужно узнать количество уникальных позиций в этом списке. Как это сделать? Проще, чем вы думаете
