Tính tổng theo màu trong Excel
Ví dụ: Mình có bảng dữ liệu như bên dưới: Show 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 SUBTOTALThườ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. 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 VBAHiệ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:
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 đó:
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 đó:
Tính tổng theo màu bằng cách sử dụng Get.CELL + Mẹo Named RangeTrướ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ì 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 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. 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. |