Hàm LET trong Excel với các ví dụ về công thức

Nếu bạn làm việc với các công thức dài dòng trong Excel, thì chắc chắn bạn đã quen thuộc với ý tưởng phạm vi được đặt tên làm cho các công thức phức tạp dễ đọc hơn. Và bây giờ, Microsoft đang tiến thêm một bước và cho phép gán tên cho các phép tính và giá trị trực tiếp trong một công thức. Nếu công thức của bạn sử dụng cùng một biểu thức nhiều lần, bạn có thể để Excel tính toán nó chỉ một lần, lưu trữ kết quả bên trong công thức và sử dụng lại nhiều lần nếu cần. Nghe có vẻ thú vị phải không?

Hàm LET trong Excel

Hàm LET trong Excel cho phép bạn gán tên cho kết quả tính toán và xác định các biến bên trong công thức, để công thức trông rõ ràng hơn và hoạt động nhanh hơn.

Về cơ bản, khái niệm này giống như đặt tên ô, phạm vi và công thức trong Trình quản lý tên. Điều làm cho hàm LET khác biệt là các tên được khai báo chỉ tồn tại trong phạm vi của một công thức nhất định và không có ở đâu khác.

Cú pháp

LET có cú pháp và đối số sau:

LET (name1, name_value1, [name2], [name_value2], …, phép tính)

Ở đâu:

• Tên1 (bắt buộc) – tên đầu tiên để gán. Nó phải bắt đầu bằng một chữ cái.
• Name_value1 (bắt buộc) – giá trị hoặc phép tính được gán cho tên1.
• Name2 / name_value2 (tùy chọn) – tên và giá trị thứ hai.
• Phép tính (bắt buộc) – một phép tính sử dụng các tên và giá trị được chỉ định.

Hàm có thể xử lý tới 126 cặp tên / giá trị.

khả dụng

Hiện tại, LET là một tính năng beta chỉ dành cho Người dùng nội bộ Office. Trong vài tháng nữa, nó dự kiến ​​sẽ được phát hành cho tất cả người đăng ký Microsoft 365.

Công thức LET cơ bản

Nếu bạn chưa hiểu rõ về hàm, một ví dụ về công thức LET ở dạng đơn giản nhất sẽ giúp đưa những điều cần thiết vào trọng tâm.

Hãy xem xét biểu thức đơn giản này: x + y. Ở đây, chúng ta có 2 biến, x và y. Hãy gán giá trị 2 cho x, giá trị 5 thành y và nhân cái này với cái kia:

=LET(x, 2, y, 5, x*y)

Nhập công thức này vào một ô và nó sẽ trả về kết quả là 10.

Thay vì các giá trị, các biến có thể được chỉ định cho các tham chiếu ô, chẳng hạn x đến B2 và y đến B3:

=LET(x,B2, y, B3, x * y)

Để dễ dàng xây dựng công thức, các tên đã được khai báo sẽ xuất hiện trong danh sách thả xuống intellisense, giống như các tên được xác định trong trình quản lý tên.

Nhìn vào công thức trên, bạn có thể tự hỏi, những thứ quá phức tạp có ích gì? Tại sao không chỉ đơn giản đặt:

=B2*B3

Trong trường hợp cụ thể này, một phép nhân thông thường đơn giản hơn. Mục đích là làm rõ khái niệm. Khi nói đến các công thức phức tạp hơn với các phép tính lặp lại, hàm LET có một mức độ hữu ích hoàn toàn mới như được trình bày trong các ví dụ khác.

Ưu điểm

Nếu bạn vẫn nghi ngờ rằng LET sẽ là một bổ sung xứng đáng cho hộp công cụ Excel của bạn, thì hãy xem xét những lợi ích sau:

Công thức đơn giản hóa. Đặt tên mô tả cho các biến và phép tính trung gian giúp bạn dễ dàng hiểu công thức thực sự đang làm gì. Bạn không còn cần phải tìm ra logic bên trong của từng biểu thức hoặc tham chiếu cụ thể trong một công thức.

Tính toán nhanh hơn. Khi cùng một biểu thức được lặp lại nhiều lần trong một công thức, Excel sẽ tính toán nó nhiều lần. Khi biểu thức được gọi theo tên, Excel chỉ tính toán nó một lần. Kết quả là toàn bộ trang tính sẽ được tính toán lại nhanh hơn nhiều. Tác động tích cực này đối với hiệu suất đặc biệt đáng chú ý trong trường hợp công thức dài và tập dữ liệu lớn.

Bảo trì dễ dàng. Sử dụng tên cho các phép tính lặp lại không chỉ giúp công thức trở nên gọn gàng hơn mà còn dễ cập nhật hơn. Khi cần điều chỉnh, bạn chỉ thực hiện một thay đổi duy nhất thay vì cập nhật các biểu thức giống nhau nhiều lần, do đó tiết kiệm thời gian và giảm lỗi do con người thay đổi.

Xem thêm:

Hàm UNIQUE trong Excel: với tiêu chí, bỏ qua khoảng trống

Cách tạo biểu đồ đường trong Excel

Hàm PPMT của Excel với các ví dụ về công thức

Tính ngày từ / trước ngày trong Excel

Ghi chú sử dụng

Để rút ngắn đường cong học tập và ngăn ngừa các lỗi phổ biến, vui lòng làm theo các hướng dẫn đơn giản sau:

• Công thức LET phải có một số đối số lẻ – một hoặc một số cặp tên / giá trị và một phép tính. Đối số cuối cùng phải luôn là một phép tính trả về kết quả cuối cùng.
• Trong công thức của bạn, hãy tuân theo cùng một quy tắc đặt tên như trong trình quản lý tên, tức là bắt đầu bằng một chữ cái, không có khoảng trắng, không có ký tự chấm câu, v.v.
• Để tránh nhầm lẫn, hãy tránh khai báo các tên đã được sử dụng trong trình quản lý tên. Nếu cùng một tên được gán bên trong LET và được xác định trong trình quản lý tên, phiên bản trình quản lý tên sẽ bị bỏ qua.
• Các tên được khai báo bằng LET chỉ hợp lệ trong phạm vi của công thức cụ thể đó.

Cách sử dụng hàm LET trong Excel – ví dụ về công thức

Và bây giờ, đã đến lúc xem xét các trường hợp sử dụng thực tế hơn và tiết lộ toàn bộ sức mạnh của chức năng mới.

Ví dụ 1. Công thức LET để giảm các phép tính lặp lại

Khi viết các công thức nhiều cấp, thường xảy ra trường hợp sử dụng cùng một biểu thức hoặc phép tính nhiều hơn một lần. Một ví dụ điển hình là các câu lệnh IF lồng nhau. Trong bối cảnh này, hãy xem LET có thể đơn giản hóa mọi thứ như thế nào.

Giả sử bạn có kết quả của các kỳ thi học sinh trong ba môn học khác nhau (cột B, C và D). Bạn muốn tìm điểm trung bình cho mỗi học sinh và cho điểm như trong bảng bên phải:

Các TRUNG BÌNH CỘNG hàm có thể dễ dàng tính giá trị trung bình cộng cho mỗi hàng:

AVERAGE(B2:D2)

Và sau đó, bạn xây dựng một câu lệnh IF lồng nhau dựa trên các tiêu chí trên.

=IF(AVERAGE(B2:D2)>249, "Excellent", IF(AVERAGE(B2:D2)>=200, "Good", IF(AVERAGE(B2:D2)>150, "Satisfactory", "Poor")))

Vấn đề là cùng một chức năng AVERAGE được lặp lại ba lần. Chà, có thể không thực sự là một vấn đề, mà là một sự phức tạp không cần thiết buộc Excel phải thực hiện cùng một phép tính ba lần.

Để tối ưu hóa công thức, chúng ta có thể gán tên cho hàm AVERAGE (giả sử, trung bình) và thay thế hàm bằng tên “cục bộ” này. Bằng cách này, trung bình được tính chỉ một lần giúp công thức chạy nhanh hơn:

=LET(avg, AVERAGE(B2:D2), IF(avg>249, "Excellent", IF(avg>=200, "Good", IF(avg>150, "Satisfactory", "Poor"))))

Nhập công thức vào E2, kéo công thức xuống qua E10 và bạn sẽ nhận được kết quả sau:

Hãy tưởng tượng bạn thêm một kỳ thi nữa (cột E) vào bảng nguồn. Đương nhiên, bạn muốn đưa điều đó vào giá trị trung bình. Nếu không có LET, bạn phải thực hiện điều chỉnh này trong kiểm tra logic của mọi hàm IF. Là một con người, bạn có thể bỏ qua điều gì đó và không cập nhật tất cả các tài liệu tham khảo một cách chính xác. Với LET, bạn chỉ thực hiện một thay đổi duy nhất:

=LET(avg, AVERAGE(B2:E2), IF(avg>249, "Excellent", IF(avg>=200, "Good", IF(avg>150, "Satisfactory", "Poor"))))

Ví dụ 2. LET với nhiều tên

Đây là một ví dụ khác cho thấy cách hàm LET có thể dễ dàng tạo các công thức phức tạp.

Giả sử bạn có một danh sách tên đầy đủ (cột A) mà bạn muốn trích xuất tên đệm. Công thức dưới đây thực hiện công việc một cách hoàn hảo, nhưng ngay từ cái nhìn đầu tiên, logic của nó khá mù mờ:

=MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) +1) - SEARCH(" ", A2) - 1)

Để cung cấp một số ngữ cảnh có ý nghĩa cho chính bạn và những người dùng khác, bạn có thể xác định một vài tên như:

• Họ và tên – A2
• không gian – TÌM KIẾM (“”, full_name)

Rõ ràng, ô A2 chứa tên đầy đủ cần được tách và TÌM KIẾM hàm trả về vị trí của ký tự khoảng trắng đầu tiên trong tên.

Và sau đó, chúng tôi thay thế tham chiếu ô và hàm TÌM KIẾM bằng các tên đã khai báo. Để dễ đọc hơn, tên và phép tính được bao bọc trên các dòng riêng biệt:

=LET(full_name, A2, space, SEARCH(" ", full_name),
MID(full_name, space + 1, SEARCH(" ", full_name, space + 1) - space - 1))

Nếu bạn quen thuộc với MID cú pháp, công thức trở nên dễ hiểu hơn nhiều:

Đến vị trí của khoảng trắng đầu tiên, bạn thêm 1 để bắt đầu trích xuất từ ​​ký tự tiếp theo (khoảng trắng + 1). Để có độ dài của tên đệm (tức là có bao nhiêu ký tự để trích xuất từ ​​tên đầy đủ), bạn xác định vị trí của khoảng trắng thứ hai bằng cách lồng một hàm TÌM KIẾM vào một hàm TÌM KIẾM khác, sau đó tìm sự khác biệt giữa vị trí của hai khoảng trắng và trừ 1 từ kết quả để loại bỏ khoảng trắng ở cuối (SEARCH (“”, full_name, space + 1) – space -1)).

Ví dụ 3. Hàm LET với mảng động

Trong các ví dụ trước, hàm LET hoạt động trên một ô duy nhất và chúng tôi đã sao chép công thức vào các ô bên dưới. Tuy nhiên, LET cũng có thể chấp nhận mảng làm đầu vào và tạo mảng làm đầu ra. Điều này có thể thực hiện được do công cụ tính toán mới của Excel 365 và mảng động.

Từ bảng dưới đây, giả sử bạn muốn nhận danh sách các bài kiểm tra sẽ được thực hiện trong n ngày, không bao gồm ngày hôm nay. Điều này có thể được thực hiện bằng cách sử dụng Chức năng FILTER với nhiều tiêu chí:

FILTER(data, (dates>today) * (dates<=today+n), "No results")

Tất cả những gì bạn cần làm là xác định các tên tương ứng:

• dữ liệu – A2: C19
• ngày – C2: C19
• hôm nay – HÔM NAY ()
• n – F3

Sau đó, đặt tên và công thức FILTER bên trong LET:

=LET(data, A2:C19, dates, C2:C19, today, TODAY(), n, F3,
FILTER(data, (dates>today) * (dates<=today+n), "No results"))

Kết quả là một mảng các bản ghi khớp với các tiêu chí đã chỉ định:

Hàm LET trong Excel không hoạt động

Nếu bạn không thể tìm thấy hàm LET trong Excel của mình hoặc công thức của bạn dẫn đến lỗi, điều đó có thể xảy ra vì những lý do sau:

Không có hàm LET trong Excel của tôi

Tại thời điểm viết bài, chức năng LET chỉ khả dụng cho Người dùng nội bộ Office. Khi giai đoạn beta hoàn thành, nó sẽ được phát hành cho tất cả người đăng ký Microsoft 365. Hàm không tương thích ngược và sẽ không xuất hiện trong các phiên bản Excel trước.

#TÊN? lỗi

Vì có rất ít khả năng ai đó có thể in sai tên của hàm, rất có thể vấn đề là với các tên được chỉ định. Hãy đảm bảo rằng bạn đã khai báo từng tên được sử dụng trong phép tính và tất cả các tên đều được viết đúng chính tả.

Đó là cách bạn có thể đơn giản hóa và tăng tốc độ tính toán của mình với sự trợ giúp của hàm LET. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!