Hàm LAMBDA trong Excel
Hàm LAMBDA trong Excel

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 theo kết quả của hàm. Phải là đối số cuối cùng.
Phiên bản
Ghi chú sử dụng

Hàm LAMBDA cung cấp một cách để tạo một hàm tùy chỉnh trong Excel. Sau khi được xác định và đặt tên, một hàm LAMBDA có thể được sử dụng ở bất kỳ đâu trong sổ làm việc. Các hàm LAMBDA có thể rất đơn giản hoặc khá phức tạp, xâu chuỗi nhiều hàm Excel lại với nhau trong một công thức. Một hàm LAMBDA tùy chỉnh không yêu cầu VBA hoặc macro.

ví dụ 1 | Ví dụ 2 | Ví dụ 3 | Các ví dụ khác

Trong lập trình máy tính, thuật ngữ 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 lần đầu tiên được sử dụng để tạo một công thức chung (không có tên). Sau khi một phiên bản chung đã được tạo và thử nghiệm, nó sẽ được chuyển đến Name Manager, nơi nó được chính thức xác định và đặt tên.

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 truyền cho tất cả các phiên bản của chức năng LAMBDA trong sổ làm việc.

Bạn sẽ thấy Hàm LET trong các công thức LAMBDA phức tạp hơn. LET cung cấp cách khai báo biến và gán giá trị trong công thức. Điều này làm cho các công thức phức tạp hơn dễ đọc hơn, đơn giản hóa công thức bằng cách giảm mã thừa và cải thiện hiệu suất.

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 một hàm LAMBDA tùy chỉnh:

  1. Xác minh logic bạn sẽ sử dụng với một công thức chuẩn
  2. Tạo và kiểm tra phiên bản LAMBDA chung (không tên) của công thức
  3. Đặt tên và xác định công thức LAMBDA với trình quản lý tên
  4. Gọi hàm tùy chỉnh mới với 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 – ví dụ cơ bản

Để 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

Phiên bản công thức tiêu chuẩn

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 một giá trị cho x và một 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:

Phiên bản lambda chung (không tên)

Bây giờ chúng tôi đã sẵn sàng đặt tên cho hàm LAMBDA với Người quản lý tên. Đầu tiên, hãy 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 + F3và nhấp vào Mới.

Nhấp vào Mới trong Trình quản lý tên

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

Đặt tên và xác định LAMBDA trong trình quản lý tê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à:

Chức năng LAMBDA được đặt tên đang hoạt động

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 – thể tích khối cầu

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. Tổng thể Công thức Excel để 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:

Công thức Excel tiêu chuẩn cho thể tích hình cầu

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:

Công thức LAMBDA chung (không tên) cho thể tích hình cầu

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 Người 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 đó.

Công thức LAMBDA được đặt tên cho thể tích khối cầu

Ví dụ 3 – đếm từ

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 LENSUBSTITUTE các chức năng như thế này:

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

Công thức chuẩn để đếm từ

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)

Chú ý “văn bản” xuất hiện dưới dạng Đầu tiên đối số, và phép tính là thứ hai và sau cùng tranh luận. Trong màn hình bên dưới, 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 văn bản:

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

LAMBDA chung để đếm từ

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 Người quản lý tên, như đã giải thích trước đây. 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 đặt tên là LAMBDA để đếm từ - CountWords

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 thử nghiệm:

=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ố 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 tôi lại cần sử dụng Trình quản lý tên:

  1. Mở Trình quản lý tên
  2. Chọn tên “CountWords” và nhấp vào “Edit”
  3. 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ư được thấy bên dưới:

Sau khi cập nhật CountWords trong Name Manager

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 trường hợp của công thức Count AdWords được cập nhật ngay lập tứ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 formula có thể được quản lý ở một nơi.

https://exceljet.net/excel-functions/excel-lambda-function