메인 컨텐츠로 가기

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. 색상으로 계산 또한 이 기능은 표준 글꼴 색상, 조건부 서식의 배경 또는 글꼴 색상, 채우기 및 조건부 서식 색상의 조합을 기반으로 셀 계산 및 합산을 지원합니다.
  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에서 이 문제를 어떻게 해결할 수 있습니까?