조건에 따라 보이는 셀의 합계/개수 구하기 :: 엑셀 함수 공식
함수공식
= SUMPRODUCT(--(조건범위=조건)*SUBTOTAL(109,OFFSET(합계범위시작셀,ROW(합계범위)-ROW(합계범위시작셀),0,1)))
공식에 대한 설명
SUMPRODUCT 함수와 SUBTOTAL 함수를 응용하여 보이는 조건별 보이는 셀의 합계 또는 보이는 셀의 개수를 구하는 방법을 알아봅니다.
예제파일 다운로드 및 응용방법
사용된 함수
본 예제파일에서는 3개의 매장 (신촌점, 홍대점, 강남점) 의 매출이익률이 30% 미만인 날을 '빨간색'으로 조건부서식을 적용한 뒤, 각 매장별로 이익률이 빨간색인 날의 개수를 계산합니다.
만약 조건별 보이는 셀의 합계를 구할경우, SUBTOTAL의 첫번째 인수로 '109' 를 사용하세요.
= SUMPRODUCT ( --(B6:B35="신촌점") * SUBTOTAL ( 103, OFFSET ( E6, ROW(E6:E35) - ROW(E6), 0, 1)))
함수의 동작 원리
1. ROW(E6:E35) - ROW(E6)
ROW 함수를 응용하여 각 셀의 위치를 숫자 0부터 반환합니다. 이후 OFFSET 함수를 통해 배열로 반환합니다.
- ROW(E6:E35)는 각 행번호의 배열을 반환합니다.
= { 6; 7; 8; ... ; 34; 35 }
- ROW(E6) 은 숫자 6을 반환합니다.
- 따라서, ROW(E6:E35) - ROW(E6)은 아래 배열을 반환합니다.
= { 0; 1; 2; 3;... ; 27; 28 }
2. OFFSET(E6, ROW(E6:E35)-ROW(E6),0,1))
OFFSET 함수를 통해, 시작셀(E6)부터 한칸씩 내려가며 각 셀의 값을 배열로 반환합니다.
- E6셀을 시작으로 한칸씩 내려가며 총 29개의 셀값을 배열로 반환합니다.
= OFFSET(E6, { 0; 1; 2; 3; ...; 27; 28 }, 0, 1))
- 따라서, 아래의 배열이 출력됩니다.
= { 34.0%; 21.0%; 43.0%; 48.0%; 36.0%; 39.0%; 26.0%; 38.0%; 29.0%; 23.0%; ... }
3. SUBTOTAL(103, OFFSET(E6, ROW(E6:E35)-ROW(E6),0,1)))
SUBTOTAL함수 에서 첫번째 인수로 '103'을 사용하면, 입력된 범위에서 '보이는 셀'의 개수만 세는 동작을 합니다. 따라서, E6:E35 범위중 빨간색 글씨(이익률이 30%미만)만 보이도록 필터를 적용할 경우, 그 중 보이는 셀의 값만 1로 반환합니다.
- 앞전단계에서 본 OFFSET 함수의 반환값이 SUBTOTAL 함수의 두번째 인수로 들어갑니다.
= SUBTOTAL(103, { 34.0%; 21.0%; 43.0%; 48.0%; 36.0%; 39.0%; 26.0%; 38.0%; 29.0%; 23.0%; ... }
- SUBTOTAL 함수의 첫번째 인수로 103을 사용하면 보이는 셀의 개수만 계산하므로,
={ 0; 1; 0; 0; 0; 0; 1; 0; 1; 1; ... } 을 반환합니다.
4. --(B6:B35="신촌점")
앞에 사용된 "--" 기호는 논리값을 숫자로 강제변환하는 기호입니다.
- B6:B35는 매장의 범위를 반환합니다.
= { 신촌점; 홍대점; 강남점; 신촌점; 홍대점; 강남점; ... }
- B6:B35="신촌점" 은 B6:B35 범위 중 "신촌점"과 일치여부를 논리값으로 반환합니다.
= {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; ... }
- -- 기호를 통해 논리값이 숫자로 변환되어 반환됩니다.
= {1; 0; 0; 1; 0; 0; ... }
5. = SUMPRODUCT ( --(B6:B35="신촌점") * SUBTOTAL ( 103, OFFSET ( E6, ROW(E6:E35) - ROW(E6), 0, 1)))
앞전 단계를 통해, SUMPRODUCT 함수에 입력된 각 인수들의 반환값을 알아봤습니다. SUMPRODUCT 함수의 동작원리는 이전 포스트에서 자세히 설명드렸으니 관련 포스트를 참고하세요.
- 앞전단계에서 알아본 각 배열의 곱셈을 계산합니다.
= SUMPRODUCT( {1; 0; 0; 1; 0; 0; ... } * {0; 1; 0; 0; 0; 0; 1; 0; 1; 1; ... } )
- 결과값으로 나온 배열의 각 항목을 SUMPRODUCT 함수로 합계를 구합니다.
= SUMPRODUCT( {0; 0; 0; 0; 0; 0; 1; 0; 0; ... })
- 따라서 SUMPRODUCT 함수는 매장명이 "신촌점"이고 이익률이 "30% 미만" 인 경우의 개수 합계를 계산하게 됩니다.
