Đây là cách tự động phát hiện những lỗi này trong Excel
Do có nhiều tùy chọn tính toán, các lỗi điển hình trong các hàm Excel xảy ra lặp đi lặp lại với bảng tính. Không thành vấn đề - trong hầu hết các trường hợp, chúng có thể được sửa chữa chỉ bằng một vài cú nhấp chuột và bạn có thể tiếp tục sử dụng bảng. Ở đây bạn sẽ tìm thấy tổng quan về các lỗi phổ biến nhất và các mẹo thực tế về cách khắc phục chúng. Bạn cũng sẽ học được các chiến lược tránh né có thể có.
Đây là 10 lỗi thường gặp trong các hàm Excel
Sau đây là mười mã lỗi phổ biến nhất cho các sự cố Excel có thể xảy ra và các giải pháp.
1. #NAME?
Lỗi nhập xảy ra - nếu đây là trường hợp trong công thức, thông báo lỗi #NAME? Sẽ xuất hiện thay vì kết quả. Không có ích gì khi ẩn lỗi với một hàm như IFERROR. Bạn phải sửa nó. Tất cả những gì bạn phải làm là xem kỹ tên mà bạn đã đặt cho công thức. Có một chữ cái xoắn? Bạn đã quên một chữ cái hoặc nhập nó hai lần? Ví dụ, sửa = SUME (A3: A16) thành = SUM (A3: A16) và bạn sẽ nhận được kết quả chính xác.
Sử dụng trình hướng dẫn công thức
Bạn có thể tránh những loại lỗi chính tả này bằng cách sử dụng Công thức hỗ trợ. Nó hoạt động như thế này:
Khi bạn bắt đầu nhập tên công thức, menu thả xuống sẽ mở ra với các tên khớp với giá trị bạn đã nhập.
Nếu bạn nhập tên và dấu ngoặc mở, văn bản di chuột sẽ viết đúng chính tả.
Trình hướng dẫn hàm sẽ trợ giúp khi nhập hàm. Nếu bạn đánh dấu ô có công thức và chọn "Chèn hàm" trong tab "Công thức", Excel sẽ gọi trình hướng dẫn. Các đối số riêng lẻ được hiển thị ở đây - và cả liệu có lỗi hay không.
2. #NULL!
Thông báo lỗi này có thể chỉ ra hai điều:
- Bạn đã chỉ định một toán tử phạm vi không chính xác trong một công thức.
- Bạn sử dụng toán tử giao nhau trên hai khu vực không chồng chéo.
Trong trường hợp đầu tiên, bạn đang đề cập đến một dải ô liên tục trong một công thức - ví dụ: ô B4 đến B12. Bạn sử dụng dấu hai chấm làm toán tử phạm vi. Ví dụ, nếu bạn muốn tính tổng, công thức đúng là = SUM (B4: B12). Nếu bạn đang đề cập đến hai khu vực mà không chồng chéo, dấu chấm phẩy là toán tử thích hợp. Nếu bạn muốn tính tổng từ các vùng B4 đến B12 và C9 đến C23, công thức này đúng: = SUM (B4: B12; C9: C23).
Trong trường hợp thứ hai, bạn muốn làm việc với các dải ô chồng chéo, nhưng hai dải ô được chỉ định không trùng nhau. Ví dụ: bạn gặp lỗi cho công thức = CELL ("Địa chỉ" (B4: B12 D4: D6)). Không có sự chồng chéo giữa hai khu vực. Nếu bạn thay đổi các khu vực để chúng chồng lên nhau - ví dụ như = CELL ("Địa chỉ" (B4: B12 B5: D5)) - kết quả là điểm giao nhau của cả hai khu vực được hiển thị. Trong trường hợp này, nó là ô B5.
3. # THAM KHẢO!
Thông báo lỗi #REZUG! chỉ ra rằng một khu vực bạn đang đề cập đến không tồn tại. Chương trình không thể tham chiếu đến phạm vi được chỉ định cho phép tính. Điều này xảy ra, ví dụ, khi một trang tính, hàng, cột hoặc ô mà bạn tham chiếu đến trong công thức bị xóa.
Ví dụ: bạn có một bảng tính Excel với các hàng 1, 2 và 3 và các cột A, B và C. Công thức là = SUM (A2; B2; C2). Bây giờ xóa dòng 2. Thay vì kết quả, sau đó bạn sẽ thấy giá trị lỗi #REFER, vì một trong các giá trị cho phép tính không tồn tại.
Nếu bạn đã vô tình xóa phần bị thiếu cho phép tính, bạn có thể sửa lỗi của mình trực tiếp bằng lệnh "Hoàn tác". Nếu vùng phụ đã bị xóa đúng cách, hãy định dạng lại công thức. Nếu bạn nhập = SUM (A2: C2), bạn sẽ được hiển thị kết quả chính xác vì Excel chỉ cần loại bỏ cột thứ hai đã bị xóa.
4. #VALUE!
Thông báo lỗi này có thể chỉ ra nhiều sự cố Excel khác nhau. Một trong các giá trị trong bảng không khớp với thông tin trong công thức nên chương trình không thực hiện được phép tính. Đây là trường hợp, ví dụ, nếu một trong các ô được sử dụng cho phép tính có chứa một từ thay vì một số.
Vì có nhiều nguồn lỗi tiềm ẩn cho #VALUE! có nhiều cách để khắc phục nó. Các giải pháp sau có thể giúp:
- Bạn có thể sử dụng các hàm thay vì các toán tử toán học. Vì vậy, thay vì = B4 + B5 + B6, hãy nhập hàm = SUM (B4: B6).
- Kiểm tra các ô được đề cập để tìm các ký tự đặc biệt. Nếu cần, hãy sử dụng hàm ISTTEXT trong một cột riêng cho việc này. Điều này cho bạn biết lỗi nằm ở ô nào.
- Có lẽ giá trị lỗi là do khoảng trắng trong ô xuất hiện trong hàm. Để tìm hiểu, hãy đánh dấu các ô thích hợp. Sau đó đi tới "Tìm kiếm và Chọn" trong "Bắt đầu" và nhấp vào "Thay thế". Nhập khoảng trắng cho "Tìm kiếm" và không nhập gì cho "Thay thế bằng". Bây giờ bạn có thể thay thế các khoảng trắng không mong muốn.
- Các ký tự ẩn cũng có thể dẫn đến lỗi phổ biến này trong các hàm Excel. Để loại bỏ những điều này, hãy chuyển đến "Bắt đầu" và "Sắp xếp và Lọc" trên "Bộ lọc". Dưới mũi tên bộ lọc, hủy kích hoạt "Chọn tất cả" và đánh dấu vào "Các ô trống" và tất cả các vị trí không có gì. Nếu bạn nhấn OK, Excel sẽ hiển thị cho bạn tất cả các ô trống được cho là có chứa các ký hiệu ẩn. Đánh dấu nó và nhấn "Xóa". Xóa bộ lọc một lần nữa.
5. #####
Khi toàn bộ ô Excel xuất hiện đầy kim cương, nó trông tệ hơn thoạt nhìn. Điều này chỉ có nghĩa là cột Excel quá hẹp để hiển thị toàn bộ nội dung ô. Đơn giản chỉ cần kéo cột rộng hơn bằng chuột hoặc chọn kích thước phông chữ nhỏ hơn để toàn bộ nội dung có thể được hiển thị.
6. # DIV / 0!
Ví dụ: giả sử bạn nhập một hàm mà bạn muốn chia nhiều giá trị cho các giá trị khác. Sau đó, có thể xảy ra trường hợp có số 0 ở một trong các ô có giá trị mà bạn muốn chia - hoặc không có gì cả. Trong trường hợp này, giá trị lỗi # DIV / 0! Xuất hiện để chỉ ra rằng không được phép chia cho 0.
Giải pháp: Đảm bảo rằng không có số 0 hoặc không có gì trong các ô Excel tương ứng hoặc thay đổi tham chiếu. Ngoài ra, bạn có thể chặn hiển thị lỗi - cụ thể là khi bạn vẫn đang đợi các giá trị mà bạn muốn chèn vào các ô tương ứng. Đối với điều này, bạn có thể sử dụng công thức IFERROR, ví dụ, được trình bày chi tiết hơn bên dưới.
7. #NV!
Với giá trị lỗi này, Excel chỉ ra rằng không thể tìm thấy những gì bạn đang tìm kiếm. Ví dụ: nếu bạn đã gán giá trị cho các điều khoản nhất định và muốn hiển thị chúng, nhưng thiếu một trong các điều khoản, thông báo lỗi này sẽ xuất hiện thay vì kết quả. Điều này thường được thực hiện cùng với các công thức LOOKUP, HLOOKUP, MATCH hoặc VLOOKUP. Ví dụ: bạn có thể hiển thị lỗi với hàm VLOOKUP vì nó giúp bạn so sánh các bảng với nhau hoặc để tóm tắt chúng.
Ví dụ: Bạn đã tạo một bảng với các giá tương ứng cho vít, móc, ghim, bu lông, đai ốc, v.v. và quên một trong các điều khoản. Chương trình không tìm thấy anh ta. Theo đó, không thể có giá, chỉ có #NV! biểu thị. Bạn giải quyết vấn đề này bằng cách thêm thuật ngữ. Ngoài ra, bạn có thể chặn hiển thị lỗi bằng công thức IFERROR (xem bên dưới).
8. #NUMBER
Nếu hàm chứa giá trị số không hợp lệ, bạn sẽ nhận được lỗi #NUMBER. Điều này có thể xảy ra, ví dụ, khi bạn viết các số như 1000 với dấu chấm sau dấu chấm. Nhập các số mà không cần bất kỳ định dạng nào. Có lẽ bạn cũng đang lấy căn bậc hai của một số âm ở đâu đó hoặc thực hiện các phép tính khác không hoạt động theo toán học?
Một tình huống khác có thể dẫn đến giá trị lỗi #NUM là việc sử dụng các hàm lặp lại chẳng hạn như lãi suất. Để nhận được kết quả trong trường hợp này, chỉ cần thay đổi số lần lặp mà Excel cho phép tính toán - nghĩa là việc sử dụng lặp lại cùng một phương pháp tính toán. Điều này là cần thiết khi tính tiền lãi, chẳng hạn, nếu ba phần trăm phải được cộng đi cộng lại nhiều lần.
Làm thế nào để làm nó:
-
Trong "Tệp" và "Tùy chọn", hãy chọn "Công thức". Tại đây, trong "Tùy chọn tính toán", hãy chọn hộp bên cạnh "Kích hoạt tính toán lặp lại".
-
Trong "Số lần lặp tối đa", bạn có thể chỉ định số lượng phép tính mà Excel sẽ thực hiện. Con số này càng cao thì thời gian tính toán càng lâu.
-
Trong trường "Thay đổi tối đa", bạn có thể chỉ định số tiền thay đổi có thể là bao nhiêu giữa hai kết quả hóa đơn.
Trong một số trường hợp hiếm hoi, cũng có thể xảy ra trường hợp kết quả của một công thức quá lớn hoặc quá nhỏ đối với một phép tính trong Excel. Tuy nhiên, đối với điều này, kết quả phải dưới -1x10 (cao) 307 hoặc trên 1x10 (cao) 307. Trong trường hợp này, bạn phải chỉnh sửa các công thức để kết quả nằm trong một phạm vi mà Excel có thể tính toán.
9. Tham chiếu vòng
Thông báo lỗi Thông tư tham chiếu? Sau đó, bạn đã nhập một công thức tham chiếu trực tiếp hoặc gián tiếp đến ô chứa công thức đó. Lỗi tham chiếu vòng tròn chỉ ra rằng công thức không thể tự tính toán và do đó không thể có kết quả. Nếu các hàm tham chiếu đến nhau, nó là một tham chiếu gián tiếp, không chính xác.
Cách giải quyết lỗi:
-
Nếu có một tham chiếu vòng tròn, thông báo lỗi sẽ xuất hiện tại điểm tương ứng. Trong trường hợp này, bạn có thể thay đổi ô trực tiếp. Không thụt lề ô hiện tại khi bạn nhập công thức.
-
Nếu bạn muốn tìm các tham chiếu vòng chưa được khám phá, hãy chuyển đến "Công thức", "Giám sát công thức" và "Tham chiếu vòng". Tất cả các tham chiếu vòng tròn trong tài liệu hiện được hiển thị và bạn có thể giải quyết chúng như trong trường hợp đầu tiên.
10. E +
E + không phải là một lỗi điển hình trong các hàm Excel, nhưng thỉnh thoảng nó vẫn tạo ra sự nhầm lẫn cho người dùng. Nếu bạn nhập một số rất lớn vào một trong các ô, Excel có thể viết tắt nó. Sau đó, E + phát huy tác dụng. Ví dụ: 265000000000000 trong ô Excel trở thành 2,65E + 14. Nó là một định dạng hàm mũ. Excel sử dụng nó để có thể hiển thị các số lớn chẵn sao cho chúng hoàn toàn có thể nhìn thấy trong các ô.
Đây là cách bạn có thể hiển thị đầy đủ các con số
Nếu bạn muốn các số được hiển thị đầy đủ, cần thực hiện một số bước đơn giản:
-
Đánh dấu các vị trí liên quan trong danh sách của bạn.
-
Nhấn Ctrl và 1 để có thể định dạng các ô.
-
Trong hộp thoại mở ra, chọn tab "Số" và danh mục "Số".
-
Nhập số lớn và chọn số lượng chữ số thập phân bạn muốn hiển thị và liệu bạn có muốn hiển thị dấu phân cách nghìn hay không.
-
Sau khi bạn bấm OK, Excel sẽ hiển thị cho bạn đầy đủ số trong bảng.
Tự động phát hiện các lỗi điển hình trong các hàm Excel
Bạn có tùy chọn để kích hoạt kiểm tra Excel tự động. Nếu nó chưa được bật cho bạn, một vài cú nhấp chuột là đủ:
Trong "Tệp", chuyển đến "Tùy chọn".
Chọn "Công thức".
Chọn hộp bên cạnh "Bật kiểm tra lỗi nền".
Nếu bạn bấm vào một trong các ô trong sổ làm việc có thông báo lỗi, một dấu chấm than nhỏ sẽ xuất hiện bên cạnh nó. Nếu bạn chọn nó, một danh sách sẽ xuất hiện. Nó giải thích đó là thông báo lỗi nào và đưa ra các tùy chọn giải pháp, các bước tính toán hoặc trợ giúp thêm về vấn đề. Ngoài ra, lỗi cũng có thể được bỏ qua.
Các hàm này xác định lỗi trong các hàm Excel
Bạn có thể sử dụng các hàm này để kiểm tra nhanh các lỗi điển hình trong các hàm Excel.
IFERROR
Một số công thức có thể không hoàn chỉnh khi mọi thứ vẫn tồn tại. Bạn có muốn ngăn lỗi hiển thị cho bạn không? Hàm IFERROR phù hợp cho việc này. Nó đánh giá các loại lỗi #NV, #VALUE !, #REFERENCE !, # DIV / 0 !, #NUMBER !, #NAME? hoặc #NULL! kết thúc. Nó có cấu trúc đơn giản vì nó chỉ chứa giá trị và value_if_error. Giá trị đầu tiên kiểm tra tính đúng đắn của hàm. Chương trình sẽ hiển thị giá trị cuối cùng trong kết quả nếu có lỗi. Chức năng này rất hữu ích khi bạn chỉnh sửa một bảng trong thời gian dài.
Một ví dụ:
Bạn nhập = IFERROR (B3 * C3; "Nhập giá trị") hoặc = IFERROR (B3 * C3; ""). Trong trường hợp đầu tiên, bạn sẽ được nhắc nhở về những gì bạn vẫn phải làm (nếu có lỗi) với dòng chữ "Thêm giá trị". Trong trường hợp thứ hai, ô thường chứa kết quả chỉ đơn giản là trống.
Chú ý: Khi nhập văn bản vào công thức, bạn phải luôn đặt nó trong dấu ngoặc kép. Nếu không nó sẽ không hoạt động. Bởi vì không có dấu ngoặc kép, Excel sẽ hiểu các từ như một phần của công thức.
BÁC SĨ
Cùng với hàm IFERROR, hàm ISERROR cũng có thể chỉ ra các lỗi hiện có. Bằng cách này, chúng không cản trở bất kỳ tính toán nào nữa trong danh sách. Để thực hiện việc này, hãy nhập, ví dụ: = IF (ISERROR (B3); "vui lòng kiểm tra"; B3 / 6). Nếu phép tính có thể được tiến hành, bạn sẽ nhận được kết quả. Nếu Excel phát hiện lỗi trong khi kiểm tra B3, các từ "vui lòng kiểm tra" sẽ xuất hiện trong ô tương ứng.
Kết luận: Sửa lỗi trong các hàm Excel một cách nhanh chóng và dễ dàng
Cho dù đó là một chữ cái bị đảo ngược, một công thức không thể tính toán bằng toán học, một tham chiếu sai hoặc một dấu cách vì một giá trị vẫn bị thiếu - những sai lầm vẫn xảy ra trong công việc hàng ngày. Excel cũng biết điều đó. Tính năng tự động kiểm tra chỉ ra các lỗi điển hình trong các hàm Excel và giải thích tất cả những lỗi đó. Ngoài ra còn có các tính năng kiểm tra hoặc ẩn lỗi để bạn có thể chuyển sang phần còn lại của bảng tính.