索引(匹配 - 与另一个 excel 文件中不同工作表中的动态数组匹配

索引(匹配 - 与另一个 excel 文件中不同工作表中的动态数组匹配

我正在尝试借助 Index(match) 公式从另一个文件中检索数据,挑战在于我希望数组是动态的;因为文件位置(路径)可能会有所不同,并且工作表名称也会有所不同。

我曾尝试在单元格 A1 中输入路径,在 A2 中输入工作簿名称,在 A3 中输入工作表名称(单元格数组始终是总工作表),并使用“concacenate”来获得完整路径、文件名和数组。不幸的是,它给出了 med #REf。

我也尝试在公式中写入整个路径,并使用“&A2'”来获取位置 + 工作表名称(后者输入到单元格 A2 中),但随后会出现一个对话框,提示“工作簿中不存在工作表 A2”。看来我不会使用 Excel,无法理解在 A2 中输入的内容应用作输入)。

这个公式是我的起点;'=INDIRECT("'["&B6&"]"&C6&"'!"&D6) 不幸的是 - 它不包含文件位置的路径,这显然让我感到困惑。

我已经看过好几遍了,但不幸的是,我现在完全看不懂——看不出错误可能在哪里。希望你能给我一些很好的提示

答案1

解决此问题的传统方法是使用INDIRECT()(以及其他函数,如果文件打开则查找不同的文件,如果文件关闭则不查找)将电子表格的一部分专门用于收集该数据,并使用公式来查看另一个电子表格,即使在关闭时也是如此。

因此,您可能有一个非常隐蔽的页面,它从已关闭的电子表格中收集了一些数据。然后,您的“用户可见”页面实际上将引用当前电子表格中的数据,而不是已关闭电子表格中的数据来源。这很简单。您仍然可以使用INDIRECT()这种方法,因为可以组织数据,以便以各种方式反映其来源INDIRECT(),因此您可以利用这些来源。

困难在于所有这些都必须内置。例如,您可能为每个文件都准备了一个非常隐蔽的工作表,并且数据可能通过公式(例如='blah-blah'!A1或任何匹配)收集。因此,大多数元素都会在那里,您可以要求用户提供或选择文件名、路径等,然后以略微不同于直接的方式实际使用它们,以符合您在此电子表格中接近镜像的结构中寻找答案的想法。

那么,难度如何?嗯,这取决于你如何通过寻求输入的方法做到这一点,你可能完全没有问题。例如,从你创建(并维护)的列表中进行选择可以让你完全掌控并完全预见。他们无法输入你事先不知道的信息,因此你可以以适当的方式构建你的“传输数据”来处理你允许的任何组合。

但是……动态……这可能意味着他们输入的是完全随机的路径和文件名,或者即使不是这些,他们也可能输入数据子集(范围),如果他们输入了错误的信息,一切都会崩溃。如果范围是 A1:D4,而他们输入的是 C1:M28,那么,没有真正的方法可以克服它,除非在你的公式中进行可怕的错误检查。

但是,范围本身甚至可以是用户电子表格中的命名范围。当然,数据收集应该使用它们(与用户可用的范围并行命名{因此数据收集使用类似“Input_Range”的内容,用户使用或从“InputRange”列表中选择,这种并行}),这样就可以获得正确的数据,而不是仅仅为了……好玩而输入奇怪的范围。

但要真正做到广泛应用呢?基于公式的方法无法做到这一点,除非采用一种有趣的方法,在这里解释起来有点复杂。那么你能做什么呢?

如果您想要一个实时的、经常刷新的数据链接,Power Query 可以为您做到这一点。这是一个需要学习的全新事物,但它不仅可以做到这一点,而且它还“隐藏”了 SQL,但不会对您隐藏它,只是让您以类似于在 GUI 中用鼠标指向文件的方式工作,而不是在命令窗口中键入。它可以被编辑和修改。可以把它想象成一个宏,您可以记录它,然后编辑它以使其“恰到好处”。优点是自动刷新、可构建的特性、使用几乎无限的数据(比如说数亿行)的能力、包含 Excel 文件,以及很多人(可能包括您)已经知道如何使用 SQL。

但是,要想获得绝对控制权,最好还是使用 VBA。在 VBA 中,错误检查和用户限制非常简单。如果您自己对 VBA 不够了解,那么有很多人会为人们编写 VBA,以赚钱。这没什么可羞耻的,您是在雇佣技术人才,可以从他们完成的工作中学习。VBA 的一个有趣之处在于,如果需要,它可以打开文件并提取数据,而无需将文件暴露给用户。它可以遵循任何给定的数据路径,并轻松处理其中的错误。它可以用其他几种语言进行扩展,如果 Excel 不允许使用它们,其他各种语言都有自己的 Excel 软件包。它还可以完全控制您可以使用的表单,而不是原始的工作表端电子表格。这确实是黄金标准。

因此,一切都取决于您对输入的控制程度。如果用户可以输入任何旧内容,那么您将面临一场噩梦。如果他们必须从经过验证的列表中进行选择,您可以按照公式进行选择。如果您拥有知识和/或资金,Power Query 和 VBA 可以提供近乎完美的解决方案。如果没有,那么,就会有一个学习曲线或一些金钱支出。

然而,没有人可以提供一件事: INDIRECT()不会为您查看另一个文件,并且没有人发现其中的漏洞或强迫其这样做的方法。

最后的想法,不管是否已经足够长,是 VBA 能够通过查看单元格的修改,甚至只是单击,或者更传统地,通过屏幕上放置的命令按钮来触发操作。因此,如果您不需要绝对精巧和难以理解的东西,并且您的用户是驯服的生物,您可以录制宏来打开文件并从每个文件(其中的不同位置等等)收集适当的数据,并将其放在电子表格中的工作表中。打开、收集和放置您计划让他们使用的每个文件和范围,您的宏将包含来自录制的命令以再次执行每个操作。然后编辑宏以清除需要放置数据的工作表,然后放置变量,这些变量在用户单击按钮(甚至是您在其中键入“单击以加载”的单元格)时从他们的输入中收集用户的路径、文件名和范围。使用这些变量从记录的行中选择正确的打开/收集/放置命令。让宏执行该收集命令并结束。轰!宏会清理中间表,然后使用用户选择的参数打开正确的文件,选择正确的数据范围,并将其全部放入电子表格中,这样展示页面的公式就可以找到它并发挥你的魔力。有了这些,设置起来就很容易了,不过如果你以前没有这样做过,那么在设置时会很担心,虽然它无法处理错误或用户故意犯错,但如果用户表现良好,它就会很好地发挥作用。

如果需要,您可以随着时间的推移不断改进它,因为您有时间并学会了下一步。您会学到东西,您会得到可以应用于其他事物的实践,最终,您实际上会有一个程序,而不是电子表格。

当然,一旦你让它运行良好,人们就不会再问“你为什么不……?”之类的问题了,你就会有一个经过测试、完全符合规格的程序,并且可以轻松地聘请程序员在 Excel 之外为你编写一个专用程序,该程序将通过你公司自己的私人程序完成所有工作。所有常见的价格上涨问题都可以避免,因此它可能非常经济。我们在这里通常使用电子表格来实现这个想法,一旦我们通过电子表格解决了所有问题和需求,我们公司的骨干程序所有者就会为我们编写独特的函数和报告。你只需要使用 VBA 而不是展示页面的工作即可。

相关内容