Cách đặt tên cho ô hoặc vùng dữ liệu Excel

Các công thức trong Excel thường sử dụng dữ liệu và giá trị từ những ô khác bằng cách sử dụng tham chiếu đến các ô đó. Nếu bạn có nhiều công thức, các tham chiếu ô có thể gây nhầm lẫn, làm cho công thức của bạn khó hiểu.

Tính năng Defined Names trong Excel làm cho công thức, cũng như những dữ liệu khác bớt rối và dễ hiểu hơn. Thay vì tham chiếu một ô chứa giá trị hoặc công thức, hay phạm vi ô theo tiêu đề hàng và cột (A1, B2, v.v...), bạn có thể sử dụng một tên được xác định để gán cho ô hoặc phạm vi ô đó.

Hôm nay, Quantrimang.com sẽ giới thiệu cách làm việc với các phạm vi ô được đặt tên (named range), bao gồm tạo tên, quy tắc và phạm vi cho tên, cũng như cách chỉnh sửa, xóa, xem và sử dụng những tên này.

Phân biệt Defined Names và Table Names trong Excel

Đừng nhầm lẫn Defined Names với Table Names. Excel Table là tập hợp dữ liệu được lưu trữ trong các bản ghi (row/hàng) và các trường (column/cột). Excel gán tên mặc định (Table1, Table2, v.v...) cho mỗi bảng bạn tạo nhưng bạn có thể thay đổi nó nếu muốn.

Trước đây, Quantrimang.com đã giới thiệu cho bạn về bảng trong Excel và cung cấp một hướng dẫn kỹ lưỡng hơn về vấn đề này. Để tìm hiểu thêm về các bảng trong Excel, hãy xem trang hỗ trợ của Microsoft. 

support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664?ui=en-US&rs=en-US&ad=US

Quy tắc cho Defined Names trong Excel

Khi tạo Defined Names trong Excel, bạn phải tuân theo các quy tắc nhất định. Ví dụ, những tên này không thể chứa các khoảng trắng và ký tự đầu tiên phải là một chữ cái, dấu gạch dưới (_) hoặc dấu gạch chéo ngược (\).

Để biết danh sách đầy đủ các quy tắc, hãy xem phần “Learn about syntax rules for names” trên trang hỗ trợ của Microsoft.

support.office.com/en-us/article/Define-and-use-names-in-formulas-4D0F13AC-53B7-422E-AFD2-ABD7FF379C64#bmsyntax_rules_for_names

Phạm vi cho Defined Names trong Excel

Phạm vi của một Defined Name trong Excel đề cập đến nơi mà tên được nhận dạng không cần điều kiện, như tên trang tính hoặc file workbook. Mỗi tên có thể có phạm vi cấp độ bảng tính cục bộ hoặc cấp độ workbook.

Để biết thêm thông tin về vấn đề này, hãy xem phần “The scope of a name” trên trang hỗ trợ của Microsoft (link phía trên).

Tạo một Named Range trong Excel

Bạn có một vài tùy chọn khác nhau để tạo Named Range trong Excel. Bạn có thể xác định tên bằng hộp Name trên thanh Formula, sử dụng văn bản ô tiêu đề cho một nhóm các ô đã chọn hoặc sử dụng hộp thoại New Name.

Tên không phải tham chiếu đến các ô. Bạn có thể sử dụng tên để gắn nhãn cho một giá trị hoặc công thức bạn sử dụng ở nhiều nơi trong workbook.

Khi xác định tên cho một ô hoặc phạm vi ô, tham chiếu ô tuyệt đối được sử dụng theo mặc định.

Xác định tên bằng hộp Name

Sử dụng hộp Name là cách dễ nhất để xác định tên cho một nhóm ô. Xác định tên bằng hộp Name chỉ tạo tên với phạm vi cấp workbook, nghĩa là tên có thể được tham chiếu ở bất kỳ đâu trong workbook mà nó được tạo, không cần thêm bất kỳ tên trang tính hoặc tên file workbook nào.

Bài viết sẽ đề cập đến một phương pháp khác, cho phép bạn tạo các tên cấp độ bảng tính trong phần sau.

Để sử dụng hộp Name xác định tên, hãy làm như sau:

1. Chọn ô hoặc phạm vi ô bạn muốn đặt tên.

2. Nhập tên bạn muốn vào hộp Name ở bên trái của thanh Formula và nhấn Enter.

Hãy chắc chắn rằng bạn làm theo các quy tắc cho tên được đề cập trước đó.

Xác định tên từ phần văn bản trong tiêu đề của ô

Nếu đã thêm các tiêu đề hàng hoặc cột vào dữ liệu của mình, bạn có thể sử dụng các tiêu đề này làm tên.

Để xác định tên từ một tiêu đề, hãy làm như sau:

1. Chọn các ô bạn muốn đặt tên bao gồm nhãn trên hàng hoặc cột.

