Những thủ thuật Excel cho phép bạn viết công thức dễ dàng hơn

Nếu bạn đã từng nhìn chằm chằm vào một công thức Excel dài ngoằng và nghĩ rằng "có cách viết nào tốt hơn không", thì tin vui là có. Hay sử dụng LAMBDA và LET -  bạn không cần phải đến lớp dạy lập trình để học cách sử dụng chúng.

Sử dụng LET để dọn dẹp các công thức lộn xộn

Bạn đã bao giờ xây dựng một công thức dài đến mức phải có thanh cuộn chưa? Hàm LET có thể khắc phục điều đó.

LET cho phép bạn gán tên cho các phần của công thức - giống như đặt biệt danh cho những giá trị hoặc phép tính. Thay vì lặp lại cùng một công thức con 5 lần, bạn chỉ cần định nghĩa công thức đó một lần, đặt tên cho công thức rồi sử dụng lại tên đó trong suốt quá trình tính toán.

Ví dụ sẽ sử dụng LET để chấm điểm cho học sinh. Nếu không có LET, công thức sẽ trở nên lộn xộn rất nhanh:

=IFS(AVERAGE(B2:E2) > 85, "Grade A", AVERAGE(B2:E2) >= 70, "Grade B", AVERAGE(B2:E2)

Đây là 3 lệnh gọi AVERAGE(B2:E2) giống hệt nhau, khá kém hiệu quả và khó đọc. Hàm LET cho phép chúng ta định nghĩa hàm này một lần:

=LET(student_average, AVERAGE(B2:E2), IFS(student_average > 85, "Grade A",
student_average >= 70, "Grade B", student_average < 70, "Grade C"))

Bây giờ công thức gọn hơn, dễ đọc và dễ bảo trì hơn. Nếu cập nhật logic student_average, thay đổi sẽ được áp dụng trong toàn bộ bảng.

Bạn có thể định nghĩa tối đa 126 biến trong một hàm LET, điều này làm cho nó hoàn hảo để tính toán các số liệu phức tạp, xây dựng dashboard ô đơn chứa đầy logic hoặc thực sự hiểu một công thức mà bạn đã viết cách đây 6 tháng.

Sử dụng LAMBDA để tạo các hàm Excel của riêng mình

Nhiều người rất thích LAMBDA vì nó cho phép họ lấy một công thức khổng lồ, xấu xí và biến nó thành thứ gì đó dễ quản lý như:

=GrowthRate(B2, C2)

Bạn định nghĩa nó một lần, lưu nó với một tên và sử dụng lại nó trên toàn bộ workbook của mình. Khi cần điều chỉnh logic, bạn thay đổi nó ở một vị trí (thông qua Name Manager của Excel) và bản cập nhật sẽ áp dụng ở mọi nơi.

Cách xây dựng hàm tùy chỉnh đầu tiên

Quy trình này đơn giản hơn bạn nghĩ:

1. Khởi chạy workbook mà bạn muốn sử dụng hàm tùy chỉnh.
2. Thay thế các giá trị thực của bạn bằng tên giữ chỗ (như "price" và "rate")
3. Kiểm tra hàm LAMBDA trong bất kỳ ô nào để xem nó có hoạt động không.
4. Nhấn Ctrl + F3 để mở Name Manager, nhấp vào New và dán chuỗi LAMBDA của bạn vào trường Refers To.
5. Đặt bất kỳ tên nào bạn thích cho hàm mới của bạn và đưa vào bất kỳ comment nào có thể hữu ích sau này.

Bây giờ bạn có thể nhập công thức bên dưới, giống như bất kỳ hàm Excel tích hợp nào:

=YourFunctionName()

Giả sử bạn là một gia sư tự do và liên tục tính điểm. Bạn có thể phải sử dụng một công thức như thế này:

=IFS(A1>=80,"Honors", A1>=50,"Pass", A1 < 50,"Fail")

Thay vì nhập code này mỗi lần, bạn có thể tạo một hàm Score Calculator:

=LAMBDA(score, IFS(score>=80,"Honors", score>=50,"Pass", score < 50,"Fail"))

Lưu mục này trong Name Manager dưới dạng "Calculator" và bây giờ bạn chỉ cần nhập nội dung bên dưới vào bất kỳ đâu trong workbook của mình:

=Calculator(B2)

Bạn thậm chí có thể sử dụng LAMBDA cho các hàm thú vị hơn, chẳng hạn như hàm xóa từng ký tự không mong muốn hoặc hàm tiếp tục nhân cho đến khi đạt 1.

Những hàm thú vị này thoạt đầu có vẻ phức tạp, nhưng sau khi lưu, tất cả các hàm LAMBDA đều có thể chạy rất đơn giản. Và nhờ có chatbot AI, bạn có thể dễ dàng tạo các công thức và chuỗi truy vấn cần thiết.

LET và LAMBDA: Công thức thân thiện với con người

LET và LAMBDA hoạt động độc lập rất tuyệt. Nhưng khi bạn kết hợp chúng lại, chúng là một cặp đôi quyền lực.

Khi tạo hàm LAMBDA, bạn có thể sử dụng LET bên trong chuỗi để đặt tên cho các phần logic. Hãy nghĩ về nó giống như bạn đang chia một công thức phức tạp thành các bước rõ ràng trước khi nướng chiếc bánh cuối cùng. Hãy đặt tên cho từng thành phần để mỗi bước có ý nghĩa.

Giả sử bạn cần tính diện tích tam giác bằng công thức Heron, đây là một phép tính nổi tiếng là lộn xộn:

=SQRT(((a+b+c)/2)*(((a+b+c)/2)-a)*(((a+b+c)/2)-b)*(((a+b+c)/2)-c))

Chúc may mắn khi gỡ lỗi sau 6 tháng nữa. Rất may, với LET và LAMBDA, bạn có thể dọn dẹp công thức như sau:

=LAMBDA(a, b, c, LET(s, (a + b + c) / 2, SQRT(s * (s - a) * (s - b) * (s - c))))

Thay vì lặp lại (a+b+c)/2 bốn lần, giờ đây bạn có s xuất hiện 4 lần, dễ hiểu hơn nhiều. Trên hết, bạn có thể lưu toàn bộ hàm LAMBDA trong Name Manager. Giờ đây, phép tính lộn xộn đó trở nên dễ đọc, hiệu quả và dễ sử dụng lại.

Bạn cũng có thể xây dựng toàn bộ báo cáo bên trong một ô duy nhất bằng cách sử dụng kết hợp này. Hãy tưởng tượng một hàm lấy dữ liệu bán hàng và đưa ra bản tóm tắt hàng tháng theo một ô được định dạng:

=LAMBDA(p_col, s_col, LET(_total, SUM(s_col), top, INDEX(pcol, XMATCH(MAX(s_col), s_col)), TEXTJOIN(CHAR(10), TRUE, "Report", "Total: " & TEXT(_total, "$#,##0.00"), "Top: " & _top)))

Lưu hàm này dưới dạng MonthlyReport và gọi hàm này bằng công thức bên dưới. Với một hàm trong một ô, bạn sẽ có được báo cáo hoàn chỉnh.

=MonthlyReport(B:B, C:C)

Khi bắt đầu sử dụng LET và LAMBDA, bạn sẽ ngừng suy nghĩ máy móc. LET cung cấp cấu trúc cho các công thức bằng cách đặt tên cho từng bước, trong khi LAMBDA biến logic thành các hàm có thể tái sử dụng, thân thiện với con người. Cùng nhau, chúng giúp các công thức Excel dễ viết, dễ đọc và dễ sử dụng hơn.

Vì vậy, lần tới khi bạn thấy mình đang viết một công thức trông giống như trong một cuốn tiểu thuyết khoa học viễn tưởng, hãy dừng lại và tự hỏi: Có thể dùng LAMBDA không? Rất có thể, câu trả lời là có.