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屋！