Bỏ qua nội dung chính

Mẹo Excel: Đếm/tổng ​​các ô theo màu (nền, phông chữ, định dạng có điều kiện)

Trong công việc hàng ngày, đánh dấu màu là phương pháp phổ biến để nhanh chóng phân biệt và làm nổi bật các dữ liệu quan trọng. Tuy nhiên, làm cách nào để đếm hoặc tính tổng dữ liệu ô dựa trên màu cụ thể (màu tô, màu phông chữ, định dạng có điều kiện)? Theo mặc định, Excel không cung cấp tính năng trực tiếp để đếm hoặc tính tổng theo màu. Tuy nhiên, bằng một số thủ thuật và phương pháp gián tiếp, chúng ta vẫn có thể đạt được điều này. Bài viết này sẽ khám phá cách đếm hoặc tính tổng dữ liệu theo màu sắc.

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

Đếm và tính tổng các ô dựa trên màu phông chữ

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


Video: Đếm và tính tổng các ô dựa trên màu sắc


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

Ví dụ: nếu bạn có một phạm vi dữ liệu trong đó các giá trị được lấp đầy bằng các màu nền khác nhau như minh họa trong ảnh chụp màn hình bên dưới. Để đếm hoặc tính tổng các ô dựa trên một màu cụ thể, Excel không cung cấp tính năng trực tiếp để đếm hoặc tính tổng các ô dựa trên màu nền của chúng. Tuy nhiên, với một chút khéo léo và một số kỹ thuật hữu ích, bạn có thể hoàn thành được nhiệm vụ này. Hãy cùng khám phá một số phương pháp hữu ích trong phần này.


Đếm và tính tổng các ô theo màu nền bằng Hàm do người dùng xác định

Ở đây, chúng tôi sẽ hướng dẫn bạn cách tạo và sử dụng Hàm do người dùng xác định để giải quyết tác vụ này trong Excel. Hãy thực hiện theo các bước sau:

Bước 1: Mở trình chỉnh sửa mô-đun VBA và sao chép mã

  1. Ấn Bản Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
  2. Trong cửa sổ mở ra, nhấp vào Chèn > Mô-đun để tạo một mô-đun trống mới.
  3. Sau đó, sao chép và dán đoạn mã dưới đây vào mô-đun trống.
    Mã 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 = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Bước 2: Tạo công thức đếm và tính tổng ô theo màu nền

Sau khi dán đoạn mã trên, hãy đóng cửa sổ mô-đun, sau đó áp dụng các công thức sau:

  • Đếm các ô dựa trên màu nền cụ thể:
    Sao chép hoặc nhập công thức được cung cấp bên dưới vào ô bạn muốn để có kết quả. Sau đó, kéo núm điều khiển điền xuống dưới để có các kết quả khác. Xem ảnh chụp màn hình:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Chú thích: Trong công thức này, G2 là ô tham chiếu có màu nền cụ thể mà bạn muốn khớp; $B$2:$E$12 là phạm vi mà bạn muốn đếm số ô có màu của G2; KHÔNG ĐÚNG được sử dụng để đếm các ô có màu phù hợp.
  • Tính tổng các ô dựa trên màu nền cụ thể:
    Sao chép hoặc nhập công thức được cung cấp bên dưới vào ô bạn muốn để có kết quả. Sau đó, kéo núm điều khiển điền xuống dưới để có các kết quả khác. Xem ảnh chụp màn hình:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Chú thích: Trong công thức này, G2 là ô tham chiếu có màu nền cụ thể mà bạn muốn khớp; $B$2:$E$12 là phạm vi mà bạn muốn đếm số ô có màu của G2; TRUE được sử dụng để tính tổng các ô có màu phù hợp.

Đếm và tính tổng các ô theo màu nền với tính năng mạnh mẽ

Đối với những người không quen với lập trình, VBA có vẻ khá phức tạp. Ở đây, chúng tôi sẽ giới thiệu một công cụ mạnh mẽ - Kutool dành cho ExcelCủa nó, Đếm theo màu Tính năng này cho phép bạn dễ dàng tính toán (đếm, tổng, trung bình, v.v.) dựa trên màu nền chỉ bằng vài cú nhấp chuột. Ấn tượng, Đếm theo màu Tính năng này không chỉ đơn thuần là màu nền – nó còn có thể phân biệt và tính toán dựa trên màu phông chữ và định dạng có điều kiện.

Sau tải xuống và cài đặt Kutools cho Excel, trước tiên, hãy chọn phạm vi dữ liệu bạn muốn đếm hoặc tính tổng các ô dựa trên màu nền cụ thể. Tiếp theo, điều hướng đến Kutools Plus Và chọn Đếm theo màu.

Trong tạp chí Đếm theo màu hộp thoại, hãy chỉ rõ các thao tác:

  1. Chọn Định dạng tiêu chuẩn từ Phương pháp màu danh sách thả xuống;
  2. Chỉ định Tiểu sử từ Loại đếm danh sách thả xuống và bạn có thể xem trước kết quả thống kê cho từng màu nền trong hộp thoại;
  3. Cuối cùng, hãy nhấp vào Tạo báo cáo để xuất kết quả tính toán sang một sổ làm việc mới.

Kết quả:

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

Lời khuyên:
  1. Mô hình Đếm theo màu Tính năng này cũng hỗ trợ đếm và tính tổng các ô dựa trên màu phông chữ tiêu chuẩn, màu nền hoặc màu phông chữ từ định dạng có điều kiện và sự kết hợp của cả màu tô và màu định dạng có điều kiện.
  2. Quan tâm đến tính năng này, vui lòng nhấp để tải xuống để dùng thử miễn phí trong 30 ngày.

Đếm và tính tổng các ô theo màu nền bằng chức năng Filter và SUBTOTAL

Giả sử chúng ta có một bảng bán 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 Số tiền cột.

Bước 1: Áp dụng hàm SUBTOTAL

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

  • Để đếm tất cả các ô có cùng màu nền, bạn hãy nhập công thức:
    =SUBTOTAL(102, F2:F16)
  • Để tính tổng tất cả các ô có cùng màu nền, bạn hãy nhập công thức;
    =SUBTOTAL(109, F2:F16)
  • Chú thích: trong các công thức trên, 102 đại diện để đếm các giá trị số trong danh sách được lọc trong khi loại trừ các ô ẩn; 109 biểu thị tổng hợp các giá trị trong danh sách được lọc không bao gồm các ô ẩn; F2: F16 là phạm vi mà số lượng hoặc tổng sẽ được tính toán.

Bước 2: Lọc ô dựa trên màu cụ thể

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

Kết quả:

Sau khi lọc, công thức SUBTOTAL sẽ tự động đếm và tính tổng các ô được tô màu trong Số tiền cột. 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ổng nằm trong cùng một cột.

Đếm và tính tổng các ô dựa trên màu phông chữ

Bạn muốn đếm hoặc tính tổng các ô dựa trên màu phông chữ của chúng trong Excel? Giả sử bạn có dữ liệu, như trong ảnh chụp màn hình nhất định, với các ô chứa văn bản có màu đỏ, xanh lam, cam và đen. Excel không thực hiện việc này dễ dàng theo mặc định. Nhưng đừng lo lắng! Trong phần này, chúng tôi sẽ chỉ cho bạn một số thủ thuật đơn giản để làm việc đó.


Đếm và tính tổng các ô dựa trên màu phông chữ với Hàm do người dùng xác định

Để đếm và tính tổng các ô có màu phông chữ cụ thể, Hàm do người dùng xác định sau đây có thể giúp bạn giải quyết tác vụ này. Hãy thực hiện theo các bước sau:

Bước 1: Mở trình chỉnh sửa mô-đun VBA và sao chép mã

  1. Ấn Bản Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
  2. Trong cửa sổ mở ra, nhấp vào Chèn > Mô-đun để tạo một mô-đun trống mới.
  3. Sau đó, sao chép và dán đoạn mã dưới đây vào mô-đun trống.
    Mã VBA: Đếm và tính tổng các ô dựa trên màu phông chữ
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Bước 2: Tạo công thức đếm và tính tổng ô theo màu chữ

Sau khi dán đoạn mã trên, hãy đóng cửa sổ mô-đun, sau đó áp dụng các công thức sau:

  • Đếm các ô dựa trên màu phông chữ cụ thể:
    Sao chép hoặc nhập công thức được cung cấp bên dưới vào ô bạn muốn để có kết quả. Sau đó, kéo núm điều khiển điền xuống dưới để có các kết quả khác. Xem ảnh chụp màn hình:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Chú thích: Trong công thức này, G2 là ô tham chiếu có màu phông chữ cụ thể mà bạn muốn khớp; $B$2:$E$12 là phạm vi mà bạn muốn đếm số ô có màu của G2.
  • Tính tổng các ô dựa trên màu phông chữ cụ thể:
    Sao chép hoặc nhập công thức được cung cấp bên dưới vào ô bạn muốn để có kết quả. Sau đó, kéo núm điều khiển điền xuống dưới để có các kết quả khác. Xem ảnh chụp màn hình:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Chú thích: Trong công thức này, G2 là ô tham chiếu có màu phông chữ cụ thể mà bạn muốn khớp; $B$2:$E$12 là phạm vi mà bạn muốn đếm số ô có màu của G2.

Đếm và tính tổng các ô dựa trên màu phông chữ bằng một tính năng dễ dàng

Bạn muốn dễ dàng đếm hoặc tính tổng các giá trị ô trong Excel dựa trên màu phông chữ? Đi sâu vào Kutools cho Excel's Đếm theo màu tính năng! Với công cụ thông minh này, việc đếm và tính tổng các ô theo màu phông chữ cụ thể trở nên dễ dàng. Khám phá cách Kutools có thể biến đổi trải nghiệm Excel của bạn.

Sau tải xuống và cài đặt Kutools cho Excel, trước tiên, hãy chọn phạm vi dữ liệu bạn muốn đếm hoặc tính tổng các ô dựa trên một màu phông chữ cụ thể. Sau đó nhấn vào Kutools Plus > Đếm theo màu để mở Đếm theo màu hộp thoại.

Trong tạp chí Đếm theo màu hộp thoại, hãy chỉ rõ các thao tác:

  1. Chọn Định dạng tiêu chuẩn từ Phương pháp màu danh sách thả xuống;
  2. Chỉ định Font từ Loại đếm danh sách thả xuống và bạn có thể xem trước kết quả thống kê cho từng màu phông chữ trong hộp thoại;
  3. Cuối cùng, hãy nhấp vào Tạo báo cáo để xuất kết quả tính toán sang một sổ làm việc mới.

Kết quả:

Bây giờ, bạn có một sổ làm việc mới hiển thị số liệu thống kê chi tiết dựa trên màu phông chữ. Xem ảnh chụp màn hình:

Lời khuyên: Quan tâm đến tính năng này, xin vui lòng nhấp để tải xuống để dùng thử miễn phí trong 30 ngày.

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

Trong Excel, bạn thường có thể sử dụng Định dạng có điều kiện để áp dụng màu cụ thể cho các ô đáp ứng các tiêu chí nhất định, giúp trực quan hóa dữ liệu một cách trực quan. Nhưng nếu bạn cần đếm hoặc tính tổng các ô có định dạng đặc biệt đó thì sao? Mặc dù Excel không cung cấp cách trực tiếp cho việc này nhưng đây là những cách để khắc phục hạn chế này.


Đếm và tính tổng các ô được định dạng có điều kiện bằng mã VBA

Việc đếm và tính tổng các ô được định dạng có điều kiện trong Excel không đơn giản bằng cách sử dụng các hàm dựng sẵn. Tuy nhiên, bạn có thể hoàn thành nhiệm vụ này bằng mã VBA. Hãy xem xét cách bạn có thể sử dụng VBA cho việc này:

Bước 1: Mở trình chỉnh sửa mô-đun VBA và sao chép mã

  1. Ấn Bản Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
  2. Trong cửa sổ mở ra, nhấp vào Chèn > Mô-đun để tạo một mô-đun trống mới.
  3. Sau đó, sao chép và dán đoạn mã dưới đây vào mô-đun trống.
    Mã VBA: Đếm và tính tổng các ô dựa trên màu định dạng có điều kiện
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Bước 2: Thực thi mã VBA này

  1. Sau khi dán mã, nhấn F5 để chạy mã này, một hộp nhắc sẽ xuất hiện, vui lòng chọn phạm vi dữ liệu bạn muốn đếm và tính tổng các ô dựa trên định dạng có điều kiện. Sau đó nhấn vào OK, Xem ảnh chụp màn hình:
  2. Trong một hộp nhắc khác, chọn màu định dạng có điều kiện cụ thể mà bạn muốn đếm và tính tổng, rồi bấm vào OK nút, xem ảnh chụp màn hình:

Kết quả:

Bây giờ, kết quả bao gồm cả số lượng và tổng số ô có màu định dạng có điều kiện đã chỉ định sẽ được hiển thị trong hộp bật ra. Xem ảnh chụp màn hình:


Đếm và tính tổng các ô được định dạng có điều kiện bằng tính năng thông minh

Nếu bạn đang tìm kiếm các phương pháp nhanh chóng và dễ dàng khác để đếm và tính tổng các ô được định dạng có điều kiện, Kutools cho Excel là giải pháp phù hợp của bạn. Của nó Đếm theo màu tính năng này có thể giải quyết nhiệm vụ này chỉ trong vài cú nhấp chuột. Đi sâu vào để khám phá hiệu quả và độ chính xác mà Kutools có thể mang lại cho quy trình làm việc của bạn.

Sau tải xuống và cài đặt Kutools cho Excel, trước tiên, hãy chọn phạm vi dữ liệu mà bạn muốn đếm hoặc tính tổng các ô dựa trên màu định dạng có điều kiện cụ thể. Sau đó nhấn vào Kutools Plus > Đếm theo màu để mở Đếm theo màu hộp thoại.

Trong tạp chí Đếm theo màu hộp thoại, hãy chỉ rõ các thao tác:

  1. Chọn Định dạng có điều kiện từ Phương pháp màu danh sách thả xuống;
  2. Chỉ định Tiểu sử từ Loại đếm danh sách thả xuống và bạn có thể xem trước kết quả thống kê cho từng màu định dạng điều kiện trong hộp thoại;
  3. Cuối cùng, hãy nhấp vào Tạo báo cáo để xuất kết quả tính toán sang một sổ làm việc mới.

Kết quả:

Bây giờ, bạn có một sổ làm việc mới hiển thị số liệu thống kê chi tiết dựa trên màu định dạng có điều kiện. Xem ảnh chụp màn hình:

Lời khuyên: Quan tâm đến tính năng này, xin vui lòng nhấp để tải xuống để dùng thử miễn phí trong 30 ngày.

Bài viết liên quan:

  • Nếu màu phông chữ là màu đỏ thì trả về một văn bản cụ thể
  • Làm thế nào bạn có thể trả lại một văn bản cụ thể nếu màu phông chữ là màu đỏ trong một ô khác như hình ảnh chụp màn hình bên dưới? Trong bài viết này, tôi sẽ giới thiệu một số thủ thuật để thực hiện một số thao tác dựa trên văn bản phông chữ màu đỏ trong Excel.
  • Lọc dữ liệu theo nhiều màu
  • Thông thường, trong Excel, bạn có thể nhanh chóng lọc các hàng chỉ có một màu, nhưng, bạn đã bao giờ nghĩ đến việc lọc các hàng có nhiều màu cùng một lúc chưa? Bài viết này, tôi sẽ nói về thủ thuật nhanh chóng để bạn đối phó với vấn đề này.
  • Thêm màu vào danh sách thả xuống
  • Trong Excel, việc tạo danh sách thả xuống có thể giúp ích cho bạn rất nhiều và đôi khi, bạn cần phải tô màu các giá trị danh sách thả xuống tùy theo lựa chọn tương ứng. Ví dụ: tôi đã tạo một danh sách thả xuống gồm các tên trái cây, khi tôi chọn Apple, tôi cần ô được tô màu đỏ tự động và khi tôi chọn Màu cam, ô có thể được tô màu cam.
  • Tô màu các hàng thay thế cho các ô đã hợp nhất
  • Sẽ rất hữu ích khi định dạng các hàng thay thế có màu khác trong một dữ liệu lớn để chúng tôi quét dữ liệu, nhưng đôi khi, có thể có một số ô được hợp nhất trong dữ liệu của bạn. Để đánh dấu các hàng xen kẽ với một màu khác cho các ô đã hợp nhất như hình minh họa bên dưới, làm cách nào bạn có thể giải quyết vấn đề này trong Excel?