根据我的阅读，INDIRECT() 似乎不能与 ARRAYFORMULA() 一起使用，但是已经看到了一些针对特定情况的自定义解决方案。任何人都可以帮助我修改以下方程，以便我可以使用 ARRAYFORMULA() 吗？
等式1：
=IF($I$11<>"", SUMIFS(INDIRECT("'Earnings Pivot Statement'!"&$M$5&"6:"&$M$5&""),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2,12,31)), SUMIFS(INDIRECT("'Earnings Pivot Statement'!"&$P$5&"6:"&$P$5&""),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2,12,31)))
等式 2：=IFERROR(IFNA(SUMIFS(INDIRECT("'Earnings Pivot Statement'!"&$M$5&"6:"&$M$5&""),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2,12,31))/O2,"-"),"-")
等式 3：=SUMIFS(INDIRECT("'Earnings Pivot Statement'!"&$P$5&"6:"&$P$5&""),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2,12,31))
感谢我在了解如何修改这些方面可以获得的任何帮助！
编辑：采用 Erik 的不需要 INDIRECT() 的建议，我设法使用 OFFSET() 引用了同一组数据，其中包含以下内容：
=ARRAYFORMULA(IF($I$11:I11<>"", SUMIFS(OFFSET('Earnings Pivot Statement'!$A$6, 0, $M$2-1, COUNTA('Earnings Pivot Statement'!$A$6:A)+COUNTBLANK('Earnings Pivot Statement'!$A$6:A),1),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2:A,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2:A,12,31)), SUMIFS(OFFSET('Earnings Pivot Statement'!$A$6, 0, $P$2-1, COUNTA('Earnings Pivot Statement'!$A$6:A)+COUNTBLANK('Earnings Pivot Statement'!$A$6:A),1),'Earnings Pivot Statement'!$A$6:A,">="&DATE(A2:A,1,1),'Earnings Pivot Statement'!$A$6:A,"<="&DATE(A2:A,12,31))))
但是， ARRAYFORMULA() 目前只填充 1 个单元格，不知道为什么
回答1
您正在从枢轴 table 查找数据，这似乎是一个不必要的步骤。尝试这样的方法直接从 'Earnings Input' 获取总数：
=query(
'Earnings Input'!A1:G,
"select year(A), sum(G)
where B is not null
group by year(A)
label year(A) 'Year', sum(G) 'Total' ",
1
)
=arrayformula(
iferror(
vlookup(
G18:G,
query(
'Earnings Input'!A1:G,
"select year(A), sum(G)
where B " & if(len(I11), "= '" & I11 & "' ", "is not null") & "
group by year(A)",
1
),
2, false
)
)
)这些公式不需要任何帮助列。单元格 I11 是一个下拉列表，可让您选择要过滤的名称，或留空以显示全部。
可以像这样获得额外的数据点：
=arrayformula( if( not(len(G5)) * len(F9:F), "-", iferror( F9:F / E9:E ) ) )
=arrayformula(
if(
not(len(G5)) * len(F9:F),
"-",
iferror( 100% - G9:G / isnumber(G9:G) )
)
)
=if(
len(G5),
G5 & " % (" & to_text(round(average(G9:G), 2)) & ")",
"No filter"
)上面的解决方案仍然有点冗长，主要是因为您选择的报告格式。一次显示所有类别的数据通常更容易。要了解它的外观，请尝试以下操作：
=query(
'Earnings Input'!A1:G,
"select year(A), sum(G)
where B is not null
group by year(A)
pivot B
label year(A) 'Year', sum(G) 'Total' ",
1
)
=arrayformula(
if(
isnumber(B3:J15) * isnumber(B3:B15) * isnumber(B3:J3),
B3:J15 / B3:B15,
B3:J15
)
)