구글시트 날짜 자동 변경 구성
Jira WBS 뷰어 만들기 - 구글시트 #2
구글시트 화면을 구성할 때 자주 기준 날짜를 변경해야 한다면?
통계를 매일 업데이트 해서 공유를 하는데 "최근 3주"의 자료가 보여져야 하는 경우를 생각해 봅시다. 보여져야 하는 자료가 그리 많지 않다면 통계 수치를 입력하는 작업 시점에 적절히 보여지는 자료를 함께 수정해도 시간이 많이 걸리지 않을 것입니다.
하지만 조금만 살펴보면 구글시트의 기능으로 자동화를 할 수 있는 부분이 많은데요, 예시를 통해 설명을 드리겠습니다.
핵심은 기준 날짜를 수식으로 만드는 것예시에서 보여지는 자료 범위는 최근 3주라는 기간이지만, 정확한 3주의 폭을 가지는 것이 아닌 3주전 월요일 기준으로 어제까지의 자료를 보여주는 구성으로 되어 있습니다. 매일 자동으로 날짜 계산이 되기 위해 today()를 사용하는 것에 더해서, 요일 단위의 계산을 위해 weekday()를 함께 사용하면 위와 같은 동작이 가능하게 됩니다. 이렇게 해두고 아래 통계 수치들은 표시된 날짜 기준으로 자료를 가져오도록 만들면 되겠죠.
12->19 로 변경해 본 화면그리고 당연한 것이지만 기준 날짜 수식에서 12를 19로 바꾸면 4주전 월요일을 기준으로 정보가 보이는 것을 확인할 수 있을 것입니다.
세부적으로 각 시트의 값들에 적용되는 수식 몇가지를 설명해 보겠습니다.
주요 수식 3가지 샘플 캡처[ L11 ] 날짜 기준값 이후에 3주간의 자료는 (기준날짜+1), 그리고 그 옆인 M11 은 마찬가지로 L11+1 같이 구성하면 날짜 영역은 모두 자동으로 구성이 됩니다.
[K12 ] 통계 자료는 '통계raw' 라는 시트에서 vlookup 으로 가져오게 됩니다. 자료가 없는 경우도 있기 때문에 iferror 구문을 함께 사용하면 좋습니다.
[K10] 시트를 보는 사람에게 오늘 날짜를 보여주면 좋겠다고 생각해서 부가적으로 "⬇today"라는 표시를 해줍니다.
통계raw 시트자료를 채워 넣는 곳은 별도로 구성
> 우측 '통계raw' 시트 캡처 참고
수작업일수도 있겠고, 다른 방법으로 자동으로 매일 한줄씩 추가가 되도록 구성이 되어도 됨
사실 예전글 누적 조회수 2천 돌파 알림을 보고, 시트 기능 변경된 것을 정리할 겸 이번 글을 쓰기 시작한 것이기도 한데요, 오래전에 소개를 드렸던 "Jira WBS 뷰어 만들기 - 구글시트"도 이런 기능을 동일하게 활용해서 확장한 부분을 추가로 설명 드리겠습니다.
예전 글 링크 : https://brunch.co.kr/@muyeolhong/18
그리고 긴 글을 읽지 않을 분들을 위해 Jira Viewer 구현 부분을 직접 보실 수 있게 복잡한 부분을 제외한 샘플 시트를 만들었으니 참고하세요.
https://myhong.link/brunch.32.sheet.jiraviewer
예전 글에서는 날짜 부분을 주기적으로 직접 수정하는 형태로 소개를 했는데요, 오늘 소개한 수식을 활용하면 고정된 폭을 기준으로 자동으로 날짜가 옮겨지면서 보여지게 할 수 있습니다.
참고로 아래 캡처 화면에 주요 기능은 1열과 2열에 구성이 되어 있지만, 실제 사용시에는 보여질 정보는 아니기 때문에 숨김 처리를 해두어도 됩니다.
기준 날짜를 수식으로 설정
기준 날짜 수식을 바꾸면 자동으로 변경. 즉 날짜가 변경에 대해 자동으로 대응이 됨.
아래 날짜에서 토요일과 일요일에 조건부 서식을 적용하기 위한 수식
기준 날짜를 연결해서 표시 #1
기준 날짜를 연결해서 표시 #2
월/일을 한꺼번에 표기하면 폭이 넓어짐. 시작 월만 따로 표시하기 위한 수식 #1
시작 월만 따로 표시하기 위한 수식 #2
일자를 표시하기 위한 수식
주말을 구분하기 위한 조건부 서식끝~
