Bỏ qua nội dung chính

Tạo biểu đồ Ngân sách so với Phương sai thực tế trong Excel

Tác giả: Tiểu Dương Sửa đổi lần cuối: 2020-12-17

Trong Excel, biểu đồ Ngân sách so với Phương sai thực tế được sử dụng để so sánh hai tập hợp dữ liệu chuỗi và hiển thị sự khác biệt hoặc phương sai của hai chuỗi dữ liệu. Nếu sự khác biệt là giá trị âm, một thanh hoặc cột màu được hiển thị, nếu giá trị dương, thanh hoặc cột có màu khác được hiển thị như hình minh họa bên dưới. Bài viết này, tôi sẽ nói về cách tạo biểu đồ phương sai so sánh và biểu đồ phương sai so sánh nhỏ trong ô trong sổ làm việc Excel.


Tạo ngân sách so với biểu đồ phương sai thực tế trong Excel

Để tạo biểu đồ ngân sách so với phương sai thực tế, vui lòng thực hiện theo các bước sau:

1. Nhập công thức bên dưới vào ô D2 để tính toán sự khác biệt giữa ngân sách và giá trị thực, sau đó kéo chốt điền xuống các ô bạn cần, xem ảnh chụp màn hình:

=C2-B2

2. Sau đó, chọn dữ liệu trong cột A và cột D, rồi bấm Chèn > Chèn biểu đồ cột hoặc thanh > Cột được nhóm, xem ảnh chụp màn hình:

3. Và một biểu đồ cột được chèn vào, sau đó bạn có thể xóa các phần tử không mong muốn của biểu đồ khi cần, chẳng hạn như xóa tiêu đề biểu đồ, trục tung, đường lưới và bạn sẽ nhận được kết quả như ảnh chụp màn hình dưới đây:

4. Sau đó, nhấp chuột phải vào trục hoành và chọn Định dạng trục từ menu ngữ cảnh, xem ảnh chụp màn hình:

5. Trong mở Định dạng trục ngăn, dưới Tùy chọn trục tab, nhấp vào Nhãn để mở rộng phần này, rồi chọn Thấp từ Vị trí nhãn danh sách thả xuống và trục hoành sẽ được đặt ở cuối biểu đồ, xem ảnh chụp màn hình:

6. Và sau đó, nhấp chuột phải vào bất kỳ thanh nào trong số các thanh và chọn Định dạng chuỗi dữ liệu tùy chọn, xem ảnh chụp màn hình:

7. Trong mở Định dạng chuỗi dữ liệu ngăn, dưới Điền vào dòng tab, từ Điền , vui lòng thực hiện các thao tác sau:

  • Chọn Điền đặc Tùy chọn;
  • kiểm tra Đảo ngược nếu âm hộp kiểm tra;
  • Chỉ định hai màu cho các giá trị âm và dương riêng biệt với Màu thả xuống.

8. Sau khi thiết lập màu sắc cho các thanh, sau đó, bạn nên thêm các nhãn dữ liệu, vui lòng nhấp để chọn các thanh trong biểu đồ, sau đó nhấp vào Thành phần biểu đồ biểu tượng, sau đó kiểm tra Nhãn dữ liệu từ Thành phần biểu đồ hộp danh sách, xem ảnh chụp màn hình:

9. Các thanh cột quá hẹp trông không đẹp mắt, trong trường hợp này, bạn có thể điều chỉnh độ rộng của thanh theo nhu cầu của mình, vui lòng nhấp chuột phải vào bất kỳ thanh nào trong số các thanh, sau đó chọn Định dạng chuỗi dữ liệu, Trong Định dạng chuỗi dữ liệu ngăn, dưới Tùy chọn chuỗi tab, thay đổi Chiều rộng khoảng cách theo nhu cầu của bạn, trường hợp này, tôi sẽ thay đổi nó thành 80%, xem ảnh chụp màn hình:

Lời khuyên:

Sau khi tạo biểu đồ, đôi khi, bạn có thể muốn hiển thị các nhãn dữ liệu với các màu khác nhau, ví dụ: nhãn dữ liệu dương có màu xanh lục và nhãn dữ liệu âm có màu cam, vui lòng thực hiện như sau:

Chọn dữ liệu trong cột Phương sai từ bảng dữ liệu ban đầu, sau đó nhấn Ctrl + 1 để mở Format Cells hộp thoại, trong Format Cells hộp thoại, nhấn Tuỳ chỉnh từ Phân loại hộp danh sách, sau đó nhập mã bên dưới vào Kiểu hộp văn bản:

[Color10]General;[Color53] -General

Sau đó nhấn vào OK và bạn sẽ nhận được kết quả như bạn cần, xem ảnh chụp màn hình:

Chú thích: Nếu bạn cần các màu khác, vui lòng xem bảng chỉ số màu sau:


Tạo ngân sách nhỏ so với biểu đồ phương sai thực tế trong các ô

Nếu bạn muốn chèn một ngân sách nhỏ so với biểu đồ phương sai thực tế trong các ô, các bước sau có thể giúp bạn:

1. Nhập công thức bên dưới vào ô D2 để tính toán sự khác biệt giữa ngân sách và giá trị thực, sau đó kéo chốt điền xuống các ô bạn cần, xem ảnh chụp màn hình:

=C2-B2

2. Sau đó, hiển thị các giá trị âm của phương sai, vui lòng áp dụng công thức sau vào ô E2, rồi sao chép công thức sang các ô khác mà bạn cần, xem ảnh chụp màn hình:

=IF(D2<0,D2,"")

