엑셀 동일한 일치하는 값의 항목들 한 셀에 넣기

아래 그림과 같이 동일한 그룹으로 분류되는 값들을 한 셀에 모아 정리하는 방법입니다.

고급 필터를 이용해 중복 그룹 제거 후 목록 만들기

아래와 같이 Benz, BMW, Audi, Porshe 4개 독일 자동차 제작사의 차명으로 분류된 독일 중고차 그룹이 있다고 하면

우선 [고급필터]기능을 이용하여 중복항목을 제거하여 그룹을 분류해 목록을 만듭니다. 

 

[데이터]메뉴의 [정렬 및 필터] 항목에서 [고급] 선택하여 [고급 필터]창을 엽니다.

[고급 필터] 창에서
새로 표를 만드는 작업을 하므로 
[다른 장소에 복사(O)]를 선택,
[목록 범위(L)]은 [Class]열을 선택,
[복사 위치(T)]는 새로 표를 만들 셀 선택,
중복을 제거해야 하니깐 [동일한 레코드는 하나만(R)] 체크 후
[확인]을 하면 새로 그룹 목록이 만들어 집니다.

동일한 일치하는 값을 한 셀에 넣는 함수 만들기

Excel창에서 [Alt+F11]키를 눌러 Visual Basic 편집기 창을 엽니다. 

메뉴에서 [삽입] -> [모듈(M)] 을 차례로 선택 [Module(코드)] 입력창을 엽니다. 

[Module(코드)]입력창]에 아래의 내용을 복사해서 붙여 넣기 하거나 블로그에서 바로 복사가 안되면 TXT파일을 다운 받아 복사하여 붙여 넣기를 합니다. 

Function Lookup_Incell(lookup_value As String, table_array As Range, del As String)

Dim i As Integer, str_sum As String

For i = 1 To table_array.Rows.Count
    If table_array.Cells(i, 1) = lookup_value Then str_sum = str_sum & table_array.Cells(i, 2) & del
Next i

If str_sum <> "" Then
    Lookup_Incell = Left(str_sum, Len(str_sum) - Len(del))
Else
    Lookup_Incell = ""
    
End If

End Function

lookup_Incell.txt

0.00MB

위의 사진처럼 붙여 넣기를 했으면 Visual Basic 창을 닫고 Excel 창으로 돌아갑니다.

동일한 일치하는 값을 한셀에 넣는 함수 사용하기

함수를 사용하는 방법은 일반적인 엑셀에서 사용하는 다른 함수와 같은데 위의 코드 창의 첫 줄을 살펴보면 함수 이름을 lookup_InCell 정의해 주었기 때문에 =look 정도로 검색만 해도 엑셀에서 원래 존재하던 함수처럼 Vlookup_InCell 함수가 보입니다. 

Function Lookup_Incell(lookup_value As String, table_array As Range, del As String)

lookup_value : 표의 첫 열에서 찾으려는 값
table_array : 데이터를 검색하고 추출하려는 범위
del  : delimiter 구분자

del 구분자는 각 값들을 구분해주는 역할을 하는 것인데 ", " 와 같이 입력하면 각 값들은 [값1, 값2, 값3, 값4]와 같이 구분 됩니다.  
당연히 del 값을 셀로 지정하고 셀 내용을 변경하여 구분자를 쉽게 바꿀 수도 있습니다. 

 

처음으로 돌아가서

아래와 같이 Benz 사의 세부 중고차 명이 들어갈 "E3"셀에서
=lookup_Incell(D3,A3:B23,", ")
와 같이 입력 후 아래로 빈칸 채우기를 하면 한셀에 일치하는 값의 내용이 표시 되는 것을 볼 수 있습니다.

이후 끌어서 채워 넣기를 하면 나머지 Porsche, BMW, Audi 의 세부 중고차 명이 채워집니다. 
Lookup_Incell 함수를 계속 사용하고 싶다면 저장 형식을 .xlsm으로 저장하면 나중에 파일을 불러왔을 때도 사용할 수 있습니다. 

만약 Office 365 이상 버전을 사용하고 있다면 사용자 정의 함수를 사용하지 자체적으로 지원하는 함수인 TEXTJOIN 함수를 이용하면 보다 간단하게 위와 같은 작업을 할 수 있습니다. 

엑셀 조건을 만족하는 텍스트 합치기 함수 TEXTJOIN 사용방법