Cách tạo biểu đồ tương tác với các yếu tố động trong Excel

Hướng dẫn này sẽ trình bày cách tạo biểu đồ tương tác với các yếu tố động trong tất cả các phiên bản Excel: 2007, 2010, 2013, 2016 và 2022.

Biểu đồ tương tác là một biểu đồ cho phép người dùng kiểm soát chuỗi dữ liệu nào sẽ được minh họa trong biểu đồ, giúp hiển thị hoặc ẩn dữ liệu bằng cách sử dụng các điều khiển biểu mẫu. Chúng có nhiều hình dạng và nhiều dạng, từ các nút tùy chọn đến thanh cuộn và danh sách thả xuống.

Đổi lại, làm cho một số thành phần biểu đồ động - chẳng hạn như tiêu đề biểu đồ - giúp bạn có thể tự động hóa quá trình cập nhật biểu đồ của mình bất cứ khi nào bạn thêm hoặc xóa các mục khỏi dữ liệu nguồn cơ bản.

Vì vậy, tạo biểu đồ tương tác với các yếu tố động cho phép bạn giết hai con chim bằng một viên đá.

Để bắt đầu, các biểu đồ tương tác giúp bạn hiển thị một cách gọn gàng lượng dữ liệu khổng lồ chỉ với một biểu đồ:

Ngoài ra, việc chuyển một số phần tử biểu đồ từ tĩnh sang động giúp tiết kiệm vô số giờ về lâu dài và ngăn bạn quên cập nhật biểu đồ bất cứ khi nào bạn thay đổi bảng dữ liệu của mình.

Nói như vậy, hướng dẫn này là một khóa học tổng hợp về các kỹ thuật xây dựng biểu đồ nâng cao trong Excel: bạn sẽ học cách tạo biểu đồ tương tác cả với danh sách thả xuống và với thanh cuộn cũng như nắm vững các cách tạo tiêu đề biểu đồ của bạn. và dải dữ liệu biểu đồ động.

Dữ liệu mẫu

Để cho bạn biết đó là những gì, chúng tôi cần một số dữ liệu thô để xử lý. Vì lý do đó, hãy xem bảng sau minh họa số liệu bán sách của một hiệu sách trực tuyến hư cấu:

Cách tạo tiêu đề biểu đồ động

Trước tiên, hãy giải quyết phần dễ dàng: làm cho tiêu đề biểu đồ của bạn trở nên động.

Bạn có thể làm cho tiêu đề động bằng cách liên kết nó với một ô cụ thể trong một trang tính để mọi thay đổi đối với giá trị trong ô đó được tự động phản ánh trong tiêu đề biểu đồ.

Và cái hay của phương pháp này là chỉ cần một vài bước đơn giản để chuyển tiêu đề biểu đồ của bạn từ tĩnh sang động:

1. Bấm đúp vào tiêu đề biểu đồ của bạn.
2. Nhấn vào Thanh công thức và gõ “=”Vào đó.
3. Chọn bất kỳ ô nào để gán nó làm tiêu đề biểu đồ mới của bạn.

Thoạt nhìn, ý tưởng có vẻ khá sơ khai. Nhưng chúng tôi thậm chí hầu như không làm xước bề mặt của những thứ có thể thực hiện được bằng cách áp dụng kỹ thuật đơn giản này. Để tìm hiểu thêm, hãy xem hướng dẫn chuyên sâu về tiêu đề biểu đồ động trong Excel một cách chi tiết hơn.

Cách tạo Phạm vi biểu đồ động

Tiếp theo: thiết lập phạm vi biểu đồ động. Một lần nữa, theo quy ước, mỗi khi bạn thêm hoặc xóa các mục khỏi phạm vi dữ liệu được sử dụng trong việc xây dựng biểu đồ Excel, dữ liệu nguồn cũng phải được điều chỉnh theo cách thủ công.

May mắn thay, bạn có thể tránh cho mình những rắc rối khi phải đối phó với tất cả những điều đó bằng cách tạo một phạm vi biểu đồ động tự động cập nhật biểu đồ mỗi khi dữ liệu nguồn được thay đổi.

Về cơ bản, có hai cách để sử dụng: phương pháp bảng và kỹ thuật phạm vi được đặt tên. Chúng tôi sẽ trình bày ngắn gọn về phương pháp bảng dễ dàng hơn ở đây, nhưng để hiểu đầy đủ về cách khai thác sức mạnh của phạm vi biểu đồ động trong Excel, hãy xem hướng dẫn bao gồm tất cả các nội dung chi tiết hơn.

Dù sao, trở lại phương pháp bảng. Đầu tiên, chuyển phạm vi ô bạn muốn sử dụng để xây dựng biểu đồ thành bảng.

