카테고리 색인에서 일치하는 부분 텍스트를 기준으로 Google 스프레드 시트의 텍스트를 분류합니다.

Nov 25 2020

VLOOKUP을 사용하여 기본 시트의 열에있는 단어의 일부를 다른 시트에 쉽게 저장할 수 있도록 키워드 / 카테고리의 인덱스에 일치 시키려고합니다.

다음은 워크 시트의 예입니다. https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

비용 시트에는 수신 된 데이터가 A ~ C 열에 있습니다.
D 열에서 이러한 비용에 범주를 자동 할당하려고합니다. 예를 들어 B 열의 정확한 텍스트로이 작업을 수행하면 BANK VISA PREAUTH PYMT쉽습니다. D 열의 VLOOKUP 수식은 '카테고리'탭에서이 정확한 참조를 가져 오는 방식으로 작동합니다 (셀 A1은 정확히 이런 식으로 표현됨). 이 공식은 다음과 같이 작동합니다.=IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
문제는 비슷한 유형의 비용이 항상 정확한 텍스트를 따르지는 않는다는 것입니다. 예 : SEND E-TFR US***ke5및 SEND E-TFR US***xja. 따라서 텍스트 만 일치시키고 싶습니다 SEND E-TFR.

이는 VLOOKUP이 '카테고리'탭 첫 번째 열의 부분과 일치해야 함을 의미합니다. 이를 달성하기 위해 VLOOKUP 공식을 어떻게 조정합니까?

추신. 내 질문은 부분 문자열을 일치시키고 싶다는 중요한 구별을 가진 이것과 매우 유사합니다.
Google 스프레드 시트에서 셀 분류

편집 : 답변 됨.

답변

2 Rubén Nov 25 2020 at 11:31

이 경우 VLOOKUP일부 카테고리 kyes가 일치하는 데이터 내에 포함되어 있기 때문에 사용할 수 없습니다. 반면에 케이스는 몇 가지 카테고리가 있으므로 질문 본문에 포함 된 설명보다 더 복잡합니다. 둘 사이의 부분 일치 (예 : BANK VISA PREAUTH PYMT및 Bank Visa)와 일치가 대소 문자를 구분하지 않아야하기 때문에 ( BANK MUTUAL FUNDS로 분류되어야 함 Mutual Funds)

제안 된 공식은 다음과 같습니다.

=ArrayFormula(ARRAY_CONSTRAIN(
SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE({
IF(
TRIM(B2:B9)=TRIM(TRANSPOSE(Categories!A1:A3)),Categories!B1:B3&"|",),
TRANSPOSE(IF(IFERROR(SEARCH(TRIM(Categories!A4:A8),TRANSPOSE(TRIM(B2:B9))),),Categories!B4:B8&"|",))
}),,1000000))),"|"),
COUNTA(B2:B9),1))

B2:B9 카테고리 키와 비교할 값이있는 셀입니다.

참고 :이 범위에는 빈 셀이 없다고 가정합니다.
Categories!A1:A3전체 일치를 수행해야하는 키를 포함하는 셀이며 Categories!B1:B3해당 범주입니다.
Categories!A4:A8부분 일치를 수행해야하는 키를 포함하는 셀이며 Categories!B4:B8해당 범주입니다.

2 PKHunter Nov 26 2020 at 01:35

답변에 대한 @Ruben에게 감사드립니다. 결국 우리는 더 간단한 접근 방식을 취하기로 결정했습니다. 대신 VLOOKUP에서 검색 문자열의 정규 표현식을 확인하고 카테고리 탭의 키 인덱스에 해당 하위 문자열을 포함하십시오.

따라서 비용의 B1 셀에 다음 텍스트가있는 경우 :

SEND E-TFR US***ke5
SEND E-TFR US***abc
SEND E-TFR US***xyz
...

정확히 일치하는 문제를 일으키는 원인이되었으므로 두 번째 공백 이전에 발생하는 부분 문자열로 문자열을 자릅니다.

REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")

우리에게 이것을주기 위해 :

SEND E-TFR

그리고 새로 워진 VLOOKUP 기능 :

=IFERROR(VLOOKUP(TRIM(REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")),Categories!A:B,2,FALSE), "")

그런 다음 카테고리 키 색인은 조회를 위해 잘린 텍스트를 참조합니다.

따라서 올바른 카테고리와 일치하고 채워집니다.

이 작업을 확인하는 테스트 파일은 다음과 같습니다. https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing