Hàm LET trong Excel 365

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))



- 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"))


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


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


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


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

 

Cảm ơn bạn @hocexcel_1991, hàm của bạn ra đúng kết quả với bảng Pivot table nhưng nó lại không có tiêu đề, cái này bạn có thể bổ sung được không? Mình cảm ơn!
View attachment 268968

Cái dòng tiêu đề thì nên gõ tay vào, nó cũng không mất thời gian là mấy, nếu công thức có cả tiêu đề sẽ nặng file hơn. Nếu bạn muốn công thức thì thử công thức này

=LET(a,Sales[Mặt hàng],n,Sales[Nhóm],s,Sales[Số lượng],d,Sales[Đơn giá],t,Sales[Thành tiền],au,SORT(UNIQUE(a)),
se,SEQUENCE(COUNTA(au)+2),
sltc,SUMIFS(s,a,au,n,"Trái cây"),
tttc,SUMIFS(t,a,au,n,"Trái cây"),
slgk,SUMIFS(s,a,au,n,"Giải khát"),
ttgk,SUMIFS(t,a,au,n,"Giải khát"),
sldh,SUMIFS(s,a,au,n,"Đồ hộp"),
ttdh,SUMIFS(t,a,au,n,"Đồ hộp"),
tongsl,sldh+slgk+sltc,
tongtt,ttdh+ttgk+tttc,
mahang,FILTERXML("<x><y>"&"Mã hàng"&"</y><y>"&TEXTJOIN("</y><y>",,au)&"</y></x>","//y"),
sldh1,FILTERXML("<x><y>"&"Số lượng Đồ hộp"&"</y><y>"&TEXTJOIN("</y><y>",,sldh)&"</y></x>","//y"),
ttdh1,FILTERXML("<x><y>"&"Thành tiền Đồ hộp"&"</y><y>"&TEXTJOIN("</y><y>",,ttdh)&"</y></x>","//y"),
slgk1,FILTERXML("<x><y>"&"Số lượng Giải khát"&"</y><y>"&TEXTJOIN("</y><y>",,slgk)&"</y></x>","//y"),
ttgk1,FILTERXML("<x><y>"&"Thành tiền Giải khát"&"</y><y>"&TEXTJOIN("</y><y>",,ttgk)&"</y></x>","//y"),
sltc1,FILTERXML("<x><y>"&"Số lượng Trái cây"&"</y><y>"&TEXTJOIN("</y><y>",,sltc)&"</y></x>","//y"),
tttc1,FILTERXML("<x><y>"&"Thành tiền Trái cây"&"</y><y>"&TEXTJOIN("</y><y>",,tttc)&"</y></x>","//y"),
tongsl1,FILTERXML("<x><y>"&"Total Số lượng"&"</y><y>"&TEXTJOIN("</y><y>",,tongsl)&"</y></x>","//y"),
tongtt1,FILTERXML("<x><y>"&"Total Thành tiền"&"</y><y>"&TEXTJOIN("</y><y>",,tongtt)&"</y></x>","//y"),
IFERROR(CHOOSE(SEQUENCE(1,9),INDEX(mahang,se),sldh1,ttdh1,slgk1,ttgk1,sltc1,tttc1,tongsl1,tongtt1),CHOOSE(SEQUENCE(1,9),"Grand Total",SUM(sldh),SUM(ttdh),SUM(slgk),SUM(ttgk),SUM(sltc),SUM(tttc),SUM(tongsl),SUM(tongtt))))

 

Hi Anh ptm0412,

Anh viết file excel ứng dụng các hàm mảng trong offcie 365 để lên BCTC luôn nhé từ NKC lên báo cáo tài chính & sổ cái để E học hỏi ạ

Thầy ptm0412 ơi,

Thầy làm giúp ứng dụng dùng hàm Let lên sổ cái, sổ chi tiết công nợ từ NKC nha, E đang rất muốn ứng dụng hàm này để lên BCTC (bảng CĐSPS, CĐKT, KQKD, LCTT) nhưng không biết có được không ạ?

 

Thầy ptm0412 ơi,

Thầy làm giúp ứng dụng dùng hàm Let lên sổ cái, sổ chi tiết công nợ từ NKC nha, E đang rất muốn ứng dụng hàm này để lên BCTC (bảng CĐSPS, CĐKT, KQKD, LCTT) nhưng không biết có được không ạ?

