엑셀 Excel 달력 만드는 방법, 날짜 관련 함수 이용

엑셀로 자신만의 달력을 만들어 보겠습니다. 괜찮은 일정관리 툴이 많아서 엑셀을 사용하지 않겠지만 어떻게 적성하는지 공부해 두면 많은 도움이 될 겁니다. 달력을 만들면서 들어간 기능들은 “이름 정의”, “데이터 유효성 검사”, “사용자 지정 서식”, “새 서식 규칙” 등 입니다. 엑셀의 주요 기능들을 한 번에 연습해 볼 수 있습니다.

 

 

# 달력 샘플 파일 링크 : 

엑셀_달력샘플.xlsx

다운로드

 

 

¤ 년도와 월 선택을 위한 목록 만들기

 

 

▼ 먼저 “이름 정의” 기능을 이용해서 달력을 선택할 수 있는 년도와 월 콤보박스를 만들어 보겠습니다. 개발 도구에서 컨트롤을 추가해도 되지만 이름 정의를 사용하시면 간단하게 만들 수 있고 관리도 쉽습니다. 이름 정의를 하기 전에 콤보박스에 들어갈 데이터를 만들어야 합니다. 년도와 월 데이터를 다른 시트에 만듭니다. 그리고 [수식] 탭의 [정의된 이름] 그룹으로 가서 [이름 정의] 리본메뉴를 선택합니다.

 

▼ 이름 관리자 창이 뜨면 년도와 월에 해당하는 영역을 선택한 뒤 [새로 만들기] 버튼을 클릭해서 두 개의 이름을 만듭니다.

 

▼ 다음은 달력이 만들어 질 시트로 갑니다. 콤보박스를 만들기 위해 [데이터] 탭의 [데이터 도구] 그룹으로 가서 [데이터 유효성 검사] 리본메뉴를 선택합니다. 그럼 [데이터 유효성] 설정을 위한 팝업창이 뜹니다. 팝업창에서 [제한 대상]을 [목록]으로 선택하고 [원본] 값에 위에서 만든 이름을 입력합니다.

 

▼ 아래 그림처럼 원본 값에 이름 정의로 만든 이름을 넣으면 데이터 값이 모두 들어간 콤보박스가 만들어 집니다. 콤보박스의 화살표는 셀을 선택하면 나타나고 포커스 다른 곳으로 가면 사라집니다. 이렇게 년도와 월 값을 선택하면 아래 표는 해당하는 달력의 날짜를 표시하는 것입니다.

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 새해 달력 쉽게 만들기 ▶ 엑셀 Excel 지정한 달 마지막 날짜 EOMONTH 함수 사용하기 ▶ 엑셀(Excel) 날짜를 보고 해당 연도가 윤년인지 알아내는 방법 ▶ 엑셀 Excel 함수 MATCH, INDEX 를 이용해서 행, 열에 일치 데이터 찾기 ▶ 엑셀 디데이 D-DAY 계산하는 방법 – 날짜 함수 이용

 

 

 

¤ 달력 만들기

 

 

▼ 그럼 년도와 월을 선택했을 때 표에 데이터를 표시하기 위한 수식을 만들어 보겠습니다. 함수는 두 가지가 사용되었습니다. DATE 는 년도와 월, 일 값을 넣으면 날짜 형식을 리턴 합니다. WEEKDAY 는 요일값을 리턴 하는데 해당 년도와 월의 1일 에서 빼 주면 일요일의 날짜를 구할 수 있습니다. 달력의 첫 셀은 일요일 부터 이기 때문에 1일이 무슨 요일인지 알면 날짜 연산에 의해서 일요일 날짜가 구해 지는 것입니다. 여기에 끝에 1을 더하는 것은 요일 값이 하나 모자라기 때문입니다. 이렇게 첫 셀을 만들면 나머지는 쉽게 연산이 가능합니다. 날짜를 더 해주기만 하면 되거든요. 

=DATE(B2,E2,1)-WEEKDAY(DATE(B2,E2,1)) + 1

 

 

 

▼ 첫 번째 일요일에 해당하는 셀이 구해 지고 나면 해당 셀을 기준으로 날짜를 더해 갑니다. 날짜 연산이 되기 때문에 계속해서 1 씩 더해 가면 자동으로 월과 년도가 계산이 됩니다.

 

▼ 가로에 값을 채웠다면 새로도 드래그해서 값을 채웁니다. 그렇게 해서 모든 셀에 날짜를 채워 넣습니다. 그리고 상단에 년도와 월을 변경하게 되면 아래 표의 값은 자동으로 변경이 됩니다.

 

¤ 사용자 정의 서식으로 일자만 추출하기

 

 

▼ 이제 년도, 월, 일 이 모두 표현된 날짜 형식을 “일”만 보일 수 있도록 변경해 보겠습니다. Ctrl + 1 을 눌러 [셀 서식] 대화상자를 띄웁니다. 그리고 사용자 지정 서식으로 가서 형식을 “d” 로 바꿉니다. 잘 아시겠지만 년도는 “yyyy”, 월은 “mm”, 일은 “d” 로 표기합니다.

 

▼ 그럼 아래 그림과 같이 선택한 영역의 날짜는 “일”로 전부 변경이 됩니다. 마지막으로 정말 달력처럼 보이려면 해당 년도와 월에 날짜가 아니면 흐리게 표시해야겠죠.

 

¤ 조건부 서식으로 해당 월에 날짜가 아닐 때 흐리게 표시와 토,일 색 구분

 

 

▼ 해당 년도와 월에 날짜가 아니면 흐리게 표시하기 위해 [조건부 서식]을 사용하였습니다. 규칙을 만들기 위해 [홈] 탭의 [조건부 서식] 리본메뉴를 클릭해서 [새 규칙]을 선택합니다.

 

▼ [새 서식 규칙] 대화상자에서 유형을 “수식을 사용하여 서식을 지정할 셀 결정”으로 선택하고 아래에 식을 입력합니다. 식에서 쓰인 MONTH 함수는 해당 날짜의 월 값을 추출해 줍니다. 상단에서 선택한 월 값과 비교해서 같지 않으면 “<>” 지정한 서식으로 변경하는 것입니다. 그러니까 아래 수식이 참이 되면 서식이 적용되는 것이죠. 그리고 월을 나타내는 $E$2 는 변하지 않는 위치이기 때문에 절대값을 적용한 것입니다.

 

=MONTH(B5)<>$E$2

 

 

▼ 위에서 만든 규칙이 참일 때 날짜 값에 적용할 서식을 지정합니다. 흐리게 표시하기 위해서 색을 회색으로 지정했습니다.

 

▼ 서식을 지정하고 새로운 규칙을 달력에 적용하시면 아래 그림과 같이 선택한 년도와 월에 날짜가 아니면 흐리게 표시 됩니다.

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 새해 달력 쉽게 만들기 ▶ 엑셀 Excel 지정한 달 마지막 날짜 EOMONTH 함수 사용하기 ▶ 엑셀(Excel) 날짜를 보고 해당 연도가 윤년인지 알아내는 방법 ▶ 엑셀 Excel 함수 MATCH, INDEX 를 이용해서 행, 열에 일치 데이터 찾기 ▶ 엑셀 디데이 D-DAY 계산하는 방법 – 날짜 함수 이용