google-apps-script - 带 IN 子句的 Google 表格过滤器

Question

问题:在 Google 表格中，仅使用内置函数，如何编写过滤器来根据每行中的列不存在于另一个范围的有效值列表中。

详细信息

我正在使用 Google 表格编写财务电子表格，其中我的所有支出和收入都输入到名为“交易”的单张表格中。我有一个名为常量的单独工作表，其中保存了收入类别列表和费用类别列表。

以下是用于解决问题的一些示例数据:

常量

交易

我有一张名为“ByMonth”的工作表，我想在其中一个部分中显示所有费用交易，并在一个单独的部分中显示所有收入交易。目标:

我本质上需要在 Google Sheets 函数中执行此 SQL 查询:

select date, category, amount from transactions 
where category not in (
    select * from expense_categories
)

但我不知道如何让 Google 表格让我用它们的函数做 IN 概念。

这是一个单元格函数表达式，我用它来根据日期字段过滤行:

=filter(Transactions!A2:C, 
        DATEVALUE(Transactions!A2:A) >= date(2015,4,1), 
        DATEVALUE(Transactions!A2:A) <= date(2015,4,30)
)

我将向 filter() 函数添加第三个条件，第三个条件将以某种方式将每行数据的类别列与有效费用或收入类别列表进行比较，并返回一个 bool 值。

以下是我尝试过的其他一些方法，但没有成功，包括 MATCH、ARRAYFORMULA 等的一些变体:

=filter(Transactions!A2:C, row(Transactions!B2:B) = UNIQUE(Constants!A2:A))
=filter(Transactions!A2:C, Transactions!B2:B = UNIQUE(Constants!A2:A))
=filter(Transactions!A2:C, Transactions!B2:B = Constants!A2:A)

有什么建议吗？

Answer 1

您感兴趣的公式是:

=filter(Journal!A2:C13,IFERROR(Match(Journal!B2:B13,Categories!A2:A3,0)))

函数match(a, b, 0)搜索是否可以在 b 中找到 a。末尾需要 0 才能精确匹配(默认为最接近匹配)。如果找不到 a，则会出现错误，因此您可以使用 iferror 包装函数来跳过错误。

这里是working example file