Cách hiển thị dữ liệu chồng chéo trong biểu đồ phân tán Excel

Trong bài viết này, bạn sẽ tìm hiểu Cách xếp chồng biểu đồ trong Excel và dễ dàng so sánh hai bộ dữ liệu trong một biểu đồ, như doanh số bán hàng thực tế so với kết quả bán hàng mục tiêu, xếp hạng thực tế so với xếp hạng mục tiêu, v.v.

Nếu bạn cố gắng vẽ các giá trị Thực tế so với Mục tiêu ở dạng Cột được nhóm trong Excel, nó sẽ trông như thế này

Mặc dù, nó sẽ hoàn thành công việc nhưng người dùng sẽ đọc chậm và khó diễn giải. So sánh quyền của từng thanh sẽ mất thời gian và có thể dễ bị lỗi

Trong khi đó, chỉ cần thay đổi định dạng của cột Cụm, chúng ta có thể chuyển đổi nó thành biểu đồ lớp phủ. Biểu đồ lớp phủ sẽ giúp bạn diễn giải dữ liệu trong nháy mắt

Lớp phủ đồ thị này có vẻ như là một bài tập khó nhưng nó là một quá trình rất dễ dàng

Tiếp tục đọc để tìm hiểu cách xếp chồng các biểu đồ trong Excel

Trong ví dụ bên dưới, chúng tôi có cả doanh số bán hàng thực tế và doanh số theo kế hoạch cho mỗi tháng và bạn cần vẽ biểu đồ đó trên biểu đồ để dễ so sánh

Xem Cách chồng hai biểu đồ trong Excel trên YouTube và tán thành

Thực hiện theo hướng dẫn từng bước về cách tạo lớp phủ biểu đồ trong Excel và tải xuống sổ làm việc Excel này để thực hành cùng

TẢI XUỐNG SỔ LÀM VIỆC EXCEL

BƯỚC 1. Chọn tất cả các ô trong bảng.

BƯỚC 2. Chuyển đến Tab Chèn > Trong Nhóm Biểu đồ, nhấp vào biểu tượng Biểu đồ Cột Cụm.

Biểu đồ cột nhóm sẽ xuất hiện bên cạnh bảng dữ liệu

BƯỚC 3. Nhấp vào Thanh giá trị kế hoạch.

BƯỚC 4. Nhấp chuột phải vào thanh và chọn Định dạng chuỗi dữ liệu.

BƯỚC 5. Trong hộp thoại Định dạng chuỗi dữ liệu, chọn Chồng chéo chuỗi là 100%.

BƯỚC 5. Trong Tab Điền, Chọn Không Điền.

BƯỚC 6. Chọn Đường liền nét làm Đường viền, Màu xanh đậm làm Màu và 2pt làm Chiều rộng.

BƯỚC 7. Nhấp chuột phải vào Thanh giá trị thực tế và chọn chuỗi dữ liệu định dạng.

BƯỚC 8. Chọn Màu đồng nhất và Xanh dương nhạt làm màu.

BƯỚC 9. Chọn Gridlines và nhấn Delete.

BƯỚC 10. Chọn Tiêu đề biểu đồ và nhấn Delete.

Biểu đồ lớp phủ của bạn hiện đã sẵn sàng

Phần kết luận

Tóm lại đây là cách chồng biểu đồ trong Excel

Trước tiên, bạn cần chỉnh sửa biểu đồ "Kế hoạch" của mình bằng cách nhấp vào chuỗi của nó và nhấn phím tắt CTRL+1. Sau đó, trong hộp Định dạng, bạn cần chọn

ĐỔ ĐẦY. KHÔNG CÓ ĐIỀN
MÀU VIỀN. Vạch RẮN và BÓNG TỐI
KIỂU VIỀN. CHIỀU RỘNG 2 điểm

Sau đó, bạn cần chỉnh sửa biểu đồ “Thực tế” của mình bằng cách nhấp vào chuỗi của nó và nhấn phím tắt CTRL+1. Sau đó, trong hộp Định dạng, bạn cần chọn

Nếu bạn sử dụng biểu đồ phân tán cho tập dữ liệu có các giá trị rời rạc trong một chiều, chẳng hạn như trục x của bạn hiển thị các ngày trong tuần, thì bạn có thể nhận được các điểm trùng nhau khi bạn vẽ biểu đồ dữ liệu

Để làm cho biểu đồ dễ diễn giải hơn, bạn có thể đưa jitter vào các điểm dữ liệu. Điều này có nghĩa là di chuyển các điểm được vẽ một chút để chúng không chồng lên nhau quá nhiều

Làm điều này giúp dễ dàng xem phân phối và tần suất của các giá trị

Tải xuống sổ làm việc mẫu

Nhập địa chỉ email của bạn bên dưới để tải xuống sổ làm việc chứa dữ liệu, biểu đồ và ví dụ được sử dụng trong bài đăng này

Nhận sổ làm việc

Bằng cách gửi địa chỉ email của bạn, bạn đồng ý rằng chúng tôi có thể gửi email cho bạn bản tin Excel của chúng tôi

Vui lòng nhập địa chỉ email hợp lệ

Dữ liệu, biểu đồ và ví dụ trong bài đăng này có thể được tải xuống trong sổ làm việc này

Tải xuống Sổ làm việc Excel. Ghi chú. Đây là một. xlsx, vui lòng đảm bảo trình duyệt của bạn không thay đổi phần mở rộng của tệp khi tải xuống

Tạo rung động

Điều đầu tiên cần nói là một trục cần phải là các giá trị số rời rạc

Nếu bạn đang vẽ các giá trị x-y trong đó x đã thay đổi rất nhiều, thì không cần sử dụng jitter

Trong trường hợp ví dụ của tôi, tôi đang xem giá trị hóa đơn nhà hàng trong bốn ngày khác nhau. Những ngày này có thể được biểu thị bằng các số khác nhau e. g. 6 tượng trưng cho Thứ Sáu, 7 là Thứ Bảy, v.v.

Rung động trong Power BI

Tôi đang sử dụng bộ dữ liệu mẹo tương tự từ bài đăng của tôi trên Jitter trong Power BI

GHI CHÚ. Excel không cung cấp một cách tích hợp để phân tán dữ liệu phân loại biểu đồ trong đó các danh mục không phải là số. Nếu bạn đang ở trong tình huống này thì bạn sẽ cần gán các giá trị số cho các danh mục của mình để dữ liệu của bạn có thể được vẽ trên biểu đồ, sau đó tạo nhãn văn bản của riêng bạn trên trục phân loại

Sự thay đổi

Chúng tôi muốn di chuyển hoặc dịch chuyển giá trị x sang trái và phải một chút. Chúng tôi không thực sự thay đổi giá trị mà chúng tôi đang đo được vẽ trên trục y

Mẫu dữ liệu

Bạn sẽ nhận thấy rằng các giá trị trục x đều là 6. Điều này tương đương với Thứ Sáu khi tôi đặt định dạng trục thành ngày và sử dụng định dạng tùy chỉnh ddd

Để bắt đầu, hãy sử dụng hàm RAND để tạo số ngẫu nhiên. Bạn sẽ nhận được giá trị lớn hơn hoặc bằng 0 và nhỏ hơn 1

Nếu bạn thêm giá trị này vào giá trị x ban đầu của mình, nó sẽ tăng lên (tất nhiên). Làm điều này cho tất cả các điểm dữ liệu của bạn sẽ dẫn đến việc dữ liệu được đẩy dọc theo trục x theo hướng tích cực

Dữ liệu mẫu được chuyển sang phải

Nhưng chúng tôi thực sự muốn dữ liệu được phân phối ở hai bên của giá trị gốc, trung tâm. Để đạt được điều này, hãy trừ 0. 5 từ giá trị do RAND tạo ra. Đối với các số ngẫu nhiên nhỏ hơn 0. 5 bạn sẽ nhận được giá trị âm. Việc thêm điểm này vào điểm dữ liệu của bạn sẽ di chuyển điểm này sang trái dọc theo trục x

Đối với các giá trị ngẫu nhiên lớn hơn 0. 5 (cũng phải nhỏ hơn 1), bạn sẽ nhận được một số lớn hơn hoặc bằng 0 và nhỏ hơn 0. 5. Thêm phần này vào điểm dữ liệu của bạn sẽ dịch chuyển nó theo hướng tích cực dọc theo trục x của bạn

Dữ liệu mẫu được dịch chuyển sang trái và phải

Bạn kết thúc với một số điểm dữ liệu được dịch chuyển sang trái và một số dịch chuyển sang phải, tạo ra một phân phối đẹp mắt xung quanh giá trị trục x ban đầu

Lây lan

Sau khi tạo ra sự dịch chuyển chẵn, chúng ta cũng có thể kiểm soát sự lan truyền của các giá trị này, nghĩa là khoảng cách di chuyển từ giá trị ban đầu

Để kiểm soát mức chênh lệch, hãy nhân giá trị thay đổi với một số. Số càng lớn thì mức chênh lệch càng rộng. Trong hình động bên dưới, với giá trị chênh lệch nhỏ bằng 0. 1 các điểm dữ liệu được nhóm gần với giá trị trung tâm của Thứ Sáu. Với giá trị chênh lệch bằng 0. 8, các điểm được trải ra nhiều hơn

Trình duyệt của bạn không hỗ trợ thẻ video

Đối với dữ liệu tôi đang sử dụng, giá trị là 0. 3 là tốt nhưng bạn sẽ cần tìm một giá trị chênh lệch phù hợp cho dữ liệu của riêng mình

Công thức giật

Chúng tôi kết thúc với công thức cuối cùng này để tính jitter

= x + ((RAND() - 0.5) * 0.3)

Trong thực tế, chúng ta có thể sử dụng một công thức như thế này và sao chép xuống cho mọi điểm dữ liệu

=A1+((RAND()-0.5)*$E$2)

trong đó A1 là giá trị của chúng tôi trên trục x và $E$2 chứa giá trị kiểm soát mức độ lan truyền của dữ liệu. Nếu bạn tham chiếu đến một ô cho giá trị chênh lệch, bạn có thể thay đổi điều này một cách nhanh chóng

GHI CHÚ. RAND không ổn định nên các điểm được tính toán lại và vẽ lại mỗi khi Excel tính toán lại. Đây là lý do tại sao các giá trị RAND trong các hình ảnh trên lại khác nhau ở mỗi hình ảnh. Bạn có thể để nó theo cách này trong sổ làm việc của mình hoặc bạn có thể sao chép/dán dưới dạng giá trị sau khi tính toán jitter. Mình để nguyên và like nhấn F9 xem điểm nhảy

Vẽ sơ đồ dữ liệu bị xáo trộn

Vẽ sơ đồ dữ liệu không thay đổi, chúng tôi nhận được điều này

Như bạn có thể thấy có nhiều điểm chồng chéo. Không dễ đọc

Áp dụng công thức jitter ở trên cho giá trị x cho từng điểm dữ liệu, chúng tôi nhận được một biểu đồ cung cấp nhiều thông tin hơn

Cách tốt nhất để hiển thị dữ liệu chồng chéo là gì?

Bạn có thể phủ dữ liệu trong Excel không?