Tính tổng theo màu ô trong excel

hàm số. Hướng dẫn từng bước này sẽ hỗ trợ người dùng Excel ở mọi cấp độ trong việc tính tổng các giá trị trong các ô dựa trên màu nền.  

Hình 1. Kết quả cuối cùng. Sử dụng SUMIF để tính tổng các ô dựa trên màu nền

Công thức cuối cùng. ______0

Cú pháp của Hàm SUMIF

SUMIF tính tổng các giá trị trong một phạm vi được chỉ định, dựa trên một tiêu chí nhất định

=SUMIF(range,criteria, [sum_range])

Các tham số là

• Phạm vi . phạm vi dữ liệu mà chúng tôi sẽ đánh giá bằng tiêu chí
• Tiêu chí . tiêu chí hoặc điều kiện xác định ô nào sẽ được thêm vào
• Sum_range . các ô sẽ được thêm vào;

Thiết lập dữ liệu

Bảng của chúng tôi có ba cột. ID sản phẩm (cột B), Đơn đặt hàng (cột C) và Màu nền của cột trợ giúp (cột D). Lưu ý rằng ID sản phẩm và Đơn đặt hàng có màu nền đặt sẵn là vàng và xanh lục

Các ô F3 và F4 chứa hai màu nền vàng và xanh lục, sẽ dùng làm tiêu chí của chúng tôi. Chúng tôi muốn xác định tổng đơn đặt hàng dựa trên màu nền. Ta nhập kết quả vào ô G3 và G4.  

Hình 2. Dữ liệu mẫu để tính tổng các ô dựa trên màu nền

Không có cách đơn giản nào để tính tổng các ô dựa trên màu nền trong Excel. Đối với ví dụ này, điều quan trọng là gán một giá trị cho mỗi màu nền và sử dụng giá trị đó làm tiêu chí cho hàm SUMIF của chúng ta.  

Chỉ định một số cho mỗi màu nền

Có một chức năng tích hợp sẵn trong Excel, đó là GET. Hàm CELL, trả về một số duy nhất cho mỗi màu nền trong một ô. Tuy nhiên, nó không thể được nhập trực tiếp dưới dạng hàm trang tính. Thay vào đó, nó được sử dụng trong một phạm vi được đặt tên. Hãy để chúng tôi làm theo các bước sau

Bước 1 . Chọn ô D3. D8

Bước 2 . Nhấp vào tab Công thức , sau đó chọn Trình quản lý tên . Thao tác này sẽ khởi chạy hộp thoại Trình quản lý tên.

Hình 3. Tạo một phạm vi được đặt tên mới

Bước 3 . Nhấp vào Mới

Bước 4 . Trong hộp thoại Tên mới, hãy nhập “ Màu ” cho Tên và

=GET.CELL(38,Sheet1!C3) trong Đề cập đến thanh.

hinh 4. Bước vào NHẬN. Chức năng CELL cho một phạm vi được đặt tên mới “Màu”

Công thức này trả về số màu duy nhất cho màu nền trong ô liền kề C3.  

Bước 5 . Nhấp vào OK, sau đó ĐÓNG.

Hình 5. Phạm vi được đặt tên “Màu” được tạo trong Trình quản lý tên

Bước 6 . Chọn ô D3 và nhập công thức. =Màu sắc

Kết quả là giá trị “6” được trả về trong ô D3, là số màu cho màu nền màu vàng được sử dụng trong ô C3.  

Hình 6. Gán một số cho màu nền của C3 bằng cách sử dụng phạm vi được đặt tên là “Màu”

Bước 7 . Sao chép công thức trong ô D3 sang ô D4. D8. Hình dưới đây cho thấy cột D được tô bằng số màu tương ứng cho màu nền trong cột C.

Hình 7. Chỉ định một số cho mỗi màu nền bằng cách sử dụng phạm vi được đặt tên là “Màu”

Bước 8 . Nhập số màu cho từng màu nền vào cột F. Trong ô F3, nhập giá trị “ 6 ” cho màu vàng, trong khi ở ô F4, nhập giá trị “ 10 . ” for green.

Hình 8. Chỉ định một số cho mỗi màu nền làm tiêu chí

Tổng đơn đặt hàng dựa trên màu nền

Giờ đây, mỗi màu nền có một số màu tương ứng,  chúng ta có thể dễ dàng tổng hợp các đơn đặt hàng dựa trên màu nền bằng cách sử dụng hàm SUMIF. Hãy để chúng tôi làm theo các bước sau

Bước 1 . Chọn ô G3

Bước 2 . Nhập công thức. = ______4

Bước 3 . Nhấn Enter

Bước 4 . Sao chép công thức trong ô G3 sang ô G4.

Phạm vi chúng tôi muốn đánh giá là D3. D8 , chứa số cho màu nền trong cột C . Tiêu chí của chúng tôi trong ô F3 là “6”, hoặc màu vàng. Chúng tôi muốn tính tổng các đơn đặt hàng trong C3. C8 thỏa mãn tiêu chí đã cho.

Các sản phẩm thỏa mãn tiêu chí của chúng tôi và có màu nền vàng là các ô C3, C5 và C6. Kết quả là tổng đơn hàng ở ô G3 là $3,700

Hình 9. Nhập công thức bằng SUMIF để tính tổng các ô dựa trên màu nền

Sao chép công thức trong ô G3 vào ô G4 trả về giá trị $7,500, là tổng của các đơn đặt hàng có màu nền xanh lục.  

Hình 10. đầu ra. Tính tổng các ô bằng SUMIF dựa trên màu nền

Hầu hết thời gian, vấn đề bạn cần giải sẽ phức tạp hơn một ứng dụng đơn giản của công thức hoặc hàm. Nếu bạn muốn tiết kiệm thời gian nghiên cứu và giảm bớt sự thất vọng, hãy dùng thử dịch vụ Excelchat trực tiếp của chúng tôi. Các chuyên gia Excel của chúng tôi luôn sẵn sàng 24/7 để trả lời bất kỳ câu hỏi nào về Excel mà bạn có thể có. Chúng tôi đảm bảo kết nối trong vòng 30 giây và giải pháp tùy chỉnh trong vòng 20 phút.