Hàm LET trong Excel 365

Blue Softs Liên hệ QC
Microsoft 365 vừa cho sử dụng chính thức hàm LET trong Excel. Hàm này rất hay và ứng dụng rất nhiều, hôm nay tôi mới thử viết vài hàm ứng dụng:
So với Pivot table:
- Có thể truyền tham số trên bảng tính
- Thay đổi tham số, thêm bớt dữ liệu không cần refresh
- Công thức dạng mảng 365: chỉ gõ công thức trong 1 ô, kết quả trả về 1 mảng (SPILL), sửa xoá công thức chỉ cần thực hiện cho 1 ô đầu tiên.
- Kết quả là hẳn 1 báo cáo chuẩn mực

File đính kèm
- Có 1 báo cáo đơn giản: Lấy danh sách mặt hàng duy nhất, tính tổng doanh thu:
G2 =LET(dm,SORT(UNIQUE(Sales[[Mặt hàng]:[Nhóm]]),{2,1}),mh,Sales[Mặt hàng],tt,Sales[Thành tiền],dt,SUMIF(mh,INDEX(dm,,1),tt),CHOOSE({1,2,3},dm,dm,dt))

1616672829502.png

- Báo cáo có 2 tham số: lấy mặt hàng có doanh thu lớn hơn ô L1, của nhóm L2

M2 =LET(dm,SORT(UNIQUE(Sales[[Mặt hàng]:[Nhóm]]),{2,1}),mh,Sales[Mặt hàng],tt,Sales[Thành tiền],dt,SUMIF(mh,INDEX(dm,,1),tt),FILTER(CHOOSE({1,2},dm,dt),(dt>L1)*(INDEX(dm,,2)=L2),"Nothing"))

1616672975199.png
Trái cây lớn hơn 5 triệu

1616673209427.png
Giải khát lớn hơn 5 triệu

1616673241495.png
Giải khát lớn hơn 10 triệu

1616673435971.png
Giải khát lớn hơn 15 triệu
 

File đính kèm

  • Let Function.xlsx
    15 KB · Đọc: 69
Đang làm dở thì thấy @befaint cười haha, là sao?

Mã:
=LET(dmm,DMuc[Mã],dmt,DMuc[Tên],dmdv,DMuc[Đvt],stt,SEQUENCE(ROWS(dmm)),MaSdu,SDu[Mã],SlDu,SDu[Số lượng],TtDu,SDu[Thành tiền],
SLDau,SUMIF(MaSdu,dmm,SlDu),ttdau,SUMIF(MaSdu,dmm,TtDu),preps,Data!J3#,presln,SUMIFS(INDEX(preps,,2),INDEX(preps,,1),dmm,INDEX(preps,,4),"N"),
preslx,SUMIFS(INDEX(preps,,2),INDEX(preps,,1),dmm,INDEX(preps,,4),"X"),prettn,SUMIFS(INDEX(preps,,5),INDEX(preps,,1),dmm,INDEX(preps,,4),"N"),
prettx,SUMIFS(INDEX(preps,,5),INDEX(preps,,1),dmm,INDEX(preps,,4),"X"),
curps,Data!P3#,cursln,SUMIFS(INDEX(curps,,2),INDEX(curps,,1),dmm,INDEX(curps,,4),"N"),curslx,SUMIFS(INDEX(curps,,2),INDEX(curps,,1),dmm,INDEX(curps,,4),"X"),
curttn,SUMIFS(INDEX(curps,,5),INDEX(curps,,1),dmm,INDEX(curps,,4),"N"),curttx,SUMIFS(INDEX(curps,,5),INDEX(curps,,1),dmm,INDEX(curps,,4),"X"),
CHOOSE(SEQUENCE(1,12),stt,dmm,dmt,dmdv,SLDau+presln-preslx,ttdau+prettn-prettx,cursln,curttn,curslx,curttx,SLDau+presln-preslx+cursln-curslx,ttdau+prettn-prettx+curttn-curttx))
 
Code cho nguyên trang báo cáo nhập xuất tồn 12 cột từ ngày C1 đến ngày C2 bao gồm cả cột STT (chưa chính xác lắm). Công thức hàm Let đặt duy nhất tại 1 ô A9
Kết quả có loại trừ mặt hàng không tồn cũng không nhập xuất
Dữ liệu 57 ngàn dòng, khi sửa ngày C1 & C2 tự cập nhật chưa kịp chớp mắt

1616774400767.png