3. Hiển thị các giá trị dương của phương sai, vui lòng nhập công thức bên dưới vào ô F2, sau đó sao chép công thức sang các ô khác mà bạn cần, xem ảnh chụp màn hình:

=IF(D2>0,D2,"")

4. Và sau đó, bạn nên chèn một biểu tượng cụ thể sẽ được sử dụng làm thanh biểu đồ, vui lòng nhấp vào Chèn >, và trong hộp thoại, chọn Các yếu tố khối mà bạn có thể tìm thấy trong hầu hết mọi phông chữ từ Tập hợp con danh sách thả xuống và nhấp vào Toàn khối biểu tượng, sau đó nhấp vào Chèn để chèn biểu tượng, hãy xem ảnh chụp màn hình:

5. Sau khi chèn biểu tượng đặc biệt, sau đó, áp dụng công thức dưới đây vào ô G2 và kéo công thức này sang các ô khác, và bạn sẽ nhận được kết quả dưới đây:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
Chú thích: Trong công thức trên, E2 là ô chứa giá trị phương sai âm, A16 là ô chứa ký hiệu cụ thể mà bạn được chèn và số 100 là một dữ liệu biến được sử dụng để tính toán số ký hiệu dựa trên giá trị phương sai, bạn có thể thay đổi nó thành của riêng mình.

6. Tiếp tục áp dụng công thức dưới đây vào ô H2, sau đó kéo công thức này sang các ô khác và bạn sẽ nhận được kết quả dưới đây:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
Chú thích: Trong công thức trên, F2 là ô chứa giá trị phương sai dương, A16 là ô chứa ký hiệu cụ thể mà bạn được chèn và số 100 là một dữ liệu biến được sử dụng để tính toán số ký hiệu dựa trên giá trị phương sai, bạn có thể thay đổi nó thành của riêng mình.

7. Và sau đó, chọn các ô trong cột G, rồi bấm Trang chủ > Sắp xếp đúng để căn chỉnh dữ liệu ngay trên các ô, hãy xem ảnh chụp màn hình:

8. Cuối cùng, bạn có thể định dạng màu phông chữ cho hai cột riêng biệt khi cần và bạn sẽ nhận được kết quả như hình minh họa bên dưới:


Tạo biểu đồ phương sai ngân sách so với thực tế với một tính năng tuyệt vời

Kutools cho Excel cung cấp hàng chục loại biểu đồ đặc biệt mà Excel không có, chẳng hạn như Biểu đồ Bullet, Biểu đồ mục tiêu và thực tế, Biểu đồ mũi tên khác biệt và như thế. Với công cụ tiện dụng của nó- Biểu đồ so sánh sự khác biệt, bạn có thể tạo biểu đồ ngân sách so với phương sai thực tế trong trang tính hoặc ngân sách nhỏ so với biểu đồ phương sai thực tế trong các ô một cách nhanh chóng và dễ dàng. Nhấp để tải xuống Kutools cho Excel dùng thử miễn phí!


Tải xuống tệp mẫu Biểu đồ phương sai ngân sách so với thực tế


Video: Tạo biểu đồ ngân sách so với phương sai thực tế trong Excel


Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel - Giúp bạn nổi bật giữa đám đông

🤖 Trợ lý AI của Kutools: Cách mạng hóa việc phân tích dữ liệu dựa trên: Thực thi thông minh   |  Tạo mã  |  Tạo công thức tùy chỉnh  |  Phân tích dữ liệu và tạo biểu đồ  |  Gọi các hàm Kutools...
Các tính năng phổ biến: Tìm, đánh dấu hoặc xác định các bản sao  |  Xóa hàng trống  |  Kết hợp các cột hoặc ô mà không làm mất dữ liệu  |  Vòng không có công thức ...
Super VLookup: Nhiều tiêu chí  |  Nhiều giá trị  |  Trên nhiều trang tính  |  Tra cứu mờ...
Khuyến cáo. Danh sách thả xuống: Danh sách thả xuống dễ dàng  |  Danh sách thả xuống phụ thuộc  |  Danh sách thả xuống nhiều lựa chọn...
Trình quản lý cột: Thêm một số cột cụ thể  |  Di chuyển cột  |  Chuyển đổi trạng thái hiển thị của các cột ẩn  So sánh các cột với Chọn các ô giống nhau và khác nhau ...
Các tính năng nổi bật: Tiêu điểm lưới  |  Chế độ xem thiết kế  |  Thanh công thức lớn  |  Trình quản lý sổ làm việc & trang tính | Thư viện tài nguyên (Văn bản tự động)  |  Bảng chọn ngày  |  Kết hợp các bảng tính  |  Mã hóa/Giải mã ô  |  Gửi email theo danh sách  |  Siêu lọc  |  Bộ lọc đặc biệt (lọc in đậm/nghiêng/gạch ngang...) ...
15 bộ công cụ hàng đầu12 bản văn CÔNG CỤ (thêm văn bản, Xóa ký tự ...)  |  50 + Biểu đồ Các loại (Biểu đồ Gantt ...)  |  40+ Thực tế Công thức (Tính tuổi dựa trên ngày sinh ...)  |  19 chèn CÔNG CỤ (Chèn mã QR, Chèn ảnh từ đường dẫn ...)  |  12 Chuyển đổi CÔNG CỤ (Số thành từ, Chuyển đổi tiền tệ ...)  |  7 Hợp nhất & Tách CÔNG CỤ (Các hàng kết hợp nâng cao, Tách ô Excel ...)  |  ... và nhiều hơn nữa

Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...

Mô tả


Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)

  • Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
  • Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
  • Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
  • Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
bonjour,
Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
merci
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations