=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])
Giải thích :
– Lookup_value: Giá trị cần dò tìm. Bạn có thể điền giá trị trực tiếp vào công thức hoặc tham chiếu tới một ô trên bảng tính Excel.
– Table_array: Bảng giới hạn để dò tìm, bạn cần F4 để Fix cố định giá trị cho mục đích copy công thức tự động, nếu bạn sử dụng laptop, có thể bạn sẽ cần bấm phím FN+F4 để cố định tham chiếu cho vùng bảng tính này.
– Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
– Range_lookup: Là giá trị Logic (TRUE=1, FALSE=0) quyết định so sánh, tìm kiếm chính xác hay so sánh, tìm kiếm tương đối với bảng giới hạn.
+ Nếu Range_lookup = 1 (TRUE): So sánh tương đối.
+ Nếu Range_lookup = 0 (FALSE): So sánh chính xác.
+ Nếu bỏ qua đối này thì Excel hiểu là Range_lookup = 1 một cách mặc định.
Vậy khi nào thì sử dụng hàm Vlookup và khi nào dùng hàm Hlookup ?
Đơn giản thôi, nếu bảng số lượng giới hạn dò tìm xếp dọc như ví dụ dưới đây thì ta dùng hàm Vlookup, còn bảng số lượng giới hạn dò tìm là ngang thì ta dùng hàm Hlookup, vậy bạn chỉ cần nhớ V – dọc, H – ngang ( Trong tiếng Anh, V – viết tắt của từ Vertical, còn H – viết tắt của từ Horizontal )
Nội dung chính
Ví dụ cách sử dụng hàm Vlookup trong Excel
Cách tính thuế nhập khẩu theo đối tượng
Ví dụ. Bạn hãy tính thuế nhập khấu theo Đối tượng của những mẫu sản phẩm dưới đây :
Trong ví dụ trên, tại ô G5 ta gõ công thức: =VLOOKUP(D5,$D$17:$F$20,2,0)*E5
Trong đó :
- Vlookup: là hàm dùng để tìm kiếm ra thuế nhập khẩu tại Bảng quy định thuế.
- D5: Giá trị là đối tượng cần tìm ở đây là các đối tượng từ 1,2,3,4.
- $D$17:$F$20: Bảng giới hạn dò tìm, chính là D17:F20 nhưng được F4 để Fix cố định địa chỉ ô để Copy công thức xuống các ô G6->G12 thì công thức sẽ không bị thay đổi.
- 2: Thứ tự cột giá trị cần lấy, trong trường hợp này chính là cột Thuế nhập khẩu
- 0: Trường hợp này chúng ta lấy giá trị tuyệt đối nên chọn là 0 hoặc False
- E5: Chính là đơn giá sản phẩm để tính ra thuế nhập khẩu.
Với công thức trên, hiệu quả ta được là :
Copy công thức xuống những ô G6 -> G12 ta được hiệu quả như ảnh trên .
Cách dùng hàm VLOOKUP để tìm kiếm gần đúng
Sẽ có bạn hỏi mình, làm thế nào xác định được Range_lookup=1 hay Range_lookup=0?
Theo đúng định nghĩa của Microsoft Office Range_lookup=1 khi chúng ta cần tìm giá trị tương đối nghĩa là gần đúng hay giá trị hợp lý nhất khi không thể tìm thấy một giá trị chính xác.
Ví dụ: Để xếp loại học sinh dựa trên điểm trung bình trong trường hợp dưới đây thì Range_lookup=1, vì để xếp loại theo bảng tham chiếu thì ta bắt buộc phải lấy giá trị tương đối nghĩa là gần đúng, 9.1 gần với 9, 5.3 gần với 5 … hay diễn đạt theo một cách khác, chúng ta có thể đưa ra tiêu chí xếp loại như sau:
- Từ 9: xếp loại giỏi ( lớn hơn hoặc bằng 9, 9 <= x)
- Từ 6.5 đến dưới 9: xếp loại khá (6.5 <= x < 9)
- Từ 5 đến dưới 6.5: xếp loại trung bình (5 <= x < 6.5)
Để điền vào cột Xếp loại (cột D), chúng ta có thể dùng hàm VLOOKUP như sau trong ô D4:
=VLOOKUP(C4,$B$10:$C$12,2,TRUE)
hoặc
=VLOOKUP(C4,$B$10:$C$12,2,1)
Lưu ý: Khi sử dụng hàm VLOOKUP trong Excel để thực hiện tra cứu gần đúng, bảng tra cứu cần được sắp xếp theo thứ tự từ nhỏ tới lớn. Nếu bạn không sắp xếp bảng tra cứu theo thứ tự này, kết quả tra cứu dùng hàm VLOOKUP sẽ bị sai.
Cũng với ví dụ tra cứu xếp loại dựa trên điểm số trên sử dụng hàm VLOOKUP, khi bảng điểm có một bạn được 4 điểm, thì chúng ta sẽ nhận được lỗi #N/A khi sử dụng hàm VLOOKUP. Để khắc phục lỗi này của hàm VLOOKUP, bạn có thể thêm vào bảng tra cứu một dòng dữ liệu như hình bên dưới
Cách sử dụng hàm VLOOKUP kết hợp với Data Validation trong Excel
Trong ví dụ ứng dụng hàm VLOOKUP này, tất cả chúng ta sẽ tham chiếu tới ô có Data Validation, để khi biến hóa giá trị trong list dropdown validation này, thì tác dụng trả về từ hàm VLOOKUP cũng được đổi khác một cách tự động hóa .
Bước tiên phong, tất cả chúng ta sẽ đi chuẩn bị sẵn sàng Data Validation bằng cách chọn ô C10, sau đó bấm vào thẻ Data / Data Validation :
Trong hộp thoại Data Validation, tất cả chúng ta sẽ chọn thẻ Settings ( 1 ), sau đó trong mục Allow, chọn List ( 2 ) và sau cuối chọn Source ( 3 ) là vùng B4 : B7
Sau đó, tất cả chúng ta sẽ hoàn thành xong hàm VLOOKUP tham chiếu đến ô chứa Data Validation như hình
=VLOOKUP(C10,B4:D7,2,0)
=VLOOKUP(C10,B4:D7,3,0)
Tìm mã ngành học sử dụng hàm VLOOKUP
Yêu cầu của bài lần này của tất cả chúng ta là dựa vào bảng mã ngành để hoàn toàn có thể tìm ra tên ngành học của sinh viên dựa trên ký tự tiên phong của số báo danh
Trong trường hợp này, chúng ta có thể kết hợp hàm VLOOKUP với hàm LEFT như sau
=VLOOKUP(LEFT(C10),F4:G5,2,0)
Trong đó, LEFT(C10) sẽ lấy ra ký tự đầu tiên trong số báo danh được lưu trong ô C10, và hàm VLOOKUP sẽ sử dụng ký tự này cho việc tra cứu trong bảng mã ngành.
Xử lý lỗi khi hàm VLOOKUP không tìm thấy giá trị cần tra cứu, lỗi #N/A
Trong trường hợp giá trị dùng để tra cứu không được tìm thấy trong bảng tra cứu, hàm VLOOKUP sẽ trả về lỗi # N / A .
Nếu bạn muốn đưa ra một thông tin rõ ràng hơn, có ý nghĩa hơn, ví dụ “ Không tìm thấy số báo danh ”, thì bạn hoàn toàn có thể sử dụng hàm VLOOKUP phối hợp với hàm IFERROR trong ô C12 như sau :
=IFERROR(VLOOKUP(C10,B4:D7,2,0),"Không tìm thấy SBĐ")
Hàm IFERROR sẽ phát hiện lỗi # N / A trong trường hợp này và trả về giá trị tất cả chúng ta đưa ra là “ Không tìm thấy SBĐ ” .
Đây chỉ là một trường hợp lỗi phổ biến khi bạn sử dụng Hàm Vlookup ứng dụng trong công việc. Để tìm hiểu về cách sửa những lỗi khác khi sử dụng hàm Vlookup như lỗi Lỗi #REF!, Lỗi #VALUE!, Lỗi #NAME? thì đừng bỏ qua bài viết này bạn nhé! Các lỗi hàm Vlookup thường gặp nhất định bạn phải biết để tránh
5 điểm hạn chế của hàm Vlookup
Chỉ hỗ trợ tham chiếu từ trái sang phải
Phần nhiều người dùng cho rằng Vlookup chỉ hoạt động giải trí theo chiều từ trái sang phải, chứ không có chiều ngược lại. Một số khác cho rằng để hoàn toàn có thể dùng Vlookup tham chiếu từ phải sang thì bắt buộc phải tích hợp nhiều công thức lại với nhau .
Thực tế mà nói, việc tích hợp công thức là một thao tác khá là rắc rối và không hề thuận tiện gì. Vì thế đây vẫn được coi là một hạn chế lớn của Vlookup
Giải pháp : Sử dụng công thức Match và Index
Chỉ hoạt động hiệu quả với các giá trị riêng biệt
Vlookup có khuynh hướng chỉ chăm sóc đến giá trị tham chiếu đầu và bỏ quên những dòng khác chứa giá trị tựa như. Nếu như tài liệu của bạn không có quá nhiều sự phân hóa thì bạn bắt buộc phải lách luật một chút ít để Vlookup hoàn toàn có thể tham chiếu được giá trị bạn cần .
Giải pháp : Tạo một bảng Pivot chứa những giá trị riêng không liên quan gì đến nhau và khi đó bạn hoàn toàn có thể dùng hàm Vlookup một cách thông thường
Số thứ tự cột tham chiếu luôn cố định
Việc số thứ tự của cột tham chiếu được điền thủ công bằng tay vào nội dung của công thức khiến cho thao tác sao chép sang ô tính khác gặp nhiều khó khăn vất vả và phiền phức .
Giải pháp : Bạn hoàn toàn có thể sử dụng số thứ tự biến hóa liên tục trải qua hàm Match
Mặc định thiết lập “approximate match”
Nếu chú ý kỹ, bạn hoàn toàn có thể thấy rằng điều kiện kèm theo kiểm duyệt tham chiếu ở trong nội dung hàm Vlookup là yếu tố tùy chọn ( được ký hiệu bằng hai dấu ngoặc vuông [ ] ) nên nếu như bạn không điền nội dung gì vào đó thì công thức sẽ tự mặc định là “ approximate match ”
Bạn thật sự cần phải quan tâm nếu như không muốn hiệu quả tham chiếu trở nên xô lệch
Giải pháp : Luôn chú ý quan tâm phải thiết lập là “ exact match ” để có hiệu quả tham chiếu đúng chuẩn nhất, tuy nhiên nếu bạn vẫn muốn chọn thiết lập mặc định “ approximate match ” thì phải thật sự cẩn trọng
Làm tốc độ phản hồi của bảng tính bị chậm đi
Một vài quan điểm cho rằng việc sử dụng quá nhiều hàm Vlookup để tham chiếu sẽ làm giảm hiệu năng của chương trình, thậm chí còn gây ra hiện tượng kỳ lạ crash .
Giải pháp : Bạn không bị số lượng giới hạn số lượng hàm Vlookup cần dùng, tuy nhiên nỗ lực thay thế sửa chữa chúng bằng lệnh Paste Special để thay thế sửa chữa những hàm bằng giá trị cố định và thắt chặt, điều đó sẽ giúp chương trình hoạt động giải trí hiệu suất cao hơn .
Để tìm hiểu kĩ hơn về cách tra cứu gần đúng, tra cứu với kí tự đại diện dấu sao * trong hàm VLOOKUP, mời các bạn theo dõi bài viết cập nhật:
Tất cả về hàm VLOOKUP trong excel cơ bản cho người mới bắt đầu
Ngoài ra, nếu bạn thích xem video hướng dẫn về hàm VLOOKUP cũng như những hàm tra cứu khác như Index và Match, thì Thanh có sẵn sàng chuẩn bị sẵn video sau đây để những bạn hoàn toàn có thể tìm hiểu thêm
Hi vọng với bài viết chia sẻ này các bạn sẽ nắm được chi tiết về hàm Vlookup trong excel và cách sử dụng. Trong thời gian tới Học Excel Online sẽ chia sẻ thêm về hàm Hlookup và các hàm kết hợp với hàm Vlookup và Hlookup hay được sử dụng trong công việc.
Bên cạnh đó, để giúp các bạn sử dụng Excel thành thạo, tiết kiệm thời gian tra cứu, ứng dụng linh hoạt, phát triển kỹ năng sử dụng hàm và lồng ghép hàm, Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao, đây là chương trình học trực tuyến đầy đủ có tính ứng dụng thực tế, có sự hỗ trợ giải đáp của giảng viên và chuyên gia Nguyễn Đức Thanh trong suốt quá trình học, học viên có thể học đi học lại nhiều lần, học linh hoạt bất kỳ thời điểm nào.
Chi tiết về khoá học, bạn vui lòng tham khảo: EX101 – Excel từ cơ bản tới chuyên gia
Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào việc làm như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table …
Toàn bộ những kiến thức và kỹ năng này những bạn đều hoàn toàn có thể học được trong khóa học trên .
Source: http://139.180.218.5
Category: Thuật ngữ đời thường