Excel间接计数 [英] Excel Indirect with Count

问题描述

我每个月都有一本工作簿，上面有不同的工作表.我写了另一张纸，把A1的月份记下来. 我想同时使用Counta(针对月度表中的A:A)并间接使用，但是我的公式得到的结果为"1".

I have a workbook with different sheets for each month. I made another sheet where I put the months in A1 down. I wanted to use Counta (for A:A in the months' sheets) and indirect together but I am getting the result "1" with my formula.

请帮助:

= COUNTA(INDIRECT(SheetNames！A1&！A:A"))

=COUNTA(INDIRECT(SheetNames!A1&"!A:A"))

我最终还是想对countif做同样的事情.如果可以的话，请解释一下我想念的东西会对我有帮助.

I would also eventually like to do the same for countif. If you can please explain what I missed that will help me.

谢谢！

推荐答案

除非您在工作表名称中有空格，否则请尝试一下公式，该公式将非常有效. 尝试以下公式:
=COUNTA(INDIRECT("'" & SheetNames!A1 & "'!A:A"))

Trying out your formula it works perfectly unless you've got a space in the worksheet name. Try this formula:
=COUNTA(INDIRECT("'" & SheetNames!A1 & "'!A:A"))

当工作表名称包含空格时，必须将工作表名称括在撇号内.因此=COUNTA('Sheet 4'!A:A)是一个有效的公式，而=COUNTA(Sheet 4!A:A)将不起作用并引起各种问题.
INDIRECT公式需要一个有效的引用作为字符串(而不是实际引用)进行计算-因此'"& SheetNames！A1& '！A:A" 返回字符串'Sheet 4'！A:A .

When a sheet name contains a space then the sheet name must be enclosed within apostrophes. So =COUNTA('Sheet 4'!A:A) is a valid formula, while =COUNTA(Sheet 4!A:A) will not work and causes all kinds of problems.
The INDIRECT formula needs a valid reference as a string (rather than an actual reference) to calculate - so "'" & SheetNames!A1 & "'!A:A" returns the string 'Sheet 4'!A:A.

这篇关于Excel间接计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！