Hàm Vlookup và hàm IF là các hàm quan trọng và cơ bản trong Excel. Khi kết hợp 2 hàm Vlookup IF với nhau, bạn có thể lọc giá trị theo điều kiện cụ thể, giải được nhiều bài toán, thống kê dữ liệu khác nhau như hiện thông tin nhân viên mà chỉ cần nhấn mã.
Với bài thống kê có điều kiện nào đó và yêu cầu người dùng phải điền thông tin dựa theo điều kiện đó, bạn có thể sử dụng kết hợp hàm Vlookup với hàm IF, như trong bảng xếp loại đánh giá. Ví dụ dưới đây sẽ giúp bạn hiểu rõ hơn cách sử dụng hàm Vlookup và hàm IF để lọc giá trị có điều kiện.
- Cách tự động hiện tên khi nhập mã trong Excel
- Cách kết hợp hàm Sumif và hàm Vlookup trên Excel
Cách dùng hàm Vlookup với hàm IF
Đây là bảng kết quả thi và đánh giá đậu hoặc trượt của học sinh. Chúng ta đã có điểm chuẩn của từng khối thi để biết được học sinh có đủ điểm đậu, hoặc trượt. Bạn cần nhập kết quả thi cho từng học sinh.
Hàm IF sẽ đóng vai trò tìm kiếm những kết quả thỏa mãn với điểm chuẩn, từ đó đưa ra kết luận đậu hay trượt. Sau đó kết hợp với hàm Vlookup để lọc những giá trị thỏa mãn từng học sinh. Cuối cùng 2 hàm sẽ cho ra kết quả cần tìm.
Hàm Vlookup sẽ có công thức là:
- VLOOKUP[lookup_value,table_array,col_index_num,[range_lookup]]
Hàm IF có công thức là:
- IF [Logical_test, Value_if_True, Value_if_False]
Công thức khi lồng hàm Vlookup và hàm IF với nhau:
- IF[Logical_test>=Vlookup[lookup_value,table_array,col_index_num,[range_lookup]],Value_if_True, Value_if_False]]
Bước 1:
Tại ô kết quả đầu tiên, bạn hãy nhập công thức lồng 2 hàm: =IF[C2>=Vlookup[B2,$B$10:$C$13,2,0],"Đậu","Trượt".
Trong đó C2>=Vlookup với C2 là cột Điểm mà các học sinh đã đạt được. Khi C2>=Vlookup thỏa mãn điều kiện sẽ ra kết quả Đậu, ngược lại không thỏa mãn là sẽ Trượt.
Tiếp đến B2,$B$10:$C$13,2,0 là tham chiếu cột nhóm nằm ở cột B và dòng thứ 2 và thứ tự dòng là 10 đến cột chi tiêu ở cột C với giá trị cuối cùng ở dòng 13.
2, 0 với 2 là giá trị cột số 2 tính từ cột tham chiếu trong bảng tham chiếu. 0 là nếu không tìm được giá trị thỏa mãn hàm Vlookup thì trả về 0.
Lưu ý, để hiển thị ký tự dạng liệt kê $ bạn cần khoanh vùng như bình thường rồi nhấn F4.
Bước 2:
Khi nhấn Enter chúng ta sẽ được kết quả cho học sinh đầu tiên là Đậu, ứng với điểm chuẩn bên dưới. Học sinh đầu tiên được 18 điểm cao hơn với điểm chuẩn khối A nên sẽ có kết quả là Đậu như hình.
Để thực hiện tiếp kết quả cho những học sinhh còn lại, bạn chỉ cần kéo kết quả ở ô đầu tiên xuống các dòng của cột Kết quả. Cuối cùng chúng ta sẽ được bảng đánh giá như hình.
Việc kết hợp 2 hàm Vlookup và hàm IF như vậy sẽ giúp bạn tiết kiệm thời gian trong việc tìm kiếm giá trị có điều kiện, mà không cần phải lọc giá trị lần lượt theo hàm Vlookup rồi từ đó thực hiện tiếp hàm IF. Bảng tìm kiếm ít nhất phải có 2 cột, 1 cột chứa điều kiện tìm kiếm, 1 cột là giá trị của điều kiện đó.
Giới thiệu cách tính lương cho nhân viên trong bài tập mẫu hàm Vlookup, sử dụng hàm này để tìm ra tên phòng ban, hoặc phụ cấp chức vụ khi biết mã nhân viên. Đây là một trong những bài toán khá phổ biến trong excel
- Hàm if-hàm điều kiện Cú pháp=IF [điều kiện, giá trị 1, giá trị 2] Ý Nghĩa: Nếu như “điều kiện” đúng thì kết quả hàm trả về là “giá trị 1”, ngược lại trả về “giá trị 2
- Hàm vlookup-dò tìm giá trị ở một cột nào đó Cú pháp =Vlookup[lookup_value, table_array, col_index_num, [range_lookup] lookup_value: Giá trị dùng để dò tìm table_array: Bảng giá trị dò col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò. range_lookup: Phạm vi tìm kiếm
Nếu bạn chỉ mới bắt đầu học về hàm trong excel thì có lẽ sự kết hợp giữa if và vlookup sẽ khá phức tạp, nhưng khi sử dụng đã quen, bạn sẽ thấy rằng đây là sự kết hợp vô cùng tuyệt vời cho nhiều công việc văn phòng.
Câu hỏi
Câu 1]Căn cứ vào ký tự đầu tiên bên trái của Mã Nhân Viên và BẢNG PHÒNG BAN
Câu 2]hãy điền tên phòng ban cho các nhân viên ở cột Phòng.
Câu 3]Căn cứ vào số liệu ở cột Chức Vụ và BẢNG PHỤ CẤP CHỨC VỤ, hãy tính tiền Phụ Cấp Chức Vụ cho mỗi nhân viên
Câu 4]Tính Thực Lãnh cho mỗi nhân viên biết rằng Thực Lãnh = Phụ Cấp Chức Vụ + Lương cơ bản * hệ số lương [hệ số lương = 4.0]
Hàm Vlookup và hàm If được xem là bộ đôi quyền lực không thể thiếu mỗi khi thao tác trên nhiều bảng tính thỏa mãn các điều kiện khác nhau hỗ trợ đắc lực cho bạn các công việc tính toán, việc kết hợp hàm Vlookup và hàm IF trong Excel cũng khá đơn giản nếu bạn đã quen dùng exel.
Bên cạnh đó, bạn tham khảo thêm các bài mẫu hàm DURATION cũng được sử dụng nhiều trong Excel là hàm sử dụng trong các nghành tài chính và chứng khoán, về việc tính toán lãi suất, phần trăm tiền gửi, cú pháp hàm này khá đơn giản, được giới thiệu trong các bài mẫu hàm DURATION của chúng tôi, mời các bạn theo dõi.