FREQUENCY: đếm số lượng rơi vào từng khoảng, dựng phân phối dữ liệu chỉ bằng một công thức

Bài trước, UNICHAR chèn ký tự đặc biệt. FREQUENCY chuyển hẳn sang thống kê — đếm số lượng giá trị rơi vào từng khoảng, dựng ra một bảng phân phối tần suất chỉ với một công thức duy nhất.

Cú pháp #

=FREQUENCY(mang_du_lieu, mang_khoang)

mang_khoang khai các mốc trên của từng khoảng — Excel tự hiểu khoảng đầu là "nhỏ hơn hoặc bằng mốc thứ nhất", các khoảng giữa là "lớn hơn mốc trước, nhỏ hơn hoặc bằng mốc này", và luôn có thêm một khoảng cuối "lớn hơn mốc lớn nhất" mà không cần khai riêng.

Ứng dụng: phân phối điểm thi vào các khoảng xếp loại #

10 học sinh với điểm thi cho trước, muốn biết có bao nhiêu bài rơi vào từng khoảng ≤59, 60-69, 70-79, 80-89, >89:

=FREQUENCY(A2:A11,{59,69,79,89})

Với dữ liệu điểm 45, 67, 72, 88, 91, 55, 78, 82, 95, 60, kết quả trả về 2, 2, 2, 2, 2 — mỗi khoảng đều có đúng 2 bài thi, cho thấy điểm số phân bố khá đều qua các mức.

Cách nhập: công thức mảng, kết quả tràn ra nhiều ô #

Trên các phiên bản Excel có mảng động, chỉ cần gõ công thức vào một ô rồi Enter, kết quả tự tràn xuống đúng số ô bằng số khoảng cộng thêm 1 (khoảng cuối luôn tự động có thêm). Trên bản Excel cũ hơn, cần chọn trước đúng số ô đó rồi nhấn Ctrl+Shift+Enter:

Chọn thiếu số ô sẽ làm mất phần kết quả cuối — đây là lỗi thường gặp nhất với FREQUENCY trên Excel bản cũ, giống lỗi từng nói ở bài TRANSPOSE.

Vì sao không dùng nhiều công thức COUNTIFS thay thế #

Có thể đếm từng khoảng bằng COUNTIFS riêng lẻ, nhưng phải viết lại công thức cho mỗi khoảng và tự tay xử lý ranh giới trên dưới:

=COUNTIFS(A2:A11,"<=59")
=COUNTIFS(A2:A11,">59",A2:A11,"<=69")

FREQUENCY chỉ cần một công thức duy nhất cho toàn bộ các khoảng, tự động xử lý đúng ranh giới trên dưới mà không cần lặp lại điều kiện ">"/"<=" cho từng cặp mốc.

Lưu ý: bỏ qua ô trống và văn bản, chỉ đếm số #

=FREQUENCY(A2:A12,{59,69,79,89})

Nếu vùng dữ liệu lẫn cả ô trống hoặc ô chứa văn bản (ví dụ ghi chú "Vắng thi" thay vì điểm số), FREQUENCY tự động bỏ qua các ô đó khi đếm, không báo lỗi và cũng không tính nhầm chúng vào bất kỳ khoảng nào — chỉ những ô thực sự chứa số mới được tính.

Tổng kết #

FREQUENCY đếm số lượng giá trị rơi vào từng khoảng tự định nghĩa, dựng ra một phân phối tần suất chỉ với một công thức, tự động xử lý đúng ranh giới trên dưới mà không cần lặp lại điều kiện cho từng khoảng như dùng nhiều COUNTIFS riêng lẻ.

Đây là bài cuối trong cụm 5 bài lô 14, bắt đầu từ IMAGE.