양식콘트롤을 활용한 엑셀 대시보드 만들기
스핀단추와 옵션단추의 활용
여기 판매 데이터가 있습니다.
우리는 이 데이터의 '금액'에 대한 분석을 몇 가지 방법을 이용해서 계산할 것입니다.
1. 총 판매금액 계산
먼저 금액의 합계를 구해서 총 판매금액을 계산해 봅니다.
SUM 함수로도 가능하지만, SUBTOTAL함수를 이용하면 결과는 동일하지만 추후 필터링했을 때 소계를 계산할 수 있다는 확장성이 있습니다.
2. 제품군 및 제품회사별 판매금액 계산
이번에는 조건부 계산을 해보겠습니다.
판매금액을 특정 제품군 및 제조회사의 판매금액만 합산하는 것이 핵심입니다.
(1) 필터
먼저 기존 데이터에서 필터를 통해서 간단히 해볼 수 있습니다. Apple 사의 액세서리 판매금액은 두 개의 열에서 필터링 함으로써 250,000원임을 확인할 수 있습니다.
(2) SUMIFS (상수 활용)
조건부 연산을 위한 가장 널리 알려진 함수는 `SUMIFS` 입니다.
=SUMIFS($D$2:$D$11,$B$2:$B$11,"액세서리",$C$2:$C$11,"Apple")
(3) SUMIFS (참조 활용)
엑셀 함수는 상수가 아닌 참조와 변수를 활용할 때 더욱 확장력 있는 강력한 함수를 만들 수 있습니다.
앞서 작성한 함수에서 집계해야할 조건이 상수로 입력되어 있는데 이것을 특정 셀을 참조하도록 바꿀 수 있습니다. [H4] 셀에 제품군, [I4] 셀에 제조회사를 입력해서 함수가 참조할 수 있도록 준비하고 함수를 변경합니다.
이렇게 함수를 변경하면 다른 조건으로 판매금액을 집계하고자 할 때 결과 셀의 함수를 변경하는 것이 아니라 [H4] 셀과 [I4] 셀에 입력 또는 수정하면 집계할 수 있으므로 보다 직관적이고 수월합니다.
또한, [H4], [I4] 셀에 각각 데이터 유효성 검사를 활용해서 '목록'을 제한 대상으로 설정하면 사용자가 드롭다운 메뉴에서 선택하거나 제한된 값만 입력할 수 있어서 더욱 직관적이고 정확도가 높아집니다.
3. INDEX 함수를 활용한 조건 표시
(1) INDEX 함수 작성
[H4], [I4] 셀은 금액을 집계하기 위한 조건입니다.
이 조건을 표시할 때 [L1:M6]에 있는 목록을 활용해서 INDEX 함수로 바꿀 수 있습니다.
이것은 양식 콘트롤을 활용하기 위한 선행 작업입니다.
아래의 INDEX 함수는 `[L2:L6]의 범위(배열)에서 3번째에 위치한 값을 표시해달라` 라는 논리의 지시가 됩니다. [I4] 셀에도 같은 방식으로 1열이 증가한 함수식을 입력하면 됩니다.
=INDEX(L2:L6,3)
(2) row_num 을 셀 참조 구조로 함수 변경
앞서 작성한 INDEX 함수식에서 두 번째 인수인 row_num이 현재 상수 '3'으로 입력되어 있습니다.
3번째 행에 있는 값을 참조하라는 구조인데, 이 상수를 셀을 참조하도록 변경합니다.
[H6] 셀에 해당 함수가 참조할 값을 입력하고 함수식에서 상수 '3'을 셀 주소 [H6]으로 변경합니다.
4. 개발도구 리본 메뉴 추가
여기까지 준비되었으면 이제 드디어 양식 컨트롤을 사용할 차례입니다.
먼저, `개발도구` 메뉴를 리본 메뉴에 추가해 보겠습니다.
탭 메뉴 중 `개발도구`가 없는 분들은 먼저, 현재의 탭 메뉴 아무 곳에서나 마우스 오른쪽 버튼을 클릭해서 `리본 메뉴 사용자 지정`을 선택해 주세요.
이후 팝업된 `Excel 옵션` 창에서 오른쪽에 `개발 도구` 메뉴의 체크되지 않은 박스를 선택해서 체크해 주세요
이후 `개발 도구`가 추가된 것을 볼 수 있습니다.
5. 스핀단추
(1) 스핀단추 삽입
개발도구 양식 컨트롤 중 `스핀단추`를 먼저 활용해 보겠습니다.
스핀단추는 위, 아래로 이동을 지시하는 단추를 통해서 데이터를 변화시키는 컨트롤러입니다.
`개발도구 탭 > 컨트롤 그룹 > 삽입 > 스핀 단추` 를 선택해 주세요.
선택하게 되면 마우스 커서가 얇은 십자가 모양으로 바뀌는데 임의의 위치에서 클릭한 후 드래그앤 드롭 해주세요. 크기는 관계 없으니 적당한 크기로 만들어 주세요.
아래 화면과 같이 위, 아래를 향하는 버튼이 있는 `스핀단추`가 생성된 것을 확인할 수 있습니다.
(2) 스핀단추 설정
생성된 스핀단추를 마우스 오른쪽 버튼으로 클릭한 뒤 팝업된 메뉴에서 맨 아래에 있는 `컨트롤 서식`을 선택해 주세요.
컨트롤 서식 창을 아래와 같이 설정합니다.
현재값: 1 (스핀단추의 초기 값)
최소값: 1 (스핀단추가 변경할 가장 낮은 값, 우리 목록에서는 2번째 행이 가장 낮은 행)
최대값: 5 (스핀단추가 변경할 가장 높은 값, 우리 목록에서는 6번째 행이 가장 높은 행)
증분 변경: 1 (스핀단추가 눌러질 때마다 증가될 값의 크기)
셀 연결: $H$6 (값을 증감시킬 셀 주소 - 가장 중요!)
설정을 완료한 후 확인을 누릅니다.
이제 스핀 단추를 눌러보면 값이 변경되는 것을 확인할 수 있습니다.
이것은 아래와 같이 3단계에 걸친 참조로 이루어진 것을 확인할 수 있습니다.
그리고 필요하다면 스핀단추가 참조하고 있는 [H6] 셀의 글꼴 색을 `흰색` 으로 바꾸면 사용자는 스핀단추가 [H4]셀의 `제품군`을 직접 변경하는 것으로 느낄 수 있습니다.
스핀단추가 [H6]셀의 값을 변경
[H6]셀의 값이 [H4]셀의 값을 변경
[H4]셀의 값이 [J4]셀의 값을 변경
6. 옵션단추
(1) 옵션단추 삽입
`제조회사`는 `옵션단추`를 활용해서 컨트롤하도록 만들어 보겠습니다.
(먼저, [I4]셀에 입력된 INDEX 함수의 두번째 인수가 [I6]셀을 참조하도록 변경해야 합니다)
`개발도구 탭 > 컨트롤 그룹 > 삽입 > `옵션단추` 를 선택해 주세요.
스핀단추와 마찬가지로 선택하고 나면 마우스 커서가 얇은 십자가 모양으로 바뀌는데 임의의 위치에서 클릭 해주면 옵션 단추가 삽입됩니다.
(2) 옵션단추 설정
생성된 옵션단추를 마우스 오른쪽 버튼을 클릭해서 `컨트롤 서식`을 선택합니다.
값을 `선택한 상태`로 선택하고, `셀 연결`에 [$I$6]을 입력합니다.
이것은 I6 셀의 값을 옵션단추로 컨트롤하겠다는 명령이 됩니다.
이제 만들어진 옵션단추를 복사해서 붙여넣겠습니다.
제조회사가 5개이므로, 4개를 더 추가하겠습니다.
마지막으로 각 옵션 단추의 내용을 식별할 수 있도록 각 제조회사의 이름으로 변경합니다.
각 단추를 선택하고 마우스 커서가 "I" 모양으로 됐을 때 안쪽을 클릭하면 내용을 변경할 수 있습니다.
앞서 스핀단추와 마찬가지로 옵션 단추는 아래와 같은 구조로 컨트롤합니다.
스핀단추가 [I6] 셀의 값을 변경합니다. (1부터 5까지의 값 중 하나)
[I6] 셀의 값은 [I4] 셀의 값을 변경합니다.
[I4] 셀의 값은 [J4] 셀의 값을 변경합니다.
7. 끝마치며
지금까지 양식컨트롤 도구 중 스핀단추와 옵션단추에 대해서 알아보았습니다.
슬라이서가 표와 피벗 테이블에 대해서만 적용되는 것에 비해서 양식컨트롤은 셀의 값을 변경시키는 것이기 때문에 활용도가 상대적으로 높다고 할 수 있겠습니다.
따라서 수식 또는 함수를 활용한 대시보드 제작시 폭넓게 활용할 수 있습니다.
(클릭하면 전체 시연 GIF를 볼 수 있습니다)