如何在多张 Excel 工作表中使用索引

Question

我有一个公式，用于检查办公室工作表的 I 列中是否有数字。有没有办法让这个公式也检查其他工作表的 I 列？ =IFERROR(INDEX(Office!$B$2:$B$45,SMALL(IF(ISNUMBER(Office!$I$2:$I$45),ROW(Office!$I$1:$I$44),""),ROW(Office!B11))),"")

我希望最终结果看起来像这样，我希望这是封面，这样有人可以轻松地看到需要购买什么，或者什么已经过期：

这是 Office 工作表的副本，套件 1 看起来几乎完全相同，只是包含不同的项目（相同是相同的）。还有其他工作表，看起来几乎完全相同，只是项目列表不同。项目列表和编号应列在查找表中。

Answer 1

我创建了您的数据子集作为演示。我有两张表：

1. 办公室
2. 套件 1

我有两个命名表，分别放置在上述表格的左上角的单元格 B2 中：

1. 办公室
2. 套件1

选择 Office 表中的任意单元格，然后使用“数据”>“获取和转换数据”>“从表/范围”。在 Power Query 编辑器中，单击“使用主页”>“关闭并加载到”，然后选择“仅创建连接”。

重复此过程以创建与工作簿中每个表的连接。

完成此操作后，您应该会获得工作簿中所有表的连接列表。现在使用“数据”>“获取和转换数据”>“获取数据”>“合并查询”>“附加”，然后将所有连接移动到对话框的右侧：

这将创建一个包含所有表的查询。唯一缺少的是您需要为每行创建一个新的表名列。

在 Power Query 编辑器中，使用主页>查询>高级编辑器。我的查询最初如下所示：

let
    Source = Table.Combine({office,kit_1})
in
    Source

编辑查询定义，以便上面括号 {} 内的每个查询名称都被以下形式替换：

Table.AddColumn(office,"TableName",each "Office")

其中第一个参数是查询名称，第二个参数是列名，第三个参数是最终结果中表名列中的值。

因此，对于我的疑问，我有这样的：

let
    Source = Table.Combine({Table.AddColumn(office,"TableName",each "office"), Table.AddColumn(kit_1,"TableName",each "kit 1")})
in
    Source

现在查询如下所示：

由于我们只对已过到期日或购买需求小于零的行感兴趣，因此使用添加列>常规>自定义列添加一列并使用以下定义：

if [Need to Purchase]<0 or [Expiry Date]<=DateTime.LocalNow() then "Show on front page" else "Don't show on front page"

将该列命名为 Filter。

然后，在您的数据中，将该列过滤为“在首页上显示”，然后右键单击该列并使用删除。

现在使用 Home>Close & Load 将查询结果放回工作簿。

为了保持首页上的数据刷新，您可以手动右键单击最终查询结果并使用刷新，或者右键单击查询和连接窗口中的查询并选择属性，然后配置首页的刷新方式和刷新频率：