excel - 这些公式可以简化吗？为什么INDIRECT函数在MATCH公式中的ISBLANK测试中似乎不起作用？

Question

摘要
我需要一个数组公式，该公式需要从Sheet1获取一定长度的数据行。对于该行，在每个非空白列中，我需要获取该列的Sheet1标头值，并将该数据显示在Sheet2上的连续行中（该行的单元格之间没有任何空格）。
背景
我在工作表1上有一张数据表（员工和行业认证，失效日期是该表的单元格数据），电子表格正在跟踪的每个员工都有一行。认证是专栏。
我们正在使用此信息链接到ID徽章打印机软件（Bodno Silver），在此我们仅限于将数据列链接到特定文本框。
问题在于并非每个人都拥有所有认证。这些行上用空格隔开，以分隔每位员工确实拥有的证书。在徽章软件模板中设置所需的文本框时，每个文本框都链接到一个特定的列，我很快意识到，如果不是每个人都使用此数据，那么每个人都可以获得所有认证，因此在它们之间会有很多奇怪的空白。列出的认证，而不是连续的列表。
我做了什么
我对此的解决方案（除了“使用更好的软件”之外，如果有人知道的话，我会向更好的人开放），是创建一个新的工作表和数组公式，除了我和id打印机，没人会使用软件。该工作表将具有类似的数据表，该数据表将在到期日期之间散布有空白单元格的数据行，并将其中具有日期的单元格的匹配列标题放入具有相同最大长度的连续行中（消除了空白单元格） ）。
从本质上讲，这将使我能够规避徽章软件的限制，并且每个文本框都是MatchCert1，MatchCert2，MatchedCert3等，最高可达原始的最大证书数量。
图片可能比我的文字更好地解释了我要做什么：
Sheet1（来源）

Sheet2（结果）

数组公式
我做了一段时间。我认为这将是一个简单的INDEX，MATCH，ISBLANK公式（我可以使用适当的相对和绝对单元链接来创建），然后扩展到整个工作表，变成巫婆狩猎，而我为所有这些为自己的罪过祈祷可能是圣洁的。还有很多谷歌搜索...。我很快意识到这可能毕竟不是那么简单。
最后，我得出以下两个数组公式，以正确显示我要做什么：
培训科第一栏

{=IFERROR(INDEX(Sheet1!$E$2:$P3,1,MATCH(FALSE,ISBLANK(Sheet1!E3:Q3),0)),"")}

（足够容易，对吗？我是这么认为的...）
在对此进行思考之前，我一直在努力思考如何使公式具有通用性，以便可以在整个表格中使用它。
我只是公开发布以下内容而感到肮脏，但是这里...
第二列到最后一列的数组公式

{=IFNA(INDEX(INDIRECT(ADDRESS(ROW($E$2),(MATCH(E3,Sheet1!$2:$2,0)+1),1,1, "Sheet1")&":"&ADDRESS(ROW(E3),COLUMN($Q3),1)),1,MATCH(FALSE, ISBLANK(INDEX(INDIRECT("Sheet1!"&ADDRESS(ROW(E3),(MATCH(E3,Sheet1!$2:$2,0)+1),1)&":"&ADDRESS(ROW(E3),COLUMN($Q3),1)),0,0)), 0)),"")}

（请不要报警...）
[ninja edit]尽管此数组公式适用于从第二列到最后一列的结果，但如果结果范围后面没有空白列，则该列公式将不起作用。实际的电子表格具有水平运行的4个不同的认证组，但是我能够轻松地在另一张表的相应数据中添加空白列，因此我就放任不管了。我也会给别人一个答案，为什么在这里也是这种情况[/ edit]
结果
第一个数组公式以及使用ISBLANK的INDEX MATCH非常简单。
对我来说，最大的问题是，为什么第二个数组公式需要嵌套在ISBLANK函数内部的附加INDEX函数，所以让我连续几天疯了。
在拆解函数并进行试验时，我意识到，如果ISBLANK函数内部具有任何INDIRECT引用，而ISBLANK函数本身就是MATCH函数内部的对象，则匹配结果始终为1：

{=MATCH(FALSE,ISBLANK(INDIRECT("$E3:$Q3")), 0)}

上面的ALWAYS返回1，但是如果我将范围显式放入，该函数将正常工作。这不是我的选择，因为我需要使用上一个单元格的地址动态返回比赛的起始位置。
但是，添加INDEX函数（列和行值为0）以封装INDIRECT函数可提供正确的答案。我只是通过反复试验弄清楚了这一点。
问题
可以让有更多知识的人让我知道是什么导致了这种现象吗？
作为一个更广泛的问题，考虑到我仅限于使用公式（不使用VBA），我还想知道我是否以错误的方式进行此操作，或者是否有一种更简单的方法来完成此操作，而又没有这种庞然大物式？
我知道这张纸可能会在一年内需要维护-祝自己好运！

Answer 1

将此放入E3，上下复制

=IFERROR(INDEX(Sheet1!$2:$2,AGGREGATE(15,6,COLUMN(INDEX($E:$P,MATCH($C3,Sheet1!$C:$C,0),0))/(INDEX(Sheet1!$E:$P,MATCH($C3,Sheet1!$C:$C,0),0)<>""),COLUMN(A:A))),"")

至于为什么您的公式不起作用，它太复杂了以至于无法解析。请注意，除非工作表是变量，否则应尽可能避免INDIRECT。 INDEX几乎总是可以代替它使用。

INDIRECT和ADDRESS都是易失性函数。每次Excel重新计算时，易失函数都会重新计算，从而导致许多不必要的计算。