Thủ thuật Office Excel
2010(P4): Tạo mục lục
Thủ thuật Office Excel 2010_Tạo mục lục
Nếu bạn đã tốn quá nhiều thời gian trong một workbook (bảng tính) với rất
nhiều worksheet (trang tính), Khi đó, ta nên lập một chỉ mục các trang tính
đang có để thuận tiện cho việc điều hướng trong bảng tính. Bạn có thể tạo
một chỉ mục trong một vài cách: bằng tay, tự động tạo ra bởi mã VBA, hoặc
là sử dụng trình đơn tùy chọn theo ngữ cảnh.
1. Tạo mục lục trong Excel
Bằng cách sử dụng một chỉ mục các trang tính sẽ cho phép bạn nhanh chóng
và dễ dàng điều hướng trong bảng tính, chỉ bằng một cú nhấp chuột sẽ đưa
bạn đến chính xác nơi bạn muốn đến mà không lo bị nhầm lẫn. Bạn có thể
tạo một chỉ mục trong một vài cách: bằng tay, tự động tạo ra bởi mã VBA,
hoặc là sử dụng trình đơn tùy chọn theo ngữ cảnh.
Tạo chỉ mục thủ công
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ận 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 | 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.
Tạo chỉ mục tự động bằng cách sử dụ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:
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 ý, 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.
2. Đánh dấu những ô chứa công thức bằng Conditional Formatting
Khi một ô có chứa dữ liệu, bạn có thể muốn biết dữ liệu trong ô đơn thuần là
dữ liệu nhập vào, hay dữ liệu là kết quả của 1 công thức. Bạn có thể chỉ cần
click chọn ô đó và xem trên thanh công thức. Bạn cũng có thể dùng phím tắt
Ctrl + ~ để chuyển qua lại giữa chế độ xem giá trị và xem công thức.
Chiêu này sẽ giới thiệu với bạn 1 hàm tự tạo, kết hợp với Conditional
Formatting để đánh dấu ô chứa công thức. Bằng cách này có thể giúp bạn
tìm ra tất cả những ô chứa công thức trong s 10.000 ô mà không phải ngó
từng ô một.
Mặc dù bạn có thể dùng 1 hàm có sẵn của Macro4 trong Conditional
Formatting, như sau: Trong hộp thoại Conditional Formatting, chọn công
thức, gõ công thức này: =CELL("type",A1). Nhưng hạn chế của việc dùng
hàm Cell() là công thức sẽ tự tính lại mỗi khi có sự thay đổi nhỏ xíu trong
bảng tính. Vì Cell() là 1 hàm thuộc loại volatile. Khi Excel tính lại Cell() cho
10.000 ô như trên sẽ khiến cho bạn bực mình vì chờ đợi.
Do đó bạn hãy dùng tuyệt chiêu sau đây, đơn giản, dễ làm và không phải
hàm loại volatile: Bạn hãy nhấnAlt - F11 để vào cửa sổ VBA, nhấn chuột
phải vào This Workbook để insert vào 1 module. Nhập đoạn code sau vào
khung soạn thảo: