Tổng Hợp Các Lỗi Trong Excel Thường Gặp Và Cách Khắc Phục

Nội dung bài viết được cố vấn chuyên môn bởi Th.S Nguyễn Quốc Tới - Giảng viên giảng dạy và ứng dụng công nghệ thông tin từ cơ bản đến nâng cao tại Trường ĐH Công nghệ GTVT - Giảng viên Khóa học tin học văn phòng tại trung tâm đào tạo thực hành Lê Ánh.

Chắc hẳn bạn đã từng gặp phải nhiều lỗi trong Excel quen thuộc nhưng quên mất hoặc không biết nguyên nhân và cách sửa. Có thể kể đến một số lỗi như lỗi #####, lỗi #VALUE, lỗi #NAME, lỗi #REF,... Vậy cách khắc phục những nỗi này như thế nào?

Hãy cùng Lê Ánh HR tìm hiểu các lỗi trong Excel thường gặp phải và cách khắc phục để sử dụng Excel hiệu quả hơn nhé!

I. Lỗi ##### trong excel là lỗi gì?

Microsoft Excel các bạn thường gặp lỗi ##### trong ô khi cột không đủ rộng để hiện tất cả nội dung trong ô. Bên cạnh đó, công thức trả về ngày và giờ có giá trị âm cũng có thể xuất hiện lỗi #####.

1. Cách khắc phục lỗi ##### trong Excel khi cột không đủ rộng

Cách khắc phục lỗi ##### trong Excel khi cột không đủ rộng như sau:

Cách 1: Để làm cột rộng hơn để hiện đủ nội dung của ô chọn, hãy bấm đúp vào cạnh phải của phần tiêu đề cột, hoặc bạn có thể kéo cạnh đó đến độ rộng mà bạn muốn.

Cách 2: Để làm cho nội dung của ô nhỏ hơn, hãy bấm (kí hiệu) để mở Format cells => Hộp thoại mở ra chọn Alignment (Căn chỉnh) ⇒ chọn Shrink to fit (Thu nhỏ) cho vừa khít trong hộp thoại Text control (Định dạng Ô)

- Nếu số có quá nhiều con số sau dấu thập phân, ở mục Number ấn chọn Decrease Decimal (Giảm thập phân).

- Nếu ngày quá dài, ở mục Number ấn chọn mũi tên kế bên Định dạng Số và chọn Short Date (Ngày Ngắn)

2. Cách khắc phục lỗi ##### trong Excel khi ngày hoặc giờ là giá trị âm

Khi dùng excel, lỗi hiện ##### ngoài lý do ô không đủ rộng để hiện thị thì còn lý do vì ô có giá trị ngày hoặc giờ âm, bạn phải nhớ rằng:

- Xác nhận ngày và giờ là giá trị dương khi bạn cài đặt hệ thống là ngày tháng năm 1900.

- Khi dùng công thức để tính ngày hoặc cộng - trừ thời gian chính xác để tránh kết quả ngày hoặc giờ hiển thị giá trị âm.

- Thay đổi định dạng số khác với kết quả công thức giá trị âm được định dạng ngày và giờ.

II. Lỗi #value trong excel

Hiển thị lỗi #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ác hàm 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, đa dạng nguyên nhân và khó có thể tìm được nhanh chóng lỗi đó là gì.

1. Lỗi #value đối với một hàm

Với lỗi #value bạn có thể gặp ở những hàm như: hàm IF, hàm SUM, Hàm Sumif, hàm Average, hàm INDEX, hàm VLOOKUP,...

Lê Ánh HR chia sẻ tới các bạn cách khắc phục lỗi này với một số hàm cơ bản như sau:

- Hàm IF: do sự phức tạp mà câu lệnh IF có thể được xây dựng, cấu tạo công thức khá dễ bị lỗi #VALUE! Thông thường, bạn có thể ngăn lỗi này bằng cách thêm các hàm cụ thể xử lý lỗi như ISERR, ISERROR hoặc IFERROR vào công thức đang gặp lỗi.

- Hàm AVERAGE hoặc SUM: Chúng ta khắc phục lỗi này bằng cách sử dụng kết hợp hàm AVERAGE, SUM cùng với IF và ISERROR để xác định xem có lỗi trong dải ô đã xác định hay không.

Đây là công thức mảng vậy nên bạn cần phải nhấn ENTER (nếu bạn dùng Microsoft 365) hoặc CTRL+SHIFT+ENTER (với các Microsoft còn lại) trong công thức.

- Hàm VLOOKUP: Khi bạn làm việc với hàm này, có khả năng cao là bạn có thể đã gặp lỗi #VALUE! trong excel vài lần.

+ Nguyên nhân: Tham đối giá_trị_tra_cứu có nhiều hơn 255 ký tự.

Giải pháp là rút ngắn giá trị hoặc sử dụng kết hợp hàm INDEX và MATCH như một giải pháp thay thế đối với lỗi này.

+ Nguyên nhân: Tham đối số_chỉ_mục_cột chứa văn bản (text) hoặc nhỏ hơn 0.

Lỗi này có thể xảy ra do lỗi đánh máy trong tham đối số_chỉ_mục_cột hoặc vô tình chỉ định một số nhỏ hơn 1 làm giá trị chỉ mục.

Giá trị tối thiểu của tham đối số_chỉ_mục_cột là 1, trong đó 1 sẽ là cột tìm kiếm, 2 sẽ là cột đầu tiên ở bên phải của cột tìm kiếm, v.v.. Vì vậy, nếu bạn đang tìm kiếm trong cột A thì số 1 sẽ tham chiếu cột đó, 2 sẽ tham chiếu cột B, 3 sẽ tham chiếu cột C, v.v..

2. Lỗi #value đối với vấn đề với phép trừ

Lỗi #value với phép trừ cơ bản: Với lỗi này, các bạn thao tác như sau để giải quyết:

Bước 1: Trước tiên, bạn hãy thực hiện một lượt kiểm tra nhanh. Nếu lỗi đơn giản thì bạn có thể giải quyết bằng cách nhập hai giá trị vào ô riêng biệt rồi nhập công thức trừ ở ô thứ ba. Nếu bạn nhận được lỗi #VALUE! , đi tới bước tiếp theo.

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

Bước 3: Ở trên tab Định dạng, bạn bấm vào Thiết đặt bổ sung

Bước 4: Tìm Dấu tách danh sách. Nhập số mà bạn chọn vô hộp Decimal places.

Bước 5: Bấm vào OK

Bước 6: Mở sổ excel 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 từng ô tham chiếu một

Bước 7: Nếu xuất hiện dấu phẩy ở nơi nên là dấu trừ đối với phép trừ, hãy thay đổi những dấu phẩy đó thành dấu trừ.

Bước 8: Ấn ENTER để kết thúc.

- Lỗi #value khi trừ ngày tháng được lưu trữ dưới dạng văn bản: Các bạn cần chú ý điều này như sau: Nếu ngày tháng được căn sang bên phải thì đó sẽ là định dạng ngày. Nhưng nếu được căn sang trái thì điều này có nghĩa là đang ở định dạng văn bản. Bạn khắc phục bằng hai cách như sau:

• Cách 1: Kiểm tra khoảng trắng cách quãng => Định dạng dữ liệu lại cho cột bằng cách chọn vào định dạng Date (Ngày)
• Cách 2: Kiểm tra thiết bị ngày cho máy tính của bạn. Bạn có thể thay đổi hệ thống ngày tháng mà máy tính của mình sử dụng sao cho khớp với hệ thống ngày tháng bạn muốn nhập vào Excel. Hoặc bạn có thể tạo cột mới, rồi sử dụng hàm DATE để tạo ngày tháng đúng dựa trên ngày đang được lưu trữ dưới dạng văn bản.

- Lỗi #value do khoảng trắng tạo ra: Lỗi này thường xảy ra do các khoảng trắng ẩn, các khoảng trắng ẩn này có thể làm cho ô có vẻ như trống trong khi chúng thực tế thì không trống. Bạn khắc phục bằng phương pháp như sau:

• Bước 1: Chọn ô được tham chiếu: Tìm ô mà công thức bạn đang dùng để tham chiếu.
• Bước 2: Tìm và thay thế: bằng cách ở tab Home ⇒ bấm chọn Find & Select ⇒ chọn Replace.

Bước 3: Loại bỏ khoảng trắng: Hộp thoại Find and Replace hiện ra ⇒ Nhập một khoảng trắng đơn (gõ dấu cách) ⇒ xóa mọi thứ có thể nằm trong hộp ở hộp Replace with

Bước 4: Chọn thay thế (Replace) hoặc thay thế tất cả (Replace all). Nếu lỗi chưa được giải quyết ⇒ Đi đến bước 5

Bước 5: Bật bộ lọc (Sort & Filler): Đôi khi còn ký tự ẩn khác ngoài khoảng trắng nên ở tab Home ⇒ bấm chọn Sort & Filler ⇒ chọn Filler

Bước 6: Bấm chọn mũi tên lọc ⇒ Bỏ chọn Select all (chọn tất cả) ⇒ Ấn chọn Blanks

