Nhãn động trong biểu đồ Excel

Tìm hiểu cách tạo nhãn dữ liệu biểu đồ động có thể thay đổi bằng bộ cắt để hiển thị các chỉ số hoặc phép tính khác nhau. Download file mẫu để làm theo

dòng dưới cùng. Tìm hiểu cách tạo biểu đồ tương tác này trong đó chỉ số nhãn dữ liệu thay đổi dựa trên bộ cắt

trình độ kỹ năng. Nâng cao

Trước đây tôi đã viết về việc tôi KHÔNG phải là người yêu thích biểu đồ xếp chồng như thế nào và đã chia sẻ một số lựa chọn thay thế cho biểu đồ cột hoặc thanh xếp chồng

Về cơ bản, rất khó để so sánh giữa các chuỗi trong các thanh vì đường cơ sở không đồng đều

Tuy nhiên, những loại biểu đồ này rất phổ biến và tôi cũng có lỗi khi sử dụng chúng. 🙂

Nhưng các biểu đồ xếp chồng lên nhau dường như luôn để lại cho chúng ta nhiều câu hỏi hơn…

• Tổng số tiền lớn cho thanh (tổng của chuỗi) là bao nhiêu?
• Tỷ lệ phần trăm của tổng số cho mỗi chuỗi (đoạn) trong thanh là bao nhiêu?
• Phần trăm thay đổi so với giai đoạn trước là bao nhiêu?

Đây là những câu hỏi tuyệt vời có thể giúp xác định xu hướng và nhu cầu phân tích dữ liệu hơn nữa

Nhãn dữ liệu động trên biểu đồ xếp chồng

Vì vậy, biểu đồ này cố gắng giúp trả lời một số câu hỏi đó. Nó cho phép người dùng thay đổi chỉ số nhãn dữ liệu bằng một bộ cắt

Sau đó, người dùng có thể nhanh chóng xem số lượng, tỷ lệ phần trăm của tổng số hoặc phần trăm thay đổi cho từng chuỗi trong mỗi thanh

Tất nhiên, bạn có thể đặt tất cả các số liệu này vào một nhãn, nhưng điều đó thường dẫn đến một biểu đồ rất lộn xộn

Cách tạo nhãn dữ liệu biểu đồ động

Việc thiết lập các nhãn động không quá phức tạp nhưng nó đòi hỏi một chút công sức

Vì vậy, các thành phần cho biểu đồ này là

• Chức năng VĂN BẢN
• Chức năng CHỌN
• Một bảng tổng hợp
• một máy thái
• Biểu đồ một cột hoặc thanh (duh)
• Tốt nhất là Excel 2013 hoặc 2016 cho tính năng nhãn “Giá trị từ các ô”

Trong bài viết này, tôi sẽ cung cấp các hướng dẫn cấp cao về cách tạo. Bạn có thể tải file về để làm theo hoặc sửa đổi để sử dụng cho riêng mình

Tải tập tin

Tải file ví dụ về để làm theo

Dynamic-Chart-Data-Labels-Stacked-Column. xlsxTải xuống

Cảnh báo. Tệp này hoạt động tốt nhất trong Excel 2013 hoặc 2016 cho Windows. Nó sẽ hoạt động vào năm 2007/2010 nhưng bạn sẽ phải gán lại các nhãn bằng phương pháp được đề cập trong bước 5 bên dưới. Nó cũng hoạt động vào năm 2016 cho Mac, nhưng không hoạt động vào năm 2011

Bước 1. Tạo biểu đồ xếp chồng với tổng số

Bước đầu tiên là tạo một biểu đồ cột xếp chồng thông thường với các tổng lớn phía trên các cột. Jon Peltier có một bài viết giải thích cách thêm tổng lớn vào biểu đồ cột xếp chồng

Bước 2. Tính toán số liệu nhãn

Dữ liệu nguồn cho biểu đồ xếp chồng trông giống như sau. Chúng tôi cũng sẽ cần tính toán các số liệu khác nhau cho các nhãn

Tôi đã tạo một phần trên trang tính cho từng chỉ số. $Số tiền, % trên Tổng số và %Thay đổi

Điều này khá dễ và tôi sẽ không đi vào chi tiết của từng phép tính

Bước 3. Sử dụng Hàm TEXT để Định dạng Nhãn

Thông thường, biểu đồ sẽ hiển thị nhãn dữ liệu dựa trên dữ liệu nguồn cơ bản cho biểu đồ. Trong Excel 2013, một tính năng mới có tên là “Giá trị từ các ô” đã được giới thiệu. Tính năng này cho phép chúng tôi chỉ định phạm vi mà chúng tôi muốn sử dụng cho các nhãn

Vì nhãn dữ liệu của chúng tôi sẽ thay đổi giữa định dạng tiền tệ ($) và tỷ lệ phần trăm (%) nên chúng tôi cũng cần một cách để thay đổi định dạng số trong biểu đồ. Nếu không, nó sẽ hiển thị số thập phân thay vì tỷ lệ phần trăm

May mắn thay, chúng ta có thể sử dụng chức năng TEXT cho việc này. Hàm TEXT cho phép bạn cung cấp cho nó một giá trị và chỉ định định dạng số mà bạn muốn hiển thị giá trị đó trong

Hàm TEXT sẽ thực sự trả về một giá trị văn bản cho ô, mặc dù nó trông giống như một số. Vì vậy, chúng tôi có thể sử dụng điều này làm nguồn nhãn dữ liệu của mình và biểu đồ sẽ hiển thị đúng định dạng

Bước 4. Sử dụng chức năng CHOOSE để xác định nhãn nào sẽ hiển thị

Hiện chúng tôi có ba phần chứa các số liệu khác nhau. Tiếp theo, chúng tôi muốn tạo một phần sẽ chứa các nhãn biểu đồ

Chúng ta sẽ cần sử dụng một số loại chức năng tra cứu để trả về số liệu chính xác dựa trên lựa chọn bộ cắt

Chức năng CHOOSE hoạt động rất tốt cho việc này. CHOOSE cho phép chúng ta chỉ định một số chỉ mục (1,2,3,…) và nó sẽ trả về một giá trị dựa trên số chỉ mục

Hiện tại, chúng tôi sẽ chỉ thêm một ô chứa số chỉ mục và trỏ đến ba số liệu cho mỗi giá trị trong công thức CHỌN. Cuối cùng, máy cắt sẽ kiểm soát số chỉ mục

Bước 5. Thiết lập Nhãn dữ liệu

Bước tiếp theo là thay đổi nhãn dữ liệu để chúng hiển thị các giá trị trong các ô chứa công thức CHỌN của chúng tôi

Như tôi đã đề cập trước đây, chúng ta có thể sử dụng tính năng “Giá trị từ các ô” trong Excel 2013 hoặc 2016 để thực hiện việc này dễ dàng hơn

Về cơ bản, bạn cần chọn một chuỗi nhãn, sau đó nhấn nút Giá trị từ Ô trong menu Định dạng Nhãn Dữ liệu. Sau đó, chọn phạm vi chứa số liệu cho chuỗi đó

Lặp lại bước này cho từng chuỗi trong biểu đồ

Nếu bạn đang sử dụng Excel 2010 trở về trước, biểu đồ sẽ giống như sau khi bạn mở tệp

Điều này là do Excel 2010 không chứa tính năng Giá trị từ ô. Jon Peltier có một bài viết tuyệt vời với một số cách giải quyết để áp dụng nhãn dữ liệu tùy chỉnh. Điều này bao gồm việc sử dụng Phần bổ trợ Công cụ dán nhãn biểu đồ XY, đây là bản tải xuống miễn phí dành cho Windows hoặc Mac

Bước 6. Thiết lập Pivot Table và Slicer

Bước cuối cùng là làm cho các nhãn dữ liệu có tính tương tác. Chúng tôi làm điều này với bảng tổng hợp và máy cắt

Dữ liệu nguồn cho bảng tổng hợp là Bảng ở phía bên trái trong hình ảnh bên dưới. Bảng này chứa ba tùy chọn cho các nhãn dữ liệu khác nhau

Nó cũng bao gồm số Chỉ mục sẽ được tham chiếu trong công thức CHỌN (bước 4)

Thêm các trường Tên, Chỉ mục và Biểu tượng vào vùng Hàng của bảng tổng hợp

Sau đó chèn một slicer cho trường Biểu tượng. Hãy xem chuỗi video miễn phí của tôi về bảng tổng hợp và trang tổng quan để tìm hiểu cách tạo bảng tổng hợp và thêm bộ cắt

Bộ cắt có thể được định dạng để phù hợp với chủ đề của biểu đồ của bạn. Tôi cũng đã thay đổi Cột thành 3 để hiển thị các nút cắt theo chiều ngang

Bước cuối cùng là tham chiếu số Chỉ mục trong bảng tổng hợp (ô F2) trong Số Chỉ mục của công thức CHỌN

Khi người dùng chọn nút slicer, ô F2 trong bảng tổng hợp sẽ hiển thị số chỉ mục của mục đã chọn. Các công thức CHỌN sẽ tự động trả về chỉ số nhãn của mục đã chọn

Mang tất cả lại với nhau

Có rất nhiều bước để tạo biểu đồ tương tác này. May mắn thay, bạn có thể và sửa đổi để sử dụng

Ví dụ bên dưới hiển thị chủ đề màu gradient nhạt hơn. Bạn có thể di chuyển bộ cắt vào khu vực biểu đồ và cũng có thể thêm hướng dẫn để người dùng hiểu cách thức hoạt động của nó

Chúng tôi có thể thêm những số liệu nào khác?

Có rất nhiều khả năng với kỹ thuật này. Điều này có thể được áp dụng cho các loại biểu đồ khác ngoài biểu đồ xếp chồng

Bạn sẽ sử dụng biểu đồ hoặc chỉ số nhãn nào khác cho biểu đồ động này?

Trước Biểu đồ bánh rán tiến trình với định dạng có điều kiện trong Excel

Tiếp theo 3 cách nhóm thời gian trong Excel

Bạn cũng có thể thích

5 biểu đồ xấu và các lựa chọn thay thế

7 Công cụ Bảng điều khiển Excel & Mẹo dành cho Thanh công cụ Truy cập Nhanh

Biểu đồ xoay vòng chứng khoán cao thấp cho Excel

Bảng điều khiển quảng cáo Olympic NBC