Hướng Dẫn Chi Tiết Hàm BS_VLOOKUP Tìm Và Trả Về Mảng động Với ...

Có thể bạn quan tâm

/ Kiến thức Add-in A-Tools / Hàm mảng / 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ướ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ố ResultColIndex, LookupColIndex 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 mô 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ức tìm kiếm: chỉ dùng cho nhà phát triển ứng dụng cao cấp).	+ 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) /-strong/-heart:>:o:-((:-h+ 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") /-strong/-heart:>:o:-((:-h '+ 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ả:

- 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à:

- 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

(SORT=n là khai báo sắp xếp theo một hoặc nhiều cột, n có thể vị trí một cột hoặc danh sách vị trí các cột, ngắn cách nhau bởi dấu phảy (,), số âm là giảm dần. Xem hướng dẫn hàm BS_SORTARRAY) 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_HLOOKUP và Nhậ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 Tham khảo khóa học Tạo báo cáo động trong Excel để làm chủ Add-in A-Tools Download Add-in A-Tools