10 biểu đồ Excel nâng cao ai cũng cần biết (Phần 2)

Nhã Linh
Nhã Linh
Jul 28 2021

Trong phần 1 của series biểu đồ Excel nâng cao, chúng ta đã thực hành cách vẽ biểu đồ Excel dạng cột lồng nhau, biểu đồ Milestone và biểu đồ Gantt. Nếu các bạn đã nắm vững kiến thức về 3 loại biểu đồ Excel này, hãy cùng Gitiho khám phá các biểu đồ tiếp theo ngay thôi nào!

Xem thêm: 10 biểu đồ Excel nâng cao ai cũng cần biết (Phần 1)

Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

Biểu đồ Excel nâng cao: Đường cong chuông

Mục đích sử dụng đường cong chuông

Được sử dụng phổ biến trong lĩnh vực thống kê, đường cong chuông (Bell curve) là đường biểu diễn phân phối chuẩn (normal distribution) với hình dạng cân đối như một quả chuông. Loại phân phối này thể hiện xác suất xảy ra các biến, với điểm cao nhất trên đỉnh chuông đại diện cho sự kiến có xác suất xảy ra cao nhất và các điểm trên thân chuông tương ứng với các xác suất thấp dần đến điểm rìa chuông. Độ rộng của đường cong chuông phụ thuộc vào độ lệch chuẩn của dữ liệu.

Biểu đồ Excel nâng cao này còn có nhiều ứng dụng khác. Các nhà đầu tư tài chính sử dụng đường cong chuông để đo lường mức độ lợi nhuận của chứng khoán hoặc độ nhạy của thị trường. Dựa vào biến động được thể hiện bởi chỉ số độ lệch chuẩn, các nhà đầu tư có thể đưa ra các giả định về xu hướng lợi nhuận với các loại cổ phiếu của mình trong tương lai. Mặt khác, các trường học hoặc các công ty có thể sử dụng đường cong chuông để đánh giá kết quả học tập của học sinh hoặc hiệu suất làm việc của nhân viên.

Xem thêm: Hướng dẫn cách tạo biểu đồ tần suất cùng đường phân phối chuẩn trong Google Sheets

Cách vẽ đường cong chuông

So với các cách vẽ biểu đồ trong Excel nâng cao chúng ta đã tìm hiểu trong phần trước, đường cong chuông có vẻ đơn giản hơn rất nhiều. Giả sử chúng ta có một bảng tổng hợp điểm của 12 em học sinh như dưới đây:

Bước 1: Tính trung bình và độ lệch chuẩn

Trước khi bắt đầu, các bạn hãy sắp xếp các thành phần trong bảng dữ liệu theo thứ tự từ thấp đên cao bằng cách chọn toàn bộ cột Điểm, đi đến thẻ Data > nhóm Sort & Filter > Sort. Bảng trong hình trên đã được sắp xếp theo thứ tự nên mình không thực hiện lại thao tác này nữa.

Bây giờ chúng ta sẽ thực hiện tính trung bình (mean) và độ lệch chuẩn (standard deviation) cho các con số trong bảng. Công thức tính toán đơn giản như sau:

  • Trung bình: =AVERAGE(B2:B12)
  • Độ lệch chuẩn: =STDEV.P(B2:B12)


Bước 2: Xác định xác suất

Dựa vào các giá trị trung bình và độ lệch chuẩn đã tính ở trên, chúng ta sẽ tạo một cột mới bên phải cột B để tìm xác suất theo phân phối chuẩn của từng giá trị tại cột Điểm. Hàm tính toán hỗ trợ chúng ta thực hiện thao tác này chính là hàm NORM.DIST với công thức như sau:

=NORM.DIST(B2,$F$2,$F$3,FALSE)

Vậy là chúng ta đã hoàn thành bảng dữ liệu trong hình dưới đây.


