#Ref Là Gì Trong Excel / TOP 14 # Xem Nhiều Nhất & Mới Nhất 6/2023 # Top View

Đang xem: Lỗi ref trong excel là gì

1. Ý nghĩa của lỗi #REF!

REF là tên viết tắt của từ Reference, tức là Tham chiếu. Do đó trong mọi trường hợp xuất hiện lỗi này đều hiểu là lỗi tham chiếu.

Một số nguyên nhân chủ yếu gây ra lỗi #REF!

Tham chiếu tới 1 file đang chưa được mở nên không tham chiếu được.Đối tượng cần tham chiếu không có trong vùng tham chiếu.Đối tượng cần tham chiếu bị mất bởi việc xóa cột, xóa hàng chứa đối tượng đó, do bị ghi đè bởi việc copy paste

2. Cách khắc phục một số trường hợp cụ thể

a. Tham chiếu bị mất bởi bị xóa, bị ghi đè

Trong ví dụ trên chúng ta thấy cột C chứa dữ liệu năm 2017 đã bị xóa, dẫn tới kết quả ở cột Tổng bị lỗi #REF! (bị mất tham chiếu ở cột năm 2017)

Việc này rất hay xảy ra bởi chúng ta hay có thói quen sử dụng thao tác Delete cả cột, cả hàng thay vì chỉ xóa dữ liệu trong cột, hàng đó.

Ngoài ra việc cut dữ liệu rồi paste trong Excel cũng gây ra lỗi tham chiếu khi chúng ta Paste đè vào 1 vùng dữ liệu khác đang được tham chiếu tới. Ví dụ như sau:

Sau khi thực hiện thao tác Cut và paste vào cột C thì xảy ra lỗi tham chiếu ở cột E

Cách khắc phục:

Nếu lỡ tay vừa thực hiện xong hành động trên thì chúng ta có thể bấm phím Ctrl + Z để hoàn tác (trở lại lúc trước khi thực hiện thao tác đó)Nếu phát hiện muộn, bạn có thể đóng file excel mà không lưu. Sau đó mở lại file.Một khi đã lưu file chứa lỗi tham chiếu thì không thể sửa lại được nữa. Lúc đó chúng ta đành phải tự rà soát lại dữ liệu và sửa lại bằng tay.

b. Tham chiếu không có sẵn, vượt quá phạm vi

Thường xảy ra khi chúng ta tham chiếu tới các đối tượng là col_index_num, row_index_num trong một số công thức vượt quá phạm vi vùng cần tham chiếu.

Ví dụ:

Trong bảng trên chúng ta thấy vùng A2:E4 chỉ có 5 cột, trong khi đó đối tượng Col_index_num trong hàm vlookup lại tính theo giá trị cột thứ 6, vượt quá phạm vi của vùng dữ liệu cần tham chiếu. Do đó kết quả trả về là lỗi #REF!

Cách khắc phục:

Sử dụng công thức tại đối tượng col_index_num hoặc row_index_num trong các công thức dò tìm, tham chiếu để tránh lỗi khi nhập bằng tay (kết quả công thức sẽ tự thay đổi khi bảng tham chiếu thay đổi). Ví dụ: Sử dụng hàm Match() để dùng cho row_index_num, dùng hàm column() để dùng cho col_index_numSửa lại bằng tay nếu như bạn không biết dùng các hàm ở trên

c. Tham chiếu tới 1 file đang đóng

Thường xảy ra khi sử dụng hàm Indirect để tham chiếu tới 1 đối tượng bên ngoài workbook đang làm việc và workbook được tham chiếu đang đóng.

Cách khắc phục:

Mở Workbook được tham chiếu hoặc thay đổi phương pháp tham chiếu (không sử dụng hàm Indirect mà thay bằng hàm khác ít gặp lỗi hơn)

