Đây là một siêu hàm của Add-in A-Tools v10 (2024). Hàm cho phép tính ra số giá trị tổ hợp, liệt kê tất cả các giá trị tổ hợp trong Excel. Hàm có nhiều tùy chọn đáp ứng đa dạng về nhu cầu làm tổ hợp.
- source: là mảng giá trị phục vụ cho việc lấy tổ hợp. Nếu tham số này là số nguyên hàm trả về giá trị tổ hợp.
- number_chosen: là số giá trị được gộp lại. number_chosen <= số phần tử mảng của source.
+ Nếu giá trị của tham số này giá trị là: < 0 hoặc lớn hơn số phần tử của source trả về lỗi #NUM!
+ Nếu giá trị tham số này là 0 hàm trả về một trường hợp mà các phần tử hợp thành chính là các phần tử của mảng source.
- result_type: nếu là 0 - Các phần tử hợp lại tạo thành mảng; Nếu là 1 thì các phần tử được nối lại và ngăn cách bởi dấu phảy (,); Nếu là -1 hàm trả về số trường tính theo tổ hợp. : là giá trị cần tìm, là một con số hoặc biểu thức so sánh.
- options: tham số này kiểu chuỗi, có thể bỏ qua. Các thuộc tính sử dụng:
+ Nếu muốn thay đổi ký tự ghép giá trị (khi tham số result_type=1), nhập "SEP=Delimiter;". Delimiter là ký tự bất kỳ, nếu là NULL hàm không dùng ký tự để ghép.
+ Quy định thời gian chạy: "TIMEOUT=m;" m là số phút tối đa chạy hàm. Nếu hàm chạy quá thời gian TIMEOUT sẽ dừng lại. Nếu không khai báo hàm chạy đến khi hoàn thành.
+ Chỉ định số giá trị: "TOP=n;" n là số giá trị tối đa được lấy ra. Nếu không khai báo hàm lấy ra tất cả trường hợp. Hàm chạy trên bảng tính (UDF) 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 bạn muốn lấy số giá trị lớn hơn thì cần chạy hàm API trong môi trường VBA hay ngôn ngữ khác.
Tùy chọn cách tính của hàm: Với tham số OPTIONS bạn nhập các thuộc tính để nhận kết quả khác nhau:
"FUNC=PERMUTA;" hoặc "FUNC=1;" hàm tính chỉnh hợp lặp.
"FUNC=PERMUT;" hoặc "FUNC=2;" hàm tính chỉnh hợp không lặp.
"FUNC=COMBIN;" hoặc "FUNC=0;" hoặc không khai báo (ngầm định) hàm tính tổ hợp.
Từ phiên bản Add-in A-Tools v10 ngày 22-10-2024 cung cấp hàm API BS_COMBINLIST cho phép chạy đủ tính năng trong phiên bản FREE. Tại sao đã có hàm UDF này trong Add-in A-Toools rồi chúng tôi lại cung cấp API? Bởi vì với hàm UDF BS_COMBINLIST gọi ở trên bảng tính nó bị giới hạn tối đa 1048576 với Excel 2007 hoặc cao hơn, 65536 với Excel 2003 hoặc thấp hơn. Hàm API BS_COMBINLIST gọi trong môi trường lập trình cho phép xử lý hành tỷ giá trị mà không bị ảnh hưởng đến giới hạn của bộ nhớ máy tính. Đây làm hàm API được lập trình để người lập trình gọi nó trong nhiều ngôn ngữ lập trình như VBA, DELPHI, C#, VB.NET, C++. Mã nguồn các ngôn ngữ tôi trình bày trong link dưới đây các bạn có thể trải nghiệm.
1. Ngôn ngữ lập trình VBA
(Liệt kê tôt hợp, chỉnh hợp băng VBA với hàm API BS_COMBINLIST)
Đầu tiên chúng ta cần khai báo hàm API - import hàm. BS_COMBINLIST trong AddinATools.dll
Tạo một module, copy mã nguồn VBA dưới đây:
C#:
'BEGIN COPY -----------------------------
' Import the CombinListAPI function from AddinATools.dll
#If VBA7 Then
Declare PtrSafe Function CombinListAPI Lib "AddinATools.dll" Alias "BS_COMBINLIST" ( _
ByVal SrcArr As Variant, _
ByVal number_chosen As Long, _
ByVal result_type As Long, _
ByVal Options As Variant, _
ByVal pFuncCallback As LongPtr, _
ByRef pRes As Variant) As LongPtr
#Else
Declare Function CombinListAPI Lib "AddinATools.dll" Alias "BS_COMBINLIST" ( _
ByVal SrcArr As Variant, _
ByVal number_chosen As Long, _
ByVal result_type As Long, _
ByVal Options As Variant, _
ByVal pFuncCallback As Long, _
ByRef res As Variant) As Long
#End If
Const MaxRows = 1048576 'in sheet 2007
Const MaxCols = 16384 'in sheet 2007
Dim I&, J&, t
Sub TestComBin()
Const s = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"
Const number_chosen As Long = 6
Const Options = "" '"FUNC=PERMUT;" , "FUNC=PERMUTA;"; "" -> COMBIN
Dim arr, res, result
arr = Split(s, ",")
I = 0: J = 1
t = Timer
res = False 'True: receive array of values; False: do not receive
' Call the CombinListAPI function
result = CombinListAPI(arr, number_chosen, 1, Options, AddressOf TestValueCbk, res)
Debug.Print "A-Tools:", "Count: " & result, "Time: " & (Timer - t) & " seconds."
End Sub
' Callback function implementation
#If VBA7 Then
Function TestValueCbk(AValue As Variant, _
ByVal Count As LongPtr) As Boolean
#Else
Function TestValueCbk(AValue As Variant, ByVal Count As Long) As Boolean
#End If
TestValueCbk = (Count < 1000000) 'TRUE: Continue; FALSE: Stop
I = I + 1
If I > MaxRows Then
I = 1
J = J + 1
'Debug.Print "Column " & J - 1 & " Values: " & Format(Count, "#,##0"), "Time: " & (Timer - t)
DoEvents
End If
If J > MaxCols Then
TestValueCbk = False 'Stop finding.
Debug.Print "Overflow column index."
End If
If Count <= 1000 Then 'Limit 1000 for testing only
'Cells(I, J).Value = AValue
Debug.Print "Item: " & Count & " Value: " & AValue
End If
End Function
'END COPY -----------------------------------------------------
Bây giờ bạn hãy chạy thủ tục TestComBin để xem kết quả trả về ở cửa sổ Immediate
(*) Lưu ý
Các tham số trong hàm API cơ bản giống với hàm UDF BS_COMBINLIST. Tuy nhiên có hai tham số và cách trả về của hàm API cần lưu ý dưới đây:
- pProcCallback:
Là con trỏ đến hàm callback để kiểm tra mỗi lần lấy được giá trị. Cấu trúc hàm callback
Function Callback(AValue As Variant, _
ByVal Count As LongPtr) As Boolean
Hàm Callback trả về True hàm API BS_COMBINLIST sẽ tiếp tục tìm giá trị; False hàm sẽ dừng lại.
- pRes:
Nếu không được khai báo hoặc gán giá trị FALSE thì tham số này sẽ không nhận được giá trị. Nếu là địa chỉ biến có kiểu VARIANT thì nó sẽ nhận được mảng các giá trị kết hợp hoặc hoán vị.
RETURN
+ Hàm trả về một số nguyên là số trường hợp được tính toán.
+ Tham số pRes nhận mảng các giá trị kết hợp hoặc tổ hợp. Nếu đưa biến kiểu VARIANT vào tham số pRes và giá trị của nó không được khác là False.
2. Lập trình bằng Delphi
(Liệt kê tôt hợp, chỉnh hợp băng Delphi với hàm API BS_COMBINLIST)
- Bước 1: Tạo dự án, đặt tên như là "CombinListAPI"
- Bước 2: Tạo unit và đặt tên như là "uCombinListAPI" (File -> New-> Unit Delphi)
- Bước 3: Dán code Delphi dưới đây thay thế toàn bộ mã trong uCombinListAPI:
C++:
//BEGIN COPY---------------------------------------------
unit uCombinListAPI;
//Author: Nguyen Duy Tuan - duytuan@bluesofts.net
interface
uses
SysUtils, Variants, Windows;
function CombinListAPI(SrcArr: OleVariant;
const number_chosen: Integer;
const result_type: Integer;
Options: OleVariant;
const pFuncCallback: Pointer;
const pResVariant: POleVariant): NativeUInt; stdcall;
procedure TestCombinList;
implementation
// Import the CombinListAPI function from AddinATools.dll
function CombinListAPI; external 'AddinATools.dll' name 'BS_COMBINLIST';
// Callback function implementation
function TestValueCbk(const AValue: OleVariant; const Count: NativeUInt): Wordbool; stdcall;
begin
Writeln(Format('Item: %d Value: %s', [Count, string(AValue)]));
Result := (Count < 10000); //True: Continue; False: Stop
end;
procedure TestCombinList;
const
s = 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z';
number_chosen = 6;
Options: WideString = ''; //'FUNC=PERMUT;' , 'FUNC=PERMUTA;' ; '' -> COMBIN
var
res: OleVariant;
result: NativeUInt;
t: Cardinal;
arr: TArray<string>;
begin
arr := s.Split([',']);
{
arr := VarArrayof(['A','B','C','D','E','F','G','H','I','J','K','L','M',
'N','O','P','Q','R','S','T','U','V','W','X','Y','Z']);
}
t := GetTickCount;
res := True; //True: receive array of values; False: do not receive
// Call the CombinListAPI function
result := CombinListAPI(arr, number_chosen, 1, Options, @TestValueCbk, @res);
Writeln(Format('A-Tools: Count: %d, Time: %f seconds', [result, (GetTickCount - t)/1000]));
Res := Unassigned;
end;
end.
//END COPY ------------------------------------------------------
- Bước 4: Mở mã nguồn dữ án "CombinListAPI", dán toàn bộ mã nguồn dưới dây:
C++:
//BEGIN COPY---------------------------------------------
program CombinListAPI;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils,
Winapi.Windows,
uCombinListAPI in 'uCombinListAPI.pas';
begin
try
{ TODO -oUser -cConsole Main : Insert code here }
TestCombinList;
except
on E: Exception do
Writeln(E.ClassName, ': ', E.Message);
end;
MessageBox(GetActiveWindow, 'Completed.','CombinList API', MB_ICONINFORMATION);
end.
//END COPY ------------------------------------------------------
Bây giờ bạn hãy chạy dự án để xem kết quả liệt kê tổ hợp hoặc chỉnh hợp trên mà hình Console.
3. Lập trình bằng ngôn ngữ C#
(Liệt kê tổ hợp, chỉnh hợp bằng C# với hàm API BS_COMBINLIST)
- Bước 1: Tạo Solution C# kiểu ứng dụng Console App
- Bước 2: Mở file Program.cs dán toàn bộ mã nguồn dưới đây:
C#:
//BEGIN COPY -----------------------------------------------------
//Author: Nguyen Duy Tuan - duytuan@bluesofts.net
using System;
using System.Runtime.InteropServices;
namespace Using_BS_CombinList_API
{
class Program
{
// Define the delegate for the callback function
public delegate bool CallBack(ref object AValue, UIntPtr Count);
// Import the CombinListAPI function from AddinATools.dll
[DllImport("AddinATools.dll", EntryPoint = "BS_COMBINLIST")]
public static extern UIntPtr CombinListAPI(object SrcArr,
int number_chosen,
int result_type,
object Options,
CallBack pFuncCallback,
ref object pRes);
// Callback function implementation
public static bool TestValueCbk(ref object AValue, UIntPtr Count)
{
Console.WriteLine("Item: {0} Value: {1}", Count, AValue);
if ((UInt64)Count >= 10000) //for testing only
return false; //Stop
else
return true; //true: Continue; false: Stop
}
static void Main(string[] args)
{
string s = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z";
int number_chosen = 6;
object Options = ""; //"FUNC=PERMUT;" , "FUNC=PERMUTA;" ; "" -> COMBIN
object res = false; //true: receive array of values; false: do not receive
object arr = (object)s.Split(',');
CallBack myCallBack = new CallBack(TestValueCbk);
DateTime t = DateTime.Now;
// Call the CombinListAPI function
UIntPtr result = CombinListAPI(arr, number_chosen, 1, Options, myCallBack, ref res);
Console.WriteLine($"A-Tools: Count: {result}, Time: {(DateTime.Now - t).TotalSeconds} seconds.");
Console.ReadLine();
}
}
}
//END COPY --------------------------------------------------------
4. Lập trình bằng ngôn ngữ vb.net
(Liệt kê tổ hợp, chỉnh hợp bằng vb.net với hàm API BS_COMBINLIST)
- Bước 1: Tạo Solution vb.net kiểu ứng dụng Console App
- Bước 2: Mở file Module1.vb dán toàn bộ mã nguồn dưới đây:
C#:
//BEGIN COPY -----------------------------------------------------
' Author: Nguyen Duy Tuan - duytuan@bluesofts.net
Imports System.Runtime.InteropServices
Module Module1
' Define the delegate for the callback function
Public Delegate Function CallBack(ByRef AValue As Object, ByVal Count As UIntPtr) As Boolean
' Import the CombinListAPI function from AddinATools.dll
<DllImport("AddinATools.dll", EntryPoint:="BS_COMBINLIST")>
Public Function CombinListAPI(ByVal SrcArr As Object,
ByVal number_chosen As Integer,
ByVal result_type As Integer,
ByVal Options As Object,
ByVal pFuncCallback As CallBack,
ByRef pRes As Object) As UIntPtr
End Function
' Callback function implementation
Public Function TestValueCbk(ByRef AValue As Object, ByVal Count As UIntPtr) As Boolean
Console.WriteLine("Item: {0} Value: {1}", Count, AValue)
If Count.ToUInt64 >= 10000 Then ' for testing only
Return False ' Stop
Else
Return True ' True: Continue; False: Stop
End If
End Function
Sub Main()
Dim s As String = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"
Dim number_chosen As Integer = 6
Dim Options As Object = "" ' "FUNC=PERMUT;" , "FUNC=PERMUTA;" ; "" -> COMBIN
Dim res As Object = False ' True: receive array of values; False: do not receive
Dim arr As Object = s.Split(","c)
Dim myCallBack As CallBack = New CallBack(AddressOf TestValueCbk)
Dim t As DateTime = DateTime.Now
' Call the CombinListAPI function
Dim result As UIntPtr = CombinListAPI(arr, number_chosen, 1, Options, myCallBack, res)
Console.WriteLine($"A-Tools: Count: {result}, Time: {(DateTime.Now - t).TotalSeconds} seconds.")
Console.ReadLine()
End Sub
End Module
//END COPY --------------------------------------------------------
Lời kết
Với yêu cầu bài toán liệt kê tổ hợp, chỉnh hợp sẽ có thể phải liệt kê số lượng số phần tử rất lớn, có thể lên đến hàng tỷ giá trị, việc hàm lấy vào một mảng rồi trả về cho người dùng có thể làm tràn bộ nhớ "Out of memory". Để giải quyết việc này chúng ta dùng phương pháp liệt kê từng đoạn rồi kiểm tra giá trị thỏa mãn hoặc gọi hàm callback như ứng dụng hàm API BS_COMBINLIST trong bài này để kiểm tra, nếu thấy thỏa mãn thì thoát khỏi chu trình chạy. Trong ví dụ bài viết này bạn có thể viết code trong hàm callback điền giá trị lấy được vào một tập tin để dùng làm dữ liệu phân tích sau nàycũng là một ý tưởng tốt.
Các bài toán ứng dụng trong xác suất thống kê thì Excel có các hàm tính số tổ hợp - COMBIN; chỉnh hợp PERMUT; chỉnh hợp lặp PERMUTATIONA; hoán vị - FACT. Hàm BS_COMBINLIST tôi đưa thêm một dạng ứng dụng là liệt kê các trường hợp đó ngoài cách trả về số trường hợp. Những người làm kế toán, tài chính thì những hàm toán học, xác suất sẽ rất ít dùng, còn trong lính vực khác trong cuộc sống thì vẫn ứng dụng. Hàm này được dùng miễn phí trong Add-in A-Tools, thêm một hướng ứng dụng ngoài các hàm trọng tâm phục vụ kế toán, tài chính mà Add-in A-Tools đã có.
Một vài câu hỏi liên quan các hàm xác suất tại đây: https://tech12h.com/bai-tap/chia-da...am-4-moi-10-hoc-sinh-em-hay-tinh-so-cach-phan
Nếu không đặt giá trị TOP thì máy treo còn đặt giá trị TOP là 10.000 thì hết thời gian như hình, có thể do ghi xuống Sheet lâu hơn khi xuất kết quả ra console ạ
Nếu không đặt giá trị TOP thì máy treo còn đặt giá trị TOP là 10.000 thì hết thời gian như hình, có thể do ghi xuống Sheet lâu hơn khi xuất kết quả ra console ạ
Ghi giá trị ra sheet là ảnh hưởng đến tốc độ là đúng rồi đó. Nếu bạn muốn ghi ra sheet thì nên ghi theo mảng. Cứ đóng gói từng mảng với một kích thước nhất định, đủ thì điền ra, sau khi điền hết chu trình lấy giá trị thì điền mảng còn lại ra nốt.
Mình thêm ví dụ nữa về dùng hàm API BS_COMBINLIST bằng ngôn ngữ C++, dự án tạo bằng Visual C++ 2019.
(Toàn bộ mã nguồn ứng dụng hàm API với các ngôn ngữ lập trinhg VBA, DELPHI, C#, VB.NET, C++ tại đường dẫn: "C:\A-Tools\HELP & DEMOS\A-Tools VBA Programming\BS_COMBINLIST API\")
C++:
// Author: Nguyen Duy Tuan - duytuan@bluesofts.net
//BEGIN COPY---------------------------------------------
#include <iostream>
#include <windows.h>
#include <string>
#include <vector>
#include <ctime>
#include <comutil.h>
#include <atlbase.h>
// Định nghĩa kiểu callback
typedef BOOL(WINAPI* CallBack)(VARIANT* AValue, size_t Count);
// Khai báo hàm CombinListAPI từ DLL
typedef size_t(WINAPI* CombinListAPIFunc)(VARIANT SrcArr,
int number_chosen,
int result_type,
VARIANT Options,
CallBack pFuncCallback,
VARIANT* pRes);
// Hàm callback
BOOL WINAPI TestValueCbk(VARIANT* AValue, size_t Count)
{
// Giả sử AValue là chuỗi ký tự (BSTR), cần chuyển đổi sang std::wstring
if (AValue->vt == VT_BSTR)
{
std::wstring value(AValue->bstrVal, SysStringLen(AValue->bstrVal));
std::wcout << L"Item: " << Count << L" Value: " << value << std::endl;
}
return Count < 1000; // TRUE: Continue, FALSE: Stop
}
int main()
{
// Load DLL
HINSTANCE hInstLib = LoadLibrary(TEXT("AddinATools.dll"));
if (hInstLib == nullptr)
{
std::cerr << "Unable to load DLL!" << std::endl;
return 1;
}
// Lấy địa chỉ hàm CombinListAPI
CombinListAPIFunc CombinListAPI = (CombinListAPIFunc)GetProcAddress(hInstLib, "BS_COMBINLIST");
if (CombinListAPI == nullptr)
{
std::cerr << "Unable to find BS_COMBINLIST function in DLL!" << std::endl;
FreeLibrary(hInstLib);
return 1;
}
// Chuẩn bị dữ liệu đầu vào
std::wstring s = L"A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z";
int number_chosen = 6;
CComVariant options(L""); // "FUNC=PERMUT;", "FUNC=PERMUTA;", hoặc "" -> COMBIN
// Tách chuỗi thành mảng VARIANT
std::vector<CComVariant> arr;
size_t start = 0, end = s.find(L',');
while (end != std::wstring::npos)
{
arr.push_back(CComVariant(s.substr(start, end - start).c_str()));
start = end + 1;
end = s.find(L',', start);
}
arr.push_back(CComVariant(s.substr(start).c_str())); // Thêm phần tử cuối cùng
// Tạo mảng VARIANT (SAFEARRAY)
SAFEARRAYBOUND bounds[1];
bounds[0].lLbound = 0;
bounds[0].cElements = ULONG(arr.size());
SAFEARRAY* psa = SafeArrayCreate(VT_VARIANT, 1, bounds);
for (LONG i = 0; i < LONG(arr.size()); ++i)
{
SafeArrayPutElement(psa, &i, &arr[i]);
}
VARIANT varArr;
varArr.vt = VT_ARRAY | VT_VARIANT;
varArr.parray = psa;
VARIANT res = CComVariant(true); //true nhận mảng giá trị; false không nhận
clock_t t = clock();
size_t result = CombinListAPI(varArr, number_chosen, 1, options, TestValueCbk, &res);
// Hiển thị kết quả
std::wcout << L"A-Tools: Count: " << result << L", Time: " << (double)(clock() - t) / CLOCKS_PER_SEC << L" seconds." << std::endl;
// Giải phóng bộ nhớ
VariantClear(&varArr);
VariantClear(&res);
// Giải phóng DLL
FreeLibrary(hInstLib);
std::wcin;
return 0;
}
//END COPY---------------------------------------------
