Thủ thuật về Office Excel 2010

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.
Nội dung trích xuất từ tài liệu:
Thủ thuật về Office Excel 2010Thủ thuật Office Excel2010(P4): Tạo mục lụcThủ thuật Office Excel 2010_Tạo mục lụcNếu bạn đã tốn quá nhiều thời gian trong một workbook (bảng tính) với rấtnhiề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ạomột chỉ mục trong một vài cách: bằng tay, tự động tạo ra bởi mã VBA, hoặclà sử dụng trình đơn tùy chọn theo ngữ cảnh.1. Tạo mục lục trong ExcelBằng cách sử dụng một chỉ mục các trang tính sẽ cho phép bạn nhanh chóngvà dễ dàng điều hướng trong bảng tính, chỉ bằng một cú nhấp chuột sẽ đưabạ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ôngCá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ộtcái tên, ví dụ như tên là Index. Sau đó, bạn nhận vào tên của các worksheetcó trong workbook và tạo các siêu liên kết (hyperlink) đến các worksheettươ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ạinhóm Links | Insert | Hyperlinks] hoặc nhấn phím tắt là Ctrl+K để mở hộpthoạ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útOK để 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 ranhiề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 VBAMột cách thay thế khác là sử dụng VBA để tự động tạo ra các chỉ mục. Đoạnmã sau đây sẽ tự động tạo ra một bảng chỉ mục liên kết đến tất cả cácworksheet mà bạn có trong workbook. Bảng chỉ mục này sẽ được tạo lại mỗikhi worksheet lưu chỉ mục được chọn. Đoạn mã này phải được đặt trongprivate module của Sheet chứa chỉ. Chèn một worksheet mới vào workbookvà đặt tên cho nó là Index chẳng hạn. Nhấp chuột phải vào tên worksheetvừa tạo và chọn ViewCode từ trình đơn ngữ cảnh hoặc nhấn tổ hợp phímAlt+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 sangworksheet khác trong workbook và sau đó chọn lại worksheet Index để kíchhoạ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 worksheettrong bảng tính (worksheet đầu tiên có chỉ mục là 1, kế đó là 2, 3…. n). Điềunày bảo đảm rằng ô A1 trên mỗi trang tính có một tên khác nhau. Nếu ô A1trê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ở) trongworkbook 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ỏ đếnhyperlink 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 FormattingKhi 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ầnclick chọn ô đó và xem trên thanh công thức. Bạn cũng có thể dùng phím tắtCtrl + ~ để 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 ConditionalFormatting để đánh dấu ô chứa công thức. Bằng cách này có thể giúp bạntì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 ConditionalFormatting, như sau: Trong hộp thoại Conditional Formatting, chọn côngthức, gõ công thức này: =CELL(type,A1). Nhưng hạn chế của việc dùnghàm Cell() là công thức sẽ tự tính lại mỗi khi có sự thay đổi nhỏ xíu trongbảng tính. Vì Cell() là 1 hàm thuộc loại volatile. Khi Excel tính lại Cell() cho10.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ảihàm loại volatile: Bạn hãy nhấnAlt - F11 để vào cửa sổ VBA, nhấn chuộtphải vào This Workbook để insert vào 1 module. Nhập đoạn code sau vàokhung soạn thảo:Do tính chất của Property HasFormula, hàm bạn mới tạo sẽ trả về các giá trịluận lý True, False. Nghĩa là khi bạn gõ vào ô bất kỳ công thức =IsFormula(A1) sẽ cho kết quả True nếu A1 chứa công thức và cho kết quảFalse nếu A1 chứa giá trị.Đóng cửa sổ VBA lại, trở về bảng tính. Bây giờ đánh ấu toàn bộ vùng dữliệu của bạn (có thể chọn dư ra một số cột và dòng, phòng khi bạn cập nhậtthêm dữ liệu) sao cho ô A1 là ô hiện hành.Bạn vào được chỗ cần thiết để gõ công thức trong hộp thoại ConditionalFormatting, và gõ vào: = sFormula(A1), sau đó định dạng tô màu hoặc đổimàu chữ cho khác những ô còn lại ...