쿠키는 우리의 서비스를 제공 도움이됩니다. 우리의 서비스를 사용하면 쿠키의 우리의 사용에 동의합니다.
팁 : 다른 언어는 Google 번역입니다. 방문하실 수 있습니다. English 이 링크의 버전.
로그인
x
or
x
x
등록하기
x

or

Excel에서 배경색을 기반으로 셀을 계산하고 합산하는 방법은 무엇입니까?

빨강, 초록, 파랑 ​​등 다양한 배경색을 가진 셀 범위가 있다고 가정하면 해당 범위의 셀에 특정 배경색이 몇 개 있는지 계산해야하며 동일한 특정 색으로 채색 된 셀을 합산해야합니다 . Excel에서는 색상 셀의 합계 및 개수를 계산하는 직접 수식이 없으므로 여기에서이 문제를 해결하는 몇 가지 방법을 소개합니다.

필터 및 SUBTOTAL에 의해 특정 채우기 색상을 기반으로 셀 개수 및 합계

GET.CELL 함수를 사용하여 특정 채우기 색상을 기반으로 셀 수를 계산하고 합합니다.

사용자 정의 함수로 특정 채우기 색상을 기반으로 셀 수를 계산하고 합계합니다.

Excel 용 Kutools를 사용하여 특정 채우기 색상 (또는 조건부 서식 색상)을 기반으로 셀 수를 계산하고 합치십시오.

한 번의 클릭으로 계산, 합계 및 평균 컬러 셀 Excel에서

훌륭한 색상으로 계산 특징 Excel 용 KutoolsExcel에서 한 번의 클릭으로 지정된 채우기 색 또는 글꼴 색으로 셀을 빠르게 계산, 합계 및 평균 할 수 있습니다. 게다가이 기능은 채우기 색이나 글꼴 색으로 셀의 최대 값과 최소값을 찾습니다. 전체 기능 무료 평가판 60 일!
색상 2에 의한 광고 수


필터 및 하위로 셀 개수 및 합계를 계산합니다.

아래에 표시된 스크린 샷과 같은 과일 판매 테이블이 있다고 가정하고 Amount 열의 유색 세포를 계산하거나 합산합니다. 이 경우 색으로 Amount 열을 필터링 한 다음 Excel에서 SUBTOTAL 함수를 사용하여 필터링 된 색의 셀을 쉽게 계산하거나 합계 할 수 있습니다.

1. 빈 셀을 선택하여 SUBTOTAL 기능을 입력하십시오.

  1. 배경색이 같은 모든 셀을 계산하려면 수식을 입력하십시오. = SUBTOTAL (102, E2 : E20);
  2. 배경색이 같은 모든 셀을 합치려면 수식을 입력하십시오. = SUBTOTAL (109, E2 : E20);


주의 사항: 두 수식 모두에서 E2 : E20은 색칠 된 셀을 포함하는 Amount 열이며 필요에 따라 변경할 수 있습니다.

2. 테이블의 헤더를 선택하고를 클릭하십시오. 데이터 > 필터. 스크린 샷보기 :

3. 필터 아이콘을 클릭하십시오. Amount 열의 머리글 셀에서 색상별로 필터 지정된 색상은 연속해서 계산됩니다. 스크린 샷보기 :

필터링 후 SUBTOTAL 수식 둘 다 Amount 열의 모든 필터링 된 색 셀을 자동으로 계산하고 합산합니다. 스크린 샷보기 :

주의 사항:이 방법을 사용하려면 계산할 색이 지정된 셀이 필요하거나 합이 같은 열에 있어야합니다.


GET.CELL 함수로 채색 된 셀 수 또는 합계

이 메서드에서는 GET.CELL 함수를 사용하여 명명 된 범위를 만들고 셀의 색 코드를 가져온 다음 Excel에서 색 코드로 쉽게 계산하거나 합계합니다. 다음과 같이하십시오 :

1. 딸깍 하는 소리 방식 > 이름 정의. 스크린 샷보기 :

2. 새 이름 대화 상자에서 다음과 같이 표시된 스크린 샷을 수행하십시오.
(1) 이름 상자에 이름을 입력하십시오.
(2) 수식 입력 = GET.CELL (38, Sheet4! $ E2) 참조 대상 상자 (주의: 공식에서, 38 셀 코드를 반환하는 것을 의미합니다. Sheet4! $ E2 테이블 데이터를 기반으로 변경해야하는 열 머리글을 제외한 금액 열의 첫 번째 셀입니다.
(3) OK 버튼을 클릭합니다.

3. 이제 새로운 색상 열을 원래 테이블에 바로 추가하십시오. 다음에 수식을 입력하십시오. = NumColor , AutoFill 핸들을 드래그하여 수식을 Color 열의 다른 셀에 적용합니다. 스크린 샷보기 :
주의 사항: 공식에서, NumColor 첫 번째 2 단계에서 지정한 명명 된 범위입니다. 지정한 이름으로 변경해야합니다.

Amount 열의 각 셀의 색상 코드가 색상 열에서 반환됩니다. 스크린 샷보기 :

4. 활성 워크 시트의 빈 범위에 채우기 색상을 복사하고 나열하고 아래 스크린 샷과 같이 그 옆에 공식을 입력하십시오.
A. 색으로 셀 수를 계산하려면 수식을 입력하십시오. = COUNTIF ($ F $ 2 : $ F $ 20, NumColor);
B. 색상별로 세포를 합치려면 수식을 입력하십시오. = SUMIF ($ F $ 2 : $ F $ 20, NumColor, $ E $ 2 : $ E $ 20).

주의 사항: 두 공식 모두에서, $ F $ 2 : $ F $ 20 색상 열입니다. NumColor 지정된 명명 된 범위입니다. $ E $ 2 : $ E $ 20 Amount Column이며 필요에 따라 변경할 수 있습니다.

이제 Amount 열의 셀이 채워지고 채우기 색상으로 합계가 표시됩니다.


사용자 정의 함수로 특정 채우기 색상을 기반으로 셀 수를 계산하고 합계합니다.


위의 스크린 샷과 같이 범위 내에서 유색 세포가 산란된다고 가정하면 위의 두 방법 모두 유색 세포를 계산하거나 합산 할 수 없습니다. 여기서이 방법은 문제를 해결하기 위해 VBA를 도입합니다.

1. 누르고있어. ALT + F11 키를 열면 응용 프로그램 용 Microsoft Visual Basic 창.

2. 딸깍 하는 소리 끼워 넣다 > 모듈다음 코드를 모듈 창에 붙여 넣습니다.

VBA : 배경색을 기반으로 셀 수를 계산하고 합계합니다.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. 그런 다음 코드를 저장하고 다음 수식을 적용하십시오.
A. 유색 세포 수를 세어 라. = colorfunction (A, B : C, FALSE)
B. 유색 세포를 합친다. = colorfunction (A, B : C, TRUE)

참고 : 위의 수식에서, A 셀 수를 계산하려는 특정 배경색을 가진 셀이며 기원전 개수와 합계를 계산할 셀 범위입니다.

4. 예를 들어 다음 스크린 샷을 찍은 다음 수식을 입력하십시오.= 컬러 함수 (A1, A1 : D11, FALSE) 노란 세포를 세지. 그리고 공식을 사용하십시오. = 컬러 함수 (A1, A1 : D11, TRUE) 노란 세포를 합친다. 스크린 샷보기 :

5. 다른 색의 세포를 세고 합치려면 4 단계를 반복하십시오. 그러면 다음과 같은 결과가 나타납니다.


Excel 용 Kutools로 특정 채우기 색상을 기반으로 셀 개수 및 합계 계산

위의 사용자 정의 함수를 사용하면 수식을 하나씩 입력해야합니다. 색상이 많이있는 경우이 방법은 지루하고 시간이 오래 걸립니다. 하지만 네가 가진다면 Excel 용 Kutools색상으로 계산 유틸리티를 사용하면 유색 세포의 보고서를 신속하게 생성 할 수 있습니다. 당신은 유색 세포를 세고 합계 할 수있을뿐만 아니라 색깔 범위의 평균, 최대 및 최소 값을 얻을 수 있습니다.

1. 사용할 범위를 선택하고 다음을 클릭하십시오. Kutools 기타 > 색상으로 계산, 스크린 샷보기 :

2. 그리고에 색상으로 계산 대화 상자에서 다음과 같이 화면을 캡처하십시오.
(1) 선택 표준 서식 ~로부터 색상 방법 드롭 다운 목록;
(2) 선택 배경 ~로부터 조사 유형 드롭 다운 목록.
(3) 보고서 생성 버튼을 클릭하십시오.

Excel 용 Kutools - 300 이상의 유용한 Excel 도구가 포함되어 있습니다. 전체 기능 무료 평가판 60 일, 신용 카드 필요 없음! 서두르세요!

주의 사항: 특정 조건부 서식 색상으로 채색 된 셀을 계산하고 합치려면 선택하십시오. 조건부 서식 ~로부터 색상 방법 위의 대화 상자에서 드롭 다운 목록을 선택하거나 표준 및 조건부 서식 드롭 다운 목록에서 지정된 색상으로 채워진 모든 셀을 계산합니다.

이제 통계가 포함 된 새로운 통합 문서를 받게됩니다. 스크린 샷보기 :


관련 기사 :

Excel에서 글꼴 색상을 기반으로 셀을 계산 / 합계하는 방법은 무엇입니까?


데모 : 배경, 조건부 서식 색상을 기준으로 셀 수를 계산하고 합합니다.

이 비디오에서 Kutools 탭과 Kutools 기타 탭이에 의해 추가됩니다. Excel 용 Kutools. 필요하면 클릭하십시오. here 제한없이 60 일 무료 평가판을 사용할 수 있습니다!



권장 생산성 도구

사무실 탭

금색 star1 Chrome, Firefox 및 새 Internet Explorer와 마찬가지로 Excel 및 기타 Office 소프트웨어에 편리한 탭을 가져옵니다.

Excel 용 Kutools

금색 star1 놀랄 만한! 5 분 동안 생산성을 높입니다. 매일 2 시간을 절약 할 수있는 특별한 기술이 필요하지 않습니다!

금색 star1 300 Excel의 새로운 기능, Excel을 훨씬 쉽고 강력하게 만듭니다 :

  • 데이터 손실없이 셀 / 행 / 열 병합.
  • 여러 시트 및 통합 문서 결합 및 통합.
  • 범위 비교, 여러 범위 복사, 날짜, 단위 및 통화 변환 텍스트 변환.
  • 색상, 페이징 부분합, 고급 정렬 및 수퍼 필터,
  • 추가 선택 / 삽입 / 삭제 / 텍스트 / 형식 / 링크 / 주석 / 통합 문서 / 워크 시트 도구 ...

Kutools for Excel의 스크린 샷

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Hien · 1 years ago
    $2,322.29 2322.29
    $100.00 128.66
    $76.13 76.13
    $450.02 450.02
    $28.66 128.66
    $544.31 488.77

    0 0
    $278.66
    $636.76 636.76
    Please help the numbers on the left are the correct number but when i dragged down the formula i get the numbers on the right. As you can see some of the numbers are correct but others come out wrong. Please help me, i dont understand why this is not working. Thanks.
  • To post as a guest, your comment is unpublished.
    Hien · 1 years ago
    Please help, why do some of my numbers come out correct and some do not? Maybe two out of the 6 will be inccorect because its adding a different number from a different color.
    • To post as a guest, your comment is unpublished.
      mc · 1 years ago
      Change everything that says ".colorindex" to ".color"

      .ColorIndex only supports 256 Colours. And will round similar colours together essentuially.
  • To post as a guest, your comment is unpublished.
    RB · 1 years ago
    Very Helpful.

    Thanks a Lot.

    BR,
    RB.
  • To post as a guest, your comment is unpublished.
    Vamshi · 1 years ago
    The above function is only working for the data in a single sheet
    I want to capture the data from multiple workbooks.

    Please help .
  • To post as a guest, your comment is unpublished.
    Vamshi · 1 years ago
    Hello,

    The above function works only if the all the coloured cells are in single sheet .
    I am unable to perform the colorfunction sum if the coloured cells are in different sheets.
    Any help around this.

    EX;- Trying to consolidate the sum of coloured cells in a Master sheet it says #VALUE