Excel 팁: 색상별로 셀 개수/합계(배경, 글꼴, 조건부 서식)

일상적인 작업에서 색상 표시는 중요한 데이터를 빠르게 구별하고 강조 표시하는 데 널리 사용되는 방법입니다. 하지만 특정 색상(채우기 색상, 글꼴 색상, 조건부 서식)을 기준으로 셀 데이터를 어떻게 계산하거나 합산합니까? 기본적으로 Excel은 색상별로 계산하거나 합계하는 직접적인 기능을 제공하지 않습니다. 그럼에도 불구하고 몇 가지 트릭과 간접적인 방법을 사용하면 여전히 이를 달성할 수 있습니다. 이 문서에서는 색상별로 데이터를 계산하거나 합산하는 방법을 살펴보겠습니다.

배경색을 기준으로 셀 수 및 합계

• 사용자 정의 기능을 사용하여
• 강력한 기능을 사용하여 – Excel용 Kutools
• 필터 및 소계를 사용하여

글꼴 색상을 기준으로 셀 개수 및 합계

• 사용자 정의 기능을 사용하여
• 쉬운 기능을 사용하여 – Excel용 Kutools

조건부 서식 색상을 기준으로 셀 개수 및 합계

• VBA 코드를 사용하여
• 스마트 기능을 사용하여 – Excel용 Kutools

비디오: 색상을 기준으로 셀 개수 및 합계

배경색을 기준으로 셀 수 및 합계

예를 들어, 아래 스크린샷에 표시된 것처럼 값이 다양한 배경색으로 채워지는 데이터 범위가 있는 경우입니다. 특정 색상을 기준으로 셀 수를 계산하거나 합산하기 위해 Excel에서는 배경색을 기준으로 셀 수를 계산하거나 합산하는 직접적인 기능을 제공하지 않습니다. 그러나 약간의 독창성과 몇 가지 편리한 기술을 사용하면 이 작업을 수행할 수 있습니다. 이 섹션에서는 몇 가지 유용한 방법을 살펴보겠습니다.

사용자 정의 함수를 사용하여 배경색별로 셀 개수 및 합계

여기에서는 이러한 사용자 정의 함수를 만들고 사용하여 Excel에서 이 작업을 해결하는 방법을 보여줍니다. 다음 단계를 수행하십시오.

1단계: VBA 모듈 편집기를 열고 코드를 복사합니다.

1. PR 기사 Alt + F11 키를 눌러 응용 프로그램 용 Microsoft Visual Basic 창.
2. 열린 창에서 다음을 클릭하세요. 끼워 넣다 > 모듈 새 빈 모듈을 만듭니다.
3. 그런 다음 아래 코드를 복사하여 빈 모듈에 붙여넣습니다.
   VBA 코드: 배경색을 기준으로 셀 개수 및 합계

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

2단계: 배경색을 기준으로 셀 수를 계산하고 합산하는 수식 만들기

위 코드를 붙여넣은 후 모듈 창을 닫고 다음 수식을 적용합니다.

• 특정 배경색을 기준으로 셀 수를 계산합니다.
  결과를 얻으려면 아래에 제공된 수식을 원하는 셀에 복사하거나 입력하세요. 그런 다음 채우기 핸들을 아래쪽으로 드래그하여 다른 결과를 얻습니다. 스크린샷 보기:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  주의 사항:이 공식에서 G2 일치시키려는 특정 배경색을 가진 참조 셀입니다. $B$2:$E$12 G2 색상의 셀 수를 계산하려는 범위입니다. 그릇된 일치하는 색상의 셀 수를 계산하는 데 사용됩니다.
  
• 특정 배경색을 기준으로 셀 합계를 계산합니다.
  결과를 얻으려면 아래에 제공된 수식을 원하는 셀에 복사하거나 입력하세요. 그런 다음 채우기 핸들을 아래쪽으로 드래그하여 다른 결과를 얻습니다. 스크린샷 보기:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  주의 사항:이 공식에서 G2 일치시키려는 특정 배경색을 가진 참조 셀입니다. $B$2:$E$12 G2 색상의 셀 수를 계산하려는 범위입니다. TRUE 일치하는 색상의 셀을 합산하는 데 사용됩니다.
  

강력한 기능을 사용하여 배경색별로 셀 수를 계산하고 합산합니다.

프로그래밍에 익숙하지 않은 사람들에게는 VBA가 상당히 복잡해 보일 수 있습니다. 여기서는 강력한 도구를 소개합니다. Excel 용 Kutool그 색상으로 계산 기능을 사용하면 몇 번의 클릭만으로 배경색을 기준으로 쉽게 계산(개수, 합계, 평균 등)을 할 수 있습니다. 인상적이게도, 색상으로 계산 이 기능은 단순한 배경색을 뛰어넘어 글꼴 색상과 조건부 서식을 기준으로 구별하고 계산할 수도 있습니다.

후 Excel용 Kutools 다운로드 및 설치, 먼저 특정 배경색을 기준으로 셀을 계산하거나 합산하려는 데이터 범위를 선택합니다. 다음으로 이동하세요. 쿠툴즈 플러스 선택하고 색상으로 계산.

. 색상으로 계산 대화 상자에서 작업을 지정하십시오.

1. 선택 표준 서식 인사말 색상 방법 드롭 다운 목록;
2. 지정 배경 인사말 개수 유형 드롭다운 목록을 클릭하면 대화 상자에서 각 배경색에 대한 통계 결과를 미리 볼 수 있습니다.
3. 마지막으로 보고서 생성 계산된 결과를 새 통합 문서로 내보냅니다.

결과 :

이제 통계가 포함 된 새 통합 문서를 얻을 수 있습니다. 스크린 샷보기 :

필터 및 SUBTOTAL 기능을 사용하여 배경색별로 셀 수를 계산하고 합산합니다.

아래 스크린샷과 같은 과일 판매 테이블이 있다고 가정하고, 금액 열입니다.

1단계: SUBTOTAL 함수 적용

SUBTOTAL 함수를 입력하려면 빈 셀을 선택하세요.

• 배경색이 동일한 모든 셀의 개수를 계산하려면 다음 수식을 입력하세요.

  =SUBTOTAL(102, F2:F16)

• 동일한 배경색을 가진 모든 셀을 합산하려면 수식을 입력하세요.

  =SUBTOTAL(109, F2:F16)

• 주의 사항: 위의 수식에서, 102 숨겨진 셀을 제외하면서 필터링된 목록의 숫자 값을 계산하는 것을 나타냅니다. 109 숨겨진 셀을 제외하고 필터링된 목록의 값을 합산하는 것을 나타냅니다. F2 : F16 개수 또는 합계가 계산되는 범위입니다.
• 

2단계: 특정 색상을 기준으로 셀 필터링

1. 테이블 헤더를 선택하고 Data > 필터. 스크린 샷보기 :
   
2. 클릭 필터 아이콘  헤더 셀에 금액 열을 클릭하고 색상별로 필터 그리고 당신이 연속적으로 계산할 지정된 색상. 스크린 샷보기 :
   

결과 :

필터링 후 SUBTOTAL 수식은 자동으로 색상이 지정된 셀을 계산하고 합산합니다. 금액 기둥. 스크린 샷보기 :

글꼴 색상을 기준으로 셀 개수 및 합계

Excel에서 글꼴 색상을 기준으로 셀 수를 계산하거나 합산하고 싶으십니까? 주어진 스크린샷과 같이 빨간색, 파란색, 주황색 및 검정색 텍스트가 포함된 셀이 있는 데이터가 있다고 가정해 보겠습니다. Excel에서는 기본적으로 이 작업을 쉽게 수행할 수 없습니다. 하지만 걱정하지 마세요! 이 섹션에서는 이를 수행하는 몇 가지 간단한 방법을 보여 드리겠습니다.

사용자 정의 함수를 사용하여 글꼴 색상을 기준으로 셀 개수 및 합계

특정 글꼴 색상으로 셀을 계산하고 합산하려면 다음 사용자 정의 함수를 사용하여 이 작업을 해결하는 데 도움이 될 수 있습니다. 다음 단계를 수행하십시오.

1단계: VBA 모듈 편집기를 열고 코드를 복사합니다.

1. PR 기사 Alt + F11 키를 눌러 응용 프로그램 용 Microsoft Visual Basic 창.
2. 열린 창에서 다음을 클릭하세요. 끼워 넣다 > 모듈 새 빈 모듈을 만듭니다.
3. 그런 다음 아래 코드를 복사하여 빈 모듈에 붙여넣습니다.
   VBA 코드: 글꼴 색상을 기준으로 셀 개수 및 합계

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

2단계: 글꼴 색상별로 셀 수를 계산하고 합산하는 수식 만들기

위 코드를 붙여넣은 후 모듈 창을 닫고 다음 수식을 적용합니다.

• 특정 글꼴 색상을 기준으로 셀 수를 계산합니다.
  결과를 얻으려면 아래에 제공된 수식을 원하는 셀에 복사하거나 입력하세요. 그런 다음 채우기 핸들을 아래쪽으로 드래그하여 다른 결과를 얻습니다. 스크린샷 보기:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  주의 사항:이 공식에서 G2 일치시키려는 특정 글꼴 색상이 있는 참조 셀입니다. $B$2:$E$12 G2 색상의 셀 수를 계산하려는 범위입니다.
  
• 특정 글꼴 색상을 기준으로 셀 합계를 계산합니다.
  결과를 얻으려면 아래에 제공된 수식을 원하는 셀에 복사하거나 입력하세요. 그런 다음 채우기 핸들을 아래쪽으로 드래그하여 다른 결과를 얻습니다. 스크린샷 보기:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  주의 사항:이 공식에서 G2 일치시키려는 특정 글꼴 색상이 있는 참조 셀입니다. $B$2:$E$12 G2 색상의 셀 수를 계산하려는 범위입니다.
  

손쉬운 기능으로 글꼴 색상을 기준으로 셀 개수 계산 및 합산

글꼴 색상을 기준으로 Excel에서 셀 값을 쉽게 계산하거나 합산하고 싶으십니까? 로 뛰어 들어라 Excel 용 Kutools's 색상으로 계산 특징! 이 스마트 도구를 사용하면 특정 글꼴 색상으로 셀을 계산하고 합산하는 것이 매우 쉬워집니다. 방법을 알아보세요 쿠툴 Excel 환경을 변화시킬 수 있습니다.

후 Excel용 Kutools 다운로드 및 설치, 먼저 특정 글꼴 색상을 기준으로 셀을 계산하거나 합산하려는 데이터 범위를 선택합니다. 그런 다음 클릭하세요. 쿠툴즈 플러스 > 색상으로 계산 를 열려면 색상으로 계산 대화 상자.

. 색상으로 계산 대화 상자에서 작업을 지정하십시오.

1. 선택 표준 서식 인사말 색상 방법 드롭 다운 목록;
2. 지정 글꼴 인사말 개수 유형 드롭다운 목록을 사용하면 대화 상자에서 각 글꼴 색상에 대한 통계 결과를 미리 볼 수 있습니다.
3. 마지막으로 보고서 생성 계산된 결과를 새 통합 문서로 내보냅니다.

결과 :

이제 글꼴 색상을 기준으로 자세한 통계를 표시하는 새 통합 문서가 생겼습니다. 스크린샷 보기:

조건부 서식 색상을 기준으로 셀 개수 및 합계

Excel에서는 일반적으로 조건부 서식을 사용하여 특정 기준을 충족하는 셀에 특정 색상을 적용함으로써 데이터 시각화를 직관적으로 만들 수 있습니다. 하지만 특별히 서식이 지정된 셀의 개수를 계산하거나 합산해야 하는 경우에는 어떻게 해야 할까요? Excel에서는 이에 대한 직접적인 방법을 제공하지 않지만 이 제한 사항을 해결할 수 있는 방법은 다음과 같습니다.

VBA 코드를 사용하여 조건부 서식이 지정된 셀 수 계산 및 합산

Excel에서 조건부 서식이 지정된 셀을 계산하고 합산하는 것은 기본 제공 기능을 사용하는 것이 간단하지 않습니다. 그러나 VBA 코드를 사용하여 이 작업을 수행할 수 있습니다. 이를 위해 VBA를 사용하는 방법을 살펴보겠습니다.

1단계: VBA 모듈 편집기를 열고 코드를 복사합니다.

1. PR 기사 Alt + F11 키를 눌러 응용 프로그램 용 Microsoft Visual Basic 창.
2. 열린 창에서 다음을 클릭하세요. 끼워 넣다 > 모듈 새 빈 모듈을 만듭니다.
3. 그런 다음 아래 코드를 복사하여 빈 모듈에 붙여넣습니다.
   VBA 코드: 조건부 서식 색상을 기준으로 셀 개수 및 합계

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

2단계: 이 VBA 코드 실행

1. 코드를 붙여 넣은 후 F5 키를 눌러 이 코드를 실행하면 프롬프트 상자가 나타납니다. 조건부 서식에 따라 셀 수를 계산하고 합산하려는 데이터 범위를 선택하세요. 그런 다음 클릭하세요. OK, 스크린 샷 참조 :
   
2. 다른 프롬프트 상자에서 계산하고 합계하려는 특정 조건부 서식 색상을 선택하고 OK 버튼, 스크린 샷 참조 :
   

결과 :

이제 지정된 조건부 서식 색상이 적용된 셀의 개수와 합계가 모두 포함된 결과가 팝업 상자에 표시됩니다. 스크린샷 보기:

스마트 기능을 사용하여 조건부 서식이 지정된 셀 수 계산 및 합산

조건부 서식이 지정된 셀의 개수를 계산하고 합산하는 빠르고 쉬운 다른 방법을 찾고 있다면, Excel 용 Kutools 당신의 솔루션입니다. 그것은 색상으로 계산 기능을 사용하면 단 몇 번의 클릭만으로 이 작업을 해결할 수 있습니다. Kutools가 귀하의 작업 흐름에 가져올 수 있는 효율성과 정확성을 알아보세요.

후 Excel용 Kutools 다운로드 및 설치, 먼저 특정 조건부 서식 색상을 기준으로 셀 수를 계산하거나 합산하려는 데이터 범위를 선택합니다. 그런 다음 클릭하세요. 쿠툴즈 플러스 > 색상으로 계산 를 열려면 색상으로 계산 대화 상자.

. 색상으로 계산 대화 상자에서 작업을 지정하십시오.

1. 선택 조건부 서식 인사말 색상 방법 드롭 다운 목록;
2. 지정 배경 인사말 개수 유형 드롭다운 목록을 사용하면 대화 상자에서 각 조건의 서식 색상에 대한 통계 결과를 미리 볼 수 있습니다.
3. 마지막으로 보고서 생성 계산된 결과를 새 통합 문서로 내보냅니다.

결과 :

이제 조건부 서식 색상을 기반으로 자세한 통계를 표시하는 새 통합 문서가 생겼습니다. 스크린샷 보기:

관련 기사: