Как я могу классифицировать список?

Question

Я ищу, чтобы классифицировать / сортировать список, сопоставляя определенные строки. Ранее я не мог найти решение, так что надеюсь, что этот путь проще.

Пример списка:

[A]
The apple is the pomaceous fruit of the apple tree
Apples grow on deciduous trees which are large if grown from seed
Apples are an important ingredient in many desserts, such as apple pie
Puréed apples are generally known as apple sauce
A banana is an edible fruit produced by several kinds of large plants
Worldwide, there is no sharp distinction between "bananas" and "plantains"
The term "banana" is also used as the common name for the plants
Orange is the colour of saffron, pumpkins and apricots
The colour orange is named after the appearance of the ripe orange fruit
In ancient Egypt, artists used an orange mineral pigment called realgar
Apple, orange and banana smoothie
Eating an orange and banana exceed allowable sugar intake
Kale or borecole (Brassica oleracea Acephala Group) is a vegetable
Until the end of the Middle Ages, kale was one of the most common green vegetables

Ищите строки и как они будут классифицированы (без учета регистра):

Apple = Apple
Apple Pie = Dessert
Banana = Banana
Orange = Orange
(anything not categorized) = Vegetables
(multiple found strings) = Multiple --> if this isn't possible it's fine

Вот что может сказать столбец рядом со списком:

[B]
Apple
Apple
Pie
Apple
Banana
Banana
Banana
Orange
Orange
Orange
Multiple
Multiple
Vegetables
Vegetables

И тогда я бы просто использовал сортировку / фильтр. Спасибо!

Answer 1

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

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

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

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

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

Answer 2

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

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" ничем, а затем подсчитываете буквы, чтобы увидеть, есть ли их меньше оригинала. Обычно это операция с учетом регистра, но я использовал нижний регистр обоих текстов, которые я сравниваю первым. Это выведет текст результата в каждом столбце, если для этого столбца был найден текст поиска.

Чтобы объединить hiarchy, вы можете изменить столбец B на IF(LEN(C2)> 0, "", NORMAL FORMULA), чтобы в столбце не было указано APPLE, если столбец C уже имеет значение. НОРМАЛЬНАЯ ФОРМУЛА как раз формула для 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))}

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