지난번에는 구글폼과 구글 시트를 연동하는 방법에 대해 설명했습니다. 구글이 제공하는 무료 오피스 도구인 구글 폼(Google Survey)과 구글 시트(Google Sheeets)를 활용하면 업무 효율성을 크게 향상시킬 수 있습니다. 실제로, 구글폼을 통해 수집된 데이터를 구글 시트에서 가공하여 업무에 활용하는데, 날짜 부분이 항상 중요하게 다뤄집니다.
예를 들어, 구글 폼을 통해 들어오는 데이터는 자동으로 타임스탬프 형식의 로그가 생성됩니다. 이것은 데이터 제출 시간을 나타냅니다. 그러나, 일반적으로 회사에 제출해야 하는 보고서에서는 일별, 주간, 월간, 분기 등 다양한 날짜 형식이 필요합니다. 이러한 경우, 구글 서베이 폼 입력 단계에서 모든 형식을 추가하면 설문을 사용하는 사용자에게 불편함을 주게 됩니다.
이럴 때 활용할 수 있는 만능 날짜 형태를 공유합니다. (실전에서도 적극 사용 중입니다!)
그러나, 이번 포스팅을 이해하는 데에는 Arrayformula와 Query에 대한 기본 지식이 필요합니다. 따라서 이전 포스팅을 먼저 보고 오시는 것을 권장합니다.
[데이터 분석/구글 스프레드시트(Google Sheets)] - 구글시트 - 쿼리1 (Google Query Basic 1)
[데이터 분석/구글 스프레드시트(Google Sheets)] - 구글시트 - 쿼리2 (Google Query Basic 2)
[데이터 분석/구글 스프레드시트(Google Sheets)] - 구글시트 - 쿼리4 (Google Query + Importrange (사용법 및 오류해결))
간단한 원리부터 설명하겠습니다. (바쁘신 분은 마지막 부분의 함수를 복사해서 사용하시면 됩니다.)
🧑🎓원리 이해하기
1. (일요일) 주차(Week) 만들기
주간 보고서(Weekly Business Report)를 작성하는 회사라면 주의 시작이 일요일인지 월요일인지 확인하고 자신에게 맞는 공식을 택하세요.
핵심 공식 설명
=A2:A-WEEKDAY(A2:A,1)+1 공식은 구글 시트에서 해당 주의 일요일시작 주차 날짜를 찾는 공식입니다.
A2:A는 A열의 2번째 행부터 마지막 행까지의 범위를 나타냅니다.WEEKDAY(A2:A,1)는 각 A열의 날짜가 주의 몇 번째 날인지를 반환합니다. 이 때, 일요일이 1이고 토요일이 7입니다.- 따라서
A2:A-WEEKDAY(A2:A,1)는 각 날짜에서 그 날짜가 주 중 몇 번째 날인지를 뺍니다. 이렇게 하면 전주의 토요일 날짜를 얻을 수 있습니다. - 마지막으로 "+1"을 해주면, 주의 일요일 날짜를 얻을 수 있습니다.
- (
일요일) 주차(Week) 만들기
=IFERROR(ARRAYFORMULA(If(A2:A="","",(A2:A-WEEKDAY(A2:A,1)+1))),"")추가 설명 : 예) 2023-06-21(수) 값 → 원하는 결과 값은 2023-06-18(일) (일요일 시작 주차 - Week 06/18). WEEKDAY()는 요일을 반환하는 함수입니다. 6월 21일 수요일은 WEEKDAY() = 4 (일,월,화,수)를 반환합니다.
2023-06-21 - 4 = 2023-06-17이므로, 여기에 +1을 하면 일요일 주차인 2023-06-18 결과 값이 나옵니다.
- (
월요일) 주차(Week) 만들기 - Week 만들기 Weekday(,1) 으 (,2)로 변경하면됨
=ARRAYFORMULA(If(B2:B="","",(B2:B-WEEKDAY(B2:B,2)+1)))공식문서 - WEEKDAY(날짜, [유형]) : 주어진 날짜의 요일을 나타내는 숫자를 반환합니다.
2. Month (월) 만들기
Month 함수를 간단히 사용해도 됩니다. 그러나 Month 함수의 결과는 1,2,3 등의 단순한 형태로 표시됩니다. 장기적으로 데이터가 쌓일 경우, 2023년 01월과 2024년 01월과 같이 구분되어야 합니다. 또한, 날짜 형식으로 출력하면 다양한 계산에 편리하기 때문에, 이 방법을 추천드립니다.
핵심 공식 설명
A2:A-DAY(A2:A)+1 공식은 구글 시트에서 월의 첫 날을 반환하는 공식입니다.
A2:A는 A열의 2번째 행부터 마지막 행까지의 범위를 나타냅니다.DAY(A2:A)는 A열의 각 행에 있는 날짜의 "일"을 반환합니다. 예를 들어, 만약 날짜가 2023-06-21이라면,DAY()함수는 21을 반환합니다.A2:A-DAY(A2:A)는 각 날짜에서 그 날짜의 "일"을 뺍니다. 예를 들어, 2023-06-21에서 21을 빼면 결과는 2023-05-31이 됩니다.A2:A-DAY(A2:A)+1은 위에서 계산한 결과에 1을 더하여 항상 각 월의 첫 날을 얻습니다. 위의 예에서, 2023-05-31에 1을 더하면 결과는 2023-06-01이 됩니다.
- 월(Month) 만들기
=IFERROR(ARRAYFORMULA((if(A2:A="","",A2:A-DAY(A2:A)+1))),"")
추가설명 : DAY(): 특정 날짜에 대응하는 월의 일자를 숫자 형식으로 반환합니다.
예) DAY(June 1, 2023) = 1 , DAY(June 21, 2023) = 21 (1일부터 해당 일까지의 일수를 나타냅니다)
*2023-06-21 - 21 = 2023-05-31 에 +1을 해주면 항상 월의 시작일로 설정됩니다 (2023-06-01)
3. Quarter (분기) 만들기
= "Q"&ROUNDUP(MONTH(A2)/3)&"-"&YEAR(A2) 이 공식은 다음과 같이 설명할 수 있습니다:
MONTH(A2:A): 월(Month)을 반환합니다. 즉, A2:A 셀에 있는 날짜의 월을 찾습니다. 예를 들어, 2022년 6월 21일이라면 이 함수는 6을 반환합니다.ROUNDUP(MONTH(A2:A)/3): 이전 단계에서 찾은 월을 3으로 나눕니다. 그런 다음에 반올림하여 가장 가까운 정수로 만듭니다. 이렇게 하면 월을 기반으로 분기(Quarter)를 찾을 수 있습니다. 예를 들어, 2월은 1분기, 5월은 2분기, 8월은 3분기, 11월은 4분기가 됩니다."Q"&ROUNDUP(MONTH(A2:A)/3): 분기 번호 앞에 'Q' 문자를 붙입니다. 예를 들어, 2월은 'Q1', 5월은 'Q2'가 됩니다.YEAR(A2:A): 년(Year)을 반환합니다. 즉, A2:A 셀에 있는 날짜의 년도를 찾습니다. 예를 들어, 2022년 6월 21일이라면 이 함수는 2022를 반환합니다."Q"&ROUNDUP(MONTH(A2:A)/3)&"-"&YEAR(A2:A): 마지막으로, 분기 정보와 년도 정보를 하이픈(-)으로 연결하여 하나의 문자열로 만듭니다. 예를 들어, 2022년 6월 21일이라면 최종 결과는 'Q2-2022'가 됩니다.
=IFERROR(ArrayFormula(IF(A2:A="","",ArrayFormula("Q"&ROUNDUP(MONTH(A2:A)/3)&"-"&YEAR(A2:A)))),"")
결과(Output) : Q1-2022
📌Pro Tip (형식 고정)
주의! Google 설문 양식을 통해 쌓이는 데이터의 경우, Date 형식을 계속 변경하는 문제가 발생합니다. 형식을 바꿀 수는 있지만, 이 작업이 계속 반복되면 비효율적입니다. 따라서, =QUERY 함수 안에 Format을 사용하는 방법을 생각해냈습니다.
해결법 : Format 고정 (Query) 를 활용해서 형식 자체를 고정하는 방법입니다.
일별(Date) - Format 고정
=QUERY(ArrayFormula(DATEVALUE(A2:A)),"select Col1 format Col1 'YYYY-MM-DD'",0)주간(Week) - Format 고정
=QUERY(ARRAYFORMULA(If(A2:A="","",(A2:A-WEEKDAY(A2:A,1)+1))),"select Col1 format Col1 'YYYY-MM-DD'",0)
월간(Month) - Format 고정
=QUERY(ARRAYFORMULA((if(A2:A="","",A2:A-DAY(A2:A)+1))),"select Col1 format Col1 'YYYY-MM-DD'",0)✨(최종) Pro Tip (형식 + 머릿글_Header 고정)
제목(Head)을 고정하여 완성하는 방법 - 최종
= {"제목"; 공식}
최종 모습을 보면, 타임스탬프에서 일별, 주간, 월간 열(Column)이 생성되는 걸 볼 수 있습니다.
- 자동 채워짐 (Automation) : 혹시라도 밑에 데이터를 누가 지우더라도 (예_ F2,F3셀 삭제) 자동으로 복구 가능 + 구글 설문을 통해서 데이터 값이 채워지면 자동으로 Date, Week, Month 데이터가 채워집니다.
- 날짜 형식 유지 : Query - Format을 통해서 날짜 형식이 틀어 지지 않습니다.
일별(Date) - 최종
={"Date";QUERY(ArrayFormula(DATEVALUE(A2:A)),"select Col1 format Col1 'YYYY-MM-DD'",0)}주간(Week) - 최종
={"Week";QUERY(ARRAYFORMULA(If(A2:A="","",(A2:A-WEEKDAY(A2:A,1)+1))),"select Col1 format Col1 'YYYY-MM-DD'",0)}월간(Month) - 최종
={"Month";QUERY(ARRAYFORMULA((if(A2:A="","",A2:A-DAY(A2:A)+1))),"select Col1 format Col1 'YYYY-MM-DD'",0)}분기(Quarter) - 최종
={"Querter";ArrayFormula(IF(A2:A="","",ArrayFormula("Q"&ROUNDUP(MONTH(A2:A)/3)&"-"&YEAR(A2:A))))}
실전 활용 예시 (구글 폼 - 구글시트 링크)
위의 링크를 사용하여 구글폼과 구글시트를 연동하는 방법을 확인하실 수 있습니다. 이를 통해 만능 날짜 함수의 사용법을 자세히 배우실 수 있습니다. 이 함수는 매우 유용하므로, 이를 활용해 보시는 것을 추천드립니다. 또한, 지금까지 배운 내용을 토대로 자신만의 구글폼을 제작해 보시는 것도 좋은 학습 방법입니다. 이를 구글시트와 연동하면, 날짜 형식 작업을 보다 간편하게 처리할 수 있습니다. 이 과정을 통해 배운 지식을 실제로 적용해 보시는 것은 매우 중요하므로, 꼭 시도해 보시기 바랍니다.
'데이터 분석 > 구글 스프레드시트(Google Sheets)' 카테고리의 다른 글
| 구글시트 - 쿼리4 (Google Query + Importrange (사용법 및 오류해결)) (0) | 2024.03.12 |
|---|---|
| 구글시트 - 쿼리3 (Google Query : WHERE + contains/ starts with/ends with /matches (Regex)) (0) | 2024.03.12 |
| 구글시트 - 쿼리2 (Google Query Basic 2 :: Limit /Label /Format / Pivot ) (0) | 2024.03.12 |
| 구글시트 - 쿼리1 (Google Query Basic 1 :: Select, Group by, Order by) (0) | 2024.03.12 |
| 구글시트 - 피벗테이블 3 (0) | 2024.03.06 |
| 구글시트 - 피벗테이블 2 (0) | 2024.03.06 |
| 구글시트 - 피벗테이블 1 (0) | 2024.02.28 |
| 구글시트 - Arrayformula + IF(AND, OR) (0) | 2024.02.27 |
