Đánh dấu những ô chứa công thức bằng Conditional Formatting

Đánh dấu những ô chứa công thức bằng Conditional Formatting

Chiêu số 19 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ấn Alt – 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:

Function IsFormula (CheckCells As Range)

IsFormula = CheckCells.HasFormula

End Function 

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 dấ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ật thêm dữ liệu) sao cho ô A1 là ô hiện hành.

Bằng cách như chiêu số 18, bạn vào được chỗ cần thiết để gõ công thức trong hộp thoại Conditional Formatting, và gõ vào:

=IsFormula(A1), sau đó định dạng tô màu hoặc đổi màu chữ cho khác những ô còn lại.

Sau khi nhấn OK bạn sẽ được kết quả là tất cả những ô chứa công thức sẽ được tô màu. Nếu bạn thêm hoặc thay đổi 1 ô, nếu ô đó trở thành công thức thì lập tức ô đó đổi màu.

Đôi khi bạn không thấy kết quả, vì anh Bill lanh chanh và chậm hiểu, anh ta cho rằng công thức sử dụng hàm của bạn là 1 text nên ảnh tự sửa thành : =”IsFormula(A1)”. Vậy bạn phải vào chỗ cũ sửa lại.

Bây giờ mỗi khi bạn sửa hoặc thêm 1 ô trở thành công thức, ô đó sẽ có màu. ngược lại, nếu bạn sửa 1 công thức thành giá trị hoặc thêm giá trị vào 1 ô, ô đó sẽ không có màu.

7. Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn.

Ví dụ như, bạn có thể thay thế những ký tự không mong muốn bằng chuỗi rỗng tựa như nó chưa hề tồn tại. Muốn vậy bạn cần biết mã của từng ký tự mà bạn muốn gỡ bỏ. Tất cả các ký tự đều mang một mã riêng và Excel sẽ cho bạn biết nó là gì khi bạn sử dụng hàm CODE.

Hàm CODE sẽ trả về một mã số cho ký tự đầu tiên trong một chuỗi. Mã này tương đương ký tự mà máy tính của bạn đã thiết lập.

Để thực hiện điều này, chọn một trong các ô có chứa những ký tự không mong muốn. Từ thanh công thức, bôi đen ký tự và sao chép ký tự đó. Tiếp theo chọn ô trống bất kỳ (A1 chẳng hạn) và dán ký tự đó vào ô đã chọn (A1).

Tại ô khác, nhập công thức sau:

=CODE($A$1)

Công thức này trả về mã của ký tự không mong muốn.

Chọn toàn bộ dữ liệu của bạn, chọn Home ➝ Editing ➝ Find & Select ➝ Replace (với phiên bản trước Excel 2010: chọn Edit ➝ Replace…), ở khung Find what: nhấn phim Alt và gõ số 0 kèm theo code đã đưa ra bởi công thức trên. Nếu mã số là 163 thì nhấn Alt và nhấn 0163. (Hoặc bạn có thể để con trỏ chuột tại ô có ký tự không mong muốn, sao chép ký tự đó và dán vào ô Find what cũng được)

Bỏ trống khung Replace With và nhấn Replace all. Việc làm này sẽ xóa bỏ tất cả những ký tự không mong muốn rất nhanh qua việc dò tìm mã ký tự. Lặp lại các bước ở trên cho mỗi ký tự không mong muốn tiếp theo.

8. Chuyển đổi con số dạng văn bản sang số thực

Bạn kéo cột rộng ra một ít để đễ phân biệt việc canh lề, khi đó bạn sẽ thấy các giá trị số, ngày tháng sẽ được canh lề phải và văn bản sẽ được canh lề trái.

Đây là cách nhanh và dễ dàng nhất để chuyển các giá trị số dang văn bản sang số thực. Các bước thực hiện như sau:

Chọn một ô trống nào đó và nhấn lệnh Copy (Ctrl + C)➝ quét chọn vùng số liệu dạng văn bản định chuyển đổi (ví dụ như vùng A1:A9 ở hình trên) ➝ nhấp phải chuột và chọn Paste Special… ➝ chọn Add tại nhóm Operation ➝ nhấn OK để hoàn tất.

Việc làm trên sẽ giúp chuyển toàn số các con số dạng văn bản sang số thực, vì ô rỗng có giá trị là 0 và khi bạn cộng bất kỳ số nào vào một con số lưu dưới dạng văn bản trong Excel thì bạn đã làm cho con số dạng văn bản chuyển thành số thực.

