들어가기 전에
Excel은 데이터 분석에 강력한 도구이지만, 색상별로 셀 합계나 개수를 계산하는 기본 기능은 없습니다. 하지만 몇 가지 유용한 기술을 활용하면 이 숨겨진 기능을 활용할 수 있습니다. 프로젝트 진행 상황 추적, 비용 분류, 추세 분석 등에서 색상별로 셀 합계와 개수를 계산하는 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Learn to Count Excel Cells by Color Like a Pro : SUM & COUNT Cells By Color
- URL: https://www.geeky-gadgets.com/excel-sum-cells-by-color-guide/
방법 1: SUBTOTAL 함수 사용
이 방법은 Excel의 기본 필터 기능과 SUBTOTAL 함수를 함께 사용하여 색상별로 셀의 합계나 개수를 계산합니다. 간단하고 최소한의 설정만 필요하므로 빠른 작업에 적합합니다.
- 먼저 데이터세트에 필터를 적용합니다. 데이터 범위를 선택한 후 [데이터] 탭 - [정렬 및 필터] 그룹 - [필터]를 선택합니다.
- 필터 드롭다운 메뉴를 사용하여 분석하려는 특정 셀 색상을 선택합니다.
- SUBTOTAL 함수를 적용하여 표시된 셀의 합계 또는 개수를 계산합니다. 예를 들면 다음과 같습니다.
- 필터링된 셀을 합산: =SUBTOTAL(109, A1:A100)
- 필터링된 셀을 계산 =SUBTOTAL(103, A1:A100)`를 사용합니다.
장점:
- 구현이 빠르고 쉽습니다.
- 고급 설정이나 추가 도구가 필요하지 않습니다.
단점:
- 결과는 한 번에 한 가지 색상으로 제한됩니다.
- 셀 색상이 수정되면 결과를 업데이트하려면 필터를 다시 적용해야 합니다.
방법 2: 이름 관리자 및 보조 열 사용
이 방법은 GET.CELL 수식을 사용하여 색상 코드를 추출하고, 보조 열을 사용하여 합계나 개수를 동적으로 계산하는 방식을 사용합니다. 첫 번째 방법보다 설정이 더 복잡하지만, 유연성과 자동화 기능이 더 뛰어납니다.
- [수식] 탭 - [정의된 이름] 그룸 - [이름 관리자]를 클릭합니다.
- GET.CELL 수식을 사용하여 이름이 지정된 범위를 만듭니다. 예를 들어, =GET.CELL(63, A1)을 입력하면 A1 셀의 배경색 코드를 가져옵니다.
- 각 셀의 색상 코드를 표시하기 위해 데이터세트에 보조 열을 추가합니다. 예를 들어, 데이터가 A열에 있는 경우 B열을 도우미 열로 사용하여 해당 색상 코드를 표시합니다.
- SUMIF 또는 COUNTIF와 같은 함수를 사용하여 색상 코드를 기준으로 합계나 개수를 계산합니다. 예를 들면 다음과 같습니다.
- 특정 색상의 값을 합산: =SUMIF(B1:B100, “6”, A1:A100)
- 특정 색상의 셀을 계산: =COUNTIF(B1:B100, "6")
장점:
- 데이터가 변경되면 계산을 자동으로 업데이트합니다.
- 여러 색상을 동시에 분석할 수 있습니다.
단점:
- 보조 열을 만드는 등 추가 설정이 필요합니다.
- 셀 색상을 변경하면 수동으로 업데이트해야 합니다.
참고 영상:
방법 3: VBA로 사용자 지정 함수 만들기
코딩에 익숙한 사용자를 위해 사용자 지정 VBA 함수를 생성하면 색상별로 셀의 합계나 개수를 계산하는 자동화되고 효율적인 방법을 제공합니다. 이 방법은 특히 대용량 데이터세트나 반복적인 작업을 처리할 때 유용합니다.
- [개발 도구] 탭을 활성화한 다음 [개발 도구] 탭 - [코드] 그룹 - [Visual Basic]을 선택하여 VBA 편집기를 엽니다.
- SumColor 또는 CountColor와 같은 사용자 지정 VBA 함수를 작성하여 셀 색상을 식별하고 처리합니다. 이러한 함수는 지정된 색상을 기준으로 합계나 개수를 동적으로 계산할 수 있습니다.
- 셀 색상이 변경될 때 결과가 업데이트되도록 하려면 계산을 새로 고치는 매크로를 만드세요. 예를 들어, 통합 문서의 모든 수식을 새로 고치는 매크로에 연결된 단추를 추가할 수 있습니다.
- VBA 기능을 유지하려면 통합 문서를 매크로 사용 파일(.xlsm)로 저장합니다.
장점:
- 완전 자동화되어 필터나 도우미 열이 필요 없습니다.
- 대규모 또는 복잡한 데이터 세트에 매우 효율적입니다.
단점:
- VBA 및 매크로에 대한 기본 지식이 필요합니다.
- 매크로를 사용하여 계산을 새로 고치지 않으면 결과가 자동으로 업데이트되지 않을 수 있습니다.
참고 영상(멤버십 회원용):
추가 고려 사항
분석에서 배경색 대신 글꼴 색상을 기준으로 셀의 합계나 개수를 계산해야 하는 경우, VBA 코드나 수식을 그에 맞게 조정할 수 있습니다. 각 방법마다 장단점이 있으므로, 특정 요구 사항에 맞는 방법을 선택하는 것이 중요합니다.
- 필터 및 소계: 최소한의 설정으로 빠르고 일회성 작업에 적합합니다.
- 이름 관리자 및 도우미 열: 동적이고 다양한 색상의 분석에 이상적이지만 더 많은 준비가 필요합니다.
- 사용자 정의 VBA 함수: 자동화된 대규모 작업에 적합하지만 코딩에 대한 익숙함이 필요합니다.
마치며
여러 가지 해결 방법을 알고 있으면 상황에 맞게 해결할 수 있습니다. 자신이 처한 환경에 적합한 방법을 사용하여 데이터 분석 프로세스를 간소화하고 생산성을 높여 보세요.
'Excel' 카테고리의 다른 글
| [강의 오픈 안내] 엑셀 사용자라면 무조건 바꿔야 할 엑셀 5가지 필수 설정 (0) | 2025.11.15 |
|---|---|
| 공유 Excel 시트를 더 쉽게 탐색할 수 있는 8가지 방법 (1) | 2025.11.14 |
| AI 비서 "노트북LM" 사용 이후 달라진 Excel 데이터 분석 방식 (0) | 2025.11.11 |
| 이 강력한 도구를 사용하면 Excel 문서를 쉽게 결합할 수 있습니다 (0) | 2025.11.10 |
| Excel에서 시간을 절약해주는 7가지 파워 쿼리 팁 (0) | 2025.11.07 |
