엑셀 색깔 카운트, 색깔 합계 3초 만에 구하기

찾기 및 바꾸기와 이름 정의 범위를 활용하여 매크로 없이 색깔별 카운트, 색깔별 합계 구하는 방법!

홈페이지 » 엑셀 색깔 카운트, 색깔 합계 3초 만에 구하기

엑셀 색깔 카운트, 색깔 합계 구하기

엑셀 색깔 카운트, 색깔 합계 목차 바로가기
영상강의

큰화면보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀프레소] 엑셀 색깔별 카운트, 색깔별 합계 구하기
    예제파일
  • [엑셀프레소] 엑셀 색깔별 카운트, 색깔별 합계 구하기
    완성파일

색깔 카운트 / 합계 구하기 공식

이번 강의에서는 매크로를 사용하지 않고 찾기 및 바꾸기와 이름정의 범위를 활용하여 색깔별 카운트, 색깔별 합계를 구하는 방법에 대해 소개해드렸습니다. 이번 강의에서 다룬 내용을 활용하면 색깔별 카운트, 색깔별 합계 이외에 색깔별 평균, 색깔별 최대값 등 다양한 방법으로 활용할 수 있습니다.

매크로를 활용하기 위한 전체 명령문과 사용법은 아래 자세히 설명해드렸습니다.

색깔별 카운트

= COUNT ( 색깔범위 )

색깔별 합계

= SUM ( 색깔범위 )

색깔별 카운트, 합계 구하는 방법

  1. 단축키 Ctrl + F 키로 찾기 및 바꾸기 대화상자를 실행합니다. [옵션] 버튼을 눌러 상세 옵션을 활성화합니다.

    엑셀 색깔별 합계 찾기 및 바꾸기
    찾기 및 바꾸기 대화상자에서 '서식'으로 이동합니다.
  2. [서식]을 서식 찾기로 이동한 뒤, [채우기] (또는 글꼴)에서 색깔별 카운트, 색깔별 합계를 구하기 위한 서식을 지정합니다.

    색깔별 카운트 서식 지정
    색깔별 카운트, 색깔별 합계를 구하기 위한 서식을 지정합니다.
  3. [모두 찾기]를 클릭한 뒤, 검색결과에서 Ctrl + A 키를 눌러 검색 결과를 한 번에 선택합니다.

    찾기 및 바꾸기 모두 찾기
    모두 찾기로 검색한 뒤, 검색 결과를 한 번에 선택합니다.
  4. 이름상자로 이동한 뒤, '내범위'로 이름정의범위를 추가합니다.

    엑셀 색깔 지정된 이름정의범위
    선택된 범위를 이름정의범위에 추가합니다.
  5. 빈 셀로 이동한 뒤, SUM 함수 (또는 COUNT 함수)로 색깔별 카운트, 색깔별 합계를 계산합니다.

    엑셀 색깔별 합계 구하기
    함수로 색깔별 카운트, 색깔별 합계를 계산합니다.

매크로로 색깔별 카운트, 색깔별 합계 구하기

아래 VBA 명령문을 엑셀에 복사/붙여넣기 하여 사용자 함수를 추하면 색깔별 카운트, 색깔별 합계를 손쉽게 구할 수 있습니다.

  1. 엑셀을 실행한 뒤, 단축키 Alt + F11 키로 매크로 편집기를 실행합니다. [삽입] - [모듈]로 새로운 모듈을 추가합니다.

    엑셀 모듈 추가
    매크로 편집기 - 삽입 - 모듈로 새로운 모듈을 추가합니다.
  2. 아래 명령문을 복사한 뒤, 모듈안에 붙여넣기 합니다. (매크로를 포함한 뒤, 파일 저장시에는 반드시 '매크로가 포함된 통합문서(*.xlsm)' 형태로 저장해야합니다.)
    Option Explicit
     
    Function SumByBgColor(Rng As Range, Ref As Range)
     
    Dim r As Range: Dim v As Double
     
    For Each r In Rng
        If CheckColour(r) = CheckColour(Ref) Then v = Application.WorksheetFunction.Sum(v, r.Value)
    Next
     
    SumByBgColor = v
     
    End Function
     
    Function SumByFontColor(Rng As Range, Ref As Range)
     
    Dim r As Range: Dim v As Double
     
    For Each r In Rng
        If CheckColour(r, True) = CheckColour(Ref, True) Then v = Application.WorksheetFunction.Sum(v, r.Value)
    Next
     
    SumByFontColor = v
     
    End Function
     
    Public Function CheckColour(r As Range, Optional isFont As Boolean = False)
    Application.Volatile
    If isFont = False Then CheckColour = r.Parent.Evaluate("GetColor(" & r.Address(False, False) & ")") Else CheckColour = r.Parent.Evaluate("GetFontColour(" & r.Address(False, False) & ")")
    End Function
     
    Public Function GetColor(r As Range):    GetColor = r.DisplayFormat.Interior.Color: End Function
    Public Function GetFontColor(r As Range):    GetFontColor = r.DisplayFormat.Font.Color: End Function
  3. VBA 사용자 함수로 배경색이 같을 경우, 또는 글씨색이 같을 경우 합계를 구할 수 있습니다.
    =SumByBgColor ( 합계범위, 참조셀 )
    '참조셀과 배경색이 같을 경우 합계를 구합니다.
    =SumByFontColor (합계범위, 참조셀)
    '참조셀과 글씨색이 같을 경우 합계를 구합니다.
  4. 명령문의 아래부분을 필요에 따라 바꾸면 색깔이 같을 경우 카운트, 합계, 최대값, 평균 등으로 손쉽게 계산할 수 있습니다.
    '합계 구하기
    Application.WorksheetFunction.Sum(v, r.Value)
    '개수 구하기
    Application.WorksheetFunction.CountA(v, r.Value)
    '최대값 구하기
    Application.WorksheetFunction.Max(v, r.Value)
    '평균 구하기
    Application.WorksheetFunction.Average(v, r.Value)
5 5 투표
게시글평점
guest
6 댓글
Inline Feedbacks
모든 댓글 보기
테일즈
테일즈
2021년 2월 4일 11:20 오전
게시글평점 :
     

질문이 영상강의로 뿌듯!

좁쌀영감
좁쌀영감
2021년 2월 6일 5:26 오전
게시글평점 :
     

정말 아이디어가 좋습니다. 감사해요~

용갈스
용갈스
2021년 2월 14일 12:43 오후
게시글평점 :
     

끝까지 배워 보겠습니다

빈파
빈파
2021년 5월 6일 10:52 오후
게시글평점 :
     

대단하네요

슝슝
슝슝
2021년 6월 19일 12:23 오후
게시글평점 :
     

엑셀계의 아인슈타인

버섯치킨도리아
버섯치킨도리아
2021년 6월 19일 3:53 오후
게시글평점 :
     

이거 어떻게 하는건지 궁금했는데 ㅠㅠ 엑셀에서도 바로 가능하다니... 알면 알 수록 엑셀은 신기하네요, 안되는게 뭐지? 👀 ㅋㅋ

6
0
여러분의 생각을 댓글로 남겨주세요.x