Hàm chạy trên bảng tính (UDF) chỉ lấy tối đa 1048576 giá trị với Excel 2007 trở lên, 65536 với phiên bản Excel thấp hơn. Nếu muốn lấy số giá trị lớn hơn thì cần chạy hàm trong môi trường VBA với hàm API cua A-Tools là BS_CombinList. Hãy xem bài
Ví dụ
Giả sử ta có 5 giá trị A, B, C, D, E ở vùng A3:A7
- Ví dụ 1: Lấy ra các cặp giá trị gồm 2 giá trị từ vùng A4:A8
+ Công thức là:
=BS_COMBINLIST(A4:A8, 2)
+ Kết quả là một mảng mà các dòng là các trường hợp ghép cặp:
Nếu lấy ra số trường hợp - tổ hợp thì công thức là:
=BS_COMBINLIST(A4:A8, 2, -1)
(Tham số result_type là -1)
Nếu lấy mảng giá trị mà mỗi mảng các giá trị kết hợp tạo thành danh sách nối tiếp, ngăn cách bởi dấu phảy (,). Công thức là:
=BS_TRANSPOSE( BS_COMBINLIST(A4:A8,D2, 1) )
(Tham số result_type là 1. Dùng hàm BS_TRANSPOSE mục đích để xoay chiều mảng kết quả.)
(*) Nếu Excel của bạn không phải là Excel 365 thì phải dùng hàm
BS_FILLARRAY bao bên ngoài để điền cả mảng vào bảng tính. Đây là hàm có nhiều tính năng hỗ trợ hay các bạn nên xem hướng dẫn chi tiết.
=
BS_FILLARRAY(
BS_COMBINLIST(A4:A8, 2) )
-
Ví dụ 2: có 5 vận động viên bóng bàn trong một bảng thi đấu theo thể trức đầu vòng tròn.
Danh sách CÁC VĐV bóng bàn đặt trong A4:A8 là:
WANG Chuqin
LIANG Jingkun
MA Long
FAN Zhendong
LEBRUN Felix
+ Công thức như sau:
=BS_COMBINLIST(A4:A8, 2) )
+ Kết quả là:
-
Ví dụ 3: Theo dữ liệu ở Ví dụ 1. Số giá trị được kết hợp đặt tại ô
D2.
-
Ví dụ 4: Theo dữ liệu ở Ví dụ 3. Các giá trị được ghép vào nhau.
=BS_FILLARRAY(
BS_COMBINLIST(A4:A8,D2, 1),
"TP=YES;")
(*) Nếu không dùng ký tự ",'" ngăn cách các giá trị thì nhập "SEP=NULL" vào tham số Options.
=BS_FILLARRAY(
BS_COMBINLIST(A4:A8,D2, 1, "SEP=NULL;"),
"TP=YES;")
Kết quả là:
Ví dụ 5: Liệt kê giá trị tổ hợp, hàm chỉ chạy trong 1 phút, số giá trị lấy ra tối đa là 1000000.
Giả xử có 26 chữ cái A-Z trong vùng A4:A29, ô D2 nhập số ký tự được ghép vào nhau.
Công thức là:
sd =BS_FILLARRAY(
BS_COMBINLIST(A4:A29,D2,1,"SEP=NULL;
TIMEOUT=1;
TOP=1000000;"),
"TP=YES")
(*) Nếu bạn chỉ cần tính ra giá trị tổ hợp tương tự hàm COMBIN của Excel thì tham số đầu tiên (source) của hàm BS_COMBINLIST la một số nguyên. Trường hợp này thì hàm BS_COMBINLIST tương tự hàm COMBIN cảu Excel.
Ví dụ tính giá trị tổ hợp của 3 trong 5 phần tử:
=BS_COMBINLIST(5, 3)
Hàm Excel thì là:
=COMBIN(5,3)
Kết quả: 10
Ví dụ 6: Liệt kê chỉnh hợp lặp.
Liệt kê các cặp giá trị gồm k phần tử (ở ô D2) trong tổng 5 phần tử trong vùng A4:A8 (A,B,C,D,E).
Mỗi phần tử tham gia từ 1 đến k lần.
=BS_FILLARRAY( BS_COMBINLIST(A4:A8,D2,1,"FUNC=PERMUTA"), "TP=YES")
Hoặc
=BS_FILLARRAY( BS_COMBINLIST(A4:A8,D2,1,"FUNC=1"), "TP=YES")
Kết quả là:
| A,A,A |
| A,A,B |
| A,A,C |
| A,A,D |
| A,A,E |
| A,B,A |
| A,B,B |
| A,B,C |
| A,B,D |
| A,B,E |
| ... |
(Còn nữa tôi không liệt kê hết)
Ví dụ 7: Liệt kê chỉnh hợp không lặp.
Liệt kê các cặp giá trị gồm k phần tử (ở ô D2) trong tổng 5 phần tử trong vùng A4:A8 (A,B,C,D,E).
=BS_FILLARRAY( BS_COMBINLIST(A4:A8,D2,1,"FUNC=PERMUT"), "TP=YES")
Hoặc
=BS_FILLARRAY( BS_COMBINLIST(A4:A8,D2,1,"FUNC=2"), "TP=YES")
Kết quả là:
| A,B,C |
| A,B,D |
| A,B,E |
| A,C,B |
| A,C,D |
| A,C,E |
| A,D,B |
| A,D,C |
| A,D,E |
| A,E,B |
| A,E,C |
| A,E,D |
| B,A,C |
| B,A,D |
| ... |
(Còn nữa tôi không liệt kê hết)
Ví dụ 8: Cách xử lý kết quả tổ hợp, chỉnh hợp, chỉnh hợp lặp hàng trăm triệu giá trị.
Bạn cần phải chạy hàm API BS_CombinList của A-Tools trong VBA hay ngôn ngữ lập trình nào đó.
Xem chi tiết tại đây.
(*) Hãy mở file "
C:\A-Tools\Help & Demos\\Function BS_ COMBINLIST.xlsx" để xem ví dụ đầy đủ.
Tác giả Nguyễn Duy Tuân
Download Add-in A-Tools