Hàm IF là hàm phổ biến và được sử dụng nhiều trong Excel để trả về kết quả theo điều kiện đặt ra. Trong bài viết này, Điện máy XANH sẽ giải thích và hướng dẫn cho bạn cách sử dụng hàm IF trong Excelnhé!
1Công thức Hàm IF trong Excel
Hàm IF được dùng để kiểm tra dữ liệu có thỏa điều kiện người dùng đặt ra hay không và trả về kết quả theo biểu thức logic đúng hoặc sai.
Một vài ứng dụng thực tế của hàm IF:
- Nếu điểm trung bình của học sinh từ 5 - 6.5 xếp loại trung bình, từ 6.5 - 8 xếp loại khá, từ 8 trở lên xếp loại giỏi.
- Nếu chức vụ là nhân viên thì phụ cấp 300, chuyên viên thì phụ cấp 500, trưởng phòng thì phụ cấp 700.
- Nếu khách hàng mua số lượng từ 100 - 1000 thì giá là 500 đồng, từ 1000 - 10000 thì giá là 450 đồng, từ 10000 trở lên thì giá là 400 đồng.
Xem thêm:Hàm SUMIF
2Ví dụ hàm IF
Để hiểu rõ hơn về cách sử dụng hàm IF, mời bạn cùng xem qua các bài tập đơn giản bên dưới nhé.
Bạn là giảng viên của một lớp học và bạn cần kiểm tra xem học sinh của mình có qua môn không với điều kiện như sau:
Tại ô D2, ta dùng công thức: =IF[C2>=7,"Đạt","Không Đạt"]
Giải thích:
- C2>=7: Kiểm tra xem ô C2 [điểm số] có lớn hơn hoặc bằng 7 hay không
- "Đạt": Kết quả trả về khi ô C2 lớn hơn hoặc bằng 7
- "Không Đạt": Kết quả trả về khi ô C2 nhỏ hơn 7
Lưu ý: Khi kết quả trả về là dạng chữ, bạn cần thêm dấu ngoặc kép ["] như trong công thức ở trên.
Kết quả:
3Một số cách dùng hàm IF
Trong thực tế khi sử dụng hàm IF, chúng ta sẽ cần lồng nhiều hàm IF với nhau hoặc lồng hàm IF với các hàng khác.
Bạn có thể hiểu cách dùng hàm IF khác như sau:
- Nếu điều kiện IF đúng => Thực hiện hành động 1.
- Nếu điều kiện IF sai => Thực hiện hành động 2.
Lồng nhiều hàm IF
Trong trường hợp bạn có từ 2 điều kiện khác nhau trở lên, bạn nên lồng các hàm IF lại với nhau để tạo thành một công thức hoàn chỉnh.
Giả sử bạn là nhân viên tiền lương và phúc lợi của một công ty, và bạn cần phải tính toán phụ cấp tương ứng theo chức vụ như sau:
Tại ô D2, ta dùng công thức: =IF[C2="Nhân viên",500000,IF[C2="Chuyên viên",700000,1000000]]
Giải thích:
- Công thức IF 1: Nếu C2 là Nhân viên, trả về kết quả 500000, không phải Nhân viên thì kiểm tra tiếp với IF 2
- Công thức IF 2: Nếu C2 là Chuyên viên, trả về kết quả 700000, không phải Chuyên viên thì trả về kết quả 1000000 [vì không phải Nhân viên, không phải Chuyên viên thì chỉ còn lại Trưởng phòng]
Kết quả:
Lồng hàm IF với hàm khác
Ngoài các hàm IF được lồng với nhau, chúng ta cũng lồng hàm IF với các công thức khác trong các trường hợp điều kiện phức tạp hơn. Ví dụ bên dưới là một trường hợp phổ biến sử dụng hàm AND lồng với hàm IF.
Tại ô E2, ta dùng công thức:=IF[AND[C2>=5,D2>=5],"Đạt","Không Đạt"]
Giải thích:
- AND[C2>=5,D2>=5: Kiểm tra xem ô C2 và D2 xem mỗi ô có lớn hơn hoặc bằng 5 không
- "Đạt": Kết quả trả về khi cả ô C2 và D2 đều từ lớn hơn 5
- "Không Đạt": Kết quả trả về khi một trong hai nhỏ hơn 5
Kết quả:
Sử dụng hàm IF nhiều điều kiện
Trong trường hợp cần xét nhiều điều kiện khác nhau, ta có thể dùng hàm IFS.
Công thức:
=IFS[logical_test1, value_if_true1, [logical_test2, value_if_true2]…]
Trong đó
- logical_test1: Biểu thức điều kiện 1.
- value_if_true1: Giá trị trả về nếu điều kiện 1 đúng.
- logical_test2: Biểu thức điều kiện 2.
- value_if_true2: Giá trị trả về nếu điều kiện 2 đúng.
Để giải thích hàm IFS, ta hãy cùng đến ví dụ::Cho một bảng danh sách mã sản phẩm với phần trăm khuyến mãi khác nhau, khi mua sản phẩm nhân viên sẽ quét mã sản phẩm và trả về số tiền khuyến mãi.
Ngoài việc sử dụng hàm VLOOKUP ra, ta còn có thể sử dụng hàm IFS như sau:
=IFS[A2="Xà Phòng",0.5, A2="Sữa tắm",0.4, A2="Bột giặt",0.8]
Trong đó:
- A2 là sản phẩm cần dò điều kiện.
- Xà Phòng, sữa tắm, bột giặt: là các loại sản phẩm cần dò
- 0.5, 0.4, 0.8: là tỉ lệ giảm giá sẽ trả về nếu thỏa điều kiện 1, 2, 3.
Hàm IF kết hợp AND
Để hiểu hơn về trường hợp này, ta có thể đi tới ví dụ sau:
Giả sử ta có điểm trung bình của một học sinh là 8.0, học sinh sẽ được xếp loại học sinh giỏi nếu điểm trung bình đạt 8.0 và hạnh kiểm Tốt
Vậy sử dụng hàm IF kết hợp and trong trường hợp này sẽ là:
=IF[AND[A2>=8, B2="Tốt"], "Học Sinh Giỏi", "Học Sinh Tiên Tiến"]
Trong đó:
- AND: So sánh cả 2 điều kiện IF [DTB >=8, Hạnh Kiểm là Tốt]
- "Học Sinh Giỏi": Kết quả trả về nếu thỏa 2 điều kiện
- "Học Sinh Tiên Tiến": Kết quả trả về nếu 1 trong hai điều kiện đó không thỏa.
4Một số lỗi thường gặp khi sử dụng hàm IF
Mời bạn tham khảo một số lỗi thường gặp khi sử dụng hàm IF và cách khắc phục:
Kết quả hiển thị trong ô bằng 0 [không]
Lỗi này xảy ra một trong hai giá trị value_if_true hoặc value_if_false đang để trống.
Nếu mục đích của bạn là muốn giá trị trả về để trống thay vì 0, hãy thêm 2 dấu ngoặc kép [""], hoặc thêm giá trị cụ thể trả về.
Ví dụ: =IF[A1>5,"Đạt",""] hoặc=IF[A1>5,"Đạt","Không Đạt"]
Kết quả hiển thị trong ô là #NAME?
Lỗi này thường xảy ra khi công thức của bạn bị sai chính tả, như thay vì IF thì lại thành UF hoặc OF do các phím U, I, O này ở gần nhau.
Để khắc phục, bạn hãy kiểm tra lại chính tả của công thức và các dấu ngoặc đã đủ chưa [đặc biệt trong hàm IF lồng].
Mời bạn tham khảo một số mẫu laptop đang kinh doanh tại Điện máy XANH
Trên đây là bài viết chia sẻ cho bạn cách dùng hàm IF trong Excel. Mong rằng những thông tin này sẽ giúp ích cho bạn trong việc sử dụng hàm IF.
Sử dụng ứng dụng Excel thì làm việc với các hàm cơ bản trong Excel là việc rất thường xuyên. Chúng giúp thực hiện tính toán dữ liệu trực tiếp trên bảng tính nhanh và tiện lợi hơn. Các hàm Excel là những công thức được định nghĩa trước và đã có sẵn trong Excel, bạn chỉ việc lôi ra sử dụng mà thôi, chúng tiện hơn so với các biểu thức rất nhiều.
Những hàm cơ bản trong Excel mà chúng tôi tổng hợp dưới đây như hàm Excel tính toán, thống kê,... sẽ rất có ích với các bạn thường xuyên phải làm việc trên bảng tính Excel, đặc biệt là trong lĩnh vực kế toán, hành chính nhân sự. Vì thế, hãy chắc chắn rằng bạn đã thuộc nằm lòng những hàm Excel cơ bản này. Mời bạn tham khảo nhé.
Hàm Excel cơ bản
- 1. HÀM ĐẾM VÀ TÍNH TỔNG
- Hàm đếm COUNT
- Hàm đếm COUNTIF với điều kiện cụ thể
- Hàm hàm đếm COUNTIFS với nhiều điều kiện
- Hàm tính tổng SUM
- Hàm tính tổng SUMIF có điều kiện
- Hàm tính tổng SUMIFS với nhiều điều kiện
- Hàm tính giá trị trung bình AVERAGE
- Hàm đếm ô trống COUNTBLANK
- Hàm đếm ô không trống COUNTA
- Hàm tính tổng giá tiền sản phẩm SUMPRODUCT
- Hàm MIN, MAX
- 2. HÀM LOGIC
- Hàm IF
- Hàm AND
- Hàm OR
- Hàm IF lồng nhau
- 3. HÀM NGÀY THÁNG
- Hàm YEAR, MONTH, DAY
- Hàm DATE
- Hàm NOW hiển thị thời gian hệ thống
- Hàm HOUR, MINUTE, SECOND
- Hàm TIME
- Hàm DATEIF
- Hàm WEEKDAY
- Hàm TEXT
- Hàm NETWORKDAYS tính số ngày làm việc
- Hàm EOMONTH
- 4. HÀM LÀM VIỆC VỚI CHUỖI VĂN BẢN
- Nối chuỗi văn bản
- Hàm LEFT
- Hàm RIGHT
- Hàm MID
- Hàm LEN
- Hàm FIND
- Hàm SUBSTITUTE thay thế văn bản
- Hàm cắt các khoảng trống TRIM
- Hàm LOWER, UPPER, PROPER để chuyển đổi chữ hoa, chữ thường
- Hàm EXACT để so sánh hai cột
- Hàm CONCATENATE kết hợp nội dung
- 5. HÀM TRA CỨU VÀ THAM CHIẾU
- Hàm VLOOKUP
- Hàm MATCH
- Hàm INDEX
- Hàm CHOOSE
1. HÀM ĐẾM VÀ TÍNH TỔNG
Hàm đếm COUNT
Bạn có một bảng tính với cơ sở dữ liệu khổng lồ và cần biết có bao nhiêu ô trong một vùng nào đó hay trong toàn bộ bảng tính chỉ chứa chữ số, không chứa chữ cái. Thay vì phải ngồi đếm một cách thủ công thì bạn có thể sử dụng hàm =COUNT. Ví dụ, bạn cần đếm từ ô B1 đến B10, hãy gõ =COUNT[B10:B10].
Hàm đếm COUNTIF với điều kiện cụ thể
Để đếm các ô dựa trên một điều kiện cụ thể [ví dụ, lớn hơn 9], hãy sử dụng hàm COUNTIF sau đây.
Hàm hàm đếm COUNTIFS với nhiều điều kiện
Để đếm các ô dựa trên nhiều điều kiện [ví dụ, green và lớn hơn 9], hãy sử dụng hàm COUNTIFS sau.
Hàm tính tổng SUM
Đây có thể nói là hàm đầu tiên mà bất kỳ người dùng nào mới học Excel cũng cần phải biết đến.Hàm SUM trong Excel cộng các giá trị số trong một phạm vi ô. Được phân loại theo hàm toán học và lượng giác, hàm được nhập bằng cách gõ “=SUM” theo sau là các giá trị được tính tổng. Các giá trị được cung cấp cho hàm có thể là số, tham chiếu ô hoặc phạm vi.
Công thức SUM tự động cập nhật khi người dùng chèn hoặc xóa một giá trị. Nó cũng bao gồm những thay đổi được thực hiện đối với một phạm vi ô hiện có. Hơn nữa, hàm cũng tự bỏ qua các ô trống và giá trị văn bản.
Cú pháp của hàm SUM trong Excel được hiển thị trong hình sau:
Hàm chấp nhận các đối số sau:
- Number1: Đây là giá trị số đầu tiên được thêm vào.
- Number2: Đây là giá trị số thứ hai được thêm vào.
- Đối số “number1” là bắt buộc trong khi các số tiếp theo [“number2”, “number3”, v.v...] là tùy chọn.
Các quy tắc điều chỉnh việc sử dụng hàm được liệt kê như sau:
- Các đối số được cung cấp có thể là số, mảng, tham chiếu ô, hằng số, phạm vi và kết quả của những hàm hoặc công thức khác.
- Trong khi cung cấp một dải ô, chỉ dải ô đầu tiên [cell1:cell2] là bắt buộc.
- Đầu ra là số và đại diện cho tổng các giá trị được cung cấp.
- Các đối số được cung cấp có thể lên tới tổng số 255.
Lưu ý: Hàm SUM excel trả về giá trị “#VALUE!” lỗi nếu tiêu chí được cung cấp là một chuỗi văn bản dài hơn 255 ký tự. Phạm vi ô được cung cấp phải khớp với kích thước của nguồn.
Ô chứa đầu ra phải luôn được định dạng dưới dạng số.
Cùng tìm hiểu ví dụ sau:
Giả sử, bạn muốn cộng các con số trong ô A2 và B2 lại với nhau, sau đó hiển thị kết quả trong ô B3. Để thực hiện, bạn chỉ cần di chuyển đến ô B3 và gõ cụm từ "=SUM" vào rồi chọn hàm =SUM vừa xuất hiện trong danh sách nổi lên.
Tiếp theo, hãy nhấn phím Ctrl cùng với thao tác nhấn chuột chọn vào ô A2 và B2 rồi cuối cùng là nhấn phím Enter. Kết quả tổng của hai con số trong hai ô A2 và B2 mà bạn vừa chọn sẽ xuất hiện tức thì trong ô B3. Bạn có thể sử dụng hàm SUM để tính tổng của hai hay nhiều ô, chỉ với thao tác chọn thêm những ô cần thiết vào trong nội dung của hàm.
Hàm tính tổng SUMIF có điều kiện
Để tính tổng các ô dựa trên một điều kiện [ví dụ, lớn hơn 9], hãy sử dụng hàm SUMIF sau [hai đối số].
Để tính tổng các ô dựa trên một tiêu chí [ví dụ, green], hãy sử dụng hàm SUMIF với 3 đối số [đối số cuối cùng là phạm vi cần tính tổng].
Hàm tính tổng SUMIFS với nhiều điều kiện
Để tính tổng các ô dựa trên nhiều điều kiện [ví dụ, blue và green], hãy sử dụng hàm SUMIFS sau [đối số đầu tiên là phạm vi bảng tính cần tính tổng].
Hàm tính giá trị trung bình AVERAGE
Hàm =AVERAGE làm đúng chức năng như tên gọi của nó là đưa ra con số có giá trị trung bình trong những số được chọn. Ví dụ bạn muốn tính giá trị trung bình từ ô A10 đến ô J10, chỉ cần gõ =AVERAGE[A10:J10] rồi nhấn Enter.
Kết quả đưa ra trong ô K10 là con số có giá trị trung bình giữa các ô từ A10 đến J10. Ngoài ra, bạn cũng có thể sử dụng con trỏ chuột kéo thả và đánh dấu chọn vùng dữ liệu hoặc có thể đồng thời nhấn phím Ctrl rồi nhấn chuột vào từng ô riêng rẽ trong trường hợp nếu các ô không nằm kế cận nhau.
Tương tự như SUMIF, COUNTIF bạn có thể sử dụng AVERAGEIF và AVERAGEIFS để tính giá trị trung bình của các ô dựa trên một hoặc nhiều tiêu chí.
Hàm đếm ô trống COUNTBLANK
Bạn sử dụng hàm này khi cần đếm các ô trống. Cú pháp =COUNTBLANK[phạm vi bảng tính cần đếm].
Hàm đếm ô không trống COUNTA
Hàm COUNTA có chức năng dùng để đếm số ô có chứa nội dung bất kỳ bao gồm chữ số, chữ cái hay biểu tượng, hay nói cách khác nó dùng để đếm các ô không trống. Cú pháp =COUNTA[phạm vi bảng tính cần đếm].
Hàm tính tổng giá tiền sản phẩm SUMPRODUCT
Để tính tổng sản phẩm của các số tương ứng [kiểu tính tổng tiền của các sản phẩm dựa trên số lượng và giá tương ứng của từng sản phẩm] trong một hoặc nhiều dãy, bạn hãy sử dụng hàm SUMPRODUCT mạnh mẽ của Excel.
Ví dụ dưới đây dùng hàm SUMPRODUCT để tính tổng số tiền đã tiêu.
Cụ thể ở trên, hàm SUMPRODUCT đã thực hiện phép tính: [2 * 1000] + [4 * 250] + [4 * 100] + [2 * 50] = 3500.
Phạm vi tính tổng phải có cùng kích thước, nếu không Excel sẽ hiển thị lỗi #VALUE!
Nếu giá trị trong ô không phải dạng số thì SUMPRODUCT sẽ mặc định giá trị của chúng là 0.
Hàm MIN, MAX
Nếu bạn muốn tìm một con số có giá trị nhỏ nhất trong một vùng dữ liệu, hàm =MIN có thể làm việc đó. Chỉ cần gõ vào cụm từ =MIN[D3:J13], Excel sẽ trả về cho bạn số nhỏ nhất nằm trong phạm vi đó.
Ngược với hàm =MIN vừa đề cập, hàm =MAX sẽ trả về con số có giá trị lớn nhất trong phạm vi cần tìm. Cú pháp của hàm này cũng tương tự như hàm =MIN, gồm địa chỉ ô đầu tiên cho đến ô cuối cùng.
2. HÀM LOGIC
Hàm IF
Hàm IF giúp bạn kiểm tra xem điều kiện có được đáp ứng không, nếu đúng nó sẽ trả về giá trị đúng, nếu sai sẽ trả về giá trị sai. Công thức =IF[điều kiện,"giá trị đúng","giá trị sai"]. Để áp dụng cho nhiều điều kiện bạn sẽ phải dùng đến hàm IF lồng nhau.
Trong ví dụ dưới đây, chúng ta sẽ kiểm tra xem ô A có thỏa mãn điều kiện lớn hơn 10 không, nếu thảo mãn thì trả về giá trị Correct trong ô C1, nếu không thỏa mãn sẽ trả về giá trị Incorrect. Tại ô C1, bạn nhập vào hàm =IF[A1>10,"Correct","Incorrect"].
Hàm IF trả về Correct vì giá trị trong ô A1 lớn hơn 10.
Hàm AND
Hàm AND sẽ trả về giá trị đúng nếu tất cả các điều kiện được thỏa mãn và trả về giá trị sai nếu có bất kỳ điều kiện nào sai. Công thức =IF[AND[điều kiện],"giá trị đúng","giá trị sai]
Vẫn ví dụ trên, giờ ta kết hợp kiểm tra 2 điều kiện là A1 lớn hơn 10 và B1 lớn hơn 5, nếu cả 2 thỏa mãn sẽ trả về Correct, ngược lại trả về Incorrect. Tại ô D1 bạn nhập =IF[AND[A1>10,B1>5],"Correct","Incorrect"]
Hàm AND trả về giá trị sai vì ô B1 nhỏ hơn 5, do đó hàm IF trả về giá trị sai tương ứng ở đây là Incorrect.
Hàm OR
Ngược lại với AND, OR trả về giá trị đúng nếu có bất kỳ điều kiện nào được thỏa mãn và trả về giá trị sai nếu tất cả các điều kiện đều không được đáp ứng.
Thử lại điều kiện trên với hàm OR. Tại ô E1 bạn nhập =IF[OR[A1>10,B1>5],"Correct","Incorrect"]
Hàm OR trả về giá trị đúng vì ô A1 lớn hơn 10, vì thế kết quả là hàm IF trả về giá trị Correct.
Ghi chú chung: Hàm AND và OR có thể kiểm tra đến 255 điều kiện.
Hàm IF lồng nhau
Như đã nói bên trên, khi bạn có nhiều hơn một điều kiện cần kiểm tra thì đó là lúc dùng đến hàm IF lồng nhau. Giá trị sai sẽ được thay thế bằng một hàm IF khác để thực hiện thêm một lần kiểm tra. [Nếu dùng Excel 2016, bạn chỉ cần sử dụng IFS là được].
Hãy xem các ví dụ dưới đây:
Bạn nhập vào công thức =IF[A1=1,"Bad",IF[A1=2,"Good",IF[A1=3,"Excellent","No Valid Score"]]] để kiểm tra giá trị của ô A1, nếu bằng 1 trả về Bad, nếu bằng 2 trả về Good, nếu bằng 3 trả về Excellent, nếu là một giá trị khác sẽ trả về No Valid Score.
Bạn nhập vào công thức =IF[A1