Nắm vững cách xử lý lỗi nâng cao trong bảng tính phức tạp

Việc xử lý lỗi trong các bảng tính phức tạp là một thách thức, đặc biệt khi làm việc với tập dữ liệu lớn, công thức phức tạp hoặc nhiều trang tính được liên kết. Các kỹ thuật xử lý lỗi nâng cao giúp duy trì độ chính xác của dữ liệu, giúp nâng cao khả năng sử dụng, giúp phát hiện và khắc phục sự cố dễ dàng hơn. Hướng dẫn này sẽ hiển thị cách xử lý lỗi nâng cao trong các bảng tính phức tạp. Nó sẽ đề cập đến các phương pháp nâng cao để xử lý lỗi, giúp quản lý dữ liệu mượt mà hơn.

Hãy xem xét dữ liệu bán hàng của siêu thị có một số điểm không nhất quán để đưa ra các ví dụ thực tế về cách xử lý lỗi trong các bảng tính lớn phức tạp.

1. Xử lý các lỗi thường gặp bằng IFERROR

Bạn có thể xử lý #DIV/0! (Chia cho số 0) bằng cách sử dụng hàm IFERROR. Nếu bạn muốn tính Số tiền bán hàng trung bình trên mỗi đơn vị, bạn có thể gặp phải phép chia cho 0 nếu Số lượng đặt hàng bằng 0. Hàm IFERROR thay thế các lỗi phổ biến, chẳng hạn như #N/A, #DIV/0! và #VALUE! với các thông báo tùy chỉnh hoặc các phép tính thay thế.

Công thức:

=IFERROR(F2:F71/E2:E71, "No Quantity")

Công thức này tính toán số tiền bán hàng trung bình trên mỗi đơn vị và hiển thị “Không có số lượng” nếu “Số lượng đặt hàng” bằng 0.

Đầu ra:

2. Kiểm tra sự không khớp với hàm IFNA và hàm tra cứu

Nếu muốn lấy dữ liệu khu vực của khách hàng từ một bảng hoặc trang tính khác, bạn có thể gặp phải lỗi #N/A nếu không tìm thấy tên khách hàng. Bạn có thể sử dụng IFNA với VLOOKUP để xử lý các giá trị tra cứu bị thiếu một cách linh hoạt. IFNA hoạt động cụ thể đối với lỗi #N/A.

Công thức:

=IFNA(VLOOKUP("Ela Muller", B2:F71,5,FALSE), "Name Not Found")

Công thức này trả về “Không tìm thấy tên” nếu thiếu tên khách hàng trong bảng tra cứu.

Đầu ra:

3. Xác định các giá trị ngoài phạm vi

Nếu bạn muốn đảm bảo rằng “Số lượng đặt hàng” nằm trong phạm vi thực tế (ví dụ:1-10), bạn có thể kiểm tra các giá trị ngoại lệ. Bạn có thể dùng IF để kiểm tra xem số lượng có nằm ngoài phạm vi hay không:

Công thức:

=IF(AND(F2>=1, F2<=10), "Valid", "Out of Range")

Công thức này trả về “Ngoại phạm vi” nếu bất kỳ giá trị “Số lượng đặt hàng” nào nằm ngoài phạm vi dự kiến.

Đầu ra:

4. Sử dụng thông báo lỗi tùy chỉnh trong xác thực dữ liệu

Xác thực dữ liệu có thể ngăn chặn việc nhập dữ liệu không chính xác và giúp giảm lỗi trước khi chúng xảy ra. Nếu bạn muốn đảm bảo rằng cột Khu vực chỉ chứa Bắc, Nam, Đông hoặc Tây, việc xác thực dữ liệu có thể giúp ngăn chặn các mục nhập không chính xác.

• Chọn Vùng cột và đi tới Dữ liệu tab>> từ Công cụ dữ liệu>> chọn Xác thực dữ liệu .

• Đặt tiêu chí xác thực thành Danh sách với các giá trị được phép:Bắc, Nam, Đông, Tây.
• Chọn Cảnh báo lỗi để hiển thị thông báo tùy chỉnh nếu dữ liệu được nhập không hợp lệ.
  • “Sai khu vực, vui lòng kiểm tra chính tả”.

Đầu ra:

Với bất kỳ mục nhập sai nào trong cột “Khu vực”, một thông báo lỗi sẽ bật lên.

5. Xác thực các phép tính trong Excel

