Hàm LAMBDA trong Excel: hàm tự tạo, MAP và BYROW

=LAMBDA(price,price*1.2)(B2) định nghĩa một hàm nhỏ có một đầu vào, price, và gọi nó ngay trên B2: giá 2,5 của cây bút thành 3. Chỉ như vậy thì nó chẳng khác gì một =B2*1.2 dài hơn. Giá trị thật của LAMBDA là đặt cho hàm một cái tên trong Name Manager, để một công thức dài trở thành =ADDVAT(B2), và truyền nó vào MAP, BYROW cùng các hàm khác bên dưới.

Công thứcĐặt lại

C2fx

=LAMBDA(price,price*1.2)(B2)

	A	B	C
1	Item	Price	With VAT
2	Pen	2.5	3
3	Bag	120	144
4	Lamp	35	42
5	Mug	8	9.6
6	Desk	150	180

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

Cú pháp hàm LAMBDA

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

• Mỗi parameter là tên của một đầu vào, giống các tên trong LET. Được phép có tối đa 253 tham số.
• Đối số cuối cùng là calculation, phép tính dùng các tham số đó.
• Giá trị của các tham số đặt trong ngoặc ngay sau dấu đóng ngoặc: =LAMBDA(x,y,x*y)(3,4) trả về 12.

LAMBDA, MAP, BYROW, BYCOL, SCAN, REDUCE và MAKEARRAY cần Microsoft 365, Excel 2024 hoặc Excel cho web. Excel 2021 có LET nhưng không có các hàm này. Google Sheets cũng có LAMBDA, và lưu nó dưới một tên bằng Data > Named functions.

Lưu LAMBDA thành hàm tự tạo

Một LAMBDA trở nên dùng lại được khi bạn đặt tên cho nó. Excel không cần VBA hay add-in nào cho việc này:

1. Vào Formulas > Name Manager và bấm New (hoặc Formulas > Define Name).
2. Trong Name, nhập tên hàm, ví dụ ADDVAT.
3. Trong Refers to, nhập LAMBDA không kèm đầu vào: =LAMBDA(price,price*1.2).
4. Bấm OK. Giờ hãy nhập =ADDVAT(B2) vào bất kỳ ô nào trong workbook.

Name:        ADDVAT
Refers to:   =LAMBDA(price,price*1.2)
In a cell:   =ADDVAT(B2)        returns 3 when B2 is 2.5

Hàm chỉ tồn tại trong workbook đó. Sao chép một trang tính có dùng hàm sang workbook khác thì tên hàm cũng đi theo. Sửa LAMBDA một lần trong Name Manager và mọi ô gọi nó đều cập nhật. Hãy thử LAMBDA trong một ô với đầu vào trong ngoặc trước khi lưu; ở đó dễ thấy lỗi hơn.

MAP: áp dụng LAMBDA cho từng ô

MAP gọi LAMBDA một lần cho mỗi ô của vùng và trả về một vùng cùng hình dạng. Ở đây mọi mức giá trên 100 được giảm 10%:

Công thứcĐặt lại

C2fx

=MAP(B2:B6,LAMBDA(p,IF(p>100,p*0.9,p)))

	A	B	C
1	Item	Price	Price to pay
2	Pen	2.5	2.5
3	Bag	120	108
4	Lamp	35	35
5	Mug	8	8
6	Desk	150	135

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

Chiếc túi (120) thành 108 và chiếc bàn (150) thành 135; các giá khác giữ nguyên. Một công thức ở C2 xử lý cả cột. MAP còn có thể đi song song qua hai vùng cùng kích thước: với số lượng ở D2:D6, =MAP(B2:B6,D2:D6,LAMBDA(p,q,p*q)) nhân mỗi mức giá với số lượng tương ứng.

BYROW: mỗi hàng một kết quả

BYROW đưa cho LAMBDA cả một hàng mỗi lần, nên LAMBDA có thể dùng MAX, SUM hoặc AVERAGE trên hàng đó. Điểm cao nhất và điểm trung bình của từng học sinh:

Công thứcĐặt lại

E2fx

=BYROW(B2:D5,LAMBDA(r,MAX(r)))

	A	B	C	D	E	F
1	Student	Test 1	Test 2	Test 3	Best	Average
2	Ann	72	85	90	90	82.3
3	Ben	64	70	58	70	64
4	Cara	88	92	95	95	91.7
5	Dan	75	60	81	81	72

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

E2 trả về 90, 70, 95 và 81; F2 trả về 82,3; 64; 91,7 và 72. Một =MAX(B2:D5) thông thường chỉ cho một con số cho cả bảng; BYROW là thứ giữ các hàng tách biệt trong một công thức duy nhất. BYCOL làm điều tương tự theo từng cột: =BYCOL(B2:D5,LAMBDA(c,AVERAGE(c))) trả về điểm trung bình của từng bài kiểm tra.

SCAN và REDUCE: tổng lũy kế

REDUCE đi qua một vùng và mang theo một giá trị, chỉ trả về kết quả cuối cùng. SCAN cũng làm vậy nhưng trả về mọi bước, nên chỉ một công thức là có tổng lũy kế:

Công thứcĐặt lại

C2fx

=SCAN(0,B2:B6,LAMBDA(total,x,total+x))

	A	B	C	D
1	Item	Price	Running total	Total
2	Pen	2.5	2.5	315.5
3	Bag	120	122.5
4	Lamp	35	157.5
5	Mug	8	165.5
6	Desk	150	315.5

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

Đối số đầu tiên, 0, là giá trị khởi đầu. Với mỗi mức giá, LAMBDA nhận tổng tính đến lúc đó cùng mức giá, rồi trả về tổng mới. C2 lần lượt là 2,5; 122,5; 157,5; 165,5; 315,5, còn D2 chỉ hiện kết quả cuối 315,5. Với một tổng đơn giản thì SUM dễ hơn, nhưng REDUCE mang theo được bất cứ thứ gì, như một đoạn văn bản dài dần hay một số đếm chỉ tăng ở vài hàng.

Đặt tên LAMBDA trong một công thức bằng LET

Một LAMBDA không cần Name Manager nếu chỉ một công thức dùng nó. Đặt tên cho nó bằng LET rồi truyền tên đó vào MAP hoặc BYROW:

Công thứcĐặt lại

C2fx

=LET(discount,LAMBDA(p,p*0.9),MAP(B2:B6,discount))

	A	B	C
1	Item	Price	Sale price
2	Pen	2.5	2.25
3	Bag	120	108
4	Lamp	35	31.5
5	Mug	8	7.2
6	Desk	150	135

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

Mọi mức giá được giảm 10%: 2,25; 108; 31,5; 7,2 và 135. Trong Excel bạn cũng có thể gọi trực tiếp LAMBDA đã đặt tên ngay trong LET, =LET(f,LAMBDA(x,x*2),f(5)), trả về 10.

Luyện tập: tổng mỗi hàng với BYROW

Công thứcĐặt lại

E2fx

	A	B	C	D	E
1	Student	Test 1	Test 2	Test 3	Total
2	Ann	72	85	90
3	Ben	64	70	58
4	Cara	88	92	95
5	Dan	75	60	81

Bấm vào một ô để xem công thức. Đổi một số hoặc công thức, bảng tính sẽ tự tính lại.

Đến lượt bạn: Trong E2, trả về tổng ba bài kiểm tra của từng học sinh, mỗi hàng một con số, chỉ với một công thức.

Kiểm traHiện đáp án

Các lỗi thường gặp với LAMBDA

=LAMBDA(x,x*2)          #CALC!   defined but never called
=LAMBDA(x,x*2)(5)       10
=LAMBDA(x,y,x*y)(3)     #VALUE!  two parameters, one value
=LAMBDA(x,x*2)(3,4)     #VALUE!  one parameter, two values

• #CALC! nghĩa là một LAMBDA nằm trong ô mà không được gọi. Thêm đầu vào trong ngoặc, hoặc lưu nó trong Name Manager rồi gọi theo tên.
• #VALUE! nghĩa là số giá trị không khớp với số tham số. Hãy đếm ở cả hai phía.
• #NAME? nghĩa là phiên bản Excel không có LAMBDA, hoặc một tên đã lưu bị gõ sai. Tên tham số theo quy tắc của LET: không có dấu cách, không đặt tên trông giống địa chỉ ô.
• Một LAMBDA trong BYROW trả về nhiều giá trị cho mỗi hàng sẽ cho #CALC!. Mỗi hàng phải tạo ra một giá trị; nếu muốn trả về một hàng kết quả, hãy dùng MAKEARRAY hoặc một công thức mảng thông thường.