Khắc phục lỗi #DIV/0! trong Excel khi công thức chia cho số 0 hoặc ô trống

Thông báo Lỗi

Bạn đang làm việc trong Excel, và đột nhiên, công thức của bạn trả về kết quả:

#DIV/0!

Lỗi này thường xuất hiện trong một ô mà phép tính đang cố gắng chia một số cho số không hoặc cho một ô trống mà Excel hiểu là số không.

Giải thích

Tôi đã thấy lỗi này vô số lần, và hầu như lúc nào cũng do một trong hai nguyên nhân sau:

• Chia cho Số Không Trực Tiếp: Công thức của bạn trực tiếp cố gắng chia một số cho giá trị 0. Ví dụ, =10/0 sẽ ngay lập tức trả về #DIV/0!.

• Chia cho Ô Trống hoặc Ô Không Chứa Số: Phổ biến hơn, công thức của bạn tham chiếu đến một ô trống hoặc chứa văn bản. Khi Excel thực hiện các phép toán số học, nó coi các ô trống và giá trị văn bản là 0. Trong ngữ cảnh phép chia, điều này có nghĩa là nếu ô B2 trống và công thức của bạn là =A2/B2, Excel thực tế sẽ coi đó là =A2/0, dẫn đến lỗi.

Đây là cách Excel nói với bạn rằng, "Này, tôi không thể thực hiện phép toán này! Phép chia cho số không là không xác định."

Cách Sửa Lỗi Từng Bước

Cách tiếp cận ưu tiên của tôi để sửa lỗi #DIV/0! là làm cho các công thức của bạn mạnh mẽ hơn để xử lý các trường hợp chia cho số không hoặc ô trống. Dưới đây là một vài phương pháp:

Phương pháp 1: Ngăn chặn Chia cho Số Không với Hàm IF

Phương pháp này chủ động kiểm tra xem số chia có bằng không hoặc trống hay không trước khi phép chia xảy ra. Nếu có, bạn có thể yêu cầu Excel trả về một giá trị khác thay vì lỗi. Đây có thể là một ô trống, số không, hoặc một văn bản mô tả.

Tình huống: Tính Doanh số Trung bình trên Mỗi Đơn vị

Giả sử bạn có số liệu doanh số ở cột A và số đơn vị bán được ở cột B. Công thức của bạn trong C2 là =A2/B2. Nếu B2 là 0 hoặc trống, bạn sẽ nhận được lỗi #DIV/0!.

Ví dụ Công thức Gốc:

=A2/B2

Cách sửa bằng hàm IF:

Chúng ta sẽ kiểm tra xem B2 có bằng không hay không. Nếu có, chúng ta sẽ trả về một chuỗi trống ("") để giữ cho ô sạch sẽ. Ngược lại, thực hiện phép chia.

=IF(B2=0, "", A2/B2)

Giải thích:

• B2=0: Đây là phép kiểm tra logic. Nó kiểm tra xem giá trị trong ô B2 có phải là 0 hay không. Đáng chú ý, Excel cũng diễn giải một ô trống là 0 trong các phép so sánh số. Do đó, kiểm tra này thường bao gồm cả số không rõ ràng và các ô trống.
• "": Đây là giá trị nếu phép kiểm tra logic là ĐÚNG (tức là B2 là 0 hoặc trống). Một chuỗi trống làm cho ô hiển thị trống. Bạn cũng có thể sử dụng 0 để trả về số không, hoặc "N/A" cho một chỉ báo văn bản.
• A2/B2: Đây là giá trị nếu phép kiểm tra logic là SAI (tức là B2 không phải là 0 hoặc trống), do đó phép chia diễn ra bình thường.

Khi nào nên sử dụng phương pháp này: Sử dụng phương pháp này khi bạn cần kiểm soát chính xác đầu ra nếu số chia là không hoặc trống. Nó lý tưởng cho các tình huống mà bạn có thể muốn xử lý điều kiện cụ thể này khác với các lỗi tiềm ẩn khác.

