本文介绍了为什么特定的数组公式在 google 表格中不起作用,但在 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))))))),今天()))

但同样的解决方案拒绝在带有错误的谷歌表格中工作

but the same solution refuses to work in google sheets with the error

在 XIRR 评估中,值数组必须包含正数和负数.

知道为什么这在谷歌表格中不起作用以及如何使它起作用吗?

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},))))

将对 A1A2A7 中的值求和,但在 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 中工作正常的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-25 15:26