›
›

Tuyệt chiêu sử dụng Excel - Chiêu thứ 22: Tạo hiệu ứng 3D trong các bảng tính hay các ô

pdf 87 trang ngocly 4290
Download
Bạn đang xem 20 trang mẫu của tài liệu "Tuyệt chiêu sử dụng Excel - Chiêu thứ 22: Tạo hiệu ứng 3D trong các bảng tính hay các ô", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdftuyet_chieu_su_dung_excel_chieu_thu_22_tao_hieu_ung_3d_trong.pdf

Nội dung text: Tuyệt chiêu sử dụng Excel - Chiêu thứ 22: Tạo hiệu ứng 3D trong các bảng tính hay các ô

  1. Chiêu thứ 22: Tạo hiệu ứng 3D trong các bảng tính hay các ô Bất cứ khi nào bạn nhìn thấy một hiệu ứng 3D trong một chương trình hoặc một ứng dụng, chẳng hạn như Excel, ắt hẳn bạn sẽ thấy một sự đánh lừa qua thị giác được tạo ra bởi cách định dạng đặc biệt. Chính bạn cũng có thể tạo ra hiệu ứng này một cách dễ dàng bằng cách định dạng một ô hoặc một dãy các ô. Phiên bản Excel 2007 đã đưa vào các styles của ô, vì vậy bạn có thể tạo hiệu ứng 3D và lưu nó lại để sử dụng vào bất cứ khi nào bạn muốn. Hãy bắt đầu với một ví dụ đơn giản, chúng ta sẽ tạo hiệu ứng 3D cho một ô để nó nhìn nổi lên trên giống như một nút lệnh(button). Trong một bảng tính trống, bạn chọn ô D5. (Bạn chọn ô D5 bởi vì nó không nằm rìa bảng tính). Dưới Cell Options ở tab Home, chọn Format ➝ Format Cells ➝ Border (với phiên bản trước Excel 2007: Format ➝ Cells ➝ Border). Hoặc các bạn có thể click chuột phải, chọn Format cells ➝ Border. Trong hộp line, chọn đường dày nhất thứ 2 (bên phải, thứ 3 từ dưới đếm lên). Chắc chắn rằng màu được chọn là màu đen (hoặc chọn automatic nếu bạn chưa thay đổi mặc định của lựa chọn này). Bây giờ click chọn đường viền bên phải và đường viền bên dưới ở bảng
  2. Quay trở lại với box color để chọn màu trắng. Vẫn chọn đường viền dày thứ 2, và chọn hai đường viền còn lại là đường viền bên trên và bên trái của ô.
  3. Chọn tab Fill (với phiên bản trước Excel 2007: chọn tab Patterns) trong hộp thoại Format Cells và chọn màu xám. Click chọn OK và thôi không chọn ô D5 nữa (di chuyển con trỏ chuột sang ô khác). Ô D5 sẽ xuất hiện nổi lên giống như một button. Bạn đã làm tất cả điều đó chỉ với các đường viền và bóng đổ. Nếu muốn vui hơn và đa dạng hơn, bạn tạo một ô nhìn giống như là bị lõm hoặc thụt vào trong thì chọn ô E5 (vì nó kế ô D5 và sẽ sử dụng cho bài tập tiếp theo).
  4. Chọn Home ➝ Cells ➝ Format ➝ Format Cells ➝ Bord (với phiên bản trước Excel 2007: Format ➝ Cells ➝ Border) và chọn đường viền dày nhất thứ 2 (thứ 3 bên phải từ dưới đếm lên) trong bảng line và chắc chắn màu được chọn là màu đen cho đường viền ở trên và bên trái. Chọn màu trắng trong bảng màu cho đường viền ở bên phải và bên dưới ô. Chọn tab Patterns và chọn màu xám. Click OK. Ô E5 sẽ bị lõm xuống. Việc này trái ngược với ô D5 là hiệu ứng trội lên. Nếu bạn cảm thấy thích thú với style của ô mà bạn đã tạo ra. Bạn lưu lại bằng cách chọn Home ➝ Styles ➝ Cell Style ➝ NewCell Style, bạn gõ tên mà bạn muốn đặt cho style này vào và click OK. Chú ý rằng các Styles của ô chỉ được lưu lại ở workbook hiện tại mặc dù bạn có thể kết hợp các Styles này từ những workbooks khác. Lựa chọn này không có trong các phiên bản trước Excel 2007. Nếu bạn muốn lưu một style của ô trong các phiên bản trước thì bạn vào Format➝ Style. Sử dụng hiệu ứng 3D cho một bảng dữ liệu Kế tiếp, chúng ta sẽ làm thí nghiệm với công cụ này để thấy được các loại hiệu ứng 3D mà bạn có thể tạo ra cho các bảng hoặc các sheets của mình. Chọn ô D5 và E5, click chọn công cụ Format Painter (biểu tượng cái chổi quét ) dưới Clipboard options trên tab Home (với phiên bản trước Excel 2007: xem trên thanh standard toolbar). Trong khi nhấn chuột trái, Click chọn ô F5 và rê chuột qua ô J5, sau đó nhả ra.
  5. Bây giờ chọn vùng D5:J5 và click chọn công cụ Format Painter một lần nữa. Cùng lúc nhấn chuột trái, chọn ô D6, kéo chuột ngang sang phải và kéo xuống ô J15, sau đó nhả ra. Hiệu ứng 3D của một dãy ô Nếu bạn muốn lưu style của bảng này, bạn chọn Home ➝ Styles ➝Format as Table ➝ NewTable Style. Với các phiên bản trước Excel 2007 thì bạn không thể lưu style của một bảng. Chúng ta đã sử dụng một đường viền dày để chắc rằng hiệu ứng được thấy một cách rõ ràng, tuy nhiên, bạn có thể tạo ra một sự tinh tế hơn bằng cách sử dụng một đưởng mảnh hơn.
  6. Bạn cũng có thể sử dụng các đường khác để tạo ra những hiệu ứng hay hơn. Cách dễ nhất để tìm ra những sự kết hợp có hiệu quả là bạn nên thử và kiểm tra sai sót trên một bảng tính trống để tạo ra hiệu ứng mà bạn muốn. Bạn chỉ bị giới hạn bởi sự tưởng tượng và có lẽ là khiếu thẩm mỹ. Thí dụ dùng màu khác Thí dụ dùng đường kẻ không liền nét cho những đường kẻ ngang
  7. Hãy luôn luôn nhớ rằng hiệu ứng 3D có thể làm cho bảng tính dễ đọc, nhìn có vẻ chuyên nghiệp và dễ cảm nhận hơn, nhưng khi chúng được sử dụng quá mức chúng có thể có những ảnh hưởng trái ngược. Nên nhớ, sử dụng mọi thứ có mức độ. Nếu bạn muốn tạo ra đi xa hơn và cung cấp những hiệu ứng 3D một cách tự động và sinh động, bạn có thể kết hợp 3D với định dạng có điều kiện để tự động cung cấp nhiều style mà bạn thích. Bổ sung: Bạn có thể dùng chính hiệu ứng 3D cho 1 ô (đã thực hiện ở trên) để dùng như 1 button thực thụ, nếu bạn kết hợp với VBA. Giả sử bạn muốn bảng tính của bạn có 1 button (nổi, đương nhiên), khi nhấn vào nó sẽ chìm xuống và thực thi 1 macro nào đó. Khi nhấn 1 lần nữa thì nút này sẽ nổi lên và thực hiện 1 macro khác hoặc dừng thực hiện macro thứ nhất. Dùng công cụ ghi macro để ghi lại quá trình lập hiệu ứng 3D cho 1 ô D5, ta được 1 đọan code trong Module, sửa lại chút đỉnh với 1 biến Public IsRun để tuỳ trường hợp gán Border thích hợp: Sub Change1() With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = IIf(IsRun, 2, 0) End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = IIf(IsRun, 2, 0) End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium
  8. .ColorIndex = IIf(IsRun, 0, 2) End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = IIf(IsRun, 0, 2) End With End Sub Bây giờ dựa vào sự kiện SelectionChange của sheet, ta làm cho ô D5 thay đổi border như sau: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$D$5" Then If Target = "Run" Then Target = "Stop" IsRun = False Else Target = "Run" IsRun = True End If Change1 Cells(1, 1).Select End If End Sub Chiêu thứ 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox Bạn thường dùng Data Validation để giới hạn dữ liệu nhập vào, hoặc dùng Conditional Formatting để cảnh báo khi nhập dữ liệu trùng. Nhưng đôi khi bạn muốn tắt chúc năng cảnh báo của CF. Có 1 cách đơn giản để bật tắt chức năng này bằng cách sử dụng 1 checkbox. Xem thêm bài Chiêu số 18: Điều khiển Conditional Formating bằng checkbox. Nhưng ở đây là CF tô màu dữ liệu nhập trùng. Trong thí dụ dưới đây, bạn sẽ dùng CF để đánh dấu (tô màu) những ô có dữ liệu
  9. xuất hiện nhiều hơn 1 lần trong vùng dữ liệu. Giả sử trong vùng A1:A100 bạn đã thiết lập CF với công thức như sau: =CountIf($A$1:$A$100,$A1)>1 Bây giờ bạn muốn tuỳ ý bật tắt cái CF này. Trước tiên bạn cùng công cu Form hoặc Control Toolbox, vẽ 1 cái Checkbox lên sheet. Thiết lập Property Cell Link cho cái checkbox này là 1 ô nào đó, thí dụ K1. Ta vào Name box đặt nuôn tên cho ô này là IsCheck. Bây giờ click chọn vùng dữ liệu A1:A100 sao cho ô A1 là ô hiện hành. Thiết lập Conditional Formatting cho vùng này với công thức sau: =AND(COUNTIF($A$1:$A$100,$A1)>1,IsCheck) Nhấn nút Format để tô màu theo ý muốn, rồi OK. Sau khi hoàn thành, khi bạn check vào cái checkbox, ô K1 sẽ thành True, và những ô có dữ liệu trùng sẽ bị tô màu. Trái lại, khi bạn uncheck cái checkbox, ô K1 sẽ có giá trị False, và chẳng có ô nào bị tô màu nữa. Cũng như chiêu số 18, bạn hoàn toàn có thể dùng cái toggle Button làm 1 cái công tắc bật tắt y như cái checkbox. Chắc cũng cần nói lại cách mà công tắc này hoạt động: Do công thức của bạn là AND(điều kiện 1, điều kiện 2), nên chỉ cần 1 trong 2 điều kiện không thoả (=False), nguyên đk chung sẽ False, CF không hoạt động; vì CF chỉ có tác dụng khi điều kiện chung là True. Chiêu thứ 24: Dùng nhiều List cho 1 Combobox Excel cung cấp cho bạn những cách để chọn 1 thứ trong 1 danh sách để nhập liệu vào 1 ô. Trong đó có công cụ combobox, từ danh sách xổ xuống của combobox, bạn có thể chọn 1 ngày trong tuần, 1 tháng trong năm, hoặc 1 sản phẩm trong danh sách. Nếu bạn cần chọn lựa trong 3 danh sách khác nhau, bạn sẽ nghĩ rằng bạn cần 3 cái combobox.
  10. Thay vì vậy, bạn dùng chiêu sau đây, kết hợp 1 combobox với 3 option button. Trong thí dụ sau, bạn hãy tạo 1 bảng tính, điền số từ 1 đến 7 vào vùng A1:A7, điền từ chủ nhật đến thứ hai vào vùng B1:B7, và điền tháng từ tháng 1 đến tháng 7 vào vùng C1:C7. Một cách nhanh nhất để điền 21 ô này là hãy gõ 1 vào A1. Trong khi nhấn giữ Ctrl, nắm cái fill handle của ô A1 và kéo xuống A7. Tiếp theo, gõ Sunday vào B2, doubled-click vào fill handle của B1. Cuối cùng gõ Jan vào ô C1, doubled-click vào fill handle của C1. Xong. Bây giờ bạn vào Developer ➝ Controls ➝ Insert ➝ Form Controls (với Excel 2003, View ➝ Toolbars ➝ Forms) và nhấn icon Option Button. Vẽ 3 cái lên sheet. Vẽ thêm 1 cái Groupbox bao quanh 3 cái Option cho đẹp. Bây giờ vẽ 1 cái Combobox ở 1 chỗ thích hợp. Nhấn chuột phải vào các Option, chọn Edit Text, sửa các từ Option1, Option 2, Option 3, Groupbox1 như hình: Nhấn chuột phải vào 1 Option bất kỳ, chọn Format Control, trong tab Control, gõ $F$1 vào ô Cell Link.
  11. Ở ô D6 gõ công thức =ADDRESS(1;$F$1)&":"&ADDRESS(7;$F$1) Vào Define Name, đặt mới 1 name MyRange, công thức là =INDIRECT($D$6) Nhấn chuột phải vào cái combobox, vào Format control, trong Cell link gõ MyRange, Cell link là $G$1. Bây giờ khi bạn chọn 1 trong các option, ô F1 thay đổi các giá trị từ 1 đến 3, ô D6 thay đổi với các giá trị $A$1:$A$7, $B$1:$B$7, $C$1:$S$7. Và list trong combobox cũng đổi theo. Bổ sung: Có 1 vấn đề là 3 list không phải lúc nào cũng dài bằng nhau (bằng 7 trong thí dụ trên), thí dụ số từ 1 đến 10, thứ trong tuần từ Chủ nhật đến thứ bảy, tháng từ 1 đến 12. Vậy ta sẽ phải làm gì? Ta hãy làm từ từ nhé, trước tiên là cách dùng ô phụ (tác giả cũng dùng 2 ô phụ). Gõ công thức sau vào ô G1: =COUNTA(INDIRECT(ADDRESS(1;$F$1)&":"&ADDRESS(100;$ F$1)))
  12. Với công thức trên, G1 sẽ có các giá trị là 10, 7, 12 tương ứng với F1 là 1, 2, 3. Sửa tiếp số 7 chết trong công thức ô D6 thành $G$1 cho động: =ADDRESS(1;$F$1)&":"&ADDRESS($G$1;$F$1) Kết quả hoàn toàn mỹ mãn. Ô D6 sẽ lần lượt là $A$1:$A$10, $B$1:$B$7, $C$1:$C$12 Bây giờ là cách GPE: dấu (bỏ) các ô phụ. Hiện Name MyRange của ta là =INDIRECT($D$6), Sửa bước thứ nhất là thay $D$6 bằng công thức của D6: =INDIRECT(ADDRESS(1;$F$1)&":"&ADDRESS($G$1;$F$1)) Kế đó thay $G$1 bằng công thức của G1: =INDIRECT(ADDRESS(1;$F$1)&":"&ADDRESS(COUNTA(INDIR ECT(ADDRESS(1;$F$1)&":"&ADDRESS(100;$F$1)));$F$1))
  13. Ta có thể đàng hoàng xoá ô D6 và ô G1. Chỉ còn 1 ô F1 là link cell của 3 options, ta format nó thành chữ trắng là OK. Chiêu thứ 25: Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác Trước tiên, chúng ta cần chuẩn bị một số dữ liệu để thực hành bài này. Bạn hãy tạo một sheet mới và đặt tên là List và tại ô A1 nhập vào tiêu đề “Objects”. Tại ô B1 nhập vào tiêu đề “Corresponding List”. Vùng A2:A5 bạn nhập vào từ “Can”. Vùng A2:A9 nhập vào từ “Sofa”. Vùng A10:A13 nhập vào từ “Shower”. Vùng A14:A17 nhập vào từ “Car”. Sau đó, trong vùng B2:B17 bạn nhập vào các từ sau: Tin, Steel, Opener, Lid, Bed, Seat, Lounge, Cushion, Rain, Hot, Cold, Warm, Trip, Journey, Bonnet, và Boot. Tại ô C1 nhập vào tiêu đề “Validation List”. Kế đó, bạn nhập vào các từ sau tại các ô C2:C5: Can, Sofa, Shower và Car. Đây là danh sách chứa các từ duy nhất từ vùng A2:A17. Bạn có thể dùng chứa năng Advanced Filter để lọc ra danh sách duy nhất này bằng cách chọn vùng A2:A17 ➝ Data ➝ Sort & Filter ➝ Advanced (E2003: Data ➝ Filter ➝➝ chọn Unique Records Only ➝ chọn Filter the List in Place ➝ OK. Sau đó chép danh sách kết quả lọc duy nhất và dán vào vùng C2:C5. Advanced Filter) Sau khi nhập xong bạn có danh sách như hình sau:
  14. Bước tiếp theo là chúng ta sẽ định nghĩa một số Name cho các List trên. Bạn vào Formulas ➝➝ Name Manager ➝ chọn New (E2003: Insert ➝ Name ➝ Define). Trong hộp Name bạn nhập vào từ “Objects”. Tại hộp Refer To bạn nhập vào công thức sau: Define Names =OFFSET($A$2,0,0,COUNTA($A$2:$A$20),1) Nhấn nút OK sau khi nhập xong công thức trên. Tiếp theo, bạn nhấn New (E2003: nhấn Add) để tạo Name mới. Trong hộp Name nhập vào tên là “ValList” và tại Refer To nhập vào =$C$2:$C$5. Nhấn Close để đóng hộp thoại lại. Bạn chèn thêm một sheet mới có tên là “Sheet1”. Bạn vào Formular ➝ chọn Define Names ➝Manager ➝ chọn New (E2003: Insert ➝ Name ➝ Define). Trong hộp Name bạn nhập vào “CorrespondingList” và tại Refer To nhập vào công thức sau: Name =OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,Objects,0) +1,2,,,"Lists")),0,0,COUNTIF(Objects,Val1Cell),1)
  15. Tiếp tục nhấn New (hoặc Add trong Excel 2003) để thêm Name mới. Trong hộp Name bạn đặt tên là Val1Cell và tại Refer To nhập vào =$D$6 và nhấn OK. Tiếp tục nhấn New để thêm Name mới với tên là Val2Cell và Refer To là =$E$6 nhấn OK sau khi hoàn tất. Các Name mà bạn đã tạo như hình sau: Chúng ta đã chuẩn bị xong các thứ cần thiết, bây giờ đã đến lúc áp dụng chức năng Data Validation. Bạn chọn ô $D$6 trên Sheet1, sau đó vào ngăn Data ➝ tại nhóm Data Tools bạn chọn Data Validation ➝ chọn ngăn Settings trong hộp thoại Data Validatin (E2003: Data ➝ Validation ➝ Settings). Bạn chọn loại List tại hộp Allow và tại hộp Source nhập vào =ValList, đánh dấu chọn In-cell dropdown, sau đó nhấn OK để hoàn tất.
  16. Chọn ô E6, sau đó vào ngăn settings trong hộp thoại Data Validation như trên. Bạn chọn List tại Allow và nhập vào =CorrespondingList tại hộp Source, đánh dấu chọn vào In-cell dropdown và nhấn OK để hoàn tất. Trong quá trình thiết lập Data Validation cho ô E6, bạn sẽ nhận được thông báo lỗi “The source currently evaluates to an error. Do you want to continue?”. Bạn hãy chọn Yes. Lỗi này xuất hiện là do ô D6 đang rỗng. Bạn thử chọn một tên trong danh sách tại ô D6 và xem sự thay đổi tương ứng trong danh sách tại ô E6 như hình minh họa sau:
  17. Chiêu thứ 26: 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 2007: 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ố
  18. 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. Chiêu thứ 27: 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 ngăn Alignment (E2003: Format ➝ Cells ➝ 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.
  19. 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ố 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 để
  20. 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. Dùng các hàm TEXT để chuyển đổi 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:
  21. 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:
  22. =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
  23. Chiêu thứ 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA Bạn thường lấy dữ liệu từ nguồn ngoài, chẳng hạn từ Internet, trong đó có dữ liệu số xen lẫn chữ như: “1,254.00VND” hoặc “USD 2,500.00”, thậm chí còn phức tạp hơn. Và cũng có khi bạn đã nhập liệu hỗn hợp text và số không theo quy luật nào để có thể lấy riêng số ra bằng các hàm tách chuỗi thông thường. Dùng 1 hàm tự tạo viết bằng VBA, bạn có thể trích xuất riêng phần số ra, dù cho chuỗi có kiểu dạng gì đi nữa. Bạn hãy nhấn Alt-F11 để vào cửa sổ VBA, insert 1 module và dán đoạn code sau vào: Function ExtractNumber(rCell As Range) Dim lCount As Long Dim sText As String Dim lNum As String sText = rCell For lCount = Len(sText) To 1 Step -1 If IsNumeric(Mid(sText, lCount, 1)) Then lNum = Mid(sText, lCount, 1) & lNum End If Next lCount ExtractNumber = CLng(lNum)
  24. End Function Vào lại Excel, trong ô B1 gõ dữ liệu số xen lẫn text tuỳ ý, trong ô kế bên C1 gõ công thức: =ExtractNumber(B1) Ta sẽ có kết quả như hình: Bổ sung: Vẫn còn chút vấn đề: nếu dữ liệu là số thập phân như ô B5, hoặc dữ liệu gồm 2 nhóm số riêng biệt trở lên như ô B4, kết quả sẽ không như ý muốn. 1. Để giải quyết vấn đề số thập phân, ptm0412 có 1 hàm khác: Function CtoN(Mystr As String, Optional Dautp As String) As Doub le Dim Kqng, Kqtp, Neg As Double, Kqtam As String Dim Sotp As Double, Le As Byte Neg = 1 Le = 0 For i = 1 To Len(Mystr) tam = Mid(Mystr, i, 1) Select Case tam Case 0 To 9 Kqtam = Kqtam & tam Case "-" Neg = -1
  25. Case Dautp Kqng = Kqtam Le = 1 Mystr = Right(Mystr, Len(Mystr) - i) Kqtp = CtoN(Mystr) Sotp = Kqtp * 10 ^ (-Len(Kqtp)) End Select Next i Select Case Le Case 0 CtoN = IIf(Kqtam = "", 0, Kqtam) Case 1 CtoN = Kqng + Sotp End Select CtoN = CtoN * Neg End Function Ghi chú:- Khi sử dụng hàm này, bạn sẽ thêm vào hàm 1 tham số cho biết dấu thập phân là dấu nào, “,” hay “.”. Thí dụ =CtoN(“USD 14255.20”,”.”), và nếu bạn biết chắc là số nguyên thì không cần thêm. - Hàm này đọc được cả số âm nếu ký hiệu số âm là dấu trừ và đứng trước số. 2. Để giải quyết vấn đề nhiều nhóm số khác nhau trong chuỗi, Ptm0412 cũng có 1 hàm: Function CtoNPlus(Mystr As String, sttchuoi As Byte, Optional Da utp As String) As Double Newstr = Mystr For i = 1 To sttchuoi If Len(Newstr) < 2 Then Exit For
  26. CtoNPlus = CtoN1st(Newstr, Dautp) Next i Newstr = "" End Function PHP Code: Function CtoN1st(ByVal Mystr As String, Optional Dautp As String ) As Double Dim Kqng, Kqtp, Neg As Double, Kqtam As String Dim Sotp As Double, Le As Byte, NewStr2 As String Neg = 1 Le = 0 For i = 1 To Len(Mystr) tam = Mid(Mystr, i, 1) Select Case tam Case 0 To 9 Kqtam = Kqtam & tam If IsNumeric(Mid(Mystr, i + 1, 1)) = False And _ Mid(Mystr, i + 1, 1) "." Then Newstr = Right(Mystr, Len(Mystr) - i) Exit For End If Case "-" Neg = -1 Case Dautp Kqng = Kqtam Le = 1 NewStr2 = Right(Mystr, Len(Mystr) - i) Kqtp = CtoN1st(NewStr2) Sotp = Kqtp * 10 ^ (-Len(Kqtp)) End Select Next i Select Case Le Case 0 CtoN1st = IIf(Kqtam = "", 0, Kqtam) Case 1
  27. CtoN1st = Kqng + Sotp End Select CtoN1st = CtoN1st * Neg End Function Cú pháp hàm: CtoNPlus(Mystr , sttchuoi, [Dautp]) Sttchuoi là số thứ tự nhóm số trong chuỗi, Dautp là ký tự dấu phân cách thập phân. Chiêu thứ 29: Tùy biến chú thích của ô bảng tính Khi chúng ta chèn chú thích cho ô bằng lệnh Review ➝ Comments ➝ New Comment (E2003: Insert ➝ Comment). Excel sẽ mặc định chèn tên người dùng của máy tính vào hộp chú thích. Bạn có thể thay đổi điều này bằng cách vào Office ➝ Excel Options ➝ Popular (E2003: Tools ➝ Options ➝ General). Bạn nhập lại đoạn văn bản mặc định mong muốn tại hộp User name:
  28. Mặc dù chú thích cho ô có mục đích chính là hiển thị các thông báo cho bạn hoặc cho người dùng khác, bạn có thể tùy biến nó nhằm mục đích làm rõ hơn chủ ý của bạn. Trước tiên, bạn cần tùy biến thanh Quick Access Toolbar (QAT) để thêm một nút lệnh vào đó, các bước thực hiện như sau: Office ➝ Excel Options ➝ Customize. Tại hộp Choose commands from bạn chọn Smart Art Tools | Format Tab ➝ nhấn nút Add để thêm vào QAT ➝ nhấn OK hoàn tất (E2003: gọi thanh Drawing bằng cách View ➝ Toolbars ➝ Drawing). Chèn một chú thích cho ô bằng cách vào ngăn Review ➝ tại nhóm Comments ➝ chọn New Comment (E2003: Insert ➝ Comment). Một hộp chú thích sẽ xuất hiện và bạn có thể nhập nội dung chú thích vào đó. Để thay đổi hình dạng của hộp chú thích này bạn nhấp trái chuột lên khung viền
  29. của nó để thoát khỏi chế độ nhập liệu ➝ chọn lệnh Change AutoShape từ thanh QAT ➝ chọn kiểu hình dạng mong muốn trong các nhóm hình Basic Shapes, Block Arrow, Flow Chart, Callouts và Stars and Banners ➝ sau khi chọn hình thì hình dạng hộp chú thích của ô sẽ thay đổi tức thì. Đối với phiên bản Excel 2007 thì việc hiệu ứng 3-D cho hộp chú thích được thiết lập mặc định và các tùy chọn Shadow Setting đã bị loại bỏ. Bạn có thể thay đổi các tùy chọn đổ bóng, hiệu ứng 3-D trong phiên bản Excel trước đó như sau: chọn khung viền của hộp chú thích ➝ vào thanh Drawing ➝ chọn nút lệnh Shadow Settings ➝ chọn kiểu mong muốn:
  30. Thêm ảnh vào chú thích Một điều cũng khá thú vị là bạn có thể dùng các hộp chú thích để hiển thị các ảnh mà không làm ảnh hưởng đến các nội dung khác. Thí dụ như, bạn có thể chèn một đồ thị vào hộp chú thích nhằm minh họa tốt hơn cho các số liệu mà không cần phải lúc nào cũng hiện đồ thị lên. Để thêm hình, bạn chọn ô đang có chú thích ➝ nhấp phải chuột lên ô ➝ chọn Edit Comment ➝ chọn khung viền của hộp chú thích ➝ nhấp phải chuột ➝ chọn Format Comment (E2003: nhấp chuột 2 lần liên tiếp lên khung viền của hộp chú thích) ➝ chọn ngăn Colors and Lines ➝ tại hộp Color, chọn Fill Effects ➝ chọn nút Select Picture ➝ chọn hình và nhấn Insert ➝ OK ➝ OK.
  31. Trích xuất nội dung chú thích Để trích xuất nội dung trong hộp chú thích, chúng ta cần viết một hàm người dùng đơn giản. Bạn nhấn tổ hợp ALT+F11 để vào cửa sổ VBE hoặc vào ngăn Developer ➝ Code ➝ Visual Basic (E2003: Tools ➝ Macro ➝ Visual Basic Editor), sau đó vào Insert ➝ chọn Module ➝ nhập vào đoạn mã sau:
  32. VB Code: Function GetCommentText(rCommentCell As Range) Dim strGotIt As String On Error Resume Next strGotIt = WorksheetFunction.Clean(rCommentCell.Comment.Text) GetCommentText = strGotIt On Error GoTo 0 End Function Vào File ➝ Save (Ctrl+S) để lưu Module, sau đó vào File ➝ Close and Return to Microsoft Excel (ALT+Q) để trở về cửa sổ bảng tính. Bạn vào một ô trống nào đó và nhập vào: =GetCommentText(B2) Với B2 là ô đang có chú thích. Nhấn Enter để xem kết quả. Chiêu thứ 30: Sort thứ tự dựa trên nhiều hơn ba cột Chức năng sort của Excel bị giới hạn với sort trong phạm vi 3 cột. Trong hầu hết các trường hợp, sort theo 3 cột là đủ nhưng đôi khi bạn cần sort nhiều hơn 3 cột dữ liệu. Chiêu này sẽ giúp bạn vượt qua hạn chế này. Với ví dụ sau, chúng tôi giả sử bạn có các cột dữ liệu liên quan với nhau A, B, C, D và E, và bạn muốn sort dữ liệu này đầu tiên theo cột A, tiếp theo là cột B, kế đến là cột C, tiếp nữa là cột D và cuối cùng là cột E. Để làm điều này, bạn sort lần lượt theo thứ tự ngược: sort theo cột cuối cùng trước và tiếp theo tuần tự ngược đến cột đầu tiên. Chọn các cột từ A đến E, sau đó chọn Data ➝ Sort. Chọn để sort theo thứ tự cột C đầu tiên, tiếp theo là đến cột D và sau
  33. đó đến cột E. Nhấn vào sort. Bây giờ chọn các cột từ A đến E và chọn Data ➝ Sort. Lúc này, sort theo thứ tự cột A trước rồi mới đến cột B. Click sort và mọi thứ sẽ được sắp xếp theo thứ tự. Excel đã sort theo 5 cột thay vì 3 cột. Nếu bạn muốn Excel tự động làm điều này, bạn có thể sử dụng một macro mà sẽ sort theo vùng chọn và giả định rằng dữ liệu của bạn có các tiêu đề cột được xác định và định dạng ở hàng đầu tiên của vùng chọn. Nếu các tiêu đề được in đậm, Excel sẽ cho biết chúng là các tiêu đề và sẽ không sort chúng. Thay vào đó, nó sẽ sort cột đầu tiên bên trái trước rồi mới tới cột bên phải cho đến tối đa là 256 cột. Đoạn code macro bạn cần sử dụng phải được đặt trong một module chuẩn. Để làm điều này, bạn chọn Tools ➝ Macro ➝ Visual Basic Editor (Hoặc nhấn Alt+F11), sau đó chọn Insert ➝ Module và gõ đoạn code sau vào: Sub SortByX( ) Dim i As Long For i = Selection.Columns.Count To 1 Step -1 Selection.Sort Key1:=Selection.Cells(2, i), _ Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom Next i End Sub Để quay lại excel, bạn đóng cửa sổ code này lại hoặc nhấn Alt+ Q. Ngay khi bạn dán đoạn code này vào, bạn đã có thể thực hiện được việc sort này: Tô chọn vùng cần sort, kể cả tiêu đề, rồi chạy code. Chiêu thứ 31: Sắp xếp ngẫu nhiên Bạn có thể dùng Excel để chọn ra một cách ngẫu nhiên ba người thắng cuộc – 1, 2 và 3 từ một danh sách trên bảng tính. Để thực hiện điều này một cách dễ dàng nhất và công bằng nhất, bạn nên dùng hàm RAND kết hợp với tính năng sắp xếp của Excel. Giả sử bạn có một bảng dữ liệu như hình sau:
  34. Tại ô A2 bạn nhập vào hàm =RAND và sao chép xuống đến ô A10. Bạn có thể sắp xếp các cột Name, Age và ID No. theo cột A và từ đó chọn ra được ngẫu nhiên 3 người thắng cuộc. Hàm RAND sẽ được tự động tính lại mỗi khi có sự thay đổi trên bảng tính, do vậy chúng ta có thể nhấn F9 để ép Excel tính toán lại và từ đó chọn ra được những phần tử trong danh sách một cách ngẫu nhiên. Tuy nhiên chúng ta cần phải tạo một thủ tục sắp xếp lại dữ liệu sau khi Excel tạo ra bộ số ngẫu nhiên mới. Để việc chọn lựa được dễ dàng, chúng ta sẽ ghi một Macro và gán nó vào một nút lệnh trên bảng tính. Mỗi khi muốn chọn ra nhóm người thắng cuộc thì bạn chỉ cần nhấn nút lệnh này. Bạn làm theo hướng dẫn sau: Bước này bạn sẽ tiến hành ghi Macro, bạn chọn một ô bất kỳ và vào Developer ➝ Code ➝ Record Macro (E2003: Tools ➝ Macro ➝ Record New Macro ). Chọn 4 cột A, B, C và D rồi nhấn F9. Vào Data ➝ Sort & Filter ➝ Sort ➝ chọn sắp xếp cột A theo thứ tự tùy ý (nhỏ ➝ lớn hoặc lớn ➝ nhỏ).
  35. Bước tiếp theo, bạn hãy vào ngăn Developer ➝ nhóm Controls ➝ Insert (E2003: View ➝ Toolbars ➝ Forms) ➝ chọn Button (Form Control) và vẽ thành một nút trên bảng tính ➝ chọn tên Macro vừa ghi ➝ nhấn OK để hoàn tất. Bạn chọn cột A và ẩn nó để người dùng không thấy các số ngẫu nhiên. Mỗi lần bạn nhấp chuột lên nút Pick Winner thì dữ liệu của bạn sẽ được sắp xếp một cách ngẫu
  36. nhiên và bạn có thể dễ dàng chọn ra 3 người thắng cuộc. Lưu ý: Hàm RAND trong các phiên bản trước Excel 2003 có tồn tại lỗi trong việc phát số ngẫu nhiên. Về mặt lý thuyết, hàm RAND chỉ trả về các giá trị từ 0 đến 1, điều này không phải luôn luôn đúng khi phát nhiều số ngẫu nhiên vì đôi khi hàm trả về giá trị nhỏ hơn 0. Xem thêm thông tin về việc Microsoft thay đổi giải thuật phát số ngẫu nhiên trong các phiên bản Excel 2003 & 2007 tại Chiêu thứ 32: Thao tác trên dữ liệu với Advanced Filter Nếu bạn đã quen với công cụ Auto Filter, bạn hẳn đã thấy những hạn chế của AutoFilter. Nếu bạn cần xử lý chọn lọc dữ liệu ngoài giới hạn này, Advanced Filter là 1 chọn lựa tốt. Dù cho bị hạn chế, AutoFilter cũng đã là 1 công cụ hữu ích để chỉ thể hiện 1 phần dữ liệu lên màn hình theo 1 vài điều kiện nào đó. Nhưng đôi khi bạn không thể chọn lọc thông tin cần thiết khi dùng AutoFilter. Xin nói thêm, Excel 2003 và kể cả Excel 2007 chỉ có thể lọc 1 lần 2 điều kiện (And hoặc Or) cho 1 cột. Hơn thế, nếu bạn muốn lọc dữ liệu theo 2 tiêu chí trên 2 cột, thì chỉ có thể kết hợp bằng toán tử And giữa các điều kiện của 2 cột. Advanced Filter sẽ hữu dụng hơn nhiều. Tuy nhiên nếu bạn muốn dùng AdF, bạn
  37. phải sắp xếp dữ liệu đúng chuẩn như lời nói đầu (bài 1, 2, 3): - chừa ít nhất 3 dòng trống trên cùng bảng dữ liệu - Dùng 1 dòng duy nhất làm tiêu đề bảng dữ liệu - Không merge bất cứ ô nào của bảng dữ liệu Khi sử dụng AdF, bạn sẽ phải dùng tiêu đề cột làm tiêu chí lọc, nên bạn sẽ phải copy tiêu đề cột dán vào chỗ nào đó trên dòng 1. Nhưng tốt nhất bạn nên dùng 1 công thức chẳng hạn như =A4 để bảo đảm rằng bất cứ lúc nào điều kiện lọc cũng đúng, dù cho bạn có thay đổi tiêu đề cột. Dùng công thức này cho tất cả các cột mà bạn muốn làm tiêu chí lọc. Sau đó ngay dưới mỗi tiêu đề (dòng 2, và 3) bạn để những điều kiện lọc. Những điều kiện lọc sẽ dùng những toán tử so sánh như: =, >, =, . Ghi nhớ 1 điều rằng những điều kiện Or cần sắp xếp theo chiều đứng, những điều kiện And sẽ sắp theo hàng ngang. Do đó nếu bạn cần lọc theo 2 điều kiện And và cùng 1 tiêu chí, thì phải dùng 1 tiêu đề cột trên 2 ô. Sau đây là các hình minh hoạ: Các điều kiện Or:
  38. Các điều kiện And:
  39. Kết hợp 2 And và 1 Or: (MLuong 400 And BPhan=”KT”) Or BPhan=”HC” Kết hợp 2 And và 2 Or: (MLuong 400 And BPhan=”KT”) Or (MLuong 400 And BPhan=”HC”)
  40. Trên đây chỉ là những thí dụ đơn giản, mà bạn có thể sử dụng AutoFilter. Bây giờ ta áp dụng AdF cho vài thí dụ mà AutoFilter không làm được Bây giờ ta áp dụng AdF cho vài thí dụ mà AutoFilter không làm được: Bất cứ khi nào áp dụng AdF cho điều kiện là công thức như các trường hợp dưới đây, bạn phải nhớ không dùng tiêu đề của bảng dữ liệu làm tiêu đề cho điều kiện. Bạn hãy đặt 1 tên cho điều kiện, hoặc để trống. Thí dụ 1: Bạn muốn lọc dữ liệu cột mức lương với những giá trị thoả điều kiện nhỏ hơn mức lương trung bình, thì dùng công thức sau: =D6<AVERAGE($D$6:$D$22), kết quả sẽ là True hoặc False, bạn đừng quan tâm, mà cứ dùng AF, ô D2 là giá trị trung bình tính sẵn 468,8235 để bạn tiện theo dõi:
  41. Bạn cũng có thể kết hợp với điều kiện bộ phận là KT: Lưu ý rằng D6 là 1 tham chiếu tương đối trong khi $D$6:$D$22 là 1 tham chiếu tuyệt đối. Điều này là cần thiết để Excel dò tìm lần lượt D6, D7, trong vùng cố định $D$6:$D$22, hễ giá trị nào của D6, D7, mà có kết quả True thì cho hiện, False thì ẩn dòng. Đó là cách mà anh Bill ta thực thi AdF. Thí dụ 2: Nhận thấy rằng có những mức lương mà nhiều người hưởng, và có những mức lương mà chỉ 1 người có, ta muốn lọc ra những người có mức lương trùng với 1 người nào khác, ta dùng công thức như sau:
  42. =COUNTIF($D$6:$D$22;D6)>1 Còn nếu muốn lọc những người có mức lương độc, chỉ 1 người có, ta dùng công thức: =COUNTIF($D$6:$D$22;D6)=1 Bây giờ nếu bạn muốn lọc ra chỉ những mức lương không trùng nhau, cho ra 1 cột khác thì làm như sau: khi mở hộp thoại AF, hãy click vào ô Copy to another
  43. location, click chọn vào ô Unique records only, chọn thêm ô sẽ copy sang ở ô Copy To, và để trống vùng Criteria: Kết quả:
  44. Chiêu thứ 33: Tạo các định dạng số cho riêng bạn Excel đã có những định dạng số của riêng nó nhưng thỉnh thoảng bạn cần sử dụng một định dạng số không có sẵn trong Excel. Sử dụng chiêu này, bạn có thể tạo ra các định dạng số mà bạn có thể điều chỉnh cho nhu cầu riêng của bạn. Trước khi bạn thử những chiêu này, sẽ có ích cho bạn nếu bạn hiểu được làm thế nào Excel nhận biết được các định dạng của ô. Excel nhận biết một định dạng của ô khi có 4 phần sau (từ trái sang phải): số dương, số âm, giá trị 0, và giá trị chuỗi. Mỗi phần được ngăn cách bởi một dấu chấm phẩy (;). Khi bạn tạo ra một định dạng số cho riêng bạn, bạn không cần phải định dạng tất cả 4 phần này. Nói cách khác, nếu định dạng của bạn chỉ có hai phần thì phần đầu tiên được sử dụng cho cả các số dương và các giá trị 0, trong khi phần thứ 2 sẽ được sử dụng cho số âm. Nếu định dạng của bạn chỉ bao gồm một phần, tất cả các loại số sẽ dùng một định dạng. Chuỗi bị ảnh hưởng bởi các định dạng riêng chỉ khi bạn sử dụng tất cả 4 phần, phần cuối sẽ sử dụng cho chuỗi . Đừng cho rằng những định dạng riêng chỉ áp dụng chỉ cho các dữ liệu kiểu số. Các định dạng số cũng áp dụng cho các dữ liệu kiểu chữ. Định dạng riêng cho số được hiển thị ở hình 2-18 là định dạng tiền tệ chuẩn của Excel, nó chỉ ra số tiền âm là màu đỏ. Nếu bạn gõ một số dương tương ứng với một giá trị tiền tệ, Excel sẽ định dạng nó
  45. ngay lập tức bao gồm dấu phẩy (,) ngăn cách hàng ngàn, theo sau hai số lẻ thập phân. Excel sẽ định dạng tương tự cho giá trị âm, ngoại trừ số âm được hiển thị là màu đỏ. Bất kỳ giá trị 0 nào cũng sẽ không có ký hiệu tiền tệ và sẽ được hiển thị hai số lẻ thập phân (0.00). Nếu bạn gõ một chuỗi vào một ô, Excel sẽ hiển thị dòng chữ “Chuỗi hiển thị,” bất kể giá trị thật sự của chuỗi bạn gõ vào là gì. Điều quan trọng là phải chú ý, việc định dạng một giá trị của ô không ảnh hưởng đến giá trị đích thực của ô. Ví dụ, gõ bất kỳ số nào vào ô A1. Nhấn chuột phải và chọn Format Cells ➝ Number ➝ Custom, và sử dụng bất kỳ định dạng nào ngay tại con trỏ chuột đầu tiên (trong hộp "Type), gõ "Hello" (với dấu ngoặc kép ("")). Sau đó nhấn OK. Mặc dù ô hiển thị từ "Hello" nhưng bạn có thể thấy giá trị thật sự của ô bằng cách chọn ô và xem trên thanh công thức (Formula bar), hoặc nhấn F2. Nếu bạn tham chiếu ô này trong một công thức - ví dụ, =A1+20 - kết quả của ô sẽ dẫn đến định dạng lúc nãy, nghĩa là sẽ trả về kết quả là "Hello" (bạn hãy làm thử, bạn sẽ thấy kết quả ngay tức khắc!) Nếu bạn đã tham chiếu ô A1 cùng với những ô khác mà có bất kỳ định dạng chuẩn Excel nào - ví dụ, =Sum(A1:A10)- kết quả trả về sẽ vẫn theo định dạng riêng của ô A1. Excel đưa ra một giả thiết đã được thiết lập trước rằng bạn muốn kết quả của ô đã được định dạng giống như cách tham chiếu đến một hay nhiều ô. Nếu các ô tham chiếu chứa nhiều hơn một loại định dạng thì bất cứ định dạng riêng nào cũng sẽ được ưu tiên trước. Điều này có nghĩa là, bạn phải luôn luôn nhớ rằng Excel sử dụng một giá trị thật sự của ô để tính toán, và sẽ không lấy giá trị thể hiện của nó. Điều này có thể tạo ra ngạc nhiên khi Excel tính toán dựa trên các ô đã được định
  46. dạng không có số lẻ thập phân hoặc có một vài số lẻ thập phân, thí dụ: Để thấy được điều này, bạn gõ 1.4 vào ô A1 và 1.4 vào ô A2, định dạng cho cả hai ô là không có số lẻ thập phân theo sau bằng cách bạn chọn vùng A1:A2, click chuột phải, chọn Format Cells ➝ Number, trong khung "decimal places" chọn 0. Sau đó gõ công thức =A1+A2 vào một ô khác. Dĩ nhiên kết quả trả về sẽ là 3 vì Excel đã làm tròn. Excel có một lựa chọn được gọi là “Precision as Displayed”, bạn có thể tìm thấy bằng cách chọn Office button ➝ Excel Options ➝ Advanced (Với phiên bản trước Excel 2007, chọn Tools ➝ Options ➝ Calculation), nhưng bạn nên biết rằng định dạng này sẽ thay đổi vĩnh viễn các giá trị trong các ô từ đầy đủ các số lẻ thập phân (gồm 15 ký số) đến bất kỳ định dạng nào, bao gồm các số lẻ thập phân, được thể hiện. Nói cách khác, khi bạn đánh dấu chọn “Precision as Displayed” và nhấn OK thì nó sẽ không trả lại kết quả ban đầu. (Bạn có thể thử nhưng các thông tin về các số lẽ thập phân mở rộng sẽ bị mất đi.)
  47. Ghi chú thêm: để hiểu thêm điều này, bạn làm ví dụ như sau ô A1 bạn gõ 1.53, A2: 2.75, A3: 5.68, A4: 3.25, A5: sum(A1:A4). Kết quả bạn thấy được ở ô A5 là 13.21. Giờ bạn vào Office button ➝ Excel Options ➝ Advanced và chọn "Set precision as display". Chọn vùng A1:A4, bạn nhấn nút Decrease Decimal , bạn sẽ thấy phần thập phân bị làm tròn dần lên. Khi còn 1 số thập phân thì tổng là 13.30; khi không còn chữ số thập phân nào nữa thì tổng là 14. Bây giờ, bạn lại dùng nút Increase Decimal, bạn sẽ thấy phần số lẻ ở sau các số ban đầu bạn gõ vào đã trở thành sô 0 hết. Đây là điều bạn nên chú ý khi lựa chọn tùy chọn này. Tuy nhiên nếu ở các ô A1:A4 là công thức thì dữ liệu ban đầu của bạn vẫn còn nguyên. Ví dụ,
  48. thay vì gõ trực tiếp ô A1 là 1.53, bạn gõ =1.53 và tương tự như thế cho các ô khác và làm theo các bước hồi nãy và kiểm tra kết quả bạn sẽ thấy có sự khác biệt. Định dạng mặc định cho tất cả các ô trong Excel là General. Nếu bạn gõ một con số vào một ô, Excel thường sẽ đoán định dạng số nào là phù hợp nhất. Ví dụ, nếu bạn gõ 10% vào một ô, Excel sẽ định dạng ô đó là kiểu Percentage. Trong hầu hết các trường hợp thì Excel đoán khá chính xác nhưng thỉnh thoảng bạn cần thay đổi lại. Khi sử dụng định dạng cho các ô, hãy tránh việc bị cám dỗ để định dạng canh trái, canh phải hay canh giữa. Theo mặc định các số sẽ được canh phải và chuỗi sẽ được canh trái. Nếu bạn bỏ điều này, bạn có thể nhận biết thoáng qua dù cho ô đó là chuỗi hay số vì trong trường hợp ví dụ trước, ô A1 bạn thấy là chuỗi ("Hello") nhưng thực sự nó là một số. Mỗi phần của một định dạng được đưa ra sử dụng những mã định dạng riêng của nó. Các mã này ảnh hưởng đến Excel trong việc thể hiện dữ liệu theo cách mà bạn muốn. Vì vậy, thí dụ, giả sử bạn muốn số âm xuât hiện trong ngoặc đơn(), và tất cả các số, số dương, số âm và số 0 đều hiển thị 2 số lẻ thập phân. Để làm điều này, bạn click chuột phải chọn Format Cells, tab Number mục Custom, ở khung Type bạn gõ: 0.00_ ;(-0.00) Nếu bạn muốn số âm màu đỏ, bạn làm tương tự như trên nhưng trong khung Type bạn gõ: 0.00_ ;[Red](-0.00) Chú ý là hãy sử dụng dấu ngoặc vuông ([]) trước code. Code sử dụng cho định dạng nói cho Excel biết để tạo ra số màu đỏ. Bạn có thể sử dụng nhiều code định dạng khác nhau trong các mục ở Format cells/number/custom. Bảng 2-1 đến bảng 2-5 được lấy từ tài liệu của Microsoft đã giải thích những code này. Bảng 2.1: các mã về định dạng số:
  49. Mã số Mô tả General Định dạng số chung (gõ như thế nào thấy như thế ấy) Ký hiệu 0 đóng vai trò như số thế chổ cho một ký hiệu số. Ký hiệu 0 chỉ ra rằng nếu số được định dạng không có nhiều ký tự số như số 0 trong 0 (số 0) mã định dạng thì số 0 sẽ thế chổ số đó. Thí dụ nếu mã định dạng là 0000.000 thì số 123.45 mà bạn gõ vào sẽ được hiển thị là 0123.450 Ký hiệu # đóng vai trò như ký hiệu 0 chỉ khác là nó không buộc một ký # số phải thế chổ nếu không có số nào tương ứng. Ví dụ nếu mã định dạng là #,###.## thì số 1234.5 sẽ được hiển thị là 1,234.5. Ký hiệu này cũng giống ký hiệu 0 chí có khác là nếu trống chổ thì nó thay bằng một khoản trắng chứ không phải là số 0. Điều này hữu ích nếu bạn muốn canh thẳng đấu phân cách thập phân trong bảng. Ngoài ra ký ? hiệu ? trong cũng được sử dụng trong phần định dạng phân số. Ví dụ: # ???/??? thì số được thể hiện sẽ đuợc trình bày chính xác đến 3 con số và nếu có thể giản ước tốt thì nó hiện ra đến mức dưới 3 và có thêm khoản trắng. Nếu bạn sử dụng ký hiệu này thì khi hiển thi số Excel sẽ tự động nhân % số đó với 100 và thêm dấu này đằng sau. ,(dấu Dấu phân cách hàng ngàn. phẩy) E+, E- Thể hiện một số dưới dạng scientific thí dụ: mã là 0.00E+00 thì số ,e+,e- 12345.56 được thể hiên 1.23E+04 Bảng 2.2: Các mã định dạng chuỗi Mã chuỗi Mô tả %,- Các ký hiệu này được thể hiện trong mã định dạng thì sẽ được ,+,/,:,(),khoảng thể hiện trong phần hiển thị cúa số đó. Các ký hiệu khác muốn
  50. trắng được thể hiện ra thì bạn phải tham khảo ký tự \ bên dưới Đây là ký hiệu đặc biệt không thể hiện trong định dạng nhưng nó bắt buộc Excel thể hiện ký tự tiếp theo sau nó dù ký tự đó là ký \ tự gì ví dụ: \” thì dấu ” sẽ được thể hiện. Nếu bạn muốn thể hiện dấu \ thì bạn phải ghi trong mã \\ Trong trường hợp bạn muốn thể hiện một chuổi ký tự sau hay trước một số thì bạn bỏ chuỗi ký tự đó trong dấu ngoặc kép như "text" thế này."VND" #,##0.00 thì một số bất kỳ sẽ được thêm vào đằng trước “VND” Giống như ký hiệu \ nhưng khác một điều là Excel sẽ hiện ra ký * tự sau nó chiếm đến hết ô Ký hiệu này thường được dùng là _), nó nhắc nhở Excel chèn một khoảng trắng vào vị trí của nó. Ví dụ: trong một định dạng có dấu ngoặc bao quanh số âm, dấu chấm thập phân của một số dương không được canh thẳng hàng với dấu chấm thập phân của _ (dấu gạch số âm, bởi vì số âm sẽ mất nhiều vị trí hơn bên phải dấu thập dưới) phân vì phải thể hiện dấu đóng ngoặc. Trong trường hợp này bạn dùng một dấu _ tại cuối phần định dạng của số dương để Excel dành một khoảng trắng sau số dương: một khoảng trắng tương đương với vị trí dấu đóng ngoặc trong số âm. Ví dụ: #,##0_);(#,##0). Ký hiệu @ dùng để chỉ chuổi ký tự đã được nhập vào ô. Trong @ ví dụ ở phần trên ký hiệu @ thể hiện những gì đã nhập vào ô. Bảng 2.3: Các mã về định dạng ngày tháng năm Mã Mô tả ngày M Chỉ ra số tháng mà không có số 0 ở đầu với số tháng có một chữ số. Ví
  51. dụ: 1, 2, 12. Chỉ ra số tháng và có số 0 ở đầu với số tháng có một chữ số. Ví dụ: 01, Mm 02,03 12 Chỉ tháng nhưng là với 3 chữ đầu của tháng (theo tiếng Anh) thí dụ Mmm tháng 1 = Jan Mmmm Chỉ tháng và hiện đầy đủ tên tháng thíí dụ tháng 1 = January D Chỉ ngày với 1 chữ số đối với số nhỏ hơn 10 Dd Chỉ ngày vơi 2 chữ số đối với số nhỏ hơn 10 ví dụ ngày 8 thành 08 Ddd Chỉ thứ trong tuần với 3 ký tự thí dụ thứ Hai = Mon, thứ Ba = Tue Chỉ thứ trong tuần với đầy đủ các ký tự. Thí dụ: Monday, Tuesday, Dddd Wednesday Yy Chỉ năm với 2 số cuối của năm. Ví dụ: năm 2008 = 08, năm 2009 = 09 Yyyy Chỉ năm vơi đủ 4 số của năm. Ví dụ: 2008, 2009, 2010 Bảng 2.4: Các mã định dạng về thời gian Mã ngày Mô tả H Chỉ giờ với 1 chữ số Hh Chỉ giở với 2 chữ số m Chỉ phút với 1 chữ số mm Chỉ phút với 2 chữ số s Chỉ giây với 1 chữ số ss Chỉ giây với 2 chữ số
  52. AM/PM Hiển thị giờ với 12 tiếng, và hiển thị AM, PM hay bạn cũng có thể am/pm dùng am/pm, A/P, a/p, S/C Bảng 2.5: Các mã định dạng khác: Mã định dạng Mô tả Dùng để thể hiện màu. Màu [BLACK],[BLUE],[CYAN],[GREEN],[MAGENTA],[RED], bạn chọn phải [WHITE],[YELLOW], [CÁC MÀU KHÁC TÙY BẠN CHỌN] là màu trong 56 màu của bảng màu. Dùng để ra điều kiện vơi các toán tử so sánh: >, =, (không bằng). Ví du: [>100] : điều kiện lớn hơn 100 Hãy chú ý đến điểm đặc biệt ở mã định dạng cuối cùng trong bảng 2-5: các toán tử so sánh. Giả sử bạn muốn định dạng số là: 0.00_ ;[Red](-0.00) để hiển thị số âm là màu đỏ và trong ngoặc đơn nếu số nhỏ hơn -100. Để làm điều này, bạn làm như sau: 0.00_ ;[Red][<-100](-0.00);0.00 Mã định dạng [Red][<-100](-0.00) được đặt trong phần số âm tạo nên kết quả này. Việc sử dụng phương pháp này kết hợp với định dạng có điều kiện bạn có thể nhân đôi số điều kiện định dạng có điều kiện từ 3 lên đến 6 điều kiện. Thường người sử dụng muốn hiện thị giá trị dollar ở dạng chữ. Để làm điều này, ở
  53. khung type của Format cells/number/custom, bạn gõ: 0 "Dollars and" .00 "Cents" Định dạng này sẽ ảnh hưởng đến một số được nhập vào như 55.25 được hiển thị là "55 Dollars and .25 Cents". Nếu bạn muốn đổi số thành dollars và cents, hãy tham khảo thêm hai hàm tự tạo từ link sau của Microsoft: and Bạn cũng có thể sự dụng một định dạng riêng để hiển thị các từ như : Low, Average, hay High cùng với số được gõ vào. Đơn giản chỉ việc sử dụng mã định dạng sau: [ 20]"High"* 0;"Average"* 0 Hãy chú ý đên việc sử dụng dấu "*". Dấu này sẽ lặp lại các ký tự tiếp theo trong định dạng để điền đầy ô theo độ rộng cột, nghĩa là tât cả các từ Low, Average, or High sẽ bị dồn về bên phải, trong khi số sẽ bị dồn về bên trái. Chiêu thứ 34: Tăng thêm số lần Undo cho Excel Tất cả chúng ta đều đã quen thuộc với chức năng Undo của Excel, cho phép chúng ta làm lại những sai lầm của mình. Tuy nhiên, mặc định, chúng ta chỉ có thể Undo được 16 lần. Chiêu này giúp bạn có thể tăng số lần Undo lên nhiều hơn, có thể đến 100 lần. 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.
  54. Để 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 2007 thì nó là 12.0
  55. 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.
  56. 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.
  57. 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 vẫn mất. Chiêu thứ 35: Tự tạo danh sách để fill Bằng cách tự tạo 1 danh sách, bạn có thể chỉ cần gõ 1 giá trị đầu, sau đó fill xuống hoặc fill ngang bằng cái Fill Handle, danh sách sẽ được tự động điền vào tuần tự. Excel cung cấp cho bạn 1 khả năng tiết kiệm đáng kể thời gian cho việc nhập liệu, không chỉ cho số mà cả cho chuỗi. Nó có tạo sẵn cho bạn vài danh sách: tháng trong năm, thứ trong tuần. Bạn chỉ cần gõ 1 từ đầu (có trong list), rồi kéo cái Fill Handle, danh sách sẽ tự động được điền vào với những giá trị kế tiếp của list. Thí dụ bạn gõ Tue, và fill xuống, các ô kế sẽ tự động được điền là Wed, Thu, Fri, Sat, Sun, Mon, và trở lại Tue nếu còn tiếp. Bạn hoàn toàn có thể tự tạo 1 list riêng của bạn để dùng sau này. Một cách đơn giản để tạo list là gõ danh sách bạn cần vào các ô. Thí dụ bạn cần danh sách các tháng trong năm bằng tiếng Việt. Vậy bạn hãy gõ vào các ô từ A1 đến A12: Rồi chọn trong Office button ➝ Excel Options ➝ Popular ➝ Edit Custom Lists
  58. (đối với Excel trước 2007, Tools ➝ Options ➝ Custom Lists), trong vùng Import List in cells, chọn $A$1:$A$12. Nhấn Import và nhấn OK. Một khi bạn đã tạo 1 list như vậy, chỉ cần gõ vào 1 ô với 1 giá trị trong list, rồi nắm kéo cái fill Handle, bạn sẽ được kết quả:
  59. Bạn cũng có thể fill giá trị ngược chiều nếu bạn gõ 2 giá trị, 1 nằm dưới trong danh sách, 1 nằm kế trên trong danh sách, tô chọn cả 2 ô rồi Fill:
  60. Chiêu thứ 36: Làm nổi các Subtotal của Excel Khi làm việc với subtotal trong Excel bằng lệnh Data ➝ Outline ➝ Subtotal, các dòng subtotal rất khó phân biệt với các số liệu khi có nhiều cột dữ liệu. Cụ thể là, các cột subtotal xuất hiện bên phải tập dữ liệu trong khi tiêu đề của nó thường lại nằm ở cột đầu tiên và các subtotal lại không được định dạng in đậm như các tiêu đề nên gây khó khăn cho việc đọc các kết quả. Trong phần này sẽ hướng dẫn bạn các cách sử dụng định dạng theo điều kiện để định dạng các subtotal sao cho dễ phân biệt và dễ đọc hơn.
  61. Trước tiên, chúng ta cần có tập số liệu để thực hành. Các bạn nhập vào các số liệu như hình sau: Tiếp theo là các bạn hãy tính subtotal cho từng Region bằng cách đặt ô hiện hành vào dùng số liệu và vào Data ➝ Outline ➝ Subtotal (E2003: Data ➝ Subtotals). Hộp thoại Subtotal xuất hiện, bạn thiết lập các tuỳ chọn như hình sau và nhấn nút OK.
  62. Trong hình trên, bạn thấy rằng các tiêu đề subtotal của từng Region tại cột A được in đậm nhưng các giá trị subtotal bên cột B thì không. Đối với tập số liệu trên thì cũng không quá khó để đọc các kết quả, tuy nhiên chúng ta sẽ khó nhận biết ngay các subtotal khi có nhiều cột số liệu hơn. Chúng ta sẽ dùng định dạng theo điều kiện để định các các giá trị subtotal in đậm một cách tự động và bạn phải thực hiện điều này trước khi dùng lệnh subtotal. Do vậy nếu đã thực hiện lệnh subtotal thì bạn hãy xoá bỏ nó theo cách sau: chọn ô hiện hành trong vùng kết quả subtotal và vào Data ➝ Outline ➝ Subtotal ➝ chọn Remove All. Sau đó chọn vùng A1:B16, lưu ý để ô hiện hành tại tại ô A1, sau đó vào Home ➝ Styles ➝ Conditional Formatting ➝ New Rules ➝ Use a formula to determine which cells to format (E2003: Format ➝ Conditional Formatting ➝ Formula Is) và nhập vào công thức sau tại hộp “Format values where this formula is true”: =Right($A1,5)=”Total”
  63. Lưu ý phải cố định cột A (thêm $ vào trước A) và dùng tham chiếu tương đối cho dòng. Nhấn nút Format ➝ vào hộp Format Cells ➝ chọn ngăn Font ➝ chọn Bold tại Font Style và chọn màu đỏ tại Color ➝ nhấn OK ➝ nhấn tiếp OK để trở về bảng tính. Tiếp theo, bạn chọn một ô nào đó trong vùng số liệu và vào Data ➝ Outline ➝ Subtotal ➝ để các tuỳ chọn mặc định ➝ OK. Kết quả như hình sau:
  64. Do bạn chọn ô A1 là ô mặc định và sau khi áp dụng định dạng theo điều kiện, Excel sẽ thay đổi địa chỉ tham chiếu trong công thức định dạng trên cho các ô trong vùng chọn. Ví dụ như tại ô A2 và B2 sẽ có công thức định dạng theo điều kiện là =Right($A2,5)=”Total” và các ô A3, B3 sẽ là =Right($A3,5)=”Total”. Khi công thức trong ô nào trả về True thì ô đó sẽ được áp dụng định dạng theo thiết lập trong lệnh định dạng theo điều kiện. Cải tiến định dạng cho subtotal Các định dạng theo hướng dẫn trên sẽ như nhau cho các dòng có tính subtotal, do vậy cũng sẽ khó phân biệt dòng Grand Total với các dòng subtotal khác. Phần này, chúng ta cũng sẽ dùng định dạng theo điều kiện để làm cho chúng có định dạng
  65. khác nhau. Trước tiên, bạn phải xoá định dạng theo điều kiện ở phần trước bằng cách đặt ô hiện hành trong vùng số liệu rồi vào Home ➝ Styles ➝ Conditional Formatting ➝ Manage Rules ➝ chọn Rule và nhấn nút Delete Rule. Bạn phải xoá vùng kết quả Subtotal tương tự như hướng dẫn ở phần trước. Sau đó, bạn chọn vùng A1:B16, nhớ chọn ô A1 là ô hiện hành rồi vào Home ➝ Styles ➝ Conditional Formatting ➝ Manage Rule ➝ New Rule ➝“Use a formula to determine which cells to format” (E2003, Format ➝ Conditional Formatting ➝ Formula Is) ➝ nhập công thức sau tại hộp “Format values where this formula is true” =$A1=”Grand Total” Nhấn nút Format ➝ chọn ngăn Font ➝ chọn Bold tại Font Style ➝ chọn màu xanh tại Color ➝ nhấn OK. Tiếp tục, bạn nhấn nút New Rule (E2003: nhấn Add) ➝ chọn “Use a formula to determine which cells to format” ➝nhập công thức sau tại hộp “Format values where this formula is true”:
  66. =Right($A1,5)=”Total” Nhấn nút Format ➝ chọn ngăn Font ➝ chọn Bold Italic tại Font Style ➝ chọn Single tại Underline ➝ nhấn OK ➝ OK. Nhấp tiếp OK để trở lại màn hình bảng tính. Trong Excel 2007, Rule thêm vào trước sẽ nằm dưới cùng. Do vậy bạn chọn Rule tạo đầu tiên =$A1=”Grand Total” và nhấn Move Up để đưa nó lên đầu tiên trong danh sách các Rule. Cuối cùng, bạn vào Data ➝ Outline ➝ chọn Subtotal ➝ nhấn OK và xem kết quả như hình sau:
  67. Chiêu thứ 37: Chuyển đổi các hàm và công thức trong Excel thành giá trị. Hầu hết các bảng tính Excel đều chứa các công thức. Thỉnh thoảng bạn chỉ muốn hiện lên kết quả của một công thức trong một ô, thay vì để công thức ở đó, vì nó sẽ thay đổi khi dữ liệu mà nó tham chiếu đến thay đổi. Bạn có thể làm điều này bằng tay theo hai cách hoặc bạn có thể sử dụng một macro mà sẽ làm cho công việc nhẹ đi. Trước hết hãy xem các phương pháp bằng tay.
  68. 1. Dùng Paste Special Bạn có thể sao chép các kết quả của các công thức và vẫn để lại công thức tại ô gốc bằng việc sử dụng công cụ Paste Special của Excel. Giả sử bạn có các công thức ở trong vùng A1:A100. Chọn vùng này, chọn lệnh copy (bạn có thể làm điều này bằng cách chọn các lựa chọn trong Clipboard ở tab Home hoặc nhấn chuột phải), sau đó chọn ô bắt đầu để dán kết quả (giả sử ô B1). Chọn Clipboard ➝ Paste ➝ Paste Values (hoặc nhấn phải chuột và chọn Paste Special ➝ Values hoặc dùng phím tắt Alt+E+S+V) và nhấn OK. Lúc này bạn sẽ thấy vùng B1:B100 sẽ là các kết quả của công thức ở các ô trong vùng A1:A100 nhưng chỉ là các giá trị. Nếu bạn muốn chép đè các công thức gốc với kết quả của chúng, chọn dãy công thức và chọn Copy. Vẫn ở dãy công thức được chọn đó, chọn Paste ➝ Paste Values (hoặc click phải chuột và chọn Paste Special ➝ Values hoặc dùng phím tắt Alt+E+S+V), sau đó click OK. 2. Sử dụng "Copy Here As Values Only" Bạn cũng có thể sao chép các kết quả của công thức mà vẫn để lại các công thức ở ô gốc bằng cách sử dụng một menu ẩn mà thậm chí nhiều người dùng không biết sự tồn tại của nó. Chọn dãy công thức, nhấn phải chuột ở đường biên bên phải hoặc bên trái vùng chọn (nói cách khác là bạn có thể click chuột phải ở bất kỳ đường biên nào của vùng chọn ngoại trừ việc bạn sử dụng nút "fill handle" có hình dấu thập). Trong lúc nhấn giữ chuột phải, kéo chuột đến nơi cần dán, nhả chuột phải và nhấn "Copy Here as Values Only" từ một menu xổ xuống. Bạn cũng có thể chép đè lên các công thức với kết quả của chúng. Chọn dãy có công thức, sau đó nhấn phải chuột ở đường biên bên phải hoặc bên trái vùng chọn (nhắc lại một lần nữa là bạn có thể click chuột phải ở bất kỳ đường biên nào của vùng chọn ngoại trừ việc bạn sử dụng nút "fill handle" có hình dấu thập). Trong khi nhấn phải chuột (hoặc nhấn Ctrl), kéo chuột qua một cột bên phải hoặc bên trái và sau đó quay về dãy ban đầu, nhả chuột phải và nhấn "Copy Here as Values Only" từ kết quả của một một menu xổ xuống.
  69. 3. Sử dụng Macro Nếu bạn thường xuyên chuyển đổi các ô có công thức hoặc hàm thành giá trị thì bạn có thể sử dụng macro đơn giản như sau: Sub ValuesOnly( ) Dim rRange As Range On Error Resume Next Set rRange = Application.InputBox(Prompt:="Select the formulas", _ Title:="VALUES ONLY", Type:=8) If rRange Is Nothing Then Exit Sub rRange = rRange.Value End Sub Để sử dụng macro này, chọn Developer ➝ Code ➝ Visual Basic (với phiên bản trước Excel 2007, chọn Tools ➝ Macro ➝ Visual Basic Editor) hoặc nhấn Alt + F11 để chèn một module chuẩn. Sao chép và dán đoạn code ở trên trực tiếp vào module.
  70. Nhấn chọn nút Close của cửa sổ code hoặc nhấn Alt + Q để quay trở lại bảng tính Excel. Chọn Developer ➝ Code ➝ Macros (với phiên bản trước Excel 2007, chọn Tools ➝ Macro ➝ Macros) hoặc nhấn Alt+F8, chọn ValuesOnly, sau đó click chọn nút Options, ở shortcut key bạn thêm một phím nào đó để gán một nút tắt cho macro này (Ví dụ: bạn gõ ở shortcut key là E thì sau này muốn sử dụng macro này bạn chỉ cần gõ Ctrl+E). Chú ý là bạn không nên chọn các phím tắt trùng với các phím tắt có sẳn của Excel như Ctrl+V, Ctrl+C, Ctrl+S Khi bạn sử dụng macro, bạn sẽ thấy một Input box được hiển thị và hỏi dãy chứa công thức của bạn là dãy nào. Địa chỉ của dãy được chọn sẽ được hiển thị một cách tự động trong Inputbox, và tất cả những gì bạn cần để làm thực hiện việc chuyển đổi là nhấn OK. Chiêu thứ 38: Thêm dữ liệu vào danh sách Validation một cách tự động Nếu bạn đã từng sử dụng validation, bạn sẽ thấy đó là một tính năng rất hay. Có lẽ điểm ấn tượng nhất của nó chính là khả năng thêm một danh sách lựa chọn vào bất kỳ ô nào trên bảng tính và cho phép người sử dụng chọn lựa. Nó sẽ tuyệt hơn hay không nếu khi bạn nhập vào một tên mới trong một ô đang áp dụng tính năng validation thì Excel sẽ tự động thêm tên này vào trong danh sách validation? Điều này có thể thực hiện được khi bạn làm theo các hướng dẫn trong bài này. Giả sử bạn có danh sách tên trong vùng A1:A10 như hình sau:
  71. Danh sách này chính là tên của các nhân viên trong một công ty. Tại một ô đang áp dụng validation, bạn sẽ không thể nào nhập vào được tên một nhân viên mới ngoài danh sách lựa chọn, mà bạn phải thêm tên nhân viên mới này vào dòng cuối trong danh sách và điều chỉnh lại vùng dữ liệu của validation trước. Điều này sẽ rất bất tiện trong sử dụng. Để hạn chế nhược điểm này, tại ô A11 bạn nhập vào công thức bên dưới và sao chép đến A20 (dự trù trước sẽ thêm 10 tên mới). =IF(OR($D$1="",COUNTIF($A$1:A10,$D$1)),"x",$D$1) Chọn Formulas ➝ Defined Names ➝ Define Name (E2003: Insert ➝ Name ➝ Define), và nhập vào tên MyName tại hộp Names. Tại Refers To, bạn nhập vào công thức bên dưới rồi nhấp OK (E2003: nhấn Add ➝ OK). =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) Theo tôi, ta thay bằng công thức =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)- COUNTIF(Sheet1!$A:$A,"=x"),1)
  72. Choạ ô D1, vào Data ➝ Data Tools ➝ Data Validation (E2003: Data ➝
  73. Validation). Chọn List từ hộp Allow, và tại Source nhập vào =MyNames, bạn đảm bảo rằng đã chọn thêm hộp In-Cell dropdown. Chọn ngăn Error Alert và bỏ chọn Show error alert after invalid data is entered. Nhấn nút OK khi hoàn tất. Nhấp phải chuột lên tên Sheet1 và chọn View Code. Sau đó bạn nhập vào đoạn mã sau:
  74. VB Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$D$1" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0 Then lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion) If lReply = vbYes Then Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target End If End If End If End Sub Đóng cửa sổ VBE và lưa bảng tính lại, sau đó chọn ô D1 và nhập vào một tên mới chưa có trong danh sách validation và nhấn Enter. Bạn sẽ thấy tên mới xuất xuất hiện trong danh sách dữ liệu trên bảng tính tại ô A11 và nếu chọn ô D1 và mở danh sách validation bạn cũng sẽ thấy tên mới đã được bổ sung vào. Chiêu thứ 39: Ngày tháng trong Excel Ngày tháng trong Excel được xử lý rất tuyệt khi được tạo và sử dụng trong Excel, tuy nhiên có những vướng mắc khi xử lý với những dữ liệu ngoại lai import vào. May thay, Excel có những công cụ và phương thức để giải quyết khi nó không đúng như bạn muốn. Mặc định, Excel sử dụng hệ thống ngày dựa trên năm gốc 1900. Nó coi ngày 01/01/1900 có giá trị 1, ngày 02/01/1900 có giá trị 2, và cứ thế. Các giá trị này giúp cho Excel thực hiện những yêu cầu tính toán thời gian. Giờ phút cũng tương tự như vậy, nhưng Excel coi giờ như là số thập phân, với 1 tương đương 24:00 hay 00:00. 18:00 được coi như là 0,75 vì 18 giờ là ¾ của 1
  75. ngày. 9ể có thể thấy giá trị số của ngày và giờ, bạn hãy định dạng ô là General. Thí dụ dữ liệu ngày giờ đầy đủ 03/05/2007 03:00:00 PM có 1 giá trị số là 39.025,625, nghĩa là đã 39.025 ngày tính từ ngày gốc 01/01/1900 và kèm theo 0,625 ngày tương đương 15/24 ngày. Tính toán cho kết quả nhiều hơn 24 giờ: Bạn có thể cộng các giá trị giờ với nhau bằng toán tử cộng hoặc dùng hàm Sum(). Vì vậy Sum(A1:A5) sẽ cho kết quả là tổng giờ của vùng A1:A5 nếu trong vùng có những giá trị giờ đúng nghĩa. Thế nhưng nếu bạn không để ý bạn sẽ tưởng kết quả sai nếu như tổng tính được nhiều hơn 24 giờ: mỗi 24 giờ Excel coi như 1 ngày, và do định dạng giờ phút, con số chỉ ngày không hiển thị. bạn hãy thử thì biết: Trong 1 ô nào đó bạn gõ công thức =5:00 + 17:00 + 6:00, bạn cho rằng kết quả phải là 28:00, nhưng Excel lại chỉ cho bạn thấy trên ô là 4:00, vì 24 giờ đã bị chuyển thành 1 ngày, chỉ còn 4 giờ lẻ. Muốn buộc Excel hiện rõ số giờ lớn hơn 24, bạn phải định dạng custom cho ô đó là [h]:mm hoặc [h]:mm:ss. Bạn cũng có thể dùng kiểu định dạng đó để biết số phút hoặc số giây của 1 giá trị giờ, hoặc ngày. Thí dụ như 1 ô có giá trị 1 (24 giờ) và định dạng [m] sẽ hiển thị số phút của 24 giờ là 1.440. Nếu bạn định dạng [s], bạn sẽ thấy số giây là 86.400. Tính toán ngày và giờ: Để tính toán ngày giờ bạn cần nhớ những con số sau: 1 ngày = 24 giờ = 1.440 phút = 86.400 giây 1 giờ = 60 phút = 3.600 giây Khi đã nhớ các con số này, bạn có thể dễ dàng sử dụng để tính toán quy đổi đơn vị thời gian. Thí dụ bạn có con số 5,5 trong ô A1, và bạn muốn: - Bạn muốn nó là 5:30 hoặc 5:30 AM, bạn dùng công thức =A1/24
  76. - Bạn muốn nó là 17:30 hoặc 5:30 PM, bạn dùng công thức =A1/24 + 0,5 - ngược lại bạn muốn tính xem trong 5,5 ngày có bao nhiêu giờ, dùng công thức =A1*24 Thí dụ khác: bạn có 1 giá trị ngày (đúng nghĩa) như là 22/05/2007 15:36 và bạn chỉ muốn: - Lấy số ngày : = Int(A1) - Lấy số giờ: =A1- Int(A1), hoặc = Mod(A1, 1) Để tìm khoảng thời gian tính bằng ngày giữa 2 mốc ngày, bạn tính bằng công thức: =DatedIf(A1, A2, “d”), với A1 là ngày sớm hơn (có giá trị nhỏ hơn) Hàm DatedIf có thể dùng với tham số “m” cho tháng, “y” cho năm. Hàm datedif không có trong danh sách hàm của Excel vì nó là 1 hàm của Lotus123. Nếu bạn không biết chắc ngày nào nhỏ hơn ngày nào trong công thức tính, hãy dùng kết hợp Min và Max như sau: =DatedIf(Min(A1, A2), max(A1, A2), ”d”) Để hiển thị giờ âm sau khi tính toán: Khi tính toán thời gian, khi có những kết quả âm, Excel sẽ bị lỗi và hiển thị tràn số: #########. Chẳng hạn bạn tính khoảng thời gian giữa giờ bắt đầu và giờ kết thúc: =A2 - A1. Nếu như giờ bắt đầu là 17:00 và kết thúc là 5:00 sáng hôm sau thì công thức trên sẽ bị lỗi. Bạn hãy dùng công thức này: =A2- A1+ If(A2<A1, 1) Còn nếu bạn cần tính hiệu số của 2 giờ trong cùng ngày, bạn dùng công thức =Max(A1:A2)- Min(A1:A2) hoặc = ABS(A2- A1) Cách này là chuyển số âm thành số dương, còn có 1 cách để hiện số giờ âm: chỉnh Option - Calculation - Đánh dấu chọn vào mục 1904 Date Systems. Tuy nhiên cách này có thể gây lỗi tính toán cho những công thức khác,
  77. Đây còn gọi là hệ thống ngày MacinTosh, sẽ nói kỹ hơn trong Chiêu 87. Bạn cũng có thể yêu cầu Excel cộng thêm vào giá trị ngày có sẵn 1 khoảng thời gian mà bạn muốn (khi tính thời hạn này nọ kể từ 1 thời điểm), bằng cách dùng hàm date() với cú pháp sau: =Date(Year(A1)+ số năm, Month(A1) + số tháng, Day(A1) + số ngày) Thí dụ như thêm 3 tháng vào ô A1: = Date(Year(A1, Month(A1) + 3, Day(A1)) AddIns Analysis ToolPak còn hỗ trợ nhiều hàm trong đó có những hàm thời gian như Edate() để cộng trừ 1 số tháng vào 1 ngày cho trước. Hoặc hàm EoMonth() để tính ngày cuối tháng của 1 thời điểm bất kỳ. Ghi chú: Excel có 1 lỗi là coi năm 1900 là 1 năm nhuận, trong khi không phải vậy. Điều ngạc nhiên là anh Bill cố tình làm vậy, theo như họ tuyên bố. Sau đây là 1 số link hữu ích về xử lý ngày tháng: HOW TO: Use Dates and Times in Excel 2000 n-us;Q214094#6 Text or Number Converted to Unintended Number Format ;en-us;Q214233 Maximum Times in Microsoft Excel ;en-us;Q214386 Dates and Times Displayed as Serial Numbers When Viewing Formulas ;en-us;Q241072 Controlling and Understanding Settings in the Format Cells Dialog Box ;en-us;Q264372 How to Use Dates and Times in Microsoft Excel b;en-us;214094 Xử lý Ngày giờ là 1 trong những điều kỳ diệu của Excel. Nắm vững những thủ thuật này bạn sẽ tiết kiệm vô khối thời gian với nó.
  78. Chiêu thứ 40: Cho phép sử dụng tính năng Group and Outline trên bảng tính bị khoá Để thực hiện điều này, trước tiên bạn hãy thiết lập Group and Outline cho dữ liệu của mình. Chọn vùng dữ liệu rồi vào Data ➝ Group ➝ Outline và chọn Auto Outline (E2003: Data ➝ Group & Outline) kết quả như hình sau: Sau đó, bạn dùng tính năng Protect Sheet để khoá bảng tính lại: vào Review ➝ tại nhóm Changes ➝ chọn Protect Sheet và đặt vào mật mã bảo vệ. (Ví dụ như mật mã là Secret). Khi bảng tính trong chế độ bảo vệ thì bạn không thể sử dụng được tính năng Group and Outline. Do vậy chúng ta sẽ sử dụng tham số UserInterfaceOnly trong phương thức Protect kết hợp với sự kiện mở bảng tính (Workbook_Open) để bật UserInterfaceOnly và EnableOutlining thành True.
  79. Sau khi áp dụng Group and Outline xong, bạn nhấn ALT+F11 để vào cửa sổ VBE. Bạn chọn ThisWorkbook và nhập vào đoạn mã sau (Tên Sheet1 là CodeName của Sheet Formulas Exercise, bạn cũng có thể thay bằng Index của sheet trong bảng tính hoặc dùng TabName): VB Code: Private Sub Workbook_Open() With Sheet1 .Protect Password:="Secret", UserInterfaceOnly:=True .EnableOutlining = True End With End Sub Sau đó nhấn Save và đóng cửa sổ VBE để trở về cửa sổ bảng tính, hãy lưu bảng tính và đóng lại. Sau đó, bạn mở lại tập tin và cho phép Macro thực thị (nếu nhận được hộp thoại cảnh báo), khi đó bạn có thể sử dụng được các nút Group and Outline trong bảng tính đang bị khoá (các nút lệnh đã bị mờ).
  80. Ghi chú thêm: Tham số UserInterfaceOnly của phương thức Protect là True: sẽ bảo vệ giao diện người dùng (user interface) nhưng cho phép dùng macro để thay đổi. nếu không khai báo thì nhận giá trị mặc định là False: bảo vệ cả giao diện người dùng và ngăn các lệnh macro can thiệp vào giao diện người dùng. EnableOutlining = True ➝ bật các nút Show/ Hide Detail Chiêu thứ 41: Bẫy lỗi để trống dữ liệu Bằng cách dùng Data Validation, chúng ta có thể bảo đảm rằng 1 trường dữ liệu nào đó nhất thiết phải nhập liệu, không được để trống. Thí dụ ta tạo 1 bảng dữ liệu 2 trường, với tên trường là Tên và Bộ phận nằm ở 2 ô A1 và B1.
  81. Bên dưới, bạn muốn rằng bất kỳ người nào nhập liệu vào bảng cũng phải nhập đủ 2 nội dung này. Nếu bạn bắt đầu với 1 bảng dữ liệu có sẵn những ô bị để trống, cần điền đầy vào, hãy xem phần sau: Điền dữ liệu vào các ô trống: Một số các công cụ hoặc hàm của Excel không làm việc với ô trống như Pivot table, Sorting, Filter, hàm SumProduct, Giả sử bạn có 1 cột A với 1 đống những ô trống nằm xen kẽ nhau. bạn hãy nhấn F5, hoặc Control – G, nhấn Special, chọn ô chọn blank, và nhấn OK. Bây giờ tất cả các ô trống đã được chọn. Nếu bạn muốn điền vào đó những giá trị hoặc công thức giống ô liền ngay trên của nó, hãy nhấn dấu bằng (=), nhấn mũi tên lên, rồi nhấn Ctrl-Enter. Bây giờ đến phần chính: Tô chọn từ ô A3 đến ô B100 chẳng hạn, theo độ lớn của vùng bạn định nhập dữ liệu. Mở hộp thoại Data – Validation, trong tab setting chọn Custom trong ô Allow, rồi điền công thức sau vào khung công thức: =AND(COUNTA($A$2:$A2)=ROW()-2,COUNTA($B$2:$B2)=ROW( )-2) Chú ý các ký hiệu $ liên quan đến tham chiếu tương đối và tuyệt đối, nếu sai hoặc thiếu, validation không hoạt động như ý muốn ráng chịu.
  82. Vào tab Alert, gõ tiêu đề cho thông báo cảnh báo trong ô Title, gõ câu thông báo của bạn vào ô Error Message, chọn Stop trong khung Error Style.
  83. Bây giờ mỗi khi bạn nhập thiếu dòng trên, mà nhảy xuống nhập dòng dưới, bạn sẽ được cảnh báo như sau:[/ Chiêu thứ 42: Giảm danh sách xổ xuống của Validation, sau khi chọn 1. Rất hữu ích cho người dùng Excel khi bạn cho họ 1 danh sách để chọn bằng Validation, nhưng cái danh sách này tự động ngắn lại mỗi khi họ chọn 1 mục trong danh sách, để khỏi phải lựa chọn trong 1 danh sách quá dài, đồng thời tránh việc nhập trùng dữ liệu (đôi khi cần thiết). Bước 1: Trong 1 sheet bất kỳ (thí dụ sheet1) bạn có 1 danh sách trong vùng A1:A10. Bạn hy đặt name cho danh sách này bằng cách tô chọn vùng danh sách, rồi gõ tên MyList trong hộp namebox. Bước 2: Trong 1 sheet khác mà bạn muốn dùng validation dựa vào danh sách MyList nói trên, tô chọn vùng bạn cần, mở menu Data – Validation, chọn Allow là List, source là =MyList, rồi nhấn OK. Bước 3: mở cửa sổ VBA của sheet đó, copy đoạn code sau: vb Code:
  84. Private Sub Worksheet_Change(ByVal Target As Range) Dim strVal As String Dim strEntry As String On Error Resume Next strVal = Target.Validation.Formula1 If Not strVal = vbNullString Then strEntry = Target Application.EnableEvents = False With Sheet1.Range("MyList") .Replace What:=strEntry, _ Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False .Sort Key1:=.Range("A1"), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom .Range("A1", .Range("A65536").End(xlUp)).Name = "MyList" End With End If Application.EnableEvents = True On Error GoTo 0 End Sub Đóng cửa sổ VBA rồi quay về Excel. Thử chọn 1 mục trong validation, sang 1 ô khác và xổ cái validation xuống, bạn sẽ thấy mục vừa chọn xong không còn trong list nữa. Lưu ý trong code, khi nói đến name MyList, phải xác định rõ Sheet1.MyList, vì nếu không, VBA sẽ cho rằng name này nằm chung sheet với sheet chứa code và sẽ không tìm thấy và báo lỗi. Chiêu thứ 43: Thêm các danh sách có sẵn và cả danh sách tự tạo vào menu chuột phải Tạo một danh sách để fill trong Excel thông qua Fill handle là việc làm rất hay để nhanh chóng nhập danh sách các chuỗi số hoặc chuỗi văn bản vào bảng tính. Excel đã xây dựng sẵn một số Custom List về các ngày trong tuần (Sun - Sat), danh sách các tháng (Jan – Dec) và các chuỗi số nhưng bạn cũng có thể tự tạo thêm các danh sách mới cho mình. Phần này sẽ hướng dẫn bạn các tạo thêm Custom List cho Fill
  85. Handle. Trước tiên, bạn cần nhập vào danh sách các phần tử cần đưa vào Custom List trên bảng tính. Ví dụ như bạn nhập và 26 chữ cái trong các ô A1:A26 trên Sheet1. Sau đó nhấn vào nút Office ➝ Excel Options ➝ Popular ➝ Edit Custom Lists (E2003: Tools ➝ Options ➝ Custom Lists). Nhấp chọn nút nằm bên trái nút Import và dùng chuột quét chọn vùng dữ liệu A1:A26. Sau đó nhấn nút Import và OK. Kể từ lúc này danh sách các chữ cái sẽ có thể sử dụng để fill trong tất cả bảng tính trên máy tính này. Tiếp theo, để đưa các Custom List vào trong thực đơn ngữ cảnh thì bạn nhấn tổ hợp ALT+F11 rồi vào Insert ➝ Module. Sau đó, bạn nhập vào đoạn mã như sau:
  86. VB Code: Sub AddFirstList() Dim strList As String strList = Application.CommandBars.ActionControl.Caption If Not strList Like "* *" Then Exit Sub ActiveCell = Left(strList, InStr(1, strList, ".", vbTextCompare) - 1) End Sub Bạn kích chuột hai lần lên ThisWorkbook trong cửa sổ VBAProject và nhập vào đoạn mã sau: VB Code: Private Sub Workbook_SheetBeforeRightClick _ (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim cBut As CommandBarButton Dim lListCount As Long Dim lCount As Long Dim strList As String Dim MyList On Error Resume Next With Application lListCount = .CustomListCount For lCount = 1 To lListCount MyList = .GetCustomListContents(lCount) strList = .CommandBars("Cell").Controls(MyList(1) & " " & _ MyList(UBound(MyList))).Caption .CommandBars("Cell").Controls(strList).Delete Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True) With cBut .Caption = MyList(1) & " " & MyList(UBound(MyList)) .Style = msoButtonCaption .OnAction = "AddFirstList" End With
  87. Next lCount End With On Error GoTo 0 End Sub Đóng cửa sổ VBE và lưu bảng tính lại. Sau đó nhấp phải chuột vào một ô nào đó trên bảng tính, bạn sẽ thấy các Custom List dựng sẵn và do bạn tạo xuất hiện trong trình đơn ngữ cảnh. Muốn sử dụng, bạn chỉ cần nhấn chuột phải vào ô chọn, rồi chọn 1 trong các list hiện ra trong menu ngữ cảnh, bạn sẽ có giá trị thứ nhất của list. Sau đó kéo cái Fill handle để fill list như mọi khi