Microsoft Excel là một ứng dụng bảng tính mạnh mẽ và linh hoạt, rất phù hợp để theo dõi và quản lý mọi thứ từ hàng tồn kho của doanh nghiệp, đến ngân sách doanh nghiệp nhỏ, đến thể lực cá nhân. Một trong những lợi ích của Excel là bạn có thể thiết lập các công thức trước thời hạn sẽ tự động cập nhật khi bạn nhập dữ liệu mới. Thật không may, một số công thức không thể về mặt toán học nếu không có dữ liệu cần thiết, dẫn đến các lỗi trong bảng của bạn, chẳng hạn như # DIV / 0!, #VALUE!, #REF!, Và #NAME?. Mặc dù không nhất thiết có hại, những lỗi này sẽ được hiển thị trong bảng tính của bạn cho đến khi được sửa hoặc cho đến khi dữ liệu được yêu cầu được nhập, điều này có thể làm cho bảng tổng thể kém hấp dẫn và khó hiểu hơn. Rất may, ít nhất là trong trường hợp thiếu dữ liệu, bạn có thể ẩn các lỗi Excel với một số trợ giúp từ các hàm IF và ISERROR. Đây là cách để làm điều đó.
Chúng tôi đang sử dụng bảng tính theo dõi giảm cân nhỏ làm ví dụ về loại bảng sẽ gây ra lỗi tính toán (tính toán tỷ lệ phần trăm giảm cân) trong khi chờ dữ liệu mới (cân tiếp theo).
Bảng tính mẫu của chúng tôi chờ đầu vào trong cột Trọng lượng và sau đó tự động cập nhật tất cả các cột khác dựa trên dữ liệu mới. Vấn đề là cột Phần trăm bị mất phụ thuộc vào một giá trị, Thay đổi, chưa được cập nhật trong những tuần mà trọng lượng chưa được nhập, dẫn đến # DIV / 0! lỗi, chỉ ra rằng công thức đang cố chia cho số không. Chúng tôi có thể giải quyết lỗi này theo ba cách:
- Chúng tôi có thể loại bỏ công thức từ các tuần không có trọng lượng nào được nhập, và sau đó thêm lại thủ công mỗi tuần. Điều này sẽ hoạt động trong ví dụ của chúng tôi vì bảng tính tương đối nhỏ, nhưng sẽ không lý tưởng trong các bảng tính lớn hơn và phức tạp hơn.
- Chúng ta có thể tính phần trăm bị mất bằng cách sử dụng một công thức khác không chia cho số không. Một lần nữa, điều này có thể xảy ra trong ví dụ của chúng tôi, nhưng có thể không phải lúc nào cũng phụ thuộc vào bảng tính và tập dữ liệu.
- Chúng ta có thể sử dụng hàm ISERROR, khi được kết hợp với câu lệnh IF cho phép chúng ta xác định một giá trị thay thế hoặc phép tính nếu kết quả ban đầu trả về lỗi. Đây là giải pháp chúng tôi sẽ chỉ cho bạn hôm nay.
Hàm ISERROR
Chính nó, ISERROR kiểm tra ô hoặc công thức được chỉ định và trả về giá trị xác thực, nếu kết quả tính toán hoặc giá trị của ô là lỗi và sai sai nếu không đúng. Bạn có thể sử dụng ISERROR đơn giản bằng cách nhập phép tính hoặc ô trong ngoặc đơn theo hàm. Ví dụ:
ISERROR ((B5-B4) / C5)
Nếu phép tính (B5-B4) / C5 trả về lỗi, thì ISERROR sẽ trả về Hồi giáo đúng khi kết hợp với một công thức có điều kiện. Mặc dù điều này có thể được sử dụng theo nhiều cách khác nhau, vai trò hữu ích nhất của nó là khi được kết hợp với chức năng IF.
Hàm IF
Hàm IF được sử dụng bằng cách đặt ba kiểm tra hoặc giá trị trong ngoặc đơn được phân tách bằng dấu phẩy: IF (giá trị được kiểm tra, giá trị nếu đúng, giá trị nếu sai). Ví dụ:
NẾU (B5> 100, 0, B5)
Trong ví dụ trên, nếu giá trị trong ô B5 lớn hơn 100 (có nghĩa là kiểm tra là đúng), thì số 0 sẽ được hiển thị dưới dạng giá trị ô. Nhưng nếu B5 nhỏ hơn hoặc bằng 100 (có nghĩa là thử nghiệm là sai), giá trị thực của B5 sẽ được hiển thị.
Kết hợp IF và ISERROR
Cách chúng ta kết hợp các hàm IF và ISERROR là bằng cách sử dụng ISERROR làm thử nghiệm cho câu lệnh IF. Hãy lấy bảng tính giảm cân của chúng tôi làm ví dụ. Lý do ô E6 trả về # DIV / 0! lỗi là do công thức của nó đang cố gắng chia tổng trọng lượng đã giảm cho trọng lượng của tuần trước, điều này chưa có sẵn cho tất cả các tuần và hoạt động hiệu quả như cố gắng chia cho số không.
Nhưng nếu chúng tôi sử dụng kết hợp IF và ISERROR, chúng tôi có thể yêu cầu Excel bỏ qua các lỗi và chỉ cần nhập 0% (hoặc bất kỳ giá trị nào chúng tôi muốn) hoặc chỉ cần hoàn thành phép tính nếu không có lỗi. Trong ví dụ của chúng tôi, điều này có thể được thực hiện với công thức sau:
NẾU (ISERROR (D6 / B5), 0, (D6 / D5))
Với chức năng đó, bạn có thể sao chép nó vào bất kỳ ô nào còn lại và mọi lỗi sẽ được thay thế bằng số không. Tuy nhiên, khi bạn nhập dữ liệu mới trong tương lai, các ô bị ảnh hưởng sẽ tự động cập nhật thành giá trị chính xác của chúng vì điều kiện lỗi sẽ không còn đúng nữa.
Hãy nhớ rằng khi cố gắng che giấu các lỗi Excel, bạn có thể sử dụng bất kỳ giá trị hoặc công thức nào cho cả ba biến trong câu lệnh IF; nó không phải là một số 0 hoặc toàn bộ như trong ví dụ của chúng tôi. Các lựa chọn thay thế bao gồm tham chiếu một công thức hoàn toàn riêng biệt hoặc chèn một khoảng trắng bằng cách sử dụng hai dấu ngoặc kép (giá đỡ) làm giá trị thực sự của bạn. Để minh họa, công thức sau đây sẽ hiển thị một khoảng trắng trong trường hợp xảy ra lỗi thay vì 0:
NẾU (ISERROR (D6 / B5), "", (D6 / D5))
Chỉ cần nhớ rằng các câu lệnh IF có thể nhanh chóng trở nên dài dòng và phức tạp, đặc biệt là khi được ghép nối với ISERROR và thật dễ dàng để đặt sai dấu ngoặc đơn hoặc dấu phẩy trong các tình huống như vậy. Các phiên bản gần đây của công thức mã màu Excel khi bạn nhập chúng để giúp bạn theo dõi các giá trị ô và dấu ngoặc đơn.
