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

Mục lục

Tóm lược

Hàm LAMBDA trong Excel cung cấp một cách tạo các hàm tùy chỉnh có thể được sử dụng lại trong toàn bộ sổ làm việc mà không cần VBA hoặc macro.

Mục đích

Tạo chức năng tùy chỉnh

Giá trị trả lại

Theo định nghĩa của công thức

Cú pháp

= LAMBDA (tham số,…, phép tính)

Tranh luận

tham số - Giá trị đầu vào cho hàm.
Phép tính - Phép tính để thực hiện dưới dạng kết quả của hàm. Phải là đối số cuối cùng.

Phiên bản

Excel 365

Ghi chú sử dụng

Trong lập trình máy tính, LAMBDA đề cập đến một hàm hoặc biểu thức ẩn danh. Một hàm ẩn danh là một hàm được định nghĩa mà không có tên. Trong Excel, hàm LAMBDA cung cấp một cách để xác định và đóng gói chức năng công thức cụ thể, giống như một hàm Excel. Sau khi được xác định, một hàm LAMBDA có thể được đặt tên và sử dụng lại ở những nơi khác trong sổ làm việc. Nói cách khác, hàm LAMBDA là một cách để tạo các hàm tùy chỉnh.

Một trong những lợi ích chính của hàm LAMBDA tùy chỉnh là logic có trong công thức chỉ tồn tại ở một nơi. Điều này có nghĩa là chỉ có một bản sao mã để cập nhật khi khắc phục sự cố hoặc cập nhật chức năng và các thay đổi sẽ tự động phổ biến cho tất cả các phiên bản của hàm LAMBDA trong sổ làm việc. Hàm LAMBDA không yêu cầu VBA hoặc macro.

Ví dụ 1 | Ví dụ 2 | Ví dụ 3

Tạo một hàm LAMBDA

Các hàm LAMBDA thường được tạo và gỡ lỗi trong thanh công thức trên trang tính, sau đó được chuyển vào trình quản lý tên để gán một tên có thể được sử dụng ở bất kỳ đâu trong sổ làm việc.

Có bốn bước cơ bản để tạo và sử dụng công thức tùy chỉnh dựa trên hàm LAMBDA:

Xác minh logic bạn sẽ sử dụng với một công thức chuẩn
Tạo và kiểm tra phiên bản LAMBDA chung (không có tên) của công thức
Đặt tên và xác định công thức LAMBDA với trình quản lý tên
Kiểm tra chức năng tùy chỉnh mới bằng cách sử dụng tên đã xác định

Các ví dụ dưới đây thảo luận chi tiết hơn về các bước này.

ví dụ 1

Để minh họa cách LAMBDA hoạt động, hãy bắt đầu với một công thức rất đơn giản:

=x*y // multiple x and y

Trong Excel, công thức này sẽ sử dụng thường sử dụng các tham chiếu ô như sau:

=B5*C5 // with cell references

Như bạn có thể thấy, công thức hoạt động tốt, vì vậy chúng tôi đã sẵn sàng chuyển sang tạo công thức LAMBDA chung (phiên bản chưa được đặt tên). Điều đầu tiên cần xem xét là nếu công thức yêu cầu đầu vào (tham số). Trong trường hợp này, câu trả lời là "có" - công thức yêu cầu giá trị cho x và giá trị cho y. Với điều đó được thiết lập, chúng tôi bắt đầu với chức năng LAMBDA và thêm các tham số bắt buộc cho đầu vào của người dùng:

=LAMBDA(x,y // begin with input parameters

Tiếp theo, chúng ta cần thêm phép tính thực tế, x * y:

=LAMBDA(x,y,x*y)

Nếu bạn nhập công thức tại thời điểm này, bạn sẽ nhận được lỗi #CALC! lỗi. Điều này xảy ra vì công thức không có giá trị đầu vào nào để làm việc, vì không còn bất kỳ tham chiếu ô nào nữa. Để kiểm tra công thức, chúng ta cần sử dụng một cú pháp đặc biệt như sau:

=LAMBDA(x,y,x*y)(B5,C5) // testing syntax

Cú pháp này, trong đó các tham số được cung cấp ở cuối hàm LAMBDA trong một tập hợp các dấu ngoặc đơn riêng biệt, là duy nhất cho các hàm LAMBDA. Điều này cho phép công thức được kiểm tra trực tiếp trên trang tính, trước khi LAMBDA được đặt tên. Trong màn hình bên dưới, bạn có thể thấy rằng hàm LAMBDA chung trong F5 trả về kết quả chính xác giống như công thức ban đầu trong E5:

Bây giờ chúng ta đã sẵn sàng đặt tên cho hàm LAMBDA bằng Trình quản lý tên. Đầu tiên, chọn công thức, * không bao gồm * các tham số thử nghiệm ở cuối. Tiếp theo, mở Trình quản lý tên bằng phím tắt Control + F3 và nhấp vào Mới.

Trong hộp thoại Tên mới, nhập tên "XBYY", để phạm vi được đặt thành sổ làm việc và dán công thức bạn đã sao chép vào vùng nhập "Tham chiếu đến".

Đảm bảo công thức bắt đầu bằng dấu bằng (=). Bây giờ công thức LAMBDA đã có tên, nó có thể được sử dụng trong sổ làm việc giống như bất kỳ hàm nào khác. Trong màn hình bên dưới công thức trong G5, được sao chép xuống, là:

Hàm tùy chỉnh mới trả về kết quả giống như hai công thức còn lại.

Ví dụ 2

Trong ví dụ này, chúng tôi sẽ chuyển đổi một công thức để tính thể tích của một hình cầu thành một hàm LAMBDA tùy chỉnh. Công thức Excel chung để tính thể tích của một hình cầu là:

=4/3*PI()*A1^3 // volume of sphere

trong đó A1 đại diện cho bán kính. Màn hình bên dưới hiển thị công thức này đang hoạt động:

Lưu ý rằng công thức này chỉ yêu cầu một đầu vào (bán kính) để tính thể tích, vì vậy hàm LAMBDA của chúng ta sẽ chỉ cần một tham số (r), tham số này sẽ xuất hiện dưới dạng đối số đầu tiên. Đây là công thức được chuyển đổi thành LAMBDA:

=LAMBDA(r,4/3*PI()*r^3) // generic lambda

Quay lại trang tính, chúng tôi đã thay thế công thức ban đầu bằng phiên bản LAMBDA chung. Lưu ý rằng chúng tôi đang sử dụng cú pháp thử nghiệm, cho phép chúng tôi cắm vào B5 cho bán kính:

Kết quả từ công thức LAMBDA chung hoàn toàn giống với công thức gốc, vì vậy bước tiếp theo là xác định và đặt tên cho công thức LAMBDA này bằng Trình quản lý tên, như đã giải thích ở trên. Tên được sử dụng cho một hàm LAMBDA có thể là bất kỳ tên Excel hợp lệ nào. Trong trường hợp này, chúng tôi sẽ đặt tên cho công thức là "SphereVolume".

Quay lại trang tính, chúng tôi đã thay thế công thức LAMBDA chung (không được đặt tên) bằng phiên bản LAMBDA được đặt tên và nhập B5 cho r. Lưu ý rằng các kết quả được trả về bởi hàm SphereVolume tùy chỉnh giống hệt như các kết quả trước đó.

Ví dụ 3

Trong ví dụ này, chúng ta sẽ tạo một hàm LAMBDA để đếm từ. Excel không có hàm cho mục đích này, nhưng bạn có thể đếm các từ bằng một ô với công thức tùy chỉnh dựa trên các hàm LEN và SUBSTITUTE như sau:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Đọc giải thích chi tiết tại đây. Đây là công thức đang hoạt động trong một trang tính:

Lưu ý rằng chúng tôi nhận được số 1 không chính xác khi công thức được cung cấp một ô trống (B10). Chúng tôi sẽ giải quyết vấn đề này bên dưới.

Công thức này chỉ yêu cầu một đầu vào, đó là văn bản có chứa các từ. Trong hàm LAMBDA của chúng tôi, chúng tôi sẽ đặt tên cho đối số này là "text". Đây là công thức được chuyển đổi thành LAMBDA:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

Lưu ý "văn bản" xuất hiện dưới dạng đối số đầu tiên và phép tính là đối số thứ hai và cuối cùng. Trong màn hình bên dưới, chúng tôi đã thay thế công thức gốc bằng phiên bản LAMBDA chung. Lưu ý rằng chúng tôi đang sử dụng cú pháp thử nghiệm, cho phép chúng tôi cắm vào B5 cho văn bản:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)(B5)

Kết quả từ công thức LAMBDA chung giống với công thức gốc, vì vậy bước tiếp theo là xác định và đặt tên cho công thức LAMBDA này bằng Trình quản lý tên, như đã giải thích trước đó. Chúng tôi sẽ đặt tên cho công thức này là "CountWords".

Dưới đây, chúng tôi đã thay thế công thức LAMBDA chung (không được đặt tên) bằng phiên bản LAMBDA được đặt tên và nhập B5 cho văn bản. Lưu ý rằng chúng tôi nhận được kết quả chính xác như nhau.

Công thức được sử dụng trong Trình quản lý tên để xác định AdWords giống như trên, không có cú pháp kiểm tra:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+1)

Khắc phục sự cố ô trống

Như đã đề cập ở trên, công thức trên trả về số lượng 1 không chính xác khi một ô trống. Sự cố này có thể được khắc phục bằng cách thay thế +1 bằng mã bên dưới:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

Giải thích đầy đủ ở đây. Để cập nhật công thức LAMDA có tên hiện có, chúng ta lại cần sử dụng Trình quản lý tên:

Mở Trình quản lý tên
Chọn tên "CountWords" và nhấp vào "Edit"
Thay thế mã "Tham chiếu đến" bằng công thức này:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+(LEN(TRIM(text))>0))

Sau khi Trình quản lý tên bị đóng, Count AdWords sẽ hoạt động chính xác trên các ô trống, như bên dưới:

Lưu ý: bằng cách cập nhật mã một lần trong Trình quản lý tên, tất cả các phiên bản của công thức AdWords đều được cập nhật cùng một lúc. Đây là lợi ích chính của các chức năng tùy chỉnh được tạo bằng LAMBDA-các bản cập nhật định dạng có thể được quản lý ở một nơi.