엑셀 다이나믹 간트 차트 만들기, 60분 완벽 가이드
엑셀 다이나믹 간트 차트 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [차트 강의] 엑셀 프로젝트 관리 끝판왕! 다이나믹 간트차트 만들기 - 예제파일예제파일✨ 엑셀 프로젝트 관리 - 다이나믹 간트차트 서식회원자료
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
🌟 핵심 과정 정리 🌟
이번 포스트에서는 다이나믹 간트차트 제작에 필요한 핵심 과정을 엄선하여 정리했습니다.
이번 포스트에서 안내해드린 과정을 따라하면 다이나믹 간트차트의 기본 틀을 만들 수 있습니다.
다이나믹 차트를 만드는 전체 과정은 영상 강의를 참고해주세요!😊
조건부서식으로 간트차트 기본 틀 만들기
- 그룹 프로젝트 강조하기 : 예제파일을 실행한 후, [동적간트차트] 시트로 이동합니다. 가장 먼저 각 그룹별 프로젝트를 자동으로 강조하는 조건부서식을 적용해보겠습니다.
각 그룹별 프로젝트를 강조하는 조건부서식을 적용합니다. - 구분이 "G(그룹)"일 때 강조할 C10:BQ35 범위를 선택한 후, [홈] 탭 - [조건부서식] - [새 규칙]으로 이동합니다.
범위 선택 후, 홈 - 조건부서식 - 새 규칙으로 이동합니다. - 새 서식 규칙 대화상자가 실행되면, 마지막 옵션인 '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 후 수식 입력창에 다음과 같이 수식을 작성합니다.
=$C10="G"
// C열의 값이 "G"일 경우 조건부서식을 적용합니다.
구분이 "G" 일 경우 적용되도록 수식을 입력합니다. 오빠두Tip : 전체 행을 강조하는 조건부서식의 원리는 아래 영상강의에서 알기 쉽게 정리했으니 참고하세요!😊
- 수식을 입력한 후, [서식] 버튼을 클릭하고 굵은 글꼴과 위 아래 테두리를 적용합니다. 서식을 모두 적용한 후 [확인] 버튼을 클릭해서 창을 종료합니다.
서식 - 테두리에서 위 아래 테두리를 추가합니다. - 이제 구분에 "G"를 입력하면 해당 행에 굵은 글꼴과 테두리가 자동으로 적용되는 것을 확인할 수 있습니다.
이제 구분에 G를 입력하면 테두리가 자동으로 적용됩니다. - 데이터 막대 추가하기 : 이제 조건부서식의 데이터 막대를 사용해 프로젝트의 진행률을 간단하게 강조해보겠습니다. 예제파일에서 진행률이 작성된 G10:G35 범위를 선택하고 [홈] 탭 - [조건부서식] - [데이터막대]로 이동합니다.
진행률 범위를 선택한 후, 조건부서식 - 데이터 막대로 이동합니다. - 목록에서 원하는 스타일의 데이터 막대를 선택하면 진행률을 강조하는 조건부서식이 적용됩니다.
원하는 막대 스타일을 선택해서 서식을 적용합니다. 오빠두Tip : 조건부서식을 활용한 데이터 시각화의 더 다양한 예제는 아래 기초 입문 강의를 참고하세요!👇
- 날짜 표시하기 : 이제 간트차트에 날짜를 표시해보겠습니다. 날짜를 "년-월-일"로 모두 표시하면 차트가 길어지므로, 이번에는 "월 / 일" 로만 간략하게 표시하겠습니다. 예제파일에서 R3셀을 선택한 후, 아래 수식을 입력합니다. MIN 함수를 사용해서 프로젝트의 시작일 중 가장 작은 날짜를 기준으로 차트를 생성합니다.
=MIN(E:E)-2
차트의 날짜 시작일은 프로젝트 중 가장 작은 날을 참조하여 동적으로 입력합니다. - 이제 오른쪽 범위에는 이전날짜 + 1 로 수식을 자동채우기합니다. S3셀에 아래 수식을 입력한 후, 수식을 차트 범위 끝까지 자동채우기합니다.
=R3+1
이전 날짜 + 1 로 자동채우기해서 나머지 날짜를 입력합니다. 오빠두Tip : 엑셀에서 숫자, 날짜 데이터의 경우, 셀 너비가 좁을 시 "###" 으로 표시됩니다. 실무에서 자주 발생하는 엑셀 오류와 해결 방법은 아래 강의에서 알기 쉽게 정리했으니 참고하세요!👇
- 이제 날짜를 각 '월/일'과 '요일' 텍스트로 표시합니다. R6셀에 다음과 같이 TEXT 함수를 입력하면 날짜가 '월/일'로 표시됩니다.
TEXT 함수로 월/일을 표시합니다. - 함수를 입력한 셀을 우클릭 - 셀 서식으로 이동한 후, 맞춤에서 텍스트 방향을 -90로 변경합니다.
셀 서식 - 맞춤에서 텍스트 방향을 -90도로 변경합니다. - 완성된 날짜를 오른쪽으로 자동채우기하면 다음과 같이 '월 / 일' 로 표시되는 날짜가 완성됩니다.
셀을 자동채우기하면 간트차트의 날짜 범위가 완성됩니다. - R8 셀에는 다음과 같이 TEXT 함수를 입력해서 자동채우기하면 요일이 표시됩니다.
=TEXT(R3,"aaa")
TEXT 함수로 요일까지 표시합니다. 오빠두Tip : 이 외에도 "ddd"로 영어 요일, "mmm"으로 영어 월을 표시할 수 있습니다. 엑셀 셀 서식의 기초 이론은 아래 5분 영상 강의에서 자세히 정리했으니 참고해주세요!😊
다이나믹 간트차트에 필요한 함수 입력하기
- 그룹별 ID : 이제 다이나믹 간트 차트를 만들 때 필요한 데이터를 하나씩 추가해보겠습니다. 먼저 각 그룹을 구분할 ID를 추가합니다. 예제파일에서 B10셀에 아래 수식을 작성 후, 수식을 아래로 자동채우기 하면 각 그룹별로 ID가 구해집니다.
=IF(C10="G",N(B9)+1,B9)
각 그룹을 구분하기 위한 ID를 추가합니다. - 시작일_계산 : 다음으로, 계산된 시작일을 구합니다. 항목이 'G(그룹)'일 경우, 해당 그룹의 프로젝트 중 가장 빠른 시작일을 불러오고 'T(항목)' 일 경우 기존 시작일을 그대로 불러오도록 동작하도록 수식을 작성합니다. 예제파일에서 K10셀을 선택한 후, 아래 수식을 입력합니다. MINIFS 함수는 엑셀 2019 이후 버전에서만 제공되므로, 엑셀 2016 이전 버전 사용자는 아래 남겨드린 배열 수식을 사용합니다.
· 엑셀 2019 이후
=IF(C10="G",MINIFS(E:E,C:C,"T",B:B,B10),E10)
· 엑셀 2016 이전 (Ctrl + Shift + Enter 로 입력합니다.)
=IF(C10="G",MIN(IF(($C$10:$C$34="T")*($B$10:$B$34=B10)*($E$10:$E$34>0),$E$10:$E$34,"")),E10)
프로젝트 그룹, 항목에 따라 시작일이 계산되도록 함수를 작성합니다. - 만약 아래 그림과 같이 날짜가 숫자로 표시될 경우, 범위를 선택하고 [홈] - [표시형식]을 날짜로 변경하거나 단축키 Ctrl + Shift + 3 으로 날짜 서식을 적용합니다.
날짜가 숫자로 표시될 경우, 표시형식을 변경합니다. 오빠두Tip : 엑셀에서 문자와 숫자, 날짜 데이터가 계산되는 원리는 아래 기초 입문 강의에서 알기 쉽게 정리했으니 참고하세요!😊
- 함수 입력하기 : 나머지 종료일과 종료일_계산, 진행률_계산, 총 작업일수 등 항목에도 아래 수식을 입력하여 간트차트에 필요한 데이터를 완성합니다.
· 종료일 (J10셀) : =WORKDAY.INTL(E10,F10,"0000011")
· 종료일계산 (L10셀) : =IF(C10="G",MAXIFS(J:J,C:C,"T",B:B,B10),J10)
* 2016 이전 버전 : =IF(C10="G",MAX(IF(($C$10:$C$34="T")*($B$10:$B$34=B10),$J$10:$J$34,"")),E10)
· 진행률계산 (M10셀) : =IF(C10="G",AVERAGEIFS(G:G,C:C,"T",B:B,B10),G10)
· 총작업일수 (N10셀) : =L10-K10
· 진행일수 (O10셀) : =N10*M10
· 진행일계산 (P10셀) : =K10+O10
나머지 간트 차트 제작에 필요한 데이터를 하나씩 입력합니다.
간편한 수식 작성을 위한 동적참조 범위 만들기
이제 다이나믹 간트차트에 필요한 조건부 서식을 편리하게 적용하기 위한 '동적 참조' 범위를 추가합니다. 동적 참조의 자세한 설명은 엑셀 기초 영상에서 꼼꼼히 정리했으니 참고해주세요!😊 이번 포스팅에서는 동적 참조 목록만 정리합니다.
R10:BQ35 범위를 선택한 후, 이름관리자에서 동적 참조 범위를 추가합니다. 예제파일에서 R10:BQ35 범위를 선택한 후, [수식] - [이름관리자]에서 아래에 정리한 이름 범위를 하나씩 추가합니다.
· 구분 : =동적간트차트!$C10
· 시작일 : =동적간트차트!$K10
· 오늘 : =동적간트차트!R$3
· 종료일 : =동적간트차트!$L10
· 진행률 : =동적간트차트!$M10
· 진행일 : =동적간트차트!$P10프로젝트 진행일을 표시하는 조건부서식 적용하기
- 이전 단계에서 등록한 동적 참조를 활용하여 프로젝트 진행 현황을 보여주는 조건부서식을 적용해보겠습니다. [동적간트차트] 시트에서 진행현황을 표시할 R10:BQ35 범위를 선택한 후 [홈] 탭 - [조건부서식] - [새 규칙]으로 이동합니다.
프로젝트 일정을 표시할 범위 선택 후 조건부서식 - 새 규칙으로 이동합니다. - 마지막 옵션인 '수식을 사용하여 서식을 지정할 셀 결정'을 선택하고 아래 수식을 입력합니다.
=AND(오늘>=시작일,오늘<=종료일)
// 날짜가 프로젝트 시작일보다 크고, 종료일보다 작을 경우 강조합니다.
오늘 날짜가 일정 시작일보다 크고, 종료일보다 작을 경우 강조하도록 수식을 입력합니다. - 수식을 입력한 후, [서식] - [채우기] 에서 옅은 회색 채우기를 적용합니다.
서식에서 배경색으로 회색 채우기를 적용합니다. - 확인 버튼을 클릭하면 아래 그림과 같이 프로젝트 예상 일정이 표시됩니다.
조건부서식을 추가하면 프로젝트의 예상 진행일정이 표시됩니다. - 아래 조건부서식을 동일한 과정으로 하나씩 추가하면, 프로젝트별 예상 일정과 진행 상황이 표시됩니다.
· 프로젝트 그룹 예상 일정 (진한 회색 배경, 흰색 글씨)
=AND(오늘>=시작일,오늘<=종료일,구분="G")
· 프로젝트 진행 상황 (옅은 초록 배경, 진한 초록색 글씨)
=AND(오늘>=시작일,오늘<=진행일,진행률>0)
· 프로젝트 그룹 진행 상황 (진한 초록 배경, 흰색 글씨)
=AND(오늘>=시작일,오늘<=진행일,구분="G",진행률>0)
프로젝트의 예상 일정및 진행 일정 등을 강조하도록 나머지 조건부서식을 하나씩 추가합니다.
스핀 단추로 동적 날짜범위 만들기
- 날짜 조정값 넣기 : 마지막으로 스핀단추를 사용해 동적으로 움직이는 날짜 범위를 만들어보겠습니다. [동적간트차트] 시트에서 D4셀의 조정 값으로 임의의 숫자를 입력합니다.
조정값으로 임의의 숫자를 입력합니다. - 이후 R3셀에 작성된 시작 날짜에 조정값을 더하도록 수식을 수정합니다. 이제 조정값을 변경하면 날짜가 동적으로 이동하게 됩니다.
=MIN(E:E)-2+D4
간트차트의 시작 날짜에 조정값을 더합니다. - 이제 스핀단추를 추가해보겠습니다. [개발도구] 탭 - [삽입] 에서 양식 컨트롤에 있는 스핀단추를 추가합니다.
개발도구에서 양식컨트롤의 스핀단추를 추가합니다. 오빠두Tip : 개발도구가 보이지 않을 경우, 리본메뉴를 우클릭 - 리본메뉴 사용자 지정에서 개발도구 탭을 활성화합니다. - 스핀단추를 우클릭 - [컨트롤 서식]으로 이동한 후, [컨트롤] 탭의 '셀 연결'을 조정값이 입력된 D4셀로 연결합니다.
컨트롤 서식 - 컨트롤 탭에서 셀 연결로 조정값이 작성된 셀을 연결합니다. - 이제 스핀단추를 클릭해보세요! 날짜가 이동하며 동적으로 표시되는 다이나믹 간트 차트가 완성되었습니다.😎
스핀단추를 클릭해서 일정을 자유롭게 확인하는 다이나믹 간트차트가 완성됩니다.
그런데 추가로 궁금한게 있어서 문의드립니다. 예제파일에는
1)작업일수가 고정값(목표값)으로 정해지고
2)작업일수의 진행률(%)을 입력하여 실적일 및 종료일수등을 구하도록되어있는데요.
실제로 실무에서는 프로젝트의 G별 계획일(예상소요일)을 세우고,
각 항목별(T) 계획일도 세워서 진척률을 챙기고있는데요.
이상적으로 계획일 대비 실행일이 정확히 딱 맞아떨어지면 좋겠지만....
실제로 회사실무에서는 계획일이 Tight하게 설정되기도하고
변수들이 많아서 계획일 대비 실행일(실적)이 Over되는경우가 허다합니다.
알려주신 간트차트 서식은 현장에서 활용이 어려운데요.
계획일 대비 실행일(실적일)이 over되는 경우를 감안한 서식으로 사용하고싶은데요. 어떻게 바꾸면 될까요?
계획일 대비 실적일이 over되는걸 표현하고 수치화하고 over된 해당 항목의 사유를 기입하고 싶습니다.
간트차트의 주요 목적은 프로젝트 일정을 시각적으로 관리하는 것입니다. 현재 서식으 ㅣ경우 T(항목)의 실제 진행상황에 따라 G(그룹)의 기간이 자동으로 조정되므로, 현재 템플릿으로도 유연한 일정 관리가 가능할 것으로 생각됩니다.
다만 계획 대비 실행 현황을 상세히 관리하시려면, 별도의 진척 관리 보고서를 만드는 것을 추천드립니다.예를 들어,
- 계획일 대비 실적일 차이
- 지연 사유
- 대응 방안
등은 간트차트 내에서 관리하는 것이 아닌 별도의 보고서를 기준으로 관리하는게 효율적일 수 있습니다.추가해야 할 항목이 많을 경우, 수식과 조건부서식을 먼저 넉넉하게 자동채우기 한 후 사용해보세요.
단 적용할 범위가 수백개 이상으로 많아질 경우 처리속도가 느려질 수 있습니다.:)
감사합니다.
이후 표에서 슬라이서를 추가해보세요 :)
예제파일의 구분이 그룹일경우, T이고 ID가 1일때의 진행률 가중평균을 구하려면 함수를 어떻게 작성해야하나요 ?
SUMPRODUCT함수를 이용해야하는건 알겠는데.. 함수작성이 잘 안되네요. ㅠㅠㅠ
가중평균은 SUMPRODUCT/SUM 공식으로 구할 수 있습니다.
아래 링크를 한번 확인해보세요.
https://www.oppadu.com/%ec%97%91%ec%85%80-%ea%b0%80%ec%a4%91%ed%8f%89%ea%b7%a0-%ea%b5%ac%ed%95%98%ea%b8%b0/
차크 아래쪽으로 항목을 추가한 후, 조건부서식 적용할 범위를 확장해서 사용해보세요.
그러실 경우 태스크를 그룹 단위로 묶어서, 태스크를 G로 하위 일정을 T로 관리해보시면 좋을 것 같습니다.
네, 맞습니다. 같은 항목을 여러 일정으로 나누어서 동일행에 표시하는건 불가합니다.🥲
네, T 하위 그룹도 추가할 수 있습니다.
조건부서식을 적용하면 됩니다. 아래 영상을 참고해보세요.
https://www.oppadu.com/%ec%a7%84%ec%a7%9c%ec%93%b0%eb%8a%94-%ec%8b%a4%eb%ac%b4%ec%97%91%ec%85%80-3-4-1/
다만 기본 설정만으로는 어렵고 셀 참조방식, 함수에 대한 이해가 조금 더 필요할 수 있습니다.
고맙습니다.