Chào các bạn, lâu lắm rồi mình mới có thời gian viết bài trở lại, do công việc nhiều quá không sắp xếp được, lần trở lại này mình mang đến cho các bạn một chủ để mà thường xuyên được áp dụng trong excel, đó là cách sử dụng các hàm tìm kiếm tương ứng với từng yêu cầu cụ thể.
Trong excel chúng ta có nhiều hàm sử dụng để tìm kiếm, hàm có sẵn của excel, kết hợp các hàm với nhau hay rộng hơn là sử dụng vba excel để làm nhưng chung quy lại các bạn dùng cách gì thì kết quả tìm kiểm trả về sẽ có 4 dạng cơ bản đó là:
1. Tìm kiếm trả về 1 kết quả với 1 điều kiện.
2. Tìm kiếm trả về 1 kết quả với 2 điều kiện.
3. Tìm kiếm trả về nhiều kết quả trong cùng một ô với 1 điều kiện.
4. Tìm kiếm trả về nhiều kết quả cho từng ô với 1 điều kiện.
Bây giờ chúng ta đi vào từng trường hợp cụ thể
1. Tìm kiếm trả về 1 kết quả với 1 điều kiện.
Đây là kiểu tìm kiếm phổ biến nhất trong excel, các bạn có thể đoán hàm được sử dụng ở đây là gì rồi phải không nào
Đó chính là hàm quốc dân VLOOKUP, và ở ví dụ này chúng ta sẽ sử dụng hàm là
=VLOOKUP(B2;$G$2:$H$2;2;0)
Kết quả trả về không có gì đáng để bàn cãi cả, các bạn có thể đối chiếu lại để kiểm tra, vì chỉ tìm kiếm với 1 điều kiện là dò theo SBD do vậy kết quả toàn toàn chính xác, do SBD ở trường hợp này là duy nhất
Bây giờ chúng ta sẽ đi vào tìm kiếm với hai điều kiện, cụ thể ở đây chúng ta sẽ tìm điểm theo họ và tên của học viên, mà không tìm theo số báo danh nữa, bây giờ các bạn thử sử dụng hàm VLOOKUP xem thế nào nhé
=VLOOKUP(C2;$H$2:$I$6;2;0)
Copy xuống các ô còn lại ta có kết quả như bình thường, tưởng không có gì phải bàn nhưng hãy nhìn kỹ lại nào, ở đây Nguyễn Văn A có tới hai người ở hai lớp khác nhau, nhưng điểm trả về lại như nhau, vậy là có gì không đúng ở đây rồi, đó cũng chính là hạn chế của hàm VLOOKUP
Nhưng không sao cả, các bạn đừng lo lắng, chúng ta sẽ sử dụng hàm INDEX và MATCH để thay thế hàm VLOOKUP, và công thức triển khai là
=INDEX($G$2:$I$6;MATCH(1;(B2=$G$2:$G$6)*(C2=$H$2:$H$6);0);3)
Nếu bạn không sử dụng office 365 thì hãy nhấn Ctrl + Shift + Enter để kết thúc công thức trên, và nó sẽ thành như thế này
{=INDEX($G$2:$I$6;MATCH(1;(B2=$G$2:$G$6)*(C2=$H$2:$H$6);0);3)}
Dấu { } được sử dụng ở công thức mảng
Giờ chúng ta sẽ đi qua trường hợp thứ 3, với trường hợp này một học viên có thể học ở nhiều lớp học, và nhiệm vụ chúng ta là làm sao để kết quả trả về đúng toàn bộ lớp học mà học viên đó tham gia
Nếu các bạn sử dụng hàm VLOOKUP thì hoàn toàn không được rồi, nếu dùng INDEX và MATCH thì sao, mình để trường hợp này lại cho các bạn tự tìm hiểu nhé, còn mình sẽ giới thiệu 1 hàm khác hay hơn nhiều
=TEXTJOIN(",";TRUE;IF($F$2:$F$10=$B2;$G$2:$G$10;""))
Kết quả, đúng chưa nào
Nhưng nếu bạn sử dụng office cũ hơn, không có hàm TEXTJOIN thì sao, phải nâng cấp office? không cần phải làm vậy đâu nhé, hãy sử dụng vba, các bạn vào Developer - Visual Basic
Và copy đoạn code sau dán vào khung soạn thảo
Quay trở lại file excel, các bạn gõ =VLOOKUPPLUS(B2;$F$2:$F$10;2) vào
Cuối cùng là kết quả trả về theo từng ô
=IFERROR(INDEX($B$2:$B$10;SMALL(IF(D$1=$A$2:$A$10;ROW($B$2:$B$10)-1;"");ROW()-1));"")
Như mình đã nói ở trên, hãy sử dụng Ctrl + Shift + Enter để kết thúc công thức nếu bạn không sử dụng office 365 nhé
Copy xuống bên dưới kết quả trả về như hình chính là kiểu thứ 4 mà mình muốn nói đến
Mình có lưu file ví dụ lại cho bạn nào cần để tham khảo nhanh, các bạn có thể tải về
Mình không giải thích cụ thể từng công thức, mà dành phần đó cho các bạn tự suy nghĩ, để hiểu hơn.
Chúc các bạn thành công
