엑셀에서 특정 조건을 만족하는 문자열만 합치기

전체 목차
001. lookup 함수를 이용한 양도세 구간 구하기
002. 공백 셀 제거 후 정렬
003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)
004. 목록상자, 토글버튼 연동
005. 다른 시트에 있는 특정 데이터를 복사해오기
006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)
007. 상반기/하반기 매출 집계(SUMIF)
008. 콤보박스 활용하여 필터 변경하기

009. 특정 위치에 있는 데이터만 추출하기 (address, indirect)

010. 엑셀에서 숫자를 특정 패턴으로 입력하기, 숫자 건너뛰어 입력하기

011. 엑셀을 이용하여 도수분포표 작성하기

012. 엑셀로 로또 번호 생성기를 만들어보자

 

포스트 목차.

• 01. 문제
• 02. 문제해결 방법
• 03. 예제 및 설명

 

01. 문제

Q 엑셀) 동일값이 여러셀인 경우, 그 동일값들의 특정셀들을 한 셀에 넣고 싶습니다. 엑셀 예시 시트입니다! 주문번호가 지금 11, 222, 3333 ... 이렇게 되어있는데 1,2,3,4,5에 해당하는 상품들을 한 셀에 나열하고 싶습니다. concat으로 하는 것도 한계가 있어서 혹시 수식이 있으시다면 부탁드립니다.!

 

질문을 정리하면, A열 (주문번호)의 정보가 일치하는 행에 대해서 B열(상품)의 내용을 합쳐달라는 것이다.

 

구지 함수로 만든다면 CONCATIF 와 같은 이름을 지을 수 있을 것 같다.

 

 

02. 문제해결 방법

(1) CONCAT 함수

 

CONCAT함수는 여러 범위 및 문자열의 텍스트를 결합하는 함수이다.  기존의 CONCATENATE 함수를 대체한다.

 

함수 구문

CONCAT(text1, [text2], ...)

text1: 열결할 텍스트 항목, 문자열 또는 셀 범위와 같은 문자열 배열

[text2]: 연결할 추가 텍스트항목

*CONCAT 함수는 254개의 인수를 받을 수 있다.

 

인수로 Range를 받을 수 있어서, CONCATENATE함수보다는 편하다.

 

 

(2) 배열 연산의 IF 함수

=IF($A$2:$A$16=D2,$B$2:$B$16,"")

 

위의 그림과 같이 IF 함수에 배열 수식을 넣어서 배열 연산을 할 수 있다. 위 수식을 CTRL+SHIFT+ENTER를 통해 입력하면, 셀D2의 값과 A2:A16에 입력된 값이 같을 때는 B2:B16에 입력된 내용이 인자가되고, 셀D2의 값과 A2:A16에 입력된 값이 일치하지 않을 때는 공백이 인자가 되는 배열이 생성된다. 

 

배열 수식에 대해 자세히 알고 싶으면 다음 포스트를 참고하기 바란다.

 

https://eggdrop.tistory.com/54

엑셀에서 배열 수식 사용하기 (1)

 

https://eggdrop.tistory.com/55

엑셀에서 배열 수식 사용하기 (2)

 

03. 예제 및 설명

이제 위의 두 가지 정보를 이용하여 문제를 해결해보자.

 

셀E2에 위와같은 IF함수를 배열수식으로 입력하면 "사과 1개/" 만 출력된다. 배열의 첫 요소만 E2에 출력된 것이다.

 

사실 위 수식의 값은 다음과 같은 배열이다.

{"사과 1개/", "복숭아 2개/", "", "", "", "", "", "", "", "", "", "", "", "", "" }

이 배열에 있는 값들을 CONCAT 함수를 이용하여 합쳐주자.

 

 

=CONCAT(IF($A$2:$A$16=D2,$B$2:$B$16,""))

 

위의 수식을 입력한 결과는 다음과 같다. (수식을 입력할 때는 Ctrl + Shift + Enter로 입력한다.)

 

위의 수식은 비교할 주문번호와 상품의 범위가 절대주소로 입력되어 있어서 주문번호 2~5에 대해서도 그냥 수식을 복사하면 된다.

 

절대주소 / 상대주소는 다음 포스트를 참고하자.

https://eggdrop.tistory.com/50

엑셀 절대주소 vs 상대주소 (절대주소 활용법)

 

모든 주문번호에 대해 수식을 적용한 결과는 다음과 같다.