seychellesartprojects.org

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:

1. Cách sử dụng công thức SUBTOTAL trong excel và chức năng lọc theo màu.
2. Á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ố họ -

Bạn có thể tải về Mẫu Excel tính tổng theo màu này tại đây - Mẫu Excel tính tổng theo màu

# 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 cung cấp thông tin chi tiết về số tiền tính theo đô 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 cho cùng mộ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 toán 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, sau đó 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 dưới đây.

Như được hiển thị trong ảnh chụp màn hình ở trên, việc tính toán ô màu đạt được 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 đượ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 mà chúng ta sẵn sàng để đạt được 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 tôi 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 cho GET.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 chức năng Tên 'CellColor' vào ô bên cạnh ô mã màu đã được xác định trong hộp thoại như được 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 vào để 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
• Các tiêu chí được nhập dưới dạng G2 cần truy xuất các giá trị tóm tắt của ai.
• 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ột khác nhau, 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ể mà thôi.
• 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 ô.