Cách chỉnh sửa, đánh giá và sửa lỗi công thức trong Excel

Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn một vài cách nhanh chóng và hiệu quả để kiểm tra và sửa lỗi công thức trong Excel. Cách sử dụng phím F9 để đánh giá từng phần của công thức, làm thế nào để làm nổi bật các ô tham chiếu hoặc được tham chiếu bởi một công thức nhất định, cách xác định lỗi không khớp hoặc sai vị trí, và nhiều thứ khác.

Phím F2 trong Excel – chỉnh sửa công thức

Phím F2 trong Excel lai giữa chế độ Edit và  Enter. Khi bạn muốn thay đổi công thức hiện tại, hãy chọn ô công thức và nhấn F2 để vào Edit mode. Một khi bạn làm điều này, con trỏ sẽ bắt đầu nhấp nháy ở cuối dấu ngoặc trong thanh công thức hoặc ô công thức (tùy thuộc vào tùy chọn Allow editing directly in cells được chọn hay không chọn). Và bây giờ, bạn có thể thực hiện bất kỳ chỉnh sửa nào trong công thức:

• Sử dụng mũi tên trái và phải để điều hướng trong công thức.
• Sử dụng các phím mũi tên cùng với Shift để chọn các phần công thức (cũng có thể thực hiện bằng chuột).
• Nhấn Delete hoặc Backspace để xóa tham chiếu ô nhất định hoặc các phần tử khác của công thức.

Khi bạn hoàn tất chỉnh sửa, nhấn Enter để hoàn thành công thức.

Để thoát chế độ Chỉnh sửa mà không thực hiện bất kỳ thay đổi nào đối với công thức, hãy nhấn phím Esc.

Chỉnh sửa trực tiếp trong ô hoặc trong thanh công thức

Theo mặc định, việc nhấn phím F2 trong Excel sẽ đặt con trỏ vào cuối công thức trong một ô. Nếu bạn thích chỉnh sửa trong thanh công thức Excel , hãy làm như sau:

• Nhấp vào File > Options .
• Trong ngăn bên trái, chọn Advanced .
• Trong ngăn bên phải, bỏ chọn tùy chọn Allow editing directly in cells bên dưới Editing Options.
• Nhấp OK để lưu các thay đổi và đóng hộp thoại.

Hiện nay, F2 thường được coi là một cách cổ điển để chỉnh sửa các công thức. Hai cách khác để vào Edit mode  là:

• Nhấp đúp vào ô, hoặc
• Nhấp vào bất cứ nơi nào trong thanh công thức.

Vậy F2 có hiệu quả hơn hay nó có lợi thế gì? Không. Đơn giản là một số người thích làm việc với bàn phím trong khi những người khác lại thấy thuận tiện hơn khi sử dụng chuột.

Cho dù bạn chọn phương pháp chỉnh sửa nào, bạn có thể tìm thấy chế độ Edit mode ở góc dưới cùng bên trái của màn hình. Ngay sau khi bạn nhấn phím F2 , hoặc nhấp đúp vào ô, hoặc nhấp vào thanh công thức, từ Edit  sẽ xuất hiện ngay bên dưới tab sheet:

Phím F9 trong Excel – đánh giá các phần của công thức

Trong Microsoft Excel, phím F9 là một cách dễ dàng và nhanh chóng để kiểm tra và sửa lỗi công thức. Nó cho phép bạn đánh giá chỉ phần được lựa chọn của công thức bằng cách thay thế các giá trị thực hoặc thay bằng kết quả tính toán. Ví dụ dưới đây cho thấy cách hoạt động của phím F9.

Giả sử bạn có công thức IF trong bảng tính của bạn:

=IF(AVERAGE(A2:A6)>AVERAGE(B2:B6),”Good”,”Bad”)

Để đánh giá từng hàm Average có trong công thức, thực hiện theo các bước sau:

• Chọn ô có công thức, trong ví dụ này là D1.
• Nhấn F2 hoặc nhấp đúp vào ô đã chọn để vào chế độ Edit mode.
• Chọn phần công thức mà bạn muốn kiểm tra và nhấn F9 .

Ví dụ, nếu bạn chọn hàm Average đầu tiên, tức là AVERAGE (A2: A6), và nhấn F9 , Excel sẽ hiển thị kết quả:

Nếu bạn chỉ chọn phạm vi ô (A2: A6) và nhấn F9 , bạn sẽ thấy các giá trị thực tế thay vì các tham chiếu ô:

Để thoát chế độ đánh giá công thức, bấm phím Esc .

 Mẹo F9:

• Hãy chắc chắn đã chọn một phần của công thức trước khi nhấn F9, nếu không phím F9 sẽ thay thế toàn bộ công thức với giá trị đã được tính toán của nó.
• Khi ở chế độ đánh giá công thức, không nhấn phím Enter vì điều này sẽ thay thế phần đã chọn bằng giá trị được tính toán hoặc giá trị ô. Để giữ lại công thức gốc, nhấn phím Esc để hủy thử nghiệm công thức và thoát khỏi chế độ đánh giá công thức.

Xem thêm: Thủ thuật Excel hay

Kỹ thuật  F9 đặc biệt hữu ích cho việc kiểm tra các công thức phức tạp, chẳng hạn như các công thức lồng nhau hoặc các công thức mảng, vì nó bao gồm nhiều phép tính trung gian hoặc các phép thử logic. Phương pháp sửa lỗi này cho phép bạn thu hẹp một lỗi đến một phạm vi hoặc hàm cụ thể gây ra.

Sửa lỗi một công thức bằng cách sử dụng Evaluate Formula feature

Một cách khác để đánh giá các công thức trong Excel là tùy chọn Evaluate Formula feature nằm trên tab Formulas, trong nhóm Formula Auditing.

Ngay sau khi bạn nhấp vào nút này, hộp thoại Evaluate Formula sẽ bật lên, bạn có thể kiểm tra từng phần của công thức theo thứ tự tính công thức.

Tất cả bạn cần làm là nhấp vào nút Evaluate và kiểm tra giá trị của phần công thức được gạch chân. Kết quả của đánh giá gần nhất được in nghiêng.

Tiếp tục nhấp vào nút Evaluate cho đến khi từng phần của công thức đã được kiểm tra.

Để kết thúc đánh giá, nhấp vào nút Close .

Để đánh giá công thức từ đầu, hãy nhấp vào Restart .

Nếu phần gạch chân của công thức là một tham chiếu đến một ô có chứa công thức khác, nhấp vào nút Step In để công thức khác hiển thị trong hộp Evaluation. Để trở về công thức trước, nhấp vào Step Out.

Chú ý. Nút Step In không hoạt động đối với tham chiếu ô chỉ đến một công thức khác trong một bảng tính khác. Ngoài ra, nó cũng không áp dụng cho một tham chiếu ô xuất hiện trong công thức lần thứ hai (như ví dụ thứ hai của D1 trong ảnh chụp màn hình ở trên).

Làm nổi bật và khớp các cặp dấu ngoặc đơn trong một công thức

Khi tạo công thức phức tạp trong Excel, bạn thường cần nhiều cặp ngoặc đơn để chỉ định thứ tự của phép tính hoặc tổ chức một vài hàm khác nhau. Điều này dễ gây nhầm lẫn, bỏ quên, hoặc thừa ngoặc đơn trong các công thức.

Nếu bạn bỏ lỡ hoặc đặt sai dấu ngoặc đơn mà nhấn phím Enter, Microsoft Excel sẽ hiển thị một cảnh báo gợi ý để sửa công thức cho bạn:

Nếu bạn đồng ý với đề xuất sửa đổi, hãy nhấp vào Yes. Nếu công thức chỉnh sửa không phải là điều bạn muốn, hãy nhấp vào No và thực hiện chỉnh sửa theo cách thủ công.

Chú ý. Microsoft Excel không phải lúc nào cũng sửa các dấu ngoặc bị thiếu hoặc không tương xứng. Vì vậy, luôn luôn xem xét lại đề xuất sửa đổi cẩn thận trước khi chấp nhận nó.

Để giúp bạn cân bằng các cặp dấu ngoặc đơn , Excel cung cấp ba kiểu trực quan khi bạn gõ hoặc chỉnh sửa một công thức:

