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

Microsoft Excel có hàng ngàn hàm, nhưng hầu hết mọi người đều tập trung vào những hàm cơ bản, chẳng hạn như SUM và AVERAGE. Mặc dù những hàm này hoạt động tốt với các tác vụ đơn giản, nhưng có 3 hàm xử lý các tình huống phức tạp với ít công sức hơn. SEQUENCE, LET và LAMBDA không được sử dụng phổ biến, nhưng chúng giải quyết các vấn đề cụ thể mà nếu không sẽ đòi hỏi những giải pháp thay thế phức tạp hoặc các công thức dài dòng khó xem xét.

4. Hàm SEQUENCE tự động tạo dữ liệu

Tạo chuỗi số và ngày động

SEQUENCE tạo các mảng số tuần tự mà không cần nhập thủ công từng giá trị. Nếu bạn cần danh sách ID nhân viên, số hóa đơn hoặc phạm vi ngày, hàm này sẽ xử lý dễ dàng.

Cú pháp rất đơn giản:

=SEQUENCE(rows, [columns], [start], [step])

​​​​​Hãy cùng phân tích các tham số:

• rows: Xác định số lượng số bạn muốn theo chiều dọc.
• columns: Kiểm soát độ phân bổ theo chiều ngang - để trống nếu chỉ có một cột.
• start: Đặt số bắt đầu, mặc định là 1.
• step: Xác định khoảng cách giữa các số, cũng mặc định là 1.

Xét về tập dữ liệu bán hàng, SEQUENCE tỏ ra hữu ích khi tạo số tham chiếu. Ví dụ, công thức sau tạo ra các số từ 1 đến 32.

=SEQUENCE(32)

Tương tự, nếu cần bắt đầu từ 1001, bạn có thể sử dụng:

=SEQUENCE(32, 1, 1001)

Công thức này rất hữu ích với các chuỗi ngày. Công thức sau sẽ tạo ra 12 ngày liên tiếp bắt đầu từ ngày 1 tháng 1. Điều này tốt hơn việc nhập ngày thủ công cho báo cáo hàng tháng hoặc mốc thời gian dự án.

=SEQUENCE(12, 1, DATE(2025, 1, 1), 1)

Bạn cũng có thể chỉ tạo ngày làm việc bằng cách kết hợp SEQUENCE với các hàm DATE khác trong Excel, chẳng hạn như WORKDAY, cho những tình huống lập lịch phức tạp hơn.

3. Hàm LET giúp các công thức phức tạp dễ bảo trì

Loại bỏ các phép tính lặp lại và cải thiện khả năng đọc

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")))

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.

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))))