Cách sử dụng hàm VLOOKUP – Từ cơ bản – Nâng cao (PRO)

Bạn được học về: Cách sử dụng hàm vlookup, Video hướng dẫn, Download 6 Bài tập thực hành có lời giải. Học cách kết hợp với hàm Left, Match, IF, Value, ISNA, Right, Mid, And, Columns,… Ad sẽ hướng dẫn các bạn từ những kiến thức cơ bản tới nâng cao về hàm này. Bởi lẽ đó là một trong những hàm PHẢI BIẾT của bất kỳ ai muốn sử dụng excel để học tốt và làm việc thành công. Bài viết có lượng kiến thức khá lớn từ cơ bản tới nâng cao. Do đó sẽ khá dài và là một trong những bài viết có độ sâu – chất lượng nhất hiện nay về hàm vlookup. Mục tiêu của ad là bạn không cần phải tìm kiếm đâu nữa để có thể thành thạo được hàm này. Do đó, Hãy lưu lại bài viết để đọc bất kỳ khi nào cần hoặc quên một điểm  nào đó. Và chia sẻ cho bạn bè mình cùng biết. Lưu lại bài viết trên trình duyệt: Các bạn có thể lưu lại bài viết bằng cách đánh dấu bài viết vào trình duyệt (nhấn Ctrl + D nếu là chrome) để mở lại bài viết khi cần xem lại nhé. Lưu lại trên Facebook: Copy link bài viết rồi đăng lên Facebook để lưu lại xem khi cần Tóm lược các kiến thức bạn sẽ được học trong bài chia sẻ này (click vào phần muốn xem) 1. Định nghĩa, Cú pháp & Ví dụ minh họa 2. Một số bài tập thực hành 3. Vlookup + IF 4. Vlookup + Left (Mid, Right) 5. Vlookup +Match (Nâng cao) 6. Vlookup + Columns (Nâng cao) 7. Lỗi #N/A & cách khắc phục như thế nào (gặp lỗi khi dùng với Left, Right, Mid) 8. Lỗi #REF & cách khắc phục 9. Tìm kiếm dữ liệu từ file khác, sheet khác (kiến thức nâng cao) 10. Tìm kiếm với 2 hay nhiều điều kiện + Hàm choose (dùng công thức mảng) 11. Tìm kiếm với 2 hay nhiều điều kiện (không dùng công thức mảng) 12. Download 6 bài tập từ cơ bản tới nâng cao cùng với đáp án 13. 6 cách vận dụng vlookup trong kế toán excel: Thu chi, kho, bảng lương, cân đối kế toán, sổ cái,… 14. 20 lưu ý quan trọng khi dùng vlookup để tìm kiếm (Quan Trọng)   Bây giờ thì có thể bắt đầu hành trình Trở thành Master về hàm tìm kiếm theo cột này rồi. 1. Cú pháp, cách sử dụng hàm vlookup trong excel Định nghĩa: Hiểu một cách đơn giản là hàm tìm kiếm giá trị dựa trên điều kiện cho trước (tìm kiếm theo cột) trong excel. Trong excel có nhiều hàm kiếm tìm kiếm tuy nhiên hàm này là hàm được sử dụng nhiều nhất. Cú pháp hàm: Trên đây là cú pháp của hàm bằng cả tiếng anh và tiếng việt. Tại sao ad lại viết cả cú pháp tiếng Anh và tiếng Việt, bởi lẽ khi các bạn dùng excel đa phần là ta dùng giao diện bằng tiếng anh. Và đặc biệt hơn là từ excel 2007 trở đi mỗi khi ta viết hàm thì excel sẽ có phần gợi nhớ tên hàm và cú pháp của hàm đó bằng tiếng anh. Do vậy, khi bạn hiểu rõ về hàm bằng cả Anh và Việt thì khi làm việc thực tế các bạn không gặp trở ngại nào và hiệu suất làm việc là cao nhất. Cú pháp hàm gồm có 4 thành phần: Ví dụ ngắn gọn sau đây sẽ giúp bạn hiểu nhanh về hàm. Bên cạnh đó các ví dụ và bài tập ở phía dưới sẽ giúp bạn hiểu sâu và biết nhiều cách vận dụng hàm trong thực tế làm việc. Yêu cầu: Tìm kiếm phụ cấp của từng nhân viên dựa vào Chức vụ của người đó và Bảng phụ cấp. Gợi ý: Dùng hàm tìm kiếm theo cột để tìm kiếm phụ cấp của từng nhân viên từ Bảng Phụ cấp có sẵn. Bảng phụ cấp gồm 2 phần: Cột số 1: Các chức vụ có trong doanh nghiệp: GĐ, PGĐ, TP, PP, NV Cột số 2: Phụ cấp tương ứng với từng chức vụ. Như vậy ta xác định được các thành phần sau: Chức vụ của nhân viên đang muốn tìm phụ cấp Điều kiện tìm kiếm Bảng phụ cấp là vùng ô H3:I7 Vùng tìm kiếm Là cột số 2 -> Cột chứa giá trị phụ cấp trong bảng phụ cấp Số thứ tự cột chứa giá trị tìm kiếm 0 – là kiểu tìm kiếm chính xác 100%. Kiểu tìm kiếm Bên cạnh đó còn có kiểu tìm kiếm là 1 (gần đúng) -> với thành phần này các bạn không cần phải quá quan tâm tới nó nhé. Chỉ cần biết trên 99% các nhu cầu tìm kiếm khi sử dụng hàm này thì thành phần này đều là 0. TRÌNH TỰ THỰC HIỆN Bước 1: Nhập công thức hàm tìm kiếm theo logic ở trên vào ô D3 với điều kiện tìm kiếm là giá trị của ô C3 Bước 2: Copy công thức ở ô D3 xuống các ô còn lại của cột phụ cấp trong bảng tính lương. Như vậy chỉ mất vài giây là bạn có thể tìm được phụ cấp cho hàng trăm, nghìn nhân viên rồi. Công thức Nhập công thức ở ô D3 như sau: = Vlookup (C3,$H$3:$I$7,2,0) => Hàm vlookup sẽ trả về giá trị phụ cấp của GĐ là 50.000 đ. Chắc hẳn bạn sẽ hỏi tại sao lại có dấu $ trong phần vùng tìm kiếm phải không nào? Điểm này rất quan trọng, không chỉ với hàm vlookup đâu nhé các bạn. Ý nghĩa của dấu $: Một cách ngắn gọn đó là việc cố định vùng tìm kiếm để khi copy công thức ở ô D3 xuống cho các nhân viên còn lại trong bảng lương thì vùng tìm kiếm này không thay đổi. Điều này cho phép hàm vlookup tính toán 1 cách chính xác mà không phải viết đi viết lại hàm khi tìm kiếm phụ cấp cho từng nhân viên. Còn nếu bạn muốn hiểu tận tường về ý nghĩa, cách dùng của dấu $ thì hãy xem ngay bài viết này nhé. Giải thích cơ chế hoạt động của lệnh vlookup: Khi viết hàm như trên, excel sẽ tự động tìm trong cột Chức vụ của bảng chức vụ dòng nào chứa giá trị của ô C3 (GĐ). Khi tìm được dòng đó rồi excel sẽ tự động di chuyển sang 2 cột (số cột ta chỉ định trong hàm vừa viết) tính từ cột mã hàng và lấy giá trị ở ô vừa di chuyển tới để trả kết quả về ô ta viết công thức. Đương nhiên là sau khi nhấn enter rồi. => Bài viết nên xem: Danh sách hàm Excel hay dùng bạn nên biết Copy công thức của ô đầu tiên xuống các ô còn lại: Copy công thức ở ô D3 (nhấn Ctrl+C) Bôi đen các ô còn lại: Vùng ô D4:D12 Dán công thức, nhấn Ctrl + V Như vậy, chỉ với 2 thao tác sao chép – dán: Bạn có thể tìm được phụ cấp của hàng trăm nghìn nhân viên trong nháy mắt rồi. Video hướng dẫn cách sử dụng hàm vlookup siêu dễ hiểu và ngắn gọn   Bài viết tham khảo thêm Trên đây chỉ là một ví dụ đơn giản và ngắn gọn giúp bạn biết được định nghĩa và cú pháp của hàm này. Để hiểu rõ và sâu hơn về hàm này các bạn hãy xem một bài viết khác sau đây: Với bài viết này, các bạn sẽ biết những kiến thức cơ bản nhất như định nghĩa, cấu trúc hàm và ví dụ đơn giản về cách sử dụng. Ngoài ra bài viết cũng cung cấp cho bạn cả video hướng dẫn sử dụng hàm để tìm số dư trong kế toán. Click thẳng vào ảnh để lấy link ảnh >> Copy >> Paste vào Facebook nhé bạn.   2. Bài tâp hàm vlookup với 2 cấp độ: Cơ bản và Nâng cao Đương nhiên là Học phải đi đôi với hành, khi bạn biết cách sử dụng rồi thì phải có bài tập để thực hành thì mới mong thành thạo được. Trong bài viết này ad mới chỉ upload 3 bài tập: 1 cơ bản và 2 nâng cao để các bạn thực hành trước và sẽ up nhiều hơn trong thời gian tới. Nói gì thì nói khi học một cái mới cũng như đi cái xe máy mới, phải chạy roda đã nên hãy làm bài tập cơ bản. Ngon rồi thì làm tiếp bài tập nâng cao. Sau đó tìm các bài tập khác trên Excel.webkynang.vn để làm tiếp – Link download 2.1. Bài tập cơ bản về sử dụng hàm vlookup trong excel Ad chọn giới thiệu tới các bạn bài tập này là bởi vì nó vừa có yêu cầu cơ bản vừa có yêu cầu nâng cao đối với hàm tìm kiếm theo cột này. Bên cạnh đó quan trọng hơn cả là có cả video bài giải, như vậy khi gặp khó trong lúc làm bài tập bạn sẽ có phần hướng dẫn cụ thể để phát hiện ra mình sai ở đâu. Ngoài ra trong video hướng dẫn này, ad còn hướng dẫn một vài thủ thuật nhỏ nhưng lại rất hữu ích với những bạn mới sử dụng excel. Ad hi vọng rằng các bạn sẽ hiểu được và thành thạo cách vận dụng hàm vlookup cơ bản để xử lý các yêu cầu thực tế. Click để xem và tải bài tập cơ bản 2.2. Bài tập nâng cao Trong phần này, ad giới thiệu tới các bạn 2 bài tập ngắn nhưng lại yêu cầu kỹ năng vận dụng hàm kết hợp tìm kiếm với các hàm khác. Đây là dạng bài tập thách thức khả năng xử lý linh hoạt tình huống thay vì vận dụng hàm một cách dập khuôn. Click để tải bài tập nâng cao Gợi ý giải bài tập: Trong trường hợp bạn bí trong việc giải 2 bài tập nâng cao này thì hãy xem phần gợi ý dưới đây nhé: Bài 1: Sử dụng hàm vlookup để Tìm Đơn giá nhập/ xuất Thực chất bài tập này chỉ khó ở phần tìm Đơn giá nhập/ xuất của từng giao dịch phát sinh dựa trên cột Mã hàng. Ký tự cuối của mỗi mã hàng là: N hoặc X tương ứng với Nhập hàng hoặc Xuất hàng. Và oái oăm là ta lại có 2 bảng đơn giá: 1 bảng đơn giá nhập, 1 bảng đơn giá xuất. Để giải được bài tập này các bạn phải xác định được từng thành phần của hàm vlookup thì mới có thể giải được. Mã hàng -> Cần phải tách được chữ N hoặc X khỏi mã hàng trong bảng kê nhập xuất Điều kiện tìm kiếm Mã hàng -> Dựa vào ký tự cuối của mã hàng trong bảng kê nhập xuất để xác định Vùng tìm kiếm Ở cả 2 bảng đơn giá thì phần giá đều nằm ở cột thứ 2 Cột chưa giá trị đang tìm kiếm 0 – đương nhiên là loại tìm kiếm chính xác 100% rồi. Loại tìm kiếm   Các hàm tham khảo để giải bài này: – If – hàm nếu thì – Vlookup – hàm tìm kiếm theo cột – Len – hàm đếm số ký tự – Left – hàm tách ký tự từ một chuỗi ký tự Bài 2: Tìm tên vùng của số điện thoại Bài này nhìn thì ngắn nhưng mà độ hóc và xương của nó thì cũng không hề đơn giản đâu nhé các bạn. Cái khó ở bài này là điều kiện tìm kiếm có thể là 2, 3 hoặc 4 ký tự (mã vùng) trong khi số điện thoại lại phải có nghĩa và lại cũng gồm nhiều loại: 9 và 10 số. Để giải bài này bạn hãy khoanh vùng danh sách mã vùng và dùng hàm if để loại bỏ tất cả các trường hợp gây trùng và lẫn. Đương nhiên đây là hàm if lồng rồi. Bạn hãy xem ví dụ về hàm if lồng nếu bạn chưa từng dùng nó bao giờ nhé.