Hướng dẫn cách lồng ghép hàm, viết hàm lồng nhau trong Excel

By exel.tailieu247.edu.vn On Th6 27, 2023

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

Có 2 trường hợp chính thường xảy ra tình trạng lồng hàm:

Trường hợp 1: Nesting function để tránh lỗi cho main function
Trường hợp 2: Đảm bảo đủ logic của vấn đề

Chức năng lồng nhau để tránh lỗi cho chức năng chính

Đây thường là trường hợp với các chức năng được tham chiếu. Bởi vì tham chiếu rất dễ bị lỗi. Vì vậy, để tránh sai sót, chúng tôi tích hợp các hàm bẫy lỗi, hàm logic để tránh sai sót.

Một ví dụ phổ biến là việc sử dụng chức năng 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 dễ bị lỗi nếu:

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 tra cứu
Giá trị của 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

Vì vậy, để tránh sai sót, chúng ta thường thêm một số chức năng như:

Hàm IF + Hàm IsBlank để tranh luận nếu giá trị tìm kiếm là một ô trống
Hàm COUNTIF để đếm nếu giá trị tìm kiếm nằm trong cột đầu tiên của không gian bảng tra cứu
Hàm MATCH để xác định cột chứa kết quả là cột nào trong bảng

Vì vậy, mục đích chính là sử dụng hàm VLOOKUP, nhưng để tránh lỗi của hàm này, có thể phải sử dụng nhiều hàm con để tích hợp vào hàm chính.

Ví dụ như sau:

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

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

>>> Xem thêm: Cách viết hàm VLOOKUP trả về nhiều giá trị ngăn cách bởi dấu phẩy

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

Tại G2 tích hợp hàm IF để xét:

Nếu việc đếm giá trị của F2 trong phạm vi A2:A8 cho kết quả bằng 0 (kết quả của hàm COUNTIF=0), văn bản “Không có mã này” sẽ được trả về.
Nếu kết quả của hàm COUNTIF khác 0 (nghĩa là giả sử hàm IF sai, ở value_if_false) sẽ sử dụng hàm VLOOKUP

Tại G3, hãy xem xét giả định rằng giá trị tìm kiếm là một ô trống

Nếu F3 là ô trống (mệnh đề F3=””), hãy trả về kết quả là ô trống (dấu nháy kép biểu thị ô trống).
Nếu F3 không phải là ô trống (nghĩa là giả định của hàm IF là sai, tại value_if_false) thì hàm VLOOKUP sẽ được sử dụng để tìm kết quả.

Tuy nhiên, nếu F2 là một ô trống thì sao? Còn giá trị trong F3 không phải là ô trống mà là giá trị không có trong cột A thì sao? Trong trường hợp này, chúng ta phải tích hợp bẫy lỗi ở cả G2 và G3 vào một chức năng 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 đó:

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

>>> Xem thêm: Cách chỉnh sửa, đánh giá và sửa lỗi công thức trong Excel

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

* Lưu ý: Ngoài các lỗi thường gặp, chúng ta cũng phải biết về lỗi Kiểu dữ liệu. Vì nếu không đúng kiểu dữ liệu thì chúng ta không thể tính toán chính xác được.

Tham khảo: Kiểu dữ liệu ảnh hưởng như thế nào đến kết quả của hàm trong Excel?

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

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

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

Hãy xem xét ví dụ sau:

Để thực hiện yêu cầu Rating, chúng ta cần phân tích 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ị “Đạt” hoặc “Không đạt” dựa vào điều kiện ở cột Điểm môn học và Điểm kiểm tra

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

logic 1: Nếu điểm thi Excel dưới 7 bạn sẽ trượt. Ở đây ta có: Điểm thi Excel phụ thuộc vào các giá trị ở cột Môn học (cột C) và cột Điểm thi (cột D). Hai điều kiện này được xem xét đồng thời nên cần sử dụng hàm AND để kết hợp hai nội dung này. Hàm IF để xét logic: nếu thỏa mãn đồng thời 2 điều kiện của logic 1 thì kết quả là “Không đạt”
logic 2: logic 2 sẽ thực hiện khi câu lệnh logic 1 sai. Do đó, sau khi xét câu đúng của logic 1, ta sẽ xét ngay logic 2. Logic 2 là tổ hợp của điều kiện Course = Word, điểm thi dưới 8 nên sẽ 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à “Không đạt”.
còn lại nếu cả 2 logic trên đều sai (nghĩa là câu lệnh sai ở logic 2 cũng là câu lệnh sai của logic 1) thì kết quả trả về không phải là “Không đạt” mà sẽ là “Đạt”.

>>> Xem thêm: Hướng dẫn cách mở userform bằng thao tác double click VBA Excel

Làm thế nào để viết một chức năng như thế này:

E2=IF(AND(C2=”Excel”,D2<7),"Không đạt",IF(AND(C2="Word",D2<8),"Không đạt","Đạt"))

Logic 1 = IF(AND(C2=”Excel”,D2<7),”Slip”,….xét logic 2 tại vị trí ký hiệu….mệnh đề value_if_false của hàm IF
Logic 2 =IF(AND(C2=”Word”,D2<8),"Fail","Pass")) ở đây đóng 2 dấu ngoặc kết thúc cho 2 hàm IF

Thẩm quyền giải quyết

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 hàm không chỉ liên quan đến các hàm IF, AND, OR mà xảy ra ở nhiều hàm. Thông thường chúng ta sử dụng hàm IF để diễn đạt logic của bài toán. Bạn có thể tham khảo một số ứng dụng tích phân hàm trong hàm SUMPRODUCT, SUMIFS, COUNTIFS dưới đây:

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

Cách tạo báo cáo tóm tắt hàng tháng với SUMIFS. chức năng

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

Nguồn : Blog hocexcel