Bài 2, phần tiếp theo của bài 1
Nhấn vào nút design, click chọn cái control bạn vừa vẽ, nhấn thêm nút Property. Trong cửa sổProperty, sửa dòng Caption thành View/ Hide, sửa dòng Linked Cell thành $C$2. (cả 2010 và 2003 như nhau, cả checkbox và Toggle Button như nhau).
Bây giờ khi bạn click chọn checkbox hoặc nhấn nút Toggle, ô C2 sẽ lần lượt có các giá trị TRUE vàFALSE.
PHẦN 4
Khi nhập dữ liệu hay khi sao chép và dán dữ liệu từ nguồn khác vào Excel thì các ký tự không mong muốn sẽ xuất hiện trong toàn bộ bảng tính của bạn. Sử dụng chiêu này bạn có thể khắc phục được những rắc rối khi gỡ bỏ các ký tự không mong muốn bằng tay. Chức năng thay thế (replace) trong Excel có thể giúp bạn gỡ bỏ các ký tự không mong muốn trong bảng tính, nhưng phải qua một vài bước phụ.
Bây giờ giả sử vùng dữ liệu của bạn gồm 4 fields, trong đó bạn chỉ muốn 3 fields hiện thường xuyên, còn field thứ 4 thì khi nào cần mới hiện ra để xem, không cần thì dấu đi. Bạn đánh dấu chọn vùng chứa field 4, trong 2010 bạn vào tab Home, Conditional Formating, New Rule, chọn tiếp "use a formula to determine which cells to format", trong 2003 là Fornat - Conditional Formating – chọn tiếp "Formula is". Trong ô kế bên, bạn gõ: = $C$2=FALSE.
Nhấn vào nút Format, định dạng font chữ màu trắng. Nhấn OK và OK. Nhấn nút design 1 lần nữa để thoát ta khõi chế độ design Mode. Và nhấn nút toggle hoặc click chọn cái checkbox xem kết quả.
Nếu bạn không thích thì định dạng ô C2 chữ trắng luôn, để khỏi thấy chữ TRUE, FALSE hiện lên.
b. Tắt mở định dạng màu cho ô:
Dùng Conditional Formating nhằm tô màu ô theo điều kiện giúp ta dễ tìm được những ô có giá trị đặc biệt cho trước. Excel 2010 có nhiều định dạng khác nhau cho giá trị số nằm trong khoảng cho trước. Nhưng biện pháp để mở tắt bằng checkbox là không có sẵn.
Tương tự như phần trên, ta tạo ra 1 checkbox hoặc 1 Toggle Button link tới ô $C$2. Nhưng lần này ta đặt name cho nó là IsFill. Chẳng hạn., ta cũng đặt name cho ô $A$2 là BeginNum và $B$2 là EndNum, với A2 là giới hạn dưới thí dụ 100, và B2 là giới hạn trên thí dụ 1.000.
Trong vùng dữ liệu B5:B16, ta muốn giá trị nào nằm trong khoảng BeginNum và EndNum sẽ được tô màu. Vậy dùng conditional Formating như trên, chọn vùng C8:C18, lần này công thức là: =AND($C8>=BeginNum,$C8<=EndNum,IsFill) Chọn cho nó 1 định dạng màu theo ý muốn.
Kết quả: khi nhấn button hoặc click checkbox thay đổi trạng thái thành True, các ô chứa s trong khoảng (100, 1.000) sẽ được tô màu, các ô còn lại không tô. Khi thay đổi thành False, các ô trở lại bình thường.
Đồng thời, vì bạn đặt công thức liên quan đến BeginNum và EndNum, nên khi thay đổi 2 s này, kết quả tô màu cũng thay đổi.
Bạn thấy đấy, nếu bạn chưa xem bài này mà thấy 1 file tương tự của người khác, bạn có thể lầm tưởng người ta sử dụng code của VBA.
4. Đá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 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ấ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:
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ậ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: = sFormula(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 : =" sFormula(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.
5. Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn
Khi nhập dữ liệu hay khi sao chép và dán dữ liệu từ nguồn khác vào Excel thì các ký tự không mong muốn sẽ xuất hiện trong toàn bộ bảng tính của bạn. Sử dụng chiêu này bạn có thể khắc phục được những rắc rối khi gỡ bỏ các ký tự không mong muốn bằng tay. Chức năng thay thế (replace) trong Excel có thể giúp bạn gỡ bỏ các ký tự không mong muốn trong bảng tính, nhưng phải qua một vài bước phụ.
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.
PHẦN 5
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.
6. Chuyển đổi con số dạng văn bản sang số thực
Các giá trị số (number) trong Excel được mặc định canh lề phải và văn bản (text) thì canh lế trái. Do vậy, cách đơn giản để nhận biết các giá trị số và văn bản trong một cột trên bảng tính là bạn thiết lập chế độ canh lề mặc định cho cột đó. Bạn vào Home → nhóm Alignment → chọn Format Cells → vào Tab Alignment → chọn General tại hộp Horizontal để thiết lập việc canh lề mặc định cho cột đang chọn → nhấn OK để đóng hộp thoại Format Cells lại.
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.
Dùng Paste Special để chuyển giá trị số dạng văn bản sang số thực
Đây là cách nhanh và dễ dàng nhất để chuyển các giá trị số dạng 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.
