我正在使用这个公式:
=IF(A3=A2,
pull(CONCATENATE("'F:\ITO\",A3," Project","\", "[",A3,".xlsx]Server Config'!","A5")),
pull(CONCATENATE("'F:\ITO\",A3," Project","\","[",A3,".xlsx]Server Config'!","A4")))
其中 A 列包含项目编号。并非所有项目都具有相同的行数,并且每次项目更改时都必须重新计算 A4。
我该如何表达这个公式,或者说,如果项目编号与前一行相同,则继续用 A5、A6..... 计数,直到项目编号发生变化,在这种情况下必须重新从 A4 开始?
该表的结构如下:
A2 = "Project Number"
B2 = =pull(CONCATENATE("'F:\ITO\",A2," Project","\","[",A2,".xlsx]Server Config'!","A4"))
A3 = "Project Number"
B3 = =IF(A3=A2,pull(CONCATENATE("'F:\ITO\",A3," Project","\","[",A3,".xlsx]Server Config'!","A5")),pull(CONCATENATE("'F:\ITO\",A3," Project","\","[",A3,".xlsx]Server Config'!","A4")))
每个项目都是对一个文件的引用,每个文件在相同的单元格中包含不同的信息。
答案1
所以基本上您想计算 A 列中引用同一项目的前一个单元格的数量,然后从该计数中的外部文档中选择适当的单元格?
如何使用 COUNTIF 来计算项目引用,然后使用该计数添加到“A4”?如果您在第一行号前面放置一个美元符号,则当您向下复制公式时,该符号将保持不变,例如始终从工作表顶部向下计数到当前行。
=COUNTIF(A$1:A1;A1)
=COUNTIF(A$1:A2;A2)
最终公式如下
B2 = =pull(CONCATENATE("'F:\ITO\",A2," Project","\","[",A2,".xlsx]Server Config'!","A", 3+COUNTIF(A$2:A2;A2)))