Hướng dẫn chi tiết vẽ biểu đồ cột kết hợp với đường biểu diễn trên Excel

Đối với bộ dữ liệu vừa gồm giá trị số vừa gồm tỷ lệ phần trăm, bạn sẽ vẽ biểu đồ như thế nào? Hoặc là phải thực hiện ra sao để có thể minh họa một biểu đồ đồng thời hiển thị các cột cho giá trị số và các đường cho tỷ lệ phần trăm? Hãy theo dõi cùng Siêu Marketing để biết cách vẽ biểu đồ cột và đường kết hợp trên Excel.

Cụ thể ví dụ

Bảng dữ liệu dưới đây cho chúng ta thấy doanh số của các nhân viên:

cách vẽ biểu đồ cột kết hợp đường
cách vẽ biểu đồ cột kết hợp đường

Đề xuất: Cần vẽ biểu đồ thị hiện doanh số của mỗi nhân viên và tỷ lệ % của họ so với tổng doanh số của công ty.

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

Khi muốn vẽ biểu đồ trong Excel, bước phân tích dữ liệu được xem là bước cực kỳ quan trọng. Chúng ta phải chắc chắn rằng dữ liệu cần được sắp xếp, không phải là dạng nguyên thủy ban đầu. Như ở ví dụ phía trên, dữ liệu vẫn còn thô và chưa được xử lý:

  • Các tên nhân viên có sự trùng lặp. Trong báo cáo tổng hợp, mỗi nhân viên cần hiển thị một lần duy nhất.
  • Tỷ lệ % chưa được tính toán để có dữ liệu cần thiết.

Sau đây là cách chúng ta có thể tổng hợp thông tin từ bảng dữ liệu:

1. Loại bỏ các giá trị lặp lại trong cột Nhân viên

Các thao tác thự hiện như sau:

  • Chép cột Nhân viên vào cột D (đây là một cột không chứa dữ liệu)
  • Trong cột D, chọn lựa chức năng Remove Duplicates từ thẻ Data
  • Sau khi áp dụng Remove Duplicates, ta thu được danh sách không còn giá trị lặp lại như hình sau:
Loại bỏ giá trị lặp trong cột
Loại bỏ giá trị lặp trong cột

Tham khảo thêm: Hướng dẫn chi tiết cách loại bỏ dữ liệu lặp lại trong danh sách Excel

2. Định kỳ doanh số cho từng nhân viên cụ thể

Đối với trường hợp này, cần tính theo điều kiện là tên của từng nhân viên, ta sử dụng hàm SUMIF để thực hiện:

Công thức của hàm là =SUMIF(range,criteria,sum_range)

  • range: cột Nhân viên (cột A) nơi bao gồm các điều kiện Tên nhân viên
  • criteria: tiêu chí là tên nhân viên nằm trong cột D.
  • sum_range: phần tính tổng nằm trong cột Doanh số (cột B)

Kết quả được như hình dưới:

Phân tích dữ liệu và vẽ biểu đồ cột kết hợp với đường
Phân tích dữ liệu và vẽ biểu đồ cột kết hợp với đường

Từ bảng kết quả, chú ý rằng:

  • Có sự chênh lệch đáng kể trong dữ liệu với con số nhỏ nhất là 10 và lớn nhất là 260 (cao gấp 26 lần).
  • Dữ liệu chưa được xếp theo một trật tự nào cả.

3. Xếp thứ tự dữ liệu

Để thuận lợi hơn trong quá trình

Để bắt đầu quá trình vẽ biểu đồ cột, cần sắp xếp dữ liệu từ cao xuống thấp. Theo đó, chúng ta sẽ xếp dữ liệu theo thứ tự giảm dần.

Những bước cần thực hiện gồm:

  • Lựa chọn bảng tổng hợp dữ liệu cần sắp xếp, kể cả tiêu đề.
  • Chọn vào tab Data và tiếp tục chọn Sort.
  • Trong khung Sort, chọn cột “Doanh số” để sắp xếp sao cho giảm dần từ lớn xuống nhỏ.
Phân tích dữ liệu vẽ biểu đồ cột kết hợp đường 1
Phân tích dữ liệu vẽ biểu đồ cột kết hợp đường 1

Tham khảo thêm: Hướng dẫn sắp xếp dữ liệu theo nhiều tiêu chí trong Excel

4. Tính tỷ lệ phần trăm doanh số của từng nhân viên

Để xác định tỷ lệ phần trăm doanh số của từng nhân viên so với toàn công ty, chúng ta cần phải thêm dòng chứa Tổng cộng doanh số.

Phần trăm doanh số của từng nhân viên được tính như sau: Doanh số cá nhân chia cho Tổng doanh số của công ty.

Để làm điều này, thêm cột “Tỷ lệ” và thực hiện tính toán:

Phân tích dữ liệu vẽ biểu đồ cột kết hợp đường 2
Phân tích dữ liệu vẽ biểu đồ cột kết hợp đường 2

Ghi chú: Định dạng dữ liệu ở cột tỷ lệ thành Phần trăm (%) và giữ lại 2 chữ số sau dấu phẩy.

Chọn lọc biểu đồ và thực hiện vẽ biểu đồ

Xác định loại biểu đồ

Đối với nhu cầu vẽ biểu đồ bao gồm cả dữ liệu dạng số và dữ liệu tỷ lệ phần trăm, chúng ta cần nghĩ đến loại biểu đồ tổ hợp. Chẳng hạn, trong Địa lý chúng ta thường thấy biểu đồ tổ hợp giữa độ ẩm (%) và lượng mưa (mm). Loại biểu đồ này trong Excel có thể tìm thấy dưới dạng Combo Chart.

Trên thẻ Insert, ta chọn nhóm Chart và sau đó chọn Combo Chart.

Chọn lọc biểu đồ và vẽ biểu đồ
Chọn lọc biểu đồ và vẽ biểu đồ

Ở đây chúng ta có thể thấy biểu đồ tổ hợp được gợi ý, kết hợp cả dạng cột và đường.

Phương pháp vẽ biểu đồ cột trong Excel

Có hai cách để tạo biểu đồ này:

  • Cách thứ nhất: Vẽ trực tiếp từ mục Combo Chart, đặc biệt áp dụng cho Excel từ phiên bản 2013 trở về sau.
  • Cách thứ hai: Bắt đầu từ một biểu đồ cột thông thường rồi điều chỉnh nó thành Combo Chart, có thể áp dụng cho mọi phiên bản của Excel.

Cách một thường nhanh chóng và đơn giản hơn. Tuy nhiên, nếu không sử dụng được phiên bản Excel 2013, 2016 thì ta có thể áp dụng cách thứ hai, như sau:

Chọn vùng dữ liệu cần vẽ (ví dụ: D1:F10, không bao gồm dòng tổng cộng). Trong thẻ Insert, mở một biểu đồ cột thông thường:

Cách vẽ biểu đồ cột kết hợp đường
Hướng dẫn phương pháp vẽ biểu đồ cột kết hợp với đường

Sau khi thực hiện, biểu đồ dạng cột mà chúng ta thu được như sau:

Hướng dẫn biểu đồ cột kết hợp với đường (phần 2)
Hướng dẫn biểu đồ cột kết hợp với đường (phần 2)

Phần tỷ lệ trong biểu đồ này thường chỉ chiếm một diện tích rất nhỏ, đến nỗi khó có thể nhận biết.

Như vậy, ta cần phải chuyển phần tỷ lệ sang dạng đường và thể hiện theo tỷ lệ phần trăm.

Bổ sung trục phần trăm đặt ở cạnh phải của biểu đồ

Điều này cần hiểu là chúng ta đang tiến hành vẽ một biểu đồ cột với hai trục đứng và một trục ngang, trong đó một trục đứng để biểu diễn giá trị bằng số và trục còn lại để biểu diễn giá trị bằng phần trăm. Các bước thực hiện cụ thể như sau:

  • Chọn một cột trong phần Doanh số, sau đó nhấn Ctrl + Mũi tên lên để chọn cột tỷ lệ.
  • Nhấn chuột phải vào cột tỷ lệ và chọn Change Series Chart Type.
Bổ sung trục phần trăm vào biểu đồ từ bên phải (1)
Bổ sung trục phần trăm vào biểu đồ từ bên phải (1)

Trong cửa sổ Change Chart Type, cấu hình cho phần Tỷ lệ chúng ta lựa chọn như sau:

  • Chart Type là dạng đường có đánh dấu (Line with Markers = Đường có chấm tròn trên cột)
  • Secondary Axis: gắn dấu tích. Ý của tùy chọn này là thêm một trục đứng thứ hai ở phía bên phải của biểu đồ.
Thêm trục phần trăm bên phải biểu đồ (phần 2)
Thêm trục phần trăm bên phải biểu đồ (phần 2)

Kết quả của việc cài đặt sẽ thể hiện ngay trên màn hình ngay lập tức. Sau khi hoàn tất việc cài đặt, chọn OK để hoàn thiện biểu đồ:

Bước đệm vẽ biểu đồ cột và đường trong excel
Bước đệm vẽ biểu đồ cột và đường trong excel

Mời bạn tham gia khóa học SM90 – “Excel từ cơ bản tới chuyên gia” của Siêu Marketing. Trong khóa học này bạn sẽ được học về Excel, nắm vững các hàm và công cụ, cách tạo biểu đồ, và cách áp dụng chúng vào công việc hiệu quả. Đừng bỏ lỡ khóa học để nhận nhiều ưu đãi từ Siêu Marketing. Để biết thêm thông tin chi tiết, hãy truy cập Siêu Marketing.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop