Tính Tổng Các Ô Dựa Trên Màu Của Chúng Trong Excel

Khi sếp bạn kêu phải lọc ra các giá trị trong vùng dữ liệu chứa màu để tính tổng mà bạn không biết phải làm sao. Đừng lo lắng, trong bài viết này sẽ hướng dẫn cho bạn mẹo tính tổng các ô dựa trên màu của chúng trong Excel. Hãy cùng mình tìm hiểu hết bài viết này nha.

Ví dụ: Mình có bảng dữ liệu như bên dưới:

hình dữ liệu

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.

hình ảnh tính tổng subtotal

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.

hình hiển thị bộ lọc

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ị.

hình ảnh chọn màu lọc

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ị.

hình ảnh tính tổng ô màu lọc

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

hình hiển thị giao diện VBA

Tiếp theo, bạn vào thẻ Insert => chọn vào Module.

hình ảnh tạ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é!

hình chạy chương trì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.

hình chọn màu mỗi ô

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.    

hình tính tổng theo màu xanh

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.    

hình ảnh tính tổng theo màu cam

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.

hình vào công cụ 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.

hình ảnh gõ giá trị trong new name

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

hình tính tổng theo giá trị

Để 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.

hình ảnh tổng màu xanh sumif

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)        

hình tính tổng màu cam sumifVậ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.

CHO ĐIỂM BÀI VIẾT NÀY

vote data

HÌNH ẢNH HỌC VIÊN

học vi tính văn phòng trung tâm tin học Sao Việt học máy vi tính văn phòng trung tâm dạy tin học văn phòng lớp học máy tính văn phòng khóa học tin học Excel khóa học tin học văn phòng khóa học sử dụng máy vi tính học vi tính cho người đi làm khóa học tin học văn phòng khóa học sử dụng máy tính khóa học vẽ Autocad 2D - 3D
TOP