Plage Tên Động (Dynamic Named Range) trong Excel là một tính năng mà không phải người dùng Excel nào cũng biết. Bài viết này sẽ làm cho Plage Tên Động gần gũi hơn với bạn. Ở các phần tiếp theo, các mẹo và thủ thuật liên quan đến Plage Tên Động Excel sẽ được trình bày.
Plage Tên Động Với Hàm OFFSET Của Excel
Để tạo một plage động, hàm Offset có thể được sử dụng.
Hàm Offset Của Excel
Dưới đây là cú pháp của hàm OFFSET trong Excel:
OFFSET (reference, rows, cols, [height], [width])
Ý nghĩa các đối số trong hàm:
- Reference – Tham chiếu từ đó bạn muốn cơ sở cho offset.
- Rows – 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.
- Height, width – Xác định chiều cao và chiều rộng của lựa chọn, từ ô được tham chiếu.
Plage Tên Động Với Hàm OFFSET
Trong hộp thoại Name Manager, nó có thể được dùng để định nghĩa Plage Tên Động.
Cách thực hiện như sau:
Đi đến tab Formulas, trong nhóm lệnh Defined Names, nhấp vào lệnh Define name, và bạn sẽ nhận được hộp thoại New Name như sau:
Một Plage Tên Động sẽ được tạo để chọn tất cả phạm vi ô bắt đầu thấp hơn một ô so với A1, cùng cột, và chọn số cột và hàng cho trước trong ô K1 và K2, từ ví dụ của hình ảnh bên dưới:
Công thức trong trường "Refers to" trông như sau:
=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)
Tất cả các yếu tố trong hàm OFFSET có thể được làm động. Ví dụ, tôi muốn chọn một số ngày cho một ngày cụ thể nào đó.
Dưới đây là một số kết quả:
=OFFSET (Sheet1!$A$1; 1; 0; 6; 2) = Sẽ tham chiếu phạm vi A2: B7
Công thức phụ thuộc vào các giá trị nhập trong K1, K2, K3 và K4 nên trông như sau:
=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)
Giống như bất kỳ hàm nào khác trong Excel, OFFSET có thể kết hợp với các hàm khác cung cấp kết quả là số. Giả sử tôi muốn chọn tất cả các ngày thứ Ba trừ ngày của tuần cuối cùng, lấy thứ Hai làm tham chiếu. Dựa trên cùng ví dụ, công thức nên trông như sau:
=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)
Đọc Thêm: Plage Tên Động Excel Dựa Trên Giá Trị Ô (5 Cách Dễ Dàng)
Các Bài Đọc Tương Tự
- Plage Động Excel Dựa Trên Giá Trị Ô
- Excel VBA: Plage Động Dựa Trên Giá Trị Ô (3 Phương Pháp)
- Cách Sử Dụng Plage Động Cho Hàng Cuối Cùng Với VBA Trong Excel (3 Phương Pháp)
Plage Tên Động Với Hàm INDEX
Các hàm khác cũng có thể dùng cho Plage Tên Động, như INDEX, ví dụ – bạn muốn chọn tất cả các "Thứ Hai", không quan tâm có bao nhiêu:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))
Nếu bạn muốn làm tham chiếu động cũng vậy, bạn có thể dùng kết hợp công thức. Giả sử bạn muốn nhập tên một ngày trong một ô, sau đó chọn tất cả các ngày trong bảng thuộc ngày đó.
=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)
Đọc Thêm: Plage Động OFFSET Nhiều Cột Trong Excel Cách Hiệu Quả
Plage Tên Động Với VBA
Trong công việc hàng ngày, các chuyên gia đối mặt với tình huống cần thực hiện cùng một thao tác lặp đi lặp lại. Cùng chuyện có thể xảy ra khi tạo Plage Tên Động. Để tránh các tác vụ lặp lại, VBA có thể được dùng cho việc này.
VBA hoạt động chính xác như công thức, chỉ cần tuân theo cú pháp đặc trưng cho việc thêm Plage Tên.
Cú pháp như sau:
ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE THAT YOU WANT TO SELECT”
Ví dụ, một trong các ví dụ trước có thể dễ dàng áp dụng trong VBA. Một macro hoàn chỉnh sẽ trông như sau:
Sub naming()
ActiveWorkbook.names.Add Name:=”NAME9″, RefersTo:=”=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)”
End sub
Đọc Thêm: Tạo Plage Tên Động Với VBA Trong Excel (Hướng Dẫn Bước Để Bước)
Plage Tên Động Với Ô Trống
Bạn thường gặp vấn đề khi không phải tất cả ô đều được điền, hoặc nói cách khác, một số ô là trống. Quan trọng nhất là biết định nghĩa của công thức dùng để tìm tham chiếu. Ví dụ, COUNTA là hàm đếm tất cả ô KHÔNG TRỐNG! trong một phạm vi. Tùy thuộc vào việc bạn có muốn bao gồm ô trống hay không.
Hình ảnh bên dưới cho thấy tình huống có ô trống:
Nếu bạn muốn chọn tất cả các "Thứ Hai", thì dùng công thức như ví dụ trước sẽ chọn mọi thứ Hai trừ cái cuối cùng, vì có ô trống – A5. Giải pháp cho việc này là dùng công thức khác – tổ hợp 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ả ô từ A2 đến A7.
Các dòng và ví dụ trước nên giúp bạn thực hiện công việc nhanh hơn và hiệu quả hơn. Plage Tên Động đại diện cho một lĩnh vực rộng lớn với nhiều tùy chọn để khám phá và có thể là 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. Đã chỉ ra rằng có nhiều tổ hợp, và tùy thuộc vào bạn để tìm ứng dụng của chúng.
Đọc Thêm: Tạo Plage Tổng Động Dựa Trên Giá Trị Ô Trong Excel (4 Cách)
Tải Tệp Làm Việc
Tải tệp làm việc từ liên kết bên dưới:
Các Bài Viết Liên Quan
- Cách Sử Dụng Plage Động VBA Trong Excel (11 Cách)
- Danh Sách Thả Xuống Data Validation Với Plage Động Bảng Excel
- Tạo Plage Biểu Đồ Động Trong Excel (2 Phương Pháp)
- Cách Tạo Dãy Số Trong Excel (3 Phương Pháp Dễ Dàng)