Điểm mấu chốt: Tìm hiểu cách tạo biểu đồ cột hiển thị phần trăm thay đổi hoặc phương sai giữa các cột.
Cấp độ kỹ năng: Trung cấp
Video # 2 – Thanh màu động
Xem full màn hình : TẠI ĐÂY
Tải xuống tệp Excel để theo dõi hoặc sử dụng trong các dự án của riêng bạn. Tệp đã được cập nhật với biểu đồ từ video # 2.
Biểu đồ cột với thay đổi tỷ lệ phần trăm
Bài viết này được lấy cảm hứng từ một biểu đồ mà tôi đã thấy trong một bài viết về Visual Capitalist về doanh số bán hàng của ngành công nghiệp âm nhạc .
Nguồn: visualcapitalist.com
Tôi thích cách họ hiển thị các chênh lệch giữa các năm và quyết định tạo lại nó trong Excel.
Khi tạo biểu đồ cột đơn giản cho các xu hướng, chúng tôi hầu như luôn muốn xem số lượng hoặc phần trăm thay đổi giữa mỗi cột. Có nhiều cách để giải quyết vấn đề này, bao gồm việc hiển thị các phương sai trên một biểu đồ riêng biệt.
Đối với giải pháp này, chúng tôi sẽ sử dụng các thanh lỗi và cột ẩn để hiển thị phần trăm thay đổi giữa mỗi cột.
Đây là một giải pháp thay thế trong đó các thanh lỗi dương và âm sử dụng một chuỗi riêng biệt. Điều này cho phép chúng tôi thay đổi định dạng của các thanh âm và dương.
Tôi đã tạo thay thế này dựa trên các đề xuất từ Conor và Wayne trên video YouTube. Tôi đã đưa nó vào tệp ví dụ mà bạn có thể tải xuống ở trên trên trang ‘Định dạng có điều kiện (2)’. Tôi tin rằng đây sẽ là lựa chọn phổ biến nhất. Bạn KHÔNG phải thay đổi vị trí nhãn cho các thanh âm cho giải pháp này.
Video thứ 2 ở trên giải thích thêm về giải pháp này. Tôi sẽ cập nhật bài đăng trong tương lai để giải thích chi tiết hơn.
Tạo biểu đồ
Biểu đồ không quá khó để thiết lập. Nó yêu cầu một vài cột công thức để tạo dữ liệu nguồn cho các cột biểu đồ, các thanh lỗi và các nhãn dữ liệu.
Tôi giải thích các bước để tạo biểu đồ bên dưới, điều này cũng sẽ giúp bạn tìm hiểu thêm về các yếu tố và kỹ thuật biểu đồ khác nhau có sẵn cho chúng tôi. Tuy nhiên, bạn không phải tạo biểu đồ. Bạn chỉ có thể tải xuống tệp ví dụ và cắm dữ liệu của riêng bạn .
Dưới đây là các bước để tạo biểu đồ.
1. Công thức dữ liệu nguồn
Biểu đồ sử dụng một vài cột công thức để tính số tiền cho các cột và phương sai vô hình được sử dụng cho các thanh và nhãn lỗi.
Dưới đây là giải thích nhanh về từng công thức bắt đầu trong cột C:
- Nhãn năm: Sử dụng hàm REPT để tham gia năm trong cột A với số lượng khoảng trắng lặp lại. Điều này làm cho nhãn dài hơn và di chuyển văn bản sang trái để căn chỉnh với thanh hiển thị cho số tiền đó. Nếu không, nhãn sẽ căn chỉnh ở giữa thanh hiển thị và vô hình.
- Doanh thu $ K: Tham chiếu đến số tiền trong cột B có áp dụng định dạng số hàng nghìn ($ #, ###, K).
- Thanh vô hình: Tham chiếu đến số tiền của kỳ tiếp theo (năm). Điều này được sử dụng cho các thanh vô hình, đó là đầu / cơ sở của các thanh lỗi.
- Phương sai: Phương sai giữa năm hiện tại và năm tiếp theo.
- Var +: Sử dụng hàm IF để trả về phương sai nếu nó dương. Được sử dụng cho các thanh lỗi. Trả về số âm để bắt đầu ở đầu thanh vô hình và xuống dưới .
- Var -: Sử dụng hàm IF để trả về phương sai nếu nó là số âm. Được sử dụng cho các thanh lỗi. Trả về số âm để bắt đầu ở cuối thanh lỗi và tăng lên .
- % Chênh lệch: Được sử dụng cho các nhãn trên cột ẩn trong thanh lỗi.
Tôi giải thích từng công thức cụ thể hơn trong video ở trên.
2. Tạo biểu đồ cột
Bước đầu tiên là tạo biểu đồ cột:
- Chọn dữ liệu trong cột C: E, bao gồm hàng tiêu đề.
- Trên tab chèn, hãy chọn Biểu đồ cột được nhóm từ trình đơn thả xuống Cột hoặc Biểu đồ thanh.
- Biểu đồ sẽ được chèn vào trang tính và trông giống như ảnh chụp màn hình sau đây.
3. Xóa phần tử phụ
Định dạng biểu đồ mặc định bao gồm một số yếu tố bổ sung mà chúng tôi sẽ không cần.
Bạn có thể nhấp chuột trái vào từng yếu tố sau và nhấn phím Delete.
- Huyền thoại
- Đường lưới ngang – Đây là tùy chọn. Bạn có thể giữ các đường lưới hoặc làm cho chúng có màu xám nhạt hơn.
Điều này làm sạch biểu đồ và cho phép người xem tập trung vào các yếu tố quan trọng.
4. Thêm thanh lỗi
Các thanh lỗi vẽ kết nối giữa các đỉnh của số lượng cột giữa mỗi thanh.
Dưới đây là hướng dẫn chèn Thanh Lỗi:
- Nhấp chuột trái vào một trong các cột trong chuỗi Invisible Bar (cột màu cam).
- Từ trình đơn Yếu tố, chọn Thanh lỗi> Tùy chọn khác.
- Ngăn tác vụ sẽ hiển thị tùy chọn Thanh Lỗi Định dạng.
- Hướng nên được đặt thành Cả hai.
- Đối với tùy chọn Số lượng Lỗi, hãy chọn nút radio Tùy chỉnh, sau đó nhấp vào nút Xác định Giá trị.
- Chọn phạm vi giá trị lỗi dương và âm.
- G5: G11 cho các giá trị dương
- H5: H11 cho các giá trị âm
- Bấm OK để thêm các Thanh Lỗi vào biểu đồ.
Các thanh lỗi bắt đầu ở đầu cột Vô hình (màu cam) để tạo phần tử phương sai. Thanh Invisible là số tiền trong giai đoạn tiếp theo. Các thanh lỗi đi xuống cho những thay đổi tích cực và lên cho những thay đổi tiêu cực. Điều này kết nối đầu cột từ giai đoạn hiện tại lên đầu cột trong giai đoạn tiếp theo.
5. Thay đổi chồng chéo và chiều rộng khoảng cách của chuỗi
Bước tiếp theo là làm cho các thanh rộng hơn và loại bỏ bất kỳ chồng chéo nào.
- Nhấp chuột trái vào bất kỳ thanh số lượng (màu xanh) để chọn toàn bộ chuỗi.
- Nhấp chuột phải> Định dạng chuỗi
- Thay đổi số lượng chồng chéo và chiều rộng khoảng cách thành 0%.
6. Định dạng các thanh vô hình không có Fill
Các thanh màu cam chỉ được sử dụng làm cơ sở của các thanh lỗi. Chúng tôi không cần phải hiển thị chúng trên biểu đồ, và có thể làm cho chúng vô hình.
- Nhấp chuột trái vào bất kỳ thanh màu cam nào trong Dòng vô hình để chọn toàn bộ chuỗi.
- Trên tab Format, chọn No Fill từ menu thả xuống Shape Fill.
7. Thêm nhãn dữ liệu
Cuối cùng, chúng ta cần thêm các nhãn dữ liệu vào các cột và các thanh lỗi. Dưới đây là hướng dẫn cho Excel 2013 trở lên. Xem ghi chú của tôi bên dưới nếu bạn đang sử dụng Excel 2010 hoặc phiên bản cũ hơn.
- Nhấp chuột trái vào bất kỳ thanh vô hình nào (màu cam) để chọn tất cả các thanh.
Lưu ý: Nếu bạn gặp sự cố khi nhấp vào các thanh. Chọn biểu đồ, chuyển đến tab Định dạng trong ruy-băng và chọn Chuỗi “Thanh vô hình” từ menu thả xuống ở bên trái. - Chọn Nhãn dữ liệu> Tùy chọn khác từ trình đơn Yếu tố
- Chọn menu phụ Tùy chọn nhãn trong ngăn tác vụ Định dạng nhãn dữ liệu.
- Nhấp vào hộp kiểm Value from Cells.
- Chọn phạm vi I5: I11 và nhấn OK.
- Bỏ chọn Value và Show Leader Lines.
- Vị trí Nhãn nên được đặt thành Bên ngoài theo mặc định.
- Đối với bất kỳ chênh lệch âm nào, hãy chọn từng nhãn dữ liệu và thay đổi vị trí thành Inside End. Xem video ở trên để biết chi tiết.
Các nhãn sẽ được hiển thị cho cả số lượng và các thanh lỗi.
Bạn có thể thay đổi định dạng văn bản và màu sắc cần thiết để phù hợp với báo cáo hoặc trang tổng quan của mình.
Excel 2010 & trước đó
Nếu bạn đang sử dụng Excel 2010 hoặc cũ hơn, bạn sẽ không có tùy chọn Giá trị từ ô cho các nhãn dữ liệu. Tuy nhiên, bạn có thể sử dụng trình bổ sung XY Labeler miễn phí từ AppsPro để tạo nhãn. Điều này sẽ giúp bạn tiết kiệm rất nhiều thời gian. Đây là liên kết để tải xuống bổ trợ.
Giải pháp thay thế
Tôi cũng có một bài đăng cũ hơn giải thích cách tạo biểu đồ sau với phương sai giữa hai chuỗi. Điều này thật tuyệt vời cho các báo cáo thực tế so với ngân sách.
Phần kết luận
Tôi nghĩ biểu đồ này là một cách hay để hiển thị phần trăm thay đổi / phương sai giữa các cột.
Nó sẽ hoạt động tốt nhất khi bạn có số cột nhỏ hơn, có thể là 12 hoặc ít hơn. Nếu không, tôi nghĩ biểu đồ có thể trở nên quá lộn xộn. Bạn chắc chắn sẽ muốn loại bỏ càng nhiều yếu tố bổ sung (đồ thị rác) càng tốt để cho nó một cái nhìn sạch sẽ dễ đọc.
Tôi hy vọng bạn có thể đặt cái này để sử dụng tốt. Xin vui lòng để lại một bình luận dưới đây và cho chúng tôi biết những gì bạn nghĩ, hoặc nếu bạn có bất kỳ câu hỏi nào.
Cảm ơn bạn! ?
DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.Cám ơn.
