지출이 포함된 Excel 파일이 있고(지출된 금액은 한 열에 있음) 다음 열에는 대부분 여러 단어로 구성된 간단한 설명이 있습니다. 나는 설명을 "단순화"하고 각 설명에 한두 단어를 할당하고 싶습니다. 설명은 옆에 있는 다른 열에 있습니다. 문제는 설명이 "통합"되지 않는다는 것입니다. 예를 들어 "비즈니스 점심", "XXX 레스토랑에서의 비즈니스 저녁 식사", "언론인과의 커피" 등과 같은 문자열이 있을 수 있으며 이러한 설명을 "음식"으로 지정하고 싶습니다. " 라벨. 비슷한 패턴을 따르는 다양한 카테고리도 있습니다.
내 아이디어는 다른 시트에 다른 테이블을 만드는 것이었습니다. 한 열에는 "커피", "점심", "저녁 식사"와 같은 키워드가 있고 그 옆 열에는 할당하고 싶은 레이블인 "음식"이 있습니다. ". 대략적으로 일치하는 vlookup 함수를 사용했지만 잘못된 결과가 반환되었습니다. 어떤 이유로 목록의 단어 순서가 결과에 영향을 미치는 것으로 보이며 부분 일치(문자열의 한 단어에서 정확히 일치)가 있어도 vlookup은 이를 무시하고 다른 것을 반환합니다. 예를 들어 "호텔 xxx 주차"가 있고 테이블에 "주차"-"여행 비용" 쌍이 있으면 vlookup은 "음식" 레이블을 반환합니다.
이 문제를 해결하도록 도와주실 수 있나요? (당신이 제안하고 싶은 다른 접근법이 있습니까?)
답변1
FIND()당신은 및/또는 기능을 원합니다 SEARCH(). 용법:
FIND(find_text, within_text)
두 번째 텍스트 문자열 내에서 첫 번째 텍스트 문자열의 시작 위치를 반환합니다
(위치 1에서 시작).
따라서 FIND("lunch", "lunch with customer")1을 반환하고 FIND("lunch", "business lunch")10을 반환합니다. 첫 번째 문자열이 두 번째 문자열에서 발견되지 않으면 #VALUE!오류 값이 반환됩니다. 대소문자를 구분하지만 그렇지 않다는 점을 제외 SEARCH()하면 같습니다 . 그래서FIND()FIND()SEARCH()
FIND("lunch", "Lunch with customer")반환#VALUE!
하지만
SEARCH("lunch", "Lunch with customer")1을 반환합니다.
SEARCH()대소문자를 구분하지 않는 를 사용한다고 가정하겠습니다 .
다음과 같이 배열을 설정하고 싶을 것입니다:
별도의 시트에 이 작업을 수행하는 것이 더 나을 것입니다. 그것을 부르자 Key-Sheet. 그런 다음 데이터 시트에서 자유 형식 설명이 열 A
(셀에서 시작 A1)에 있는 경우 셀에 다음을 입력합니다 B1.
=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))
Ctrl+ Shift+ 를 눌러 Enter"배열 수식"으로 만듭니다. (수식 입력줄에 중괄호로 표시됩니다.) 설명:
SEARCH('Key-Sheet'!$A$1:$A$7,$A1)– 키 시트 열의 각 키워드(“커피”, “점심”, “저녁” 등)에 대해 데이터 시트의A현재 행 열에 있는 설명에서 검색합니다 (예: “비즈니스 점심”A"). 이렇게 하면정렬{를 포함#VALUE!;10;#VALUE!; … } (7개 요소(이 예에서는), 키워드당 하나씩, 두 번째 요소는 에 있는 “lunch”에 대한 결과를 표시합니다'Key-Sheet'!A2.)IFERROR(…,LEN($A1)+1)–#VALUE!값을 로 바꾸십시오15. 즉 는LEN("business lunch")+1의 유효한 반환 값일 수 없지만SEARCH()(실제로 의 가능한 유효한 반환 값보다 높음SEARCH()) 유효한 숫자입니다. 이제 우리의 배열은 {15;10;15; … }.MIN(…)– 배열에서 최소값을 추출합니다. 이 예에서는10. 일반적으로 이는 에서 (첫 번째) 성공적인 반환이 됩니다SEARCH().=MATCH(…, …)– 두 번째 매개변수는MATCH()위의 첫 번째 항목과 동일합니다. 그래서 우리는10배열에서 {#VALUE!;10;#VALUE!; … }. 이는 데이터 시트("비즈니스 점심")에 키 시트의 두 번째 행에 있는 "점심"이 포함되어 있다는10사실에 해당하는 2 의 위치를 반환합니다 .A1
B비용 범주를 얻으려면 키 시트 열에 색인을 생성하는 것이 간단합니다 . 셀을 C1로 설정합니다 =OFFSET('Key-Sheet'!$B$1,B1-1,0). (배열 수식일 필요는 없습니다.)
(위에 예시된 대로) 비용 설명에 여러 키워드가 포함된 경우 첫 번째 키워드만 검색됩니다.
중간 값에 신경쓰지 않으려면 다음을 계산하면 됩니다.
=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)
이것하다배열 수식이어야 합니다.
PS FIND()및 SEARCH()함수에는 선택적 세 번째 인수가 있습니다.
SEARCH(find_text, within_text, [start_num])
그래서
SEARCH("cigar", "Sometimes a cigar is just a cigar.")13을 반환
하지만
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17)29를 반환합니다.
나는 당신이 그것을 사용할 이유가 없다고 생각합니다.
답변2
Tyson이 말했듯이 "close/about." 일치는 단어를 의미하지 않습니다. 도움말 파일을 인용하려면:
If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
즉, "1,2,5,8,12"에서 "7" 값을 조회하면 반환되는 값은 "5"가 됩니다. 이는 7보다 크지 않고 7에 가장 가까운 값입니다.
개별 단어와 문법 분석에 대한 광범위한 프로그래밍과 평가 없이는 원하는 것을 쉽게 수행할 수 없습니다.
당신이 해야 할 일은 원래 데이터를 입력할 때 일종의 "범주 코드"를 입력하도록 훈련한 다음 "추가 세부 정보"에 대한 메모 열을 사용하는 것입니다. 그의 생일을 위한 저녁 식사."
이미 많은 양의 데이터가 있고 이를 수행하기 어려울 수 있는 경우 몇 가지 방법을 사용하여 작업 속도를 높일 수 있습니다(물론 여전히 많은 수동 정렬 작업을 수행해야 함).
"park"라는 단어에 대한 설명을 확인하는 열을 추가하여 시작하고, 찾을 수 없으면 0을 반환하고, 찾으면 1을 반환합니다. "=If(Search("park",A1)>1,1,0)"와 같은 것입니다. (그런 다음 데이터의 모든 행에 수식을 자동 복사합니다.) 그런 다음 해당 열을 기준으로 전체 표를 정렬하면 데이터가 두 그룹, 즉 'park'가 포함된 설명과 포함되지 않은 설명으로 분할됩니다. 예를 들어 "음식"이 포함된 열을 추가하세요. 그런 다음 "food"와 "park" 사이에서 (두 열을 사용하여) 4개의 그룹으로 정렬할 수 있습니다. 두 단어 모두 없는 그룹, "food"가 있는 그룹, "park"가 있는 그룹, 둘 다 있는 그룹입니다.
이 작업을 반복적으로 수행하면 명확하게 하나의 카테고리 또는 다른 카테고리인 그룹을 신속하게 분류하고 카테고리 코드로 표시한 다음 모든 것이 분류될 때까지 추가 단어 검색을 수행하는 동안 해당 그룹을 무시할 수 있습니다.