엑셀로 자신만의 달력을 만들어 보겠습니다. 괜찮은 일정관리 툴이 많아서 엑셀을 사용하지 않겠지만 어떻게 적성하는지 공부해 두면 많은 도움이 될 겁니다. 달력을 만들면서 들어간 기능들은 “이름 정의”, “데이터 유효성 검사”, “사용자 지정 서식”, “새 서식 규칙” 등 입니다. 엑셀의 주요 기능들을 한 번에 연습해 볼 수 있습니다.
# 달력 샘플 파일 링크 :
¤ 년도와 월 선택을 위한 목록 만들기 |
▼ 먼저 “이름 정의” 기능을 이용해서 달력을 선택할 수 있는 년도와 월 콤보박스를 만들어 보겠습니다. 개발 도구에서 컨트롤을 추가해도 되지만 이름 정의를 사용하시면 간단하게 만들 수 있고 관리도 쉽습니다. 이름 정의를 하기 전에 콤보박스에 들어갈 데이터를 만들어야 합니다. 년도와 월 데이터를 다른 시트에 만듭니다. 그리고 [수식] 탭의 [정의된 이름] 그룹으로 가서 [이름 정의] 리본메뉴를 선택합니다.
▼ 이름 관리자 창이 뜨면 년도와 월에 해당하는 영역을 선택한 뒤 [새로 만들기] 버튼을 클릭해서 두 개의 이름을 만듭니다.
▼ 다음은 달력이 만들어 질 시트로 갑니다. 콤보박스를 만들기 위해 [데이터] 탭의 [데이터 도구] 그룹으로 가서 [데이터 유효성 검사] 리본메뉴를 선택합니다. 그럼 [데이터 유효성] 설정을 위한 팝업창이 뜹니다. 팝업창에서 [제한 대상]을 [목록]으로 선택하고 [원본] 값에 위에서 만든 이름을 입력합니다.
▼ 아래 그림처럼 원본 값에 이름 정의로 만든 이름을 넣으면 데이터 값이 모두 들어간 콤보박스가 만들어 집니다. 콤보박스의 화살표는 셀을 선택하면 나타나고 포커스 다른 곳으로 가면 사라집니다. 이렇게 년도와 월 값을 선택하면 아래 표는 해당하는 달력의 날짜를 표시하는 것입니다.
※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 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 계산하는 방법 – 날짜 함수 이용 |
댓글을 달아 주세요
배움 2017.07.31 15:56 댓글주소 수정/삭제 댓글쓰기
잘 배우고 갑니다. 요긴하게 잘 쓸것같아요.
네 감사합니다. ^^
전성욱 2017.08.31 08:18 댓글주소 수정/삭제 댓글쓰기
안녕하세요 다름이 아니오라 엑셀 달력 만들기 하실때 일요 = DATE(2017,8,1) - WEEKDAY(DATE(2017,8,1),1) + 1 여기서 1를 더하는 이유가 머에요?
이호승 2018.03.06 16:13 댓글주소 수정/삭제 댓글쓰기
와.... 설명은 진짜 잘해주신거 같은데 안만들어져요 굉장히 어렵네요
엑셀 완전 초보는 따라하기 힘들것 같아요 ㅜㅜ 저 달력 성공하면
고대로의 모양에다가 밑에 한칸씩 더 넣어서 pc 다이어리로 쓸려고했는데
실패했네요 ㅠㅠ 년도, 월 은 되는데 날짜함수 데이트랑 위크데이 넣는데 안되요
엑셀 2013입니다
tj 2018.07.14 13:02 댓글주소 수정/삭제 댓글쓰기
근데 요일 토요일 하고 일요일 색상변경 설명도 ㅠㅠ
ㅂㄷㄱ 2018.08.29 09:45 댓글주소 수정/삭제 댓글쓰기
덕분에 정말 멋지게 하나 만들었습니다. 감사해요!!!
네 감사합니다..^^
송경근 2019.04.29 16:32 댓글주소 수정/삭제 댓글쓰기
항상 고개가 숙여 집니다.
또, 감사히 배우고 갑니다.
감사합니다.
여우 2021.06.22 15:11 댓글주소 수정/삭제 댓글쓰기
상세 설명 해주셔서 깔끔하게 달력 만들 수 있겠네요
감사합니다
레몬2 2022.04.26 09:00 댓글주소 수정/삭제 댓글쓰기
엑셀에 기준날짜를 두고 예를들면 4숼26일 기준 2달 뒤 ㅡ렇게 세팅이 될까요?(에를들면 2달후 6월26일) 이방법을 찾아 헤매는 중밉니다.ㅠ 도와주세요
4월26일에 2달을 더해서 날짜를 구할 수 있으면 되나요? EDATE 함수 사용해 보세요.