Hàm Lambda trong Excel: hướng dẫn đầy đủ và ví dụ thực tế

Informatec Digital » Recursos » Hàm Lambda trong Excel: hướng dẫn đầy đủ và ví dụ thực tế

Hàm Lambda trong Excel đã cách mạng hóa cách chúng ta làm việc với các công thức trong bảng tính Microsoft. Nó cho phép bạn tạo các hàm tùy chỉnh của riêng mình chỉ bằng ngôn ngữ công thức của Excel, mà không cần viết một dòng mã VBA hay lập trình truyền thống nào . Điều này giống như việc bạn có khả năng thêm các hàm gốc mới, được thiết kế tùy chỉnh vào chương trình.

Hơn nữa, một số hàm liên quan đã xuất hiện xung quanh LAMBDA, chẳng hạn như BYROW, BYCOL, MAP, SCAN, REDUCE và MAKEARRAY , được thiết kế để làm việc với các phạm vi và ma trận một cách linh hoạt và mạnh mẽ hơn nhiều. Nói một cách tổng quát, các hàm này hoạt động như những vòng lặp nhỏ lặp qua dữ liệu và áp dụng phép biến đổi được định nghĩa bởi LAMBDA, mở ra vô số khả năng phân tích nâng cao trực tiếp trên bảng tính.

Hàm LAMBDA trong Excel là gì và nó được sử dụng để làm gì?

Hàm Lambda là một công cụ cho phép bạn định nghĩa các hàm tùy chỉnh chỉ bằng các công thức Excel. Thay vì lập trình bằng VBA hoặc dựa vào macro, bạn có thể gói gọn bất kỳ phép tính phức tạp nào vào một hàm duy nhất, có thể tái sử dụng, với các tham số riêng và kết quả cuối cùng rõ ràng, mạch lạc.

Trên thực tế, hàm LAMBDA biến bất kỳ công thức nào thành một hàm mà bạn có thể sử dụng lại nhiều lần tùy ý. Ưu điểm chính của nó là tích hợp liền mạch với phần còn lại của công cụ tính toán của Excel và có thể được kết hợp với các hàm chuẩn, tham chiếu, tên được định nghĩa và mảng động.

Cú pháp cơ bản khi sử dụng trực tiếp trong một ô là:

=LAMBDA(parameter1; parameter2; …; parameterN; calculation)(value1; value2; …; valueN)

Trong cấu trúc này, parameter1, parameter2, …, parameterN là tên bạn đặt cho các biến trong hàm, trong khi calculation là công thức sử dụng các tham số đó để tạo ra kết quả. Cuối cùng, trong cặp dấu ngoặc đơn thứ hai, bạn truyền các giá trị thực tế mà các tham số đó sẽ nhận khi hàm được thực thi.

Nếu bạn sử dụng Trình quản lý tên của Excel để tạo một hàm LAMBDA cố định, cú pháp sẽ thay đổi một chút, vì bạn định nghĩa hàm ở đó nhưng chưa gọi nó. Trong trường hợp đó, định dạng sẽ là:

=LAMBDA(var1; var2; …; varN; phép tính)

Sau đó, bạn sẽ gọi hàm đó bằng tên mà bạn đã đặt cho nó trong Trình quản lý tên, chỉ cần gõ tên và các tham số giống như bạn vẫn làm với SUM, AVERAGE hoặc bất kỳ hàm chuẩn nào khác.

Các phương pháp tốt nhất khi tạo và kiểm thử hàm Lambda

Khi bắt đầu làm việc với các hàm Lambda, điều quan trọng là phải tuân theo một vài nguyên tắc để đảm bảo chúng hoạt động như mong đợi và bạn không lãng phí thời gian gỡ lỗi các sự cố phức tạp. Một trong những cách thực tế nhất để bắt đầu là tạo và kiểm tra hàm Lambda trực tiếp trong một ô mã.

Thông thường, quy trình là trước tiên viết toàn bộ công thức với định nghĩa hàm LAMBDA và lời gọi hàm trong cùng một biểu thức, để bạn có thể ngay lập tức kiểm tra xem kết quả có đúng như mong đợi hay không. Bằng cách này, bạn có thể phát hiện lỗi cú pháp hoặc lỗi logic trước khi lưu nó dưới dạng một hàm có tên.

