Giả sử bạn cần sử dụng Excel để đối chiếu hiệu suất thực tế của bản thân mình với KPIs đề ra đầu tháng, các bạn sẽ sử dụng biểu vật nào? nếu khách hàng còn chưa chắc chắn câu trả lời thì cùng Gitiho mày mò biểu đồ so sánh trong Excel dạng cột lồng nhau trong nội dung bài viết hôm ni nhé.

Bạn đang xem: Vẽ biểu đồ so sánh trong excel


*
EXG01: tuyệt đỉnh Excel - đổi thay bậc thầy Excel
*
Nhập môn Excel thuộc Gitiho
*
EXG05 - năng lực sử dụng phương pháp và hàm A-Z trong Excel

Đặc điểm report thể hiện tại qua biểu đồ đối chiếu trong Excel

Mỗi biểu trang bị được chọn lựa 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 tiến hành KPIs bọn họ đang cần sẵn sàng có những điểm lưu ý gì? Thông thường, dạng report này có kết cấu 3 phần:

Chỉ tiêu,đối tượng: chúng ta cần nhận xét tình hình chođối tượng cụ thể nào?
Số kế hoạch: bọn họ có từng nào kế hoạch KPIs? Số liệu này thông thường có sẵn từkhi lập mưu hoạch
Số thực tế: họ đang tiến hành kế hoạch mang lại đâu? Số liệu này cần phải tính toán, tổng hợp lại từ bỏ bảng tài liệu theo dõi đưa ra tiết.

Để giúp các bạn hiểu rõ hơn, chúng ta cùng đi kiếm hiểu một lấy ví dụ về bảng tài liệu và planer dưới đây:

*

Trong bảng tài liệu phát sinh là chi tiết các lần bán sản phẩm theo ngày. Bảng report trên Excel bên buộc phải là khu vực ta sẽ triển khai lập biểu đồ đối chiếu trong Excel để so sánh tiến độ thao tác với KPIs.

Việc đầu tiên chúng ta cần làm cho là giám sát và đo lường tổng từng loại thành phầm đã buôn bán ra. Kết hợp với các con số KPIs, bọn họ mới hoàn toàn có thể vẽ được biểu thiết bị so sánh vào 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 người dùng tính tổng tương xứng với từng một số loại sản phẩm, nên họ sử dụng hàm SUMIF.

Cách đo lường và tính toán như sau:

Tại bảng báo cáo trên Excel mặt phải, họ nhập hàm SUMIF vào ô G3:

=SUMIF($A$3:$A$23,E3,$C$3:$C$23)

Sau đó bọn họ nhấn tổ hợp phím Ctrl + D nhằm sử dụng chức năng Filldown trong Excel, điền công thức cho các ô còn lại trong cột.

Kết trái thu được như sau:

*

Cách vẽ biểu đồ đối chiếu trong Excel dạng cột lồng nhau

Chèn biểu đồ đối chiếu trong Excel với nhập dữ liệu report vào biểu đồ

Bước trước tiên để vẽ biểu đồ so sánh trong Excel là chèn biểu đồ. Sau đó, bọn họ sẽ nhập dữ liệu từ report trên Excel vừa giám sát ở 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 thứ cột > biểu trang bị 2D-Column > Clustered Column.

*

Biểu đồ đối chiếu trong Excel lúc này đang là một trong trang trắng vì Excel chưa xác định dữ liệu buộc phải biểu thị. Vì đó, 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 phương pháp vào tab Chart Tools > kiến thiết > 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ừ report trên Excel như sau

*

Tại mục Legend Entries:

Ta nhấn add và thêm tài liệu vào các phần như sau:

Phần KPIs:Series name làô F2 (tên title cột KPIs), Series values là vùng tài 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 tài liệu của báo cáo cho cộtthực hiệntừ G3:G12.
*

Tương tự, trên mục Horizontal (category) Axis Labels, bọn họ nhấn Edit rồi thêm dữ liệu cột "Sản phẩm" từ report trên Excel. Sau khi đã hoàn chỉnh nhập dữ liệu, biểu đồ đối chiếu trong Excel của chúng ta như sau:

*

Thay đổi bối cảnh biểu đồ so sánh trên Excel

Chúng ta đã tạo ra biểu đồ đối chiếu trong Excel dạng cột. Tuy nhiên, thay bởi vì để cột chiến lược (cột xanh) với cột thực tiễn (cột cam) tức tốc nhau, họ muốn 2 cột này lồng vào nhau. Vậy họ cần làm gì?

Đầu tiên, bọn họ ấn vào 1 cột bất cứ trong biểu đồ, thừa nhận chuột yêu cầu chọn Change Series Chart Type…

*

Cửa sổ Change Chart Type xuất hiện. Chúng ta đánh vệt mục Secondary Axis Label mang đến nhóm cột Thực hiện. Lưu ý chúng ta cần giữ Chart Type của biểu vật dụng là Clustered Column.

*

Như vậy, chúng ta đã lồng những nhóm cột vào nhau. Bên trên biểu đồ đối chiếu trong Excel sẽ lộ diện thêm một trục tung bên phải. Ở đây họ nhận thấy tất cả một vân đề: thông số 2 trục tung không giống nhau. Như vậy, dữ liệu report 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 bắt buộc đưa 2 trục về thước đo như là nhau. Chúng ta 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 đề nghị (trục nhỏ dại hơn) với nhấn chuột bắt buộc chọn Format Axis.

*

Cửa sổ Format Axis xuất hiện bên buộc phải màn hình. Bọn chúng ta đổi khác các số đo của trục tọa độ này làm thế nào để 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ừ report trên Excel đã có được thể hiện cụ thể hơn trong biểu đồ. Để biểu hiện sự khác biệt giữa cột KPIs và cột Thực tế, họ thực hiện thêm 1 bước kiểm soát và điều chỉnh phần thân biểu đồ nhằm cột Thực tế nhỏ hơn cột KPIs. Họ làm như sau:

Chọn cột KPIs (cột xanh) > Format
Data Series.Cửa sổ Format Data Series xuất hiện. Trên mục Gap
Width tùy chỉnh cấu hình chiều rộng cột ở mức khoảng 40% (Số càng nhỏ tuổi thì thân biểuđồ càng rộng ra).
*

Như vậy chúng ta thấy phần cột KPIs sẽ nằm bao quanh bên kế bên phần cột Thực tế.

Những thành phầm có phần cột thực tế vượt ra phía bên ngoài phạm vi cột KPIs có nghĩa là Vượt KPIs.Những sản phẩm có phần cột thực tiễn 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 sản phẩm phát sinh, họ đã khám phá cách lập biểu đồ vật so sánh vào Excel để sở hữu cái nhìn ví dụ về tiến độ, công suất công việc.Để học tập thêm các cách lập biểu đồ phân tích dữ liệu kết quả trong Excel, các bạn hãy đón gọi các bài viết trên blog tin học văn phòng công sở Gitiho và đăng kí khóa học:

Hướng dẫn dưới đây sẽ chỉ cho bạn cách sản xuất một biểu trang bị trong Excel nhằm mục tiêu so sánh ngân sách chi tiêu dự đưa ra so cùng với số liệu thực tiễn trong Excel.

Bước 1: Lấy tài liệu

Thiết lập tài liệu của bạn. Hãy nói rằng các bạn có dự chi ngân sách chi tiêu và giá trị thực tiễn 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. đo lường và thống kê phương sai với % phương không đúng bằng những công thức đơn giản như bên dưới đây.

*

Bước 2: tạo nên biểu thiết bị 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 bí quyết vẽ các loại biểu vật dụng cơ bản trong Excel để xem lại phương pháp tạo biểu thứ cột. Sau đó, các 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ứ

Thêm dự chi và giá chỉ trị thực tế vào biểu đồ dùng một lần nữa. Hiện thời chúng ta bao gồm mỗi hạng mục gồm 4 cột cho mỗi loại, như được hiển thị mặt dưới.

*

Bước 4: thay đổi các cột bắt đầu được thêm thành những đường

Nhấp chuột phải vào một trong hai cột new được thêm vào, chọn“Change series chart type” và chuyển đổi cả nhì cột thành những đường.

Bước này đang được thực hiện bằng những cách không giống nhau trong những phiên bạn dạng Excel cũ hơn, và bạn phải làm cho điều đó cho từng cột. Trong Excel 2013 trở lên, bạn sẽ chuyển đến màn hình “Combination chart” để vẽ biểu đồ phối kết hợp và chúng ta cũng có thể điều chỉnh loại dữ liệu cho toàn bộ 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 những hai cái và sử dụng hình tượng + để chèn thanh lên / xuống. Trong số phiên bạn dạng trước của Excel, bạn cần sử dụng Insert ribbon hoặc menu để gia công tương tự.

