如何使用查询函数与复选框分类多个列

发布于 2025-02-13 20:27:49 字数 926 浏览 4 评论 0原文

我需要使用查询函数和复选框对多个列进行排序，这些复选框指示要排序哪些列。这应该与多列一起使用。

这是公式：

=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…")

如果我仅检查1个复选框，则适用于单列。如果我检查多个复选框，是否有办法使其对多列进行排序？

演示文件：
https://docs.google.com/spreadsheets/d/1x-ikvur8heobwkkaozwfditsf4wxwfcgropiewokaoi/edit

原文

I need to sort multiple columns using the QUERY function together with checkboxes which indicate which columns to sort. This should work with multiple columns.

This is the formula:

=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…")

It works for single columns if I check only 1 checkbox.
Is there a way to make it sort multiple columns if I check multiple checkboxes?

Demo file:
https://docs.google.com/spreadsheets/d/1x-IkvUr8hEoBwkKaoZWfdITSf4WXwFCgROpIEwOkaOI/edit

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

水水月牙 2025-02-20 20:27:49

尝试：

=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 "&IF(SUM(C2:O2*1)>0,
  "order by "&TEXTJOIN(", ", 1, "Col"&MATCH(FILTER(C3:O3, C2:O2=TRUE), AI3:AY3, 0)&" "&
  IF(D1="descending", "desc", )), ), ))

try:

=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 "&IF(SUM(C2:O2*1)>0,
  "order by "&TEXTJOIN(", ", 1, "Col"&MATCH(FILTER(C3:O3, C2:O2=TRUE), AI3:AY3, 0)&" "&
  IF(D1="descending", "desc", )), ), ))