달력 구조 이해하기
일정을 기록할 수 있는 달력이다. 달력은 크게 admin 시트와 main 시트로 나뉜다. admin은 달력 생성 코드를 포함한 달력에 필요한 모든 정보를 담은 시트다. 반면 main은 사용자가 직접 값을 수정하거나 admin에서 계산된 정보를 보여주는 공간이다. 이렇게 시트를 분리한 이유는 달력 원본을 보존하기 위해서이다. 달력에 직접 정보를 작성하면 달력이 훼손된다. 시트를 분리해 사용자가 수정할 수 없는 부분과 사용자가 수정 가능한 범위를 나누었다. 쉽게 backend와 frontend라고 생각하면 된다.
사전 지식
셀 서식 (yyyy-mm-dd)
셀을 우클릭하면 셀 서식을 볼 수 있다. 셀 서식 > 표시 형식은 데이터를 어떤 형식으로 표시할지에 대한 설정이다.
날짜 정보는 yyyy-mm-dd로 표시된다. yyyy는 년, mm은 월, dd는 일을 뜻한다.
| 표시 형식 | 결과 |
| yyyy-mm-dd (기본) | 2024-01-03 |
| yy-mm | 24-01 |
| yy년 mm월 | 24년 01월 |
| dd | 03 |
| d | 3 |
표와 같이 y, m, d를 조합해 다양한 형식으로 나타낼 수 있다.
날짜 관련 함수
=DATE(년, 월, 일) // 정수를 조합해 날짜 생성
=YEAR(날짜) // 날짜의 년도
=MONTH(날짜) // 날짜의 월
=DAY(날짜) // 날짜의 일
=WEEKDAY(날짜) // 날짜의 요일(1:일, 2:월 ... 7:토)
=TODAY() // 오늘 날짜달력을 만들기 위해 알아야 하는 함수다.
달력 특징
모든 월의 1일(첫날)은 일/월/화/수/목/금/토요일 중 하나다. 따라서 1일은 반드시 달력 첫 줄에 위치한다.
한 달은 최소 28일에서 최대 31일이다. 따라서 달력 크기는 7x6(=42)이다. 여기서 세로 7열은 요일이다. 1일이 토요일이면 1행은 한 칸만 사용할 수 있기 때문에 7x4(=28)에 위/아래 2행을 추가해 총 6행이 필요하다.
달력 구현
월 (MONTH)
달력 상단에 해당 월의 1일을 표시한다. 이 셀을 본 글에서 "제목"이라고 부르겠다. 제목은 이후 "mm" 형식으로 표시해 월을 나타내는 셀로 사용한다. 첫 달은 직접 "yyyy-mm-dd' 형식으로 작성하거나 입력 받은 시작일로부터 YEAR, MONTH를 추출해 만들 수 있다.
=DATE(YEAR(입력), MONTH(입력), 1) // 첫 달 제목다음 달은 EDATE로 나타낼 수 있다. 이전 달 제목을 첫 인자로 주고 두 번째 인자로 1을 준다.
=EDATE(이전_달, 1) // 다음 달 제목일 (DAY)
달력 내부는 7x6 크기로 크게 다섯 부분으로 나뉜다. 가이드 이미지를 보면 각 부분을 색으로 구분해뒀다. 셀은 이후 "d" 형식으로 표시해 일자만 노출한다. 본 글에서는 이해를 돕기 위해 "yyyy-mm-dd" 형식으로 두고 개발을 진행하겠다. 참고로 일요일부터 시작하는 달력 기준으로 설명하며 (행, 열) 표기를 사용한다.
달력 첫 줄에는 반드시 해당 월의 1일이 포함된다. 만약 첫날이 일요일(1)이면 (1, 1)에 들어간다. 이미지처럼 첫날이 금요일(6)이면 (1, 6)에 들어간다. 이 규칙을 이용하면 첫 줄에 들어갈 코드는 다음과 같다. 코드가 익숙한 사람이라면 아래 코드를 읽어보면 된다. 모르면 패스해도 된다.
firstDate // 제목(달력 상단에 표시된 1일)
previous // 식이 입력되는 이전 셀
nowWeekday // 현재 셀이 나타내는 요일. 예: (1, 3)이라면 nowWeekday==3.
cell // 현재 셀
// (1,1)
if (WEEKDAY(firstDate) == 1) {
cell = firstDate
} else {
cell = ""
}
// (1,2) ~ (1,7)
if (previous == "") {
if (WEEKDAY(firstDate) == nowWeekday) {
cell = firstDate
} else {
cell = ""
}
} else {
cell = previous + 1
}(1, 1)은 해당 월의 1일이 일요일이면 제목(1일)을 표시하고, 아니면 빈칸이 된다.
(1, 2)부터는 이전 셀에 값이 있다면 단순히 이전 셀 +1 하면 된다. 하지만 이전 셀이 빈칸이라면 (1, 1)과 같이 1일이 현재 요일에 해당하는지 확인해야 한다.
B6 =DATE(2024, 3, 1) // 제목(해당 월의 첫 날)
B8 =IF(WEEKDAY(B6)=1,B6,"") // (1, 1)셀
C8 =IF(B8="",IF(WEEKDAY(B6)=2,B6,""),B8+1)
D8 =IF(C8="",IF(WEEKDAY(B6)=3,B6,""),C8+1)
E8 =IF(D8="",IF(WEEKDAY(B6)=4,B6,""),D8+1)
// 생략...첫 줄이 완성됐다. 이제 이전 셀 +1을 통해 하루씩 늘려가면 된다.
B9 =H8+1 // (1, 2) 셀
C9 =B9+1
D9 =C9+1
// 생략달력은 7x6으로 총 42일이기 때문에 값을 채우다 보면 다음 달에 해당하는 날짜가 채워진다. 최악의 경우 (5, 1)부터 다음 달이 입력되는 현상이 나타난다.
(5, 1)부터 날짜가 현재 월에 해당하는지 검사해야 한다. 이번에도 코드가 편한 사람을 위해 코드도 적어두겠다.
// (5, 1)
if (DAY(previous + 1) == 1) {
cell = ""
} else {
cell = previous + 1
}
// (5, 2) ~ (6, 7)
if (previous == "") {
cell = ""
} else {
if (DAY(previous + 1) == 1) {
cell = ""
} else {
cell = previous + 1
}
}아이디어는 간단하다. 셀에 입력된 값이 1일이면 빈칸으로 표시한다. 만약 이전 셀이 빈칸이면 현재 셀도 빈칸으로 처리한다. 이전 셀에 값이 있다면 이전 셀 +1이 1일인지 검사해 준다. 이 방식을 이용하면 처음 1일만 찾아서 빈칸 처리하면 그 뒤 셀은 자연스럽게 빈칸으로 채워진다.
B12 =IF(DAY(H11+1)=1,"",H11+1) // (5, 1)
C12 =IF(B12="","",IF(DAY(B12+1)=1,"",B12+1))
D12 =IF(C12="","",IF(DAY(C12+1)=1,"",C12+1))달력이 완성됐다. 이제 상단에 제목을 표시한 뒤 7x6 셀을 복사&붙여넣기하면 다음 달 달력이 완성된다.
정리하면 아래와 같다.
표시 형식
글 초반에 이야기했지만 새로운 시트를 만들어 달력을 참조해 가져오기로 했다. 달력은 "admin" 시트에 만들었고 위 이미지에서 볼 수 있듯 B6에 제목이 있고, B8부터 7x6 달력이 위치한다. 따라서 아래와 같이 참조한다.
이렇게 하면 달력이 그대로 새 시트에 옮겨진다. 만약 셀에 참조가 지워져도 원본은 그대로 남아있기 때문에 다시 복원할 수 있다.
제목은 표시 형식을 "yy년 mm월" 또는 "yy.mm" 등 원하는 형태로 바꿔준다. 7x6 달력 내부도 "d"로 표시해준다. 그리고 취향껏 꾸며주면 된다.
부가 기능
D-day
="D"&TODAY()-종료일D-day는 종료일까지 얼마나 남았는지를 뜻한다. 따라서 오늘에서 종료일을 빼면 남은 날을 알 수 있다.
진행률 그래프
=(TODAY()-시작일)/(종료일-시작일)진행률을 전체에서 현재까지 얼마나 지났는지로 정의했다. 위 식은 0.0~1.0 사이 수로 계산되는데 표시 형식을 "0.00%"로 두면 백분율(%)로 나타낼 수 있다.
| 표시 형식 | 결과 |
| (일반) | 0.72719903 |
| 0% | 73% |
| 0.00% | 72.72% |
진행률을 막대그래프로 표현할 수 있다. 엑셀은 조건부 서식에서 막대 그래프 형태를 지원하지만 한셀은 지원하지 않는다. 따라서 조금 더 직접적인 방식으로 구현해 보겠다.
먼저 10칸짜리 셀을 색칠해서 준비해주고, 셀 위에 0.1, 0.2, 0.3 ... 1을 적어준다. 만약 퍼센트가 위에 적힌 구간을 넘어가면 아래 셀이 채워지는 식이다. 조건부 서식을 이용해 조건을 만족하면 셀 배경색을 바꾸는 식으로 구현해 보자.
수식 =$진행률>=구간
적용영역 =그래프_전체마지막으로 구간 부분을 배경색과 같은 색으로 만들어 숨겨주면 완성된다.
위 예시는 0.1 간격으로 채워지게 만들었지만 상황에 맞게 구간을 조절해 사용할 수도 있다.
스티커 & 개수 세기
스티커는 사용자가 달력에 일정을 표시하는 기능이다. 예를 들어 A, B, C, D라는 스티커(label)를 만들어 A가 휴가를 나갈 때 달력에 미리 표시하는 식으로 사용할 수 있다. 스티커는 미리 만들어 두고, 필요할 때 복사&붙여넣기를 통해 사용할 수 있다.
붙인 스티커를 지울 때는 달력 내 아무 셀이나 복사 후 붙여넣기하면 된다. 셀 값은 admin 시트를 참조하는 코드로 구성되어 있다. 그리고 엑셀은 셀 위치에 따라 자동으로 참조 값을 변경한다. 따라서 7x6 달력 내 값을 복사&붙여넣기하면 원래 달력으로 복구시킬 수 있다.
그리고 COUNTIF로 사용한 스티커를 셀 수 있다.
=COUNTIF(달력, 스티커)본 글에서는 달력 자동 생성과 기본적인 부가 기능에 대해 소개했다. main 페이지는 사용자가 수정하면 안 되는 셀에 셀 잠금 기능을 활용할 수 있다. 셀 잠금을 통해 수식이 깨지지 않도록 보호한다.