.st0{fill:#FFFFFF;}

Sử dụng hàm Countifs trong báo cáo theo tỷ lệ và vẽ biểu đồ hình tròn 

 Tháng Chín 21, 2021

By  Lê Đình Chi

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ớpXế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à:

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

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

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

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

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

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

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 zik.vn xin giới thiệu với các bạn EXG01 – Khoá học phát triển tư duy tổ chức dữ liệu, thiết lập báo cáo chuyên. Đâ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:

Đánh giá bài viết này

zik.vn – Nền tảng học online duy nhất tại Việt Nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm

Với sứ mệnh: Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển zik.vn đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Zik.vn tự hào khi được đồng hành cùng:

  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
  • 100.000+ học viên trên khắp Việt Nam

Tìm hiểu ngay các khóa học của Zik.vn TẠI ĐÂY



Lê Đình Chi


Your Signature

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!

DMCA.com Protection Status
>