PHP:
=LET(dmm,DMuc[Mã],dmt,DMuc[Tên],dmdv,DMuc[Đvt],MaSdu,SDu[Mã],SlDu,SDu[Số lượng],TtDu,SDu[Thành tiền],
SLDau,SUMIF(MaSdu,dmm,SlDu),ttdau,SUMIF(MaSdu,dmm,TtDu),preps,Data!$J$3#,presln,SUMIFS(INDEX(preps,,2),INDEX(preps,,1),
dmm,INDEX(preps,,4),"N"),preslx,SUMIFS(INDEX(preps,,2),INDEX(preps,,1),dmm,INDEX(preps,,4),"X"),
prettn,SUMIFS(INDEX(preps,,5),INDEX(preps,,1),dmm,INDEX(preps,,4),"N"),prettx,SUMIFS(INDEX(preps,,5),INDEX(preps,,1),dmm,INDEX(preps,,4),"X"),
curps,Data!$P$3#,cursln,SUMIFS(INDEX(curps,,2),INDEX(curps,,1),dmm,INDEX(curps,,4),"N"),curslx,SUMIFS(INDEX(curps,,2),INDEX(curps,,1),dmm,INDEX(curps,,4),"X"),
curttn,SUMIFS(INDEX(curps,,5),INDEX(curps,,1),dmm,INDEX(curps,,4),"N"),curttx,SUMIFS(INDEX(curps,,5),INDEX(curps,,1),dmm,INDEX(curps,,4),"X"),
mhps,FILTER(dmm,(SLDau+presln-preslx+cursln+curslx)>0),stt,SEQUENCE(ROWS(mhps)),
FILTER(CHOOSE(SEQUENCE(1,12),stt,dmm,dmt,dmdv,SLDau+presln-preslx,ttdau+prettn-prettx,cursln,curttn,curslx,curttx,SLDau+presln-preslx+
cursln-curslx,ttdau+prettn-prettx+curttn-curttx),(SLDau+presln-preslx+cursln+curslx)>0))
Dữ liệu gồm 1 bảng danh mục, 1 bảng số dư đầu năm, 1 bảng dữ liệu nhập xuất cả năm (2013)
1616774578745.png
1616774792700.png

Sử dụng 2 bảng trung gian: dữ liệu trước kỳ báo cáo (để tính số dư đầu kỳ BC) và dữ liệu trong kỳ BC (để tính nhập xuất trong kỳ)

1616774900380.png

Công thức Let cho 2 bảng trung gian:

PHP:
preps =LET(preps,FILTER(Data[[MA_HH]:[ThanhTien]],Data[Ngay_CT]<Report!$C1),preps)
//
curps =LET(curps,FILTER(Data[[MA_HH]:[ThanhTien]],(Data[Ngay_CT]<=Report!C2)*(Data[Ngay_CT]>=Report!C1)),curps)

Lý do phải đưa 2 bảng này ra ngoài vì Sumif và Sumifs chỉ chạy trên sheet, không chạy trên bảng ảo nếu các tham số không cùng kích thước (đoán vậy)
 

File đính kèm

  • Let Function 02.xlsx
    2.6 MB · Đọc: 42
Hàm LET tạo dòng tổng cộng
Với cột số thứ tự dư 1 dòng, có thể tạo dòng dư thành dòng tổng cộng:
Tham số: Chọn mặt hàng, chọn ngày bắt đầu và ngày kết thúc

1616983443748.png

1616983462702.png

Mã:
=LET(dta,FILTER(Data,(Data[Ngay_CT]>=C1)*(Data[Ngay_CT]<=C2)*(Data[LoaiXN]="X")*(Data[MA_HH]=E1),"Không có doanh thu"),
sct,INDEX(dta,,1),nct,INDEX(dta,,2),makh,INDEX(dta,,3),tkh,VLOOKUP(makh,DMKH,2,0),sl,INDEX(dta,,5),dg,INDEX(dta,,6),tt,INDEX(dta,,8),stt,SEQUENCE(ROWS(dta)+1),
IFERROR(CHOOSE(SEQUENCE(1,8),stt,sct,nct,makh,tkh,sl,dg,tt),CHOOSE(SEQUENCE(1,8),"","","Cộng","","",SUM(sl),"",SUM(tt))))
 

File đính kèm

  • Let Function 03.xlsx
    2.5 MB · Đọc: 26
Hàm LET cho dòng tổng của bài #3
Mã:
=LET(dmm,DMuc[Mã],dmt,DMuc[Tên],dmdv,DMuc[Đvt],MaSdu,SDu[Mã],SlDu,SDu[Số lượng],TtDu,SDu[Thành tiền],
SLDau,SUMIF(MaSdu,dmm,SlDu),ttdau,SUMIF(MaSdu,dmm,TtDu),preps,Data!$J$3#,presln,SUMIFS(INDEX(preps,,2),INDEX(preps,,1),dmm,INDEX(preps,,4),"N"),
preslx,SUMIFS(INDEX(preps,,2),INDEX(preps,,1),dmm,INDEX(preps,,4),"X"),prettn,SUMIFS(INDEX(preps,,5),INDEX(preps,,1),dmm,INDEX(preps,,4),"N"),
prettx,SUMIFS(INDEX(preps,,5),INDEX(preps,,1),dmm,INDEX(preps,,4),"X"),
curps,Data!$P$3#,cursln,SUMIFS(INDEX(curps,,2),INDEX(curps,,1),dmm,INDEX(curps,,4),"N"),curslx,SUMIFS(INDEX(curps,,2),INDEX(curps,,1),dmm,INDEX(curps,,4),"X"),
curttn,SUMIFS(INDEX(curps,,5),INDEX(curps,,1),dmm,INDEX(curps,,4),"N"),curttx,SUMIFS(INDEX(curps,,5),INDEX(curps,,1),dmm,INDEX(curps,,4),"X"),
stt,SEQUENCE(ROWS(dmm)+1),
IF(stt<=ROWS(dmm),CHOOSE(SEQUENCE(1,12),stt,dmm,dmt,dmdv,SLDau+presln-preslx,ttdau+prettn-prettx,cursln,curttn,curslx,curttx,
SLDau+presln-preslx+cursln-curslx,ttdau+prettn-prettx+curttn-curttx),CHOOSE(SEQUENCE(1,12),"","","Cộng","","",SUM(ttdau+prettn-prettx),"",
SUM(curttn),"",SUM(curttx),"",SUM(ttdau+prettn-prettx+curttn-curttx))))
 
Mình góp thêm một ví dụ vui về Hàm Let để tính thuế thu nhập cá nhân theo quy định thuế hiện tại nhé:

Với A2 là thu nhập tính thuế (sau khi giảm trừ bản thân và giảm trừ gia cảnh, giảm các khoản không chịu thuế khác):

= LET(PITLevel, {0,5,10,18,32,52,80},SUM(TEXT(A2-PITLevel*10^6,"#;\0")*5%))
 
Hàm LET cho dòng tổng của bài #3
Mã:
=LET(dmm,DMuc[Mã],dmt,DMuc[Tên],dmdv,DMuc[Đvt],MaSdu,SDu[Mã],SlDu,SDu[Số lượng],TtDu,SDu[Thành tiền],
SLDau,SUMIF(MaSdu,dmm,SlDu),ttdau,SUMIF(MaSdu,dmm,TtDu),preps,Data!$J$3#,presln,SUMIFS(INDEX(preps,,2),INDEX(preps,,1),dmm,INDEX(preps,,4),"N"),
preslx,SUMIFS(INDEX(preps,,2),INDEX(preps,,1),dmm,INDEX(preps,,4),"X"),prettn,SUMIFS(INDEX(preps,,5),INDEX(preps,,1),dmm,INDEX(preps,,4),"N"),
prettx,SUMIFS(INDEX(preps,,5),INDEX(preps,,1),dmm,INDEX(preps,,4),"X"),
curps,Data!$P$3#,cursln,SUMIFS(INDEX(curps,,2),INDEX(curps,,1),dmm,INDEX(curps,,4),"N"),curslx,SUMIFS(INDEX(curps,,2),INDEX(curps,,1),dmm,INDEX(curps,,4),"X"),
curttn,SUMIFS(INDEX(curps,,5),INDEX(curps,,1),dmm,INDEX(curps,,4),"N"),curttx,SUMIFS(INDEX(curps,,5),INDEX(curps,,1),dmm,INDEX(curps,,4),"X"),
stt,SEQUENCE(ROWS(dmm)+1),
IF(stt<=ROWS(dmm),CHOOSE(SEQUENCE(1,12),stt,dmm,dmt,dmdv,SLDau+presln-preslx,ttdau+prettn-prettx,cursln,curttn,curslx,curttx,
SLDau+presln-preslx+cursln-curslx,ttdau+prettn-prettx+curttn-curttx),CHOOSE(SEQUENCE(1,12),"","","Cộng","","",SUM(ttdau+prettn-prettx),"",
SUM(curttn),"",SUM(curttx),"",SUM(ttdau+prettn-prettx+curttn-curttx))))
Công thức em nhìn hoa hết mắt Thầy ạ!
 
Chỉnh sửa lần cuối bởi điều hành viên:
Hàm let tạo báo cáo chi tiết nhập xuất của 1 mặt hàng
Cũng với dữ liệu ở bài #3, tạo chi tiết nhập xuất của 1 mặt hàng E1 trong khoảng thời gian từ ngày C1 đến ngày C2:
1620059134004.png

Có sử dụng hàm Lambda (thực chất là hàm Filter) lấy dữ liệu trước ngày date1 để tính số dư đầu kỳ ở K8 và L8

