Cách tạo mục lục tự động Excel

Chào các bạn, hôm nay chúng ta sẽ có một nhiệm vụ là đi tạo mục lục bảng tính trong file Excel của mình. Khi bạn có 1 file Excel với rất nhiều sheets trong đó và bạn muốn bằng một cách nhanh chóng có thể tạo ra một menu để có thể dễ dàng đi từ sheet này qua sheet khác. Để làm được điều này, chúng ta sẽ theo từng bước như hướng dẫn sau của Học Excel Online. Cùng bắt đầu ngay thôi

Tạo mục lục bảng tính trong file Excel

Đầu tiên, ở sheet bạn muốn tạo mục lục, tạo 1 bảng tính theo cấu trúc như sau

Tạo mục lục trong bảng tính excel

Giả sử bạn có 10 sheets thì ở đây chúng ta sẽ đánh số cho cột Index từ 1 tới 10. Sau đó, chúng ta sẽ cần tạo 1 Name Range bằng cách bấm vào thẻ Formulas > Define Name

Tạo Name Range

Trong cửa sổ New Name, bạn hãy thiết lập như sau:

Name: SheetNames

Refers to:

=REPLACE[GET.WORKBOOK[1],1,FIND["]",GET.WORKBOOK[1]],""]

Thiết lập Name Range

Tiếp theo, bạn sẽ cần thiết lập công thức trong ô B2 và lặp lại cho các ô phía dưới của bảng tính như sau:

=INDEX[SheetNames,A2]

Thiết lập công thức với Name Range

Như vậy, tới đây, bạn đã tạo mục lục tên của các sheet trong file Excel rồi. Bây giờ, chúng ta sẽ cùng nhau đi thêm Hyperlink cho các bảng tính này bằng cách sử dụng công thức sau đây từ ô C2:

=HYPERLINK["#" & B2 & "!A1","Go to"]

Tạo Hyperlink trong bảng tính đến sheet khác

Trong ví dụ trên thì mục lục được tạo ra nằm trên Sheet1, nếu các bạn muốn ở mỗi bảng tính có nút “Quay về” để khi click vào sẽ trở về Sheet1, thì các bạn có thể thêm lần lượt vào các bảng tính đó tại ô mong muốn công thức sau đây:

=HYPERLINK["#Sheet1!A1","Quay về"]

Bạn chỉ cần viết công thức này 1 lần, sau đó có thể copy qua các sheet khác để tiết kiệm thời gian.

Nếu tên sheet của các bạn có dấu cách, thì hàm Hyperlink cần được viết như sau:
=HYPERLINK["#'Co dau cach'!A1","Quay về"]

Khi tên sheet đặt trong 1 ô khác thì cách nối chuỗi trong hàm Hyperlink như sau:

=HYPERLINK["#'" & B2 & "'!A1","Quay về"]

[Giả sử B2 là ô chứa tên sheet]

Và cuối cùng, vì trong Name Range chúng ta sử dụng hàm Macro 4, nên chúng ta sẽ lưu file Excel này lại dưới dạng file có hỗ trợ Macros để lần sau mở lên thì thiết lập mục lục của chúng ta vẫn sẽ hoạt động bằng cách bấm phím F12 trên bàn phím và trong cửa sổ Save As …, phần Save As Type, các bạn cần chọn là Excel Macro-Enabled Workbook.

Video tham khảo:

Hi vọng qua bài lần này, các bạn đã có thể tự tạo ra một file Excel và tự tạo mục lục của mình và sẽ không mất nhiều thời gian để chuyển giữa các sheet trong khi làm việc nữa. Các bạn có thể download file ví dụ minh hoạ tại đường  link này.

Bạn muốn nâng cao trình độ Excel hoặc hệ thống lại kiến thức cùng với chuyên gia Nguyễn Đức Thanh? Hãy tham khảo khoá học EX101 – Excel từ cơ bản tới chuyên gia dành cho người đi làm

Nếu như bạn muốn tạo một sheet MucLuc chứa liên kết đến tất cả sheet còn lại của bảng tính [workbook] cách thức tiến hành như sau .1. Tạo một trang tính chứa mục lục ở đầu bảng tính Chọn trang tính đầu tiên của bảng tính Bấm tổ hợp phím để tạo thêm một trang tính mới Bạn có thể đặt tên bất kỳ cho trang tính mới này như mục lục, mucluc, … ngoại trừ Index 2. Tạo code cho trang tính chứa mục lục Click chuột phải lên thẻ của trang tính chứa mục lục, chọn View code

Nhập đoạn code sau vào cửa sổ Microsoft Visual Basic Private Sub Worksheet_Activate[] Dim wSheet As Worksheet Dim M As Long M = 1 With Me .Columns[1].ClearContents .Cells[1, 1] = "INDEX" .Cells[1, 1].Name = "Index" End With For Each wSheet In Worksheets If wSheet.Name Me.Name Then M = M + 1 With wSheet .Range["a1"].Name = "Start" & wSheet.Index.Hyperlinks.Add Anchor:=.Range["a1"], Address:="", SubAddress:="Index", TextToDisplay:=wSheet.NameEnd With Me.Hyperlinks.Add Anchor:=Me.Cells[M, 1], Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name End If Next wSheet

End Sub 

Page 2

Home Tài liệu Nhóm gỗ Cẩm nang Quy trình kỹ thuật Tên cây khoa học Thiết bị điều tra rừng Văn bản pháp luật ▼

Home Thủ thuật tin học Tài liệu tin học ▼

Home Hồ Chí Minh Sách đời sống Ô số sudoku Cờ tướng English ▼

Home Ninh Thuận 05/2017 Cà Mau 12/2016 Hồ Tây - Hà Nội 10/2016 Đà Nẵng 09/2016 Quảng Trị 09/2016 Huế 09/2016 Thanh Hóa 09/2016 Vườn quốc gia Cúc Phương, Ninh Bình 06/2016 Bình Phước 12/2015 Vườn Quốc gia Ba Vì, Hà Nội 11/2015 Ninh Thuận 09/2015 Phú Quốc, Kiên Giang 08/2015 Bình Thuận 07/2015 Sea Links City - Phan Thiết, Bình Thuận 04/2015 Bình Thuận 11/2014 Điều tra ô định vị sinh thái Quốc gia 2013 Kiên Giang 2010 ▼

Để thuận tiện cho việc tìm kiếm sheet cũng như kiểm tra trong Excel thì chúng ta thường nghĩ đến việc làm sao để ấn một cái là có thể mở được luôn.

Việc tạo danh mục chứa link liên kết giúp cho các bạn mở sheet đó nhanh hơn.

Thường được sử dụng khá phổ biến trong các bản báo cáo hoặc mục lục được thiết lập trên Excel.

Dưới đây, HocThatNhanh.vn sẽ chia sẻ với bạn 02 cách tạo danh mục các sheets trong Excel nhanh nhất:

Cách 1: Tạo mục lục các sheet trong Excel bằng Hyperlink

• Bước 1: Ấn chuột phải chọn Hyperlink hoặc nhấn Ctrl + K

• Bước 2: Trong bảng cài đặt bạn chọn Place in this document

• Bước 3: Lựa chọn tên sheet

• Bước 4: Ấn OK để kết thúc

Ví dụ cách tạo mục lục các sheet trong Excel

 

Cách 2: Tạo mục lục các sheet trong Excel tự động bằng tiện ích Excel XDAddins

Nhanh nhất, bạn có thể tham khảo thêm phần mềm Tiện ích Excel XDAddins để tạo mục lục hàng loạt chỉ với một vài click chuột như sau:

Chúc các bạn thành công!