Có thể bạn quan tâm: How To Delete All Blank Rows In Excel
Giới thiệu
Excel là công cụ xử lý dữ liệu phổ biến nhất trên thế giới, với hàng ngàn người dùng từ nhà quản lý, nhà khoa học dữ liệu, kế toán cho tới lập trình viên. Một trong những thao tác thường gặp khi làm việc với các workbook phức tạp là cần liệt kê ra tên tất cả các sheet (hoặc trang tính) trong một workbook. Việc này rất hữu ích khi bạn cần kiểm tra cấu trúc file, tự động hóa quy trình, hoặc lập báo cáo tổng hợp. Trong bài viết này, chúng ta sẽ đi sâu vào cách lấy danh sách tên sheet trong Excel bằng nhiều phương pháp khác nhau: qua VBA (Visual Basic for Applications), Power Query, công thức Excel, và công cụ dòng lệnh Python. Bạn sẽ học được cách áp dụng từng phương pháp trong môi trường thực tiễn, cùng với ví dụ code, ưu nhược điểm và mẹo thực tiễn.
Có thể bạn quan tâm: Hướng Dẫn Chi Tiết: Làm Thế Nào Để Tạo Biểu Đồ Stacked Bar Chart Trong Excel
1. Tại sao cần lấy tên tất cả sheet trong Excel?
- Kiểm tra tính toàn vẹn dữ liệu: Khi một workbook lớn có hàng trăm sheet, bạn có thể nhanh chóng xác định sheet nào bị thiếu hoặc có tên sai.
- Tự động hóa quy trình: Để viết macro, script, hoặc báo cáo, bạn cần danh sách sheet để lặp qua và thực hiện thao tác.
- Tích hợp với hệ thống khác: Khi xuất dữ liệu sang hệ thống quản lý dữ liệu (SQL, Tableau, Power BI), bạn có thể cần danh sách sheet để quyết định cách xử lý.
- Quản lý tài nguyên: Đối với các file Excel lớn, có thể cần phân loại sheet theo nhóm để tối ưu hóa bộ nhớ.
Có thể bạn quan tâm: How To Create A Budget Spreadsheet In Excel: A Step-by-step Guide For Beginners And Advanced Users
2. Phương pháp 1: Sử dụng VBA (Visual Basic for Applications)
VBA là ngôn ngữ lập trình tích hợp sẵn trong Excel, cho phép bạn truy cập và thao tác với workbook, sheet, cell, và nhiều đối tượng khác. Dưới đây là các bước chi tiết để lấy tên sheet bằng VBA.
2.1. Mở cửa sổ VBA
- Mở Excel và workbook của bạn.
- Nhấn
Alt + F11để mở Microsoft Visual Basic for Applications. - Trên thanh menu, chọn
Insert > Moduleđể tạo một module mới.
2.2. Viết macro lấy tên sheet
Sub GetAllSheetNames() Dim ws As Worksheet Dim i As Long i = 1 ' Tạo một sheet mới để lưu danh sách On Error Resume Next Application.DisplayAlerts = False Sheets("SheetList").Delete Application.DisplayAlerts = True On Error GoTo 0 Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = "SheetList" For Each ws In ThisWorkbook.Worksheets Sheets("SheetList").Cells(i, 1).Value = ws.Name i = i + 1 Next ws MsgBox "Đã lưu danh sách tên sheet vào bảng tính 'SheetList'.", vbInformation
End Sub
Giải thích:
For Each ws In ThisWorkbook.Worksheets: Duyệt qua từng sheet trong workbook hiện tại.Sheets("SheetList").Cells(i, 1).Value = ws.Name: Ghi tên sheet vào cột A của bảng tínhSheetList.On Error Resume Next+Application.DisplayAlerts = False: Xóa sheetSheetListnếu đã tồn tại, tránh lỗi.
2.3. Chạy macro
- Trở lại Excel, nhấn
Alt + F8, chọnGetAllSheetNames, rồi nhấnRun. - Một sheet mới tên
SheetListsẽ xuất hiện, chứa danh sách tên tất cả các sheet.
2.4. Lưu macro
Nếu bạn muốn lưu macro để sử dụng lại:
- Chọn
File > Save Workbook As. - Chọn định dạng
Excel Macro-Enabled Workbook (.xlsm).
2.5. Các biến thể và cải tiến
- Lưu danh sách vào một file riêng: Thay vì ghi vào một sheet trong workbook hiện tại, bạn có thể tạo workbook mới và ghi danh sách vào đó.
- Lấy thêm thông tin: Bạn có thể thêm cột thứ hai ghi ngày tạo sheet, màu sắc, hoặc dữ liệu khởi tạo.
- Sử dụng hàm tự định nghĩa (UDF): Viết một hàm trả về mảng tên sheet, sử dụng trong công thức.
Function GetSheetNames() As Variant Dim ws As Worksheet Dim arr() As String ReDim arr(1 To ThisWorkbook.Worksheets.Count) Dim i As Long i = 1 For Each ws In ThisWorkbook.Worksheets arr(i) = ws.Name i = i + 1 Next ws GetSheetNames = arr
End Function
Sau khi lưu, bạn có thể dùng công thức như =GetSheetNames() trong một ô và áp dụng TRANSPOSE để hiển thị danh sách.
3. Phương pháp 2: Sử dụng Power Query
Power Query (Get & Transform) là công cụ mạnh mẽ trong Excel để trích xuất, chuyển đổi dữ liệu. Bạn có thể dùng Power Query để lấy danh sách tên sheet mà không cần viết mã VBA.
3.1. Mở Power Query
- Trong Excel, vào tab
Data. - Chọn
Get Data > From Other Sources > Blank Query.
3.2. Viết M code
Trong cửa sổ Power Query Editor:
- Nhấn
Advanced Editor. - Dán đoạn code sau:
let Source = Excel.CurrentWorkbook(), #"Filtered Rows" = Table.SelectRows(Source, each (Kind = "Sheet")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Data", "Kind"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name", "SheetName"}})
in #"Renamed Columns"
Giải thích:
Excel.CurrentWorkbook()trả về bảng dữ liệu chứa tất cả các đối tượng trong workbook, bao gồm sheets.Table.SelectRowslọc ra các dòng cóKind = "Sheet".Table.RemoveColumnsbỏ cột dữ liệu không cần thiết.Kết quả là một bảng với cột
SheetName.Nhấn
Close & Loadđể tải bảng vào sheet mới.
3.3. Lưu và làm mới
- Khi workbook thay đổi (thêm/xóa sheet), bạn chỉ cần click
Refreshtrên tabDatađể cập nhật danh sách.
3.4. Ưu và nhược điểm
| Ưu điểm | Nhược điểm |
|---|---|
| Không cần viết VBA, dễ dàng cho người mới | Không linh hoạt khi cần thực hiện thao tác phức tạp trên từng sheet |
| Dễ dàng làm mới dữ liệu | Đòi hỏi phiên bản Excel 2026 trở lên hoặc Office 365 |
4. Phương pháp 3: Công thức Excel (Dynamic Array)
Nếu bạn sử dụng Excel 365 hoặc Excel 2026 với tính năng Dynamic Arrays, bạn có thể lấy danh sách tên sheet bằng công thức mà không cần VBA.
4.1. Công thức
Trong một ô, nhập công thức:
=LET( sheets, SHEETNAMES(), FILTER(sheets, LEN(sheets) > 0)
)
Lưu ý: Hàm SHEETNAMES() không tồn tại trong Excel. Thay vào đó, bạn cần viết một hàm UDF trong VBA để trả về mảng tên sheet, sau đó gọi nó trong công thức. Nếu bạn không muốn VBA, phương pháp này không thực hiện được.
Có thể bạn quan tâm: Cách Trích Xuất Số Từ Văn Bản Trong Excel: Hướng Dẫn Chi Tiết Đầy Đủ
4.2. Thay thế: Sử dụng hàm CELL và INDEX
Một cách hack:
=INDEX(GET.WORKBOOK(1),ROW(INDIRECT("1:" & GET.WORKBOOK(1))))
Nhưng GET.WORKBOOK là hàm tệp tính toán (xlam) của Excel 2003 và không hoạt động trong Excel 365. Do đó, phương pháp này không đáng tin cậy.
4.3. Kết luận
- VBA vẫn là cách duy nhất để lấy danh sách sheet qua công thức mà không viết macro phức tạp.
- Nếu bạn muốn tránh VBA, Power Query là lựa chọn tốt nhất.
5. Phương pháp 4: Sử dụng Python (openpyxl / pandas)
Đối với người làm data science, Python là công cụ phổ biến. Dưới đây là cách lấy tên sheet bằng openpyxl và pandas.
5.1. Cài đặt thư viện
pip install openpyxl pandas
5.2. Mã Python
import openpyxl # Đường dẫn đến file Excel
file_path = r'C:\Users\YourName\Documents\example.xlsx' # Mở workbook
wb = openpyxl.load_workbook(file_path, read_only=True) # Lấy danh sách tên sheet
sheet_names = wb.sheetnames print("Danh sách sheet:")
for name in sheet_names: print(name)
5.3. Lưu danh sách vào một file khác
Bạn có thể ghi danh sách vào một file văn bản hoặc Excel mới.
import pandas as pd df = pd.DataFrame(sheet_names, columns='SheetName')
df.to_excel('sheet_list.xlsx', index=False)
5.4. Ưu nhược điểm
| Ưu điểm | Nhược điểm |
|---|---|
| Hỗ trợ làm việc với dữ liệu lớn, có thể tích hợp vào pipeline | Cần cài đặt Python và thư viện |
| Có thể tự động hoá quy trình phức tạp | Đòi hỏi kiến thức lập trình |
6. Phương pháp 5: Sử dụng công cụ dòng lệnh (CLI) Excel (msoffice)
Nếu bạn đang làm việc trên Windows Server hoặc môi trường không có GUI, bạn có thể dùng Microsoft.Office.Interop.Excel thông qua PowerShell.
6.1. PowerShell Script
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open("C:\Users\YourName\Documents\example.xlsx") $sheetNames = @()
foreach ($sheet in $workbook.Worksheets) { $sheetNames += $sheet.Name
} $workbook.Close($false)
$excel.Quit()
System.Runtime.Interopservices.Marshal::ReleaseComObject($excel) | Out-Null $sheetNames | Out-File -FilePath "C:\Users\YourName\Documents\sheet_names.txt"
6.2. Lưu ý
- Cần cài đặt Office trên máy tính để có COM object.
- Đảm bảo chạy PowerShell với quyền Administrator nếu cần.
7. Mẹo thực tiễn khi làm việc với danh sách sheet
- Đặt tên sheet rõ ràng: Tránh tên trùng lặp, tránh ký tự đặc biệt.
- Sử dụng bảng tính “SheetList”: Luôn có một sheet chứa danh sách cập nhật tự động (được VBA hoặc Power Query).
- Kiểm tra số lượng sheet: Dùng hàm
=COUNTA(SheetList!A:A)để nhanh chóng biết có bao nhiêu sheet. - Tự động hóa báo cáo: Khi báo cáo cần dữ liệu từ nhiều sheet, hãy viết macro lặp qua
SheetListđể lấy dữ liệu. - Sử dụng công cụ quản lý tài liệu: Nếu workbook rất lớn, cân nhắc chia nhỏ thành các file nhỏ, mỗi file có một số sheet.
8. So sánh các phương pháp
| Phương pháp | Thời gian cài đặt | Độ phức tạp | Khả năng mở rộng | Khuyến nghị |
|---|---|---|---|---|
| VBA | Nhanh | Trung bình | Tốt, hỗ trợ tự động | Đối với người dùng Excel truyền thống |
| Power Query | Trung bình | Dễ | Tốt, có thể làm mới | Người dùng có Office 365 |
| Công thức | N/A | Rất thấp | Hạn chế | Không thực tế trong thực tiễn |
| Python | Cài đặt | Trung bình | Rất tốt | Đối với data scientist |
| PowerShell | Cài đặt | Trung bình | Tốt | Đối với môi trường server |
9. Kết luận
Lấy danh sách tên sheet trong Excel là một thao tác quan trọng, mở đường cho nhiều quy trình tự động hóa và kiểm tra dữ liệu. Bạn có thể chọn phương pháp phù hợp với môi trường làm việc và kỹ năng của mình: VBA cho những người quen với Excel, Power Query cho người muốn thao tác nhanh chóng, Python cho những ai làm việc với dữ liệu lớn, hoặc PowerShell cho môi trường server.
Hãy thử áp dụng một trong những phương pháp trên vào dự án của bạn, và bạn sẽ thấy quy trình làm việc trở nên chính xác, nhanh chóng và hiệu quả hơn rất nhiều. Happy Excel-ing!
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ẻ.