Bạn đang xem bài viết Hướng Dẫn Cách Sử Dụng Hàm If Với Nhiều Điều Kiện: And, Or, Hàm If Lồng Nhau Và Hơn Thế được cập nhật mới nhất trên website Uta.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Đăng ký khoá học Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ
Cách sử dụng hàm IF với nhiều điều kiện trong Excel
Hàm AND: Nếu điều kiện kiểm tra chứa hàm AND, Microsoft Excel sẽ trả về TRUE (Đúng) nếu tất cả các điều kiện được đáp ứng; nếu không sẽ trả về FALSE (Sai).
Hàm OR: Trong trường hợp sử dụng hàm OR trong kiểm tra, Excel sẽ trả về TRUE nếu bất kỳ điều kiện nào được đáp ứng; nếu khác sẽ trả về FALSE (Sai).
Để minh họa rõ hơn, chúng ta hãy xem xét một vài ví dụ của hàm IF với nhiều điều kiện.
Kết hợp hàm IF với các hàm khác trong Excel
Sử dụng hàm IF và hàm AND trong Excel
Giả sử, bạn có một bảng với kết quả của hai điểm thi. Điểm số đầu tiên, được lưu trữ trong cột A, phải bằng hoặc lớn hơn 20. Điểm thứ hai, được liệt kê trong cột B, phải bằng hoặc vượt quá 30. Chỉ khi đáp ứng cả hai điều kiện trên, học sinh mới vượt qua kỳ thi. Cách dễ nhất để tạo một công thức thích hợp là viết ra điều kiện trước, sau đó kết hợp nó vào đối số kiểm tra của bạn:
Sử dụng hàm IF với hàm OR trong Excel
Bạn sử dụng kết hợp các hàm IF và hàm OR theo cách tương tự như với hàm AND ở trên. Khác biệt so với công thức IF và AND ở trên là Excel sẽ trả về TRUE (Đúng) nếu ít nhất một trong các điều kiện được chỉ định được đáp ứng. Vì vậy, công thức ở trên sẽ được sửa đổi theo cách sau:
Cột D sẽ trả về giá trị “Đậu” nếu điểm thứ nhất bằng hoặc lớn hơn 20 hoặc điểm thứ hai bằng hoặc lớn hơn 30.
Sử dụng hàm IF với các hàm AND và OR trong Excel
Trong trường hợp bạn phải đánh giá dữ liệu của mình dựa trên nhiều điều kiện, sẽ phải sử dụng cả hai hàm AND và OR cùng một lúc. Trong bảng trên, giả sử bạn có các tiêu chí sau để đánh giá điều kiện đạt của học sinh:
Nếu một trong các điều kiện trên được đáp ứng thì bạn đó được coi là đã vượt qua, nếu không thì trượt. Công thức có vẻ phức tạp, nhưng thực ra cũng không quá khó lắm. Bạn chỉ cần biểu thị hai điều kiện là các câu lệnh AND và đặt chúng trong hàm OR vì không yêu cầu cả hai điều kiện được đáp ứng, chỉ cần một trong hai điều kiện đáp ứng là đủ:
Cuối cùng, sử dụng hàm OR ở trên làm điều kiện kiểm tra logic trong hàm IF và cung cấp các đối số TRUE (Đúng) và FALSE (Sai). Kết quả là bạn sẽ nhận được công thức hàm IF sau với nhiều điều kiện AND/OR:
Đương nhiên, bạn không bị giới hạn khi chỉ sử dụng với hai hàm là AND/OR trong các công thức hàm IF của Excel, mà có thể sử dụng nhiều chức năng logic như logic kinh doanh của bạn yêu cầu, miễn là:
Trong Excel 2016, 2013, 2010 và 2007, công thức không bao gồm quá 255 đối số và tổng độ dài của công thức không vượt quá 8.192 ký tự.
Trong Excel 2003 trở xuống, có thể sử dụng tối đa 30 đối số và tổng độ dài công thức không vượt quá 1.024 ký tự.
Sử dụng nhiều câu lệnh IF trong Excel (các hàm IF lồng nhau)
Nếu cần tạo các kiểm tra logic phức tạp hơn cho dữ liệu của mình, bạn có thể bao gồm các câu lệnh IF bổ sung trong các đối số TRUE (Đúng) và FALSE (Sai) của các công thức hàm IF. Các hàm IF này được gọi là các lồng nhau và nó đặc biệt hữu ích nếu bạn muốn công thức của mình trả về 3 hoặc nhiều kết quả khác nhau.
Kém: 40 hoặc ít hơn (<= 40)
Để bắt đầu, cần chèn thêm một cột tổng số điểm cột (D) của hai cột B và C với công thức tính tổng các số trong cột B và C: =B2+C2
Bây giờ chỉ cần viết một hàm IF lồng nhau dựa trên các điều kiện trên. Lúc này công thức hàm IF lồng nhau sẽ như sau:
Như bạn thấy, chỉ cần một lồng nhau là đủ trong trường hợp này. Đương nhiên, bạn có thể lồng nhiều hàm IF hơn nếu bạn muốn. Ví dụ:
Công thức trên thêm một điều kiện nữa đó là tổng số điểm 70 và nhiều hơn nữa đủ điều kiện là “Xuất sắc”.
Sử dụng hàm IF với các hàm SUM, AVERAGE, MIN và MAX
Ở ví dụ về các hàm IF lồng nhau phía trên, chúng ta đã biết công thức trả về các thứ hạng khác nhau (Xuất sắc, Tốt, Đạt yêu cầu hoặc Kém) dựa trên tổng số điểm của mỗi học sinh. Trường hợp trên, chúng ta đã thêm một cột mới với công thức tính tổng số điểm trong cột B và C.
Nhưng nếu bảng của bạn có cấu trúc được xác định trước mà không cho phép bất kỳ sửa đổi nào? Trong trường hợp này, thay vì thêm cột trợ giúp, bạn có thể thêm giá trị trực tiếp vào công thức hàm IF của mình, như sau:
Nhưng nếu bảng của bạn chứa nhiều điểm riêng lẻ với rất nhiều cột khác nhau thì sao? Tổng hợp rất nhiều số liệu trực tiếp trong công thức IF sẽ làm cho nó rất lớn. Một cách khác là nhúng hàm SUM trong bài kiểm tra logic của IF, lúc này công thức trên sẽ sửa thành:
Theo cách tương tự, bạn có thể sử dụng các hàm Excel khác trong kiểm tra logic các công thức với hàm IF của mình.
Sử dụng hàm IF và hàm AVERAGE trong Excel
Công thức sẽ trả về là “Tốt” nếu điểm trung bình trong các cột B đến F bằng hoặc lớn hơn 30, “Đạt yêu cầu” nếu điểm trung bình nằm trong khoảng từ 29 đến 25 và “Kém” nếu dưới 25.
Sử dụng hàm IF và MAX/MIN
MAX: =IF(F2=MAX($F$2:$F$6);”Tốt nhất”;””) MIN: =IF(F3=MIN($F$2:$F$6);”Kém nhất”;””)
Nếu bạn muốn có cả kết quả Min và Max trong cùng một cột, có thể lồng một trong các hàm trên vào các hàm khác, ví dụ:
=IF(F2=MAX($F$2:$F$10);”Tốt nhất”;IF(F2=MIN($F$2:$F$10);”Kém nhất”;””))
Theo cách tương tự, bạn có thể sử dụng hàm IF với các hàm bảng tính tùy chỉnh của mình. Ngoài ra, Excel còn cung cấp một số hàm IF đặc biệt để phân tích và tính toán dữ liệu dựa trên các điều kiện khác nhau.
Ví dụ: Để đếm số lần xuất hiện của một văn bản hoặc giá trị số dựa trên một hoặc nhiều điều kiện, bạn có thể sử dụng lần lượt hàm COUNTIF và COUNTIFS. Để tìm ra tổng các giá trị dựa trên (các) điều kiện được chỉ định, hãy sử dụng các hàm SUMIF hoặc SUMIFS. Để tính trung bình theo các tiêu chí nhất định, sử dụng AVERAGEIF hoặc AVERAGEIFS.
Sử dụng hàm IF với các hàm ISNUMBER và ISTEXT
Đây là ví dụ về hàm IF lồng nhau trả về “Văn bản” nếu ô B1 chứa bất kỳ giá trị văn bản nào, “Số” nếu B1 chứa giá trị số và “Trống” nếu B1 trống.
=IF(ISTEXT(B1);”Văn bản”;IF(ISNUMBER(B1);”Số”;IF(ISBLANK(B1);”Trống”;””)))
Ghi chú: Công thức trên hiển thị kết quả là “Số” cho các giá trị số và ngày. Điều này là do Microsoft Excel lưu trữ ngày dưới dạng số, bắt đầu từ ngày 1 tháng 1 năm 1900, tương đương với 1.
Hướng dẫn cách dùng hàm index trong Excel chi tiết nhấtHướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Hướng dẫn cách sắp xếp ngày trong ExcelHướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng caoHướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế
Tài liệu kèm theo bài viết
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
Hướng Dẫn Cách Sử Dụng Các Hàm Thống Kê Có Điều Kiện Trong Excel
Trong Excel có rất nhiều hàm sử dụng cho mục đích thống kê, bởi việc thống kê được thực hiện chủ yếu thông qua công cụ Excel. Trong bài viết này chúng ta sẽ cùng nhau tìm hiểu một số hàm thống kê có điều kiện trong Excel. Các hàm đó là những hàm nào? Công dụng và cách sử dụng ra sao? Các bạn sẽ có câu trả lời sau khi tìm hiểu bài viết này.
Tính trung bình có điều kiện
Nhắc tới tính trung bình, chúng ta nghĩ ngay tới nhóm hàm AVERAGE. Và để phục vụ cho việc tính trung bình theo điều kiện thì Excel cung cấp cho chúng ta 2 hàm là:
AVERAGEIF(range,criteria,average_range): Tính trung bình theo 1 điều kiện
AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2, criteria2],…): Tính trung bình theo nhiều điều kiện
Về cấu trúc và cách viết các thành phần trong hàm thì hai hàm này khá giống với SUMIF và SUMIFS, nhưng thay vì tính tổng thì sẽ tính trung bình của các giá trị.
Để giải thích rõ hơn về 2 hàm này chúng ta xét ví dụ sau
Vùng tính trung bình: cột Mức lương
Vùng chứa điều kiện: cột Bộ phận
Điều kiện: Kinh doanh
Chúng ta có thể sử dụng cả 2 hàm để tính trong trường hợp này, bởi yêu cầu này chỉ có 1 điều kiện.
Kết quả như sau:
E12=AVERAGEIF(C2:C9,”Kinh doanh”,D2:D9)
F12=AVERAGEIFS(D2:D9,C2:C9,”Kinh doanh”)
Với yêu cầu 2: Tính mức lương trung bình bộ phận kinh doanh trong tháng 2
Phân tích yêu cầu:
Vùng tính trung bình: cột Mức lương
Vùng điều kiện 1: cột Bộ phận
Điều kiện 1: Kinh doanh
Vùng điều kiện 2: cột Tháng
Điều kiện 2: 2 (tháng 2)
Như vậy trong yêu cầu này có tới 2 điều kiện. Trường hợp này không dùng được hàm AverageIF mà chỉ dùng được hàm AverageIFS
Cách viết hàm như sau:
F13=AVERAGEIFS(D2:D9,C2:C9,”Kinh doanh”,A2:A9,2)
Hàm đếm theo điều kiện
Trong yêu cầu đếm thì chúng ta có nhóm hàm COUNT. Việc đếm theo điều kiện có thể sử dụng hàm COUNTIF để đếm theo 1 điều kiện, hàm COUNTIFS để đếm theo nhiều điều kiện.
Các bài viết về hàm đếm theo điều kiện đã có sẵn khá nhiều trên blog.hocexcel.online. Các bạn có thể tham khảo các bài viết sau:
Cách dùng hàm COUNTIFS và COUNTIF với nhiều điều kiện Hướng dẫn cách đếm theo nhiều điều kiện trong Excel bằng hàm COUNTIFS
Hàm tìm giá trị lớn nhất, nhỏ nhất theo điều kiện
Từ phiên bản OFFICE 2016, OFFICE 365 trở đi chúng ta có thêm hàm giúp tìm giá trị lớn nhất, nhỏ nhất theo điều kiện. Cụ thể là
MAXIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],…) Tìm giá trị lớn nhất theo 1 hoặc nhiều điều kiện
MINIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],…) Tìm giá trị nhỏ nhất theo 1 hoặc nhiều điều kiện
Ví dụ về 2 hàm này như sau:
Vùng xét giá trị lớn nhất: cột Mức lương
Vùng điều kiện 1: cột Bộ phận
Điều kiện 1: Kinh doanh
Vùng điều kiện 2: cột Tháng
Điều kiện 2: 2 (tháng 2)
Như vậy cần dùng tới 2 điều kiện để tìm ra giá trị lớn nhất. Cách viết hàm MAXIFS như sau:
E12=MAXIFS(D2:D9,C2:C9,”Kinh doanh”,A2:A9,2)
Yêu cầu 2: tìm mức lương nhỏ nhất trong tháng 2
Phân tích yêu cầu:
Vùng xét giá trị nhỏ nhất: cột Mức lương
Vùng điều kiện 1: cột Tháng
Điều kiện 1: giá trị là 2 (tháng 2)
Như vậy trường hợp này chỉ cần dùng 1 điều kiện. Tuy nhiên hàm tìm giá trị nhỏ nhất theo điều kiện chỉ có hàm MINIFS nên chúng ta sử dụng hàm này như sau:
E13=MINIFS(D2:D9,A2:A9,2)
Hàm Countifs Trong Excel, Hàm Thống Kê Có Điều Kiện, Ví Dụ Minh Họa
Điều kiện trong hàm COUNTIFS có thể là ký tự, số, ngày tháng,…vì vậy bạn có thể áp dụng một cách linh hoạt vào việc tính toán trong bảng tính excel để đạt hiệu quả công việc cao nhất. Hướng dẫn sử dụng hàm CountIFS là bài học cơ bản trong giáo trình Học Excel trên chúng tôi đã từng đề cập đến trước kia.
Hàm COUNTIFS thống kê tính toán
Hàm COUNTIFS cũng được sử dụng rất nhiều trường hợp khác nhau, tuy nhiên với người mới thì trong bài viết này chúng tôi sẽ đưa ra ví dụ cụ thể, đơn giản nhất đến bạn đọc.
HƯỚNG DẪN DÙNG HÀM COUNTIFS TRONG EXCEL – VÍ DỤ MINH HỌA
1. Cú pháp hàm COUNTIFS trong EXCEL
Hàm Excel COUNTIFS trả về số lượng các ô thỏa mãn một hoặc nhiều điều kiện. COUNTIFS có thể được sử dụng với các điều kiện như ngày, số, text, và các điều kiện khác.
Cú pháp: COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)
range1 – phạm vi đầu tiên để đánh giá.
criteria1 – xác định phạm vi đầu tiên áp dụng điều kiện đầu tiên (criteria1), bắt buộc.
range2 – [điều kiện bổ sung] phạm vi thứ hai để đánh giá.
criteria2 – [điều kiện bổ sung] xác định phạm vi áp dụng điều kiện thứ 2.
2. Một số lưu ý khi dùng hàm COUNTIFS trong EXCEL
COUNTIFS đếm số ô trong phạm vi khớp với điều kiện được cung cấp. Không giống như hàm COUNTIF, hàm COUNTIFS có thể áp dụng nhiều hơn một điều kiện với nhiều hơn một phạm vi. Phạm vi và điều kiện được áp dụng theo cặp, và chỉ yêu cầu cặp đầu tiên. Đối với mỗi điều kiện bổ sung, bạn phải cung cấp các cặp phạm vi / điều kiện khác. Cho phép đến 127 cặp phạm vi / điều kiện.
Lưu ý:
– Mỗi vùng dữ liệu bổ sung phải có cùng số hàng và số cột với vùng dữ liệu đầu tiên (range 1). COUNTIFS có thể đếm những vùng dữ liệu liền kề và không liền kề. Nếu vùng dữ liệu cung cấp không phù hợp, bạn sẽ nhận được lỗi # VALUE.
– Các ký tự đại diện? và * có thể được sử dụng trong điều kiện. Dấu hỏi chấm (?) có thể khớp với bất kỳ ký tự nào và dấu hoa thị (*) khớp với bất kỳ dãy ký tự nào.
– Để tìm dấu hỏi chấm (?) hoặc dấu hoa thị (*), sử dụng dấu ngã (~) trước dấu hỏi chấm trước hoặc dấu hoa thị (tức là ~ ?, ~ *).
Cấu trúc của hàm COUNTIFS khá khác so với hàm Count, Hàm COUNTIFS có thể có 127 cặp Criteria_range,Criteria
.Criteria_range là một hay nhiều ô dạng số, tên, mảng, hay tham chiếu chứa số.Criteria là ở dạng số, biểu thức, tham chiếu ô hay chuỗi văn bản xác định. Ô trống hoặc ô chứa dữ liệu dạng text bị bỏ qua.
Mẹo Để đếm các ô chứa dữ liệu trong bảng tính thì các bạn dùng hàm Counta
3. Các ví dụ cụ thể hàm COUNTIFS trong EXCEL
Ví dụ1: Cho bảng điểm một số học sinh, hãy đếm số học sinh đạt điểm 9 và 8 ở cả 3 môn:
Để đếm được ta dùng công thức COUNTIFS : Đếm số học sinh đạt cả 3 điểm 9 với công thức
Công thức ô D11 là : = COUNTIFS(D5:D9,9,E5:E9,9,F5:F9,9)
Còn nếu bạn muốn đếm số học sinh đạt cả 3 điểm 8 thì sử dụng công thức sau:
Công thức ô D11 là : = COUNTIFS(D5:D9,8,E5:E9,8,F5:F9,8)
Và các bạn đã đếm được số lượng học sinh đạt cả 3 điểm 8 hay 3 điểm 9 cùng lúc, tương tự với các điểm khác thì bạn cũng làm như vậy. Bạn cũng có thể lọc các điểm khác nhau.
https://thuthuat.taimienphi.vn/ham-countifs-trong-excel-962n.aspx
Khảo Sát Hàm Số Và Ứng Dụng Đạo Hàm
Tiếp tục với những phương pháp và thủ thuật giải nhanh trắc nghiệm. Chúng tôi giới thiệu đến quý độc giả bộ sách tham khảo 12. Bộ sách “Phương pháp và thủ thuật giải nhanh trắc nghiệm 12 này” được viết trên cơ sở tự học của học sinh và tham kháo cho giáo viên. Nó được tổng hợp rất nhiều nguồn và rất nhiều phương pháp mà chúng tôi công bố trong quyển sách.
Quyển sách được chia thành 9 chủ đề. Bao gồm:
Chủ đề 1: Sự biến thiên của hàm số. Chủ đề 2: Cực trị của hàm số. Chủ đề 3: Giá trị lớn nhất- Giá trị nhỏ nhất của hàm số. Chủ đề 4: Tiệm cận của đồ thị hàm số. Chủ đề 5: Khảo sát hàm số. Chủ đề 6: Điểm đặc biệt của đồ thị hàm số. Chủ đề 7: Sự tương giao của đồ thị hàm số. Chủ đề 8: Tiếp tuyến của đồ thị hàm số. Chủ đề 9: Bài tập ôn tập chương I.
Mỗi chủ đề chúng tôi đều nhắc lại những kiến thức cần nhớ, thủ thuật giải toán trắc nghiệm, những lưu ý và sai lầm thường mắc phải. Bên cạnh những bài tập mẫu có hướng dẫn giải và những bài tập trắc nghiệm có đáp án cụ thể. Cuối mỗi chương, tác giả lại bố trí một loạt những bài toán cũng cố lại những kiến thức đã học.
Do đặc trưng của phương thức thi trắc nghiệm. Tác giả và cộng sự đã hệ thống lại những phần, những bài toán mà chỉ có trong phần thi trắc nghiệm mới xuất hiện. Chẳng hạn: trong phẩn khảo sát hàm số. Sau một vài bài tập mang tính giới thiệu đến dạng của khảo sát. Chúng tôi bố trí những bài toán mang tính chất nhận biết những đặc trưng của đồ thị hàm số. Qua đó giúp các em có được những loại toán đi kèm với dạng toán khảo sát.
Bên cạnh những bài toán cấp độ trung bình. Chúng tôi còn bố trí những bài toán nâng cao và tất nhiên có hướng dẫn giải cụ thể. Những bài toán mang tính chất nâng cao thường tốn thời gian hơn những bài toán nhận biết. Nên chúng tôi đã cố gắng đưa ra những kiến thứ cần thiết nhất có thể.
Cập nhật thông tin chi tiết về Hướng Dẫn Cách Sử Dụng Hàm If Với Nhiều Điều Kiện: And, Or, Hàm If Lồng Nhau Và Hơn Thế trên website Uta.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!