Xác định tên Excel
|
Tên đã xác định là một bộ mô tả văn bản mà bạn có thể sử dụng để mô tả ý nghĩa hoặc nội dung của một ô, một dải ô, một hằng số hoặc một công thức. Khi Tên đã xác định được thiết lập, bạn có thể sử dụng Tên đó ở bất cứ nơi nào bạn thường sử dụng địa chỉ ô thực tế hoặc nội dung khác. Điều này làm cho các công thức dễ hiểu và dễ bảo trì hơn nhiều. Ví dụ, công thức =G12*K15 không có ý nghĩa lắm trừ khi bạn tình cờ biết ý nghĩa của các ô G12 và K15. Tuy nhiên, nếu bạn gán Tên xác định cho G12 và K15, bạn có thể viết công thức tương tự như sau =Thu nhập*Mức thuế Rõ ràng, việc sử dụng Tên được xác định làm cho công thức dễ hiểu và dễ bảo trì hơn nhiều  Có hai cách để tạo Tên xác định (không bao gồm làm như vậy với mã VBA). Phương pháp đầu tiên là chọn ô hoặc phạm vi ô mà bạn muốn gán Tên. Sau đó, nhấp vào Hộp Tên và nhập Tên. Hộp tên là hình chữ nhật màu trắng ở bên trái tiêu đề cột "A" và phía trên tiêu đề hàng "1". Nếu bạn nhập vào Hộp tên một Tên đã được xác định, Excel sẽ hiển thị phạm vi được tham chiếu bởi Tên đó. Nó sẽ không ghi đè và thay đổi Tên đã xác định hiện có. Cách thứ hai để tạo Tên là chọn ô hoặc phạm vi ô mà bạn muốn gán Tên, vào menu Chèn, chọn Tên, rồi Xác định. Thao tác này sẽ hiển thị hộp thoại Tên được xác định. Ở đó, nhập Tên bạn muốn sử dụng. Trong hộp thoại đó, bạn cũng có thể thay đổi (các) ô mà Tên hiện tại đề cập đến Tên được xác định phải bắt đầu bằng một chữ cái hoặc dấu gạch dưới ( _ ) và chỉ bao gồm các chữ cái, số hoặc dấu gạch dưới. Khoảng trắng không được phép trong Tên đã xác định. Ngoài ra, Tên được xác định có thể không giống với tham chiếu ô hợp lệ. Ví dụ: tên AB11 không hợp lệ vì AB11 là tham chiếu ô hợp lệ. Tên không phân biệt chữ hoa chữ thường Giao diện người dùng để làm việc với Tên được xác định trong Excel 2003 trở về trước tương đối thô sơ. Jan Karel Pieterse đã tạo một add-in đẹp, có sẵn miễn phí tại www. quảng cáo jkp. com/officemarketplacenm-en. asp, điều đó cải thiện đáng kể trình quản lý Tên xác định cơ bản. Trình quản lý tên được xác định trong Excel 2007 trở lên đã được cải thiện rất nhiều so với các phiên bản trước Tên xác định không giới hạn tham chiếu một ô hoặc phạm vi ô. Tên có thể tham chiếu đến một hằng số, giá trị số hoặc giá trị văn bản. Nó cũng có thể đề cập đến một công thức. Ví dụ: bạn có thể có Tên được xác định của MaxRows với giá trị là 25, sau đó sử dụng tên đó ở bất kỳ đâu mà bạn sẽ sử dụng số đó. Sử dụng Tên có lợi thế là nếu bạn sử dụng nó ở nhiều vị trí và sau đó cần thay đổi giá trị, bạn chỉ cần thay đổi tên đó trong định nghĩa và giá trị mới sẽ được sử dụng ở tất cả các vị trí khác Khi bạn sử dụng Tên xác định trong một công thức hoặc ô, bạn không bao gồm dấu ngoặc đơn như khi sử dụng một hàm. Ví dụ: nếu bạn có một Tên được xác định là MyName với giá trị là Chip Pearson, bạn sẽ nhập vào các ô =MyName, không phải =MyName(). Sử dụng dấu ngoặc đơn sẽ gây ra lỗi #REF Tên xác định cũng có thể chứa công thức. Ví dụ: bạn có thể xác định Tên có tên là TheSum và gán công thức =SUM($A$1. $A$100) sang tên. Sau đó, bạn có thể sử dụng =TheSum ở bất cứ đâu bạn muốn để lấy tổng của A1. A100. Như đã lưu ý trước đây, bạn không sử dụng dấu ngoặc đơn trong tham chiếu đến tên. =TheSum() sẽ gây ra lỗi #VALUE. Bạn có thể sử dụng bất kỳ công thức nào bạn muốn trong Tên, nhưng bạn không thể chuyển tham số cho công thức được xác định bởi Tên Nếu bạn sử dụng một công thức trong Tên xác định, thì công thức đó được đánh giá như thể nó là một công thức mảng. Không có cách nào để buộc một công thức trong Tên xác định được đánh giá là công thức không phải mảng Thông thường, một Tên xác định có phạm vi toàn cầu. Điều này có nghĩa là Tên có thể được xác định trên bất kỳ trang tính nào và sau đó được tham chiếu trên bất kỳ trang tính nào trong sổ làm việc. Ví dụ: nếu bạn có một ô, chẳng hạn như A1 trên Trang tính 1, chứa ngày in cuối cùng và bạn đặt tên cho ô đó là LastPrintDate, thì bạn có thể sử dụng Tên LastPrintDate trên bất kỳ trang tính nào và ô này sẽ luôn tham chiếu trở lại A1 trên Trang tính 1 Tuy nhiên, có thể có các Tên nằm trong phạm vi chỉ một trang tính và bất kỳ hoặc tất cả các trang tính có thể có một tên trong phạm vi trang tính có cùng tên. Khi Tên đó được sử dụng trên một trang tính, giá trị của Tên trên trang tính đang sử dụng nó được đánh giá. Nếu bạn có nhiều tên trong phạm vi trang tính, trên các trang tính khác nhau, những tên đó không cần tham chiếu đến cùng một tham chiếu ô. Nghĩa là, tên SheetLastPrinted trên Sheet1 có thể tham chiếu đến A1 trên Sheet1 và tên SheetLastPrinted trên Sheet2 có thể tham chiếu đến K10 trên Sheet2. Khi tên SheetLastPrinted được sử dụng trên Sheet1, giá trị được lấy từ A1 trên Sheet1. Nếu Tên được sử dụng trên Sheet2, giá trị được lấy từ K10 trên Sheet2 Để tạo Tên trong phạm vi trang tính, hãy chọn ô cần đặt tên, mở Hộp thoại Tên được Xác định (menu Chèn, Tên, sau đó Xác định) và nhập tên là Trang tính 1. TheName (trong đó Sheet1 là tên của trang tính mà Tên sẽ được đặt trong phạm vi. Sự hiện diện của tên trang tính khiến Excel đặt tên trang tính trong phạm vi. Lặp lại quy trình này trên tất cả các trang tính cần sử dụng tên phạm vi trang tính. Khi xác định Tên, hãy đặt tên trang tính trong dấu nháy đơn nếu tên trang tính chứa khoảng trắng hoặc các ký tự không phải là số. e. g. , 'Tờ một'. Tên Một trong những tính năng rất hữu ích của Tên được xác định là phá vỡ một số hạn chế của Định dạng có điều kiện và Xác thực dữ liệu. Trong Định dạng Điều kiện, nếu bạn sử dụng phương pháp Công thức Là, thì tất cả các ô trong công thức phải nằm trên cùng một trang tính với ô được áp dụng Định dạng có Điều kiện. Ví dụ: nếu bạn đang áp dụng Định dạng có điều kiện cho ô A1 trên Trang tính 1, bạn sẽ không thể sử dụng công thức như = Trang tính 2. A10>100, vì công thức đề cập đến một ô trên một trang tính khác. Bạn có thể khắc phục hạn chế này bằng cách gán Tên cho ô trên trang tính khác -- e. g. , gán tên TestVal cho Sheet2. A10 -- rồi sử dụng tên đã xác định trong hộp thoại Định dạng có điều kiện. =TestVal>10 Phương thức tương tự này có thể được sử dụng cho danh sách giá trị trong ràng buộc Xác thực dữ liệu kiểu danh sách. Nếu danh sách các giá trị hợp lệ của bạn nằm trong phạm vi Sheet2. A1. A10, bạn không thể tham chiếu phạm vi đó làm nguồn của danh sách xác thực nếu ô đang được xác thực nằm trên một trang tính khác. Tuy nhiên, nếu bạn gán một Tên xác định cho phạm vi đó, e. g. , Danh sách hợp lệ, bạn có thể sử dụng =ValidList làm danh sách giá trị cho Xác thực dữ liệu Trong Excel 2003 trở về trước, hộp tên đã xác định không thể thay đổi kích thước và nó cắt ngắn phần hiển thị tên ở khoảng 16 ký tự. Vì vậy, nếu bạn có hai tên dài như SomeLongDefinedNameOne và SomeLongDefinedNameTwo, bạn không thể biết đó là tên nào trong hộp tên thả xuống. Tuy nhiên, sử dụng một chút mã, bạn có thể mở rộng độ rộng của danh sách thả xuống. Mã này sẽ không tự mở rộng hộp tên như khi nó xuất hiện trên thanh công thức, nhưng sẽ mở rộng danh sách thả xuống. Mã để làm điều này được hiển thị dưới đây Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long
Sub WidenNameBoxDrop2()
Dim Res As Long
Const CB_SETDROPPEDWIDTH = 352
Const cWidth = 400 '< Change To Desire Width in Pixels
Res = SendMessage( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString), _
CB_SETDROPPEDWIDTH, cWidth, 0)
End Sub
Đặt tất cả mã được hiển thị ở trên vào một số mô-đun mã, rồi trong thủ tục Auto_Open của bạn hoặc trong thủ tục Workbook_Open, hãy gọi thủ tục WidenNameBoxDrop2. Ví dụ, Sub Auto_Open()
WidenNameBoxDrop2
End Sub
Trong Excel 2007 trở lên, Hộp tên có kích thước khá lớn, vì vậy mã được cung cấp ở trên là không cần thiết Bạn có thể áp dụng Tên cho các công thức hiện có. Nếu công thức của bạn sử dụng tham chiếu ô thông thường và sau đó bạn gán Tên cho các ô đó, thì bạn có thể tự động cập nhật công thức để sử dụng Tên được xác định. Ví dụ: giả sử bạn có công thức =A5+D5. Nếu sau này bạn gán Tên CellOne cho A5 và CellTwo cho D5, bạn có thể chọn ô có công thức, vào menu Chèn, chọn Tên, rồi Áp dụng. Chọn các tên thích hợp trong danh sách hoặc chỉ cần chọn tất cả và nhấp vào OK. Excel sẽ thay đổi công thức thành =CellOne+CellTwo Bạn có thể sử dụng mã VBA để làm việc với các tên đã xác định. Để thêm tên, hãy sử dụng ThisWorkbook.Names.Add Name:="SomeName", _
RefersTo:=Worksheets("Sheet2").Range("A1:A10")
Điều này sẽ gán Tên SomeName cho phạm vi A1. A10 trên Sheet2. Nếu bạn thêm một Tên đã tồn tại, Tên đó sẽ tự động bị xóa và được tạo lại với tham chiếu mới. Không nhất thiết phải xóa Name trước khi tạo lại Theo mặc định, các tên được tạo thủ công hoặc bằng mã VBA sẽ hiển thị -- chúng sẽ xuất hiện trong Hộp Tên thả xuống và trong hộp thoại Tên. Tuy nhiên, bạn có thể ẩn tên để người dùng không nhìn thấy. Tên ẩn có thể được sử dụng theo bất kỳ cách nào giống như Tên hiển thị bình thường. Để ẩn tên các bạn set thông số Visible là False. Ví dụ, ThisWorkbook.Names.Add Name:="SomeName2", _
RefersTo:=Worksheets("Sheet2").Range("B1:B10"), _
Visible:=False
Tên SomeName2 có thể được sử dụng chính xác như một tên hiển thị, nhưng sẽ không xuất hiện với người dùng. Cách duy nhất để ẩn Tên đã xác định là thông qua mã VBA. Không có gì trong giao diện người dùng Excel cho phép bạn ẩn Tên Để xóa tên bằng mã VBA, hãy sử dụng mã như sau ThisWorkbook.Names("TheName").Delete
Sử dụng tên đã xác định trong mã VBA khác với việc sử dụng chúng trong các ô của trang tính. Bạn phải lấy giá trị của Tên bằng thuộc tính RefersTo hoặc RefersToRange. Nếu một Tên không đề cập đến một ô hoặc phạm vi ô (e. g. , nó đề cập đến một hằng số), nỗ lực sử dụng RefersToRange sẽ không thành công. Để nhận giá trị của Tên chứa hằng số, hãy sử dụng thuộc tính RefersTo V = ThisWorkbook.Names("TheName").RefersTo
Nếu tên đề cập đến một phạm vi từ 2 ô trở lên, bạn có thể gán nó cho một biến kiểu Phạm vi. Ví dụ, Dim R As Range
Set R = ThisWorkbook.Names("BigName").RefersToRange
Debug.Print R.Address
Nếu tên đề cập đến một hằng số, bạn sẽ cần loại bỏ một số ký tự để nhận được giá trị thực. Ví dụ: nếu tên MyName đề cập đến hằng số văn bản Chip Pearson, thì thuộc tính RefersTo sẽ trả về văn bản ="Chip Pearson". Bạn cần loại bỏ dấu bằng ở đầu và các dấu ngoặc kép kèm theo. Bạn có thể làm điều này với mã như sau Dim S As String
S = ThisWorkbook.Names("MyName").RefersTo
S = Mid(S, 3, Len(S) - 3)
Debug.Print S
Nếu tên đề cập đến một hằng số, sẽ có một dấu bằng ở đầu nhưng không có dấu ngoặc kép. Vì vậy, bạn sẽ cần loại bỏ dấu bằng ở đầu. Đoạn mã dưới đây minh họa điều này ________số 8Chúng ta có thể đặt tất cả những thứ này lại với nhau thành một hàm sẽ trả về giá trị mà tên đã cho đề cập đến, có thể là một phạm vi, hằng số văn bản hoặc hằng số |
