본문 바로가기

컴퓨터 활용/구글스프레드시트 응용

엑셀에서 필터된 데이터 실시간 집계하기. 엑셀 & 구글시트 모두 동작

엑셀과 구글시트에는 데이터필터라는 것이 있는데 필터를 선택할 때 마다 보여지는 표에서 합계(특정 열 집계)를 실시간으로 보여지도록 구현해 보았습니다. 이것은 가계부 혹은 장부에서 유용하게 활용할 수 있을 것 같습니다.

엑셀에서는 SUBTOTAL과 SUMPRODUCT 함수를 이용하여 가능 합니다. 예제로 다음과 같이 세팅하였습니다.

표에는 필터를 적용해 놨고 데이터 필터를 선택할 때 마다 위의 카드, 현금, 계좌의 값이 실시간으로 바뀌는 것입니다. 수식은 다음과 같습니다.

=SUMPRODUCT( SUBTOTAL(9,OFFSET($E$5,ROW($D$5:$D$34)-ROW($E$5),))*($C$5:$C$34=A1) )

SUBTOTAL의 사용법은 SUBTOTAL( 함수번호, 영역1, 영역2) 입니다. 여기서 사용된 함수번호 9 는 합계(SUM) 입니다.

OFFSET 은 $E$5 셀을 기준으로 영역을 구하는 함수로
사용법은 OFFSET(기준셀, 가로방향 이동 셀 수, 세로방향 이동 셀 수, 높이, 너비) 인데

배열수식으로 적용되어 배열 수 만큼 OFFSET 계산이 반복한 후 SUBTOTAL 값의 다중배열로 받아서 SUMPRODUCT가 더해 줍니다.

ROW($D$5:$D$34) 에는 { 5,6, ..........., 34 } 로 치환 되고 ROW($E$5)에는 { 5 } 가 들어가는데
ROW($D$5:$D$34)-ROW($E$5) 에는 일대일 배열 계산으로 {0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29} 가 치환 됩니다. 결국 { 5-5, 6-5, 7-5, .......... 34-5} 가 들어가게되는 거죠.

1) 배열수식으로 OFFSET 함수가 30 번 계산 되면

{ 
	SUBTOTAL(9,OFFSET( $E$5, 0,0)),  SUBTOTAL(9,OFFSET( $E$5, 1,0)), SUBTOTAL(9,OFFSET( $E$5, 2,0)), ........ 
 SUBTOTAL(9,OFFSET( $E$5, 29,0,0,0)) 
 }

 

로 배열이 들어가게 됩니다.

2) 마지막 배열수식 연산인 *($C$5:$C$34=A1)에서
$C$5:$C$34는 30개의 구성요소를 가진 배열 {카드,카드,카드, 현금,...... 카드} 로 되고 A1 이 { 카드 } 이므로 1: 1 연산에 의하여 {1, 1, 1,0,..... 1} 로 치환 됩니다.

위의 SUBTOTAL로 계산된 배열과 1:1 배열 연산이 되면 1 인 부분만 걸러지는데 결국

=SUMPRODUCT( {0;0;0;0;0;0;5000;1000;800;0;0;0;0;0;0;5000;1000;800;0;0;0;0;0;0;5000;1000;800;0;0;0} )

이 남게 됩니다.

배열 요소를 모두 더하게 되면 카드인 부분만 합해져서 결국에는 카드항목의 금액의 합이 찍히게 되는 것이죠.

예제파일

필터된 데이터 실시간 집계하기.xlsx
0.02MB

 

구글 스프레드 시트 및 엑셀에서 모두 동작하도록 구현

위 엑셀파일을 구글스프레드 시트에서 불러오면 안타깝게도 위의 엑셀 수식은 구글 스프레드시트에서는 동작이 안 됩니다. 그래서 모두 동작하도록 첫 번째 시트에 구현해 보았습니다.

배열수식함수인 SUMIFS 함수와 각행별로 SUBTOTAL을 지정하여 사용하는 방법입니다.
표에 열을 추가하고 SUBTOTAL(103, 조건이 있는 열) 수식을 라인마다 지정해줘야 합니다.

여기서는 카드,현금,계좌가 있는 C열이 되겠습니다

데이터 필터가 적용되어 보여지는 표에서 카드, 현금, 계좌로 쓰인 금액의 합을 실시간으로 보는 목적입니다. 카드의 합의 수식은 다음과 같습니다. 

=SUMIFS($E$5:$E$34,$C$5:$C$34,A$1,$F$5:$F$34,1)

=SUMIFS( 더해질 영역, 조건영역1, 조건1 , 조건영역2, 조건2) 인데

C 열의 각 셀들에서 카드만 F열의 각 셀에서 1 인 조건이 있는 라인을 골라낸 후 금액들이 있는 E열 적용하여 모두 더합니다. 여기서 핵심은 F 열에 일일히 넣은 수식입니다.
subtotal(103,셀주소)가 화면에 보여지는 데이터만 골라주는 중요한 역할을 합니다.

구글스프레드 시트에서는 일일히 각 라인에 넣어줘야 동작을 합니다. 추가된 F 열들이 보기가 싫으면 열전체를 숨기거나 해당 셀들을 조건부서식을 이용하거나 값이 있으면 글자 및 배경색을 흰색으로 바꾸면 됩니다. 엑셀에서도 구글시트에서 모두 동일하게 동작이 됩니다

구글 스프레드 시트의 주소는 다음과 같습니다.

 

필터된 데이터 실시간 집계하기

구글스프레드시트&엑셀모두동작 카드,현금,계좌 29,800,22,600,23,000,=SUMIFS($E$5:$E$34,$C$5:$C$34,A$1,$F$5:$F$34,1) 월,사용자,결제수단,항목,금액,필터 1월,A,카드,밥,5000,1,=SUBTOTAL(103,C5) 1월,A,카드,과자,10000,1,

docs.google.com