Cách sử dụng hàm If trong Excel

Phần 1 – Hàm If cơ bản

Hàm If – (tiếng Việt là “nếu”) – là hàm điều kiện được ứng dụng rất rộng rãi trong excel bởi cách dùng đơn giản và nó hỗ trợ mạnh mẽ cho công việc khi kết hợp cùng các hàm khác trong excel.  Ứng dụng làm kế toán, nó được dùng phổ biến để tạo lập công thức trên các file kế toán excel, ứng dụng trong tính lương, thuế thu nhập cá nhân. Trong quản trị bán hàng, người ta dùng hàm if để xác định các khách hàng có doanh số lớn, công nợ lớn, kết hợp với các hàm khác tính tuổi nợ. Trong công tác nhân sự, người ta dùng nó để xác định đối tượng được nâng bậc lương theo thâm niên … Về cơ bản, đây là hàm có ứng dụng rất rộng rãi trong excel.

Công thức của hàm If:

=If(Logical_test,[Value_if_true,[Value_if_false])

Diễn giải:

=If (Biểu thức logic a, kết quả nếu a xảy ra, kết quả nếu a không xảy ra)

Giản lược hơn:

=If(biểu thức logic a, kết quả b, kết quả c)

Công thức này có thể được hiểu đơn giản như sau:

Nếu điều kiện a đúng (xảy ra) thì kết quả nhận được là b, nếu a sai (không xảy ra) ta đi xem xét các điều kiện khác.

Lưu ý, biểu thức logic A có thể là một đẳng thức (A=B) hoặc một bất đẳng thức (A<>B, A>B, A<B, A >= B, A<=B)

Ví dụ

Để hiểu rõ hơn về cách sử dụng của hàm if, chúng ta hãy cùng nhau xem ví dụ bên dưới:

if-1

Yêu cầu ở đây là cần xác định với doanh số theo hệ số của tháng như vậy, từng nhân viên kinh doanh có đạt chỉ tiêu hay không.

Như chúng ta thấy, bài toán của chúng ta là đặt ra một điều kiện (nếu) để gán vào một giá trị (thì), do đó chúng ta dùng hàm If để thực hiện. Đầu tiên, chúng ta xác định biểu thức logic a là gì, sau đó sẽ xác định kết quả khi a đúng và kết quả khi a sai.

Trong ví dụ trên ta thấy:

Biểu thức logic a ở đây là sự so sánh giữa doanh số theo hệ số (Cột J:J) và doanh số theo chỉ tiêu (Cột B:B). Khi so sánh nó sẽ có 3 trường hợp: nhỏ hơn, lớn hơn, bằng nhau. Tuy nhiên bài toàn chỉ yêu cầu chúng ta tìm ra 2 kết quả, tương ứng với 2 phép so sánh: nhỏ hơn (Không đạt), lớn hơn hoặc bằng (Đạt). Do vậy, ta chỉ cần chọn 1 trong 2 bất đẳng thức để làm logical_test (Biểu thức logic a). Ở đây chúng ta sẽ chọn trường hợp doanh số theo hệ số nhỏ hơn doanh số theo chỉ tiêu.

Tại ô J4, ta lập công thức như trên hình:

=If(J4<B4; “Không đạt”; “Đạt”)

Lưu ý, nếu chúng ta muốn viết 1 chuỗi các ký tự (bao gồm cả việc gõ chữ, số hoặc ký tự đặc biệt) vào các thông số của 1 hàm trong excel, ta để nó trong dấu “ ” thì khi đó excel mới hiểu được (excel sẽ hiểu bạn muốn đặt vào đó 1 đoạn text – chuỗi ký tự). Nếu không nó sẽ mặc định là bạn muốn viết 1 hàm nào đó (Excel mặc định 1 hàm sẽ bắt đầu bằng chữ cái. Nếu bạn gõ Text mà không có dấu “ ”, nó hiểu bạn đang muốn gõ 1 hàm nào đó và khi excel không nhận dạng được tên hàm, cấu trúc hàm, nó sẽ không cho ra kết quả)

Chúng ta cùng đi phân tích các thành phần của hàm if được sử dụng trong ví dụ trên.

  • Logical test tại ô K4 chính là J4<B4
  • Kết quả nếu J4<B4 là Không đạt
  • Nếu J4 không nhỏ hơn B4 (Doanh số theo hệ số không nhỏ hơn doanh số theo chỉ tiêu) thì ta đi xem xét các trường hợp còn lại. Ở đây ta chỉ còn 1 trường hợp là J4>=B4. Như vậy ta cho ngay kết quả nếu J4>=B4 là Đạt. (Nếu còn nhiều hơn 1 trường hợp, ta phải dùng tới các hàm if khác ở thông số value_if_false). Chú ý là chúng ta không nên giới hạn hàm if khi hiểu là nếu a sai thì cho ra kết quả c. Ở đây chúng ta cần khẳng định lại, nếu a sai, ta sẽ đi xem xét các trường hợp khác.

Chúng ta cùng xem ví dụ sau để hiểu hơn về cách dùng hàm if, đặc biệt trong trường hợp biểu thức logic a không đúng (không xảy ra) và còn nhiều hơn 1 trường hợp khác (logical_test).

Ví dụ

if-2

Xem xét ví dụ trên ta thấy, thành phần value_if_false của công thức trên được sử dụng thêm tới 2 hàm if khác lồng vào nhau để cho ra kết quả vì sau trường hợp doanh số theo hệ số/doanh số theo chỉ tiêu (tạm gọi là x) x>=200%, chúng ta còn phải xem xét tới 3 trường hợp khác là x >=150% đồng thời x<200%, x >=100% đồng thời x<150%, và x<100%.

Ở đây, chúng ta chưa phân tích bài toán này mà chỉ đang phân tích về thành phần value_if_false trong hàm if để không bị giới hạn bởi suy nghĩ “giá trị nếu logical_test sai” chỉ là 1 giá trị hoặc 1 trường hợp. Bài toán này sẽ được phân tích tiếp ở trong phần II – Sử dụng hàm if kết hợp với các hàm cơ bản khác.

Phần II – Sử dụng hàm if kết hợp với các hàm cơ bản

Kết hợp các hàm trong excel để đạt hiệu quả công việc cao là một điều thú vị và không đơn giản. Đối với những người đam mê excel thì việc kết hợp các hàm excel để giải quyết một bài toán có thể được xem là một thú vui đầy thách thức và kích thích. Tuy nhiên, với những người mới học và tìm hiểu excel, chúng ta chỉ ứng dụng excel để giải quyết công việc một cách hiệu quả và tốn ít thời gian nhất là được. Chính vì vậy, ngay bây giờ chúng ta sẽ cùng nhau tìm hiểu và ứng dụng hàm if, kết hợp với một số hàm cơ bản trong excel để giải quyết công việc.

1. Hàm if kết hợp với hàm if

Hàm if có thể kết hợp với nhiều hàm khác nhau để tạo ra hiệu quả của bài toán. Tuy nhiên một trong những cách kết hợp rất phổ biến là hàm if lồng trong hàm if (If kết hợp với if). Hàm if kết hợp với chính nó thường dùng để xử lý bài toán lựa chọn nhiều khung (cấp/mức) giá trị khác nhau. Trong kế toán nó dùng để tính thuế thu nhập cá nhân, tính lương, gán bậc lương, hệ số … Hành chính nhân sự dùng if kết hợp if để phân loại, lập danh sách thưởng lễ, tết theo chức vụ, lập thang bảng lương … Người bán hàng dùng để phân nhóm khách hàng theo doanh số …

Chúng ta hãy cùng xem ví dụ dưới đây để hiểu cách sử dụng hàm if kết hợp với if nhé.

if-2

Ở phần 1, chúng ta đã được xem qua ví dụ này để thấy, hàm if có khả năng tự nó mở rộng ở phần value_if_false. Nói cách khác, đây là cách mà chúng ta sử dụng các hàm if lồng vào nhau, kết hợp với nhau để giải quyết bài toán này.

Với yêu cầu của bài toán là xác định (gán, chọn, áp đặt) một mức thưởng doanh thu (trong nhiều mức thưởng) theo tỷ lệ giữa doanh thu theo hệ số với chỉ tiêu doanh thu (điều kiện/biểu thức logic).

Công thức của hàm này tại ô L4 như sau:

=IF(J4/B4>=200%;5%;IF(J4/B4>=150%;2%;IF(J4/B4>=100%;1%;0)))

Ở đây tôi chọn điều kiện Doanh thu theo hệ số (các giá trị tại cột J:J)>= 200% chỉ tiêu doanh thu (các giá trị tại cột B:B) để làm biểu thức logic đầu tiên. Có một lưu ý rằng, với một dải giá trị liên tiếp, ta thường chọn giá trị đầu, hoặc cuối để làm biểu thức logic đầu tiên trong hàm, vì nó sẽ giúp giảm được số hàm if lồng vào nhau hoặc giảm việc sử dụng các hàm hỗ trợ khác. Mục tiêu cuối cùng là làm giảm độ dài và phức tạp của hàm.

Ta cùng diễn giải công thức trên như sau:

Nếu J4/B4 >=200% là đúng (xảy ra), mức thưởng sẽ là 5%, nếu không đúng, ta đi xem xét trường hợp khác.

Ở đây rõ ràng J4/B4 nhỏ hơn 200% nên biểu thức logic đầu tiên là không đúng hay trường hợp đầu tiên không xảy ra.

Xét đến trường hợp tiếp theo là J4/B4 < 200% và J4/B4 >= 150% (hay 200%>J4/B4>=150%), nếu điều này đúng, giá trị tại ô L4 (mức thưởng của Lưu Ngọc Khánh) sẽ là 1%, nếu không đúng, xem xét trường hợp khác.

Lưu ý:

Có một lưu ý, khi sử dụng hàm if lồng vào nhau, vì sao ta không cần viết If(200%>J4/B4>=150%, value_if_true, value_if_false) ? Bởi vì trường hợp đầu tiên ta xét J4/B4 >=200%, nên trường hợp tiếp theo mặc định là xem xét J4/B4 <200%, do đó chỉ cần viết hàm if tiếp theo là If(J4/B4>=150%, value_if_true, value_if_false). Các trường hợp sau chắc không cần phân tích thêm.

Tới trường hợp cuối cùng, ta không còn xem xét thêm trường hợp nào khác (nghĩa là tất cả các trường hợp khác đều cho cùng 1 giá trị) thì ta không phải đặt thêm 1 hàm if nữa mà tại thành phần value_if_false của hàm if trong cùng (lớp nhỏ nhất), ta chỉ cần gán vào đó 1 giá trị là xong.

Vậy ta có thể lồng bao nhiêu hàm if với nhau theo cách như trên?

Câu trả lời là từ excel 2007 đến 2013, chúng ta có thể lồng tới 64 hàm if theo cách trên. Tuy nhiên, chắc dân văn phòng chẳng có ai dùng tới nhiều hàm if đến vậy.

Ta cùng tham khảo thêm một ví dụ nữa về ứng dụng hàm if kết hợp với if trong tính lương để xác định thuế thu nhập cá nhân phải nộp.

if-3

Công thức tại ô AC9 như sau:

= IF( AB9>80000000; (AB9-80000000)*35% + 18150000; IF( AB9>52000000; (AB9-52000000)*30% + 9750000; IF(AB9>32000000;(AB9-32000000)*25%+4750000; IF( AB9>18000000; (AB9-18000000)*20% + 1950000; IF( AB9>10000000; ( AB9-10000000)*15% + 750000;  IF( AB9>5000000; (AB9-5000000)*10% + 250000; IF( AB9>0; AB9*5%; 0)))))))

Ngoài ra, hàm If còn có thể kết hợp với nhiều hàm khác như: Hàm logic (And, Or), hàm tìm kiếm (vlookup, hlookup), Hàm toán học (Sum, Sumif, Sumifs, count, countif, countifs), Hàm xử lý chuỗi (Len, Left, right …), hàm ngày tháng (Day, Month, Year) …