Ngừng kiểm tra từng công thức Excel một – Hãy dùng hàm này thay thế

Hàm FORMULATEXT thường bị bỏ qua vì nó chỉ đơn thuần là chuyển một công thức thành văn bản. Tuy nhiên, chính cái văn bản thô đó lại là điều khiến nó trở nên vô cùng giá trị. Vì vậy, đừng nghĩ nó chỉ là một công cụ đơn giản – nó là công cụ hoàn hảo để kiểm toán, kiểm soát đầu vào và xác thực logic.

Hàm FORMULATEXT có một đối số:

=FORMULATEXT(ref)

trong đó ref là tham chiếu đến một ô hoặc, trong các phiên bản Excel hiện đại, là một phạm vi ô.

Nếu đối số ref tham chiếu đến một sổ làm việc (workbook) khác, thì sổ làm việc đó phải được mở. Nếu không, công thức sẽ trả về lỗi #N/A. Bạn cũng sẽ thấy lỗi này nếu ô được tham chiếu không chứa công thức.

Dưới đây là ba cách bạn có thể sử dụng nó trong bảng tính của mình ngay hôm nay.

Xem thêm: Hướng dẫn cách sử dụng DISM và SFC để khắc phục sự cố trên Windows 11

1. Gỡ lỗi công thức (Debugging Formulas)

Cách sử dụng đơn giản nhất của hàm FORMULATEXT là để kiểm toán công thức trực tiếp.

Giả sử bạn được gửi một bảng tính có nhiều lỗi #DIV/0!, #VALUE!, #REF!, #SPILL! và các lỗi khác. Việc chọn từng ô một để xem lại công thức trong thanh công thức rất tốn thời gian. Hơn nữa, việc phát hiện các mẫu lỗi là gần như không thể.

Thay vào đó, bạn có thể sử dụng FORMULATEXT kết hợp với IF và ISERROR trong một công thức duy nhất để chỉ hiển thị công thức của những ô phát hiện sự cố.

Để thực hiện điều này, hãy chọn ô trên cùng bên trái nơi bạn muốn bắt đầu kiểm toán công thức và nhập công thức sau (thay thế cả hai trường hợp A2:D19 bằng phạm vi bạn muốn kiểm toán trong bảng tính của mình):

=IF(ISERROR(A2:D19),FORMULATEXT(A2:D19),"")

Kết quả của công thức duy nhất này sẽ tự động tràn (spill) ra toàn bộ khu vực kiểm toán, chỉ trả về các công thức của các ô chứa lỗi.

Hiệu ứng tràn tự động này có sẵn trong Excel 2021 trở lên, Excel cho Microsoft 365, Excel cho web, và các ứng dụng Excel trên máy tính bảng và thiết bị di động. Trong các phiên bản cũ hơn, hãy nhập công thức dưới dạng công thức mảng kế thừa bằng cách chọn tất cả các ô trong khu vực kiểm toán, gõ công thức vào ô được chọn đầu tiên, và nhấn Ctrl+Shift+Enter để cam kết công thức cho từng ô.

Khung nhìn song song này cho phép bạn xem lại công thức ngay lập tức và chẩn đoán nguyên nhân gốc rễ của các lỗi. Ngay khi bạn sửa chúng, các công thức sẽ biến mất khỏi khu vực kiểm toán. Hơn nữa, nó cho phép bạn dễ dàng phát hiện xem cùng một logic bị lỗi có được áp dụng cho nhiều ô hay không.

2. Ghi chép công thức chính (Documenting Key Formulas)

Bạn có thể sử dụng FORMULATEXT để tạo một thư viện chứa tất cả các công thức quan trọng trong sổ làm việc của mình. Điều này mang lại ba lợi ích chính:

• Cải thiện khả năng hiểu: Nó cung cấp một vị trí trung tâm nơi bất kỳ ai cũng có thể đến để hiểu logic cốt lõi của sổ làm việc.