Phương pháp 2: Xử lý Lỗi một cách Mượt mà với Hàm IFERROR

Hàm IFERROR là một trình xử lý lỗi đa năng hơn. Nó cố gắng đánh giá một công thức, và nếu bất kỳ lỗi nào xảy ra (không chỉ #DIV/0!), nó sẽ trả về một giá trị được chỉ định thay thế.

Tình huống: Bất kỳ công thức nào có thể gây ra lỗi

Sử dụng ví dụ trước của chúng ta về =A2/B2.

Ví dụ Công thức Gốc:

=A2/B2

Cách sửa bằng hàm IFERROR:

Chúng ta sẽ bao bọc phép chia trong hàm IFERROR. Nếu phép chia gây ra lỗi, nó sẽ trả về một chuỗi trống.

=IFERROR(A2/B2, "")

Giải thích:

• A2/B2: Đây là giá trị hoặc phép tính mà Excel cố gắng đánh giá.
• "": Đây là giá trị mà Excel trả về nếu đối số đầu tiên (A2/B2) gây ra bất kỳ lỗi nào (#DIV/0!, #VALUE!, #REF!, v.v.). Một lần nữa, bạn có thể sử dụng 0, "N/A", hoặc bất kỳ giá trị phù hợp nào khác.

Khi nào nên sử dụng phương pháp này: Khi bạn chỉ muốn loại bỏ bất kỳ lỗi nào và trả về một giá trị mặc định, và bạn không cần phân biệt giữa các loại lỗi khác nhau. Nó thường gọn gàng và ngắn gọn hơn so với các câu lệnh IF lồng nhau để kiểm tra nhiều lỗi.

Phương pháp 3: Đảm bảo Tính toàn vẹn Dữ liệu (Sửa dữ liệu nguồn)

Đôi khi, cách sửa tốt nhất không nằm ở bản thân công thức, mà ở dữ liệu cơ bản. Nếu một ô mà lẽ ra không bao giờ được bằng không hoặc trống lại bằng không hoặc trống, điều này thường chỉ ra lỗi nhập liệu, vấn đề nhập dữ liệu, hoặc một sai sót trong quy trình thu thập dữ liệu của bạn.

Các bước:

• Xác định Nguồn: Truy ngược lại ô số chia. Giá trị của nó đến từ đâu? Nó là do người dùng nhập, một công thức khác, hay một dữ liệu nhập vào?

• Sửa lỗi Nhập liệu: Nếu đó là dữ liệu do người dùng nhập, hãy đảm bảo người dùng biết rằng trường này không được bằng không hoặc trống. Bạn có thể cần đào tạo họ hoặc triển khai các quy tắc xác thực dữ liệu.

• Áp dụng Xác thực Dữ liệu: Sử dụng tính năng Data Validation (Xác thực Dữ liệu) của Excel để ngăn chặn các mục nhập không hợp lệ. Ví dụ, bạn có thể đặt một quy tắc chỉ cho phép các số nguyên lớn hơn 0 trong cột "Số Đơn vị Bán được" của mình.

  Chọn cột (ví dụ: B).

  • Đi đến tab Data (Dữ liệu) > Data Validation (Xác thực Dữ liệu).
  • Trong mục Allow (Cho phép), chọn "Whole number" (Số nguyên) hoặc "Decimal" (Số thập phân).
  • Trong mục Data (Dữ liệu), chọn "greater than" (lớn hơn) và đặt Minimum (Giá trị tối thiểu) là 0.
  • Tùy chọn, thêm Input Message (Thông báo đầu vào) và Error Alert (Cảnh báo lỗi) để hướng dẫn người dùng.

• Xem xét lại các Công thức/Dữ liệu nhập từ nguồn: Nếu giá trị không/trống đến từ một công thức khác hoặc một dữ liệu nhập vào, hãy điều tra nguồn đó. Tại sao nó lại tạo ra giá trị không hoặc trống khi lẽ ra không nên như vậy?

Khi nào nên sử dụng phương pháp này: Khi số chia bằng không hoặc trống đại diện cho dữ liệu không chính xác hoặc thiếu cần được sửa tại nguồn gốc của nó, thay vì chỉ xử lý ở phần hiển thị.

Xác minh

Sau khi áp dụng một trong các cách sửa lỗi, bạn cần xác nhận rằng nó hoạt động chính xác trong nhiều điều kiện khác nhau. Dưới đây là cách tôi xác minh:

• Kiểm tra với Dữ liệu Hợp lệ: Nhập các số hợp lệ, khác không vào cả ô số bị chia và số chia. Công thức sẽ tạo ra kết quả đúng (ví dụ: 10/2 sẽ là 5).

• Kiểm tra với Số chia bằng Không: Thay đổi thủ công ô số chia thành 0. Công thức bây giờ sẽ hiển thị 0, "" (trống), hoặc "N/A" tùy theo cách sửa lỗi bạn đã chọn, chứ không phải #DIV/0!.

• Kiểm tra với Số chia trống: Xóa nội dung của ô số chia (làm cho nó hoàn toàn trống). Công thức sẽ lại hiển thị giá trị thay thế bạn đã chọn (0, "", hoặc "N/A"), chứ không phải #DIV/0!.

• Kiểm tra với Số chia là Văn bản (đối với IFERROR): Nếu bạn đã sử dụng IFERROR, hãy thử nhập một số văn bản (ví dụ: "abc") vào ô số chia. Công thức vẫn phải xử lý điều này một cách mượt mà (vì #VALUE! thường sẽ xảy ra) và hiển thị giá trị thay thế bạn đã chọn.

• Kéo Công thức xuống/ngang: Nếu bạn đang áp dụng cách sửa lỗi cho một vùng, hãy kéo tay cầm điền để áp dụng công thức cho các ô khác và nhanh chóng quét tìm bất kỳ lỗi #DIV/0! nào còn sót lại.

Mẹo để Ngăn chặn Lỗi #DIV/0! trong Tương lai

• Chủ động với IF/IFERROR: Hãy tạo thói quen sử dụng các hàm này khi viết công thức liên quan đến phép chia, đặc biệt nếu số chia đến từ dữ liệu nhập của người dùng hoặc dữ liệu biến đổi.

• Sử dụng Xác thực Dữ liệu: Đối với các ô nhập liệu quan trọng, hãy thiết lập các quy tắc xác thực dữ liệu để ngăn người dùng nhập số không hoặc để trống các ô nếu các giá trị đó không hợp lệ cho các phép tính.

• Ghi nhãn và Hướng dẫn Rõ ràng: Nếu một bảng tính được sử dụng bởi người khác, hãy ghi nhãn rõ ràng các ô nhập liệu và cung cấp hướng dẫn về các loại giá trị được mong đợi.

• Ô Tính toán Trung gian: Đối với các bảng tính phức tạp, đôi khi việc có các ô tính toán trung gian sẽ hữu ích. Bạn có thể tính số chia của mình trong một ô, sau đó kiểm tra ô đó trước khi sử dụng nó trong phép chia cuối cùng.

• Hiểu rõ Nguồn Dữ liệu của bạn: Luôn biết dữ liệu của bạn đến từ đâu. Nếu nó được nhập vào, hãy đảm bảo quy trình nhập xử lý dữ liệu số bị thiếu hoặc không hợp lệ một cách thích hợp (ví dụ: chuyển đổi giá trị null thành một giá trị mặc định).

Bằng cách thực hiện các chiến lược này, bạn sẽ xây dựng các bảng tính Excel mạnh mẽ hơn. Chúng sẽ xử lý một cách mượt mà các tình huống chia cho số không khó chịu đó.