时间戳

首页 > 解决方案 > 表格中的弹性报告数据 - 从多个表格中提取（相同的电子表格）

arrays - 表格中的弹性报告数据 - 从多个表格中提取（相同的电子表格）

问题描述

我使用 Google 表格跟踪试验，并为每个客户提供一个标签。在该表中，有一个未完成项目的部分。我不必查看每个选项卡中的未完成项目，而是尝试将“打开”的项目汇总到单个摘要页面。

摘要页面需要是动态的，因为项目从“打开”变为“关闭”，而且还自动考虑为启动的新客户试用创建的新选项卡。同样，数据可能并不总是从 A45 开始，如果我必须在上面添加内容，它可能会从 A49 开始，但我在上面的行中有一些东西可以尝试触发（如果可能的话）

尝试使用FILTER功能执行此操作，但这有问题。我要么必须手动添加每一页，并具有适当的间距，要么使用串联过滤器来解释有问题的确切单元格。

希望看到摘要页面是动态的（或尽可能动态的）。对于现有的试验，如果我将某些内容从“开放”移动到“关闭”，它将从摘要页面中删除，其他所有内容都会向上移动。如果某些内容从“关闭”变为“打开”，请相应地插入并将所有内容向下移动。最好的情况是，当我将模板文件复制到新客户的新选项卡时，该新选项卡会自动包含在从中提取摘要报告的数据集中。

更新：

https://docs.google.com/spreadsheets/d/1759z0SoxANp--yt4HcI06-jpEt951ejJquYpwKE9V9E/edit?usp=sharing

这是我正在尝试做的事情的基础，但需要一些手动操作和错误。您可以看到 Cust-A 和 B 的问题数据如何填充到该FILTER函数提供的“未决问题”选项卡中。不幸的是，如果您要将客户 A 的 item4 的状态更改为“打开”，那么结果页面 (A3) 将进入#REF!状态，因为它现在需要 4 行，并且第 4 行被下一个过滤器手动占用。此外，FILTERA 与 B 不同，因为客户 B 有更多的联系人和成功标准。

我真的很想有一些东西可以自动运行并查看每张纸，包括当我为客户 C 添加一个新选项卡时。这将阻止我必须定义所需的正确数量的空间以及重新FILTER为每个新标签。

标签： arraysgoogle-sheetsfiltergoogle-sheets-formulagoogle-sheets-query

解决方案

解决这个问题：

0

删除A列中的所有内容并将其粘贴到A3单元格中：

={FILTER('CUST-A'!A18:O29, 'CUST-A'!F18:F29="Open");
  FILTER('CUST-B'!A20:O31, 'CUST-B'!F20:F31="Open")}

0

但这还不够，为了应对进一步的错误，您需要像这样：

=QUERY(
 {IFERROR(FILTER('CUST-A'!A18:O29, 'CUST-A'!F18:F29="Open"), 
 {"","","","","","","","","","","","","","",""});
  IFERROR(FILTER('CUST-B'!A20:O31, 'CUST-B'!F20:F31="Open"),
 {"","","","","","","","","","","","","","",""})}, 
 "where Col1 is not null", 0)

0

那么你甚至可以这样做：

=QUERY(
 {IFERROR(FILTER('CUST-A'!A18:O29, 'CUST-A'!F18:F29="Open"), 
 {"","","","","","","","","","","","","","",""});
  IFERROR(FILTER('CUST-B'!A20:O31, 'CUST-B'!F20:F31="Open"),
 {"","","","","","","","","","","","","","",""});
  IFERROR(FILTER('CUST-C'!A20:O31, 'CUST-C'!F20:F31="Open"),
 {"","","","","","","","","","","","","","",""});
  IFERROR(FILTER('CUST-D'!A20:O31, 'CUST-D'!F20:F31="Open"),
 {"","","","","","","","","","","","","","",""});
  IFERROR(FILTER('CUST-E'!A20:O31, 'CUST-E'!F20:F31="Open"),
 {"","","","","","","","","","","","","","",""})}, 
 "where Col1 is not null", 0)

0

推荐阅读