셀 배경색을 기준으로 합계 구하기

안녕하세요.

 

구독자 김**님이 질문을 하셨습니다.

 

안녕하세요

선생님 엑셀로 공부하는 구독자입니다

다름이 아니고 첨부파일을 보시면 색칠된 셀만 합계를 하고 싶습니다
노가다로 =SUM(B4,B7,B10) 이런식으로 말고 3번째 행만 구하여 총합계를 할수 있는 방법은 없는지 하여 문의 드립니다

감사합니다 ^^

 

첨부하신 파일 내용입니다.

 

 

사용자 정의 함수로 구하기

셀 배경색을 엑셀 함수에서 알아낼 수 없기 때문에 특정 셀 배경색이 지정된 셀들의 합계값을 구할 때는 VBA로 사용자 정의 함수를 만드는 방법이 일반적으로 사용되는 방법입니다.

 

셀 배경색 기준으로 합계 구하기

 

배열 수식으로 구하기

예시 파일처럼 일정한 기준으로 색상이 적용되어 있다면 함수를 이용해 값을 구하는 방법도 가능합니다.

 

실습 파일 다운로드

예시1.xlsx

0.01MB

 

완성 파일 다운로드

예시1_완성.xlsx

0.01MB

 

예시 파일에서 합계 값을 구하는데 사용될 셀은 [B4], [B7], [B10] 셀인데 일정한 규칙이 있습니다.

규칙은 행 숫자인 4, 7, 10을 3으로 나누었을 때 나머지 값이 1이 되는 셀들이라는 것입니다.

이런 식으로 나머지 연산을 이용해 특정한 셀들을 선택하는 이 알고리즘은 널리 사용되는 방법입니다.

 

MOD 함수가 나머지 연산을 하는 함수입니다.

IF 함수의 조건 인수에 MOD 함수식을 넣고 합계값을 구할 셀의 값만 선택해 SUM 함수로 합계를 구합니다.

하나의 셀 값만 대상으로 하는 것이 아니라 셀 범위를 대상으로 수식을 만들어야 하므로 배열 수식이 되어야 합니다.

 

엑셀 실무] 배열 수식 기본 사용법

 

엑셀] 배열 수식과 배열 함수 SUMPRODUCT

[B13] 셀 수식을 지우고 새로 수식을 작성합니다.

수식을 작성합니다.

배열 수식은 수식을 끝낼 때 <Ctrl + Shift + Enter>를 눌러야 합니다.

 

입력된 수식은 

=SUM(IF(MOD(B4:B12,3)=1,B4:B12))

이고, 완성된 수식은 배열 수식이므로 수식 입력줄에서 중괄호로 묶은 모습으로 나타납니다.

{=SUM(IF(MOD(B4:B12,3)=1,B4:B12))}

 

필터 기능과 SUBTOTAL 함수로 구하기

필터 기능에 색상으로 필터하는 기능이 있고, 필터된 셀 값만 계산하는 SUBTOTAL 함수를 이용하면 값을 구할 수 있습니다.

이 방법으로 값을 구하려면 현재 작성된 문서 형태를 조금 수정해야 합니다.

 

Sheet1 시트를 복사해서 복사된 시트에서 작업하겠습니다.

화면 아래 시트 탭에서 Sheet1 시트 이름에 마우스 커서를 두고 마우스 오른쪽 클릭해서 [이동/복사]를 선택하고, [복사본 만들기]를 선택해 복사합니다.

복사된 시트 이름을 더블 클릭해 시트 이름을 '필터'로 수정합니다.

13행 번호를 클릭해 행을 선택하고 단축키 <Ctrl + -(빼기)>를 눌러 행을 지웁니다.

4행 번호를 클릭해 행을 선택하고 단축키 <Ctrl + +(더하기)>를 눌러 행을 추가합니다.

[A4] 셀에 '필드1', [B4] 셀에 '필드2', [C4] 셀에 '필드3'을 입력합니다.

필터 기능을 쓰려면 필드명이 데이터 범위 첫번째 행에 입력되어 있어야 하기 때문입니다.

[A4:C13] 셀 범위를 선택한 뒤 [데이터]-[필터]를 선택합니다.

합계 값을 나타낼 수식은 데이터 범위와 한 행 떨어진 [B15] 셀에 작성합니다.

[B15] 셀에 커서를 두고 직접 수식을 입력합니다.

=SUBTOTAL(9,B5:B13)

 

SUBTOTAL 함수 자세한 사용법은 예전 강좌를 참고하세요.

엑셀 70강] Subtotal함수를 사용하여 실시간 부분합 구하기

이제 셀 배경색이 적용된 셀만 나타나도록 필터 합니다.

 

[B4] 셀 필드2 필드명 옆 역삼각형을 눌러 필터 메뉴를 나타내고 [색 기준 필터]에서 필터할 색을 선택합니다.

[B15] 셀에 셀 배경색을 기준으로 필터된 셀의 합계값이 나타납니다.

---

지금까지 셀 배경색을 기준으로 합계를 구하는 세가지 방법을 소개했습니다.

 

이 중 첫번째 사용자 정의 함수를 이용하는 방법이 여러가지 경우에 적용할 수 있는 방법입니다.

두번째 배열 수식을 이용하는 방법과 세번째 필터와 SUBTOTAL 함수를 이용하는 방법은 일정한 기준으로 나열되어 있거나 작성된 문서 형태를 바꿀 수 있어야 사용할 수 있는 방법입니다.

 

필요하신 분들께 도움이 되기를 바랍니다.