VietNetwork.Vn

Chức năng đơn giản này hoạt động như một mạng lưới an toàn quan trọng cho sổ làm việc của bạn.

Hàm FORMULATEXT thường bị bỏ qua vì nó chỉ đơn thuần chuyển đổi công thức thành văn bản. Tuy nhiên, chính văn bản thô đó mới là thứ làm nên giá trị của nó. Vì vậy, đừng nghĩ nó chỉ là một công cụ đơn điệu - nó là công cụ hoàn hảo cho việc kiểm tra, 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 dải ô.

Nếu đối số ref là một sổ làm việc khác, sổ làm việc đó phải đang 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.

Sau đâ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.

1. Công thức gỡ lỗi

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

Giả sử bạn nhận được một bảng tính đầy 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 trên thanh công thức rất tốn thời gian. Hơn nữa, việc phát hiện ra các quy luật trong lỗi gần như là không thể.


Thay vào đó, bạn có thể sử dụng FORMULATEXT 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 các ô phát hiện ra sự cố.

Để thực hiện việc 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 tra 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 tra trong bảng tính):

=IF(ISERROR(A2:D19),FORMULATEXT(A2:D19),"")
Kết quả của công thức đơn này sẽ tràn ra toàn bộ khu vực kiểm tra, chỉ trả về 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à ứng dụng Excel dành cho 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 cũ bằng cách chọn tất cả các ô trong vùng kiểm tra, nhập công thức vào ô đầu tiên được chọn và nhấn Ctrl+Shift+Enter để áp dụng công thức cho từng ô.

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

3. Ghi lại các công thức chính

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

    Hiểu biết tốt hơn: Cung cấp một vị trí trung tâm nơi bất kỳ ai cũng có thể tìm đến để hiểu logic cốt lõi của sổ làm việc.
    Chuyển giao hợp lý: Nếu giao cho người khác 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 thức hoạt động của mọi thứ.
    Lưới an toàn công thức: Bạn có thể sao chép định kỳ tất cả các công thức chính vào một bảng tính khác (dán giá trị) hoặc tài liệu để lưu trữ. Đóng dấu ngày tháng vào các công thức đã dán để tạo lịch sử phát triển của công thức theo thời gian.

Việc tạo khóa công thức rất đơn giản, chỉ bao gồm hai cột trên một bảng tính chuyên dụng: nhãn công thức ở cột A và văn bản công thức ở cột B.


Bây giờ, hãy xem qua từng bảng tính và mỗi khi bạn nhấn vào ô chứa công thức quan trọng—chẳng hạn như phép tính lợi nhuận gộp cuối cùng trên bảng tính Doanh số của bạn—hãy nhập nhãn công thức vào cột A và vào cột B, hãy nhập:

=FORMULATEXT(
Sau đó, chọn ô chính, đóng dấu ngoặc đơn và nhấn Enter.


Cuối cùng, lặp lại quy trình này cho tất cả các công thức chính khác trong bảng tính 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. Bằng cách đó, mỗi khi bạn nhập nhãn công thức vào một hàng mới trong cột A, bảng sẽ mở rộng để ghi lại phần bổ sung mới nhất, chứa chỉ mục của bạn trong một đối tượng duy nhất có thể đặt tên.

4. Kiểm tra tính nhất quán của công thức

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 trong một cột là một trong những vấn đề kiểm soát chất lượng gây đau đầu nhất.

Ví dụ, nếu ai đó vô tình ghi đè công thức bằng một số được mã hóa cứng hoặc một công thức khác, vì ô đó dường như chứa 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 thoạt nhìn có vẻ hợp lệ, nhưng chúng không chứa cùng cơ chế cơ bản như các ô khác trong cột J.


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

Trước 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ì ô này sẽ được sử dụng làm ô neo tuyệt đối để bạn kiểm tra 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 Trang chủ, nhấp vào Định dạng có Điều kiện > Quy tắc Mới.


Bây giờ, hãy nhấp vào "Sử dụng công thức để xác định ô nào cần đị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 là (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 lại (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ó, hàm 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 từng 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 "Định dạng" và chọn ô 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à một giá trị được mã hóa cứng, sẽ không có ô nào được tô sáng, do đó bạn có nguy cơ hiểu cột là không có lỗi thay vì việc kiểm tra đã bị hủy bỏ. Đây là lý do tại sao việc kiểm tra lại ô neo để đảm bảo tính chính xác là rất quan trọng.

Ngay khi bạn sửa công thức trong ô được tô sáng, định dạng của ô đó sẽ bị mất và trước khi bạn nhận ra, 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 tra bảng tính của bạn. Một trong những cách yêu thích của tôi là công cụ Go To Special, 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 này chỉ cho bạn biết vị trí của các công thức. Bằng cách xử lý công thức dưới dạng chuỗi văn bản, hàm FORMULATEXT biến đổi từ một trình xem đơn giản thành một công cụ kiểm soát chất lượng mạnh mẽ.