계획 관리 끝판왕! 엑셀 To-Do 리스트 대시보드 만들기
엑셀 Todo 대시보드 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [대시보드강의] 엑셀 To-Do 리스트 대시보드 만들기예제파일✨ 엑셀 일정관리 투두리스트 대시보드회원자료
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
년도/월을 선택하면 바뀌는 To-Do 리스트 월간 달력 만들기
엑셀 To-Do 리스트의 첫걸음은, 선택한 년도와 월에 따라 자동으로 업데이트 되는 월간 달력을 만드는 것부터 시작합니다. 이번 강의에서 소개해드린 간단한 서식과 함수를 활용하면, 누구나 월간 달력을 손쉽게 만들 수 있습니다.
- 년도와 월을 선택하는 목록상자 만들기 : 예제파일의 J10 셀을 선택한 후, [데이터] 탭 - [데이터 유효성 검사]로 이동합니다. 데이터 유효성 대화상자가 실행되면 제한 대상으로 목록을 선택한 후, 아래 그림과 같이 년도로 선택할 항목을 쉼표로 나누어 작성합니다. 이번 강의에서는 2023년부터 2026년까지 작성하였습니다.
년도를 선택한 셀을 선택한 후, [데이터 유효성 검사] 에서 목록 상자를 적용합니다. - [확인] 버튼을 클릭하면 아래 그림과 같이 년도를 선택하는 목록상자가 완성됩니다. 동일한 방법으로 L11셀에는 1부터 12까지 값을 선택하는 목록상자를 적용합니다.
년도를 선택하는 목록상자가 적용됩니다. 오빠두Tip : 표와 INDIRECT 함수를 활용하면, 값이 추가되거나 바뀔 때마다 목록상자가 실시간으로 업데이트되는 동적 목록상자를 만들 수 있습니다. 동적 목록상자를 만드는 방법은 아래 1분 영상강의를 참고하세요!👇
- 월간 달력 만들기 : 먼저 달력의 '일'로 사용할 일 번호를 작성합니다. 예제파일에서 J12, K12셀에 1과 2를 각각 입력한 후, 오른쪽으로 자동채우기하여 1부터 31까지 연속된 순번을 생성합니다.
날짜의 일로 사용할 1부터 31까지 연속된 순번을 '일번호' 범위에 생성합니다. - 이제 선택한 년도와 월의 1일부터 31일까지 날짜를 만들기 위해, J14셀에 아래와 같이 DATE 함수를 작성한 후 오른쪽으로 자동채우기해서 날짜를 완성합니다. DATE 함수에서 년도와 월이 작성된 J10, L10셀은 절대참조로 작성하는 것을 주의합니다.
=DATE($J$10,$L$10,J12)
DATE함수로 각 년도, 월, 일을 참조하여 선태한 년도와 월의 날짜를 생성합니다. 오빠두Tip : 셀 참조방식($)을 이해하면 복잡한 함수도 편리하게 작성할 수 있습니다. 셀 참조방식에 대한 자세한 설명은 아래 영상 강의를 참고하세요!👇
- 날짜가 작성된 J14:AN14 범위를 선택한 후, 우클릭하여 '셀 서식'으로 이동합니다. 셀 서식 대화상자가 실행되면 [표시 형식] - [사용자 지정]에서 다음과 같이 서식을 작성합니다. 서식을 'd'를 작성하면, 날짜에서 '일'만 표시할 수 있습니다.
날짜가 작성된 범위를 선택한 후, [셀 서식]에서 표시형식을 d로 변경합니다. - 마지막으로 날짜의 요일을 표시하면 달력이 완성됩니다. 예제파일에서 J13셀을 선택한 후, 아래와 같이 TEXT 함수를 작성합니다. TEXT 함수에서 표시형식으로 "aaa"를 작성하면, 날짜의 요일을 표시할 수 있습니다.
=TEXT(J14,"aaa")
TEXT 함수로 날짜의 요일을 표시합니다. 오빠두Tip : 셀 서식에 대한 자세한 설명은 아래 영상 강의를 참고하세요!👇
- 작성한 수식을 To-Do 리스트의 오른쪽 끝까지 자동채우기하면 월간 달력이 완성됩니다.
TEXT 함수를 오른쪽으로 자동채우기하면 월간 달력이 완성됩니다. - 월 단위 주 번호 계산하기 : 각 날짜가 선택한 월의 '몇번째 주'인지 계산합니다. 예제파일에서 J11셀에 아래 공식을 붙여넣기 한 후, 공식에서 "날짜"를 날짜가 작성된 셀 주소인 "J11"로 변경해서 수식을 입력합니다.
=WEEKNUM(날짜,2)-WEEKNUM(EOMONTH(날짜,-1)+1,2)+1
완성 된 공식으로 각 날짜의 월 기준 주 번호를 계산합니다. - 작성한 수식을 오른쪽으로 자동채우기하면, 각 날짜의 "월요일 기준 주 번호"가 계산됩니다.
■공식의 동작 원리 :
① WEEKNUM(날짜,2)
: 날짜의 년 단위 주 번호를 월요일 기준으로 계산합니다.
예) 2024/05/18 → 20
② EOMONTH(날짜,-1)+1
: 날짜가 속한 달의 첫째 날, 즉 그 달의 1일을 반환합니다.
예) EOMONTH(2024/05/18, -1) +1 → 2024/05/01
③ 따라서 공식은 다음과 같이 동작합니다.
=WEEKNUM(날짜,2)-WEEKNUM(EOMONTH(날짜,-1)+1,2)+1
=[해당 날짜의 주 번호] - [선택한 달 1일의 주번호] + 1
=WEEKNUM(2024/05/18, 2) - WEEKNUM(2024/05/01, 2) + 1
=20 - 18 + 1
=3 (즉, 5월 18일은 5월의 3번째 주임)
주 번호 계산 공식을 To-Do 리스트의 끝까지 자동채우기해서 월간 달력을 완성합니다. 오빠두Tip : 상황별 주 번호 계산 공식은 아래 영상 강의에서 꼼꼼히 정리했습니다.👇
엑셀 모든 버전에서 사용가능한 체크박스 적용하기
각 해야 할 일의 완료 상태를 명확하게 표시하고, 전체적인 진행 상황을 쉽게 추적할 수 있는 체크박스를 추가해보겠습니다. 이번 강의에서는 '셀 서식'을 활용하여, 엑셀 모든 버전에서 사용할 수 있는 체크박스를 추가합니다. M365 버전에서 새롭게 추가 된 '확인란' 기능을 사용하면, 체크박스를 더욱 편리하게 추가할 수 있습니다. M365 버전의 확인란 기능에 대한 자세한 설명은 아래 영상 강의를 참고하세요!
- 셀 서식으로 체크박스 만들기 : 예제파일에서 체크박스를 적용할 범위인 J15:AN29를 선택한 후, 범위를 우클릭 - 셀 서식으로 이동합니다.
체크박스를 적용할 범위를 선택한 후, [셀 서식]으로 이동합니다. - 셀 서식 대화상자가 실행되면, [표시 형식] - [사용자 지정]에서 형식으로 아래 서식을 복사하여 붙여넣기 한 후, [확인] 버튼을 클릭해서 서식을 적용합니다.
_.☑️;🔲;🔲
- 이제 범위를 선택한 상태에서 0을 입력한 후, Ctrl + Enter 를 동시에 눌러 범위에 0을 일괄 입력하면, 비어있는 박스가 한 번에 작성됩니다.
셀 서식을 적용한 범위에 0을 입력하면 비어있는 체크박스가 표시됩니다. 오빠두Tip : Ctrl + Enter 를 동시에 누르면, 선택한 범위에 값을 일괄적으로 입력할 수 있습니다. 실무자에게 유용한 엑셀 필수 단축키 정리는 아래 기초 영상 강의에서 꼼꼼히 정리했습니다.👇
- 체크박스 사용해보기 : 체크박스를 적용한 범위를 선택한 후, [홈] 탭에서 정렬 방향을 '가운데 정렬'로 변경합니다.
체크박스의 정렬 방향을 가운데 정렬로 변경합니다. - 이제 각 일자별로 완료된 항목의 값을 1로 변경하면, 체크박스가 간단하게 작성됩니다.
체크박스가 적용된 범위에 1을 입력하면, 체크박스가 표시됩니다.
주차별 다른 색으로 조건부서식 적용하기
조건부서식을 활용하면 각 주차별로 다른 색상을 적용해서, To-Do 리스트의 가독성을 크게 높이고 관리의 효율성을 개선할 수 있습니다.
- 서식에 사용할 색상 코드 만들기 : 먼저 대시보드에 적용할 색상 코드를 정합니다. 아래 링크로 남겨드린 'Gradient-palette' 도구를 사용하면, 원하는 색상 코드를 쉽게 찾을 수 있습니다. 아래 링크를 클릭하여 'Gradient-Pallate' 페이지로 이동한 후, Random 버튼을 클릭하여 무작위 색을 고르거나 회사에서 사용하는 브랜드 주요 색상 기준의 색상 코드를 선택합니다. 이번 강의에서는 예제파일의 [색상표] 시트에 미리 정리 코드를 사용하겠습니다.
To-Do 리스트 대시보드에 사용할 색상 코드를 정합니다. - [색상표] 시트에 미리 정리된 가장 연한 색상의 표의 색 코드를 복사하여 다른 시트에서도 사용할 수 있도록 메모장에 붙여넣기합니다.
예제파일 [색상표]에 미리 정리된 색상 코드표를 복사하여 메모장에 붙여넣기합니다. - 머리글 범위에 조건부서식 적용하기 : 예제파일에서 머리글 범위인 J11:AN14 범위를 선택한 후, [홈] 탭 - [조건부서식] - [새 규칙]으로 이동합니다.
- [새 서식 규칙] 대화상자가 실행되면, 마지막 옵션인 '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 후 아래 입력창에 수식을 작성합니다. 먼저 1주차일 경우, 색상표의 색1(분홍색)을 적용하기 위해 아래 수식을 작성합니다. 주 번호가 작성된 J11은 숫자 앞에만 $ 기호를 붙이는 것을 주의합니다.
=J$11=1
'J11(주 번호)가 1일 경우 서식을 적용합니다.
머리글 범위 선택 - [조건부서식] - [새 규칙] - [수식을 사용하여 서식을 지정할 셀 결정]에서 수식을 입력합니다. 오빠두Tip : 조건부서식과 셀 참조방식으로 전체 행과 열을 강조하는 방법과 동작 원리는 아래 영상 강의에서 자세히 정리했습니다.👇
- [서식] 버튼을 클릭한 후, [채우기] 탭 - [다른 색] - [사용자 지정]에서 색1(분홍색)에 해당하는 색 코드를 적용합니다. HEX 코드는 엑셀 2021 이후 버전에서만 제공되므로, 엑셀 2019 이전 버전 사용자는 RGB 값으로 색상을 적용합니다. 색상을 변경한 후, [확인] 버튼을 클릭하여 조건부서식을 적용합니다.
[서식]에서 에서 채우기 색상을 1주차에 해당하는 색 코드로 변경합니다. - 조건부 서식을 적용하면 아래 그림과 같이 1주차의 배경색이 분홍색으로 변합니다. 동일한 원리로 2주차부터 6주차까지, 색2~색6 구간을 적용합니다.
=J$11=주번호
'색2부터 색6까지 각 주차별 채우기색을 적용합니다.
1주차의 머리글 색상이 변경됩니다. 동일한 방법으로 2주~6주까지 조건부서식을 적용합니다. - 체크박스에 조건부서식 적용하기 : 이번에는 체크박스 범위에 조건부 서식을 적용합니다. 체크박스가 작성된 J15:AN29 범위를 선택한 후, 이전과 동일하게 조건부서식을 적용합니다.
체크박스 범위를 선택한 후, 머리글에 적용한 것과 동일하게 조건부서식을 적용합니다. - 체크박스의 서식은 [글꼴]에서 글꼴색을 변경합니다. [색상표] 시트에서 가장 진한 색상의 표의 색 코드를 복사하여 색1부터 색6까지 차례대로 적용합니다.
체크박스는 글꼴색이 바뀌도록 조건부서식을 적용합니다. - 서식을 모두 적용하면 아래 그림과 같이 각 주차별로 다른 색이 적용된 To-Do 리스트가 완성됩니다.
각 주차별 다른 색상이 적용된 To-Do 체크리스트가 완성됩니다.
각 항목별 진행률 집계 및 대시보드 완성하기
마지막 단계로, 각 항목별 진행률을 집계하고 이를 시각적으로 표현하여 대시보드를 완성합니다.
- 선택한 월의 전체 일수 계산하기 : 먼저 각 항목의 진행률을 계산하려면, 선택한 월의 전체 일 수를 알아야 합니다. 예제파일에서 N10셀 (또는 임의의 비어있는 셀)에 아래 수식을 입력합니다.
=DATE(J10,L10+1,1)-DATE(J10,L10,1)
'다음 달 1일에서 이번달 1일을 뺍니다 → 이번 달의 전체 일수 계산
선택한 월의 전체 일수를 계산하는 공식을 작성합니다. - 수식을 입력하면 선택한 월의 전체 일수가 계산됩니다.
선택한 월의 전체 일수가 계산됩니다. - 각 항목별 진행률 시각화하기 : 각 항목별 진행률을 계산합니다. AP15셀에 아래 수식을 입력한 후, To-Do 리스트 범위 끝까지 아래로 자동채우기 합니다.
각 항목의 진행 개수 / 전체 일수로 항목별 진행률을 계산합니다. - 엑셀은 수식에 참조한 범위에 서식이 적용되어 있을 경우, 계산 결과에도 해당 서식을 적용합니다. 따라서 진행률이 체크박스로 표시될 경우, 범위를 선택한 후, [홈] 탭 - [표시형식] 에서 백분율 서식을 적용하거나, 단축키 Ctrl + Shift + 5 로 백분율 서식을 적용합니다.
표시형식을 백분율로 변경합니다. - 오른쪽 완료현황 범위에 데이터막대를 추가합니다. AP15:AP29 에 계산한 진행률 값을 AQ15:AQ29 범위에 동일하게 불러온 후, [홈] 탭 - [조건부서식] - [데이터막대] - [기타규칙]으로 이동합니다.
완료 현황 범위를 선택한 후, [조건부서식] - [데이터 막대] - [기타 규칙]으로 이동합니다. - [새 서식 규칙] 대화상자가 실행되면, 서식 스타일에서 '막대만 표시'를 체크한 후, 최소값과 최대값의 종류를 각각 숫자로 변경하고 0%와 100% 구간으로 설정합니다. 마지막으로 막대모양의 채우기 색상을 적절히 변경한 후, [확인] 버튼을 클릭합니다.
서식 규칙 대화상자에서, 데이터막대의 서식 스타일 및 종류, 막대 모양을 적절히 변경합니다. - 데이터 막대가 추가되면서, 항목별 완료 현황의 시각화가 완료됩니다.
각 항목별 진행상황을 시각화하는 데이터 막대가 추가됩니다. - 시각화 차트 만들기 : 동일한 방법으로 일별 완료 현황 및 전체 완료 현황을 집계한 후, 차트를 추가하면 To-Do 리스트 대시보드가 완성됩니다. 일별, 전체 진행현황을 집계하고 차트를 만드는 과정은 영상 강의를 참고하세요!
마지막으로 일별, 전체 진행현황을 집계한 후, 차트를 주가하면 대시보드가 완성됩니다. 오빠두Tip : 차트 간소화와 색감 규칙으로 실무에 바로 적요할 수 있는 시각화 기법에 대한 내용은 아래 7분 기초 입문 강의에서 자세히 정리했습니다.👇
Subscribe
Login
지금 가입하고 댓글에 참여해보세요!
30 Comments
