Báo cáo lương tháng 8 của tôi hiển thị mức trung bình 87 triệu đồng mỗi nhân viên. Giám đốc nhìn số liệu và hỏi tại sao chi phí nhân sự tăng vọt 40% so với tháng trước. Thực tế thì không có gì tăng cả. Tôi đang dùng hàm AVERAGE cho dữ liệu có hai giám đốc lương 300 triệu trong danh sách 50 nhân viên. Con số 87 triệu không phản ánh đúng thực tế khi 48 người còn lại có mức lương từ 15 đến 35 triệu.
Vấn đề với hàm AVERAGE
Hàm AVERAGE tính tổng tất cả các giá trị rồi chia cho số lượng. Công thức đơn giản này hoạt động tốt khi dữ liệu phân bố đều. Nhưng khi có giá trị ngoại lai, kết quả bị méo hoàn toàn.
Với dữ liệu lương của tôi:
- 48 nhân viên: 15-35 triệu đồng
- 2 giám đốc: 300 triệu đồng
- Hàm AVERAGE cho kết quả: 87 triệu đồng
Kết quả này vô nghĩa khi mô tả mức lương đại diện. Không ai trong công ty có mức lương gần 87 triệu ngoại trừ hai người ở vị trí quản lý cao nhất. Tôi từng thử loại bỏ hai giá trị này thủ công trước khi tính, nhưng mỗi khi dữ liệu thay đổi lại phải xóa và tính lại.
Hàm TRIMMEAN loại bỏ ngoại lai tự động
Hàm TRIMMEAN tính trung bình sau khi loại bỏ một phần trăm giá trị cao nhất và thấp nhất. Microsoft thiết kế hàm này cho phân tích thống kê khi dữ liệu có ngoại lai.
Cú pháp hàm:
=TRIMMEAN(array, percent)
Tham số đầu tiên là vùng dữ liệu cần tính. Tham số thứ hai là tỷ lệ phần trăm cần loại bỏ. Nếu bạn nhập 0.1 tức là loại 10% tổng số điểm dữ liệu – 5% từ đầu và 5% từ cuối sau khi sắp xếp.
Điểm quan trọng: Hàm làm tròn số lượng điểm cần loại xuống bội số gần nhất của 2. Với 50 giá trị và phần trăm 0.1 thì 50 × 10% = 5 điểm. Hàm làm tròn xuống 4 để loại 2 giá trị cao nhất và 2 giá trị thấp nhất. Điều này đảm bảo tính đối xứng khi loại bỏ.
Áp dụng vào báo cáo lương
Tôi thay công thức từ:
=AVERAGE(B2:B51)
Sang:
=TRIMMEAN(B2:B51, 0.04)
Với 50 nhân viên, phần trăm 0.04 tức 4% hay 2 điểm dữ liệu. Hàm loại 1 giá trị cao nhất và 1 giá trị thấp nhất. Đúng là hai mức lương 300 triệu của giám đốc.
Kết quả mới: 24.8 triệu đồng – con số thực tế phản ánh mức lương đại diện của nhân viên. Độ lệch giảm từ 62 triệu (87 triệu so với thực tế 25 triệu) xuống chỉ còn 0.2 triệu. Tính chính xác tăng gấp đôi khi so sánh độ lệch tương đối.
So sánh với các phương pháp khác
Hàm MEDIAN trả về giá trị ở giữa danh sách đã sắp xếp. Với dữ liệu lương, MEDIAN cho kết quả 25 triệu – rất gần với TRIMMEAN. Nhưng MEDIAN hoàn toàn bỏ qua phân bố của 49 giá trị còn lại. Nếu 24 người có lương 15 triệu và 24 người có lương 35 triệu, MEDIAN vẫn trả về giá trị giữa mà không phản ánh sự chênh lệch này.
TRIMMEAN tốt hơn vì:
- Tính đến toàn bộ dữ liệu sau khi loại ngoại lai
- Nhạy cảm với phân bố giá trị
- Linh hoạt điều chỉnh phần trăm loại bỏ
Tôi thử với nhiều phần trăm khác nhau:
- 0.02 (loại 1 điểm từ mỗi đầu): 24.8 triệu
- 0.1 (loại 5 điểm từ mỗi đầu): 24.2 triệu
- 0.2 (loại 10 điểm từ mỗi đầu): 23.1 triệu
Khi tăng phần trăm, kết quả hơi giảm vì loại thêm các mức lương cao trong nhóm trưởng phòng. Với dữ liệu lương, 0.04 đến 0.1 cho kết quả hợp lý nhất.
Trường hợp thực tế khác
Báo cáo hiệu suất bán hàng tháng 9 có vấn đề tương tự. Một nhân viên bán được 200 đơn hàng nhờ khách hàng doanh nghiệp lớn. 15 người còn lại bán từ 8 đến 25 đơn. Hàm AVERAGE cho kết quả 23 đơn trên mỗi người, khiến ban quản lý nghĩ hiệu suất chung tốt.
Sau khi chuyển sang TRIMMEAN với phần trăm 0.12:
=TRIMMEAN(C2:C17, 0.12)
Kết quả: 16 đơn trên mỗi người. Con số này chính xác hơn và giúp quản lý nhận ra cần cải thiện đào tạo cho đội ngũ bán hàng chính.
Khi nào không nên dùng TRIMMEAN
Hàm không phù hợp khi:
- Dữ liệu phân bố đều không có ngoại lai
- Bạn cần giá trị chính xác tuyệt đối
- Các giá trị cực đoan là hợp lệ và quan trọng
Ví dụ báo cáo tổng doanh thu tháng. Nếu một chi nhánh thực sự bán được gấp 10 lần các chi nhánh khác, đây là dữ liệu hợp lệ cần tính vào tổng. Dùng TRIMMEAN trong trường hợp này sẽ che giấu hiệu suất xuất sắc của chi nhánh đó.
Trước khi áp dụng, tôi luôn vẽ biểu đồ phân bố hoặc dùng hàm MIN và MAX để xem có ngoại lai không. Nếu MIN là 5 và MAX là 8 trong dữ liệu 100 điểm, không cần TRIMMEAN vì không có giá trị nổi bật bất thường.
Lỗi thường gặp
Lỗi đầu tiên tôi mắc phải là nhập phần trăm lớn hơn 1. Hàm TRIMMEAN chỉ chấp nhận giá trị từ 0 đến 1. Nếu bạn nhập 10 thay vì 0.1, Excel trả về lỗi #NUM!. Tương tự với phần trăm âm.
Lỗi thứ hai là không hiểu cách làm tròn. Với 25 giá trị và phần trăm 0.1 thì 25 × 10% = 2.5 điểm. Hàm làm tròn xuống 2, loại 1 giá trị mỗi đầu. Nhiều người nghĩ hàm sẽ loại 2 và 3 giá trị, dẫn đến nhầm lẫn khi kết quả không khớp với dự đoán.
Lỗi thứ ba là dùng TRIMMEAN cho dữ liệu phi số. Nếu vùng dữ liệu chứa văn bản hoặc ô trống, hàm bỏ qua chúng tự động. Nhưng nếu toàn bộ vùng là văn bản, Excel trả về lỗi #DIV/0!.
Tích hợp vào quy trình báo cáo
Tôi không thay thế hoàn toàn AVERAGE bằng TRIMMEAN. Thay vào đó, báo cáo hiện có cả hai giá trị với nhãn rõ ràng:
- Trung bình thô (AVERAGE): Hiển thị tất cả dữ liệu
- Trung bình điều chỉnh (TRIMMEAN): Loại bỏ 4% ngoại lai
Cách này giúp người đọc hiểu rằng số liệu đã được xử lý. Tôi thêm ghi chú nhỏ bên dưới: “Trung bình điều chỉnh loại bỏ 2 giá trị cao nhất và thấp nhất để giảm ảnh hưởng của ngoại lai.”
Với các dashboard tự động cập nhật, TRIMMEAN đặc biệt hữu ích. Khi thêm dữ liệu mới mỗi tháng, hàm tự động tính lại và loại ngoại lai mới nếu có. Không cần can thiệp thủ công như trước đây.
Tương thích và phiên bản
Hàm TRIMMEAN có sẵn từ Excel 2007 trở về sau, bao gồm Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, và Excel 2010. Hàm hoạt động giống nhau trên Windows và Mac. Phiên bản web Excel.com cũng hỗ trợ đầy đủ.
Nếu bạn chia sẻ file cho người dùng phiên bản cũ hơn Excel 2007, hàm sẽ trả về lỗi #NAME!. Trong trường hợp này, cần tính thủ công bằng cách sắp xếp dữ liệu, loại giá trị đầu cuối, rồi dùng AVERAGE cho phần còn lại.
Hàm tương tự trong Google Sheets cũng có tên TRIMMEAN với cú pháp giống hệt Excel. Tôi dùng cả hai công cụ và chưa thấy khác biệt nào về kết quả tính toán.
Công thức nâng cao
Kết hợp TRIMMEAN với các hàm khác tạo ra phân tích mạnh mẽ hơn. Công thức này tính trung bình có điều kiện và loại ngoại lai:
=TRIMMEAN(IF(A2:A51="Phòng kinh doanh",B2:B51),0.1)
Nhập công thức dạng mảng bằng Ctrl + Shift + Enter trên Excel cũ, hoặc Enter trên Excel 365. Công thức lọc chỉ lấy lương nhân viên phòng kinh doanh rồi áp dụng TRIMMEAN.
Một cách khác là tính độ lệch giữa AVERAGE và TRIMMEAN để đánh giá mức độ ảnh hưởng của ngoại lai:
=(AVERAGE(B2:B51)-TRIMMEAN(B2:B51,0.1))/AVERAGE(B2:B51)
Nếu kết quả lớn hơn 15%, dữ liệu có ngoại lai đáng kể. Nhỏ hơn 5% thì dữ liệu khá đồng đều.
So sánh hiệu quả với phương pháp cũ
Trước khi biết TRIMMEAN, quy trình xử lý báo cáo lương mất 25 phút mỗi tháng:
- Sao chép dữ liệu sang sheet mới (2 phút)
- Sắp xếp theo giá trị (1 phút)
- Tìm và xóa ngoại lai thủ công (5 phút)
- Tính AVERAGE cho dữ liệu còn lại (1 phút)
- Kiểm tra và điều chỉnh nếu sai (8 phút)
- Sao chép kết quả về báo cáo chính (3 phút)
- Format lại và cập nhật ghi chú (5 phút)
Với TRIMMEAN, toàn bộ quy trình chỉ còn 3 phút:
- Nhập công thức TRIMMEAN vào ô kết quả (1 phút)
- Điều chỉnh phần trăm nếu cần (1 phút)
- Kiểm tra nhanh bằng cách so với MEDIAN (1 phút)
Tiết kiệm 22 phút mỗi tháng có vẻ không nhiều, nhưng tôi làm 8 loại báo cáo tương tự. Tổng cộng tiết kiệm được 176 phút tức gần 3 giờ mỗi tháng. Hơn nữa, không còn lo lắng về việc vô tình xóa sai dữ liệu khi xử lý thủ công.
Kết quả sau 6 tháng sử dụng
Tất cả báo cáo phân tích hiện dùng TRIMMEAN cho các chỉ số trung bình. Độ chính xác của phân tích tăng rõ rệt – ban lãnh đạo không còn đặt câu hỏi về những con số bất thường. Họ tin tưởng vào số liệu vì biết rằng ngoại lai đã được xử lý hợp lý.
Hàm TRIMMEAN có sẵn trong Excel từ 2007 nhưng ít người biết đến. Nếu bạn thường làm việc với dữ liệu có giá trị nổi bật bất thường – lương, doanh số, điểm số, thời gian phản hồi – hàm này đáng để thử ngay. Bắt đầu với phần trăm 0.1 cho hầu hết trường hợp, sau đó điều chỉnh dựa trên phân bố dữ liệu cụ thể của bạn.
