excel - 使用公式标准引用外部工作簿中的命名范围

Question

在公式中的命名范围方面需要帮助 :

我有第二本工作簿 ('TEST.xlsx')作为目标，在源工作簿 ('FLOW.xlsx') 中引用工作表范围的命名范围(12 列 X 75 行) .我想创建一个与查找值匹配的公式(在 C3 中输入单元格 TEST 的日期，如果匹配的命名范围/列中有 2 个或更多空白单元格，则返回匹配的命名范围，并且该组 12 列中剩余的命名范围/列具有 2+ 个空白单元格。源工作簿 ('FLOW') 中的 12 个单独列按月、年和位置 (ex., "jan_2019_class.1","feb_2019_class.1", etc.) 命名，工作表列是 C, H, M, R, W, AB, AG, AL, AQ, AV, BA 和BF 。行数为 80-155。我只能在我的 COUNTBLANK 工作簿中创建一个简单的 TEST 公式，例如:
=COUNTBLANK('[FLOW.xlsx]Class_1-Chart'!jan_2019_class.1)
但不适用于连续的列(具有不同的命名范围并且列是非顺序的)；而且我无法弄清楚与此相结合的功能公式以获取上述条件返回的计数和数据。请不要使用 VBA/宏。

预先感谢您的帮助!

'TEST.xlsx' Screen Shot-RVSD

FLOW.xlsx- sample screenshot

Answer 1

有很多方法，但我个人更喜欢使用辅助行/列/单元格和命名范围。
在我的演示中，我使用了从 1 月到 6 月两个不同年份的两个类主任时间表，如下所示(在我的示例中，他们坐在 C 到 M 列):

如上所示，我在每个计划的顶部添加了两个辅助行。第一个辅助行用于查找每个月是否有 2 个或更多职位空缺，如果有则返回 TRUE .我给了名字 check.2019.class.1 和 check.2021.class.5 对于他们每个人。
第二个辅助行只是显示每个月的范围名称，例如 jan_2019_class.1 , feb_2019_class.2等等。我给了名字 NameRng.2019.class.1 和 NameRng.2021.class.5 对于他们每个人。
在 TEST表我有以下设置:

单元格中的查找值C3实际上是由公式返回的，因此它可以由用户“动态”更改。请注意，在下面的公式中，我使用了一个名称 类号 这本质上是单元格 B3 中的值.

=B2&"_"&B1&"_class."&ClassNo

我还命名了单元格 C3作为 开始_MthYrClass 这将在我的以下公式中使用。
在 中查找第一个可用月份的公式2019 如果开始月份是 jan_2019_class.1是:

=INDEX(NameRng.2019.class.1,MATCH(1,(TRANSPOSE(ROW($1:$11))>=MATCH(Start_MthYrClass,NameRng.2019.class.1,0))*Check.2019.class.1,0))

请注意它是 数组公式所以你 必须 完成公式栏中的公式后按 Ctrl+Shift+Enter，否则它们将无法正常工作。

The logic is to first "filter" the range NameRng.2019.class.1 using this formula =TRANSPOSE(ROW($1:$11))>=MATCH(Start_MthYrClass,NameRng.2019.class.1,0), in which ROW($1:$11) represents {1;2;3;4;5;6;7;8;9;10;11} and TRANSPOSE will turn it into {1,2,3,4,5,6,7,8,9,10,11}. This range of numbers represents the column index in that specific range which is Column C to M (in your case it would be ROW($1:$56) as your data is in Column C to BF). Then I use MATCH to return the start column index of the look up month jan_2019_class.1, and it should return 1 as this month starts in the 1st place/column in the range NameRng.2019.class.1. So this is what I am actually comparing: {1,2,3,4,5,6,7,8,9,10,11}>=1, and it will return {TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}.

Then I multiply the above result with range Check.2019.class.1 which is essentially {FALSE,0,FALSE,0,TRUE,0,FALSE,0,TRUE,0,TRUE}. Then I will get {0,0,0,0,1,0,0,0,1,0,1}. FYI in Excel TRUE=1 and FALSE=0, so TRUE x FALSE = 0 while TRUE x TRUE = 1.

Lastly, I use MATCH to find out the position of the first 1 in the above result which is the 5th place/column, and then use INDEX to return the corresponding value from range NameRng.2019.class.1 which is mar_2019_class.1.

这是一个更通用的公式，可让您在第一个单元格中输入 C6并向下拖动以全面应用， 如果您以与我演示的方式相同的方式为相关单元格和范围命名 .

=IFERROR(INDEX(INDIRECT("NameRng."&B6&".class."&ClassNo),MATCH(1,(TRANSPOSE(ROW($1:$11))>=MATCH(Start_MthYrClass,INDIRECT("NameRng."&B6&".class."&ClassNo),0))*INDIRECT("Check."&B6&".class."&ClassNo),0)),"")

它也是一个 数组公式所以你 必须 在编辑栏中完成公式后按 Ctrl+Shift+Enter。

It is essentially the same formula as the first one but I have added IFERROR to return a blank cell if there is no match, and I used INDIRECT to refer to the named ranges dynamically based on the year and class number chosen.

现在，如果我将查找条件更改为 mar_2021_class.5 ，这是一个更新的结果:

如果您有任何问题，请告诉我。干杯:)