• Chuyển giao công việc hợp lý: Nếu người khác được giao nhiệm vụ quản lý sổ làm việc, thư viện công thức sẽ đóng vai trò là tài liệu hữu ích về cách mọi thứ hoạt động.

• Lưới an toàn công thức: Bạn có thể định kỳ sao chép tất cả các công thức quan trọng vào một trang tính khác (dán dưới dạng giá trị) hoặc tài liệu để lưu giữ an toàn. Thêm dấu ngày tháng vào các công thức đã dán để tạo lịch sử về cách một công thức đã phát triển theo thời gian.

Việc tạo khóa công thức (formula key) rất đơn giản, chỉ cần hai cột trên một trang tính chuyên dụng: một nhãn công thức (formula label) ở Cột A và văn bản công thức ở Cột B.

Bây giờ, hãy đi qua từng trang tính, và mỗi khi bạn gặp một ô chứa công thức quan trọng – chẳng hạn như công thức tính tổng lợi nhuận gộp cuối cùng trên trang tính Bán hàng của bạn – hãy nhập nhãn công thức vào Cột A, và ở Cột B, gõ:

=FORMULATEXT(

Sau đó, chọn ô quan trọng đó, đóng dấu ngoặc đơn và nhấn Enter.

Cuối cùng, lặp lại quy trình cho tất cả các công thức quan trọng khác trong sổ làm việc của bạn.

Định dạng thư viện công thức chính của bạn dưới dạng bảng Excel (Excel table). Bằng cách đó, mỗi khi bạn gõ một nhãn công thức trên một hàng mới ở Cột A, bảng sẽ tự động mở rộng để nắm bắt mục bổ sung mới nhất, lưu trữ chỉ mục của bạn trong một đối tượng duy nhất, có thể đặt tên.

3. Kiểm tra tính nhất quán của công thức (Checking Formula Consistency)

Nếu bạn quản lý một tập dữ liệu lớn, bạn sẽ biết rằng tính nhất quán của công thức theo một cột là một trong những vấn đề kiểm soát chất lượng lớn nhất của bạn.

Ví dụ, nếu ai đó vô tình ghi đè công thức bằng một số được mã hóa cứng (hard-coded number) hoặc một công thức khác, vì ô đó trông có vẻ có thông tin chính xác, lỗi sẽ không bị phát hiện. Trong bảng tính này, mặc dù các ô J8, J11 và J14 trông có vẻ hợp lệ thoạt nhìn, nhưng chúng không chứa cùng một cơ chế nền tảng như các ô khác trong cột J.

Khi được sử dụng cùng với định dạng có điều kiện (conditional formatting), hàm FORMULATEXT sẽ giúp bạn giải quyết vấn đề này.

Đầu tiên, hãy đảm bảo công thức trong ô đầu tiên của cột là chính xác, vì đây sẽ được sử dụng làm ô neo tuyệt đối để xác minh tính nhất quán của phần còn lại của cột. Tiếp theo, chọn cột bạn muốn kiểm tra (trừ ô neo) bằng cách chọn ô thứ hai và nhấn Ctrl+Shift+Mũi tên xuống. Sau đó, trong tab Home (Trang chủ), nhấp vào Conditional Formatting (Định dạng có điều kiện) > New Rule (Quy tắc mới).

Bây giờ, nhấp vào “Use A Formula To Determine Which Cells To Format” (Sử dụng công thức để xác định ô nào sẽ định dạng), và nhập công thức sau (giả sử bạn muốn kiểm tra Cột J):

=IF(NOT(ISFORMULA(J3)),TRUE,IF(NOT(ISFORMULA($J$2)),FALSE,FORMULATEXT(J3)<>FORMULATEXT($J$2)))

Trong đó:

• IF(NOT(ISFORMULA(J3)),TRUE  kiểm tra xem ô hiện tại có phải là giá trị được mã hóa cứng hay không. Nếu có (TRUE), tất cả các kiểm tra khác sẽ bị bỏ qua và ô trả về TRUE ngay lập tức.

• IF(NOT(ISFORMULA($J$2)),FALSE kiểm tra xem ô neo có phải là giá trị được mã hóa cứng hay không. Nếu có, quá trình sẽ dừng (FALSE).

• FORMULATEXT(J3)<>FORMULATEXT($J$2)) kiểm tra xem ô hiện tại có chứa công thức khác với ô neo (J2) hay không. Nếu có, nó trả về TRUE.

Vì tham chiếu đến ô J3 là tương đối, quy tắc định dạng có điều kiện sẽ điều chỉnh cho mỗi hàng. Mặt khác, ô neo là tuyệt đối (do đó có dấu đô la), vì vậy nó luôn đóng vai trò là điểm so sánh.

Tiếp theo, nhấp vào “Format” (Định dạng) và chọn tô màu ô bằng màu vàng để làm nổi bật sự không nhất quán.

Cuối cùng, nhấp vào “OK” hai lần để đóng cả hai hộp thoại và xem kết quả.

Nếu ô neo là giá trị được mã hóa cứng, sẽ không có ô nào được tô sáng, vì vậy bạn có nguy cơ hiểu rằng cột không có lỗi thay vì kiểm tra đã bị hủy bỏ. Đây là lý do tại sao điều quan trọng là bạn phải kiểm tra kỹ độ chính xác của ô neo.

Ngay sau khi bạn sửa công thức trong một ô được tô sáng, nó sẽ mất định dạng – và, bạn sẽ có một bảng tính nhất quán, không có lỗi.

Excel cung cấp nhiều cách để kiểm toán bảng tính của bạn. Một trong những công cụ yêu thích của tôi là Go To Special (Đi tới đặc biệt), cho phép bạn nhanh chóng chọn và chỉnh sửa tất cả các ô thuộc một loại cụ thể – chẳng hạn như tất cả các ô chứa công thức, hằng số hoặc định dạng có điều kiện. Tuy nhiên, công cụ tích hợp sẵn này chỉ cho bạn biết vị trí của các công thức. Bằng cách coi công thức là một chuỗi văn bản, hàm FORMULATEXT đã chuyển từ một công cụ xem đơn giản thành một công cụ kiểm soát chất lượng mạnh mẽ.

4. Kết luận

Việc phải kiểm tra từng công thức trong Excel không chỉ mất thời gian mà còn dễ dẫn đến sai sót, đặc biệt khi bạn đang xử lý bảng tính phức tạp hoặc dữ liệu có số lượng lớn. Nhờ hàm được giới thiệu trong bài viết, bạn có thể tự động phát hiện vấn đề, rút ngắn thao tác thủ công và đảm bảo độ chính xác cao hơn nhiều lần. Khi biết cách tận dụng những hàm mạnh mẽ này, bạn sẽ làm chủ Excel theo hướng thông minh và hiệu quả hơn, biến những tác vụ vốn tốn hàng giờ thành việc chỉ mất vài giây.

Xem thêm: Hướng dẫn cách sử dụng các phím chức năng trong Microsoft Excel

Nếu bạn muốn nâng cấp trải nghiệm làm việc của mình với laptop mạnh mẽ, màn hình chuẩn màu, bàn phím gõ sướng hoặc cần thêm phụ kiện công nghệ hỗ trợ công việc văn phòng, hãy ghé COHOTECH – nơi cung cấp thiết bị chính hãng, giá hợp lý và tư vấn chuyên nghiệp. COHOTECH luôn sẵn sàng đồng hành cùng bạn để tối ưu hiệu suất làm việc mỗi ngày.

Hãy để lại bình luận bên dưới nếu bạn có thêm mẹo Excel hay muốn chia sẻ trải nghiệm của mình. Đừng quên chia sẻ bài viết để nhiều người cùng biết cách đơn giản hóa việc kiểm tra công thức trong Excel!