Dải ô được đặt tên động trong Excel là một trong những tính năng của Excel mà không nhiều người dùng Excel biết đến. Bài viết này sẽ làm cho Dải ô được đặt tên động gần gũi hơn với bạn. Trong các dòng tiếp theo, các gợi ý và thủ thuật liên quan đến Dải ô được đặt tên Động trong Excel sẽ được hiển thị.
Dải ô được đặt tên động với Hàm OFFSET của Excel
Để tạo phạm vi động, chức năng Bù đắp có thể được sử dụng.
Hàm bù đắp của Excel
Đây là cú pháp của hàm OFFSET trong Excel:
OFFSET (tham chiếu, hàng, cột, [chiều cao], [chiều rộng])
![Dải ô được đặt tên động trong Excel [4 cách]](/article/uploadfiles/202210/2022103116295381.png)
Ý nghĩa của các đối số trong hàm:
- Tham chiếu - Tham chiếu mà bạn muốn làm cơ sở cho phần bù.
- Hàng - Số hàng, lên hoặc xuống từ ô tham chiếu.
- Cols - Số cột, phải hoặc trái từ ô tham chiếu.
- Chiều cao, chiều rộng - Xác định chiều cao và chiều rộng của vùng chọn từ ô được tham chiếu.
Dải ô được đặt tên động với hàm OFFSET
Trong hộp thoại Trình quản lý tên , nó có thể được sử dụng để xác định Phạm vi được đặt tên động.
Đây là cách bạn có thể làm điều đó:
Đi tới Công thức trong tab Tên được xác định nhóm lệnh, nhấp vào Xác định tên và bạn sẽ nhận được Tên mới hộp thoại như thế này:
![Dải ô được đặt tên động trong Excel [4 cách]](/article/uploadfiles/202210/2022103116295379.png)
Một Dải ô được Đặt tên Động sẽ được tạo để chọn tất cả một dải ô bắt đầu một ô thấp hơn A1, trong cùng một cột và chọn số cột và hàng được cho trong các ô K1 và K2, từ ví dụ của hình ảnh bên dưới:
![Dải ô được đặt tên động trong Excel [4 cách]](/article/uploadfiles/202210/2022103116295361.png)
Công thức trong trường "Tham chiếu đến" trông giống như sau:
=OFFSET (Trang tính1! $ A $ 1; 1; 0; Trang tính1! $ K $ 1; Trang tính1! $ K $ 2)
Tất cả các phần tử trong hàm OFFSET có thể được tạo động. Ví dụ:tôi muốn chọn một số ngày cho một số ngày cụ thể.
Dưới đây là một số kết quả đầu ra:
=OFFSET (Sheet1! $ A $ 1; 1; 0; 6; 2) =Nó sẽ tham chiếu đến phạm vi A2:B7
![Dải ô được đặt tên động trong Excel [4 cách]](/article/uploadfiles/202210/2022103116295367.png)
Công thức sẽ phụ thuộc vào các mục nhập trong K1, K2, K3 và K4 sẽ giống như sau:
=OFFSET (Trang tính1! $ A $ 1; Trang tính1! $ K $ 3; Trang tính1! $ K $ 4; Trang tính1! $ K $ 1; Trang tính1! $ K $ 2)
Như bất kỳ hàm nào khác trong Excel, OFFSET cũng có thể được kết hợp với các hàm khác cung cấp số. Giả sử rằng tôi muốn chọn tất cả các ngày Thứ Ba ngoại trừ một ngày trong tuần trước, bằng cách lấy Thứ Hai làm tham chiếu. Dựa trên cùng một ví dụ, công thức sẽ giống như sau:
=OFFSET (Trang tính1! $ A $ 1; 1; 1; COUNT (Trang tính1! $ B:$ B); 1)
Đọc thêm:Dải ô được đặt tên động trong Excel dựa trên giá trị ô (5 cách dễ dàng)
Bài đọc tương tự
- Dải động Excel dựa trên giá trị ô
- VBA trong Excel:Phạm vi động dựa trên giá trị ô (3 phương pháp)
- Cách sử dụng dải động cho hàng cuối cùng với VBA trong Excel (3 phương pháp)
Dải ô được đặt tên động với hàm INDEX
Các chức năng khác cũng có thể được sử dụng cho Phạm vi được đặt tên động, chẳng hạn như INDEX - bạn muốn chọn tất cả các “Thứ Hai”, bất kể có bao nhiêu trong số đó:
=Trang tính1! $ A $ 2:INDEX (Trang tính1! $ A:$ A; COUNTA (Trang tính1! $ A:$ A))
Nếu bạn cũng muốn làm cho tham chiếu động, bạn có thể sử dụng kết hợp các công thức. Giả sử bạn muốn nhập tên của một ngày vào một ô, sau đó chọn tất cả các ngày trong bảng thuộc về ngày đó.
=OFFSET (INDIRECT (ADDRESS (2; MATCH (Sheet1! $ K $ 6; Sheet1! $ 1:$ 1; 0))); 0; 0; COUNT (Trang tính1! $ A:$ A); 1)
Đọc thêm:Excel OFFSET Dải động Nhiều cột theo cách hiệu quả
Dải ô được đặt tên động với VBA
Trong công việc hàng ngày của họ, các chuyên gia phải đối mặt với các tình huống mà họ cần thực hiện lặp đi lặp lại cùng một hoạt động. Điều tương tự cũng có thể xảy ra khi tạo Dải được đặt tên động. Để tránh các tác vụ lặp lại, VBA cũng có thể được sử dụng cho việc này.
VBA hoạt động chính xác theo cách giống như công thức, chỉ cần bạn làm theo cú pháp đặc trưng để thêm Dải ô được đặt tên.
Cú pháp như sau:
ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”THAY ĐỔI MÀ BẠN MUỐN CHỌN”
Ví dụ, một trong những ví dụ trước có thể dễ dàng áp dụng trong VBA. Một macro hoàn chỉnh sẽ giống như sau:
Đặt tên phụ ()
ActiveWorkbook.names.Add Name:=”NAME9 ″, RefersTo:=” =OFFSET (Sheet1! $ A $ 1,0,1, counta (A:A), 2) ”
Kết thúc phụ
Đọc thêm:Tạo phạm vi được đặt tên động với VBA trong Excel (Hướng dẫn từng bước)
Dải ô được đặt tên động có ô trống
Bạn thường có thể gặp phải các vấn đề khi không điền tất cả các ô, hay nói cách khác, một số ô trống. Điều quan trọng nhất là biết các định nghĩa của công thức được sử dụng để tìm tham chiếu. Ví dụ:COUNTA là một hàm đếm tất cả KHÔNG CÓ BLANK! ô trong một phạm vi. Tùy thuộc vào việc bạn có muốn bao gồm khoảng trống hay không.
Hình ảnh dưới đây cho thấy tình huống với một ô trống:
![Dải ô được đặt tên động trong Excel [4 cách]](/article/uploadfiles/202210/2022103116295332.png)
Nếu bạn muốn chọn tất cả “Thứ Hai”, thì sử dụng công thức như trong các ví dụ trước sẽ chọn Thứ Hai hàng tuần nhưng là ngày cuối cùng, vì có một ô trống - A5. Giải pháp cho điều này sẽ là sử dụng một công thức khác - sự kết hợp của công thức:
=OFFSET (Sheet1! $ A $ 1; 1; 0; SUMPRODUCT (MAX ((Sheet1! $ A:$ A <> ””) * ROW (Sheet1! $ A:$ A))) - 1; 1 )
Nó sẽ chọn tất cả các ô từ A2 đến A7.
Các dòng và ví dụ trước sẽ giúp bạn thực hiện công việc của mình nhanh hơn và hiệu quả hơn. Dãy được đặt tên động đại diện cho một khu vực rộng lớn với nhiều tùy chọn để khám phá và có thể là một công cụ hữu ích, tùy thuộc vào kỹ năng và sự sáng tạo của bạn. Nó chỉ ra rằng có nhiều cách kết hợp và việc tìm ứng dụng của chúng là tùy thuộc vào bạn.
Đọc thêm:Tạo phạm vi tổng động dựa trên giá trị ô trong Excel (4 cách)
Tải xuống tệp làm việc
Tải xuống tệp làm việc từ liên kết bên dưới:
Các bài viết có liên quan
- Cách sử dụng VBA dải động trong Excel (11 cách)
- Danh sách thả xuống xác thực dữ liệu với dải động của bảng Excel
- Tạo Phạm vi Biểu đồ Động trong Excel (2 Phương pháp)
- Cách tạo Dải số trong Excel (3 Phương pháp Dễ dàng)