안녕하세요! gbgg 입니다. 능력 있는 직장인이 되는 법! 엑셀 자동화 강의 벌써 네 번째입니다!
엑셀의 함수 종류는 매우 많고 정말 다양한 기능들이 많지만 간단한 함수 기능만을 가지고도
조합을 통해 많은 것들을 구현해낼 수 있다는 것을 보여드리고자 합니다!
오늘 구현할 기능은 다음과 같습니다!
- 항목을 클릭하면 다음 선택할 항목이 바뀐다.
- 목록들을 수정할 수 있는 공간을 하나의 시트로 구현한다
오늘 목표는 사용자가 손쉽게 사용할 수 있는 UI를 엑셀로 구현하는 것입니다.
엑셀 내에 존재하는 간단한 기능들로 자동화 프로그램의 UI를 구성해보겠습니다.
기본 세팅
다음과 같이 엑셀 파일 A를 새로 만든 뒤 시트 3개를 생성합니다.
MAIN, 목록관리, DATA라는 시트명으로 생성해보겠습니다.
'MAIN' 시트를 아래 그림과 같이 꾸밉니다. (지난 포스팅 디자인과 같습니다!)
'목록관리' 시트를 아래 그림과 같이 꾸밉니다. (항목은 마음대로 하셔도 됩니다)
'DATA' 시트를 다음과 같이 꾸밉니다. (지난 포스팅 디자인과 같습니다!)
함수 및 기본 세팅
지난 시간에 콤보박스 목록 설정하는 법을 올려드렸었는데요! 기억나시나요?
아래 이미지와 같이 콤보박스를 누르고 컨트롤 서식 - 입력 범위와 셀 연결을 통해
목록관리에 있는 항목들과 매칭 시켜주면 됩니다. (지난 포스팅 참조)
아래 이미지처럼 물품 종류의 콤보 박스를 누르면 내가 세팅한 목록 중에 선택할 수 있도록 매칭 시켜 보겠습니다!
셀 연결에 지정한 셀에는 내가 콤보박스에서 선택한 목록의 INDEX가 표시됩니다.
아래 이미지를 보면 '목록관리' 시트의 선택 값 셀에 선택한 목록의 INDEX가 표시되는 것을 확인하실 수 있습니다.
자 그렇다면 이제 내가 선택한 물품 종류에 따라 'MAIN'시트의 '물품이름' 콤보박스 내용이 바뀌도록
구현하려면 어떻게 해야 할까요?
우선 물품 이름 콤보박스에 들어가야 할 내용을 정리해보겠습니다.
- A 시리즈
- A1050, A1060, A750, A960
- B 시리즈
- B980, B1080, B1050, B8600
- C 시리즈
- C950, C850, C240
이렇게 선택한 시리즈에 따라서 'MAIN' 시트의 '물품이름' 콤보박스 내용이 바뀌어야 합니다.
이 기능 구현을 위해 다음 그림과 같이 디자인을 해 봅시다.
이제 함수를 사용하여 선택 값에 따라 '선택한 제품 시리즈'에 값이 표기되도록 해 보겠습니다.
차근차근 따라 하시면 됩니다!
아주 간단한 IF 함수를 3개 중첩해서 사용했습니다.
| =IF(B7=1, C2, IF(B7=2, D2, IF(B7=3, E2))) |
물품종류 선택 값이 1이면 A2열, 2면 B2열, 3이면 C2열.
이 부분은 추후 수정하실 때 물품 종류 개수에 따라 바꿔주시면 됩니다.
이제 아래쪽으로 드래그하여 나머지 셀에도 적용시켜야 하는데 변하지 않아야 할 값들이 3 부분 존재합니다.
바로 B7입니다. 저 부분은 드래그하여 자동으로 적용시켜도 셀이 변하면 되지 않기 때문에 절댓값을 넣어줍니다.
절댓값은 함수 코드 부분의 B7 부분을 클릭하고 'F4'키를 눌러주시면 됩니다.
이렇게 B7이 있는 3 부분을 모두 절댓값 처리해줍니다.
| =IF($B$7=1, C2, IF($B$7=2, D2, IF($B$7=3, E2))) |
여기까지 잘 오셨다면 F2셀을 선택한 뒤 아래로 드래그해줍니다.
F2셀의 하단에 있는 굵은 점을 누르고 아래로 드래그하면 됩니다.
자 이렇게 원하는 대로 적용이 되었나요?
여기까지 성공하셨다면 'MAIN' 시트로 돌아가 B 시리즈를 눌러봅시다.
다음 그림과 같이 내가 'MAIN' 시트에서 선택한 값에 따라 F열에 있는 값이 변경되게 됩니다.
자 이제 거의 다 끝나갑니다! 이제 제품 이름의 목록과 셀 연결을 바꾸러 이동해봅시다.
목록은 이렇게.
셀 연결은 이렇게 해줍니다.
INDEX 함수를 사용하여 현재 선택한 항목 값이 어떤 것인지 표시해봅시다. (지난 포스팅 참조)
아래 이미지처럼 완성시켜봅시다.
결과
자 여기까지 잘 따라오셨나요? 목표했던 대로 '물품종류' 콤보박스에서 A 시리즈를 선택하면
'물품이름' 콤보박스의 값들이 변경됩니다.
'물품종류' 의 콤보박스에서 B 시리즈를 누르면 뒤의 값이 다음과 같이 변경됩니다.
방금 구현한 기능은 UI를 구성함에 있어서 가장 기초적인 부분이라고 생각합니다.
(상위 메뉴에서 선택한 항목에 따라 하위 메뉴의 목록 상자가 바뀌게 하는 것)
제가 구현한 방식보다 더 빠르고 간단하게 코드로 구현할 수 있지만
실제 업무에 적용하여 사용할 때는 위 내용처럼 목록 관리 시트를 따로 만들어 내용을 변경할 수 있게 하는 것이
더 사용하기 편합니다.
엑셀 자동화
이번 포스팅에서는 vba 없이 간단한 단계식 UI를 구성하는 법을 설명해드렸는데요.
기본 기능과 간단한 함수만 사용하여 굉장히 간단하지만 체계적으로 동작할 수 있도록
UI를 구성해보았는데 어떠셨나요?
능력 있는 직장인이 되기 위해 엑셀로 전산 프로그램을 만들 때 까지 한번 열심히 달려봅시다!
다음 포스팅에서 뵙겠습니다!
최근댓글