Giả sử bạn cần sử dụng Excel để so sánh hiệu suất thực tế của mình với KPIs đặt ra đầu tháng, bạn sẽ sử dụng biểu đồ nào? Nếu bạn còn chưa biết câu trả lời thì cùng Gitiho tìm hiểu biểu đồ so sánh trong Excel dạng cột lồng nhau trong bài viết hôm nay nhé.
Bạn đang xem: Vẽ biểu đồ so sánh trong excel
Đặc điểm báo cáo thể hiện qua biểu đồ so sánh trong Excel
Mỗi biểu đồ được lựa chọn dựa vào đặc điểm của báo cáo trên Excel. Vậy báo cáo về tình hình thực hiện KPIs chúng ta đang cần chuẩn bị có những đặc điểm gì? Thông thường, dạng báo cáo này có cấu trúc 3 phần:
Chỉ tiêu,đối tượng: Chúng ta cần đánh giá tình hình chođối tượng cụ thể nào?Số kế hoạch: Chúng ta có bao nhiêu kế hoạch KPIs? Số liệu này thường có sẵn từkhi lập kế hoạch
Số thực tế: Chúng ta đang thực hiện kế hoạch đến đâu? 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.
Để giúp các bạn hiểu rõ hơn, chúng ta cùng đi tìm hiểu một ví dụ về bảng dữ liệu và kế hoạch dưới đây:
Trong bảng dữ liệu phát sinh là chi tiết các lần bán sản phẩm theo ngày. Bảng báo cáo trên Excel bên phải là nơi ta sẽ thực hiện lập biểu đồ so sánh trong Excel để đối chiếu tiến độ làm việc với KPIs.
Việc đầu tiên chúng ta cần làm là tính toán tổng mỗi loại sản phẩm đã bán ra. Kết hợp với các con số KPIs, chúng ta mới có thể vẽ được biểu đồ so sánh trong Excel.
Vì chúng ta đang thực hiện phép tính tổng có điều kiện là đối tượng tính tổng tương ứng với từng loại sản phẩm, nên chúng ta sử dụng hàm SUMIF.
Cách tính toán như sau:
Tại bảng báo cáo trên Excel bên phải, chúng ta nhập hàm SUMIF vào ô G3:
=SUMIF($A$3:$A$23,E3,$C$3:$C$23)
Sau đó chúng ta nhấn tổ hợp phím Ctrl + D để sử dụng tính năng Filldown trong Excel, điền công thức cho các ô còn lại trong cột.
Kết quả thu được như sau:
Cách vẽ biểu đồ so sánh trong Excel dạng cột lồng nhau
Chèn biểu đồ so sánh trong Excel và nhập dữ liệu báo cáo vào biểu đồ
Bước đầu tiên để vẽ biểu đồ so sánh trong Excel là chèn biểu đồ. Sau đó, chúng ta sẽ nhập dữ liệu từ báo cáo trên Excel vừa tính toán ở phần trên vào biểu đồ.
Để vẽ biểu đồ so sánh trong Excel, chúng ta vào tab Insert > biểu đồ cột > biểu đồ 2D-Column > Clustered Column.
Biểu đồ so sánh trong Excel hiện tại đang là một trang trắng vì Excel chưa xác định dữ liệu cần biểu thị. Do đó, chúng ta sẽ lựa chọn dữ liệu từ báo cáo trên Excel để đưa vào biểu đồ bằng cách vào tab Chart Tools > Design > Select Data.
Hộp thoại Select Data Resource hiện lên. Tại đây chúng ta chọn dữ liệu từ báo cáo trên Excel như sau
Tại mục Legend Entries:
Ta nhấn Add và thêm dữ liệu vào các phần như sau:
Phần KPIs:Series name làô F2 (tên tiêu đề cột KPIs), Series values là vùng dữ liệu của báo cáo trên Excel cho cột kế hoạchtừ F3:F12.Phần thực tế:Series name làô G2 (tên tiêuđề cột Thực tế), Series values là vùng dữ liệu của báo cáo cho cộtthực hiệntừ G3:G12.
Tương tự, tại mục Horizontal (category) Axis Labels, chúng ta nhấn Edit rồi thêm dữ liệu cột "Sản phẩm" từ báo cáo trên Excel. Sau khi đã hoàn tất nhập dữ liệu, biểu đồ so sánh trong Excel của chúng ta như sau:
Thay đổi giao diện biểu đồ so sánh trên Excel
Chúng ta đã tạo được biểu đồ so sánh trong Excel dạng cột. Tuy nhiên, thay vì để cột Kế hoạch (cột xanh) và cột Thực tế (cột cam) liền nhau, chúng ta muốn 2 cột này lồng vào nhau. Vậy chúng ta cần làm gì?
Đầu tiên, chúng ta ấn vào 1 cột bất kì trong biểu đồ, nhấn chuột phải chọn Change Series Chart Type…
Cửa sổ Change Chart Type xuất hiện. Chúng ta đánh dấu mục Secondary Axis Label cho nhóm cột Thực hiện. Lưu ý chúng ta cần giữ Chart Type của biểu đồ là Clustered Column.
Như vậy, chúng ta đã lồng các nhóm cột vào nhau. Trên biểu đồ so sánh trong Excel sẽ xuất hiện thêm một trục tung bên phải. Ở đây chúng ta nhận thấy có một vân đề: tham số 2 trục tung khác nhau. Như vậy, dữ liệu báo cáo trên Excel được hiển thị sẽ không có ý nghĩa so sánh.
Để giải quyết vấn đề này, ta cần đưa 2 trục về thước đo giống nhau. Các bạn hãy tuân theo nguyên tắc: Điều chỉnh trục đang có số nhỏ hơn. Cách thực hiện như sau:
Chọn trục bên phải (trục nhỏ hơn) và nhấn chuột phải chọn Format Axis.
Cửa sổ Format Axis xuất hiện bên phải màn hình. Chúng ta thay đổi các số đo của trục tọa độ này sao cho tương ứng với trục còn lại: min = 0, max = 120.
Chúng ta thu được kết quả biểu đồ so sánh trong Excel như sau:
Lúc này, dữ liệu từ báo cáo trên Excel đã được thể hiện rõ ràng hơn trong biểu đồ. Để biểu thị sự khác biệt giữa cột KPIs và cột Thực tế, chúng ta thực hiện thêm 1 bước điều chỉnh phần thân biểu đồ để cột Thực tế nhỏ hơn cột KPIs. Chúng ta làm như sau:
Chọn cột KPIs (cột xanh) > FormatData Series.Cửa sổ Format Data Series xuất hiện. Tại mục Gap
Width thiết lập chiều rộng cột ở 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 KPIs đã nằm bao quanh bên ngoài phần cột Thực tế.
Những sản phẩm có phần cột Thực tế vượt ra ngoài phạm vi cột KPIs tức là Vượt KPIs.Những sản phẩm có phần cột Thực tế nằm bên trongphạm vi cột KPIs tức làChưađạt KPIs.
Tổng kết
Dựa vào báo cáo trên Excel về các chỉ số KPIs và các giao dịch bán hàng phát sinh, chúng ta đã tìm hiểu cách lập biểu đồ so sánh trong Excel để có cái nhìn rõ ràng về tiến độ, hiệu suất công việc.Để học thêm các cách lập biểu đồ phân tích dữ liệu hiệu quả trong Excel, các bạn hãy đón đọc các bài viết trên blog tin học văn phòng Gitiho và đăng kí khóa học:
Hướng dẫn sau đây sẽ chỉ cho bạn cách tạo một biểu đồ trong Excel nhằm so sánh ngân sách dự chi so với số liệu thực tế trong Excel.
Bước 1: Lấy dữ liệu
Thiết lập dữ liệu của bạn. Hãy nói rằng bạn có dự chi ngân sách và giá trị thực tế cho một loạt các danh mục (sản phẩm, tháng, phòng ban, v.v.) ở định dạng này. Tính toán phương sai và % phương sai bằng các công thức đơn giản như dưới đây.
Bước 2: Tạo biểu đồ cột
Chỉ cần chọn danh mục, dự chi và các cột thực tế của bạn và chèn biểu đồ cột (được nhóm). Quay lại hướng dẫn cách vẽ các loại biểu đồ cơ bản trong Excel để xem lại cách tạo biểu đồ cột. Sau đó, bạn sẽ nhận được điều này.
Bước 3: Thêm lại ngân sách và dữ liệu thực tế vào biểu đồ
Thêm dự chi và giá trị thực tế vào biểu đồ một lần nữa. Bây giờ chúng ta có mỗi danh mục gồm 4 cột cho mỗi loại, như được hiển thị bên dưới.
Bước 4: Thay đổi các cột mới được thêm thành các đường
Nhấp chuột phải vào một trong hai cột mới được thêm vào, chọn“Change series chart type” và chuyển đổi cả hai cột thành các đường.
Bước này sẽ được thực hiện bằng các cách khác nhau trong các phiên bản Excel cũ hơn, và bạn phải làm điều đó cho mỗi cột. Trong Excel 2013 trở lên, bạn sẽ chuyển đến màn hình “Combination chart” để vẽ biểu đồ kết hợp và bạn có thể điều chỉnh loại dữ liệu cho tất cả các chuỗi từ đó.
Kết quả:
Bước 5: Thêm thanh lên/xuống cho các đường này
Chọn một trong hai dòng và sử dụng biểu tượng + để chèn thanh lên / xuống. Trong các phiên bản trước của Excel, bạn cần sử dụng Insert ribbon hoặc menu để làm tương tự.
Bước 6: Định dạng xuống thanh và cột
Nhanh chóng điều chỉnh màu sắc của từng thanh (không chạm vào các đường).
Bước 7: Điều chỉnh độ rộng khoảng cách và các chuỗi giá trị chồng lên nhau
Thực hiện các bước dưới đây:
Chọn các cột đầu tiên. Chuyển đến loạt định dạng (Ctrl + 1).Điều chỉnh Series overlap lên 0%.Đặt Gap width đến 150%.Bây giờ chọn các dòng.Điều chỉnh Gap width đến 300%Hãy thoải mái điều chỉnh/thử nghiệm với các kết hợp độ rộng khoảng cách khác nhau để xem thay đổi nào vừa mắt bạn nhất.Kết quả của bạn sẽ như thế này:
Bước 8: Làm cho các đường trở nên vô hình
Chọn các dòng (một lúc) và nhấp vào Format ribbon -> Shape Outline -> No Outline
Kết quả:
Bước 9: Thêm tiêu đề vào biểu đồ của bạn và xóa các mục chú thích không cần thiết
Nhấp đúp chuột vào tiêu đề biểu đồ và nhập một cái gì đó có ý nghĩa. Ngoài ra, bạn cũng có thể liên kết nó với một giá trị ô. Để làm điều đó, chọn tiêu đề, nhấn = và trỏ đến một ô có tiêu đề bạn muốn sử dụng.
Để xóa các mục chú thích, nhấp vào chú giải biểu đồ, bây giờ bấm lại vào chuỗi 3, nhấn phím DEL. Lặp lại quy trình cho loạt 4.
Bước 10: Thêm nhãn dữ liệu cho cả hai đường
Chọn từng dòng một (nhớ rằng, các dòng là vô hình, vì vậy chỉ cần nhấp vào nơi chúng được cho là hoặc sử dụng hộp định dạng để chọn chúng). Bây giờ sử dụng nút + để thêm nhãn dữ liệu. Trong các phiên bản cũ hơn của Excel, bạn cần sử dụng ribbon hoặc menu để thêm nhãn. Ở giai đoạn này, biểu đồ của bạn sẽ trông như thế này:
Bước 11: Tính nhãn mới
Đây là phần thú vị. Bắt đầu bằng cách thiết lập quy tắc cho biểu tượng + giá trị bạn muốn hiển thị. Ví dụ, bạn có thể muốn hiển thị,
Ngón tay cái hướng xuống nếu phương sai dưới -5%Ngón tay đan chéo nếu phương sai nằm trong khoảng từ -5% đến 0%Biểu tượng OK nếu phương sai dương và dưới 10%Đồng ý nếu nó nằm trong khoảng từ 10% đến 25%Nhân đôi ngón tay cái lên nếu hơn 25%Tạo một phạm vi nơi biểu tượng % sẽ đi đến và điền vào các biểu tượng bằng tùy chọn Insert > Symbol. Chọn phông chữ Segoe UI Emoji để chèn biểu tượng cảm xúc thú vị.
Bảng giải thích biểu tượng của bạn sẽ trông như sau:
Lưu ý giá trị đầu tiên. Điều đó có nghĩa là chúng tôi sẽ hiển thị ngón tay cái xuống cho tất cả các giá trị từ -5% đến -100%.
Bây giờ, hãy tính toán các nhãn. Có hai bộ nhãn. Tích cực và tiêu cực. Điều này cho phép bạn kiểm soát tốt hơn về định dạng chúng. Khu vực dữ liệu thô của chúng tôi bây giờ trông như thế này:
Công thức cho nhãn:
Biểu tượng: = VLOOKUP (var%, ánh xạ bảng, 2) Chúng tôi đang sử dụng kỹ thuật tra cứu gần đúng để có được biểu tượng liên quan.Var 1: = IF (var% = 0, Biểu tượng & văn bản (ABS (var%), 0 0%), phạm lỗi)
Bước 12: Nhập các nhãn thông minh vào biểu đồ
Bây giờ chúng ta đã có những nhãn hiệu tuyệt đẹp, hãy thay thế những cái cũ bằng những cái này.
Chọn dòng đầu tiên (Dự chi) và nhấn CTRL + 1 để đi đến các tùy chọn định dạng.Nhấp vào “Value from cells” và chỉ vào cột Var 1.Lặp lại quá trình như trên cho nhãn của dòng thứ hai (thực tế) quá.Xem thêm:
Kết quả:
Bước 13: Điều chỉnh vị trí nhãn
Nhấp vào nhãn và chọn vị trí là “Above”.