• Khi nhập một công thức phức tạp có chứa nhiều dấu ngoặc đơn, Excel sẽ tạo ra các cặp dấu ngoặc đơn với các màu khác nhau để dễ dàng nhận diện chúng. Cặp ngoặc ngoài cùng luôn luôn là màu đen. Điều này có thể giúp bạn xác định xem bạn đã chèn đúng số ngoặc đơn vào công thức hay chưa.
• Khi bạn gõ dấu đóng ngoặc trong công thức, Excel sẽ in đậm cặp dấu ngoặc đơn (dấu ngoặc đơn bạn vừa nhập và dấu ngoặc đơn bên trái). Nếu bạn nghĩ bạn đã gõ dấu ngoặc đóng cuối cùng trong công thức và Excel không in đậm dấu ngoặc mở, thì dấu ngoặc đơn của bạn không khớp hoặc không cân bằng.
• Khi bạn di chuyển trong công thức bằng các phím mũi tên và vượt qua một dấu ngoặc đơn, dấu ngoặc đơn khác trong cặp được in đậm và định dạng cùng màu. Bằng cách này, Excel cố gắng làm cho các cặp ngoặc đơn rõ ràng hơn.

Trong hình sau, tôi đã di chuyển qua dấu đóng ngoặc cuối cùng bằng phím mũi tên, và cặp dấu ngoặc ngoài (màu đen) đã được in đậm:

Làm nổi bật tất cả các ô được tham chiếu trong một công thức nhất định

Khi bạn sửa lỗi một công thức trong Excel, các ô tham chiếu cũng cần được xem xét. Để làm nổi bật tất cả các ô phụ thuộc, hãy thực hiện theo các bước sau:

• Chọn ô công thức và nhấn phím tắt Ctrl + [. Excel sẽ in đậm tất cả các ô mà công thức của bạn đề cập đến, và di chuyển vùng lựa chọn đến ô được tham chiếu đầu tiên hoặc một dải ô.
• Để điều hướng đến ô được tham chiếu tiếp theo, nhấn Enter.

Trong ví dụ này, tôi chọn ô F4 và nhấn Ctrl + [ . Hai ô (C4 và E4) được tham chiếu trong công thức của F4 đã được đánh dấu, ô C4 được chọn:

Làm nổi bật tất cả các công thức tham chiếu đến ô đã chọn

Các thủ thuật trước đã hướng dẫn cách đánh dấu các ô tham chiếu trong một công thức nhất định. Nhưng nếu bạn muốn tìm ra tất cả các công thức đề cập đến một ô đặc biệt? Ví dụ: bạn có thể muốn xóa một số dữ liệu không liên quan hoặc đã cũ trong một bảng tính, nhưng bạn muốn chắc rằng việc xóa sẽ không làm hỏng bất kỳ công thức nào hiện có.

Để làm nổi bật tất cả các ô chứa công thức tham chiếu đến một ô cho trước, hãy chọn ô đó và nhấn phím tắt Ctrl + ].

Giống như trong ví dụ trước, công thức đầu tiên trên bảng tính sẽ được chọn. Để chọn những công thức khác, nhấn phím Enter liên tục.

Trong ví dụ này, tôi đã chọn ô C4, nhấn Ctrl +] và Excel ngay lập tức đánh dấu các ô (E4 và F4) có chứa tham chiếu C4:

Theo dõi các mối quan hệ giữa các công thức và các ô trong Excel

Một cách khác để hiển thị trực quan các ô liên quan đến một công thức nhất định là sử dụng nút Trace Precedents  và  Trace Dependents nằm trên tab Formulas > Formula Auditing group.

Trace Precedents  – hiển thị ô cung cấp dữ liệu cho một công thức cho trước

