엑셀 색깔 카운트 - egsel saegkkal kaunteu

HKEBI 2019. 9. 3. 12:19

엑셀을 사용하다가 보면 셀별로 의미를 부여해서 색을 넣어 두는 경우가 있는데요. 이런작업을 한후에 나중에 필요할때 색깔별로 셀을 셀수 있으면 좋은데요. 이번 포스팅에서는 오랜만에 오피스 엑셀 팁을 하나 공유 해볼까 합니다. 바로 엑셀 셀 색으로 갯수 세는 방법인데요. 몇가지 방법이 있지만 그중에서 제일 간단한 방법 한가지를 포스팅 해보려고 합니다. 혹시 엑셀파일중에서 셀 섹상으로 표시해놓고 나중에 갯수를 세어야 하는 일이 있다면 활용해 보시기 바랍니다. 

엑셀 셀을 색으로 갯수 세는 방법은 추가기능을 이용하거나, 혹은 매크로를 이용하는 방법도 있지만 아래 설명해드릴 방법이 제일 간단한 방법입니다. 

간단한 예로, 위와 같이 셀에 색이 들어가 있고 각각 색별로 몇개의 셀이 있는지 세어보도록 하겟습니다. 순서대로 진행을 할테니 그대로 따라해 보시기 바랍니다. ^^

1. 수식 -> 이름정의로 GET.CELL 함수 정의 하기

GET.CELL 이라는 함수가 있습니다. 하지만 시트에서 직접 사용할수 없는 함수 인데요. 아주 오래전 엑셀4 (?) 매크로 기능이라고 합니다. 호환성 유지를 위해서 아직 사용을 할수는 있지만 직접 시트에 작성해서 사용은 불가능한 함수라고 하네요. 이를 사용하기 위해서 수식에서 이름정의로 지정을 한후에 사용을 하는데요.

아래와 같이 상단 리본메뉴에서 [수식] 선택후에 [이름 정의] 를 클릭 하신후에 "새 이름" 창에서 아래와 같이 입력을 하시면 됩니다.

이름 : GetColor (원하시는 이름으로)

범위 : 통합문서

설명 : 공백 (원하시면 넣어도 됩니다.)

참조 대상 : =GET.CELL(38,Sheet1!C2) 

이렇게 입력을 하시면 C2 를 기준으로 GetColor 를 사용하게 되면 해당 셀의 색상값을 가져올수가 있습니다. 생상값은 숫자로 반환이 되며, 이숫자를 이용해서 색상을 셀수가 있게 됩니다.

2. 생성한 GetColor 를 이용해서 색값 구하기 

이제 각 셀의 색상값을 구하시면 되는데요. 색상값을 구하고자 하는 바로 옆 셀에서 함수를 사용하는것과 같이 =get 을 입력해보시면 아래 이미지처럼 위에서 생성한 이름이 보이게 됩니다. 그럼 해당 이름을 선택 하시기만 하면 됩니다. 

그리고 아래로 드래그 해서 그대로 복사를 해주시면 모든 셀들의 색상값을 구할수가 있게 됩니다. 간단하죠?

3. CountIf 함수로 색상 갯수 구하기 

이제 각 셀의 색상값을 구했으니, 갯수만 구하시면 되는데요. 갯수는 countif 를 이용하시면 쉽게 구할수가 있습니다. 

아래 이미지처럼 일단 구할 색상 바로 옆에서 =countif(D2:D11,GetColor) 와 같이 입력을 하시면 되는데요. D2:D11 은 위에서 GetColor 로 구한 색상값이 있는 범위이고, GetColor 는 구하고자 하는 색상의 색상값을 구하기 위해 입력을 했습니다. 노란색 바로 옆에서 위와 같이 입력을 하면 노란색의 색상값과 같은값을 범위에서 구하기 때문에 갯수를 셀수가 있게됩니다. 

최종적으로 아래와 같이 각 색상의 갯수를 정확히 셀수가 있게 됩니다. 

이상으로 엑셀에서 색을 많이 이용하신다면 유용하게 활용할수 있는 엑셀 셀 색으로 갯수 세는 방법 이였습니다. 활용하기에 따라서 매우 유용하게 사용할수 있으니 조금만 들여다 보시면 쉽게 활용할수 있습니다. 그럼 이만 포스팅 마치도록 하겠습니다. 여기 까지 읽어 주셔서 감사합니다. ^^ 다음에 또 유용한 팁으로 포스팅 하도록 할께요 ~

