엑셀 배열수식과 양식컨트롤의 콤보상자를 활용하여 특정 데이터 값만을 추출하는 방법

엑셀 배열수식과 양식컨트롤의 콤보상자를 활용하여 특정 데이터 값 추출하는 방법

이번에 소개해드리는 방법은 출력 양식을 만들어 놓고 배열수식과 콤보박스를 활용하여 필요한 데이터를 이쁘게 추출해 원하는 엑셀 문서 양식을 만들고 싶을때 쓰는 방법입니다.

아래 소개해드리는 예제에서는 굳이 배열수식을 쓰지 않고도 필터를 활용하면 데이터를 추출할 수 있을 정도로 간단한 양식입니다. 

하지만 동일한 양식에 해당 데이터들을 나열시키려면 필터링을 통한 데이터 검색으로 반복작업을 할 수 밖에 없답니다.

이해를 돕기 위한 쉬운 예제이니 이해해주시고 보셨으면 해요.

제가 뽑아내려고 하는 데이터는 특정 과일을 선택했을때 이 과일만의 여러가지 데이터들을 뽑아내려고 합니다.

시트1에 기본데이터가 나열되어 있습니다. 그리고 콤보박스를 만들 과일 데이터도 있습니다.

시트2에 콤보박스를 만들고 컨트롤서식으로 들어가 입력범위를 시트1의 과일 전체로 지정합니다.

셀연결과 위치는 나중에 다시 정리할 것이니 크게 신경안쓰셔도 됩니다.

다음에는 원하는 데이터를 추출하기 위한 배열수식을 만들어 보겠습니다. 

=INDEX(Sheet1!$A$1:$E$50,SMALL(IF(Sheet1!$B$1:$B$50=Sheet1!$G$1,ROW(Sheet1!$B$1:$B$50)),ROW()),COLUMN()-9)

A1~E50까지의 데이터중에서 G1의 사과와 같다면 사과에 해당되는 데이터들을 똑같이 나열해주는 수식입니다. 

해당 수식을 그대로 적용시키면 오류를 뿜어냅니다. 하지만 CTRL+SHIFT+ENTER 로 배열수식을 만들어준다면 경우는 달라집니다. 수식의 앞뒤에 중괄호( {} ) 가 삽입되면서 엑셀 배열 수식으로 적용됩니다.

J1에 위 수식을 입력후에 CTRL+SHIFT+ENTER를 입력해서 배열수식을 만들어줍니다.

그다음에 열과 행으로 드래그를 해서 해당 수식을 똑같이 적용시켜주면 됩니다.

사과에 대한 데이터들이 나열되고 있습니다. 

그럼 G1을 바나나로 바꿔볼까요?

바나나에 대한 데이터로 바뀌는걸 볼수가 있습니다.

그럼 이제 준비는 끝났습니다. 

시트2에 양식을 만들어서 과일별 판매현황표를 만들어볼게요

출력할 양식을 만들고 아까 만든 콤보박스를 적당한 위치로 이동합니다.

다음에는 콤보박스와 데이터를 연결해야 하므로 콤보박스의 셀연결을 눌러 시트1의 빈공간을 지정합니다.

저는 시트1의 F1셀을 지정했습니다.

사과를 선택하니 숫자 1이 찍히네요. 바나나를 선택하면 2가 찍힙니다. 즉 배열된 순서의 숫자가 찍히는 것을 확인할 수 있습니다.

우리는 과일명이 나와야 하니 시트1의 G1셀에 VLOOKUP 수식을 이용하여 숫자와 연결된 과일명 데이터를 가지고 오겠습니다.

마지막으로 해당 양식에 배열수식으로 추출한 데이터를 가지고 오는 식만 만들어서 넣어주면 됩니다.

다만, #NUM! 이라는 오류 메세지는 없에야 하므로 (나중에 합계 계산등이 적용이 안됨) 아래 수식을 넣어서 오류는 0값으로 변환하라는 수식을 추가해서 입력합니다.

=IF(ISERROR(Sheet1!J1), 0, Sheet1!J1)

0만 있어도 보기 싫으니까 조건부 서식을 활용해 가려주는것도 한 방법입니다.

이제 완성입니다.

제목과 합계란에도 아래와 같은 수식을 적용시키면 콤보박스에서 과일명을 선택할때 같이 바뀐답니다. 

=Sheet1!$G$1&" 판매현황"

=Sheet1!$G$1&" 판매 합계"

위에서도 언급했듯이 간단하게 할일을 왜이리 어렵게 하나 생각하실수도 있는데요. 해당 배열수식과 콤보박스를 잘 활용하면 여러가지 양식들이나 보고서들을 잘 만드실수 있답니다. 

위의 설명으로 부족하신 분들을 위해 예제로 설명드렸던 엑셀 파일을 올려드리니 보시고 업무에 잘 활용해보시기 바래요^^

배열수식과 콤보박스 예제.xlsx