Nắm rõ cách tính tổng các ô cùng màu trong Excel, Google Sheet sẽ giúp trực quan hóa bảng tính, cho phép bạn dễ dàng phân tích dữ liệu hoặc thuận tiện tạo biểu đồ. Trong bài viết này, Sửa chữa Laptop 24h sẽ hướng dẫn chi tiết 3 cách tính tổng theo màu trong Excel. Show  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. 1. Tính tổng các ô cùng màu trong Excel bằng hàm SUBTOTALNế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 2. Tính tổng theo màu trong Excel bằng hàm VBAHà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 ExcelCá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 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 đó:
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. 3. Sử dụng Get.CELL + Mẹo Named RangeĐâ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é! |
