Sửa Lỗi Filter Trong Excel / Top 18 Xem Nhiều Nhất & Mới Nhất 10/2023 # Top Trend

Lỗi value trong excel là cách để Excel thông báo, “Đã xảy ra lỗi với cách nhập công thức của bạn. Hoặc đã xảy ra lỗi với các ô bạn đang tham chiếu tới.” Lỗi này rất chung chung và khó có thể tìm thấy nguyên nhân chính xác cho lỗi. Thông tin ở trang này trình bày về các sự cố phổ biến, cùng giải pháp cho lỗi. Có thể bạn sẽ cần thử một hoặc nhiều giải pháp để khắc phục được lỗi cụ thể của mình.

Lỗi VALUE trong excel là gì?

Khi viết hàm trong excel, bạn nhận được các thông báo lỗi #Value, #Ref,… ta có thể hiểu đó là: 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ử).

Cách sửa lỗi #VALUE! Các vấn đề với phép trừ Cách thực hiện phép trừ cơ bản Trừ một tham chiếu ô từ một ô khác Hoặc sử dụng công thức SUM với các số dương và số âm Lỗi #VALUE! với phép trừ cơ bản

Nếu bạn đang sử dụng Windows thì bạn có thể gặp phải lỗi #VALUE! ngay cả khi thực hiện công thức trừ cơ bản nhất. Thao tác sau có thể giải quyết sự cố của bạn:

Trước tiên, hãy thực hiện một lượt kiểm tra nhanh. Trong sổ làm việc mới, nhập số 2 vào ô A1. Nhập số 4 vào ô B1. Sau đó, hãy nhập công thức này =B1-A1 vào ô C1. Nếu bạn gặp lỗi #VALUE!, hãy đi tới bước tiếp theo. Nếu bạn không gặp lỗi, hãy thử các giải pháp khác trên trang này.

Trong Windows, hãy mở panel điều khiển cho Khu vực của bạn.

Windows 10: Bấm vào Bắt đầu, nhập Khu vực, rồi bấm vào panel điều khiển Khu vực.

Windows 8: Ở màn hình Bắt đầu, nhập Khu vực, bấm vào Thiết đặt, rồi bấm vào Khu vực.

Windows 7: Bấm vào Bắt đầu, nhập Khu vực, rồi bấm vào Khu vực và ngôn ngữ.

Trên tab Định dạng, bấm vào Thiết đặt bổ sung.

Tìm Dấu tách danh sách. Nếu Dấu tách danh sách được đặt là dấu trừ, hãy thay đổi dấu tách đó thành một ký hiệu khác. Ví dụ: dấu phẩy là dấu tách danh sách phổ biến. Dấu chấm phẩy cũng là dấu tách danh sách phổ biến. Tuy nhiên, có thể một dấu tách danh sách khác sẽ phù hợp hơn với khu vực cụ thể của bạn.

Bấm vào OK.

Mở sổ làm việc của bạn. Nếu một ô có chứa lỗi #VALUE!, hãy bấm đúp vào ô để chỉnh sửa.

Nếu xuất hiện dấu phẩy ở nơi nên là dấu trừ cho phép trừ, hãy thay đổi những dấu phẩy đó thành dấu trừ.

Nhấn ENTER.

Lặp lại quy trình này cho các ô khác chứa lỗi.

Cách để trừ ngày Trừ một tham chiếu ô từ một ô khác Hoặc sử dụng hàm DATEDIF Lỗi #VALUE! khi trừ ngày được lưu trữ dưới dạng văn bản Kiểm tra khoảng trắng cách quãng

Bấm đúp vào một ngày đang được sử dụng trong công thức trừ.

Đưa con trỏ của bạn tới phần đầu, rồi xem liệu bạn có thể chọn một hay nhiều khoảng trắng được không. Khoảng trắng được chọn ở phần đầu của ô sẽ trông như thế này: Ô có khoảng trắng được chọn trước ngày 01/01/2023

Nếu ô của bạn gặp sự cố, hãy đi tới bước tiếp theo. Nếu bạn không thấy một hoặc nhiều khoảng trắng, hãy đi tới mục tiếp theo nói về việc kiểm tra thiết đặt ngày cho máy tính của bạn.

Chọn cột chứa ngày bằng cách bấm vào tiêu đề cột.

Bấm vào Tiếp theo hai lần.

Ở Bước 3 trên 3 của trình hướng dẫn, bên dưới Định dạng dữ liệu cột, bấm vào Ngày.

Chọn định dạng ngày, rồi bấm vào Hoàn tất.

Lặp lại quy trình này với các cột khác để đảm bảo chúng không chứa các khoảng trắng cách quãng trước ngày.

Các sự cố với khoảng trắng và văn bản Loại bỏ khoảng trắng tạo ra lỗi #VALUE! 1. Chọn ô được tham chiếu 2. Tìm và thay thế 3. Loại bỏ khoảng trắng 4. Thay thế hoặc Thay thế tất cả 5. Bật bộ lọc 6. Đặt bộ lọc 7. Chọn mọi hộp kiểm chưa được đặt tên 8. Chọn các ô trống, rồi xóa 9. Xóa bộ lọc 10. Kết quả

GHI CHÚ: Trong ví dụ này, hãy lưu ý rằng ô E4 chứa một hình tam giác màu lục và số được căn sang trái. Điều này có nghĩa là số được lưu trữ dưới dạng văn bản. Điều này có thể gây ra nhiều sự cố về sau. Nếu bạn gặp vấn đề này, chúng tôi khuyên bạn nên chuyển đổi các số được lưu trữ dưới dạng văn bản sang số.

Kiểm tra xem có ký tự hay văn bản đặc biệt nào không Ví dụ với lỗi #VALUE! Cùng một ví dụ với hàm ISTEXT Hãy thử sử dụng các hàm thay cho các toán tử

Các công thức chứa toán tử toán học như + và * không thể tính toán các ô chứa văn bản hoặc khoảng trắng. Trong trường hợp này, hãy thử sử dụng một hàm thay vào đó. Hàm sẽ thường bỏ qua các giá trị văn bản và tính mọi thứ dưới dạng số, giúp loại bỏ lỗi #VALUE!. Ví dụ: thay vì =A2+B2+C2, hãy nhập =SUM(A2:C2). Hoặc thay vì =A2*B2, hãy nhập =PRODUCT(A2,B2).

Các giải pháp khác để thử Cố gắng xác định nguồn lỗi Chọn lỗi Thay thế lỗi #VALUE! bằng nội dung khác

Đôi khi, bạn chỉ muốn thay thế lỗi #VALUE bằng nội dung khác, chẳng hạn như văn bản của bạn, số 0 hoặc một ô trống. Trong trường hợp này, bạn có thể thêm hàm IFERROR vào công thức của mình. Hàm IFERROR sẽ kiểm tra xem liệu có tồn tại lỗi không và nếu có, hàm này sẽ thay thế lỗi bằng một giá trị khác do bạn lựa chọn. Nếu không có lỗi, công thức ban đầu của bạn sẽ được tính toán. Hàm IFERROR chỉ hoạt động trong Excel 2007 trở lên. Đối với các phiên bản cũ hơn, bạn có thể sử dụng hàm IF(ISERROR()).

CẢNH BÁO: Hàm IFERROR sẽ ẩn mọi lỗi, không chỉ riêng lỗi #VALUE!. Việc ẩn lỗi không được khuyến khích vì lỗi thường là một ký hiệu cho bạn biết có điều cần khắc phục, chứ không phải để ẩn đi. Chúng tôi không khuyến khích bạn sử dụng hàm này trừ khi bạn chắc chắn hoàn toàn rằng công thức của mình hoạt động theo cách bạn muốn.

Ô chứa lỗi #VALUE! Lỗi ẩn bằng hàm IFERROR Đảm bảo các kết nối dữ liệu sẵn sàng Hãy đăng câu hỏi vào Diễn đàn Cộng đồng Excel

Nếu bạn không chắc chắn mình cần làm gì vào thời điểm này thì bạn có thể tìm kiếm các câu hỏi tương tự trong Diễn đàn Cộng đồng Excel hoặc đăng câu hỏi riêng của bạn.

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.

1. Lỗi #REF! trong Excel

Lỗi #REF! Trong Excel xảy ra nếu một tham chiếu không hợp lệ. Trong nhiều trường hợp, lỗi này là do trang tính, các hàng, cột bị xóa hoặc công thức có tham chiếu tương đối bị sao chép sang vị trí khác, trong đó tham chiếu không hợp lệ.

=SUM(C5:C9) công thức ban đầu trong ô C10

=SUM(#REF) công thức khi được sao chép vào ô E5

2. Ngăn lỗi #REF! xảy ra trong Excel

Cách tốt nhất để ngăn lỗi #REF! xảy ra là trước khi xóa các cột, hàng hoặc trang tính, cần đảm bảo các cột, hàng này không được tham chiếu bởi các công thức trong bảng tính đang làm việc.

Nếu đang sao chép và dán công thức sang ô, cột mới, bạn sẽ phải chuyển đổi tham chiếu ô thành tham chiếu tuyệt đối để ngăn các thay đổi xảy ra trong quá trình sao chép.

3. Cách sửa lỗi #REF! trong Excel

Lỗi #REF trong Excel khó khắc phục hơn so với các lỗi Excel khác vì tham chiếu ô ban đầu đã mất vĩnh viễn. Nếu biết tham chiếu là gì, bạn có thể sửa lỗi bằng tay. Nếu không biết tham chiếu ô là gì, bạn sẽ phải nghiên cứu kỹ bảng tính trước khi sửa lỗi các công thức.

Ngoài ra nếu xóa một cột hoặc ô và thấy xuất hiện lỗi #REF, giải pháp là nên hoàn tác hành động đó ngay lập tức. Sau khi hoàn tác, các lỗi #REF sẽ không còn nữa và được thay thế bằng các công thức ban đầu. Nếu muốn, bạn có thể chỉnh sửa các công thức để loại trừ các cột mà bạn muốn xóa.

Lưu ý: bạn không thể hoàn tác việc xóa một trang tính Excel. Nếu xóa tab bảng tính và thấy hiển thị thông báo lỗi #REF, giải pháp là đóng file lại và mở phiên bản đã lưu cuối cùng. Vì vậy chúng tôi khuyến cáo bạn nên sao lưu bảng tính trước khi xóa một hoặc nhiều sheet khác nhau.

4. Xóa lỗi #REF

Để xóa lỗi #REF khỏi bảng tính, bạn có thể sử dụng chức năng Find và Replace. Sử dụng phím tắt Ctrl + H để mở hộp thoại, sau đó nhập #REF! vào khung Find và khung Replace để trống.

Bạn có thể thực hiện các thay đổi với Find và Replace, hoặc sử dụng Replace All để thay thế tất cả các lỗi #REF cùng lúc.

Khi chỉ mục cột được đặt thành giá trị chính xác là 2 thì lỗi #REF! sẽ không còn nữa, hàm VLOOKUP sẽ hoạt động đúng cách:

Lưu ý: nếu hàm INDEX tham chiếu hàng hoặc cột không hợp lệ, nó cũng trả về lỗi #REF.

Lưu ý: bạn cũng có thể thấy lỗi #REF với hàm INDEX khi tham chiếu hàng hoặc cột khôg hợp lệ.

6. Bẫy lỗi #REF! với hàm IFERROR

Trong hầu hết các trường hợp, việc bẫy lỗi #REF! trong một công thức không có ý nghĩa gì, vì lỗi #REF! không phải là lỗi quan trọng. Tuy nhiên, nếu muốn bạn cũng có thể sử dụng hàm IFERROR để bắt lỗi #REF trong trường hợp nếu đang xây dựng các tham chiếu động với hàm INDIRECT.

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

Để khắc phục sự cố này các bạn có thể loại bỏ các khoảng trắng ẩn này như sau:

Bước 1: Chọn các ô được tham chiếu, tiếp theo nhấn tổ hợp Ctrl + H để mở hộp thoại thay thế.

Bước 2: Trong hộp thoại Find and Replace các bạn nhập dấu cách trong ô Find what và trong Replace with các bạn không nhập gì. Sau đó nhấn Replace All để loại bỏ tất cả các khoảng trắng.

Nhiều khi có các ký tự ẩn khác ngoài các khoảng trắng làm cho ô nhìn thì thấy trống nhưng thực sự ô đó không trống, để loại bỏ các ký tự này thì các bạn có thể thực hiện tiếp các bước tiếp theo.

Bước 4: Chọn biểu tượng lọc trên tiêu đề cột và bỏ chọn trong ô Select All, sau đó đánh dấu trong hộp kiểm Blanks hoặc những hộp kiểm không có nội dung gì.

Bước 5: Xuất hiện kết quả lọc, các bạn chọn các ô trống và nhấn phím Delete. Như vậy các bạn đã xóa mọi ký tự ẩn trong các ô.

Bước 6: Xóa bộ lọc bằng cách nhấn chọn biểu tượng lọc trên ô tiêu đề và chọn Clear Filter From …

Như vậy các bạn sẽ được kết quả như sau:

Lỗi #VALUE! khi có văn bản hay ký tự đặc biệt

Nếu có ô chứa văn bản hay các ký tự đặc biệt sẽ tạo ra lỗi #VALUE! nhưng thật khó để biết ô nào chứa văn bản hay ký tự đặc biệt nếu danh sách dữ liệu rất dài.

Để tìm kiếm ô có chứa văn bản hay ký tự đặc biệt để các bạn có thể sửa lỗi #VALUE! thì các bạn sử dụng hàm ISTEXT để kiểm tra các ô.

Bước 1: Tạo thêm 1 cột cạnh cột dữ liệu các bạn cần kiểm tra, trong ô đầu tiên của cột các bạn nhập công thức hàm =ISTEXT(C6)

Bước 2: Sao chép công thức xuống tất cả các ô còn lại, hàm ISTEXT() sẽ trả về giá trị TRUE nếu trong ô có chứa văn bản hay ký tự đặc biệt, trả về FALSE nếu ô không chứa văn bản hay ký tự đặc biệt.

Bước 3: Tiếp theo các bạn chọn đến những ô chứa TRUE và xóa văn bản hay ký tự đặc biệt đó đi thì hàm sẽ không còn lỗi #VALUE!

Các bạn cũng cần xóa cột mà bạn tạo ở bước 1, và kết quả sẽ không còn lỗi #VALUE!

Lỗi Value xảy ra khi sử dụng các toán tử số (+,*)

Ví dụ các bạn sử dụng toán tử + để cộng doanh số bán hàng của hai ngày mà một ngày nghỉ bán không có doanh số. Các bạn sẽ nhận được lỗi #VALUE.

Để sửa lỗi này các bạn thay thế toán tử + bằng công thức hàm SUM, hàm SUM sẽ bỏ qua chuỗi và trả về kết quả.

Tương tự với toán tử *, nếu bị lỗi #VALUE các bạn có thể thay thế bằng hàm PRODUCT để nhân trong Excel.

Lỗi Value xảy ra khi sử dụng hàm Average, Sum

Nếu trong vùng tính trung bình hay tính tổng có chứa lỗi #Value! thì kết quả hàm Average, Sum cũng sẽ trả về lỗi #VALUE.

Để sửa lỗi này các bạn có thể dùng công thức mảng kết hợp với 2 nhóm hàm: If + Isseror hoặc Iferror.

Cách 1 {=SUM(IFERROR(C6:C8;””))}

Vì đây là công thức mảng nên khi các bạn nhập xong hàm SUM thì các bạn nhấn tổ hợp Ctrl + Shift + Enter để hiển thị dấu ngoặc nhọn.

Cách 2

{=SUM(IF(ISERROR(C6:C8);””;C6:C8))}

Hàm ISERROR sẽ xác định xem giá trị nào trong vùng C6:C8 là giá trị lỗi thì sẽ trả về kết quả là trống, như vậy các giá trị để tính tổng sau cùng hàm SUM sẽ loại bỏ được tất cả các giá trị lỗi.

Tương tự như với hàm SUM thì hàm AVERAGE các bạn cũng sử dụng công thức mảng hàm IF và ISERROR.

Cách 1 {=AVERAGE(IFERROR(C6:C8;””))} Cách 2 {=AVERAGE(IF(ISERROR(C6:C8);””;C6:C8))}

Tương tự với một số hàm khác (IF, CONCATENATE…) các bạn nếu gặp phải lỗi #VALUE! khi tham chiếu đến các giá trị lỗi thì các bạn cũng có thể xử lý lỗi bằng cách sử dụng hàm IF, ISERROR và ISERR hoặc IFERROR trong công thức.

Khi xử lý bảng dữ liệu Excel, thông báo lỗi Circular Reference có thể xuất hiện ở từng ô hoặc khi bảng vừa được mở ra. Thực tế đây không hẳn lỗi trên Excel mà ở dạng thông báo, cảnh báo người dùng với nội dung chính có 1 vòng lặp trong công thức tính.

Hướng dẫn fix lỗi Circular Reference trong Excel

Thông báo lỗi vòng lặp trên Excel có nội dung There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells.

Trong bảng này bạn sẽ thấy công thức tại ô F7 tham chiếu đến ô F7. Khi đó các công thức dùng trong các ô D7, C11 và D11 sẽ đều bị loại bỏ kết quả.

Bước 1:

Khi xảy ra thông báo trên, trước hết chúng ta nhấn vào mục Formulas rồi chọn nhấn tiếp vào Error Checking.

Bước 2:

Hiển thị danh sách menu nhấn chọn Circular References chỉ dẫn để kiểm tra lại công thức, loại bỏ các tham chiếu gây vòng lặp trong công thức đó. Bạn sẽ nhìn thấy ngay vị trí lỗi vòng lặp đó trong bảng Excel. Sau khi biết được vị trí bạn tiến hành xóa, chuyển công thức, sửa lại tham chiếu trong công thức.

Như vậy lỗi vòng lặp đã được sửa.

Trong trường hợp người dùng bắt buộc phải sử dụng vòng lặp để tính toán thì cũng có thể xác lập số vòng lặp tối đa khi sử dụng. Nhấn vào File trên giao diện Excel rồi chọn Options trong menu bên trái giao diện. Tiếp đến trong nội dung mới nhấn vào mục Formulas.

Nhìn sang nội dung bên phải người dùng tích chọn vào Enable iterative calculation để sử dụng tính toán lặp. Bên dưới phần Maximum Iterations là số tối đa vòng lặp được sử dụng. Nhấn OK để lưu lại.

Như vậy việc sử dụng vòng lặp trong Excel cũng có thể xảy ra tùy thuộc vào nội dung dữ liệu Excel mà bạn đang xử lý. Nếu muốn dùng vòng lặp thì trước hết bạn cần phải kích hoạt tùy chọn cho phép dùng vòng lặp trong giao diện Options trên Excel.