2. Trên tab Formulas, bấm vào Create from Selection trong phần Defined Names hoặc nhấn Ctrl+Shift+F3.

Nếu nhãn chứa khoảng trắng hoặc các ký tự không hợp lệ khác như ký hiệu (&), chúng sẽ được thay thế bằng dấu gạch dưới.

Trên hộp thoại Create Names from Selection, chọn vị trí của nhãn tiêu đề bạn muốn sử dụng làm tên.

Ví dụ, nhãn tiêu đề trong hình trên nằm ở đầu cột được chọn. Vì vậy, ví dụ tích vào hộp Top row và nhấn OK.

Tên này là một tên cấp độ workbook và áp dụng cho tất cả các ô được chọn ngoại trừ ô nhãn tiêu đề. Khi chọn các ô được đề cập bằng tên, không có ô nhãn tiêu đề, bạn sẽ thấy tên trong hộp Name.

Xác định tên bằng hộp thoại New Name

Hộp thoại New Name cung cấp nhiều tùy chọn hơn khi tạo tên, như chỉ định phạm vi cấp độ bảng tính hoặc thêm nhận xét vào tên. Ngoài ra, hộp thoại New Name cho phép bạn xác định tên cho một giá trị hoặc công thức.

Để sử dụng hộp thoại New Name đặt tên cho một ô hoặc phạm vi ô, hãy bắt đầu với các mục sau:

1. Nếu bạn đang xác định tên cho một ô hoặc phạm vi ô, hãy chọn các ô muốn đặt tên. Nếu xác định tên cho một giá trị hoặc công thức, bạn có thể chọn bất cứ ô nào.

2. Trên tab Formulas, bấm Define Name trong phần Defined Names.

Trên hộp thoại New Name, chỉ định thông tin sau:

• Name: Nhập tên theo quy tắc cho tên được xác định.
• Scope: Theo mặc định, Workbook được chọn làm phạm vi cho tên. Nếu bạn muốn đưa ra tên phạm vi cấp bảng tính, hãy chọn bảng tính bạn muốn từ danh sách drop-down Scope.
• Comment: Thêm bất kỳ ghi chú nào bạn muốn vào tên.
• Refers to: Các ô hiện được chọn và tên của trang tính (hoặc tab) hiện tại đang được tự động nhập vào hộp Refers to. Nếu bạn xác định tên cho một giá trị hoặc công thức, hãy thay thế những gì trong hộp Refers to bằng dấu bằng (=), theo sau là giá trị hoặc công thức.

Nút ở bên phải của hộp Refers to cho phép bạn thu nhỏ hộp thoại New Name và chọn các ô trên trang tính để nhập một phạm vi ô. Bài viết sẽ nói nhiều hơn về cách sử dụng nút đó trong phần tiếp theo, “Chỉnh sửa một Named Range”.

Khi bạn đã hoàn tất việc nhập thông tin cho tên, nhấp OK để quay lại bảng tính.

Chỉnh sửa Named Range trong Excel

Sau khi xác định tên, bạn có thể thay đổi tên, nhận xét và tham chiếu của nó.

Để chỉnh sửa Named Range, nhấp vào Name Manager trong phần Defined Names của tab Formulas.

Nếu có một danh sách dài các tên, bạn có thể lọc chúng để tìm tên muốn chỉnh sửa.

Bấm Filter ở góc trên bên phải của hộp thoại Name Manager. Sau đó, chọn loại tên bạn muốn hiển thị. Bạn có thể chọn nhiều bộ lọc bằng cách nhấp vào Filter một lần nữa và chọn tùy chọn khác.

Để xóa tất cả các bộ lọc và hiển thị lại tất cả các tên, nhấp vào Filter và chọn Clear Filter.

Để thay đổi tên hoặc nhận xét, chọn tên bạn muốn thay đổi và nhấp vào Edit.

Thay đổi Name, Comment hoặc cả hai, và bấm OK.

Bạn không thể thay đổi Scope (Phạm vi) của một tên hiện có. Hãy xóa tên và xác định lại một lần nữa phạm vi chính xác.

Bạn có thể thay đổi các ô trong Refers to trên hộp Edit Name. Nhưng bạn cũng có thể làm điều này trực tiếp trên hộp thoại Name Manager (tìm hiểu trong phần tiếp theo). Phương thức thay đổi tham chiếu ô Name Manager giống nhau trên cả hai hộp thoại.

Để thay đổi ô hoặc phạm vi ô mà tên này đề cập đến trong hộp thoại Name Manager, trước tiên hãy chọn tên bạn muốn thay đổi tham chiếu ô. Sau đó, nhấp vào nút mũi tên lên ở bên phải của hộp Refers to.

Hộp thoại Name Manager thu nhỏ xuống chỉ còn hộp Refers to. Để thay đổi tham chiếu ô, hãy làm như sau:

1. Chọn ô hoặc phạm vi ô trên bảng tính.

