Top 7 # Xem Nhiều Nhất Lỗi Value Trong Excel Hàm Sumif Mới Nhất 3/2023 # Top Like

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)

Đăng ký ngay khoá học Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ

Nguyên nhân 1: Giá trị tra cứu vượt quá 255 ký tự

Có thể bạn chưa biết, hàm VLOOKUP không thể tìm kiếm các giá trị có từ 256 ký tự trở lên. Do đó, nếu giá trị tra cứu của bạn vượt quá giới hạn này, kết quả trả về sẽ hiện lỗi #VALUE.

Như trong ví dụ sau:

Cách khắc phục: Hàm INDEX kết hợp MATCH là sự lựa chọn thay thế phù hợp trong trường hợp này. Công thức hàm bạn có thể gán là:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2: B7=F$2,0),0))

Lúc này, bạn sẽ nhận được kết quả chính xác.

Nguyên nhân 2: Không có đường dẫn đầy đủ tới bảng tính tra cứu

Trong công thức hàm VLOOKUP, khi lấy dữ liệu từ một bảng tính khác, bạn phải bao gồm đường dẫn đầy đủ đến tệp đó. Nói một cách cụ thể hơn, bạn phải kèm theo tên của tệp (bao gồm cả phần mở rộng) trong dấu ngoặc vuông [], và sau đó chỉ định tên của trang tính (sheet) và dấu chấm than (!). Hơn thế nữa, bạn nên đặt chúng trong dấu nháy (”) trong trường hợp tệp (file) hoặc tên trang tính (sheet) có chứa khoảng trống. Như vậy thì công thức VLOOKUP mới có thể chạy hoàn hảo.

Công thức hàm VLOOKUP khi tra cứu dữ liệu từ bảng tính khác như sau:

=VLOOKUP(lookup_value, ‘[tên tệp] tên trang tính’! Table_array, col_index_num, FALSE)

Ví dụ: Trong trường hợp thực tế, công thức có thể hiển thị như sau:

=VLOOKUP($A$2,'[New chúng tôi Sheet1′! $B:$D, 3, FALSE)

giá trị ô Công thức này có nghĩa là: Tra cứu A2 trong cột B của Sheet1 trong tệp ” New Prices” và trả về một giá trị phù hợp tại cột D.

Nếu bất kỳ phần tử nào của đường dẫn bị thiếu, công thức hàm VLOOKUP của bạn sẽ không hoạt động và kết quả trả về lỗi #VALUE (trừ khi bảng tính tra cứu hiện đang mở).

Nguyên nhân 3: Đối số col_index_num nhỏ hơn 1

Rất khó để xảy ra tình huống nhập một số nhỏ hơn ” 1” để xác định cột giá trị trả về. Tuy nhiên, điều này có thể xảy ra nếu đối số này là kết quả của một hàm Excel khác được lồng vào công thức hàm VLOOKUP của bạn.

Do đó,

Kết luận

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ

Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Gitiho tự hào khi được đồng hành cùng:

50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

Lỗi #VALUE với khoảng trắng

Để khắc phục sự cố này, các bạn thực hiện như sau:

Bước 1: Chọn ô được tham chiếu, sau đó chọn Ctrl + H để mở một hộp thoại mới để thay thế

Bước 2: Xuất hiện hộp thoại Find and Replace. Khi đó, trong ô Find What các bạn nhập dấu cách. Và trong mục Replace With để nguyên và không nhập gì. Cuối cùng chọn Replace All để loại bỏ tất cả các khoảng trắng.

Trường hợp có các ký tự ẩn khác ngoài các nhưng thực sự các ô đó không trống, khi đó ta thực hiện như sau:

Bước 3: Khi đó chọn Data → Filter

Bước 4: Kích chuột vào biểu tượng lọc trong cột và chọn trong ô Select All. Tiếp tục tích chuột vào ô Banks.

Bước 5: Chọn các ô trống và nhấn Delete.

Lỗi #value với khoảng trắng. Hình 5

Bước 6: Chọn Clear Filter From để xóa bộ lọc

Lỗi #value với khoảng trắng. Hình 6

Sửa lỗi #VALUE khi xuất hiện các dấu (+,*,..)

Và để thay thế công thức trên, ta sẽ sử dụng công thức với hàm SUM để trả về kết quả:=SUM(C4:C6)

Cùng với đó, bạn hoàn toàn có thể nâng cao kiến thức tin học văn phòng cũng như nắm trọn hệ thống hàm Excel để nâng cao hiệu suất công việc và lưu trữ, xử lý giấy tờ một cách nhanh chóng, chính xác, chuyên nghiệp nhất với khóa học Trở thành cao thủ hàm Excel trong 10 giờ của giảng viên Nguyễn Thành Phương trên UNICA.

Chi tiết khóa học “Trở thành cao thủ hàm Excel trong 10 giờ”

Khóa học này được giảng dạy theo hình thức trực tuyến, phù hợp với người bận rộn không có thời gian học.

Khóa học “Trở thành cao thủ hàm Excel trong 10 giờ”

Khi kết thúc khóa học, bạn sẽ nắm được chi tiết cách sử dụng bài bản, đầy đủ những công cụ trong Excel. Biết cách xử lý, phân tích, thống kê dữ liệu. Có kiến thức cũng như hiểu biết, thành thạo về các hàm Excel.

Hàm SUMIF là gì ?

Trong Microsoft Excel , SUMIF là một công thức được sử dụng để cộng các giá trị lại với nhau trên một phạm vi ô. Hàm SUM cơ bản thêm các giá trị trong một phạm vi ô, bất kể giá trị là gì. Với SUMIF, người dùng có thể xác định các điều kiện cụ thể phải được đáp ứng cho các giá trị trong một phạm vi ô được đưa vào quy trình bổ sung.

Công thức và cú pháp hàm SUMIF

SUMIF( phạm vị, điều kiện, [sum_range] )

phạm vi Phạm vi của các ô mà bạn muốn áp dụng các tiêu chí chống lại. tiêu chí Các tiêu chí được sử dụng để xác định các ô cần thêm. sum_range Không bắt buộc. Đây là phạm vi của các ô để tổng hợp lại với nhau. Nếu tham số này bị bỏ qua, nó sử dụng phạm vi là sum_range .

Hàm SUMIF trả về một giá trị số.

Áp dụng đối với

Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Cách sử dụng hàm SUMIF

Hãy nhìn vào hình ảnh bên phải. Nếu bạn chỉ muốn thêm các giá trị lớn hơn năm, bạn có thể viết công thức SUMIF như sau:

Vì các ô A2 và A5 chứa một giá trị lớn hơn năm, chúng sẽ là các ô duy nhất được cộng với nhau bởi công thức, dẫn đến giá trị là 17.

Ví dụ về hàm SUMIF

Sử dụng hàm sumif với con số

Ví dụ 1: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi A1: A5 nhỏ hơn hoặc bằng 10.

SUMIF(A1:A5,"<=10")

Ví dụ 2: Hàm SUMIF sau đây cho kết quả chính xác như nhau. Nó sử dụng toán tử & để nối biểu tượng ‘nhỏ hơn hoặc bằng’ và giá trị trong ô C1.

SUMIF(A1:A5,"<="&C2)

Ví dụ 3: Hàm SUMIF bên dưới (ba đối số, đối số cuối cùng là phạm vi cần tính tổng) tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa giá trị 25.

Ví dụ 4: Hàm SUMIF sau đây cho kết quả chính xác tương tự (đối số thứ hai đề cập đến ô D1).

Sử dụng hàm sumif với chữ văn bản

Ví dụ 5: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1:B5 nếu các ô tương ứng trong phạm vi A1:A5 chứa chính xác “circle”.

Ví dụ 6: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 không chứa chính xác “triangle”.

Ví dụ 7: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “circle” + 1 ký tự. Một dấu hỏi (?) Khớp với chính xác một ký tự.

Ví dụ 8: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa một chuỗi gồm 0 hoặc nhiều ký tự + le. Dấu hoa thị (*) khớp với một loạt các ký tự bằng 0 hoặc nhiều hơn.

Ví dụ 9: Các hàm SUMIF bên dưới các giá trị tổng trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “triangle” hoặc “circle8”.

Sử dụng hàm SUMIF thứ ngày tháng

Lưu ý: hàm DATE trong Excel chấp nhận ba đối số: năm, tháng và ngày.

Ví dụ 12: Hàm SUMIFS bên dưới (có chữ S ở cuối) tính tổng doanh số giữa hai ngày (bao gồm cả ngày bắt đầu và ngày kết thúc).

Lưu ý: hàm SUMIFS trong Excel tính tổng các giá trị dựa trên hai hoặc nhiều tiêu chí (đối số đầu tiên là phạm vi cần tính tổng, theo sau là hai hoặc nhiều cặp phạm vi / tiêu chí). Điều chỉnh ngày để tính tổng doanh số trong một tháng, năm cụ thể, v.v.