Как классифицировать список?

Question 1

Я бы решил эту проблему с помощью надстройки Power Query. Для этого нужно выполнить несколько шагов, но не требуется никакого кода или изменений в структуре входных данных.

Я создал прототип, который вы можете просмотреть или загрузить — это «Демонстрация Power Query — поиск по списку ключевых слов и категоризация» в моем One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

По сути, мой метод заключался в создании предварительного запроса для загрузки списка категорий и назначении фиктивного ключа слияния, затем Merge его с текстом для поиска с использованием фиктивного ключа слияния. Это создает строку для каждой входной строки x каждой категории. Затем я вычислил категорию с помощью функции Text.Contains и, наконец, использовал Group By для возврата к исходному набору строк.

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

Answer

Я бы решил эту проблему с помощью надстройки Power Query. Для этого нужно выполнить несколько шагов, но не требуется никакого кода или изменений в структуре входных данных.

Я создал прототип, который вы можете просмотреть или загрузить — это «Демонстрация Power Query — поиск по списку ключевых слов и категоризация» в моем One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

По сути, мой метод заключался в создании предварительного запроса для загрузки списка категорий и назначении фиктивного ключа слияния, затем Merge его с текстом для поиска с использованием фиктивного ключа слияния. Это создает строку для каждой входной строки x каждой категории. Затем я вычислил категорию с помощью функции Text.Contains и, наконец, использовал Group By для возврата к исходному набору строк.

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

Question 2

Тот факт, что вы хотите, чтобы он был динамическим И иерархическим (apple pie имеет приоритет над apple), немного усложняет задачу, но если вы готовы запрограммировать его статически, вы можете сделать что-то вроде этого:

Row 1 - Your search text
Row 2 - Your result text

B1=Apple
B2=Apple
B3=If(Len($A2)>LEN(SUBTITUTE(LOWER($A2),LOWER(B$1),"")),B$2,"")
C1=Apple Pie
C2=Deserts
D1=Orange
D2=Orange
Drag B3 across and down

То, что вы делаете, это заменяете экземпляры "apple" ничем, а затем подсчитываете буквы, чтобы увидеть, меньше ли их, чем в оригинале. Обычно это чувствительная к регистру операция, но я использовал строчные буквы в обоих текстах, которые я сравниваю первыми. Это выведет текст результата в каждом столбце, если был результат для текста поиска этого столбца.

Чтобы включить иерархию, можно изменить столбец B на IF(LEN(C2)>0,"",NORMAL FORMULA), так что столбец не будет содержать APPLE, если столбец C уже имеет значение. NORMAL FORMULA — это просто формула для B3, указанная выше.

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

=IF(COUNTA(B2:D2)=0,"Vegitables",IF(COUNTA(B2:D2)>1,"Multiple",B2&C2&D2))

Если совпадений 0, то овощи, если несколько, то несколько, в противном случае у вас будет заполнено только одно поле, поэтому вы сможете получить окончательный ответ, объединив результаты.

Также я придумал формулу, которая просто берет динамический список и вычисляет, сколько у вас совпадений. Это функция массива, поэтому вам придется ввести ее без {}, а затем вместо нажатия Enter нажать ctrl+shift+enter

{=SUM(--(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($F$1:$F$6),""))>0))}

Однако этот вариант не срабатывает, поскольку он мог бы сработать для всего, что содержит слово «яблочный пирог», поскольку в нем есть и яблоко, и пирог, но он выигрывает, поскольку может работать с динамическим списком, представленным в столбце F.

Answer