为什么特定的arrayformula在Google表格中不起作用，但是在Excel中能正常工作 [英] Why does a specific arrayformula not work in google sheets but works fine in excel
问题描述
这是如何在Excel和Google中动态计算XIRR的延续纸
拟议的数组公式**解决方案(如下所述)在excel中效果很好
The proposed array formula** solution (mentioned below) works perfectly fine in excel
= XIRR(INDEX(F:G，N(IF(1，SMALL(IF(B $ 2:B $ 8 = J2，ROW(B $ 2:B $ 8)))，ROW(INDEX(A:A，1) :INDEX(A:A，COUNTIF(B $ 2:B $ 8，J2))))))，N(IF(1，{1,2})))，选择({1,2}，INDEX(A: A，N(IF(1，小(IF(B $ 2:B $ 8 = J2，ROW(B $ 2:B $ 8)))，ROW(INDEX(A:A，1):INDEX(A:A，COUNTIF(B $ 2:B $ 8，J2)))))))))，TODAY()))
=XIRR(INDEX(F:G,N(IF(1,SMALL(IF(B$2:B$8=J2,ROW(B$2:B$8)),ROW(INDEX(A:A,1):INDEX(A:A,COUNTIF(B$2:B$8,J2)))))),N(IF(1,{1,2}))),CHOOSE({1,2},INDEX(A:A,N(IF(1,SMALL(IF(B$2:B$8=J2,ROW(B$2:B$8)),ROW(INDEX(A:A,1):INDEX(A:A,COUNTIF(B$2:B$8,J2))))))),TODAY()))
，但是相同的解决方案拒绝在Google表格中使用该错误
but the same solution refuses to work in google sheets with the error
在XIRR评估中，值数组必须包含正数和负数.
In XIRR evaluation, the value array must include positive and negative numbers.
有人知道为什么这在Google表格中不起作用以及如何使其起作用吗?
Any idea why this is not working in google sheets and how to make it work?
源数据
PurchaseDate Script No.ofunits PurchNAVrate NetAmount ForXIRR TotalReturn
17/11/2014 A 2241 33 75000 -75000 96000
8/1/2015 B 53 649 35000 -35000 43000
14/1/2015 B 75 658 50000 -50000 61500
14/10/2014 C 2319 32 75000 -75000 108000
8/1/2015 D 318 109 35000 -35000 40000
14/1/2015 D 450 110 50000 -50000 57000
8/6/2015 D 175 114 20000 -20000 22000
Values for Fund A should be around 14%
Values for Fund B should be around 13%
Values for Fund C should be around 21%
Values for Fund D should be around 8%
推荐答案
看来，允许我们从INDEX生成收益数组的结构，即:
It appears that the the constructions which allow us to generate an array of returns from INDEX, i.e.:
N(IF(1,,,
或
N(INDEX(,,,
在Excel中有效，但在Google表格中无效，在后者中，两者都只能解析为所传递数组中的单个(即第一个)元素.
are valid in Excel but not in Google Sheets, in the latter both resolving to just a single (i.e. the first) element in the array passed.
例如，在Excel中，以下内容:
For example, in Excel, the following:
=SUM(INDEX(A1:A10,N(IF(1,{1,2,7}))))
或:
=SUM(INDEX(A1:A10,N(INDEX({1,2,7},))))
将对A1，A2和A7中的值求和，尽管在Google表格中，两者都将仅对A1中的值求和.
will sum the values in A1, A2 and A7, though in Google Sheets both will sum only the value in A1.
我对Google表格的了解不足，无法知道为什么会这样.可以使用volatile OFFSET重构我的公式.我会看看，然后回覆您.
I do not know enough about Google Sheets to know why this is the case. It may be possible to reconstruct my formula using the volatile OFFSET. I will have a look and get back to you.
更新:看来，即使是基于OFFSET的解决方案，即:
Update: It appears that even an OFFSET-based solution, i.e.:
=XIRR(N(OFFSET(F2,SMALL(IF(B$2:B$8=J2,ROW(B$2:B$8)-MIN(ROW(B$2:B$8))),ROW(INDIRECT("1:"&COUNTIF(B$2:B$8,J2)))),{0,1})),CHOOSE({1,2},N(OFFSET(A2,SMALL(IF(B$2:B$8=J2,ROW(B$2:B$8)-MIN(ROW(B$2:B$8))),ROW(INDIRECT("1:"&COUNTIF(B$2:B$8,J2)))),{0,1})),TODAY()))
是不可能的.再次，它在Excel中可以正常运行，但在Google表格中却不能正常运行，原因与上述类似(传递给OFFSET的数组无法按要求解析).
is not possible. Again, it works fine in Excel but not in Google Sheets, for reasons similar to those given above (the arrays passed to OFFSET do not resolve as required).
我认为这需要精通Google表格的人进行解释.
I think this requires an explanation by someone well-versed in Google Sheets.
参考:
https://excelxor.com/2014/09/05/index-returning-of-values/
致谢
这篇关于为什么特定的arrayformula在Google表格中不起作用，但是在Excel中能正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
