엑셀 달력 자동화, 공휴일+일정까지 완벽 해결! | CALENDAR 함수 만들기
엑셀 달력 자동화 함수 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 달력 자동화, CALENDAR 함수 만들기예제파일[실무기초] 엑셀 달력 자동화, CALENDAR 함수 만들기완성파일
강의에 사용된 공식 빠르게 살펴보기
① M365 버전 : CALENDAR 함수 완성 공식
M365 버전 사용자는 아래 공식을 '이름관리자'에 붙여넣기 한 후, CALENDAR 함수를 엑셀에서 바로 사용해보세요!
오빠두Tip : 🚀 최신 버전의 CALENDAR 함수는 아래 링크에서 확인하실 수 있습니다.② 휴일과 일정을 동시에 표시하는 함수 공식 (영상 10분 45초)
아래 공식을 예제파일 [달력 만들기]시트의 B7셀에 붙여넣기하면 공휴일과 일정을 표시된 달력이 생성됩니다.
=IFERROR("["&VLOOKUP(SEQUENCE(6,7,F3),휴일목록,2,0)&"] ","")&TEXT(SEQUENCE(6,7,F3),"m/d")&CHAR(10)&IFERROR(VLOOKUP(SEQUENCE(6,7,F3),일정목록,2,0),"")M365 버전 : CALENDAR 함수 사용법
- CALENDAR 함수 등록하기 : 예제 파일의 [달력] 시트로 이동한 후, [수식] - [이름관리자] - [새로 만들기] 버튼을 클릭합니다.
- '새 이름' 대화상자가 실행되면, 아래와 같이 이름을 정의한 후 [확인] 버튼을 클릭하면 현재 실행 중인 통합문서에 CALENDAR 함수가 등록됩니다.
- CALENDAR 함수 사용하기 : 이름 관리자를 종료한 후, [달력] 시트 B7셀을 선택하고 "=CAL" 을 작성하면 등록된 CALENDAR 함수를 확인할 수 있습니다.
=CALENDAR(년도,월,[개월수],[공휴일표],[일정표],[날짜서식],[요일표시],[전후월표시],[월요일시작])
· 년도, 월 : 달력의 시작 년도와 월입니다.
· 개월수 : 표시할 개월 수 입니다.
· 공휴일표 : 달력에 표시할 공휴일 목록으로, '날짜 | 공휴일명' 형식의 2열로 작성된 표입니다.
· 일정표 : 달력에 표시할 일정으로, '날짜 | 시간 | 설명' 형식의 3열로 작성된 표입니다.
· 날짜서식 : 달력에 적용할 날짜 서식입니다. 기본값은 "m/d" 입니다.
· 요일표시 : 1일 경우 날짜에 요일을 표시합니다. 기본값은 표시안함(=0)입니다.
· 전후월표시 : 1일 경우 전/후월을 표시합니다. 기본값은 표시안함(=0)입니다.
· 월요일시작 : 1일 경우 달력이 월요일부터 시작합니다. 기본값은 일요일시작(=0)입니다.오빠두Tip : 셀 서식에 대한 자세한 설명은 「진짜쓰는 실무엑셀」 5분 기초 강의에서 자세히 정리했습니다.👇
- B7 셀에 아래 수식을 입력하면 선택한 년도, 월, 개월수, 공휴일, 일정이 반영된 달력이 완성됩니다.
=CALENDAR(B3,C3,D3,공휴일표[#모두],일정표[#모두],,,,E3)
엑셀 기본 달력 만들기 (SEQUENCE + WEEKDAY 함수)
- 달력 시작요일 만들기 : 예제파일에서 [달력만들기] 시트로 이동합니다. 첫번째로 선택한 년도와 월의 1일을 작성하겠습니다. D3셀에 아래 DATE 함수를 입력하면 선택한 년도와 월의 1일이 출력됩니다.
=DATE(B3,C3,1)
- 달력은 시작 월의 1일이 있는 주의 일요일(또는 월요일)부터 시작합니다. 따라서, 매월 1일을 포함하는 주의 시작일을 계산하겠습니다. E3셀을 선택한 후, 아래와 같이 WEEKDAY 함수를 작성하면 1일의 요일번호가 계산됩니다.
=WEEKDAY(D3)
[ISPAN]WEEKDAY 함수의 기본값은 1(일요일) ~ 7(토요일) 이며, 월요일시작 달력을 만들어야 할 경우 WEEKDAY 함수의 두번째 인수를 2로 사용합니다.[/ISPAN]
- 요일 번호를 계산했으므로, '달력 시작일'은 "=시작일(1일) - 요일번호 + 1"로 계산할 수 있습니다. F3셀에 아래 수식을 입력하면 선택한 년도/월의 달력 시작일이 계산됩니다.
=D3-E3+1
- 기본 달력 완성하기 : 마지막으로 달력의 시작셀인 B7셀에 SEQUENCE 함수를 입력하면 달력 시작요일부터 가로(7) X 세로(6) 으로 구성된 달력이 완성됩니다.
=SEQUENCE(6,7,F3)
'=SEQUENCE(행높이,[열너비],[시작값],[단계])오빠두Tip : 엑셀 2021 이후 버전부터 새롭게 추가된 '분산 범위'와 '동적 배열'은 「진짜쓰는 실무엑셀」 기초 강의에서 알기 쉽게 정리했습니다!👇
개인 일정과 공휴일을 출력하는 달력 만들기
- 이제 달력으로 만들어진 날짜를 일정표에서 VLOOKUP 함수로 검색하면 날짜와 일정을 동시에 표시하는 자동화 달력을 만들 수 있습니다. B7셀에 방금 작성한 함수를 아래 수식으로 변경합니다.
=SEQUENCE(6,7,F3)&CHAR(10)&VLOOKUP(SEQUENCE(6,7,F3),일정목록,2,0)
' ① SEQUENCE(6,7,F3) : 날짜 배열입니다.
' ② CHAR(10) : 줄바꿈입니다.
' ③ VLOOKUP(SEQUENCE(6,7,F3),일정목록,2,0) : 일정목록에서 일정을 검색 후 반환된 값입니다. - 위 수식을 입력하면, 일정이 없는 경우 #N/A 오류가 반환됩니다.
- 따라서, 일정을 검색하는 VLOOKUP 함수를 IFERROR 함수로 묶어서 오류가 발생할 경우 빈칸을 반환하도록 작성합니다.
=SEQUENCE(6,7,F3)&CHAR(10)&IFERROR(VLOOKUP(SEQUENCE(6,7,F3),일정목록,2,0),"")
' IFERROR(VLOOKUP(SEQUENCE(6,7,F3),일정목록,2,0),"")
: VLOOKUP 함수가 오류를 반환할 경우, 오류 대신 빈칸을 반환합니다.오빠두Tip : VLOOKUP 함수와 IFERROR 함수의 오류처리 방법은 아래 강의를 참고하세요!👇
- 마지막으로 TEXT 함수를 사용해 날짜의 표시형식을 "월/일"로 변경하면 날짜와 일정을 동시에 표시하는 자동화 달력이 완성됩니다.
=TEXT(SEQUENCE(6,7,F3),"m/d")&CHAR(10)&IFERROR(VLOOKUP(SEQUENCE(6,7,F3),일정목록[#모두],2,0),"")
' TEXT(SEQUENCE(6,7,F3),"m/d") : 날짜를 "월/일" 서식으로 표시합니다.
- 동일한 원리로 아래 공식을 사용하면 휴일과 일정을 동시에 표시하는 자동화 달력을 만들 수 있습니다. 아래 완성 공식을 참고하여 나만의 멋진 자동화 달력을 만들어보세요!
=IFERROR("["&VLOOKUP(SEQUENCE(6,7,F3),휴일목록,2,0)&"] ","")&TEXT(SEQUENCE(6,7,F3),"m/d")&CHAR(10)&IFERROR(VLOOKUP(SEQUENCE(6,7,F3),일정목록,2,0),"")
이전/이후 월을 숨기고 공휴일을 강조하는 조건부서식 적용하기
- 날짜범위 만들기 : 조건부서식을 사용하면 공휴일이거나 선택한 월이 아닌 날짜에 자동으로 서식을 적용할 수 있습니다. 하지만 이전 단계에서 생성한 달력은 순수한 날짜 형식이 아니기 때문에, 조건부서식을 직접 적용할 수 없습니다. 따라서, 조건부서식에 사용할 수 있는 순수한 날짜 범위를 별도로 생성해야 합니다.
- 달력 우측 비어있는 셀을 선택합니다. 이번 강의에서는 J7셀을 선택하겠습니다. 이후, 아래 SEQUENCE 함수를 입력하여 순수한 날짜 범위를 생성합니다.
=SEQUENCE(6,7,F3)오빠두Tip : 만약 SEQUENCE 함수의 결과가 숫자로 표시된다면, 범위를 선택 후 Ctrl + Shift + 3을 눌러 날짜 서식으로 변경합니다. 실무에 꼭 필요한 20개 필수 단축키는 아래 기초 강의를 참고하세요!👇
- 조건부서식 적용하기 : 조건부서식을 적용할 달력 범위를 선택한 후, [홈] 탭 - [조건부 서식] - [새 규칙]으로 이동합니다. [새 서식 규칙] 대화상자가 실행되면, 규칙 유형으로 마지막 항목인 '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 후, 아래 수식을 입력합니다.
=MONTH(J7)<>$C$3
'위 공식은 선택한 월이 아닌 날짜를 확인합니다. 셀 참조방식으로 동작하는 수식의 원리는 영상 강의를 참고해주세요!
오빠두Tip : 셀 참조방식을 활용하여 원하는 행/열에 조건부서식을 올바르게 적용하는 방법은 아래 「진짜쓰는 실무엑셀」 기초 강의에서 자세히 정리했습니다!👇
- [서식] 버튼을 클릭한 후, [글꼴] 탭에서 글꼴 색상으로 옅은 회색을 선택하고 [확인] 버튼을 클릭하여 조건부서식을 적용합니다.
- 아래 그림과 같이 이전/이후 월의 날짜가 회색 글꼴로 표시됩니다.
- 동일한 방법으로 조건부서식의 새 규칙으로 아래 수식을 적용하면, [일정] 시트에 작성된 공휴일을 참조하여, 공휴일일 경우 빨간색 글씨로 적용할 수 있습니다.
=COUNTIF(일정!$E$1:$E$13,J7)>0