Google Таблицы - Как объединить функцию фильтра с представлением фильтра

Я работал над электронной таблицей с более чем 100 строками и нашел хитрый способ включить флажок «скрыть», который будет скрывать любую строку, в которой столбец C соответствует определенному значению (типу здания), указанному рядом с полем. Для этого я сначала создал такую ​​функцию: =FILTER(Data!A1, OR(Data!$C1<>$O$2, $P$2)) и перетащил ее через каждую строку и столбец на отдельном листе. Это читается как: «Отображать текущую ячейку, если соответствующий столбец C в этой строке в Data не соответствует типу здания, или если флажок установлен. Таким образом, вся строка скрывается, когда тип здания совпадает, а поле не отмечен. A1 настраивается для каждой строки индивидуально, $ C1 ссылается на тип здания, $ O $ 2 ссылается на целевой тип, который потенциально можно скрыть, и $ P $ 2 был флажком.

Проблема №1: в сотнях ячеек было создано множество формул, и когда тип здания не был найден, во всей строке отображалось # N / A. В представлении фильтра можно было скрыть эти значения, но было неудобно сбрасывать значения каждый раз, когда я хотел скрыть или показать другой тип здания.

Моя попытка исправить: я снова использовал функцию фильтра, чтобы воссоздать весь лист из одной ячейки, скрывая соответствующие строки, используя это: =FILTER(Data!A2:J191, ARRAYFORMULA((Data!$C2:C191<>$O$2)+(Data!D2:D191*$P$2)) Это хакерская часть. Я умножил значение «истина» флажка на массив произвольных положительных числовых значений в столбце 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))) Это уродливо и очень медленно загружается. Есть ли способ создать диапазон функций для обработки одних и тех же проверок в нескольких строках и превратить его в единую формулу?


google-sheets sorting show-hide google-sheets-filter-view filterfunction
person Braxton Istace    schedule 12.06.2020    source источник
comment
ОБНОВЛЕНИЕ: я нашел способ обойти это, добавив флажок во второй строке для обработки сортировки, установив значения в отмеченных полях в диапазоне от 1 до 11, а не отмеченный равным 0, добавив функцию сортировки вокруг этой функции-монстра и добавив A2, False , B2, False и т. Д. И т. Д., Но из любви ко всему цифровому, кто-то должен сказать мне, как сделать это более эффективным.   -  person Braxton Istace    schedule 12.06.2020
comment
Пожалуйста, предоставьте образец листа.   -  person kishkin    schedule 12.06.2020
comment
Извините, я новичок на этом сайте. Ссылка на него будет работать? Я посмотрю, как предоставить образец. А пока вот ссылка. docs.google.com/spreadsheets   -  person Braxton Istace    schedule 13.06.2020

Ответы (1)


arrow_upward
1
arrow_downward

Вот вам еще одно чудовище (у этого меньше повторений):

=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 и простые регулярные выражения изменены.

person kishkin    schedule 13.06.2020
comment
Удивительный! Я понятия не имел о функции запроса. Это гораздо более управляемый элемент для расширения типов и строк. Именно то, что мне нужно. Большое спасибо! - person Braxton Istace; 13.06.2020
comment
@BraxtonIstace немного исправил решение. - person kishkin; 14.06.2020
comment
Ваше предыдущее решение, похоже, сработало, но когда я пробую обновленную версию, оно не скрывает все строки, которые должны быть скрыты. Я посмотрю на них и посмотрю, что я смогу выяснить за это время. Спасибо за приложенные усилия! - person Braxton Istace; 14.06.2020
comment
Извините, произошла опечатка. Исправлено сейчас. - person kishkin; 14.06.2020
comment
Спасибо, что убили этого монстра. Хотел бы я предложить больше, чем положительный голос, галочку и мою благодарность. - person Braxton Istace; 14.06.2020