조건에 따라 보이는 셀의 합계/개수 구하기 :: 엑셀 함수 공식
함수공식
= 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% 미만" 인 경우의 개수 합계를 계산하게 됩니다.
아직 자세히 들여다 보질 못해서 이해 못하고 있었습니다.
이제 원리를 이해할 수 있겠군요.
연습 많이해 보겠습니다.
그런데, 눈에 보이는 셀의 조건별 합계를 구하는 공식을 다른 사이트에서 찾아서 적용해 본 결과, 동일한 결과가 도출되는데, 아래 함수에서 첫번째 인수에 "+0"을 붙인 게 "--"랑 같은 역할을 하나요?
=SUMPRODUCT((criteriarange=criteria)+0,SUBTOTAL(109,OFFSET(sumrange,ROW(sumrange)-MIN(ROW(sumrange)),0,1,1)))
네 맞습니다. -- 와 동일한 역할을 합니다.
신촌이 들어간 단어를 찾으라고 할땐 어찌해야되나요 ?
”*”&신촌&”*” 으로 해보았는데 제대로 카운터를 못하네요..
본 수식은 배열수식이므로 특정단어 포함 조건을 아래와 같이 작성해주셔야 합니다. 아래 공식을 활용해보세요
( ISNUMBER(FIND("신촌",조건범위)) )
Subtotal 함수 계산방식에 대한 자세한 설명은 아래 링크를 한번 확인해보시겠어요?:) 감사합니다.
https://www.oppadu.com/엑셀-subtotal-함수/
혹시 구글에서 해당 함수를 사용하려면 어떻게 해야될까요?
본 수식은 엑셀에서만 잘 동작하고 구글시트에서는 사용할 수 없습니다.
형태로 조건1과 조건2에 신촌점과 홍대점을 입력해서 사용해보세요.
엑셀 2021 / M365 이전 버전에서는 배열이 동적으로 반환되지 않기때문에, 배열이 반환되는 넓은 범위를 우선 선택한 후, Ctrl +Shift + Enter로 수식을 입력해야 합니다.
자세한 내용은 아래 영상 강의를 참고하세요.
https://www.oppadu.com/%EC%A7%84%EC%A7%9C%EC%93%B0%EB%8A%94-%EC%8B%A4%EB%AC%B4%EC%97%91%EC%85%80-7-4-1/
감사합니다.