1. Chọn toàn bộ phạm vi dữ liệu (A2: G6).
2. Đi đến Chèn chuyển hướng.
3. Nhấn vào "Bàn" cái nút.

Nếu dữ liệu của bạn không có bất kỳ tiêu đề nào, hãy bỏ chọn “Bảng của tôi có tiêu đề" hộp.

Khi đó, phạm vi dữ liệu sẽ được chuyển đổi thành một bảng.

Bây giờ, tất cả những gì bạn cần làm là vẽ biểu đồ của mình trong khi sử dụng bảng làm dữ liệu nguồn của biểu đồ:

1. Đánh dấu bảng (A2: G6).
2. Điều hướng đến Chèn chuyển hướng.
3. Tạo bất kỳ biểu đồ 2-D nào, chẳng hạn như biểu đồ thanh nhóm (Chèn Biểu đồ Cột hoặc Thanh> Thanh Cụm).

Voilà! Bạn đã sẵn sàng. Bây giờ, hãy kiểm tra bằng cách chèn các mục mới vào bảng để xem chúng tự động được nạp lại trên biểu đồ.

Cách tạo biểu đồ tương tác với danh sách thả xuống

Bây giờ bạn đã thực hiện những bước đầu tiên của mình trong lĩnh vực công cụ trực quan hóa dữ liệu nâng cao trong Excel, đã đến lúc chuyển sang phần khó: biểu đồ Excel tương tác.

Khi bạn có nhiều dữ liệu để lập biểu đồ, việc giới thiệu các tính năng tương tác cho đồ thị Excel của bạn - có thể là danh sách thả xuống, hộp kiểm hoặc thanh cuộn - giúp bạn duy trì không gian bảng điều khiển và khai báo biểu đồ trong khi giúp người dùng thoải mái hơn để phân tích dữ liệu.

Để làm ví dụ, hãy xem biểu đồ cột được hiển thị bên dưới. Thay vì hiển thị một biểu đồ lộn xộn chứa toàn bộ bảng dữ liệu, việc chọn biểu đồ tương tác này với danh sách thả xuống cho phép người dùng phóng to số liệu bán hàng của một năm nhất định chỉ trong hai lần nhấp chuột.

Chỉ cần làm theo các bước chi tiết được nêu dưới đây để tìm hiểu cách bạn có thể làm điều tương tự.

Bước # 1: Đặt nền móng.

Trước khi chúng tôi bắt đầu, hãy truy cập vào Nhà phát triển tab có chứa một số tính năng nâng cao của Excel mà chúng tôi sẽ chuyển sang phần tiếp theo. Tab bị ẩn theo mặc định, nhưng chỉ cần một vài cú nhấp chuột để thêm nó vào Băng rôn.

Đầu tiên, nhấp chuột phải vào bất kỳ không gian trống nào trong Băng rôn và chọn “Tùy chỉnh Ribbon.”

bên trong Tùy chọn Excel hộp thoại, điều hướng đến Các tab chính danh sách, hãy kiểm tra “Nhà phát triểnHộp "và nhấn"VÂNG.”

Các Nhà phát triển tab bây giờ sẽ hiển thị trên Băng rôn. Tại thời điểm này, hãy chọn bất kỳ ô trống nào để hiển thị đầu ra menu thả xuống (B8). Điều này sẽ giúp Excel hiển thị dữ liệu phù hợp dựa trên tùy chọn nào được chọn từ danh sách thả xuống.

Bước # 2: Thêm danh sách thả xuống.

Bước tiếp theo của chúng tôi là thêm trình đơn thả xuống vào trang tính:

1. Đi đến Nhà phát triển chuyển hướng.
2. Nhấp chuột "Chèn.”
3. Dưới "Kiểm soát biểu mẫu," lựa chọn "Hộp kết hợp.”

Vẽ hộp tổ hợp trống đó ở bất cứ đâu bạn muốn. Để minh họa, đây là menu thả xuống trống trông như thế nào:

Bước # 3: Liên kết danh sách thả xuống với các ô của trang tính.

Để menu nhập danh sách các năm trong bảng dữ liệu, bạn phải liên kết nó với phạm vi dữ liệu tương ứng trong trang tính. Để làm điều đó, hãy nhấp chuột phải vào hộp tổ hợp và chọn “Kiểm soát định dạng.”

Trong hộp thoại, hãy làm như sau:

1. Vì "Phạm vi đầu vào, ”Đánh dấu tất cả các giá trị thực tế trong bảng dữ liệu ($ A $ 3: $ G $ 6).
2. Vì "Liên kết ô, ”Chọn ô trống được chỉ định để lưu trữ đầu ra menu thả xuống ($ B $ 8).
3. Vì "Thả xuống dòng, "Nhập số điểm dữ liệu trong tập dữ liệu của bạn (trong trường hợp của chúng tôi là bốn) và nhấp vào"VÂNG.”

Hành động này sẽ dẫn đến tên của các điểm dữ liệu được thêm vào danh sách thả xuống trong khi giá trị ô trợ giúp (B8) đặc trưng cho mục hiện được chọn từ menu dưới dạng tùy chọn 1, 2, 3 hoặc 4:

Bước # 4: Thiết lập bảng dữ liệu biểu đồ.

Vì biểu đồ chỉ nên minh họa một chuỗi dữ liệu tại một thời điểm, như một cách giải quyết, hãy thiết lập một bảng trợ giúp động sẽ lọc qua dữ liệu thô và chỉ trả về số liệu bán hàng cho năm được chọn từ danh sách.

Bảng đó cuối cùng sẽ được sử dụng làm dữ liệu nguồn của biểu đồ, cho phép người dùng chuyển đổi chế độ xem một cách hiệu quả bằng menu thả xuống.

Đầu tiên, sao chép tiêu đề của bảng dữ liệu bán hàng và để lại một hàng trống để hiển thị số liệu bán hàng đã lọc.

Nhưng làm thế nào để bạn đảm bảo dữ liệu được trích xuất một cách chính xác? Đó là lúc hàm INDEX phát huy tác dụng. Nhập công thức sau vào A11 và sao chép nó sang G11:

1	= INDEX (A3: A6, $ B $ 8)

Về cơ bản, công thức lọc qua cột Năm (cột A) và chỉ trả về giá trị trong hàng mà số tương ứng với mục hiện được chọn trong danh sách thả xuống.

Bước # 5: Thiết lập biểu đồ dựa trên dữ liệu biểu đồ động.

Bạn đã vượt qua phần khó nhất, vì vậy tất cả những gì còn lại cần làm là tạo biểu đồ 2-D dựa trên bảng trợ giúp (đánh dấu bảng trợ giúp [A10: G11]> Chèn> tạo biểu đồ cột nhóm).

Cuối cùng, nếu bạn muốn trình đơn thả xuống được đặt trên đầu biểu đồ, hãy nhấp chuột phải vào hộp tổ hợp, nhấp vào “Đặt hàng," và lựa chọn "Mang ra phía trước. ” (Hoặc, nhấp chuột phải vào sơ đồ biểu đồ, nhấp vào “Đặt hàng," và lựa chọn "Gửi lại.”)

Đó là nó! Ở đó, bạn có biểu đồ cột tương tác của mình được liên kết với menu thả xuống.

Cách tạo biểu đồ tương tác với thanh cuộn

Sự thật mà nói, quá trình thêm thanh cuộn vào biểu đồ Excel của bạn tương tự như các bước đã nêu ở trên.

Nhưng điều gì sẽ xảy ra nếu bạn muốn phân tích động lực bán hàng của từng loại sách (các cột), thay vì hiển thị số liệu bán hàng hàng năm (các hàng)? Để minh họa, hãy xem biểu đồ đường tương tác bên dưới để thực hiện điều đó:

Làm thế nào để bạn kéo nó ra? Chà, đó là lúc mọi thứ bắt đầu phức tạp hơn một chút. Tuy nhiên, nếu bạn chỉ cần tuân thủ quy trình từng bước dưới đây, làm theo hướng dẫn thì bạn sẽ cảm thấy giống như đang đi dạo trong công viên, ngay cả khi bạn là một người mới hoàn toàn.

Bước # 1: Đặt nền móng.

Trước khi bạn bắt đầu, hãy thêm Nhà phát triển tab để Băng rôn, vì có chứa tính năng cho phép chúng tôi vẽ một thanh cuộn (nhấp chuột phải vào bất kỳ khoảng trống nào trong Ribbon> tùy chỉnh Ribbon> Tab chính> chọn hộp “Nhà phát triển”> OK).

Để làm cho mọi thứ hoạt động như đồng hồ, hãy chỉ định một ô trống để hiển thị đầu ra thanh cuộn để Excel có thể định vị chính xác hộp cuộn trong thanh cuộn (B8).

Bước # 2: Vẽ thanh cuộn.

Bước tiếp theo của chúng tôi là chèn thanh cuộn vào trang tính:

1. Đi đến Nhà phát triển chuyển hướng.
2. Nhấn vào “Chèn" cái nút.
3. Dưới "Kiểm soát biểu mẫu," lựa chọn "Thanh cuộn.”

Tại thời điểm này, bạn có thể vẽ thanh cuộn bất cứ nơi nào bạn muốn. Chúng tôi sẽ chuyển nó vào vị trí sau.

Bước # 2: Liên kết thanh cuộn với dữ liệu trang tính.

