Фильтрация таблицы с использованием информации из ячеек за пределами таблицы

Question

У меня есть таблица, которая извлекает информацию из нескольких различных источников по всей моей рабочей книге. Есть 4 категории, по которым таблица будет регулярно фильтроваться и использоваться пользователями, которые не сильны в Excel.

Я хотел бы настроить раздел фильтрации со списками, которые фильтруют таблицу ниже. На снимке экрана показана предлагаемая настройка со значениями списка в ячейках B4, C4, D4, E4и таблица ниже, которая будет отфильтрована на основе введенных значений.

Кто-нибудь знает, как это сделать или что-то подобное?

^{(Нажмите на изображение, чтобы увеличить)}

Answer 1

Вы имеете в виду, что хотите фильтровать по конкретному значению ячейки, а не фильтровать с помощью встроенной фильтрации таблицы?

Что ж, вы можете вызвать автофильтр с помощью макроса и использовать значения ячеек в качестве критериев.

Вы можете поместить макрос на лист и автоматически вызывать его каждый раз, когда одна из соответствующих ячеек изменяется с помощью подпрограммы Worksheet_Change.

Есть много способов записать это, но нам нужно указать, какую таблицу мы хотим фильтровать, а также указать, какую строку фильтровать.

Вот код, который я вставил в лист (щелкнув правой кнопкой мыши по вкладке листа и выбрав «Показать код») для моего примера:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tabl As String, i As Long, C as Range
tabl = "Table1" 'Name of the Table
If Not Application.Intersect(Range(Target.Address), Range("B4:E4")) Is Nothing And Target.Count < 5 Then
    For Each C In Target
        tCol = Range(tabl & "[" & C.Offset(-1).Value2 & "]").Column
        If C.Value2 = "" Then
            ListObjects(tabl).Range.AutoFilter Field:=tCol
        Else
            ListObjects(tabl).Range.AutoFilter Field:=tCol, Criteria1:=C.Value2
        End If
    Next C
End If
End Sub

Результат:

Редактировать
Добавлен цикл для возможности очистки всех фильтров одновременно.

Этот код требует, чтобы заголовки во входной ячейке и в таблице были одинаковыми, в противном случае возникнет ошибка.

-

Worksheet_Changeвызывается при изменении ячеек на листе.
Это часто используется в сочетании с
If Not Application.Intersect(Range(Target.Address), Range("")) Is Nothing Then
для ограничения выполнения подпрограммы, если не указаны конкретные области.

Наличие ограничения Target.Countтакже является хорошей идеей, чтобы код не зависал. Часто вы хотите ограничить его одной целью, если только вы не собираетесь зацикливать выборку, как я сделал в этом случае.

Если вам нужны раскрывающиеся списки с вариантами выбора, вот подход на VBA:excel-удаление-дубликатов-с-проверкой-данных

Answer 2

Я хотел бы предложить формулу массива (CSE), которая поможет вам извлечь несколько строк на основе нескольких критериев.

Как это работает:

• Я предполагаю, что исходные данные находятся в диапазоне A2:E10.
• Диапазон критериев: A16:E16.
• Введите эту формулу в ячейку A20, закончите с помощью Ctrl+Shift+Enter, заполните Вправо, затем Вниз.

{=IFERROR(INDEX($A$2:$E$10,SMALL(IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})=COUNTA($A$16:$E$16),MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}

Примечание:

• Если ячейка/ячейки критериев пусты, эта формула вернет все записи.

• Вы можете указать один или несколько критериев для фильтрации связанных записей.

Позвольте мне объяснить механизм Приказа.

• MMULT functionне может работать с булевыми значениями, поэтому для того, чтобы это заработало, формула должна умножить массив на 1.

MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})

становится,

MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})

и возвращается,

{0;0;1;0;0;2;1;1;2;1}

и,

MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16)

становится,

{0;0;1;0;0;2;1;1;2;1}=COUNTA($A$16:$E$16)

становится,

{0;0;1;0;0;2;1;1;2;1}=2

и возвращается,

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

становится,

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

• МАЛЕНЬКИЙФункция возвращает k-е наименьшее число в массиве.

• ИНДЕКСФункция возвращает значение из диапазона ячеек или массива на основе номера строки и столбца.