엑셀 달력 자동화, 공휴일+일정까지 완벽 해결! | CALENDAR 함수 만들기
엑셀 기본 함수만으로 달력 만들기 자동화하는 방법! CALENDAR 함수 사용법부터, 함수 공식을 만드는 과정을 15분 총정리 강의로 확인하세요!🔥
이 강의에서는 엑셀의 SEQUENCE, WEEKDAY, VLOOKUP 함수를 조합해 년도와 월만 바꾸면 자동으로 갱신되는 달력을 만드는 방법을 다룹니다. 공휴일과 개인 일정을 한 화면에 표시하고, 조건부서식으로 이전·이후 월을 회색으로 흐리는 단계까지 정리해, 매년 새로 그릴 필요 없는 자동화 달력을 완성합니다.
실습자료를 준비했어요
수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇
목차 보기
강의에 사용된 공식 빠르게 살펴보기
① M365 버전 : CALENDAR 함수 완성 공식
M365 버전을 사용한다면 아래 공식을 '이름 관리자'에 그대로 붙여넣은 뒤, 엑셀에서 CALENDAR 함수를 바로 호출할 수 있습니다.
② 휴일과 일정을 동시에 표시하는 함수 공식 (영상 10분 45초)
아래 공식을 예제파일 [달력 만들기] 시트의 B7셀에 붙여넣으면 공휴일과 일정이 함께 표시되는 달력이 완성됩니다.
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
여러 개로 나누어서 입력한 일정을 모두 표시하려면, FILTER 함수 + MMULT(2차원 배열 계산) 함수 조합의 트릭을 사용해야 합니다. (공식이 많이 복잡합니다.)
FILTER 함수는 2차원 배열(x-y축)만 생성할 수 있고, 여러 일정을 필터링 할 경우 날짜(x축) + 여러일정(y축) + 달력(z축)으로 검색되어 올바른 결과를 반환하지 않게 됩니다.
FITLER + MMULT 함수 공식은 완성된 CALENDAR 함수에 적용되어 있으니 필요하실 경우 확인해보시길 바랍니다.
대안책은 2가지가 있습니다.
1. 일정 표에서 각 날짜별 여러 일정을 줄바꿈으로 나누어서 입력하는 방법
2. IF+TEXTJOIN 으로 날짜별 일정을 하나로 합친 보조 테이블을 만든 후, 보조 테이블에 VLOOKUP 함수로 검색하는 방법
(https://www.oppadu.com/%EC%97%91%EC%85%80-%ED%8A%B9%EC%A0%95-%EC%A1%B0%EA%B1%B4-%EB%A7%8C%EC%A1%B1%ED%95%98%EB%8A%94-%ED%85%8D%EC%8A%A4%ED%8A%B8-%ED%95%A9%EC%B9%98%EA%B8%B0/)
을 사용하면 보다 간단하게 여러 일정을 표시할 수 있습니다.
제시해드린 답변이 문제를 해결하시는데 도움이 되었길 바랍니다. 감사합니다.
오늘 날짜 배경색은 조건부서식을 다음과 같이 적용하면 됩니다.
감사합니다. :)
아쉽게도 이번 강의에서 소개해드린 내용은 2016 버전에서는 사용이 불가합니다.
엑셀 2016 버전을 사용 중이실 경우, 이전 강의에서 소개해드린 달력 만들기 강의를 한번 참고해보시겠어요? :)
엑셀 달력, 공휴일 및 일정 관리 자동달력/만년달력 만들기 - 오빠두엑셀 (oppadu.com)
감사합니다.
엑셀 함수로 글꼴을 굵게하거나 밑줄을 추가하는 것은 불가합니다.
함수 인수 중, "날짜 서식"을 변경해보시길 바랍니다.
예를 들어, "■yyyy-mm-dd■" 와 같이 강조할 수 있습니다.
남겨드린 답변이 문제를 해결하시는데 도움이 되었길 바랍니다. 감사합니다.
vlookup함수로 만들어서 최상단 일정만 표시되는거 같은데.. 해결법 부탁드리겠습니다..
관련하여 이전에 답변을 드린적이 있습니다.
아래 댓글을 한번 확인해보시겠어요? :)
https://www.oppadu.com/%ec%97%91%ec%85%80-%eb%8b%ac%eb%a0%a5-%ec%9e%90%eb%8f%99%ed%99%94/#comment-23583
감사합니다.
LAMBDA 함수는 엑셀 M365 에서만 제공되어 구글시트에서는 사용하실 수 없습니다. :)
감사합니다.
공휴일은 근무를 하지 않는 4조 2교대 근무를 보내주신 예제 파일에 추가하는 방법을 알고 싶습니다.
LAMBDA 함수는 M365 버전에서만 제공되는 함수여서, 2021 버전에서는 사용이 제한됩니다. :)