Bạn có thể áp dụng nguyên tắc như cách trên vào một số hàm có sẵn của Excel để thực hiện việc chuyển đổi. Thông thường, khi bạn dùng một hàm thuộc nhóm TEXT và kết quả trả về dưới dạng con số thì Excel vẫn xem con số đó là giá trị dạng văn bản.

Giả sử bạn có một vùng dữ liệu A1:A7 như hình sau:

Bạn dùng hai hàm trong nhóm TEXT là LEFT và FIND để tách các giá trị ra khỏi các chuỗi văn bản như sau:

=LEFT(A1,FIND(" ",A1)-1)

Sau khi dùng hàm để tách phần giá trị ra thì các kết quả trả về vẫn được Excel xem như là văn bản vì chúng được canh lề trái như hình sau:

Do vậy, bạn cần phải hiệu chỉnh công thức tác chuỗi trên một ít để kết quả trả về là các con số thực sự bằng cách cộng thêm số 0 vào sau công thức trên:

=LEFT(A1,FIND(" ",A1)-1) + 0

9. Tăng thêm số lần Undo cho Excel

Khi bạn sử dụng chức năng Undo của Excel, và bạn đã thực hiện Undo 16 lần, thì nút Undo bị mờ đi, không thể Undo được nữa. Ngoài ra, khi bạn nhấn nút Save để lưu bảng tính, thì nút Undo cũng bị mờ đi, và danh sách những tác vụ (những hành động đã thực hiện trên bảng tính) mà bạn đã thực hiện bị mất sạch. Đó là do khi bạn lưu bảng tính, Excel cho rằng bạn đã hài lòng với bảng tính này (thì mới nhấn Save), và nó thấy rằng không cần thiết phải giữ lại danh sách những tác vụ trước khi lưu nữa.

Bạn có thể thấy rằng, quay lui lại 16 lần là không đủ, nhưng làm cách nào để tăng số lần này lên? Nghĩa là làm cách nào để tăng danh sách các tác vụ của bạn lên? Thưa rằng, có cách, và bạn có thể tăng con số này lên đến 100 lần.

Để làm điều này, trước hết, bạn hãy thoát khỏi Excel. Sau đó bạn chạy lệnh Run của Windows (Start | Run, hoặc nhấn phím Window + R), gõ vào đó Regedit.exe và nhấn OK.

Khi bạn đã mở được chương trình Regedit, hãy tìm đến khóa:

HKEY_CURRENT_USER \ Software \ Microsoft \ Office \ 11.0 \ Excel \ Options

Con số 11.0 ở trên đây có thể khác, ví dụ với Excel2000 thì nó là 10.0, với Excel 2010 thì nó là 12.0

Nhấn Edit | New | DWORD Value (xem hình). Một mục mới New Value #1 sẽ được tạo ra trong khung bên phải, bạn nhập vào đó chữ UndoHistory, và nhấn Enter.

Nhấn đúp (double-click) vào mục UndoHistory mới tạo, rồi gõ vào hộp thoại mở ra một con số bất kỳ, miễn là lớn hơn 16 và nhỏ hơn 100. Nhấn OK, rồi thoát Regedit.

Bạn hãy khởi động lại Excel và thử làm gì trong đó, rồi nhấn vào nút Undo xem bạn có thể Undo được bao nhiêu lần nhé. Hy vọng rằng Undo 100 lần, đã là quá đủ cho bạn. Tuy nhiên, nếu bạn nhấn lưu bảng tính, thì danh sách các tác vụ có thể Undo

10. Tách họ và tên trong cùng một cột trên bảng tính Excel

Private Function Tachten(ten As String, lg As Integer)

Dim j As Integer

Name = Trim(ten)

For j = Len(Name) To 1 Step -1

If Mid(Name, j, 1) = " " Then

If lg = "1" Then

Tachten = Right(Name, Len(Name) - j)

Else

Tachten = Left(Name, j)

Exit For

Next

- Để tách riêng tên vào một ô mới, bạn đặt con trỏ tại ô sẽ chứa tên cần tách và nhập vào công thức =TachTen(B3,1) rồi nhấn Enter là xong.

11. 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 (thậm chí có thể dùng các hàm Macro4 – không trình bày ở đây).

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:

Private Sub Worksheet_Activate()

Dim wSheet As Worksheet

Dim lCount As Long

lCount = 1

With Me

 .Columns(1).ClearContents

End With

 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

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.