Подсчет различных значений в excel — частотная функция

Мне было поручено подсчитать количество различных строк в столбце Excel. Быстрый поиск в Google позже дал следующую формулу: %20%20functions" rel="nofollow noreferrer">здесь:

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10,B2:B10,0),ПОИСКПОЗ(B2:B10,B2:B10,0))>0,1))

Рассмотрим данные:

A B C D A B E C

Теперь функция match вернет массив (поскольку первый аргумент — это массив):

1 2 3 4 1 2 7 3

Все идет нормально. Чего я не понимаю, так это того, как здесь работает функция ЧАСТОТА, в частности, как она обрабатывает реплицированные бины (например, бин 1 реплицируется в приведенных выше данных). Результат частотной функции:

2 2 2 1 0 0 1 0 0

Спасибо

Тарас


excel excel-formula vba
person Trent    schedule 15.09.2009    source источник

Ответы (2)


arrow_upward
2
arrow_downward

РЕДАКТИРОВАТЬ: я понял, как работает ваше решение - изменено, чтобы отразить это.

FREQUENCY ищет записи из ваших корзин в массиве поиска. Вот как это работает:

Массив поиска: 1 2 3 4 1 2 7 3

Бункеры: 1 2 3 4 1 2 7 3

Bin 1 => есть две единицы => 2

Bin 2 => есть две двойки => 2

Ячейка 3 => есть две тройки => 2

Ячейка 4 => есть одна 4 => 1

Ячейка 1 повторяется => 1 уже подсчитано => 0

Ячейка 2 повторяется => 2 уже подсчитано => 0

Ячейка 7 => есть одна 7 => 1

Ячейка 3 повторяется => 3 уже подсчитано => 0

Похоже, что решение использует особенность FREQUENCY, то есть оно не будет считать один и тот же бин дважды, потому что вы можете ожидать, что второй бин со значением 1 также будет ненулевым. Но это то, как это работает — поскольку будет подсчитываться только количество вхождений для первого бина, а не для дублирующегося бина, количество строк со значением больше нуля даст вам количество различных записей.

Вот альтернативный подход, который может оказаться полезным. его можно использовать для вычисления количества различных значений:

Предположим, ваш диапазон строк — B2:B10. Заполните в другой столбец

=(MATCH(B2,B$2:B2,1)-(ROW(B2)-ROW(B$2)))>0

Строка должна меняться по мере копирования, поэтому вторая строка должна быть, например:

=(MATCH(B3,B$2:B3,1)-(ROW(B3)-ROW(B$2)))>0

Это сигнализирует TRUE, если текущая строка содержит первый экземпляр строки (если вы дадите ей пару минут, вы сможете понять, что она делает). Поэтому, если вы подсчитаете количество ИСТИН с помощью COUNTIF(), вы должны получить количество различных строк.

person Joel Goodwin    schedule 15.09.2009
comment
Я думал, что это то, что делает функция частоты, эффективно игнорируя дублированные ячейки. Я надеялся, что кто-то может с уверенностью сказать, что это задокументированное поведение функции, но похоже, что это хорошо известная причуда. Спасибо за ответ! - person Trent; 17.09.2009

arrow_upward
0
arrow_downward

Вы можете использовать процедуру vba:

Sub Uniques()

    Dim rng As Range
    Dim c As Range
    Dim clnUnique As New Collection

    Set rng = Range("A1:A8")

    On Error Resume Next
    For Each c In rng
        clnUnique.Add c.Value, CStr(c.Value)
    Next c
    On Error GoTo 0

    MsgBox "Number of unique values = " & clnUnique.Count

End Sub

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

person dendarii    schedule 15.09.2009
comment
Эй, дендарийцы, на самом деле я спрашивал, как работает частотная функция... хотя ваша процедура ценится! - person Trent; 17.09.2009
comment
Вы совершенно правы! Похоже, Джоэл дал ответ на ваш актуальный вопрос :) - person dendarii; 17.09.2009