엑셀로 자동 달력 만들기(공휴일 적용하기_vlookup함수, 조건부 서식 활용)

앞의 글에서 엑셀로 자동 달력을 만들어 보았습니다.

앞에서 만든 달력에 vlookup함수와 조건부 서식을 활용하면 공휴일인 날짜의 색상을 

다른 색으로 표시되게 쉽게 만들 수 있습니다.

공휴일을 기록할 시트 만들기

 

먼저, 공휴일을 기록할 시트를 새로 추가합니다. 그리고 그림과 같이 공휴일을 기록해 줍니다.

공휴일

B열의 내용은 무슨 날인지 확인하기 위해 작성한 것일 뿐 별 의미는 없습니다.

혹, 달력에 무슨 날인지 표시하고 싶다면 달력의 디자인을 변경해서 사용할 수는 있습니다.

달력시트에 조건부서식 적용하기

 

달력시트에서 날짜가 표시되는 셀을 범위 선택한 후 조건부서식에서 새 규칙 선택,

▶ 수식을 사용하여 서식을 지정할 셀 결정을 선택한 후 수식에 다음과 같이 작성합니다.

 

  =NOT(ISERROR(VLOOKUP(B4,공휴일!$A:$A,1,0)))

 

수식을 작성한 후 서식 버튼을 누른 후 원하는 글꼴이나 글자 색상을 선택하신 후 확인버튼

누리시면 끝입니다.

조건부서식 예제그림

 

위 그림은 이미 서식이 적용 돼 있어 추석연휴 기간이 이미 빨간색으로 적용되어 있습니다.

 

참고사항으로, 날짜를 나타내는 셀 중 첫 번째 행에는 전월 마지막주 날짜값이 마지막행에는 익월

첫째 주 날짜값이 입력되어 있지만 글자색이 흰색이어서 보이지 않을 뿐이기 때문에 해당날짜에

공휴일이 포함되어 있다면 날짜가 위에서 지정한 서식이 반영되어 표시되는데 이 현상을 해결하는

방법으로는 조건부서식에서 규칙관리를 선택한 후 아래 그림과 같이 서식의 순서를 변경해 주시면

됩니다. 조건부서식에서 수식은 아래 수식부터 순차적으로 적용되기 때문에 위에서 작성한 수식이

아래로 내려가면 문제가 해결됩니다.

 

확인버튼 누르시면 끝입니다.

조건부서식 수식순서

VLOOKUP, ISERROR, NOT 함수 알아보기

VLOOKUP(찾을 값, 값을 찾을 범위, 반환할 값의 열 번호, 조건(찾을 값의 정확한 일치 여부))
VLOOKUP(B4,공휴일!$A:$A,1,0) - 위 수식

위의 수식을 참고해 설명을 드리면,

 

첫 번째 인수인 '찾을 값' B4는 날짜값을 의미합니다. VLOOKUP함수는 '찾을 값'을 범위중 첫 번째 열에서 찾습니다.

조건부서식에서 서식이 적용된 각 셀의 날짜값을 적용하기 위해 참조를 일반참조로 하였습니다.

 

두 번째 인수인 '값을 찾을 범위' 공휴일!$A:$A는 '공휴일'시트의 A열 전체를 의미합니다.

조건부서식에서 서식이 적용된 각 셀에 동일한 범위를 적용하기 위해 참조를 절대참조로 하였습니다.

 

세 번째 인수인 '반환할 값의 열 번호' 1은 첫 번째 열을 의미합니다.

범위의 첫 번째 열에서 B4에 해당하는 날짜와 동일한 값을 찾은 후 그 값이 있는 행의 첫 번째 열에 있는 값을

반환하라는 의미입니다.

 

네 번째 인수인 '조건' 0은 정확한 일치를 의미합니다. 조건으로는 1(TRUE)과 0(FALSE)이 있는데

1은 대략적인 일치를 나타내고, 0은 정확한 일치를 나타냅니다. 대략적인 일치는 숫자값을 찾을 때는 용이하지만

그 외에서는 거의 사용할 일이 없습니다.

 

그럼 VLOOKUP함수에서는 각 셀의 날짜를 가지고 공휴일 시트에서 같은 값이 있는지 찾을 것이고 찾는 값이

있다면 찾은 값을 반환하지만 찾는 값이 없다면 '에러(error)'를 반환합니다.

 

ISERROR(값)
ISERROR(VLOOKUP(B4,공휴일!$A:$A,1,0)) - 위 수식

ISERROR() 함수는 값의 오류 여부를 확인하여 오류여부에 따라 TRUE, FALSE를 반환합니다.

그렇다면 위 수식에서 VLOOKUP으로 값을 찾아서 값이 있다면 오류가 아니므로 FALSE를 반환하고,

찾는 값이 없다면 오류이므로 TRUE를 반환하게 됩니다.

 

NOT(값)
NOT(ISERROR(VLOOKUP(B4,공휴일!$A:$A,1,0))) - 위 수식

NOT() 함수는 값이 TRUE면 FALSE로 FALSE면 TRUE로 변환하는 함수입니다.

그럼 위 수식에서 찾는 값이 있을 때는 오류가 아니므로 ISERROR() 함수에서 FALSE를 반환하지만 NOT() 함수를 통해

TRUE로 변환되고 조건이 TRUE이므로 우리가 지정한 서식이 적용되는 원리입니다.

반대의 경우는 결과적으로 FALSE가 되어 서식이 적용되지 않겠지요.

 

이렇게 공휴일이 반영된 달력을 만들어 보았습니다. 제 설명이 필요하신 분들에게 도움이 되기를 바랍니다.

필요한 분들을 위해 원본파일도 첨부해서 올리겠습니다.

자동달력(공휴일반영).xlsx

0.01MB