Hàm LET và LAMBDA trong Excel: Hướng dẫn đầy đủ kèm ví dụ

Khi bạn làm việc với dữ liệu trong Excel, sẽ đến lúc công thức lặp lại và phạm vi vô tận trở nên không thể kiểm soát được; đó là lúc chúng phát huy tác dụng các hàm LET và LAMBDA, hai phần quan trọng để tạo ra các phép tính sạch, có thể tái sử dụng và bảo trì nhanh hơn.

Với những chức năng này, và với một số các hàm ma trận hiện đại dựa vào chúng, bạn có thể định nghĩa tên trung gian, đóng gói các phép tính và áp dụng các phép biến đổi trên ma trận như thể bạn đang lặp; BYROW, BYCOL, MAP, SCAN, REDUCE và MAKEARRAY Chúng là lớp kem phủ trên bánh cho phép bạn áp dụng LAMBDA vào các hàng, cột hoặc phần tử của ma trận theo cách tinh tế và hiệu quả.

LET và LAMBDA trong Excel là gì?

Hàm LAMBDA cho phép tạo các chức năng tùy chỉnh bằng chính ngôn ngữ đó công thức excel, không cần macro hoặc VBA: bạn xác định tham số, viết phép tính và nếu muốn, có thể gọi nó bằng đối số để có được kết quả tức thì.

Mặt khác, hàm LET được sử dụng để gán tên cho các kết quả trung gian trong cùng một công thức, do đó bạn có thể sử dụng lại chúng nhiều lần mà không cần tính toán lại, tăng khả năng đọc và hiệu quả trong các bảng tính phức tạp.

Xung quanh LAMBDA xuất hiện các hàm xử lý mảng như thể chúng là các bộ sưu tập cần được duyệt: THEO DÕI (theo hàng), BYCOL (theo cột), MAP (bản đồ các yếu tố), SCAN (tích lũy và trả về các trạng thái trung gian), GIẢM (chỉ trả về tích lũy cuối cùng) và KIẾM TIỀN (tạo ma trận theo yêu cầu).

Hãy nghĩ về bộ này như những công cụ mô phỏng các vòng lặp và duyệt qua các mảng: Bạn truyền LAMBDA với phép biến đổi mong muốn và Excel sẽ thực hiện phần còn lại, trả về các vectơ hoặc ma trận có kết quả.

Kết quả là bạn có thể xây dựng các phép tính phức tạp mà không cần dùng đến các cột trợ giúp hoặc mã bên ngoài; mọi thứ đều được gói gọn trong các công thức thuần túy, ít rủi ro lỗi hơn và bảo trì tốt hơn.

Cách kiểm tra và xác định LAMBDA mà không có lỗi

Một thực hành tốt là xây dựng và xác thực LAMBDA trực tiếp trong một ô trước khi định nghĩa nó như một hàm có thể tái sử dụng: định nghĩa các tham số, viết phép tính và thêm lệnh gọi thử nghiệm với các đối số mong muốn vào cuối.

Nếu bạn không thực hiện cuộc gọi thử nghiệm đó, bạn sẽ dễ dàng vấp phải lỗi #CALC! Trong các công thức chưa hoàn chỉnh, lệnh gọi cuối cùng buộc bạn phải đánh giá phép tính và xác nhận rằng cấu trúc là chính xác.

Cách làm việc rõ ràng nhất là: LAMBDA(tham số 1; tham số 2; …; phép tính)(đối số 1; đối số 2; …); do đó, trong một ô duy nhất, bạn viết định nghĩa và thực thi để xác thực kết quả.

Ví dụ, để cộng 1 vào một số, bạn có thể thử: =LAMBDA(number; number + 1)(1). Biểu thức này trả về giá trị 2, điều này khẳng định rằng LAMBDA và lời kêu gọi của họ đã được lên kế hoạch tốt.

Sau khi xác minh, bạn có thể đăng ký LAMBDA đó dưới dạng hàm tùy chỉnh (có tên) hoặc chèn nó vào các công thức khác và các hàm ma trận để giải quyết các vấn đề phức tạp hơn.

LET: cú pháp, đối số và cân nhắc

Cú pháp chung của LET là: =LET(nombre1; nombre_valor1; cálculo_o_nombre2; ); mục tiêu của nó là đặt tên cho kết quả trung gian và sử dụng những tên đó trong phép tính cuối cùng.

Những luận điểm chính của LET: name1 (bắt buộc) là mã định danh đầu tiên bạn chỉ định; nó phải bắt đầu bằng một chữ cái, không thể là kết quả của một công thức và không thể xung đột với cú pháp phạm vi trong Excel.

Đối số tên_giá trị1 (bắt buộc) là giá trị hoặc biểu thức sẽ được liên kết với name1; theo cách này bạn tránh lặp lại cùng một phép tính và cải thiện hiệu suất nếu bạn định tái sử dụng nó.

Lập luận thứ ba, tính_toán_hoặc_tên2 (bắt buộc), nó có thể là một trong hai điều sau: hoặc là tính toán cuối cùng sử dụng tất cả các tên đã khai báo hoặc một tên thứ hai cần được xác định; nếu bạn quyết định khai báo một tên, thì bạn cũng sẽ cần cung cấp tên_giá trị2 y tính_toán_hoặc_tên3.

tên_giá trị2 (tùy chọn) gán một giá trị cho tên được khai báo ở bước trước; nếu bạn tiếp tục nối các tên, lặp lại mẫu tên/giá trị cho đến khi đối số cuối cùng là một phép tính.

Cuối cùng, tính_toán_hoặc_tên3 (tùy chọn) có thể là phép tính cuối cùng hoặc tên thứ ba; hãy nhớ rằng đối số cuối cùng của LET phải luôn là một phép tính trả về kết quả mong đợi.

Mẫu có thể mở rộng này cho phép bạn liên kết các định nghĩa một cách rõ ràng và ngăn bạn sao chép các biểu thức trong cùng một công thức; nói tóm lại, LET đơn giản hóa, làm rõ và tăng tốc sổ bài tập phức tạp.

Các hàm ma trận hiện đại tận dụng LAMBDA

Các hàm này duyệt qua các mảng bằng cách áp dụng phép biến đổi được xác định bằng LAMBDA, hoạt động như thể chúng là các trình lặp; cung cấp tổng phụ theo hàng hoặc cột, ánh xạ, tích lũy và xây dựng ma trận tùy chỉnh.

BYROW: Áp dụng LAMBDA cho mỗi hàng

THEO DÕI Đánh giá LAMBDA cho mỗi hàng trong phạm vi đầu vào và trả về một vectơ cột của kết quả; lý tưởng để tạo tổng phụ hoặc chỉ số theo từng hàng mà không có cột phụ.

Cú pháp của nó là: =BYROW(rango; LAMBDA(fila; cálculo_por_fila)), trong đó tham số fila biểu thị hàng hiện tại mà hàm đang xử lý; LAMBDA trả về một giá trị duy nhất cho mỗi hàng.

Ví dụ thực tế: nếu ma trận của bạn ở dạng B2:D7 và bạn muốn cộng từng hàng lại với nhau E2 viết =BYROW(B2:D7; LAMBDA(fila; SUMA(fila))); bạn sẽ nhận được một vectơ có tổng của mỗi hàng, sẵn sàng để sử dụng trong phân tích hoặc đồ họa.

BYCOL: áp dụng LAMBDA cho mỗi cột

BYCOL Hoạt động tương tự như BYROW, nhưng nó lặp qua các cột; nó trả về một vectơ cột với một kết quả cho mỗi cột trong phạm vi nguồn.

Cú pháp là: =BYCOL(rango; LAMBDA(columna; cálculo_por_columna)); tham số trụ hiển thị cột hiện tại cho LAMBDA, tạo ra một giá trị cho mỗi cột.

Ví dụ thực tế: với dữ liệu trong B2:D7, đặt vào B8 công thức =BYCOL(B2:D7; LAMBDA(columna; PROMEDIO(columna))); bạn sẽ nhận được một vectơ có giá trị trung bình của mỗi cột, hữu ích như một bản tóm tắt hoặc kiểm soát chất lượng.

MAKEARRAY: tạo các mảng được tính toán

KIẾM TIỀN tạo ra một ma trận có kích thước bạn chỉ định, tính toán từng phần tử bằng LAMBDA nhận chỉ mục hàng và chỉ mục cột; trong một số môi trường tiếng Tây Ban Nha, nó được coi là TỆP MAKEARRAY.

Dạng tổng quát của nó là: =MAKEARRAY(n_filas; n_columnas; LAMBDA(fila; columna; cálculo_por_posición)). Mỗi giao điểm hàng/cột đi qua LAMBDA và trả về giá trị mong muốn cho tọa độ đó.

Ví dụ về định danh vị trí: trong bất kỳ ô nào, hãy sử dụng =ARCHIVOMAKEARRAY(3; 2; LAMBDA(fila; col; -(fila&col))) để tạo ra một mảng 3 hàng x 2 cột trong đó mỗi phần tử nối hàng và cột của nó (và buộc phải đánh số bằng dấu trừ).

Một ví dụ khác kết hợp nhiều chức năng: =LET(arrPos; ARCHIVOMAKEARRAY(3; 2; LAMBDA(fila; col; -(fila&col))); arrPosF; COINCIDIR(arrPos; K.ESIMO.MENOR(arrPos; SECUENCIA(6))); INDICE(G8:G13; arrPosF)). Với cấu trúc này, bạn tạo ra các vị trí, bạn nhận được 6 vị thành niên và bạn ánh xạ chúng trên một phạm vi bằng INDEX.

MAP: chuyển đổi phần tử thành phần tử

MAP lấy một hoặc nhiều mảng và trả về một mảng khác có cùng kích thước bằng cách áp dụng LAMBDA cho mỗi phần tử; nó hoàn hảo cho làm sạch, chuẩn hóa hoặc thẻ có điều kiện không có cột phụ.

Cú pháp cơ bản là: =MAP(matriz; LAMBDA(valor; transformación)); nếu bạn truyền nhiều mảng, LAMBDA sẽ nhận được nhiều tham số, mỗi tham số cho một mảng; kết quả giữ nguyên kích thước của ma trận đầu vào.

Ví dụ kinh điển để đánh dấu số chẵn và số lẻ trong A21:A26: =MAP($A$21:$A$26; LAMBDA(param1; SI(ES.PAR(param1); param1; "-"))). Do đó, mỗi phần tử được thay thế bằng chính nó nếu nó chẵn hoặc bằng dấu gạch nối nếu nó không chẵn; tất cả xử lý là vector.

QUÉT: tích lũy với các trạng thái trung gian

SCAN Lặp lại qua một mảng với bộ tích lũy LAMBDA và trả về tất cả các trạng thái trung gian, không chỉ trạng thái cuối cùng; lý tưởng cho tổng số đang chạy, phần trăm tích lũy và các phép tính phụ thuộc vào kết quả trước đó.

Cấu trúc là: =SCAN(valor_inicial; matriz; LAMBDA(acumulador; valor; nuevo_acumulado)), ở đâu giá trị ban đầu bộ tích lũy bắt đầu, ma trận là phạm vi cần xử lý và LAMBDA xác định quá trình chuyển đổi giữa các tiểu bang.

Đối với một giải độc đắc cổ điển hơn A31:A36, anh viết: =SCAN(0; A31:A36; LAMBDA(acum; param1; acum + param1)); bạn sẽ nhận được chuỗi các tổng riêng phần trong một bước.

Và nếu bạn muốn tỷ lệ tích lũy, bạn có thể kết hợp LET và SCAN: =LET(total; SUMA(A31:A36); SCAN(0; A31:A36; LAMBDA(acum; param1; (acum + param1))) / total). Ở đây trước tiên bạn tính toán tổng cộng với LET và sau đó bạn chia từng trạng thái trung gian cho tổng đó.

GIẢM: tích lũy cuối cùng

GIẢM Nó hoạt động giống như SCAN nhưng chỉ trả về trạng thái cuối cùng của bộ tích lũy; nghĩa là, giảm toàn bộ mảng thành một giá trị duy nhất áp dụng phép biến đổi được xác định bởi LAMBDA.

Mẫu của nó là: =REDUCE(valor_inicial; matriz; LAMBDA(acumulador; valor; nuevo_acumulado)); giá trị cuối cùng thường là tổng, tích, giao điểm logic hoặc kết quả của quá trình mà bạn quan tâm.

Ví dụ về tổng chạy cuối cùng trên A1:A6: =REDUCE(0; A1:A6; LAMBDA(acum; param1; acum + param1)); trong một biểu thức duy nhất, bạn nhận được tổng số tiền mà không cần phải trải qua các bước trung gian.

Bài viết liên quan:

Các hàm IF, VLOOKUP và CONCATENATE trong Excel: Hướng dẫn đầy đủ

Isaac

Người viết đam mê về thế giới byte và công nghệ nói chung. Tôi thích chia sẻ kiến ​​thức của mình thông qua viết lách và đó là những gì tôi sẽ làm trong blog này, cho bạn thấy tất cả những điều thú vị nhất về tiện ích, phần mềm, phần cứng, xu hướng công nghệ, v.v. Mục tiêu của tôi là giúp bạn điều hướng thế giới kỹ thuật số một cách đơn giản và thú vị.