엑셀 달력, 공휴일 및 일정관리 가능한 달력 만들기
엑셀 달력 목차 바로가기
엑셀 달력 만들기, 어렵지 않습니다. 간단한 함수 몇가지와 조건부서식을 응용하면 공휴일, 일정이 자동으로 업데이트 되는 만년달력, 자동달력을 누구나 손쉽게 만들 수 있습니다. 이번 강의에서는 엑셀 자동달력 만드는 과정을 단계별로 알아보겠습니다.
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 공휴일 자동 업데이트 달력 만들기예제파일[실무기초] 공휴일 자동 업데이트 달력 만들기완성파일
패치노트
- 2019년 1월 15일 :
일요일부터 시작하는 달력 만들기 관련 내용을 추가하였습니다. - 2019년 11월 12일 :
연간 달력을 추가하였습니다. - 2020년 3월 8일 :
익월 표시 달력의 수식을 수정하였습니다. (12월에서+1 더할시 13으로 계산되어 발생되는 오류 수정)=IFERROR(IF(IF('달력'!$S$2+1<>MONTH(셀+1),"",셀+1),"")
=IFERROR(IF(IF(('달력'!$S$2+1)>12,1,'달력'!$S$2+1)<>MONTH(셀+1),"",셀+1),"")
'// 12월에서 '+1'을 더해 13이 될 경우, 1월 반환합니다.
관련 기초함수
엑셀 달력 자동화 양식 다운로드
더욱 많은 기능이 포함된 엑셀 달력 자동화양식을 무료 서식 페이지에 올려드렸습니다. 아래 링크를 확인해주세요.
달력 첫째주 날짜 함수공식
= COLUMNS(열범위)/2 + DATE(년,월,1) - WEEKDAY(DATE(년,월,1),2)함수 함수 설명 COLUMNS 함수 입력한 범위의 열개수를 반환합니다. DATE 함수 년/월/일 값을 받아 날짜로 반환합니다. WEEKDAY 함수 선택한 날짜의 요일을 지정한 형식의 일련번호로 반환합니다. (예: 일요일=1, 월요일=2, 등...) 예제파일에서 사용된 함수
=COLUMNS($A$6:F$6)/2+DATE($Q$2,$S$2,1)-WEEKDAY(DATE($Q$2,$S$2,1),2)- COLUMNS($A$6:F$6)
'// A6~F6 범위의 열 개수를 반환합니다.
= 6 - DATE($Q$2,$S$2,1)
'// Q2셀 : 년도를 받아옵니다
'// S2셀 : 월을 받아옵니다.
= 선택한 년도, 월의 1일을 출력합니다. - WEEKDAY(DATE($Q$2,$S$2,1),2)
'// 매월 1일의 요일번호를 반환합니다. WEEKDAY의 인수로 '2'가 입력되었습니다.
= 월요일(1) ~ 일요일(7) 까지 요일번호를 반환합니다. - = COLUMNS($A$6:F$6)/2+DATE($Q$2,$S$2,1)-WEEKDAY(DATE($Q$2,$S$2,1),2)
= 6/2 + ‘2019/2/1’ – WEEKDAY(‘2019/2/1’,2)
= 3 + ‘2019/2/1’ – 5
= ‘2019/2/1’ – 2
= ‘2019/1/30’
달력 둘째주 날짜 표시를 위한 함수공식
= 이전셀 + 1
'// 이전 날짜에 +1 을 순차적으로 더해 달력을 완성합니다.해당 날짜가 휴일일 경우, 휴일 설명을 불러오기 위한 함수
=IFERROR(VLOOKUP(셀,휴일!$A:$B,2,0),"")함수 함수 설명 IFERROR 함수 입력한 수식이 오류를 반환할 경우, 오류대신 특정 값을 출력합니다. VLOOKUP 함수 범위의 맨 좌측열에서 값을 찾은 뒤, 옆에 있는 다른 값을 출력합니다. 만약 범위에 찾으려는 값이 없을 경우 #N/A! 오류를 반환합니다. 달력 예제파일에서 사용된 함수
=IFERROR(VLOOKUP( 2019-01-02,휴일!$A:$B,2,0),"")- = VLOOKUP( 2019-01-02, 휴일!$A$B,2,0 )
'// 2019-01-02 를 휴일시트의 A열에서 검색한 뒤, 값이 있으면 같은 행 B열에 있는 값을 출력합니다.
'// A 열에 2019-01-02 이 없을 경우, #N/A! 오류를 반환합니다.
= #N/A! - = IFERROR ( #N/A, “”) :
'// VLOOKUP 함수에서 #N/A 오류가 반환될 경우 IFERROR 함수를 통해 빈칸을 출력합니다.
= ""
자동 달력 조건부서식 설정하기
본 예제파일에는 2개의 조건부서식이 미리 적용되어 있으며, 모든 서식에는 동일한 원리의 수식이 사용되었습니다. 따라서 아래 예제만 이해하시면, 엑셀 달력에 적용된 나머지 조건부서식은 쉽게 이해하실 수 있습니다.
- 엑셀 달력의 범위를 B6부터 N17까지 선택합니다. 단, 기준셀이 B6셀이 되도록 반드시 B6셀을 시작으로 범위를 선택합니다.
달력시트의 B6:N17 까지 달력범위를 선택합니다. - [홈] - [조건부서식] - [새규칙]으로 이동합니다.
조건부서식의 새규칙으로 이동합니다. - '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 뒤, 아래 수식을 복사/붙여넣기합니다. 이후 [서식]을 클릭하여 달력의 조건부서식을 변경합니다.
=AND(MONTH(B6)<>$S$2,B6<>"")
수식을 붙여넣기 한 뒤, [서식] 버튼을 클릭합니다. - '글꼴' 로 이동합니다. 이후 색상을 옅은 회색을 선택한 뒤, [확인] 버튼을 눌러 서식설정을 마무리합니다.
글꼴 색상을 옅은회색으로 선택한 뒤, 확인을 눌러 마무리합니다. - 방금 적용한 조건부서식은, 해당셀이 빈칸이 아니고 기준월과 다를경우, 즉 기준월이 아닌 전달 또는 다음달일 경우, 회색으로 글꼴을 적용하는 조건부서식입니다.
=AND(MONTH(B6)<>$S$2,B6<>"")
'// MONTH(B6) <> $S$2 : 해당셀이 기준월과 다르고
'// B6<>"" : 해당셀이 빈칸이 아닐 경우 조건부서식을 적용합니다.
연결된 그림 기능은 엑셀 2007 이후 버전에서 지원되는 기능입니다.
- [연결이미지] 시트로 이동한 뒤, 달력 제목을 표시하기 위한 P2:S2 범위를 복사합니다.
연결이미지 시트의 P2:S2 범위를 복사합니다. - [달력] 시트로 이동합니다. J2 셀을 우클릭한 뒤, '선택하여 붙여넣기' - '연결된 그림' 으로 붙여넣기합니다.
복사한 범위를 '연결된 그림' 형태로 붙여넣기합니다. - [연결이미지] 시트의 B2:N10 범위도 복사한 뒤, [달력] 시트 B18셀에 연결된 그림으로 붙여넣기합니다.
'연결이미지' 시트의 달력범위도 복사한 뒤, 연결된그림으로 붙여넣기합니다.
자주묻는질문
Q1. 일요일부터 시작하는 달력을 만들고 싶어요
달력 첫째주 날짜를 입력하는 공식의 WEEKDAY 함수 두번째 인수만 변경하면, 일요일부터 시작하는 달력으로 손쉽게 변경할 수 있습니다. 공식은 아래와 같습니다.
=COLUMNS(열범위)/2+DATE(년,월,1)-WEEKDAY(DATE(년,월,1),1)- 위 공식에서 WEEKDAY 함수의 두번째 인수를 '1'로 변경하면, 반환되는 일련번호가 일요일(1) ~ 월요일(7)로 변경됩니다.
- 달력 토/일요일의 서식 색상을 변경하면 아래와 같이 일요일부터 시작하는 달력이 간단하게 완성됩니다.
토/일요일의 글꼴 색상을 변경하여 일요일 시작하는 달력을 완성합니다.
Q2. 엑셀 연간 만년달력을 만들고 싶어요
달력 날짜계산에 사용된 함수를 복사/붙여넣기하면 연간달력 또한 손쉽게 만들 수 있습니다. 제작된 만년달력은 완성파일 달력(연간) 시트에 같이 첨부하여 드렸습니다.
날짜계산에 사용된 함수를 복사/붙여넣기 하면 연간달력또한 손쉽게 만들 수 있습니다.
= AND(MONTH(B6)$S$2),B6””) ->= AND((MONTH(B6)$S$2),B6””)
DATE 함수와 WEEKDAY 함수를 통한 계산만 맞다면, COLUMN 함수나 COUNT 함수를 사용하셔도 잘 동작하실거에요 ^_^*
질문이 있는데, 혹시 같은 날짜에 두개 이상의 일정이 있을 시에는,
동시에 이 두 일정을 표시하지 못하는것인가요?
엑셀 시트 상 더 상단에 작성된 일정만 표시가 되거든요.
혹시 방법이 있는지 궁금합니다.
달력시트의 셀도 마찬가지로 [자동 줄바꿈]을 활성화하시면 됩니다.^-^
좀 더 개선된 달력을 작성해서 업데이트해드릴 예정이니, 이후에 다시 방문해 주시면 더 나은 양식을 확인하실 수 있을겁니다.
제 답변이 도움이 되셨길 바랍니다.
감사합니다.
이전달/지난달에는 휴일여도 휴일을 표시안하고 싶으신거지요?
그럴 경우 예제파일의 B7셀을 기준으로 아래와 같이 수식을 변경해보시겠어요?^^
이후 다른 셀에도 복사/붙여넣기 하시면 됩니다.
제 답변이 도움이 되셨길 바랍니다.
감사합니다.^^*
한가지 옥에 티가 있어서요. 매년 12월일때 다음해 1월 달력이 3째주부터 나오질 않아서 나름 수정을 해보았습니다.
=IFERROR(IF((달력!$S$2+1)MONTH(N7+1),"",N7+1),"") 을 아래와 같이
=IFERROR(IF(DAY(N7+1)=DAY(1),"",IF(달력!$S$212,IF((달력!$S$2+1)=MONTH(N7),N7+1,""),IF((달력!$S$2-11)=MONTH(N7),N7+1,""))),"")
작동은 잘 되는데요(고민 많이 했습니다). 위 수식을 간략하게 줄일수 있을지요?
헛~ 수식에 오류가 있었습니다~~!^^;; 완성파일 수정해드렸으니 확인해보시겠어요?
수식은 아래와 같이 수정하시면 됩니다.
=IFERROR(IF(IF(('달력'!$S$2+1)>12,1,'달력(일-월)'!$S$2+1)MONTH(N33+1),"",N33+1),"")
글씨 굵은 부분이 수정된 부분입니다.
감사합니다!
강의 중에
=NOT(ISERROR(VLOOKUP(셀,휴일!$A:$A,1,0)))
함수를 사용하여 휴일을 빨갛게 표시하는 조건부 서식을 사용하셨는데
이때 왜
=VLOOKUP(셀,휴일!$A:$A,1,0)
함수를 사용하지 않으신건지 궁금합니다.
조건부서식은 결과가 TRUE 일때 해당 서식을 적용합니다.
을 입력할 경우, 해당 날짜가 결과값으로 반환됩니다. 따라서,
을 입력해서 VLOOKUP 결과가 오류가 아닐경우 TRUE 를 반환하도록 수식을 입력합니다 ^^
제 답변이 도움이 되셨길 바랍니다.
감사합니다.