Mẹo chỉnh sửa, đánh giá và khắc phục lỗi trong công thức Excel

Trong nội dung dưới đây, Siêu Marketing sẽ chia sẻ cách kiểm tra và sửa chữa lỗi trong công thức của Excel một cách nhanh và hiệu quả. Bạn sẽ học cách sử dụng phím F9 để kiểm tra từng phần của công thức, cách hiện rõ các ô được tham khảo hoặc tham chiếu đến bởi công thức cụ thể, cách xử lý lỗi không tương ứng hoặc sai lệch vị trí và nhiều điều khác.

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

Khi muốn chỉnh sửa công thức đang có, bạn nên chọn ô chứa công thức và nhấn F2 để bắt đầu chế độ Edit mode. Thực hiện điều này, con trỏ sẽ nhấp nháy ở phần cuối của thanh công thức hoặc ngay trong ô chứa công thức, phụ thuộc vào việc bạn có chọn lựa chức năng Allow editing directly in cells hay không. Bây giờ, bạn có khả năng tiến hành các chỉnh sửa cần thiết trên công thức:

  • Dùng phím mũi tên trái, phải để di chuyển qua lại trong công thức.
  • Kết hợp phím mũi tên với Shift để lựa chọn phần của công thức, hoặc có thể dùng chuột để thực hiện việc này.
  • Ấn Delete hay Backspace để loại bỏ các tham chiếu ô hoặc các phần khác trong công thức.

Để kết thúc việc chỉnh sửa, ấn Enter để công thức có hiệu lực.

Nếu muốn thoát khỏi chế độ chỉnh sửa mà không làm thay đổi gì, hãy ấn Esc.

Chỉnh sửa ngay trên ô công thức hoặc thanh công thức

Bất kỳ lúc nào bạn nhấn F2, Excel mặc định sẽ đưa con trỏ đến cuối công thức trong ô. Nếu bạn muốn chỉnh sửa trên thanh công thức của Excel, hãy thực hiện các bước sau đây:

  • Chọn File > Options.
  • Chọn mục Advanced từ thanh bên trái.
  • Ở thanh bên phải, hãy bỏ chọn Allow editing directly in cells phía dưới mục Editing Options.
  • Ấn OK để lưu lại và đóng cửa sổ hộp thoại.

For the F2 key to position the cursor in the formula bar, uncheck the 'Allow editing directly in cells' option.

Bây giờ, phím F2 đã trở thành phương pháp truyền thống để chỉnh sửa công thức. Ngoài ra còn có hai cách khác để vào chế độ Edit mode:

  • Nhấn đúp vào ô công thức, hoặc
  • Chọn bất kỳ nơi nào trên thanh công thức.

Liệu phím F2 có hiệu quả hơn không, hay có ưu điểm gì đặc biệt? Không hẳn. Chỉ đơn giản là một số người ưa thích dùng bàn phím còn người khác thì thấy dùng chuột thuận tiện hơn.

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

A visual indication that Microsoft Excel is in the Edit mode.

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

Trong Microsoft Excel, phím F9 rất hữu ích và nhanh chóng đối với việc kiểm tra và sửa lỗi công thức. Chức năng này cho phép bạn đánh giá riêng biệt từng phần của công thức thông qua việc thay thế các tham số bằng giá trị thực tế hoặc kết quả tính toán của chúng. Dưới đây là ví dụ về cách sử dụng phím F9.

Xét bạn có công thức IF như sau trong bảng tính của mình:

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

Để đánh giá mỗi hàm Average trong công thức, bạn làm theo các bước dưới đây:

  • Chọn ô chứa công thức,
    • Bắt đầu bằng cách chọn ô chứa công thức mà bạn muốn kiểm tra, ví dụ ở đây là D1.
    • Để chỉnh sửa công thức, bạn nhấn F2 hoặc làm theo cách thực hiện nhấp chuột đúp vào ô được chọn.
    • Thực hiện lựa chọn một phần nhất định trong công thức để kiểm tra bằng cách nhấn F9.

    Khi bạn chọn và đánh giá hàm AVERAGE (A2: A6) bằng F9, Excel sẽ trình bày kết quả như sau:

    Select the function and press F9 to see the calculated value.

    Trong trường hợp bạn tập trung kiểm tra đoạn dữ liệu trong các ô từ A2 đến A6 và sử dụng F9, bạn sẽ có thể nhìn thấy giá trị cụ thể thay vì các tham chiếu ô:

    Select the function and press F9 to see the calculated value.

    Đối với việc rời khỏi chế độ đánh giá công thức này, bạn hãy nhấn phím Esc.

    Lưu ý khi dùng phím tắt F9:

    • Trước khi nhấn F9, hãy cẩn thận chọn một phần của công thức. Nếu không, F9 sẽ ghi đè toàn bộ công thức bằng giá trị đã tính toán của nó.
    • Trong khi công thức đang trong chế độ kiểm tra, hãy tránh nhấn Enter vì nó sẽ thay thế phần được chọn bằng giá trị tính toán hoặc giá trị ô. Để giữ nguyên công thức ban đầu, sử dụng phím Esc để hủy đánh giá và thoát khỏi chế độ này.

    Cách sử dụng phím F9 rất có ích khi kiểm tra công thức có cấu trúc phức tạp, như công thức lồng nhau hay mảng công thức, giúp đơn giản hóa việc tìm và thu hẹp phạm vi lỗi đến các phần hoặc hàm cụ thể.

    Cách sử dụng Evaluate Formula để kiểm tra lỗi công thức

    Bạn cũng có thể đánh giá công thức trong Excel thông qua tùy chọn Evaluate Formula, có trên tab Formulas, trong nhóm Formula Auditing.

    The Evaluate Formula button on the ribbon

    Kích chuột vào nút này sẽ mở cửa sổ Evaluate Formula, từ đó có thể xem xét từng bước của công thức theo trật tự tính toán.

    Để kiểm tra, chỉ cần nhấp vào nút Evaluate và quan sát giá trị của bộ phận công thức đang được gạch dưới. Kết quả đánh giá gần nhất sẽ được in nghiêng để dễ nhận biết.

    Click the Evaluate button and examine the value of the underlined formula part.

    Việc nhấn tiếp tục nút Evaluate là cần thiết cho đến khi các phần của công thức đều đã được đánh giá rõ ràng.

    Nhấn Close để kết thúc quá trình đánh giá.

    Để bắt đầu lại quá trình đánh giá từ đầu, bạn nhấn vào nút Restart.

    Nếu phần công thức hiện đang được gạch chân là tham chiếu đến một ô chứa một công thức khác, bạn có thể nhấn nút Step In để thấy công thức đó trong cửa sổ Evaluation. Để quay lại công thức ban đầu, hãy nhấn Step Out.

    Click the <strong>Step In</strong> button to evaluate another formula that the underlined part refers to.
    Ghi chú: Nút Step In không hoạt động khi tham chiếu đó chỉ đến một công thức trong một trang tính khác hay trong trường hợp tham chiếu ô xuất hiện lần thứ hai trong công thức (như ví dụ thứ hai của D1 trong hình minh họa).

    Cách làm nổi bật các dấu ngoặc đơn có chứa trong công thức

    Trong quá trình xây dựng công thức phức tạp trong Excel, bạn thường cần sử dụng nhiều cặp ngoặc đơn để xác định thứ tự thực hiện các phép toánhoặc gặp phải việc quên mất, nhầm lẫn hoặc để thừa dấu ngoặc đơn khi phối hợp các công thức.

    Khi bạn quên một dấu ngoặc đơn hoặc đặt nó không chính xác và sau đó nhấn Enter, Microsoft Excel sẽ xuất hiện một thông báo cảnh báo và đề xuất sửa công thức tự động cho bạn:

    Microsoft Excel displays an alert suggesting to fix the formula for you automatically.

    Nếu đề xuất chỉnh sửa này phù hợp, bạn có thể chọn Yes. Nếu bạn không muốn áp dụng sửa đổi đó, chọn No và chỉnh sửa công thức thủ công.

    Lưu ý: Không phải mọi lúc Microsoft Excel cũng có thể tự sửa lỗi dấu ngoặc thiếu hoặc không cân đối. Do đó, bạn nên kiểm tra lại kỹ lưỡng những đề xuất chỉnh sửa trước khi đồng ý.

    Để hỗ trợ bạn trong việc điều chỉnh cân bằng dấu ngoặc đơn, Excel cung cấp ba tính năng hình ảnh khi bạn nhập hoặc chỉnh sửa công thức:

    • Trong quá trình nhập một công thức phức tạp chứa nhiều dấu ngoặc đơn, Excel sẽ tạo hiệu ứng màu sắc khác nhau cho từng cặp dấu ngoặc, để bạn dễ dàng phân biệt. Dấu ngoặc cặp ngoài cùng luôn có màu đen. Điều này giúp bạn xác định liệu bạn đã nhập đủ số dấu ngoặc cần thiết cho công thức hay chưa.
    • Excel sẽ in đậm cặp dấu ngoặc (dấu bạn mới nhập và dấu đối ứng của nó) khi bạn gõ một dấu đóng ngoặc. Nếu bạn không thấy dấu ngoặc mở nào được in đậm sau khi bạn nhập dấu ngoặc đóng cuối cùng, có nghĩa là dấu ngoặc của bạn chưa đúng hoặc đang thiếu.
    • Khi bạn di chuyển con trỏ trong công thức sử dụng các phím mũi tên và đi qua một dấu ngoặc đơn, dấu ngoặc đối ứng sẽ được làm nổi bật và có cùng màu sắc. Excel cố gắng làm cho việc nhận biết các cặp dấu ngoặc dễ dàng hơn.

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

    Parenthesis pairs in an Excel formula

    Nổi bật tất cả ô liên quan đến một công thức cụ thể

    Khi sửa đổi một công thức trong Excel, việc xác định các ô tham chiếu cũng rất quan trọng. Dưới đây là cách làm nổi bật tất cả các ô liên quan:

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

    Ví dụ, tôi chọn ô F4 và ap dụng phím tắt Ctrl + [. Hai ô (C4 và E4) có trong công thức của F4 đã hiện lên một cách nổi bật, và ô C4 được chọn đầu tiên:

    Highlighting all cells the formula refers to

    Nổi bật tất cả công thức liên quan đến ô đã chọn

    Để xác định các công thức liên quan đến một ô cụ thể, bạn có thể thực hiện theo phương pháp đã hướng dẫn ở trên. Nhưng khi cần biết tất cả công thức nào đang tham chiếu đến một ô đặc biệt, bạn cần làm thế nào? Điều này quan trọng khi bạn muốn xóa dữ liệu không còn liên quan hoặc đã lỗi thời mà không ảnh hưởng tới các công thức hiện tại.

    Chọn ô mà bạn muốn kiểm tra và dùng phím tắt Ctrl + ] để làm nổi bật các ô chứa công thức tham chiếu đến ô đó.

    Như trong ví dụ trước, công thức xuất hiện đầu tiên trên bảng tính sẽ được chọn. Để chuyển đến các công thức tiếp theo, liên tục nhấn Enter.

    Ví dụ, tôi đã chọn ô C4, bấm Ctrl + ], và Excel đã nhanh chóng đánh dấu các ô (E4 và F4) chứa tham chiếu C4:

    Điểm nổi bật tất cả các công thức liên kết với ô được chọn
    Xác định mối liên hệ giữa công thức và ô trong Excel

    Bạn có thể dùng các nút Trace Precedents và Trace Dependents, nằm trong nhóm Formulas > Formula Auditing, để thể hiện mối quan hệ giữa các ô và một công thức cụ thể.

    Trace Precedents – biểu thị ô cung cấp thông tin cho một công thức cụ thể

    Trace Precedents có chức năng tương đương với phím tắt Ctrl + [, đó là hiện ra các ô mang thông tin tới ô công thức đang được chọn.

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

    Nhấp chuột vào nút Trace Precedents để hiển thị các ô cung cấp dữ liệu cho công thức bạn đã chọn.

    Bạn cũng có thể dùng phím tắt Alt + T U T để loại bỏ các đường vạch này.

    Trace Dependents – chỉ ra các công thức liên quan tới một ô cụ thể

    Nút Trace Dependents hoạt động tương tự như phím tắt Ctrl + ]. Nó chỉ đến các ô phụ thuộc hoặc nói cách khác, là những ô chứa công thức có liên kết tới ô được chọn.

    Trong hình dưới đây, khi ô D2 được chọn, các đường nét màu xanh chạy tới các công thức nằm ở những ô có tham chiếu tới D2:

    Nhấn nút Trace Dependents để biểu hiện các công thức liên quan đến ô đã chọn.

    Để kích hoạt tính năng này, bạn cũng có thể sử dụng phím tắt Alt + T U D.

    Mẹo. Để ẩn đi các mũi tên chỉ đường, hãy nhấn vào nút Remove Arrows ở dưới nút Trace Dependents.

    Theo dõi công thức và kết quả của chúng qua Watch Window

    Khi bạn xử lý một bảng dữ liệu lớn, việc theo dõi những công thức quan trọng và quan sát sự thay đổi của các kết quả khi bạn sửa dữ liệu nguồn là cực kỳ hữu ích. Excel có chức năng Watch Window phục vụ cho việc này.

    Watch Window sẽ hiện ra các thông tin về ô, bao gồm tên trang và bảng tính, ô hoặc tên vùng dữ liệu nếu có, địa chỉ ô, giá trị và công thức trong một cửa sổ riêng biệt. Việc này giúp bạn luôn nắm bắt được những thông tin cần thiết nhất mỗi khi di chuyển giữa các bảng tính khác nhau.

    Sử dụng cửa sổ Watch Window trong Excel để theo dõi các công thức chính trong một sổ làm việc.

    Hướng dẫn thêm ô vào Watch Window

    Để mở Watch Window và thêm ô vào danh sách theo dõi, hãy làm theo các bước sau đây:

    1. Lựa chọn (các) ô bạn muốn theo sát.

    Mẹo. Để theo dõi toàn bộ ô có công thức trong trang tính, di chuyển đến tab Home  > Editing, kích Find & Replace, tiếp đến chọn Go To Special và chọn mục Formulas.

    1. Nhấp sang Formulas  > Formula Auditing,
      1. Nhấp vào Watch Window để nó hiện ra sau đó click vào nút Add Watch…

      Sử dụng Tính năng Watch Window của Excel để theo dõi các ô chứa công thức quan trọng

      1. Tiếp theo, cửa sổ Watch Window sẽ hiển thị, bấm vào Add Watch… để thêm.

      Nhấn vào nút Add Watch… để thêm một ô vào Watch Window.

      Lưu ý khi sử dụng Watch Window:

      • Chỉ được phép thêm duy nhất một lượt xem cho mỗi ô.
      • Nếu các ô có liên kết đến bảng tính khác thì chỉ khi nào bảng tính đó đang được mở thì mới hiển thị được trên Watch Window.

      Cách thức gỡ bỏ các ô ra khỏi Watch Window

      Chọn ô cần loại bỏ và bấm Delete Watch để xóa ô đó khỏi Watch Window.

      Nhấn Delete Watch để loại bỏ ô khỏi Watch Window.
      Tip. Để xóa một loạt ô cùng lúc, giữ phím Ctrl và lựa chọn các ô muốn xóa.

      Quy trình di chuyển và cố định Watch Window

      Watch Window có thể được di chuyển hoặc cố định ở các vị trí khác nhau trên màn hình, cách làm là kéo nó đến nơi bạn muốn đặt.

      Ví dụ, bạn có thể gắn Watch Window ở dưới cùng màn hình để nó luôn hiện hữu và bạn có thể kiểm tra công thức mà không cần di chuyển nhiều.

      Watch Window được cố định ở phía dưới màn hình
      Đồng thời, xin phép được giới thiệu một số thủ thuật khác giúp bạn kiểm tra và chuẩn đoán các lỗi trong công thức Excel.

      Mẹo giúp sửa lỗi trong công thức:

      1. Để nhìn thấy rõ công thức dài mà không làm ảnh hưởng tới những ô xung quanh, sử dụng thanh công thức. Bạn cũng có thể mở rộng thanh công thức bằng cách nhấn Ctrl + Shift + U hoặc dùng chuột kéo lề của thanh công thức ra.
      2. Bạn có thể hiển thị các công thức thay vì kết quả công thức trên toàn bảng tính bằng cách dùng tổ hợp phím Ctrl +` hoặc kích vào nút Show Formulas trên tab Formulas.

      Vì để sử dụng Excel hiệu quả, không chỉ cần biết đến các hàm tính toán mà cận phải thành thạo cả việc áp dụng các công cụ đi kèm như Data validation, Conditional formatting, Pivot table, cùng với đó là những hàm phức tạp như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Mọi kiến thức này đều có thể học được qua các khóa đào tạo.

      Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop