Как создать умный фильтр в Excel для значений, разделённых запятыми?

Как создать библиографическую базу данных в Excel и фильтровать публикации по нескольким авторам

Ведение библиографической базы данных — важная задача для многих исследователей и студентов. Часто список ссылок формируется в текстовом редакторе, например, Word, а затем требуется перенести его в Excel для удобного анализа и фильтрации, особенно если публикации имеют несколько авторов. В этой статье рассмотрим, как правильно структурировать данные об авторах в Excel и организовать фильтрацию по нескольким именам, вне зависимости от порядка их появления в списке.

Структура исходных данных в Excel

Представим, что у вас есть таблица с тремя столбцами: ID статьи, название статьи и перечень авторов через запятую. Пример:

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

Задача: фильтрация по нескольким авторам

Важной задачей является фильтровать список таким образом, чтобы отображать все статьи, где определённый автор присутствует, независимо от порядка авторов или позиционного размещения в списке. Например, вывести все статьи, в которых участвуют Sue или Chris, даже если они не указаны первыми.

Способы фильтрации по нескольким авторам в Excel (MS 365)

Метод 1: Использование функций FILTER, BYROW и TEXTSPLIT

Формула, позволяющая отфильтровать строки таблицы по заданным авторам из списка, выглядит так:

excel
=VSTACK(A1:C1,
FILTER(A2:C13,
BYROW(C2:C13,
LAMBDA(x,
OR(TEXTSPLIT(x, ", ")={"Sue";"Chris"})
)
)
)
)

• TEXTSPLIT разбивает строку с авторами на массив отдельных имён.
• BYROW проходит строку за строкой, проверяя наличие каждого из заданных авторов (Sue или Chris).
• FILTER возвращает только те записи, в которых встречается хотя бы один из нужных авторов.
• VSTACK объединяет заголовки таблицы с результатами фильтра.

Метод 2: Использование функции FIND с обработкой ошибок и фильтрацией

excel
=FILTER(A2:C13,
BYROW(1-ISERR(FIND(", "&{"Sue","Chris"}&",",", "&C2:C13&",")),
OR)
)

Здесь:

• Каждое имя окружено запятыми для точного поиска (чтобы избежать частичных совпадений).
• FIND ищет указанных авторов.
• ISERR преобразует ошибки в логические значения.
• BYROW проверяет наличие авторов по каждой строке.
• Используется логическое OR, чтобы указать, что достаточно одного совпадения.

Метод 3: Использование MMULT для подсчёта совпадений

excel
=FILTER(A2:C13,
MMULT(1-ISERR(SEARCH(", "&{"Sue","Chris"}&",",", "&C2:C13&",")), {1;1})
)

• SEARCH по аналогии с FIND ищет имена.
• MMULT суммирует результат по аргументам.
• Если сумма больше 0 — строка содержит хотя бы одного из авторов.

Метод 4: Фильтрация с помощью регулярных выражений REGEXTEST

excel
=FILTER(A2:C13,
BYROW(REGEXTEST(", "&C2:C13&",", ", "&{"Sue","Chris"}&",", 1),
OR)
)

• REGEXTEST использует регулярные выражения для точного поиска имен.
• Позволяет избежать ложных срабатываний при частичных совпадениях (например, «Ken» и «Kendra»).

Более продвинутый вариант с динамическим списком авторов

Если у вас есть таблица Excel с названием AuthTbl, где столбец авторов содержит список всех авторов, и отдельный диапазон с именами, по которым нужно фильтровать (например, F1:F10), можно использовать формулу с LET и REGEX:

excel
=LET(
authors, F1:F10,
expr, "\b(?:" & TEXTJOIN("|", , authors) & ")\b",
reg, REGEXTEST(AuthTbl[Authors], expr, 1),
f, FILTER(AuthTbl, reg),
VSTACK(AuthTbl[#Headers], IFERROR(f, ""))
)

• TEXTJOIN соединяет все имена в одно регулярное выражение с границами слов (\b), чтобы исключить частичные совпадения.
• REGEXTEST проверяет каждый элемент столбца.
• Если совпадение найдено — строка добавляется в результат.

Заключение

Создание библиографической базы данных в Excel с возможностью фильтрации по нескольким авторам — задача, которая решается с помощью встроенных функций MS Excel версии 365. Главное — разделить имена авторов на отдельные значения и применять фильтры, использующие поиск по всему списку. Регулярные выражения (REGEX) обеспечивают качественный и точный поиск, особенно при большом количестве данных и имен, которые могут совпадать частично.

Использование данных формул позволит быстро находить все публикации, связанные с конкретными авторами, вне зависимости от их позиции и порядка в списке.

Ключевые слова для SEO

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

Источник