Google Sheets - 일정 간격의 값들을 가져와 자동으로 채우기

Photo by Lukas Blazek on Unsplash

 

시작

 

매일 몸무게, 턱걸이, 푸샵을 하며 기록으로 관리한다.

매일의 몸무게보다는 주간 평균 몸무게의 추이를 보는 것이 좀더 정확하겠다 싶었고

턱걸이와 푸샵은 주간 목표를 정해 두었기에 주간 합계를 보고 싶었다.

 

Autofill handle 구현의 어려움

 

그런데 주간 데이터 범위의 sum, average 구하는 수식을 자동으로 채워주는 핸들이 먹히도록 구현하는게 의외로 쉽지 않았다. 원하는 것은 아래와 같았다.

 

1) 일주일마다의 날짜를 가져오기

2) 일주일간의 평균 또는 합계를 계산하기

 

일주일마다의 날짜를 가져온다는 건

아래와 같이 7번째 마다의 값을 가져오는 것이다.

 

 

일주일간의 평균 또는 합계를 계산한다는 건

 

아래와 같이 7개씩의 값을 모아 평균 또는 합계를 계산하는 것이다.

 

찾아낸 수식들

 

물론 위 내용들은 하나씩 찾아서 일일이 Cell이나 Range를 입력해서 계산할 수도 있다.

하지만 Ctrl+마우스로 끌어서 자동계산이 되도록 하는 방법을 찾아보았다.

 

필요한 함수는 INDIRECT() 와 ROW() 였다.

 

INDIRECT: https://support.google.com/docs/answer/3093377

ROW: https://support.google.com/docs/answer/3093316

 

최종 구현

 

실제로 만들어낸 수식을 보고 이해하는 것이 훨씬 편할 것이다.

 

예제 Sheet 공유: https://bit.ly/3kQO4RY

G2셀 - 간격을 띄워서 값을 가져오기

 

G2 셀에 날짜를 7일 간격으로, 즉 매 주 일요일 날짜를 가져오게 하는 수식

G2셀에는 A8의 값을 가져와야 한다. 그리고 나서 Ctrl+마우스로 끌어내리면 자동완성된다.

 

=INDIRECT("A"&(ROW(A1)*7+1))

 

1. INDIRECT는 주어진 파라미터로 생성되는 문자열을 Cell또는 Cell Range 정보를 반환해준다.

 

2. ROW가 재미있다. ROW(A1)이라면 A1 Cell이 몇 번째 행인지 값이 리턴된다. 즉 1이다.

따라서 위의 수식에서 (ROW(A1)*7+1) 는 1*7+1 = 8이 되며 문자열 연산인 "A"&8은 A8이 된다.

 

참고로 자동생성한 G3, G4, G5의 값은 각각 아래와 같다

 

=INDIRECT("A"&(ROW(A2)*7+1))

=INDIRECT("A"&(ROW(A3)*7+1))

=INDIRECT("A"&(ROW(A4)*7+1))

 

I2셀 - 간격을 띄워서 Cell range의 값을 평균내기

 

I2 셀에는 =AVERAGE(C2:C8)이 들어가야 한다. 그리고 나서 Ctrl+마우스로 끌어내리면 자동완성된다.

 

=AVERAGE(INDIRECT("C"&(ROW(A1)*7-7+2)&":C"&(ROW(A1)*7+1)))

 

자동생성한 I3, I4, I5셀의 값은 각각 아래와 같다.

 

=AVERAGE(INDIRECT("C"&(ROW(A2)*7-7+2)&":C"&(ROW(A2)*7+1)))

=AVERAGE(INDIRECT("C"&(ROW(A3)*7-7+2)&":C"&(ROW(A3)*7+1)))

=AVERAGE(INDIRECT("C"&(ROW(A4)*7-7+2)&":C"&(ROW(A4)*7+1)))

 

J2셀 - 간격을 띄워서 Cell range의 값을 더하기

 

J2셀에는 = SUM(D2:D8)이 들어가야 한다. (K2셀은 생략) 그리고 나서 Ctrl+마우스로 끌어내리면 자동완성된다.

이건 위 G2에서 AVERAGE를 SUM으로만 바꿔주면 되니 생략하겠다.