Bước 7: Chọn mọi ô hộp thoại chưa được đặt tên.

Bước 8: Chọn các ô tham chiếu trống, rồi chọn Delete.

Bước 9: Xóa bộ lọc: Bấm vào mũi tên lọc ⇒ bấm chọn Clear Filler From “Expense” (Xóa bộ lọc khỏi…) để mọi ô đều được hiển thị.

III. Lỗi #N/A trong excel

Lỗi #N/A trong Excel thường cho biết rằng một công thức không thể tìm thấy những nội dung mà công thức đó được yêu cầu tìm và xảy ra nhiều đối với các hàm VLOOKUP, hàm HLOOKUP, XLOOKUP, LOOKUP hoặc hàm MATCH.

Bạn hãy đảm bảo rằng giá trị tra cứu tồn tại trong dữ liệu nguồn hoặc sử dụng trình khắc phục lỗi như IFERROR lồng trong công thức.

Ví dụ: =IFERROR(FORMULA(),0).

Ngoài ra, bạn có thể sử dụng “” để không hiển thị nội dung gì hoặc thay thế bằng văn bản mà bạn chọn, Ví dụ: =IFERROR(FORMULA(),”Thông báo lỗi”)

IV. Lỗi #NAME trong excel

Lỗi #NAME? chỉ ra rằng bạn cần sửa một nội dung gì đó trong cú pháp nhập hoặc sai công thức các hàm. Đa phần đây là lỗi có thể nhìn thấy trong công thức nên không sử dụng bất cứ hàm xử lý lỗi nào để ẩn lỗi.

Cách tránh mắc lỗi nhập công thức các hàm thì bạn hãy sử dụng Trình hướng dẫn Công thức trong Excel, khi bạn nhập tên dấu bằng và tên công thức, trình hướng dẫn công thức sẽ hiển thị cú pháp dưới dạng văn bản trôi để bạn tham khảo.

Ngoài ra, bạn cũng có thể sử dụng Trình hướng dẫn Hàm để tránh mắc lỗi #Name trong excel. Bạn chọn ô chứa công thức đang nhập ⇒ mở tab Công thức ⇒ nhấn chọn Chèn Hàm.

V. Lỗi #REF trong excel

Lỗi #REF trong excel hiển thị khi công thức tham chiếu đến ô không hợp lệ. Điều này thường xuyên xảy ra nhất khi các ô được công thức tham chiếu bị dán đè hoặc bị xóa đi.

1. Lỗi #REF xảy ra do việc xóa cột, hàng

Nếu bạn vô tình xóa các hàng hoặc các cột tham chiếu thì lỗi #REF sẽ xảy ra.

Khắc phục: Có 2 cách để khắc phục lỗi này dưới đây:

Cách 1: Bạn có thể bấm ngay Nút hoàn tác trên Thanh công cụ truy cập nhanh hoặc ấn tổ hợp phím CTRL + Z

Cách 2: Bạn có thể điều chỉnh công thức để tham chiếu 1 dải ô thay vì tham chiếu riêng lẻ. Sau điều chỉnh đó, bạn có thể xóa bất cứ cột nào, hàng nào khi tính tổng và excel sẽ tự động điều chỉnh công thức.

2. VLOOKUP với tham chiếu bảng ô không chính xác

Ví dụ lỗi #REF trong excel như sau:

Công thức: =VLOOKUP(A8;A2:D5;5;FALSE) sẽ trả về lỗi #REF! vì công thức này đang tìm kiếm giá trị để trả về từ cột 5 nhưng dải ô lại là A:D, chỉ là 4 cột.

Khắc phục: Bạn chỉ cần điều chỉnh cho dải ô tham chiếu lớn hơn hoặc giảm đi giá trị tra cứu cột để khớp với dải ô.

3. INDEX có tham chiếu cột hoặc hàng không chính xác

Tương tự như lỗi của hàm VLOOKUP ở phía trên.

VI. Lỗi Font chữ trong excel

Nguyên nhân gây ra lỗi Font chữ trong excel có thể là:

• Máy của bạn không nhận hỗ trợ các loại font cần thiết, hay sử dụng
• Mở file excel với font chữ khác với font chữ máy bạn sử dụng để nhập dữ liệu vào file
• Nhận file excel từ một máy khác sử dụng font chữ khác với font chữ máy bạn đang sử dụng.

Cách khắc phục lỗi font chữ trong excel như sau:

