Hướng dẫn cách tạo Named Range động trong Excel

Kỹ thuật sử dụng Named Range động (Relative Named Range) rất hữu dụng khi các bạn tạo những báo cáo và Dashboard bởi tính chất không cần thay đổi tham chiếu khi dữ liệu tăng lên hay giảm đi. Bởi với kỹ thuật tạo Named Range động trong Excel, bạn không cần phải thay đổi tham chiếu mỗi khi dữ liệu thay đổi nữa. Hôm nay, Gihito.com sẽ hướng dẫn bạn cách thiết lập và áp dụng Named Range động trong Excel nhé!

Thiết lập Named Ranges

Đầu tiên, bạn hãy nhớ lại cách tạo Named Ranges cơ bản. Bởi vì nó có liên quan mật thiết tới cách tạo Named Ranges động. Tạo Named Ranges cơ bản thì bạn sẽ thiết lập được Named Ranges “động” 1 cách dễ dàng.

Nhấn thẻ Fomulas trên thanh công cụ => chọn Name Manager

Cửa sổ Name Manager xuất hiện => Nhấn New

Sau đó, cửa sổ New Name sẽ mở ra

Có vài option quan trọng bạn cần biết:

• Name: Nhập tên vùng bạn muốn đặt vào
• Scope: Phạm vi sử dụng tên. Với scope workbook, bạn có thể áp dụng tên vừa đặt cho toàn bộ các trang tính. Với các scope nhỏ hơn như các sheet, bạn chỉ có thể áp dụng cho riêng sheet đó mà thôi
• Comment: Thêm thông tin liên quan đến vùng đặt tên (không bắt buộc).
• Refers to: Vùng muốn áp dụng tên.

Hoàn thành xong việc đặt tên, bạn nhấn OK để áp dụng.

Ta thấy ở ví dụ trên, tên vùng =nameRange (mục “Name”) được gán với vùng =Sheet1$A$1:$H$10 (mục “Refers to”) cố định.

Thiết lập vùng tham chiếu động

Excel sẽ tự động nhập ký hiệu $ (để cố định tham chiếu đến một ô nhất định) dù là không có nhu cầu nào đòi hỏi phải dùng kí hiệu này. Một Named Range có thể được thiết lập có giá trị như ở mục Refers to là =Sheet1$A$1:$H$10. Không có kí hiệu $ thì lúc này, Named Range sẽ trở nên “động”. Vùng đã được thiết lập “động” với ô được chọn tại thời điểm lúc bạn tạo Named Range.

Để chứng minh, bây giờ mình chọn ô C4 rồi đặt tên vùng A1-B5 (không có kí hiệu $).

Bằng cách sử dụng Named Range trong công thức dưới đây với vùng ô được chọn (A1-B5), vùng named sẽ auto dời xuống 1 ô.

Giá trị nhỏ nhất trong Named Range là 1 (ô A1) nhưng sau khi tính toán, kết quả lại là 2. Tại vì sao? Bởi Named Range đã “động”, tức là linh động di chuyển xuống 1 ô.

Ta cũng có thể ghép khớp với ký hiệu $ để cố định cột/hàng cụ thể.

Xem thêm: Cách cố định tọa độ tham chiếu trong Excel

Áp dụng Named Range động

Liệu Named Range động có thật sự hữu ích trong công việc? Dưới đây là một vài ý tưởng về việc áp dụng Named Range “động”.

Luôn luôn tham chiếu đến ô bên trên

Nếu bạn có công thức mà luôn tham chiếu đến ô bên trên thì điều gì sẽ xảy ra khi bạn thêm 1 côt/hàng nữa?

Range trong công thức không hề thay đổi. Dù định dạng đã được sao lại nhưng công thức thì không.

Vì thế, Named Range động sẽ giải quyết vấn đề này giúp bạn.

Thêm bao nhiêu hàng/cột không quan trọng, công thức sẽ luôn tham chiếu trực tiếp đến ô bên trên.

Đơn giản hóa việc tính toán

Một trong những lợi ích chính của Named Range đó là đơn giản hóa việc tính toán. Ví dụ cho thấy tổng việc tính toán có thể được tạo ra và copied xuống.

Tên vùng D2-D5: totalCalc được gán với vùng =Sheet2!B2+Sheet2!C2. Excel đã tính ra từng kết quả khác cho mỗi ứng dụng của Named Range động.

Tính tổng lũy tiến trong Excel

Ngoài ra, Named Range động cũng có thể giúp bạn tính tổng dưới dạng lũy tiến được. Ví dụ dưới đây cho thấy ta có thể ghép giá trị tương đối và tuyệt đối của vùng ô được tham chiếu đến trong Named Range.

Tên vùng E2-E5: runningTotal được gán với vùng =Sheet2$D$2:$E$2.

Lưu ý

Ta không thể tạo Named Range động toàn cục được. Trong mỗi ví dụ ở trên thì tên của sheet được đặt cho Named Range, vì thế cần phải có 1 cái Named Range động mới cho mỗi Worksheet. Mình đã thấy đôi chút về việc sử dụng ký hiệu “!” mà không có tên sheet để tạo nên tính toàn cục. Tuy nhiên, điều này có thể gây ra 1 vài trạng thái bất thường và có thể dẫn đến Excel bị phá hủy (kết thúc chương trình/việc tính toán của bạn). Trong những trường hợp như thế, nếu muốn bạn có thể nghiên cứu bằng cách dùng hàm INDIRECT hoặc hàm OFFSET.

Hi vọng thủ thuật nhỏ này sẽ giúp ích cho các bạn trong công việc.