Cách phát hiện lỗi trong các công thức phức tạp bằng công cụ gỡ lỗi thông minh của Excel

Làm việc với bảng tính đầy công thức có thể rất tuyệt vời… cho đến khi có sự cố xảy ra và một vấn đề bí ẩn xuất hiện. Một lỗi trong Excel mà bạn không biết nguyên nhân từ đâu. và nhận ra Các lỗi thường gặp nhất trong công thức ExcelKhi công thức trở nên dài và phức tạp hơn, việc phát hiện nguồn gốc vấn đề bằng mắt thường gần như là không thể, và rất nhiều thời gian bị lãng phí vào việc kiểm tra từng ô một.

Với công cụ gỡ lỗi thông minh và các công cụ kiểm tra lỗi mới, Excel đang cung cấp ngày càng nhiều hỗ trợ hơn cho người dùng. Xác định, hiểu và sửa lỗi trong các công thức phức tạp.Tuy nhiên, để tận dụng tối đa chúng, điều cần thiết là phải nắm vững một vài quy tắc cơ bản khi viết công thức, hiểu ý nghĩa của các thông báo lỗi khác nhau và biết cách tự động hóa việc sửa lỗi hàng loạt, đặc biệt là khi dữ liệu được nhập từ các chương trình khác và chúng có nhiều hình dạng khác thường.

Các khái niệm cơ bản để đảm bảo công thức không bị lỗi ngay từ đầu.

Trước khi đi sâu vào các công cụ thông minh và trình gỡ lỗi nâng cao, điều cần thiết là phải hiểu một số quy tắc cơ bản giúp ngăn ngừa hầu hết các lỗi. Excel khá khắt khe về cú pháp, và bất kỳ chi tiết nhỏ nào cũng có thể gây ra vấn đề. Công thức được hiểu là văn bản hoặc hoàn toàn không hoạt động..

Điều đầu tiên và quan trọng nhất là Mọi công thức đều phải bắt đầu bằng dấu bằng (=)Nếu bỏ qua dấu bằng, Excel sẽ không hiểu rằng bạn muốn thực hiện phép tính, mà thay vào đó là bạn đang nhập văn bản hoặc ngày tháng. Ví dụ, nếu bạn nhập SUM(A1:A10) mà không có dấu bằng, ô sẽ hiển thị chính xác SUM(A1:A10) và sẽ không thực hiện bất kỳ phép tính nào. Điều tương tự xảy ra với ngày tháng: nếu bạn nhập 11/2 và định dạng ô là Chung, Excel có thể hiển thị 2-nov (ngày 2 tháng 11) thay vì chia 11 cho 2, vì nó hiểu rằng bạn đang nhập ngày tháng chứ không phải phép tính. Điều này có liên quan đến Lỗi ngày tháng kỳ lạ của Excel.

Một điểm quan trọng khác là việc sử dụng dấu ngoặc đơn chính xác. Mỗi hàm sử dụng chúng đều phải có dấu ngoặc đơn mở và đóng được đặt đúng chỗKhi làm việc với các chức năng lồng nhauRất thường xảy ra trường hợp thừa hoặc thiếu một dấu ngoặc đơn, dẫn đến lỗi cú pháp. Hãy tưởng tượng một công thức như =IF(B5<0,"Không hợp lệ",B5*1,05). Nếu bạn vô tình viết =IF(B5<0,"Không hợp lệ",B5*1,05)) với một dấu ngoặc đơn đóng thừa, công thức sẽ ngừng hoạt động vì số lượng dấu ngoặc đơn không khớp. Luôn kiểm tra xem số lượng dấu ngoặc đơn mở và đóng có bằng nhau và chúng có ở đúng vị trí hay không là điều cần thiết để tránh những lỗi khó tìm.

Việc xử lý phạm vi một cách chính xác cũng rất quan trọng. Để tham chiếu đến nhiều ô liên tiếp, bạn phải sử dụng dấu hai chấm (:) giữa ô đầu tiên và ô cuối cùngVí dụ, phạm vi được viết là A1:A5. Nếu bạn bỏ dấu hai chấm và viết như =SUM(A1 A5), Excel sẽ hiểu đây là các tham chiếu không hợp lệ giữa các ô không liên quan và sẽ trả về lỗi. #VÔ GIÁ TRỊ!, đây là lỗi xuất hiện khi có các giao điểm không tồn tại hoặc các tham chiếu không nhất quán.

Cuối cùng, số lượng và thứ tự các đối số của mỗi hàm phải được tuân thủ. Một số hàm yêu cầu các lập luận bắt buộc và ở một vị trí cụ thểMột số hàm yêu cầu nhiều đối số, trong khi những hàm khác cho phép các tham số tùy chọn. Nếu bạn hết đối số hoặc thêm nhiều hơn mức cần thiết, Excel sẽ cảnh báo bạn bằng các thông báo lỗi liên quan đến hàm đó. Việc nắm rõ cú pháp chính xác của từng hàm (số lượng đối số, kiểu dữ liệu mong đợi, v.v.) giúp giảm thiểu các sự cố trước khi chúng xảy ra.

Các loại đối số: số, văn bản và giới hạn lồng nhau

Các hàm Excel không chỉ yêu cầu một số lượng đối số cụ thể mà còn mong muốn được truyền vào các tham số khác. kiểu dữ liệu chính xác trong mỗi đối sốHàm dùng để cộng các số không giống với hàm dùng để thao tác văn bản; nếu chúng được sử dụng thay thế cho nhau, kết quả có thể không như mong đợi hoặc thậm chí gây ra lỗi.

Ví dụ, các hàm như SUM, AVERAGE hoặc PRODUCT yêu cầu các đối số dạng số. Nếu bạn truyền cho chúng các giá trị văn bản tại nơi chúng mong đợi các số, Excel có thể trả về 0, bỏ qua các ô hoặc hiển thị lỗi tùy thuộc vào ngữ cảnh. Ngược lại, các hàm như REPLACE, CONCAT hoặc RIGHT được thiết kế để làm việc với chuỗi văn bản. Trong các hàm này, ít nhất một trong các đối số phải là một giá trị văn bản hoặc một tham chiếu đến một ô chứa văn bản; nếu bạn ép buộc một số được xử lý như văn bản mà không xử lý đúng cách, hàm có thể không hoạt động như mong đợi.

Hơn nữa, Excel đặt ra một giới hạn đáng kể đối với việc kết hợp các hàm với nhau. Bạn không thể... lồng ghép hơn 64 cấp độ hàm trong một công thức duy nhấtĐiều này có nghĩa là nếu bạn có một công thức với các câu lệnh IF lồng nhau, rồi lại lồng nhau nữa, v.v., thì Excel sẽ không chấp nhận thêm bất kỳ cấp độ nào nữa sau một điểm nhất định. Mặc dù trên thực tế ít người đạt đến mức độ phức tạp này, nhưng trong các mô hình rất phức tạp hoặc được thiết kế kém, giới hạn này có thể bị vượt quá, gây ra các lỗi khó giải thích. Vượt quá một mức độ phức tạp nhất định, thường thì nên chia logic thành nhiều ô phụ trợ hơn là cố gắng tập trung mọi thứ vào một công thức duy nhất, cực kỳ dài; vì vậy, bạn cũng nên xem lại... Nguyên nhân và giải pháp cho các vấn đề về hiệu suất trong Excel.

Một ví dụ điển hình về lỗi do kiểu và số lượng đối số là hàm ABS. Hàm này chỉ chấp nhận một đối số số duy nhất và trả về giá trị tuyệt đối của nó. Nếu bạn nhập một cái gì đó như =ABS(-2;134), Excel sẽ hiển thị lỗi vì hàm không biết phải làm gì với hai đối số được phân cách bởi dấu chấm phẩy. Cách đúng sẽ là một cái gì đó như =ABS(-2134) hoặc =ABS(A1) nếu ô A1 chứa số âm mà bạn muốn chuyển đổi thành số dương.

Mặt khác, việc sử dụng dấu phân cách cần được xem xét tùy thuộc vào cài đặt khu vực. Trong nhiều phiên bản Excel tiếng Tây Ban Nha, người ta sử dụng định dạng sau: dấu chấm phẩy (;) được sử dụng làm dấu phân cách đối số. và dấu phẩy (,) cho phần thập phân. Nếu một công thức vô tình trộn lẫn dấu phẩy và dấu chấm phẩy ở những vị trí không phù hợp, nó có thể tạo ra các lỗi cú pháp bổ sung, làm phức tạp khả năng đọc hiểu và gỡ lỗi của công thức.

Tóm lại, việc lựa chọn đúng kiểu dữ liệu để truyền cho mỗi hàm, tuân thủ số lượng đối số được định nghĩa trong cú pháp của hàm và không vượt quá giới hạn lồng nhau là ba trụ cột cơ bản để giảm thiểu lỗi trong các công thức phức tạp.

Tham chiếu đến các trang và sách khác: cách tránh lỗi liên kết

Khi các công thức bắt đầu liên kết đến các trang tính khác trong cùng một sổ làm việc hoặc thậm chí đến các sổ làm việc bên ngoài, rất dễ mắc phải những lỗi đánh máy nhỏ dẫn đến thông báo tham chiếu không hợp lệ. Excel cần bạn phải Tên của các trang, cuốn sách và tuyến đường được viết một cách rất chính xác. để xác định vị trí dữ liệu một cách chính xác.

Nếu trong công thức bạn tham chiếu đến một trang tính có tên chứa khoảng trắng hoặc các ký tự không phải chữ cái (số, dấu gạch ngang, ký hiệu…), thì tên trang tính đó phải luôn được bao gồm. trong dấu ngoặc đơnVí dụ, nếu bạn có một trang tính tên là Dữ liệu hàng quý, thì tham chiếu chính xác đến ô D3 trên trang tính đó sẽ là ='Dữ liệu hàng quý'!D3. Tương tự, nếu trang tính có tên là 123, bạn sẽ viết ='123'!A1 để Excel hiểu tên trang tính và không nhầm lẫn nó với một số ngẫu nhiên.