XFilter =LAMBDA(dt,crit1,crit2,FILTER(INDEX(dt,SEQUENCE(ROWS(dt)),{2,4,5,7,8}),(INDEX(dt,,2)<crit1)*(INDEX(dt,,4)=crit2),{"","","",0}))

Lấy các cột 2, 4, 5, 7, 8 của bảng data, điều kiện ngày nhỏ hơn ngày date1 và mã bằng mặt hàng chọn. Nếu không có dữ liệu thì tạo 1 dòng có giá trị 0 để coi như đầu kỳ báo cáo.

1620059333600.png

- Số dư đầu kỳ số lượng (K8):
K8 =LET(dta,Data!$L$3#,sdu,IFERROR(VLOOKUP($E$1,SDu,2,0),0),pren,SUMIF(INDEX(dta,,4),"N",INDEX(dta,,3)),prex,SUMIF(INDEX(dta,,4),"X",INDEX(dta,,3)),sdu+pren-prex)

- Số dư đầu kỳ (thành tiền) (L8)
L8 =LET(dta,Data!$L$3#,sdu,IFERROR(VLOOKUP($E$1,SDu,3,0),0),pren,SUMIF(INDEX(dta,,4),"N",INDEX(dta,,5)),prex,SUMIF(INDEX(dta,,4),"X",INDEX(dta,,5)),sdu+pren-prex)

- Hàm chính đặt ở A9, có đặt dòng tổng

A9 =LET(dta,FILTER(Data,(Data[Ngay_CT]>=C1)*(Data[Ngay_CT]<=C2)*(Data[MA_HH]=E1)),
sct,INDEX(dta,,1),nct,INDEX(dta,,2),makh,INDEX(dta,,3),tkh,VLOOKUP(makh,DMKH,2,0),
sln,IF(INDEX(dta,,7)="N",INDEX(dta,,5),0),dg,INDEX(dta,,6),slx,IF(INDEX(dta,,7)="X",INDEX(dta,,5),0),
stt,SEQUENCE(ROWS(dta)+1),
ttn,IF(INDEX(dta,,7)="N",INDEX(dta,,8),0),ttx,IF(INDEX(dta,,7)="X",INDEX(dta,,8),0),
IF(stt<=ROWS(dta),CHOOSE(SEQUENCE(1,10),stt,sct,nct,makh,tkh,dg,sln,ttn,slx,ttx),
CHOOSE(SEQUENCE(1,10),"","","","","Cộng","",SUM(sln),SUM(ttn),SUM(slx),SUM(ttx))))


- Hai cột số tồn (sau khi nhập xuất từng dòng)

K9 =INDEX(K8+SUMIF(INDEX(A9#,,1),"<="&INDEX(A9#,,1),INDEX(A9#,,7))-SUMIF(INDEX(A9#,,1),"<="&INDEX(A9#,,1),INDEX(A9#,,9)),SEQUENCE(ROWS(A9#)-1),1)
L9 =INDEX(L8+SUMIF(INDEX(A9#,,1),"<="&INDEX(A9#,,1),INDEX(A9#,,8))-SUMIF(INDEX(A9#,,1),"<="&INDEX(A9#,,1),INDEX(A9#,,10)),SEQUENCE(ROWS(A9#)-1),1)


Bằng đầu kỳ + tổng nhập - tổng xuất (chỉ tổng tới ngang nghiệp vụ dòng đang đứng, dùng cột STT làm điều kiện tính SumIf)
 

File đính kèm

  • Let Function 04.xlsx
    2.5 MB · Đọc: 13
Mình góp thêm một ví dụ vui về Hàm Let để tính thuế thu nhập cá nhân theo quy định thuế hiện tại nhé:

Với A2 là thu nhập tính thuế (sau khi giảm trừ bản thân và giảm trừ gia cảnh, giảm các khoản không chịu thuế khác):
Anh ơi cho em hỏi Công thức A = LET(PITLevel, {0,5,10,18,32,52,80},SUM(TEXT(A2-PITLevel*10^6,"0.00;\0")*5%)), thì PIT level: là mảng name range
hay là j ạ? Công thức LET phiên bản Excel 2016 không có function của hàm này anh ạ, phải cài lên Ex365 ạ
 
Anh ơi cho em hỏi Công thức A = LET(PITLevel, {0,5,10,18,32,52,80},SUM(TEXT(A2-PITLevel*10^6,"0.00;\0")*5%)), thì PIT level: là mảng name range
hay là j ạ? Công thức LET phiên bản Excel 2016 không có function của hàm này anh ạ, phải cài lên Ex365 ạ
Theo cú pháp hàm Let thì PITLevel là giá trị liền kề của nó trong công thức, tức là mảng {0, 5, 10, 18,32, 52, 80}
Let chỉ có trong Excel 365
 
Top Bottom