Google表格-如何将过滤器功能与过滤器视图结合 [英] Google Sheets - How to Combine Filter Function with Filter View

问题描述

我一直在处理具有超过100行的电子表格，并且发现了一种巧妙的方式来合并隐藏"复选框，该复选框将隐藏在C列与框旁边指定的特定值(建筑物类型)匹配的任何行.为此，我首先创建了一个像这样的函数:=FILTER(Data!A1, OR(Data!$C1<>$O$2, $P$2))并将其拖动到单独工作表中的每一行和每一列上.读作如下:如果数据中该行中的对应列C与建筑物类型不匹配，或者如果选中了复选框，则显示当前单元格.这样，当建筑物类型匹配时，整个行都被隐藏，并且该框A1分别针对每一行进行了调整，$ C1引用了建筑物的类型，$ O $ 2引用了可能隐藏的目标类型，$ P $ 2是复选框.

问题1:这在数百个单元格中创建了许多公式，并且当找不到建筑物类型时，它在整行中显示#N/A. 过滤器视图"能够隐藏这些值，但是每次我想隐藏或取消隐藏其他建筑物类型时都必须重置这些值是很不方便的.

我的修复尝试:我再次使用过滤器功能从一个单元格重新创建整个工作表，并使用以下方法隐藏适当的行:=FILTER(Data!A2:J191, ARRAYFORMULA((Data!$C2:C191<>$O$2)+(Data!D2:D191*$P$2))这是很棘手的部分.我将复选框的"true"乘以D列中的任意正数值数组，然后将其与每个建筑物类型值进行或"运算，以实现与以前相同的目标，但对于每个单元格.

问题2出现了:当我得到漂亮的图纸时，我无法通过过滤器视图对其进行排序，否则它将引发错误并且不显示任何内容.我要对原始选项卡进行排序，但打算完全忽略它.那么，如何结合过滤器视图"和过滤器功能"这两者来创建一个不错的电子表格，在其中可以对行进行隐藏和隐藏?

奖金问题3:要添加更多按钮，我的公式是:=FILTER(Data!A1:J191, ARRAYFORMULA((Data!$C1:C191<>$O$2)+(Data!D2:D192*$P$2)), ARRAYFORMULA((Data!$C1:C191<>$O$3)+(Data!D2:D192*$P$3)), ARRAYFORMULA((Data!$C1:C191<>$O$4)+(Data!D2:D192*$P$4)), ARRAYFORMULA((Data!$C1:C191<>$O$5)+(Data!D2:D192*$P$5)), ARRAYFORMULA((Data!$C1:C191<>$O$6)+(Data!D2:D192*$P$6)), ARRAYFORMULA((Data!$C1:C191<>$O$7)+(Data!D2:D192*$P$7)), ARRAYFORMULA((Data!$C1:C191<>$O$8)+(Data!D2:D192*$P$8)), ARRAYFORMULA((Data!$C1:C191<>$O$9)+(Data!D2:D192*$P$9)))这很丑陋，加载速度很慢.有没有一种方法可以创建一个函数范围来处理多行上的相同检查，并将其压缩为一个公式?

解决方案

这是另一种怪兽(此重复次数较少):

 =QUERY(
  {IGNORE!A2:J, IGNORE!P2:P},
    "SELECT * "
  & "WHERE Col3 is not null "
  & IF(COUNTIF(P2:P9, False) = 0, "", "AND NOT Col3 MATCHES '^" & JOIN("$|^", IFNA(FILTER(O2:O9, P2:P9 = False))) & "$' ")
  & IF(COUNTIF(A2:K2, ">0") = 0, "", "ORDER BY Col" & JOIN(", Col", IFNA(FILTER(COLUMN(A2:K2) & IF(COLUMN(A2:K2) = 1, "", " DESC"), A2:K2)))),
  0
)
 

您的复选框应保留.第二行可以只包含True/False值，而无需列号(COUNTIF(A2:K2, ">0")-> COUNTIF(A2:K2, True)将需要进行简单的更改).因此，随后的排序现在也可以使用(但仅按列的实际顺序:如果选中1、3、4，则将首先按1、2、4对其进行排序).您可以在排序的右侧放置另一个配置表，在这里您可以选择要排序的所有列，它们的相互顺序以及它们的desc/asc.

:添加了IFNA，因此FILTER不会返回错误，将多个ANDS更改为MATCHES和简单的正则表达式.

I've been working on a spreadsheet with over 100 rows, and found a hacky way to incorporate a "hide" checkbox that will hide any row where column C matches a specific value (building type), specified beside the box. To do this, I first created a function like this: =FILTER(Data!A1, OR(Data!$C1<>$O$2, $P$2)) and dragged that across every row and column in a seperate sheet. This reads as, "Display current cell if the corresponding column C in that row in Data does not match the building type, or if the the checkbox is checked. This way, the whole row is hidden when the building type matches, and the box is unchecked. A1 adjusted to each row individually, $C1 referenced the building's type, $O$2 referenced the targeted type to potentially hide, and $P$2 was the checkbox.

Problem #1: This created a lot of formulas in hundreds of cells, and when the building type was not found, it displayed #N/A across the entire row. A Filter View was able to hide these values, but it was inconvenient to have to reset the values every time I wanted to hide or unhide another building type.

My Attempt to Fix: I used a filter function once again to recreate the entire sheet from one cell, hiding the appropriate rows, using this: =FILTER(Data!A2:J191, ARRAYFORMULA((Data!$C2:C191<>$O$2)+(Data!D2:D191*$P$2)) This is the hacky part. I multiplied the checkbox's "true" by an array arbitrary positive numerical values in the D column to "OR" it with each building type value to achieve the same goal as before, but for EVERY cell.

Problem #2 arose: When I get my beautiful sheet, I can not sort it via a filter view, or it will throw an error and display nothing. I'm resorting to sorting the original tab, but intend to have it be ignored entirely. So how do I combine these two, Filter View, and Filter Function, to create a nice spreadsheet where I can SORT AND HIDE rows?

Bonus Problem #3: To add more buttons, my formula is this: =FILTER(Data!A1:J191, ARRAYFORMULA((Data!$C1:C191<>$O$2)+(Data!D2:D192*$P$2)), ARRAYFORMULA((Data!$C1:C191<>$O$3)+(Data!D2:D192*$P$3)), ARRAYFORMULA((Data!$C1:C191<>$O$4)+(Data!D2:D192*$P$4)), ARRAYFORMULA((Data!$C1:C191<>$O$5)+(Data!D2:D192*$P$5)), ARRAYFORMULA((Data!$C1:C191<>$O$6)+(Data!D2:D192*$P$6)), ARRAYFORMULA((Data!$C1:C191<>$O$7)+(Data!D2:D192*$P$7)), ARRAYFORMULA((Data!$C1:C191<>$O$8)+(Data!D2:D192*$P$8)), ARRAYFORMULA((Data!$C1:C191<>$O$9)+(Data!D2:D192*$P$9))) This is ugly, and very slow to load. Is there a way to create a function range to handle the same checks on multiple rows, and crunch it into a single formula?

解决方案

Here is another monstrosity (this one has less repetition) for you:

=QUERY(
  {IGNORE!A2:J, IGNORE!P2:P},
    "SELECT * "
  & "WHERE Col3 is not null "
  & IF(COUNTIF(P2:P9, False) = 0, "", "AND NOT Col3 MATCHES '^" & JOIN("$|^", IFNA(FILTER(O2:O9, P2:P9 = False))) & "$' ")
  & IF(COUNTIF(A2:K2, ">0") = 0, "", "ORDER BY Col" & JOIN(", Col", IFNA(FILTER(COLUMN(A2:K2) & IF(COLUMN(A2:K2) = 1, "", " DESC"), A2:K2)))),
  0
)

Your checkboxes should remain. The second row can have just True/False values, no need for column number (a simple change will be needed COUNTIF(A2:K2, ">0") -> COUNTIF(A2:K2, True)). Also consequent sort works now (but only in the actual order of columns: if checked 1, 3, 4 then it will be sorted first by 1, then by 2, then by 4). You could place another config table on the right about sorting, where you would select all the columns you wish to sort by, their mutual order, and desc/asc for them.

Edit: added IFNA so FILTER won't return an error, changed multiple ANDS to MATCHES and simple regexes.

这篇关于Google表格-如何将过滤器功能与过滤器视图结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！