1. Hàm LAMBDA trong Excel

Hàm LAMBDA trong Excel được thiết kế để tạo các hàm tùy chỉnh có thể được gọi bằng tên thân thiện với người dùng và được sử dụng lại trong toàn bộ sổ làm việc.

Nói một cách đơn giản, bây giờ bạn có thể lấy bất kỳ công thức hiện có nào, bất kể nó phức tạp như thế nào, gói gọn nó trong LAMBDA và đặt cho nó bất kỳ tên nào bạn thích, chẳng hạn như BestFunction. Và sau đó, thay vì nhập công thức dài ban đầu, bạn có thể tham khảo BestFunction() ở bất kỳ đâu trong sổ làm việc của mình.

Chức năng LAMBDA hoạt động mà không cần macro, VBA hoặc JavaScript, vì vậy mọi người dùng chứ không chỉ lập trình viên đều có thể hưởng lợi từ chức năng này.

2. Lamda có nghĩa là gì?

Ký hiệu lambda (λ) đại diện cho chữ cái thứ 11 trong bảng chữ cái Hy Lạp thường được sử dụng trong vật lý, toán học và khoa học máy tính, nơi nó mang các ý nghĩa khác nhau.

Trong khoa học máy tính, khái niệm lambda bắt nguồn từ phép tính lambda (λ-phép tính) được giới thiệu bởi nhà toán học và logic học người Mỹ Alonzo Church vào những năm 1930. Về cơ bản, nó là một mô hình tính toán phổ quát, trong đó tất cả các chức năng được coi là ẩn danh và có thể được hình thành bằng cách trừu tượng hóa.

Microsoft đã công bố LAMBDA vào tháng 12 năm 2020 và tự hào tuyên bố rằng hàm mới giúp Excel trở nên hoàn chỉnh (tức là phổ biến về mặt tính toán) và cho phép người dùng thực hiện hầu hết mọi phép tính bằng ngôn ngữ công thức gốc. Làm thế nào là có thể? Do một hàm do LAMBDA xác định có thể gọi các hàm khác hoặc thậm chí chính nó nhiều lần nếu cần. Tính năng này được gọi là "đệ quy" và đây là điều làm cho LAMBDA trở nên hiệu quả. Trước đó, đệ quy trong Excel chỉ có thể thông qua VBA hoặc Office Script.

3. Cú pháp

Hàm LAMBDA trong Excel có cú pháp và đối số như sau:

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

Parameter (tùy chọn) - một giá trị đầu vào có thể được cung cấp ở dạng tham chiếu ô, số hoặc chuỗi văn bản. Hàm chấp nhận tối đa 253 tham số.

Calculation (bắt buộc) - công thức để thực hiện hoặc tính toán để thực hiện. Nó phải là đối số cuối cùng và nó phải trả về kết quả.

Đây là một ví dụ về hàm LAMBDA tùy chỉnh ở dạng đơn giản nhất:

4. Ghi chú sử dụng

Các hướng dẫn bên dưới sẽ giúp bạn tăng cơ hội xây dựng một chức năng tùy chỉnh hoạt động hoàn hảo ở lần thử đầu tiên và giúp tránh các lỗi phổ biến:

- Khi đặt tên hàm LAMBDA và các tham số của chúng, hãy đảm bảo tuân thủ các quy tắc đặt tên tiêu chuẩn của Excel.

- Không được sử dụng dấu chấm (.) trong tên tham số.

- Đối với các tham số, không sử dụng tên có thể gây nhầm lẫn với tham chiếu ô. Ví dụ: thay vì val1 khớp với ô VAL1, hãy sử dụng val_1 hoặc value1.

- Như với bất kỳ hàm sẵn có nào, hãy làm theo các phương pháp hay nhất để viết công thức: cung cấp số lượng đối số chính xác, khớp mở và đóng dấu ngoặc đơn, v.v.

5. Phiên bản Excel nào có hàm LAMBDA?

Hàm LAMBDA chỉ sẵn dùng trong các đăng ký Microsoft 365 bao gồm:

- Excel 365 cho Windows.

- Excel 365 cho Mac.

- Excel cho web.

6. Cách viết LAMBDA trong Excel

Để tạo một hàm Lambda trong sổ làm việc của bạn, đây là các bước cần thực hiện:

a. Bước 1: Xây dựng công thức cốt lõi

Trong hầu hết các trường hợp, bạn bắt đầu bằng việc viết một công thức cốt lõi trả về kết quả mong muốn. Để tập trung vào quá trình tạo LAMBDA, chúng ta sẽ giữ logic của công thức rất đơn giản.

Ví dụ, hãy lấy công thức phương sai phần trăm cổ điển:

=new_value / old_value – 1

Bạn sẽ tạo ra công thức gốc như hình bên dưới.

b. Bước 2: Tạo và kiểm tra công thức LAMBDA trong một ô

Nếu công thức của bạn yêu cầu giá trị đầu vào, hãy thêm chúng làm tham số cho hàm LAMBDA. Công thức mẫu của chúng tôi tính phần trăm thay đổi giữa 2 số, vì vậy chúng tôi khai báo 2 tham số:

=LAMBDA(old, new

Tiếp theo, thêm công thức vào đối số tính toán. Xin lưu ý rằng thay vì tham chiếu ô, chúng tôi cung cấp các tham số đã khai báo:

=LAMBDA(old, new, IFERROR(new/old-1, "-"))

Nếu được nhập vào một ô tại thời điểm này, công thức của chúng tôi sẽ trả về lỗi #CALC! lỗi vì nó không có giá trị để xử lý. Đối với mục đích thử nghiệm, bạn nên cung cấp các giá trị đầu vào trong một bộ dấu ngoặc đơn bổ sung sau công thức:

=LAMBDA(old, new, IFERROR(new/old-1, "-"))(B2, C2)

Cú pháp thử nghiệm này cho phép gọi hàm LAMBDA từ bên trong một ô trước khi đặt tên cho nó:

=LAMBDA([param1, param2, …], calculation) (function call)

Ảnh chụp màn hình bên dưới chứng minh rằng kết quả do LAMBDA trả về không khác với công thức ban đầu:

c. Bước 03: Đặt tên cho hàm LAMBDA

Sau khi thử nghiệm thành công, bạn đã sẵn sàng đặt tên cho LAMBDA của mình. Đây là cách mà chúng ta sẽ làm.

- Sao chép công thức LAMBDA mà không cần gọi hàm ở cuối. Trong ví dụ của chúng tôi, đó là:

=LAMBDA(old, new, IFERROR(new/old-1, "-"))

- Mở Name Manager bằng cách nhấn phím tắt Ctrl + F3.

- Trong hộp thoại Name Manager hãy ấn vào New.

- Trong cửa sổ New Name hãy tiếp tục làm như sau.

            + Trong hộp thoại Name, bạn hãy gõ vào tên Function. Chú ý là bạn nên đặt tên ngắn và thực sự dễ hiểu thôi nhé.

            + Đặt Scope thành Workbook (mặc định).

            + Trong hộp thoại Refers, hãy dán công thức đã sao chép, đảm bảo công thức bắt đầu bằng dấu bằng.

            + Nhấn OK để lưu tên vừa tạo.

Mẹo: Để chỉnh sửa công thức trong hộp Refers to, nhấn phím F2 để chuyển từ Enter sang chế độ Edit. Điều này sẽ cho phép bạn điều hướng qua công thức bằng cách sử dụng các phím mũi tên mà không phá vỡ nó.

7. Cách sử dụng hàm LAMBDA trong Excel

Ngay sau khi hàm LAMBDA của bạn có tên, bạn có thể tham khảo nó giống như bất kỳ hàm gốc nào. Lambda của chúng ta có tên là PercentVar và nó yêu cầu 2 đối số - giá trị cũ và giá trị mới:

8. Các ví dụ sử dụng hàm LAMBDA trong Excel

Bây giờ bạn đã biết khái niệm cơ bản về LAMBDA trong Excel, hãy thảo luận thêm một vài ví dụ về công thức để thực sự hiểu rõ về nó.

8.1. Ví dụ 01: Sử dụng hàm LAMBDA để thu gọn các công thức dài trong Excel

LAMBDA là 1 hàm lý tưởng để tối ưu hóa các công thức dài khó hiểu trong Excel.

Ví dụ: để lấy số từ bất kỳ vị trí nào trong chuỗi, bạn có thể sử dụng công thức khó hiểu này.

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

Kết quả hoàn toàn chính xác nhưng không phải ai cũng có thể viết công thức như trên đúng không nào.

Khi xem xét kỹ hơn, bạn có thể nhận thấy rằng công thức chỉ yêu cầu một giá trị đầu vào (chuỗi gốc trong A2). Vì vậy, không có gì có thể ngăn chúng tôi dễ dàng chuyển đổi nó thành hàm LAMBDA tùy chỉnh:

- Đầu tiên, chúng ta khai báo tham số chuỗi, là chuỗi ban đầu để trích xuất các số.

- Thứ hai, chúng tôi thay thế tất cả các phiên bản của A2 bằng chuỗi là được.

=LAMBDA(text, IF(SUM(LEN(text)-LEN(SUBSTITUTE(text, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0& text, LARGE(INDEX(ISNUMBER(--MID(text, ROW(INDIRECT("$1:$"&LEN(text))),1))* ROW(INDIRECT("$1:$"&LEN(text))),0), ROW(INDIRECT("$1:$"&LEN(text))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(text)))/10),""))

Khi bạn đã chắc chắn rằng hàm LAMBDA hoạt động chính xác như công thức ban đầu (đối với điều này, hãy sử dụng cú pháp kiểm tra được mô tả trong phần trước), hãy chuyển đến Trình quản lý tên để xác định một số tên hay cho hàm, chẳng hạn như ExtractNumber.

Thì đấy, bây giờ bạn đã có một giải pháp ngắn gọn và tinh tế để lấy các số từ bất kỳ đâu trong một chuỗi:

8.2. Hàm LAMBDA với nhiều tham số

Đối với ví dụ này, chúng ta sẽ tạo một hàm tùy chỉnh để tính tốc độ tăng trưởng gộp hàng năm dựa trên công thức CAGR (Compound Annual Growth Rate – Tỷ lệ tăng trưởng kép hàng năm) chung này:

=(End_val/Begin_val)^(1/periods)-1

Yêu cầu 3 giá trị đầu vào:

- begin_val - Giá trị ban đầu của khoản đầu tư

- end_val - Giá trị cuối cùng của khoản đầu tư

- periods - Số kỳ

Vì vậy công thức gốc ở bên trên sẽ được chuyển sang công thức dạng LAMBDA như sau:

=LAMBDA(begin_val, end_val, periods, (end_val/begin_val)^(1/periods)-1)

Sau khi kiểm tra, chúng ta vào Name Manager để đặt tên cho hàm CAGR của mình. Và ở đây, thật hợp lý khi để lại một số mẹo cho chính bạn và đặc biệt là cho những người dùng khác trong sổ làm việc của bạn, giải thích từng tham số là gì. Phần Nhận xét là một nơi hoàn hảo cho việc này.

Trường hợp bạn muốn xuống dòng trong phần Comment thì bạn hãy sử dụng phím tắt là Ctrl J nhé.

Và dưới đây là kết quả, công thức hoạt động hoàn toàn trơn tru và hoàn hảo.

Tuy nhiên, trong suy nghĩ thứ hai, có vẻ như tham số N không thực sự cần thiết trong trường hợp của chúng tôi. Nếu tất cả các khoản đầu tư được liệt kê trong một cột như trong hình trên, thì chúng ta có thể tự động tính số kỳ với sự trợ giúp của hàm ROW:

=(end_val/begin_val)^(1/(ROW(end_val)-ROW(begin_val)))-1

Kết quả là Lambda của chúng ta dài hơn một chút nhưng loại bỏ được một đối số thừa:

=LAMBDA(begin_val, end_val, (end_val/begin_val)^(1/(ROW(end_val)-ROW(begin_val)))-1)

Và bây giờ, bạn chỉ cần cung cấp giá trị đầu và cuối của khoản đầu tư:

=CAGR(B2, B7)

8.3. Sử dụng hàm LAMBDA với mảng động (Dynamic Array)

Vì hàm LAMBDA được thiết kế cho Excel 365, đôi khi được gọi là Dynamic Array Excel, nên hai tính năng này kết hợp với nhau một cách tuyệt vời.

Để xem nó hoạt động như thế nào trong thực tế, hãy xác định một hàm tùy chỉnh để sắp xếp danh sách theo số lượng mục. Đối với điều này, chúng ta sẽ sử dụng hàm SORTBY kết hợp với COUNTIF và UNIQUE.

Giả sử các mục được sắp xếp nằm trong C2:C85, công thức sẽ có dạng như sau:

=SORTBY(UNIQUE(C2:C85), COUNTIF(C2:C85, UNIQUE(C2:C85)), -1)

Logic khá dễ hiểu:

Đầu tiên, chúng tôi lấy tất cả các mục duy nhất từ ​​danh sách ban đầu: UNIQUE(C2:C85). Mảng các giá trị duy nhất này chuyển đến đối số đầu tiên của SORTBY (mảng).

Tiếp theo, chúng tôi đếm số lần mỗi mục xuất hiện trong danh sách ban đầu: COUNTIF(C2:C85, UNIQUE(C2:C85)). Những số lượng này chuyển đến đối số thứ 2 (by_array).

Đối với đối số thứ 3 (sort_order), chúng ta sử dụng -1 để sắp xếp giảm dần.

Do đó, công thức SORTBY của chúng tôi sắp xếp danh sách các mục duy nhất theo số lượng mục và sắp xếp kết quả từ cao nhất đến thấp nhất.

Với sự trợ giúp của công thức này, chúng tôi có được danh sách những người chiến thắng Grand Slam quần vợt trong thế kỷ 21 được sắp xếp theo số trận thắng. Để xác minh kết quả, bạn có thể trả lại số trận thắng cho mỗi nhà vô địch bằng cách sử dụng công thức sau:

=COUNTIF(C2:C85, E2#)

Trong trường hợp bạn muốn kết hợp 2 công thức trên thành 1 thì bạn có thể sử dụng hàm HSTACK như sau.

=HSTACK(SORTBY(UNIQUE(C2:C85),COUNTIF(C2:C85,UNIQUE(C2:C85)),-1),COUNTIF(C2:C85,SORTBY(UNIQUE(C2:C85),COUNTIF(C2:C85,UNIQUE(C2:C85)),-1)))

Bạn có thể thấy tham số đầu vào ở đây chỉ sử dụng đúng List C2:C85 là List các vận động viên chiến thắng các giải qua các năm (C2:C85)

Vì thế giờ ta sẽ sử dụng hàm LAMBDA để thu gọn công thức trên như sau.

=LAMBDA(list,HSTACK(SORTBY(UNIQUE(list),COUNTIF(list,UNIQUE(list)),-1),COUNTIF(list,SORTBY(UNIQUE(list),COUNTIF(list,UNIQUE(list)),-1))))

Giờ công thức của chúng ta đã trở nên siêu đơn giản rồi đúng không nào.

Để trả về một số mục hạn chế, chẳng hạn như top 3, top 5 hoặc top 10, bạn có thể bọc công thức SORTBY trong hàm CHOOSEROWS và sử dụng hằng số mảng như {1;2;3} hoặc hàm SEQUENCE hoặc ROW ở dạng mảng để xác định kích thước của mảng đầu ra.

=LAMBDA(list,topN,CHOOSEROWS(HSTACK(SORTBY(UNIQUE(list),COUNTIF(list,UNIQUE(list)),-1),COUNTIF(list,SORTBY(UNIQUE(list),COUNTIF(list,UNIQUE(list)),-1))),SEQUENCE(topN)))

9. Cách xuất/nhập LAMBDA sang File khác.

Giống như mọi thứ được xác định trong Trình quản lý tên của Excel, LAMBDA bị giới hạn trong cửa sổ làm việc mà nó được tạo.

May mắn thay, có một cách khá dễ dàng để chuyển LAMBDA sang sổ làm việc khác. Bạn chỉ cần sao chép một trang tính trắng từ sổ làm việc cũ sang sổ làm việc mới. Vì hàm đã được lưu trong phạm vi của Sổ làm việc, nên nó hoàn toàn di chuyển với bất kỳ trang tính nào mà bạn sao chép hoặc di chuyển.

Xin lưu ý rằng phương pháp này xuất hoàn toàn tất cả các hàm LAMBDA tồn tại trong sổ làm việc gốc.

Hy vọng rằng những ví dụ này đã truyền cảm hứng cho bạn tìm kiếm cách sử dụng LAMBDA của riêng bạn trong Excel. Bây giờ, hãy để tôi tóm tắt ngắn gọn những điểm chính.

9.1. 3 lợi ích tuyệt vời của hàm LAMBDA

Nếu bạn vẫn còn nghi ngờ liệu hàm LAMBDA có phải là thứ bạn thực sự cần trong sổ làm việc của mình hay không, đây là ba lý do thuyết phục để bắt đầu sử dụng nó:

- Thay vì các công thức cồng kềnh, khó đọc, bạn sử dụng các hàm nhỏ gọn và thanh lịch với các tên mô tả mà bạn chọn.

- Thay vì cập nhật mọi công thức trong sổ làm việc, bạn chỉnh sửa hàm Lambda của mình ở một nơi (Name Manager) - một cải tiến lớn sẽ giúp bạn tiết kiệm rất nhiều thời gian!

- Nhiều tác vụ phức tạp trước đây chỉ có thể được giải quyết bằng VBA giờ đây có thể được thực hiện bằng các công thức. Điều này có nghĩa là bạn không cần phải lưu các sổ làm việc như vậy dưới dạng tệp .xlsm đã bật macro cũng như không bận tâm đến việc bật macro. Điều này hầu như có thể thực hiện được nhờ Lambda ở dạng đệ quy.

9.2. 3 hạn chế lớn nhất của LAMBDA

Ở đây, chúng tôi sẽ đề cập đến những nhược điểm cơ bản nhất:

- LAMBDA không tương thích ngược. Nó chỉ có sẵn trong Excel 365 và sẽ không hoạt động trong các phiên bản cũ hơn.

- Các hàm lambda dành riêng cho sổ làm việc và không thể sử dụng lại trên các sổ làm việc khác nhau. Điều này có thể gây nhầm lẫn trong trường hợp khi, trong các tệp khác nhau, bạn tạo các Lambda có cùng tên nhưng hơi khác về cú pháp và chúng tạo ra các kết quả khác nhau cho cùng một dữ liệu đầu vào.

- Để chuyển một hàm do LAMBDA xác định từ sổ làm việc này sang sổ làm việc khác, bạn có thể sao chép bất kỳ trang tính nào từ sổ làm việc có chứa LAMBDA quan tâm. Sau đó, bạn có thể xóa trang tính đã sao chép, nhưng chức năng LAMBDA sẽ vẫn còn trong Trình quản lý tên. Xin lưu ý rằng phương pháp này sao chép tất cả LAMBDA từ sổ làm việc gốc ngay cả khi trang tính được sao chép không chứa một tham chiếu LAMBDA nào.

10. Hàm LAMBDA của Excel không hoạt động

Nếu bạn đang gặp sự cố khi xác định Lambda hoặc công thức của bạn báo lỗi, thông tin sau đây có thể giúp bạn xác định nguyên nhân và khắc phục.

10.1. Lỗi #NAME!

Có thể xảy ra vì những lý do sau:

- Phiên bản Excel của bạn không hỗ trợ LAMBDA - hiện tại phiên bản này chỉ sẵn dùng cho người dùng Microsoft 365. Nếu bạn có đăng ký Microsoft 365, hãy đảm bảo rằng phiên bản Office mới nhất đã được cài đặt trên máy tính của bạn.

- Tên của hàm Lambda tùy chỉnh của bạn bị nhập sai trong một ô.

10.2. Lỗi #VALUE!

Có thể được gây ra bởi một trong những vấn đề sau:

- Khi viết một hàm Lambda, các tên được sử dụng trong tính toán không khớp với các tham số đã khai báo.

- Khi nhập công thức vào một ô, bạn đã chỉ định sai số lượng đối số - hãy kiểm tra kỹ cú pháp của hàm Lambda trong Name Manager.

- Ít có khả năng hơn 253 tham số được khai báo. (Tôi không thể tưởng tượng được một chức năng như vậy, nhưng về lý thuyết thì điều này có thể xảy ra)

10.3. Lỗi #NUM!

Xảy ra do lệnh gọi vòng LAMBDA từ bên trong chính nó, ví dụ: khi hàm Lambda đệ quy không có cách thoát khỏi vòng lặp.

10.4. Lỗi #CALC!

Có thể được kích hoạt khi tạo hàm LAMBDA mới trong ô mà không cung cấp giá trị đầu vào để kiểm tra

Bạn có thể xem lại đầu bài viết mình đã hướng dẫn cách đưa tham số vào để test hàm LAMBDA ngay trong Excel.

10.5. Tên tham số không hợp lệ

Khi một hàm LAMBDA có vẻ đơn giản mà bạn đang tạo không thành công, vấn đề có thể nằm ở tên tham số không hợp lệ có thể gây nhầm lẫn với tham chiếu ô. Trong những trường hợp như vậy, Excel sẽ đánh dấu các tham số như được hiển thị trong ảnh chụp màn hình bên dưới và đưa ra lỗi Bạn đã nhập quá ít đối số cho hàm này:

Vấn đề là các chuỗi num1 và num2 khớp với địa chỉ ô NUM1 và NUM2, do đó Excel không chấp nhận chúng làm tên tham số. Khi bạn đổi tên, chẳng hạn như thành num_1 và num_2, lỗi sẽ biến mất và hàm LAMBDA bắt đầu hoạt động như mong đợi:

Bạn có thể download file đính kèm tại địa chỉ sau: