Excel cho Microsoft 365 Excel cho Microsoft 365 dành cho máy Mac Excel cho web Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 Xem thêm...Ít hơn
Bài viết này mô tả cú pháp công thức và cách dùng hàm Averageif hàm trong Microsoft Excel.
Trả về giá trị trung bình [trung bình cộng] của tất cả các ô trong một phạm vi đáp ứng tiêu chí đưa ra.
AVERAGEIF[range, criteria, [average_range]]
Cú pháp hàm AVERAGEIF có các đối số sau:
-
Range Bắt buộc. Một hoặc nhiều ô để tính giá trị trung bình, bao gồm các số hoặc tên, mảng hoặc tham chiếu có chứa số.
-
Criteria Bắt buộc. Tiêu chí ở dạng số, biểu thức, tham chiếu ô hoặc văn bản xác định các ô sẽ tính giá trị trung bình. Ví dụ, các tiêu chí có thể được biểu thị dưới dạng như 32, "32", ">32", "táo" hoặc B4.
-
Average_range Tùy chọn. Tập hợp các ô thực tế để tính giá trị trung bình. Nếu bỏ qua, phạm vi được dùng.
-
Các ô trong phạm vi có chứa TRUE hoặc FALSE sẽ bị bỏ qua.
-
Nếu ô trong phạm vi trung bình là một ô trống, AVERAGEIF sẽ bỏ qua nó.
-
Nếu phạm vi là giá trị trống hoặc dạng văn bản, AVERAGEIF sẽ trả về giá trị lỗi #DIV0! .
-
Nếu một ô trong tiêu chí bị bỏ trống, AVERAGEIF sẽ xem ô đó như giá trị 0.
-
Nếu không có ô nào trong phạm vi đáp ứng các tiêu chí, AVERAGEIF sẽ trả về giá trị lỗi #DIV/0! .
-
Bạn có thể dùng các ký tự đại diện, dấu chấm hỏi [?] và dấu sao [*] trong tiêu chí. Dấu chấm hỏi sẽ khớp với bất kỳ ký tự đơn nào; dấu sao sẽ khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã [~] trước ký tự.
-
Average_range không nhất thiết phải có cùng kích cỡ và hình dạng như phạm vi. Các ô thực tế được tính giá trị trung bình sẽ được xác định bằng cách dùng ô trái trên cùng trong average_range làm ô đầu tiên, sau đó gộp các ô tương ứng về kích cỡ và hình dạng với phạm vi. Ví dụ:
| Nếu phạm vi là | Và average_range là | Khi đó các ô thực tế được đánh giá là |
| A1:A5 | B1:B5 | B1:B5 |
| A1:A5 | B1:B3 | B1:B5 |
| A1:B4 | C1:D4 | C1:D4 |
| A1:B4 | C1:C2 | C1:D4 |
Lưu ý: Hàm AVERAGEIF đo xu hướng trung tâm, là vị trí trung tâm của một nhóm số trong phân phối thống kê. Ba cách đo lường thông dụng nhất về xu hướng trung tâm là:
-
Trung bình là trung bình số học, được tính bằng cách cộng một nhóm các số rồi chia cho số lượng các số. Ví dụ, trung bình của 2, 3, 3, 5, 7 và 10 là 30 chia cho 6, ra kết quả là 5.
-
Trung vị là số nằm ở giữa một nhóm các số; có nghĩa là, phân nửa các số có giá trị lớn hơn số trung vị, còn phân nửa các số có giá trị bé hơn số trung vị. Ví dụ, số trung vị của 2, 3, 3, 5, 7 và 10 là 4.
-
Mode là số xuất hiện nhiều nhất trong một nhóm các số. Ví dụ, mode của 2, 3, 3, 5, 7 và 10 là 3.
Với một phân phối đối xứng của một nhóm các số, ba cách đo lường xu hướng trung tâm này đều là như nhau. Với một phân phối lệch của một nhóm các số, chúng có thể khác nhau.
Sao chép dữ liệu ví dụ trong bảng sau đây và dán vào ô A1 của một bảng tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.
| Giá trị Bất động sản | Tiền hoa hồng | |
| 100000 | 7000 | |
| 200000 | 14000 | |
| 300000 | 21000 | |
| 400000 | 28000 | |
| Công thức | Mô tả | Kết quả |
| =AVERAGEIF[B2:B5,"250000",B2:B5] | Trung bình của tất cả các khoản tiền hoa hồng với giá trị bất động sản lớn hơn 250000. Hai khoản tiền hoa hồng đáp ứng điều kiện này và tổng của chúng là 49000. | 24500 |
| Khu vực | Lợi nhuận [Ngàn] | |
| Phía Đông | 45678 | |
| Phía Tây | 23789 | |
| Phía Bắc | -4789 | |
| Phía Nam [Văn phòng Mới] | 0 | |
| Trung Tây | 9678 | |
| Công thức | Mô tả | Kết quả |
| =AVERAGEIF[A2:A6,"=*Tây",B2:B6] | Trung bình của lợi nhuận của vùng Tây và Trung Tây. | 16733,5 |
| =AVERAGEIF[A2:A6,"*[Văn phòng Mới]",B2:B6] | Trung bình của lợi nhuận của tất cả các vùng, trừ văn phòng mới. | 18589 |
Hàm IF là một trong những hàm phổ biến và quan trọng nhất trong excel. Bạn dùng hàm để yêu cầu Excel kiểm tra một điều kiện và trả về một giá trị nếu điều kiện được đáp ứng, hoặc trả về một giá trị khác nếu điều kiện đó không được đáp ứng.
Trong bài viết này, Blog Học Excel Online sẽ tìm hiểu về cú pháp và cách dùng hàm IF phổ biến trong Excel, sau đó sẽ có cái nhìn sâu hơn bằng các ví dụ về công thức mà hy vọng là sẽ bổ ích cho cả những người mới dùng Excel và những người có kinh nghiệm.
Cú pháp hàm IF và cách dùng:
Hàm IF là một trong những hàm logic cho phép đánh giá một điều kiện nhất định và trả về giá trị mà bạn chỉ định nếu điều kiện là TRUE và trả về một giá trị khác nếu điều kiện là FALSE
Cú pháp cho hàm IF như sau:
IF [logical_test, [value_if_true], [value_if_false]]Như bạn thấy, hàm IF có 3 tham số, nhưng chỉ có tham số đầu tiên là bắt buộc phải có, còn 2 tham số còn lại là không bắt buộc
- logical_test: Là một giá trị hay biểu thức logic có giá trị TRUE [đúng] hoặc FALSE [sai]. Bắt buộc phải có. Đối với tham số này, bạn có thể chỉ rõ đó là ký tự, ngày tháng, con số hay bất cứ biểu thức so sánh nào.
Ví dụ: Biểu thức logic của bạn có thể là hoặc B1=”sold”, B110.
- Value_if_true: Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị TRUE hay nói cách khác là điều kiện thỏa mãn. Không bắt buộc phải có.
Ví dụ: Công thức sau sẽ trả về từ “Good” nếu giá trị ở ô B1 lớn hơn 10: =IF[B1>10, “Good”]
- Value_if_false: là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị FALSE hay nói cách khác là điều kiện không thỏa mãn. Không bắt buộc phải có.
Ví dụ: Nếu bạn thêm biến thứ 3 là “Bad” vào công thức ở ví dụ trên, nó sẽ trả về từ “Good” nếu giá trị ở trong ô B1 lớn hơn 10, còn nếu ngược lại thì giá trị trả về sẽ là “Bad”:
=IF[B1>10, "Good", "Bad"]Những điều cần nhớ về hàm IF trong Excel:
Mặc dù hai biến cuối cùng trong hàm IF là không bắt buộc nhưng công thức có thể trả về những giá trị không mong đợi nếu như bạn không nắm vững những quy tắc cơ bản nhất
1. Nếu như value_if_true bị bỏ qua
Nếu value_if_true bị bỏ qua trong công thức IF [ví dụ chỉ có dấu phải sau logical_test], thì hàm IF sẽ trả về kết quả là 0 nếu điều kiện chính được đáp ứng. Đây là ví dụ:
=If[B1>10,,”Bad”]Nếu bạn không muốn hàm If của mình không hiển thị bất cứ điều gì khi điều kiện thỏa, hãy nhập 2 lần dấu nhấy trong tham số thứ 2 như thế này:
=If[B1>10,””,”Bad”]. Về cơ bản, trường hợp này hàm if sẽ trả về chuỗi trống.
2. Nếu như value_if_false bị bỏ qua
Nếu bạn không quan tâm đến điều gì sẽ xảy ra nếu điều kiện quy định không được đáp ứng, bạn có thể bỏ qua biến thứ 3 trong công thức hàm IF, điều này sẽ dẫn đến kết quả như sau
Nếu biểu thức logic được cho là FALSE và thông số value_if_false bị bỏ qua [chỉ có một giá trị duy nhất ứng với tham số value_if_false] thì hàm IF sẽ trả về giá trị FALSE. Đây quả là một điều không mong muốn phải không nào?
Đây là một ví dụ cho công thức
=IF[B1>10, "Good"]Nếu bạn đặt dấu phẩy sau tham số value_if_true thì hàm IF sẽ trả về giá trị bằng 0, điều này có nghĩa rằng giá trị trả về không tương thích với công thức =IF[B1>10, “Good”,].
Lần nữa, lý do thuyết phục nhất để đặt “” trong tham số thứ ba là bạn sẽ nhận giá trị rỗng nếu điều khiện không thỏa mãn =IF[B1>10, “Good”, “”].
3. Làm cho hàm IF hiện lên giá trị TRUE hoặc FALSE
Nếu như bạn muốn các công thức Excel có thể hiện lên các giá trị logic như TRUE hoặc FALSE khi một điều kiện nhất định được thỏa mãn thì bạn phải gõ TRUE trong ô tham số value_if_true. Ô value_if_false có thể điền vào là FALSE hoặc để trống. Đây là một ví dụ cho công thức trên:
=IF[B1>10, TRUE, FALSE]hoặc
=IF[B1>10, TRUE]Lưu ý. Nếu như bạn muốn hàm IF trả về giá trị TRUE và FALSE như giá trị logic [Boolean] mà công thức excel khác có thể nhận dạng thì bạn cần đảm bảo rằng không đặt chúng trong dấu ngoặc kép. Dấu hiệu của một Boolean trong một ô như bạn có thể thấy trong hình minh họa trên.
Nếu bạn muốn giá trị “TRUE” và “FALSE” là ký tự thì hãy đặt chúng trong dấu ngoặc kép. Trong trường hợp này, giá trị được trả về sẽ nằm bên trái và được định dạng là dạng General. Không có công thức Excel nào nhận dạng “TRUE” và “FALSE” là giá trị logic cả.
4. Làm cho hàm IF hiển thị một phép toán và trả về một kết quả
Thay vì trả về một gái trị nhất định thì bạn có thể làm cho công thức hàm IF kiểm tra điều kiện đưa ra, tính toán một công thức toán và trả về giá trị dựa trên kết quả của phép toán đó. Bạn thực hiện điều này bằng cách dử dụng các công thức sô học hoặc các hàm khác của Excel trong ô tham số value_if_true và /hoặc value_if_false. Dưới đây là một vài ví dụ điển hình:
Ví dụ 1:
=IF[A1>B1, C3*10, C3*5]Công thức so sánh giá trị trong cột A1 và B1, và nếu giá trị trong cột A1 lớn hơn trong cột B1 thì kết quả sẽ là việc nhân giá trị trong ô C3 với C10, còn ngược lại sẽ nhân với 5
Ví dụ 2:
=IF[A1B1, SUM[A1:D1], ""]Công thức sẽ so sánh giá trị trong các ô A1 và B1, nếu giá trị trong ô A1 không bằng B1 thì công thức sẽ trả về giá trị là tổng của tất cả các giá trị từ ô A1 tới D1, ngược lại thì sẽ là một chuỗi ký tự rỗng.
Cách sử dụng hàm IF trong Excel và các ví dụ:
Bây giờ bạn đã quen thuộc với cú pháp của hàm IF, hãy xem xét một số ví dụ về công thức và tìm hiểu cách sử dụng hàm IF như là một hàm tính toán trong Excel
Công thức ví dụ về hàm IF cho phép so sánh số học như: lớn hơn, nhỏ hơn, bằng
Việc sử dụng hàm IF với các giá trị số dựa trên việc sử dụng các toán tử so sánh khác nhau để diễn tả các điều kiện của bạn. Bạn sẽ tìm thấy danh sách đầy đủ các toán tử logic được minh họa bằng các ví dụ về công thức trong bảng dưới đây.
| Điều kiện | Toán tử | Ví dụ về công thức | Mô tả | ||||||||||||||||
| Lớn hơn | > | =IF[A2>5, “OK”,] | Nếu số trong ô A2 lớn hơn 5 thì công thức sẽ trả về giá trị là “OK”, ngược lại thì trả về 0 | ||||||||||||||||
| Nhỏ hơn | =5, “OK”, “Poor”] | Nếu số trong ô A2 lớn hơn hoặc bằng 5 thì công thức sẽ trả về giá trị là “OK”, ngược lại thì sẽ hiển thị “Poor” | |||||||||||||||||
| Nhỏ hơn hoặc bằng | 11/19/2014. Không có công thức nào là đúng cả!
Ví dụ 1. Công thức hàm IF cho ngày tháng với hàm DATEVALUEĐể hàm IF có thể nhận dạng được ngày tháng trong một biểu thức logic, bạn phải đặt nó trong hàm DATEVALUE như thế này: DATEVALUE[“11/19/2014”]. Công thức hoàn chỉnh có dạng như sau: =IF[C230, "Future date", ""]Để chỉ ra những ngày đã diễn ra cách đây hơn 30 ngày, bạn có thể dùng công thức sau: =IF[TODAY[]-A2>30, "Past date", ""]Nếu bạn muốn có cả hai dấu hiệu trong cùng một cột bạn sẽ cần sử dụng đến hàm IF được lồng ghép như sau: =IF[A2-TODAY[]>30, "Future date", IF[TODAY[]-A2>30, "Past date", ""]] Ví dụ về công thức hàm IF cho dữ liệu và ô trống:Đôi khi bạn muốn đánh dấu ô dữ liệu hay ô trống nhất định thì bạn cần thực hiện một trong các cách sau:
Bảng dưới đây sẽ giải thích rõ sự khác biệt giữa hai cách trên và đưa ra ví dụ
Ví dụ dưới đây sẽ biểu diễn biểu thức logic có chứa dữ liệu/ rỗng sẽ như thế nào. Giả sử như dữ liệu trong cột C chỉ có được sau khi đã có dữ liệu trong cột B tương ứng với game đã diễn ra thì bạn có thể dùng công thức hàm IF sau để đánh dấu những game đã hoàn thành =IF[$C2"", "Completed", ""]=IF[ISBLANK[$C2]=FALSE, “Completed”, “”] Vì không có độ dài chuỗi bằng 0 trong bảng nên cả hai công thức đều trả về kết quả như nhau: Nếu bạn muốn xem hướng dẫn cách sử dụng hàm IF trong Excel này dưới dạng video, thì mời bạn theo dõi video sau đây: Ví dụ sử dụng hàm IF trong việc xếp loại theo điểm sốTrong trường hợp bạn có một yêu cầu sắp xếp điểm số như sau:
giả sử điểm số nằm trong ô A1, thì bạn có thể viết hàm IF lồng nhau như sau: =IF[AND[A1>=5,A1=6.5, A1=8,"Giỏi","Không xếp loại"]]] Trong trường hợp này, nếu bạn thấy việc viết hàm IF lồng nhau quá phức tạp và khó hiểu, thì bạn có thể tham khảo cách làm khác ngắn gọn và dễ hiểu hơn bằng cách sử dụng hàm VLOOKUP Như vậy với bài viết này, blog.hocexcel.online đã chia sẻ cho các bạn cách dùng hàm IF trong Excel: Công thức cho số, ký tự, dữ liệu và ô trống và một số các ví dụ. Nếu có bất kỳ thắc mắc gì có thể comment ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất. Những kiến thức bạn đang xem thuộc khóa học: Excel từ cơ bản tới nâng cao của Học Excel Online. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Video liên quanChủ Đề |