by
Nov 21. 2023
ArrayFormula 활용 sumif대체하기
ArrayFormula를 활용하여 Sumif를 써보자(조건 1개)
저번 글에서는 엑셀의 배열 기능을 활용하여 Sumif/s 를 사용하지 않고 sumif/s 기능을 대체하여 보았다.
엑셀의 경우 범위를 여러 행 혹은 열로 지정할 경우 배열 데이터를 자동으로 출력하기 때문에 따로 배열 처리를 해줄 필요가 없다.
하지만 Google SpreadSheet 같은 경우에는 배열 처리를 따로 해주지 않기 때문에, sumif/s를 대체하기 위해서는 다른 방식으로 처리해야 한다.
우선 엑셀에서 sumif/s를 좀 더 쉽게 대체하기 위한 방법은 아래 글을 통해서 확인해보시기 바랍니다.
https://brunch.co.kr/@chunja07/122
이번에는 Google SpreadSheet에서 ArrayFormula를 활용하여 Sumif/s 기능을 대체해 보도록 하겠다.
1. ArrayFormula란?
ArrayFormula 함수는 Google Spreadsheet에서만 기능하는데, Google Spreadsheet에서 정보를 확인해보면 "배열 수식에서 여러 행 및 열에 반환된 값을 표시하고, 배열이 아닌 함수에 배열을 사용할 수 있습니다."와 같이 나오는 것을 확인할 수 있다.
배열이란 간단히 말하면, 한 개의 데이터 값이 아닌 여러 개의 데이터 값을 지니는 자료구조 중 하나이다. 그냥 값이 1개가 아니라 여러 개라고 생각하면 편할 것이다.
가장 간단한 예로 아래와 같이 A1:A3 범위 값과 B1:B3 값을 더하기 위해 ArrayFormula를 사용하여 두 개의 범위를 +로 묶어주면 A5:A7범위에 서로 더한 값이 배열로 출력되는 것을 확인할 수 있다.
2. ArrayFormula를 활용한 Sumif/s 가 무슨 관계가 있는걸까?
아래와 같이 J열에 날짜가 있고, V,W,X,Y,Z열에 어떤 값이 있는 데이터를 살펴보자.
J열은 비용이 지불된 날짜이고, V,W,X,Y,Z는 각 비용 값들이다.
내가 하고 싶은 것은 2023-9-20일에 지불된 모든 비용의 합계들이다. 이를 Sumif/s로 처리할 수도 있지만, Arrayformula를 활용해 더 간단하게 처리할 수도 있다.(아래 데이터는 '시트1'에 위치한다)
시트1아래 그림은 위 시트1 데이터의 J열에 있는 데이터가 날짜 데이터이기 때문에, 대시보드 시트를 하나 따로 만들어 Array Formula로 2023년 9월 20일에 해당하는 배열 데이터를 불러온 것이다. 20203년 9월 20일에 해당하는 데이터는 True, 아닌 데이터는 False로 처리되었다.
헤더(제목)은 제외하고 가져오고자 J2부터 불러왔고, B2에 "2023-09-20" 날짜 값을 넣고 C열에 ArrayFormula를 사용한 것을 보면 모두 True 값을 가져온 것을 볼 수 있다.
=ARRAYFORMULA('시트1'!J2:J=B2)
J열에서 "2023-09-20"과 같은 것은 J2부터 차례대로 True로 불러오고, 그 값이 다른 경우 False로 불러온다.(False 값은 35열부터라 그림에는 빠져있다.)
시트2ArrayFormula에서는 연산을 수행할 수 있기 때문에, 앞으로 ArrayFormula 연산을 활용해 보도록 하자.
아래 그림은 위에서 불러온 TRUE/FALSE열을 ArrayFormula 안에서 V열과 곱한 것이다.
=arrayformula(('시트1'!J2:J1000=B2)*('시트1'!V2:V1000))
TRUE의 경우는 1로 변환되어 V와 곱한 값이 나온 것 확인할 수 있으며, False가 나온 행의 경우 False가 0으로 치환되어 0의 값이 나온 것을 확인할 수 있을 것이다.
자 그럼 여기서 2023-09-20일의 비용 데이터를 TRUE값으로 치환하여 데이터 하나를 확인하였는데, 비용이 복수열라면 어떻게 하면 될까?
아래 수식을 살펴보면 시트 1의 V열과 Y열을 더한 것을 확인할 수 있을 것이다.
=ARRAYFORMULA(('시트1'!$J$2:$J$999=$B$2)*(('시트1'!$V$2:$V$999)+('시트1'!$Y$2:$Y$999)))
위 수식을 보면 알겠지만, 추가로 더해야 하는 열의 경우 J열의 곱셉부분과 분리 되기 위해 괄호로 묶어주었다.
J열 날짜 중 True인 것과 V열과 Y열 비용 합계의 곱으로 2023년 9월 23일에 해당하는 값이 나왔다.
만약 그에 더해 다른 비용까지 합산하고자 한다면, 원하는 열을 덧셈 기호로 붙여주면 늘려주면 된다. 만약 더 붙일 것이 있다면 뒤에 더하기만 추가시키면 될 것이다.
=ARRAYFORMULA(('시트1'!$J$2:$J$999=$B$2)*ARRAYFORMULA(('시트1'!$V$2:$V$999)+('시트1'!$Y$2:$Y$999)+('시트1'!$Z$2:$Z$999)+('시트1'!$W$2:$W$999)+('시트1'!$X$2:$X$999)))
그렇다면 마지막으로 Sumif/s를 완성시키기 위해서는 어떻게 하면 될까?
바로 Arrayformula 앞에 Sum 함수만 붙여주면 된다.
=sum(ARRAYFORMULA(('시트1'!$J$2:$J$999=$B$2)*ARRAYFORMULA(('시트1'!$V$2:$V$999)+('시트1'!$Y$2:$Y$999)+('시트1'!$Z$2:$Z$999)+('시트1'!$W$2:$W$999)+('시트1'!$X$2:$X$999))))
3. 마무리하며
아마 Sumif/s를 함수를 만들 때 이런 로직으로 프로그래밍을 했을 것이다. 배열을 통해서 True/false 값을 확인 한 후 그에 해당하는 열의 값을 모두 더하면 Sumif/s 기능을 스스로도 구현할 수 있다.
거기에 더해서 sumif로 더하고 싶은 열을 추가하고 싶다면, 그 열을 퍼러미터 값으로 넣어서 함수 기능을 업그레이드 할 수도 있겠지만, 이건 프로그래머의 영역이니 여기서는 넘어가도록 하겠다.
결론은 sumif/s 같은 기능은 위와 같이 True/False 값을 바탕으로 로직을 찾기 때문에, 로직만 이해한다면 비슷한 기능들은 훨씬 더 쉽게 이해할 수 있다.
이런 로직들을 몇 개 이해하게 되면, 엑셀이나 구글스프레드 시트 함수를 사용할 때 훨씬 더 쉽게 여러 함수들을 조합하는 나를 발견할 수 있을 것이다.
글의 마무리에 이렇게 또 광고가 들어가지만, 역시나 나의 브런치에서 나의 강의 홍보를 빼놓을 수는 없을 것이다.
현재 아래 사이트에서 엑셀/구글 스프레드시트 강의를 진행중이다.
다양한 엑셀/구글스프레드시트 기능뿐만 아니라 데이터 베이스 처리 언어인 SQL 역시 같이 배울 수 있다.(SQL기능은 구글스프레드시트에서 제공하는 기능이 한정되어있기 때문에, SQL만 배우고 싶은 분들과는 어울리지 않는다.)
엑셀/구글 스프레드 시트를 활용하여 데이터 분석을 하고 싶다거나, PM 업무를 데이터 기반으로 하고 싶다거나, 엑셀/구글 스프레드 시트 고수가 되고 싶으시다면 언제든 환영합니다.
강의는 주로 엑셀/구글 스프레드 시트를 잘하기 위한 각종 함수 기본 개념 설명 및 실습 시간이 주를 이루지만, PM 프로젝트 경험을 통해 어떻게 데이터를 가공하고, 데이터를 추출하여 어떤 결과값과 기획안을 내는지 강의에 모두 녹여내었습니다.
기술적인 부분뿐 아니라 그 기술적인 부분을 어떻게 프로젝트에 활용할지에 대한 고민이 필요하신 분이라면 한번 들어보시기 바랍니다.
https://comento.kr/class/pt/list
