Ví dụ

Giả sử ta có vùng dữ liệu từ A4: B13 như dưới đây

Code	Qty
A1	10
A2	40
A3	20
A4	50
A5	80
A6	70
A7	30
A8	60
A9	90
A10	10

- Ví dụ 1: Lấy ra dánh sách vị trí các phần tử mảng ở cột QTY (B4:B13)  mà tổng bằng 100

+ Công thức là:

BS_MATCHCALC(100, B4:B13)

+ Kết quả là một mảng các vị trí như sau:
1
2
3
7

Để điền cả mảng vào bảng tính ta dùng hàm BS_FILLARRAY và để tạo NAME, xoay chiều mảng. 

Công thức là:

BS_FILLARRAY( BS_MATCHCALC(E1,B4:B13) ,
                              "TP=YES; NAME=MYROWS_1")

+ Địa chỉ E1 là ô chứa giá trị tìm kiếm là 100.
+ TP=YES; để xoay chiều mảng kết quả. Vì mảng trả về của hàm BS_MATCHCALC phân bố theo cột.
+ NAME=MYROWS_1; để A-Tools tạo NAME có tên MYROWS_1 cho vùng kết quả.

Kết quả như hình dưới đây.


Khi công thức đã chạy, bạn có name MYROWS_1 bao vùng kết quả là các vị trí. Nếu muốn nhận mảng giá trị ở cột QTY ứng với mỗi vị trí dùng công thức Excel

=BS_FILLARRAY( INDEX($B$4:$B$13, MYROWS_1) )

Hàm lấy trực tiếp giá trị là:
 =BS_FILLARRAY(BS_MATCHCALC(E1,B4:B13,1,1), "TP=YES")

Kết quả là:
10
40
20
30

- Ví dụ 2: Lấy ra các CODE ứng với các vị trí ở QTY mà tổng của chúng là 100 ở ô E1.

+ Phân tích:
Các tham số cần dùng là
lookup_array: chỉ định vùng A4:B13 vì hai cột: A là CODE; B là QTY phục vụ cho tìm kiếm và trả về giá trị
lookup_column: chỉ định là 2 vì trong mảng A4:B13 vị trí 2 là cột QTY.
result_column: chỉ định là 1 vì trong mảng A4:B13 vị trí 1 là COD ta cần lấy giá trị.

+ Công thức là:

=BS_FILLARRAY( BS_MATCHCALC(E1,A4:B13,2,1),"TP=YES;")

+ Kết quả là:


- Ví dụ 3:  Lấy ra mảng dữ liệu gồm các cột thông tin CODE, QTY trong vùng A3:B13 (dữ liệu nguồn), ứng với các dòng mà tổng các QTY ở đó là 100. Giá trị 100 đặt tại ô E1.

+ Cách làm:

Cách làm vẫn giống "Ví dụ 2" nhưng kết quả lấy về là nhiều cột từ dữ liệu nguồn. Vì vậy ta sẽ làm công thức với các bước.

+ Công thức:

Công thức tại D4:

=BS_FILLARRAY(BS_MATCHCALC(E1,A4:B13,2, {1,2})")

Lưu ý: tham số result_column bây giờ là mảng chứa các vị trí cột cần lấy giá trị : {1,2}.
Nếu lấy tất cả các cột thì gàn tham số này là "*". Công thức như sau:

=BS_FILLARRAY(BS_MATCHCALC(E1,A4:B13,2, "*")")

Kết quả là:

 - Ví dụ 4: Vẫn theo ví dụ 3. Nhưng muốn hiển thị tối đa 4 trường hợp (mảng), mỗi trường hợp tổng cột QTY là giá trị tại E1.

+ Phân tích: Tham số [limit] để chỉ ra số cặp kết quả được trả về. Chỉ định tham số này giá trị là 4.
+ Công thức là:
Bạn chỉ cần sửa công thức tìm mảng vị trí tại ô D4, thêm giá trị 4 vào tham số limit.

=BS_FILLARRAY(BS_MATCHCALC(E1,A4:B13,2,"*",,4), "INSERT=YES;")

+ Kết quả là:

(Như hình trên các bạn thấy hàm nhận được 4 trường hợp mà mảng kết quả được ghép vào nhau. Mỗi mảng đều có tổng QTY đều là 100 ở E1.)

- Ví dụ 5: Tính ra tổng số trường có thể nhận kết quả?
 
=BS_MATCHCALC(E1,B4:B13,,,, 0)

Kết quả là 17. Tức ta có 17 mảng kết quả.

- Ví dụ 6: Lấy ra tất cả các trường hợp có tổng QTY ltaij ô E1

=BS_FILLARRAY(BS_MATCHCALC(E1,A4:B13,2,"*",,-1))

(Tham số limit = -1 )

- Ví dụ 7: Vẫn là yêu cầu ví dụ 4 nhưng lấy kết quả mà số phần tử mảng là thấp nhất.

+ Phần tích: ta sử dụng tham số  min_elements gán là TRUE
+ Công thức là:

=BS_FILLARRAY(BS_MATCHCALC(E1,A4:B13,2,"*",,,TRUE),
"INSERT=YES;")

+ Kết quả:
 

(Hãy so sánh kết quả trong hình này với hình ở Ví dụ 4)

- Ví dụ 8: Lấy mảng giá trị mà giá trị trung bình ở cột QTY là một số ở E1

+ Phan tích: tham số  func_kind điền giá trị là 1 - Hàm Average.
+ Công thức:
 =BS_MATCHCALC(E1,B4:B13,,,,,,1)

(*) Hãy mở file "C:\A-Tools\Help & Demos\\Function BS_ MATCHCALC.xls" để xem ví dụ đầy đủ.

Tác giả Nguyễn Duy Tuân

Download Add-in A-Tools