[엑셀/EXCEL]유효성검사, 콤보박스로 대시보드 만들기

 

[엑셀/EXCEL]유효성검사, 콤보상자 사용법

일전에 콤보상자와 유효성 검사를 이용하는 이용법을 설명드렸습니다.

콤보상자와 유효성 검사를 이용하면 개발자가 사용자의 행동 패턴을 고정화해서 공유할 수 있습니다.

이를 이용하면 간단한 대시보드 화면을 만들 수 있습니다.

만들어진 화면의 예시를 먼저 보여드리겠습니다.

먼저 유효성 검사를 이용해서 만든 대시보드입니다.

아래는 콤보상자를 이용한 대시보드입니다.

---

대시보드를 만들기 위해서는 단순한 하나의 기능만을 사용하는 것이 아니라 지금껏 설명드렸던 많은 함수들을 다 사용해야만 만들 수 있습니다.

우선 전체적인 형태를 보여드리겠습니다.

보통 더 큰 파일을 관리한다면 데이터를 저렇게 배치하는 것이 아니라 RAW 파일 관리용 탭을 따로 만들어서 관리합니다.

다만 지금은 설명을 위해서 간단하게 RAW 데이터를 만들었습니다.

가장 중요한 건 일자별로는 해당 일자의 데이터가 나오고, 월별은 해당 월의 합계, 연간은 해당 연도의 연간 합계가 나오도록 만들었습니다.

데이터를 불러오는 함수는 SUMIFS로 모두 작성하였습니다.

또한 SUMIFS를 통해서 기간을 지정해서 데이터를 가져오도록 했는데 이는 예전에 설명한 내용이 있어 해당 내용을 참조하시면 쉽게 만드실 수 있습니다.

[엑셀/EXCEL]SUMIFS 기본사용, 범위합산

이것을 참조한다면 결과적으로 날짜 값만 콤보상자와 유효성 검사로 변경한다면 그 조건만큼 범위가 표기되도록 만들어 줄 수 있습니다.

---

□ 유효성 검사 사용

 - 일자별 데이터

일자별 데이터를 표현하는 장소에 유효성 검사를 통해서 선택한 날짜가 나오도록 하고, 해당 날짜를 기준으로 모든 날짜를 깔아줍니다. 이후 해당 날짜를 기반으로 SUMIFS를 지정해주면 됩니다.

 - 월간 데이터

월간 데이터의 표기는 기간을 이용해서 작업해야 하기 때문에 월의 처음 즉 선택한 월의 1일이 날짜로 잡히게 해야 합니다.

Date 함수를 이용해서 유효성 검사 데이터를 불러와서 Day를 1로 고정해줍니다.

이렇게 해준 후 조건을 ( [해당 월의 1일] <= [값] and [다음 월의 1일] > [값] )으로 지정함으로써 항상 해당 월의 전체 데이터를 sumifs로 가져오도록 합니다.

 - 연간 데이터

연간 데이터는 월간과 동일하게 작업하면 됩니다. 다만 현재는 2021년도만 데이터를 넣어 놨기 때문에 2021년도 1월 1일부터 그 뒤의 모든 일자 데이터는 모두 가져오는 것으로 했습니다.

연간 데이터를 불러오는 방식은 위와 동일합니다.

---

□ 콤보상자 사용

 - 콤보상자 일자 설정

콤보상자는 전에 설명드렸다시피 특정한 날짜 값을 지정해 주는 것이 아니고 선택된 목록의 위치를 1부터 정수 값으로 커지도록 되어 있습니다.

콤보상자의 셀 연결은 P4셀에 연결해서 해당 셀에 데이터가 들어가도록 합니다.

이렇게 나눈 뒤 콤보상자의 목록 값은 항상 1부터 시작하기 때문에 여기 [-1]을 해서 값을 목록과 값을 맞춰줍니다.

그렇게 만들어낸 날짜 값을 일자별 범위로 잡아줍니다.

그렇게 지정된 값을 가져와서 일자에 지정해줍니다.

이런 방식으로 일자별 데이터를 지정 해준 후 줍니다.

아래 SUMIFS 함수는 모두 동일하기 때문에 별도로 설정하지 않습니다.

---

현재는 데이터가 하나밖에 없는 데이터를 가공했기 때문에 매우 간단하게 만들었지만 실제로 RAW 데이터가 쌓이게 되면 훨씬 복잡한 형태의 대시보드가 만들어집니다.

간단하게 조금만 추가해준다면 이런 식으로 전월, 금월 값을 불러와서 표현하거나

일자별 데이터에 그래프를 추가해줄 수 있습니다.

대시보드를 만들 수 있는 간단한 방법만 설명드렸고 이걸 응용하면 주식 수익률 차트나 일자별 현황 관리용 대시보드를 만들 수 있습니다.