Như vậy là chúng ta đã có thể hiểu rõ về lỗi #REF! trong Excel rồi. Hy vọng là chúng ta sẽ tránh được việc xảy ra lỗi này cũng như nếu gặp phải lỗi này thì chúng ta cũng có thể biết cách sửa được nó.

Lỗi #REF khá nghiêm trọng và thường xuyên gặp trong Excel. Khi xảy ra tình trạng lỗi sẽ phải mất rất nhiều thời gian cũng như công sức để sửa. Vậy lỗi ref trong excel là gì? Có cách nào để khắc phục tình trạng này xảy ra.

Lỗi ref trong excel là gì? 

Lỗi ref trong excel là gì? – REF chính là tên viết tắt của cụm từ Reference mang nghĩa là tham chiếu. Vậy nên trong trường hợp báo lỗi này thì đã có một tham chiếu không hợp lệ. Cũng có thể do các hàng, cột trong trang tính bị xóa. Hoặc là công thức dùng để tham chiếu tính tương đối đã bị copy sang nơi khác. 

Nguyên nhân gây ra lỗi #REF

– Khi đến một file vì chưa được mở nên không tham chiếu được. 

– Có một đối tượng cầm phải tham chiếu nhưng lại không được cập nhật trong vùng đó. 

– Khi xóa cột hay hàng vô tình loại bỏ mất đối tượng cần phải tham chiếu. 

Cách sửa lỗi ref trong excel là gì?

Tuy nhiên, lỗi này khó khắc phục hơn các lỗi khác vì ô tham chiếu lúc đầu đã không còn nữa. Trường hợp xác định được tham chiếu thì có thể hoàn toàn sửa lỗi bằng tay. Ngược lại không biết tham chiếu ô thì phải xem xét lại bảng tính trước khi sửa lỗi.

 Nếu sau khi xóa cột và ô mới xuất hiện lỗi #REF thì cách tốt nhất là hoàn tác lại hành động đó. Thực hiện xong điều đó lỗi sẽ không còn mà thay thế bằng công thức như lúc đầu. Với phương pháp này, bạn chỉ việc chỉnh sửa số liệu cũng như công thức tại cột muốn xóa. 

Phương pháp khắc phục trong tình huống cụ thể 

Ô tham chiếu bị ghi đè hoặc xóa mất

Ví dụ bạn đang có bảng tính tổng nhưng trong một cột bị xóa mất dữ liệu khiến cho cột tổng số hiện lỗi #REF. Tình huống này xảy ra khi người dùng có thói quen xóa cả cột hay cả hàng thay vì chỉ xóa dữ liệu cột đó. Không chỉ thế, việc cắt rồi dán đè vùng dữ liệu cũng gây ra lỗi. Muốn khắc phục tình trạng đó làm như sau: 

– Nếu là hành động xóa ngay tức thì, bạn hãy bấm ngay Ctrl + Z để mọi thứ trở lại như lúc ban đầu. 

– Trường hợp đã lưu file chứa lỗi mới phát hiện ra nên không sửa được. Lúc này cách duy nhất là rà soát lại từng dữ liệu rồi sửa theo cách thủ công mà thôi. 

Tham chiếu vượt quá phạm vi 

Khi các đối tượng tham chiếu là col_index_num sẽ có một số công thức vượt quá phạm vi cần tham chiếu. Bạn đang có một bảng tính hàm đối tượng chỉ được 5 cột nhưng hàm Vlookup với Col_index_num thuộc cột thứ 6. Như thế vùng dữ liệu đã vượt quá nên kết quả để lại là lỗi #REF. 

Để khắc phục lỗi chỉ cần thao tác đúng như sau: 

– Sử dụng công thức có chứa đối tượng col_index_num để dò tìm. Nhớ tham chiếu lại để tránh lỗi khi nhập công thức theo phương pháp thủ công. Chú ý là tùy tùy đối tượng mà dùng hàm khác nhau. Ví dụ như: hàm Match () chỉ để dùng cho row_Index_num còn column() thì chỉ phù hợp cho col_index_num. 

– Trường hợp các hàng quá rắc rối không biết sử dụng thì hãy sửa lại bằng tay theo phương pháp thủ công. 

File chứa ô tham chiếu bị đóng 

Tình huống này thì ít xảy ra, nó sẽ xảy ra khi sử dụng hàm Indirect sẽ tham chiếu các đối tượng bên ngoài phần workbook đang làm việc. Lúc này, hãy nhớ mở workbook đang làm việc lẫn workbook cần nhưng đang đóng tham chiếu. Hoặc bạn có thể lựa chọn không sử dụng hàm Indirect để thay bằng một hàm khác ít xảy ra lỗi hơn là được. 

Sử dụng bẫy lỗi #REF bằng hàm IFERROR 

Nhiều người cho rằng việc bẫy lỗi #REF trong một công thức là không cần thiết và vô nghĩa. Bởi lỗi này không có gì là quan trọng và khó khăn. Và muốn dùng hàm IFERROR để xử lý lỗi nhưng với một điều kiện phải đang sử dụng các tham chiếu động chứa hàm Indirect. 

Một số chú ý để không gặp lỗi #REF

– Trong Excel bạn sẽ không thể hoàn tác khi xóa một trang tính. Trong trường hợp xóa bảng tính sẽ hiển thị một thông báo lỗi #REF. Vì thế trước khi xóa một hay nhiều sheet khác nhau nên sao lưu bảng tính trước. 

– Muốn ngăn lỗi này xảy ra, trước khi quyết định xóa cột hay hàng nhớ đảm bảo các cột không tham chiếu bởi công thức trong bảng tính đang làm việc. 

– Muốn sao chép hay dán công thức ở cột mới cần chuyển đổi từ tham chiếu ô sang tham chiếu tuyệt đối. Điều này để ngăn chặn những thay đổi không muốn trong quá trình sao chép thông tin. 

Khi làm việc với công thức trong Excel, rất có thể bạn đã không nhận được kết quả mong muốn mà nhận được những giá trị như sau bắt đầu bằng dấu # như: #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, #NULL!

Nguyên nhân gây lỗi #NUM! trong Excel

Lỗi #NUM! trong Excel thường xảy ra:

Khi một công thức yêu cầu tham số là một giá trị số nhưng lại được người dùng đưa vào một giá trị không phải là số hoặc không hợp lệ.

Khi kết quả của phép tính trong Excel quá lớn hoặc quá nhỏ

Nguyên nhân gây lỗi #VALUE! trong Excel

Có thể nói, lỗi #VALUE! là một trong những lỗi hay gặp nhất khi sử dụng công thức Excel. Đây là cách Excel thông báo cho chúng ta rằng có vấn đề với công thức hoặc vùng bảng tính được tham chiếu. Vấn đề ở đây là gì thì tuỳ vào công thức mà bạn đang sử dụng:

Cách sửa lỗi #N/A trong Excel

Nói chung lỗi #N/A xảy ra khi các hàm tìm kiếm VLOOKUP, HLOOKUP, MATCH, LOOKUP không tìm được giá trị cần tìm.

Lỗi #DIV/0! xảy ra khi trong 1 công thức Excel xuất hiện phép chia cho 0. Vì phép chia cho 0 không được định nghĩa trong toán học nên đây là 1 lỗi và được thể hiện bằng #DIV/0! trong Excel:

Lỗi #REF! do xoá vùng tham chiếu trong công thức

Lỗi #REF! thường xảy ra khi vùng tham chiếu trong công thức Excel bị xoá hoặc bị ghi đè lên.

Nếu bạn lỡ tay xoá nhầm cột thì hãy sử dụng phím tắt CTRL + Z để có thể lấy lại cột vừa bị xoá (chức năng Undo)

Ngoài ra để hạn chế việc xảy ra lỗi #REF!, bạn nên sử dụng công thức sắp theo cú pháp =SUM(B2:D2)

Cách khắc phục: Sửa lại công thức VLOOKUP cho phù hợp =VLOOKUP(A8,A2:D5,4,FALSE)

Lỗi #REF! do sử dụng tham số Row hoặc Column của hàm Index không phù hợp

Cách khắc phục: Sửa lại công thức cho phù hợp: =INDEX(B2:E5,4,4)

Lỗi #REF! khi sử dụng hàm Indirect tham chiếu tới file excel đóng

Cách khắc phục: Mở file Excel đang đóng.

Lỗi #NAME? do viết sai tên hàm

Cách khắc phục: Kiểm tra lại tên hàm đã được viết đúng chưa và sử dụng tính năng Function Arguments như hình dưới

Ví dụ: name “Profit” trong công thức sau đây chưa được khai báo trong Excel

Lỗi #NAME! khi name sử dụng bị viết sai trong Excel

Khi tên (name) đã được khai báo trong Excel nhưng bị viết sai cũng sẽ gây ra lỗi #NAME!

Lỗi #NAME! do dữ liệu kiểu chữ không có dấu ngoặc kép bao quanh

Ví dụ:

Lỗi #NAME! do thiếu dấu : ngăn cách giữa địa chỉ vùng trong Excel

Ví dụ:

Lỗi #NAME! do sử dụng 1 hàm hỗ trợ bởi Addin nhưng Addin chưa được cài đặt

Hàm có thể được hỗ trợ bởi Addin hoặc là 1 hàm UDF tự viết và không có trong Workbook bạn đang làm việc

Cách khắc phục: Cài đặt bổ sung Addin hoặc Code của UDF

Lỗi #NULL! xảy ra khi công thức lấy phần giao nhau giữa 2 vùng không giao nhau. Ví dụ như sau:

Rate this post

Khi đến thời điểm gửi bảng tính Excel của bạn, thật quan trọng để bảo vệ các dữ liệu mà bạn đang chia sẻ. Bạn có thể muốn chia sẻ dữ liệu của bạn, nhưng không có nghĩa là nó nên được thay đổi bởi một ai đó.

Đang xem: Workbook trong excel là gì

Các bảng tính thường chứa các dữ liệu cần thiết mà không được thay đổi hoặc xoá người nhận. Thật may mắn, Excel có các tính năng có sẵn để bảo vệ bảng tính của bạn.

Mật khẩu bảo vệ toàn bộ workbook để ngăn chặn chúng bị mở bởi người sử dụng trái phép. Bảo vệ những sheet riêng biệt và các cấu trúc của workbook đó, để ngăn chặn việc chèn hoặc xóa các sheet trong workbook đó. Bảo vệ ô, cho phép hoặc không cho phép các thay đổi đến các ô quan trọng hoặc các công thức trong bảng tính Excel của bạn.

Thậm chí người dùng với những dự định tốt nhất có thể vô tình làm hỏng một công thức quan trọng hay phức tạp. Điều tốt nhất để làm là loại bỏ tùy chọn để thay đổi hoàn toàn bảng tính của bạn.

Làm thế nào để bảo vệ Excel: ô, Sheet & Workbook (xem và học)

Chúng ta sẽ bắt đầu với việc bảo vệ worksheet rộng hơn, sau đó hạ xuống bảo vệ mục tiêu hẹp hơn mà bạn có thể áp dụng trong Excel. Chúng ta cùng bắt đầu học cách bảo vệ dữ liệu bảng tính của bạn:

1. Mật khẩu bảo vệ một tập tin Workbook của Excel

Chúng ta sẽ bắt đầu bằng việc bảo vệ toàn bộ các tập tin Excel (hoặc workbook) với một mật khẩu để ngăn chặn người khác mở nó.

Làm việc này rất dễ dàng. Trong khi thao tác trong Excel, điều hướng đến tab File chọn tab Info. Nhấp vào tùy chọn Protect Workbook thả xuống và chọn Encrypt with Password.

Khi chọn bất cứ mật khẩu nào, chọn một sự kết hợp mạnh mẽ và an toàn bao gồm các chữ cái, số và ký tự, hãy nhớ rằng mật khẩu phân biệt dạng chữ.

Quan trọng cần lưu ý đó là Microsoft đã thực sự làm tăng giá trị mức độ nghiêm trọng của việc bảo vệ mật khẩu trong Excel. Trong những phiên bản trước, đã có những cách khắc phục dễ dàng để phớt lờ việc bảo vệ mật khẩu của Workbook trong Excel, nhưng không nằm trong những phiên bản mới hơn.

Thiết lập một mật khẩu phức cho mục đích an ninh, nhưng hãy chắc chắn lưu trữ nó an toàn.

Trong Excel 2013 và những bản sau này, đặt mật khẩu sẽ ngăn chặn các phương pháp truyền thống để phớt lờ nó. Hãy đảm bảo rằng bạn lưu trữ mật khẩu của mình một cách cẩn thận và an toàn hoặc bạn sẽ có nguy cơ mất quyền truy cập vĩnh viễn vào Workbook của bạn.

Workbook trong Excel – chức năng Mark as Final (đánh dấu là bản cuối cùng)

Nếu bạn muốn giảm tác động với các bảng tính của mình, hãy xem xét sử dụng các tính năng Mark as Final. Khi bạn đánh dấu một file Excel như là phiên bản cuối cùng, nó chuyển tập tin sang chế độ chỉ đọc read-only, và người dùng sẽ phải cấp quyền lại để chỉnh sửa.

Đánh dấu một tập tin như là phiên bản cuối cùng để để lại một cảnh báo lịch sự cho người sử dụng tập tin đó.

Đánh dấu một tập tin như là phiên bản cuối cùng sẽ thêm vào một cảnh báo mềm nằm trên đầu của tập tin. Bất cứ ai mở tập tin sau khi đã được đánh dấu như bản cuối cùng thì sẽ nhìn thấy một chú ý, cảnh báo họ rằng tập tin đó là bản cuối cùng.

Khi một tập tin được đánh dấu là phiên bản cuối cùng, workbook đó sẽ hiển thị như thế này 

Đánh dấu tập tin như là phiên bản cuối cùng là một cách không chính thức báo hiệu rằng một tập tin không nên thay đổi nữa. Người nhận vẫn có khả năng bấm vào chỉnh sửa Edit Anyway và chỉnh sửa bảng tính. Đánh dấu một tập tin như phiên bản cuối cùng thì nhiều hơn là sự gợi ý, nhưng đó là cách giải quyết tuyệt vời nếu bạn tin tưởng những người dùng khác.

2. Mật khẩu bảo vệ cấu trúc Sheet trong Excel của bạn

Tiếp theo, chúng ta hãy học cách bảo vệ các Structure (cấu trúc) của một workbook trong Excel. Tùy chọn này sẽ đảm bảo rằng không có sheet nào bị xóa, thêm vào, hoặc sắp xếp lại bên trong của workbook đó.

Nếu bạn muốn tất cả mọi người có thể truy cập vào workbook, nhưng giới hạn việc thay đổi mà họ có thể làm trên một tập tin, đây là một khởi đầu tuyệt vời. Việc bảo vệ cấu trúc của workbook, và giới hạn cách người dùng có thể thay đổi các sheet bên trong nó.

Khi tùy chọn này được bật, những điều sau sẽ có tác động:

Không có workbook mới nào có thể được thêm vào workbook. Không có sheet mới nào có thể bị xóa bởi workbook. Sheet có thể không còn được ẩn đi hoặc bỏ ẩn khỏi chế độ xem của người dùng. Người dùng không thể kéo và thả các tab sheet để sắp xếp lại chúng trong workbook đó.

Đối Tượng Workbook Trong Excel Là Gì ? Hướng Dẫn Cách Tạo Workbook Trong Excel 15

3. Cách bảo vệ các ô trong Excel

Bây giờ, chúng ta hãy xem tiếp các phương pháp chi tiết để bảo vệ một bảng tính. Cho đến bây giờ, chúng ta đã biết cách cài mật khẩu bảo vệ toàn bộ một workbook hoặc cấu trúc của một tập tin Excel. Trong phần này, chúng ta đào vào cách bảo vệ các ô trong Excel với cài đặt cụ thể mà bạn có thể áp dụng. Chúng ta sẽ học cách làm thế nào để cho phép hoặc chặn một số kiểu thay đổi cho các phần của bảng tính.

Đối Tượng Workbook Trong Excel Là Gì ? Hướng Dẫn Cách Tạo Workbook Trong Excel 16

Tùy chọn này cho phép bảng tính của bạn được bảo vệ rất cụ thể. Theo mặc định, các tùy chọn sẽ gần như hoàn toàn khóa lại các bảng tính. Hãy thêm một mật khẩu để sheet này được bảo vệ. Nếu bạn bấm OK vào thời điểm này, chúng ta hãy xem chuyện gì xảy ra khi bạn cố gắng thay đổi một ô.

Sau khi một sheet đã được bảo vệ, cố gắng để thay đổi một ô sẽ xuất hiện thông báo lỗi này.

Excel thông báo lỗi rằng ô đó được bảo vệ, đó chính xác là điều mà chúng tôi muốn cho bạn thấy.

Về cơ bản, tùy chọn này có thể đoán trước được nếu bạn muốn đảm bảo rằng bảng tính của bạn sẽ không bị thay đổi bởi những người truy cập vào tập tin. Sử dụng tính năng bảo vệ sheet là cách mà bạn có thể chọn lọc bảo vệ bảng tính đó.

Bảo vệ cụ thể trong Excel

Chúng ta hãy xem lại các tùy chọn hiển thị khi bạn bắt đầu bảo vệ một sheet trong Workbook trong Excel.

Mật khẩu bảo vệ các ô worksheet trong tùy chọn của Excel:

Trình đơn Protect Sheet cho phép bạn tinh chỉnh các tùy chọn cho việc bảo vệ Sheet. Mỗi hộp trong trình đơn này cho phép người dùng thay đổi thêm một chút bên trong một worksheet được bảo vệ.

Để xóa bảo vệ, đánh dấu vào ô tương ứng trong danh sách. Ví dụ, bạn có thể cho phép người sử dụng bảng tính để định dạng ô bằng cách chọn các phần tương ứng.

Chọn ô Format cells, columns và rows cho phép người dùng thay đổi hình thức trực quan của các ô mà không cần sửa đổi các dữ liệu ban đầu. Insert columns và rows có thể được chọn vì vậy người dùng có thể thêm nhiều dữ liệu hơn, trong khi bảo vệ các ô gốc.

Ô quan trọng không chọn là ô Protect worksheet and contents of locked cells Điều này là để bảo vệ dữ liệu bên trong các ô dữ liệu.

Khi bạn đang làm việc với dữ liệu tài chính quan trọng hoặc những công thức sẽ được sử dụng trong việc đưa ra quyết định, bạn phải nắm giữ việc kiểm soát các dữ liệu và chắc chắn rằng nó không thay đổi. Sử dụng các kiểu bảo vệ có mục tiêu là một kỹ năng quan trọng trong Excel cho người sử dụng chuyên nghiệp.

Ôn lại và tiếp tục học hỏi thêm về Excel

Hãy khóa một bảng tính trước khi bạn gửi đi là điều rất quan trọng để bảo vệ nhũng dữ liệu có giá trị của bạn và đảm bảo rằng nó không bị dùng sai mục đích. Những lời khuyên tôi chia sẻ trong hướng dẫn này giúp bạn duy trì kiểm soát dữ liệu đó, thậm chí là sau khi bảng tính Excel của bạn được chuyển tiếp và chia sẻ.