엑셀을 활용한 언제나 사용할 수 있는 자동 달력 만들기
엑셀을 활용한 언제나 사용할 수 있는 자동 달력 만들기
엑셀을 사용하여 언제든지 나의 취향에 맞게 사용할 수 있는 자동 달력 만드는 방법을 안내하겠습니다. 복잡해 보일 수 있지만, 몇 가지 간단한 단계를 따라하면 누구나 쉽게 자신만의 맞춤 달력을 만들 수 있습니다.
본 글에서 사용하는 함수는 다음과 같습니다. 주요 기능 함수는 WEEKDAY 함수입니다.
- DATE
- WEEKDAY
- VLOOKUP
- TEXT 함수를 사용합니다.
달력 작성
위의 예시 이미지와 같은 달력을 만들어 보겠습니다.
위에 예시된 달력의 날짜는 2024년 3월이며 요일은 일요일로 시작합니다.
달력 구성
위의 예시 달력 ① 번 표시 영역(셀범위:B1:H15)이 달력 영역입니다.
- 달력의 첫 번째 및 두번째 행은 작성되는 달력의 연,월이며 수식으로 표시하겠습니다.
- 달력의 세 번째 행은 요일 표시행입니다. 일주일에 해당되는 7개의 컬럼으로 구성되며 요일 표시는 수식으로 표시하겠습니다.
- 다음행은 날짜 표시 행이며 일주일에 해당하는 7개의 컬럼으로 구성됩니다. 날짜는 수식으로 표시합니다.
- 다음행은 바로 위의 셀인 날짜에 대한 공휴일 표시행이며 수식으로 표시합니다.
- 주일은 해당월의 1일의 요일에 따라 최대 6주가 될 수 있습니다.
따라서 주별 행은 총 12개의 행으로 표시됩니다.
2024년 3월은 1일이 금요일 마지막 날짜 31일은 일요일입니다. 총 6주일이 됩니다.
달력 기본 값 지정(연,월,주 시작요일)
- 위의 예시 달력 ② 번 표시 영역(셀범위:J2:K15)에서 만들려는 달력의 기본 값을 지정합니다.
- 월의 입력은 드롭다운 목록으로 입력하겠습니다.
데이터 탭> 리본 메뉴>데이터 도구>데이터 유효성 검사를 선택합니다. 이어 나온 데이터 유효성 검사 창에서- 제한 대상: 드롭다운 메뉴에서 목록을 선택합니다.
- 원본: 1~12의 숫자를 쉼표로 구분하여입력합니다.(1,2,3,4,5,6,7,8,9,10,11,12)
- 시작요일은 아래 요일 반환옵션 표의 요일 컬럼의 값을 목록으로 드롭다운 형식으로 입력되도록 만들었습니다.
아래의 표 영역은 요일과 그에 해당하는 WEEKDAY 함수의 반환옵션을 보여줍니다. - 시작요일선택 드롭다운 목록
데이터 탭> 리본 메뉴>데이터 도구>데이터 유효성 검사를 선택합니다. 이어 나온 데이터 유효성 검사 창에서- 제한 대상:목록을 선택합니다.
- 원본: =$J$9:$J$15 (2번영역 요일 컬럼 영역 범위 지정: "일요일~토요일")
을 설정하고 확인 버튼을 클릭하면 시작요일을 드롭다운으로 선택하게 됩니다. 월요일을 선택하면 달력의 주일은 월요일부터 시작합니다.
달력 상단 연, 월 표시
- B2셀에 ② 번 영역에서 지정한 월을 표시합니다.
=TEXT(DATE($K$4,$K$5,1),"m월") - C2셀 에 ② 번 영역에서 지정한 연도를 표시합니다.
=TEXT(DATE($K$4,$K$5,1),"yyyy년") - 글씨 크기 색을 지정할 수도 있습니다.
월 표시는 글꼴를 크게하고 B1셀 B2셀을 병합하고 가운데 맟춤을 적용했으며 연, 월의 글꼴 색은 빨강으로 표시했습니다.
요일 표시
달력의 시작행 요일 표시 행의 요일 표시는 다음 행에 표시될 첫째 주 날짜로 text 함수로 표시합니다. 달력의 날짜는 B4셀부터 시작합니다.=TEXT(B4,"aaaa")
aaa는 한글 표기이고(일요일) 짧은 요일 표기는 aaa(일)입니다.
dddd는 영문 표기(Sunday) 짧은 표기는 ddd(Sun)입니다.
엑셀 도움말 (날짜를 요일로 표시)
날짜 채우기
달력 작성의 주요 기능 함수는 WEEKDAY 함수입니다.
WEEKDAY 함수
WEEKDAY 함수는 특정 날짜의 요일을 구하는 함수이며 WEEKDAY 함수 구문은 아래와같습니다. 기본적으로 1~7까지의 숫자를 반환하며 요일 옵션에 따라 반환값이 다릅니다.
WEEKDAY(날짜,반환옵션)
- 날짜는 기본 날짜 서식의 데이터입니다.
- 반환옵션에 따른 반환 값은 아래의 표와 같습니다.
| 반환옵션 | 반환되는 값 |
| 1 또는 생략 | 1(일요일)에서 7(토요일) 사이의 숫자 |
| 2 | 1(월요일)에서 7(일요일) 사이의 숫자 |
| 3 | 0(월요일)에서 6(일요일) 사이의 숫자 |
| 11 | 1(월요일)에서 7(일요일) 사이의 숫자 |
| 12 | 1(화요일)에서 7(월요일) 사이의 숫자 |
| 13 | 1(수요일)에서 7(화요일) 사이의 숫자 |
| 14 | 1(목요일)에서 7(수요일) 사이의 숫자 |
| 15 | 1(금요일)에서 7(목요일) 사이의 숫자 |
| 16 | 1(토요일)에서 7(금요일) 사이의 숫자 |
| 17 | 1(일요일)에서 7(토요일) 사이의 숫자 |
달력 만들기 핵심은 해당 월의 1일의 요일을 구하는 것입니다.
- 특정 날짜의 요일은 위에서 설명한 WEEKDAY 함수로 구합니다.
- 반환되는 값은 요일 번호이며 옵션에 따라 1~7의 숫자입니다.
- 예시된 달력의 1일은 2024년 3월 1일입니다. 시작요일을 일요일(반환옵션: 1)로 했을 때 WEEKDAY 함수는 6을 반환합니다. 반환 값 6은 금요일입니다. 일요일과의 날짜 차이는 5일입니다. 첫째 주의 시작요일(여기서는 일요일)의 날짜는 2024년 3월 1일에서 WEEKDAY 함수 반환되는 요일 번호 6을 빼고 1을 더해 줍니다.
- 아래는 WEEKDAY 함수로 달력의 해당 월 1일의 요일옵션에 대한 반환값을 구하는 수식입니다.
=WEEKDAY(DATE($K$4,$K$5,1),VLOOKUP($K$6,$J$9:$K$15,2,0))
날짜는 DATE 함수로 반환옵션(return type)은 요일/반환옵션표(J9:K15)에서 VLOOKUP 함수로 가져옵니다.- 해당 달력 월 1일은 ② 번 영역의 연 입력셀(K4), 월 입력셀(K5)의 값과 숫자 1로 DATE 함수로 구합니다.
=DATE($K$4,$K$5,1) - VLOOKUP 함수를 사용하여 ② 번 영역의 요일 영역 범위(J9:K15)에서 ② 번 영역의 시작요일 입력셀(K6)의 값에 대한 반환옵션을 가져옵니다.
- 해당 달력 월 1일은 ② 번 영역의 연 입력셀(K4), 월 입력셀(K5)의 값과 숫자 1로 DATE 함수로 구합니다.
첫째주 날짜 채우기
- 달력에 표시될 첫째주 날짜(B4 셀)에 아래의 수식을 입력합니다.
=DATE($K$4,$K$5,1)-WEEKDAY(DATE($K$4,$K$5,1),VLOOKUP($K$6,J9:K15,2,0))+1
월의 1일 날짜 (2024-03-01)- WEEKDAY 함수 반환값(6)+1의 결과 2024-02-25이 나옵니다. - 다음 셀(C4)에 수식
=B4+1
를 입력하여 다음 날짜가 나오게 합니다. C4셀을 마지막 요일 셀(H4)까지 복사합니다.
둘째 주 날짜 채우기
- 첫째 주의 다음 행은 공휴일 표시 행으로 비워두고 다음 행의 첫 셀(B6)에 첫째 주 마지막 셀(H4)에 1을 플러스합니다.
=H4+1 - 첫째 주 둘째 요일부터 마지막 요일의 셀범위(C4:H4)를 선택하고 둘째 주 둘째 요일부터 마지막 요일의 셀범위(C6:H6)까지 복사합니다.
셋째 주 이후 날짜 채우기
- 공휴일 표시 행은 비워 두고 두번째 주 셀 범위 전체(C6:H6)를 셋째 주( (B10:H10) )및 마지막 주 셀 범위(B14:H14)까지 복사합니다.
공휴일 표시
첫째 주 날짜 표시 행 밑의 행에 공휴일을 표시하겠습다. 시트 하나를 추가하고 시트명을 휴일로 변경합니다.
기본 대한민국 공휴일로 날짜가 고정된 휴일만 기재했고 휴일 표시 영역 A1:B10영역을 Ctrl+T를 눌러 테이블로 만들었습니다. 테이블로 목록을 만들면 데이터 추가, 삭제 시 자동으로 목록이 변동합니다.(음력 및 임시 공휴일은 일단 뺐습니다. 복잡한 공정이 필요한듯?)
- VLOOKUP 함수로 공휴일 표시 첫 번째 셀(B5) 위의 날짜(B4)에 대한 공휴일 표시를 불러옵니다. B5셀에 아래의 수식을 입력합니다.
=IFERROR(VLOOKUP(CONCATENATE(MONTH(B4),"월",DAY(B4),"일"),휴일!$A$2:$B$10,2,0),"")
휴일 테이블의 날짜 표시가 3월1일 형태입니다. 달력 영역의 날짜 셀의 날짜의 월(MONTH), 일(DAY)에 문자 월, 일을 CONCATENATE 함수로 결합해서 휴일 테이블의 날짜를 참조했습니다. - 공휴일 표시 첫 번째 셀(B5)을 마지막 요일 셀범위(C5:H5)까지 복사합니다.
- 위에 표시한 행을 마지막 주일 공휴일 표시 행까지 복사합니다.(공휴일 표시 행만)
- 복사된 수식은 "일치하지 않는 수식" 오류 경고가 나옵니다. 윗셀 참조 부분인 것 같습니다. 오류 무시 또는 파일 탭>옵션>수식에서 오류 검사 규칙 중 한 영역에서 다른 수식이 사용된 셀 표시 선택을 해제하면 오류 경고는 없어집니다.
지금까지 작성된 달력
지금까지의 경로로 만들어진 달력은 위와 같습니다. 이제 달력의 형태를 변경할 필요가 있습니다.
- 날짜 서식 변경
날짜 셀의 서식 yyyy-mm-dd 형태를 d로 사용자 지정 서식을 변경합니다.
콘트롤(Ctrl) 키와 드래그 방식으로 전체 날짜 셀을 선택하고 사용자 지정 서식에서 “yyyy-mm-dd” 인 날짜 서식을 “d”로 변경합니다.
날짜 값 2024-03-01이 1로 변경 표시됩니다. - 요일 표시 셀과 날짜 셀의 크기와 글꼴 스타일, 크기, 색을 적당히 변경합니다.
조건부 서식 적용
조건부 서식 참조 (수식을 사용하여 지정할 셀 결정을 선택 부분 참조)
위의 달력을 보면 첫째 주를 보면 전월(여기서는 2월)이 포함돼있고 마지막 주에는 다음 달(여기서는 4월)의 날짜가 포함돼있습니다. 또 일요일과 토요일의 날짜의 글꼴 색도 변경할 필요가 있습니다. 조건부 서식을 적용해서 변경할 셀의 서식을 변경합니다.
달력 영역
- 달력 영역 범위 선택(B4:H14)를 선택합니다.
- 홈탭>스타일 도구>조건부 서식 메뉴>새 규칙에서 수식을 사용하여 지정할 셀 결정을 선택 수식으로 조건을 작성합니다.
- 일요일 글자 색 변경
수식 입력 상자에 수식=WEEKDAY(B4)=1을 입력합니다.
서식(F) 버튼을 눌러 셀 서식 창에서 글꼴 색을 빨강으로 지정합니다 - 토요일 글자 색 변경
수식 입력 상자에 수식=WEEKDAY(B4)=7을 입력합니다.
서식(F) 버튼을 눌러 셀 서식 창에서 글꼴 색을 파랑으로 지정합니다 - .해당월이 아닌 날짜 숨기기
수식 입력 상자에=MONTH(B4)<>$K$5를 입력하여 해당월 확인을 합니다. 서식(F) 버튼을 눌러 셀 서식 창에서 글꼴 색을 흰색으로 지정합니다 그러면 다른 달(여기서는 2월과 4월)의 날짜는 보이지 않게 됩니다. 필요하면 다른 서식으로 구분할 수도 있겠습니다. - 해당월이 아닌 공휴일 표시 숨기기
수식 입력 상자에 아래 수식을 입력합니다.=AND(ISNUMBER(B3),MONTH(B3)<>$K$5)
공휴일 셀의 날짜는 위의 셀입니다. 참조셀이 B4가 아닌 B3입니다. 위의 셀이 날짜가 아닌 경우가 있으므로 ISNUMBER 함수로 숫자 확인 후 해당 월 확인을 합니다. 서식(F) 버튼을 눌러 셀 서식 창에서 글꼴 색을 흰색으로 지정합니다 그러면 다른 달(여기서는 2월과 4월)의 표시는 보이지 않게 됩니다. 필요하면 다른 서식으로 구분할 수도 있겠습니다.
요일 표시행
- 요일 표시행 범위(B3:H3)를 선택합니다.
- 일요일 글꼴색 지정
조건부 서식 수식에=B3="일요일"을 입력하고 서식 글꼴을 빨강으로 지정합니다. - 토요일 글꼴색 지정
조건부 서식 수식에=B3="토요일"을 입력하고 서식 글꼴을 파랑으로 지정합니다.
이상의 조건부 서식은 날짜 셀과 공휴일 셀의 데이터 형식이 다르기때문에 경우에 따라 수식 결과가 오류가 납니다. 조건부 서식에서는 수식 결과 오류일 경우 조건부 서식이 적용되지 않습니다. 따라서 이상의 조건부 서식은 의도대로 적용됩니다.
조건부 서식의 규칙 적용 우선 순위
조건부 서식 규칙 적용은 나중에 만든 규칙이 우선 순위를 갖습니다. 해당월이 아닌 날짜 숨기기 규칙은 토요일 일요일 글꼴 색 규칙과 중복되는 부분이 있습니다. 해당월이 아닌 날짜 숨기기 규칙은 최 우선 순위를 갖기 위해 가장 나중에 만듭니다. 또는 규칙 관리 창에서 순위를 변경할 수 있습니다.
아래는 조건부 서식 규칙 관리 창 이미지입니다. 위에서 만든 6개의 규칙이 표시돼 있습니다.
이상으로 엑셀 자동 달력 만들기에 대 알아보았습니다. 위에서 만든 달력 파일을 아래와 같이 첨부합니다. 필요하시면 다운로드하셔서 참고하시기 바랍니다. 서두에 간단한 단계라고 했지만 조금은 복잡한 듯합니다.