Nhấp chuột phải vào thanh cuộn và chọn “Kiểm soát định dạng.”

bên trong Kiểm soát định dạng hộp thoại, sửa đổi cài đặt của thanh cuộn:

• Giá trị hiện tại: Giá trị này xác định vị trí mặc định của hộp cuộn trong thanh cuộn. Đặt giá trị thành “1.”
• Giá trị tối thiểu: Cài đặt này biểu thị giá trị thấp nhất của thanh cuộn. Trong trường hợp của chúng tôi, hãy nhập “1" ở trong cánh đồng.
• Gia trị lơn nhât: Tham số này xác định giá trị cao nhất của thanh cuộn và phải tương ứng với số điểm dữ liệu được sử dụng để vẽ biểu đồ tương tác. Vì bảng dữ liệu bao gồm sáu danh mục sách, hãy thay đổi giá trị thành “6.”
• Gia tăng: Giá trị này xác định mức độ trơn tru của hộp cuộn thay đổi vị trí khi bạn di chuyển nó. Luôn đặt giá trị thành “1.”
• Thay đổi trang: Giá trị này xác định cách hộp cuộn thay đổi vị trí khi bạn bấm vào vùng giữa hộp cuộn và các mũi tên. Luôn đặt giá trị thành “1.”
• Liên kết ô: Giá trị này đặc trưng cho vị trí hiện tại của hộp cuộn. Chỉ cần đánh dấu ô trong trang tính được chỉ định trước đó cho mục đích này ($ B $ 8).

Bước # 3: Tạo bảng dữ liệu biểu đồ.

Khi bạn đã hoàn tất quá trình thiết lập, hãy tạo một bảng trợ giúp mới để lưu trữ dữ liệu biểu đồ động được trích xuất từ ​​dữ liệu thô. Dữ liệu biểu đồ sẽ tự động điều chỉnh các giá trị tùy thuộc vào vị trí hiện tại của hộp cuộn - liên kết hiệu quả thanh cuộn với biểu đồ.

Để làm điều đó, hãy bắt đầu với việc sao chép cột Năm như thể hiện trên ảnh chụp màn hình bên dưới (A2: A6).

Khi đó, hãy triển khai hàm INDEX sau để chọn phạm vi ô chứa tên danh mục sách và trả về giá trị tương ứng dựa trên vị trí của hộp cuộn.

Nhập công thức sau vào ô B10:

1	= INDEX ($ B $ 2: $ G $ 2, $ B $ 8)

Bây giờ, hãy nhập số liệu bán hàng tương ứng với danh mục sách hiện tại bằng cách nhập công thức này vào B11 và sao chép nó xuống (B11: B14):

1	= CHỈ SỐ ($ B $ 3: $ G $ 6, TRẬN ĐẤU (A11, $ A $ 3: $ A $ 6,0), TRẬN ĐẤU ($ B $ 10, $ B $ 2: $ G $ 2, 0))

Sự kết hợp của hàm INDEX và MATCH giúp trả về chính xác giá trị mà số hàng và cột phù hợp với tiêu chí đã chỉ định.

Đầu tiên công thức lọc qua cột Năm ($ A $ 3: $ A $ 6) và chọn hàng phù hợp với năm tương ứng trong bảng trợ giúp (A11). Sau đó, công thức sẽ lọc tên danh mục sách theo cách tương tự ($ B $ 2: $ G $ 2). Kết quả là công thức trả về giá trị động chính xác sẽ điều chỉnh bất cứ khi nào bạn di chuyển hộp cuộn.

Bước # 4: Tạo biểu đồ dựa trên bảng trợ giúp.

Khi bảng dữ liệu biểu đồ đã được kết hợp với nhau, chỉ cần tạo một biểu đồ đơn giản để xem thanh cuộn đang hoạt động.

Đánh dấu toàn bộ bảng trợ giúp (A10: B14), đi đến Chèn và vẽ bất kỳ biểu đồ 2-D nào, chẳng hạn như biểu đồ đường (Chèn Đường hoặc Biểu đồ Khu vực> Đường có Điểm đánh dấu).

LƯU Ý: Nếu bạn muốn thanh cuộn được hiển thị trên đầu biểu đồ, hãy nhấp chuột phải vào thanh cuộn, chọn “Đặt hàng"Và chọn"Mang về phía trước. ” (Hoặc, nhấp chuột phải vào sơ đồ biểu đồ, nhấp vào “Đặt hàng," và lựa chọn "Gửi lại.”)

Và đó là cách bạn làm điều đó. Bạn vừa học được mọi thứ bạn cần biết về các kỹ thuật nâng cao mạnh mẽ sẽ giúp trò chơi trực quan hóa dữ liệu của bạn tăng trưởng nhảy vọt.