☰

목록에서 셀이 어떤 키워드로 시작하는지 확인하고 일치하는 키워드를 반환합니다.

tag-icon • tag-icon microsoft-excel worksheet-function
목록에서 셀이 어떤 키워드로 시작하는지 확인하고 일치하는 키워드를 반환합니다.

제품 목록을 가져와 제품 이름 시작 부분부터 제조업체를 추출하려고 합니다. 모든 제품 이름은 제조업체로 시작됩니다. 일부 품목에는 이름 본문에 다른 제조업체가 포함되어 있기 때문에 이는 복잡합니다. 항목이 무엇으로 시작하는지 확인해야 합니다. 저는 50,000개 이상의 품목과 3,000개 이상의 제조업체를 상대하고 있습니다. 지금까지 내가 가지고 있는 공식은 다음과 같습니다.

=LOOKUP(1,1/(FIND($C$2:$C$5,B2)),$C$2:$C$5)

이것은 때때로 작동하지만 다른 경우에는 작동하지 않습니다. 예를 들어, 아래 워크시트에서 행 2와 3은 정확하지만 행 4는 정확하지 않습니다. A4"Mike's Fun Toys" 셀(셀에 있음)의 결과는  B4"Mike's"여야 하지만 "Fun"으로 표시됩니다.

제조업체가 알파벳 역순으로 나열된 워크시트

(복사하여 붙여넣을 수 있는 텍스트 형식의 데이터는 다음과 같습니다.

+---+---------+-----------------+---------------+
|   |    A    |        B        |       C       |
+---+---------+-----------------+---------------+
| 1 | Formula | Items           | Manufacturers |
+---+---------+-----------------+---------------+
| 2 | Brown   | Brown Cat Toys  | Mike's        |
| 3 | Cat     | Cat Fun Toys    | Fun           |
| 4 | Fun     | Mike's Fun Toys | Cat           |
| 5 |         |                 | Brown         |
+---+---------+-----------------+---------------+

)

C하지만 열 (제조업체) 의 순서를 변경하면 다음과 같습니다  .

제조업체가 (일반) 알파벳 순서로 나열된 워크시트

행 4는 정확하지만(“Mike's”) 행 2는 잘못되었습니다.

A 열 아래에는 공식이 있습니다. 예상 결과:

A2 - Brown
A3 - Cat
A4 - Mike's

Column 순서에 관계없이 수식이 작동하도록 하려면 어떻게 해야 합니까  C?

답변1

RyanMark의 공식을 이해하지 못하는 독자들을 위해 다음과 같이 설명합니다.

  • 특정 제품명에서 각 제조업체 이름의 위치를 ​​찾습니다. 이로 인해
    • 1제품 이름으로 시작하는 제조업체 이름 의 값 (제품 이름이1첫 번째 문자),
    • 제품 이름에 표시되는 다른 제조업체 이름의 경우 더 높은 번호(나중에 더 높은 위치 번호에 표시되기 때문)
    • #VALUE!제품명에 표시되지 않는 제조업체 이름에 대한 오류 코드입니다 .
  • 위의 각 항목을 반전하여(1로 나누기) 결과는 다음과 같습니다.
    • 1제품 이름으로 시작하는 제조업체 이름(우리가 찾고자 하는 이름)
    • 제품 이름에 나타나는 다른 제조업체 이름의 경우 더 낮은 양수(1을 1보다 큰 숫자로 나누면 비율이 1보다 작기 때문)
    • #VALUE!제품명에 표시되지 않는 제조업체 이름에 대한 오류 코드입니다 .
  • 위의 LOOKUP내용을 찾는 데 사용됩니다 .1

예를 들어 셀 A4(셀의 "Mike's Fun Toys"에 해당 B4)의 경우 첫 번째 이미지에서 순서대로 다음을 얻습니다.

  • 1, "Mike's" ( C2)가 "Mike's Fun Toys"로 시작하기 때문에,
  • 8C3, "Mike's Fun Toys"의 8번째 문자에 "Fun"( )이 나타나기 때문입니다 .
  • #VALUE!그리고 #VALUE!, 왜냐하면 “Cat”( C4)과 “Brown”( C5)은 “Mike's Fun Toys”에 나타나지 않기 때문입니다.

1이를 반전시키면 ,  0.125( 1/8)  #VALUE!및 가 됩니다 #VALUE!. 그런 다음 해당 배열에서 을 찾습니다 1. 이 "해야 한다"는 것은 1첫 번째 결과이고 "Mike's"가 Column의 첫 번째 이름이기 때문에  작동합니다 C.

문제는 다음 도움말 페이지에서 확인할 수 있습니다 LOOKUP.

에 대한조회기능이 올바르게 작동하려면 조회되는 데이터를 오름차순으로 정렬해야 합니다.

분명히 1뒤에 오는 것은 0.125오름차순으로 정렬되지 않습니다.

제안된 대로 를 LOOKUP사용하여 이 문제를 해결할 수 있습니다 MATCH. 수식과 동일한 기본 접근 방식을 사용하는(불필요한 반전 없이) 원하는 수식은 다음과 같습니다.

=INDEX($C$2:$C$5, MATCH(1, FIND($C$2:$C$5,$B2), 0))

세 번째 인수는 MATCH"match_type"입니다. 여기 로 설정했습니다 0. 즉, MATCH배열에서 정확히 의 첫 번째 요소를 찾고 1배열이 정렬되었다고 가정하지 않습니다.

배열 수식이므로 입력할 때 Ctrl+ Shift+ 를 눌러야 합니다.Enter

관련 정보