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) )

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 các mảng dữ liệu gồm các 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:

Bước 1: Tạo công thức điền mảng kết quả là vị trí dòng tìm được và đặt name là MYROWS_3

Công thức tại D4:

=BS_FILLARRAY(BS_MATCHCALC(E1,B4:B13),
"TP=YES; NAME=MYROWS_3; ONAFTERUPDATE=UPDATE(F4);")

Lưu ý công thức trên có thêm khai báo thuộc tính của hàm BS_FILLARRAY
ONAFTERUPDATE=UPDATE(F4); A-Tools sẽ cập nhật công thức ơt F4 sau khi công thức này hoàn tất. Lý do ô F4 dùng công thức lấy giá trị từ mảng trả về từ công thức này lưu trong name MYROWS_3.

Kết quả là:


Bước 3: Điền công thức lấy giá trị trong bảng dữ liệu nguồn (A3:B13) dựa vào các vị trí lấy được ở công thức trên, lưu trong name MYROWS_3.

Công thức tại F4 là:

=BS_FILLARRAY(
                               INDEX(A4:B13,MYROWS_3,{1,2}),
                               "INSERT=YES;")

Giải thích:
INDEX(array. rows, cols) - Lấy mảng giá trị trong array từ rows dòng, cols cột.
MYROWS_3 là mảng các vị trí dòng để hàm INDEX lấy giá trị ở vùng D4:D7.
{1, 2} là mảng các vị trí cột hàm INDEX sẽ lấy giá trị;
INSERT+YES; để bảng kết quả tự chèn dòng, chèn cột khi giá trị điều kiện thay đối.

+ Kết quả là:

 - Ví dụ 4: Vẫn theo ví dụ 3. Nhưng muốn hiển thị tối đa 4 mảng kết quả, mỗi mảng tổng của nó 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,B4:B13,,,,4),
"TP=YES; NAME=MYROWS_3; ONAFTERUPDATE=UPDATE(F4);")

+ 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 kép vào nhau. Mỗi mảng 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: 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,B4:B13,,,,,TRUE),
"TP=YES; NAME=MYROWS_3;
ONAFTERUPDATE=UPDATE(F4);")

+ 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ụ 7: Lấy mảng vị trí dòng mà giá trị trung bình các phần từ mảng ở 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