Hàm ISFORMULA Excel 2013 Giúp Phát Hiện Công Thức Ẩn Trong 3 Giây

Kiểm tra từng ô trong bảng tính 500 dòng để tìm công thức nào đó mất cả buổi sáng. Bạn click vào từng ô, nhìn thanh công thức, ghi chép lại vị trí. File Excel từ đồng nghiệp gửi sang có những ô trông giống nhau nhưng không biết ô nào tính tự động, ô nào nhập tay. Hàm ISFORMULA trong Excel 2013 giải quyết vấn đề này chỉ trong vài giây.

Mục Lục

Vấn đề với kiểm tra công thức thủ công

Khi nhận file Excel từ người khác, cách duy nhất để biết ô nào có công thức là click vào từng ô và kiểm tra thanh công thức phía trên. Với bảng tính nhỏ 20-30 ô thì không sao, nhưng báo cáo hàng tháng có 2000 ô thì câu chuyện khác.

Một số người dùng thủ thuật ẩn công thức hoặc khóa sheet để bảo vệ logic tính toán. Khi đó thanh công thức không hiển thị gì cả. Bạn nhìn số 1500 trong ô B5 nhưng không biết đó là kết quả từ công thức tính tổng hay ai đó gõ tay con số này vào.

Hệ quả trực tiếp là báo cáo sai số. Giả sử ô D10 cần tính tổng doanh thu nhưng ai đó vô tình xóa công thức và gõ số cũ vào. Bảng tính vẫn chạy bình thường, không có lỗi nào, nhưng dữ liệu đã sai từ tuần trước mà không ai phát hiện.

Chi phí thật sự của việc kiểm tra thủ công

Với file Excel 50 sheet và mỗi sheet trung bình 300 ô dữ liệu, tổng cộng 15000 ô cần kiểm tra. Click và kiểm tra mỗi ô tốn 2 giây. Tính toán đơn giản: 15000 nhân 2 giây bằng 30000 giây, tương đương 8.3 giờ làm việc thuần túy chỉ để click chuột.

Audit báo cáo tài chính cuối quý thường mất 2 ngày đầy đủ chỉ để verify các công thức còn nguyên vẹn. Kế toán trưởng một công ty 200 người chia sẻ rằng team của họ dành 16 giờ mỗi tháng chỉ để kiểm tra công thức trong file lương. Con số này không tính thời gian fix lỗi khi phát hiện.

Tệ hơn nữa là sai sót không được phát hiện. Một công thức bị xóa nhầm trong ô tính thuế có thể dẫn đến báo cáo sai hàng nghìn đô la. Phát hiện sau 3 tháng nghĩa là phải audit lại toàn bộ dữ liệu quý đó.

Hàm ISFORMULA xác định công thức tức thì

ISFORMULA xuất hiện từ Excel 2013 với mục đích duy nhất: kiểm tra xem một ô có chứa công thức hay không. Hàm này trả về TRUE nếu ô đó có công thức, FALSE nếu là giá trị nhập tay hoặc ô trống.

Cú pháp đơn giản chỉ có một tham số:

=ISFORMULA(tham_chiếu_ô)

Trong đó tham chiếu ô là địa chỉ ô bạn muốn kiểm tra. Ví dụ để kiểm tra ô A1 có công thức không, gõ vào ô bất kỳ công thức này:

=ISFORMULA(A1)

Kết quả hiển thị TRUE nếu A1 chứa bất kỳ công thức nào, kể cả công thức đơn giản như =5+3 hay phức tạp như =VLOOKUP. Kết quả FALSE nếu A1 chứa số nhập tay, văn bản, hoặc để trống.

XEM THÊM: 5 Cách Hàm INFO Trong Excel Giúp Tôi Tiết Kiệm 2 Giờ Mỗi Tuần

Lưu ý quan trọng: ISFORMULA chỉ hoạt động với tham chiếu ô. Bạn không thể gõ =ISFORMULA(500) hay =ISFORMULA(“văn bản”). Nếu làm vậy hàm trả về lỗi VALUE!.

Ngay cả khi công thức trong ô bị lỗi như DIV/0! hay N/A, ISFORMULA vẫn trả về TRUE vì bản chất ô đó vẫn chứa công thức. Hàm này không quan tâm kết quả tính toán ra sao, chỉ quan tâm ô có phải công thức hay không.

Tô màu tự động ô chứa công thức

Ứng dụng mạnh nhất của ISFORMULA là kết hợp với Conditional Formatting để tô màu tất cả ô có công thức trong một lần thao tác. Thay vì kiểm tra từng ô, bạn nhìn trực quan toàn bộ bảng tính với màu sắc phân biệt rõ ràng.

Giả sử bạn có bảng dữ liệu từ A1 đến H20 và muốn tô màu vàng tất cả ô chứa công thức. Quy trình thực hiện như sau:

Bước 1: Bôi đen toàn bộ vùng cần kiểm tra, từ A1 đến H20.

Bước 2: Vào tab Home trên thanh ribbon, click vào nút Conditional Formatting trong nhóm Styles, chọn New Rule từ menu xuất hiện.

Bước 3: Trong hộp thoại New Formatting Rule, chọn dòng cuối cùng: Use a formula to determine which cells to format.

Bước 4: Trong ô công thức phía dưới, gõ công thức sau:

=ISFORMULA(A1)

Lưu ý quan trọng: Dù bạn bôi đen từ A1 đến H20, công thức chỉ tham chiếu A1 vì đây là ô đầu tiên trong vùng chọn. Excel tự động điều chỉnh công thức cho các ô khác. Ô A2 sẽ kiểm tra =ISFORMULA(A2), ô B5 sẽ kiểm tra =ISFORMULA(B5), và cứ thế.

Bước 5: Click nút Format, chuyển sang tab Fill, chọn màu vàng hoặc màu bất kỳ bạn muốn dùng để highlight. Click OK.

Bước 6: Click OK lần nữa để đóng hộp thoại New Formatting Rule.

Ngay lập tức, tất cả ô trong vùng A1:H20 có công thức được tô màu vàng. Ô nào là số nhập tay hoặc văn bản thì giữ nguyên màu trắng. Toàn bộ thao tác mất không quá 30 giây.

Với bảng tính 500 dòng và 20 cột tức là 10000 ô, phương pháp này cho kết quả tức thì. So với kiểm tra thủ công mất 5.5 giờ, Conditional Formatting với ISFORMULA tiết kiệm 99.8 phần trăm thời gian.

Audit file Excel nhanh chóng

Khi nhận file Excel từ bên ngoài, việc đầu tiên là kiểm tra xem các con số trong báo cáo có được tính tự động hay nhập tay. ISFORMULA giúp phát hiện điều này ngay lập tức.

Một trường hợp thường gặp là file báo cáo tháng có cột tổng doanh thu. Tháng 1 đến tháng 11 đều dùng công thức =SUM(B2:B50) để tính tổng. Nhưng tháng 12 ai đó vô tình xóa công thức và gõ tay số tổng của tháng 11 vào. Báo cáo tháng 12 sai nhưng không ai biết.

Tạo một cột phụ bên cạnh cột tổng, gõ công thức =ISFORMULA(D2) kéo xuống hết tháng 12. Tháng nào có công thức sẽ hiển thị TRUE, tháng 12 hiển thị FALSE. Phát hiện sai sót trong 10 giây.

Đối với file lương 200 nhân viên, mỗi dòng có 15 cột tính toán khác nhau như lương cơ bản, phụ cấp, khấu trừ bảo hiểm, thuế thu nhập. Tạo cột kiểm tra tại cột cuối cùng với công thức:

=COUNTIF(B2:P2,TRUE)

Kết hợp với mảng công thức để kiểm tra từng ô:

=SUMPRODUCT(--ISFORMULA(B2:P2))

Công thức này đếm có bao nhiêu ô từ B2 đến P2 chứa công thức. Nếu kết quả là 15 thì hoàn hảo, tất cả ô đều là công thức. Nếu kết quả là 13 hoặc 14 nghĩa là có ô bị xóa công thức và cần kiểm tra lại.

XEM THÊM: Cách Tôi Dùng Hàm CELL Để Tự Động Hóa Báo Cáo Excel Trong 5 Phút

Áp dụng công thức này cho 200 dòng nhân viên, kéo xuống một lần, quét toàn bộ file trong 2 giây. Bất kỳ dòng nào có số khác 15 đều cần xem xét kỹ.

Kết hợp với hàm IF để hiển thị cảnh báo

Thay vì chỉ hiển thị TRUE hay FALSE, kết hợp ISFORMULA với hàm IF để tạo thông báo rõ ràng hơn. Công thức này giúp người không chuyên Excel vẫn hiểu được vấn đề.

=IF(ISFORMULA(B5),"Công thức OK","Cảnh báo: Nhập tay")

Khi áp dụng cho toàn bộ bảng tính, bạn có cột trạng thái hiển thị “Công thức OK” màu xanh hoặc “Cảnh báo: Nhập tay” màu đỏ. Người xem file ngay lập tức nhìn thấy dòng nào có vấn đề mà không cần hiểu TRUE/FALSE nghĩa là gì.

Nâng cao hơn, kiểm tra nhiều ô cùng lúc và đưa ra cảnh báo chi tiết:

=IF(SUMPRODUCT(--ISFORMULA(B2:F2))=5,"Tất cả ô đều công thức","Thiếu "&(5-SUMPRODUCT(--ISFORMULA(B2:F2)))&" công thức")

Công thức này kiểm tra 5 ô từ B2 đến F2. Nếu cả 5 ô đều là công thức, hiển thị “Tất cả ô đều công thức”. Nếu chỉ có 3 ô là công thức, hiển thị “Thiếu 2 công thức”. Thông tin cụ thể giúp xác định vấn đề nhanh hơn.

Đối với báo cáo quan trọng, thêm cột validation ở đầu mỗi sheet. Cột này kiểm tra tất cả công thức quan trọng và hiển thị “PASS” hoặc “FAIL”. Trước khi gửi báo cáo cho sếp, chỉ cần nhìn cột validation, thấy toàn “PASS” là yên tâm.

Tìm ô công thức nhanh với Go To Special

Ngoài ISFORMULA, Excel có tính năng Go To Special giúp chọn tất cả ô chứa công thức trong một sheet. Nhấn F5 hoặc Ctrl+G để mở hộp thoại Go To, click nút Special ở góc dưới bên trái.

Trong hộp thoại Go To Special, chọn Formulas và bỏ chọn các ô check Numbers, Text, Logicals, Errors nếu chỉ muốn tìm công thức số. Click OK.

Tất cả ô có công thức trong sheet hiện tại được bôi đen. Bây giờ có thể tô màu nền cho các ô này hoặc copy sang sheet khác để phân tích. Phương pháp này nhanh với file nhỏ nhưng không linh hoạt bằng ISFORMULA khi cần kiểm tra tự động hoặc tạo báo cáo.

Điểm khác biệt chính: Go To Special là thao tác thủ công mỗi lần cần kiểm tra, ISFORMULA là công thức tự động update khi dữ liệu thay đổi. File được mở lại sau một tháng, công thức ISFORMULA vẫn kiểm tra đúng, còn Go To Special cần làm lại từ đầu.

Phát hiện công thức ẩn trong sheet được bảo vệ

Một số người khóa sheet và ẩn công thức để bảo vệ logic tính toán. Khi ô được chọn, thanh công thức chỉ hiển thị kết quả mà không hiển thị công thức bên trong. ISFORMULA vẫn hoạt động bình thường trong trường hợp này.

Thử nghiệm với sheet bị khóa: Tạo file mới, gõ công thức =5*10 vào ô A1. Chuột phải vào ô A1, chọn Format Cells, vào tab Protection, tick vào ô Hidden. Sau đó vào tab Review, click Protect Sheet, đặt mật khẩu.

Bây giờ khi click vào A1, thanh công thức chỉ hiển thị số 50 mà không hiển thị =5*10. Nhưng gõ =ISFORMULA(A1) vào ô B1, kết quả vẫn là TRUE. Hàm ISFORMULA không bị ảnh hưởng bởi protection hay hidden setting.

XEM THÊM: Hàm ISLOGICAL Trong Excel: 5 Sai Lầm Tôi Từng Mắc Phải Và Cách Khắc Phục

Điều này hữu ích khi audit file từ vendor hoặc consultant. Họ gửi file có nhiều tính toán phức tạp nhưng ẩn công thức. Dùng ISFORMULA để xác định ô nào có logic tính toán, ô nào là input data. Từ đó yêu cầu giải thích các công thức quan trọng hoặc mở khóa sheet nếu cần thiết.