*

Bước 6: Định dạng xuống thanh cùng cột

Nhanh giường điều chỉnh màu sắc của từng thanh (không va vào những đường).

*

cách 7: Điều chỉnh độ rộng khoảng cách và những chuỗi giá chỉ trị ông chồng lên nhau

Thực hiện các bước dưới đây:

Chọn những cột đầu tiên. Chuyển đến loạt format (Ctrl + 1).Điều chỉnh Series overlap lên 0%.Đặt Gap width cho 150%.Bây tiếng chọn những dòng.Điều chỉnh Gap width mang lại 300%Hãy thoải mái điều chỉnh/thử nghiệm cùng với các phối kết hợp độ rộng khoảng chừng cách khác biệt để xem thay đổi nào vừa mắt các bạn nhất.Kết trái của bạn sẽ như nuốm này:

*

cách 8: Làm cho các đường trở nên vô hình dung

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 title vào biểu đồ của công ty và xóa những mục ghi chú không cần thiết

Nhấp đúp loài chuột vào title biểu đồ và nhập một cái nào đó có ý nghĩa. Ngoại trừ ra, chúng ta có thể liên kết nó với một quý giá ô. Để làm điều đó, lựa chọn tiêu đề, dấn = với trỏ đến một ô tất cả tiêu đề bạn muốn sử dụng.

Để xóa các mục chú thích, nhấp vào ghi chú biểu đồ, bây chừ bấm lại vào chuỗi 3, dấn phím DEL. Lặp lại tiến trình cho loạt 4.

*

Bước 10: Thêm nhãn dữ liệu cho cả hai mặt đường

Chọn từng loại một (nhớ rằng, những dòng là vô hình, vì chưng vậy chỉ việc nhấp vào địa điểm chúng được cho là hoặc áp dụng hộp định dạng để chọn chúng). Hiện giờ sử dụng nút + nhằm thêm nhãn dữ liệu. Trong những phiên bạn dạng cũ rộng của Excel, bạn cần sử dụng ribbon hoặc menu nhằm thêm nhãn. Ở tiến độ này, biểu trang bị của bạn sẽ trông như thế này:

*

Bước 11: Tính nhãn bắt đầu

Đây là phần thú vị. Bước đầ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ụ, chúng ta cũng có thể muốn hiển thị,

Ngón tay mẫu hướng xuống nếu như phương sai bên dưới -5%Ngón tay đan chéo cánh nếu phương sai nằm trong khoảng từ -5% đến 0%Biểu tượng OK ví như phương sai dương cùng dưới 10%Đồng ý nếu như nó nằm trong khoảng từ 10% đến 25%Nhân đôi ngón tay dòng lên ví như hơn 25%

Tạo một phạm vi nơi biểu tượng % đã đi cho 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 nhằm chèn hình tượng cảm xúc thú vị.

*

Bảng giải thích hình tượng của các bạn sẽ trông như sau:

*

Lưu ý giá trị đầu tiên. Điều đó gồm nghĩa là shop chúng tôi sẽ hiển thị ngón tay mẫu xuống cho toàn bộ các cực hiếm từ -5% mang lại -100%.

Bây giờ, hãy tính toán các nhãn. Gồm hai cỗ nhãn. Tích cực và tiêu cực. Điều này cho phép bạn kiểm soát và điều hành tốt hơn về định hình chúng. Khu vực dữ liệu thô của bọn chúng tôi bây giờ trông như thế này:

*

Công thức mang đến 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 dạng (ABS (var%), 0 0%), phạm lỗi)

Bước 12: Nhập các nhãn lý tưởng vào biểu đồ vật

Bây giờ họ đã tất cả những thương hiệu tuyệt đẹp, hãy sửa chữa thay thế những dòng cũ bằng các cái này.

Chọn dòng trước tiên (Dự chi) với 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 quy trình như trên mang lại nhãn của cái thứ nhị (thực tế) quá.

Xem thêm:

*

Kết quả:

*

bước 13: Điều chỉnh địa điểm nhãn

Nhấp vào nhãn và lựa chọn vị trí là “Above”.