Biểu đồ theo ô đã chọn

Giả sử rằng bạn và tôi cần trực quan hóa dữ liệu từ bảng sau với giá trị doanh số bán xe của các quốc gia khác nhau vào năm 2021 (nhân tiện, dữ liệu thực được lấy từ đây):

Vì số lượng chuỗi dữ liệu (quốc gia) lớn, cố gắng nhồi nhét tất cả chúng vào một biểu đồ cùng một lúc sẽ dẫn đến một “biểu đồ mì Ý” khủng khiếp hoặc xây dựng các biểu đồ riêng biệt cho từng chuỗi, điều này rất cồng kềnh.

Một giải pháp thanh lịch cho vấn đề này có thể là vẽ biểu đồ chỉ trên dữ liệu từ hàng hiện tại, tức là hàng có ô hiện hoạt:

Thực hiện điều này rất dễ dàng - bạn chỉ cần hai công thức và một macro nhỏ trong 3 dòng.

Bước 1. Số dòng hiện tại

Điều đầu tiên chúng ta cần là một phạm vi được đặt tên để tính toán số hàng trên trang tính nơi ô hiện hoạt của chúng ta hiện đang nằm. Mở trên một tab Công thức - Trình quản lý Tên (Công thức - Trình quản lý tên), nhấn nút Tạo (Tạo ra) và nhập cấu trúc sau vào đó:

Bước 2. Liên kết đến tiêu đề

Để hiển thị quốc gia đã chọn trong tiêu đề và chú thích của biểu đồ, chúng ta cần lấy tham chiếu đến ô có tên (quốc gia) từ cột đầu tiên. Để làm điều này, chúng tôi tạo một cục bộ khác (tức là Khu vực = trang tính hiện tại, không phải Sách!) một dải ô được đặt tên với công thức sau:

Ở đây, hàm INDEX chọn từ một phạm vi nhất định (cột A, nơi các quốc gia ký kết của chúng tôi nằm) một ô có số hàng mà chúng tôi đã xác định trước đó.

Bước 3. Liên kết đến dữ liệu

Bây giờ, theo cách tương tự, chúng ta hãy lấy liên kết đến một phạm vi có tất cả dữ liệu bán hàng từ hàng hiện tại, nơi đặt ô hiện hoạt. Tạo một dải ô được đặt tên khác với công thức sau:

Ở đây, đối số thứ ba, là XNUMX, khiến INDEX không trả về một giá trị nào mà là kết quả toàn bộ hàng.

Bước 4. Thay thế các liên kết trong biểu đồ

Bây giờ hãy chọn tiêu đề bảng và hàng đầu tiên có dữ liệu (phạm vi) và xây dựng biểu đồ dựa trên chúng bằng cách sử dụng Chèn - Biểu đồ (Chèn - Biểu đồ). Nếu bạn chọn một hàng có dữ liệu trong biểu đồ, thì hàm sẽ được hiển thị trong thanh công thức HÀNG (LOẠT) là một hàm đặc biệt mà Excel tự động sử dụng khi tạo bất kỳ biểu đồ nào để tham chiếu đến dữ liệu và nhãn gốc:

Hãy cẩn thận thay thế các đối số (chữ ký) và thứ ba (dữ liệu) trong hàm này bằng tên của các phạm vi của chúng ta từ bước 2 và 3:

Biểu đồ sẽ bắt đầu hiển thị dữ liệu bán hàng từ hàng hiện tại.

Bước 5. Macro tính toán lại

Các liên lạc cuối cùng vẫn còn. Microsoft Excel chỉ tính toán lại các công thức khi dữ liệu trên trang tính thay đổi hoặc khi một phím được nhấn F9và chúng tôi muốn tính toán lại xảy ra khi lựa chọn thay đổi, tức là khi ô hiện hoạt được di chuyển trên trang tính. Để thực hiện việc này, chúng ta cần thêm một macro đơn giản vào sổ làm việc của mình.

Nhấp chuột phải vào tab bảng dữ liệu và chọn lệnh nguồn (Mã nguồn). Trong cửa sổ mở ra, nhập mã của trình xử lý macro cho sự kiện thay đổi lựa chọn:

Như bạn có thể dễ dàng hình dung, tất cả những gì nó làm là kích hoạt tính toán lại trang tính bất cứ khi nào vị trí của ô hiện hoạt thay đổi.

Bước 6. Đánh dấu dòng hiện tại

Để rõ ràng, bạn cũng có thể thêm quy tắc định dạng có điều kiện để đánh dấu quốc gia hiện đang được hiển thị trên biểu đồ. Để làm điều này, hãy chọn bảng và chọn Trang chủ - Định dạng có điều kiện - Tạo quy tắc - Sử dụng công thức để xác định ô cần định dạng (Trang chủ - Định dạng có điều kiện - Quy tắc mới - Sử dụng công thức để xác định ô cần định dạng):

Ở đây, công thức sẽ kiểm tra mỗi ô trong bảng xem số hàng của nó có khớp với số được lưu trữ trong biến TekRow không và nếu có khớp, thì thao tác điền với màu đã chọn sẽ được kích hoạt.

Đó là nó - đơn giản và đẹp, phải không?

Chú ý

• Trên các bảng lớn, tất cả vẻ đẹp này có thể làm chậm lại - định dạng có điều kiện là một thứ tốn nhiều tài nguyên và việc tính toán lại cho mỗi lựa chọn cũng có thể rất nặng.
• Để ngăn dữ liệu biến mất trên biểu đồ khi một ô vô tình được chọn ở trên hoặc dưới bảng, bạn có thể thêm kiểm tra bổ sung cho tên TekRow bằng cách sử dụng các hàm IF lồng nhau của biểu mẫu:

  =IF(CELL(“hàng”)<4,IF(CELL("hàng")>4,CELL(“hàng”)))
  

• Đánh dấu các cột được chỉ định trong biểu đồ
• Cách tạo biểu đồ tương tác trong Excel
• Lựa chọn tọa độ