0. 차례

1. 들어가기

2. 필터도구를 이용한 색별로 갯수 세기

3. GET.CELL() 함수을 이용한 색별로 갯수 세기

4. 요약

5. 참고자료(Reference)

6. 관련 글

1. 들어가기

엑셀에서 셀별로 의미를 부여하여 색깔을 넣는 경우가 많습니다. 이런 작업을 한 후, 색깔별로 셀을 세는 경우가 발생할 수 있는 데요. 이번 포스팅은 어떻게 셀 색별로 갯수를 파악할 수 있는 2가지 방법을 말씀드리겠습니다.

​

2. 필터도구를 이용한 색별로 갯수 세기

먼저 아래와 같이 중요와 덜중요를 빨간색, 초록색으로 구분해두었다고 합시다.

셀 포인터를 표 내용에 놓고 [데이터] → [정렬 및 필터] → [필터] or Alt + A + T을 눌러 테이블에 자동필터기능을 추가합니다.

자동 필터 기능이 추가된 표는"▼" 기호가 생기며 다음과 같이 " ▼"를 누르고 [색 기준 필터]에 마우스를 올리면 해당 컬럼에 사용되고 있는 셀 색깔이 뜹니다. 여기서 보고 싶은 색깔을 클릭합니다. 저는 빨간색을 선택해보겠습니다.

그러면 결과가 다음과 같이 빨간색만 필터링되어서 나타납니다. 여기서 드래그를 해서 오른쪽 아래의 갯수를 보면 5개임을 파악할 수 있습니다.

​

3. GET.CELL() 함수을 이용한 색별로 갯수 세기

엑셀에서 GET.CELL() 함수을 사용하여 색별로 갯수를 셀 수 있습니다. 단, GET.CELL() 함수는 단순히 셀 입력창에 입력하는 것이 아니라 셀이름에 정의가 되어야 합니다.

먼저, 셀 이름정의에 GET.CELL() 함수를 넣어주도록 합시다. 데이터가 시작하는 D3에 셀 포인터를 놓고, [수식] → [정의된 이름] → [이름 정의] → [이름 정의] or Alt + M + M + D 을 다음과 같이 누릅니다.

그러면 다음과 같이 [이름 정의] 대화상자가 뜨게 되며 아래와 같이 [이름]에 GetColor로 [참조 대상]에 =GET.CELL(38, Sheet1!C3)을 입력해주고 [확인]을 누릅니다.

여기까지가 D3의 이름이 정의가 된 것이며 D3 셀 활성화가 된 상테에서 "=GetColor"을 입력하고 Enter을 누릅니다. 그리고 채우기 핸들을 이용하여 D4:D11도 "=GetColor"이 입력되도록 합시다.

위에서 확인하는 것처럼 색깔별로 빨간색은 3, 녹색은 10이 나오고 이를 이용하여 그룹 계산이 가능할 수 있음을 판단할 수 있습니다. 그럼 countif() 함수로 색별로 갯수를 세어 보도록 합시다.

먼저 원하는 위치에 countif() 함수에 D2:D11의 범위를 입력하고 빨간색은 3, 녹색은 10으로 criteria으로 설정하여 각각 갯수를 구할 수 있으며 결과는 아래와 같습니다.

위에서 확인할 수 있듯이 빨간색은 5개, 초록색은 4개로 나타남을 확인할 수 있습니다.

​

4. 요약

- 자동필터의 색 기준 필터를 이용하여 색별로 데이터를 필터링을 할 수 있으며 이를 이용하여 색별 갯수를 셀 수 있음.

- GET.CELL() 함수를 이름정의에 이용하여 색깔별로 특정 숫자로 나타낼 수 있으며 이를 이용하여 그룹별 연산이 가능함.

​

5. 참고자료(Reference)

1) https://hkebi.tistory.com/1360

​

6. 관련 글

1) 엑셀에서 필터 기능 사용하기

https://blog.naver.com/pmw9440/221624347470

​

2) 엑셀에서 셀범위 이름정하기

https://blog.naver.com/pmw9440/221492505332