Hạn chế và lưu ý khi dùng ISFORMULA

ISFORMULA chỉ hỗ trợ từ Excel 2013 trở đi. Nếu công ty đang dùng Excel 2010 hoặc 2007, hàm này không tồn tại. Khi mở file có ISFORMULA trong Excel 2010, công thức hiển thị lỗi NAME? vì Excel không nhận diện tên hàm.

Giải pháp cho Excel cũ là dùng VBA macro hoặc các add-in bên thứ ba. Nhưng phương pháp này phức tạp hơn nhiều và yêu cầu kiến thức lập trình. Cách tốt nhất là nâng cấp lên Excel 2013 hoặc Microsoft 365 để có đầy đủ tính năng.

Hàm không phân biệt loại công thức. Cho dù ô chứa công thức đơn giản =A1+B1 hay phức tạp =IFERROR(VLOOKUP(…), ISFORMULA đều trả về TRUE. Nếu cần biết chính xác công thức là gì, dùng thêm hàm FORMULATEXT.

FORMULATEXT cũng xuất hiện từ Excel 2013, trả về nội dung công thức dưới dạng văn bản. Ví dụ gõ =FORMULATEXT(A1) sẽ hiển thị “=SUM(B1:B10)” nếu đó là công thức trong A1. Kết hợp cả hai hàm cho kết quả toàn diện:

=IF(ISFORMULA(A1),FORMULATEXT(A1),"Không có công thức")

Công thức này kiểm tra A1 có công thức không. Nếu có thì hiển thị nội dung công thức, nếu không thì báo “Không có công thức”. Cột kiểm tra này giúp document lại toàn bộ logic tính toán trong file Excel phức tạp.

Tăng hiệu suất kiểm tra file lớn

Với file Excel 100MB có 50 sheet và mỗi sheet 10000 dòng, việc áp dụng Conditional Formatting cho toàn bộ file có thể làm chậm Excel. Cách tối ưu là chỉ format các vùng quan trọng cần kiểm tra thường xuyên.

Ví dụ trong báo cáo tài chính, các cột tổng cộng, tổng doanh thu, lợi nhuận ròng là những ô quan trọng nhất. Chỉ áp dụng Conditional Formatting với ISFORMULA cho các cột này thay vì toàn bộ sheet. Giảm số lượng ô cần format từ 10000 xuống còn 500 ô, tốc độ mở file cải thiện đáng kể.

Một kỹ thuật khác là tạo sheet riêng tên “Validation” chứa tất cả công thức kiểm tra. Sheet này có các công thức ISFORMULA tham chiếu đến các sheet dữ liệu. Khi cần audit, chỉ mở sheet Validation và xem kết quả, không cần format trực tiếp trên sheet dữ liệu.

Sheet Validation có thể có cấu trúc như sau:

Cột A: Tên sheet cần kiểm tra
Cột B: Địa chỉ ô quan trọng
Cột C: Công thức =ISFORMULA(Sheet1!D10)
Cột D: Kết quả PASS/FAIL

Khi file có vấn đề, nhìn vào sheet Validation là biết ngay ô nào, sheet nào có lỗi. Phương pháp này đặc biệt hiệu quả cho file được nhiều người cùng chỉnh sửa, giúp theo dõi tính toàn vẹn dữ liệu theo thời gian.

Kết quả sau khi áp dụng ISFORMULA

Thời gian audit file Excel giảm từ 8 giờ xuống còn 15 phút. Conditional Formatting với ISFORMULA tô màu toàn bộ công thức trong vài giây, nhìn trực quan thay vì click từng ô. Phát hiện lỗi công thức bị xóa nhầm trước khi báo cáo gửi đi, tránh được sai sót nghiêm trọng trong số liệu tài chính.

Hàm này hỗ trợ từ Excel 2013 trở lên và hoạt động với mọi loại công thức, kể cả công thức ẩn trong sheet được bảo vệ. Kết hợp với Conditional Formatting để tô màu trực quan hoặc với IF để hiển thị cảnh báo rõ ràng. Đối với người thường xuyên làm việc với file Excel từ nhiều nguồn khác nhau, ISFORMULA là công cụ không thể thiếu để đảm bảo tính toàn vẹn dữ liệu.