Bước 3: Vẽ đường hình chuông
Chúng ta sẽ áp dụng cách vẽ biểu đồ histogram trong Excel để vẽ đường cong chuông. Tất cả những gì bạn cần làm là đi đến thẻ Insert > nhóm Charts > biểu tượng biểu đồ phân tán > Scatter with Smooth Lines and Markers. Vậy là chúng ta đã hoàn thành đường cong chuông trong Excel rồi.

Biểu đồ Excel nâng cao: Biểu đồ hình phễu

Mục đích sử dụng biểu đồ hình phễu

Biểu đồ hình phễu biểu diễn các giá trị tương ứng với các giai đoạn được chia ra trong một quy trình. Thông thường, loại biểu đồ Excel nâng cao này được sử dụng để hiển thị số tiền yêu cầu đối với từng giai đoạn bán hàng. Ngoài ra, biểu đồ hình phễu còn thể hiện các vấn đề có thể xảy ra trong quá trình bán hàng.

Cách tạo biểu đồ hình phễu

Giả sử chúng ta có một bảng dữ liệu như sau:

Bước 1: Xác định khoảng cách cân bằng

Như chúng ta đã biết, biểu đồ Excel hình phễu đòi hỏi sự cân bằng về mặt hình học giữa các phần của biểu đồ. Do đó, chúng ta cần phải xác định khoảng cách đến điểm cân bằng này. Đầu tiên, hãy chèn thêm 1 cột mới bên trái cột B. Mình sẽ đặt tên cột là Khoảng cách cân bằng.

Tại ô đầu tiên của cột (ô B2) tương ứng với giai đoạn Khách hàng tiềm năng và số tiền 500, chúng ta sẽ nhập số 0 vì đây là phần phễu lớn nhất, gắn liền với trục tung. Tại các ô tiếp theo, chúng ta sẽ sử dụng hàm LARGE để xác định khoảng cách giữa các phần đến điểm cân bằng trong đồ thị Excel.

=(LARGE($C$2:$C$7,1)-C3)/2


Bước 2: Vẽ biểu đồ hình phễu

Bạn hãy chọn toàn bộ phạm vi chứa dữ liệu cần đưa lên biểu đồ (A2:C7), đi đến thẻ Insert > nhóm Charts > biểu tượng biểu đồ thanh > Stacked Bar. Bạn sẽ có một biểu đồ như sau:

Bạn có nhận ra điểm bất thường trong biểu đồ này không? Hình như chiếc phễu của chúng ta đã bị úp ngược mất rồi. Các bạn hãy nhấn vào phần ghi chú giai đoạn trên biểu đồ Excel, chọn Format Axis và tích chọn ô Categories in reverse order để đảo ngược thứ tự các giai đoạn nhé.


Giờ thì biểu đồ Excel của chúng ta đã được đưa về đúng trình tự thời gian rồi.

Bước 3: Định dạng biểu đồ

Thao tác định dạng đầu tiên chúng ta cần làm chính là loại bỏ phần thanh màu xanh, chỉ để hiển thị phần thanh màu cam. Để làm được điều này, hãy nhấn chuột phải tại phần cần xóa, chọn Format Data Series, lần lượt tích No fill trong mục Fill và No line trong mục Border.


Bước 4: Ghép các thanh về đúng hình dạng phễu
Nếu bạn muốn Excel hiển thị các số liệu chi tiết trên từng phần biểu đồ hình phễu, hãy nhấn chuột phải tại một thanh màu cam và chọn Add Data Label. Lúc này, các con số tại cột Số tiền sẽ hiện lên trên biểu đồ Excel nâng cao.
Bước cuối cùng để hoàn thiện cách vẽ biểu đồ trong Excel nâng cao là phóng to các thanh màu cam để hình phễu của chúng ta không còn bị hở một chỗ nào. Các bạn hãy nhấn chuột phải tại một thanh màu cam và chọn Format Data Series.

Tại đây, các bạn chỉ cần kéo thông số Gap Width về xuống 0% là đã hoàn thành rồi.


Kết quả là chúng ta có được một biểu đồ hình phễu hoàn chỉnh như sau:


Biểu đồ Excel nâng cao: Biểu đồ Pareto

Mục đích sử dụng biểu đồ Pareto

Dựa trên quy luật Pareto (quy luật 80/20), biểu đồ Pareto được sử dụng chủ yếu trong lĩnh vực quản lý chất lượng và quản lý dự án. Để giải thích về quy luật này, bạn có thể hiểu đơn giản như sau: Khoảng 80% kết quả đến từ 20% nguyên nhân gây ra. Ví dụ: 80% doanh thu đến từ 20% khách hàng.

Biểu đồ Pareto sẽ trở nên vô cùng hữu dụng khi bạn là quản lý dự án và bạn cần xác định nguyên nhân xảy ra sự cố trong một hoạt động nhất định.

Cách vẽ biểu đồ Pareto

Chúng ta sẽ cùng thực hành vẽ biểu đồ trong Excel nâng cao với một bảng dữ liệu dưới đây:

Bước 1: Tính tổng cộng dồn

Đầu tiên, chúng ta sẽ thêm vào bên phải cột B một cột mới để tính tổng cộng dồn lượng phản hồi theo đơn vị phần trăm, nghĩa là với nguyên nhân cuối cùng trong bảng, con số đưa ra sẽ là 100%.

Công thức hàm tính toán của chúng ta tại ô C3 trong trường hợp này như sau:

=SUM($B$3:B3)/SUM($B$3:$B$11)


Bước 2: Vẽ biểu đồ cột

Hãy chọn toàn bộ phạm vi dữ liệu cần đưa lên biểu đồ (A3:C11) và đi tới thẻ Insert > nhóm Charts > biểu tượng biểu đồ cột > Clustered Column.

Phần mềm sẽ vẽ cho chúng ta một biểu đồ Excel đơn giản như sau:



Bước 3: Vẽ biểu đồ Pareto
Với biểu đồ trên, chúng ta sẽ chuyển các cột màu cam về dạng đường để đúng với định dạng biểu đồ Pareto. Hãy nhấn chuột phải tại bất kỳ cột nào trên biểu đồ Excel và chọn Change Series Chart Type.

Hộp thoại Change Chart Type hiện lên. Bạn hãy đổi Chart Type của dữ liệu Cộng dồn sang dạng Line và tích chọn ô Secondary Axis.

Bây giờ thì biểu đồ Pareto của chúng ta đã gần hoàn chỉnh rồi.
Bước 4: Điều chỉnh thông số trục tung
Bạn sẽ để ý rằng trục tung bên phải đang hiển thị giá trị cao nhất là 120%. Điều này hoàn toàn không cần thiết vì số liệu của chúng ta dừng lại ở mức 100%. Chính vì vậy, hãy điều chỉnh thông số bằng cách nhấn chuột tại trục tung, chọn Format Axis. Sau đó, điền 1 (bằng với 100%) vào ô Maximum và thay đổi thông số Major thành 0.2 (bằng với 20%) để hiển thị các mốc trên trục tung cách nhau 20%.


Và đây là thành quả biểu đồ Pareto của chúng ta. Vậy là bạn đã học thêm được một biểu đồ Excel nâng cao nữa rồi đó.



Tổng kết

Như vậy, chúng ta đã học được thêm 3 dạng biểu đồ Excel nâng cao, bao gồm đường cong chuông, biểu đồ hình phễu và biểu đồ Pareto. So với các biểu đồ ở phần 1, các biểu đồ ở phần 2 tập trung nhiều hơn vào khâu chuẩn bị dữ liệu. Các bạn hãy chăm chỉ thực hành để ghi nhớ kiến thức nhé. Bên cạnh đó, đừng quên đón đọc các bài viết tiếp theo trên blog Gitiho!

Gitiho xin cảm ơn và chúc bạn thành công!


0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội