У меня есть файл .xls со столбцом с некоторыми данными. Как подсчитать, сколько уникальных значений содержит этот столбец?
У меня есть множество вариантов, но формулы, которые они дают, всегда дают мне ошибки. Например,
=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0))
возвращает
Чтобы подсчитать количество различных значений в A2: A100 (не считая пробелов):
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
Скопировано из ответа @Ulli Schmid на Что это за формула COUNTIF()?:
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
Подсчитывает уникальные ячейки в пределах A1: A100, исключая пустые ячейки и пустые строки ("").
Как это делается? Пример:
A1:A100 = [1, 1, 2, "apple", "peach", "apple", "", "", -, -, -, ...]
then:
A1:A100&"" = ["1", "1", "2", "apple", "peach", "apple", "", "", "", "", "", ...]
поэтому этот & " необходим, чтобы превратить пустые ячейки (-) в пустые строки (" "). Если вы должны были подсчитывать напрямую, используя пустые ячейки, COUNTIF() возвращает 0. Используя трюк, оба значения" " и - считаются одинаковыми:
COUNTIF(A1:A100,A1:A100) = [2, 2, 1, 2, 1, 2, 94, 94, 0, 0, 0, ...]
but:
COUNTIF(A1:A100,A1:A100&"") = [2, 2, 1, 2, 1, 2, 94, 94, 94, 94, 94, ...]
Если теперь мы хотим получить количество всех уникальных ячеек, исключая пробелы и "", мы можем разделить
(A1:A100<>""), which is [1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, ...]
нашим промежуточным результатом, COUNTIF (A1: A100, A1: A100 & "") и суммируем по значениям.
SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
= (1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0/94 + 0/94 + 0/94 + 0/94 + ...)
= 4
Если бы мы использовали COUNTIF(A1:A100,A1:A100) вместо COUNTIF(A1:A100,A1:A100&""), то некоторые из этих 0/94 были бы 0/0. Поскольку деление на ноль не разрешено, мы бы допустили ошибку.
try - =SUM(IF(FREQUENCY(MATCH(COLUMNRANGE,COLUMNRANGE,0),MATCH(COLUMNRANGE,COLUMNRANGE,0))>0,1))
где COLUMNRANGE= диапазон, в котором у вас есть эти значения.
например. - =SUM(IF(FREQUENCY(MATCH(C12:C26,C12:C26,0),MATCH(C12:C26,C12:C26,0))>0,1))
Нажмите Ctrl + Shift + Enter, чтобы сделать формулу массивом (в противном случае не будет правильно вычисляться)
Вот еще один быстрый способ получить уникальный счетчик значений, а также получить уникальные значения. Скопируйте столбец, который вам нужен, на другой рабочий лист, затем выберите весь столбец. Нажмите "Данные" → "Удалить дубликаты" → "ОК". Это удаляет все повторяющиеся значения.
Здесь представлена элегантная формула массива (которую я нашел здесь http://www.excel-easy.com/examples/count-unique-values.html), которая делает трюк красиво:
Тип
= СУММ (1/СЧЕТЕСЛИ (Список, Список))
и подтвердите с помощью CTRL-SHIFT-ENTER
Считать уникальным с условием. Col A - это идентификатор и использование условия ID=32, Col B - это имя, и мы пытаемся подсчитать уникальные имена для определенного идентификатора
=SUMPRODUCT((B2:B12<>"")*(A2:A12=32)/COUNTIF(B2:B12,B2:B12))
Еще один сложный способ, который мне только что пригодился (проверено, и это сработало!).
Conditional Formatting, Highlight Cells, Duplicate ValuesData, а затем FilterФильтр по цвету:
По общему признанию, это больше для одноразовых проверок данных, чем таблица, которую вы будете использовать часто, поскольку она требует некоторых изменений форматирования.
Вы можете выполнить следующие действия:
Сначала выделите столбец (вставив пустой столбец до и/или после столбца, который вы хотите подсчитать уникальные значения, если есть соседние столбцы;
Затем выберите весь столбец, перейдите в "Данные" > "Расширенный фильтр" и установите флажок "Только уникальные записи". Это скроет все не уникальные записи, чтобы вы могли подсчитать уникальные, выбрав весь столбец.
Если вы используете Mac
pbpaste|sort -u|wc -lПользователи Linux заменяют pbpaste на xclip xsel или аналогичные
Пользователи Windows, возможно, но возьмут некоторые скрипты... начните с http://brianreiter.org/2010/09/03/copy-and-paste-with-clipboard-from-powershell/
Вы можете добавить новую формулу для уникального количества записей
=IF(COUNTIF($A$2:A2,A2)>1,0,1)
Теперь вы можете использовать сводную таблицу и получить SUM уникального количества записей.
Это решение работает лучше всего, если у вас есть две или более строк, где существует одно и то же значение, но вы хотите, чтобы сводная таблица сообщала уникальный счет.
Я использую таблицу с заголовками в строке 1, данные находятся в строках 2 и ниже.
Идентификаторы находятся в столбце A. Чтобы подсчитать количество различных значений, я поместил эту формулу из строки 2 в конец электронной таблицы первого доступного столбца [F в моем случае]: "=IF(A2=A1,F1+1,1)".
Затем я использую следующую формулу в свободной ячейке: "=COUNTIF(F:F,1)". Таким образом, я уверен, что каждый идентификатор подсчитан.
Обратите внимание, что идентификаторы должны быть отсортированы, иначе они будут считаться более одного раза... но в отличие от формул массива это очень быстро даже при таблице 150000.
С помощью формул динамического массива (на данный момент эта публикация доступна только для инсайдеров Office 365):
=COUNTA(UNIQUE(A:A))
Мой набор данных - D3: D786, Заголовки столбцов в D2, функция в D1. Формула будет игнорировать пустые значения.
= СУММ (ЕСЛИ (частоты (ПЧ (субтотальная (3, OFFSET (D3, ROW (D3: D786) -строка (D3), 1)), ЕСЛИ (D3: D786 < > ", MATCH (" ~ "& D3: D786, D3: D786 &" ", 0))), СТРОКА (D3: D786) -строка (D3) + 1), 1))
При вводе формулы CTRL + SHIFT + ВВОД
Я нашел это на сайте ниже, там больше объяснений о Excel, который я не понял, если вы в этом верите.
Я скопировал и вставил свой набор данных в другой лист, чтобы проверить его, и это сработало для меня.