Вычислить процентиль в Excel 2010, используя пользовательскую функцию vba

У меня нет опыта работы с vba, и мне нужно преобразовать эту процентильную функцию в пользовательскую функцию vba.

=PERCENTILE(
IF('RAW JO TIME'!$J$2:$J$51816=$A7, 
  IF($B$4="(All)",'RAW JO TIME'!$P$2:$P$51816, 
    IF('RAW JO TIME'!$M$2:$M$51816=$B$4,'RAW JO TIME'!$P$2:$P$51816)
  )
),
E$6)

Где $A7, $B$4 и E$6 должны быть переданы в качестве параметров...

Я пробовал разные подходы, но ни один из них не работает. Поискав на форумах, я нашел возможное решение:

ActiveCell.Formula = "=IF('RAW JO TIME'!$J$2:$J$51816=" & InputRange & ", IF($B$4=""(All)"",'RAW JO TIME'!$P$2:$P$51816, IF('RAW JO TIME'!$M$2:$M$51816=$B$4,'RAW JO TIME'!$P$2:$P$51816)))"

Где "InputRange" - это ячейка, переданная в качестве параметров, значение которой ищется в диапазоне другого листа...

Также пробовал это

Dim r As Range

Set r.FormulaArray = "=IF('RAW JO TIME'!$J$2:$J$51816=$A7, IF($B$4=""(All)"",'RAW JO TIME'!$P$2:$P$51816, IF('RAW JO TIME'!$M$2:$M$51816=$B$4,'RAW JO TIME'!$P$2:$P$51816)))"

Как видите, я не использовал параметры для этого, просто попытался протестировать формулу массива как есть... но я получил тот же результат. Выполнение останавливается в Set r.FormulaArray

Насколько я понимаю, этот оператор IF вернет диапазон ячеек или матрицу данных, которые впоследствии будут использоваться функцией Percentile...

Но потом, когда я отлаживаю код, он застревает в ActiveCell.Formula... почему это может быть?

Мне интересно, будет ли лучшим решением в этом случае реализовать каждое из «если» в функции как оператор VB if?

Помощь будет оценена

Спасибо!


excel vba percentile
person jprealini    schedule 10.07.2014    source источник
comment
Это верно, это формула массива.   -  person ApplePie    schedule 11.07.2014
comment
@jprealini Вам нужно хотя бы знать, как начать работу с VBA. Одним из простых способов было бы восстановить формулу и использовать Application.Evaluate().   -  person ApplePie    schedule 11.07.2014
comment
@Dave.GuggDave.Gugg, как я объяснил, мне нужно создать пользовательскую функцию PERCENTILE, используя в качестве матрицы для нее диапазон данных, которые будут результатом оператора IF и сравнений, которые вы видите там ...   -  person jprealini    schedule 11.07.2014

Ответы (1)


arrow_upward
1
arrow_downward

Это то, что я сделал, чтобы решить эту...

Я начал экспериментировать с методом Evaluate, запустив только блок «IF», ​​и обнаружил, что он возвращает массив, содержащий «false» для записей, не соответствующих условию, и значение, необходимое для тех, которые соответствовали условию... так что Сначала я передал все диапазоны в качестве параметров и использовал их для построения IF (формулы массива), затем передал его в метод Evaluate и использовал полученный массив для построения окончательного массива значений, которые будут переданы в метод процентилей WorksheetFunction. (важное примечание: результат и диапазоны должны быть объявлены как массивы... иначе это не сработает, или, по крайней мере, не сработало для меня):

Dim result() As Variant
Dim ranges() As Variant
Dim i, j, k As Integer
Dim func As Variant

func = "=IF('" & officeRangeSheet & "'!" & officeRangeAddress & "=" & officeValue.Address & ",IF('" & statusRangeSheet & "'!" & statusRangeAddress & "=" & statusValue.Address & ",IF(" & yearFunctionString & ",'" & valuesRangeSheet & "'!" & valuesRangeAddress & ")))"

result = Application.ActiveSheet.Evaluate(func)

    For i = 1 To UBound(result)
        If result(i, 1) <> False Then
            ReDim Preserve ranges(1 To k + 1)
            ranges(k + 1) = result(i, 1)
            k = k + 1
        End If
    Next i

CustomPercentileWFC = Application.WorksheetFunction.Percentile(ranges, Percentile)

Сложность здесь заключалась в том, что в некоторых случаях функция PERCENTILE становилась действительно огромной, поэтому метод EVALUATE не работал из-за ограничения в 255 символов, поэтому мне пришлось разбить его на две части...

person jprealini    schedule 23.07.2014