Ngoài ra, bất cứ khi nào một công thức trỏ đến một trang tính khác, phải đặt dấu gạch ngang ngay sau tên trang tính đó. dấu chấm than (!)Dấu chấm than (```) biểu thị sự chuyển tiếp giữa mã định danh trang tính và tham chiếu ô cụ thể. Một ví dụ đầy đủ là: ='Quarterly Data'!D3. Nếu dấu chấm than bị bỏ sót hoặc đặt sai vị trí, công thức sẽ không hợp lệ và sẽ xảy ra lỗi tham chiếu.

Khi thông tin nằm trong một bảng tính khác, Excel cần thêm ngữ cảnh. Trong những trường hợp này, tham chiếu bên ngoài phải bao gồm... Tên tệp trong ngoặc, tên trang tính và phạm viVí dụ, nếu bạn muốn đếm số hàng trong phạm vi A1:A8 của bảng tính Operations T2.xlsx, trên trang tính Sales, bạn có thể sử dụng công thức như =ROWS('Sales'!A1:A8). Nếu tệp không được mở, bạn cũng cần cung cấp đường dẫn đầy đủ của tệp, chẳng hạn như 'C:\My Documents\Sales'!A1:A8, trong công thức.

Một công thức điển hình trong ngữ cảnh này sẽ là =ROWS('C:\My Documents\Sales'!A1:A8). Lệnh này sẽ trả về số hàng trong phạm vi A1:A8 của sổ làm việc khác, trong trường hợp này là 8. Nếu có bất kỳ lỗi nào khi nhập đường dẫn, tên sổ làm việc, tên trang tính hoặc dấu ngoặc vuông, kết quả sẽ là lỗi. lỗi tham chiếu hoặc giá trị không được cập nhật Vì Excel không thể định vị được nguồn dữ liệu bên ngoài.

Cũng nên theo dõi những gì xảy ra khi các tệp chứa dữ liệu được liên kết bị di chuyển, đổi tên hoặc xóa. Công cụ kiểm tra lỗi có thể phát hiện một số sự cố, nhưng nếu tệp bên ngoài không còn tồn tại ở vị trí mong muốn, bạn sẽ cần cập nhật thủ công các liên kết hoặc xác định lại đường dẫn để khôi phục chức năng của công thức.

Định dạng số trong công thức: lỗi do ký hiệu và dấu phân cách

Một trong những vấn đề phổ biến nhất trong bảng tính có nhiều dữ liệu, đặc biệt là khi dữ liệu đến từ các ứng dụng khác, là việc sử dụng... Định dạng số không chính xác trong công thứcExcel phân biệt rõ ràng giữa giá trị thực tế được lưu trữ trong ô và định dạng được sử dụng để hiển thị giá trị đó. Nhầm lẫn giữa hai cấp độ này thường dẫn đến những lỗi nhỏ khó nhận biết.

Số không được định dạng trong công thức. Tức là, nếu giá trị là 1000 euro, nó phải xuất hiện trong công thức dưới dạng 1000, không có ký hiệu €, dấu phân cách hàng nghìn hoặc dấu phẩy. Nếu bạn viết 1.000 hoặc 1,000 (tùy thuộc vào cài đặt khu vực), Excel có thể sẽ hiểu đó là dấu phân cách đối số hoặc một giá trị khác với giá trị bạn muốn. Số được định dạng với định dạng tiền tệ, dấu phân cách hàng nghìn hoặc dấu phân cách thập phân. sauSử dụng các tùy chọn định dạng ô, không phải bên trong biểu thức; để biết thêm chi tiết về cách áp dụng định dạng chính xác, hãy xem Định dạng dữ liệu trong Excel 365.

Giả sử bạn muốn cộng 3100 vào nội dung của ô A3. Nếu bạn gõ theo bản năng =SUM(3.100,A3) với ý nghĩ là cộng 3100, Excel sẽ hiểu điều này là cộng 3 và 100 trước, rồi mới cộng giá trị của A3 vào kết quả. Nói cách khác, nó sẽ tính (3 + 100) + A3, không giống với A3 + 3100. Công thức chính xác phải là =SUM(3100,A3), mà không cần cố gắng thể hiện định dạng dấu phân cách hàng nghìn.

Điều tương tự cũng xảy ra với các hàm chỉ chấp nhận một số lượng đối số nhất định, chẳng hạn như hàm ABS, chỉ chấp nhận giá trị số. Nếu bạn cố gắng viết dấu phẩy hoặc dấu chấm phẩy trong số để mô phỏng hàng nghìn, Excel sẽ coi đó là nhiều đối số riêng biệt và hàm sẽ không hoạt động. sẽ trả về lỗi cú phápDo đó, các biểu thức như =ABS(-2;134) không hoạt động, trong khi =ABS(-2134) thì hợp lệ.

Điều này đặc biệt quan trọng cần lưu ý khi nhập dữ liệu từ các hệ thống kế toán, ERP hoặc chương trình lập hóa đơn. Nhiều hệ thống này xuất các số tiền kèm theo định dạng, ký hiệu tiền tệ, khoảng trắng hoặc thậm chí cả từ "EUR" ở cuối. Tất cả những điều này biến giá trị số thành chuỗi văn bản mà Excel không thể sử dụng trực tiếp trong các phép tính, dẫn đến lỗi đánh máy, kết quả trống hoặc tổng không khớp.

Cách làm tốt nhất là giữ dữ liệu đã lưu trữ ở dạng Số liệu thô trong ô không có định dạng đặc biệt Sau đó, áp dụng định dạng trực quan cho tiền tệ, phần trăm hoặc dấu phân cách hàng nghìn từ menu định dạng. Điều này giúp ngăn các ký hiệu gây nhiễu quá trình tính toán và cho phép công cụ xử lý lỗi phát hiện sự không nhất quán tốt hơn.

Kiểm tra lỗi và gỡ lỗi thông minh trong Excel

Ngoài việc sửa lỗi cú pháp, Excel còn bao gồm một hệ thống Chức năng kiểm tra lỗi phân tích công thức và đề xuất sửa lỗi. Khi phát hiện điều gì đó bất thường, công cụ ngày càng thông minh này sẽ giúp xác định các tham chiếu bị thiếu, sự không nhất quán và các vấn đề về kiểu dữ liệu trong các công thức phức tạp, giảm thời gian tìm ra nguồn gốc lỗi.

Trong phiên bản Excel dành cho máy tính để bàn, bạn có thể truy cập chức năng kiểm tra lỗi từ tab Công thức, trong nhóm Chỉnh sửa công thức, nơi có tùy chọn Kiểm tra lỗi. Trình hướng dẫn này sẽ quét các ô có cảnh báo và hiển thị thông báo về các nguyên nhân có thể gây ra sự cố, cũng như đề xuất các thay đổi cụ thể có thể được áp dụng chỉ bằng một cú nhấp chuột. Chức năng này hữu ích để phát hiện các phạm vi không nhất quán, các công thức chưa được cập nhật khi kéo, tham chiếu đến các ô trống hoặc sự không nhất quán trong tổng số.

Tuy nhiên, trong Excel Online (phiên bản web), hiện tại không thể cấu hình hoặc sử dụng các quy tắc kiểm tra lỗi nâng cao này theo cùng một cách. Dịch vụ đám mây cung cấp các chức năng cơ bản, nhưng không bao gồm toàn bộ các quy tắc xem xét công thức. Có sẵn trên máy tính để bàn. Do đó, khi làm việc với các bảng tính rất phức tạp, cách thiết thực nhất là mở chúng bằng ứng dụng dành cho máy tính để bàn để tận dụng tối đa công cụ gỡ lỗi.

Nếu bạn đang sử dụng phiên bản Excel trên máy tính để bàn, bạn có thể dùng nút "Mở bằng Excel" trong Excel Online để mở bảng tính trong ứng dụng đầy đủ. Tại đó, bạn có thể thực hiện tất cả các kiểm tra cần thiết để phát hiện các lỗi công thức tiềm ẩn, kích hoạt các quy tắc cụ thể và xem xét bất kỳ cảnh báo nào mà hệ thống đưa ra. Đây là một cách hiệu quả để kết hợp sự tiện lợi của việc làm việc trực tuyến với sức mạnh của ứng dụng trên máy tính để bàn.

Để luôn cập nhật những khả năng mới của công cụ xác thực và những cải tiến đối với các công cụ gỡ lỗi thông minh, bạn nên thường xuyên kiểm tra tài liệu. Blog chính thức của Microsoft ExcelĐây là nơi đăng tải tin tức, cập nhật và những thay đổi đối với phiên bản máy tính và trực tuyến, giúp bạn biết khi nào các tính năng sửa lỗi mới được phát hành.

Nếu bạn cần quyền truy cập toàn diện hơn vào tất cả các ứng dụng Office (Word, Excel, PowerPoint, v.v.) và các dịch vụ liên quan, bạn luôn có thể dùng thử hoặc mua trọn bộ ứng dụng thông qua Office.com, đảm bảo bạn có các công cụ kiểm tra lỗi tiên tiến nhất cho tất cả các bảng tính của mình.

Các lỗi do số được lưu trữ dưới dạng văn bản và cách khắc phục chúng hàng loạt.

Một tình huống rất phổ biến, đặc biệt trong môi trường kế toán hoặc tài chính, phát sinh khi xuất sổ cái tổng hợp chi tiết (chi tiết GL) từ phần mềm quản lý. Trong nhiều trường hợp, các loại ứng dụng này... Họ xuất các số liệu dưới dạng văn bản.Kết quả là, khi mở tệp trong Excel, các công thức cố gắng cộng, trừ hoặc phân tích các trường đó không nhận dạng được các giá trị là số, và các phép toán sẽ thất bại hoặc cho ra kết quả không chính xác.

Chức năng kiểm tra lỗi tích hợp sẵn của Excel có thể phát hiện vấn đề này và hiển thị biểu tượng tam giác màu xanh lá cây quen thuộc ở góc ô, cho biết rằng "số được lưu trữ dưới dạng văn bản". Nhấp vào biểu tượng cảnh báo sẽ cho phép Excel chuyển đổi văn bản thành số. Tuy nhiên, nhược điểm là theo mặc định, nếu bạn sử dụng công cụ Kiểm tra lỗi từ tab Công thức > Chỉnh sửa công thức > Kiểm tra lỗi, thì việc sửa lỗi thường được áp dụng. từng tế bào mộtĐiều này có thể gây khó chịu trong một sổ cái tổng hợp với hàng nghìn dòng.

Hơn nữa, nếu toàn bộ tài liệu được chọn, việc kiểm tra lỗi không chỉ tập trung vào các cột số mà còn cả các cột khác chứa ngày tháng, văn bản và mô tả, khiến quá trình sửa lỗi hàng loạt càng phức tạp hơn. Câu hỏi hợp lý trong trường hợp này là liệu có cách nào để chọn toàn bộ trang tính và để Excel tự động sửa tất cả các lỗi này bằng cách chuyển đổi văn bản thành số thực trong một lần hay không.

Có một số chiến lược có thể được áp dụng trực tiếp từ Excel trên máy tính để giải quyết những tình huống này. Một trong những chiến lược trực tiếp nhất liên quan đến việc sử dụng... keo đặc biệt với phép toánVí dụ, bạn có thể nhập số 1 vào một ô trống, sao chép ô đó, chọn phạm vi ô chứa các số được lưu dưới dạng văn bản (ví dụ: toàn bộ cột số tiền), và sử dụng Dán Đặc biệt > Nhân. Thao tác này khiến Excel hiểu mỗi mục nhập "văn bản" là một số, nhân nó với 1, và lưu kết quả dưới dạng giá trị số thực, giữ nguyên số tiền. Đây là một cách rất hiệu quả để chuyển đổi lượng lớn dữ liệu cùng một lúc.

Một phương án khác là sử dụng hàm VALUE trong cột phụ. Ví dụ, nếu văn bản chứa số tiền ở cột B, bạn có thể tạo công thức như =VALUE(B2) trong cột C, sao chép xuống, sau đó sao chép và dán kết quả dưới dạng giá trị vào cột gốc, thay thế văn bản. Kỹ thuật này cũng cho phép bạn kiểm tra trực quan xem mọi thứ có khớp nhau trước khi xóa dữ liệu gốc.

Trong một số trường hợp, cảnh báo "số được lưu dưới dạng văn bản" cho phép bạn chọn phạm vi rộng hơn và áp dụng chuyển đổi cho nhiều ô cùng một lúc từ menu ngữ cảnh hình tam giác màu xanh lá cây nhỏ. Tuy nhiên, hiệu quả của chúng phụ thuộc vào cách dữ liệu được tạo ra và liệu Excel có phát hiện tất cả các trường hợp là cùng một loại lỗi hay không.

Khi làm việc với khối lượng lớn dữ liệu kế toán, bạn cũng nên xem xét cài đặt khu vực của Excel và định dạng xuất của chương trình nguồn. Điều chỉnh các tham số này để đảm bảo tính tương thích của dấu phân cách thập phân, ký hiệu tiền tệ và định dạng ngày tháng sẽ giảm đáng kể sự xuất hiện của các số ở định dạng văn bản và các lỗi phổ biến, đồng thời cho phép các công thức hoạt động chính xác ngay từ đầu.

Tóm lại, mặc dù việc kiểm tra lỗi tiêu chuẩn thường hoạt động theo từng dòng, nhưng việc kết hợp các cảnh báo của nó với các kỹ thuật như dán đặc biệt và các hàm chuyển đổi là hoàn toàn khả thi. Sửa chữa hàng loạt khối dữ liệu bị lỗi định dạng. và khôi phục lại trạng thái của chúng dưới dạng số mà không cần phải xem xét từng ô một.

Toàn bộ các khuyến nghị này liên quan đến dấu bằng, dấu ngoặc đơn, phạm vi, kiểu đối số, định dạng số, tham chiếu ngoài và công cụ kiểm tra lỗi đều có chung một mục tiêu: giúp công cụ gỡ lỗi thông minh mới của Excel hoạt động hiệu quả tối đa. Khi công thức được xây dựng tốt và dữ liệu sử dụng đúng kiểu và định dạng, hệ thống cảnh báo và đề xuất sẽ trở thành một trợ thủ đắc lực giúp nhanh chóng xác định sự không nhất quán, tham chiếu bị lỗi hoặc các ô có vấn đề, ngay cả trong các bảng tính có công thức phức tạp và dữ liệu được nhập từ nhiều nguồn khác nhau.

Bài viết liên quan:

Những lỗi thường gặp nhất trong công thức Excel và cách khắc phục

Isaac

Người viết đam mê về thế giới byte và công nghệ nói chung. Tôi thích chia sẻ kiến ​​thức của mình thông qua viết lách và đó là những gì tôi sẽ làm trong blog này, cho bạn thấy tất cả những điều thú vị nhất về tiện ích, phần mềm, phần cứng, xu hướng công nghệ, v.v. Mục tiêu của tôi là giúp bạn điều hướng thế giới kỹ thuật số một cách đơn giản và thú vị.