같은 내용을 묶어서 합치기(피벗 테이블, MATCH, TEXTJOIN)

안녕하세요.

질문을 받았습니다.

출판사별 분야 목록을 나타내고 싶다는 것이었습니다.

원하는 결과 모습입니다.

몇가지 방법으로 구해 보겠습니다.

실습 파일 다운로드

같은 내용을 묶어서 합치기_실습.xlsx

0.01MB

완성 파일 다운로드

같은 내용을 묶어서 합치기_완성.xlsx

0.02MB

피벗 테이블 이용

첫번째 방법으로 딱 떠오른 방법이 피벗 테이블을 이용하는 방법입니다.

출판사 항목과 분야 항목을 조건으로 각각 행과 열에 두고 분야 항목을 값에 넣어 결과를 표시하려고 합니다.

[A1] 셀에 커서를 두고 [삽입] - [피벗 테이블]을 선택합니다.

[피벗 테이블 만들기] 대화상자에서 [확인]을 누릅니다.

오른쪽 [피벗 테이블 필드] 창에서 항목을 끌어 집어 넣으면 결과가 나타납니다.

'출판사' 필드를 끌어 [행]에 놓고, '분야' 필드를 끌어 [열]에 놓습니다.

그리고 다시 '분야' 필드를 끌어 [값]에 놓습니다.

출판사별 분야를 확인할 수 있는 피벗 테이블이 완성되었습니다.

원래 생각했던 결과와 모양이 많이 다르기는 하지만 어떤 출판사에서 어떤 분야의 도서를 출판했는지 확인하는 것에는 어려움이 없습니다.

그리고 꼭 원래 모양으로 나타나게 하려면 이 결과를 보고 문서를 조금 수정하면 됩니다.

MATCH 함수 이용

두번째 방법은 MATCH 함수를 이용해 결과를 구하려고 합니다.

시트 탭에서 'Sheet1'을 선택합니다.

먼저 출판사 필드에서 중복된 항목은 제외하고 유일한 목록만 나타내는 작업을 하겠습니다.

[A1:A9] 셀 범위를 선택하고 복사한 뒤 [A12] 셀에 붙여넣기 합니다.

[데이터] - [중복된 항목 제거]를 선택합니다.

[중복 값 제거] 대화상자에서 [확인]을 선택합니다.

중복된 값이 제거되고 고유한 값만 남았습니다.

'분야' 필드는 제목을 제외한 [C2:C9] 셀 범위를 선택하고, 복사한 뒤 [H12] 셀에 붙여넣기 합니다.

[데이터] - [중복된 항목 제거]를 선택합니다.

[중복 값 제거] 대화상자에서 [확인]을 누릅니다.

중복된 값이 제거되고 고유한 값만 남았습니다.

[H12:H15] 셀 범위를 선택하고, 복사합니다.

[B12] 셀을 선택한 다음 마우스 오른쪽 단추를 눌러 [선택하여 붙여넣기]를 누릅니다.

[선택하여 붙여넣기] 대화상자에서 [행/열 바꿈]에 체크한 다음 [확인]을 누릅니다.

이제 수식을 작성할 틀이 만들어졌습니다.

[B13] 셀에 배열 수식을 작성하고 [B13:E16] 셀 범위까지 채워서 값을 구할 예정입니다.

그래서 수식을 작성할 때 절대 참조와 혼합 참조를 적절히 적용해 작성하겠습니다.

[B13] 셀에 커서를 두고 =if 를 입력하고 나타난 함수 목록에서 IF 함수를 더블클릭합니다.

단축키 <Shift + F3>키나 수식 입력줄의 [함수 삽입]을 눌러 [함수 인수] 대화상자를 엽니다.

[함수 인수] 대화상자를 이용하면 중첩되는 함수를 작성할 때 헷갈리지 않아 도움이 됩니다.

IF 함수 Logical_test 인수에 커서를 두고 match()를 입력합니다.

수식 입력줄의 match()를 마우스로 클릭합니다.

그러면 열려 있는 [함수 인수] 대화상자가 IF 함수 인수창에서 MATCH 함수 인수창으로 바뀝니다.

MATCH 함수 Lookup_value 인수에 커서를 두고 수식을 작성합니다.

Lookup_value 인수에는 1을 입력합니다.

Lookup_array 인수에는 ($A13=$A$2:$A$9)*(B$12=$C$2:$C$9)를 입력합니다.

혼합 참조와 절대 참조가 쓰인 부분을 주의해서 입력해야 합니다.

Match_type은 0을 입력합니다.

[A2:A9] 셀 범위에서 [A13] 셀의 출판사명과 같은 항목을 찾습니다.

마찬가지로 [C2:C9] 셀 범위에서 [B12] 셀의 분야와 같은 항목을 찾습니다.

둘 다 같으면 1이 되고, 둘 다 틀리거나 하나만 맞는 경우는 0이 됩니다.

수식 입력줄에 있는 수식 중 IF 함수 글자를 마우스로 클릭합니다.

그러면 [함수 인수] 대화상자가 IF 함수 인수 창으로 전환됩니다.

Value_if_true 인수에 "O"(대문자 o)를 나타내고, Value_if_false 인수에는 ""를 나타냅니다.

배열 수식이기 때문에 <Ctrl + Shift>키를 누른 상태에서 [확인]을 누릅니다.

[B13]  셀 수식을 [B16] 셀까지 채우고, 다시 [E16] 셀까지 채웁니다.

'#N/A' 오류메시지가 나타나는 이유는 해당 셀에 조건에 맞는 값이 없기 때문입니다.

IFERROR 함수를 이용해 아무런 표시 나지 않도록 처리할 수 있습니다.

여기서는 수식이 너무 복잡해 지기 때문에 IFERROR 함수는 쓰지 않겠습니다.

 

MATCH 함수를 이용해 나온 결과는 앞에서 피벗 테이블로 만든 결과와 같은 모양입니다.

 

TEXTJOIN 함수 이용

TEXTJOIN 함수는 2016/2019 이상 버전에 있는 함수 입니다.

UNIQUE 함수와 함께 배열 함수로 이 문제를 해결하는데 도움이 됩니다.

앞의 작업을 유지하고 새로 함수를 이용해 문제를 풀기 위해 시트를 복사하겠습니다.

시트 탭에서 Sheet1 이름 위에 커서를 두고 마우스 오른쪽 단추를 눌러 [이동/복사]를 선택합니다.

'(끝으로 이동)'을 선택하고 [복사본 만들기]를 체크한 뒤 [확인]을 누릅니다.

[12:16] 행까지 범위선택해 마우스 오른쪽 단추를 눌러 [삭제] 합니다.

[A12] 셀에 커서를 두고 수식을 작성합니다.

=UNIQUE(A1:A9)

함수 목록에 UNIQUE 함수가 보이지 않는다면 현재 사용하는 엑셀 버전에서 UNIQUE 함수를 지원하지 않기 때문입니다.

그렇다면 이 부분을 따라서 하실 수 없습니다.

[B12] 셀에 '분야'라고 입력합니다.

[B13] 셀에 커서를 두고 =te 를 입력하면 나타나는 함수 목록 중 TEXTJOIN 함수를 더블클릭합니다.

단축키 <Shift + F3>키를 누르거나 수식 입력줄 [함수 삽입]을 누릅니다.

Delimiter 인수에 ", "를 입력합니다.

Ignore_empty 인수에 TRUE를 입력합니다.

Text1 인수에 if()를 입력합니다.

수식 입력줄에서 IF 함수 글자를 클릭합니다.

Logical_test 인수에 $A$2:$A$9=A13을 입력합니다.

Value_if_true 인수에 $C$2:$C$9를 입력합니다.

Value_if_false 인수에 ""를 입력합니다.

배열 수식이기 때문에 <Ctrl + Shift>키를 누른 상태에서 [확인]을 누릅니다.

[B13] 셀 수식을 [B13:B16] 셀 범위까지 채우기 합니다.

금방 결과가 구해졌죠?

그러나 이 결과도 완전하지는 않습니다.

[B13] 셀에 '웹서비스'가 두 번 표시되고, [B15] 셀에도 '프로그래밍'이 두 번 반복되어 나타나 있습니다.

수식 상태에서는 필요없는 부분만 따로 지울 수 없으므로 복사한 뒤 [선택하여 붙여넣기] - [값]을 선택해 붙여넣고 필요없는 부분을 지우면 완성됩니다.

 

VBA를 이용해 구하는 방법도 소개합니다.

 

2020.06.25 - 같은 내용을 묶어서 합치기(VBA)

같은 내용을 묶어서 합치기(VBA)

 

2020.06.25 - 같은 내용을 묶어서 합치기2(배열)

같은 내용을 묶어서 합치기2(배열)

 

2020.06.26 - 같은 내용을 묶어서 합치기3(중복된 항목 제거, 정렬, VBA)

같은 내용을 묶어서 합치기3(중복된 항목 제거, 정렬, VBA)

 

필요하신 분들께 도움되길 바라며 마치겠습니다.

수고하셨습니다.