Cách Tạo Tên Định Dạng (named Range) Từ Vùng Chọn Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

Có thể bạn quan tâm: Cách Tạo Bộ Lọc (filter) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu: Tại Sao Việc Đặt Tên Cho Vùng Dữ Liệu Lại Quan Trọng?

Trong môi trường làm việc với Microsoft Excel, hầu hết người dùng đều biết tới các công cụ cơ bản như công thức, biểu đồ, và bảng dữ liệu. Tuy nhiên, một trong những tính năng mạnh mẽ nhưng lại ít được khai thác đúng mức chính là Named Range – hay còn gọi là “đặt tên cho vùng dữ liệu”. Khi bạn gán một tên cho một vùng ô, bạn có thể:

1. Rút gọn công thức: Thay vì viết =SUM(A1:A10), bạn chỉ cần =SUM(Sales_Q1).
2. Tăng tính đọc hiểu: Khi người khác mở file, họ sẽ dễ hiểu hơn nếu thấy =VLOOKUP(ProductID, ProductList, 2, FALSE) thay vì =VLOOKUP(B2, $A$1:$D$100, 2, FALSE).
3. Dễ bảo trì: Khi thay đổi vị trí vùng dữ liệu, chỉ cần điều chỉnh tên mà không cần sửa từng công thức.
4. Tăng tốc độ làm việc: Khi sử dụng các hàm như Data Validation, Conditional Formatting, hoặc PivotTable, việc chọn một Named Range thay vì kéo chuột mỗi lần sẽ rất tiện lợi.

Bài viết này sẽ tập trung vào cách tạo tên (named range) từ một vùng chọn (selection) trong Excel, bao gồm các phương pháp thủ công, sử dụng phím tắt, VBA, và thậm chí là Power Query. Bạn sẽ nắm vững mọi cách để nhanh chóng gán tên cho dữ liệu, bất kể bạn là người mới bắt đầu hay một chuyên gia Excel.

Có thể bạn quan tâm: Cách Xóa Drop‑down List (danh Sách Thả Xuống) Trong Excel Một Cách Nhanh Chóng Và Hiệu Quả

1. Kiến Thức Cơ Bản Về Named Range

1.1 Định Nghĩa

• Named Range: Là một vùng ô trong bảng tính được gán một tên duy nhất, không chứa dấu cách (có thể dùng dấu gạch dưới “_”). Tên này được lưu trữ trong Workbook và có thể được sử dụng trong bất kỳ sheet nào của cùng một workbook (trừ khi bạn tạo local name cho một sheet cụ thể).

1.2 Quy Tắc Đặt Tên

1.3 Các Loại Named Range

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Tạo Combo Chart Trong Excel: Từ Cơ Bản Đến Nâng Cao

2. Cách Tạo Named Range Từ Vùng Chọn Bằng Giao Diện Người Dùng (GUI)

2.1 Sử Dụng Hộp Đối Thoại “Name Manager”

1. Chọn vùng dữ liệu bạn muốn đặt tên (ví dụ A2:A20).
2. Vào Formulas → Name Manager → New.
3. Trong hộp New Name:
4. Name: Nhập tên (ví dụ Sales_Q1).
5. Scope: Chọn Workbook (mặc định) hoặc một sheet cụ thể.
6. Refers to: Đã tự động hiển thị $Sheet1!$A$2:$A$20. Kiểm tra lại.
7. Nhấn OK, sau đó Close.

Mẹo: Nếu bạn muốn tạo một dynamic range, thay vì nhập địa chỉ cố định, bạn có thể nhập công thức, ví dụ =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1).

2.2 Tạo Tên Nhanh Từ Thanh Định Danh (Name Box)

1. Chọn vùng ô cần đặt tên.
2. Ở phía trên bên trái, ngay trên cột A, có một ô nhập tên (Name Box). Nhấp vào ô này.
3. Gõ tên mong muốn (ví dụ Revenue_2023) và nhấn Enter.

Lưu ý: Khi bạn gõ tên trong Name Box, Excel sẽ tự động tạo một workbook-level name cho vùng vừa chọn. Nếu tên đã tồn tại, Excel sẽ hiện thông báo lỗi.

2.3 Sử Dụng Thanh Công Thức (Formula Bar) Với Từ Khóa Define Name

1. Chọn vùng dữ liệu.
2. Nhấn Ctrl + F3 để mở Name Manager.
3. Nhấn New và thực hiện các bước như trong mục 2.1.

2.4 Tạo Tên Từ Menu Context (Chuột Phải)

Trong một số phiên bản Excel (đặc biệt là Excel 365), bạn có thể:

1. Chọn vùng ô.
2. Nhấp chuột phải → Define Name….
3. Điền tên và confirm.

