Tin Học

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

Ví dụ: Tôi có một bảng dữ liệu như sau:

hình dạng dữ liệu

Yêu cầu: Tôi sẽ phải tính tổng các giá trị ô được tô màu.

Bạn Đang Xem: Tính Tổng Các Ô Dựa Trên Màu Của Chúng Trong Excel 5/2022

Để làm như vậy, tôi sẽ hướng dẫn bạn 3 cách nhanh chóng và đơn giản:

Tính tổng theo màu với SUBTOTAL. hàm số

Thông thường để tính tổng, bạn sẽ sử dụng hàm TÓM LƯỢC tính toán. Tuy nhiên, trong trường hợp này, tôi sẽ sử dụng hàm TIỀM NĂNG tính tổng theo màu để khi bạn lọc ra điều kiện, hàm TIỀM NĂNG Sẽ cập nhật kết quả sớm nhất.

Các bước thực hiện như sau:

Bước 1: Trong ô để hiển thị kết quả tổng kết, hãy nhập 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 bạn muốn tính tổng.

hình ảnh tổng phụ

Bước 2: Chọn một ô bất kỳ trong vùng dữ liệu, chuyển đến. chuyển hướng Dữ liệu => chọn công cụ Lọc để hiển thị một bộ lọc ngay bên cạnh mỗi tiêu đề cột.

bộ lọc hiển thị

Bước 3: Bạn sẽ chọn bộ lọc ở tiêu đề cột để tính tổng, sau đó chọn Bộ lọc màu và chọn màu bạn muốn hiển thị.

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

Sau khi bạn chọn màu để lọc hiển thị. Sau đó bên dưới ô hiển thị tổng kết quả sẽ tự động cập nhật tổng các giá trị đã hiển thị.

hình ảnh tính toán tổng của các ô màu bộ lọc

Tính tổng theo màu với hàm VBA

Hiện tại, không có công thức Excel nào có thể làm cho bạn những gì bạn muốn. Tuy nhiên những gì bạn có thể làm là sử dụng VBA rất đơn giản và tạo hàm của riêng bạn và sau đó bạn có thể sử dụng hàm tùy chỉnh ở đó ở bất kỳ đâu trong trang tính hoặc trong sổ làm việc và nhận 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 thị giao diện VBA

Hiển thị giao diện VBA

Xem Thêm : Hàm Switch Trong Excel Và Những Ưu Điểm Của Nó 5/2022

Tiếp theo, bạn đi đến I. Thẻnsert => chọn Mô-đun.

mô-đun tạo hình ảnh

Bước 3: Trong mô-đun mới được tạo, hãy nhập mã sau:

Xem Thêm : Cách xác định thời gian khấu hao tài sản cố định

Hàm SumByColor (SumRange, SumColor As Range)
Dim SumColorValue dưới dạng số nguyên
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Đặt rCell = SumRange
Đối với mỗi rCell trong SumRange
Nếu rCell.Interior.ColorIndex = SumColorValue Thì
TotalSum = TotalSum + rCell.value
EndIf
RCell tiếp theo
SumByColor = TotalSum
Kết thúc chức năng

Bước 4: Nhấp chuột Chạy trên thanh công cụ hoặc nhấn F5 để chạy chương trình.

chương trình chạy hình ảnh

Bước 5: Bạn sẽ thoát khỏ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 lam.

công cụ chọn màu cho mỗi hộp

Bước 6: Trong ô màu xanh lam hoặc bất kỳ ô nào khác mà bạn muốn tính tổng các giá trị trong ô màu xanh lam, hãy nhập công thức sau: = SumByColor (C3: C17, F4)

Trong đó:

tổng màu xanh lam

Tương tự để tính tổng các giá trị theo vùng màu cam, bạn nhập công thức sau: = SumByColor (C3: C17, F5)

Trong đó:

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

Tính tổng theo màu sử dụng Get.CELL + Mẹo phạm vi được đặt tên

Trước khi tính tổng từng màu các giá trị trong ô, bạn sẽ phải hiển thị số ký hiệu cho các màu đó bằng cách:

Đầu tiên, bạn chọn một ô trống bất kỳ để hiển thị giá trị số logo.

Tiếp theo, bạn nhập Công thức => chọn Người quản lý tên.

hình ảnh vào công cụ quản lý tên

Hiển thị ra bàn Người quản lý tênbạn chọn Mới để tạo một tên mới. Tiếp theo, hiển thị bảng Tên mới

Tại Tên: bạn gõ tên bất kỳ
Tại Đề cập đến: 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.

giá trị loại hình ảnh trong tên mới

Tiếp theo, nhấn ĐƯỢC RỒI hoàn thành.

Sau khi hoàn thành việc đặt tên, hãy chọn một ô bên cạnh giá trị bạn muốn tính tổng và nhập: = Tổng giá trị

tổng bằng giá trị

Để tính các ô tiếp theo, bạn cũng đặt chuột vào cuối ô, hiển thị dấu cộng và nhấn đúp chuột trái để hiển thị các giá trị khác.

Sau khi hiển thị, chúng ta biết giá trị tượng trưng của các màu: xanh lam là số 37, cam là số 40.

Tiếp theo, để tính tổng các giá trị bằng màu xanh lục, hãy nhập: = 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ố thể hiện màu.

$ C $ 3: $ C $ 17: Vùng dữ liệu chứa các giá trị tổng.

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

Tương tự, để tính tổng các giá trị bằng màu cam, bạn nhập: = SUMIF ($ D $ 3: $ D $ 17,40, $ C $ 3: $ C $ 17)

sumif cam sumifVậy là mình đã hướng dẫn xong thủ thuật tính tổng các ô dựa trên 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.

Nguồn: https://lienket365.com
Danh mục: Tin Học

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai.

Back to top button