Chức năng lambda google sheet

Hàm LAMBDA trong Excel 365​

Chào các bạn. Mình xin giới thiệu hàm mới được đưa vào kênh phiên bản thử nghiệm của Excel 365, sẽ đưa vào Excel 365 chính thức trong tương lai gần. Hàm LAMBDA dùng để xây dựng hàm tự tạo (UDF) bằng công thức, qua đó rút ngắn công thức phức tạp sử dụng nhiều lần.
Cú pháp.

Mã. Sao chép.

=LAMBDA([parameter1, parameter2, …,] calculation)

Các tham số tham số1, 2. là tùy chọn, có thể đến 253 tham số, phép tính là biểu thức cần tính toán (thân hàm tự tạo). Có thể nhập công thức này trên trang tính nhưng đơn giản nhất là tạo Tên. nhấn Ctrl-Alt-F3 để tạo tên mới, đặt tên hàm vào ô Tên, chọn phạm vi, nhận xét (bình luận này sẽ được hướng dẫn khi nhập công thức), nhập công thức vào tham khảo. Hàm LAMBDA cho phép gọi đệ quy mà không bị vòng tham chiếu lỗi.
- Ví dụ 1. tạo hàm Add1 có tác dụng đơn giản tăng 1 vào biến. đặt Tên=Add1, Tham khảo =LAMBDA(x,x+1). Trên trang tính, ta nhập công thức =Add1(A1);
- Ví dụ 2 minh họa đệ quy, xây dựng hàm FIBO trả về hạng thứ n của dãy Fibonacci (F(1)=F . set name=FIBO, Tham khảo công thức điền.
=LAMBDA(n, IF(n
- Ví dụ 3. mình xây dựng hàm giải phương trình bậc 2 nhận 3 tham số a, b, c. Nếu a=0 thì hàm trả về lỗi #NUM (không rõ hàm nào trả về lỗi này không nên mình gọi hàm SQRT(-1)), nếu delta

Mã. Sao chép.

=LAMBDA(a,b,c,IF(a=0,SQRT(-1),LET(d,b*b-4*a*c,IF(d<0,NA(),({-1,1}*SQRT(d)-b)/2/a))))

Công thức trên sử dụng hàm LET để không phải tính lại d (delta) nhiều lần để tăng tốc độ (nghe nói vậy). {-1,1} là mảng để tính toán x1, x2 trả về 2 ô cùng dòng, nếu muốn trả về 2 ô cùng cột thì thay bằng {-1;1}
Cách sử dụng: nhập công thức vào A1 = GPTB2(1,2,0), A1 và B1 sẽ chứa 2 nghiệm.

 

Trước hết, phải xác định lại Lambda là λ (1 ký tự Hy lạp như anpha, béta, gamma, epsilon) tương đương phụ âm L của mẫu tự la tinh, chứ không phải nhảy lam ba da< . Tính tổng theo danh mục và sắp xếp theo cột tổng của dữ liệu bất kỳ
Thử ứng dụng hàm Lambda rộng hơn: Tính tổng theo danh mục và sắp xếp theo cột tổng của dữ liệu bất kỳ
Cách sử dụng giống như hàm tư tạo bằng VBA
Nếu kết quả mảng thì chỉ .
Name:

Mã. Sao chép.

SumSort =LAMBDA(a,num,c,SORT(CHOOSE({1,2},UNIQUE(a),SUMIF(a,UNIQUE(a),num)),2,c))

Ý nghĩa:
a = cột tiêu chí cần tính tổng sau khi lọc duy nhất
num = cột số liệu cần tính tổng
c = 1 hoặc -1 là tham số sắp xếp tăng dần hay giảm dần của hàm SORT
choose({1,2}, giá trị 1, giá trị 2) là 2 côt kết quả: 1 cột là danh mục duy nhất, 1 cột là kết quả sumif theo danh mục.

1. Tổng doanh số bán:
- theo nhóm mặt hàng (tăng dần, giảm dần)



- theo mặt hàng



2. Tính tổng lương theo phòng ban, theo công trình

 

Hàm LAMBDA lồng nhau
Với hàm SumSort ở bài #8, nếu muốn trích xuất các dòng đã .

Mã. Sao chép.

SumFilterAmt =LAMBDA(a,num,c,numcri,FILTER(SumSort(a,num,c),INDEX(SumSort(a,num,c),,2)>numcri))

Với dữ liệu của file bài #8, can can test.
K10 =SumFilterAmt(Sales[Mặt hàng],Sales[Thành tiền],1,L8)
With L8 is tham số lọc giá trị

Nếu muốn rộng hơn, lọc lớn hơn hay nhỏ hơn tuỳ ý, thì thêm 1 đối số:

Mã. Sao chép.

SumFilterAmt =LAMBDA(a,num,c,numcri,cri,FILTER(SumSort(a,num,c),
IF(cri=">",INDEX(SumSort(a,num,c),,2)>numcri,INDEX(SumSort(a,num,c),,2)

Nếu K8 là 1 validation chọn giữa < và > thì gọn hơn nữa: Chỉ cần sửa K8 và/ hoặc L8 là công thức chạy lại

 

Hàm Lambda lồng và đệ quy

Giả sử có bảng dữ liệu theo cột như sau.

Giờ ta muốn.
1. Lấy dữ liệu theo tháng (có thể sử dụng let hoặc Index), ở đây tôi viết Index nhưng lồng vào Lambda

Mã. Sao chép.

GetColData =LAMBDA(dta,cn,mth,CHOOSE({1,2},INDEX(dta,,cn),INDEX(dta,,mth+2)))

2. Chuyển dữ liệu thành hàng dọc, muốn lấy nhiều tháng tùy ý
2. 1. Bước 1.
Viết hàm lấy dữ liệu 1 tháng bất kỳ có 1 cột thông số tháng đó

Mã. Sao chép.

GetMthData =LAMBDA(dta,mths,mth,CHOOSE({1,2,3},INDEX(dta,,2),
INDEX(mths,1,mth),INDEX(dta,,mth+2)))

2. 2. Bước 2.
Viết hàm nối 2 bảng cùng cấu trúc (3 cột) (hàm Append2)

Mã. Sao chép.

Append2 =LAMBDA(data1,data2,IF(SEQUENCE(ROWS(data1)+ROWS(data2))<=ROWS(data1), 
CHOOSE({1,2,3},INDEX(data1,,1),INDEX(data1,,2),INDEX(data1,,3)),
CHOOSE({1,2,3},INDEX(data2,SEQUENCE(ROWS(data1)+ROWS(data2))- ROWS(data1),1),
INDEX(data2,SEQUENCE(ROWS(data1)+ROWS(data2))-ROWS(data1),2),
INDEX(data2,SEQUENCE(ROWS(data1)+ROWS(data2))-ROWS(data1),3))))

2. 3. Bước 3.
Viết hàm đệ quy lấy n tháng (hàm Append)
- Nếu lấy 2 tháng (n=2) thì sử dụng hàm bước 2 Append2 nối 2 bảng tháng 1 . Use function step 2, connection Append of n-1, and function GetMthData of month n
- Nếu n> 2: Dùng hàm bước 2, nối Append của n-1, và hàm GetMthData của tháng n

Mã. Sao chép.

________số 8

Kết quả: Giống như dùng power query - Unpivot, nhưng thuận tiện hơn là muốn lấy bao nhiêu cột cũng được (từ 2 đến 12)