☰

문자열에서 여러 코딩된 값을 찾은 다음 발견된 각 코드에 대한 조회 값을 반환합니다.

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
문자열에서 여러 코딩된 값을 찾은 다음 발견된 각 코드에 대한 조회 값을 반환합니다.

600개의 아이템 코드가 있고 각 코드는 전체 아이템 코드의 속성으로 코딩되어 있습니다. 예를 들어 일반 항목 600, 600BK(검은색 본체), 600BKR(검은색 본체, 빨간색 디테일), 600BKR-YEL(검은색 본체, 빨간색 디테일, 노란색 베이스)이 있을 수 있습니다.

따라서 항목 코드 목록이 있습니다.

600
600BK
600BKR
600BKR-YEL

그런 다음 별도의 워크시트에 각 코드의 조회 목록과 그 의미가 표시됩니다.

BK    Black Body
R     Red Detail
YEL   Yellow Base

조회 시트에 있는 항목 제목의 모든 코드를 찾은 다음 발견된 각 코드에 해당하는 값을 모두 하나의 셀에 반환하는 함수를 작성하고 싶습니다.

600
600BK         Black Body
600BKR        Black Body Red Detail
600BKR-YEL    Black Body Red Detail Yellow Base

이것이 가능한가?

답변1

나는 다음과 같은 일을 할 것입니다. 항목 코드 목록이 Sheet1의 A열, 즉 2-20행에 있고 조회 목록이 Sheet2의 A열과 B열, 2-10행에 있다고 가정합니다.

  =IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,8,3),Sheet2!$A$2:$B$4,2,0),"")

B2 셀에 복사한 다음 Sheet1의 모든 행에 대해 복사했습니다.

조회를 단일 수식에 넣기는 하지만 코드가 많으면 이 접근 방식이 매우 번거로울 수 있습니다. 각 코드 위치(예: mid(A2,4,2))에 대해 Sheet1에 도우미 행을 삽입한 다음 이를 단일 문자열로 연결하는 것이 좋습니다.

답변2

짧은 대답은 "예"입니다. 한 가지 방법은 오히려 더 깁니다.

귀하의 질문에서 제외되지 않은 많은 가능성이 있으므로(예: 3자리 숫자 또는 영숫자보다 긴 코드로 구성된 일반 항목, 일반 항목당 3개 이상의 코드, 일반 항목에 따라 의미가 다른 코드 등) 구문 분석부터 시작하는 것이 가장 안전하다고 제안합니다. 코드. 이렇게 하면 GR이 빨간색 디테일(종종 호환되는 색상 구성표!)이 있는 회색 본체 또는 녹색 본체와 같은 합병증을 피할 수 있습니다.

이것이 구문 분석되었다고 가정하면(Row2에서 시작하는 B:D 세 개의 열) 이후 조회 테이블(별도의 시트에 있는 테이블)을 사용하면 이름이 다음과 같이 쉽습니다 codes.

=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)

품목 코드 시트에 있습니다. 값 사이에 공백을 얻기 위해 코드 배열의 오른쪽 열에 있는 모든 항목이 공백으로 끝나는 것으로 가정했습니다(=A2&" " 복사된 것과 같이 정렬하기가 충분히 쉽습니다).

따라서 어려운 부분은 항목 코드에서 코드를 구문 분석하는 것일 수 있습니다. 이를 위해 아래와 같이 열을 추가하는 것이 좋습니다(코드가 3개 이상인 경우 추가).

SU531526 첫 번째 예

아래와 같은 공식으로:

SU531526 두 번째 예

ColumnB는 코드 검색을 시작할 위치를 정의합니다(일반 코드가 3자리 숫자가 아닌 경우). C:D 열은 다음 코드/다음 코드 길이 검색을 시작할 위치입니다. 나는 우아하지는 않지만 상대적으로 다재다능하다는 데 동의합니다. 오른쪽으로 작업하기 전에 왼쪽에서 구문 분석이 올바른지 확인하세요.

구문 분석이 성공적으로 완료되면 특수 항목/값 복사/붙여넣기(공식을 제거하기 위해)를 제안하고 교체합니다.공백마침표를 사용합니다(더 복잡하게 만들지 않고 조회 수식 오류가 발생하는 것을 방지하기 위해)*. 또한 항목 코드 목록이 ColumnA에 있다고 가정하고 위와 같이 조회 수식을 적용하기 전에 ColumnsB:F를 삭제하고(또는 그에 맞게 참조를 조정) 필요한 경우 더 많은 조회를 추가합니다.

*감독: 조회 테이블의 각 열에 있는 셀에 마침표가 추가되었는지 확인하세요.

(a) 최대 길이 7과 (b) '최악의 경우' 시나리오(즉, 모두 단일 문자)를 허용하도록 c5, c6 및 c7에 대한 열을 추가합니다.

답변3

다음은 검색 기능을 사용하는 버전입니다(대소문자를 구분하지 않는 find 버전). 설정은 다음과 같습니다.

시트 1에서 조회할 코드는 시트 1의 A열에서 시작됩니다. 최종 결과는 B열에 있습니다. C, D열 등은 1행에 가로로 배열된 고유 코드(예: "BK")를 갖습니다. C1, D2 등의 "R". 이를 달성하는 실용적인 방법은 조회 테이블의 코드 목록을 복사하고 선택하여 붙여넣기하여 셀 C1, D1 등에서 가로로 바꾸는 것입니다.

그런 다음 먼저 B2 셀에 다음 수식을 입력합니다.

  =IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")

이 공식을 C열부터 2행에 입력한 코드 열 수까지 복사하세요.

마지막으로 C2 셀에서 2행의 모든 ​​결과를 연결합니다. 즉, 다음 공식

 =D2&" "&E2&" "&F2

등, 행 1의 코드가 있는 모든 열에 대해. 이 단계는 지루하지만 범위의 모든 셀을 연결할 수 있는 다음 VBA 함수로 단축할 수 있습니다.

  Function Concat(useThis As Range, Optional delim As String) As String
     ' this function will concatenate a range of cells and return the result as a single string
     ' useful when you have a large range of cells that you need to concatenate
     ' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

  Dim retVal As String, dlm As String, cell As Range
  retVal = ""
  If delim = Null Then
     dlm = ""
  Else
     dlm = delim
  End If
  For Each cell In useThis
      If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
          retVal = retVal & CStr(cell.Value) & dlm
      End If
  Next
  If dlm <> "" Then
     retVal = Left(retVal, Len(retVal) - Len(dlm))
  End If
  Concat = retVal
  End Function

이 함수를 Developer VBA의 모듈에 삽입하고 복사합니다. 사용법은 간단합니다(예: concat(C1:D1," ")).

이 접근 방식은 모든 2자 코드와 2자 이상의 문자 코드에 없는 경우, 즉 "R" 및 "BR"과 같은 코드 쌍이 없는 경우 모든 1자 코드에 대해 작동합니다.

관련 정보