Nếu được hỏi bạn sử dụng hàm cụ thể nào trong Excel nhất, bạn sẽ trả lời như thế nào? Đối với nhiều người, đó là sử dụng hàm IF trong Excel. Cùng 123job tìm hiểu thêm về chức năng này trong bài viết sau nhé!
Công thức hàm IF trong Excel nếu chỉ có một điều kiện sẽ rất dễ viết. Nhưng nếu dữ liệu của bạn cần được kiểm tra bởi khá nhiều thuật toán phức tạp với rất nhiều điều kiện khác nhau thì sao? Trong trường hợp này, bạn có thể sử dụng nhiều hàm IF trong Excel trong một công thức và công thức hàm IF trong Excel hiện được gọi là hàm IF lồng nhau hoặc hàm IF đa điều kiện. Ưu điểm lớn nhất của hàm IF lồng nhau là nó sẽ cho phép bạn kiểm tra nhiều điều kiện và nhận được từ chúng các giá trị khác nhau tùy thuộc vào kết quả kiểm tra của Microsoft Excel bằng một công cụ duy nhất. thức tỉnh.
I. Cách sử dụng hàm IF trong Excel với nhiều điều kiện
1. Ví dụ 1. Cách sử dụng hàm IF đa điều kiện và hàm AND trong Excel
Giả sử bạn có một bảng dữ liệu với kết quả của hai điểm kiểm tra trong một trang tính Excel. Điểm đầu tiên, được lưu trữ tương ứng trong cột C, phải được biểu thị bằng hoặc lớn hơn 20. Điểm thứ hai, được liệt kê trong cột D của trang tính Excel, phải bằng hoặc lớn hơn 30. Chỉ khi cả hai đáp ứng đủ hai điều kiện nêu trên thì học sinh sẽ thi đậu.
Cách dễ nhất để tạo một công thức phù hợp là lần lượt ghi lại điều kiện đầu tiên, sau đó kết hợp nó trong đối số logic_test của hàm IF trong Excel hoặc hàm IF nhiều điều kiện:
Điều kiện bắt buộc: AND (B2> = 20, C2> = 30)
Công thức IF / AND: = IF ((AND (C2> = 20, D2> = 30)), “Đạt”, “Không đạt”)
Công thức cho trang tính Excel biết khi sử dụng hàm IF đa điều kiện để trả về “Đạt” nếu một giá trị cho trước trong cột C> = 20 và một giá trị trong cột D> = 30. Nếu không, công thức hàm IF trong Excel sẽ trả về ” Thất bại”. Hình sau chứng minh rằng hàm IF trong Excel và hàm AND của chúng tôi là đúng:
Ví dụ 1. Cách sử dụng hàm IF đa điều kiện và hàm AND trong Excel
Chú ý. Microsoft Excel sẽ kiểm tra tất cả các điều kiện trong hàm AND và ngay cả khi một trong các điều kiện đã được chỉ định, kiểm tra sẽ đánh giá là FALSE. Điều này hơi bất thường vì trong hầu hết các ngôn ngữ lập trình cơ bản ngày nay, các điều kiện tiếp theo không cần thực hiện kiểm tra nếu bất kỳ kiểm tra nào trước đó trả về FALSE.
Trên thực tế, một công thức hàm IF trong Excel và một công thức hàm AND đúng trong Excel có thể bị lỗi do tính năng này.
Ví dụ: công thức = IF (AND (A20, (1 / A2)> 0,5), “Tốt”, “Kém”) sẽ ngay lập tức trả về “lỗi chia cho 0” (#DIV / 0!) Nếu ô A2 trong Microsoft Excel bằng 0. Để tránh điều này, bạn nên sử dụng hàm IF lồng nhau hoặc hàm IF trong Excel với nhiều điều kiện: = IF (A20, IF ((1 / A2)> 0,5, “Tốt”, “Kém”), “Kém ”)
2. Ví dụ 2. Sử dụng hàm IF trong Excel với hàm OR trong Excel
Bạn có thể sử dụng kết hợp các hàm OR và IF trong Excel theo cách tương tự. Sự khác biệt so với việc sử dụng hàm IF hoặc AND được thảo luận ở trên là Microsoft Excel trả về giá trị TRUE nếu ít nhất một trong các điều kiện được chỉ định được đáp ứng.
Vì vậy, nếu chúng ta sửa đổi cách sử dụng hàm IF trong Excel theo cách sau:
= IF ((HOẶC (C2> = 20, D2> = 30)), “Đạt”, “Không đạt”)
Cột E sẽ được đánh dấu là “Đạt” nếu điểm đầu tiên của học sinh bằng hoặc lớn hơn 20, hoặc điểm thứ hai của học sinh bằng hoặc lớn hơn 30.
Như bạn có thể thấy trong hình ảnh bên dưới, các sinh viên trong tập dữ liệu của chúng tôi có cơ hội tốt hơn để vượt qua kỳ thi cuối kỳ trong các điều kiện đặt ra như vậy.
Ví dụ 2. Sử dụng hàm IF trong Excel với hàm OR trong Excel
Tất nhiên, bạn không bị giới hạn chỉ sử dụng hai hàm VÀ / HOẶC trong việc sử dụng IF trong Excel của mình. Bạn có thể sử dụng rất nhiều hàm logic khác nhau tùy theo yêu cầu của công việc, với điều kiện:
Trong Microsoft Excel 2013, 2010 và 2007, công thức hàm IF trong Excel của bạn có không quá 255 đối số và tổng độ dài của các công thức tương ứng không được vượt quá 8192 ký tự.
Trong Microsoft Excel 2003 trở về trước, bạn có thể sử dụng tối đa 30 đối số và tổng độ dài tương ứng của công thức của bạn sẽ không vượt quá 1.024 ký tự.
3. Ví dụ 3. Sử dụng IF trong Excel với AND và OR. chức năng
Trong trường hợp bạn phải thực hiện đánh giá dữ liệu dựa trên một số điều kiện, bạn sẽ phải sử dụng cả hàm AND và hàm OR cùng một lúc.
Trong bảng trên, giả sử bạn cần sử dụng các tiêu chí sau để đo lường sự thành công của học sinh:
Điều kiện 1: giá trị của cột C> = 20 và cột D> = 25
Điều kiện 2: giá trị của cột C> = 15 và cột D> = 20
Nếu một trong hai điều kiện trên được đáp ứng bởi dữ liệu trong Microsoft Excel, thì đánh giá cuối cùng vẫn được thông qua, nếu không – kết quả trả về không thành công.
Công thức cho hàm này có vẻ khó, nhưng trong giây lát bạn sẽ thấy nó không hề khó! Bạn chỉ cần diễn đạt hai điều kiện dưới dạng câu lệnh hàm AND và bao gồm chúng một cách khéo léo trong hàm OR vì bạn không cần đáp ứng cả hai điều kiện nói trên:
HOẶC (VÀ (C2> = 20, D2> = 25), VÀ (C2> = 15, D2> = 20)
Cuối cùng, sử dụng hàm OR ở trên làm phép kiểm tra logic tương ứng với hàm IF trong Excel và cung cấp các tham số tương ứng value_if_true và value_if_false. Kết quả là bạn sẽ nhận được một công thức hàm IF trong Excel với nhiều điều kiện VÀ / HOẶC:
= IF (OR (AND (C2> = 20, D2> = 25), AND (C2> = 15, D2> = 20)), “Đạt”, “Không đạt”)
Hình sau cho thấy rằng chúng tôi đã triển khai đúng hàm IF trong Excel:
Ví dụ 3. Sử dụng IF trong Excel với AND và OR. chức năng
II. Công thức hàm IF lồng nhau cơ bản
Dưới đây là ví dụ điển hình về hàm IF trong Excel với nhiều điều kiện. Giả sử bạn có một danh sách dữ liệu thông tin về sinh viên ở cột A và điểm tương ứng của họ ở cột B, bạn muốn thực hiện phân loại theo các điều kiện sau:
Xuất sắc: trên 249
Tốt: từ 200 đến 249
Đạt yêu cầu: từ 150 đến 199
Kém: dưới 150
Hãy viết hàm IF lồng nhau trong Microsoft Excel dựa trên các tiêu chí trên. Hãy coi đây là cơ hội luyện tập cho bạn, bằng cách bắt đầu từ những thuật ngữ quan trọng nhất và viết công thức càng đơn giản càng tốt. Công thức hàm IF trong Excel của chúng tôi sẽ giống như sau:
= IF (B2> 249, “Xuất sắc”, IF (B2> = 200, “Tốt”, IF (B2> 150, “Đạt yêu cầu”, “Kém”)))
Và đây là kết quả:
Công thức hàm IF lồng nhau cơ bản
III. Hàm IF lồng nhau bao gồm các phép tính
Yêu cầu như sau: giá của hàng hóa đưa ra sẽ thay đổi tùy theo số lượng cụ thể. Và bạn muốn viết một công thức tính tổng giá cho bất kỳ lượng dữ liệu nào và đặt nó vào một ô nhất định. Nói cách khác, công thức hàm IF trong Excel của bạn cần kiểm tra các điều kiện và thực hiện phép tính tùy thuộc vào số hàng và giá được tính như sau:
Hàm IF lồng nhau bao gồm các phép tính
Công việc bắt buộc này có thể được thực hiện bằng cách sử dụng hàm IF trong Excel theo kiểu lồng nhau. Cách hiểu tương tự như ví dụ 1, điểm khác biệt duy nhất ở đây là bạn nhân số lượng hàng hóa được chỉ định với giá trị được thanh toán bởi hàm IF trong Excel (ví dụ: giá của hàng hóa tương ứng). trên mỗi đơn vị)
Giả sử người dùng đã nhập số lượng hàng hóa nhất định vào ô B8, công thức của hàm IF trong Excel sẽ như sau:
= B8 * IF (B8> = 101, 12, IF (B8> = 50, 13, IF (B8> = 20, 16, IF (B8> = 11, 18, IF (B8> = 1, 20, “) ))))))
Và kết quả sẽ được trả về như sau:
Kết quả sẽ được trả về
Ví dụ này chỉ là cách thực hiện Microsoft Excel cơ bản, nếu bạn hiểu, bạn có thể áp dụng hàm IF trong Excel theo cách lồng ghép này vào công việc kinh doanh hàng ngày của mình.
Ví dụ: thay vì đặt trước dữ liệu về giá hàng hóa trong công thức, bạn có thể điều hướng đến các ô chứa giá trị cụ thể này (ô B2 đến B6). Phương pháp này sẽ giúp bạn thực hiện các thay đổi đối với nguồn dữ liệu đầu vào mà không cần thay đổi công thức.
= B8 * IF (B8> = 101, B6, IF (B8> = 50, B5, IF (B8> = 20, B4, IF (B8> = 11, B3, IF (B8> = 1, B2, “” ))))))
Thay đổi nguồn dữ liệu đầu vào mà không thay đổi công thức
Hoặc bạn có thể thêm điều kiện bằng cách sử dụng hàm IF trong Excel để giúp chặn phần trên, dưới hoặc cả hai đầu của phạm vi giá trị. Khi số lượng hàng hóa không nằm trong khoảng giá trị, công thức hàm IF trong Excel sẽ hiển thị cụm từ “ngoài khoảng giá trị”. Ví dụ như sau:
= IF (OR (B8> 200, B8 = 101,12, IF (B8> = 50, 13, IF (B8> = 20, 16, IF (B8> = 11, 18, IF (B8> = 1, 20 )), “”))))))))
Ví dụ
IV. Thay thế cho hàm IF lồng nhau trong Excel
Để tránh giới hạn của 7 điều kiện cụ thể trong hàm IF trong Excel trong các phiên bản Microsoft Excel cũ hơn và làm cho các công thức đã sử dụng của bạn ngắn hơn và nhanh hơn, bạn có thể chọn các phương pháp sau thay cho hàm IF trong Excel với nhiều điều kiện.
Để kiểm tra sự thỏa mãn của nhiều điều kiện, hãy sử dụng hàm LOOKUP, hàm Vlookup, hàm INDEX / MATCH hoặc hàm CHOOSE.
Sử dụng hàm IF trong Excel kết hợp với OR / AND
Sử dụng công thức mảng trong Microsoft Excel
Sử dụng hàm CONCATENATE của Excel và nối chuỗi
Giống như các hàm Excel khác, hàm CONCATENATE của chúng tôi có thể bao gồm 30 điều kiện trong các phiên bản Microsoft Excel cũ hơn và 255 điều kiện trong Microsoft Excel 2016 – 2007.
Ví dụ: để trả về các kết quả dữ liệu khác nhau dựa trên giá trị trong ô B2 trong Microsoft Excel, bạn có thể sử dụng một trong các công thức cụ thể sau:
Hàm IF trong Excel lồng nhau:
= IF (B2> 249, “Xuất sắc”, IF (B2> = 200, “Tốt”, IF (B2> 150, “Đạt yêu cầu”, “Kém”)))
Hàm CONCATENATE trong Excel:
= CONCATENATE (IF (C1 = ”a”, “Xuất sắc”, “”), IF (C1 = ”b”, “Tốt”, “”), IF (C1 = ”c”, “Kém“, “”) )
Thay thế cho hàm IF lồng nhau trong Excel
Hàm nối chuỗi trong Microsoft Excel:
= IF (B2 = ”a”, “Xuất sắc”, “”) & IF (B2 = ”b”, “Tốt”, “”) & IF (B2 = ”c”, “Kém”, “”) & IF (B2 = “d”, “Kém”, “”)
V. Các hàm IF lồng nhau và những điều cần nhớ
Như bạn vừa thấy, sử dụng hàm IF đa điều kiện trong Microsoft Excel không yêu cầu các phương pháp phong phú và công thức nâng cao. Để cải thiện các công thức hàm IF lồng nhau trong Excel và tránh những lỗi thường gặp, hãy luôn nhớ 3 điều cơ bản sau:
Trong Microsoft Excel 2016-2007, bạn có thể thực hiện kết hợp 64 điều kiện. Trong các phiên bản cũ hơn từ Microsoft Excel 2003 trở về trước, bạn có thể sử dụng tối đa 7 điều kiện.
Luôn nghĩ về thứ tự của các điều kiện trong công thức hàm IF lồng nhau trong Excel – nếu điều kiện đầu tiên được đặt thành true, các điều kiện sau sẽ không được kiểm tra.
Nếu công thức của bạn có nhiều hơn 5 hàm IF trong Excel, thì các công thức sau có thể tối ưu hơn.
TẠI VÌ. Tổng kết
Trên đây là hướng dẫn cụ thể cách sử dụng hàm IF trong Excel khi bạn có nhu cầu kết hợp nhiều điều kiện. Hi vọng điều này sẽ có ý nghĩa và giúp bạn thuận tiện hơn trong công việc máy tính văn phòng của mình. Chúc may mắn!
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.
Comments