Array CountIf Substitute - Count (Match ())

Основной вопрос

Как я могу выполнять повторяющиеся CountIf()s в диапазоне с максимальной эффективностью (с точки зрения производительности)?


Проблемы в деталях

Диапазон до массива

Поскольку каждое чтение / запись в электронную таблицу приводит к замедлению кода VBA, рекомендуется делать это как можно реже. Обычно, если кто-то многократно читает / записывает в диапазон, он или она должны сначала сохранить этот диапазон в массив, выполнить операции с массивом, а затем, при необходимости, выполнить окончательное чтение или запись в электронную таблицу.


Примеры значений и кода

Примеры значений

Как я могу использовать Perform CountIf()s в диапазоне A2:A11 выше, чтобы вычислить количество каждого значения и записать их в D2:D7? Я ожидаю, что приведенный ниже код будет работать:

Sub M1ArrayCount()

Dim arrNumbers() As Variant
Dim Long1 As Long
Dim Loop1 As Long

arrNumbers() = ThisWorkbook.Sheets(1).Range("A2:A11").Value

With ThisWorkbook.Sheets(1)
    For Loop1 = 1 To 6
        .Cells(Loop1 + 1, 4).Value = Application.CountIf(arrNumbers(), Loop1)
    Next Loop1
End With

End Sub

CountIf () не работает с массивами

Однако, поскольку Application.CountIf() работает только с диапазонами, а не с массивами, D2:D7 все показывают #VALUE! ошибки после выполнения вышеуказанного кода. Необходимо найти замену.


arrays excel countif vba
person puzzlepiece87    schedule 11.05.2016    source источник
comment
Ответ на это будет зависеть от размера вашего набора данных. Сколько строк в Range To Test и сколько строк в Values?   -  person chris neilsen    schedule 12.05.2016
comment
Это правда - я намерен ответить на этот вопрос для больших наборов данных и специально не говорил об этом. Мой собственный набор данных составлял 90 000 строк.   -  person puzzlepiece87    schedule 12.05.2016

Ответы (1)


arrow_upward
8
arrow_downward

Решение - Счетчик (Match ())

Решение, которое мы ищем:

Application.Count(Application.Match(SavedArray(), Array([lookup_value]), 0))


What? How does that work? --- How can a function that normally returns a row number be paired with an array and count to return the correct answer? How can you count row numbers?

Объяснение механики

Большое спасибо @Jeeped, вот как это работает:

Это недокументированная функция Match(lookup value, lookup array, 0), заключающаяся в том, что если вы поместите массив в качестве значения поиска, он будет Match() каждое значение в массиве, которое вы ввели, против массива поиска. Таким образом, для приведенного выше примера для Loop = 1 он будет выглядеть следующим образом:

{match(A2, Array("1"), 0),match(A3, Array("1"), 0), ... match(A11, Array("1"), 0)}

Затем, согласно @Jeeped:

Каждое совпадение вернет либо число, либо ошибку. Функция Count() считает числа, а не ошибки. Таким образом, вы получаете подсчет того, соответствует ли какое-либо из значений в A1:A11 Loop1.

Окончательный код и значения

Sub M1ArrayCount()

Dim arrNumbers() As Variant
Dim Long1 As Long
Dim Loop1 As Long

arrNumbers() = ThisWorkbook.Sheets(1).Range("A2:A11").Value

With ThisWorkbook.Sheets(1)
    For Loop1 = 1 To 6
        .Cells(Loop1 + 1, 4).Value = Application.Count(Application.Match(arrNumbers(), Array(Loop1), 0))
    Next Loop1
End With

End Sub

Окончательные результаты


References --- This comment

Этот ответ

Вопрос

Пример:

myarray = array("First","Second","Second","Third","Fourth")

тогда каков будет синтаксис countif(myarray,"second")? (результат должен быть равен 2 отсчетам)

Ответ

Попробуйте также:

MsgBox Application.Count(Application.Match(myArray, Array("Second"), 0))

Этот чат

person puzzlepiece87    schedule 11.05.2016
comment
chat.stackoverflow.com/transcript/message/30485994#30485994 Также от @Jeeped: Я наконец нашел пришло время более тщательно протестировать этот метод, и я обнаружил ограничение. Использование параметров в обратном направлении ограничивает массив как lookup_value 255 элементами. Это делает недопустимым использование в качестве подстановки на основе массива для подсчета больших блоков данных. - person puzzlepiece87; 12.05.2016
comment
Отлично; случайно наткнулся на ваш интересный подход +1) - person T.M.; 08.01.2019