Ví dụ: Mình có bảng dữ liệu như bên dưới:
Yêu cầu: Mình sẽ phải tính tổng vào các giá trị ô có màu.
Để làm được như vậy, mình sẽ hướng dẫn 3 cách làm nhanh mà đơn giản:
Tính tổng theo màu với hàm SUBTOTAL
Thường thì để tính tổng, bạn sẽ sử dụng hàm SUM để tính. Tuy nhiên, trong trường hợp này, mình sẽ sử dụng hàm SUBTOTAL để tính tổng theo màu để khi bạn lọc chọn ra điều kiện thì hàm SUBTOTAL sẽ cập nhật lại kết quả nhanh chóng.
Các bước như sau:
Bước 1: Tại ô cần hiển thị kết quả tính tổng, bạn gõ công thức sau: =SUBTOTAL(9,C3:C17)
Giải thích: 9: Số thứ tự để tính tổng.
C3:C17: Vùng dữ liệu muốn tính tổng.
Bước 2: Chọn một ô bất kì trong vùng dữ liệu, bạn vào thẻ Data => chọn vào công cụ Filter để hiển thị bộ lọc ngay bên cạnh mỗi tiêu đề cột.
Bước 3: Bạn sẽ chọn bộ lọc tại tiêu đề cột tính tổng, tiếp theo chọn vào Color Filter và chọn màu muốn hiển thị.
Sau khi bạn chọn màu để lọc hiển thị ra. Thì dưới ô hiển thị kết quả tính tổng sẽ tự cập nhật lại tổng những giá trị đang hiển thị.
Tính tổng theo màu với hàm VBA
Hiện nay không có công thức Excel có sẵn có thể làm được cho bạn như những gì các bạn muốn. Tuy nhiên, bạn có thể làm là sử dụng VBA rất đơn giản và tạo một hàm riêng cho bạn và sau đó bạn có thể sử dụng hàm tùy chỉnh ở đó bất cứ đâu trong trang tính hoặc trong sổ làm việc và lấy tổng các ô màu.
Các bước thực hiện như sau:
Bước 1: Tại trang tính chứa dữ liệu cần tính tổng. Bạn gõ phím tắt Alt + F11.
Bước 2: Hiện ra giao diện VBA
Tiếp theo, bạn vào thẻ Insert => chọn vào Module.
Bước 3: Tại module mới tạo, bạn gõ đoạn code như sau:
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 Run trên thành công cụ hoặc nhấn F5 để chạy chương trình nhé!
Bước 5: Bạn sẽ thoát ra ngoài file Excel, bạn chọn 2 ô trống bất kì: 1 ô hiển thị màu cam, 1 ô hiển thị màu xanh.
Bước 6: Tại ô màu xanh hoặc 1 ô bất kì bạn muốn tính tổng những giá trị trong ô màu xanh, bạn gõ công thức sau: =SumByColor(C3:C17,F4)
Trong đó:
- Hàm SumByColor: là hàm mình đã tạo trong giao diện VBA.
- C3:C17: Cột dữ liệu muốn tính tổng theo màu.
- F4: Ô chứa màu xanh muốn tính tổng.
Tương tự để tính tổng các giá trị theo vùng màu cam, bạn gõ công thức sau: =SumByColor(C3:C17,F5)
Trong đó:
- Hàm SumByColor: là hàm mình đã tạo trong giao diện VBA.
- C3:C17: Cột dữ liệu muốn tính tổng theo màu.
- F5: Ô chứa màu cam muốn tính tổng.
Tính tổng theo màu bằng cách sử dụng Get.CELL + Mẹo Named Range
Trước khi để tính tổng từng màu các giá trị trong ô, thì bạn sẽ phải cho hiển thị số biểu trưng cho những màu đó bằng cách:
Đầu tiên, bạn chọn vào 1 ô trống bất kì để hiển thị giá trị số biểu trưng.
Tiếp theo, bạn vào Formulas => chọn vào Name Manager.
Hiển thị ra bảng Name Manager, bạn chọn vào New để tạo ra tên mới. Tiếp đến, hiển thị bảng New Name
Tại ô Name: bạn gõ tên bất kì
Tại ô Refers to: bạn gõ như sau: =Get.Cell(38,C3)
Giải thích: 38: 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
B2: bắt đầu tính giá trị từ vị trí này trở đi.
Tiếp theo, nhấn OK để hoàn thành.
Sau khi hoàn thành xong việc đặt tên, bạn chọn một ô ngay cạnh giá trị muốn tính tổng và gõ: =Sumvalue
Để tính các ô tiếp theo, bạn cũng đặt con chuột tại vị trí cuối ô, hiển thị dấu cộng và tích chuột trái 2 lần để hiển thị các giá trị khác.
Sau khi hiển thị, thì chúng ta biết được giá trị biểu trưng của các màu: màu xanh là số 37, màu cam là số 40.
Tiếp theo, để tính tổng các giá trị có màu xanh, bạn gõ: =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.
$C$3:$C$17: Vùng dữ liệu chứa các giá trị tính tổng.
Tương tự, để tính tổng các giá trị có màu cam, bạn gõ: =SUMIF($D$3:$D$17,40,$C$3:$C$17)
Vậy là mình đã hướng dẫn cho bạn xong mẹo tính tổng các ô dựa trên các giá trị của chúng. Hy vọng bài viết này sẽ giúp ích cho bạn. Nếu thấy hay đừng quên đánh giá bài viết ngay bên dưới nhé! Trân trọng.
