Trong hướng dẫn này, chúng ta đang học cách tạo một biểu đồ phương sai tuyệt vời trong Excel biểu thị rõ ràng các số liệu thực tế và mục tiêu [ngân sách] bằng cách sử dụng các thanh và phương sai [thuận lợi và không thuận lợi] bằng các mũi tên đẹp đến mức bạn có được toàn bộ câu chuyện mà không cần nhìn hai lần
- Mỗi yếu tố trong biểu đồ có vị trí riêng và không cản trở những yếu tố khác. Lựa chọn tốt nhất để vẽ dữ liệu động thay đổi thường xuyên
- Phương sai có lợi được thể hiện bằng màu xanh lá cây và phương sai không thuận lợi trong mũi tên màu đỏ với phương sai về số lượng được hiển thị ở trên
- Một chuỗi, thực tế hoặc mục tiêu, có thanh hình kẹo mút với số liên quan ở bên trái
- Số liệu của loạt thứ hai được vẽ trong thanh màu vàng với các số ở cơ sở. Một lần nữa, sê-ri này có thể là sê-ri thực tế hoặc được lập ngân sách, theo kế hoạch hoặc mục tiêu
- Chỉ cần cung cấp số liệu thực tế và ngân sách và hoàn thành biểu đồ. Đơn giản
Xem nội dung
Nhận Mẫu biểu đồ phương sai Excel đã hoạt động đầy đủ
Đang vội?
Bỏ qua hướng dẫn ngay bây giờ và lấy tệp. Tệp Excel không có bí mật được mở khóa hoàn toàn này có sẵn để bạn thay đổi và sử dụng theo ý của bạn. Nó được thiết kế để nhập dữ liệu dễ dàng. Bạn chỉ cần cung cấp dữ liệu thực tế và mục tiêu và phần còn lại sẽ được thực hiện tự động. Và mọi sửa đổi trong tương lai đối với mẫu này sẽ được gửi cho bạn hoàn toàn miễn phí
Mua ngayCó một câu hỏi?
Biểu đồ phương sai là gì?
Hầu hết các biểu đồ phương sai thời gian so sánh hai bộ chuỗi với nhau để tính toán sự khác biệt giữa mỗi lần lặp lại. Ví dụ: chúng tôi muốn so sánh doanh số bán hàng của hai công ty trong mười năm qua. So sánh doanh số bán hàng của công ty này với công ty kia hàng năm cho chúng ta sự chênh lệch hoặc khác biệt. Vẽ đồ thị các phương sai này cho chúng ta các biểu đồ phương sai
Có một số cách để tạo biểu đồ phương sai Excel từ những biểu đồ rất đơn giản như cách tôi đã thảo luận hoặc một biểu đồ phức tạp với một số kỹ thuật Excel được áp dụng để tối ưu hóa như thế này
Quan trọng. Hiểu sự khác biệt giữa Thực tế và Mục tiêu
Như đã đề cập trước đó, phương sai chỉ đơn giản là sự khác biệt giữa các con số thực tế và mục tiêu hoặc ngân sách/kế hoạch
Hiểu rõ một điều, thực tế lớn hơn mục tiêu không phải lúc nào cũng thuận lợi. Nó phụ thuộc vào bản chất của dữ liệu và phương sai là gì
Trong trường hợp chi phí, thực tế thấp hơn mục tiêu là một điều tốt. Nhưng nếu dữ liệu là về doanh số thì việc có số liệu thực tế lớn hơn mục tiêu là điều thuận lợi
Với mục đích của hướng dẫn này, tôi đang thực hiện phân tích phương sai cho chi phí thực tế và ngân sách. Do đó, nếu thực tế thấp hơn mục tiêu. Và nếu một mục tiêu lớn hơn thực tế thì nó không thuận lợi
Biểu đồ phương sai Excel nâng cao - Từng bước
Tải xuống sổ làm việc Excel này với dữ liệu mẫu để làm theo các bước chi tiết trong hướng dẫn này
Có rất nhiều điều đang diễn ra trong hướng dẫn này. Vì vậy, tôi đã chia toàn bộ thành các bit có liên quan để dễ hiểu
Chuẩn bị dữ liệu cho các thanh phương sai
Vì chúng ta đang lập kế hoạch vẽ biểu đồ phương sai có lợi bằng màu xanh lá cây và màu đỏ không thuận lợi, nên chúng ta cần tách từng loại ra khỏi một tập hợp phương sai duy nhất
Bước 1. Trong ô D1, đặt tiêu đề V. Màu xanh lá cây và trong ô, D2 đặt công thức sau và nhấp đúp vào tay cầm kéo để điền xuống các ô
=IF[B2>C2,B2-C2,””]
=IF[B2 Mục tiêu và trong ô F2, đặt công thức sau và nhấp đúp vào núm điều khiển điền để điền xuống
=B2 Mục tiêu bằng cách sử dụng phím CTRL + chuột. Sau khi chọn nhấn ALT+F1 để chèn biểu đồ cột
Bước 2. Kích hoạt biểu đồ > chuyển đến tab thiết kế bên dưới công cụ biểu đồ > nhấp vào thay đổi loại biểu đồ
Bước 3. Nhấp vào Combo từ danh sách bên trái và thực hiện các thay đổi sau
Mục tiêu. Biểu đồ > Cột nhóm, Trục phụ > đã chọn
Thật sự. Biểu đồ > Cột nhóm, Trục phụ > đã chọn
người giữ. Biểu đồ > Cột xếp chồng, Trục phụ > bỏ chọn
V. Màu xanh lá. Biểu đồ > Cột xếp chồng, Trục phụ > bỏ chọn
V. Màu đỏ. Biểu đồ > Cột xếp chồng, Trục phụ > bỏ chọn
Bước 5. Nhấp vào Chuỗi thực tế trong biểu đồ > chuyển đến tab định dạng bên dưới công cụ biểu đồ > thay đổi hình dạng tô thành không tô. Điều này sẽ ẩn chuỗi thực tế
Bước 7. Nhấp chuột phải vào thanh lỗi > nhấp vào định dạng thanh lỗi. Từ thanh bên phải > dưới hướng nhấp trừ. Trong kiểu kết thúc > chọn không có giới hạn. Trong phần căn chỉnh lỗi, hãy nhấp vào phần trăm > nhập 100 vào hộp nhập và nhấn phím Enter
Bước 8. Các thanh lỗi vẫn được chọn > nhấp vào biểu tượng điền và dòng ở thanh bên phải. Thay đổi chiều rộng thành 2. 75 điểm. Thay đổi loại mũi tên bắt đầu thành mũi tên hình bầu dục
Bước 9. Chọn chuỗi mục tiêu và từ tab định dạng, thay đổi hình dạng tô thành không tô
Nhận các mũi tên trong biểu đồ phương sai Excel
Bước 1. bấm V. Chuỗi màu đỏ trong biểu đồ để chọn nó > chuyển đến tab thiết kế > nhấp vào thêm thành phần biểu đồ > di chuột qua các thanh lỗi và chọn lỗi tiêu chuẩn
Bước 2. Nhấp chuột phải vào thanh lỗi vừa chèn > nhấp vào định dạng thanh lỗi. Từ thanh trên, nhấp chuột phải vào biểu tượng tùy chọn thanh lỗi ở trên cùng và thực hiện các thay đổi sau
phương hướng. bấm dấu trừ
phong cách kết thúc. nhấp vào không có nắp
số lượng lỗi. nhấp vào tỷ lệ phần trăm và trong hộp nhập, gõ 100, sau đó nhấn phím Enter
Bước 3. Có các thanh lỗi vẫn được chọn, nhấp vào tùy chọn điền và dòng. Thay đổi màu thành màu đỏ hoặc bất cứ thứ gì bạn cho là phù hợp với phương sai bất lợi. Tăng chiều rộng lên 3pt. Thay đổi loại mũi tên bắt đầu thành mũi tên
Bước 5. Nhấp chuột phải vào để chọn thanh lỗi vừa chèn > chọn định dạng thanh lỗi và thực hiện các thay đổi sau trong thanh bên phải
phương hướng. bấm dấu trừ
phong cách kết thúc. nhấp vào không có nắp
số lượng lỗi nhấp vào tỷ lệ phần trăm và trong hộp nhập, nhập 100 và nhấn Enter
Bước 6. Nhấp vào điền và dòng ở thanh bên phải với các thanh lỗi vẫn chọn và thực hiện các thay đổi sau
Màu. màu xanh lá cây hoặc bất cứ thứ gì bạn cho là phù hợp với mũi tên phương sai thuận lợi
Bề rộng. 3 điểm
Kết thúc loại mũi tên thành mũi tên
Bước 7. Bấm vào chữ V. Chuỗi màu xanh lá cây một lần nữa để chọn nó > chuyển đến tab định dạng > nhấp vào hình dạng điền > nhấp vào không tô
Định cấu hình các thành phần biểu đồ
Bước này là tùy chọn và bạn có thể bỏ qua nếu biểu đồ đã theo cách bạn muốn
Của tôi có một vài điều kỳ quặc như sai lệch thanh mục tiêu, thanh thực tế quá mỏng so với ý thích của tôi, v.v.
Để kẹo mút mục tiêu được căn chỉnh ngay trên cạnh của thanh thực tế, chỉ cần nhấp chuột phải một chút vào bên trái của kẹo mút mục tiêu trong khu vực dường như trống để chọn chuỗi mục tiêu > chọn chuỗi dữ liệu định dạng. Thay đổi như sau
Sê-ri chồng chéo. -20%
Chiều rộng khoảng cách. 0%
Từ thanh trên, nhấp chuột phải vào menu thả xuống với tiêu đề tùy chọn sê-ri và chọn sê-ri giá đỡ và thực hiện các thay đổi sau
Sê-ri chồng chéo. 100%
Chiều rộng khoảng cách. 100%
Quản lý nhãn dữ liệu cho biểu đồ phương sai Excel
Đến bây giờ, chúng tôi đã hoàn thành và linh hoạt biểu đồ phương sai không chỉ trông tuyệt vời mà còn đáp ứng mục đích chính xác đến mức bạn có thể có ý tưởng khi nhìn vào nó chỉ một lần
Tuy nhiên, một cái gì đó thực sự quan trọng đang thiếu. CON SỐ
Mặc dù chúng ta có thể biết được mỗi chuỗi có thể lên tới bao nhiêu nhưng biểu đồ của chúng ta có thể hiệu quả hơn với các con số cho mỗi chuỗi
Đây là nơi nhãn dữ liệu biểu đồ được sử dụng. Nó trông giống như một điều đơn giản vì người ta có thể chèn nó qua các thành phần biểu đồ trong tab công cụ biểu đồ nhưng điều này không hoạt động trong biểu đồ này
Vì vậy, chúng tôi cần phải giải quyết xung quanh và đây là nơi bạn sẽ học cách vượt trội với các nhãn Excel để tạo các biểu đồ tuyệt vời 🙂
Tùy chỉnh nhãn biểu đồ Excel – Dán nhãn theo cách bạn muốn
Nhãn cho thanh sê-ri thực tếThanh thực tế của chúng tôi thực sự là một cột xếp chồng lên nhau. Vấn đề với cột xếp chồng lên nhau là nó chỉ hiển thị các nhãn bên trong cột không đủ rõ ràng theo sở thích của tôi. có một cái nhìn
Tốt nhất, nó nên ở phía trên cột. Đối với điều này, chúng tôi sẽ phải thêm chuỗi giả được vẽ dưới dạng biểu đồ đường chỉ cho mục đích này. Nhưng trước khi chúng tôi có thể làm điều đó, chúng tôi cần dữ liệu cho nó
Bước 1. Chuyển đến ô H1 và đặt tiêu đề là Dum. Chân. Trong ô H2, đặt công thức sau và nhấp đúp vào núm điều khiển điền vào để điền vào toàn bộ phạm vi
=IF[B2>C2,B2,C2]
Bước 2. Chọn cột vừa thêm bao gồm dữ liệu và tiêu đề rồi nhấn CTRL+C. Kích hoạt biểu đồ bằng cách nhấp một lần vào bất kỳ đâu trong biểu đồ và nhấp chuột phải vào bất kỳ góc nào của biểu đồ > chọn biểu tượng dán
Bước 3. Bấm vào chuỗi mới thêm > chuyển sang tab thiết kế > bấm thay đổi loại biểu đồ > kéo xuống tìm Dum. Chân. chuỗi trong danh sách và thay đổi loại biểu đồ thành đường > nhấp vào OK
Bước 4. Nhấp vào dòng trong biểu đồ > chuyển đến tab thiết kế > nhấp vào thêm thành phần biểu đồ > di chuột qua nhãn dữ liệu > nhấp vào bên trên. Thao tác này sẽ thêm các nhãn được tìm nạp từ chuỗi
Bước 5. Nhấp lại vào dòng nếu nó chưa hoạt động > chuyển đến tab định dạng > nhấp vào đường viền hình dạng > nhấp vào không có đường viền
=IFERROR[IF[D2="”,-E2,D2],””]
Bước 7. Trên biểu đồ, nhấp chuột phải vào nhãn thanh thực tế > nhấp vào định dạng nhãn dữ liệu và kiểm tra giá trị từ các ô và chọn phạm vi dữ liệu I2. I10 mà chúng tôi vừa tạo. Nhấp vào OK
Bước 8. Bỏ chọn các hộp khác với giá trị từ các ô
Chúng ta có thể dễ dàng thêm nhãn vào các thanh lỗi trong Excel. Và ngay cả đối với chuỗi mục tiêu trong biểu đồ của chúng tôi, nó có thể như thế này
Tuy nhiên, tôi không thích mức độ đóng của các chuỗi mục tiêu và mũi tên phương sai. Nếu chúng ta có thể quản lý các nhãn cho chuỗi mục tiêu ở trên cùng bên trái, điều đó sẽ làm cho mỗi nhãn kín đáo với từng chuỗi và dễ liên quan. Đối với điều này, chúng ta cần sự trợ giúp của biểu đồ phân tán với biểu đồ đường thẳng
Bước 1. Chuyển đến ô J1 và đặt tiêu đề theo trục X và điền vào cột bằng 1,2,3…, cho đến 9
Bước 2. Chọn phạm vi dữ liệu chuỗi mục tiêu, bao gồm tiêu đề, nhấn CTRL+C để sao chép > nhấp chuột phải vào biểu đồ > nhấp vào biểu tượng dán để chèn một chuỗi mới vào biểu đồ. Bạn có thể không xem được nhưng đừng lo lắng
Bước 3. Kích hoạt biểu đồ, chuyển đến tab thiết kế > nhấp vào thay đổi loại biểu đồ > cuộn xuống để xác định chuỗi mục tiêu ở cuối danh sách > thay đổi loại biểu đồ thành phân tán theo đường thẳng và nhấp vào OK
Bước 5. Hộp chỉnh sửa chuỗi sẽ bật lên > xóa mọi thứ trong thanh nhập giá trị chuỗi X > chọn dữ liệu của cột trục x ngoại trừ tiêu đề. Nhấn OK và OK
Bước 6. Bây giờ, chúng tôi muốn căn chỉnh chính xác điểm bắt đầu của dòng ở tâm của đầu tròn của cây kẹo mút đầu tiên. Trong kịch bản hiện tại, 0. 75 đưa ra sự liên kết thích hợp. Bây giờ chỉ cần thêm 1 vào mỗi ô tiếp theo i. e. 0. 75, 1. 75, 2. 75, 3. 75,…,8. 75. Điều này sẽ căn chỉnh dòng với phần đầu của chuỗi mục tiêu
Bước 8. Vẫn chọn dòng, chuyển đến tab định dạng > nhấp vào đường viền hình dạng > nhấp vào không có đường viền để ẩn dòng
Bước 1. Nhấp chuột phải vào biểu đồ > nhấp vào định dạng khu vực biểu đồ. Từ thanh bên phải > nhấp vào mũi tên thả xuống và chọn Chuỗi giá đỡ > chuyển đến tab thiết kế > nhấp vào thêm thành phần biểu đồ > di chuột qua nhãn dữ liệu > nhấp vào bên trong cơ sở
Bước 3. Nhấp chuột trái một lần vào nhãn dữ liệu sê-ri thực tế > chuyển đến định dạng > nhấp vào màu tô hình dạng > nhấp vào các màu tô khác. Từ bảng màu, chọn màu trắng và điều chỉnh độ trong suốt thành 50%
Loại bỏ các mục biểu đồ không mong muốn
Chỉ cần nhấp chuột trái một lần vào các mục sau và nhấn phím xóa trên bàn phím
- Tiêu đề biểu đồ
- Trục dọc phải và trái
- đường lưới chính
- truyền thuyết
Và đây là tất cả sự tuyệt vời của nó uốn cong khi cập nhật dữ liệu động
Tải xuống Mẫu biểu đồ phương sai Excel đã hoạt động đầy đủ
Cần thuyết phục hơn? . có một cái nhìn
Có một câu hỏi?
Bài viết trước Biểu đồ phương sai thực tế so với mục tiêu trong Excel với các thanh nổi
Bài viết tiếp theo Tạo biểu đồ chấm ngang hoặc biểu đồ quả tạ trong Excel – Cách thực hiện
Hasaan Fazal
Giảng dạy các môn học kinh doanh chuyên nghiệp cho học viên FIA, ACCA, CIMA, CA, v.v. Ông cũng thành lập ACCA LIVE, cổng thông tin đầu tiên của Pakistan cung cấp các lớp học trực tuyến và giải pháp đào tạo từ xa cho sinh viên FIA/ACCA. Tại PakAccountants. com anh ấy đang bận làm tài liệu học tập cho các trình độ khác nhau. Bên cạnh việc viết bài, anh ấy trả lời các câu hỏi được hỏi bằng ASK TUTOR