Tạo biểu đồ phương sai mũi tên trong Excel

Trong Excel, phương sai mũi tên hoặc biểu đồ khác nhau là một loại biểu đồ cột hoặc biểu đồ thanh nhóm với mũi tên lên hoặc xuống để biểu thị tỷ lệ phần trăm tăng hoặc giảm của hai tập dữ liệu.

Ví dụ: nếu bạn muốn hiển thị phương sai bán hàng so với năm trước, bạn có thể sử dụng mũi tên màu xanh lá cây và màu đỏ để hiển thị phần trăm thay đổi 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 loại biểu đồ phức tạp này trong Excel.

• Tạo phương sai mũi tên hoặc biểu đồ chênh lệch trong Excel
• Tạo phương sai mũi tên hoặc biểu đồ chênh lệch trong Excel với một tính năng tiện dụng
• Tải xuống tệp mẫu Phương sai mũi tên hoặc Biểu đồ chênh lệch
• Video: Tạo phương sai mũi tên hoặc biểu đồ chênh lệch trong Excel

Tạo phương sai mũi tên hoặc biểu đồ chênh lệch trong Excel

Để tạo biểu đồ phương sai mũi tên để so sánh phần trăm chênh lệch thay đổi của năm 2020 dựa trên năm 2019, trước tiên, bạn nên chèn một số cột trợ giúp để tính toán dữ liệu cơ bản như hình minh họa bên dưới:

Đầu tiên, tạo dữ liệu cột trợ giúp

1. Trong ô D2 bên cạnh dữ liệu gốc, hãy nhập công thức sau, sau đó kéo công thức vào ô D10, xem ảnh chụp màn hình:

2. Trong ô E2, vui lòng nhập công thức sau, sau đó sao chép công thức vào ô E10, xem ảnh chụp màn hình:

3. Tiếp tục nhập công thức dưới đây vào ô F2 và sao chép công thức vào ô F10, xem ảnh chụp màn hình:

4. Sau đó, trong ô G2, nhập công thức bên dưới và kéo nó đến ô G10 để điền công thức, xem ảnh chụp màn hình:

5. Và sau đó, vui lòng nhập công thức sau vào ô H2 và kéo công thức vào ô H10, xem ảnh chụp màn hình:

6. Sau đó, tiếp tục nhập công thức bên dưới vào ô I2, rồi kéo chốt điền xuống ô I10, xem ảnh chụp màn hình:

Thứ hai, tạo biểu đồ phương sai mũi tên dựa trên dữ liệu cột của trình trợ giúp

7. Sau khi tạo dữ liệu trình trợ giúp, hãy chọn dữ liệu trong cột A, B, C, D, E, sau đó 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:

8. Và một biểu đồ cột nhóm được chèn vào như ảnh chụp màn hình bên dưới:

9. Sau đó, bấm để chọn biểu đồ, rồi chọn Chọn Dữ liệu từ menu ngữ cảnh, xem ảnh chụp màn hình:

10. Trong cửa sổ bật ra Chọn nguồn dữ liệu hộp thoại, trong Mục chú thích (Chuỗi) hộp danh sách, chọn Trái tối đa chuỗi, sau đó nhấp vào nút mũi tên lên để di chuyển chuỗi dữ liệu này lên đầu, xem bản trình diễn bên dưới:

11. Sau đó nhấn vào OK để đóng hộp thoại, sau đó nhấp chuột phải vào Trái tối đa chuỗi dữ liệu, nhấp chuột Thành phần biểu đồ để mở rộng hộp danh sách, trong hộp danh sách, hãy chọn Thanh lỗi, xem ảnh chụp màn hình:

12. Tiếp tục nhấp chuột phải vào Quyền tối đa chuỗi dữ liệu, sau đó bấm Thành phần biểu đồ để mở rộng hộp danh sách, trong hộp danh sách, hãy chọn Thanh lỗi, xem ảnh chụp màn hình:

13. Và sau đó, nhấp chuột phải vào Thanh lỗi Y bên trái tối đa, và lựa chọn Định dạng thanh lỗi từ menu ngữ cảnh, xem ảnh chụp màn hình:

14. Sau đó, trong mở Định dạng thanh lỗi ngăn, dưới Tùy chọn thanh lỗi tab, trong Thanh lỗi dọc , vui lòng định cấu hình các hoạt động sau:

• Chọn Dấu trừ tùy chọn theo Chiều hướng phần;
• Sau đó, chọn Không có mũ từ Kiểu kết thúc phần;
• Và sau đó, chọn Tuỳ chỉnh từ Số tiền lỗi phần, sau đó nhấp vào Chỉ định giá trị nút, trong cửa sổ bật ra Thanh lỗi tùy chỉnh hộp thoại, giữ trống trong Giá trị lỗi dương tính và chọn dữ liệu từ cột Mũi tên Lên trong Giá trị lỗi phủ định hộp văn bản, sau đó bấm OK để đóng hộp thoại.

15. Giữ cái Định dạng thanh lỗi mở, sau đó bấm để chọn Thanh lỗi Y bên phải tối đa từ biểu đồ, dưới Tùy chọn thanh lỗi tab, trong Thanh lỗi dọc , vui lòng định cấu hình các hoạt động sau:

• Chọn Dấu trừ tùy chọn theo Chiều hướng phần;
• Sau đó, chọn Không có mũ từ Kiểu kết thúc phần;
• Và sau đó, chọn Tuỳ chỉnh từ Số tiền lỗi phần, sau đó nhấp vào Chỉ định giá trị nút, trong cửa sổ bật ra Thanh lỗi tùy chỉnh hộp thoại, giữ trống trong Giá trị lỗi dương tính hộp văn bản và chọn dữ liệu từ cột Mũi tên Xuống trong Giá trị lỗi phủ định hộp văn bản, sau đó bấm OK để đóng hộp thoại.

16. Sau đó, bạn nên định dạng thanh chuỗi dữ liệu Tối đa Trái và Tối đa Phải ẩn, nhấp chuột phải vào thanh dữ liệu Bên trái Tối đa và chọn Định dạng chuỗi dữ liệu trong tùy chọn đã mở Định dạng chuỗi dữ liệu ngăn, dưới Điền vào dòng tab, chọn Không có điền từ Điền , xem ảnh chụp màn hình:

17. Thực hiện thao tác tương tự để ẩn các thanh dữ liệu Max Right, xem ảnh chụp màn hình:

18. Sau khi ẩn thanh dữ liệu Max Left và Max Right, bây giờ, hãy nhấp chuột phải vào Thanh lỗi Y bên trái tối đa, và sau đó chọn Định dạng thanh lỗi từ menu ngữ cảnh, trong Định dạng thanh lỗi ngăn, dưới Điền vào dòng , vui lòng thực hiện các thao tác sau:

• Chọn Đường vẽ liền và sau đó chỉ định màu bạn thích cho các mũi tên lên từ Màu thả xuống;
• Sau đó, điều chỉnh độ rộng dòng thành 1.25 pt như bạn cần từ Chiều rộng phần;
• Sau đó, chọn Mũi tên biểu tượng từ Bắt đầu mũi tên gõ danh sách thả xuống.

19. Giữ cái Định dạng thanh lỗi vẫn mở, sau đó bấm để chọn Thanh lỗi Y bên phải tối đa, Trong Định dạng thanh lỗi ngăn, dưới Điền vào dòng , vui lòng thực hiện các thao tác sau:

• Chọn Đường vẽ liền và sau đó chỉ định màu bao phấn bạn thích cho các mũi tên xuống từ Màu thả xuống;
• Sau đó, điều chỉnh độ rộng dòng thành 1.25 pt như bạn cần từ Chiều rộng phần;
• Sau đó, chọn Mũi tên biểu tượng từ Mũi tên kết thúc gõ danh sách thả xuống.

20. Và bạn sẽ nhận được biểu đồ như ảnh chụp màn hình bên dưới:

21. Tiếp theo, hãy nhấp chuột phải vào thanh dữ liệu Max Left, và chọn Định dạng chuỗi dữ liệu từ menu ngữ cảnh, trong Định dạng chuỗi dữ liệu ngăn, dưới Tùy chọn chuỗi tab, chọn Trục phụ tùy chọn từ Loạt âm mưu đang bật , sau đó nhấp để chọn thanh dữ liệu Max Right, thực hiện thao tác tương tự như hình minh họa bên dưới:

22. Sau đó, nhấp chuột phải vào bất kỳ thanh nào trong biểu đồ và 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, nhập 0% và 100% vào Chồng chéo chuỗi và Chiều rộng khoảng cách hộp riêng biệt.

Và sau đó, trong Định dạng chuỗi dữ liệu ngăn, tiếp tục chọn Loạt bài “Năm 2019" hoặc là Sê-ri “Năm 2020” từ Tùy chọn chuỗi danh sách thả xuống và nhập 0% và 100% vào Chồng chéo chuỗi và Chiều rộng khoảng cách hộp riêng biệt là tốt. Xem demo dưới đây:

23. Bước này, bạn có thể thêm nhãn dữ liệu cho biểu đồ, bấm để chọn chuỗi dữ liệu Bên trái tối đa, rồi bấm Thành phần biểu đồ, trong phần mở rộng Thành phần biểu đồ hộp danh sách, kiểm tra Nhãn dữ liệu tùy chọn, xem ảnh chụp màn hình:

24. Sau đó, nhấp chuột phải vào nhãn dữ liệu và chọn Định dạng nhãn dữ liệu, xem ảnh chụp màn hình:

25. Trong mở Định dạng nhãn dữ liệu ngăn, dưới Tùy chọn nhãn tab, kiểm tra Giá trị từ các ô và trong ô bật ra Dải nhãn dữ liệu hộp thoại, chọn ô dữ liệu từ cột Nhãn Dữ liệu Lên, sau đó bấm OK nút, xem ảnh chụp màn hình:

26. Vẫn trong Định dạng nhãn dữ liệu ngăn, bỏ chọn Giá trị và Hiển thị đường dẫn và bạn sẽ nhận được biểu đồ bên dưới, xem ảnh chụp màn hình:

27. Sau đó, tiếp tục thêm các nhãn dữ liệu xuống, nhấp chuột phải vào thanh dữ liệu Bên phải Tối đa và nhấp vào Thành phần biểu đồ để mở rộng hộp danh sách, trong Thành phần biểu đồ hộp danh sách, kiểm tra Nhãn dữ liệu tùy chọn, xem ảnh chụp màn hình:

28. Sau đó, nhấp chuột phải vào các nhãn dữ liệu mới được thêm vào và chọn Định dạng nhãn dữ liệu, trong mở Định dạng nhãn dữ liệu ngăn, dưới Tùy chọn nhãn tab, kiểm tra Giá trị từ các ô và trong ô bật ra Dải nhãn dữ liệu hộp thoại, chọn các ô dữ liệu từ cột Nhãn Dữ liệu Xuống, sau đó bấm OK nút, xem ảnh chụp màn hình:

29. Vẫn còn trong Định dạng nhãn dữ liệungăn s, bỏ chọn Giá trị và Hiển thị đường dẫn và các nhãn dữ liệu thay đổi phần trăm đã được thêm vào biểu đồ, xem ảnh chụp màn hình:

30. Cuối cùng, hãy xóa các phần tử biểu đồ không cần thiết khi bạn cần, chẳng hạn như xóa đường lưới, ô biểu đồ, trục phụ và bạn sẽ nhận được biểu đồ hoàn chỉnh như hình minh họa bên dưới:

Tạo phương sai mũi tên hoặc biểu đồ chênh lệch trong Excel với một tính năng tiện dụng

Tải xuống tệp mẫu Phương sai mũi tên hoặc Biểu đồ chênh lệch

Video: Tạo phương sai mũi tên hoặc biểu đồ chênh lệch trong Excel