Mở Unikey ⇒ Mở file Excel bị lỗi font ⇒ Bôi đen vùng bị lỗi font ⇒ Nhấn tổ hợp Ctrl + C ⇒ Nhấn tổ hợp phím Ctrl + Shift + F6 ⇒ Chọn font chữ nguồn và font chữ đích phù hợp ⇒ Chọn Chuyển mã ⇒ Đi đến file Excel đang bị lỗi font ⇒ Chọn 1 ô bất kỳ ⇒ Nhấn Ctrl + V để dán dữ liệu đã được sửa lỗi font chữ trong Excel

VII. Lỗi định dạng ngày tháng trong excel

Đây là lỗi bạn dễ gặp nhất khi dùng excel, vậy cách khắc phục như thế nào? Cùng Lê Ánh HR nghiên cứu cách khắc phục lỗi định dạng ngày tháng năm nhanh nhất ở dưới đây nhé!

Phương án thiết lập ngôn ngữ Window sang Tiếng Việt:

Bước 1: Bạn hãy nhấn vào biểu tượng Windows ở góc trái màn hình ⇒ Gõ tìm kiếm Control Panel ⇒ Ấn chọn Control Panel

Bước 2: Tiếp tục ấn chọn Clock and Region ⇒ Chọn Region

Bước 3: Nhấn vào tab Formats ⇒ Nhấn chọn Vietnamese (Vietnam) tại mục Format ⇒ Tại mục mục Short date, Long date bạn hãy chọn cách hiển thị là dd/MM/yyyy (Ngày/tháng/năm) ⇒ Nhấn OK.

Cuối cùng là tiến hành kiểm tra file excel của bạn hết lỗi định dạng ngày tháng năm trong excel chưa nhé.

VIII. Lỗi #NUM trong excel

Lỗi #NUM trong excel xảy ra khi hàm hoặc công thức chứa giá trị số không hợp lệ. Khi bạn đã nhập giá trị số dùng kiểu định dạng số hoặc kiểu dữ liệu không được hỗ trợ trong phần đối số của công thức, các hàm.

Khắc phục:

Trường hợp công thức dùng hàm lặp (như IRR, RATE,...) và excel không thể tìm ra kết quả, bạn sửa lỗi như sau:

Bước 1: Chọn Tệp ⇒ ấn vào Chọn. Tuy nhiên, nếu bạn đang sử dụng Excel 2007, hãy chọn Microsoft Office bổ sung ⇒ Excel chọn

Bước 2: Trên tab Công thức, dưới phần Tùy chọn tính toán ⇒ ấn chọn Bật tính toán lặp

Bước 3: Ở hộp Lặp tối đa, nhập số lần bạn muốn excel tính toán lại. Số lần lặp lại càng lớn, số lần excel cần tính toán càng nhiều.

Bước 4: Ở hộp Thay đổi tối đa, nhập số lần bạn sẽ chấp nhận các kết quả tính toán. Số càng nhỏ thì kết quả càng chính xác hơn.

Trường hợp công thức cho kết quả là một số quá lớn hoặc quá nhỏ được hiện trong excel. Để sửa lỗi #NUM này, hãy thay đổi công thức để kết quả sẽ nằm trong khoảng (-1*10307 và 1*10307).

Xem thêm:

• Cách Tạo Bảng Trong Excel, Word, Google Sheet, Google Docs
• Mẫu Bảng Chấm Công Excel - Cách Làm Bảng Chấm Công Chi Tiết
• Mẫu Báo Cáo Công Việc Hàng Ngày (Tuần, Tháng) bằng Word, Excel

Như vậy, Lê Ánh HR đã chia sẻ và cùng các bạn nghiên cứu các cách khắc phục những lỗi trong Excel thường gặp trong bài viết hôm nay. Với lượng thông tin, kiến thức bổ ích đó sẽ hỗ trợ các bạn sử dụng Excel hiệu quả và tối ưu chức năng của Excel hơn. Chúc các bạn luôn thành công!

Lê Ánh HR - Nơi đào tạo hành chính nhân sự uy tín nhất hiện nay, đã tổ chức thành công rất nhiều khóa học hành chính nhân sự, khóa học C&B, khóa học tin học văn phòng ... và hỗ trợ kết nối tuyển dụng cho hàng nghìn học viên.

Để biết thông tin chi tiết, bạn vui lòng liên hệ với chúng tôi theo số hotline: 0904.84.88.55 để được tư vấn trực tiếp về các khoá học này.

Ngoài các khóa học hành chính nhân sự chất lượng thì trung tâm Lê Ánh còn cung cấp các khóa học kế toán online - offline, khóa học xuất nhập khẩu chất lượng tốt nhất hiện nay.