google-sheets - 在 Google 表格中的多个页面上对唯一查询求和
问题描述
这是困难的一个!
在“报告”选项卡上,在单元格 C22 和 D22 中,我试图弄清楚如何合计 11 月和 10 月支付的金额。跨所有选项卡(并在添加将来的选项卡时包括它们)。
您可以在单个股票编号页面(例如“PT-1010!”K14:K)上看到付款已按当月总额细分。我如何搜索所有页面,找出它是否显示“11 月 20 日”并将它们全部计数 - 并吐出一个数字?
我的大脑很痛大声笑 - 非常感谢任何帮助!
解决方案
这可能会有所帮助。(在这里查看我的样本表)
这使用来自客户页面的股票编号列表中的间接寻址来获取所有付款的数组,日期少于现在。我使用以下公式将此列表拉到一个位置(我的测试中的 U2):
=query(Customers!A5:A,"where A<>'' order by A",0)
所以这始终是一个当前/动态列表。
然后主公式使用间接寻址从该列表中提取选项卡名称,并收集所有数据。我添加了过滤器以减少收集的行数,因为您有很多未来的日期。
然后我使用查询来过滤所需的月份,使用间接寻址到报表中的日期单元格。这允许对 C22(11 月)和 D22(10 月)使用完全相同的公式。
计算每个月支付总额的主要公式是:
=query({filter(indirect(U3 &"!B7:E"),indirect(U3 &"!B7:B")<Now());
filter(indirect(U4 &"!B7:E"),indirect(U4 &"!B7:B")<Now());
filter(indirect(U5 &"!B7:E"),indirect(U5 &"!B7:B")<Now());
filter(indirect(U6 &"!B7:E"),indirect(U6 &"!B7:B")<Now());
filter(indirect(U7 &"!B7:E"),indirect(U7 &"!B7:B")<Now());
filter(indirect(U8 &"!B7:E"),indirect(U8 &"!B7:B")<Now());
filter(indirect(U9 &"!B7:E"),indirect(U9 &"!B7:B")<Now());
filter(indirect(U10 &"!B7:E"),indirect(U10 &"!B7:B")<Now());
filter(indirect(U11 &"!B7:E"),indirect(U11 &"!B7:B")<Now());
filter(indirect(U12 &"!B7:E"),indirect(U12 &"!B7:B")<Now());
filter(indirect(U13 &"!B7:E"),indirect(U13 &"!B7:B")<Now());
filter(indirect(U14 &"!B7:E"),indirect(U14 &"!B7:B")<Now());
filter(indirect(U15 &"!B7:E"),indirect(U15 &"!B7:B")<Now());
filter(indirect(U16 &"!B7:E"),indirect(U16 &"!B7:B")<Now());
filter(indirect(U17 &"!B7:E"),indirect(U17 &"!B7:B")<Now());
filter(indirect(U18 &"!B7:E"),indirect(U18 &"!B7:B")<Now());
filter(indirect(U19 &"!B7:E"),indirect(U19 &"!B7:B")<Now()) },
"select sum(Col4) where Col4 >0 and Col1 >= date '"& text(indirect(address(row()-3,column() ,4,1)),"yyyy-mm-dd") &"'
and Col1 < date '"& text(indirect(address(row()-3,column()-1,4,1)),"yyyy-mm-dd") &"' label sum(Col4) '' ",0)
仅使用公式而不是脚本,我想不出另一种方法来循环浏览多个选项卡。您可以重复公式中的 FILTER 行来处理您需要的尽可能多的客户。如果您想要更多,那么您可能需要使用脚本。如果您现在扩展公式,您可能需要虚拟标签来处理未来的客户。
而且,我认为可能需要进行一些错误检查,以处理间接寻址选项卡中可能出现的错误。可能是一个测试,以确保我们没有从库存编号列表中提取空白值,这等同于选项卡名称。或者,一个 IFERROR 可以涵盖这些,每个 FILTER 语句都有一个。
让我知道这是否有帮助。
如果您删除最后几行中的日期条件,并将“sum(Col4)”更改为“Col1, Col4 order by Col1”,您将获得所有包含付款的数据记录(每个选项卡中的 E 列,对吗?)并且可以验证结果。
推荐阅读
- javascript - 在不同的 div 之间对齐内部 div 项
- node.js - 我应该什么时候在 nodejs 环境中调用 mongodb 中的 createIndex?
- webos - 如何获取 webos 电视上已安装应用程序的列表?
- java - Android Java无法在片段中显示requestPermission
- c++ - 将 slice_array 分配给另一个 slice_array 是否正确?
- embedded - 我如何 JIT 编译代码以跨操作系统和裸机运行?
- asp.net-core - 我在哪里可以找到与用户注册相关的 IdentityErrors 列表?
- javascript - 使用 express/node.js 启动服务器时出错。跟随 The Coding Train YouTube 教程
- sql - 选择日期列,后跟 (DATE)
- google-cloud-platform - Google Cloud - 没有 storage.buckets.get 访问 Google Cloud Storage 存储桶的权限