Как передать список значений в пользовательскую функцию (UDF) в Excel
---------------------------------------
A B C D
---------------------------------------
Timestamp Stock Qty Price
---------------------------------------
01 February 2011 ST1 100 10
02 March 2013 ST1 900 11
01 February 2011 ST2 200 121
04 May 2014 ST1 250 15
02 March 2016 ST2 150 130
04 May 2018 ST2 250 140
08 September 2016 ST1 300 19
01 February 2012 ST3 400 190
11 November 2017 ST1 -400 20
Я ищу, чтобы создать функцию в Excel с именем MyXIRR (Даты в качестве диапазона, Значения в качестве диапазона, Баланс в виде двойной, Дата BalanceAsOn)
Для этой функции мне нужно передать данные из столбца Timestamp как Dates, Qty X Price в качестве значений для каждой акции. Balance и BalanceAsOn - это то, что я определю отдельно.
Например для ST1
Даты будут ниже
01 February 2011
02 March 2013
04 May 2014
08 September 2016
11 November 2017
значения будут ниже
100
900
250
300
-400
Проблема, с которой я сталкиваюсь, заключается в том, как передать список значений на основе фильтра по биржевому коду.
Я не эксперт по INDEX или MATCH и т.д., но когда я попробовал что-то вроде
=MyXIRR(INDEX(A1:A9,MATCH("ST1",B1:B9,0)), INDEX(C1:C9*D1:D9,MATCH("ST1",B1:B9,0)), 100.00, TODAY())
ИЛИ используйте это как массив, нажав Ctrl + Shift + Enter, т.е.
{=MyXIRR(INDEX(A1:A9,MATCH("ST1",B1:B9,0)), INDEX(C1:C9*D1:D9,MATCH("ST1",B1:B9,0)), 100.00, TODAY())}
Затем при отладке MyXIRR я получаю только одно значение в датах и одно значение в значениях, т. Е. 01 февраля 2011 и 100. Я ожидаю, что MyXIRR получит список, а не только первое значение.
Подозреваю, что неправильно использую INDEX, я пробовал IF и SUMPRODUCT, но получаю #VALUE! Ошибка для обоих ниже двух случаев
=MyXIRR(IF(B1:B9="ST1",A1:A9,0), IF(B1:B9="ST1",C1:C9*D1:D9,0), 100.00, TODAY())
ИЛИ ЖЕ
= MyXIRR(SUMPRODUCT((B1:B9="ST1"),A1:A9), SUMPRODUCT((B1:B9="ST1"),C1:C9*D1:D9), 100.00, TODAY() )
Может ли кто-нибудь подсказать, что я делаю неправильно и / или предложить дальнейшие действия?
Я использую Excel для Mac версии 16.16.2
Ниже показано, как выглядит моя функция
Public Function MyXIRR(Dates As Range, Trans As Range, Balance As Double, BalanceAsOn As Date) As Double
Dim Cell As Range
For Each Cell In Dates
MsgBox Cell.Value
Next Cell
MyXIRR = Dates.Count
End Function
Это просто зацикливается только один раз и отображает 01.02.2011, когда я вызываю его для ST1
2 ответа
[Обратите внимание, что следующее решение не было протестировано в версии Excel для Mac.]
Чтобы передать только те значения, которые соответствуют "ST1", сначала вам нужно объявить Dates и Trans как вариант, как уже упоминал Скотт, а затем соответствующим образом изменить тело вашей функции...
Public Function MyXIRR(Dates As Variant, Trans As Variant, Balance As Double, BalanceAsOn As Date) As Double
Dim currentDate As Variant
For Each currentDate In Dates
MsgBox currentDate
Next currentDate
MyXIRR = UBound(Dates)
End Function
Затем вы можете использовать следующую формулу, которая должна быть подтверждена клавишами CONTROL+SHIFT+ENTER, а не просто ENTER...
=MyXIRR(INDEX(A2:A10,N(IF(1,SMALL(IF(B2:B10="ST1",ROW(B2:B10)-ROW(B2)+1),ROW(INDIRECT("1:"&COUNTIF(B2:B10,"ST1"))))))),INDEX(C2:C10*D2:D10,N(IF(1,SMALL(IF(B2:B10="ST1",ROW(B2:B10)-ROW(B2)+1),ROW(INDIRECT("1:"&COUNTIF(B2:B10,"ST1"))))))),100,TODAY())
Если все сделано правильно, Excel автоматически поместит фигурные скобки {...} вокруг формулы.
Попробуй это:
Public Function MyXIRR(Dates As Range, Trans As Range, Balance As Double, BalanceAsOn As Date) As Double
Dim Area as Range
Dim Cell As Range
For Each Area in Dates.Areas
For Each Cell In Area
MsgBox Cell.Value
Next Cell
Next Area
MyXIRR = Dates.Count
End Function