为什么特定的数组公式在 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}))),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))))))),今天()))
=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()))
但同样的解决方案拒绝在带有错误的谷歌表格中工作
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.
知道为什么这在谷歌表格中不起作用以及如何使它起作用吗?
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 Sheets 中无效，后者都解析为传递的数组中的单个(即第一个)元素.
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 表格的了解不够，不知道为什么会这样.可以使用易失性 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-an-array-of-values/
问候
这篇关于为什么特定的数组公式在 google 表格中不起作用，但在 excel 中工作正常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
