특정 문자열을 일치시켜 목록을 분류/정렬하려고 합니다. 이전에는 해결책을 찾을 수 없었으므로 이 방법이 더 쉬웠으면 좋겠습니다.
예시 목록:
[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
그런 다음 정렬/필터를 사용합니다. 감사해요!
답변1
파워 쿼리 추가 기능을 사용하여 이 문제를 해결하겠습니다. 거기에 도달하려면 몇 가지 단계가 필요하지만 입력 데이터 구조에 대한 코드나 변경이 필요하지 않습니다.
내 One Drive에서 보거나 다운로드할 수 있는 "파워 쿼리 데모 - 키워드 목록 검색 및 분류"라는 프로토타입을 만들었습니다.
https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398
기본적으로 내 기술은 범주 목록을 로드하고 더미 병합 키를 할당하는 예비 쿼리를 구축한 다음, 더미 병합 키를 사용하여 검색할 텍스트에 대해 이를 병합하는 것이었습니다. 그러면 각 입력 행 x 각 범주에 대한 행이 생성됩니다. 그런 다음 Text.Contains 함수를 사용하여 범주를 계산하고 마지막으로 Group By를 사용하여 원래 행 집합으로 돌아갔습니다.
이 시점에는 피벗 테이블 및/또는 피벗 차트를 필터링하거나 사용하여 탐색하는 데 이상적인 정규화된 테이블이 있습니다.
답변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
당신이 하고 있는 일은 "사과"의 인스턴스를 아무것도 없는 것으로 대체한 다음 글자 수를 세어 원본보다 적은 것이 있는지 확인하는 것입니다. 일반적으로 이는 대소문자를 구분하는 작업이지만 먼저 비교하는 두 텍스트 모두 소문자를 사용했습니다. 해당 열 검색 텍스트에 대한 조회가 있는 경우 각 열의 결과 텍스트가 출력됩니다.
계층 구조를 통합하려면 B의 열을 IF(LEN(C2)>0,"",NORMAL FORMULA)로 변경할 수 있습니다. 그러면 C 열에 이미 값이 있는 경우 열에 APPLE이 표시되지 않습니다. 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))}
사과와 파이가 모두 포함되어 있으므로 "사과 파이"가 있는 모든 항목에 대해 2가 될 수 있으므로 실패하지만 F열에 제공된 동적 목록을 사용할 수 있으므로 승리합니다.