Trace Precedents hoạt động tương tự phím tắt Ctrl + [ , tức là hiển thị các ô cung cấp dữ liệu cho ô công thức được chọn.

Sự khác biệt là phím tắt Ctrl + [ làm nổi bật tất cả các ô được tham chiếu trong một công thức, trong khi nhấn vào nút Trace Precedents sẽ vẽ đường vạch màu xanh từ các ô được tham chiếu đến ô công thức đã chọn, như hình sau:

Bạn có thể sử dụng phím tắt Alt + T U T để đường này biến mất.

Trace Dependents – hiển thị các công thức tham chiếu đến một ô đã cho

Nút Trace Dependents hoạt động tương tự như phím tắt Ctrl + ]. Nó chỉ ra các ô phụ thuộc vào ô đó, tức là các ô chứa công thức tham chiếu đến một ô nhất định.

Trong hình sau, ô D2 được chọn, các đường kẻ màu xanh chỉ tới các công thức có chứa tham chiếu D2:

Một cách khác để bật tính năng này là nhấp vào phím tắt Alt + T U D .

Mẹo. Để ẩn các mũi tên, hãy nhấp vào nút Remove Arrows nằm bên dưới Trace Dependents.

Giám sát công thức và kết quả của chúng (Watch Window)

Khi bạn làm việc với bộ dữ liệu lớn, bạn có thể theo dõi các công thức quan trọng nhất trong bảng tính của mình và xem cách các giá trị đã tính thay đổi khi bạn chỉnh sửa dữ liệu nguồn. Watch Window của Excel được tạo ra cho mục đích này.

Watch Window sẽ hiển thị các thuộc tính ô, chẳng hạn như tên bảng tính và trang, ô hoặc tên dãy nếu có, địa chỉ ô, giá trị và công thức, trong một cửa sổ riêng biệt. Bằng cách này, bạn luôn nhìn thấy những dữ liệu quan trọng nhất trong nháy mắt, ngay cả khi bạn đang chuyển đổi giữa các bảng tính khác nhau.

Cách thêm các ô vào cửa sổ Watch Window 

Để hiển thị Watch Window và thêm các ô để theo dõi, hãy thực hiện theo các bước sau:

1. Chọn (những) ô mà bạn muốn xem.

Mẹo. Nếu bạn muốn theo dõi tất cả các ô có công thức trên trang tính, hãy chuyển đến tab Home  > Editing , nhấn Find & Replace, sau đó nhấp vào Go To Special, và chọn Formulas.

1. Chuyển sang tab Formulas  > Formula Auditing, và nhấn Watch  Window.

1. Watch Window sẽ xuất hiện và bạn nhấp vào Add Watch… .

Chú ý Watch Window:

• Bạn chỉ có thể thêm một lần xem cho mỗi ô.
• Các ô có tham chiếu bên ngoài cho các bảng tính khác chỉ được hiển thị khi các bảng tính đó đang mở.

Cách loại bỏ các ô từ Watch Window

Để xóa các ô nhất định khỏi Watch Window, hãy chọn ô bạn muốn xóa và nhấp vào Delete Watch:

Mẹo. Để xóa nhiều ô một lần, nhấn Ctrl và chọn các ô bạn muốn xóa.

Cách di chuyển và cắt bớt Watch Window

Giống như bất kỳ thanh công cụ nào, Watch Window có thể được di chuyển hoặc gắn lên phía trên, bên dưới, bên trái, hoặc bên phải của màn hình. Để làm điều này, chỉ cần kéo Watch Window bằng chuột đến vị trí mà bạn muốn.

Ví dụ: Nếu bạn gắn Watch Window xuống dưới cùng, nó sẽ luôn hiển thị bên dưới các tab của bảng và bạn có thể kiểm tra các công thức chính mà không cần phải di chuyển lên xuống các ô công thức nhiều lần.

Cuối cùng, tôi muốn chia sẻ một vài mẹo khác có thể hữu ích cho việc đánh giá và sửa lỗi các công thức Excel của bạn.

Mẹo sửa lỗi công thức:

1. Để xem toàn bộ công thức dài mà không chồng chéo nội dung của ô lân cận, sử dụng thanh công thức. Nếu công thức quá dài để khớp với thanh công thức mặc định, hãy mở rộng nó bằng cách nhấn Ctrl + Shift + U hoặc kéo khung dưới của nó bằng cách sử dụng con chuột .
2. Để xem tất cả công thức trên trang thay vì kết quả của chúng, nhấn Ctrl +` hoặc nhấp vào nút Show Formulas  trên tab Formulas.