Làm thế nào để tính tổng theo màu trong Excel? (2 phương pháp hữu ích)
2 phương pháp hàng đầu để tính tổng theo màu trong Excel như sau:
- Cách sử dụng công thức SUBTOTAL trong excel và chức năng lọc theo màu.
- Áp dụng công thức GET.CELL bằng cách xác định tên trong tab công thức và áp dụng công thức SUMIF trong excel để tổng hợp các giá trị theo mã màu.
Hãy để chúng tôi thảo luận chi tiết về từng người trong số chúng -
# 1 - Tính tổng theo màu sử dụng Hàm tổng phụ
Để hiểu phương pháp tính tổng các giá trị theo màu nền, chúng ta hãy xem xét bảng dữ liệu dưới đây, bảng này cung cấp chi tiết về số tiền tính bằng đô la Mỹ theo khu vực và tháng.
- Bước 1: Giả sử chúng ta muốn đánh dấu những ô có giá trị âm cho mục đích chỉ báo, điều này có thể đạt được bằng cách áp dụng định dạng có điều kiện hoặc bằng cách đánh dấu các ô theo cách thủ công, như được hiển thị bên dưới.
- Bước 2: Bây giờ, để đạt được tổng các ô được tô màu trong excel, hãy nhập công thức cho SUBTOTAL bên dưới bảng dữ liệu. Cú pháp của công thức SUBTOTAL được hiển thị bên dưới.
Công thức được nhập để tính tổng là
= SUBTOTAL (9, D2: D13)
Ở đây số '9' trong đối số function_num đề cập đến chức năng tổng và đối số tham chiếu được đưa ra dưới dạng phạm vi ô được tính toán. Dưới đây là ảnh chụp màn hình tương tự.
- Bước 3: Như đã thấy trong ảnh chụp màn hình ở trên, tổng số tiền USD đã được tính toán để tính số tiền được đánh dấu bằng màu nền đỏ nhạt. Áp dụng bộ lọc cho bảng dữ liệu bằng cách đi tới Dữ liệu rồi chọn một bộ lọc.
- Bước 4: Sau đó chọn bộ lọc theo màu và chọn màu ô đỏ nhạt trong 'Lọc theo màu ô.' Dưới đây là ảnh chụp màn hình để mô tả bộ lọc tốt hơn.
- Bước 5: Khi bộ lọc excel đã được áp dụng, bảng dữ liệu sẽ chỉ được lọc cho các ô có nền màu đỏ nhạt và công thức tổng phụ được áp dụng ở cuối bảng dữ liệu sẽ hiển thị tổng các ô màu, được lọc như hình minh họa phía dưới.
Như thể hiện trong ảnh chụp màn hình ở trên, tính toán ô màu được thực hiện trong ô E17, công thức tổng phụ.
# 2 - Tính tổng theo màu sử dụng Hàm Get.Cell
Cách tiếp cận thứ hai được giải thích để đi đến tổng các ô màu trong excel, như được thảo luận trong ví dụ dưới đây. Xem xét bảng dữ liệu để hiểu rõ hơn về phương pháp.
- Bước 1: Bây giờ, chúng ta hãy đánh dấu danh sách các ô trong cột Số tiền USD, chúng ta sẵn sàng đi đến tổng các ô màu mong muốn, như được hiển thị bên dưới.
- Bước 2: Như chúng ta có thể thấy trong ảnh chụp màn hình ở trên, không giống như trong ví dụ đầu tiên ở đây, chúng ta có nhiều màu. Do đó, chúng ta sẽ sử dụng công thức = GET.CELL bằng cách xác định nó trong hộp tên và không sử dụng trực tiếp nó trong excel.
- Bước 3: Bây giờ, khi hộp thoại 'Xác định Tên' bật lên, hãy nhập Tên và công thức quên .CELL trong 'Tham khảo' như thể hiện trong ảnh chụp màn hình bên dưới.
Như đã thấy trong ảnh chụp màn hình ở trên, Tên được nhập cho hàm là 'CellColor' và công thức = GET.CELL (38, 'Ví dụ 2! $ D2) sẽ được nhập vào' Tham chiếu đến '. Trong công thức, số 38 đề cập đến thông tin mã ô và đối số thứ hai là số ô D2 đề cập đến ô tham chiếu. Bây giờ hãy nhấp vào, OK.
- Bước 4: Bây giờ nhập hàm Tên 'CellColor' vào ô bên cạnh ô được mã hóa màu, được xác định trong hộp thoại, như đã giải thích ở bước 3.
Như có thể thấy trong ảnh chụp màn hình ở trên, hàm 'CellColor' được nhập, trả về mã màu cho màu ô nền.
Tương tự, công thức được kéo cho toàn bộ cột.
- Bước 5: Bây giờ, để đến tổng các giá trị theo màu sắc trong excel, chúng ta sẽ nhập công thức SUMIF. Cú pháp cho công thức SUMIF như sau: -
Như có thể thấy từ ảnh chụp màn hình ở trên, các đối số sau được nhập vào công thức SUMIF: -
- Đối số phạm vi được nhập cho phạm vi ô E2: E13.
- Tiêu chí được nhập dưới dạng G2, có giá trị tóm tắt cần được truy xuất.
- Phạm vi ô được nhập là D2: D13.
Công thức SUMIF được kéo xuống cho tất cả các số mã màu mà các giá trị sẽ được cộng lại với nhau.
Những điều cần ghi nhớ
- Trong khi sử dụng phương pháp tiếp cận theo công thức SUBTOTAL, chức năng này cho phép người dùng chỉ lọc một màu bộ lọc tại một thời điểm. Hơn nữa, chức năng này có thể được sử dụng để chỉ thêm một cột giá trị theo màu bộ lọc. Nếu có nhiều hơn một cột với các màu khác nhau theo các hàng trong các cột khác nhau, thì SUBTOTAL chỉ có thể hiển thị kết quả chính xác cho một bộ lọc theo màu cho một cột cụ thể.
- Công thức GET.CELL, kết hợp với phương pháp SUMIF, loại bỏ hạn chế này, vì chức năng này có thể được sử dụng để tóm tắt theo màu cho nhiều mã màu trong nền ô.