2. Nhấp vào nút ở bên phải của hộp Refers to.

Bạn sẽ thấy tham chiếu ô mới trong hộp Refers to và hộp thoại Name Manager được khôi phục đầy đủ.

Để chấp nhận tham chiếu ô mới, nhấp vào nút tích màu xanh lục. Hoặc để hoàn nguyên thay đổi trở lại tham chiếu ô ban đầu, nhấp vào nút X màu đen.

Bấm Close để đóng hộp thoại Name Manager.

Xem tất cả Defined Name trong workbook Excel

Hộp thoại Name Manager liệt kê tất cả các tên cấp độ bảng tính và cấp độ workbook mà bạn đã xác định trong workbook, cho dù bảng tính nào đang hoạt động. Nhưng trong khi hộp thoại mở, bạn không thể làm việc trên bảng tính của mình.

Sẽ có ích nếu có một danh sách các tên trực tiếp trên bảng tính hiện tại. Bằng cách đó, bạn có thể tự do di chuyển xung quanh và quyết định tên nào cần chỉnh sửa hoặc tên nào muốn xóa trong khi tham khảo danh sách của mình.

Bạn có thể tạo danh sách trực tiếp trên một vùng trống của bảng tính. Danh sách này bao gồm tên cấp độ bảng tính và workbook có phạm vi trong bảng tính hiện đang hoạt động.

Để bắt đầu tạo danh sách tên, hãy làm như sau:

1. Xác định vị trí một vùng trống của bảng tính hiện tại nơi có sẵn hai cột và chọn ô sẽ là góc trên bên trái của danh sách.

2. Trong phần Defined Names của tab Formula, bấm Use in Formula và chọn Paste Names hoặc nhấn F3.

Trên hộp thoại Paste Names, bấm Paste List.

Tên, tham chiếu ô, giá trị và công thức tương ứng của chúng được dán vào các ô trên trang tính.

Bây giờ, bạn có thể xem lại danh sách của mình và quyết định phải làm gì với các tên trong đó. Bạn có thể xóa danh sách này trên bảng tính một khi không cần nó nữa.

Xóa Name Range trong Excel

Nếu có một số tên không sử dụng nữa, thì tốt nhất là xóa chúng đi. Nếu không, danh sách tên sẽ bị lộn xộn và khó quản lý.

Để mở Name Manager, bấm vào Name Manager trong phần Defined Names của tab Formulas.

Trên hộp thoại Name Manager, chọn tên bạn muốn xóa và nhấp vào Delete. Nhấn OKtrên hộp thoại xác nhận.

Sử dụng tên để điều hướng nhanh trong Excel

Nếu tên đề cập đến một phạm vi ô, bạn có thể sử dụng tên đó để nhanh chóng điều hướng đến và chọn phạm vi ô.

Để chuyển đến một phạm vi được đặt tên, nhấp vào mũi tên xuống trên hộp Name ở bên trái của thanh Formula và chọn tên bạn muốn.

Tên cấp độ bảng tính chỉ hiển thị trong danh sách drop-down của hộp Name, nếu bảng tính chúng được tạo trong đó là bảng tính hiện đang hoạt động.

Ngoài ra, bạn có thể nhập tên cho một phạm vi ô muốn chuyển đến trong hộp Name và nhấn Enter. Nhưng hãy chắc chắn rằng bạn đã xác định tên. Nếu không, bạn sẽ áp dụng tên đó cho ô hoặc phạm vi ô hiện được chọn.

Sử dụng tên trong công thức Excel

Sử dụng Defined Name trong công thức làm cho nó dễ hiểu hơn.

Có một số cách bạn có thể nhập tên vào công thức. Nếu biết chính xác tên muốn sử dụng, bạn có thể nhập tên trực tiếp vào công thức.

Bạn cũng có thể sử dụng Formula AutoComplete. Khi nhập công thức, Excel sẽ tự động liệt kê các tên hợp lệ và bạn có thể chọn một tên để nhập nó vào công thức. Ví dụ, tên Total được đề xuất trong bảng tính sau vì phạm vi ô được chọn có tên đó.

Bạn cũng có thể bắt đầu nhập công thức của mình và sau đó nhấp vào Use in Formulatrong phần Defined Names của tab Formula. Sau đó, chọn tên bạn muốn sử dụng trong công thức từ danh sách drop-down.

Phương pháp cuối cùng để sử dụng tên trong công thức là bắt đầu nhập công thức của bạn và nhấn F3. Sau đó, chọn tên bạn muốn sử dụng trên hộp thoại Paste Name và bấm OK, hoặc nhấn Enter.

Defined Names trong Excel có thể giúp giữ cho worksheet của bạn ngăn nắp và dễ hiểu. Hãy sử dụng tên này cho không chỉ phạm vi ô mà còn cho các giá trị và công thức bạn sử dụng thường xuyên.

Chúc bạn thực hiện thành công!