Khi viết công thức trong Excel thì không phải lúc nào chúng ta cũng viết duy nhất 1 hàm. Bởi thực tế thì các vấn đề thường khá phức tạp do đó 1 hàm không để bao quát được toàn bộ vấn đề. Việc viết hàm lồng ghép nhau có khó không? Bạn sẽ tìm được câu trả lời sau khi đọc xong bài viết này. Hãy cùng Học Excel Online tìm hiểu cách lồng ghép hàm, viết hàm lồng nhau trong Excel nào:

Có 2 trường hợp chính thường xảy ra việc lồng hàm:

• Trường hợp 1: Lồng ghép hàm để tránh lỗi cho hàm chính
• Trường hợp 2: Đảm bảo đủ tính logic của vấn đề

Lồng ghép hàm để tránh lỗi cho hàm chính

Đây là trường hợp thường xảy ra với các hàm tham chiếu. Bởi việc tham chiếu rất dễ xảy ra lỗi. Do đó để tránh lỗi thì chúng ta lồng ghép thêm các hàm bẫy lỗi, hàm logic để tránh lỗi.

Ví dụ thường thấy là việc sử dụng hàm Vlookup

Tham khảo: Lỗi #VALUE của hàm VLOOKUP, nguyên nhân và cách khắc phục

Hàm VLOOKUP rất dễ xảy ra lỗi nếu như:

• Giá trị tìm kiếm là ô trống
• Giá trị tìm kiếm không xuất hiện trong cột đầu tiên của vùng bảng tìm kiếm
• Giá trị col_index_num (cột chứa kết quả) vượt quá số cột hiện có của bảng tìm kiếm

Do đó để tránh lỗi thì chúng ta hay thêm một số hàm như:

• Hàm IF + hàm IsBlank để biện luận nếu giá trị tìm kiếm là ô trống
• Hàm COUNTIF để đếm xem giá trị tìm kiếm có nằm trong cột đầu tiên của vùng bảng tìm kiếm không
• Hàm MATCH để xác định cột chứa kết quả là cột thứ mấy trong bảng

Do đó mục đích chính là sử dụng hàm VLOOKUP, nhưng để tránh lỗi của hàm này thì có thể phải dùng tới rất nhiều hàm phụ để lồng ghép vào hàm chính.

Ví dụ như sau:

Trong hình trên chúng ta thấy tại ô G2 và G3 có sử dụng hàm VLOOKUP và xảy ra lỗi. Lỗi này được hiểu là:

• G2: Giá trị tìm kiếm ở ô F2 không xuất hiện trong cột A của vùng bảng A2:D8 => Lỗi không tìm thấy giá trị #N/A
• G3: Giá trị tìm kiếm ở ô F3 là ô trống => Lỗi không tìm thấy giá trị #N/A

Do đó để tránh báo kết quả lỗi #N/A thì chúng ta có thể thêm một số hàm để bẫy lỗi và phát hiện nguyên nhân gây ra lỗi

Tại G2 lồng ghép thêm hàm IF để xét:

• Nếu đếm giá trị F2 trong vùng A2:A8 ra kết quả bằng 0 (kết quả hàm COUNTIF=0) thì trả về dòng chữ “Không có mã này”
• Nếu kết quả hàm COUNTIF ra khác không (tức là trường hợp giả thiết hàm IF sai, tại value_if_false) sẽ sử dụng hàm VLOOKUP

Tại G3 xét giả thiết giá trị tìm kiếm là ô trống

• Nếu F3 là ô trống (mệnh đề F3=””) thì trả về kết quả là ô trống (hai dấu nháy kép thể hiện ô trống)
• Nếu F3 không phải ô trống (tức là trường hợp giả thiết hàm IF sai, tại value_if_false) sẽ sử dụng hàm VLOOKUP để tìm kết quả.

Tuy nhiên nếu F2 là ô trống thì sao? Giá trị ở F3 không phải ô trống nhưng là giá trị không có trong cột A thì sao? Trường hợp này chúng ta phải lồng ghép việc bẫy lỗi cả ở G2 và G3 vào 1 hàm như sau:

=IF(F2=””,””,IF(COUNTIF($A$2:$A$8,F2)=0,”Không có mã này”,VLOOKUP(F2,$A$2:$D$8,4,0)))

Trong đó:

