Cookie giúp chúng tôi cung cấp dịch vụ của chúng tôi. Bằng cách sử dụng dịch vụ của chúng tôi, bạn đồng ý sử dụng cookie.
Mẹo: Các ngôn ngữ khác được Dịch bởi Google. Bạn có thể ghé thăm English phiên bản của liên kết này.
Đăng nhập
x
or
x
x
Ghi danh
x

or

Làm thế nào để tính và tổng hợp các tế bào dựa trên màu nền trong Excel?

Giả sử bạn có một phạm vi ô với các màu nền khác nhau, chẳng hạn như đỏ, lục, lam, v.v. . Trong Excel, không có công thức trực tiếp để tính Tổng và Số lượng ô màu, ở đây tôi sẽ giới thiệu cho bạn một số cách để giải quyết vấn đề này.

Đếm và Tổng các ô dựa trên màu tô cụ thể bằng Bộ lọc và SUBTOTAL

Đếm và Sum các ô dựa trên màu tô cụ thể bằng hàm GET.CELL

Tính và tổng các ô dựa trên màu điền cụ thể với Chức năng Xác định Người dùng

Đếm và Tổng các ô dựa trên màu tô cụ thể (hoặc màu định dạng có điều kiện) với Kutools cho Excel

Một lần bấm để đếm, tổng và các ô màu trung bình trong Excel

Với sự xuất sắc Count by Color tính năng của Kutools cho Excel, bạn có thể nhanh chóng đếm, tính tổng và các ô trung bình bằng cách tô màu hoặc màu phông chữ được chỉ định chỉ bằng một cú nhấp chuột trong Excel. Ngoài ra, tính năng này cũng sẽ tìm ra các giá trị tối đa và tối thiểu của các ô bằng màu tô hoặc màu phông chữ. Bản dùng thử miễn phí đầy đủ 60-day!
số lượng quảng cáo theo màu 2


Đếm và Sum các ô màu bằng Bộ lọc và SUBTOTAL

Giả sử chúng ta có một bảng doanh số trái cây như ảnh chụp màn hình bên dưới, và chúng ta sẽ đếm hoặc tính tổng các ô màu trong cột Số tiền. Trong tình huống này, chúng ta có thể lọc cột Số lượng theo màu, sau đó đếm hoặc tính tổng các ô được lọc màu bằng hàm SUBTOTAL một cách dễ dàng trong Excel.

1. Chọn các ô trống để nhập hàm SUBTOTAL.

  1. Để đếm tất cả các ô có cùng màu nền, vui lòng nhập công thức = SUBTOTAL (102, E2: E20);
  2. Để tổng hợp tất cả các ô có cùng màu nền, vui lòng nhập công thức = SUBTOTAL (109, E2: E20);


chú thích: Trong cả hai công thức, E2: E20 là cột Số lượng có chứa các ô màu và bạn có thể thay đổi chúng khi bạn cần.

2. Chọn tiêu đề của bảng và nhấp Ngày > Lọc. Xem ảnh chụp màn hình:

3. Nhấp vào biểu tượng Bộ lọc trong ô tiêu đề của cột Số tiền và bấm Lọc theo màu và màu sắc được chỉ định bạn sẽ đếm liên tiếp. Xem ảnh chụp màn hình:

Sau khi lọc, cả hai công thức SUBTOTAL đều đếm và tổng hợp tất cả các ô màu được lọc trong cột Số tiền. Xem ảnh chụp màn hình:

chú thích: Phương pháp này yêu cầu các ô màu bạn sẽ đếm hoặc tính tổng trong cùng một cột.


Đếm hoặc tính tổng các ô màu bằng hàm GET.CELL

Trong phương pháp này, chúng tôi sẽ tạo một phạm vi được đặt tên bằng hàm GET.CELL, lấy mã màu của các ô và sau đó đếm hoặc tính tổng bằng mã màu dễ dàng trong Excel. Hãy làm như sau:

1. Nhấp chuột Công thức > Xác định Tên. Xem ảnh chụp màn hình:

2. Trong hộp thoại Tên mới, vui lòng thực hiện như bên dưới ảnh chụp màn hình:
(1) Nhập tên vào hộp Tên;
(2) Nhập công thức = GET.CELL (38, Sheet4! $ E2) trong hộp Tham chiếu đến (ghi: trong công thức, 38 có nghĩa là trả về mã di động và Sheet4! $ E2 là ô đầu tiên trong cột Số tiền ngoại trừ tiêu đề cột mà bạn cần thay đổi dựa trên dữ liệu bảng của mình.)
(3) Nhấp vào OK nút.

3. Bây giờ thêm một cột Màu mới ngay vào bảng gốc. Tiếp theo gõ công thức = NumColor và kéo tay cầm Tự động điền để áp dụng công thức cho các ô khác trong cột Màu. Xem ảnh chụp màn hình:
chú thích: Trong công thức, NumColor là phạm vi được đặt tên mà chúng tôi đã chỉ định trong các bước 2 đầu tiên. Bạn cần thay đổi nó thành tên được chỉ định mà bạn đặt.

Bây giờ mã màu của mỗi ô trong cột Số tiền trả về trong Cột màu. Xem ảnh chụp màn hình:

4. Sao chép và liệt kê màu tô vào một khoảng trống trong bảng tính hoạt động và nhập công thức bên cạnh nó như ảnh chụp màn hình bên dưới:
A. Để đếm các ô theo màu, vui lòng nhập công thức = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Để tính tổng các ô theo màu, vui lòng nhập công thức = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

chú thích: Trong cả hai công thức, $ F $ 2: $ F $ 20 là cột Màu, NumColor là phạm vi được chỉ định, $ E $ 2: $ E $ 20 là Cột Số lượng và bạn có thể thay đổi chúng khi bạn cần.

Bây giờ bạn sẽ thấy các ô trong cột Số tiền được tính và tính tổng bằng màu tô của chúng.


Tính và tổng các ô dựa trên màu điền cụ thể với Chức năng Xác định Người dùng


Giả sử các ô màu phân tán trong một phạm vi như ảnh chụp màn hình bên dưới, cả hai phương pháp trên không thể đếm hoặc tính tổng các ô màu. Ở đây, phương pháp này sẽ giới thiệu một VBA để giải quyết vấn đề.

1. Giữ ALT + F11 phím, và nó mở ra Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đun, và dán mã sau vào cửa sổ mô-đun.

VBA: Đếm và tổng các ô dựa trên màu nền:

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. Sau đó lưu lại mã, và áp dụng công thức sau:
A. Đếm các ô màu: = chức năng màu (A, B: C, FALSE)
B. Tính tổng các ô màu: = chức năng màu (A, B: C, TRUE)

Lưu ý: Trong các công thức trên, A là ô có màu nền cụ thể mà bạn muốn tính tổng và tổng, và B: C là phạm vi ô mà bạn muốn tính tổng và tổng.

4. Chụp ảnh màn hình sau đây, nhập công thức= chức năng màu (A1, A1: D11, FALSE) đếm các tế bào vàng. Và sử dụng công thức = colorfunction (A1, A1: D11, TRUE) để tổng hợp các tế bào vàng. Xem ảnh chụp màn hình:

5. Nếu bạn muốn đếm và tổng hợp các ô màu khác, vui lòng lặp lại bước 4. Sau đó, bạn sẽ nhận được kết quả sau:


Count và Sum các tế bào dựa trên màu sắc điền cụ thể với Kutools cho Excel

Với chức năng Người dùng Xác định ở trên, bạn cần nhập từng công thức, nếu có nhiều màu sắc khác nhau, phương pháp này sẽ trở nên tẻ nhạt và tốn nhiều thời gian. Nhưng nếu bạn có Kutools cho Excel'S Count by Color tiện ích, bạn có thể nhanh chóng tạo ra một báo cáo của các tế bào được tô màu. Bạn không chỉ có thể đếm và tổng hợp các ô màu, mà còn có thể nhận được giá trị trung bình, tối đa và min của dải màu.

1. Chọn phạm vi mà bạn muốn sử dụng và nhấp vào Kutools Plus > Count by Color, xem ảnh chụp màn hình:

2. Và trong Count by Color hộp thoại, xin vui lòng làm như dưới đây ảnh chụp màn hình:
(1) Chọn Định dạng chuẩn từ Phương pháp màu danh sách thả xuống;
(2) Chọn Bối cảnh từ Loại đếm danh sách thả xuống.
(3) Nhấp vào nút Tạo báo cáo.

Kutools cho Excel - Bao gồm nhiều hơn các công cụ Excel tiện dụng 300. Tính năng dùng thử miễn phí 60-ngày, không cần thẻ tín dụng! Nhận ngay bây giờ!

chú thích: Để đếm và tính tổng các ô màu theo màu định dạng có điều kiện cụ thể, vui lòng chọn Định dạng có điều kiện từ Phương pháp màu danh sách thả xuống trong hộp thoại trên, hoặc chọn Định dạng chuẩn và có điều kiện từ danh sách thả xuống để đếm tất cả các ô được điền bởi màu đã chỉ định.

Bây giờ bạn sẽ nhận được một bảng tính mới với số liệu thống kê. Xem ảnh chụp màn hình:


Bài viết liên quan:

Làm thế nào để tính / sum các tế bào dựa trên các màu phông chữ trong Excel?


Demo: Tính và tổng các ô dựa trên nền, định dạng có điều kiện màu:

Trong video này, Kutools tab và Kutools Plus được thêm bởi Kutools cho Excel. Nếu cần nó, xin vui lòng bấm vào đây. để có một bản dùng thử miễn phí 60 ngày mà không bị giới hạn!



Các công cụ sản xuất được đề nghị

Tab Office

sao vàng1 Mang các tab tiện dụng vào Excel và các phần mềm Office khác, giống như Chrome, Firefox và Internet Explorer mới.

Kutools cho Excel

sao vàng1 Kinh ngạc! Tăng năng suất của bạn trong 5 phút. Không cần bất kỳ kỹ năng đặc biệt, tiết kiệm được hai giờ mỗi ngày!

sao vàng1 300 Các tính năng mới cho Excel, Làm cho Excel dễ dàng và mạnh mẽ:

  • Hợp nhất ô / Hàng / Cột mà không mất dữ liệu.
  • Kết hợp và Hợp nhất nhiều trang tính và Workbooks.
  • So sánh các dãy, sao chép nhiều dãy, chuyển đổi văn bản sang ngày, đơn vị và chuyển đổi tiền tệ.
  • Đếm theo màu sắc, Paging Subtotals, sắp xếp nâng cao và Super Filter,
  • Thêm / Chèn / Xóa / Văn bản / Định dạng / Liên kết / Nhận xét / Tập tin / Workheets Công cụ ...

Ảnh chụp màn hình của Kutools cho 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.
    Thirumalraj · 4 years ago
    Very Helpful...thanks a lott :)
  • To post as a guest, your comment is unpublished.
    Tiny · 4 years ago
    how do you calculate average using this?
  • To post as a guest, your comment is unpublished.
    Sree · 4 years ago
    Thanks a ton. This is awesome.
  • To post as a guest, your comment is unpublished.
    Srinu · 4 years ago
    Hi ,i have applied conditional formatting for color coding ,now i need to count & sum the colored cells ,plz help
  • To post as a guest, your comment is unpublished.
    Nik · 4 years ago
    Wonderful bit of code - thanks so much!