Nếu đã từng dùng phần mềm Excel chắc hẳn bạn đã từng nhìn thấy lỗi

REF!, đây là lỗi khá phổ biến trong Excel và thường mất nhiều thời gian cũng như công sức để khắc phục và xử lý nó. Vậy nên, ở bài học Excel ngày hôm nay Gitiho sẽ hướng dẫn các bạn cách sửa lỗi

REF trong Excel nhanh chóng và đơn giản nhất.

Ngoài ra, nếu bạn đang loay hoay trong việc muốn sao chép giá trị của một ô (ví dụ là 28) mà không muốn copy cả công thức (ví dụ =14*2), thì phần cuối bài viết này dành cho bạn, hãy đọc đến hết bài viết nhé!

Lỗi

REF là lỗi gì?

Nếu bạn là người dùng Excel thường xuyên thì chắc hẳn bạn đã từng gặp lỗi

REF! khi áp dụng các công thức và hàm tính toán. Vậy

REF trong Excel có nghĩa là gì?

REF là từ viết tắt của REFERENCE, nghĩa Tiếng Việt là Tham chiếu. Do nên, mọi ô tính hiển thị lỗi này, có nghĩa là bạn đang mắc phải lỗi tham chiếu.

Lỗi

REF! do một số nguyên nhân sau gây nên:

1. Tham chiếu tới 1 file Excel khác (bảng tính khác) mà file đó chưa được mở nên lỗi không tham chiếu được.
2. Đối tượng cần tham chiếu (ô hay dải ô) không có trong vùng tham chiếu.
3. Đối tượng cần tham chiếu bị mất, do bạn đã thực hiện xóa cột, hàng chứa đối tượng đó, hay do bị ghi đè bởi lệnh Copy/Paste.

Cách sửa lỗi

REF trong Excel với một số trường hợp cụ thể

Khi tham chiếu bị mất bởi nguyên nhân bị xóa hoặc ghi đè

Ban đầu ta có bảng dữ liệu sau:

Bảng dữ liệu chứa các công thức tính toán

Sau khi bạn thực hiện lệnh Xóa (Delete) cột dữ liệu năm 2019 đi. Kết quả cột Tổng sẽ hiển thị lỗi REF trong Excel như sau:

Lỗi REF xảy ra khi tham chiếu bị xóa

Nguyên nhân: Lỗi do bị mất cột tham chiếu dữ liệu năm 2019. Việc này rất hay xảy ra khi bạn có thói quen thao tác Xóa cả cột dữ liệu, cả hàng thay vì chỉ xóa đi giá trị trong cột, hàng đó.

Ngoài ra, việc thực hiện Cut dữ liệu từ một dải ô khác sau đó Paste (Dán) vào cùng chứa đối tượng tham chiếu cũng gây nên lỗi này do dữ liệu bị ghi đèn lên nhau.

Ghi đè dữ liệu lên tham chiếu

Kết quả hiển thị lỗi như sau:

Lỗi REF do tham chiếu bị ghi đè

Cách sửa lỗi

REF trong Excel:

• Bạn hoàn lại thao tác gây nên lỗi

  REF! Excel này bằng cách nhấn tổ hợp phím tắt Ctr+Z (bảng tính sẽ quay về trước khi thực hiện thao tác đó)

• Phát hiện ra lỗi này muộn và không quay lại thao tác trước được, bạn nên đóng lại file Excel và không lưu file. Sau đó mới 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.

Khi tham chiếu không có sẵn hay vượt quá phạm vi

Nguyên nhân:

Trong một số công thức hàm của Excel buộc bạn phải điền các tham số như col_index_num, row_index_num. Việc điền quá phạm vi tham chiếu của các đối tượng này cũng sẽ làm lỗi

REF! xuất hiện.

hàm vlookup báo lỗi

REF

Trong đó, vùng dữ liệu A2:E4 cần tham chiếu chỉ có 5 cột, trong khi đó tham số 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 đó, hàm vlookup báo lỗi

REF.

Cách sửa lỗi

REF trong Excel:

• Sử dụng công thức tại tham số col_index_num hoặc row_index_num trong các công thức tra cứu, 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_num.

• Thực hiện sửa lỗi bằng tay khi bạn không biết sử dụng các hàm trên.

  Xem thêm: Lỗi

  N/A trong Excel là gì? Làm sao để khắc phục lỗi này?

Khi tham chiếu tới file Excel đang đóng

Nguyên nhân: Bạn sử dụng hàm để tham chiếu tới 1 đối tượng bên ngoài bảng tính đang làm việc và file Excel chứa đối tượng đó đang đóng.

Cách sửa lỗi

REF trong Excel: Mở bảng tính (file Excel) đượ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)

Ngoài ra, khi file Excel được tham chiếu tới bị đổi tên, di chuyển, bị xóa hoặc hỏng thì Excel sẽ không đọc được dữ liệu tham chiếu dẫn tới bị lỗi. Đây là lỗi tên bảng tính chưa được giải quyết, bạn cần kiểm tra lại tên và file dữ liệu xem có bị xóa hay hỏng chưa và tiến hành khắc phục.

Hướng dẫn cách Copy giá trị ô thay vì công thức của ô đó

Hãy nhìn vào ví dụ dưới đây:

Sao chép kết quả không chứa công thức

Sau đây, Gitiho sẽ giới thiệu tới bạn đọc một số cách để sao chép (Copy) giá trị ô (kết quả) chứ không phải sao chép công thức của ô.

Sử dụng lệnh Paste Special

Chúng ta có thể sử dụng tính năng Paste Special trong Excel để chỉ copy giá trị của ô thay vì công thức của ô đó. Các thao tác cần thực hiện như sau:

1. Chọn vùng hoặc ô dữ liệu muốn sao chép
2. Nhấn Ctrl + C hoặc nhấn Chuột phải chọn lệnh Copy để thực hiện sao chép
3. Nhấn Chuột phải vào vùng/ ô được chọn. Sau đó, chọn lệnh Paste Values (biểu tượng 123) dưới Paste Options.
   Paste Special để copy kết quả không có công thức

Như vậy, kết quả khi bạn sao chép vùng dữ liệu sẽ hiện thị là giá trị, chứ không phải là công thức của các ô tính trong vùng.

Sử dụng VBA Excel

Đối với những người thành thạo Microsoft Excel, VBA Macro là một lựa chọn sáng suốt để nhanh chóng thay thế công thức bằng kết quả tính toán.

Bạn cần thực hiện các thao tác sau:

1. Nhấn giữ phím tắt Alt + F11, cửa sổ Microsoft Visual Basic for Applications mở ra.
2. Nhấp Insert > Module, dán đoạn mã sau vào Module Window.

Sub DisplayedToActual() 'Updateby20131126 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "Hoc Excel Online" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False For Each Rng In WorkRng    Rng.Value = Rng.Text Next Application.ScreenUpdating = True End Sub

3. Nhấn F5 để chạy mã, trong hộp thoại hiện ra, chọn vùng dữ liệu bạn muốn chuyển đổi thành giá trị thực như hình dưới đây:

Chạy mã macro để copy kết quả không có công thức

4. Nhấn lệnh OK, để kết thúc.

Xem thêm: Hướng dẫn cách khắc phục lỗi Excel không chạy công thức

Kết luận

Qua bài viết này, Gitiho hy vọng các bạn đã nắm được cách sửa lỗi

REF trong Excel và sao chép nguyên giá trị hay kết quả của ô/ vùng dữ liệu. Ngoài ra hãy đọc thêm các bài viết dưới đây để nằm lòng các kiến thức hữu ích cho công việc hàng này của bạn với Excel.

Ref trong Excel là gì?

Lỗi

REF trong Excel thường xuất hiện khi công thức tham chiếu đến ô không hợp lệ. Lỗi này thường xuyên xảy ra, nhất là với các ô nằm trong công thức tham chiếu bị xóa hoặc bị dán đè. Một số hàm thường mắc lỗi

REF Excel: AVERAGE.

Tại sao VLOOKUP ra ref?

Hàm VLOOKUP giúp tìm kiếm dữ liệu theo hàng dọc và trả về giá trị tương ứng theo hàng ngang trong một bảng. Trường hợp bạn sử dụng hàm VLOOKUP với số thứ tự của cột lấy dữ liệu vượt quá số cột hiện có của bảng tham chiếu thì sẽ gây ra lỗi

REF!

Value trong Excel là gì?

VALUE 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.

Lời name trong Excel là gì?

Lỗi

NAME là lỗi sai tên, sai định danh khi nhập các lệnh hàm trong Excel, đặc biệt là các hàm tính toán. Lỗi

NAME thường xảy ra khi bảng tính không thể nhận dạng được chủ thể nào đó thì hệ thống sẽ phát hiện lỗi

NAME và báo lỗi.