Hàm IF (tt)

Những hàm IF lồng nhau

Trong cuộc sống đời thường, có mấy ai dễ dàng chấp nhận chuyện "một cái nếu", phải không các bạn.
Chúng ta thường sẽ dùng kiểu, nếu... rồi nhưng mà nếu... nhiều khi kéo dài đến vô tận!

Trong Excel cũng vậy. Giả sử, chúng ta xếp loại học tập, nếu điểm trung bình (ĐTB) lớn hơn 9 thì giỏi, vậy ĐTB nhỏ hơn 9 thì dở? Chưa, ĐTB nhỏ hơn 9 nhưng lớn hơn 7 thì khá cái đã, rồi ĐTB nhỏ hơn 7 nhưng chưa bị điểm 5 thì trung bình, chỉ khi nào ĐTB nhỏ hơn 4 thì mới gọi là dở (cái này tôi chỉ ví dụ thôi, các bạn đừng sử dụng để xếp loại nhé).

Khi đó, chúng ta sẽ dùng những hàm IF() lồng nhau, IF() này nằm trong IF() kia. Sau này chúng ta sẽ học cách ghép thêm nhiều điều kiện khác vào nữa.

Ví dụ, tôi lấy lại ví dụ đã nói ở bài trước:

=IF(A1 >= 1000, "Big!", "Not big")

Bi giờ thêm chút, A1 lớn hơn 1000 là "big" rồi, nhưng chẳng lẽ 10000 thì cũng chỉ là "big" ? Có lẽ nên tặng thêm một danh hiệu cao hơn:

=IF(A1 >= 1000, IF(A1 >= 10000, "Really big!!", Big!"), "Not big")

Hoặc là, đồng ý rằng <1000 là "Not big", nhưng nó khác "Small" chứ (tui không lớn, chưa chắc tui nhỏ), vậy chúng ta thêm một định nghĩa "Small" thử xem:

=IF(A1 >= 1000, "Big!", IF(A1 < 100, "Small", "Not big"))

Bạn để ý nhé, ở đây tôi đặt cái IF "con" không giống như ở trên, sao cũng được bạn à, miễn là đừng sai cú pháp của IF().
Và nếu thích, bạn có thể ghép tất cả lại:

=IF(A1 >= 1000, IF(A1 >= 10000, "Really big!!", Big!"), IF(A1 < 100, "Small", "Not big"))

Chĩ cần một lưu ý, là những dấu đóng mở ngoặc đơn. Nếu bạn đóng và mở không đúng lúc hoặc không đủ, Excel sẽ không hiểu, hoặc là cho các bạn kết quả sai đấy.

Bài toán tính tiền thưởng dựa trên doanh số bán hàng

Có một cửa hàng quy định mức tiền thưởng trên doanh số bán hàng trong tháng cho nhân viên như sau:

- Nếu doanh số không đạt định mức đề ra, không thường
- Nếu doanh số vượt định mức, nhưng chưa tới 10% so với định mức, thưởng $1000
- Nếu doanh số vượt định mức, và vượt hơn 10% so với định mức, thưởng $10.000

Tôi đề nghị cách làm như vầy nhé:

Giả sử cột B là định mức doanh số bán, cột C là doanh số thực tế trong tháng, để tính được phần trăm vượt định mức của nhân viên, tại cột D ta dùng công thức:

=(C2-B2)/C2 (nếu muốn hiện số %, bạn định dạng cho cột D theo kiểu Percentage)

Và tại cột E, tính mức tiền thưởng, dùng công thức:

=IF(D2 < 0, "", IF(D2 < 0.1, 1000, 10000))

Dưới đây là hình minh họa cho ví dụ này:

1.     Hàm IF

Hàm IF()

Trả về một giá trị nếu điều kiện có giá trị TRUE, và một giá trị khác nếu điều kiện có giá trị FALSE.

Cú pháp: IF(logical_test, value_is_true [, value_is_false])

logical_test : Là bất kỳ giá trị hay công thức nào có thể trả về TRUE hoặc FALSE. Ví dụ, A1=100 là một biểu thức logic. Nếu giá trị trong A1 bằng 100, biểu thức sẽ trả về giá trị TRUE, còn ngược lại thì biểu thức trả về giá trị FALSE. Đối số này có thể bất kỳ toán tử so sánh nào.

value_is_true : Giá trị trả về nếu logical_test là TRUE.

value_is_false : Giá trị trả về nếu logical_test là FALSE.

Lưu ý:

o  Có tới 64 hàm IF có thể lồng vào nhau (với Excel 2003 trở về trước thì con số này là 7) khi đối số value_is_true và value_is_false xây dựng trên những điều kiện phức tạp hơn. Tuy nhiên, khi cần phải kiểm tra nhiều điều kiện, nên dùng các hàm LOOKUP(), VLOOKUP(), HLOOKUP(), hoặc CHOOSE().

o  Nếu có bất kỳ đối số nào của hàm IF() là mảng, mỗi phần tử trong mảng sẽ được tính toán khi câu lệnh IF được thực thi.

o  Microsoft Excel có cung cấp một số hàm khác mà bạn có thể dùng để phân tích dữ liệu dựa vào một điều kiện nào đó. Ví dụ như hàm COUNTIF(), COUNTIFS(), hàm SUMIF() hay SUMIFS().

 

1. Hàm AND()

Trả về giá trị TRUE nếu tất cả các đối số là đúng; trả về giá trị FALSE nếu có một hay nhiều đối số là sai.
Có thể dùng hàm AND() bất cứ chỗ nào, nhưng thường thì hàm AND() hay được dùng chung với hàm IF().

Cú pháp: AND(logical1, [, logical2...])

logical: Có thể có từ 1 đến 255 biểu thức (trong Excel 2003 trở về trước, con số này là 30) được xét xem đúng (TRUE) hay sai (FALSE).

Ghi chú:

o  Các đối số phải có giá trị logic là TRUE hoặc là FALSE. Nếu đối số là mảng hay tham chiếu thì mảng hay tham chiếu đó chỉ chứa những giá trị logic.

o  Nếu đối số là mảng hay tham chiếu có chứa chữ hoặc những ô rỗng, các giá trị đó sẽ được bỏ qua.

o  Nếu dãy không chứa các trị logic, AND() sẽ trả về giá trị lỗi #VALUE!

2.     Hàm OR()

Trả về giá trị TRUE nếu có một hay nhiều đối số là đúng; trả về giá trị FALSE nếu tất cả các đối số là sai.
Có thể dùng hàm OR() bất cứ chỗ nào, nhưng thường thì hàm OR() hay được dùng chung với hàm IF().

Cú pháp: OR(logical1, [, logical2...])

logical: Có thể có từ 1 đến 255 biểu thức (trong Excel 2003 trở về trước, con số này là 30) được xét xem đúng (TRUE) hay sai (FALSE).

Ghi chú:

o    Các đối số phải có giá trị logic là TRUE hoặc là FALSE. Nếu đối số là mảng hay tham chiếu thì mảng hay tham chiếu đó chỉ chứa những giá trị logic.

o    Nếu đối số là mảng hay tham chiếu có chứa chữ hoặc những ô rỗng, các giá trị đó sẽ được bỏ qua.

o    Nếu dãy không chứa các trị logic, OR() sẽ trả về giá trị lỗi #VALUE!

3.     Áp dụng hàm lý luận vào công thức mãng

Mời bạn xem bảng tính sau đây:


Nếu nhìn thoáng qua, có lẽ bạn sẽ nói rằng, có gì đâu, mấy cái TOTAL là cộng những cái có ở trên thôi.

Vậy, tại sao cái TOTAL ở B16 trống rỗng? Và cái công thức tôi trình bày đó, làm gì mà "ghê" vậy ?

Vâng, vì yêu cầu của hai cái TOTAL này là: Nếu các chỉ tiêu Advertising, Rent, Supplies, Salaries và Utilities đều lớn hơn 0 thì mới cộng chúng lại,
còn nếu chỉ có 1 trong những chỉ tiêu đó không có (Salaries của năm 2005, cell B14) thì không cộng.

Phải làm công thức mãng thôi. Trước tiên chúng ta làm nháp:

Các ô từ B3 đến B7 phải lớn hơn 0: AND(B3:B7>0)

Tổng của B3 đến B7: SUM(B3:B7)

Rồi, bây giờ ghép chữ nếu vào: =IF(AND(B3:B7 > 0), SUM(B3:B7), "")

Đây là một dạng công thức mãng. Bạn thử Enter xem, Excel sẽ báo lỗi #VALUE! liền. Bạn phải Ctrl-Shift-Enter mới được.

Thêm một ví dụ

Đề bài: Lập công thức trong F3:F8, biết rằng nó là tổng số tiền ở cột C nếu năm (year) ở cột B bằng năm ở cột E tương ứng.
(Ví dụ: ở F3 là tổng số tiền của năm 2009)



Các bạn xem lại công thức của bài trên một tí: =IF(AND(B3:B7 > 0), SUM(B3:B7), "")

Công thức này, tôi sẽ sửa lại như sau: =SUM(IF(B3:B7 > 0, B3:B7, "")

Kết quả vẫn tương đương.

Chúng ta sẽ dùng kiểu công thức thứ hai (tính SUM) cho bài này:

=SUM(IF(YEAR($B$3:$B$18) = E3, $C$3:$C$18, 0))

(nhớ nhấn Ctrl-Shift-Enter sau khi gõ công thức)

Và đây là đáp số: (bạn thử lại xem đúng không nhé)

1.     Hàm IFERROR

Hàm IFERROR()

Trả về một giá trị đã xác định nếu công thức có lỗi, hoặc trả về kết quả của công thức nếu công thức đó không có lỗi. Thường dùng IFERROR() để bẫy lỗi trong các công thức.

Cú pháp: IFERROR(value, value_if_error)

value: Là một biểu thức, một công thức cần kiểm tra có lỗi hay không.

value_if_error: Giá trị trả về nếu value gây ra lỗi, là các loại lỗi sau đây: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, hoặc #NULL!.

Ghi chú:

o  Nếu value hoặc value_if_error là một ô rỗng, IFERROR() sẽ xem chúng như những chuỗi rỗng ("").

o  Nếu value là một công thức mảng, IFERROR() trả về một mảng các kết quả cho mỗi ô của mảng trong value

2.     Hàm IFERROR

Hàm IFERROR()

Trả về một giá trị đã xác định nếu công thức có lỗi, hoặc trả về kết quả của công thức nếu công thức đó không có lỗi. Thường dùng IFERROR() để bẫy lỗi trong các công thức.

Cú pháp: IFERROR(value, value_if_error)

value: Là một biểu thức, một công thức cần kiểm tra có lỗi hay không.

value_if_error: Giá trị trả về nếu value gây ra lỗi, là các loại lỗi sau đây: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, hoặc #NULL!.

Ghi chú:

o    Nếu value hoặc value_if_error là một ô rỗng, IFERROR() sẽ xem chúng như những chuỗi rỗng ("").

o    Nếu value là một công thức mảng, IFERROR() trả về một mảng các kết quả cho mỗi ô của mảng trong value