Top 10 # Lỗi Value Trong Excel Hàm Sumif Xem Nhiều Nhất, Mới Nhất 6/2023 # Top Trend

Chủ đề này liệt kê các nguyên nhân phổ biến hơn của lỗi #VALUE! trong các hàm SUMIF và SUMIFS và cách giải quyết các lỗi.

Sự cố: Công thức tham chiếu đến các ô trong sổ làm việc đã đóng

Hàm SUMIF/SUMIFS tham chiếu đến một ô hoặc phạm vi trong một sổ làm việc đóng sẽ dẫn đến #VALUE! .

Lưu ý: Đây là một vấn đề đã biết với một vài các hàm Excel khác chẳng hạn như COUNTBLANK COUNTIF, COUNTIFS, để đặt tên cho một vài. Hãy xem SUMIF, COUNTIF và trả về các hàm COUNTBLANK “#VALUE!” Lỗi Bài viết để biết thêm thông tin.

Giải pháp: Mở sổ làm việc được biểu thị trong công thức, rồi nhấn F9 để làm mới công thức.

Bạn cũng có thể tránh vấn đề này bằng cách dùng hàm SUM và IF cùng nhau trong một công thức mảng. Hãy xem các hàm SUMIF, COUNTIF và COUNTBLANK trả về #VALUE! lỗi Bài viết để biết thêm thông tin.

Sự cố: Chuỗi tiêu chí nhiều hơn 255 ký tự

Hàm SUMIF/SUMIFS trả về kết quả không đúng khi bạn cố gắng so khớp các chuỗi dài hơn 255 ký tự.

Giải pháp: Rút ngắn chuỗi nếu có thể. Nếu bạn không thể rút ngắn chuỗi, hãy sử dụng hàm CONCATENATE hoặc toán tử Dấu và (&) để ngắt giá trị thành nhiều chuỗi. Ví dụ:

=SUMIF(B2:B12;”chuỗi dài”&”một chuỗi dài khác”)

Sự cố: Trong hàm SUMIFS, tham đối dải_ô_tiêu_chí không nhất quán với tham đối dải_ô_tổng.

Các tham đối dải ô phải luôn đồng nhất trong hàm SUMIFS. Điều đó có nghĩa là tham đối dải_ô_tiêu_chí và dải_ô_tổng sẽ tham chiếu đến cùng một số hàng và số cột.

Trong ví dụ sau đây, công thức được phép để trả về tổng doanh số bán hàng ngày của quả táo trong Bellevue. Đối số sum_range (C2:C10) Tuy nhiên không tương ứng với cùng số hàng và cột trong các đối số criteria_range (A2:A12 & B2:B12) . Bằng cách dùng cú pháp = SUMIFS ( C2:C10, A2:A12, A14, B2:B12, B14) sẽ dẫn đến #VALUE! .

Giải pháp: Tiếp tục với ví dụ này, thay đổi dải_ô_tổng thành C2:C12 và thử lại công thức.

Lưu ý: Hàm SUMIF có thể sử dụng các dải ô có kích cỡ khác nhau.

Bạn có câu hỏi về một hàm cụ thể?

Đăng câu hỏi vào diễn đàn cộng đồng Excel

Giúp chúng tôi cải thiện Excel

Sửa lỗi #VALUE! .

Hàm SUMIFS

Nâng cao nếu hàm video

Tổng quan về các công thức trong Excel

Làm thế nào để tránh công thức bị lỗi

Phát hiện lỗi trong các công thức

Tất cả các hàm Excel (bảng chữ cái)

Lỗi #VALUE trong excel là gì? Trường sẽ hướng dẫn các bạn cách sửa lỗi value trong Hàm IF, Sumif, Sumifs Vlookup, Average, …

Rất mong rằng sẽ giúp ích được cho bạn làm báo cáo chuyên nghiệp hơn.

1. Lỗi VALUE trong excel là gì?

Phần thông báo của excel cho ta biết được rằng công thức bạn vừa nhập đã bị lỗi ở đâu đó: Sai cú pháp, tham chiếu có vấn đề,…

Và #Value là một lỗi rất chung chung do vậy khó biết được ngay lập tức lỗi là do đâu.

Để tìm ra lỗi thì ta phải có những thủ thuật, biện pháp test (phép thử).

2. Cách sửa lỗi #value trong Hàm IF, Sumif, Sumifs Vlookup, Average, chứa khoảng trắng …

2.1. Lỗi value trong hàm Sum, Average kết hợp hàm Iseror + IF hoặc Iferror

Nguyên nhân:

Trong vùng tính tổng, tính trung bình có chứa lỗi #Value. Tham khảo hình sau:

Ví dụ, ta cần tính tổng số lượng bán được của tất cả các mặt hàng, cách làm như sau:

+ Sau khi nhập công thức sum các bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter. Vì đây là công thức mảng bạn ạ.

Logic tính toán của công thức:

Hàm if và Iserror sẽ xác định xem giá trị nào trong vùng B2:B5 là giá trị lỗi thì excel sẽ trả về kết quả là trống. Như vậy các giá trị để tính tổng sau cùng của hàm sum sẽ loại bỏ được toàn bộ giá trị lỗi.

Tương tự như vậy, ta có thể thay thế cú pháp hàm Sum bằng hàm Average như sau:

={AVERAGE(IFERROR(B2:B5,””))} = 227

={AVERAGE(IF(ISERROR(B2:B5),””,B2:B5))} = 227

Các bạn có thể download file excel mẫu ở phía cuối của bài viết này.

2.2. Gặp lỗi #VALUE khi dùng hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS sang một file khác bị đóng

Đây là trường hợp phổ biến khi ta dùng hàm tính tổng, hàm đếm có điều kiện trên một file khác.

Tại thời điểm ta lập các công thức này thì cả file tính toán và file chứa dữ liệu tính toán đều được mở nên không thấy lỗi gì xuất hiện.

Giải pháp duy nhất đến hiện tại là:

Mở cùng một lúc cả 2 file rồi nhấn lại F9 (nếu cần) để excel cập nhật lại công thức.

2.3. Loại bỏ lỗi #VALUE khi tính toán, dùng hàm IF, ISERROR hoặc Iferror hoặc định dạng có điều kiện.

Trong ví dụ trên, các mặt hàng đều có giá giống nhau là 200 và dựa trên số lượng ta sẽ tính được doanh thu một cách đơn giản như sau:

Khi đó, bạn hãy nghĩ tới 1 trong 02 phương án sau:

a/ Bẫy lỗi để cứ mặt hàng nào bị lỗi số lượng thì trả về kết quả là 0.

Như vậy báo sẽ nhìn ổn hơn rất nhiều. Công thức tính doanh thu lúc đó như sau:

C2= IF(ISERROR(B2),0,B2*200)

Hoặc

C2= IFERROR(B2*100,0)

Kết quả ta thu được như sau:

b/ Sử dụng conditional formatting để ẩn mọi lỗi trong excel

Thay vì phải dùng công thức ở cột doanh thu, ở cách này ta chỉ cần nhập công thức doanh thu = số lượng * đơn giá.

Trong bảng thiết lập thông số cho việc định dạng có điều kiện bạn nhập trong phần Use a formula to determines which cells to format như hình sau:

Công thức để xác định ô được định dạng: Iserror(B2)

Formating: Bạn chọn màu chữ là Trắng

Và đây là kết quả ta đạt được: Mọi ô có kết quả là #Value sẽ không hiện lên báo cáo vì màu chữ là màu trắng bạn ạ.

Các thủ thuật này bạn sẽ không học được ở các trường lớp chính quy, nhưng nó lại rất có ích trong việc thực tế của bạn.

Trường hi vọng rằng các bạn sẽ thấy thích thú.

Nếu bạn làm việc với hàm VLOOKUP, có khả năng cao là bạn có thể đã gặp lỗi #VALUE! vài lần. Chủ đề này liệt kê các sự cố phổ biến nhất có thể xảy ra với hàm VLOOKUP và các giải pháp khả thi.

Sự cố: Tham đối giá_trị_tra_cứu có nhiều hơn 255 ký tự.

Giải pháp: Rút ngắn giá trị hoặc sử dụng kết hợp hàm INDEX và MATCH như một giải pháp thay thế.

Đây là công thức mảng. Vì vậy, một trong hai nhấn Enter (chỉ khi bạn có Microsoft 365 ) hoặc Ctrl + Shift + Enter.

Lưu ý: Nếu bạn có phiên bản hiện tại của Microsoft 365, thì bạn chỉ cần nhập công thức trong ô đầu ra, rồi nhấn Enter để xác nhận công thức dưới dạng công thức mảng động. Nếu không, công thức phải được nhập dưới dạng công thức mảng kế thừa bằng cách chọn ô đầu ra, hãy nhập công thức vào ô đầu ra, rồi nhấn Ctrl + Shift + Enter để xác nhận. Excel chèn dấu ngoặc nhọn ở đầu và cuối công thức cho bạn. Để biết thêm thông tin về công thức mảng, hãy xem mục Hướng dẫn và ví dụ về công thức mảng.

Sự cố: Tham đối số_chỉ_mục_cột chứa văn bản hoặc nhỏ hơn 0.

Chỉ mục Cột là số của cột ở bên phải cột tìm kiếm mà bạn muốn trả về nếu tìm thấy kết quả khớp.

Sự cố này có thể xảy ra do lỗi đánh máy trong tham đối số_chỉ_mục_cột hoặc vô tình chỉ định một số nhỏ hơn 1 làm giá trị chỉ mục (hiện tượng phổ biến nếu một hàm Excel khác được lồng trong hàm VLOOKUP trả về một số, chẳng hạn như “0”, làm tham đối số_chỉ_mục_cột).

Giá trị tối thiểu cho tham đối số_chỉ_mục_cột là 1, trong đó 1 là cột tìm kiếm, 2 là cột đầu tiên ở bên phải của cột tìm kiếm, v.v.. Vì vậy, nếu bạn đang tìm kiếm trong cột A thì số 1 sẽ tham chiếu cột đó, 2 là cột B, 3 là cột C, v.v..

Bạn có câu hỏi về một hàm cụ thể?

Đăng câu hỏi vào diễn đàn cộng đồng Excel

Giúp chúng tôi cải thiện Excel

Sửa lỗi #VALUE! .

Mọi thứ bạn cần biết về VLOOKUP

Tổng quan về các công thức trong Excel

Làm thế nào để tránh công thức bị lỗi

Phát hiện lỗi trong các công thức

Tất cả các hàm Excel (bảng chữ cái)

Hàm SUMIF là gì? Sự khác nhau giữa hàm SUMIFS trong Excel với hàm SUMIF là như thế nào? Bài viết này sẽ hướng dẫn cho các bạn cú pháp, ý nghĩa, cách sử dụng và một số cách kết hợp giữa hàm SUMIF, SUMIFS với một số hàm thông dụng khác.

#1. Tổng quan về hàm SUMIFS

Trong công việc hàng ngày có sử dụng đến phần mềm Excel, để cộng các giá trị cùng một điều kiện cho trước với nhau thì chúng ta thường nghĩ ngay tới hàm SUMIF. Vậy Hàm SUMIF là gì?

Hàm SUMIF là gì?

Hàm SUMIFS trong Excel là một trong những hàm Excel cơ bản, hàm tính toán thường được dùng trong Excel. Để tính tổng trong Excel chúng ta sẽ dùng đến hàm SUM, nếu muốn thêm 1 điều kiện nhất định cho hàm tính tổng đó sẽ dùng hàm SUMIF.

#2. Cách sử dụng hàm SUMIFS

#2.1. Cú pháp và cách sử dụng hàm SUMIF

Cú pháp hàm này như sau:

=SUMIF(range,criteria,[sum_range])

Cú pháp hàm SUMIF có các đối số sau đây:

– Range (Bắt buộc): Phạm vi ô bạn muốn được đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn (ví dụ bên dưới).

– criteria (Bắt buộc): Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Ký tự đại diện có thể được bao gồm-dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào, dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.

– sum_range Tùy chọn. Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).

Sum_range phải có cùng kích cỡ và hình dạng theo phạm vi. Nếu không, hiệu suất có thể bị, và công thức sẽ tính tổng phạm vi ô bắt đầu với ô đầu tiên trong sum_range nhưng có cùng kích thước như phạm vi.

Bước 1: Tại ô G8, chúng ta viết công thức sau: =SUMIF(E8:E22;”nam”;F8:F22).

Trong công thức này, vùng phạm vi đánh giá tiêu chí là từ ô E8 đến ô E22, tiêu chí là giới tính nam, vùng tính tổng là từ ô F8 đến F22.

Bước 2: Bấm phím Enter, kết quả sẽ là 87.860.000 đồng. Tức tổng số tiền lương của các nhân viên nam là 87,86 triệu đồng.

#2.2. Cú pháp và cách sử dụng hàm SUMIFS

Hàm này có cú pháp tổng quát như sau:

=SUMIFS(sum_range,criteria_range1,criteria1,[criter_range2, criteria2], …)

Bước 1: Tại ô G7, chúng ta viết công thức sau:

=SUMIFS(F7:F21;E7:E21;”Nam”;D7:D21;”Văn phòng”)

Trong công thức này, vùng tính tổng là từ ô F7 đến ô F21, vùng tiêu chí đánh giá thứ nhất là từ ô E7 đến ô F21, tiêu chí thứ nhất là giới tính nam, cùng tiêu chí đánh giá thứ 2 là từ ô D7 đến ô D21, tiêu chí thứ 2 là văn phòng.

Bước 2: Bấm phím Enter, kết quả của phép tính sẽ là: 15.910.000.

Vậy, tổng tiền lương của các nhân viên nam ở bộ phận văn phòng là 15.910.000 đồng.

#2.3. Hàm SUMIFS nhiều điều kiện

Ví dụ #3, Tính tổng tiền lương của nhân viên thỏa mãn các điều kiện sau:

– Giới tính nam;

– Thuộc bộ phận văn phòng;

– Sinh năm 1992

Để giải quyết bài toàn này, chúng ta thực hiện như sau:

Bước 1: Tại ô H7, chúng ta viết công thức:

=SUMIFS(G7:G21;F7:F21;”nam”;E7:E21;”văn phòng”;D7:D21;1992)

Bước 2: Bấm phím Enter, chúng ta sẽ ra kết quả là: 8.110.000 đồng.

Hỏi: Sự khác nhau giữa hàm SUMIF và SUMIFS như thế nào?

Trả lời: Hàm SUMIF dùng để tính tổng các phần tử theo 1 điều kiện cho trước, còn SUMIFS dùng để tính tổng các phần tử thỏa mãn từ hai điều kiện trở lên.

Hỏi: Tối đa có thể dùng bao nhiêu điều kiện trong hàm SUMIFS?

Hỏi: Cách sử dụng các phím tắt trong Excel?

Hỏi: Tải file hướng dẫn hàm SUMIFS tại đâu?