Biểu đồ hình tròn là dạng biểu đồ đặc trưng trong việc biểu diễn báo cáo theo tỷ lệ, tỷ trọng của mỗi đối tượng trong tổng số. Yêu cầu lập báo cáo theo tỷ lệ và vẽ biểu đồ hình tròn để biểu diễn là yêu cầu rất thường gặp khi làm việc trên Excel. Nhưng bạn có biết rằng mấu chốt nhất để có thể đưa ra kết quả cho báo cáo đó chính là việc sử dụng hàm Countifs để đếm theo nhiều điều kiện. Chúng ta hãy cùng tìm hiểu về ứng dụng của hàm Countifs trong ví dụ dưới đây:

Trên đây chính là các bước để có thể từ 1 bảng dữ liệu lập ra báo cáo dạng đếm. Từ báo cáo dạng đếm này lập thành báo dạng tỷ lệ %, sau đó vẽ biểu đồ hình tròn biểu diễn kết quả cho từng lớp. Chúng ta hãy cùng tìm hiểu cách làm này nhé:

1. Phân tích yêu cầu đề bài

Trước hết các bạn có thể tải về file mẫu tại địa chỉ: http://bit.ly/2M5dqHj

Trong file mẫu chúng ta có dữ liệu ban đầu là bảng từ A1:C31, gồm 3 cột Học sinh – Lớp – Xếp loại, tương ứng với 30 học viên được sắp xếp ngẫu nghiên theo từng lớp.

Yêu cầu của bài tập này là vẽ biểu đồ tỷ lệ xếp loại cho từng lớp. Yêu cầu này có thể phân tích như sau:

• Mục đích cuối cùng là ra 2 biểu đồ hình tròn tương ứng cho mỗi lớp. Trong mỗi biểu đồ thể hiện tỷ lệ giữa các điểm xếp loại. Biểu đồ có thể vẽ bằng Insert/Chart/Pie chart.
• Tỷ lệ giữa các điểm xếp loại tính như thế nào? Cần tính ra được mỗi lớp có bao nhiêu học sinh, trong đó bao nhiêu học sinh xếp loại A, bao nhiêu học sinh xếp loại B, loại C, loại D. Tỷ lệ sẽ được tính theo số học sinh mỗi loại chia cho tổng số học sinh của lớp đó.
• Để tính được tổng số học sinh, số học sinh được xếp loại theo từng mức điểm thì cần sử dụng hàm COUNTIFS, bởi ở đây có 2 điều kiện: Lớp và Xếp loại

Do đó khi thực hiện chúng ta sẽ thực hiện theo thứ tự từ dưới lên của logic trên.

2. Sử dụng hàm Countifs để lập báo cáo dạng đếm theo nhiều điều kiện

Chúng ta xác định các thành phần trong hàm COUNTIFS đặt tại ô F2 như sau:

• Vùng điều kiện thứ 1 (criteria_range1): tương ứng với Lớp, là vùng B2:B31
• Điều kiện thứ 1 (criteria1): điều kiện này nằm trong vùng điều kiện thứ 1, là lớp 12A1 tại ô E2
• Vùng điều kiện thứ 2 (criteria_range2): tương ứng với Xếp loại, là vùng C2:C31
• Điều kiện thứ 2 (criteria2): điều kiện này nằm trong vùng điều kiện thứ 2, là loại A tại ô F1

Công thức có thể thực hiện là:

Tuy nhiên công thức trên không thể áp dụng ngay sang các ô khác (G2, H2…F3,G3…) bởi các vùng tham chiếu trong công thức chưa được cố định. Khi sao chép sẽ bị thay đổi tọa độ tham chiếu dẫn tới kết quả bị sai.

Cách cố định như sau:

• Các vùng Range là tham chiếu bên ngoài báo cáo, do đó sẽ cố định hoàn toàn (có dấu $ ở trước tên cột, tên dòng => Tương ứng với việc đặt chuột vào vị trí B2:B31 và bấm phím F4 một lần để chuyển sang $B$2:$B$31. Tương tự với C2:C31
• Điều kiện 1 là E2. Điều kiện ở cột E có đặc điểm là tổ chức trên 1 cột, nhiều dòng. Do đó khi sao chép công thức cần cố định cột để không thay đổi cột E, còn các dòng có thể thay đổi theo dòng chứa điều kiện tương ứng nên không cố định dòng (tại vị trí E2 trong công thức sẽ bấm phím F4 ba lần để cố định dạng $E2)
• Điều kiện 2 là F1. Điều kiện ở dòng 1 có đặc điểm là tổ chức trên 1 dòng, nhiều cột. Do đó cần cố định dòng 1 này mà không cố định cột F (tại vị trí F1 trong công thức sẽ bấm phím F4 hai lần để cố định dạng F$1)

Kết quả đúng sẽ là:

Bây giờ chúng ta sẽ sao chép công thức này sang tới cột I, sao chép xuống dòng 3 và tính tổng số trong cột J.

Kết quả thu được là:

3. Cách chuyển báo cáo dạng đếm về dạng tỷ lệ %

Cách tính tỷ lệ như đã phân tích ở trên là: Tỷ lệ sẽ được tính theo số học sinh mỗi loại chia cho tổng số học sinh của lớp đó

Do dữ liệu ở dạng % nên cần định dạng Format Cells là dạng Percentage, có thể dùng 2 số sau dấu phẩy

Lưu ý:

• Ô J2 là ô chứa dữ liệu là tổng số học sinh lớp 12A1. Do đó có thể cố định ô này trong phép tính F2/J2 để có thể áp dụng công thức tính tỷ lệ % sang các ô khác.
• Khi tính cho lớp 12A2 thì phải thay đổi J2 thành J3.

Kết quả thu được sẽ là:

4. Cách vẽ biểu đồ tỷ lệ trên Excel

Cùng ôn lại một chút cách vẽ biểu đồ trong Excel. Cụ thể là dạng biểu đồ hình tròn. Trong thẻ Insert chọn Chart. Sau đó chọn loại biểu đồ là hình tròn (pie chart) . Đây là dạng biểu đồ đặc trưng để vẽ biểu đồ tỷ lệ.

Khi vẽ biểu đồ thì sẽ xuất hiện thẻ Chart tools trên thanh công cụ. Chọn Select Data

Lựa chọn Data tương ứng cho từng biểu đồ. Ở đây chúng ta cần vẽ 2 biểu đồ. Sau đây là hình hướng dẫn cách chọn dữ liệu để vẽ biểu đồ cho lớp 12A2:

Như vậy thông qua bài viết này, chúng ta đã nắm được cách sử dụng hàm COUNTIFS trong việc lập báo cáo đạng đếm, phục vụ cho việc vẽ biểu đồ phân tích tỷ lệ.

Tham khảo: Tùy chỉnh biểu đồ Excel: thêm tiêu đề, trục, chú thích, nhãn dữ liệu và nhiều thứ khác

Ngoài ra Học Excel Online xin giới thiệu với các bạn Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. Chi tiết xem tại: