Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Thông tin sau dựa trên Microsoft Excel 365 dành cho PC, vì vậy các phiên bản Excel khác có thể hoạt động khác

Để minh họa, hãy tạo LAMBDA để xác định Chi phí trên mỗi đơn vị. Để xác định điều này, hãy chia số lượng cho tổng chi phí (xem ảnh chụp màn hình của bảng bên dưới để biết thêm chi tiết). ) LAMBDA để tính Chi phí trên mỗi đơn vị. Cách tính toán này là lấy Tổng chi phí chia cho Số lượng. (Xem ảnh chụp màn hình của bảng với thông tin này bên dưới. )

Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Chúng tôi đang sử dụng một công thức rất cơ bản trong ví dụ này để không ảnh hưởng đến việc tìm hiểu về LAMBDA, mặc dù phép tính Chi phí trên mỗi đơn vị là một công thức rất đơn giản để tạo và sao chép sang các ô khác. Việc sử dụng hàm LAMBDA để tạo lại các công thức phức tạp hơn sẽ cho thấy tiềm năng đầy đủ của hàm. LAMBDA . Sức mạnh thực sự của hàm LAMBDA sẽ được thể hiện bằng cách sử dụng hàm này để tạo lại các công thức phức tạp hơn.

Bây giờ chúng ta hãy thực hiện các hành động được nêu ở trên. ở trên.

1. Xác minh phương trình Vì đây là một bảng, nên Chi phí trên mỗi đơn vị sẽ xuất hiện trên mỗi hàng và công thức sẽ hoạt động khi tôi nhập =B2/C2 vào ô D2. =B2/C2 . Bởi vì đây là một bảng, Chi phí trên mỗi đơn vị được điền vào mỗi hàng và công thức hoạt động.

Tạo LAMBDA trong bảng tính ở bước hai. Tôi tạo LAMBDA trong ô F2, bạn có thể thay thế bằng bất kỳ ô nào bên ngoài bảng của mình bằng cách nhập =LAMBDA(Tổng chi phí,Số lượng,Tổng chi phí/Số lượng)(B2,C2). Sau đó, chúng tôi xác định tham số đầu tiên là B2 (chỉ dành cho phép tính này để đảm bảo nó hoạt động) và xác định tham số thứ hai là C2 cho các tham số Tổng chi phí, Số lượng và Tổng chi phí. Sau đó, để đảm bảo tính toán chính xác, chúng tôi xác định tham số đầu tiên là B2 và tham số thứ hai là C2. Các chức năng LAMBDA. LAMBDA trong bảng tính. Trong ô F2 (bạn có thể sử dụng bất kỳ ô nào bên ngoài bảng của mình), tôi tạo LAMBDA . Tôi nhập =LAMBDA(Tổng chi phí,Số lượng,Tổng chi phí/Số lượng)(B2,C2). TotalCost là tham số đầu tiên, Số lượng là tham số thứ hai và TotalCost. Sau đó, chúng tôi xác định tham số đầu tiên là B2 (chỉ dành cho phép tính này để đảm bảo rằng nó hoạt động) và xác định tham số thứ hai là C2. là phép tính. Sau đó, chúng tôi xác định tham số đầu tiên là B2 (chỉ dành cho phép tính này để đảm bảo rằng nó hoạt động) và xác định tham số thứ hai là C2. LAMBDA hoạt động.

3. Chỉ định LAMBDA trong Trình quản lý tênĐể tạo một chức năng tùy chỉnh có thể được sử dụng nhiều lần, hãy chuyển đến tab Công thức và chọn Xác định tên từ nhóm Tên đã xác định. Chỉ sao chép LAMBDA mà không có tham chiếu ô cho công thức cụ thể đó. Dán văn bản đã sao chép, sau đó chọn Xác định tên từ nhóm Tên đã xác định trên tab Công thức. Trong hộp Refers to, dán LAMBDA đã sao chép. Trong hộp Tên, hãy nhập tên bạn muốn đặt cho chức năng tùy chỉnh mới của mình. Nếu bạn có bất kỳ nhận xét nào, bạn có thể thêm chúng vào trường Nhận xét; . Điều này đặc biệt hữu ích nếu các tham số của bạn cần được giải thích thêm. LAMBDA trong Trình quản lý tên. Chỉ sao chép LAMBDA không có tham chiếu ô cho công thức cụ thể đó để tạo hàm tùy chỉnh có thể được sử dụng nhiều lần, . Chuyển đến tab Công thức và chọn Xác định Tên từ nhóm Tên được Xác định. Dán đã sao chép. Chuyển đến tab Công thức và chọn Xác định Tên từ nhóm Tên được Xác định. Dán LAMBDA đã sao chép vào phần Tham khảo. hộp. Nhập tên mà bạn muốn gọi chức năng tùy chỉnh mới của mình trong Tên. hộp. Tôi đặt tên cho mình là CostPerUnit. Nếu bạn muốn thêm bất kỳ bình luận nào, bạn có thể thêm chúng trong Bình luận. hộp. Điều này đặc biệt hữu ích nếu các tham số của bạn có thể cần giải thích thêm.

Xem cửa sổ Tên mới đã hoàn thành cho CostPerUnit trong ảnh chụp màn hình bên dưới. CostPerUnit.

Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Với việc bổ sung một hàm tích hợp mới, giờ đây chúng tôi có nhiều tùy chọn hơn để sử dụng Excel. Hãy đặt con trỏ trở lại ô D2, hàng đầu tiên mà chúng ta muốn tính Chi phí trên mỗi đơn vị (xem ảnh chụp màn hình bên dưới), vì hàm tùy chỉnh sẽ chỉ khả dụng trong sổ làm việc nơi nó được tạo, không phải trong tất cả Excel của bạn . Giờ đây, bạn có thể tìm thấy Loại =CostPerUnit được liệt kê cùng với các chức năng tích hợp sẵn khác. Ô B2 và ô C2 tương ứng đại diện cho tham số thứ nhất và thứ hai cho lựa chọn này. =CostPerUnit(B2,C2) hoặc =CostPerUnit([@[Tổng chi phí]],[@Số lượng]), nếu dữ liệu của bạn được chứa trong một ma trận, thì công thức cuối cùng phải là. Các giá trị được tạo bởi chức năng mới mà chúng tôi đã phát triển được hiển thị trong ảnh chụp màn hình bên dưới. được xây dựng - trong chức năng. Chúng ta có thể sử dụng nó giống như bất kỳ hàm xây dựng - trong Excel nào khác. Hàm tùy chỉnh sẽ chỉ khả dụng trong sổ làm việc nơi nó được tạo, không có trong tất cả các sổ làm việc Excel của bạn. Hãy đặt con trỏ trở lại ô D2, hàng đầu tiên mà chúng tôi muốn tính Chi phí cho mỗi đơn vị (xem ảnh chụp màn hình bên dưới). Loại =CostPerUnit . Bạn thấy nó được liệt kê trong số các hàm xây dựng - in khác ngay bây giờ. Chọn nó và xác định các tham số. Tham số đầu tiên là ô B2 và tham số thứ hai là ô C2. Công thức cuối cùng sẽ trông như thế này. =CostPerUnit(B2,C2) hoặc =CostPerUnit([@[Total Cost]],[@Quantity] ), nếu dữ liệu của bạn được chứa trong một. Xem ảnh chụp màn hình bên dưới để biết các giá trị kết quả của hàm mới mà chúng tôi đã tạo.

Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Tôi đã tạo sổ làm việc có video và ví dụ về cách sử dụng LAMBDA. sổ bài tập có ví dụ về cách sử dụng LAMBDA và video đi kèm.

Tôi khuyên bạn nên phát triển các hàm độc đáo có thể sao chép các công thức phức tạp hơn mà bạn thường sử dụng. thông minh.


Thông tin về các Tác giả

CPA và phù hợp với Kelly LO. WilliamsD. , phó giáo sư kế toán tại Đại học Kinh doanh Jones thuộc Đại học Bang Middle Tennessee, có bằng MBA


gửi câu hỏi

Bạn có bất kỳ câu hỏi nào liên quan đến công nghệ cho cột này không? Chúng tôi xin lỗi vì không thể trả lời từng câu hỏi riêng lẻ

Với phiên bản Microsoft 365, bạn có thể tạo các hàm Excel tùy chỉnh bằng hàm LAMBDA và các dải ô được đặt tên

Trình bày hàm LAMBDA

Chức năng LAMBDA được giới thiệu vào tháng 11 năm 2020 dành cho những người làm việc với phiên bản Microsoft 365

Hàm LAMBDA bao gồm một loạt các biến, lên tới 253 biến, sẽ được sử dụng bởi biểu thức nhất thiết phải là đối số cuối cùng của hàm

Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA
Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Trong ví dụ này, chúng ta có 2 biến, x và y, và công thức (hoặc biểu thức) x+y. Khi bạn gọi hàm này, bạn sẽ chuyển các giá trị của x và y làm đối số và LAMDBA sẽ trả về kết quả của biểu thức

Cách viết hàm LAMBDA

Vấn đề với hàm LAMBDA là bạn không thể viết nó trực tiếp trong một ô giống như bất kỳ hàm Excel nào khác

Nếu bạn viết LAMBDA trong một ô, Excel sẽ trả về lỗi #CALC

Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA
Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Trên thực tế, chức năng này mong muốn nhận được các tham số. Vì vậy chúng ta phải tìm một thủ thuật để truyền cho nó các giá trị cho các biến của hàm

Viết LAMBDA dưới dạng phạm vi được đặt tên

Cách duy nhất để sử dụng LAMBDA là viết nó dưới dạng một dải ô được đặt tên Công thức > Xác định tên (hoặc Trình quản lý tên)

Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA
Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA
  1. Đặt tên cho chức năng tùy chỉnh của bạn
  2. Sau đó viết hàm LAMBDA của bạn vào hộp Tham chiếu đến
Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA
Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Bây giờ, để kiểm tra xem hàm tùy chỉnh của bạn có được Excel hiểu hay không, hãy viết ký hiệu bằng (giống như bất kỳ hàm Excel nào khác) và tên hàm LAMBDA của bạn

Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA
Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Và sau đó, bạn phải chuyển dữ liệu mà hàm tùy chỉnh mong đợi dưới dạng tham số

=MyCustomFunction(2,5)

Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA
Tạo các hàm Excel tùy chỉnh của riêng bạn với LAMBDA

Trường hợp thực tế sử dụng hàm LAMBDA

Ví dụ: bạn có thể tạo một hàm LAMBDA tùy chỉnh để tính ngày lễ Phục sinh như được giải thích trong bài viết này

Sự cố tôi đang gặp phải. Bất cứ khi nào tôi thêm công thức Lambda vào một ô, nó sẽ yêu cầu tất cả người dùng khác trong bảng tính được chia sẻ mở lại tệp

Tại nơi làm việc của chúng tôi, tất cả chúng tôi đều sử dụng một bảng tính trung tâm, trong số những thứ khác, nó xác định ngày đặt hàng (tôi là người mua)
Tôi đã tạo một công thức Lambda lấy ngày bắt đầu sản xuất, tính toán thời gian đặt hàng dựa trên thời gian giao hàng cho thời điểm đó của sản phẩm và đảm bảo ngày đặt hàng không rơi vào ngày cuối tuần hoặc ngày lễ theo luật định (sử dụng hàm đệ quy trong

Vì vậy, trong các ô nhận ngày đặt hàng, tôi có "=orderDate" rất hay và dễ sử dụng
orderDate (ngày được tính không phải là ngày cuối tuần hoặc ngày lễ thống kê) =IF(SFD="","",recurse(sub,daysOff))
SFD (ngày bắt đầu fab) = giá trị ngày từ một cột khác trong cùng một hàng
recurse (công thức ngăn ngày đặt hàng là ngày cuối tuần) =LAMBDA(x,y,IF(OR(x=y,WEEKDAY(x)=1,WEEKDAY(x)=7,recurse(x-1,y),
phụ (ngày bắt đầu fab trừ đi thời gian chờ) =LAMBDA(x,y,IFERROR(x-y,HÔM NAY()))(SFD,thời gian chờ)
daysOff (cột chứa tất cả các ngày lễ thống kê trong năm tiếp theo) =OFFSET(daysOff. $A$1,,,COUNTA(ngày nghỉ. $A. $A),1)
thời gian chờ (lấy các thời gian chờ khác nhau từ bảng thời gian chờ thay đổi dựa trên cột chứa công thức) =INDEX(Bảng thời gian chờ [Thời gian chờ],COLUMN()-14)

Bất cứ khi nào công thức "=orderDate" được nhập/dán/viết bằng VBA vào một ô, nó sẽ buộc mọi người dùng khác phải mở lại bảng tính
Đây có phải là "tính năng" mà tôi có thể mong đợi với BẤT KỲ công thức Lambda nào trong excel không?

Đáp lại

Làm cách nào để tạo hàm LAMBDA trong Excel?

Tạo hàm LAMBDA() trong Excel khá đơn giản. Sử dụng LAMBDA(), bạn nhập các tham số và đối số tính toán bằng các biến. Sử dụng tính năng Xác định tên của Excel, bạn đặt tên cho hàm và nhập hàm LAMBDA() , thế là xong.

Làm cách nào để tạo một hàm tùy chỉnh trong Excel VBA?

Các cách khác nhau để sử dụng hàm do người dùng xác định trong Excel .
Chuyển đến tab Dữ liệu
Nhấp vào tùy chọn 'Chèn chức năng'
Trong hộp thoại Chèn Chức năng, chọn Người dùng Xác định làm danh mục. .
Chọn chức năng từ danh sách tất cả các chức năng do người dùng công khai xác định
Nhấp vào nút Được rồi

Bạn có thể tạo các hàm Excel của riêng mình không?

Một hàm tùy chỉnh phải bắt đầu bằng câu lệnh Hàm và kết thúc bằng câu lệnh Kết thúc hàm. Ngoài tên hàm, câu lệnh Hàm thường chỉ định một hoặc nhiều đối số. Tuy nhiên, bạn có thể tạo một hàm không có đối số .