엑셀 셀 색깔 개수를 세는 방법
엑셀의 데이터 값 특이사항 등을 표시하기 위해 셀의 색깔을 변경하는 경우가 있을 것이다.
보통 이런 경우 가독성 면에서는 좋을지도 모르지만 나중에 색깔이 변경된 셀의 개수를 확인하는 등의 작업이 필요하게 되면 반대로 불편한 상황이 발생할 수 있다.
셀 색깔 개수 세기
엑셀에서는 셀 색깔과 관련된 함수는 존재하지 않기 때문에 매크로를 통해 함수를 만들어서 사용해야 한다.
1. 비주얼 베이직 실행.
엑셀을 실행 한 뒤 [Alt + F11]을 눌러 비주얼 베이직을 실행하자.
2. 매크로 코드 입력.
비주얼 베이직 창이 나타나면 상단 메뉴에서 [삽입 -> 모듈]을 클릭.
아래의 코드를 복사해서 붙여 넣자.
| Function CountCellColors(rng As Range, Optional mode As Integer = 0) As Variant Dim cell As Range Dim colorDict As Object Dim totalCount As Long Dim result As String ' 색상별 개수를 저장할 Dictionary 초기화 Set colorDict = CreateObject("Scripting.Dictionary") totalCount = 0 ' 색상별 개수 계산 For Each cell In rng Dim cellColor As Long Dim colorIndex As Long On Error Resume Next cellColor = cell.Interior.Color colorIndex = cell.Interior.colorIndex On Error GoTo 0 ' "채우기 없음" 상태 제외 If colorIndex <> -4142 Then If colorDict.exists(cellColor) Then colorDict(cellColor) = colorDict(cellColor) + 1 Else colorDict.Add cellColor, 1 End If totalCount = totalCount + 1 End If Next cell ' 조건에 따라 결과 반환 If mode = 1 Then ' 옵션이 1일 경우: 색상별 개수 반환 result = "" Dim colorKey As Variant For Each colorKey In colorDict.Keys result = result & "Color" & colorKey & ":" & colorDict(colorKey) & "개 " & vbNewLine Next colorKey If result = "" Then result = "색상이 있는 셀이 없습니다." End If CountCellColors = result Else ' 옵션이 없거나 1이 아닌 경우: 전체 색상 개수 반환 CountCellColors = totalCount End If End Function |
주석을 보면 대충 어떻게 돌아가는지 감이 올 것이다.
셀에 아무 색도 없으면 개수에 포함하지 않았는데 포함하고 싶다면 채우기 없음 상태 제외 주석이 있는 부분을 지우면 된다.
3. 함수 사용 방법.
함수는 일반 엑셀 함수를 사용하듯이 함수의 이름을 호출하고 매개변수를 입력하면 된다.
매개변수는 셀 범위와 옵션으로 되어있는데 옵션을 넣지 않거나 1 외의 다른 숫자를 넣으면 범위 전체에서 색깔이 있는 함수의 개수를 합쳐서 반환하며, 1을 입력하면 각 색깔 별 개수를 반환하게 되어있다.
| =CountCellColors(셀, 옵션) =CountCellColors($A$1:$B$10) =CountCellColors($A$1:$B$10, 1) |
그 외 내용
- 위 매크로는 조건부 서식으로 변경된 셀의 색깔은 개수를 세지 않는다.
애초에 조건부 서식으로 셀 색깔을 변경했다면 조건부 서식을 만들 때의 조건으로 개수를 세면 된다.
예를 들어 셀 값이 합격인 경우에 조건부 서식으로 셀 색깔을 변경했다면 합격의 개수를 세는 것이 곧 조건부 서식으로 변경된 셀 색깔의 개수를 세는 것과 같다.
- 특수한 상황이 아니라면 특이사항을 위해 색깔을 직접 변경하는 것은 좋지 않다.
차라리 특이사항을 별도의 셀에 값으로 입력하고 해당 값으로 조건부 서식을 만드는 것이 나중을 위해서도 여러모로 좋기 때문이다.
- 매크로는 만들기 나름이다.
마음에 들지 않는 부분이나 원하는 부분이 있다면 수정해서 입맛에 맞게 사용하자.
아래와 같은 방식으로도 함수를 만들 수 있다.