Bạn có thể xác thực các tính toán của mình để kiểm tra lỗi hoặc giá trị bị thiếu. Giả sử bạn muốn xác thực cột Số tiền bán hàng, cột này phải là tích của Giá bán lẻ và Số lượng đặt hàng. Nếu có sự không khớp, điều đó có thể cho thấy lỗi nhập dữ liệu.

Bạn có thể sử dụng hàm IF để xác thực Số tiền bán hàng.

Công thức:

=IF(G2:G71=E2:E71*F2:F71, "OK", "Error")

Công thức này xác minh xem “Số tiền bán hàng” là đúng hay sai. Sau đó trả về “Lỗi” nếu số tiền không khớp.

Đầu ra:

Xử lý dữ liệu bị thiếu:

Nếu thiếu một số giá trị Giá bán lẻ thì các công thức phụ thuộc vào các giá trị này có thể dẫn đến lỗi. Bạn có thể sử dụng IF với ISBLANK để cung cấp giá trị dự phòng.

Công thức:

=IF(ISBLANK(E2:E71), "Retail Price Missing", E2:E71*F2:F71)

Công thức này tính toán "Số tiền bán hàng" và hiển thị "Thiếu giá" nếu giá bán lẻ không được cung cấp.

Đầu ra:

6. Sử dụng Công thức mảng với Kiểm soát lỗi nâng cao

Công thức mảng có thể thực hiện nhiều phép tính trong một ô duy nhất và với khả năng xử lý lỗi, chúng giúp nắm bắt các vấn đề trong các phép tính phức tạp. Hãy cùng tìm hiểu Giá bán lẻ trung bình trong khi bỏ qua lỗi.

Công thức:

Cột “Giá bán lẻ” có các ô trống có giá trị null nhưng hàm AVERAGE sẽ tính giá bán lẻ trung bình.

Đầu ra:

7. Sử dụng Xử lý lỗi cho sổ làm việc được liên kết

Khi liên kết nhiều sổ làm việc, lỗi có thể lan truyền khắp các tệp nếu dữ liệu nguồn thay đổi. Bạn có thể sử dụng IFERROR xung quanh các liên kết.

Công thức:

=IFERROR([Workbook2.xlsx]Sheet1!A1, "Link Error")

Thường xuyên cập nhật các liên kết trong Dữ liệu tab>> Chỉnh sửa liên kết để tránh tham chiếu bị hỏng.

8. Theo dõi lỗi bằng công cụ kiểm tra công thức

Các công cụ kiểm tra công thức của Excel giúp truy tìm lỗi về các ô nguồn, giúp xác định chuỗi lỗi phức tạp dễ dàng hơn. Nếu tập dữ liệu trở nên lớn hoặc phức tạp hơn thì có thể khó xảy ra lỗi theo dõi.

• Đi tới Công thức tab>> Chọn Kiểm tra công thức phần.
• Từ Kiểm tra công thức công cụ:
  • Bạn có thể sử dụng Tiền lệ theo dõi và Theo dõi người phụ thuộc để hình dung các mối quan hệ.
  • Bạn có thể sử dụng Công thức đánh giá cho phép bạn thực hiện từng bước trong công thức để hiểu lỗi phát sinh ở đâu.
  • Bạn có thể sử dụng Kiểm tra lỗi tùy chọn để kiểm tra hoặc theo dõi lỗi.

Các phương pháp xử lý lỗi tốt nhất

• Chia các công thức phức tạp thành các phần nhỏ hơn bằng cách sử dụng các cột trợ giúp để tìm lỗi.
• Thông báo nhất quán trong các công thức giúp khắc phục sự cố dễ dàng hơn.
• Kiểm tra việc xử lý lỗi của bạn bằng cách nhập dữ liệu không hợp lệ hoặc cố ý phá vỡ các phần phụ thuộc.

Kết luận

Xử lý lỗi nâng cao là điều cần thiết để xây dựng bảng tính đáng tin cậy và thân thiện với người dùng. Bằng cách sử dụng các hàm Excel tích hợp, công cụ kiểm tra công thức và xác thực dữ liệu, bạn có thể đảm bảo các bảng tính phức tạp của mình linh hoạt hơn và dễ bảo trì hơn. Các ví dụ trên sẽ giúp bạn sử dụng các hàm phù hợp để xử lý lỗi nâng cao. Bằng cách làm theo các chiến lược này, bạn có thể tiết kiệm thời gian, giảm bớt sự thất vọng và giúp quá trình xử lý dữ liệu của bạn mượt mà và chính xác hơn.