Biểu đồ biểu diễn tình hình thực hiện kế hoạch là một dạng biểu đồ thường gặp phải trong công việc, đặc biệt khi phương pháp đánh giá hiệu quả công việc dựa theo KPIs như hiện nay. Việc biểu diễn loại biểu đồ này cũng phức tạp hơn so với các biểu đồ khác. Có nhiều loại biểu đồ đáp ứng các yêu cầu này: biểu đồ cột lồng vào nhau biểu diễn số liệu dạng con số đạt, không đạt, vượt kế hoạch…, biểu đồ dạng hình tròn lồng vào nhau biểu diễn theo số liệu %…  Trong bài viết này chúng ta hãy cùng Học Excel Online tìm hiểu cách vẽ biểu đồ kế hoạch thực tế dạng cột lồng nhau trên Excel nhé.

Đặc điểm báo cáo tình hình thực hiện kế hoạch

Để có thể vẽ biểu đồ trong Excel thì chúng ta cần có 1 bảng dữ liệu. Bảng dữ liệu này chính là bảng báo cáo về tình hình thực hiện kế hoạch. Trong dạng báo cáo này thường có cấu trúc như sau:

• Chỉ tiêu, đối tượng, công việc: đánh giá tình hình thực hiện kế hoạch cho đối tượng nào?
• Số kế hoạch: số liệu này thường có sẵn từ khi lập kế hoạch, không cần phải tính toán lại.
• Số thực tế: Số liệu này cần được tính toán, tổng hợp lại từ bảng dữ liệu theo dõi chi tiết từng ngày, từng lần phát sinh số liệu có liên quan.

Ví dụ: chúng ta có bảng dữ liệu và kế hoạch như sau:

(Các bạn có thể tải về file đính kèm tại địa chỉ: http://bit.ly/2QlLVLZ)

Trong bảng dữ liệu phát sinh (tới dòng 102) thể hiện từng lần phát sinh của mỗi sản phẩm theo ngày. Đây là số thực hiện.

Yêu cầu của chúng ta là phải tính được tổng số lượng của mỗi sản phẩm vào bảng báo cáo tình hình thực hiện kế hoạch. Từ đó căn cứ vào số kế hoạch, số thực hiện thì chúng ta mới vẽ được biểu đồ.

Cách tính như sau:

Yêu cầu tính toán là tính tổng, có bao gồm điều kiện là từng sản phẩm có trong cột Sản phẩm, kết quả cần tính là Số lượng.

=> Đây là yêu cầu tính tổng theo 1 điều kiện, vì vậy chúng ta có thể sử dụng hàm SUMIF để tính.

Cách tính như sau:

Tại ô G3 (tính cho sản phẩm 1) đặt hàm SUMIF, trong đó:

• Range: là vùng chứa các điều kiện, chính là cột Sản phẩm trong bảng dữ liệu phát sinh, từ A3:A102
• Criteria: là điều kiện, cụ thể là sản phẩm tương ứng tại dòng 3 trong báo cáo, là ô E3
• Sum_range: là vùng tính tổng, chính là cột Số lượng, từ C3:C102

G3=SUMIF($A$3:$A$102,E3,$C$3:$C$102)

Chú ý: Các vùng Range và Sum_range cần phải cố định để không bị thay đổi khi áp dụng công thức cho các sản phẩm khác trong báo cáo.

Sau đó chúng ta filldown (phím tắt Ctrl+D) công thức từ G3 tới G12 cho các sản phẩm còn lại.

Kết quả thu được như sau:

Vẽ biểu đồ kế hoạch thực tế dạng cột lồng nhau

Chèn biểu đồ và gán dữ liệu vào biểu đồ

Để vẽ biểu đồ, chúng ta vào thẻ Insert và chọn biểu đồ hình cột, dạng 2D-Column/ Clustered Column (có thể không cần phải chọn trước bảng dữ liệu, mà thực hiện ngay thao tác insert/chart)

Sau khi thêm biểu đồ, chúng ta sẽ lựa chọn lại dữ liệu đưa vào biểu đồ tại thẻ Chart Tools/ Design/ Select Data

Tại đây chúng ta sẽ thêm các dữ liệu vào biểu đồ như sau:

Mục Legend Entires (series) bấm Add

• Phần kế hoạch: gồm Series name là ô F2 (tên tiêu đề cột Kế hoạch), Series values là vùng dữ liệu của báo cáo cho cột kế hoạch từ F3:F12
• Phần thực hiện: gồm Series name là ô G2 (tên tiêu đề cột Thực hiện), Series values là vùng dữ liệu của báo cáo cho cột thực hiện từ G3:G12

Mục Horizontal (category) Axis Labels bấm Edit, chọn tới vùng tên của các sản phẩm, từ E3:E12

Kết quả sau khi chọn các vùng dữ liệu của biểu đồ như sau:

Thay đổi cách biểu diễn của biểu đồ

Mục đích của chúng ta là biểu diễn 2 cột Kế hoạch (màu xanh) và Thực hiện (màu cam) phải lồng vào bên trong nhau. Vì vậy tới đây chúng ta cần phải làm tiếp thao tác thay đổi cách biểu diễn các trục của biểu đồ.

Bấm chọn vào 1 cột bất kỳ, bấm chuột phải và chọn Change Series Chart Type…

Trong cửa sổ Change Chart Type, chúng ta đánh dấu chọn cho mục Secondary Axis Label cho nhóm cột Thục hiện (lưu ý khi làm bước này cần giữ nguyên Chart Type của 2 nhóm này là dạng Clustered Column)

Khi đó chúng ta sẽ thấy kết quả thu được là 3 cột lồng vào nhau trên cùng 1 gốc tọa độ. Biểu đồ có thêm 1 trục tung thứ 2 bên tay phải.

Tuy nhiên độ lớn của 2 trục tung lại không bằng nhau:

• Trục bên trái từ 0 đến 350
• Trục bên phải từ 0 đến 250

Do đó cần phải đưa độ lớn của 2 trục về bằng nhau để đảm bảo 2 biểu đồ thể hiện giống như trên cùng 1 trục tọa độ. Khi làm đến đây chúng ta sẽ tuân theo nguyên tắc: Điều chỉnh ở trục đang có số nhỏ hơn. Cách làm như sau:

• Bấm chuột trái chọn trục nhỏ hơn (trục bên phải)
• Tại trục này, bấm chuột phải chọn Format Axis

• Thay đổi độ lớn của trục tọa độ này tương ứng với trục còn lại (minimum = 0 và maximum = 350)

Kết quả thu được như sau:

Tới đây chúng ta có thể làm tiếp 1 bước nữa đó là điều chỉnh phần thân biểu đồ để khiến cột Thực hiện nhỏ hơn cột Kế hoạch, nằm gọn bên trong thân cột Kế hoạch. Cách làm như sau:

• Bấm chọn cột Kế hoạch (phần cột màu xanh) chọn Format Data Series
• Trong cửa sổ Format Data Series, mục Series options/ Plot Series On/ Gap Width thiết lập ở mức khoảng 40% (Số càng nhỏ thì thân biểu đồ càng rộng ra)

Như vậy chúng ta thấy phần cột Kế hoạch đã nằm bao quanh bên ngoài phần cột Thực hiện.

• Những sản phẩm có phần cột Thực hiện vượt ra ngoài phạm vi cột Kế hoạch tức là Vượt kế hoạch.
• Những sản phẩm có phần cột Thực hiện nằm bên trong phạm vi cột Kế hoạch tức là Chưa đạt kế hoạch.

Biểu đồ của chúng ta đã hoàn thành xong phần nội dung chính rồi. Việc còn lại là trang trí, định dạng lại biểu đồ cho đẹp nữa là xong. Chúc các bạn áp dụng tốt nội dung này vào công việc nhé.

Bài tiếp theo:

Hướng dẫn cách điều khiển biểu đồ tự động thay đổi theo nút tùy chọn trên Excel

Tham khảo:

8 Cách để tút lại biểu đồ tài chính đẹp trong Excel

Vẽ biểu đồ thực tế vs kế hoạch dạng  cột nằm ngang (bar)