Làm cho kế toán thì vô chừng lắm. Lý do mẫu biểu báo cáo chỉ có một, nhưng cấu trúc dữ liệu thì cả trăm kiểu: Mỗi doanh nghiệp, mỗi ngành nghề 1 kiểu cấu trúc dữ liệu:
- Nhập 1 sheet, xuất 1 sheet hoặc nhập xuất chung 1 sheet
- Thu 1 sheet, chi 1 sheet, hoặc thu chi chung 1 sheet
- Danh mục mặt hàng, danh mục khách hàng, danh mục nhà CC, danh mục TK ngân hàng, danh mục tiền tệ, ... người có người không, cái có cái không.
- Tài khoản KT lúc thì 2 cấp, lúc thì 3, 4 cấp.
- Tài sản cố định: kẻ có người không
- 1 tỳ thứ khác

Hàm Let không làm tổng quát nổi, kể cả VBA cũng vậy. Trừ khi tất cả các yêu cầu bên trên đồng nhất và do người viết hàm/ lập trình thiết kế dữ liệu. Mà như vậy bằng với thiết kế 1 phần mềm kế toán để bán còn gì?

 

Làm cho kế toán thì vô chừng lắm. Lý do mẫu biểu báo cáo chỉ có một, nhưng cấu trúc dữ liệu thì cả trăm kiểu: Mỗi doanh nghiệp, mỗi ngành nghề 1 kiểu cấu trúc dữ liệu:
- Nhập 1 sheet, xuất 1 sheet hoặc nhập xuất chung 1 sheet
- Thu 1 sheet, chi 1 sheet, hoặc thu chi chung 1 sheet
- Danh mục mặt hàng, danh mục khách hàng, danh mục nhà CC, danh mục TK ngân hàng, danh mục tiền tệ, ... người có người không, cái có cái không.
- Tài khoản KT lúc thì 2 cấp, lúc thì 3, 4 cấp.
- Tài sản cố định: kẻ có người không
- 1 tỳ thứ khác

Hàm Let không làm tổng quát nổi, kể cả VBA cũng vậy. Trừ khi tất cả các yêu cầu bên trên đồng nhất và do người viết hàm/ lập trình thiết kế dữ liệu. Mà như vậy bằng với thiết kế 1 phần mềm kế toán để bán còn gì?

Dạ thầy,

Những điều thầy nói thì Em đã 20 năm trong nghề kế toán nên E cũng biết ạ nhưng cốt yếu E muốn ứng dụng hàm này (các hàm mảng trong office 365) để lên báo cáo trong kế toán nhanh thôi, E nghỉ mình làm từ NKC lên:
- cân đối tài khoản, sổ cái thì làm theo TK cấp 1 thôi, ai muốn chi tiết hơn thì tự phát triển ra.
- KQKD, CĐKT, LCTT thì mình lấy theo TK cấp 1 để lên ví như E đang dùng công thức sau để lấy doanh thu cho chỉ tiêu số 01 trên KQKD là =SUMPRODUCT(--("511"=LEFT(tkcoid,3)),PhatSinh)+SUMPRODUCT(--("512"=LEFT(tkcoid,3)),PhatSinh) vậy khi kết hợp với hàm Let này thì công thức có được giữ nguyên? hy vọng thầy cho 1 file ví dụ mẫu để dựa vào đó mà phát triển ra ạ.

Thân chào thầy.

 

Dạ thầy,

Những điều thầy nói thì Em đã 20 năm trong nghề kế toán nên E cũng biết ạ nhưng cốt yếu E muốn ứng dụng hàm này (các hàm mảng trong office 365) để lên báo cáo trong kế toán nhanh thôi, E nghỉ mình làm từ NKC lên:
- cân đối tài khoản, sổ cái thì làm theo TK cấp 1 thôi, ai muốn chi tiết hơn thì tự phát triển ra.
- KQKD, CĐKT, LCTT thì mình lấy theo TK cấp 1 để lên ví như E đang dùng công thức sau để lấy doanh thu cho chỉ tiêu số 01 trên KQKD là =SUMPRODUCT(--("511"=LEFT(tkcoid,3)),PhatSinh)+SUMPRODUCT(--("512"=LEFT(tkcoid,3)),PhatSinh) vậy khi kết hợp với hàm Let này thì công thức có được giữ nguyên? hy vọng thầy cho 1 file ví dụ mẫu để dựa vào đó mà phát triển ra ạ.

Thân chào thầy.

Hàm Let này tùy trường hợp mới có hiệu quả,
Đôi khi dùng Table còn nhanh hơn ấy.
Nên phải cân nhắc.

 

học đc nhiều cảm ơn b