VietNetwork.Vn

Bạn muốn sắp xếp tất cả các trang tính trong sổ làm việc Excel của mình? Hãy thử tạo mục lục. Tính năng này giúp bạn dễ dàng tìm thấy các trang tính cụ thể, đặc biệt là nếu tệp Excel của bạn chứa hàng trăm trang tính.

Thật không may, Excel không có tính năng tạo mục lục chỉ bằng một cú nhấp chuột, nhưng vẫn có cách!


1. Tại sao bạn nên thêm mục lục vào Excel?

Bạn sẽ làm gì nếu bạn có hàng trăm trang tính trong một sổ làm việc Excel và cần tìm một trang tính cụ thể để cập nhật hoặc sửa đổi dữ liệu? Tìm kiếm thủ công sẽ mất quá nhiều thời gian. Nhưng nếu bạn tạo một mục lục, bạn có thể dễ dàng điều hướng qua sổ làm việc và nhanh chóng tìm thấy trang tính bạn cần.

Là một người viết nội dung SEO, đôi khi tôi làm việc với và quản lý các tệp Excel lớn chứa dữ liệu từ khóa. Với mục lục, tôi có thể dễ dàng chuyển đến trang tính Excel chính xác nơi lưu trữ thông tin cần thiết, tiết kiệm rất nhiều thời gian và công sức. Nó cũng loại bỏ nhu cầu cuộn qua vô số trang tính và tab.

Mục lục giúp bạn duy trì bố cục có cấu trúc bằng cách sắp xếp các bảng tính và phần liên quan một cách hợp lý, cải thiện trải nghiệm chung của người dùng. Nó cũng giúp các thành viên trong nhóm của bạn dễ dàng tìm thấy các phần cụ thể để nhập và xem xét. Ngoài ra, bạn có thể giảm thiểu lỗi bằng cách giảm khả năng vô tình sửa đổi dữ liệu không liên quan.

Tôi sẽ sử dụng Microsoft Excel 365 cho phần trình diễn này. Sổ làm việc của tôi đã có bốn bảng tính: Đội A, Đội B, Đội C và Đội D.

2. Thêm mục lục vào Excel theo cách thủ công

Để tạo mục lục theo cách thủ công, trước tiên hãy quyết định nơi bạn muốn đặt mục lục. Bạn nên tạo một bảng tính mới cho mục lục để dễ tìm và quản lý hơn.

Để tạo một trang tính mới, hãy nhấp chuột phải vào bất kỳ tên trang tính hiện có nào và nhấp vào "Chèn", sau đó chọn "Trang tính". Ngoài ra, bạn có thể nhấn Shift+Alt+F1.


Tiếp theo, chọn ô mà bạn định thêm siêu liên kết, chẳng hạn như B5 (hoặc bất kỳ ô nào bạn thích).

Sau khi chọn ô, hãy chuyển đến tab Chèn, nhấp vào mục thả xuống "Liên kết" và chọn tùy chọn "Chèn liên kết" để hiển thị hộp thoại Chèn siêu liên kết. Bạn cũng có thể truy cập bằng phím tắt Ctrl+K.


Điều hướng đến phần Place In This Document, chọn trang tính bạn muốn, sau đó nhập văn bản bạn muốn hiển thị cho siêu liên kết. Sau khi thực hiện xong, nhấn "OK" để chèn liên kết.


Lặp lại quá trình này cho các tờ giấy khác.

Vậy là xong! Bây giờ bạn đã có các liên kết có thể nhấp vào và sẽ đưa bạn trực tiếp đến các trang tính tương ứng khi nhấp vào.


3. Sử dụng hàm/công thức siêu liên kết

Một cách khác để thêm mục lục thủ công trong Excel là sử dụng Hàm Hyperlink. Trong phương pháp này, bạn cần nhập tất cả tên của các trang tính và thêm công thức siêu liên kết vào từng trang tính riêng lẻ.

Để bắt đầu, hãy chọn ô mà bạn muốn mục lục xuất hiện và nhập công thức sau:

=HYPERLINK("#'WorkSheetName'!A1", "FriendlyName")
Ở đây, "WorkSheetName" là tên của worksheet mà bạn muốn tạo liên kết. Ký hiệu "#" xác định worksheet và dấu chấm than "!A1" biểu thị vị trí ô trên worksheet mục tiêu. Cuối cùng, biến "FriendlyName" biểu thị tên sẽ được hiển thị trong mục lục.


Lặp lại quy trình này cho các trang tính khác bằng cách sử dụng cùng công thức.

4. Tự động xây dựng mục lục

Bạn có thể tự động tạo mục lục bằng công cụ Power Query của Excel. Với công cụ này, bạn có thể liệt kê hàng trăm trang tính trên một trang tính cụ thể chỉ bằng vài cú nhấp chuột và tạo siêu liên kết đưa bạn trực tiếp đến từng trang tính tương ứng khi nhấp vào.

Để kết nối trơn tru trong Power Query, tôi khuyên bạn nên tạm dừng đồng bộ OneDrive với sổ làm việc. Bạn cũng nên đảm bảo rằng sổ làm việc của mình đã được lưu và tạm thời tắt chia sẻ.

Để bắt đầu, hãy vào tab Dữ liệu trong Excel. Nhấp vào "Lấy dữ liệu", sau đó chọn "Từ tệp" và nhấn tùy chọn "Từ sổ làm việc Excel".


Một hộp thoại sẽ xuất hiện, tại đó bạn có thể chọn sổ làm việc mà bạn đang làm việc. Sau khi chọn xong, hãy nhấp vào "Nhập".


Thay vì chọn một bảng hoặc trang tính cụ thể từ danh sách xuất hiện, hãy chọn tên của chính sổ làm việc. Sau đó, nhấp vào "Chuyển đổi dữ liệu".


Bây giờ bạn sẽ thấy danh sách tất cả các trang tính, bảng và tên đã xác định trong sổ làm việc. Vì chúng ta chỉ muốn tên trang tính, hãy áp dụng bộ lọc để chỉ hiển thị các trang tính từ tùy chọn "Loại".


Tiếp theo, nhấp chuột phải vào cột "Tên" (có chứa tên trang tính) và chọn "Xóa các cột khác". Bước này chỉ để lại cho bạn một cột liệt kê tất cả tên của trang tính.


Bạn cũng có thể đổi tên tiêu đề thành bất kỳ tên nào bạn muốn. Sau khi thực hiện những thay đổi này, hãy nhấp vào tùy chọn "Đóng & Tải đến".


Chọn "Bảng tính hiện có" và nhập ô mà bạn muốn danh sách bắt đầu (ví dụ: ô A1 hoặc B5).


Bây giờ bạn sẽ có một bộ sưu tập tất cả tên trang tính trong sổ làm việc của mình.

Bước cuối cùng là tạo siêu liên kết cho tên trang tính bằng công thức siêu liên kết. Khi bạn tạo siêu liên kết cho trang tính đầu tiên và nhấn Enter, tất cả các cột trang tính sẽ tự động cập nhật với siêu liên kết của chúng. Nếu không, bạn chỉ cần kéo tay cầm điền để áp dụng công thức cho tất cả các hàng trong mục lục của mình.

Bạn có thể tạo siêu liên kết bằng công thức sau:

=HYPERLINK("#'"&[@WorkSheetName]&"'!A1", [@FriendlyName])

Bây giờ, nếu bạn nhấp vào bất kỳ siêu liên kết nào, bạn sẽ được đưa thẳng đến trang tính tương ứng trong sổ làm việc của mình.

5. Tự động làm mới trang tính

Một trong những lợi ích tuyệt vời khi sử dụng Power Query là bạn có thể dễ dàng cập nhật mục lục bất cứ khi nào thêm hoặc xóa trang tính khỏi sổ làm việc.

Ví dụ, tôi đã thêm một trang tính mới vào sổ làm việc của mình và lưu nó với tên "Nhóm E". Bây giờ tôi muốn trang tính này xuất hiện trong mục lục với siêu liên kết của nó.

Để cập nhật dàn ý, bạn chỉ cần quay lại trang tính chính và nhấp đúp vào truy vấn Excel "Mục lục" hiển thị bên phải sổ làm việc của bạn.


Trong menu đã mở, nhấp vào "Làm mới bản xem trước" để cập nhật Mục lục.


Tuy nhiên, khi bạn cập nhật, bất kỳ bảng hoặc tên được xác định nào được thêm gần đây cũng sẽ được đưa vào Mục lục đã cập nhật. Để lọc, hãy điều hướng đến tùy chọn "Filtered Rows", nhấp vào menu thả xuống "Kind" và chỉ chọn "Sheet".


Vậy là xong! Power Query sẽ tự động cập nhật Mục lục và bao gồm trang tính mới được thêm vào.


6. Sử dụng VBA Code Script

Nếu bảng tính của bạn lớn, bạn cũng có thể sử dụng macro VBA để tự động hóa quy trình bằng cách lặp qua tất cả các trang tính, tạo mục danh sách cho từng trang tính và chèn siêu liên kết.

Để thêm mã VBA, bạn cần tab Developer. Nếu bạn chưa từng truy cập trước đây, tab này sẽ không hiển thị trong Ribbon. Tuy nhiên, bạn có thể kích hoạt nó bằng cách vào File > Options > Customize Ribbon và bật tùy chọn "Developer".


Tiếp theo, hãy chuyển đến tab Nhà phát triển và chọn tùy chọn "Visual Basic" để mở trình soạn thảo VBA hoặc chỉ cần sử dụng phím tắt Alt+F11.


Để chèn một mô-đun mới, hãy nhấp vào Chèn > Mô-đun.


Cuối cùng, dán mã VBA do Dennis Wallentin cung cấp vào cửa sổ trình soạn thảo và nhấp vào "Chạy" hoặc nhấn F5 để thực thi mã.


Và thế là xong! Bạn đã tạo xong bảng mục lục cho bảng tính Excel của mình.


7. Tạo liên kết quay lại trang TOC

Nếu bảng tính của bạn có nhiều trang tính, bạn có thể thêm siêu liên kết vào mỗi trang tính để đưa bạn trở về trang mục lục chính.

Để bắt đầu, hãy mở trang tính mà bạn muốn thêm liên kết trả về và chọn ô mà bạn cần liên kết hiển thị. Tiếp theo, vào Insert > Link > Place in This Document. Chọn trang tính chính và nhập "Table of Contents Page" làm văn bản hiển thị.


Bây giờ bạn đã tạo một liên kết, khi nhấp vào, sẽ đưa bạn trở về trang Mục lục chính của bạn. Bạn có thể dễ dàng sao chép liên kết này và dán vào tất cả các trang tính khác.

Cho dù bạn đang xử lý một vài trang tính hay một bảng tính lớn, những phương pháp được cung cấp dưới đây sẽ giúp bạn tạo mục lục một cách hiệu quả.