[구글시트] arrayformula함수 설명 셀에 수식이나 함수 반복적용시

구글 스프레드시트를 사용하다보면 한 셀이 아닌 여러개의 셀에 수식이나 함수를 반복 적용해야 하는 상황이 종종 발생한다. 이런경우 종종 1. 노가다를 통해 반복하면 되는 경우, 2. 수식 계산에 반복 로직이 꼭 필요해서 노가다로 해결이 불가능한 경우 이렇게 상황이 나뉘는 것 같다. 어쨋던 arrayformula 함수는 두가지 경우에서 모두 유용하게 사용된다.

 

공식 설명을 보면 ARRAYFORMULA함수는 배열수식에서 여러 셀에 반환된 값을 표시하고 배열이 아닌 함수에 배열을 사용할 수 있다고 나와있다. (공식설명 참조) 여기에서 하나 더 얻어갈 수 있는 꿀팁은 수식에 ctrl shift enter를 누르면 수식에 ARRAYFORMULA함수를 자동으로 추가할 수 있다는 점!

 

구글 스프레드시트

IF문과 함께 사용하는 경우

필자가 주로 arrayformula를 사용하는 상황인데, 어떤 연산을 할 때 또는 함수를 적용하는데 특정 조건을 만족하는 셀에만 적용하고 싶을 때 arrayformula 함수를 적용한다. 아래 수식에서는 I열에 값이 D12인 경우에 1이라는 값을 부여해서 sumproduct함수를 적용했다.

 

ex) arrayformula(sumproduct(if(Rawdata!$I:$I<=Main!D12,1,0),Rawdata!$C:$C,Rawdata!$E:$E))

 

아래 수식에서도 역시 F열의 값이 A114일때에만 Frequency함수를 이용해서 E열에 나오는 값의 빈도를 계산한 경우이다. 실제 데이터와 결과값을 보면 이해하기 더 쉽다.

 

ex)ArrayFormula(FREQUENCY(IF(EventRawdata!F:F=A114,EventRawdata!E:E),A114:A122))

 

함수적용 대상 데이터

함수적용 결과

원하는 범위에 동일한 수식이나 함수 반복 적용하고 싶을 때

셀들에 함수를 반복해서 적용하려고 하는데 마우스로 드레그해서 채우기로 하는건 처음에 몇번 할때는 괜찮지만 작업이 반복되면 손목도 아프로 굉장히 불편하게 느껴진다. 게다가 셀 범위가 가로축이나 세로축으로 무한 확장이 가능한 경우에는 데이터가 새로 들어올때마다 손으로 반복해서 채우기 작업을 하는것이 매우 비효율적이다. 이럴 때 딱 하나의 셀에만 arrayformula수식을 써줘도 원하는 범위까지 값이 뿌려지는 마법을 경험할 수 있다.

 

아래 수식에서 E70부터 AN70까지의 셀에 "✓" 라는 글자가 들어가는지 체크하기 위해 arrayformula를 사용하였다. regexmatch라는 함수를 드레그해서 채우기 할 필요없이, 단 한개의 셀에만 입력해주면 된다. 

 

ex) arrayformula(regexmatch('Table'!E70:AN70,"✓"))

 

함수 적용대상 데이터

함수적용 결과

엑셀에도 동일한 기능을 하는 함수가 있을텐데 다음에는 엑셀의 함수에 대해서 설명을 남겨보려고 한다.

 

또한 arrayformula와 함께 구글시트만의 장점을 극대화한 함수인 importrange 함수에 대해서도 알아보자