Bạn đang xem bài viết Cách Sử Dụng Hàm Vlookup Có Bài Tập Và Lời Giải được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Hàm dò tìm & trả về giá trị trong vùng dò tìm dựa vào mã dò tìm (Chúng ta sẽ hiểu hơn sau khi xem các ví dụ cụ thể)
Cột chứa giá trị cần tìm:
Thứ tự của cột cần lấy dữ liệu trong vùng dò tìm. Tại công thức trong vd trên cột chứa giá trị cần tìm là cột tên hàng, cột số 2
Kiểu tìm kiếm, tìm tuyệt đối thì chọn 0, tìm tương đối thì chọn 1, chúng ta thường dùng kiểu dò tìm tuyệt đối nên chọn 0. Tại công thức trong vd trên thì kiểu tìm kiếm là tuyệt đối- chọn 0 III. Cách dùng hàm.
Nếu đọc khái niệm & cú pháp hàm và thực hiện thì chúng ta sẽ làm được các trường hợp dò tìm cơ bản, nhưng trên thực tế có rất nhiều trường hợp khó bắt chúng ta phải kết hợp hàm Vlookup với các hàm khác để thực hiện dò tìm. Tôi sẽ đưa ra một số trường hợp cụ thể như bên dưới để các bạn hình dung ra những trường hợp có thể gặp phải.
Hàm sẽ thực hiện dò tìm theo mã hàng trong bảng tra thông tin và trả về kết quả ở cột số 2 trong bảng tra thông tin dóng theo hàng ngang, tương ứng với X là Xăng.
=VLOOKUP(LEFT(A4;2);$A$12:$B$15;2;0) Hàm sẽ trả về kết quả là Khang Dân. Chúng ta copy công thức xuống các ô còn lại
Vì yêu cầu là dùng 2 ký tự đầu tiên của Mã SP để dò tìm trong Bảng tra thông tin nên chúng ta phải dùng hàm Left để lấy 2 ký tự đầu tiên trong Mã SP và dò tìm trong bảng tra thông tin
Trường hợp này tôi tách so với trường hợp 3 là vì phải tách ký tự số trong Mã SP, nếu dùng nguyên hàm mid thì vẫn tách được nhưng kết quả là dạng text chứ không phải dạng số, vậy nên cần dùng thêm hàm value để chuyển ký tự đó thành dạng số để tiến hành dò tìm trong bảng thông tin 4
2 , thế nhưng đây lại không phải dạng số để có thể dò tìm tại bảng thông tin 4
Vậy Cần dùng = VALUE(MID(A4;4;1)) Kết quả là 2 , là dạng số để sẵn sang dò tìm trong bảng thông tin 4.
Đáp án : Nhập công thức tại ô C4 như sau.
=VLOOKUP(A4,$A$12:$D$15,IF(B4=1,2,IF(B4=2,3,4)),0) àHàm sẽ trả về kết quả là 23.000
Trường hợp này hàm if có tác dụng chọn cột lấy giá trị, IF(B4=1,2,IF(B4=2,3,4)) Nếu Loại gạo =1 thì sẽ lấy giá trị ở cột 2, nếu loại gạo = 2 thì sẽ lấy giá trị ở cột 3, còn lại lấy giá trị ở cột 4.
Bài viết này là bài khởi đầu của mình, đây chỉ là những trường hợp cơ bản nhất của hàm Vlookup thường dùng, mình sẽ cập nhật thêm các trường hợp khác hàng ngày.
One more thing , Cần phải dành nhiều thời gian cho đam mê của mình hơn nữa. Cảm ơn!
Hải đăng
Bài Tập Mẫu Hàm Vlookup Trong Excel (Có Lời Giải)
Bài này còn có ví dụ cho việc áp dụng hàm VLOOKUP kết hợp với các làm trích kí tự LEFT, RIGHT để dò tìm và lấy giá trị từ một bảng cho trước.
Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ A tin học văn phòng hiệu quả.
Ở cuối bài, có file Excel XLS chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.
Bảng dữ liệu bài tậpCâu 1) Dựa vào kí tự đầu và 2 kí tự cuối của “Mã HĐ”, tìm trong “Bảng Danh Mục” đề lấy tên mặt hàng điền vào cột “Mặt Hàng”.
Câu 2) Tìm trong “Bảng Danh Mục” và điền giá trị vào cột “Đơn vị”
Câu 3) Tìm trong “Bảng Danh Mục” và điền giá trị vào cột “Đơn giá”
Câu 4) Tính cột “Thành tiền” bằng “Đơn giá” nhân “SLượng”
Câu 5) Tính và điền giá trị vào bảng “Tổng số tiền bán được của các mặt hàng”
Câu 1:
Tại ô C4, gõ hàm:
Ý nghĩa là: Lấy bên trái của B4 1 kí tự rồi ghép với 2 kí tự bên phải, đem tìm cụm này trong “Bảng Danh Mục” theo chiều dọc và lấy kết quả là giá trị ở cột thứ 2 của bảng. Tìm chính xác.
Thủ thuật về cách kéo để copy công thức Thủ thuật về cách kéo để copy công thứcCâu 2:
Tại ô E4, gõ hàm:
Ý nghĩa tương tự như câu 1, nhưng lấy kết quả là giá trị ở cột thứ 3 của bảng (Tương ứng là cột Đơn vị).
Câu 3:
Tại ô G4, gõ hàm:
Ý nghĩa tương tự như câu 1, nhưng lấy kết quả là giá trị ở cột thứ 4 của bảng (Tương ứng là cột Đơn giá).
Câu 4:
Tại ô H4, gõ công thức:
=F4*G4
Ý nghĩa: Lấy giá trị cột F4 nhân cột G4.
Câu 5:
– Tại ô B29, gõ hàm:
= SUMIF(B4:B13, “S*M?”, H4:H13)
Ý nghĩa: Tính tổng doanh số của tất cả các hóa đơn bán đĩa mềm. Đây là tính tổng có điều kiện, vì vậy ta dùng SUMIF. Điều kiện là hóa đơn bán đĩa mềm, tức là các “Mã HĐ” sẽ có dạng
Vì vậy công thức trên có ý nghĩa là tìm trong cột B4:B13, Mã HĐ nào có dạng S*M? thì cộng các giá trị tương ứng trong cột H4:H13
– Tương tự, tại ô C29, gõ hàm:
– Tương tự, tại ô D29, gõ hàm:
Password giải nén là:
Trần Triệu Phú
Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.
Hướng Dẫn Cách Dùng Hàm Index Và Match Qua Bài Tập Có Lời Giải
Bài tập 1: Sử dụng hàm MATCH tìm vị trí theo dòng, cột
Cho bảng tính sau:
Nếu muốn biết thông tin của người có tên “Lê Thị Thủy” thì bạn phai làm thế nào? Chắc hẳn việc đầu tiên bạn cần xác định xem người đó nằm ở vị trí thứ mấy trong bảng tính này.
Bằng cách sử dụng hàm MATCH, bạn sẽ biết được 1 người nào đó nằm ở dòng thứ mấy trong bảng.
Ta có công thức sau:
=MATCH(“Lê Thị Thủy”, cột Họ tên, số 0)
Tại ô D12 nhập tên Lê Thị Thủy
cột Họ tên là vùng C3:C8
Như vậy hàm MATCH cho kết quả là số 3, tương ứng với dòng thứ 3 trong bảng dữ liệu trên.
Vậy việc tìm ra số 3 thì có ý nghĩa gì? Hay nói cách khác biết người đó ở vị trí dòng thứ 3 trong bảng thì có tác dụng gì?
Bài tập 2: Kết hợp hàm INDEX với MATCH tìm Email theo tênKhi nhìn vào bảng trên, ta thấy tại vị trí dòng 3, trong cột Email sẽ xuất hiện Email cần tìm. Như vậy có thể hiểu: Chỉ cần dóng theo cột Email, tại vị trí dòng 3 trong bảng là chúng ta có kết quả cần tìm.
Điều đó chính là cách kết hợp hàm INDEX với MATCH:
=INDEX(E3:E8,MATCH(D12,C3:C8,0))
Công thức này, hàm INDEX có nhiệm vụ:
Chỉ ra cột chứa giá trị cần tìm là cột E, vùng E3:E8
Chỉ ra dòng chứa giá trị cần tìm, dòng này đã xác định bởi hàm MATCH ở trên
Giao điểm giữa dòng và cột trong hàm INDEX tìm chính là kết quả email cần tìm.
Bài tập 3: Dò tìm từ phải qua trái với hàm INDEX kết hợp MATCHThông thường chúng ta hay dò tìm theo chiều từ Trái qua Phải. Nhưng nếu phải tìm từ Phải qua Trái (ngược lại với thông thường) thì làm thế nào?
Ví dụ như sau: Tìm tên dựa vào thông tin Email?
Như vậy để dò tìm họ tên dựa vào Email tức là chúng ta đang tìm từ Phải qua trái.
Quá trình thực hiện như sau:
Dùng hàm MATCH để tìm vị trí dòng của email trong cột Email =MATCH(Email, vùng chứa Email, 0)
Kết hợp với hàm INDEX trong việc chỉ ra vị trí cột chứa kết quả cần tìm, là cột Họ tên (cột C), vùng C3:C8
=INDEX(C3:C8,MATCH(D12,E3:E8,0))
Rất đơn giản phải không nào.
Bài tập 4: Xác định đơn giá của mặt hàng theo nhiều điều kiệnVí dụ ta có bảng đơn giá như sau:
Điều này tương đương việc chúng ta phải xét đồng thời 2 điều kiện: Mã hàng và Mã công ty
Để thực hiện yêu cầu này, chúng ta sẽ sử dụng hàm Index kết hợp hàm Match như sau:
Tham chiếu giá trị Mã hàng trong dòng 3 (dòng chứa thông tin mã hàng làm căn cứ đối chiếu)
Hàm MATCH trả về kết quả là 2, tương ứng với cột thứ 2
=MATCH(H2,A3:E3,0)
Tham chiếu giá trị Mã công ty trong cột A (cột chứa thông tin mã công ty làm căn cứ đối chiếu)
Hàm MATCH trả về kết quả là 3, tương ứng với dòng thứ 3
=MATCH(H3,A3:A7,0)
Như vậy giao điểm của dòng 3, cột 2 chính là ô B5 (bởi bảng bắt đầu tính từ dòng 3 trở đi, nên dòng 5 ứng với vị trí dòng thứ 3 của bảng đơn giá)
Bước 3: Xác định kết quả đơn giá cần tìm với hàm INDEX
Việc còn lại là dùng hàm INDEX để kết nối giữa Dòng và Cột đã xác định được ở trên.
Thay vì tham chiếu tới 1 cột cố định tại tham số đầu tiên, chúng ta tham chiếu cho cả vùng bảng A3:E7.
Trong bảng này, số dòng xác định bởi hàm MATCH tại ô I3, số cột xác định bởi hàm MATCH tại ô I2.
Kết quả của hàm INDEX là giao điểm giữa số dòng và số cột đã xác định được.
=INDEX(A3:E7,I3,I2)
Như vậy thông qua 4 bài tập ví dụ, chúng ta đã có thể hình dung được cách sử dụng hàm INDEX kết hợp hàm MATCH để thực hiện các yêu cầu dò tìm, tham chiếu, tìm kiếm trong Excel. Khi ứng dụng tốt cách làm này, bạn hoàn toàn có thể thực hiện được những yêu cầu tìm kiếm phức tạp mà hàm VLOOKUP khó đáp ứng được.
Thay vì phải đắn đo sử dụng hàm VLOOKUP hay HLOOKUP, làm công thức dài dòng khi phải kết hợp VLOOKUP với hàm MATCH, HLOOKUP với hàm MATCH, thì chúng ta chỉ cần dùng INDEX kết hợp MATCH là xong.
Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel Phân biệt hàm VLOOKUP với HLOOKUP và bí quyết sử dụng hàm Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp
Bài Tập Mẫu Hàm If Kết Hợp Left, Right, Mid Trong Excel (Có Lời Giải)
Bài này còn có ví dụ cho việc sử dụng tính năng ADVANCED FILTER để trích lọc dữ liệu từ một bảng cho trước. Phần này sẽ được tách ra trong bài kế tiếp.
Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ tin học văn phòng hiệu quả.
Ở cuối bài, có file Excel XLS chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.
Lý thuyết các bạn có thể xem bài lý thuyết và cách dùng các hàm trong excel.
Bảng dữ liệu bài tậpCâu 1. Điền cột Mã Xuất Nhập Khẩu. Cột này nhận 2 ký tự đầu của cột Mã Số.
Câu 2. Điền cột Tên Sản Phẩm. Biết nếu ký tự thứ ba bằng “G” là Gạo; “D” là Đường; “T” là Than.
Câu 3. Điền cột Số Hiệu. Số hiệu nhận 3 kí tự cuối của cột Mã Số.
Câu 4. Trích lọc những hàng hóa có Mã Số Hiệu là 003.
Câu 1. Điền cột Mã Xuất Nhập Khẩu. Cột này nhận 2 ký tự đầu của cột Mã Số
Theo yêu cầu, để tìm được Mã Xuất Nhập Khẩu ta chỉ cần trích 2 kí tự đầu của chuỗi Mã Số (Cột B). Tức ta dùng hàm LEFT.
Tại ô C4, gõ công thức:
Thủ thuật về cách kéo để copy công thức Thủ thuật về cách kéo để copy công thứcCâu 2. Điền cột Tên Sản Phẩm. Biết nếu ký tự thứ ba bằng “G” là Gạo; “D” là Đường; “T” là Than.
Tên sản phẩm phụ thuộc vào điều kiện của kí tự thứ 3 trong cột Mã số. Như vậy ở đây ta phải dùng hàm IF kết hợp với hàm lấy ký tự thứ 3 trong một chuỗi. Ta dùng hàm MID để lấy ký tự bất kì trong chuỗi.
Ví dụ lấy ký tự thứ 3 trong chuỗi Mã số, ta dùng như sau:
= MID(…Nội dung chỉ dành cho thành viên… )
Nghĩa là, Từ chuỗi B4, trích ra một chuỗi, bắt đầu tứ vị trí thứ 3, lấy một ký tự. Tức ta sẽ được giá trị là “G”
Trở lại yêu cầu bài toán, dựa vào kí tự thứ 3, nếu nó là “G” thì Tên Sản Phẩm là “Gạo”, nếu là “D” thì Tên Sản Phẩm là “Đường”, Nếu là “T” thì Tên Sản Phẩm là “Than”.
Như vậy, tại ô D4, ta gõ công thức sau:
= IF(MID(B4,3,…Nội dung chỉ dành cho thành viên… =”T” , “Than”)))
Lý giải chung cho công thức trên như sau: Nếu kí tự thứ 3 của Mã số là “G” thì hiện ra kết quả là …Nội dung chỉ dành cho thành viên…
Câu 3. Điền cột Số Hiệu. Số hiệu nhận 3 kí tự cuối của cột Mã Số.
Số hiệu nhận 3 kí tự cuối của cột Mã Số tức ta sẽ lấy 3 ký tự từ phía phải qua. Dùng hàm RIGHT.
Tại ô E4, gõ công thức sau:
= RIGHT(B4,3)
Câu 4. Trích lọc những hàng hóa có Mã Số Hiệu là 003
Cập nhật: 15/5: Lời giải trích lọc cho câu 4, mời các bạn xem video này https://www.youtube.com/watch?v=P7zEp3VDdCE (Nhớ chọn chế độ HD và phóng lớn để xem cũng như đừng quên chia sẻ lên FB cho bạn bè.)
Tải file XLS lời giảihttp://hoc.trungtamtinhoc.edu.vn/download/IF-LEFT-RIGHT.zip
Password: …Nội dung chỉ dành cho thành viên…
Trần Triệu Phú – chúng tôi
Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.
Cập nhật thông tin chi tiết về Cách Sử Dụng Hàm Vlookup Có Bài Tập Và Lời Giải trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!