Hàm LET trong Excel 365

Quảng cáo
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: 29
Đ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: 25
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: 14
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%))
 
Quảng cáo
Top Bottom