Phân tích điểm bán chạy trong dữ liệu sản phẩm có thể mất nhiều thời gian nếu làm thủ công. Tôi thường sắp xếp dữ liệu theo số lần xuất hiện, sau đó quét mắt để tìm những sản phẩm nào có cùng tần suất cao nhất. Mỗi khi thêm dữ liệu mới phải lặp lại toàn bộ quy trình.
MODE.SNGL chỉ cho bạn một nửa bức tranh
Hàm MODE.SNGL là công cụ phổ biến để tìm giá trị xuất hiện nhiều nhất, nhưng có một giới hạn lớn. Khi dữ liệu có nhiều giá trị cùng xuất hiện với tần suất cao nhất, MODE.SNGL chỉ trả về giá trị đầu tiên gặp phải trong dải dữ liệu.
Ví dụ thực tế: Trong danh sách điểm số: 85, 90, 85, 92, 90, 88, 85, 90 – cả số 85 và 90 đều xuất hiện 3 lần. MODE.SNGL chỉ trả về 85 vì nó xuất hiện trước trong danh sách. Bạn hoàn toàn bỏ lỡ thông tin về số 90.
Với báo cáo kinh doanh, điều này có nghĩa bạn chỉ nhìn thấy một sản phẩm bán chạy trong khi thực tế có ba sản phẩm cùng hiệu suất. Quyết định dựa trên thông tin không đầy đủ dẫn đến chiến lược sai lầm.
MODE.MULT hiển thị tất cả giá trị có tần suất cao nhất
Hàm MODE.MULT được giới thiệu trong Excel 2010 để giải quyết đúng vấn đề này. Thay vì chỉ trả về một giá trị, MODE.MULT trả về một mảng dọc chứa tất cả các giá trị có cùng tần suất xuất hiện cao nhất.
Cách hoạt động: MODE.MULT quét toàn bộ dải dữ liệu, đếm tần suất của từng giá trị, sau đó trả về tất cả các giá trị có tần suất lớn nhất. Nếu có ba giá trị cùng xuất hiện 5 lần và đây là tần suất cao nhất, cả ba giá trị đều được hiển thị.
Hàm này đặc biệt hữu ích cho phân tích dữ liệu kinh doanh, thống kê đa biến, hoặc bất kỳ tình huống nào cần nhìn thấy toàn bộ bức tranh thay vì chỉ một điểm dữ liệu.
Ba bước sử dụng MODE.MULT
Bước 1: Chọn vùng kết quả
MODE.MULT trả về nhiều giá trị nên cần đủ ô trống để hiển thị kết quả. Chọn một vùng ô theo chiều dọc, thường từ 3 đến 5 ô liên tiếp tùy vào dữ liệu.
Cách chọn:
- Nhấp vào ô đầu tiên nơi muốn kết quả xuất hiện
- Giữ Shift và nhấp vào ô cuối cùng (thường 3-5 ô xuống dưới)
- Vùng được chọn sẽ sáng lên
Nếu chọn quá ít ô, một số kết quả sẽ không hiển thị. Nếu chọn quá nhiều, các ô thừa sẽ hiển thị lỗi #N/A. Tốt nhất là chọn nhiều hơn số lượng mode dự kiến.
Bước 2: Nhập công thức
Với vùng ô đã chọn, nhập công thức MODE.MULT theo cú pháp: =MODE.MULT(A2:A20)
Trong đó A2:A20 là dải dữ liệu chứa các giá trị cần phân tích. Có thể thay thế bằng dải dữ liệu cụ thể của bạn.
Lưu ý quan trọng cho Excel 2010-2019: Chưa nhấn Enter ngay. Thay vào đó, nhấn Ctrl+Shift+Enter để nhập dưới dạng công thức mảng. Excel sẽ tự động thêm dấu ngoặc nhọn xung quanh công thức: {=MODE.MULT(A2:A20)}
Đối với Excel 365 và Excel 2021: Chỉ cần nhấn Enter bình thường. Các phiên bản này hỗ trợ mảng động nên tự động xử lý không cần Ctrl+Shift+Enter.
Bước 3: Kiểm tra và điều chỉnh
Sau khi nhập công thức, Excel hiển thị tất cả giá trị có tần suất cao nhất theo chiều dọc. Các ô không có kết quả sẽ hiển thị lỗi #N/A.
Ví dụ kết quả: Nếu dữ liệu có ba giá trị cùng xuất hiện 4 lần (tần suất cao nhất):
- Ô D2: 120
- Ô D3: 85
- Ô D4: 92
- Ô D5: #N/A (không còn mode nào khác)
Thay vì mất 10 phút quét thủ công và có thể bỏ sót, toàn bộ phân tích hoàn thành trong 30 giây.
Xử lý lỗi #N/A khi không có giá trị lặp
Lỗi #N/A xuất hiện trong hai tình huống: không có giá trị nào lặp lại trong dữ liệu, hoặc đã hết mode để hiển thị trong các ô còn lại.
Tình huống 1 – Không có giá trị lặp: Nếu dữ liệu có tất cả giá trị duy nhất (mỗi giá trị chỉ xuất hiện một lần), MODE.MULT không thể tìm mode và trả về #N/A cho tất cả ô. Đây là hành vi mong đợi vì không có giá trị nào “xuất hiện nhiều nhất” khi tất cả đều xuất hiện một lần.
Tình huống 2 – Hết mode: Nếu chọn 5 ô nhưng chỉ có 2 mode, ô thứ 3, 4, 5 sẽ hiển thị #N/A. Đơn giản thu nhỏ vùng chọn hoặc bỏ qua các ô #N/A này.
Cách ẩn #N/A: Kết hợp với hàm IFERROR nếu muốn hiển thị giá trị thay thế: =IFERROR(MODE.MULT(A2:A20),"Không có mode")
Tuy nhiên với Excel 365/2021, không thể dùng IFERROR trực tiếp vì MODE.MULT tự động mở rộng. Cách tốt nhất là để nguyên #N/A hoặc định dạng ẩn lỗi qua Conditional Formatting.
So sánh MODE.MULT với MODE.SNGL
Cả hai hàm đều tìm giá trị xuất hiện nhiều nhất, nhưng kết quả và cách sử dụng khác biệt rõ rệt.
MODE.SNGL:
- Trả về một giá trị duy nhất
- Không cần chọn nhiều ô
- Nhấn Enter bình thường
- Hiển thị giá trị đầu tiên gặp phải
- Phù hợp khi chắc chắn chỉ có một mode
MODE.MULT:
- Trả về mảng nhiều giá trị
- Cần chọn vùng ô theo chiều dọc
- Cần Ctrl+Shift+Enter (Excel 2010-2019)
- Hiển thị tất cả các mode
- Phù hợp khi có thể có nhiều mode
Kết quả thực tế: Với cùng dữ liệu điểm số sinh viên trong một lớp 30 người, MODE.SNGL báo có một điểm phổ biến nhất là 85. Sau khi chuyển sang MODE.MULT, phát hiện có ba điểm cùng phổ biến: 85, 88, và 92. Báo cáo cuối kỳ đầy đủ hơn 200 phần trăm về thông tin phân bố điểm.
Tương thích và phiên bản
MODE.MULT hoạt động từ Excel 2010 trở lên, bao gồm Excel 2013, 2016, 2019, 2021, và Excel 365. Không khả dụng trong Excel 2007 hoặc các phiên bản cũ hơn.
Đối với Excel 365 và Excel 2021, trải nghiệm sử dụng dễ dàng hơn nhiều nhờ hỗ trợ mảng động. Chỉ cần nhập công thức và nhấn Enter, Excel tự động mở rộng kết quả ra nhiều ô. Các phiên bản cũ hơn yêu cầu chọn trước vùng kết quả và nhấn Ctrl+Shift+Enter.
Hàm này đặc biệt hữu ích cho phân tích kinh doanh, nghiên cứu thống kê, hoặc bất kỳ báo cáo nào cần nhìn thấy toàn bộ các giá trị phổ biến thay vì chỉ một mẫu đại diện. Kết hợp với các hàm khác như COUNTIF để tính tần suất xuất hiện của từng mode, tạo báo cáo phân tích đầy đủ trong vài phút.
