Lọc dữ liệu tương tự hàm FILTER của Excel, nhưng hỗ trợ thêm wildcard trong điều kiện lọc.Hàm phù hợp khi cần lọc dữ liệu theo các điều kiện dạng chứa, bắt đầu bằng, kết thúc bằng hoặc khớp mẫu ký tự.
Ví dụ: "*Excel*"
"HN*"
"*2026"
"A?01"FILTERS hỗ trợ tối đa 5 cặp điều kiện. Các cặp điều kiện khác nhau được kết hợp theo logic AND.
=LAMBDA(array, by_array1, criteria1, [if_empty], [by_array2], [criteria2], [by_array3], [criteria3], [by_array4], [criteria4], [by_array5], [criteria5],
LET(
MatchWild, LAMBDA(rng, crit,
MAP(rng, LAMBDA(x, --(SUM(COUNTIF(x, crit))>0)))
), Include1, MatchWild(by_array1, criteria1),
Include2, IF(ISOMITTED(by_array2), 1, MatchWild(by_array2, criteria2)),
Include3, IF(ISOMITTED(by_array3), 1, MatchWild(by_array3, criteria3)),
Include4, IF(ISOMITTED(by_array4), 1, MatchWild(by_array4, criteria4)),
Include5, IF(ISOMITTED(by_array5), 1, MatchWild(by_array5, criteria5)), FILTER(
array,
Include1 * Include2 * Include3 * Include4 * Include5,
IF(ISOMITTED(if_empty), "", if_empty)
)
)
)
=LAMBDA(
array,
by_array1,criteria1,
[if_empty],
[by_array2],[criteria2],
[by_array3],[criteria3],
[by_array4],[criteria4],
[by_array5],[criteria5],
[by_array6],[criteria6],
[by_array7],[criteria7],
[by_array8],[criteria8],
[by_array9],[criteria9],
[by_array10],[criteria10],
LET(
MatchWild,
LAMBDA(rng,crit,
MAP(rng,LAMBDA(x,--(SUM(COUNTIF(x,crit))>0)))
),
Include1, MatchWild(by_array1,criteria1),
Include2, IF(ISOMITTED(by_array2),1,MatchWild(by_array2,criteria2)),
Include3, IF(ISOMITTED(by_array3),1,MatchWild(by_array3,criteria3)),
Include4, IF(ISOMITTED(by_array4),1,MatchWild(by_array4,criteria4)),
Include5, IF(ISOMITTED(by_array5),1,MatchWild(by_array5,criteria5)),
Include6, IF(ISOMITTED(by_array6),1,MatchWild(by_array6,criteria6)),
Include7, IF(ISOMITTED(by_array7),1,MatchWild(by_array7,criteria7)),
Include8, IF(ISOMITTED(by_array8),1,MatchWild(by_array8,criteria8)),
Include9, IF(ISOMITTED(by_array9),1,MatchWild(by_array9,criteria9)),
Include10, IF(ISOMITTED(by_array10),1,MatchWild(by_array10,criteria10)),
FILTER(
array,
Include1*
Include2*
Include3*
Include4*
Include5*
Include6*
Include7*
Include8*
Include9*
Include10,
IF(ISOMITTED(if_empty),"",if_empty)
)
)
)
Cú pháp=FILTERS(array, by_array1, criteria1, [if_empty], [by_array2], [criteria2], [by_array3], [criteria3], [by_array4], [criteria4], [by_array5], [criteria5])Tham Số
| Tham số | Bắt buộc | Mô tả |
|---|---|---|
| array | Có | Vùng dữ liệu cần lọc |
| by_array1 | Có | Vùng điều kiện thứ nhất |
| criteria1 | Có | Điều kiện lọc thứ nhất |
| [if_empty] | Không | Giá trị trả về nếu không có kết quả |
| [by_array2,3,4,5] | Không | Vùng điều kiện thứ hai, ba, bốn, năm |
| [criteria2,3,4,5] | Không | Điều kiện lọc thứ hai, ba, bốn, năm |
Wildcard Hỗ Trợ
| Ký tự | Ý nghĩa | Ví dụ |
|---|---|---|
| * | Đại diện cho chuỗi ký tự bất kỳ | "*Excel*" |
| ? | Đại diện cho một ký tự bất kỳ | "A?01" |
| ~* | Tìm ký tự * thật | "A~*" |
| ~? | Tìm ký tự ? thật | "A~?" |
Ví dụ
Lọc cột B chứa chữ Excel:=FILTERS(A2:D100, B2:B100, "*Excel*")Lọc cột C bắt đầu bằng HN:=FILTERS(A2:D100, C2:C100, "HN*")Lọc cột B chứa Excel, nếu không có kết quả thì trả về thông báo:=FILTERS(A2:D100, B2:B100, "*Excel*", "Không có kết quả")Lọc theo 2 điều kiện:=FILTERS(A2:D100, B2:B100, "*Excel*", "Không có kết quả", C2:C100, "HN*")Nghĩa là:Cột B chứa Excel
AND cột C bắt đầu bằng HNLọc theo 3 điều kiện:=FILTERS(A2:D100, B2:B100, "*Excel*", "Không có kết quả", C2:C100, "HN*", D2:D100, "Active")Lọc theo nhiều giá trị OR trong cùng một điều kiện:=FILTERS(A2:D100, B2:B100, {"*Excel*","*Power BI*"}, "Không có kết quả")Nghĩa là cột B chứa Excel hoặc Power BI.Kết hợp OR và AND:=FILTERS(A2:D100, B2:B100, {"*Excel*","*Power BI*"}, "Không có kết quả", C2:C100, "HN*")Nghĩa là:Cột B chứa Excel hoặc Power BI
AND cột C bắt đầu bằng HN
Lưu ý
Các vùng by_array phải có cùng số dòng hoặc cùng số cột với array, giống nguyên tắc của hàm FILTER.Ví dụ hợp lệ:
=FILTERS(A2:D100, B2:B100, "*Excel*")Ví dụ dễ lỗi do vùng điều kiện không cùng kích thước:=FILTERS(A2:D100, B2:B90, "*Excel*")Các cặp điều kiện khác nhau được kết hợp theo logic AND.Nhiều điều kiện trong cùng một criteria, ví dụ {"*Excel*","*Power BI*"}, được hiểu là logic OR.
Nhóm dữ liệu theo tên cột, thay vì phải chọn trực tiếp vùng cột như hàm GROUPBY gốc.
=LAMBDA(data,row_field_labels,value_field_labels,agg_fn,[total_depth],[sort_order],[filter_array],[field_relationship],
LET(
headers,TAKE(data,1),
body,DROP(data,1),
row_ids,XMATCH(TOROW(row_field_labels),headers,0),
val_ids,XMATCH(TOROW(value_field_labels),headers,0),
row_fields,VSTACK(CHOOSECOLS(headers,row_ids),CHOOSECOLS(body,row_ids)),
values,VSTACK(CHOOSECOLS(headers,val_ids),CHOOSECOLS(body,val_ids)),
IF(ISOMITTED(total_depth),
GROUPBY(row_fields,values,agg_fn,3),
IF(ISOMITTED(sort_order),
GROUPBY(row_fields,values,agg_fn,3,total_depth),
IF(ISOMITTED(filter_array),
IF(ISOMITTED(field_relationship),
GROUPBY(row_fields,values,agg_fn,3,total_depth,sort_order),
GROUPBY(row_fields,values,agg_fn,3,total_depth,sort_order,,field_relationship)
),
IF(ISOMITTED(field_relationship),
GROUPBY(row_fields,values,agg_fn,3,total_depth,sort_order,VSTACK(TRUE,filter_array)),
GROUPBY(row_fields,values,agg_fn,3,total_depth,sort_order,VSTACK(TRUE,filter_array),field_relationship)
)
)
)
)
)
)
Cú pháp:
=GROUPBYS(data,row_field_labels,value_field_labels,agg_fn,[total_depth],[sort_order],[filter_array],[field_relationship])
Trong đó:
| Tham số | Ý nghĩa |
|---|---|
| data | Vùng dữ liệu bao gồm cả dòng tiêu đề |
| row_field_labels | Tên cột dùng để nhóm dòng |
| value_field_labels | Tên cột chứa giá trị cần tổng hợp |
| agg_fn | Hàm tổng hợp như SUM, COUNT, AVERAGE, MAX, MIN |
| [total_depth] | Tùy chọn hiển thị dòng tổng |
| [sort_order] | Tùy chọn sắp xếp |
| [filter_array] | Mảng điều kiện lọc |
| [field_relationship] | Cách xử lý quan hệ giữa nhiều trường nhóm |
Ví dụ dữ liệu:
| Region | Product | Year | Sales | Profit |
|---|---|---|---|---|
| North | A | 2024 | 100 | 20 |
| North | B | 2024 | 150 | 30 |
| South | A | 2024 | 200 | 50 |
Nhóm theo Region, tính tổng Sales: =GROUPBYS(A1:E4,"Region","Sales",SUM)
Kết quả:
| Region | Sum of Sales |
|---|---|
| North | 250 |
| South | 200 |
| Grand Total | 450 |
Nhóm theo nhiều trường: =GROUPBYS(A1:E4,{"Region","Product"},"Sales",SUM)
Tổng hợp nhiều cột giá trị: =GROUPBYS(A1:E4,"Region",{"Sales","Profit"},SUM)
Dùng nhiều phép tính cho cùng một cột: =GROUPBYS(A1:E4,"Region","Sales",HSTACK(SUM,AVERAGE,COUNT))
Lọc dữ liệu trước khi nhóm, ví dụ chỉ lấy năm 2024: =GROUPBYS(A1:E100,"Region","Sales",SUM,,,(C2:C100=2024))
Lưu ý: filter_array chỉ áp dụng cho phần dữ liệu, không bao gồm dòng tiêu đề. Vì vậy nếu data là A1:E100, điều kiện lọc nên là C2:C100=2024.
Nếu dùng Excel với dấu phân cách tham số là dấu chấm phẩy, hãy viết: =GROUPBYS(A1:E4;"Region";"Sales";SUM)
Nhóm dữ liệu theo tên cột, thay vì phải chọn trực tiếp vùng cột như hàm GROUPBY gốc.
=LAMBDA(data,row_field_labels,col_field_labels,value_field_labels,agg_fn,[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to],
LET(
headers,TAKE(data,1),
body,DROP(data,1),
row_ids,XMATCH(TOROW(row_field_labels),headers,0),
col_ids,XMATCH(TOROW(col_field_labels),headers,0),
val_ids,XMATCH(TOROW(value_field_labels),headers,0),
row_fields,VSTACK(CHOOSECOLS(headers,row_ids),CHOOSECOLS(body,row_ids)),
col_fields,VSTACK(CHOOSECOLS(headers,col_ids),CHOOSECOLS(body,col_ids)),
values,VSTACK(CHOOSECOLS(headers,val_ids),CHOOSECOLS(body,val_ids)),
IF(ISOMITTED(row_total_depth),
PIVOTBY(row_fields,col_fields,values,agg_fn,3),
IF(ISOMITTED(row_sort_order),
PIVOTBY(row_fields,col_fields,values,agg_fn,3,row_total_depth),
IF(ISOMITTED(col_total_depth),
PIVOTBY(row_fields,col_fields,values,agg_fn,3,row_total_depth,row_sort_order),
IF(ISOMITTED(col_sort_order),
PIVOTBY(row_fields,col_fields,values,agg_fn,3,row_total_depth,row_sort_order,col_total_depth),
IF(ISOMITTED(filter_array),
IF(ISOMITTED(relative_to),
PIVOTBY(row_fields,col_fields,values,agg_fn,3,row_total_depth,row_sort_order,col_total_depth,col_sort_order),
PIVOTBY(row_fields,col_fields,values,agg_fn,3,row_total_depth,row_sort_order,col_total_depth,col_sort_order,,relative_to)
),
IF(ISOMITTED(relative_to),
PIVOTBY(row_fields,col_fields,values,agg_fn,3,row_total_depth,row_sort_order,col_total_depth,col_sort_order,VSTACK(TRUE,filter_array)),
PIVOTBY(row_fields,col_fields,values,agg_fn,3,row_total_depth,row_sort_order,col_total_depth,col_sort_order,VSTACK(TRUE,filter_array),relative_to)
)
)
)
)
)
)
)
)
Cú pháp:
=PIVOTBYS(data,row_field_labels,col_field_labels,value_field_labels,agg_fn,[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to])
Trong đó:
| Tham số | Ý nghĩa |
|---|---|
| data | Vùng dữ liệu bao gồm cả dòng tiêu đề |
| row_field_labels | Tên cột dùng làm nhóm dòng |
| col_field_labels | Tên cột dùng làm nhóm cột |
| value_field_labels | Tên cột chứa giá trị cần tổng hợp |
| agg_fn | Hàm tổng hợp như SUM, COUNT, AVERAGE, MAX, MIN |
| [row_total_depth] | Tùy chọn hiển thị tổng theo dòng |
| [row_sort_order] | Tùy chọn sắp xếp nhóm dòng |
| [col_total_depth] | Tùy chọn hiển thị tổng theo cột |
| [col_sort_order] | Tùy chọn sắp xếp nhóm cột |
| [filter_array] | Mảng điều kiện lọc dữ liệu |
| [relative_to] | Tùy chọn tính toán tương đối, ví dụ phần trăm so với tổng |
Ví dụ dữ liệu:
| Region | Product | Year | Sales | Profit |
|---|---|---|---|---|
| North | A | 2024 | 100 | 20 |
| North | B | 2024 | 150 | 30 |
| South | A | 2024 | 200 | 50 |
| South | B | 2025 | 300 | 70 |
Tạo bảng tổng doanh số theo Region và Year: =PIVOTBYS(A1:E5,"Region","Year","Sales",SUM)
Kết quả dạng:
| Region | 2024 | 2025 | Grand Total |
|---|---|---|---|
| North | 250 | 250 | |
| South | 200 | 300 | 500 |
| Grand Total | 450 | 300 | 750 |
Nhóm nhiều trường theo dòng: =PIVOTBYS(A1:E5,{"Region","Product"},"Year","Sales",SUM)
Nhóm nhiều trường theo cột: =PIVOTBYS(A1:E5,"Region",{"Year","Product"},"Sales",SUM)
Tổng hợp nhiều cột giá trị: =PIVOTBYS(A1:E5,"Region","Year",{"Sales","Profit"},SUM)
Dùng nhiều phép tính: =PIVOTBYS(A1:E5,"Region","Year","Sales",HSTACK(SUM,AVERAGE,COUNT))
Lọc dữ liệu trước khi tạo bảng, ví dụ chỉ lấy Product = "A": =PIVOTBYS(A1:E100,"Region","Year","Sales",SUM,,,,,(B2:B100="A"))
Lưu ý: filter_array chỉ áp dụng cho phần dữ liệu, không bao gồm dòng tiêu đề. Nếu data là A1:E100, điều kiện lọc nên bắt đầu từ dòng 2, ví dụ B2:B100="A".
Nếu Excel của bạn dùng dấu chấm phẩy làm dấu phân cách tham số: =PIVOTBYS(A1:E5;"Region";"Year";"Sales";SUM)
=LAMBDA(ThuNhapTinhThue,SoThang,
LET(t,ThuNhapTinhThue/SoThang,
thue_thang,
IF(t<=5000000,t*5%,
IF(t<=10000000,250000+(t-5000000)*10%,
IF(t<=18000000,750000+(t-10000000)*15%,
IF(t<=32000000,1950000+(t-18000000)*20%,
IF(t<=52000000,4750000+(t-32000000)*25%,
IF(t<=80000000,9750000+(t-52000000)*30%,
18150000+(t-80000000)*35%)))))),
thue_thang*SoThang
))
Giới Thiệu Chức Năng
Hàm này dùng để tính số thuế thu nhập cá nhân phải nộp dựa trên thu nhập tính thuế và số tháng tính thuế.
Công thức sẽ tự động quy đổi thu nhập tính thuế về mức thu nhập bình quân theo tháng, sau đó áp dụng biểu thuế lũy tiến từng phần theo các bậc sau:
| Thu nhập tính thuế/tháng | Thuế suất |
|---|---|
| Đến 10.000.000 | 5% |
| Trên 10.000.000 đến 30.000.000 | 10% |
| Trên 30.000.000 đến 60.000.000 | 20% |
| Trên 60.000.000 đến 100.000.000 | 30% |
| Trên 100.000.000 | 35% |
Kết quả cuối cùng là tổng số thuế phải nộp
=LAMBDA(ThuNhapTinhThue,SoThang,
LET(t,ThuNhapTinhThue/SoThang,
thue_thang,
IF(t<=10000000,t*5%,
IF(t<=30000000,500000+(t-10000000)*10%,
IF(t<=60000000,2500000+(t-30000000)*20%,
IF(t<=100000000,8500000+(t-60000000)*30%,
20500000+(t-100000000)*35%)))),
thue_thang*SoThang
))
Cú Pháp Sử Dụng
=TênHàm(ThuNhapTinhThue, SoThang)
Trong đó:
Ví dụ nếu đặt tên hàm là TINHTHUE_TNCN, có thể sử dụng như sau: =TINHTHUE_TNCN(120000000,12)
Hàm sẽ lấy: 120000000 / 12 = 10000000
Thu nhập tính thuế bình quân mỗi tháng là 10.000.000, thuộc bậc thuế 5%.
Thuế mỗi tháng: 10.000.000 x 5% = 500.000
Tổng thuế cho 12 tháng: 500.000 x 12 = 6.000.000
Lưu Ý Khi Sử Dụng