我正在寻找一个函数来动态地在工作簿的每个工作表中执行查找。最终可以将工作表的名称放在单独的工作表中,以使用间接函数。我想知道在哪些工作表中引用了特定值。
举个例子,我想知道在哪个工作表中引用了“Water”(A1),以逗号分隔。如果同一张工作表中有多个引用,则应多次列出工作表的名称。
执行此静态操作时我使用以下函数:
=HLOOKUP(A1; Sheet1!A1:A30;1;TRUE)&","&HLOOKUP(A1; Sheet2!A1:A30;1;TRUE)&","&HLOOKUP(A1; Sheet3!A1:A30;1;TRUE) ....
我在每张工作表的 A1 上放置了工作表名称。
所附的屏幕截图应该会更清楚一些:
A 列包含每张表中要查找的值。B 列是我想要的动态函数:
查找值的工作表示例:
您认为没有 VBA 这可能吗?
答案1
由于您似乎每张表仅查询大约 30 行,因此您应该在使用TEXTJOIN
和的解决方案的限制范围内FILTERXML
(更大的范围将使其变得不可行)。
假设你创建了一个有序的,垂直的在某个工作表中列出工作表名称(例如“Joe”、“David”、“Laura”) - 最左边和最右边的工作表分别位于顶部和底部 - 并将该列表存储为定义名称图纸清单:
=LET(δ,SheetList,κ,Joe:Laura!A$1:A$30,ζ,FILTERXML("<a><b>"&TEXTJOIN("ξ</b><b>",0,κ)&"ξ</b></a>","//b"),λ,ROWS(ζ),IFERROR(TEXTJOIN(", ",,INDEX(δ,1+QUOTIENT(FILTER(SEQUENCE(λ),ζ=A2&"ξ")-1,λ/ROWS(δ)))),""))
其中 3D 参考
Joe:Laura!A$1:A$30
应该只包含要查询的最左边和最右边的工作表。