Excel: 'Tóm tắt' bỏ qua # n / a
Tôi đang cố gắng tính tổng một số giá trị (cả dương và âm) trong một cột, nhưng nó sẽ không tính tổng vì có # n / a giá trị. Có cách nào để làm được việc này? Vui lòng xem ví dụ dưới đây:
Mỗi thành phố có các khu vực thấp hơn có điểm thu nhập, tội phạm và thất nghiệp (tích cực hoặc tiêu cực). Mục tiêu của tôi là có được điểm thu nhập, tội phạm và thất nghiệp trên toàn thành phố bằng cách tổng hợp điểm của các khu vực thấp hơn. Tôi đang sử dụng 'sumifs' trong G2 như trong = SUMIFS (C$2:C$10,$A$2:$A$10, $ A2) và sau đó kéo nó đến I10. Trong ví dụ đồ chơi này, chỉ có 10 hàng, nhưng dữ liệu của tôi có 1 triệu hàng, vì vậy tôi thực sự không thể thực hiện kéo. Bất kỳ đề xuất nào về điều đó cũng sẽ hữu ích!
Nhưng, vấn đề quan trọng nhất của tôi là tôi không thể sử dụng 'sumifs' do các giá trị # N / A. Tôi muốn bỏ qua chúng.
Hoặc chỉ cần thay thế tất cả các giá trị # N / A bằng 0 trong 1 triệu hàng cũng sẽ là một tùy chọn.
ps Tôi đã thực hiện một số nghiên cứu về các câu hỏi tương tự trước đây, nhưng chúng dường như sử dụng các công thức 'sumifs' khác nhau ...
Trả lời
Sử dụng SUMIFS với <0 AND> 0
Bạn có thể sử dụng cái này:
=SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,"<0")+SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,">0")
Nó tránh số 0 (không tạo ra sự khác biệt trong trường hợp này). Nó cũng tránh các ô chứa các giá trị không phải là số, chẳng hạn như NA, điều này thường ngăn việc tính tổng (tránh những ô này sẽ giải quyết được vấn đề của bạn trong trường hợp này).
Nếu tôi hiểu chính xác, bạn muốn lấy tổng của cả số dương và số âm (tức là giá trị hiện có của chúng, không phải giá trị tuyệt đối), tuy nhiên điều đó hiện đang bị ngăn chặn bằng cách sử dụng phương pháp hiện có của bạn bởi sự hiện diện của các mục NA.
Nếu điều này là không chính xác, xin vui lòng tư vấn.
SỬ DỤNG LOOKUP INTERIM, SAU ĐÓ SUMIF VỚI <0 VÀ> 0
Ngoài ra, bạn có thể chèn một cột tra cứu, cột này nối các giá trị để đánh giá và sau đó sử dụng SUMIF.
Ví dụ, cột mới J:
=$A2&C2
(lưu ý $ cho chữ A cũng như không có $ cho chữ C)
Sau đó điền J1 vào bên phải K1 và L1 Sau đó, trong M1:
=SUMIF(J$2:J$10,"<0",C$2:C$10)+SUMIF(J$2:J$10,">0",C$2:C$10)
Sau đó điền sang phải N1 và O1
Vấn đề tiềm ẩn với phương pháp này là nếu bạn điền vào hàng triệu hàng, bảng tính có thể chậm lại đáng kể.
THAY NA
Thay NA bằng 0 cũng sẽ hoạt động, nhưng bạn có thể không muốn mất sự khác biệt giữa "0" và "không được hiển thị trong dữ liệu nguồn".
Vui lòng đăng công thức hiện có của bạn cho các giá trị NA nếu bạn muốn đi theo con đường đó. Chúng có thể được làm lại thành một cái gì đó thay vì NA có ý nghĩa và không ngăn cản việc tính tổng.
Thường được ưu tiên hơn khi tìm kiếm dữ liệu để tìm ra khả năng xảy ra NA và trả về một số kết quả khác có ý nghĩa hơn (hoặc trong trường hợp này, thân thiện với tổng hơn).
Có lẽ cách dễ nhất là sử dụng các "<>#N/A"tiêu chí mà trong đó sẽ bỏ qua NA trong cột tổng. Bạn có thể thêm tiêu chí bổ sung cho các cột điều kiện khác nếu chúng cản trở việc kiểm tra điều kiện số.=SUMIFS(C$2:C$10,$A$2:$A$10,$A2, C$2:C$10, "<>#N/A")
Cảm ơn: https://www.mrexcel.com/board/threads/sumifs-while-ignoring-n-a.1042338/ cho tiền boa.