Cách tính tổng các ô cùng màu trong excel

LI�N HỆ QUẢNG C�O TẠI Đ�Y : 0988 123 126

1.Đếm và tính tổng các ô được tô màu bằng bộ lọc SUBTOTAL

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

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

-Để đế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)

-Để tính tổng 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)

Sau khi thực hiện 2 bước trên, chúng ta có kết quả như sau:

2.Các bạn có thể tính tổng của các màu Đỏ, Vàng, Xanh bằng cách sau:

• Nhập chữ theo từng màu vào cột bên cạnh.
• Sau đó đặt công thức tại ô D3 như sau: D3 = SUMIF($B$4:$B$19,”Đỏ”,$A:4:$A$19)
• Nhấn Enter
• Sau đó, các bạn kéo công thức xuống ô D4 và D5.
• Tại ô D4, các bạn sửa màu Đỏ thành màu “Vàng, ô D5 sửa màu Đỏ thành màu “xanh” là ok.

II.Đếm ô theo phông chữ và màu nền theo VBA

Trong Excel, chỉ mã VBA mới có thể nhanh chóng đếm các ô dựa trên một phông chữ và màu nền cụ thể.

Bước 1:  Nhấn phím Alt + F11 để mở cửa sổ Microsoft Visual Basic for Applications.

Bước 2:  Nhấp vào Insert -> Module và sao chép và dán bên dưới mã VBA vào cửa sổ Popping Module.

VBA: Đếm các ô dựa trên phông chữ và màu nền. Các bạn chỉ việc copy đoạn Code bên dưới và dán vào Module mới mở.

Sub DisplayFormatCount()

    ‘Updateby20150305

    Dim Rng As Range

    Dim CountRange As Range

    Dim ColorRange As Range

    Dim xBackColor As Long

    Dim xFontColor As Long

    On Error Resume Next

    xTitleId       = “KutoolsforExcel”

    Set CountRange = Application.Selection

    Set CountRange = Application.InputBox(“Count Range :”, xTitleId, CountRange.Address, Type: = 8)

    Set ColorRange = Application.InputBox(“Color Range(single cell):”, xTitleId, Type: = 8)

    Set ColorRange = ColorRange.Range(“A1”)

    xReturn        = 0

    For Each Rng In CountRange

        qqq           = Rng.Value

        xxx           = Rng.DisplayFormat.Interior.Color

        If Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then

            xBackColor   = xBackColor + 1

        End If

        If Rng.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color Then

            xFontColor = xFontColor + 1

        End If

    Next

    MsgBox “BackColor is ” & xBackColor & Chr(10) & “FontColor is ” & xFontColor

End Sub

Bước 3:  Bấm vào nút Run hoặc nhấn phím F5 để chạy mã, sau đó chọn phạm vi bạn cần trong hộp thoại Popping. Xem ảnh bên dưới.

Bước 4:   Bấm OK, sau đó chọn một ô có phông chữ và màu nền bạn muốn đếm các ô dựa trên.

Bước 5:  Bấm OK. Sau đó, một hộp thoại mở ra để cho bạn biết có bao nhiêu ô có cùng màu nền và màu phông chữ với ô đã chọn trong phạm vi.

Như vậy là mình vừa trình bày xong với các bạn, các cách đếm số ô theo màu và tính tổng các giá trị trong ô dựa trên màu sắc của ô trong Excel. Hi vọng thủ thuật này sẽ hữu ích trong công việc của các bạn. Chúc các bạn thành công !

Tham khảo thêm các bài viết bên dưới.

–Cách sử dụng hàm search trong excel

–Hướng dẫn cách dùng hàm Round trong Excel

–Cách dùng hàm UPPER LOWER PROPER trong EXCEL