[구글시트] 실시간 추가되는 줄에 자동 수식 반영 -1- (ArrayFormula 함수)

내가 하고 싶었던 것

: 구글 폼을 통해 구글 시트에 값이 들어갔을 때, 해당 값을 이용하여 적용되는 수식 만들기

 

예시를 보는게 더 빠르겠죠?

공구 블로그에서 금액 얼마 이상을 구매한 사람한테 서비스 금액이나 상품을 제공하는 이벤트를 한다고 가정해봅시다.

사람들이 구글 폼으로 이렇게 월별 구매금을 적고 이벤트 신청 동의까지 체크해서 제출하는 겁니다.

 

이 때, 1~3월까지의 구매금이 총 15만원이 넘어가는지, 이벤트 신청 동의를 했는지 여부에 따라 서비스 여부를 정하고,

실시간으로 체크하여 며칠 내로 서비스를 보내주게 되어 있다면?

 

G열과 H열에는 설문지가 제출될 때마다 수식이 실시간으로 적용이 되는 편이 좋을 겁니다.

 

 

적용하고자 하는 수식

G열에는 1월+2월+3월을 해야하니 C열+D열+E열을 해야겠죠?

그러니 G2셀에는 C2+D2+E2,

G3셀에는 C3+D3+E3를 넣고 싶습니다.

 

H열에는 조건부로 G의 가격이 15만원이 넘어가는지, 이벤트 개인정보 수집에 동의했는지를 알기 위해서

H2셀에는 IF(AND(G2>=150000, F2="예"),"O","X")를 넣고

H3셀에는 IF(AND(G3>=150000, F3="예"),"O","X")를 넣고 싶습니다.

 

 

방법 1 (셀의 꾸미기 서식 미반영)

함수 'arrayformula'를 사용하여 수식을 적어두면 지정된 셀범위에 동일한 수식을 적용시킬 수 있습니다.

구글 시트 공식 문서 설명

 

위와 같이 수식에 들어가는 셀에 범위를 적용하면 수식을 원하는 범위만큼 적용이 가능한데요,

예를 들어 G2에 아래와 같이 수식을 넣으면 G2부터 하단 G열에는 수식이 적용됩니다.

=ARRAYFORMULA(C2:C+D2:D+E2:E)

 

C2의 위치에 하단 C열까지의 범위를 적용하고,

마찬가지로 D2의 위치에 하단 D열 전부, E2위치에 하단 E열 전부를 적용한다는 말입니다.

아래까지 전부 적용된 모습

 

아래의 0이 보기 싫다면 딱 7행까지만 적용하면 됩니다.

=ARRAYFORMULA(C2:C7+D2:D7+E2:E7)

 

마찬가지로 H2에도 수식을 적용한다면 아래와 같이 넣어야 할 겁니다.

=ARRAYFORMULA(IF(AND(G2:G7>=150000, F2:F7="예"),"O","X"))

왜 안 됨?

하지만 이번에는 함수가 먹히지 않았습니다.

이처럼 ARRAYFORMULA 함수는 내부에서 활용할 수 있는 함수가 제한되어 있습니다.

 

이 경우에 IF는 괜찮지만 AND 함수가 반영되지 않아 문제가 발생하였습니다.

위와 같이 AND를 빼면 정상 동작하지만 우리가 원하는 다중 조건을 채우지 못합니다.

이벤트 개인정보 동의가 "아니오"인 6행에서 서비스 여부 "O"가 뜨는 것이 보이나요?

 

따라서 활용이 가능한 함수 중 IFS를 활용해보겠습니다.

=ARRAYFORMULA(IFS(G2:G7<150000, "X", F2:F7="예", "O"))

IFS 함수는 (조건1, 조건 1이 참일 경우 값, 조건2, 조건 2가 참일 경우 값, ...) 순으로 쓰여집니다.

즉,

1번: 조건 1 확인

2번: 조건 1이 참일 경우 값

3번: 조건 1이 거짓인 경우, 조건 2 확인

4번: 조건 1이 거짓 & 조건 2가 참일 경우 값

이런 식으로 이어지는 겁니다.

 

따라서 위와 같이 함수를 적용하면 아래와 같이 뜹니다.

여기서 #N/A가 뜨는 건 조건 1도 거짓, 조건 2도 거짓인 경우의 값이 없기 때문입니다.

조건 자체가 true이면 무조건 값을 반영하므로 조건3위치에 true를 쓰고, 그 뒤에 "X"를 넣으면 원하는대로 완성이 됩니다.

 

=ARRAYFORMULA(IFS(G2:G7<150000, "X", F2:F7="예", "O", true, "X"))

3개월간 15만원 이상 구매하고, 개인정보 동의까지 완료한 사람은 '나는', '짱이다' 로 잘 반영이 됩니다.

 

주의할 점!!

1) 범위는 꼭 같은 숫자로 맞춰주세요.

2) Arrayformula 함수를 적용시킨 범위에는 데이터를 넣으면 안 됩니다. 나머지 모든 곳에서 오류가 생겨요.

 

이 함수는 현재 구글 시트에서만 적용되고, 엑셀에서는 적용하지 않으니 참고해 둡시다.

 

 

이런 식으로 휴가 개수 정리나 점수에 따른 상태값 등을 적기에 좋습니다.

다만, 셀의 꾸미기 서식까지 반영되는 것이 아니기에

값에 따른 색상 변화나 셀 색상까지 이어가고 싶다면 앱스 스크립트를 활용해야 합니다.

 

 

이어서 앱스 스크립트를 활용하여 셀의 꾸미기 서식까지 반영하는 방법은 아래에 있습니다.

[구글시트] 실시간 추가되는 줄에 자동 수식, 셀 서식 반영 -2- (Apps Script)