3 cách tính tổng các ô cùng màu trong Excel, Google Sheet nhanh chóng

Hướng dẫn tính tổng các ô cùng màu trong Excel, Google Sheet

Bạn đang có một tập dữ liệu lớn và cần phân loại hoặc tóm tắt dữ liệu theo màu sắc? Với 3 cách tính tổng các ô cùng màu trong Excel, Google sheet dưới đây, bạn có thể nhanh chóng xem tổng số hoặc các chỉ số khác của các dữ liệu được phân nhóm theo màu.

Nếu bảng dữ liệu của bạn có thể lọc theo màu như hình dưới đây, hãy sử dụng hàm SUBTOTAL kết hợp với tính năng Lọc theo màu để tính tổng các ô cùng màu trong Excel. Lưu ý, bạn cũng có thể sử dụng cách này để tính tổng theo màu trong Google Sheet.

Bảng dữ liệu có thể sử dụng hàm SUBTOTAL và tính năng lọc

Khi bạn lọc ra các ô cùng màu, hàm SUBTOTAL sẽ cập nhật các kết quả tính tổng nhanh chóng mà bạn không cần chỉnh sửa lại hàm. Cách thực hiện như sau:

Bước 1: Nhập công thức hàm tại ô cần tính tổng: =SUBTOTAL(9,C3:C17)

Trong đó: 

• 9: Đối số tương ứng với hàm SUM để tính tổng các dữ liệu được hiển thị và bỏ qua dữ liệu đã bị ẩn.
• C3:C17: Vùng dữ liệu cần tính tổng.

Nhập hàm tính tổng như hình

Bước 2: Chọn một ô bất kỳ trong bảng dữ liệu > chọn thẻ Data > chọn Filter để hiển thị bộ lọc bên cạnh mỗi tiêu đề cột.

Chọn bộ lọc Filter như hình

Bước 3: Kích chọn mũi tên lọc tại tiêu đề cột cần tính tổng > chọn Color Filter và chọn màu muốn hiển thị.

Chọn Color Filter để lọc bảng dữ liệu theo màu

Sau khi bạn lọc theo màu, kết quả tính tổng sẽ tự cập nhật lại theo những giá trị đang hiển thị - tức những ô cùng màu trong Excel. Bạn chỉ cần lần lượt đổi bộ lọc sang màu khác là có thể thu được kết quả tính tổng của tất cả các ô cùng màu trong Excel mà không cần thay đổi hàm.

Hàm SUBTOTAL tự động cập nhật kết quả tính tổng các ô cùng màu trong Excel được lọc

Hàm VBA cho phép người dùng tùy chỉnh và dễ dàng ứng dụng trong tính tổng theo màu ô hoặc tính tổng theo màu chữ trong Excel. Đặc biệt, tính tổng theo màu trong Excel bằng hàm VBA sẽ giúp bạn xử lý các bảng dữ liệu phức tạp một cách nhanh chóng hơn nhờ tính toán dữ liệu tại các ô bất kỳ theo màu ô hoặc màu chữ trong hàm mà không cần phải lọc.

2.1 Sử dụng VBA để tính tổng các ô cùng màu trong Excel

Các bước thực hiện:

Bước 1: Tại trang tính chứa dữ liệu cần tính tổng, bạn gõ tổ hợp phím tắt Alt + F11 để mở  giao diện VBA.

Giao diện VBA

Bước 2: Chọn thẻ > chọn Module.

Chọn Module

Bước 3: Tại module mới tạo, bạn copy và dán đoạn code dưới đây:

Function SumByColor (SumRange, SumColor As Range)

Dim SumColorValue As Integer

Dim TotalSum As Long

SumColorValue = SumColor.Interior.ColorIndex

Set rCell = SumRange

For Each rCell In SumRange

If rCell.Interior.ColorIndex = SumColorValue Then

TotalSum = TotalSum + rCell.value

End If

Next rCell

SumByColor = TotalSum

End Function

Bước 4: Nhấn vào biểu tượng Run > nhập tên và chọn Run để chạy chương trình.

Chọn Run để chạy chương trình

Sau đó, bạn tắt cửa sổ VBA để quay lại Excel.

Bước 5: Chọn ô trống bất kỳ và tô màu theo các màu trong bảng dữ liệu.

Trong ví dụ này, Sửa chữa Laptop 24h sẽ tô 1 ô màu cam và 1 ô màu xanh.

Tô màu ô trống theo các màu trong bảng dữ liệu

Bước 6: Tại ô cần tính tổng những giá trị trong ô màu xanh, bạn nhập hàm: =SumByColor(C3:C17,F4)

Trong đó:

• Hàm SumByColor: là hàm tính tổng theo màu ô mà mình đã tạo trong VBA.
• C3:C17: Cột dữ liệu cần tính tổng theo màu.
• F4: Ô chứa màu xanh muốn tính tổng.    

Nhập hàm như hình để tính tổng các ô cùng màu xanh trong Excel

Tương tự để tính tổng các giá trị theo ô màu cam, bạn nhập hàm: =SumByColor(C3:C17,F5)

Xem thêm: Hàm tính tổng trong Excel có điều kiện 

2.2 Sử dụng hàm VBA để tính tổng theo màu chữ trong Excel

Để tính tổng theo màu chữ trong Excel, bạn cũng cần mở giao diện VBA và thêm Module tương tự như trên, chỉ khác ở bước nhập code và nhập hàm:

Tại bước nhập code, bạn nhập dãy lệnh dưới đây để tạo hàm tính tổng theo màu chữ trong giao diện VBA:

Function SumFontColor_VungTinhTong_oDieuKien(rData As Range, cellRefColor As Range)

    Dim indRefColor As Long

    Dim cellCurrent As Range

    Dim sumRes

    Application.Volatile

    sumRes = 0

    indRefColor = cellRefColor.Cells(1, 1).Font.Color

    For Each cellCurrent In rData

        If indRefColor = cellCurrent.Font.Color Then

            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)

        End If

    Next cellCurrent

    SumFontColor_VungTinhTong_oDieuKien = sumRes

End Function

Nhập code như hình để tạo hàm tính tổng theo màu chữ

Sau đó bạn quay lại Excel và nhập hàm như sau để tính tổng doanh thu của nhân viên theo màu chữ đen:

=SumFontColor_VungTinhTong_oDieuKien($B$2:$B$10, A2) 

Trong đó:

• SumFontColor_VungTinhTong_oDieuKien là hàm tính tổng theo màu chữ vừa tạo trong giao diện VBA
• $B$2:$B$10 là vùng dữ liệu cần tính tổng
• A2 là ô chứa chữ đen cần tính tổng

Nhập hàm như hình để tính tổng theo màu chữ đen trong Excel

Tương tự, bạn nhập hàm =SumFontColor_VungTinhTong_oDieuKien($B$2:$B$10, A3) để tính tổng các ô chữ màu đỏ.

Nhập hàm như hình để tính tổng theo màu chữ đỏ trong Excel

Lưu ý: Ký hiệu $B$2:$B$10 nghĩa là vùng dữ liệu B2:B10 đã được cố định giá trị trong hàm để khi sao chép công thức, vùng này không thay đổi. Bạn cố định vùng bằng cách kích chọn B2:B10 trong công thức và nhấn phím F4 hoặc Fn + F4.

Đây là cách cộng theo màu trong Excel khá đơn giản. Trước khi tính tổng theo màu trong Excel, bạn cần hiển thị số biểu trưng cho những màu đó. Cách thực hiện như sau:

Bước 1: Kích chuột tại 1 ô trống bất kỳ để hiển thị giá trị số biểu trưng.

Bước 2: Chọn Formulas > chọn Name Manager để mở bảng Name Manager.

Chọn Name Manager

Bước 3: Chọn New để tạo ra tên mới. Tại bảng New Name, bạn thiết lập như sau:

• Ô Name: bạn gõ tên bất kì
• Ô Refers to: bạn gõ: =Get.Cell(38,C3)

Sau đó nhấn OK.

Giải thích: 

• 38: Là đối số yêu cầu công thức này trích xuất giá trị màu của ô mà nó tham chiếu đến
• C3: bắt đầu tính giá trị từ vị trí này trở đi.

Thiết lập như hình

Bước 4: Chọn một ô ngay cạnh giá trị muốn tính tổng và gõ: =SUMVALUE

Sau đó sao chép công thức này cho toàn bộ cột.

Nhập =SUMVALUE tại ô bên cạnh giá trị cần tính tổng

Sau khi hiển thị, giá trị biểu trưng của màu xanh là số 37, của màu cam là số 40.

Bước 5: Bạn nhập hàm tính tổng các ô màu xanh: 

=SUMIF($D$3:$D$17,37,$C$3:$C$17)

Giải thích: 

• $D$3:$D$17: Vùng dữ liệu chứa giá trị số biểu trưng cho màu.
• 37: Giá trị biểu trưng của ô màu xanh
• $C$3:$C$17: Vùng dữ liệu chứa các giá trị tính tổng.

Nhập hàm như hình để cộng theo màu xanh trong Excel

Tương tự, để tính tổng các giá trị có màu cam, bạn nhập hàm: 

=SUMIF($D$3:$D$17,40,$C$3:$C$17)      

Trên đây là hướng dẫn chi tiết của Sửa chữa Laptop 24h về các cách tính tổng các ô cùng màu trong Excel. Hy vọng bài viết sẽ hữu ích với bạn. Đừng quên theo dõi Sửa chữa Laptop 24h để cập nhật nhiều thủ thuật hữu ích nhé!