3 hàm Excel giúp bạn trở thành chuyên gia về bảng tính

Hàm LET gán tên cho các giá trị trong công thức. Do đó, nó loại bỏ các phép tính lặp lại và giúp công việc dễ đọc hơn. Thay vì viết cùng một biểu thức nhiều lần, bạn định nghĩa nó một lần và tham chiếu theo tên.

Cú pháp tuân theo mẫu sau:

=LET(name1, value1, [name2, value2, ...], calculation)

Bạn có thể định nghĩa nhiều biến bằng cách thêm các cặp tên-giá trị. Phép tính ở cuối sẽ sử dụng những biến được đặt tên này để tạo ra kết quả.

Nhìn vào tập dữ liệu bán hàng, giả sử bạn đang tính hoa hồng của một nhân viên bán hàng kèm theo tiền thưởng. Nếu không có LET, bạn sẽ viết:

=IF(G2*0.05>500, G2*0.05*1.1, G2*0.05)

Phép tính hoa hồng B2*0.05 xuất hiện hai lần. Với LET, nó trở nên rõ ràng hơn:

=LET(commission, G2*0.05, IF(commission>500, commission*1.1, commission))

Nó thực hiện cùng một phép tính nhưng định nghĩa "commission" một lần ở đầu. Bạn chỉ cần thay đổi tỷ lệ hoa hồng ở một nơi.

Đối với các phân tích biên lợi nhuận phức tạp, LET thậm chí còn hữu ích hơn. Ví dụ sau đây định nghĩa rõ ràng từng thành phần.

=LET(revenue, G2, costs, L2, margin, (revenue-costs)/revenue, IF(margin>0.3, "High", IF(margin>0.15, "Medium", "Low")))

Công thức này tính toán tỷ suất lợi nhuận dưới dạng phần trăm, sau đó phân loại thành High (trên 30%), Medium (15-30%) hoặc Low (dưới 15%). Mỗi thành phần đều có tên rõ ràng, giúp dễ dàng theo dõi logic.

Cách tiếp cận này giúp giảm một nửa độ phức tạp của công thức, đồng thời giúp bạn dễ dàng gỡ lỗi và chỉnh sửa bảng tính sau này.

2. Hàm LAMBDA tạo các hàm tùy chỉnh có thể tái sử dụng

Xây dựng các hàm tùy chỉnh cho logic nghiệp vụ lặp lại

LAMBDA tạo các hàm tùy chỉnh mà bạn có thể sử dụng nhiều lần trong workbook của mình. Thay vì sao chép công thức khắp nơi, bạn tạo một hàm duy nhất chấp nhận đầu vào và trả về kết quả tính toán.

Cú pháp là:

=LAMBDA(parameter1, [parameter2, ...], calculation)

Các tham số hoạt động như những trình giữ chỗ - khi gọi hàm, bạn truyền các giá trị thực tế thay thế những trình giữ chỗ này. Phép tính sử dụng các tham số này để tạo đầu ra.

Giả sử bạn thường xuyên tính điểm hiệu suất có trọng số. Bạn có thể tạo một hàm LAMBDA như sau:

=LAMBDA(sales, quota, weight, (sales/quota)*weight)

Nó tạo ra một hàm có thể tái sử dụng nhận 3 đầu vào: Doanh số thực tế, hạn ngạch bán hàng và một hệ số trọng số. Hàm trả về điểm hiệu suất có trọng số bằng cách chia doanh số cho hạn ngạch và nhân với trọng số. Đặt tên cho hàm này là "PerformanceScore" bằng Name Manager của Excel.

Giờ đây, bạn có thể gọi hàm này ở bất kỳ đâu trong workbook.

=PerformanceScore(B2, C2, 0.7)

Hàm này tính điểm hiệu suất bằng cách sử dụng doanh số, hạn ngạch và hệ số trọng số được cung cấp.

Để phân tích khu vực, bạn có thể xây dựng một hàm phân loại khu vực dựa trên doanh thu:

=LAMBDA(revenue, IF(revenue>100000, "High", IF(revenue>50000, "Medium", "Low")))

Hàm này phân loại doanh thu thành 3 mức: High cho doanh thu trên 100.000 USD, Medium cho doanh thu từ 50.000 đến 100.000 USD và Low cho doanh thu dưới 50.000 USD. Bạn có thể đặt tên nó là "Revenue" và sử dụng nó trong toàn bộ bảng tính của mình như sau:

=Revenue(J2)

Hàm LAMBDA cũng hoạt động với các hàm khác và cho phép bạn viết công thức dễ hiểu hơn bằng cách sử dụng tên mô tả thay vì những tham chiếu ô khó hiểu.

1. Kết hợp các hàm này để tạo ra các giải pháp mạnh mẽ

Xây dựng các công cụ phân tích kinh doanh toàn diện

Khi bạn sử dụng SEQUENCE, LET và LAMBDA cùng nhau, chúng giải quyết các vấn đề mà nếu không sẽ cần nhiều cột hỗ trợ hoặc công thức mảng. Sự kết hợp này tạo ra các giải pháp linh hoạt, dễ bảo trì.

Hãy cùng xem xét việc xây dựng một công cụ dự báo doanh số sử dụng dữ liệu bán hàng. Công thức sau đây tính toán dự báo doanh số 12 tháng cho một doanh số khởi điểm duy nhất. Công thức bắt đầu bằng cách định nghĩa hai biến chính với LET. Công thức lấy giá trị từ ô G2 làm điểm bán hàng cơ sở ban đầu.

=LET(base_sale, G2, growth_rate, L2, ProjectMonthly, LAMBDA(month, base_sale * (1 + growth_rate)^month), ProjectMonthly(SEQUENCE(12)))

Sau đó, công thức lấy tỷ lệ tăng trưởng hàng tháng từ L2 là 0,04 (4%). Bạn có thể thay đổi giá trị này để mô hình hóa các kịch bản khác nhau. Sau đó, công thức định nghĩa một hàm nhỏ có thể tái sử dụng được gọi là ProjectMonthly. Hàm này tính toán doanh số dự kiến ​​cho một tháng nhất định dựa trên doanh số cơ sở và tỷ lệ tăng trưởng.

Hơn nữa, công thức này gọi hàm ProjectMonthly và truyền SEQUENCE(12) vào hàm. Thao tác này tạo ra một mảng các số từ 1 đến 12, và LAMBDA sẽ tự động áp dụng phép tính của nó cho mỗi số trong chuỗi đó.

Đây là một công cụ tính tiền thưởng thực tế, tính toán tiền thưởng dựa trên việc đạt được mục tiêu.

=LAMBDA(sales, target, LET(ratio, sales/target, IF(ratio>=1.2, sales*0.08, IF(ratio>=1, sales*0.05, 0))))

Hãy bắt đầu từ những điều đơn giản, sau đó dần dần nâng cao độ phức tạp!

Các chức năng này hoạt động hiệu quả nhất khi được kết hợp một cách cẩn thận. Hãy bắt đầu với các ứng dụng đơn giản - sử dụng SEQUENCE để tạo dữ liệu thử nghiệm, LET để loại bỏ những phép tính lặp lại và LAMBDA cho các quy tắc nghiệp vụ mà bạn sử dụng thường xuyên. Khi đã quen thuộc với từng chức năng riêng lẻ, bạn sẽ tìm thấy những cơ hội tự nhiên để kết hợp chúng thành các giải pháp phức tạp hơn.

Quá trình học tập không quá khó khăn, nhưng lợi ích thu được rất đáng kể. Bảng tính của bạn sẽ trở nên đáng tin cậy hơn, dễ kiểm toán hơn và dễ sửa đổi hơn khi yêu cầu nghiệp vụ thay đổi. Đó là lý do tại sao 3 hàm này đặc biệt có giá trị đối với bất kỳ ai thường xuyên làm việc với dữ liệu.