Cách tạo chỉ mục cho các Sheet trong Workbook của Excel

0
104
Loading...

Hướng dẫn chi tiết Cách tạo chỉ mục cho các Sheet trong Workbook của Excel để đỡ tốn thời gian giúp thuận tiện việc điều hướng bảng tính.

Nhiều lúc, anh chị kế toán quá mệt mỏi với một bảng tính workbook lại chứa nhiều trang tính worksheet đến như vậy, nhất là tìm kiếm bên trong đó. Giải pháp tốt nhất mà Technuy.Com khuyên  nên lập chỉ mục các trang tính để điều hướng tốt hơn trong bảng tính. TechNuy sẽ giới thiệu tới bạn đọc Cách tạo chỉ mục cho các Sheet trong Workbook của Excel.

Cách tạo chỉ mục cho các Sheet trong Workbook của Excel 2
Cách tạo chỉ mục cho các Sheet trong Workbook của Excel

>> Xem thêm: Cách đặt Cốc Cốc làm trình duyệt chính mặc định trên máy tính

Lợi ích của việc tạo chỉ mục cho các Sheet rất lớn, cho phép người dùng nhanh chóng điều hướng bảng tính dễ dàng, đưa con trỏ chuột chính đến nơi mình muốn mà không lo nhầm lẫn. Có một số cách tạo chỉ mục trong Excel như thủ công, tự động nhờ mã VBA hay sử dụng trình đơn tùy chọn theo ngữ cảnh, hay cách dùng các hàm Macro4.

Cách tạo chỉ mục thủ công trên Excel

Cách này rất dễ làm, bạn chỉ cần chèn mới một worksheet và đăt cho nó một cái tên, ví dụ như tên là Index. Sau đó, bạn nhập vào tên của các worksheet có trong workbook và tạo các siêu liên kết (hyperlink) đến các worksheet tương ứng với tên mà bạn nhập.

Để tạo hyperlink bạn chọn tên sheet, sau đó vào Insert | chọn Hyperlink tại nhóm Links [E2003: Insert | Hyperlinks] hoặc nhấn phím tắt là Ctrl+K để mở hộp thoại Insert Hyperlink.

Chọn Place in This Document và chọn tên Sheet muốn kết nối tới. Nhấn nút OK để hoàn tất.

Phương pháp này phù hợp khi bảng tính không có quá nhiều worksheet và tên worksheet không có sự thay đổi thường xuyên, nếu không sẽ gây ra nhiều khó khăn cho công tác bảo trì trang chỉ mục.

Cách tạo chỉ mục tự động bằng VBA

Một cách thay thế khác là sử dụng VBA để tự động tạo ra các chỉ mục. Đoạn mã sau đây sẽ tự động tạo ra một bảng chỉ mục liên kết đến tất cả các worksheet mà bạn có trong workbook. Bảng chỉ mục này sẽ được tạo lại mỗi khi worksheet lưu chỉ mục được chọn.

Đoạn mã này phải được đặt trong private module của Sheet chứa chỉ. Chèn một worksheet mới vào workbook và đặt tên cho nó là Index chẳng hạn. Nhấp chuột phải vào tên worksheet vừa tạo và chọn ViewCode từ trình đơn ngữ cảnh hoặc nhấn tổ hợp phím Alt+F11.

Nhập đoạn mã VBA sau vào cửa sổ Code:

PHP Code:

Private Sub Worksheet_Activate()

  Dim wSheet As Worksheet

  Dim lCount As Long

  lCount = 1

With Me

   .Columns(1).ClearContents

   .Cells(1, 1) = “INDEX”

End With

For Each wSheet In Worksheets

   If wSheet.Name <> Me.Name Then

     lCount = lCount + 1

     With wSheet

         .Range(“A1”).Name = “Start” & wSheet.Index

         .Hyperlinks.Add Anchor:=.Range(“A1″), Address:=””, SubAddress:= _

          “Index”, TextToDisplay:=”Back to Index”

     End With

     Me.Hyperlinks.Add Anchor:=Me.Cells(lCount, 1), Address:=””, SubAddress:= _

     “Start” & wSheet.Index, TextToDisplay:=wSheet.Name

  End If   

Next wSheet

