Macro IFERROR là một hàm hữu ích để xử lý các loại lỗi khác nhau trong Excel.
Excel có một hàm xử lý lỗi đa năng được gọi là IFERROR.
Nếu bạn tạo một công thức và sau đó nhận thấy nó cần phải xử lý lỗi, bạn cần chèn hàm IFERROR vào đầu công thức của mình.
Hãy xem lại công thức đơn giản này.
=A1/B1
Nếu ô B1 chứa 0, thì công thức trên sẽ trả về giá trị lỗi #DIV/0!.
Hàm IFERROR có thể bao quanh công thức trên để xử lý giá trị 0 trong B1.
=IFERROR(A1/B1,0)
IFERROR có hai đối số hoặc các phần. Đối số đầu tiên là phép tính cần thực hiện và đối số thứ hai là giá trị trả về nếu phép tính trả về lỗi. Nếu phép tính không trả về lỗi, thì kết quả của nó sẽ được hiển thị. Nếu phép tính trả về lỗi, đối số thứ hai sẽ được hiển thị.
IFERROR xử lý gần như tất cả các lỗi công thức của Excel. Nếu A1 có lỗi #VALUE, hàm IFERROR ở trên sẽ vẫn hiển thị 0. Ở một mức độ nào đó, hàm IFERROR có thể che dấu hoặc ẩn các lỗi vì nó xử lý tất cả các lỗi theo cùng một cách.
Trong các phiên bản Excel cũ hơn, IFERROR xử lý tất cả các lỗi. Trong phiên bản đăng ký của Excel, IFERROR không xử lý lỗi #SPILL, liên quan đến dynamic array.
Việc chèn hàm IFERROR vào các công thức hiện có có thể tốn nhiều thời gian và lặp đi lặp lại, đây là hai kích lý do chính của tôi để xem xét tạo một giải pháp macro.
Điều quan trọng cần nhớ là macro xóa undo list, có nghĩa là không thể hoàn tác chúng. Điều này cũng có nghĩa là bạn không thể hoàn tác bất kỳ điều gì bạn đã làm trước khi chạy macro. Lưu tệp của bạn trước khi sử dụng macro mới, sau đó đóng tệp mà không lưu nếu sự cố xảy ra. Ngoài ra, hãy kiểm tra macro trên bản sao của tệp.
AddIFERROR macro
Hình 1 có macro bổ sung hàm IFERROR vào công thức hiện có.
Macro này hoạt động trên các công thức hiện đang hiển thị lỗi. Văn bản màu xanh lá cây trong Hình 1 là văn bản giải thích, còn được gọi là “comments”. Chúng giải thích và mô tả mã, nhưng không thực hiện bất kỳ hành động nào.
Các số mục và giải thích theo sau đề cập đến các số màu xanh và trắng ở bên trái của Hình 1.
Hình 1.
01. Hai biến được xác định. Biến c sẽ được sử dụng để tham chiếu đến từng ô trong vùng đã chọn. Biến strFormula sẽ được sử dụng để nắm bắt văn bản của công thức hiện có.
02. Macro này bao gồm một lệnh xử lý lỗi cơ bản. Lệnh này bật xử lý lỗi. Khi gặp lỗi, mã được chuyển hướng đến nhãn ErrorHandler ở cuối mã (mục 11). Macro này có thể tạo ra lỗi nếu trang tính được bảo vệ và các ô bị khóa.
03. Khối mã này tắt cập nhật màn hình và dừng tính toán. Các cài đặt này tăng tốc macro trong các tệp lớn hơn hoặc khi làm việc với phạm vi lớn. Chúng tôi bật lại các cài đặt này trong mục 9.
04. Chức năng TypeName cho phép bạn xác định những gì người dùng đã chọn trước khi chạy macro. Nếu một biểu đồ được chọn, mã tiếp theo sẽ tạo ra lỗi. Chúng tôi có thể dừng macro tại thời điểm này và hiển thị thông báo cảnh báo được hiển thị tại mục 8.
05. “For Each c in Selection” là một lệnh mạnh mẽ. Nó hướng dẫn Excel lặp qua mọi ô trong phạm vi đã chọn của người dùng. Điều này làm cho macro trở nên linh hoạt, vì người dùng có thể chọn các phạm vi có kích thước khác nhau để thêm IFERROR vào.
06. “Câu lệnh If” này sẽ kiểm tra ô để xem ô đó có công thức hay không. Tất cả các công thức Excel đều bắt đầu bằng dấu =. Hàm Left xem xét ký tự đầu tiên của công thức để xác nhận nó có dấu =. Nếu ô không bắt đầu bằng =, thì không có gì được thực hiện với ô đó. Mọi giá trị, ngày tháng và mục nhập văn bản đều bị bỏ qua.
07. Biến strFormula nắm bắt văn bản công thức mà không có dấu =. Chức năng Right trích xuất văn bản từ bên phải.
Hàm Len là viết tắt của “length”, và nó đếm có bao nhiêu ký tự trong công thức.
Bằng cách trừ đi 1 từ kết quả Len, chúng ta bỏ qua dấu = khỏi công thức khi trích xuất văn bản từ bên phải.
Sau đó, chúng tôi xây dựng công thức IFERROR bằng cách có =IFERROR (ở đầu, theo sau là công thức ban đầu và sau đó ,0) ở cuối. Biểu tượng & được sử dụng để nối các văn bản lại với nhau.
08. Lệnh này hiển thị một thông báo pop-up cho người dùng rằng họ cần chọn một phạm vi trước khi chạy macro. Người dùng phải nhấp vào OK, nếu không hộp thông báo vẫn ở trên màn hình. Lưu ý rằng ký tự gạch dưới ở cuối dòng đầu tiên cho phép bạn bọc một dòng mã dài trên hai dòng.
09. HandleExit: là nhãn. Một nhãn, là văn bản theo sau bởi dấu hai chấm, đánh dấu một vị trí trong mã nơi bạn có thể hướng mã di chuyển bằng cách sử dụng lệnh GoTo hoặc Resume. Đây là phần xử lý việc đóng macro. Trước tiên, chúng tôi đưa Excel về cài đặt tiêu chuẩn để cập nhật màn hình và tính toán.
10. Biến c bị xóa và macro bị dừng.
11. HandleError: là một nhãn. Các hành động trong phần này chỉ được thực hiện nếu gặp lỗi. Một hộp thông báo pop-up được hiển thị. Người dùng phải trả lời bằng cách nhấp vào OK. Sau đó, macro được chuyển hướng trở lại nhãn HandleExit ở trên để đóng macro.
Sử dụng Macro
Đây là một macro linh hoạt có thể được sử dụng trên bất kỳ tệp nào.
Nơi tốt nhất để lưu loại macro mục đích chung này là trong một tệp được gọi là Personal Macro Workbook. Tệp có tên PERSONAL.xlsb và được lưu trong thư mục XLSTART trên hệ thống của bạn. Nó sẽ mở mỗi khi bạn mở Excel. Khi bạn ghi macro, nó là một trong những tùy chọn lưu.
Nguồn: intheblack
Xem thêm
Excel trong ngành tài chính: Kỹ năng lập bảng tính cho FP&A và hơn thế nữa