계획 관리 끝판왕! 엑셀 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분 기초 입문 강의에서 자세히 정리했습니다.👇
11:10 표시형식 복사전에는 뒤 2개 사각모형은 검정색으로 채워진 모형이지만,
11:37 복사후에는 공백으로 이뤄진 사각모형으로 바꼈는데,
엑셀버전의 문제인지, 아니면 편집에서 바뀐건지 궁금하네요.
☑️ ← 이 이모티콘을 셀 서식에 적용하면 처음에는 잘 표시되지만, 셀 서식을 적용한 후에 다시 표시형식으로 이동가면 "☑!"으로 다르게 표시됩니다.
이는 오류나 잘못된 것은 아니고, 간혹 이모티콘이 유니코드로 처리되면서 발생하는 변형으로 이해하시면 좋습니다. 특히 이러한 문제는 주로 글꼴과 관련해서 발생하는데요, 아마 엑셀 내부에서 셀서식을 적용할 때 변형이 있는 것 같습니다. :)
감사합니다!
완성파일 다운받아서 실행해보는데,
컨트롤 + ENTER로 0을 입력하고
한 지점에 완료체크를 위해 1을 입력하면
파일이 강제 종료됩니다.
0을 입력하는 것과,
처음 다운 받은 파일에서 월을 변경했을때
랜덤으로 0, 1이 변동될 때는 아무 이상이 없다가...
수동으로 1을 넣으면 무조건 파일이 강제종료 됩니다.
혹시 해결 방법이 있을까요?
답변 고대하고 있겠습니다.
감사합니다.
먼저 사용하고 계신 엑셀 버전과 운영체제(윈도우, 맥) 을 확인해주시겠어요? :)
엑셀 버전은 [홈] 탭 -> [계정] 에서 확인할 수 있습니다.
감사합니다.
엑셀은 2016버젼이며,
운영체제는 윈도우 10 pro 입니다!!!!!
엑셀 2016에서 실행해 본 결과, 잘 동작하는 것으로 확인하였습니다.
혹시 모두 0으로 입력하지 않고, 빈 칸으로 지운 상태에서 1을 입력해도 파일이 동일하게 강제 종료되는지 한번 확인해보시겠어요? :)
만약 파일이 계속 강제종료 된다면, 파일에 적용된 서식을 처리하는 과정에서 PC에 부담이 걸려 그럴 수 있습니다.
그럴 경우, 시트에서 임의의 셀을 선택 → [홈] → [조건부서식] → 규칙지우기 → 시트 전체에서 규칙 지우기로 조건부서식을 모두 지우거나, 서식에 적용된 함수(예: F20:F34)를 지워서 어느 부분에서 파일이 종료되는지 한번 확인해보세요.
감사합니다.
주 번호 계산 공식의 자세한 설명은 아래 게시글을 확인해보시겠어요? :)
https://www.oppadu.com/%EC%97%91%EC%85%80-%EB%82%A0%EC%A7%9C-%EC%A3%BC-%EB%8B%A8%EC%9C%84-%EA%B3%84%EC%82%B0/
감사합니다!
네모 체크박스 색상변경을 가르쳐 주신데로 했는데 반영이 안되고 그대로 검정색입니다. 어떻게 하면 되는지 궁금합니다.
조건부서식을 적용 후, 색상 변경이 안된다면 조건부서식의 셀 참조방식($)을 수업에서 설명해드린 대로 올바르게 적용하셨는지 한번 확인해보세요 :)
감사합니다.
그런데 이 TODO LIST를 Google Sheet에서 사용하고 싶은데, 여기서는 =RANDBETWEEN(0,1) 부분이 다 깨져서 #VALUE! 로 나오네요. 무슨 방법이 없을까요? 참고로 aaa가 깨지는것은 ddd로 구글시트에서는 변경했더니 해결되었습니다.
RANDBETWEEN 함수에서 오류가 발생하지 않아야 하는 것이 맞지만, RANDBETWEEN 함수는 임의값을 넣기 위한 것이므로 오류가 발생하는건 사실 크게 중요하지 않습니다. :)
RANDBETWEEN 함수를 모두 지운 후, 0/1 임의값을 직접 입력해서 한번 사용해보세요. 본 서식을 구글시트에 호환해서 사용할 수 있는지는 따로 확인해보지 않아서, 중간중간 오류가 발생하거나 깨지는 부분은 직접 수정하여 한번 사용해보시길 바랍니다.
감사합니다.
월을 바꿀 시 시트를 초기화하려면 매크로를 사용하면 가능합니다.
다만 매크로를 사용할 정도로 어려운 과정이 아니여서, 가능하다면 '초기화' 범위를 등록해서 초기화하는 방법(영상 초반에 안내해드린)을 사용하시고, 월을 바꿀 시 새 시트를 추가해서 사용하는 것을 권장합니다.
감사합니다.
엑셀에서 잘 사용해서 테블릿으로도 쓰고 싶어서 구글 스트레드시트에
옮겼더니 체크박스가 오류가 나더라고요 스트레드시트에서
사용할수 있는 방법이 있을까요?
구글시트에서 사용하시려면, 구글시트에서 기본으로 제공되는 체크박스 기능을 사용해보세요. 엑셀파일의 조건부서식이 구글시트에서 동일하게 적용이 되는지도 한 번 확인 후 사용해보시길 바랍니다.
감사합니다.
추가로 여쭤보고 싶은게 있습니다
혹시 진행률을 백분율이나 차트로 말고 개수로 표시 하는 방법도 있을까요?
예를들면 오늘의 할일이 10개였는데 3개가 체크되어서 7개 남았다는 걸 표시 할 수 있는 서식이 있을지 궁금합니다!
=COUNTIF(범위,TRUE)
를 사용하면 범위의 체크된 개수를 셀 수 있습니다. 한번 활용해보세요.
감사합니다.🙇♂️