Mẹo Google Sheets · 11 phút đọc
Muốn xét nhiều điều kiện, bạn có ba cách: lồng nhiều IF vào nhau, dùng IFS (xét lần lượt từng cặp điều kiện – kết quả), hoặc ghép AND/OR khi cần nhiều điều kiện cùng đúng một lúc. Với 5–7 mức trở lên như bậc thưởng doanh số, tra một bảng nhỏ gọn hơn hẳn lồng IF. Bài đi qua từng cách với ví dụ xếp loại học sinh, thưởng doanh số, phụ cấp, kèm cách xử lý ô trống, so sánh chữ và những lỗi làm công thức ra sai mà không báo.
IF hỏi một câu có hoặc không. Đúng thì trả về giá trị thứ nhất, sai thì trả về giá trị thứ hai. Mọi công thức nhiều điều kiện trong bài đều xây từ khối này.
=IF(điều_kiện; giá_trị_nếu_đúng; giá_trị_nếu_sai)=IF(B2>=5;"Đạt";"Chưa đạt")Thử lại: B2 = 6,2 thì 6,2 ≥ 5 đúng, ra Đạt. B2 = 4,9 ra Chưa đạt.
Dấu thập phân là dấu phẩy
Ở vùng Việt Nam, số lẻ viết 6,5 chứ không phải 6.5, và đối số cách bằng dấu chấm phẩy. Gõ 6.5 trong công thức thì Google Sheets báo lỗi phân tích công thức. Vùng của file nằm ở Tệp › Cài đặt.
Khi có nhiều hơn hai kết quả, bạn đặt một IF mới vào chỗ "giá trị nếu sai" của IF trước. Ví dụ xếp loại theo thang tự đặt (không phải quy chế đánh giá của Bộ): từ 8 trở lên là Giỏi, từ 6,5 là Khá, từ 5 là Trung bình, còn lại là Yếu.
=IF(B2>=8;"Giỏi";IF(B2>=6,5;"Khá";IF(B2>=5;"Trung bình";"Yếu")))Thử lại: B2 = 7,8. IF thứ nhất: 7,8 ≥ 8 sai, chuyển sang IF thứ hai. 7,8 ≥ 6,5 đúng, trả về Khá và dừng, không xét tiếp. B2 = 9,1 ra Giỏi; B2 = 5 ra Trung bình (vì dùng ≥); B2 = 3,5 ra Yếu.
Đọc IF lồng từ trái sang phải: mỗi IF chỉ chạy tới khi IF đứng trước nó sai. Vì vậy với dấu ≥, mốc lớn nhất phải đứng đầu. Excel cho lồng tối đa 64 IF, nhưng quá 3–4 tầng là đã khó sửa.
IFS nhận từng cặp điều kiện – kết quả và trả về kết quả của điều kiện đúng đầu tiên. Cùng ví dụ xếp loại:
=IFS(B2>=8;"Giỏi";B2>=6,5;"Khá";B2>=5;"Trung bình";TRUE;"Yếu")Thử lại: B2 = 7,8 cho ra Khá như IF lồng. B2 = 3,5: ba điều kiện đầu đều sai, đến TRUE (luôn đúng) nên ra Yếu.
IFS không có "giá trị nếu sai"
Theo trợ giúp của Google, nếu mọi điều kiện đều sai thì IFS trả về #N/A. Bỏ cặp TRUE;"Yếu" đi thì học sinh 3,5 điểm sẽ hiện #N/A. Luôn kết thúc IFS bằng TRUE và giá trị mặc định.
IFS có trên Google Sheets và Excel 2019, Excel 2021, Microsoft 365. Nếu file sẽ mở bằng Excel 2016 trở về trước, IFS báo lỗi #NAME?, khi đó dùng IF lồng.
AND đúng khi tất cả điều kiện bên trong đều đúng. OR đúng khi chỉ cần một điều kiện đúng. Đặt chúng vào chỗ điều kiện của IF.
Ví dụ bảng lương: C2 là doanh số tháng, D2 là số ngày công, E2 là bộ phận. Quy định thưởng: doanh số từ 100 triệu và đủ 24 công thì thưởng 2.000.000.
=IF(AND(C2>=100000000;D2>=24);2000000;0)Thử lại: C2 = 120.000.000, D2 = 22. Doanh số đạt nhưng 22 < 24 nên AND sai, thưởng 0. Sửa D2 thành 25 thì ra 2.000.000.
Phụ cấp 500.000 cho người ở bộ phận Kho hoặc Giao hàng:
=IF(OR(E2="Kho";E2="Giao hàng");500000;0)Thử lại: E2 = Giao hàng ra 500.000; E2 = Văn phòng ra 0.
Ghép cả hai: chỉ người ở Kho hoặc Giao hàng và đủ 24 công mới có phụ cấp:
=IF(AND(D2>=24;OR(E2="Kho";E2="Giao hàng"));500000;0)Thử lại: D2 = 26, E2 = Kho: điều kiện công đúng, OR đúng, ra 500.000. D2 = 20, E2 = Kho ra 0.
Kết hợp với IFS để có nhiều mức thưởng theo hai tiêu chí: từ 200 triệu và đủ công thưởng 5 triệu, từ 100 triệu và đủ công thưởng 2 triệu, còn lại 0.
=IFS(AND(C2>=200000000;D2>=24);5000000;AND(C2>=100000000;D2>=24);2000000;TRUE;0)Thử lại: C2 = 230.000.000, D2 = 26 ra 5.000.000. C2 = 150.000.000, D2 = 26 thì cặp thứ nhất sai (150 < 200), cặp thứ hai đúng, ra 2.000.000.
Khi điều kiện chỉ là "ô này bằng chữ gì", như phụ cấp theo chức vụ, SWITCH ngắn hơn IF lồng vì không phải lặp lại E2= ở mỗi tầng.
=SWITCH(E2;"Trưởng nhóm";1500000;"Tổ phó";800000;"Nhân viên";300000;0)Thử lại: E2 = Tổ phó ra 800.000. E2 = Thực tập ra 0. Bỏ số 0 cuối thì chức vụ không có trong danh sách sẽ hiện #N/A.
SWITCH chỉ so bằng, không so lớn hơn, nhỏ hơn được, nên không dùng cho xếp loại theo điểm. Giống IFS, SWITCH có trên Google Sheets và Excel 2019 trở lên. Để chức vụ luôn gõ đúng chữ, cho cột đó chọn từ danh sách — xem cách tạo danh sách thả xuống trong Google Sheets.
Thưởng theo 5 bậc doanh số mà viết bằng IF thì công thức dài, và mỗi lần công ty đổi mốc bạn phải sửa trong từng ô. Cách gọn hơn: kẻ một bảng mốc, cột "từ" xếp tăng dần, rồi tra gần đúng.
| G: Doanh số từ | H: Tỷ lệ thưởng | |
|---|---|---|
| 2 | 0 | 0% |
| 3 | 50.000.000 | 1% |
| 4 | 100.000.000 | 2% |
| 5 | 200.000.000 | 3% |
| 6 | 500.000.000 | 5% |
=VLOOKUP(C2;$G$2:$H$6;2;TRUE)=XLOOKUP(C2;$G$2:$G$6;$H$2:$H$6;0;-1)Thử lại: C2 = 150.000.000. Mốc lớn nhất không vượt 150 triệu là 100.000.000, nên cả hai công thức ra 2%. Tiền thưởng =C2*VLOOKUP(C2;$G$2:$H$6;2;TRUE) = 150.000.000 × 2% = 3.000.000. C2 = 500.000.000 đúng mốc ra 5%; C2 = 30.000.000 ra 0%.
Cách này tính một tỷ lệ cho toàn bộ doanh số. Nếu công ty tính luỹ tiến từng phần (phần dưới 50 triệu một tỷ lệ, phần trên tỷ lệ khác) thì cần công thức khác, đừng dùng nguyên công thức trên. Bảng mốc cũng chạy được với xếp loại học sinh: cột G là 0; 5; 6,5; 8 và cột H là Yếu, Trung bình, Khá, Giỏi.
Ô trống được hiểu là 0 khi so sánh số. Học sinh chưa có điểm sẽ bị xếp Yếu, nhân viên chưa nhập doanh số bị tính thưởng 0 dù tháng chưa chốt. Bọc thêm một lớp kiểm ô trống ở ngoài cùng:
=IF(B2="";"";IFS(B2>=8;"Giỏi";B2>=6,5;"Khá";B2>=5;"Trung bình";TRUE;"Yếu"))Thử lại: B2 trống ra ô trống; gõ 8 vào B2 ra Giỏi. Khi cần nhiều ô cùng phải có dữ liệu, dùng OR: =IF(OR(C2="";D2="");"";IF(AND(C2>=100000000;D2>=24);2000000;0)).
Nên dùng B2="" thay cho ISBLANK(B2). Ô chứa công thức trả về "" nhìn trống nhưng ISBLANK vẫn ra FALSE, còn B2="" ra TRUE ở cả hai trường hợp.
| Tình huống | Kết quả | Cách xử lý |
|---|---|---|
| E2 = "kho", điều kiện E2="Kho" | Đúng | IF không phân biệt chữ hoa, chữ thường. Cần phân biệt thì dùng EXACT(E2;"Kho") |
| E2 = "Kho " (thừa dấu cách) | Sai | Dấu cách thừa làm chữ khác đi. Dùng TRIM(E2)="Kho" hoặc cho chọn từ danh sách |
| E2 = "Kho tổng", muốn bắt chữ "Kho" | Sai với E2="Kho" | Tìm một phần: IF(ISNUMBER(SEARCH("kho";E2));…) |
| B2 là điểm gõ dạng chữ "7,8" | B2>=8 ra Đúng | Chữ luôn được coi là lớn hơn mọi số, nên điểm dạng chữ bị xếp Giỏi. Đổi cột về dạng số |
| Viết điều kiện B2>="5" (số trong ngoặc kép) | So sánh sai kiểu | Số trong điều kiện không đặt trong ngoặc kép; chỉ chữ mới cần |
Ô số bị lưu thành chữ thường gặp khi dán từ phần mềm khác hoặc từ web — dấu hiệu là số nằm lệch trái và SUM ra 0. Cách nhận ra và đổi lại có trong bài sửa lỗi SUM bằng 0 trong Google Sheets.
| Triệu chứng | Nguyên nhân | Sửa |
|---|---|---|
| Cả lớp ra cùng một loại | Sai thứ tự: mốc nhỏ đứng trước với dấu ≥ | Xếp mốc từ lớn xuống nhỏ (với ≤ thì từ nhỏ lên lớn) |
| #N/A ở vài dòng | IFS hoặc SWITCH thiếu giá trị mặc định | Thêm TRUE;"…" cuối IFS, thêm giá trị cuối cho SWITCH |
| Báo lỗi phân tích công thức | Thiếu hoặc thừa dấu ngoặc; dùng dấu phẩy thay chấm phẩy; số lẻ viết 6.5 | Đếm ngoặc: mỗi IF( cần một ) ở cuối; vùng Việt Nam dùng ; và 6,5 |
| Điểm 5 ra Yếu | Dùng > thay vì ≥ ở mốc biên | Đọc lại quy định "từ 5" hay "trên 5" |
| Kết quả 2000000 không cộng được | Viết "2000000" trong ngoặc kép nên thành chữ | Bỏ ngoặc kép quanh số |
| #NAME? khi mở bằng Excel | IFS, SWITCH, XLOOKUP không có ở Excel bản cũ | Dùng IF lồng hoặc VLOOKUP |
Công thức dài thì viết thử từng tầng trong ô riêng, chạy đúng rồi mới ghép lại; trước khi bấm Enter, đếm số IF( phải bằng số dấu ) ở cuối. Muốn công thức đã đúng không bị ai gõ đè, xem cách khoá ô công thức trong Google Sheets.
Trong một bảng lương thật, IF xuất hiện ở rất nhiều chỗ: có đủ công chuẩn không, khoản thưởng có chịu thuế không, lương đóng bảo hiểm có thấp hơn sàn không, thực nhận có bị âm không. Mỗi điều kiện nhỏ, nhưng sai một chỗ là sai cả bảng. Cách an toàn là để mỗi bước tính một cột riêng, như cách tính từng bước trong bài cách tính lương nhân viên bằng Excel.
Với bảng điểm, mẫu Quản lý lớp học PRO có tab bài kiểm tra theo thang điểm và hệ số, tự quy về thang 10, tính trung bình và thứ hạng, kèm phiếu báo cáo học tập từng học sinh.
Kết quả như nhau. IFS dễ đọc và ít ngoặc hơn khi có từ 4 mức trở lên, nhưng phải thêm TRUE ở cuối và không chạy trên Excel 2016 trở về trước. File cần mở bằng Excel cũ thì dùng IF lồng.
Thường là IFS hoặc SWITCH không có giá trị mặc định nên khi không điều kiện nào đúng thì ra #N/A. Thêm TRUE;"giá trị" vào cuối IFS, hoặc thêm giá trị cuối cho SWITCH. Nếu IF có VLOOKUP bên trong thì #N/A có thể đến từ VLOOKUP không tìm thấy.
Tuỳ vùng của file trong Tệp › Cài đặt. Vùng Việt Nam dùng dấu chấm phẩy giữa các đối số và dấu phẩy cho số lẻ: =IF(B2>=6,5;"Khá";"Chưa"). Vùng Hoa Kỳ dùng dấu phẩy giữa đối số và dấu chấm cho số lẻ.
Đặt kiểm tra ô trống ở ngoài cùng: =IF(B2="";"";công_thức_cũ). Ô trống không được kiểm sẽ bị coi là 0, nên học sinh chưa có điểm bị xếp loại thấp nhất.
Từ khoảng 5 mức, hoặc khi mốc hay thay đổi. Bảng mốc xếp tăng dần cộng VLOOKUP với TRUE (hoặc XLOOKUP chế độ -1) cho kết quả như IF lồng, và đổi mốc chỉ cần sửa bảng.
Mẫu khác liên quan: Quản lý lớp học PRO.
Google Sheets hay Excel: so chi phí, làm chung, dùng trên điện thoại, khi mất mạng, giới hạn dữ liệu, Apps Script với VBA và các công thức khác nhau.
Cách khoá ô công thức trong Google Sheets bằng Bảo vệ trang tính và dải ô: chỉ cảnh báo hay chặn hẳn, khoá cả trang chừa ô nhập, những gì bảo vệ không làm được.
Sửa lỗi ngày tháng Google Sheets: gõ 03/07 ra 7 tháng 3, ngày dạng chữ, TODAY lệch ngày. Đổi vùng Việt Nam, múi giờ GMT+7 và công thức sửa cột cũ.
Hàm SUM ra 0 hoặc thiếu tiền dù cột đầy số? Số đang là chữ. Cách nhận ra bằng ISTEXT, LEN và 4 cách sửa lỗi SUM bằng 0 trong Google Sheets, Excel.
Xem thêm mẹo và video hướng dẫn trên kênh YouTube HiTaVa, hoặc đọc các bài khác trong chủ đề Mẹo Google Sheets.
Cần hỏi thêm?Zalo 0935077673 (Hải Vân)
