메인 컨텐츠로 가기

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

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

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

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

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


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


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

예를 들어, 아래 스크린샷에 표시된 것처럼 값이 다양한 배경색으로 채워지는 데이터 범위가 있는 경우입니다. 특정 색상을 기준으로 셀 수를 계산하거나 합산하기 위해 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. 마지막으로 보고서 생성 계산된 결과를 새 통합 문서로 내보냅니다.

결과 :

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

방문 꿀팁:
  1. 최대 XNUMXW 출력을 제공하는 색상으로 계산 또한 이 기능은 표준 글꼴 색상, 조건부 서식의 배경 또는 글꼴 색상, 채우기 및 조건부 서식 색상의 조합을 기반으로 셀 계산 및 합산을 지원합니다.
  2. 이 기능에 관심이 있으시면 30일 동안 무료 평가판을 받으려면 클릭하여 다운로드.

필터 및 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. 마지막으로 보고서 생성 계산된 결과를 새 통합 문서로 내보냅니다.

결과 :

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

방문 꿀팁: 이 기능에 관심이 있으신가요? 30일 동안 무료 평가판을 받으려면 클릭하여 다운로드.

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

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. 마지막으로 보고서 생성 계산된 결과를 새 통합 문서로 내보냅니다.

결과 :

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

방문 꿀팁: 이 기능에 관심이 있으신가요? 30일 동안 무료 평가판을 받으려면 클릭하여 다운로드.

관련 기사:

  • 글꼴 색상이 빨간색이면 특정 텍스트를 반환합니다.
  • 아래 스크린샷과 같이 다른 셀의 글꼴 색상이 빨간색인 경우 특정 텍스트를 어떻게 반환할 수 있습니까? 이 기사에서는 Excel의 빨간색 글꼴 텍스트를 기반으로 몇 가지 작업을 수행하는 몇 가지 트릭을 소개합니다.
  • 다양한 색상으로 데이터 필터링
  • 일반적으로 Excel에서는 한 가지 색으로 만 행을 빠르게 필터링 할 수 있지만 동시에 여러 색으로 된 행을 필터링하는 것을 고려한 적이 있습니까? 이 기사에서는이 문제를 다루는 빠른 트릭에 대해 설명합니다.
  • 드롭 다운 목록에 색상 추가
  • Excel에서는 드롭다운 목록을 만드는 것이 많은 도움이 될 수 있으며 때로는 선택한 해당 항목에 따라 드롭다운 목록 값을 색상으로 구분해야 할 수도 있습니다. 예를 들어, 과일 이름의 드롭다운 목록을 만들었습니다. Apple을 선택하면 셀이 자동으로 빨간색으로 표시되고, 주황색을 선택하면 셀이 주황색으로 표시됩니다.
  • 병합된 셀의 대체 행 색상 지정
  • 우리가 데이터를 스캔할 수 있도록 대용량 데이터에서 다른 색상으로 대체 행의 서식을 지정하는 것이 매우 유용하지만 때로는 데이터에 병합된 셀이 있을 수 있습니다. 아래 스크린샷과 같이 병합된 셀에 대해 다른 색상으로 행을 번갈아 강조 표시하려면 Excel에서 이 문제를 어떻게 해결할 수 있습니까?
Comments (237)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
This comment was minimized by the moderator on the site
#NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations