Cách tạo Dải biểu đồ động trong Excel

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

Theo mặc định, khi bạn mở rộng hoặc hợp đồng tập dữ liệu được sử dụng để vẽ biểu đồ trong Excel, dữ liệu nguồn cơ bản cũng phải được điều chỉnh theo cách thủ công.

Tuy nhiên, bằng cách tạo phạm vi biểu đồ động, bạn có thể tránh được rắc rối này.

Phạm vi biểu đồ động cho phép bạn tự động cập nhật dữ liệu nguồn mỗi khi bạn thêm hoặc xóa các giá trị khỏi phạm vi dữ liệu, tiết kiệm rất nhiều thời gian và công sức.

Trong hướng dẫn này, bạn sẽ học mọi thứ bạn cần biết để giải phóng sức mạnh của Dải biểu đồ động.

Dải biểu đồ động - Giới thiệu

Hãy xem xét tập dữ liệu mẫu sau đây phân tích biến động tỷ suất lợi nhuận:

Về cơ bản, có hai cách để thiết lập phạm vi biểu đồ động:

1. Chuyển đổi phạm vi dữ liệu thành một bảng
2. Sử dụng phạm vi được đặt tên động làm dữ liệu nguồn của biểu đồ.

Cả hai phương pháp đều có ưu và nhược điểm, vì vậy chúng tôi sẽ nói chi tiết hơn về từng phương pháp để giúp bạn xác định phương pháp nào phù hợp nhất với mình.

Không cần thêm lời khuyên, hãy bắt đầu.

Phương pháp bảng

Hãy để tôi bắt đầu bằng cách chỉ cho bạn cách nhanh nhất và dễ nhất để hoàn thành nhiệm vụ trong tầm tay. Vì vậy, đây là bài tập: Biến phạm vi dữ liệu thành một bảng và bạn dễ dàng hơn so với việc bóc vỏ hạt đậu.

Bằng cách đó, mọi thứ bạn nhập vào các ô ở cuối bảng đó sẽ tự động được đưa vào dữ liệu nguồn của biểu đồ.

Đây là cách bạn có thể biến điều đó thành hiện thực chỉ trong hai bước đơn giản.

Bước # 1: Chuyển đổi phạm vi dữ liệu thành một bảng.

Ngay ra khỏi cổng, hãy chuyển đổi phạm vi ô chứa dữ liệu biểu đồ của bạn thành một bảng.

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

bên trong Tạo bảng hộp thoại, hãy làm như sau:

1. Kiểm tra kỹ xem phạm vi ô được đánh dấu có khớp với toàn bộ bảng dữ liệu hay không.
2. Nếu bảng của bạn không chứa hàng tiêu đề, hãy bỏ chọn “Bảng của tôi có tiêu đề" hộp.
3. Nhấp chuột "VÂNG.“

Kết quả là, bạn sẽ kết thúc với bảng này:

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

Nền tảng đã được đặt, có nghĩa là bây giờ bạn có thể thiết lập biểu đồ bằng cách sử dụng bảng.

1. Đánh dấu toàn bộ bảng (A1: B6).
2. Điều hướng đến Chèn chuyển hướng.
3. Tạo bất kỳ biểu đồ 2-D nào. Đối với mục đích minh họa, hãy thiết lập một biểu đồ cột đơn giản (Chèn biểu đồ cột hoặc thanh> cột được nhóm).

Đó là nó! Để kiểm tra kỹ thuật, hãy thử thêm các điểm dữ liệu mới ở cuối bảng để xem chúng được tự động lập biểu đồ trên cốt truyện. Nó có thể đơn giản hơn bao nhiêu?

GHI CHÚ: Với cách tiếp cận này, tập dữ liệu sẽ không bao giờ chứa các ô trống trong đó-sẽ làm hỏng biểu đồ.

Phương pháp phạm vi được đặt tên động

Mặc dù dễ áp ​​dụng, Phương pháp bảng có một số khuyết điểm nghiêm trọng. Ví dụ: biểu đồ bị lộn xộn bất cứ khi nào tập dữ liệu mới kết thúc nhỏ hơn bảng dữ liệu ban đầu-cộng, đôi khi bạn chỉ đơn giản là không muốn phạm vi dữ liệu được chuyển đổi thành một bảng.

Việc chọn phạm vi đã đặt tên có thể mất nhiều thời gian và công sức hơn một chút, nhưng kỹ thuật này loại bỏ nhược điểm của phương pháp bảng và trên hết, làm cho phạm vi động thoải mái hơn rất nhiều để làm việc trong thời gian dài.

Bước # 1: Tạo phạm vi được đặt tên động.

Để bắt đầu, hãy thiết lập các phạm vi được đặt tên mà cuối cùng sẽ được sử dụng làm dữ liệu nguồn cho biểu đồ trong tương lai của bạn.

1. Đi đến Công thức chuyển hướng.
2. Nhấp chuột "Người quản lý tên.”
3. bên trong Người quản lý tên hộp thoại xuất hiện, chọn “Mới.”

bên trong Tên mới hộp thoại, tạo một phạm vi được đặt tên hoàn toàn mới:

1. Kiểu "Phần tư" bên cạnh các "Tên" đồng ruộng. Để thuận tiện cho bạn, hãy đặt tên của phạm vi động khớp với ô hàng tiêu đề tương ứng của cột A (A1).
2. Bên trong "Phạm vi”, Chọn trang tính hiện tại. Trong trường hợp của chúng tôi, đó là Sheet1.
3. Nhập công thức sau vào “Đề cập đến" đồng ruộng: = OFFSET (Trang tính 1! $ A $ 2,0,0, COUNTA (Trang tính 1! $ A: $ A) -1,1)

Bằng tiếng Anh đơn giản, mỗi khi bạn thay đổi bất kỳ ô nào trong trang tính, hàm OFFSET chỉ trả về các giá trị thực trong cột A, bỏ đi ô hàng tiêu đề (A1), trong khi hàm COUNTA tính toán lại số lượng giá trị trong cột mỗi khi trang tính được cập nhật - thực hiện tất cả công việc khó khăn cho bạn một cách hiệu quả.

Hãy phân tích công thức một cách chi tiết hơn để giúp bạn hiểu cách hoạt động của công thức:

GHI CHÚ: Tên của một dải ô đã đặt tên phải bắt đầu bằng một chữ cái hoặc dấu gạch dưới và không được chứa khoảng trắng.

Bằng cùng một mã thông báo, hãy thiết lập một dải ô được đặt tên khác dựa trên cột Biên lợi nhuận (cột B) với sự trợ giúp của công thức này và ghi nhãn là “Tỷ suất lợi nhuận”:

1	= OFFSET (Trang 1! $ B $ 2,0,0, COUNTA (Trang 1! $ B: $ B) -1,1)

Lặp lại quy trình tương tự nếu bảng dữ liệu của bạn chứa nhiều cột có giá trị thực. Do đó, trong trường hợp của chúng tôi, bạn nên có hai phạm vi được đặt tên sẵn sàng để hành động:

Bước # 2: Tạo một biểu đồ trống.

Chúng tôi đã vượt qua phần khó nhất. Bây giờ, đã đến lúc thiết lập một biểu đồ trống để bạn có thể chèn các phạm vi được đặt tên động vào đó theo cách thủ công.

1. Chọn bất kỳ ô trống nào trong trang tính hiện tại (Sheet1).
2. Quay lại Chèn chuyển hướng.
3. Thiết lập bất kỳ biểu đồ 2-D nào bạn muốn. Đối với ví dụ của chúng tôi, chúng tôi sẽ tạo một biểu đồ cột (Chèn Cột hoặc Biểu đồ Thanh> Cột được Nhóm).

Bước # 3: Thêm phạm vi / phạm vi đã đặt tên có chứa các giá trị thực tế.

Đầu tiên, hãy chèn phạm vi đã đặt tên (Tỷ suất lợi nhuận) được liên kết với các giá trị thực tế (cột B) vào biểu đồ.

Nhấp chuột phải vào biểu đồ trống và chọn “Chọn dữ liệu”Từ menu ngữ cảnh.

bên trong Chọn nguồn dữ liệu cửa sổ hộp thoại, nhấp vào “Thêm vào.”

bên trong Chỉnh sửa chuỗi , tạo một chuỗi dữ liệu mới:

1. Dưới "Tên sê-ri, ”Đánh dấu ô hàng tiêu đề tương ứng (B1).
2. Dưới "Giá trị chuỗi, "Chỉ định phạm vi được đặt tên sẽ được vẽ trên biểu đồ bằng cách nhập như sau:"= Sheet1! Profit_Margin.”Tham chiếu bao gồm hai phần: tên của trang tính hiện tại (= Trang tính1) và phạm vi được đặt tên động tương ứng (Tỷ suất lợi nhuận). Dấu chấm than được sử dụng để liên kết hai biến với nhau.
3. Lựa chọn "VÂNG.”

Khi đó, Excel sẽ tự động lập biểu đồ các giá trị:

Bước # 4: Chèn dải ô đã đặt tên với các nhãn trục.

Cuối cùng, thay thế các nhãn trục danh mục mặc định bằng phạm vi được đặt tên bao gồm cột A (Phần tư).

bên trong Chọn nguồn dữ liệu hộp thoại, trong “Nhãn Trục ngang (Danh mục),”Chọn“Chỉnh sửa" cái nút.

Sau đó, chèn phạm vi đã đặt tên vào biểu đồ bằng cách nhập tham chiếu sau trong “Phạm vi nhãn trục:”

1	= Sheet1! Quý

Cuối cùng, biểu đồ cột dựa trên phạm vi biểu đồ động đã sẵn sàng:

Kiểm tra điều này: Biểu đồ được cập nhật tự động bất cứ khi nào bạn thêm hoặc xóa dữ liệu trong phạm vi động.