[엑셀실무] 여러 행의 내용을 한 개의 행으로 모으기 함수

실무를 하다 보면 아래와 같이 자료를 정리해야할 필요가 생긴다.

보통 왼쪽의 데이터는 ERP에서 얻어지는 자료 형태이고, 

오른쪽의 데이터는 보고서를 작성하기 위해 만들어야 하는 자료 형태이다.

 

언제나 반복적인 업무가 생기면, 이걸 개선할 수 없을까 고민을 해봐야 한다.

수식은 아래와 같다.

=TEXTJOIN(", ",TRUE,OFFSET($D$1,MATCH(J5,C:C,0)-1,0,COUNTIF(C:C,J5),1))

 

함수 설명을 하자면,

TEXTJOIN(a,b,c,d) : c~d의 텍스트들을 a의 구분자를 중간에 삽입해가면서 합친다. b는 중간에 빈 칸이 있으면 포함할까, 말까의 옵션이다.

쉽게 보면 아래와 같다.

MATCH(a,b,0) : a값을 b 범위에서 찾아서 몇 번째에 있는지 알려준다.

아래에서 D25:28범위에 '현대'가 두 번째에 있기 때문에 2값을 보여준다.

OFFSET(a,b,c,d,e) : 엑셀 중수 이상으로 올라가기 위한 함수라고 생각한다.

기준점 a에서 시작해서 b행,c열만큼 떨어진 셀부터 d행,e열만큼 범위를 잡는다.

아래에서는 B4부터 시작해서 3x1만큼 떨어진 C7에서 2x2의 범위를 가져왔다.

이제 다시 처음으로 돌아가서,

오른쪽 '품목'을 왼쪽에서 찾아서, 그 찾아진 셀부터 동일한 품목이 몇 개나 있는지 확인해서 그 숫자만큼 '고객사'의 범위를 잡고 TEXTJOIN 함수로 합쳐주면 된다.

위 식을 풀어 말하자면, 'KO1745' 품목(J5)을 C열에서 찾았더니(MATCH) 5번째 행에 있고, 그 품목이 3개 있어서(COUNTIF), 고객사들이 있는 열, $D$1부터 아래로 5 x 0 만큼 이동하고 그 셀부터 3 x 1만큼 범위로 잡아서(OFFSET), 콤마로 구분해서 합쳐줬다.(TEXTJOIN)
(설명 해놓고 보니 정말 설명 잘 한거 같다. ^O^)

 

예제 파일 첨부한다.

 

 

직장인을 위한 실무 엑셀:실전! 비즈니스 엑셀 완전 정복

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받을 수 있습니다.

엑셀_예제2.xlsx

0.03MB