Những hàm Excel này sẽ giúp logic phức tạp trở nên đơn giản hơn!
Nếu bạn cảm thấy mệt mỏi với các phép tính lặp đi lặp lại, các công thức khó đọc và các bảng tính Excel chậm chạp, thì hàm LET chính là giải pháp của bạn. Nó gán các tên đơn giản cho các phép tính phức tạp, giúp các công thức rõ ràng hơn, dễ cập nhật hơn, và nhanh hơn nhiều bằng cách chỉ tính toán biểu thức một lần duy nhất.
Hàm LET có sẵn cho những người dùng Excel 2021 trở lên, Excel cho Microsoft 365, Excel cho web, hoặc các ứng dụng di động và máy tính bảng Excel.
Xem thêm: Hướng dẫn 4 thủ thuật Paste Special giúp bạn tiết kiệm thời gian trong Microsoft Excel
1. Cú pháp hàm LET
Hàm LET yêu cầu ít nhất ba đối số:
=LET(name_1,value_1,[name_2],[variable_2],...,calculation)Trong đó:
tên_1 (bắt buộc) là tên của biến thứ nhất.
giá_trị_1 (bắt buộc) là giá trị hoặc phép tính được gán cho tên biến thứ nhất.
tên_2 và giá_trị_2 (tùy chọn) là cặp tên-giá trị thứ hai, tối đa lên đến 126 cặp.
tính_toán (bắt buộc) là một phép tính sử dụng các tên và giá trị đã được gán.
Các tên và giá trị được gán trong một công thức LET chỉ áp dụng cho công thức đó, nghĩa là chúng không thể được tham chiếu bởi ô khác. Tuy nhiên, các tên và giá trị tương tự có thể được sử dụng lại trong các công thức LET khác.
Tên biến phải chứa ít hơn 255 ký tự (mặc dù bạn khó có thể cần đến nhiều như vậy!), không được chứa khoảng trắng hoặc hầu hết các ký tự dấu câu, phải bắt đầu bằng một chữ cái và không được trùng với một tham chiếu ô hoặc một tên đã được gán ở nơi khác trong Trình quản lý Tên (Name Manager). Mặc dù tên có thể chứa một ký tự đơn lẻ, ký tự đó không được là “r” hoặc “c” (hoặc các chữ cái viết hoa tương ứng), vì những chữ cái này được sử dụng làm phím tắt ở những nơi khác trong Excel.
2. Cách hàm LET hoạt động (và tại sao nó đáng để sử dụng)
Hãy để tôi chỉ cho bạn cách thức hoạt động này bằng cách sử dụng một biểu thức đại số rất cơ bản. Giả sử mục tiêu của bạn là tính tổng của hai số, 2 và 3. Để làm điều này, bạn có thể định nghĩa $x$ là 2 và $y$ là 3 trong hàm LET, trước khi sử dụng hai biến này trong phép cộng:
=LET(x,2,y,3,x+y)
Excel xử lý công thức này từ trái sang phải, lưu trữ các biến đã đặt tên ($x$ và $y$) cục bộ. Sau đó, khi đạt đến đối số cuối cùng, đối số này phải sử dụng các biến đã xác định, Excel trả về một kết quả duy nhất bằng cách cộng chúng lại với nhau.
Tất nhiên, với một phép tính đơn giản như thế này, bạn có thể chỉ cần gõ:
=2+3Tuy nhiên, giải thích hàm LET bằng các thuật ngữ đơn giản này giúp bạn hiểu nó trong các tình huống thực tế dễ dàng hơn nhiều. Hơn nữa, nó làm nổi bật các lợi ích của hàm:
Logic dễ dàng hơn: Việc đặt tên cho các biến khi chúng được lặp lại trong các phép tính giúp công thức dễ hiểu hơn — bạn có thể nhanh chóng thấy logic và những gì đang diễn ra, ngay cả khi công thức LET cuối cùng có thể dài hơn cách thay thế.
Bảo trì công thức hiệu quả hơn: Do logic được đơn giản hóa, các công thức LET dễ cập nhật hơn nếu cần điều chỉnh. Thay vì phải cập nhật cùng một biểu thức nhiều lần, bạn chỉ cần thực hiện một thay đổi duy nhất.
Hiệu suất tốt hơn: Khi cùng một biểu thức được lặp lại trong một công thức, nó được tính toán nhiều lần. Tuy nhiên, khi được đặt tên trong hàm LET, nó chỉ được tính toán một lần. Kết quả là, bảng tính phải thực hiện ít phép tính tổng thể hơn, một lợi ích đặc biệt đáng chú ý khi bạn sử dụng nhiều công thức dài hoặc có một tập dữ liệu lớn.
Bây giờ, hãy áp dụng nó vào một số tình huống trong thế giới thực.
3. Thay thế các đối số IF lặp lại
Một trong những lợi ích lớn nhất của hàm LET là khả năng ngăn chặn các phép tính lặp lại, chẳng hạn như khi sử dụng các câu lệnh điều kiện IF.
Hãy tưởng tượng bạn đang tính toán tiền thưởng doanh số cho nhân viên của mình. Nếu doanh số thuần (doanh số gộp trừ đi hàng trả lại) của một người nào đó vượt quá $10,000, tiền thưởng là 5%. Nếu không, tiền thưởng là 2%.
Không sử dụng hàm LET, đây là những gì bạn có thể nhập vào ô D2 (và tự động điền xuống cột D):
=IF(B2-C2>10000,(B2-C2)*0.05,(B2-C2)*0.02)
Tôi sử dụng tham chiếu ô trực tiếp trong bài viết này để minh họa cách các công thức hoạt động. Tuy nhiên, hàm LET vẫn hoạt động tốt khi dữ liệu được định dạng dưới dạng Bảng Excel (Excel table) và công thức sử dụng Tham chiếu có cấu trúc (structured references).
Mặc dù phương pháp này hoạt động, nhưng nó gây ra ba vấn đề lớn:
Kém hiệu quả: Excel thực hiện phép trừ
B2-C2ba lần mỗi khi ô tính toán lại. Điều này lãng phí thời gian xử lý, đặc biệt trong các bảng tính lớn.Khó đọc: Công thức gây rối mắt vì logic của hàm IF bị che khuất bởi phép tính lặp lại.
Nguy cơ lỗi cao: Nếu bạn cần cập nhật phép trừ
B2-C2, ví dụ như trừ thêm một khoản phí, bạn sẽ cần phải chỉnh sửa công thức thủ công ở ba nơi, làm tăng nguy cơ gõ sai.
Hàm LET có thể khắc phục những trở ngại này:
=LET(Net_Sales,B2-C2, IF(Net_Sales>10000,Net_Sales*0.05,Net_Sales*0.02))
Trong đó:
Net_Sales,B2-C2tạo ra một biến có tên là Net_Sales, được tính bằng cách trừ hàng trả lại (C2) khỏi doanh số gộp (B2).IF(Net_Sales>10000,Net_Sales*0.05,Net_Sales*0.02)sử dụng cú pháp tương tự như công thức không dùng LET, nhưng sử dụng biến đã đặt tên thay vì lặp lại phép tính.
Khi gõ công thức trong thanh công thức (formula bar), nhấn Alt+Enter để chuyển sang dòng tiếp theo sau mỗi phân đoạn hoặc đối số. Điều này giúp đảm bảo công thức của bạn được cấu trúc chính xác.
Nói cách khác, hàm LET tạo ra một biến cục bộ, có tên (Net_Sales) chứa kết quả của phép tính doanh số thuần trung gian. Sau đó, phép tính này được sử dụng ba lần trong phép tính IF mà không cần phải tính toán lại hoặc viết lại. Đúng, công thức LET dài hơn so với cách thay thế truyền thống, nhưng điều này là có chủ đích—mục tiêu của việc sử dụng LET không phải là rút ngắn độ dài tổng thể mà là để tối đa hóa sự rõ ràng và hiệu quả.
Khi bạn tự động điền công thức LET xuống phần còn lại của cột D, biến sẽ tự động cập nhật theo mặc định để áp dụng cho hàng mà nó đang xét.
4. Sử dụng hàm LET với nhiều tên biến
Mặc dù ví dụ trước của tôi chỉ sử dụng một biến duy nhất để làm gọn một phép tính lặp lại, sức mạnh thực sự của hàm LET nhanh chóng trở nên rõ ràng khi bạn định nghĩa nhiều tên biến.
Lần này, mục tiêu là tính toán tỷ suất lợi nhuận ròng hàng tuần (weekly net profit margin). Để làm được điều này, bạn cần tuân theo logic tuần tự:
Tính lợi nhuận gộp (doanh số gộp trừ đi giá vốn hàng bán).
Sau đó, tính thu nhập ròng (lợi nhuận gộp trừ đi chi phí hoạt động).
Cuối cùng, tính tỷ suất lợi nhuận ròng (thu nhập ròng chia cho doanh số gộp).
Không sử dụng hàm LET, đây là cách bạn sẽ làm:
=((B2-C2)-D2)/B2
Mặc dù thoạt nhìn công thức này có vẻ đơn giản, nhưng nó có thể gây khó khăn về lâu dài:
Tính thiếu minh bạch và khó kiểm tra: Công thức hoàn toàn được tạo thành từ các tham chiếu ô và dấu ngoặc đơn. Ngay cả khi bạn sử dụng tham chiếu có cấu trúc thay thế, để hiểu lợi nhuận gộp được tính như thế nào, bạn sẽ cần phải hiểu và phân tích cấu trúc lồng nhau. Ngoài ra, nếu tỷ suất lợi nhuận thu được không chính xác, không có cách nào dễ dàng để kiểm tra giá trị thu nhập ròng mà không tìm và cô lập phần đó của công thức.
Nguy cơ lỗi cao: Giống như tất cả các phương án thay thế LET, nếu bạn cần điều chỉnh một phần của công thức xuất hiện nhiều hơn một lần (trong trường hợp này là tham chiếu đến ô B2), rất dễ mắc lỗi đánh máy hoặc vô tình chỉ thay đổi một trường hợp.
Mặt khác, sử dụng hàm LET xác định rõ ràng từng bước trung gian. Dưới đây là công thức cho ô E2:
=LET(Sales,B2, COGS,C2, OpEx,D2, GrossP,Sales-COGS, NetI,GrossP-OpEx, NetI/Sales)
Trong đó:
Sales,B2cho Excel biết ô B2 chứa biến có tên Sales (Doanh số).COGS,C2cho Excel biết ô C2 chứa biến có tên COGS (Giá vốn hàng bán).OpEx,D2cho Excel biết ô D2 chứa biến có tên OpEx (Chi phí hoạt động).GrossP,Sales-COGScho Excel biết biến có tên GrossP (Lợi nhuận Gộp) được tính bằng cách trừ COGS khỏi Sales.NetI,GrossP-OpExcho Excel biết biến có tên NetI (Thu nhập Ròng) được tính bằng cách trừ OpEx khỏi GrossP.NetI/Salescho Excel biết phép tính cuối cùng là chia NetI cho Sales.
Một lần nữa, bạn sẽ nhận thấy rằng công thức LET dài hơn và phức tạp hơn về mặt hình ảnh so với công thức tham chiếu-ngoặc đơn truyền thống. Tuy nhiên, nó thể hiện logic tuần tự rõ ràng hơn, dễ chỉnh sửa hơn nếu ô đầu vào doanh số thay đổi, và thực tế là nó tự tài liệu hóa (nói cách khác, các biến được xác định rõ ràng) có nghĩa là công thức luôn dễ hiểu, ngay cả nhiều năm sau đó, đối với người không viết ra nó.
Bạn có thể áp dụng cùng một logic đa tên cho ví dụ về doanh số thuần mà tôi đã sử dụng ở trên:
=LET(Net_Sales,B2-C2, Threshold,$F$2, BHigh,$G$2, BLow,$H$2, IF(Net_Sales>Threshold,Net_Sales*BHigh,Net_Sales*BLow))
Trong đó, các biến Net_Sales, Threshold, BHigh, và BLow được định nghĩa, trước khi hàm IF sử dụng các biến này để xác định tổng tiền thưởng.
Tham chiếu tuyệt đối ($) được sử dụng khi xác định các biến cố định để khi công thức được lặp lại xuống một cột, các ô chính xác vẫn được xác định.
5. Sử dụng hàm LET với mảng động (Dynamic Arrays)
Tôi đã nói nhiều lần rằng hàm LET rất cần thiết để tối ưu hóa hiệu suất. Tuy nhiên, điều này đúng hơn bao giờ hết khi làm việc với các hàm mảng động (dynamic array functions), chẳng hạn như FILTER, SORT và UNIQUE. Các hàm mảng động tiêu tốn nhiều tài nguyên vì chúng phải quét, xử lý và trải ra (spill) toàn bộ mảng kết quả cùng một lúc, gây áp lực đáng kể lên hệ thống của bạn. Hàm LET có thể khắc phục vấn đề này bằng cách đảm bảo phép tính chỉ diễn ra một lần duy nhất.
Hãy tưởng tượng bạn muốn phân tích một tập hợp con của một tập dữ liệu lớn. Cụ thể, mục tiêu của bạn là tính toán phạm vi giá (range of prices) (cột C) cho các mặt hàng được bán ở khu vực Phía Tây (được mã hóa là W trong cột B).
Đây là công thức không có LET mà bạn có thể nhập vào ô F2:
=MAX(FILTER(C2:C17,B2:B17="W")-MIN(FILTER(C2:C17,B2:B17="W")))
Có hai vấn đề chính với công thức này:
Kém hiệu quả: Hàm FILTER được gọi hai lần: một lần bên trong hàm MAX, và một lần bên trong hàm MIN. Nếu tập dữ liệu lớn hơn, điều này sẽ làm chậm đáng kể bảng tính của bạn.
Nguy cơ lỗi cao: Phạm vi giá (
C2:C17) và tiêu chí (B2:B17) bị lặp lại, vì vậy nếu sau này bạn cần cập nhật chúng, bạn sẽ phải thực hiện điều đó hai lần.
Sử dụng phương án LET thay thế, mảng đã được lọc được đặt tên là một biến, do đó, thao tác tiêu tốn tài nguyên chỉ được thực hiện một lần duy nhất:
=LET(WSales, FILTER(C2:C17,B2:B17="W"),MAX(WSales)-MIN(WSales))
Trong đó:
WSales,FILTER(C2:C17,B2:B17="W")yêu cầu Excel lọc các mức giá sao cho chỉ bao gồm những mức giá ở khu vực W, và gọi bộ lọc này là WSales.MAX(WSales)-MIN(WSales)trừ số nhỏ nhất trong bộ lọc WSales này khỏi số lớn nhất.
Ngoài việc chỉ cần lọc một lần, công thức mới còn làm rõ phép tính cuối cùng đang thực hiện điều gì. Hơn nữa, bạn có thể yên tâm rằng mình đang nhận được các số liệu tối đa và tối thiểu chính xác trong phép tính vì bạn đã sử dụng lại cùng một mảng đã được lưu trữ (WSales).
Các hàm LET và LAMBDA thường được thảo luận cùng nhau, nhưng chúng phục vụ các mục đích rất khác biệt.
Hàm LET tạo ra các biến cục bộ để sử dụng trong phạm vi một công thức duy nhất.
Hàm LAMBDA định nghĩa một tên hoạt động như một hàm tùy chỉnh hoàn toàn mới và có thể tái sử dụng.
Vì vậy, nếu mục tiêu của bạn là đơn giản hóa và tối ưu hóa một công thức phức tạp duy nhất trong một ô, bạn nên sử dụng LET. Mặt khác, nếu bạn đang muốn tạo một hàm có thể tái sử dụng, hãy chọn LAMBDA.
6. Kết luận
Những hàm Excel được giới thiệu trong bài viết chính là “vũ khí” mạnh mẽ giúp bạn đơn giản hóa các yêu cầu tính toán phức tạp, tối ưu quy trình xử lý dữ liệu và nâng cao hiệu quả công việc. Khi hiểu rõ cách vận dụng từng hàm vào đúng tình huống, bạn sẽ tiết kiệm đáng kể thời gian, giảm sai sót và biến các bài toán logic rối rắm thành những thao tác gọn gàng, tự động hóa hơn bao giờ hết.
Xem thêm: Hướng dẫn 4 thủ thuật Paste Special giúp bạn tiết kiệm thời gian trong Microsoft Excel
Dù bạn là người mới bắt đầu hay đã sử dụng Excel lâu năm, việc chủ động làm quen với các hàm logic nâng cao sẽ giúp bảng tính của bạn trở nên thông minh hơn, linh hoạt hơn và chuyên nghiệp hơn trong mọi tác vụ phân tích dữ liệu.
COHOTECH luôn mong muốn mang đến cho bạn những kiến thức hữu ích cùng các giải pháp công nghệ chất lượng để hỗ trợ học tập và công việc. Nếu bạn có câu hỏi hoặc muốn chia sẻ mẹo dùng Excel hay hơn, đừng ngại để lại bình luận. Và hãy chia sẻ bài viết để nhiều người khác cùng khám phá những hàm Excel cực kỳ hữu dụng này!
214 Tân Phước, Phường 6, Quận 10, TPHCM