3. Phím Tắt Và Các Cách Nhanh Cho Người Dùng Thường Xuyên

3.1 Phím Tắt “Ctrl + F3”

• Mô tả: Mở trực tiếp Name Manager. Từ đây bạn có thể tạo, chỉnh sửa, xóa các tên.
• Cách dùng: Chọn vùng ô → Nhấn Ctrl + F3 → Nhấn New → Điền thông tin → OK.

3.2 Phím Tắt “Ctrl + Shift + F3”

• Mô tả: Tạo tên tự động từ labels (tiêu đề) trong vùng đã chọn.
• Cách dùng:
• Chọn một vùng có tiêu đề (ví dụ A1:A10 với tiêu đề ở A1).
• Nhấn Ctrl + Shift + F3.
• Hộp thoại Create Names from Selection xuất hiện. Đánh dấu các tùy chọn (Top row, Left column, …) tùy theo nhu cầu.
• Nhấn OK → Excel sẽ tạo tên cho từng cột/row dựa trên tiêu đề.

Ví dụ thực tế: Nếu bạn có bảng Product | Price | Qty ở A1:C1, chọn A1:C10 → Ctrl + Shift + F3 → Excel tạo ba tên: Product, Price, Qty.

3.3 Kết Hợp Với “Define Name” Trên Ribbon

• Mô tả: Trên tab Formulas, nhóm Defined Names, có nút Define Name. Nhấp vào và làm theo hướng dẫn.
• Lợi ích: Giao diện trực quan, phù hợp cho người mới bắt đầu.

4. Tự Động Hóa Việc Tạo Named Range Bằng VBA

Khi làm việc với các file Excel lớn, việc tạo tên cho hàng chục, hàng trăm vùng dữ liệu thủ công sẽ mất thời gian. VBA (Visual Basic for Applications) cho phép bạn tự động hoá quá trình này.

4.1 Mã VBA Cơ Bản Để Tạo Tên Từ Vùng Đã Chọn

Sub CreateNameFromSelection() Dim rng As Range Dim nm As String On Error GoTo ErrHandler Set rng = Selection If rng Is Nothing Then MsgBox "Vui lòng chọn một vùng dữ liệu trước.", vbExclamation Exit Sub End If ' Yêu cầu người dùng nhập tên nm = InputBox("Nhập tên cho vùng dữ liệu đã chọn:", "Tạo Named Range") If nm = "" Then Exit Sub ' Kiểm tra tên hợp lệ If Not Application.IsName(nm) Then ThisWorkbook.Names.Add Name:=nm, RefersTo:=rng MsgBox "Đã tạo Named Range '" & nm & "' cho " & rng.Address, vbInformation Else MsgBox "Tên '" & nm & "' đã tồn tại!", vbCritical End If Exit Sub
ErrHandler: MsgBox "Lỗi: " & Err.Description, vbCritical
End Sub

Cách sử dụng:

1. Nhấn Alt + F11 → Vào Insert → Module → Dán mã trên.
2. Đóng VBA Editor.
3. Quay lại Excel, chọn vùng dữ liệu → Nhấn Alt + F8, chọn CreateNameFromSelection, nhấn Run.
4. Nhập tên khi hộp thoại xuất hiện.

4.2 Tạo Nhiều Tên Tự Động Dựa Trên Tiêu Đề Cột

Sub CreateNamesFromHeaders() Dim ws As Worksheet Dim rngHeader As Range Dim col As Range Dim nm As String Set ws = ActiveSheet ' Giả sử tiêu đề nằm ở hàng 1 Set rngHeader = ws.Range("1:1").SpecialCells(xlCellTypeConstants) For Each col In rngHeader nm = CleanName(col.Value) If nm <> "" Then On Error Resume Next ws.Names.Add Name:=nm, RefersTo:=col.Offset(1, 0).Resize(ws.Rows.Count - 1) On Error GoTo 0 End If Next col MsgBox "Đã tạo tên cho các cột dựa trên tiêu đề.", vbInformation
End Sub Function CleanName(s As String) As String Dim t As String t = Trim(s) t = Replace(t, " ", "_") t = Replace(t, "-", "_") t = Application.WorksheetFunction.Substitute(t, ".", "_") ' Loại bỏ ký tự không hợp lệ Dim i As Long, ch As String, result As String For i = 1 To Len(t) ch = Mid(t, i, 1) If ch Like "A-Za-z0-9_" Then result = result & ch Next i CleanName = result
End Function

Giải thích:

• CleanName: Hàm loại bỏ ký tự không hợp lệ và thay thế dấu cách bằng dấu gạch dưới.
• CreateNamesFromHeaders: Duyệt qua hàng tiêu đề (ở đây là hàng 1), tạo tên cho từng cột dựa trên tiêu đề, và gán phạm vi từ hàng 2 tới cuối dữ liệu.

4.3 Tạo Dynamic Named Range Bằng VBA

Sub CreateDynamicRange() Dim ws As Worksheet Dim nm As String Dim formula As String Set ws = ActiveSheet nm = InputBox("Nhập tên cho Dynamic Range:", "Dynamic Named Range") If nm = "" Then Exit Sub ' Giả sử dữ liệu ở cột A, bắt đầu từ A2 formula = "=OFFSET(" & ws.Name & "!$A$2,0,0,COUNTA(" & ws.Name & "!$A:$A)-1,1)" On Error Resume Next ThisWorkbook.Names.Add Name:=nm, RefersTo:=formula If Err.Number <> 0 Then MsgBox "Lỗi tạo tên: " & Err.Description, vbCritical Else MsgBox "Dynamic Named Range '" & nm & "' đã được tạo.", vbInformation End If On Error GoTo 0
End Sub

Khi dữ liệu trong cột A thay đổi (thêm hoặc xóa), COUNTA sẽ tự động cập nhật kích thước của named range.

4.4 Gán Shortcut Cho Macro

Sau khi tạo macro, bạn có thể gán phím tắt:

1. Vào View → Macros → View Macros.
2. Chọn macro → Options.
3. Đặt phím tắt, ví dụ Ctrl + Shift + N.

Giờ khi bạn nhấn Ctrl + Shift + N, macro sẽ chạy và tạo tên cho vùng đã chọn.

5. Sử Dụng Power Query Để Tạo Named Range

Power Query (Get & Transform) không tạo trực tiếp Named Range, nhưng bạn có thể:

1. Nhập dữ liệu từ một bảng trong Excel.
2. Tạo một query chỉ chứa cột hoặc hàng cần thiết.
3. Load query trở lại Only Create Connection.
4. Dùng công thức =Table.Column(YourQuery, "ColumnName") để tạo một named range trong worksheet.

Bước Thực Hiện

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Tạo Input Form Trong Excel: Từ Cơ Bản Đến Nâng Cao

1. Data → Get Data → From Table/Range (chọn bảng muốn dùng).
2. Trong Power Query Editor, Chọn cột hoặc Filter theo nhu cầu.
3. Nhấn Close & Load → Only Create Connection.
4. Quay lại sheet, trong một ô trống nhập công thức:
   =YourQueryColumnName
   Đặt tên cho ô này (ví dụ SalesData) bằng cách sử dụng Name Box.

Ưu điểm: Khi nguồn dữ liệu thay đổi, query sẽ tự động cập nhật và Named Range vẫn luôn chứa dữ liệu mới nhất.

6. Ứng Dụng Thực Tế Của Named Range

6.1 Dùng Trong Data Validation

Giả sử bạn có danh sách khách hàng trong cột A2:A50 và muốn tạo dropdown cho ô C2.

1. Đặt tên cho vùng A2:A50 là CustomerList.
2. Chọn ô C2 → Data → Data Validation.
3. Allow: List → Source: =CustomerList.
4. Bây giờ, khi bạn cập nhật danh sách khách hàng, dropdown tự động cập nhật.

6.2 Dùng Trong Conditional Formatting

Bạn muốn tô màu các ô trong cột B nếu giá trị lớn hơn trung bình của cột B.

1. Đặt tên cho cột B2:B100 là SalesData.
2. Chọn vùng B2:B100 → Home → Conditional Formatting → New Rule → Use a formula.
3. Công thức: =B2>AVERAGE(SalesData).
4. Định dạng màu nền (ví dụ: xanh nhạt). Khi dữ liệu thay đổi, định dạng vẫn đúng.

6.3 Dùng Trong VLOOKUP / XLOOKUP

=VLOOKUP(A2, ProductList, 3, FALSE)

• ProductList là một Named Range bao gồm các cột ProductID, ProductName, Price.
• Khi bạn thêm sản phẩm mới, chỉ cần mở Name Manager và mở rộng phạm vi ProductList.

6.4 Dùng Trong PivotTable

Khi tạo PivotTable, thay vì chọn một dải ô cố định, bạn có thể:

1. Đặt tên cho bảng dữ liệu (ví dụ SalesTable).
2. Khi tạo PivotTable, trong Table/Range, nhập SalesTable.
3. Khi dữ liệu tăng giảm, bạn chỉ cần Refresh PivotTable; phạm vi sẽ tự động cập nhật nếu bạn dùng Dynamic Named Range.

7. Các Lưu Ý Khi Quản Lý Nhiều Named Range

8. So Sánh Named Range Với Table (Bảng Excel)

Kết luận: Nếu mục tiêu chính là đặt tên cho một vùng cố định, Named Range là lựa chọn tốt. Nếu bạn cần tính năng bảng như lọc, sắp xếp, và tự động mở rộng, hãy chuyển sang Table và sử dụng structured references.

9. Các Bước Thực Hành Từ Đầu Đến Cuối

Bước 1: Chuẩn Bị Dữ Liệu

Giả sử bạn có sheet Sales2023 với các cột:
– A: Date
– B: Region
– C: Product
– D: Quantity
– E: UnitPrice
– F: Revenue (công thức =D2E2)

Bước 2: Đặt Tên Cho Các Cột

1. Chọn cột A2:A1000 → Name Box → nhập DateList.
2. Chọn cột B2:B1000 → Name Box → nhập RegionList.
3. Lặp lại cho ProductList, QtyList, PriceList, RevenueList.

Bước 3: Tạo Dynamic Named Range Cho Revenue

=OFFSET(Sales2023!$F$2,0,0,COUNTA(Sales2023!$F:$F)-1,1)

• Đặt tên RevenueDynamic.

Bước 4: Sử Dụng Trong Công Thức

• Tổng doanh thu: =SUM(RevenueDynamic)
• Trung bình doanh thu: =AVERAGE(RevenueDynamic)

Bước 5: Tạo Data Validation Cho Region

• Data Validation → List → Source: =RegionList.

Bước 6: Tạo Conditional Formatting Đánh Dấu Doanh Thu > 10,000

• Chọn RevenueList → Conditional Formatting → New Rule → Use a formula: =F2>10000 → Định dạng màu đỏ.

Bước 7: Tạo PivotTable

• Insert → PivotTable → Table/Range: =RevenueDynamic → Thiết lập Region (Rows), Product (Columns), SUM of Revenue (Values).

Bước 8: Kiểm Tra Và Bảo Trì

• Mở Name Manager → Đảm bảo mọi tên đúng phạm vi.
• Thêm một dòng dữ liệu mới → Kiểm tra RevenueDynamic đã tự động mở rộng chưa (bằng cách xem công thức OFFSET).

10. FAQ – Những Câu Hỏi Thường Gặp

Q1: Có thể đặt tên cho một ô duy nhất không?
A: Có. Chỉ cần chọn ô và đặt tên trong Name Box hoặc Name Manager. Tên sẽ trỏ tới một ô duy nhất.

Q2: Tên có thể chứa ký tự Unicode (tiếng Việt) không?
A: Excel cho phép một số ký tự Unicode, nhưng để tương thích và tránh lỗi, nên dùng tiếng Anh hoặc không dấu. Ví dụ DoanhThu thay vì DoanhThu_2023.

Q3: Khi sao chép một sheet sang workbook khác, các Named Range có theo không?
A: Nếu tên là workbook-level, chúng sẽ không tự động sao chép. Bạn cần sao chép qua Name Manager hoặc sử dụng VBA để sao chép.

Q4: Làm sao để xóa toàn bộ Named Range không còn dùng?
A: Mở Name Manager → Chọn các tên không cần → Nhấn Delete. Hoặc dùng macro:

Sub DeleteUnusedNames() Dim n As Name For Each n In ThisWorkbook.Names If n.RefersToRange Is Nothing Then n.Delete Next n
End Sub

Q5: Có thể dùng Named Range trong Google Sheets không?
A: Google Sheets có tính năng Named Range tương tự, nhưng cách tạo và công thức hơi khác. Bạn có thể tạo qua Data → Named ranges.

11. Kết Luận

Việc tạo tên (named range) từ vùng chọn trong Excel không chỉ giúp tăng tốc độ làm việc mà còn nâng cao tính đọc hiểu, bảo trì, và độ chính xác của các công thức và báo cáo. Từ các phương pháp đơn giản như sử dụng Name Box, đến các kỹ thuật nâng cao như VBA và Power Query, bạn có thể lựa chọn cách phù hợp nhất với môi trường làm việc của mình.

Hãy nhớ:

• Luôn đặt tên ngắn gọn, có ý nghĩa và tuân thủ quy tắc đặt tên.
• Sử dụng Dynamic Named Range khi dữ liệu thay đổi thường xuyên.
• Kết hợp Named Range với Table để tận dụng ưu điểm của cả hai.
• Tự động hoá bằng VBA nếu bạn phải tạo hàng chục, hàng trăm tên mỗi ngày.

Với kiến thức và công cụ trong bài viết này, bạn đã sẵn sàng áp dụng Named Range một cách hiệu quả trong mọi dự án Excel, từ báo cáo tài chính, quản lý kho, tới phân tích dữ liệu phức tạp. Hãy thử ngay và cảm nhận sự khác biệt!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.