Hàm LAMBDA trong Excel 365 (1 người xem)

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 Excel 365 phiên bản thử nghiệm beta channel, 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:

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

Các tham số parameter1, 2... là tùy chọn, có thể đến 253 tham số, calculation 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 sheet nhưng đơn giản nhất là tạo Name: bấm Ctrl-Alt-F3 để tạo name mới, đặt tên hàm vào ô Name, chọn phạm vi, comment (comment này sẽ là hướng dẫn khi gõ công thức), nhập công thức vào refer to. Hàm LAMBDA cho phép gọi đệ quy mà không bị lỗi tham chiếu vòng.
- Ví dụ 1: tạo hàm Add1 có tác dụng đơn giản tăng 1 vào biến: đặt Name=Add1, Refer to =LAMBDA(x,x+1). Trên sheet 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ề số hạng thứ n của dãy Fibonacci (F(1)=F(2)=1, F(n+2)=F(n+1)+F(n)): đặt name=FIBO, Refer to điền công thức:
=LAMBDA(n, IF(n<3,1,FIBO(n-1)+FIBO(n-2)))
- 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õ có 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<0 sẽ trả về lỗi #N/A, còn lại sẽ trả về hai nghiệm:

=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.

 

Không phải bản Enterprise không có. MS chỉ giới hạn quyền lắp đặt riêng cho ác-min (và những người ác-min cho phép) thôi. Nếu là ác-min thì cứ việc mở nó ra (tiếng Tây là deployment).

Cắt từ trang quép "Join the Office Insider Program" (https://insider.office. com/en-gb/join/windows)



Dòng cuối (Note): nếu bạn là ác-min của MS 365, vào cái link này để tìm hiểu cách lựa chọn áp dụng Insider cho ác-min
(xin lỗi, tôi có thói quen hiếm khi dẫn link trực tiếp)

Chú thích: vào bơ-lốc của MS 365 sẽ thấy họ nói rằng "ngay cả nếu bạn có Insider cũng chưa chắc đã nhận được những phần mềm bê-ta mới nhất. Mỗi lần đưa ra (roll-out) MS chỉ đưa cho khảng 50% số subscribers, một thời gian sau mới đưa cho chỗ còn lại. Và bao giờ chắc ăn rồi thì mới đưa ra bản thường (không có Insider)"

 

Hàm Lambda Append ở bài 15 chạy đúng và sử dụng gọn, chỉ cần viết công thức =Append(n) là nối n cột thành dòng. Như vậy hàm này không sử dụng linh hoạt cho mọi bảng dữ liệu cần chuyển cột thành dòng, mỗi bảng khác nhau phải viết 1 hàm GetColData và 1 hàm Append cho dữ liệu mới.
Cải tiến lại để có thể dùng 1 hàm cho mọi bảng sữ liệu dạng nhiều cột dữ liệu cần chuyển thành hàng dọc, nhiều cột tiêu đề (cần lấy 1):
1. Hàm GetColData (lấy bảng 3 cột cho 1 cột dữ liệu bất kỳ) sửa lại
từ
GetMthData =LAMBDA(dta,mths,mth,CHOOSE({1,2,3},INDEX(dta,,2),INDEX(mths,1,mth),INDEX(dta,,mth+2)))
thành
GetColData =LAMBDA(dta,cols,col,t,CHOOSE({1,2,3},INDEX(dta,,t),INDEX(cols,1,col),INDEX(dta,,COLUMNS(dta)-COLUMNS(cols)+col)))
trong đó
- dta = nguyên bảng dữ liệu
- cols = dòng tiêu đề của riêng các cột cần chuyển thành dòng
- col = số thứ tự cột cần lấy trong cols
- t = số thứ tự cột tiêu đề dòng cần lấy (mới thêm)

2. Hàm Append2 đổi tên thành hàm Append3Cols mang ý nghĩa là nối 2 bảng có 3 cột.

3. Hàm Append thay đổi khá nhiều nhưng cùng thuật toán đệ quy:
sửa từ
Append =LAMBDA(n,IF(n=2,Append2(GetMthData(Data,Data[[#Headers],[Tháng 1]:[Tháng 12]],1),
GetMthData(Data,Data[[#Headers],[Tháng 1]:[Tháng 12]],n)),
Append2(Append(n-1),GetMthData(Data,Data[[#Headers],[Tháng 1]:[Tháng 12]],n))))

thành
Append =LAMBDA(dta,dtahead,n,x,IF(n=2,Append3Cols(GetColData(dta,dtahead,1,x),GetColData(dta,dtahead,n,x)),Append3Cols(Append(dta,dtahead,n-1,x),GetColData(dta,dtahead,n,x))))

- Nhận tất cả tham số từ người dùng, truyền vào hàm GetColData, thay vì gắn cứng tham số trong hàm Append. Điều này làm cho hàm Append có thể dùng nhiều lần cho những bảng dữ liệu khác nhau, số lượng cột khác nhau.
- Tham số dta là nguyên bảng dữ liệu
- dtahead là dòng tiêu đề của những cột chứa dữ liệu muốn chuyển thành dòng
- n = số cột cần lấy,
- x = số thứ tự cột tiêu đề muốn lấy (chỉ lấy 1)

4. Cách viết công thức

N3 =Append(Data2,Data2[[#Headers],[Store 1]:[Store 9]],4,2)
Lấy 4 cột (từ Sore 1 đến Store 4) và lấy cột tiêu đề 2 (Category)



Nếu cột thứ nhất (tiêu đề dòng) là 1 validation chọn từ list {Type, Category, Product} và số cột cần chuyển thành dòng gắn vào 1 ô, thì viết thành công thức:





Title là range 1 dòng 3 cột chứa 3 mục chọn tiêu đề dòng
__________________________
FIle đính kèm có sheet so sánh hàm Lambda và Power query:
- Power query chỉ có thể chuyển tất cả cột thành dòng, không có tùy chọn lấy chỉ 1 số cột, không tùy chọn tiêu đề dòng
- Dữ liệu rỗng sẽ bị Power query bỏ qua, còn hàm Lambda chuyển thành số 0 và vẫn hiển thị 1 dòng.

 

Hàm Append cải tiến lấy 1 số cột bắt đầu không phải cột 1
Sau cải tiến ở bài #22 thì hàm Append đã có thể lấy 1 số cột bất kỳ chuyển thành dòng cho nhiều bảng dữ liệu khác nhau. Điều này chứng tỏ rằng viết hàm Lambda giống như viết hàm tự tạo, có thể viết sẵn để dùng dần, nhất là nếu lưu thành add-in.
Tuy nhiên hàm trên chưa ứng dụng thực tiễn mấy, nhất là khi dữ liệu theo cột là ngày/ tháng, nhiều khi muốn lấy từ tháng 4 đến tháng 6 (quý 2) của 12 cột, lấy dữ liệu 1 tuần từ ngày 8 đến ngày 14 của 31 cột, ...
Sau đâu la những chỉnh sửa của hàm Append:
sửa từ
Append =LAMBDA(dta,dtahead,n,x,IF(n=2,Append3Cols(GetColData(dta,dtahead,1,x),GetColData(dta,dtahead,n,x)),Append3Cols(Append(dta,dtahead,n-1,x),GetColData(dta,dtahead,n,x))))
thành:
Append =LAMBDA(dta,dtahead,m,n,x,IF(n=2,Append3Cols(GetColData(dta,dtahead,m,x),GetColData(dta,dtahead,m+n-1,x)),
Append3Cols(Append(dta,dtahead,m,n-1,x),GetColData(dta,dtahead,m+n-1,x))))

Trong đó m là thứ tự cột đầu và n là số cột muốn lấy (để chuyển thành dòng).
Trong file đính kèm, 3 sheet dữ liệu khác nhau xài chung 1 hàm.
Dữ liệu trong file:

Lấy từ tháng 1 đến tháng 3:



Lấy 3 tháng từ tháng 4 đến tháng 6 (3 tháng quý 2)



Lấy 6 tháng cuối năm: từ tháng 7 và lấy 6 tháng

 

Cải tiến hàm nối 2 bảng có số cột bất kỳ

Append1st =LAMBDA(data1,data2,IF(SEQUENCE(ROWS(data1)+ROWS(data2))<=ROWS(data1),
INDEX(data1,SEQUENCE(ROWS(data1)+ROWS(data2)),SEQUENCE(1,COLUMNS(data1))),
INDEX(data2,SEQUENCE(ROWS(data1)+ROWS(data2))-ROWS(data1),SEQUENCE(1,COLUMNS(data2)))))

Ứng dụng chuyển cột thành dòng (tiếp theo)
Có thể lấy 2 tiêu đề dòng để chuyển dữ liệu từ cột thành dòng
Viết thêm hàm Get4ColsData và Append đệ quy với hàm này.

 

Cải tiến hàm lấy dữ liệu 1 cột với số lượng cột chứa tiêu đề dòng bất kỳ
Bài 23 và 24 có 2 hàm Get3Cols và Get4Cols lấy được dữ liệu với 1 hoặc 2 cột chứa tiêu đề dòng, 1 cột tiêu đề cột và 1 cột dữ liệu số.
Bài này cải tiến có thể lấy n cột thay vì phải dùng những hàm riêng lẻ cho 3 cột, 4 cột, 5 cột, ...

GetnCols =LAMBDA(dta,cols,col,t,IF(SEQUENCE(1,COLUMNS(t)+2)<=COLUMNS(t),
INDEX(dta,SEQUENCE(ROWS(dta)),COLUMN(t)-@COLUMN(dta)+1),
IF(SEQUENCE(1,COLUMNS(t)+2)=COLUMNS(t)+1,INDEX(cols,1,col),
INDEX(dta,,COLUMNS(dta)-COLUMNS(cols)+col))))

Lưu ý ký tự @ trong công thức, -@COLUMN(dta)+1 để bảo đảm đúng với vị trí cột bất kỳ của dữ liệu gốc (không phải bắt đầu từ cột A)
Trong đó:
- dta là bảng full dữ liệu
- cols là dòng tiêu đề của các cột giá trị muốn lấy
- col là thứ tự cột giá trị muốn lấy trong cols
- t là range các tiêu đề dòng muốn lấy. Có thể lấy 1, 2, 3 và n cột như vậy

Thí dụ với dữ liệu sau:



Lấy tiêu đề 2 (Product, cột D) và tháng 11:



Lấy 2 tiêu đề 2, 3 (cột D và E, Product và Channel) và tháng 11



Lấy cả 3 tiêu đề (C2:E2) và tháng 11



Ứng dụng cho hàm append như những bài trước: lấy 3 tiêu đề dòng, lấy 3 tháng kể từ tháng 4 và nối lại



Nếu ai không muốn thứ tự tiêu đề dòng (1, 2, 3) thì có thể dùng Index để sắp xếp lại: thứ tự mới là 4, 3, 2, 1, 5

 

Hàm Lambda cuối cùng đã được release không còn Insider nữa.