Как я могу отфильтровать данные по элементам в списке?

Question

Как я могу отфильтровать записи, содержащие любое конкретное слово в списке слов? Например, у меня есть список названий дорог в Сингапуре.

Улица Сямэнь, Сингапур
Энн Сианг Хилл
Энсон Роуд
Арабская улица
Армянская улица, Сингапур
BBaghdad Street (Сингапур)
Balestier Road
Улица Банда
Бартли Роуд
Бич Роуд, Сингапур
Bencoolen Street
Бернам стрит
Лодочная набережная
Улица Бун Тат
Boundary Road, Сингапур
Брас Басах Роуд
Улица Бугис
Букит Баток Роуд
Букит Пасох Роуд
Букит Тимах Роуд
CCantonment Road, Сингапур
Чоа Чу Кан Роуд
Кларк Куэй
Клементи Роуд
Клубная улица
Набережная Коллиера
Коннот Драйв
Крейг Роуд (Сингапур)
Cross Street
и многое другое

Моя электронная таблица содержит большое количество записей, таких как следующие, которые могут содержать или не содержать названия дорог, упомянутые в моем списке:

1. Увидел несчастный случай на Томсон-роуд
2. Нашел это случайно
3. 6 автомобилей разбились на Balestier Road
4. Я хочу потерпеть крах сейчас. Очень устал.
5. Автобус сталкивается с велосипедом на арабской улице.
6. Авария на городской дороге.
7. Вы можете разбить мой дом позже.

Как мне отфильтровать, чтобы получить записи, которые содержат любое название дороги, указанное в списке названий? Как ввести массив / список названий дорог в Microsoft Excel, а затем связать его с функцией фильтра?

Answer 1

Прежде всего, если вы хотите сопоставить предложения, содержащие «Amoy Street», вам нужно удалить «, Сингапур» из этой первой записи (и другие, которые его содержат, а также «(Сингапур)»).  Возможно, вы захотите сделать это с помощью формулы в вспомогательном столбце, поэтому давайте предположим, что ваш свернутый список названий дорог находится в ячейках с B1 по B42 .  И предположим, что ваши семь примеров предложений (или семнадцать тысяч, или сколько у вас есть) находятся в столбце C  Тип

=AND(ISERROR(SEARCH(B$1:B$42, C1)))

в ячейку D1 и введите Ctrl+Shift+Enter.  Это вводит формулу в виде формулы массива, заставляя ее появляться в фигурных скобках:{=AND(ISERROR(SEARCH(A$1:A$3, B1)))} .  Перетащите / заполните, чтобы покрыть столбец C Это приведет к значению FALSE для каждого предложения (значение C ), которое содержит строку из B1:B42 и TRUE для каждого, которое этого не делает.  Вы можете отфильтровать это.  Если вам не нравится тот факт, что эти значения «задом наперед», просто оберните выражение в NOT( ):

=NOT(AND(ISERROR(SEARCH(B$1:B$42, C1))))

Это без учета регистра.  Если вы хотите, чтобы он учитывал регистр, замените SEARCH на FIND .

Это решение находит подстроки.  Учет чувствительности к регистру уменьшает вероятность ложных срабатываний, таких как «Banson Road» и «Uboat Quay».  Если имя дороги никогда не появится в начале предложения, вы можете исключить ложные срабатывания этих подстрок, добавив пробел к каждому названию дороги в столбце B Если название дороги может появиться в начале предложения, сделайте это и измените формулу на

=AND(ISERROR(SEARCH(B$1:B$42, " " & C1)))

Обрабатывать «Мистер Коннот водит Мерседес », делайте то же самое, но добавляйте пробелы.  Это по-прежнему не касается «Мистер Коннот водит Мерседес?», Так что вы можете выбрать вариант с учетом регистра.

Я проверил это решение для небольшого количества строк.  Возможно, что он потерпит неудачу, если список дорог будет очень длинным.

Answer 2

Использовать автофильтр с опцией Contains ... Это позволит вам просматривать элементы, которые содержат заданную текстовую строку внутри них. Строки, не содержащие строку, будут скрыты.

Ссылка

РЕДАКТИРОВАНИЕ № 1 :

Как отметил Джеймс Дженкинс, использование автофильтра с Contains ... не позволит применять длинный список включенных подстрок одновременно ......................................................................... здесь представлен альтернативный подход на основе VBA.

Допустим, у нас есть рабочая тетрадь с двумя вкладками "Дороги" и "Предметы". Список дорог находится на вкладке "дороги", начиная с A2. Список элементов, подлежащих фильтрации, находится на вкладке "Элементы", начиная с A2.

Этот макрос сначала читает список дорог в массив. Массив применяется к каждому элементу в списке элементов. Каждая строка элемента либо скрыта, либо отображается.

Sub FilterByList()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("roads")
    Set s2 = Sheets("items")
    Dim N1 As Long, N2 As Long, L1 As Long, L2 As Long
    Dim r1 As Range, r2 As Range

    N1 = s1.Cells(Rows.Count, "A").End(xlUp).Row
    N2 = s2.Cells(Rows.Count, "A").End(xlUp).Row
    ReDim rds(1 To N1 - 1) As String
    For L = 2 To N1
        rds(L - 1) = s1.Cells(L, 1)
    Next L

    s2.Cells.EntireRow.Hidden = False
    For L2 = 2 To N2
        Set r2 = s2.Cells(L2, "A")
        v2 = r2.Value
        r2.EntireRow.Hidden = True
        For L1 = 2 To N1
            If InStr(1, v2, rds(L1 - 1)) > 0 Then
                r2.EntireRow.Hidden = False
            End If
        Next L1
    Next L2
End Sub

Answer 3

Вы можете использовать Расширенный фильтр. Посмотрите на пост ниже:

https://wmfexcel.com/2017/08/02/filter-a-lists-of-items-from-a-long-long-list/