CÔNG TY CỔ PHẦN BLUESOFTS

Hướng dẫn chi tiết hàm BS_VLOOKUP tìm và trả về mảng động với nhiều điều kiện

 
Hàm BS_VLOOKUP tìm nhiều điều kiện và trả về mảng động, mảng có thể là một hay nhiều cột, tốc độ chạy nhanh, có tính ứng dụng rất đa dạng. Loạt bài chia sẻ trong trang này giúp bạn khai thác các tình huống cũng như cách thức sử dụng hàm BS_Vlookup trong các tình huống khác nhau.

Cấu trúc hàm

BS_VLOOKUP(LookupValue,TableArray,[ResultColIndex],[LookupColIndex],[Options])

Các tham số trong [ ] có thể bỏ qua. Chúng tôi giải thích các tham số theo bảng dưới đây đông thời đối chiếu tính năng giữa hàm BS_VLOOKUP() với hàm VLOOKUP.

Tham số BS_VLOOKUP() Ví dụ
(*) Kết quả trả về
(Return)
Là một hoặc nhiều giá trị tìm được trả về (mảng)
Return an array of all matches
Chỉ một giá trị
'+ vd: Lấy tất cả giá trị trong dữ liệu nguồn:
=bs_Vlookup("*",C4:F9)
1. LookupValue:
Giá trị tìm
Tìm một hoặc mảng giá trị.
Giá trị có thể tìm trên một hoặc nhiều cột.

"*" là lấy tất cả dữ liệu.
{"B",">=3","<8"} Nhập mảng giá trị tìm kiếm.
I3:I6 Giá trị tìm kiếm nằm trong vùng

"1=B, 4>=3, 4<8" Cách tạo chuỗi biểu thức tìm kiếm. Khi dùng biểu thức thì các tham số ResultColIndexLookupColIndex bị bỏ qua. 

Hãy xem các ví dụ phía dưới để hiểu cách vận dụng. 
+ vd1: Tìm "A" tại cột số 2:
=bs_Vlookup("A",C4:F9,,2)

'+ vd2: Tìm "A", "B" tại cột số 2:
=bs_Vlookup({"A","B"},C4:F9,,2)

'+ vd (*): Tìm tất cả những mã CHỨA chữ "A" tại cột số 2:
=bs_Vlookup("*A*",C4:F9,,2)
2. TableArray:
Giá trị tìm
Là mảng dữ liệu/table (giống như tham số Table_Array trong Vlookup). Cho phép nhập chuỗi mo tả NAME hoặc địa chỉ vùng dữ liệu nguồn.
+ vd (*): Tìm tất cả những mã CHỨA chữ "A" tại cột số 2:
=bs_Vlookup("*A*","C4:F9",,2)
3. ResultColIndex: cột trả về
Một hoặc nhiều cột được trả về
(Có thể nhập công thức: chỉ dùng cho nhà phát triển ứng dụng cao cấp)
Nếu bỏ trống là lấy tất cả các cột.

Ví dụ:

{1,3,5} lấy các cột 1,3,5

{"RECNO()", 1, 3, 5} lấy cột số thứ tự và các cột 1, 3, 5.

Các hàm trong A-Tools tọa độ cột tính từ 1, trừ hàm BS_SQL tọa độ tính từ 0.

+ vd3: Tìm "A" tại cột 2, giá trị trả về cột số 4:
=bs_Vlookup("A", C4:F9, 4, 2)

'+ vd4: Tìm "A" tại cột 2, giá trị trả về cột số 1,3,4:
=bs_Vlookup("A", C4:F9, {1,3,4}, 2)

'+ vd4.1: Tìm "A" tại cột 2, giá trị trả về cột số 1,3,4 và đánh số thứ tự:
=bs_Vlookup("A", C4:F9, {"RECNO()",1,3,4}, 2)
4. LookupColIndex:
Cột tìm kiếm
Một hoặc nhiều vị trí cột được tìm.
Nếu để trống là tìm trên tất cả các cột.
(Có thể nhập chuỗi công thứ tìm kiếm: chỉ dùng cho nhà phát triển ứng dụng cao cấp).

Nếu 
+ vd5: Tìm "A", ">3" tại 2 cột cột 2,4 giá trị trả về các cột:
=bs_Vlookup({"A",">3"}, C4:F9, , {2,4})

'+ vd6: Tìm "A" ở tất cả các cột, giá trị trả về ở cột 1:
=bs_Vlookup("A", C4:F9, 1 )
5. Options:
Tùy chọn
Được phép dùng một hoặc bao gồm nhiều thuộc tính cho kết quả:
'+ INSERT=YES: thuộc tính chèn dòng
'+ NAME=Tên: thuộc tính tạo NAME cho mảng kết quả
'+ SORT= vị trí cột [ASC/DESC] chỉ định cột sắp xếp tăng dần hoặc giảm dần
'+ TOP= số dòng đầu cần lấy
'+ AUTONAMES(Tên bảng, Tên đầu cột)
'+ Các sự kiện tương tự hàm BS_SQL
+ vd7: Tìm "A" tại cột số 2. Sắp xếp cột 4 tăng dần
=bs_Vlookup("A",C4:F9,,2, "SORT=4")

'+ vd8: Tìm tất cả "*" tại cột số 2. Sắp xếp cột 4 giảm dần, lấy 3 dòng đầu.
=bs_Vlookup("A",C4:F9,,2, "SORT=4 desc ; TOP=3")

'+ vd9: Tìm "A" tại cột số 2. Co giãn dòng (chèn/insert=yes), đặt tên mảng kết quả là "BS"
=bs_Vlookup("A",C4:F9,,2, "INSERT=YES; NAME=BS")

+ vd9.1: Tìm "A" tại cột số 2. Co giãn dòng (chèn/insert=yes), đặt tên mảng kết quả là "BS" và tên các cột có tên đầu là ABCD
=bs_Vlookup("A",C4:F9,,2, "INSERT=YES; AUTONAMEA(BS,ABCD);")
6. Tìm nhiều điều kiện theo phép so sánh Kết hợp ký tự só sánh *, >=, <=,>,<,<>, tìm kết hợp nhiều cột với AND, OR
+ vd10: Tìm mã hàng "A" và Qty>=3 và Qty<=8 ,  tại cột số 2 và cột 4:
=bs_Vlookup({"A",">=3","<=8"}, C4:F9 , , {2,4,4})

Hướng dẫn chi tiết theo từng ví dụ giúp bạn vận dụng đủ các tính năng của hàm BS_VLOOKUP
 
Giả sử ta có bảng dữ liệu nguồn trong vùng C4:F9
 
BILL# CODE NAME QTY
01 A Tivi 5
02 B Computer 10
03 A Tivi 3
04 A Tivi 8
05 B Computer 4
06 C Mouse 6
 
- Ví dụ 1: Tìm "A" trong cột số 2. Kết quả trả về tất cả các cột
Công thức là:

=BS_VLOOKUP("A",C4:F9,,2)

Nếu bảng kết quả tự động co giản thêm hoặc xóa bớt dòng do thay đổi điều kiện lọc thì nhập "INSERT=YES;" vào tham số Options.

=BS_VLOOKUP("A", C4:F9 , , 2, "INSERT=YES;")

Kết quả là:

01 A Tivi 5
03 A Tivi 3
04 A Tivi 8


- Ví dụ 2: Tìm "A", "B" tại cột số 2:
Công thức là:

=BS_VLOOKUP({"A";"B"},C4:F9,,2)

Kết quả là:
01 A Tivi 5
02 B Computer 10
03 A Tivi 3
04 A Tivi 8
05 B Computer 4

- Ví dụ 3: Theo ví dụ 2 nhưng giá trị làm điều kiện "A", "B"  đưa vào vùng I31:I32 (giá trị điều kiện mô tả theo chiều dọc - cột)

Công thức là:
=BS_VLOOKUP($I$31:$I$32,$C$4:$F$9,,2)

Kết quả chúng ta nhận được giống "Ví dụ 2"

Qua hai ví dụ trên chúng tôi muốn người dùng biết hàm BS_VLOOKUP cho phép nhập điều kiện như một chuỗi biểu thức trực tiếp vào trong hàm hoặc để trong một vùng.

- Ví dụ 4: Tìm "A" tại cột 2, giá trị trả về cột số 4

=BS_VLOOKUP("A",C4:F9,4,2)

Kết quả:
5
3
8
- Ví dụ 5: Tìm "A" tại cột 2, giá trị trả về cột số 1,3,4

=BS_VLOOKUP("A", C4:F9, {1;3;4}, 2)

Kết quả là:
01 Tivi 5
03 Tivi 3
04 Tivi 8

- Ví dụ 6: Theo ví dụ 4 nhưng vị trí các cột trả về nhập vào vùng I51:I53

=BS_VLOOKUP("A", Example!$C$4:$F$9, I51:I53, 2)

Kết quả là như ví dụ 5.

- Ví dụ 6: Tìm "A", ">3" tại 2 cột cột 2,4 giá trị trả về các cột

Cách 1: Mô tả điều kiện trong chuỗi biểu thức:
 
=BS_VLOOKUP({"A";">3"}, C4:F9, , {2;4})

Kết quả là:
01 A Tivi 5
04 A Tivi 8

Cách 2: Tạo vùng điều kiện: Vùng điều kiện I62:I63 (chứa "A", ">3") và I62:J63 (chứa 2, 4). Công thức là:

=BS_VLOOKUP(I62:I63, C4:F9, , J62:J63)

Kết quả là:


(*) Qua hai ví dụ này chúng tôi muốn người dùng nắm rõ hàm BS_VLOOKUP cho phép nhập điều kiện, vùng tìm kiếm trong vùng bảng tính. Với cách này người sử dụng thay đổi điều kiện bằng cách thay đổi giá trị trên ô kết quả tự động thay đổi sẽ trực quan.

- Ví dụ 7: Tìm "A" ở tất cả các cột, giá trị trả về ở cột 1
Theo yêu cầu này thì tham số Lookup_col_index bỏ trông.

=BS_VLOOKUP("A", C4:F9, 1, )
 
Kết quả là:
01
03
04

- Ví dụ 8: Tìm "A" tại cột số 2. Sắp xếp cột 4 tăng dần

=BS_VLOOKUP("A",C4:F9,,2, "SORT=4")

Kết quả là:
03 A Tivi 3
01 A Tivi 5
04 A Tivi 8

Nếu chỉ lấy 2 dòng đầu tiên thì thêm thuộc tính "TOP=2;"

=BS_VLOOKUP("A",C4:F9,,2, "SORT=4; TOP=2;")

Kết quả là:
03 A Tivi 3
01 A Tivi 5

- Ví dụ 9: Tìm mã hàng "A" và Qty>=3 và Qty<=8 .

+ Cách 1: nhập mảng chứa các gía trị làm điều kiện {"B",">=3,4<=8"; mảng chứa các cột tìm kiếm {2,4,4}

=BS_VLOOKUP({"B",">=3","<=8"}, C4:F9 , , {2,4,4},"INSERT=YES")

+ Cách 2: Tạo chuỗi biểu thức. Công thức trên có thể viết lại bằng cách gộp vùng điều kiện, cột tìm kiếm chung lại thành chuỗi biểu thức:

=BS_VLOOKUP("2=B,4>=3,4<=8", C4:F9 , , ,"INSERT=YES")

2: cột CODE
4: là cột QTY

+ Cách 3: Tạo vùng điều kiện

=BS_VLOOKUP(I19:I21, C4:F9 , ,  J19:J21 , "INSERT=YES")

Két quả cả 3 cách là:



Cách tạo cột số thứ tự trong hàm BS_VLOOKUP, BS_HLOOKUP

Tham số ResultColIndex nhập mảng như sau

=BS_VLOOKUP({"B",">=3","<=8"}, C4:F9 ,{"RECNO()",1,2,3,4} , {2,4,4},"INSERT=YES")

Theo công thức trên cột đầu là số thứ tự và các cột 1, 2,3,4 từ dữ liệu nguồn được lấy ra.

(*) Các bạn lưu ý: Chuỗi biểu thức tìm kiếm trong Add-in A-Tools dùng chung cho các hàm tìm kiểm BS_VLOOKUP, BS_HLOOKUPNhập liệu nâng cao.

Các video ví dụ ứng dụng
 
Bài 1 - Các giá trị làm điều kiện tìm kiếm trong vùng bảng tính
 

Bài 2 - Các giá trị làm điều kiện tìm kiếm trong vùng bảng tính
 

Bài 3 - Các giá trị làm điều kiện tìm kiếm trong vùng bảng tính
 
Bài 4 - Ứng dụng lọc dữ liệu từ sổ nhật ký chung trong kế toán
 
 
Bài 5 - Hàm BS_Vlookup tìm kiếm bằn biểu thức chuỗi điều kiện
Phương pháp này tham số đầu tiên được mô tả công thức tìm kiếm như sau
Giả thiết thức tự cột trong dữ liệu nguồn: 1 là Tỉnh/TP; 2- Quận/Huyện; 3- Doanh số
Điều kiện: lọc dữ liệu có tỉnh là "Hà Nội" và Quận là "Cầu Giấy" và Doanh số >=50000
Công thức tìm là:
"1=hà nội, 2=cầu giấy, 3>=50000"

Bạn có thể kết hợp điều kiện OR
"1=hà nội, hải phòng, bắc ninh, 2=cầu giấy, mỹ đình, 3>=50000"
Có nghĩa Tỉnh là: hà nội hoặc hải phòng hoặc bắc ninh
và quận là cầu giấy haowcj mỹ đình
và doanh số >=50000
 


Ví dụ về lọc bảng lương can bộ, giáo viên

Dùng hàm BS_VLOOKUP lọc và sắp xếp kết quả tìm được