Công thức Excel: Biểu đồ với FREQUENCY
Công thức Excel: Biểu đồ với FREQUENCY

Tóm lược

Một cách để tạo biểu đồ là với Hàm FREQUENCY. Trong ví dụ được hiển thị, công thức trong các ô G5: G8 là:

{=FREQUENCY(data,bins)}

Ở đâu dữ liệu (C5: C16) và thùng (F5: F8) là phạm vi được đặt tên.

Công thức này được nhập dưới dạng công thức mảng nhiều ô trong phạm vi G5: G8.

Giải trình

Lưu ý: các phiên bản Excel sau bao gồm một biểu đồ histogram gốc, đó là dễ tạo, nhưng không linh hoạt để định dạng. Ví dụ trên trang này cho thấy một cách để tạo dữ liệu biểu đồ của riêng bạn bằng hàm FREQUENCY và sử dụng biểu đồ cột thông thường để vẽ biểu đồ kết quả. Vì FREQUENCY là một công thức nên kết quả và biểu đồ sẽ tự động cập nhật nếu dữ liệu thay đổi.

Các Hàm FREQUENCY trả về phân phối tần số, là bảng tóm tắt hiển thị số lượng của từng giá trị trong một phạm vi theo “bin”. FREQUENCY hơi khó sử dụng vì phải nhập dưới dạng công thức mảng. Mặt khác, sau khi bạn thiết lập đúng các thùng của mình, FREQUENCY sẽ cung cấp cho bạn tất cả số lượng cùng một lúc!

Thiết lập và công thức

Trong ví dụ được hiển thị, chúng tôi có danh sách 12 điểm trong phạm vi được đặt tên “dữ liệu” (C5: C16). Phạm vi F5: F8 là phạm vi được đặt tên là “thùng”. FREQUENCY sẽ coi mỗi giá trị bin là phía trên giới hạn cho thùng đó. Nói cách khác, mỗi thùng sẽ bao gồm một số điểm lên đến và bao gồm cả giá trị của thùng. FREQUENCY cũng sẽ trả về “số lượng tràn” – số lượng các giá trị lớn hơn thùng cuối cùng.

Để nhập công thức FREQUENCY, hãy làm theo các bước sau trong sổ làm việc đính kèm.

1. Xóa các công thức hiện có nếu cần (xem ghi chú bên dưới).

2. Chọn phạm vi G5: G8 (tất cả bốn ô).

3. Dán hoặc nhập công thức này vào thanh công thức:

=FREQUENCY(data,bins)

4. Nhập công thức dưới dạng công thức mảng với điều khiển + shift + enter. Excel sẽ tự động thêm dấu ngoặc nhọn {}.

Lưu ý: Để chỉnh sửa các công thức hiện có, trước tiên bạn cần chọn tất cả bốn ô, sau đó nhấp vào Xóa. Bạn có thể chọn tất cả các ô cùng một lúc với điều khiển phím tắt + /.

FREQUENCY sẽ tạo số lượng cho mỗi thùng và trả về mảng kết quả như thế này:

{2;4;5;1;0} // array returned by FREQUENCY

Excel sẽ đặt 4 giá trị đầu tiên trong phạm vi G5: G8. Lưu ý rằng số tràn, số không, cũng được trả về dưới dạng phần tử thứ năm trong mảng, vì không có giá trị nào lớn hơn 100. Giá trị này không được hiển thị trong trang tính, vì chúng tôi chỉ nhập công thức trong bốn ô.

Excel 365

Trong Excel 365, bạn chỉ cần nhập một công thức trong ô G5 và kết quả sẽ tự động tràn lên trang tính. Không cần sử dụng điều khiển + shift + enter. Tuy nhiên, nếu bạn sử dụng phương pháp này, bạn cũng sẽ thấy số lượng cho đầu ra của thùng tràn. Chỉ nhập công thức trong 4 ô (như trên) sẽ loại bỏ giá trị cuối cùng này. Nếu bạn không bận tâm đến giá trị phụ, tùy chọn công thức đơn sẽ dễ dàng hơn và bạn có thể chọn không vẽ giá trị trong biểu đồ.

Nhãn

Các nhãn trong E5: E8 chỉ dành cho khả năng đọc và trình bày. Chúng có thể được tùy chỉnh theo ý muốn của bạn. Trong ví dụ được hiển thị, công thức trong ô E5, được sao chép xuống, là:

=IF(F5=MIN(bins),"≤"&F5,F4+1&"-"&F5)

Công thức này chỉ đơn giản là xây dựng một nhãn cho mỗi thùng bằng cách sử dụng các giá trị thùng trong cột F.

Biểu đồ histogram

Để lập biểu đồ đầu ra từ FREQUENCY, hãy làm theo các bước sau. Đầu tiên, giữ phím điều khiển và chọn hai phạm vi: E4: E8 và G4: G8. Sau đó, chèn biểu đồ cột (Chèn> Biểu đồ> Cột theo cụm):

Chèn biểu đồ cột nhóm

Tiếp theo, nhấp chuột phải vào một thanh và định dạng chuỗi dữ liệu để giảm độ rộng khoảng trống xuống 5% (hoặc như mong muốn):

Định dạng chuỗi dữ liệu để giảm khoảng cách

Thay đổi tiêu đề biểu đồ theo ý muốn. Trong ví dụ được hiển thị, chúng tôi chọn giá trị trong ô B2:

Đặt tiêu đề biểu đồ thành ô B2

Biểu đồ cuối cùng hiển thị các giá trị được vẽ:

Biểu đồ cuối cùng với các phạm vi hiển thị

Biểu đồ với Công cụ Phân tích Dữ liệu

Một cách khác để tạo biểu đồ trong Excel là sử dụng bổ trợ Data Analysis ToolPak. Đây là một phương pháp rất đơn giản và nó hoạt động trong các phiên bản Excel cũ hơn. Tuy nhiên, một hạn chế là đầu ra là tĩnhvà sẽ không tự động cập nhật nếu các giá trị trong dữ liệu thay đổi.

Tập tin đính kèm

https://exceljet.net/formula/histogram-with-frequency