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

엑셀(Excel)/Excel

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

 

환경: Microsoft Excel 2013

 

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

 

# 달력 샘플 파일 링크 :  엑셀_달력샘플.xlsx

 

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

 

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

 

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

 

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

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

 

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

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

 

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

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

 

¤ 달력 만들기

 

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

 

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

 

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

 

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

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

 

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

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

 

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

 

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

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

 

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

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

 

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

 

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

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

 

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

 

=MONTH(B5)<>$E$2

 

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

 

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

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

 

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

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

 

Posted by 녹두장군