Cách vẽ biểu đồ cột kết hợp đường biểu diễn trên Excel chi tiết nhất

Hãy cùng Học Excel Online khám phá cách thực hiện vẽ biểu đồ kết hợp cột và đường trên Excel. Bạn sẽ thực hiện việc vẽ biểu đồ như thế nào khi phải biểu diễn dữ liệu đồng thời ở dạng số và %? Hoặc làm thế nào để vẽ biểu đồ vừa hiển thị các cột biểu diễn các con số, vừa thể hiện đường biểu diễn cho các tỷ lệ % trên cùng một biểu đồ?

Xét ví dụ

Cung cấp bảng dữ liệu thể hiện doanh số của nhân viên như sau:

Tạo biểu đồ biểu thị doanh số của các nhân viên và phần trăm doanh số của mỗi cá nhân trong tổng số toàn công ty là yêu cầu.

Phân tích dữ liệu và yêu cầu vẽ biểu đồ

Dữ liệu hiện đang ở dạng nguyên thủy, chưa trải qua bất kỳ xử lý nào. Để tạo ra biểu đồ trên Excel, cần phải thực hiện việc tóm tắt dữ liệu trước. Điều này là rất quan trọng trong quá trình thực hiện bất kỳ yêu cầu vẽ biểu đồ nào.

Các nhân viên có sự lặp lại. Khi tổng hợp thì các nhân viên chỉ xuất hiện 1 lần trong báo cáo.

Hiện chưa có thông tin dưới dạng %. Cần thực hiện tính toán để tạo ra dữ liệu này.

Để tổng hợp thông tin từ bảng dữ liệu này, ta có thể thực hiện theo phương pháp sau đây:

1. Lọc bỏ các giá trị trùng trong cột Nhân viên

Các giai đoạn thực hiện:

Sao chép danh sách nhân viên từ cột ban đầu sang cột D (một cột trống).

Tại cột D, lựa chọn tính năng Xóa bỏ các bản sao trong thẻ Dữ liệu.

Kết quả thu được sau khi thực hiện chức năng Remove Duplicates là các giá trị không trùng như hình dưới đây.

2. Tính kết quả doanh số theo từng nhân viên

Với điều kiện là tên nhân viên, trường hợp này sử dụng hàm SUMIF để tính toán.

Công thức hàm =SUMIF(range,criteria,sum_range) có cấu trúc như vậy.

Trong phạm vi, cột A với tên Nhân viên là nơi để đặt điều kiện.

Tiêu chuẩn: yêu cầu tên nhân viên được đặt ở cột D.

Sum_range: Phạm vi tính tổng bao gồm cột Doanh số (cột B).

Kết quả được thể hiện như sau:

Trên bảng thành tích chúng ta có thể thấy:

Dữ liệu có khoảng cách rất lớn giữa giá trị nhỏ nhất là 10 và giá trị lớn nhất là 260 (tương đương với 26 lần).

Dữ liệu chưa được sắp xếp theo trật tự.

3. Sắp xếp dữ liệu theo thứ tự

Sắp xếp lại dữ liệu theo thứ tự từ lớn đến nhỏ để thuận lợi hơn trong việc vẽ biểu đồ cột.

Các giai đoạn thực hiện:

Lựa chọn bảng dữ liệu tổng hợp (bao gồm cả dòng tiêu đề).

Trên thẻ Dữ liệu chọn Sắp xếp.

Lựa chọn cột Doanh thu trong bảng Sắp xếp và sắp xếp theo thứ tự giảm dần (Từ lớn đến nhỏ).

4. Tính phần trăm doanh số của mỗi nhân viên

Chúng ta sẽ phải bổ sung thêm mục Tổng số để tính phần trăm doanh thu của từng nhân viên trong tổng số toàn bộ công ty.

Để tính tỷ lệ doanh thu của từng cá nhân, ta sử dụng phép chia doanh số của họ cho tổng số doanh thu của toàn công ty.

Dựa trên nguyên tắc này, chúng ta thêm một cột Tỷ lệ và tính toán như sau:

Dữ liệu trong cột tỷ lệ phải có định dạng dạng phần trăm (%) và có thể chứa tối đa 2 số sau dấu thập phân. Lưu ý.

Lựa chọn biểu đồ và vẽ biểu đồ

Nhận dạng biểu đồ

Trong môn địa lý, ta thường biết đến loại biểu đồ hỗn hợp giữa độ ẩm (%) và số lượng mưa (mm). Vì vậy, khi yêu cầu vẽ biểu đồ cần bao gồm cả hai loại dữ liệu này, ta có thể sử dụng loại biểu đồ kết hợp. Biểu đồ này có thể được tìm thấy trong mục Combo Chart của Excel.

Trong phần chèn thẻ, chọn mục Biểu đồ và chọn loại Biểu đồ Kết hợp.

Trong phiên bản này, ta có thể tìm thấy sự gợi ý về biểu đồ cột kết hợp với biểu đồ đường.

Cách vẽ biểu đồ cột trong Excel

Trên thực tế, bạn có thể vẽ đồ thị này bằng 2 phương pháp khác nhau.

Cách thứ nhất: Tạo biểu đồ kết hợp (kiểu thứ hai) trực tiếp từ Combo Chart. Chức năng này có thể được áp dụng từ phiên bản Excel 2013 trở lên.

Vẽ theo dạng biểu đồ cột thông thường, sau đó có thể tùy chỉnh thành dạng Combo Chart. Phương pháp này áp dụng được cho tất cả các phiên bản của Excel, là cách thứ 2.

Hãy cùng khám phá phương pháp thứ hai, đó là khi không có bản Excel 2013, 2016, phương pháp này sẽ mất nhiều thời gian và gặp nhiều khó khăn hơn.

Bằng cách lựa chọn khu vực D1:F10 (loại trừ hàng tổng), bắt đầu vẽ biểu đồ dạng cột thường trong thẻ Chèn.

Kết quả mà chúng ta thu được là một biểu đồ cột với hình dạng đặc trưng như sau:.

Tỷ lệ chiếm phần rất ít, gần như khó có thể nhận thấy trên biểu đồ kiểu này.

Chúng ta cần thực hiện việc chuyển đổi cách biểu diễn tỷ lệ sang dạng đường biểu diễn theo phần trăm.

Thêm trục tọa độ % vào bên phải biểu đồ

Thực hiện như sau: Hành động này có nghĩa là tạo ra một biểu đồ cột với hai trục tung và một trục hoành. Trong đó, trục tung thứ nhất biểu thị dưới dạng số, còn trục tung thứ hai biểu thị dưới dạng phần trăm.

Nhấn tổ hợp phím Ctrl + Mũi tên lên để chuyển sang chế độ chọn phần cột tỷ lệ, sau đó tìm và chọn một cột bất kỳ trong cột Doanh thu.

Nhấp chuột phải vào cột tỷ lệ và lựa chọn Thay Đổi Loại Biểu Đồ Chuỗi.

Trong bảng Thay đổi Loại biểu đồ, ở phần Tỷ lệ, chúng ta cài đặt như sau:

Loại biểu đồ này được gọi là đường kẻ (Line with Markers = Đường nối các điểm trên mỗi cột).

Trục phụ: tích đánh dấu. Chức năng này giúp thêm một trục tung phụ bên phải biểu đồ.

Biểu đồ mẫu được thể hiện ở phía trên khi đặt cài đặt. Nhấn nút OK để kết thúc biểu đồ sau khi hoàn thành thiết lập.

Khóa học EX101 của Học Excel Online sẽ giúp bạn nâng cao kiến thức từ cơ bản đến chuyên sâu về Excel. Trong khóa học này, bạn sẽ được học về các công cụ và hàm của Excel, cách thực hiện biểu đồ và áp dụng kiến thức vào công việc hiệu quả. Đăng ký khóa học ngay để tận hưởng nhiều ưu đãi từ Học Excel Online. Xem thêm thông tin tại: Học Excel Online.

Tác giả: dtnguyen (Nguyễn Đức Thanh)

Học Excel trực tuyến trên trang DTNguyen.Business.