CÁCH KHẮC PHỤC LỖI #REF VÀ CÁCH COPY GIÁ TRỊ TRONG EXCEL CỦA Ô MÀ KHÔNG PHẢI CÔNG THỨC

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 viết này Gitiho sẽ hướng dẫn các bạn cách sửa lỗi #REF! 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à giá trị này lại là kết quả của một hàm hay công thức nhất định (ví dụ =14*2), nếu bạn chỉ muốn sao chép (copy) kết quả là 28 thôi, không phải công thức trong ô, 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é!

Hướng dẫn cách khắc phục lỗi #REF!

Lỗi #REF! 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.

Nguyên nhân: 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 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 (Sao chép/ Dán)

Đọc thêm : Hướng dẫn cách sử dụng các tính năng trong thẻ References Microsoft WordCách khắc phục lỗi # REF ! trong một số ít trường hợp đơn cử

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 tài liệu sau :

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! như sau:

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 sau đó Paste (Dán) trong Excel cũng gây nên lỗi này do bạn đã Paste (Dán) hoặc ghi đè dữ liệu vào 1 vùng khác đang được tham chiếu. 

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

Cách khắc phục: 

• Bạn hoàn lại thao tác gây nên lỗi #REF! 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, 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

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 đó, kết quả trả về là lỗi #REF!.

Cách khắc phục: 

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

Đọc thêm : Giải đáp lỗi # VALUE !, # REF !, # NUM, # N / A, # DIV / 0 !, # NAME ?, # NULL ! trong Excel và cách sửaKhi 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 khắc phục: 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)

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

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

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 công thức của ô.

Sử dụng lệnh Paste Special

Các thao tác cần thực thi 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 dưới Paste Options.

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.

Đọc thêm : Cách sửa lỗi Circular References Warning trong Excel 2010, 2013, năm nay nhanh gọnSử 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 thi 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:

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

Qua bài viết này, Gitiho hy vọng các bạn đã nắm được cách khắc phục lỗi #REF! trong Excel hay cách 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. 

Chúc bạn học tốt !Bài viết tìm hiểu thêm khác :Hướng dẫn cách dùng hàm index trong Excel chi tiết cụ thể 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 trong Excel 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ế

Cùng tham gia hội đồng hỏi đáp về chủ đề Excel Cơ Bản

Source: https://dichvubachkhoa.vn
Category : Dịch Vụ Sửa Chữa