Cảm ơn Matt, người đã gửi câu hỏi về Excel của tuần này:
Tôi có một sổ làm việc Excel lớn và đang phát triển (rất nhiều trang tính). Tôi đã đưa số trang vào footer trong khi in, tuy nhiên càng ngày càng khó điều hướng khi chúng ta đang họp. Có cách nào để in mục lục dựa trên tên trang tính Excel để tôi và nhân viên có thể lật nhanh trang #xx không?
Ý kiến hay. Gợi ý đơn giản đầu tiên là đưa tên trang tính vào chân trang của bản in. Khi bạn nhấp vào "Custom Footer" trong hộp thoại Page Setup / Header Footer, có 7 biểu tượng. Biểu tượng ngoài cùng bên phải trông giống như thẻ chỉ mục với ba tab. Nhấp vào hộp Right section: và nhấn vào biểu tượng đó sẽ làm cho tên trang tính được in trên mỗi trang tính. Điều này chỉ có thể giúp điều hướng thông qua báo cáo.
MrExcel thích ý tưởng có một macro để tạo mục lục. Vấn đề chính là Excel không tính toán có bao nhiêu trang được in trên một trang tính cho đến khi bạn thực hiện xem trước bản in. Vì vậy, macro cho phép người dùng biết rằng họ sắp xem Bản xem trước khi in và yêu cầu họ loại bỏ nó bằng một cú nhấp chuột vào nút đóng.
Macro lặp lại qua từng trang tính trong sổ làm việc. Ở trạng thái hiện tại, nó thu thập thông tin từ tên của mỗi trang tính. Tôi cũng bao gồm hai dòng khác được bình luận. Nếu bạn muốn lấy mô tả từ tiêu đề bên trái hoặc từ tiêu đề trong ô A1, có những dòng mẫu để làm cả hai điều đó. Chỉ cần bỏ ghi chú một trong những bạn muốn sử dụng.
Macro tính toán số lượng trang bằng cách thêm một trang vào số lượng ngắt trang ngang (HPageBreaks.count). Nó thêm một vào số lượng ngắt trang dọc (VPageBreaks.Count). Nó nhân hai số này với nhau để tính số trang trên trang tính đó. Nếu bất kỳ độc giả trung thành nào có cách tốt hơn để làm điều này, vui lòng cho tôi biết. Phương pháp đếm số lần ngắt trang hiện tại chậm kinh khủng. Tôi dường như không thể tìm thấy một thuộc tính cho tôi biết có bao nhiêu trang được in, nhưng bạn sẽ nghĩ rằng Excel sẽ bao gồm một thuộc tính.
Thủ thuật cuối cùng là nhập phạm vi trang. Nếu một trang tính nằm trên các trang "3 - 4", Excel sẽ coi đây là một ngày và nhập vào ngày 4 tháng 3. Bằng cách đặt định dạng ô thành văn bản có ký tự "@", các trang sẽ nhập đúng cách.
Đây là macro:
Sub CreateTableOfContents() ' Copyright 1999.com ' Determine if there is already a Table of Contents TOCFound = False For Each s In Worksheets If s.Name = "Table of Contents" Then TOCFound = True Exit For End If Next s If Not TOCFound Then Sheets.Add Before:=Worksheets(1) ActiveSheet.Name = "Table of Contents" End If ' Set up the table of contents page TOCRow = 7 PageCount = 0 Sheets("Table of Contents").Select Range("A2").Value = "Table of Contents" Range("A6").CurrentRegion.Clear Range("A6").Value = "Subject" Range("A6").ColumnWidth = 36 Range("B6").Value = "Page(s)" Range("B6").ColumnWidth = 12 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Worksheets.Select Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." Msgbox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information For Each s In Worksheets s.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("Table of Contents").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 Next s End SubDưới đây là một macro tương đương, được cập nhật với một số kỹ thuật macro mới.
Sub CreateTableOfContents() ' Copyright 2002.com ' Determine if there is already a Table of Contents ' Assume it is there, and if it is not, it will raise an error ' if the Err system variable is> 0, you know the sheet is not there Dim WST As Worksheet On Error Resume Next Set WST = Worksheets("Table of Contents") If Not Err = 0 Then ' The Table of contents doesn't exist. Add it Set WST = Worksheets.Add(Before:=Worksheets(1)) WST.Name = "TOC" End If On Error GoTo 0 ' Set up the table of contents page WST.(A2) = "Table of Contents" With WST.(A6) .CurrentRegion.Clear .Value = "Subject" End With WST.(B6) = "Page(s)" WST.Range("A1:B1").ColumnWidth = Array(36, 12) TOCRow = 7 PageCount = 0 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." MsgBox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information ' Loop through each sheet, collecting TOC information For Each S In Worksheets If S.Visible = -1 Then S.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("TOC").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 End If Next S End SubBản tóm tắt ngắn gọn về các kỹ thuật macro mới trong macro mới hơn:
- Ít khi cần phải chọn một trang tính
- Thay vì lặp lại từng trang tính trong sổ làm việc để tìm kiếm một trang tính được gọi là Mục lục, macro thứ 2 chỉ đơn giản là giả sử nó ở đó và kiểm tra trạng thái của biến Err. Nếu Err là bất kỳ thứ gì khác 0, chúng tôi biết trang tính không tồn tại và cần được thêm vào.
- WST là một biến đối tượng và được định nghĩa là trang tính Mục lục. Do đó, bất kỳ tham chiếu nào đến Trang tính ("Mục lục"). có thể được thay thế bằng WST.
- Cấu trúc Ô (hàng, cột) hiệu quả hơn cấu trúc nhiều ô ("A" & TOCRow). Vì Cells () mong đợi các tham số số, Range ("A" & TOCRow) trở thành ô (TOCRow, 1)
- Dấu ngoặc vuông được sử dụng như một cách viết tắt để chỉ Phạm vi ("A1").
