Tôi Giảm Thời Gian Phân Tích Dữ Liệu Từ 2 Giờ Xuống 10 Phút Nhờ Hàm FREQUENCY Trong Excel

Mỗi tháng tôi phải phân loại 800 điểm số học sinh vào các mức yếu, trung bình, khá, giỏi cho báo cáo tổng kết. Phương pháp cũ của tôi là sử dụng nhiều công thức COUNTIF rồi kiểm tra từng khoảng điểm một. Công việc này tốn 2 giờ đồng hồ và dễ sai sót nếu quên cập nhật một công thức nào đó. Giờ đây với hàm FREQUENCY, toàn bộ quy trình chỉ mất 10 phút và kết quả luôn chính xác.

Mục Lục

Phương pháp cũ lãng phí thời gian

Trước khi biết hàm FREQUENCY, tôi tạo một bảng riêng với năm cột khác nhau. Mỗi cột chứa một công thức COUNTIF để đếm số học sinh trong từng khoảng điểm. Công thức cho điểm dưới 5 là =COUNTIF(A2:A801,"<5"), cho điểm từ 5 đến dưới 7 là =COUNTIFS(A2:A801,">=5",A2:A801,"<7"), và cứ tiếp tục như vậy cho các khoảng khác.

Vấn đề là mỗi khi danh sách học sinh thay đổi, tôi phải điều chỉnh phạm vi ô trong cả năm công thức. Một lần tôi quên cập nhật công thức thứ ba, kết quả báo cáo sai và phải làm lại toàn bộ. Ngoài ra, các công thức COUNTIFS dài dòng khiến file nặng và chậm, đặc biệt với bảng dữ liệu lớn.

Hàm FREQUENCY giải quyết mọi thứ

Hàm FREQUENCY là công cụ thống kê trong Excel dùng để đếm số lần xuất hiện của dữ liệu theo các khoảng giá trị. Thay vì tạo nhiều công thức riêng lẻ, bạn chỉ cần một công thức duy nhất và Excel tự động tính tần suất cho tất cả các khoảng.

Cú pháp cơ bản:

=FREQUENCY(Mảng_dữ_liệu, Mảng_khoảng)

Hai tham số bắt buộc:

Mảng_dữ_liệu: Phạm vi ô chứa dữ liệu cần phân tích
Mảng_khoảng: Phạm vi ô chứa các giới hạn khoảng giá trị

XEM THÊM: Hàm MINIFS Excel 2019: Cú Pháp Và 5 Ví Dụ Thực Tế Bạn Cần Biết

Điều đặc biệt là hàm này trả về một mảng kết quả, có số phần tử nhiều hơn mảng khoảng một đơn vị. Phần tử thêm này đếm tất cả giá trị lớn hơn khoảng cuối cùng.

Cách thiết lập bảng dữ liệu

Tôi chuẩn bị bảng tính với ba cột chính. Cột A chứa 800 điểm số học sinh từ ô A2 đến A801. Cột C chứa các giới hạn khoảng điểm: 5, 7, 8, 9 trong các ô C2 đến C5. Cột D sẽ hiển thị kết quả tần suất.

Thiết lập khoảng điểm:

Dưới 5 (yếu)
Từ 5 đến dưới 7 (trung bình)
Từ 7 đến dưới 8 (khá)
Từ 8 đến dưới 9 (giỏi)
Từ 9 đến 10 (xuất sắc)

Lưu ý quan trọng: Tôi chọn sáu ô từ D2 đến D7 để hiển thị kết quả, nhiều hơn một ô so với mảng khoảng. Ô cuối cùng D7 sẽ tự động đếm số học sinh có điểm từ 9 trở lên.

Nhập công thức theo phiên bản Excel

Cách nhập công thức phụ thuộc vào phiên bản Excel bạn đang sử dụng.

Với Excel 365 hoặc Excel 2021:

Nhập công thức vào ô đầu tiên của vùng kết quả:

Click vào ô D2
Nhập =FREQUENCY(A2:A801,C2:C5)
Nhấn Enter

Excel tự động tính toán và kết quả hiển thị xuống các ô phía dưới. Bạn sẽ thấy viền xanh bao quanh vùng D2:D7, cho biết đây là mảng động.

Với Excel 2019 hoặc cũ hơn:

Phương pháp này phức tạp hơn một chút vì phải nhập công thức dạng mảng kế thừa:

Chọn toàn bộ vùng kết quả D2:D7
Nhập =FREQUENCY(A2:A801,C2:C5) vào thanh công thức
Nhấn tổ hợp phím Ctrl + Shift + Enter

Excel tự động thêm dấu ngoặc nhọn {} bao quanh công thức. Nếu bạn tự gõ dấu ngoặc, công thức sẽ không hoạt động và biến thành văn bản.

Hiểu cách FREQUENCY phân loại dữ liệu

Hàm này hoạt động theo nguyên tắc đếm các giá trị nhỏ hơn hoặc bằng từng giới hạn khoảng, sau đó trừ đi kết quả khoảng trước để tính tần suất riêng lẻ.

XEM THÊM: 5 Ứng Dụng Thực Tế Của Hàm NEGBINOM.DIST Trong Phân Tích Dữ Liệu

Với thiết lập khoảng là 5, 7, 8, 9:

Ô D2 đếm tất cả điểm nhỏ hơn hoặc bằng 5
Ô D3 đếm điểm lớn hơn 5 và nhỏ hơn hoặc bằng 7
Ô D4 đếm điểm lớn hơn 7 và nhỏ hơn hoặc bằng 8
Ô D5 đếm điểm lớn hơn 8 và nhỏ hơn hoặc bằng 9
Ô D6 đếm điểm lớn hơn 9

Ví dụ kết quả từ 800 điểm số của tôi:

45 học sinh yếu
210 học sinh trung bình
315 học sinh khá
180 học sinh giỏi
50 học sinh xuất sắc

Tính tỷ lệ phần trăm nhanh chóng

Để báo cáo rõ ràng hơn, tôi thêm cột tính tỷ lệ phần trăm. Với Excel 365, công thức đơn giản trong ô E2:

=FREQUENCY(A2:A801,C2:C5)/COUNT(A2:A801)

Nhấn Enter và kết quả hiển thị dưới dạng thập phân. Click chuột phải vào vùng E2:E7, chọn Định dạng ô, chọn Phần trăm. Kết quả hiển thị 5,6% cho học sinh yếu, 26,3% cho học sinh trung bình và tiếp tục cho các nhóm khác.

Với Excel 2019 trở xuống, chọn vùng E2:E7, nhập công thức trên vào thanh công thức, rồi nhấn Ctrl + Shift + Enter.

Những lưu ý khi sử dụng

Hàm FREQUENCY chỉ đếm dữ liệu dạng số và tự động bỏ qua ô trống cũng như văn bản. Nếu cột điểm số có ô chứa chữ như “Chưa thi”, hàm sẽ không tính ô đó.

Với Excel 2019 trở xuống, bạn không thể chỉnh sửa một ô riêng lẻ trong mảng kết quả. Nếu cần thay đổi công thức, phải chọn toàn bộ vùng D2:D7, nhấn F2, sửa công thức, rồi nhấn lại Ctrl + Shift + Enter. Cố gắng xóa một ô sẽ hiển thị thông báo lỗi “Không thể thay đổi một phần của mảng”.

Dữ liệu trong mảng khoảng phải được sắp xếp theo thứ tự tăng dần. Nếu bạn nhập 7, 5, 8, 9 thay vì 5, 7, 8, 9, kết quả sẽ hoàn toàn sai.

XEM THÊM: Cách Tính Độ Dốc Hồi Quy Tuyến Tính Trong 30 Giây Với Hàm SLOPE

So sánh hiệu suất thực tế

File Excel của tôi với 800 dòng dữ liệu và năm công thức COUNTIFS có kích thước 2,4 MB. Sau khi chuyển sang sử dụng hàm FREQUENCY, kích thước giảm xuống 1,8 MB. Thời gian mở file giảm từ 8 giây xuống 5 giây trên máy tính văn phòng.

Quan trọng hơn, việc cập nhật dữ liệu mới trở nên dễ dàng. Khi thêm điểm cho 50 học sinh mới, tôi chỉ cần chỉnh sửa phạm vi trong công thức FREQUENCY một lần. Với phương pháp cũ, tôi phải cập nhật năm công thức COUNTIFS riêng lẻ.

Mở rộng ứng dụng cho công việc khác

Sau khi thành thạo hàm FREQUENCY, tôi áp dụng nó cho nhiều tác vụ khác. Phân loại tuổi khách hàng thành các nhóm 18-25, 26-35, 36-45, trên 45 cho chiến dịch marketing mất 5 phút thay vì 30 phút với COUNTIFS.

Phân tích doanh số bán hàng theo khoảng giá cũng nhanh hơn. Thay vì tạo nhiều điều kiện phức tạp, tôi thiết lập các khoảng giá 100.000, 500.000, 1.000.000, 5.000.000 và để FREQUENCY tự động tính số đơn hàng trong mỗi khoảng.

Hàm này hoạt động tốt nhất khi bạn cần phân loại dữ liệu số lượng lớn thành các nhóm cố định. Đối với các trường hợp cần điều kiện phức tạp hoặc nhiều tiêu chí khác nhau, COUNTIFS vẫn là lựa chọn phù hợp hơn.

Tương thích và khả năng nâng cấp

Hàm FREQUENCY hoạt động trên tất cả phiên bản Excel từ 2007 trở lên. Tuy nhiên trải nghiệm sử dụng khác biệt rõ rệt giữa các phiên bản.

Excel 365 và 2021 hỗ trợ mảng động, giúp công việc đơn giản như nhập công thức thông thường. Kết quả tự động lan rộng và cập nhật khi dữ liệu nguồn thay đổi. Đây là lý do tôi khuyên nâng cấp lên phiên bản mới nếu làm việc thường xuyên với phân tích dữ liệu.

Excel 2019 và cũ hơn yêu cầu nhập công thức dạng mảng kế thừa với Ctrl + Shift + Enter. Phương pháp này vẫn hoạt động tốt nhưng kém linh hoạt khi cần chỉnh sửa. Nếu công ty bạn vẫn dùng Excel 2016 hoặc 2019, hàm FREQUENCY vẫn tiết kiệm thời gian đáng kể so với COUNTIFS, chỉ cần quen với cách nhập công thức đặc biệt.