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 đó:
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.
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 đó:
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
Để 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 đó:
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:
Sau đó nhấn OK.
Giải thích:
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:
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é!
Tin hot
Đặt lịch
![[Tổng hợp] 50+ hình nền đen hiện đại, chất lượng cao](https://suachualaptop24h.com/images/news/2021/03/31/small/tong-hop-50-hinh-nen-den-bao-dep-va-chat-luong_1617134787.jpg)
