Giải thích công thức Excel: tách tham số, nhận diện hàm và đọc lỗi cho đúng
Dán một công thức Excel vào, công cụ nhận diện hàm ngoài cùng trong bảy nhóm hàm phổ biến rồi tách từng tham số ra thành từng dòng riêng. Việc tách chịu được công thức lồng nhau và chuỗi có dấu phẩy bên trong, nên bạn thấy rõ tham số nào ứng với vị trí nào thay vì phải nhẩm đếm dấu phẩy bằng mắt.
Tính năng nổi bật
- Nhận diện bảy nhóm hàm: VLOOKUP, XLOOKUP, SUMIF cùng SUMIFS, COUNTIF cùng COUNTIFS, IF cùng IFS, TEXTJOIN và FILTER
- Kèm một câu mô tả việc hàm đó làm, hiển thị ngay cạnh tên hàm
- Tách tham số theo độ sâu ngoặc nên công thức lồng nhiều tầng vẫn tách đúng
- Bỏ qua dấu phân cách nằm trong dấu nháy, nên chuỗi chứa dấu phẩy không bị cắt nhầm
- Nhận cả dấu phẩy và dấu chấm phẩy làm dấu phân cách tham số
- Đánh số thứ tự từng tham số để bạn đối chiếu với cú pháp chuẩn của hàm
- Vẫn tách tham số ngay cả khi không nhận diện được hàm ngoài cùng
- Chạy hoàn toàn trong trình duyệt, công thức và tên vùng dữ liệu không gửi đi đâu
Vì sao đọc một công thức dài lại khó đến vậy
Công thức Excel viết trên một dòng duy nhất, không xuống dòng, không thụt lề, không có màu phân biệt tham số. Một công thức tra cứu lồng ba tầng có thể dài hơn hai trăm ký tự và chứa mười lăm dấu phẩy, trong đó vài dấu nằm trong chuỗi văn bản và vài dấu nằm trong hàm con. Khi bạn tiếp quản một tệp do người khác làm, việc đầu tiên phải làm là xác định đâu là hàm ngoài cùng và tham số thứ ba của nó bắt đầu từ đâu, mà làm việc đó bằng mắt trên một dòng chữ liền mạch thì rất dễ đếm sai. Công cụ này cắt sẵn công thức thành từng tham số đánh số, nên bạn đọc được cấu trúc trong vài giây. Nó không nói cho bạn biết công thức đúng hay sai về mặt nghiệp vụ, nhưng nó cho bạn thấy đúng thứ cần nhìn để tự phán đoán.
Lợi ích khi sử dụng
- Thấy ngay ranh giới từng tham số thay vì đếm dấu phẩy bằng mắt
- Công thức lồng nhiều tầng vẫn tách đúng vì thuật toán đếm theo độ sâu ngoặc
- Chuỗi văn bản chứa dấu phẩy không làm hỏng kết quả tách
- Dùng được cho cả công thức viết bằng dấu phẩy và bằng dấu chấm phẩy
- Công thức nội bộ của công ty không phải gửi lên bất kỳ dịch vụ nào
Cách đọc một công thức bằng công cụ
- 1Chép công thức từ thanh công thức của Excel hoặc Google Sheets, giữ nguyên dấu bằng ở đầu.
- 2Dán vào ô nhập, kết quả phân tích hiện ngay bên phải không cần bấm nút.
- 3Đọc tên hàm được nhận diện cùng câu mô tả để biết công thức đang làm việc gì.
- 4Đối chiếu từng tham số đã tách với cú pháp chuẩn của hàm, chú ý vị trí của vùng tính tổng và vùng điều kiện.
- 5Với các tham số là vùng dữ liệu, mở lại tệp gốc kiểm tra vùng đó có đúng bảng bạn nghĩ hay không.
Công cụ hoạt động bằng bảng tra cố định, không gọi mô hình ngôn ngữ
Cần nói rõ cơ chế để bạn biết trông đợi gì. Phần nhận diện hàm là một danh sách bảy mẫu, mỗi mẫu là một biểu thức chính quy khớp với tên hàm đứng ngay sau dấu bằng, kèm sẵn một câu mô tả viết tay. Không có bước gọi mô hình ngôn ngữ, không có bước gửi công thức lên máy chủ, và cũng không có bước hiểu ngữ nghĩa công thức của bạn. Hệ quả thứ nhất là kết quả luôn ổn định: cùng một công thức luôn cho cùng một lời giải thích, không bịa thêm. Hệ quả thứ hai là phạm vi hẹp: nếu hàm ngoài cùng là INDEX, MATCH, SUMPRODUCT, LET, LAMBDA hay bất kỳ hàm nào ngoài bảy nhóm trên, công cụ sẽ báo chưa nhận diện được hàm chính nhưng vẫn tách tham số cho bạn xem. Hệ quả thứ ba là câu mô tả nói về hàm nói chung chứ không nói về công thức cụ thể của bạn; nó không biết vùng D:D trong tệp của bạn chứa gì. Phần giá trị thực sự nằm ở việc tách tham số chứ không nằm ở câu mô tả.
Vì sao tách tham số bằng cách cắt theo dấu phẩy luôn sai
Nhiều người tách công thức bằng cách cắt chuỗi tại mọi dấu phẩy, và cách đó hỏng ngay với công thức thật. Lấy ví dụ một công thức tính tổng có điều kiện, trong đó điều kiện là một chuỗi chứa dấu phẩy như tên đơn vị, hoặc một tham số là hàm con cũng có dấu phẩy riêng. Cắt thô sẽ sinh ra những mảnh vô nghĩa. Công cụ này duyệt công thức theo từng ký tự và giữ hai biến trạng thái. Biến thứ nhất là độ sâu ngoặc, tăng lên khi gặp ngoặc mở và giảm khi gặp ngoặc đóng; chỉ những dấu phân cách ở độ sâu bằng không mới được coi là ranh giới tham số của hàm ngoài cùng. Biến thứ hai là trạng thái đang ở trong dấu nháy hay không; mọi dấu phân cách nằm trong dấu nháy đều bị bỏ qua. Nhờ hai điều kiện này, một công thức lồng ba tầng có chuỗi chứa dấu phẩy vẫn được tách đúng thành các tham số của tầng ngoài cùng, giữ nguyên hàm con thành một khối liền.
Bảy nhóm hàm và thứ tự tham số hay bị nhớ nhầm
VLOOKUP nhận bốn tham số theo thứ tự giá trị cần tra, vùng bảng, số thứ tự cột trả về, và kiểu dò tìm. Số thứ tự cột đếm trong phạm vi vùng bảng chứ không đếm từ cột A của trang tính, đây là lỗi kinh điển. XLOOKUP nhận giá trị cần tra, vùng chứa giá trị tra, vùng trả về, rồi các tham số tùy chọn gồm giá trị thay thế khi không tìm thấy, kiểu khớp và chiều tìm. SUMIF nhận vùng xét điều kiện, điều kiện, rồi vùng tính tổng ở vị trí cuối; SUMIFS thì ngược hẳn, vùng tính tổng đứng đầu tiên rồi mới tới các cặp vùng điều kiện và điều kiện. Đảo lộn hai thứ tự này là nguyên nhân của rất nhiều con số sai mà không báo lỗi. COUNTIF nhận vùng và điều kiện, COUNTIFS nhận các cặp vùng và điều kiện. IF nhận điều kiện, giá trị khi đúng, giá trị khi sai; IFS nhận các cặp điều kiện và giá trị, xét lần lượt từ trái sang. TEXTJOIN nhận dấu nối, cờ bỏ qua ô rỗng, rồi các phần cần ghép. FILTER nhận vùng dữ liệu, điều kiện lọc, và giá trị trả về khi không có dòng nào thỏa.
Dấu phân cách tham số phụ thuộc cài đặt vùng của máy
Một công thức chép từ máy đồng nghiệp dán sang máy bạn có thể báo lỗi ngay dù nội dung không sai chữ nào, và nguyên nhân thường là dấu phân cách. Excel không dùng cố định dấu phẩy mà dùng dấu phân cách danh sách lấy từ cài đặt vùng của hệ điều hành. Với cấu hình theo chuẩn Anh Mỹ, dấu phân cách là dấu phẩy và dấu thập phân là dấu chấm. Với nhiều cấu hình khác, trong đó có cấu hình hay được đặt trên máy dùng tiếng Việt, dấu thập phân là dấu phẩy nên dấu phân cách tham số chuyển thành dấu chấm phẩy. Vì vậy công cụ này chấp nhận cả hai dấu khi tách tham số. Khi chép công thức qua lại giữa hai máy khác cấu hình, cách xử lý nhanh là mở phần cài đặt vùng của hệ điều hành để xem dấu phân cách danh sách hiện là gì, rồi thay đồng loạt trong công thức. Google Sheets thì luôn dùng dấu phẩy khi ngôn ngữ tệp đặt theo chuẩn Anh Mỹ, nên đây cũng là chỗ hay vấp khi chuyển tệp giữa hai nền tảng.
Đọc mã lỗi Excel để biết nên sửa chỗ nào
Công cụ không chạy công thức nên không báo lỗi thay Excel, nhưng biết đọc mã lỗi giúp bạn khoanh vùng rất nhanh. Lỗi N/A nghĩa là hàm tra cứu không tìm thấy giá trị; nguyên nhân phổ biến là khoảng trắng thừa ở cuối chuỗi hoặc một bên là số còn bên kia là văn bản trông giống số. Lỗi REF nghĩa là công thức đang trỏ tới ô không còn tồn tại, thường xảy ra sau khi ai đó xóa cột. Lỗi VALUE nghĩa là kiểu dữ liệu không phù hợp, ví dụ đem cộng một ô chứa chữ. Lỗi NAME nghĩa là Excel không hiểu tên bạn viết, thường do gõ sai tên hàm hoặc dùng hàm chỉ có ở phiên bản mới hơn. Lỗi DIV chia cho không thì đúng như tên gọi. Lỗi NUM xuất hiện khi phép tính cho ra số quá lớn hoặc tham số không hợp lệ về mặt toán học. Riêng lỗi tràn vùng gặp ở các hàm mảng động như FILTER, nghĩa là kết quả cần nhiều ô để đổ ra nhưng vùng bên dưới đang có dữ liệu chắn.
Câu hỏi thường gặp (FAQ)
Công cụ có dùng trí tuệ nhân tạo để giải thích công thức không?
Không. Phần nhận diện dựa trên một bảng bảy mẫu hàm viết sẵn kèm câu mô tả cố định, còn phần tách tham số là thuật toán duyệt ký tự chạy ngay trong trình duyệt. Nhờ vậy kết quả luôn ổn định và không bao giờ bịa, nhưng đổi lại phạm vi chỉ gói trong bảy nhóm hàm và câu mô tả nói về hàm nói chung.
Vì sao công thức của tôi báo chưa nhận diện được hàm chính?
Vì hàm ngoài cùng của bạn không nằm trong bảy nhóm đã liệt kê, ví dụ INDEX, MATCH, SUMPRODUCT hay LET. Công cụ vẫn tách tham số để bạn đọc cấu trúc, nên phần hữu ích nhất vẫn dùng được. Lưu ý công cụ chỉ xét hàm đứng ngay sau dấu bằng, hàm con nằm bên trong không được xét.
Công cụ có tính ra kết quả của công thức không?
Không. Nó chỉ phân tích cấu trúc chứ không có bộ máy tính toán, cũng không đọc tệp Excel của bạn nên không biết vùng dữ liệu chứa gì. Muốn biết kết quả, bạn phải chạy trong Excel hoặc Google Sheets; công cụ này chỉ giúp bạn hiểu công thức đang định làm gì trước khi chạy.
SUMIF và SUMIFS khác nhau ở chỗ nào?
Khác ở vị trí vùng tính tổng. SUMIF đặt vùng xét điều kiện trước, điều kiện ở giữa, vùng tính tổng ở cuối. SUMIFS đặt vùng tính tổng lên đầu tiên rồi mới tới các cặp vùng điều kiện và điều kiện. Nhớ nhầm thứ tự này thường không sinh lỗi mà chỉ cho ra con số sai, nên rất khó phát hiện.
Vì sao VLOOKUP trả về sai cột dù tôi đếm đúng?
Vì số thứ tự cột trong VLOOKUP đếm bên trong vùng bảng đã chọn, không đếm từ cột A của trang tính. Nếu vùng bảng bắt đầu từ cột C thì cột C là số 1. Ngoài ra khi bạn chèn thêm cột vào giữa vùng, con số này không tự cập nhật, đây là lý do XLOOKUP an toàn hơn vì trỏ thẳng tới vùng trả về.
Lỗi N/A trong hàm tra cứu thường do đâu?
Ba nguyên nhân phổ biến. Thứ nhất, giá trị tra có khoảng trắng thừa ở đầu hoặc cuối. Thứ hai, một bên là số thật còn bên kia là văn bản trông giống số, hay gặp với mã khách hàng nhập từ hệ thống khác. Thứ ba, dùng kiểu dò tìm gần đúng trong khi dữ liệu chưa sắp xếp tăng dần.
Tại sao dán công thức từ máy khác thì Excel báo lỗi ngay?
Thường do dấu phân cách tham số. Excel dùng dấu phân cách danh sách lấy từ cài đặt vùng của hệ điều hành, nên máy này dùng dấu phẩy còn máy kia dùng dấu chấm phẩy. Hãy mở cài đặt vùng để xem dấu phân cách hiện tại rồi thay đồng loạt trong công thức. Công cụ này chấp nhận cả hai dấu khi tách tham số.
Dùng được cho Google Sheets không?
Được với các hàm có cùng tên và cùng thứ tự tham số, gồm cả bảy nhóm mà công cụ nhận diện. Cần lưu ý Google Sheets dùng dấu phẩy khi ngôn ngữ tệp đặt theo chuẩn Anh Mỹ, và một vài hàm mảng động có hành vi khác Excel, nên vẫn nên kiểm tra lại kết quả trên chính nền tảng bạn đang dùng.
Công thức lồng nhiều tầng có tách đúng không?
Có. Thuật toán đếm độ sâu ngoặc và chỉ cắt tại dấu phân cách nằm ở tầng ngoài cùng, nên mỗi hàm con được giữ nguyên thành một khối liền thay vì bị xé nhỏ. Nếu bạn cần đọc tiếp một hàm con, hãy chép riêng khối đó vào ô nhập rồi phân tích tiếp một lần nữa.
Công thức không bắt đầu bằng một hàm thì sao?
Với những công thức dạng phép tính trực tiếp như một ô cộng với kết quả một hàm, phần tách tham số có thể cho kết quả không như mong đợi vì thuật toán giả định công thức mở đầu bằng tên hàm rồi tới ngoặc mở. Trong trường hợp đó, hãy chép riêng phần hàm ra để phân tích, phần phép tính bên ngoài bạn tự đọc.
Lỗi tràn vùng ở hàm FILTER nghĩa là gì?
Hàm FILTER trả về nhiều dòng nên cần đổ kết quả ra một vùng ô liền kề. Nếu vùng bên dưới hoặc bên phải đang có dữ liệu chắn, Excel không đổ được và báo lỗi tràn. Cách xử lý là dọn trống vùng đích, hoặc chuyển công thức sang một khu vực rộng rãi hơn của trang tính.
Công thức tôi dán vào có bị lưu lại không?
Không. Việc nhận diện hàm và tách tham số đều chạy bằng JavaScript ngay trong trình duyệt, không có bước gửi dữ liệu lên máy chủ. Bạn có thể dán công thức chứa tên trang tính, tên vùng và điều kiện nội bộ của công ty mà không lo lộ thông tin.
Từ khóa liên quan
- giải thích công thức excel
- excel formula explainer
- cách đọc công thức excel dài
- tách tham số công thức excel
- hàm SUMIFS trong excel
- sự khác nhau giữa sumif và sumifs
- hàm VLOOKUP là gì
- vlookup và xlookup khác nhau
- hàm FILTER excel 365
- hàm TEXTJOIN
- hàm IFS trong excel
- lỗi #N/A trong excel
- lỗi #REF! trong excel
- lỗi #SPILL! là gì
- dấu phẩy hay chấm phẩy trong excel
- dấu phân cách tham số excel
- công thức excel lồng nhau
- hàm COUNTIFS
- giải thích hàm excel tiếng Việt
- công cụ đọc công thức excel online