Ví dụ, một cấu trúc kiểm thử rất điển hình sẽ là:

=LAMBDA(); tính toán)(giá trị kiểm tra)

Để kiểm tra một điều rất đơn giản như cộng 1 vào một số, bạn có thể sử dụng:

=LAMBDA(số; số + 1)(1)

Trong trường hợp này, hàm sẽ trả về giá trị 2. Đây là một ví dụ rất đơn giản, nhưng nó minh họa rõ cơ chế hoạt động: trước tiên bạn định nghĩa các tham số và phép tính, sau đó gọi hàm đó và truyền đối số tương ứng.

Một khuyến nghị quan trọng để tránh lỗi #CALC! là đảm bảo hàm LAMBDA của bạn luôn trả về kết quả . Điều này đạt được bằng cách thêm rõ ràng một biểu thức ở cuối hàm để tạo ra một giá trị đơn hoặc một mảng, tùy thuộc vào nhu cầu của bạn. Nếu bạn thấy lỗi #CALC! trong quá trình kiểm tra, hãy kiểm tra xem công thức có thực sự tạo ra kết quả mà Excel có thể hiển thị hay không.

Sau khi bạn đã kiểm tra hàm LAMBDA trong một ô và thấy rằng nó hoạt động chính xác, đó là thời điểm thích hợp để chuyển logic đó vào Trình quản lý tên và biến nó thành một hàm tùy chỉnh có thể tái sử dụng trên toàn bộ trang tính hoặc sổ làm việc.

Mối quan hệ giữa LAMBDA và các hàm ma trận mới

Xung quanh hàm LAMBDA, một loạt các hàm nâng cao đã xuất hiện, chẳng hạn như BYROW, BYCOL, MAP, SCAN, REDUCE và MAKEARRAY (hàm sau được dịch trong một số phiên bản là ARCHIVOMAKEARRAY), dựa trên LAMBDA để áp dụng các phép biến đổi cho các phạm vi và ma trận hoàn chỉnh.

Ý tưởng chung là các hàm này lặp qua các phạm vi dữ liệu (theo hàng, theo cột hoặc từng phần tử) và, đối với mỗi phần tử hoặc nhóm phần tử, thực thi một hàm Lambda do bạn định nghĩa. Nói cách khác, chúng hoạt động giống như các vòng lặp, nhưng được tích hợp vào ngôn ngữ công thức của Excel.

Điều này cho phép bạn thực hiện các thao tác mà trước đây cần đến các cột phụ, bảng trung gian hoặc thậm chí là macro, trực tiếp bằng một công thức mảng duy nhất, công thức này sẽ mở rộng và trả về kết quả cho toàn bộ phạm vi cùng một lúc.

Trong số các hàm liên quan đến LAMBDA, REDUCE, MAP, SCAN, BYCOL, BYROW và MAKEARRAY nổi bật với mỗi hàm có một mục tiêu cụ thể: duyệt qua các hàng, áp dụng các phép biến đổi theo cột, tích lũy kết quả, tạo ma trận được tính toán từ đầu, v.v. Điểm chung của chúng là sử dụng LAMBDA như một "công cụ" nội bộ, truyền các giá trị và bộ tích lũy khi di chuyển qua ma trận.

Hàm BYROW: lặp qua các hàng và trả về kết quả từng hàng một.

Hàm BYROW được sử dụng để áp dụng hàm LAMBDA cho từng hàng trong một phạm vi và trả về một mảng với một giá trị cho mỗi hàng được xử lý. Đây là một cách rất hiệu quả để tính toán tổng phụ hoặc số liệu thống kê từng hàng một mà không cần phải sao chép công thức theo chiều dọc.

Cú pháp tổng quát của nó là:

=BYROW(matrix; LAMBDA(row; expression))

Tham số đầu tiên là mảng hoặc phạm vi bạn muốn lặp qua (ví dụ: B2:D7), và tham số thứ hai là một hàm LAMBDA nhận từng hàng trong phạm vi đó làm tham số, lần lượt từng hàng một. Hàm LAMBDA trả về giá trị bạn muốn gán cho hàng đó (có thể là tổng, trung bình, phép kiểm tra logic, v.v.).

Giả sử bạn có một bảng dữ liệu trong phạm vi B2:D7 và bạn muốn tính tổng phụ cho mỗi hàng. Bạn có thể viết công thức như sau vào ô E2:

=BYROW(B2:D7; LAMBDA(row; SUM(row)))

Kết quả sẽ là một vectơ đầu ra với một giá trị cho mỗi hàng của ma trận B2:D7, trong đó mỗi giá trị biểu thị tổng các phần tử trong hàng đó. Bằng cách này, bạn không cần phải viết hàm SUM từng hàng một: hàm BYROW sẽ thực hiện điều đó cho bạn và xuất kết quả xuống.

Hàm BYCOL: áp dụng hàm LAMBDA theo cột

Tương tự như hàm BYROW, hàm BYCOL được thiết kế để duyệt qua một mảng theo cột thay vì theo hàng. Nó áp dụng một hàm LAMBDA cho mỗi cột trong phạm vi và trả về một mảng kết quả, trong đó mỗi phần tử tương ứng với một cột.

Cú pháp điển hình của nó là:

=BYCOL(mảng; LAMBDA(cột; biểu thức))

Trong trường hợp này, tham số mà hàm LAMBDA nhận được là toàn bộ cột của ma trận đang được xử lý ở mỗi bước. Tương tự như BYROW, hàm này trả về một vectơ, nhưng vectơ này được thiết kế để làm việc với tổng hoặc các chỉ số theo cột.

Tiếp tục với ví dụ trước, nếu bạn muốn tính trung bình cộng của mỗi cột trong phạm vi B2:D7 , bạn có thể đặt công thức như sau vào ô B8:

=BYCOL(B2:D7; LAMBDA(cột; AVERAGE(cột)))

Kết quả sẽ là một ma trận có cùng số cột như B2:D7, trong đó mỗi vị trí chứa giá trị trung bình của cột đó . Bằng cách này, bạn có thể nhận được tất cả các giá trị trung bình cùng một lúc mà không cần phải kéo thả công thức hoặc lo lắng về các tham chiếu tương đối.

Hàm MAKEARRAY (MAKEARRAYFILE): tạo mảng tính toán

Hàm MAKEARRAY (đôi khi được hiển thị là ARCHIVOMAKEARRAY) cho phép bạn tạo một mảng hoàn toàn mới bằng cách chỉ định số hàng và cột, và tính toán từng phần tử bằng cách sử dụng hàm LAMBDA. Nó không bắt đầu từ một phạm vi hiện có, mà xây dựng mảng từ đầu.

Cú pháp tổng quát của nó là:

=MAKEARRAY(hàng; cột; LAMBDA(hàng; cột; biểu thức))

Tham số `rows` chỉ định số hàng của ma trận đầu ra, `columns` xác định số cột, và hàm LAMBDA nhận các chỉ số hàng và cột được tính toán trong mỗi lần lặp làm tham số. Với thông tin này, bạn có thể xây dựng hầu như bất kỳ mẫu số hoặc văn bản nào.

Một ví dụ rất dễ hiểu là tạo một ma trận trong đó mỗi phần tử biểu thị vị trí của chính nó. Trong bất kỳ ô nào, bạn có thể viết nội dung như sau:

=MAKEARRAYFILE(3; 2; LAMBDA(row; col; -(row & col)))

Kết quả sẽ là một ma trận 3 hàng x 2 cột , trong đó mỗi giá trị biểu thị sự kết hợp của hàng và cột (ví dụ: 11, 12, 21, 22, 31, 32), được biến đổi theo phép tính bạn nhập vào (trong trường hợp này, dấu âm được áp dụng cho sự kết hợp hàng và cột).

Một ứng dụng thú vị khác của MAKEARRAY là chuyển đổi một vectơ thành mảng trong khi kiểm soát số lượng phần tử cần lấy. Giả sử bạn muốn tạo một mảng với 6 giá trị đầu tiên của một phạm vi dọc. Bạn có thể tạo một mảng các vị trí bằng MAKEARRAYFILE trước, lấy k giá trị vị trí nhỏ nhất, và cuối cùng sử dụng INDEX để truy xuất các phần tử thực từ phạm vi ban đầu.

Một ví dụ về công thức kết hợp nhiều hàm số có thể có cấu trúc như sau:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(row; col; -(row & col))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

Ở đây, LET được sử dụng để định nghĩa các tên trung gian (arrPos, arrPosF), một mảng các vị trí được xây dựng bằng ARCHIVOMAKEARRAY (3×2), 6 vị trí nhỏ nhất được chọn bằng SMALLEST và SEQUENCE, và cuối cùng INDEX được sử dụng để trả về các giá trị tương ứng từ phạm vi G8:G13. Đây là một ví dụ mạnh mẽ về cách kết hợp LAMBDA và các hàm mảng động để thực hiện các phép biến đổi phức tạp mà không cần macro.

Hàm MAP: biến đổi từng phần tử

Hàm MAP được sử dụng để lặp qua một hoặc nhiều mảng đồng thời và trả về một mảng mới, trong đó mỗi phần tử đầu ra được tính toán bằng cách áp dụng hàm LAMBDA cho (các) phần tử đầu vào tương ứng. Nó tương đương với hàm "map" cổ điển trong lập trình hàm.

Cú pháp cơ bản là:

=MAP(matrix1; LAMBDA_or_more_matrices)

Ở dạng đơn giản nhất, nó nhận một mảng duy nhất và một hàm LAMBDA nhận từng giá trị từ mảng đó. Hàm LAMBDA này biến đổi giá trị và trả về phiên bản mới sẽ trở thành một phần của mảng đầu ra, giữ nguyên kích thước như mảng gốc.

Ví dụ, nếu bạn muốn lặp qua một phạm vi dọc A21:A26 và giữ nguyên số ban đầu nếu đó là số chẵn hoặc dấu gạch ngang nếu đó là số lẻ, bạn có thể sử dụng cú pháp như sau:

=MAP($A$21:$A$26; LAMBDA(param1; IF(ES.PAR(param1); param1; «-«)))

Trong trường hợp này, hàm MAP phân tích từng phần tử của phạm vi A21:A26. Hàm LAMBDA kiểm tra bằng IS.EVEN xem số đó có phải là số chẵn hay không. Nếu là số chẵn, nó trả về chính số đó; ngược lại, nó trả về dấu gạch ngang. Kết quả là một mảng có cùng kích thước với phạm vi ban đầu, nhưng đã được biến đổi cho từng phần tử.

Cách tiếp cận này rất hữu ích khi bạn muốn áp dụng logic điều kiện, chuyển đổi văn bản, chuẩn hóa giá trị hoặc bất kỳ thao tác đơn giản nào khác, tránh các cột phụ trợ và công thức lặp lại.

Chức năng QUÉT: kết quả tích lũy và trung gian

Hàm SCAN được sử dụng để kiểm tra một mảng bằng cách áp dụng hàm LAMBDA cho từng giá trị và tạo ra một mảng đầu ra hiển thị tất cả các giá trị trung gian của quá trình tích lũy. Nó rất giống với hàm REDUCE, nhưng thay vì chỉ trả về kết quả cuối cùng, nó lưu giữ từng bước.

Cú pháp tổng quát của nó là:

=SCAN(; mảng; LAMBDA(bộ tích lũy; giá trị))

Tham số đầu tiên, không bắt buộc, là giá trị ban đầu của biến tích lũy (ví dụ: 0 nếu bạn đang cộng). Tham số thứ hai là mảng hoặc phạm vi bạn muốn lặp qua. Cuối cùng, hàm LAMBDA nhận hai tham số: biến tích lũy (kết quả tạm thời đến thời điểm đó) và giá trị hiện tại của mảng bạn đang xử lý.

Ở mỗi bước, SCAN đánh giá giá trị LAMBDA, cập nhật bộ tích lũy và tạo ra một phần tử mới trong ma trận đầu ra với giá trị thu được. Bằng cách này, bạn nhận được một chuỗi các giá trị tích lũy hoặc các phép biến đổi liên tiếp.

Một ví dụ điển hình là tính tổng tích lũy (tổng lũy ​​kế) trên một tập hợp các giá trị và từ đó, cũng thu được tần suất tích lũy tương đối. Giả sử bạn có dữ liệu trong A31:A36 và bạn muốn tính tổng tích lũy tuyệt đối:

=SCAN(0; A31:A36; LAMBDA(accum; param1; accum + param1))

Công thức này lặp qua các ô A31:A36, cộng từng giá trị vào tổng trước đó. Kết quả là một mảng có cùng số phần tử với phạm vi ban đầu, nhưng mỗi vị trí hiển thị tổng tích lũy đến thời điểm đó.

Từ tổng tích lũy đó, bạn có thể dễ dàng tính toán tần suất phần trăm tích lũy bằng cách chia mỗi tổng tích lũy cho tổng số chung. Ví dụ, bạn có thể xác định tổng trước bằng cách sử dụng hàm SUM và sau đó áp dụng hàm SCAN một lần nữa:

=LET(tổng; SUM(A31:A36); SCAN(0; A31:A36; LAMBDA(accum; param1; (accum + param1)))/tổng)

Trong trường hợp này, LET gán tổng cho toàn bộ phạm vi A31:A36. Sau đó, SCAN tạo ra chuỗi các giá trị tích lũy và bằng cách chia chuỗi đó cho tổng, ta thu được tần suất tích lũy tương đối cho mỗi bước , tất cả trong một công thức ma trận duy nhất.

Hàm REDUCE: giảm xuống còn một giá trị tích lũy duy nhất

Hàm REDUCE cũng lặp qua một mảng bằng cách áp dụng hàm LAMBDA cho từng phần tử, nhưng không giống như SCAN, ở đây bạn chỉ quan tâm đến kết quả cuối cùng của quá trình tích lũy. Nghĩa là, nó thực hiện cùng loại lặp như SCAN, nhưng chỉ trả về giá trị cuối cùng trong bộ tích lũy.

Cú pháp của nó là:

=REDUCE(; mảng; LAMBDA(bộ tích lũy; giá trị))

Cũng giống như trong hàm SCAN, initial_value thiết lập điểm bắt đầu của bộ tích lũy, matrix là phạm vi cần xử lý, và hàm LAMBDA có các tham số là bộ tích lũy hiện tại và giá trị đang được đọc tại thời điểm đó.

Một cách sử dụng rất điển hình là để tính tổng tích lũy hoặc phép toán cộng dồn mà bạn chỉ quan tâm đến kết quả cuối cùng . Ví dụ, để tính tổng A1:A6 bằng hàm REDUCE, bạn có thể viết:

=REDUCE(0; A1:A6; LAMBDA(accum; param1; accum + param1))

Ở đây, hàm REDUCE lặp qua các ô A1:A6 và ở mỗi bước cập nhật giá trị tích lũy bằng cách cộng giá trị của ô hiện tại (param1). Cuối cùng, nó trả về một giá trị duy nhất: tổng. Về mặt khái niệm, nó tương tự như sử dụng hàm SUM, nhưng với REDUCE, bạn có thể định nghĩa bất kỳ logic tích lũy phức tạp nào, chứ không chỉ là các phép cộng đơn giản.

Sức mạnh của REDUCE nằm ở khả năng làm việc với kết quả trước đó ở mỗi bước và tiếp tục áp dụng các phép toán cho đến khi quá trình hoàn tất. Điều này cho phép bạn thực hiện các phép tính tùy chỉnh phức tạp mà trước đây thường được xử lý bằng vòng lặp trong macro.

Tạo các hàm tùy chỉnh bằng LAMBDA và Trình quản lý tên

Một trong những tính năng mạnh mẽ nhất của LAMBDA là khả năng chuyển đổi bất kỳ công thức nào thành hàm người dùng bằng Trình quản lý tên của Excel. Điều này cho phép hàm của bạn có tên riêng và được sử dụng giống như bất kỳ hàm gốc nào khác trong chương trình.

Quy trình làm việc thông thường như sau: đầu tiên, kiểm tra hàm LAMBDA trong một ô , bao gồm cả định nghĩa và lời gọi hàm với các đối số ví dụ. Sau khi xác minh rằng nó hoạt động chính xác và trả về kết quả mong đợi, hãy sao chép phần tương ứng với định nghĩa LAMBDA (không bao gồm lời gọi cuối cùng) và dán vào Trình quản lý tên.

Trong Trình quản lý tên, bạn tạo một tên mới (ví dụ: MyVATFunction, MyDiscount, MyWeightedAverage, v.v.) và trong trường "Tham chiếu đến", bạn nhập:

=LAMBDA(var1; var2; …; varN; phép tính)

Kể từ thời điểm đó, trong bất kỳ ô nào của bảng tính, bạn đều có thể gọi hàm bằng cách viết tên của nó như thể đó là một hàm tích hợp sẵn , truyền các giá trị tham số theo đúng thứ tự mà bạn đã định nghĩa chúng.

Điều này mang lại hai ưu điểm rõ rệt: thứ nhất, nó giúp bảng tính của bạn dễ đọc hơn (thay vì nhìn thấy các công thức dài dòng, bạn sẽ thấy một hàm với tên gọi mô tả rõ ràng); thứ hai, nó tập trung logic vào một nơi. Nếu sau này bạn muốn thay đổi phép tính, chỉ cần sửa đổi định nghĩa trong Trình quản lý tên, và tất cả các công thức sử dụng nó sẽ được cập nhật tự động.

Các khía cạnh thực tiễn và những cân nhắc bổ sung

Để thực sự tận dụng LAMBDA và các hàm liên quan, điều hữu ích là hiểu một số khía cạnh thực tế về hoạt động và yêu cầu của chúng . Trước hết, các hàm này là một phần của các tính năng Excel hiện đại, vì vậy bạn cần một phiên bản đã bao gồm mảng động và các hàm LAMBDA, BYROW, BYCOL mới hơn, cùng các hàm khác. Chúng thường có sẵn trong các phiên bản Microsoft 365 mới nhất.

Một vấn đề quan trọng khác là hiệu năng : mặc dù các hàm LAMBDA và hàm duyệt rất mạnh mẽ, nhưng nếu áp dụng chúng cho các phạm vi lớn với logic rất phức tạp, cuốn sách có thể mất nhiều thời gian hơn để tính toán lại. Tốt hơn hết là nên thiết kế các hàm LAMBDA sao cho hiệu quả, tránh các phép tính dư thừa và tận dụng các cấu trúc như LET để định nghĩa các giá trị trung gian có thể tái sử dụng.

Việc duy trì quy ước đặt tên nhất quán cho các tham số và hàm cũng rất quan trọng . Sử dụng tên mô tả giúp bạn hiểu logic khi xem lại tệp sau nhiều tháng hoặc khi người khác cần làm việc với bảng tính của bạn. Một tham số có tên amount, rate, dataRow hoặc valuesCol rõ ràng hơn nhiều so với chỉ đơn giản là x hoặc yoa.

Về lỗi #CALC!, lỗi này thường xuất hiện khi Excel không thể tính toán biểu thức mảng hoặc khi hàm LAMBDA không trả về kết quả hợp lệ. Luôn luôn kiểm tra xem công thức của bạn có đầu ra được xác định rõ ràng hay không và, nếu bạn đang làm việc với các hàm mảng, hãy đảm bảo rằng kích thước nhất quán (ví dụ: bạn không kết hợp các phạm vi kích thước không tương thích mà không chuyển đổi thích hợp).

Cuối cùng, mặc dù LAMBDA loại bỏ nhu cầu sử dụng VBA trong nhiều trường hợp, nhưng nó không hoàn toàn thay thế VBA. Có những trường hợp mà tự động hóa macro vẫn là lựa chọn tốt nhất, nhưng đối với rất nhiều phép tính tùy chỉnh và chuyển đổi dữ liệu, LAMBDA và các hàm liên quan cho phép bạn thực hiện tất cả công việc của mình trong môi trường công thức thông thường của Excel.

Nhờ những khả năng này, những người thường xuyên làm việc với bảng tính giờ đây có các công cụ linh hoạt hơn nhiều để thiết kế các phép tính của riêng mình, tóm tắt thông tin theo hàng hoặc cột, duyệt ma trận toàn bộ hoặc một phần, tạo tổng chi tiết và tạo ma trận mới được tính toán ngay lập tức, tất cả mà không cần rời khỏi ngôn ngữ công thức mà họ đã quen thuộc.