• IF(F2=””,””, là xét trường hợp giá trị tìm kiếm là ô trống
• IF(COUNTIF($A$2:$A$8,F2)=0,”Không có mã này”, là xét trường hợp giá trị tìm kiếm không nằm ở cột đầu tiên trong bảng tìm kiếm. Hàm IF này nằm ở mệnh đề value_if_false của hàm IF đầu tiên để xét khi F2 không phải là ô trống
• VLOOKUP(F2,$A$2:$D$8,4,0) giá trị sẽ thực thi khi mà cả 2 giả thiết hàm IF đều sai. Hàm Vlookup nằm ở mệnh đề value_if_false của hàm IF thứ 2, mà hàm IF thứ 2 lại nằm trong mệnh đề value_if_false của hàm IF thứ 1 nên được hiểu là khi cả 2 mệnh đề đều sai.

Như vậy dù công thức nhìn có vẻ dài và phức tạp, lồng nhiều hàm IF vào nhau, nhưng thực chất thì chỉ đơn giản là sử dụng hàm VLOOKUP cộng thêm việc bẫy lỗi của hàm VLOOKUP mà thôi.

* Lưu ý: Ngoài các lỗi thường thấy, chúng ta còn phải biết về lỗi Loại dữ liệu. Bởi nếu không đúng loại dữ liệu thì chúng ta không thể tính toán đúng được.

Tham khảo: Loại dữ liệu ảnh hưởng thế nào tới kết quả của hàm trong Excel

Đảm bảo đủ tính logic của vấn đề

Việc đảm bảo đủ tính logic của vấn đề thường phức tạp hơn nhiều so với việc bẫy lỗi. Bởi việc phân tích đủ và chính xác tính logic của những trường hợp phức tạp khá khó thực hiện, ngay cả với những ai đã làm tốt Excel.

Do đó để làm quen với việc này, chúng ta nên hình thành 1 thói quen đó là phân tích logic của vấn đề trước khi thực hiện giải quyết vấn đề đó. Khi đã phân tích đúng và đủ tính logic của vấn đề thì chúng ta có thể xác định được đâu là điểm bắt đầu, đâu là điểm kết thúc. Từ đó chúng ta sẽ biết cần sử dụng hàm nào, lồng ghép các hàm theo thứ tự nào, khi nào thì hoàn thành công thức.

Xét ví dụ sau:

Để thực hiện yêu cầu Xếp loại, chúng ta cần phân tích tính logic của yêu cầu này trước khi viết hàm, công thức.

Mục tiêu: Điền kết quả vào cột Xếp loại là 2 giá trị “Đỗ” hoặc “Trượt” dựa theo điều kiện ở cột Khóa học và Điểm thi

Nội dung logic của điều kiện:

• Logic 1: Nếu điểm thi Excel dưới 7 là trượt. Tại đây ta có: Điểm thi môn Excel phụ thuộc vào giá trị ở cột Khóa học (cột C) và cột Điểm thi (cột D). Hai điều kiện này xét đồng thời do đó cần sử dụng hàm AND để kết hợp 2 nội dung này. Hàm IF để xét tính logic: nếu thỏa mãn đồng thời 2 điều kiện của logic 1 thì kết quả là “Trượt”
• Logic 2: logic 2 sẽ thực hiện khi mệnh đề logic 1 là sai. Do đó sau khi xét mệnh đề đúng của logic 1 xong thì chúng ta sẽ xét ngay logic 2. Logic 2 là kết hợp điều kiện Khóa học = Word, điểm thi dưới 8 nên sẽ dùng hàm AND. Logic 2 được biểu diễn dưới hàm IF để xét: nếu thỏa mãn đồng thời 2 điều kiện của logic 2 thì kết quả là “Trượt”
• Còn lại nếu cả 2 logic trên đều sai (tức là mệnh đề sai ở logic 2 cũng là mệnh đề sai của logic 1) thì kết quả trả về không phải là “Trượt”, khi đó sẽ là “Đỗ”

Cách viết hàm như sau:

E2=IF(AND(C2=”Excel”,D2<7),”Trượt”,IF(AND(C2=”Word”,D2<8),”Trượt”,”Đỗ”))

• Logic 1 = IF(AND(C2=”Excel”,D2<7),”Trượt”,….    xét logic 2 tại vị trí dấu …. mệnh đề value_if_false của hàm IF
• Logic 2 =IF(AND(C2=”Word”,D2<8),”Trượt”,”Đỗ”)) tại đây đóng 2 ngoặc để kết thúc cho 2 hàm IF

Tham khảo

Cách tính hoa hồng bán hàng bằng hàm IF lồng nhau trên Excel

Việc lồng ghép hàm không chỉ liên quan tới các hàm IF, AND, OR mà xảy ra trong rất nhiều hàm. Thông thường chúng ta hay dùng tới hàm IF để diễn đạt tính logic của vấn đề mà thôi. Các bạn có thể tham khảo một vài ứng dụng của việc lồng ghép hàm trong các hàm SUMPRODUCT, SUMIFS, COUNTIFS dưới đây:

So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo nhiều điều kiện

Cách lập báo cáo tổng hợp theo từng tháng với hàm SUMIFS

Trên đây là một số nội dung cơ bản trong việc sử dụng hàm, viết hàm lồng nhau trong Excel. Để có thể tìm hiểu thêm nhiều kiến thức cũng như sử dụng Excel một cách có hệ thống, các bạn có thể tham gia khóa học Excel từ cơ bản tới chuyên gia của hệ thống Học Excel Online. Khóa học này sẽ cung cấp cho các bạn đầy đủ kiến thức về các hàm, công cụ trong Excel và việc ứng dụng trong công việc. Để đăng ký khóa học này các bạn bấm vào hình dưới đây: