Nếu bạn đang gặp lỗi #REF! trong Excel mà không biết cách giải quyết. Vậy thì hãy đọc nhanh bài viết bên dưới đây của Hoàng Hà Mobile. Chúng tôi sẽ chia sẻ cho bạn các nguyên nhân và cách khắc phục lỗi này nhé. Đặc biệt, chúng tôi còn đính kèm thêm ví dụ minh họa để bạn dễ hình dung, cũng như áp dụng thao tác dễ dàng hơn.
Lỗi #REF! trong Excel là gì?
Đây là một trong những thông báo lỗi phổ biến, thường xuất hiện khi một ô chứa tham chiếu không hợp lệ đối với một phạm vi hoặc ô đã nào đó. Biểu hiện của lỗi “Reference” làm ảnh hưởng đến tính toán và hiển thị kết quả trong công cụ bảng tính của Excel. Hơn nữa, nếu người dùng không biết cách sửa lỗi này thì sẽ tốn rất nhiều thời gian, cũng như công sức.
Thông thường, lỗi #REF! sẽ xuất hiện ở rất nhiều hàm tính trong Excel. Chẳng hạn như AVERAGE, SUM, VLOOKUP, SUMPRODUCT, INDEX hay IFERROR. Bởi vì các hàm này được tính toán dựa trên tham chiếu thông tin của một giá trị khác trong bảng tính. Do đó, nếu một giá trị có vấn đề thì ngay lập tức bạn sẽ nhận được thông báo lỗi.
Bên cạnh đó, lỗi tham chiếu xuất hiện ở rất nhiều trường hợp khác nhau. Nếu người dùng không “bắt đúng bệnh” thì bạn rất khó “chữa bệnh” cho tệp Excel của mình. Đặc biệt, nếu bạn đang phải xử lý một khối lượng lớn dữ liệu thì lỗi tham chiếu sẽ càng trở nên phiền phức. Chính vì vậy, bạn đọc không nên bỏ qua các nội dung quan trọng ngay sau đây.
Nguyên nhân và cách khắc phục lỗi #REF! chính xác
Có thể nói, nguyên nhân chính của lỗi tham chiếu là bao gồm việc bị trống thông tin hoặc sự thay đổi vị trí của dữ liệu mà các công thức đang phụ thuộc. Tuy nhiên, để giúp bạn đọc có hướng giải quyết cụ thể nhất. Hoàng Hà Mobile đã phân tách từng nguyên nhân gây ra lỗi tham chiếu cố định và có ví dụ minh họa, để từ đó bạn có thể hiểu rõ hơn về vấn đề nhé.
Lỗi vì dữ liệu tham chiếu bị xóa hoặc bị ghi đè
Với lỗi đầu tiên này được xem là vấn đề chung mà rất nhiều người dùng Excel đã và đang gặp phải. Chính điều này đã gây ra khó khăn cho mọi người trong việc hiểu, phân tích dữ liệu, cũng như có được kết quả chính xác.
Nguyên nhân
Nguyên nhân của vấn đề này có thể xảy ra khi bạn cố gắng thực hiện các thao tác như cắt, chép, dán, hoặc xóa dữ liệu trong bảng tính mà không cập nhật các liên kết tham chiếu tương ứng. Từ đó, dẫn đến công thức sẽ không thể xác định được nữa và xuất hiện lỗi tham chiếu ngay trên ô mà bạn muốn nhận kết quả.
Nhằm giúp bạn hình dung chi tiết hơn, chúng tôi sẽ cung cấp thêm cho bạn một vài hình ảnh về lỗi này nhé. Cụ thể, chúng ta có bảng tính chứa điểm các môn học và tổng điểm rất là hoàn thiện như hình mô tả.
Tuy nhiên, nếu người dùng vô tình xóa mất cột D chứa điểm môn lý đi thì tức nghĩa công thức của bạn sẽ không hiểu được thao tác. Vì lẽ đó mà ngay tại cột tổng điểm nó lại xuất hiện thông báo #REF!
Cách khắc phục
Đối với nguyên nhân thứ nhất, cách sửa lỗi nhanh chóng và hiệu quả nhất là bạn cần kiểm tra và cập nhật lại các liên kết tham chiếu trong các công thức để đảm bảo rằng chúng đang chỉ định vào đúng một vùng cụ thể nào đó. Như ví dụ này, chúng ta mất đi cột D thì bạn phải bổ sung lại dữ liệu chính xác cho cột thì công thức sẽ tự khắc hiểu được. Từ đó, người dùng sẽ có được kết quả tổng điểm dựa vào hàm SUM chính xác nhất.
Lỗi vì các ô tham chiếu không có sẵn hoặc vượt quá vùng dữ liệu
Một trong những nguyên nhân phổ biến tiếp theo của lỗi #REF!, đó chính là phạm vụ dữ liệu bạn tham chiếu đến đã vượt ngoài “tầm kiểm soát”. Vậy cụ thể đây là lỗi như thế nào và giải pháp ra sau. Hoàng Hà Mobile mời bạn đọc tiếp đoạn nội dung giải thích ngay sau đây nhé.
Nguyên nhân
Một khi người dùng thao tác nhập lệnh thì có khả năng vùng chọn của bạn đã quá giới hạn cho phép. Do đó, Excel không thể tìm thấy dữ liệu cần thiết và việc hiển thị lỗi tham chiếu trong ô là điều hiển nhiên.
Ví dụ dễ hiểu hơn là như bảng tính bên dưới đây, chúng ta thấy được hàm VLOOKUP chỉ áp dụng tham chiếu đến 4 phạm vi mà thôi. Nhưng đôi khi vì nhập sai số mà lệnh tìm kiếm lại xuất hiện cho 5 cột. Từ đó, lỗi tham chiếu hiển thị và gây ra nhiều hoang mang cho người dùng.
Kết quả sau khi người dùng nhấn phím “Enter”, ngay tức thì bạn sẽ được hệ thống báo về lỗi sai. Nếu không tinh ý thì có nhiều người vẫn không nhận ra được lỗi #REF này đâu nhé.
Cách khắc phục
Với mỗi hàm công thức trong Excel sẽ có một quy luật sử dụng khác nhau. Do đó, người dùng cũng nên đọc hiểu kỹ các hàm trước khi tiến hành sử dụng. Và nếu gặp lỗi đối sánh thì bạn hãy xác minh xem các ô mà công thức tham chiếu có tồn tại hay không. Nếu không, người dùng hãy tinh chỉnh công thức hàm Excel để hướng tới đúng phạm vi dữ liệu mới. Ngoài ra, bạn cũng có thể sử dụng chức năng “Tìm và Thay thế” để nhanh chóng thay đổi tất cả các liên kết tham chiếu trong bảng tính.
Chẳng hạn như ở ví dụ vừa rồi, chúng ta sẽ xét lại sự đồng nhất giữa số lượng dữ liệu cần tìm kiếm và ô tham chiếu. Đơn giản như vậy là bạn đã có thể sửa lỗi không tìm thấy dữ liệu xác định thành công rồi nhé.
Lỗi #REF! vì sao chép dữ liệu Excel từ file này sang file khác
Trong quá trình xử lý data, rất nhiều người dùng phải thường xuyên sao chép dữ liệu từ file Excel này sang file Excel khác. Không những thế, đối với những tệp dữ liệu lớn có độ xử lý phức tạp hơn, người dùng còn áp dụng công thức tham chiếu qua lại giữa các tệp khác nhau. Song, vẫn có không ít người thao tác sai nên dẫn đến lỗi tham chiếu xuất hiện.
Nguyên nhân
Khi bạn sao chép dữ liệu từ một file này sang một file khác, các liên kết tham chiếu giữa các ô có thể không được cập nhật đúng cách. Vấn đề này có thể xảy ra khi tên file, đường dẫn, hoặc cả các khoảng dữ liệu được tham chiếu thay đổi không trùng khớp. Kết quả là, Excel không thể tìm thấy dữ liệu được tham chiếu và hiển thị lỗi trong ô hoặc phạm vi bảng tính.
Chẳng hạn như hình minh họa sau đây chúng ta sẽ tận dụng lại bảng tính về tổng điểm các môn học. Chúng ta điền thông tin từ cột A đến E và tính SUM điểm ở cột F.
Tuy thế, khi copy qua một file khác, chúng ta lại đổi dữ liệu nhập vào từ cột C đến D và cuối cùng là tổng điểm ở cột H. Như vậy, Excel sẽ không hiểu được thao tác bạn vừa làm và xác định điều này là không trùng khớp với công thức.
Cách khắc phục
Cách khắc phục tốt nhất đối với lỗi #REF! khi sao chép dữ liệu không thành công, đó chính là bạn cần kiểm tra sự trùng khớp với file Excel gốc. Đặc biệt, đối với các dự án lớn, việc sử dụng công cụ “Quản lý Tên” cũng có thể giúp duy trì tính nhất quán giữa các liên kết tham chiếu khi sao chép dữ liệu giữa các file Excel khác nhau.
Và tại ví dụ dưới đây, Hoàng Hà Mobile đã tinh chỉnh lại dữ liệu ở các cột theo đúng như bản gốc thì sẽ ghi nhận được công thức và kết quả chính xác như hình.
Lỗi #REF vì tham chiếu tới hàm INDIRECT Excel đang đóng
Hàm INDIRECT trong Excel là một hàm được sử dụng để chuyển đổi một văn bản thành một tham chiếu ô hoặc phạm vi ô. Hàm này rất hữu ích khi bạn muốn sử dụng một chuỗi ký tự, cũng như bạn muốn thực hiện các phép toán hoặc tính toán trên dữ liệu đó. Mặc dù hàm INDIRECT rất dễ sử dụng, nhưng nếu không dùng đúng cách thì bạn vẫn gặp lỗi tham chiếu như bình thường.
Nguyên nhân
Việc sử dụng hàm INDIRECT có thể dẫn đến lỗi tham chiếu xuất phát từ một trong 3 nguyên nhân chính sau đây. Thứ nhất, đó là vì phần “ref_text” trong bảng tính không phải là một phạm vi đối sánh hợp lệ. Hơn nữa, việc tham chiếu đến một “miền” vượt qua giới hạn cho phép của công thức INDIRECT cũng có thể gây ra lỗi trên. Và cuối cùng là nguyên nhân phổ biến nhất, khi bảng tính hay file Excel được tham chiếu đến đang đóng.
Như hình ảnh ví dụ về số lượng sản phẩm trong kho, chúng ta thấy được kết quả sau khi áp dụng công thức tham chiếu đang trả về kết quả #REF!. Do vậy, có cách nào để người dùng có thể sửa lỗi và giải quyết công việc nhanh chóng hay không?
Cách khắc phục
Hướng giải quyết tốt nhất đối với người dùng ngay lúc này, đó chính là hãy chắc chắn rằng mình đã mở file Excel mà bảng tính đang tham chiếu. Sau đó, người dùng hãy đối chiếu lại các dữ liệu được tham chiếu trong phạm vi Excel để chúng xác định đúng vị trí, trong khi file đã mở. Thực hiện những bước này giúp tái thiết lập liên kết và giảm thiểu khả năng xuất hiện lỗi đối sánh khi tham chiếu đến file Excel đang đóng.
Một số lỗi hàm Excel khác mà bạn không nên bỏ qua
Trong quá trình làm việc với Microsoft Excel, người dùng thường xuyên phải đối mặt với những thách thức liên quan đến cú pháp. Ngoài lỗi phổ biến như #REF!, có một số lỗi cú pháp khác mà người dùng thường gặp phải. Chẳng hạn như lỗi định dạng dữ liệu sai #VALUE!, lỗi không tìm thấy kết quả #N/A, lỗi Excel chứa các giá trị số không hợp lệ #NUM!, hay lỗi chia cho 0 #DIV/0!. Song song đó, Lỗi #NAME? là một vấn đề khác, xảy ra khi tên hoặc hàm không được Excel nhận diện.
Làm thế nào để xử lý và tránh những lỗi này là một kỹ năng quan trọng cho người sử dụng Excel. Việc hiểu rõ về cách cú pháp hoạt động và kiểm soát các tham chiếu, cũng như các hàm là chìa khóa để tối ưu hóa hiệu suất và đảm bảo tính chính xác của các cú pháp hàm trong bảng tính Excel. Đồng thời, việc sử dụng các công cụ kiểm tra lỗi và theo dõi các ô chứa công thức có thể giúp người dùng phát hiện và khắc phục lỗi một cách nhanh chóng và hiệu quả. Hoặc nếu bạn muốn biết cách sửa các lỗi Excel chi tiết hơn thì có thể tìm hiểu về thủ thuật Excel tại Trang Tin Nhanh của Hoàng Hà Mobile nhé.
Kết luận
Với những thông tin về nguyên nhân và cách sửa lỗi #REF! trong bảng tính Excel, Hoàng Hà Mobile hy vọng bạn có thể vận dụng thủ thuật thành công. Và đừng quên tiếp tục theo dõi các bài viết khác của chúng tôi để biết thêm nhiều mẹo sử dụng Excel hay ho khác bạn nhé.
Xem thêm:
