Danh mục các hàm luận lý
AND (logical1, logical2, ...) : Trả về TRUE nếu tất cả các đối số là TRUE, trả về FALSE nếu một hay nhiều đối số là FALSE
FALSE(): Có thể nhập trực tiếp FALSE vào trong công thức, Excel sẽ hiểu đó là một biểu thức có giá trị FALSE mà không cần dùng đến cú pháp của hàm này
IF (logical_test, value_if_true, value_if_false) : Dùng để kiểm tra điều kiện theo giá trị và công thức
IFERROR (value, value_if_error) : Nếu lỗi xảy ra thì... làm gì đó
NOT (logical) : Đảo ngược giá trị của các đối số
OR (logical1, logical2, ...) : Trả về TRUE nếu một hay nhiều đối số là TRUE, trả về FALSE nếu tất cả các đối số là FALSE
TRUE(): Có thể nhập trực tiếp TRUE vào trong công thức, Excel sẽ hiểu đó là một biểu thức có giá trị TRUE mà không cần dùng đến cú pháp của hàm này
Hàm IF()
AND (logical1, logical2, ...) : Trả về TRUE nếu tất cả các đối số là TRUE, trả về FALSE nếu một hay nhiều đối số là FALSE
FALSE(): Có thể nhập trực tiếp FALSE vào trong công thức, Excel sẽ hiểu đó là một biểu thức có giá trị FALSE mà không cần dùng đến cú pháp của hàm này
IF (logical_test, value_if_true, value_if_false) : Dùng để kiểm tra điều kiện theo giá trị và công thức
IFERROR (value, value_if_error) : Nếu lỗi xảy ra thì... làm gì đó
NOT (logical) : Đảo ngược giá trị của các đối số
OR (logical1, logical2, ...) : Trả về TRUE nếu một hay nhiều đối số là TRUE, trả về FALSE nếu tất cả các đối số là FALSE
TRUE(): Có thể nhập trực tiếp TRUE vào trong công thức, Excel sẽ hiểu đó là một biểu thức có giá trị TRUE mà không cần dùng đến cú pháp của hàm này
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])
Lưu ý:
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.
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 ý:
- 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().
- 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.
- 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().
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:
Và nếu thích, bạn có thể ghép tất cả lại:
Bài toán tính tiền thưởng dựa trên doanh số bán hàngChú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.
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:
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:
Hàm AND()
- 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é:- 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
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: |
| hình 1 |
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...])
Ghi chú:
Hàm OR()
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ú:
- 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.
- 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.
- Nếu dãy không chứa các trị logic, AND() sẽ trả về giá trị lỗi #VALUE!
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...])
Ghi chú:
Áp dụng hàm lý luận vào công thức mãng
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ú:
- 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.
- 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.
- Nếu dãy không chứa các trị logic, OR() sẽ trả về giá trị lỗi #VALUE!
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:
Thêm một ví dụ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.
Đề 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:
Và đây là đáp số: (bạn thử lại xem đúng không nhé)
Hàm IFERROR()(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é)
 |
| Thêm chú thích |
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)
Ghi chú:
Hàm NOT
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!.
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ú:
- 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 ("").
- 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
Cho kết quả là phép phủ định của biểu thức logíc.
Hàm NOT cho kết quả TRUE nếu biểu thức logic là FALSE và ngược lại.
Cú pháp: NOT(logical)____ logical: một biểu thức, một điều kiện kiểu logicVí dụ: NOT(3>2) → TRUE
Hàm NOT cho kết quả TRUE nếu biểu thức logic là FALSE và ngược lại.
Cú pháp: NOT(logical)____ logical: một biểu thức, một điều kiện kiểu logicVí dụ: NOT(3>2) → TRUE
Hàm FALSE và TRUE
Bạn có thể nhập trực tiếp FALSE hoặc TRUE vào trong công thức, Excel sẽ hiểu đó là một biểu thức có giá trị FALSE hoặc TRUE
mà không cần dùng đến cú pháp của hai hàm này.
mà không cần dùng đến cú pháp của hai hàm này.
Nguồn từ:
http://www.giaiphapexcel.com/forum/showthread.php?7183-Excel-s-Logical-Functions-C%C3%A1c-h%C3%A0m-lu%E1%BA%ADn-l%C3%BDBài liên quan:
Làm báo cáo động trên Excel sử dụng Add-in A-tools
Phần mềm kế toán doanh nghiệp A-Excel
0 nhận xét:
Đăng nhận xét