Đếm hoặc cộng những ô đã được định dạng có điều kiện trong Excel

Trong phương pháp này, chúng ta 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 ô, sau đó đếm hoặc tính tổng theo mã màu một cách dễ dàng trong Excel. Vui lòng 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 làm như ảnh chụp màn hình dưới đây:
(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 khảo (ghi: trong công thức, 38 nghĩa là trả lại mã ô, và Trang tính 4! $ 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 .

3. Bây giờ thêm cột Màu mới vào ngay bảng ban đầu. Tiếp theo gõ công thức = NumColor và kéo nút điều khiển 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 2 bước đầ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ố lượng 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ô trong một phạm vi trống trong trang tính đang hoạt động và nhập công thức bên cạnh nó như hình minh họa bên dưới:
A. Để đếm ô 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 đặt tên được chỉ định, $ E $ 2: $ E $ 20 là Cột Số lượng và bạn có thể thay đổi chúng khi cần.

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

Giả sử các ô màu phân tán trong một phạm vi như hình minh họa bên dưới, cả hai phương pháp trên đều 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 chìa khóa và nó mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

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

VBA: Đếm và tính 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 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 số và tổng, và B: C là phạm vi ô mà bạn muốn tính số lượng và tổng.

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

5. Nếu bạn muốn đếm và tính tổng 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:

Hi vọng là bài viết này sẽ hữu ích với bạn. Xin chào tạm biệt và hẹn gặp lại các bạn trong những bài viết tiếp theo !