End Sub

Nhấn tổ hợp phím Alt+Q để thoát VBE và trở về cửa sổ bảng tính, sau đó lưu bảng tính lại. Để chạy đoạn mã vừa tạo, bạn dùng chuột chọn sang worksheet khác trong workbook và sau đó chọn lại worksheet Index để kích hoạt sự kiện Worksheet_Activate.

Lưu ý rằng, đoạn mã sẽ các đặt tên (Name) cho các ô A1 ở mỗi worksheet kèm theo số chỉ mục của worksheet trong bảng tính (worksheet đầu tiên có chỉ mục là 1, kế đó là 2, 3…. n). Điều này bảo đảm rằng ô A1 trên mỗi trang tính có một tên khác nhau. Nếu ô A1 trên worksheet của bạn đã được đặt tên, bạn nên cân nhắc đến việc thay đổi ô A1 trong đoạn mã sang một địa chỉ khác phù hợp hơn.

Lưu ý thêm, nếu bạn có thiết lập Hyperlink base (siêu liên kết cơ sở) trong workbook thì các hyperlink được tạo ra từ đoạn mã trên sẽ không thể hoạt động được, do chúng đã liên kết đến các Name trong workbook hiện hành. Khi thuộc tính hyperlink base được thiết lập thì các siêu liên kết sẽ trỏ đến hyperlink base kết hợp với các Name.

Thêm thuộc tính Hyperlink base: Vào Office | Prepare | Properties | Document Properties | chọn Advanced Properties [E2003: File | Properties | Summary] và nhập vào hyperlink base. Ví dụ như chúng ta nhập vào http://technuy.com/

Cách thêm lệnh gọi chỉ mục vào trình đơn ngữ cảnh của Excel

Cách thứ ba sẽ hướng dẫn bạn thêm một lệnh vào trình đơn ngữ cảnh để gọi danh sách các worksheet trong workbook và bạn có thể chọn lệnh này bằng cách kích phải chuột vào vùng bảng tính bất kỳ. Đoạn mã sau sẽ gọi thanh lệnh Tabs của workbook như hình sau:

Cách tạo chỉ mục cho các Sheet trong Workbook của Excel 1Chúng ta sẽ thêm lệnh “Sheet Index” vào trình đơn ngữ cảnh (menu khi bấm chuột phải như hình).

Nhấn Alt+F11 để mở cửa sổ VBE, sau đó nhập đoạn mã sau vào cửa sổ Code của ThisWorkbook:

PHP Code:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    Dim cCont As CommandBarButton

    On Error Resume Next

    Application.CommandBars(“Cell”).Controls(“Sheet Index”).Delete

    On Error GoTo 0

    Set cCont = Application.CommandBars(“Cell”).Controls.Add _

                (Type:=msoControlButton, Temporary:=True)

    With cCont

        .Caption = “Sheet Index”

        .OnAction = “IndexCode”

    End With

End Sub

Tiếp theo, bạn vào Insert | Module và nhập vào đoạn mã sau:

PHP Code:

Sub IndexCode()

      Application.CommandBars(“workbook Tabs”).ShowPopup

End Sub

Đoạn mã trên sẽ thực thi mỗi khi bạn chọn lệnh “Sheet Index” từ trình đơn ngữ cảnh và bạn phải đặt nó trong Module thì Excel mới có thể thấy được thủ tục này.

Nhấn Alt+Q để đóng cửa sổ VBE trở về bảnh tính Excel. Kể từ lúc này, bạn nhấp phải chuột tại bất kỳ ô nào trong bất kỳ worksheet nào thì trong trình đơn ngữ cảnh sẽ có thêm lệnh “Sheet Index” giúp bạn điều hướng đến bất cứ worksheet nào trong workbook một cách dễ dàng.

Với Cách tạo chỉ mục cho các Sheet trong Workbook của Excel ở trên, đối tượng được hưởng nhiều nhất chính là người dùng vì điều hướng tốt, thao tác nhanh và tỏ ra chuyên nghiệp về thủ thuật Excel. Khi đi làm, bộ công cụ của Microsoft trở thành trợ thủ đắc lực khó ứng dụng nào có thể vượt qua.

Loading...

BÌNH LUẬN