Chức năng:
Tính tổng các giá trị trong một vùng dữ liệu thỏa mãn tối đa 7 điều kiện. Hàm được thiết kế để thay thế và mở rộng khả năng của hàm SUMIFS trong Excel, đồng thời vẫn giữ nguyên
pháp quen thuộc để dễ sử dụng.
Ngoài việc hỗ trợ đầy đủ các kiểu điều kiện của SUMIFS (giá trị, biểu thức so sánh, ký tự đại diện, ngày tháng, số và văn bản), SUMIFS2 còn bổ sung nhiều khả năng mà SUMIFS không có:
SumRange có thể gồm nhiều cột hoặc nhiều hàng. Khi kết hợp với điều kiện dạng mảng, hàm sẽ tự động tính tổng cho từng cột hoặc từng hàng tương ứng và trả về mảng kết quả, rất phù hợp để tổng hợp đồng thời nhiều chỉ tiêu hoặc nhiều trường dữ liệu.SUMIFS2 đặc biệt phù hợp để xây dựng Dashboard, báo cáo quản trị và mô hình phân tích dữ liệu động, nơi cần tổng hợp đồng thời nhiều chỉ tiêu, nhiều cột dữ liệu hoặc nhiều giá trị điều kiện chỉ với một công thức duy nhất.
Hàm sử dụng core của Excel làm lõi tính toán nên có tốc độ xử lý rất nhanh trên các bảng dữ liệu lớn, đồng thời vẫn hỗ trợ đầy đủ các kiểu điều kiện của SUMIFS như:
"Hà Nội").">100", "<=" & A1, "<>"...).*, ?).Hàm được xây dựng trên nền các hàm xử lý tối ưu của thư viện SUMIFS nên có tốc độ tính toán cao ngay cả với các bảng dữ liệu lớn.
Cú pháp:
=SUMIFS2(
SumRange,
[CriteriaRange1], [Criteria1],
[CriteriaRange2], [Criteria2],
[CriteriaRange3], [Criteria3],
[CriteriaRange4], [Criteria4],
[CriteriaRange5], [Criteria5],
[CriteriaRange6], [Criteria6],
[CriteriaRange7], [Criteria7]
)Ví dụ:
=SUMIFS2(D2:D100,B2:B100,"Miền Bắc")
→ Tính tổng các giá trị trong D2:D100 của những dòng có giá trị "Miền Bắc" trong vùng B2:B100.
=SUMIFS2(D2:D100,B2:B100,F2:F5)
→ Nếu F2:F5 chứa danh sách các khu vực, hàm sẽ tự động trả về một mảng gồm tổng doanh số của từng khu vực tương ứng trong F2:F5 (kết quả Spill).
=SUMIFS2(E2:E100,B2:B100,H2:H5,C2:C100,I2:I5)
→ Tính tổng theo đồng thời hai điều kiện, trong đó cả hai điều kiện đều là vùng dữ liệu. Hàm sẽ trả về mảng kết quả tương ứng với từng cặp điều kiện mà không cần sao chép công thức.
Chức năng:
Tính tổng các giá trị trên một hoặc nhiều trang tính (Worksheet) theo tối đa 7 điều kiện. Khác với SUMIFS của Excel chỉ làm việc trên một vùng dữ liệu cố định, SUMIFS3 cho phép tổng hợp dữ liệu từ nhiều trang tính chỉ bằng một công thức, rất phù hợp với các mô hình lưu dữ liệu theo tháng, theo chi nhánh hoặc theo từng đơn vị.
Tên các trang tính được truyền thông qua đối số FromSheets. Hàm sẽ lần lượt thực hiện phép tính trên từng trang, sau đó cộng gộp kết quả và trả về tổng cuối cùng.
SUMIFS3 hỗ trợ đầy đủ các kiểu điều kiện của SUMIFS như:
">100", "<="&A1, "<>"...).*, ?).Đặc biệt, một hoặc nhiều đối số điều kiện có thể là mảng (Dynamic Array). Khi điều kiện là một vùng dữ liệu gồm nhiều giá trị, hàm sẽ tự động tính toán cho từng giá trị và trả về kết quả dưới dạng mảng động (spill), giúp thống kê nhiều chỉ tiêu cùng lúc mà không cần sao chép công thức.
Các vùng dữ liệu (SumRange, CriteriaRange) được truyền dưới dạng địa chỉ ô (chuỗi văn bản) thay vì đối tượng Range. Điều này giúp cùng một địa chỉ vùng có thể được áp dụng cho tất cả các trang tính được chỉ định trong FromSheets, giảm đáng kể số lượng công thức và đơn giản hóa việc xây dựng báo cáo tổng hợp.
Cú pháp:
=SUMIFS3(
FromSheets,
SumRange,
CriteriaRange1, Criteria1,
[CriteriaRange2], [Criteria2],
[CriteriaRange3], [Criteria3],
[CriteriaRange4], [Criteria4],
[CriteriaRange5], [Criteria5],
[CriteriaRange6], [Criteria6],
[CriteriaRange7], [Criteria7])Trong đó:
"E2:E100").Ví dụ:
=SUMIFS3("T1;T2;T3","E2:E100","B2:B100","Miền Bắc")
→ Tính tổng các giá trị trong vùng E2:E100 của ba trang tính T1, T2 và T3 đối với các dòng có giá trị "Miền Bắc" trong vùng B2:B100.
=SUMIFS3("T1:T12","E2:E100","B2:B100",H2:H5)
→ Nếu H2:H5 là danh sách các khu vực, hàm sẽ tính tổng dữ liệu trên tất cả các trang tính được chỉ định và trả về một mảng kết quả, mỗi phần tử tương ứng với một khu vực trong H2:H5.
=SUMIFS3("CN01;CN02;CN03","F2:F500","C2:C500",K2:K10,"D2:D500",2026)
→ Tính tổng vùng F2:F500 trên ba trang tính CN01, CN02 và CN03 cho từng giá trị trong K2:K10, đồng thời chỉ lấy các dòng có năm bằng 2026. Kết quả được trả về dưới dạng mảng động (spill).
Hàm SUMIFS3 có nhiều tính năng và ngắn gọn hơn hàm SUMIFS2 và SUMIFS, để tính tổng theo điều kiện từ nhiều sheet chứa dữ liệu cùng cấu trúc. Hàm SUMIF3 có đầy đủ tính năng của hàm SUMIFS2, cho phép tính tổng với đối số điều kiện phức hợp
Chức năng:
Tính tổng các giá trị trong một Excel Table (ListObject) theo tên cột và tối đa 7 điều kiện. Thay vì phải xác định địa chỉ vùng dữ liệu như SUMIFS, hàm cho phép tham chiếu trực tiếp đến tên cột trong bảng, giúp công thức dễ đọc, dễ bảo trì và không bị ảnh hưởng khi bảng thay đổi kích thước.
SUMIFDS hỗ trợ đầy đủ các kiểu điều kiện của SUMIFS như giá trị, biểu thức so sánh (">100", "<="&A1...), ký tự đại diện (*, ?), ngày tháng, số và văn bản.
Đặc biệt, các đối số điều kiện có thể là mảng (Dynamic Array). Khi một hoặc nhiều điều kiện là vùng dữ liệu gồm nhiều giá trị, hàm sẽ tự động tính toán cho từng giá trị và trả về kết quả dưới dạng mảng (spill), giúp tổng hợp nhiều chỉ tiêu chỉ với một công thức.
Hàm được xây dựng trên nền các hàm xử lý tối ưu của thư viện SUMIFS nên có tốc độ tính toán cao ngay cả với các bảng dữ liệu lớn. Đồng thời, việc sử dụng tên cột thay cho địa chỉ vùng giúp công thức rõ ràng và thuận tiện khi xây dựng Dashboard hoặc báo cáo động.
Cú pháp:
=SUMIFDS(DataTable,SumName,
[CriteriaName1], [Criteria1],
[CriteriaName2], [Criteria2],
[CriteriaName3], [Criteria3],
[CriteriaName4], [Criteria4],
[CriteriaName5], [Criteria5],
[CriteriaName6], [Criteria6],
[CriteriaName7], [Criteria7])Trong đó:
Ví dụ: =SUMIFDS(Sales,"Doanh thu","Khu vực","Miền Bắc")
→ Tính tổng cột Doanh thu của bảng Sales đối với các dòng có giá trị "Miền Bắc" trong cột Khu vực.
=SUMIFDS(Sales,"Doanh thu","Khu vực",F2:F5)
→ Nếu F2:F5 chứa danh sách các khu vực, hàm sẽ trả về một mảng kết quả, mỗi phần tử là tổng doanh thu của một khu vực tương ứng.
=SUMIFDS(Sales,"Doanh thu","Khu vực",F2:F5,"Năm",2025)
→ Tính tổng doanh thu năm 2025 cho từng khu vực trong F2:F5 và trả về kết quả dưới dạng mảng động (spill).
Chức năng:
Tính tổng các giá trị của các ô có màu nền giống với một ô tham chiếu. Hàm sử dụng thuộc tính ColorIndex của màu nền để so sánh, sau đó cộng tất cả các ô trong vùng dữ liệu có cùng màu.
SUMIFCOLOR đặc biệt hữu ích khi dữ liệu đã được phân loại hoặc đánh dấu bằng màu sắc (ví dụ: tô màu các khoản cần thanh toán, các dòng ưu tiên hoặc các giá trị đã kiểm tra) và cần tính tổng nhanh mà không phải tạo thêm cột phụ.
Lưu ý rằng hàm chỉ nhận diện màu nền (Fill Color) của ô, không kiểm tra màu chữ. Khi chỉ thay đổi màu nền của ô, Excel có thể không tự động tính lại công thức; trong trường hợp đó cần nhấn F9, sửa một ô bất kỳ hoặc tính toán lại sổ làm việc để cập nhật kết quả.
Cú pháp: =SUMIFCOLOR(ColorRange, CellRefColor)
Trong đó:
Ví dụ: =SUMIFCOLOR(C2:C100,F1)
→ Tính tổng các giá trị trong vùng C2:C100 có màu nền giống với màu nền của ô F1.
=SUMIFCOLOR(D:D,H2)
→ Tính tổng toàn bộ cột D đối với các ô có màu nền trùng với ô H2.
Chức năng: Trích xuất dữ liệu từ một hoặc nhiều bảng dữ liệu dựa trên các điều kiện lọc, đồng thời chỉ định các cột cần trả về. Hàm hỗ trợ gộp dữ liệu từ nhiều bảng, thêm số thứ tự, hiển thị nguồn dữ liệu và tùy chọn ẩn hoặc hiển thị dòng tiêu đề trong kết quả.
Cú pháp: =TABLEX(RowLabel, FilterRng, ShowSource, NoneHeader, NumOrder, DataTable1, [DataTable2], ...)
Trong đó:
; hoặc một vùng chứa các tên cột.=, >, <, >=, <=, <>) và ký tự đại diện (*, ?).0: Không hiển thị nguồn dữ liệu.1: Hiển thị cột Source ở đầu kết quả.-1: Hiển thị cột Source ở cuối kết quả.0: Trả về hàng tiêu đề.1: Không trả về hàng tiêu đề.0: Không đánh số thứ tự.1: Thêm cột No đánh số thứ tự các bản ghi.Ví dụ: =TABLEX("Mã hàng;Tên hàng;Số lượng",F1:G2,1,0,1,TableBanHang2024,TableBanHang2025)
Kết quả trả về danh sách các bản ghi từ hai bảng TableBanHang2024 và TableBanHang2025 thỏa mãn điều kiện trong vùng F1:G2, bao gồm các cột Mã hàng, Tên hàng, Số lượng, có thêm cột No ở đầu, cột Source cho biết bảng nguồn và hàng tiêu đề. Nếu không tìm thấy bản ghi nào phù hợp, hàm trả về thông báo "No record found".
Chức năng: Nhóm dữ liệu từ một hoặc nhiều bảng theo một hoặc nhiều cột, đồng thời thực hiện phép tổng hợp trên các cột giá trị. Hàm hỗ trợ tính SUM, COUNT, MAX, MIN, lọc dữ liệu theo điều kiện, sắp xếp kết quả, gộp nhiều cột nhóm thành một cột và hiển thị dòng tổng cộng ở đầu hoặc cuối bảng kết quả.
Cú pháp: =GROUPX(RowLabel, ValueLabel, ShowTotal, SortRow, MergeRow, FilterRng, DataTable1, [DataTable2], ...)
Trong đó:
; hoặc là vùng chứa các tên cột.0: Không hiển thị tổng cộng.1: Hiển thị dòng tổng cộng ở cuối.-1: Hiển thị dòng tổng cộng ngay sau tiêu đề.2, 3, 4: Thực hiện lần lượt các phép COUNT, MAX, MIN."Tổng cộng" hoặc "-Tổng cộng"): Thực hiện SUM và sử dụng chuỗi làm nhãn dòng tổng; thêm dấu - để đặt dòng tổng ở đầu bảng.0: Giữ nguyên thứ tự xuất hiện.1: Sắp xếp tăng dần.-1: Sắp xếp giảm dần.0: Mỗi trường nhóm là một cột riêng.1: Gộp các trường nhóm thành một cột, phân cách bằng " | ".=, <>, >, <, >=, <= và ký tự đại diện *, ?.Ví dụ: =GROUPX("Khu vực;Nhân viên","Doanh thu",1,1,0,F1:G2,Table2024,Table2025)
Kết quả trả về bảng tổng hợp doanh thu theo Khu vực và Nhân viên từ hai bảng Table2024 và Table2025, chỉ bao gồm các bản ghi thỏa mãn điều kiện trong vùng F1:G2. Kết quả được sắp xếp tăng dần theo trường nhóm và có thêm dòng Total ở cuối chứa tổng doanh thu của tất cả các nhóm. Hàm cũng hỗ trợ tổng hợp đồng thời nhiều cột giá trị và sẽ tự động cộng gộp dữ liệu từ tất cả các bảng được truyền vào.
Chức năng: Tạo bảng tổng hợp (Pivot Table) từ một hoặc nhiều bảng dữ liệu theo hàng và cột, đồng thời thực hiện các phép tổng hợp như SUM, COUNT, MAX hoặc MIN. Hàm hỗ trợ nhiều trường nhóm, nhiều trường giá trị, lọc dữ liệu, sắp xếp hàng và cột, gộp các trường nhóm, đồng thời hiển thị tổng cộng theo hàng, theo cột và tổng chung.
Cú pháp: =PIVOTX(RowLabel, ColLabel, ValueLabel, ShowTotal, SortRow, SortCol, MergeRow, FilterRng, DataTable1, [DataTable2], ...)
Trong đó:
; hoặc là vùng chứa tên cột.""), kết quả sẽ là bảng tổng hợp theo hàng (tương tự GROUPX).0: Không hiển thị tổng cộng.1: Hiển thị tổng cộng ở cuối bảng.-1: Hiển thị tổng cộng ngay dưới dòng tiêu đề.2: Đếm số bản ghi (COUNT).3: Lấy giá trị lớn nhất (MAX).4: Lấy giá trị nhỏ nhất (MIN)."Grand Total" hoặc "-Grand Total"): Thực hiện phép cộng (SUM) và sử dụng chuỗi làm nhãn dòng tổng.0: Giữ nguyên thứ tự xuất hiện.1: Sắp xếp tăng dần theo hàng.-1: Sắp xếp giảm dần theo hàng.0: Giữ nguyên thứ tự xuất hiện.1: Sắp xếp tăng dần theo cột.-1: Sắp xếp giảm dần theo cột.0: Mỗi trường trong RowLabel là một cột riêng.1: Gộp các trường nhóm thành một cột, phân cách bằng " | ".=, <>, >, <, >=, <= và ký tự đại diện *, ?.Ví dụ: =PIVOTX("Khu vực","Năm","Doanh thu",1,1,1,0,F1:G2,Table2024,Table2025)
Kết quả trả về bảng Pivot tổng hợp Doanh thu theo Khu vực (hàng) và Năm (cột) từ hai bảng Table2024 và Table2025, chỉ bao gồm các bản ghi thỏa mãn điều kiện trong vùng F1:G2. Các hàng và cột được sắp xếp tăng dần, đồng thời có thêm cột tổng theo từng hàng, dòng tổng theo từng cột và tổng cộng toàn bộ bảng ở cuối kết quả. Hàm cũng hỗ trợ đồng thời nhiều trường hàng, nhiều trường cột và nhiều trường giá trị để tạo các bảng tổng hợp phức tạp tương tự Pivot Table của Excel.
Chức năng: Chuyển đổi dữ liệu từ dạng bảng rộng (wide table), trong đó nhiều cột giá trị được trải ngang, sang dạng bảng dài (long table) với mỗi giá trị trở thành một bản ghi riêng. Hàm hỗ trợ lọc dữ liệu, đổi tên cột sau khi chuyển đổi, hiển thị nguồn dữ liệu, loại bỏ các giá trị bằng 0 và kết hợp dữ liệu từ nhiều bảng có cùng cấu trúc.
Cú pháp: =UNPIVOTX(RowLabel, ValueLabel, FilterRng, RowName, ValName, ShowSource, HideZero, DataTable1, [DataTable2], ...)
Trong đó:
; hoặc là vùng chứa tên cột.=, <>, >, <, >=, <= và ký tự đại diện *, ?.0: Không hiển thị nguồn dữ liệu.1: Hiển thị cột Source ở đầu kết quả.-1: Hiển thị cột Source ở cuối kết quả.0: Giữ lại các bản ghi có giá trị bằng 0.1: Loại bỏ các bản ghi có giá trị bằng 0.Ví dụ: =UNPIVOTX("Mã hàng;Tên hàng","Tháng 1;Tháng 2;Tháng 3",F1:G2,"Tháng","Doanh thu",1,1,Table2024,Table2025)
Kết quả trả về bảng dữ liệu trong đó mỗi giá trị của các cột Tháng 1, Tháng 2 và Tháng 3 được chuyển thành một dòng riêng với hai cột mới là Tháng và Doanh thu. Chỉ các bản ghi thỏa mãn điều kiện trong vùng F1:G2 được đưa vào kết quả, các dòng có giá trị bằng 0 được loại bỏ, đồng thời cột Source được thêm ở đầu để xác định bảng dữ liệu nguồn.
Chức năng: Tính tổng một hoặc nhiều cột giá trị trong một hoặc nhiều bảng dữ liệu khi các bản ghi thỏa mãn các điều kiện lọc theo hàng và theo cột. Hàm hỗ trợ nhiều điều kiện đồng thời, kết hợp dữ liệu từ nhiều bảng và cho phép cột cần tính tổng không tồn tại (được xem như giá trị bằng 0).
Cú pháp: =XSUMIF(SumLabel, RowFilterRng, ColFilterRng, DataTable1, [DataTable2], ...)
Trong đó:
; hoặc là vùng chứa tên cột.Các điều kiện lọc hỗ trợ các toán tử =, <>, >, <, >=, <= và ký tự đại diện *, ?. Hàm sẽ cộng dồn giá trị từ tất cả các bảng được truyền vào. Nếu cột trong SumLabel không tồn tại thì cột đó được xem có giá trị bằng 0 và không phát sinh lỗi.
Ví dụ: =XSUMIF("Doanh thu;Chi phí",F1:H2,K1:L3,Table2024,Table2025)
Kết quả trả về tổng của hai cột Doanh thu và Chi phí trong hai bảng Table2024 và Table2025 đối với các bản ghi đồng thời thỏa mãn các điều kiện trong vùng F1:H2 (lọc theo hàng) và K1:L3 (lọc theo cột). Nếu một trong hai cột cần tính tổng không có trong bảng dữ liệu thì cột đó được coi có giá trị bằng 0, còn hàm vẫn tiếp tục tính tổng cho các cột còn lại.
Chức năng: Đếm số lượng bản ghi thỏa mãn các điều kiện lọc trong một hoặc nhiều bảng dữ liệu. Hàm hỗ trợ đồng thời nhiều điều kiện lọc theo hàng và theo cột, đồng thời có thể cộng dồn kết quả từ nhiều bảng có cùng cấu trúc. Nếu truyền nhiều cột cần đếm, mỗi bản ghi thỏa mãn sẽ được đếm cho từng cột được chỉ định.
Cú pháp: =XCOUNTIF(CountLabel, RowFilterRng, ColFilterRng, DataTable1, [DataTable2], ...)
Trong đó:
; hoặc là vùng chứa tên cột.Các điều kiện lọc hỗ trợ các toán tử =, <>, >, <, >=, <= và ký tự đại diện *, ?. Hàm sẽ cộng dồn số lượng bản ghi từ tất cả các bảng được truyền vào. Nếu tên cột trong CountLabel không tồn tại thì hàm vẫn tính như một cột hợp lệ và tiếp tục đếm các bản ghi thỏa mãn điều kiện mà không phát sinh lỗi.
Ví dụ: =XCOUNTIF("Mã hàng",F1:H2,K1:L3,Table2024,Table2025)
Kết quả trả về số lượng bản ghi trong hai bảng Table2024 và Table2025 đồng thời thỏa mãn các điều kiện trong vùng F1:H2 (lọc theo hàng) và K1:L3 (lọc theo cột). Nếu truyền nhiều cột trong CountLabel, mỗi bản ghi thỏa mãn sẽ được đếm tương ứng cho từng cột và kết quả là tổng số lần đếm của tất cả các cột được chỉ định.
Chức năng: Tạo một hoặc nhiều cột tính toán mới từ dữ liệu của bảng bằng cách đánh giá các biểu thức (công thức) sử dụng trực tiếp tên cột. Hàm tự động thay thế tên cột bằng giá trị tương ứng trên từng dòng rồi tính toán bằng Evaluate, giúp xây dựng các cột tính toán mà không cần thêm cột phụ trong bảng dữ liệu.
Cú pháp: =XCalculate(DataTable, FormulaString, [SetHeaderName])
Trong đó:
; hoặc là vùng chứa các công thức. Trong công thức có thể sử dụng trực tiếp tên các cột của bảng.; hoặc là vùng chứa tên cột. Nếu bỏ qua, tiêu đề kết quả sẽ chính là nội dung của FormulaString.Một số lưu ý:
Evaluate.Ví dụ: =XCalculate(TableSales,"Số lượng*Đơn giá;Doanh thu*0.1","Doanh thu;Thuế VAT")
Kết quả trả về một bảng gồm hai cột mới là Doanh thu và Thuế VAT. Với mỗi dòng dữ liệu, hàm sẽ tính Doanh thu = Số lượng × Đơn giá và Thuế VAT = Doanh thu × 10%. Nếu ô Số lượng, Đơn giá hoặc Doanh thu bị trống hoặc chứa lỗi thì giá trị đó được xem là 0 trước khi thực hiện phép tính.
Chức năng: Tính giá trị trung bình của một hoặc nhiều cột trong một hoặc nhiều bảng dữ liệu khi các bản ghi thỏa mãn các điều kiện lọc theo hàng và theo cột. Hàm hỗ trợ nhiều điều kiện đồng thời, kết hợp dữ liệu từ nhiều bảng và tự động bỏ qua các giá trị không phải số khi tính trung bình.
Cú pháp: =XAVGIF(AverageLabel, RowFilterRng, ColFilterRng, DataTable1, [DataTable2], ...)
Trong đó:
; hoặc là vùng chứa tên cột.Các điều kiện lọc hỗ trợ các toán tử =, <>, >, <, >=, <= và ký tự đại diện *, ?. Hàm sẽ cộng dồn dữ liệu từ tất cả các bảng được truyền vào để tính một giá trị trung bình chung. Chỉ các giá trị số mới được đưa vào phép tính trung bình. Nếu tên cột trong AverageLabel không tồn tại thì cột đó được xem có giá trị bằng 0 và vẫn được tính vào số lượng phần tử. Nếu không có bản ghi nào thỏa mãn điều kiện, hàm trả về 0.
Ví dụ: =XAVGIF("Doanh thu",F1:H2,K1:L3,Table2024,Table2025)
Kết quả trả về giá trị trung bình của cột Doanh thu trong hai bảng Table2024 và Table2025 đối với các bản ghi đồng thời thỏa mãn các điều kiện trong vùng F1:H2 (lọc theo hàng) và K1:L3 (lọc theo cột). Nếu truyền nhiều cột trong AverageLabel, hàm sẽ tính trung bình trên toàn bộ các giá trị số của các cột được chỉ định sau khi đã áp dụng điều kiện lọc.
Chức năng: Trích xuất một hoặc nhiều cột dữ liệu từ bảng dựa trên tên cột thay vì số thứ tự cột. Hàm cho phép trả về nhiều cột cùng lúc, tùy chọn hiển thị hoặc ẩn dòng tiêu đề và tự động xử lý khi một cột không tồn tại trong bảng dữ liệu.
Cú pháp: =XColumn(DataTable, ColumnLabel, [HeaderName])
Trong đó:
; hoặc là vùng chứa các tên cột.1 hoặc TRUE (mặc định): Trả về dòng tiêu đề.0 hoặc FALSE: Chỉ trả về dữ liệu, không có dòng tiêu đề.Một số lưu ý:
Ví dụ: =XColumn(TableSales,"Mã hàng;Tên hàng;Đơn giá",TRUE)
Kết quả trả về ba cột Mã hàng, Tên hàng và Đơn giá từ bảng TableSales, bao gồm cả dòng tiêu đề. Nếu thay đối số cuối bằng FALSE, hàm chỉ trả về dữ liệu mà không có hàng tiêu đề. Nếu cột Đơn giá không tồn tại trong bảng, cột này trong kết quả sẽ chứa lỗi #N/A cho tất cả các dòng.
Chức năng: Kiểm tra xem một giá trị có thỏa mãn một hoặc nhiều điều kiện hay không và trả về kết quả TRUE hoặc FALSE. Hàm hỗ trợ so sánh bằng, khác, lớn hơn, nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng, đồng thời hỗ trợ ký tự đại diện (*, ?) theo cú pháp Like của VBA. Đây là hàm nền được nhiều hàm khác như TABLEX, GROUPX, PIVOTX, UNPIVOTX, XSUMIF, XCOUNTIF và XAVGIF sử dụng để xử lý điều kiện lọc.
Cú pháp: =LIKEX(Value, Criterias)
Trong đó:
;. Mỗi điều kiện có thể là:
=Giá trị<>Giá trị>Giá trị<Giá trị>=Giá trị<=Giá trị* và ? (không cần dấu =), theo quy tắc Like của VBA.Một số lưu ý:
LCase) trước khi gọi LIKEX, do đó việc so khớp thường không phân biệt hoa/thường.Ví dụ: =LIKEX("Táo","Tá*;Cam")
Kết quả trả về TRUE vì giá trị "Táo" khớp với điều kiện *"Tá"**.
Hoặc: =LIKEX(150,">100;=50")
Kết quả trả về TRUE vì 150 lớn hơn 100. Nếu giá trị là 80, hàm sẽ trả về FALSE vì không thỏa mãn bất kỳ điều kiện nào được chỉ định.
Chức năng: Hàm thực thi một biểu thức Excel được truyền vào dưới dạng chuỗi văn bản và trả về kết quả tính toán. Hàm hữu ích khi cần xây dựng công thức động hoặc tính toán từ các biểu thức được tạo trong ô hoặc bằng VBA.
Cú pháp:=VALUATE(Expr)
Trong đó:
Expr: Chuỗi biểu thức hoặc công thức Excel cần tính toán.Ví dụ:=VALUATE("2+3*5") → Trả về 17.
=VALUATE("SUM(A1:A10)") → Trả về tổng các giá trị trong vùng A1:A10.
Nếu ô A1 chứa giá trị 100 thì:=VALUATE("A1*1.1") → Trả về 110.
Lưu ý: Hàm chỉ nhận biểu thức dưới dạng chuỗi văn bản. Biểu thức phải được viết đúng theo cú pháp công thức của Excel, nếu không hàm sẽ trả về lỗi tương ứng. Hàm đặc biệt hữu ích khi cần tạo và thực thi công thức động từ dữ liệu hoặc các chuỗi được ghép trong Excel.
Chức năng: Hàm chuyển đổi nội dung của một ô chứa điều kiện lọc thành tiêu chí có thể sử dụng trong các hàm xử lý dữ liệu. Hàm hỗ trợ trả về một giá trị hoặc một mảng tiêu chí, đồng thời có thể đảo ngược điều kiện để phục vụ các trường hợp lọc loại trừ.
Cú pháp:=FilterCriteria(FilterCell, [AsArray], [Invert])
Trong đó:
FilterCell: Ô chứa điều kiện lọc.AsArray (tùy chọn): TRUE để trả về mảng các tiêu chí, FALSE (mặc định) để trả về một tiêu chí.Invert (tùy chọn): TRUE để đảo ngược điều kiện lọc, FALSE (mặc định) để giữ nguyên điều kiện.Ví dụ:=FilterCriteria(A1) → Chuyển nội dung trong ô A1 thành tiêu chí lọc.
=FilterCriteria(A1,TRUE) → Trả về toàn bộ các tiêu chí trong ô A1 dưới dạng mảng để sử dụng trong các hàm hỗ trợ lọc dữ liệu.
=FilterCriteria(A1,TRUE,TRUE) → Trả về mảng tiêu chí sau khi đảo ngược điều kiện, phù hợp với các trường hợp cần lọc bỏ các giá trị thỏa mãn điều kiện ban đầu.
Lưu ý: Hàm hỗ trợ nhiều dạng điều kiện lọc trong cùng một ô, giúp xây dựng các tiêu chí lọc linh hoạt cho các hàm truy vấn, lọc hoặc tổng hợp dữ liệu. Khi AsArray là TRUE, kết quả có thể được sử dụng trực tiếp trong các công thức xử lý mảng hoặc các hàm hỗ trợ lọc dữ liệu.
Chức năng: Hàm tìm tất cả các tổ hợp giá trị trong một vùng dữ liệu có tổng đúng bằng giá trị mục tiêu. Hàm hỗ trợ lựa chọn có hoặc không cho phép lặp giá trị và có thể sắp xếp danh sách kết quả theo yêu cầu.
Cú pháp: =FindMatchingSum(DataTable, SumTarget, [AllowDuplicates], [Sorting])
Trong đó:
DataTable: Vùng dữ liệu chứa các giá trị số cần tìm tổ hợp.SumTarget: Tổng mục tiêu cần đạt.AllowDuplicates (tùy chọn): TRUE để cho phép sử dụng các giá trị trùng lặp trong quá trình tìm kiếm, FALSE (mặc định) để loại bỏ các tổ hợp trùng.Sorting (tùy chọn): Quy định cách sắp xếp kết quả trả về. Giá trị mặc định là 0 (không sắp xếp).Ví dụ: Nếu vùng A2:A8 chứa các giá trị:
10
15
20
25
30
35
40=FindMatchingSum(A2:A8,50) → Trả về các tổ hợp có tổng bằng 50, ví dụ:
10;40
15;35
20;30
10;15;25=FindMatchingSum(A2:A8,50,TRUE) → Tìm các tổ hợp có tổng bằng 50 và cho phép xét các giá trị trùng theo thiết lập của hàm.
=FindMatchingSum(A2:A8,50,FALSE,1) → Trả về các tổ hợp và sắp xếp danh sách kết quả theo thứ tự được chỉ định.
Ứng dụng: Hàm rất hữu ích trong các bài toán phân bổ chi phí, đối chiếu chứng từ, ghép các khoản thanh toán để khớp với một số tiền xác định, tìm tổ hợp đơn hàng, phân tích dữ liệu tài chính hoặc giải các bài toán tổ hợp trong Excel.
Lưu ý: Hàm tự động bỏ qua các ô trống và các giá trị bằng 0 trong vùng dữ liệu. Mỗi kết quả được trả về dưới dạng một chuỗi, trong đó các giá trị được phân tách bằng dấu chấm phẩy (;). Nếu không tìm thấy tổ hợp phù hợp hoặc vùng dữ liệu không có giá trị hợp lệ, hàm sẽ trả về thông báo tương ứng. Kết quả là một mảng động nên cần đủ không gian để hiển thị toàn bộ danh sách các tổ hợp tìm được.
Chức năng: Hàm tính mức độ tương đồng giữa một chuỗi văn bản và từng giá trị trong một vùng dữ liệu, sau đó trả về điểm tương đồng cho từng phần tử. Hàm hỗ trợ tìm kiếm gần đúng (fuzzy matching), giúp nhận diện các chuỗi có cách viết tương tự ngay cả khi không hoàn toàn giống nhau.
Cú pháp: =LIKES(String1, StringOrRange2)
Trong đó:
String1: Chuỗi văn bản cần so sánh.StringOrRange2: Vùng dữ liệu chứa các chuỗi cần đối chiếu.Ví dụ:
Nếu vùng A2:A6 chứa:
Nguyễn Văn An
Nguyen Van An
Nguyễn Văn Anh
Trần Văn An
Lê Minh=LIKES("Nguyễn Văn An",A2:A6) → Trả về một cột các điểm tương đồng, ví dụ:
1.000
0.950
0.910
0.720
0.080Điểm càng gần 1 thì hai chuỗi càng giống nhau; điểm càng gần 0 thì mức độ tương đồng càng thấp.
Ứng dụng: Hàm rất hữu ích để tìm kiếm gần đúng tên khách hàng, tên nhân viên, tên sản phẩm, địa chỉ hoặc các dữ liệu văn bản có thể bị sai chính tả, thiếu dấu hoặc khác biệt nhỏ trong cách nhập liệu. Hàm cũng hỗ trợ đối chiếu và làm sạch dữ liệu từ nhiều nguồn khác nhau.
Lưu ý: Hàm trả về một mảng điểm tương đồng tương ứng với từng giá trị trong vùng dữ liệu. Giá trị 1 biểu thị hai chuỗi giống hệt nhau, trong khi các giá trị nhỏ hơn thể hiện mức độ tương đồng giảm dần. Kết quả có thể được kết hợp với các hàm như MAX, XMATCH, SORTBY hoặc FILTER để xác định chuỗi giống nhất hoặc lọc các kết quả vượt một ngưỡng tương đồng nhất định.
Chức năng: Hàm tìm giá trị trong một vùng dữ liệu có mức độ tương đồng cao nhất với chuỗi cần tìm. Hàm hỗ trợ tìm kiếm gần đúng (fuzzy matching) và có thể trả về giá trị phù hợp nhất, vị trí của giá trị đó hoặc điểm tương đồng.
Cú pháp:=LIKEIF(LookupValue, LookupRange, [ReturnType])
Trong đó:
LookupValue: Chuỗi cần tìm.LookupRange: Vùng dữ liệu chứa các giá trị cần so sánh.ReturnType (tùy chọn):
0 hoặc bỏ qua: Trả về giá trị giống nhất.1: Trả về vị trí của giá trị giống nhất trong vùng dữ liệu.2: Trả về điểm tương đồng cao nhất (từ 0 đến 1).Ví dụ: Nếu vùng A2:A6 chứa:
Nguyễn Văn An
Nguyen Van An
Nguyễn Văn Anh
Trần Văn An
Lê Minh=LIKEIF("Nguyễn Văn Ân",A2:A6) → Trả về:
Nguyễn Văn An=LIKEIF("Nguyễn Văn Ân",A2:A6,1) → Trả về vị trí của kết quả phù hợp nhất trong vùng, ví dụ: 1.
=LIKEIF("Nguyễn Văn Ân",A2:A6,2) → Trả về điểm tương đồng lớn nhất, ví dụ: 0,96.
Ứng dụng: Hàm rất hữu ích khi tìm kiếm tên khách hàng, nhân viên, sản phẩm hoặc địa chỉ có thể bị nhập sai chính tả, thiếu dấu hoặc khác biệt nhỏ. Hàm cũng phù hợp để chuẩn hóa dữ liệu, đối chiếu danh sách và ghép nối dữ liệu từ nhiều nguồn không hoàn toàn đồng nhất.
Lưu ý: Hàm luôn chọn giá trị có điểm tương đồng cao nhất trong vùng dữ liệu. Nếu có nhiều giá trị có cùng mức độ tương đồng cao nhất, hàm sẽ trả về giá trị xuất hiện đầu tiên. Khi ReturnType = 2, kết quả là điểm tương đồng trong khoảng từ 0 đến 1, trong đó 1 biểu thị hai chuỗi giống hệt nhau.
Chức năng: Hàm chuyển đổi văn bản được nhập theo kiểu gõ Telex thành văn bản tiếng Việt có dấu Unicode. Hàm hỗ trợ cả chữ thường và chữ in hoa, giúp chuyển đổi nhanh các chuỗi chưa được gõ bằng bộ gõ tiếng Việt.
Cú pháp: =VTEXT(Text)
Trong đó: Text: Chuỗi văn bản được nhập theo quy tắc gõ Telex.
Ví dụ:=VTEXT("Tooi ddi hojc Excel") → Tôi đi học Excel
=VTEXT("Xin chaof ca1c bajn") → Ví dụ này không hợp lệ vì Telex không dùng số.
Ví dụ đúng:
=VTEXT("Xin chaof ca1c ban") ❌=VTEXT("Xin chaof cac ban") → Xin chào các bạn=VTEXT("Tieengs Vieetj") → Tiếng Việt=VTEXT("Nguowif Vieetj Nam") → Người Việt Nam=VTEXT("DDaay laf mootj thuwr nghieemj") → Đây là một thử nghiệmỨng dụng: Hàm hữu ích khi xử lý dữ liệu được nhập theo quy tắc Telex nhưng chưa qua bộ gõ tiếng Việt, chuyển đổi dữ liệu từ các hệ thống cũ hoặc chuẩn hóa văn bản tiếng Việt trong Excel.
Lưu ý: Hàm chỉ chuyển đổi các ký tự được nhập theo đúng quy tắc Telex (ví dụ: aa → â, aw → ă, ee → ê, oo → ô, ow → ơ, uw → ư, dd → đ, s/f/r/x/j để thêm dấu thanh). Nếu chuỗi không tuân theo quy tắc Telex, kết quả có thể không đúng như mong muốn.
Chức năng:
Chuyển một giá trị số thành chuỗi chữ tiếng Việt để phục vụ hiển thị trên chứng từ, hóa đơn, phiếu thu, phiếu chi hoặc các báo cáo tài chính. Hàm tự động đọc số theo từng đơn vị như nghìn tỷ, tỷ, triệu, nghìn và đồng, đồng thời xử lý đúng các quy tắc đọc số trong tiếng Việt.
Cú pháp: =DocSo(Số_cần_đọc)
Trong đó Số_cần_đọc là giá trị số cần chuyển thành chữ.
Ví dụ:=DocSo(1250000) → Một triệu, hai trăm năm mươi nghìn
=DocSo(15025) → Mười lăm nghìn, không trăm hai mươi lăm
=DocSo(123456789) → Một trăm hai mươi ba triệu, bốn trăm năm mươi sáu nghìn, bảy trăm tám mươi chín
Ứng dụng:
Hàm thường được sử dụng để tự động chuyển số tiền thành chữ trên hóa đơn, phiếu thu, phiếu chi, hợp đồng, bảng kê thanh toán và các biểu mẫu tài chính. Việc tự động đọc số giúp giảm sai sót khi nhập liệu, tăng tính chuyên nghiệp của chứng từ và tiết kiệm thời gian so với việc nhập thủ công.
Lưu ý:
Hàm làm tròn số đến đơn vị nguyên trước khi chuyển sang chữ, vì vậy phần thập phân sẽ không được đọc. Giá trị đầu vào phải nằm trong phạm vi hỗ trợ của kiểu dữ liệu Currency; nếu vượt quá giới hạn, hàm sẽ trả về thông báo không thể đọc do số quá lớn.
Chức năng: Chuyển một số nguyên thành chữ tiếng Anh theo chuẩn đọc quốc tế. Hàm hỗ trợ đọc các đơn vị hàng trăm, hàng nghìn, hàng triệu và hàng tỷ, phù hợp để hiển thị số tiền hoặc giá trị bằng tiếng Anh trên chứng từ, báo cáo và tài liệu.
Cú pháp: =ReadNumber(Số_cần_đọc)
hoặc =m_ReadNumber(Số_cần_đọc)
Trong đó Số_cần_đọc là số nguyên cần chuyển thành chữ tiếng Anh.
Ví dụ:=ReadNumber(125) → One Hundred and Twenty Five
=ReadNumber(1250) → One Thousand Two Hundred and Fifty
=ReadNumber(1234567) → One Million Two Hundred and Thirty Four Thousand Five Hundred and Sixty Seven
=ReadNumber(1000000000) → One Billion
Ứng dụng: Hàm được sử dụng để chuyển số thành chữ tiếng Anh trên hóa đơn, hợp đồng, chứng từ thanh toán quốc tế, báo giá, biểu mẫu xuất nhập khẩu và các báo cáo cần thể hiện giá trị bằng tiếng Anh. Hàm cũng hữu ích khi xây dựng các mẫu Excel song ngữ hoặc các tài liệu dành cho đối tác nước ngoài.
Lưu ý: Hàm chỉ hỗ trợ số nguyên và sử dụng hệ đơn vị quốc tế gồm Thousand, Million và Billion. Kết quả trả về theo quy tắc đọc tiếng Anh, bao gồm từ and trong các trường hợp phù hợp. Đầu vào phải là giá trị số nguyên nằm trong phạm vi của kiểu dữ liệu Long.
Chức năng: Tra cứu giá trị trong một bảng dữ liệu hai chiều dựa trên tiêu đề hàng và tiêu đề cột. Hàm trả về giá trị tại giao điểm giữa hàng có nhãn rowLabel và cột có nhãn columnLabel, tương tự việc kết hợp tra cứu theo cả hàng và cột trong Excel.
Cú pháp: =LOOKUP2D(dataTABLE, rowLabel, columnLabel)
Trong đó:
dataTABLE: Vùng dữ liệu hai chiều, trong đó cột đầu tiên chứa nhãn hàng và hàng đầu tiên chứa nhãn cột.rowLabel: Giá trị cần tìm trong cột đầu tiên của bảng.columnLabel: Giá trị cần tìm trong hàng đầu tiên của bảng.Ví dụ: =LOOKUP2D(A1:F10,"Miền Bắc","Tháng 3")
Nếu cột A chứa tên khu vực, hàng 1 chứa tên các tháng và ô giao giữa hàng Miền Bắc với cột Tháng 3 có giá trị 1250, hàm sẽ trả về 1250.
Ứng dụng:
Hàm rất hữu ích khi làm việc với các bảng dữ liệu có cả tiêu đề hàng và tiêu đề cột như bảng giá, bảng doanh số theo khu vực và thời gian, bảng lương theo chức danh và bậc lương, bảng định mức, bảng tỷ giá hoặc các ma trận dữ liệu. Hàm giúp tra cứu trực tiếp một giá trị mà không cần kết hợp nhiều hàm như INDEX và MATCH.
Lưu ý:
dataTABLE phải chứa tiêu đề cột và cột đầu tiên phải chứa tiêu đề hàng.rowLabel và columnLabel phải khớp chính xác với tiêu đề trong bảng.Chức năng: Sinh ngẫu nhiên một số nguyên trong khoảng xác định, đồng thời loại trừ các giá trị đã tồn tại trong một hoặc nhiều vùng dữ liệu. Hàm đảm bảo kết quả không trùng với các số nằm trong danh sách loại trừ.
Cú pháp: =RandBetweenExclude(MinVal, MaxVal, [ExcludeRange1], [ExcludeRange2], ...)
Trong đó:
MinVal: Giá trị nhỏ nhất của khoảng sinh số.MaxVal: Giá trị lớn nhất của khoảng sinh số.ExcludeRange1, ExcludeRange2, ... (tùy chọn): Một hoặc nhiều vùng chứa các giá trị cần loại trừ.Ví dụ:
=RandBetweenExclude(1,100,A2:A20) → Sinh ngẫu nhiên một số từ 1 đến 100 nhưng không trùng với các số trong vùng A2:A20.=RandBetweenExclude(1000,9999,B2:B100,D2:D50) → Sinh một số ngẫu nhiên từ 1000 đến 9999, đồng thời loại trừ các số đã xuất hiện trong cả hai vùng B2:B100 và D2:D50.=RandBetweenExclude("Mã001","Mã999",A2:A100) → Hàm tự động lấy phần số của các giá trị để sinh số ngẫu nhiên và loại trừ các số đã có trong vùng A2:A100.Ứng dụng: Hàm phù hợp để tạo mã số, số thứ tự, số phiếu, mã khách hàng, mã đơn hàng hoặc các giá trị nhận dạng duy nhất mà không bị trùng với dữ liệu đã tồn tại. Có thể sử dụng trong các hệ thống quản lý, biểu mẫu nhập liệu hoặc quá trình sinh dữ liệu kiểm thử.
Lưu ý: Hàm chỉ sinh số nguyên trong khoảng từ MinVal đến MaxVal (bao gồm cả hai giá trị biên). Các ký tự không phải số trong MinVal, MaxVal và các vùng loại trừ sẽ được tự động bỏ qua trước khi xử lý. Có thể truyền nhiều vùng loại trừ cùng lúc. Nếu toàn bộ các số trong khoảng đã bị loại trừ hoặc khoảng giá trị không hợp lệ (MinVal lớn hơn hoặc bằng MaxVal), hàm sẽ trả về lỗi.
Chức năng:
Nối các giá trị trong một vùng dữ liệu thành một chuỗi theo một hoặc nhiều điều kiện. Hàm hoạt động tương tự sự kết hợp giữa TEXTJOIN và SUMIFS, cho phép lọc dữ liệu trước khi ghép các giá trị.
Cú pháp:=JOINIFS(ResultRange, SplitChar, CriteriaRange1, Criteria1, [CriteriaRange2], [Criteria2], ...)
Trong đó:
ResultRange: Vùng chứa các giá trị cần nối.SplitChar: Ký tự hoặc chuỗi dùng để phân tách các giá trị trong kết quả.CriteriaRange1, CriteriaRange2, ...: Các vùng điều kiện.Criteria1, Criteria2, ...: Điều kiện tương ứng với từng vùng điều kiện. Có thể là giá trị, biểu thức so sánh (ví dụ ">100"), hoặc ký tự đại diện như * và ? đối với dữ liệu văn bản.Ví dụ:
=JOINIFS(B2:B100,", ",A2:A100,"Hà Nội") → Nối tất cả giá trị trong cột B có cột A bằng Hà Nội.=JOINIFS(C2:C100,"; ",A2:A100,"Miền Bắc",B2:B100,">100") → Nối các giá trị ở cột C khi cột A bằng Miền Bắc và cột B lớn hơn 100.=JOINIFS(A2:A100,", ",B2:B100,"A*",C2:C100,"<>0") → Nối các giá trị ở cột A khi cột B bắt đầu bằng chữ A và cột C khác 0.Ứng dụng: Hàm hữu ích để tạo danh sách khách hàng, sản phẩm, nhân viên, mã hàng hoặc bất kỳ dữ liệu nào thỏa mãn nhiều điều kiện trong một ô duy nhất. Có thể sử dụng để tạo báo cáo, danh sách phân loại, mô tả tổng hợp hoặc chuẩn bị dữ liệu trước khi xuất sang các hệ thống khác.
Lưu ý: Hàm hỗ trợ nhiều cặp vùng điều kiện – điều kiện giống như các hàm SUMIFS hoặc COUNTIFS. Với dữ liệu số, có thể sử dụng các toán tử so sánh như >, <, >=, <=, <>. Với dữ liệu văn bản, hàm hỗ trợ ký tự đại diện * (nhiều ký tự) và ? (một ký tự). Các ô trống trong ResultRange sẽ tự động được bỏ qua và không tạo ký tự phân tách thừa trong kết quả. Mọi vùng điều kiện phải có cùng số dòng với ResultRange.
Chức năng: Thay thế nhiều chuỗi trong một lần trên một giá trị hoặc toàn bộ vùng dữ liệu. Hàm thực hiện lần lượt các cặp chuỗi cần tìm và chuỗi thay thế, đồng thời không phân biệt chữ hoa và chữ thường.
Cú pháp: =SUBSTITUTES(Text, FindText1, ReplaceText1, [FindText2], [ReplaceText2], ...)
Trong đó:
Text: Văn bản hoặc vùng dữ liệu cần thay thế.FindText1, FindText2, ...: Chuỗi cần tìm.ReplaceText1, ReplaceText2, ...: Chuỗi thay thế tương ứng.Ví dụ:
=SUBSTITUTES(A2,"Công ty","CTY") → Thay thế tất cả chuỗi "Công ty" thành "CTY" trong ô A2.=SUBSTITUTES(A2,"Quận","Q.","Phường","P.") → Thực hiện hai phép thay thế liên tiếp trên cùng một văn bản.=SUBSTITUTES(A2:C10,"-","/"," ","") → Thay thế dấu gạch ngang bằng dấu gạch chéo và đồng thời loại bỏ khoảng trắng trong toàn bộ vùng A2:C10.=SUBSTITUTES("Excel VBA","excel","Microsoft Excel") → Trả về "Microsoft Excel VBA", không phân biệt chữ hoa và chữ thường.Ứng dụng:Hàm thích hợp để chuẩn hóa dữ liệu văn bản, đổi tên, chuẩn hóa địa chỉ, mã hàng, tên sản phẩm hoặc làm sạch dữ liệu trước khi phân tích. Đặc biệt hữu ích khi cần thực hiện nhiều phép thay thế cùng lúc mà không phải lồng nhiều hàm SUBSTITUTE.
Lưu ý: Hàm hỗ trợ cả một giá trị và một mảng/vùng dữ liệu, trả về kết quả có cùng kích thước với dữ liệu đầu vào. Các phép thay thế được thực hiện theo đúng thứ tự các cặp tham số được truyền vào, vì vậy kết quả của phép thay thế trước có thể ảnh hưởng đến các phép thay thế sau. Việc tìm kiếm chuỗi không phân biệt chữ hoa và chữ thường. Số lượng tham số thay thế phải theo từng cặp gồm chuỗi cần tìm và chuỗi thay thế.
Substitutes2
Chức năng: Thay thế nhiều chuỗi trong một lần trên một giá trị hoặc toàn bộ vùng dữ liệu, đồng thời hỗ trợ ký tự đại diện (* và ?) để tìm kiếm theo mẫu. Hàm cho phép xử lý linh hoạt các chuỗi có cấu trúc tương tự mà không cần chỉ định chính xác toàn bộ nội dung.
Cú pháp: =SUBSTITUTES2(Text, Pattern1, ReplaceText1, [Pattern2], [ReplaceText2], ...)
Trong đó:
Text: Văn bản hoặc vùng dữ liệu cần thay thế.Pattern1, Pattern2, ...: Chuỗi hoặc mẫu tìm kiếm. Có thể sử dụng ký tự đại diện:
* đại diện cho một hoặc nhiều ký tự bất kỳ.? đại diện cho đúng một ký tự bất kỳ.ReplaceText1, ReplaceText2, ...: Chuỗi thay thế tương ứng.Ví dụ:
=SUBSTITUTES2(A2,"Công ty*","CTY") → Thay thế mọi chuỗi bắt đầu bằng "Công ty" thành "CTY".=SUBSTITUTES2(A2,"SP????","Sản phẩm") → Thay thế các mã gồm SP theo sau bởi đúng 4 ký tự thành "Sản phẩm".=SUBSTITUTES2(A2:C10,"*-OLD","", " *"," ") → Loại bỏ phần đuôi -OLD và đồng thời chuẩn hóa khoảng trắng trong toàn bộ vùng dữ liệu.=SUBSTITUTES2(A2,"HN*","Hà Nội","TP*","Thành phố") → Thực hiện nhiều phép thay thế theo mẫu trong một lần gọi hàm.Ứng dụng: Hàm phù hợp để chuẩn hóa dữ liệu văn bản có nhiều biến thể, xử lý mã hàng, tên sản phẩm, địa chỉ hoặc các chuỗi có định dạng không cố định. Đây là công cụ hữu ích khi cần thay thế theo mẫu thay vì theo chuỗi chính xác.
Lưu ý: Hàm hỗ trợ cả một giá trị và một mảng/vùng dữ liệu, trả về kết quả có cùng kích thước với dữ liệu đầu vào. Việc tìm kiếm không phân biệt chữ hoa và chữ thường. Khác với SUBSTITUTES, hàm này cho phép sử dụng ký tự đại diện * và ? trong chuỗi tìm kiếm để thay thế theo mẫu. Các phép thay thế được thực hiện tuần tự theo thứ tự các cặp tham số, do đó kết quả của phép thay thế trước có thể ảnh hưởng đến các phép thay thế sau. Số lượng tham số thay thế phải được truyền theo từng cặp gồm mẫu tìm kiếm và chuỗi thay thế.
Hai hàm đều có mục đích thay thế nhiều chuỗi trong một lần gọi hàm, hỗ trợ xử lý trên một ô hoặc cả một vùng dữ liệu, không phân biệt chữ hoa và chữ thường, đồng thời cho phép truyền nhiều cặp chuỗi tìm kiếm → chuỗi thay thế. Điểm khác biệt chủ yếu nằm ở cách tìm kiếm chuỗi cần thay thế.
SUBSTITUTES
Replace của VBA nên tốc độ xử lý rất nhanh.Ví dụ =SUBSTITUTES(A2,"Công ty","CTY","Việt Nam","VN")
Kết quả: "Công ty ABC Việt Nam" → "CTY ABC VN"
SUBSTITUTES2
* : đại diện cho một hoặc nhiều ký tự.? : đại diện cho đúng một ký tự.Ví dụ: =SUBSTITUTES2(A2,"Công ty*","CTY")
Kết quả:
đều được thay thành:
So sánh tính năng
| Tiêu chí | SUBSTITUTES | SUBSTITUTES2 |
|---|---|---|
| Thay thế nhiều chuỗi | ✔ | ✔ |
| Hỗ trợ mảng/vùng dữ liệu | ✔ | ✔ |
| Không phân biệt chữ hoa/thường | ✔ | ✔ |
| Thay theo chuỗi chính xác | ✔ | ✔ |
Hỗ trợ ký tự đại diện * và ? |
✘ | ✔ |
| Thay theo mẫu (Pattern) | ✘ | ✔ |
| Tốc độ xử lý | Nhanh hơn | Chậm hơn một chút |
| Phù hợp với dữ liệu có nhiều biến thể | Hạn chế | Rất tốt |
Khi nào nên dùng?
Chức năng: Thay thế một chuỗi bằng nhiều giá trị khác nhau và trả về tất cả các kết quả trong cùng một chuỗi. Hàm tự động tạo nhiều phiên bản của văn bản gốc tương ứng với từng giá trị thay thế, rất hữu ích khi cần sinh hàng loạt biến thể từ một mẫu duy nhất.
Cú pháp: =SUBSTITUTEX(text_string, old_text, new_text, [case_sensitive])
Trong đó:
text_string: Chuỗi văn bản gốc.old_text: Chuỗi cần thay thế.new_text: Danh sách các giá trị thay thế, phân cách bằng dấu chấm phẩy (;).case_sensitive (tùy chọn, mặc định = FALSE): Nếu TRUE, việc tìm kiếm phân biệt chữ hoa và chữ thường; nếu FALSE, không phân biệt.Ví dụ:
=SUBSTITUTEX("Màu đỏ","đỏ","xanh;vàng;đen") → Trả về: "Màu xanh;Màu vàng;Màu đen".=SUBSTITUTEX("SP001","001","101;102;103") → Trả về: "SP101;SP102;SP103".=SUBSTITUTEX("Excel VBA","VBA","Lambda;Office Script",TRUE) → Trả về: "Excel Lambda;Excel Office Script" nếu chuỗi VBA được tìm thấy đúng theo chữ hoa và chữ thường.Ứng dụng: Hàm hữu ích để tạo nhiều biến thể của tên sản phẩm, mã hàng, câu lệnh, nội dung quảng cáo hoặc bất kỳ chuỗi văn bản nào từ một mẫu chung. Có thể sử dụng để sinh dữ liệu thử nghiệm, tạo danh sách từ khóa hoặc tự động tạo nhiều nội dung có cấu trúc tương tự.
Lưu ý: Khác với SUBSTITUTES và SUBSTITUTES2, hàm này không thực hiện nhiều phép thay thế trên cùng một chuỗi, mà thực hiện một phép thay thế với nhiều giá trị thay thế khác nhau và ghép tất cả kết quả thành một chuỗi, ngăn cách bằng dấu chấm phẩy (;). Tham số new_text phải chứa các giá trị thay thế được phân tách bằng dấu chấm phẩy. Có thể lựa chọn tìm kiếm có hoặc không phân biệt chữ hoa và chữ thường thông qua tham số case_sensitive.