Google表格如何结合复选框使用QUERY函数实现多列排序

需求说明 #

通过标记待排序列的复选框，搭配QUERY函数实现排序能力，要求支持同时勾选多个列时多列联合排序生效。

现有实现问题 #

当前使用的公式仅支持单个复选框勾选时的单列排序，原公式如下：

=IFERROR(ARRAYFORMULA(
    QUERY(
        {AI4:AY101, TRIM(FLATTEN(QUERY(TRANSPOSE(AJ4:AY101),,9^9)))}, 
        "Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17
        where Col18 is not null
        order by 
        Col"&$P$1&" "&$P$3&"
        ", )),"Loading…")

原有逻辑的缺陷是order by子句仅拼接了单个列的排序规则，没有遍历所有勾选的复选框生成多列排序规则串，无法适配多列排序的QUERY语法要求。

调整方案 #

用TEXTJOIN搭配条件判断动态拼接所有被勾选列的排序规则，替换原公式中固定引用单个排序列的部分即可，调整后公式：

=IFERROR(ARRAYFORMULA(
    LET(
        data_range, AI4:AY101,
        // 替换为你实际存放复选框的行范围，需和排序列一一对应
        checkbox_range, AI3:AY3,
        // 全局统一排序方向，asc为升序、desc为降序；如果要每列单独设方向，改成和checkbox_range等宽的方向值范围即可
        sort_order, $P$3,
        col_index, SEQUENCE(1, COLUMNS(checkbox_range), 1),
        // 自动拼接所有勾选列的排序规则，逗号分隔，符合QUERY语法要求
        sort_clause, TEXTJOIN(", ", TRUE, IF(checkbox_range, "Col"&col_index&" "&sort_order, "")),
        QUERY(
            {data_range, TRIM(FLATTEN(QUERY(TRANSPOSE(AJ4:AY101),,9^9)))}, 
            "Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17
            where Col18 is not null
            order by "&sort_clause&"
            "
        )
)),"Loading…")

使用注意事项 #

• 请将公式中checkbox_range参数替换为你工作表中实际放置排序列复选框的单元格范围，保证范围宽度和待排序的数据列宽度一致、位置一一对应
• 排序优先级和复选框范围的列顺序一致，范围中越靠左的列，勾选后排序优先级越高
• 如果需要给不同排序列单独设置升降序，将sort_order替换为和复选框范围等宽的单元格范围，每个单元格填写对应列的asc（升序）或desc（降序）即可

内容的提问来源于stack exchange，提问作者Verminous