Những kỹ năng tùy biến bảng tính excel

Chia sẻ: Ba Toan | Ngày: | Loại File: PDF | Số trang:117

Thêm vào BST

Báo xấu

6.530
lượt xem 4.369
download

Những kỹ năng tùy biến bảng tính excel

Mô tả tài liệu

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Microsoft Excel, còn được gọi theo tên đầy đủ Microsoft Office Excel, là chương trình xử lý bảng tính nằm trong bộ Microsoft Office của hãng phần mềm Microsoft. Cũng như các chương trình bảng tính Lotus 1-2-3, Quattro Pro… bảng tính của Excel cũng bao gồm nhiều ô được tạo bởi các dòng và cột, việc nhập dữ liệu và lập công thức tính toán trong Excel cũng có những điểm tương tự, tuy nhiên Excel có nhiều tính năng ưu việt và có giao diện rất thân thiện với người dùng. Excel hiện nay đã là phiên bản...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Những kỹ năng tùy biến bảng tính excel

NH NG K NĂNG TÙY BI N B NG TÍNH EXCEL Nh ng m o nh v c u trúc m t b ng tính M t trong nh ng l i mà chúng ta hay m c ph i khi t o m t b ng tính, là chúng ta không thi t l p và trình bày d li u "theo cái cách mà Excel và các tính năng c a nó mong i". Sau ây là m t s l i ph bi n chúng ta hay m c ph i khi thi t l p m t b ng tính: • B trí d li u trong nhi u b ng tính (WorkBook) khác nhau • B trí d li u trong nhi u trang tính (WorkSheet) khác nhau • B trí d li u trong nhi u b ng (Table) khác nhau • Có nh ng hàng tr ng và c t tr ng trong kh i d li u (database) • B tr ng nh ng ô có d li u gi ng nhau (cùng chung m t ngày, cùng chung m t ơn v tính, v.v...) Ba i m u tiên trên ây ch mu n nói n m t i u: B n nên luôn luôn c g ng gi các m i liên quan gi a các d li u ư c liên t c trong cùng m t b ng d li u. Tôi ã th y r t nhi u các b ng tính không ư c trình bày theo cái quy t c r t ơn gi n này, và chính vì v y, các b ng tính ó không th t n d ng ư c t i a các tính năng m nh m c a Excel như PivotTable, SubTotal... B n ch có th s d ng các tính năng này khi b n gom h t d li u c a b n vào trong m t b ng th t ơn gi n. Không ph i ng u nhiên mà Excel có 1.048.567 hàng (65.536 hàng trong Excel 2003 tr v trư c) mà l i ch có 16.384 c t (256 c t trong Excel 2003 tr v trư c). i u này có ý nói r ng, b n nên thi t l p d li u c a mình v i các tiêu c t n m hàng trên cùng, và các d li u có liên quan thì n m liên t c bên dư i tiêu c a nó. N u như b n có nh ng d li u ư c l p l i hai l n ho c nhi u l n trong các hàng c a cùng m t c t (các ngày tháng, các lo i ơn v tính ch ng h n), b n hãy ch ng l i s cám d b tr ng các ô ó. Hãy c g ng s p x p (sort) d li u c a b n b t c khi nào có th . Excel có r t nhi u nh ng công c tìm ki m và tham chi u công th c, và m t s không nh trong ó, òi h i d li u ph i ư c s p x p theo m t th t h p lý. Vi c phân lo i cũng s giúp ích áng k cho t c x lý c a m t s các hàm. Nh ng m o nh khi nh d ng Ngoài vi c thi t k c u trúc c a b ng tính cho h p lý, vi c nh d ng cho nó cũng là m t v n c n bàn n. M c dù m t b ng tính nên ư c nh d ng sao cho d c và d theo dõi, nhưng chúng ta ít khi nghĩ n vi c s d ng th i gian cho có hi u qu . Hãy luôn luôn nh d ng th t ơn gi n. R t nhi u ngư i lãng phí th i gian vào vi c nh d ng m t b ng tính m c dù không nh t thi t ph i làm như v y, và chính i u này làm nh hư ng n hi u qu công vi c. Vi c thư ng xuyên áp d ng nh ng nh d ng ph c t p cho b ng tính còn làm cho nó tăng kích thư c, và cho dù b ng tính c a b n có th trông gi ng như m t tác ph m ngh thu t, nhưng nó l i có th là m t n i s hãi cho ngư i khác. M t trong nh ng ki u ph i màu t t nh t mà b n nên áp d ng cho m t b ng tính, là màu en, màu tr ng và màu xám. M t g i ý n a, là nên b tr ng vài hàng u tiên trên cùng (ít nh t là 3 hàng). Nh ng hàng này s ư c dùng cho nh ng tính năng nâng cao hơn v sau, ch ng h n như Advanced Filter, ho c dùng làm vùng i u ki n cho các công th c x lý d li u (DSUM, DMAX, v.v...) Cũng ng quan tâm n vi c canh l cho d li u. Theo m c nh, Excel canh ph i cho nh ng d li u ki u s , và canh trái cho nh ng d li u ki u text. Và ó là i u t t nh t r i, ng thay i nó. N u b n thay i ki u canh l , b n s có th không xác nh ư c ngay t c kh c r ng ó là d li u ki u s hay d li u ki u text; và nó còn có th gây nh m l n cho ngư i khác khi tham chi u n m t ô, vì nhi u khi h s tư ng r ng d li u trong ô ó là s , nhưng th t ra nó là text. N u b n thay i ki u canh l m c nh, b n s b nh c u v i nó sau này. Ch có tiêu c a b ng tính là ư c hư ng ngo i l mà thôi. Ch nh d ng các ô theo ki u text khi th t s c n thi t. B i vì t t c nh ng d li u nh p vào trong các ô ã ư c nh d ng trư c theo ki u text s b chuy n thành d li u ki u text, ngay c khi b n nghĩ r ng cái b n nh p
vào là d li u ki u s hay d li u ki u ngày tháng. Và t i t hơn n a, b t kỳ ô nào ch a công th c tham chi u n m t ô ã ư c nh d ng theo ki u text, cũng s b nh d ng thành ki u text. Nói chung, b n âu có mu n nh ng ô ch a công th c l i b nh d ng theo ki u text, ph i không. Tr n các ô (merge cells) cũng là m t v n . Có m t s công th c ho c macro không th ch y ư c v i nh ng ô ã ư c tr n. Cho nên, thay vì tr n ô, b n hãy dùng ch c năng "Center across selection", n m trong Home ➝ nhóm Alignment. Nh n vào cái mũi tên dư i cùng bên ph i, s m ra h p tho i Format v i tab Alignment ư c ch n s n. B n s d ng thanh trư t d c c a khung Horizontal ch n Center Across Selection. Ho c b n cũng có th right-click r i ch n Format Cells t shortcut menu. V i Excel 2003 tr v trư c, b n m h p tho i này t menu Format ➝ Cells (Ctrl+1). Nên s d ng Center Across Selection thay cho Merge Cells Nh ng m o nh khi dùng công th c M t sai l m r t l n khi s d ng công th c là cho nó tham chi u n toàn b các c t trong m ng d li u. i u này làm cho Excel ph i ki m tra hàng ngàn ô, n u không nói là hàng tri u, ch c ch n không b sót m t d li u nào. Ví d , gi s b n có m t m ng d li u t A1 n H1000, và b n quy t nh s dùng nh ng hàm tìm ki m c a Excel trích ra nh ng d li u c n thi t. B i vì b n còn ph i b sung thêm d li u vào m ng này, nên ch c ăn, b n t o tham chi u trong công th c n toàn b các hàng c a các c t trong m ng này. Khi ó, tham chi u c a b n có th s là A:H, ho c c th hơn, là A1:H65536. B n nghĩ r ng làm như v y thì b n s không lo l ng gì khi thêm d li u vào trong m ng. ó là m t thói quen vô cùng tai h i mà b n ph i luôn tránh xa nó. B i nó s làm cho b ng tính tr nên ì ch, th m chí là không ch y n i. B n v n có th lo i b nh ng ph n không c n tham chi u ra kh i công th c, mà l i b o m ư c nh ng d li u m i thêm vào s t ng ư c c p nh t trong tham chi u c a công th c,
b ng cách dùng Table (List trong Excel 2003 tr v trư c) ho c s d ng các Name ng. M t v n n a thư ng x y ra v i nh ng b ng tính l n, mà c u hình máy l i nh , là Excel tính toán r t ch m khi d li u ư c c p nh t. gi i quy t v n này, b n thư ng ư c khuyên là hãy chuy n i ch tính toán c a Excel thành d ng Manual: Nh n vào nút Office góc trái phía trên c a b ng tính, r i ch n Excel Options ➝ Formulas (v i Excel 2003 tr v trư c, ch n menu Tools ➝ Options ➝ Calculations), r i ch n Manual : Ch n Manual Caculation v i Excel 2007 Ch n Manual Caculation v i Excel 2003 Tuy nhiên, l i khuyên ó thì không hay cho l m, mà ch là mang tính i phó. M t b ng tính thì bao gi cũng có nh ng công th c tính toán, n u b n ang ch y m t b ng tính trong ch tính toán Manual, có th b n s ch
có ư c nh ng thông tin cũ mèm, chưa h ư c c p nh t. N u mu n có ư c thông tin chính xác, b n ph i nh n F9. Nhưng, ch c ch n là không ph i lúc nào b n cũng nh i u này! V y b n th cân nh c xem: N u như c n th ng chân b k t và làm cho chi c xe hơi c a b n ch y ch m l i, b n s s a l i c n th ng chân, hay là g b cái th ng chân r i tin tư ng hoàn toàn vào cái th ng tay? Nghĩa là, khi b ng tính c a b n ch y ch m, b n s s a l i công th c cho nó ch y nhanh hơn, hay là t nó vào ch tính toán b ng tay r i c th mà làm ti p? H u như chúng tôi không bao gi làm i u này, nhưng r t nhi u ngư i ã không ng n ng i t b ng tính c a h trong ch tính toán Manual. N u như b n th y r ng c n ph i t b ng tính c a mình trong ch tính toán Manual thì m i làm vi c ư c, b n nên suy nghĩ n vi c thi t k l i b ng tính. Công th c m ng là m t trong nh ng nguyên nhân c a v n trên. Chúng là m t l a ch n t t khi tham chi u n nh ng ô riêng l . Nhưng n u b n dùng chúng tham chi u n nh ng dãy d li u l n, thì hãy s d ng chúng càng ít càng t t. Khi có m t s lư ng l n các m ng tham chi u n m t dãy d li u l n, hi u su t c a b ng tính s b nh hư ng, ôi khi nó d ng h n luôn, và b n b t bu c ph i dùng ch tính toán b ng tay. Excel có nhi u hàm chuyên x lý nh ng d li u l n thay cho công th c m ng, mà s ư c c p n trong chiêu th 79: Tránh l i #VALUE! khi tính t ng (SUM) ho c m (COUNT) các ô. Ngoài ra, trong Excel Help có m t s ví d r t hay s giúp b n cách dùng công th c trên nh ng b ng d li u l n trích ra k t qu d a theo nh ng tiêu chu n. Ho c b n có th s d ng Pivot Table, s ư c c p n trong ph n th 4 c a lo t bài này. M c dù PivotTable thì có v ph c t p khi b n s d ng nó l n u tiên, nhưng chúng tôi thành th t khuyên b n hãy h c cách s d ng nó. M t khi b n ã làm ch ư c PivotTable, ngày nào ó b n s t h i r ng làm sao tôi có th s ng mà không có PivotTable! Tùy bi n c a s làm vi c c a b ng tính Excel cho phép hi n th ng th i nhi u b ng tính và cho phép tùy bi n cách hi n th và s p x p chúng. Sau khi thi t l p các tùy bi n, chúng ta có th lưu thành m t t p tin workspace (không gian làm vi c) .xlw s d ng l i sau này. ôi khi khi làm vi c trong Excel, b n có th c n ph i có nhi u hơn m t workbook m trên màn hình c a b n làm cho nó d dàng s d ng hơn hay xem d li u t nhi u b ng tính. Trong bài này s mô t cách th c hi n t ch c c a s làm vi c m t cách g n gàn và có t ch c. u tiên, b n hãy m t t c các b ng tính mà b n c n làm vi c: Vào Office | Open…, gi phím Ctrl trong khi ch n các t p tin b ng tính c n m | nh n nút Open m các t p tin. (E2003: File | Open...). T c a s làm vi c c a m t b ng tính ang m nào ó, ch n Windows | View | Arrange All (E2003: Window | Arrange). N u tùy ch n “Windows of active workbook” ang ư c ch n thì hãy b ch n, sau ó ch n ki u s p x p và nh p nút OK.
N u ch n ki u s p x p Tiled (lát g ch) thì các c a s làm vi c c a b ng tính như hình sau: N u ch n ki u s p x p Horizontal (n m ngang) thì các b ng tính s x p ch ng lên nhau như hình minh h a sau: N u ch n ki u Vertical (th ng ng) thì các b ng tính ư c s p x p n m c nh nhau như sau:
N u ch n ki u Cascade (thác nư c) thì các b ng tính s x p è lên nhau như hình minh h a sau Khi các c a s b ng tính ã ư c s p x p g n gàng, b n s r t d dàng thao tác và di chuy n d li u gi a các c as . s d ng l i các thi t l p này sau này, b n có th lưu thành m t t p tin workspace. Các bư c th c hi n như sau:
Ch n ngăn View | t i nhóm Window ch n Save Workspace (E2003: File | Save Workspace…) | nh p tên cho workspace t i h p File Name, ch n nơi lưu và nh n nút OK. T p tin workspace s có ph n m r ng là .xlw Mu n chuy n i m t b ng tính nào ó v ch toàn màn hình thì b n ch c n kích chu t hai l n lên thanh Title (tiêu ) c a c a s b ng tính ó. B n cũng có th nh n nút Maximize trên c a s b ng tính hi n hành phóng to c a s ho c nh n nút Close óng b ng tính sau khi ã hoàn t t công vi c v i c a s ó. s d ng l i không gian làm vi c ã thi t l p trư c ó, b n ch c n m t p tin .xlw v a t o thì giao di n làm vi c ư c b n thi t l p trư c kia s ư c khôi ph c. T t c các hi u ch nh hay c p nh t thông tin trong các b ng tính c a workspace s ư c Excel nh c nh b n lưu l i khi b n ra l nh óng workspace. N u m i ngày, b n u ph i th c hi n các công vi c l p i l p l i trên nhi u b ng tính. Tôi khuyên b n nên dành m t ít th i gian thi t l p các ki u không gian làm vi c cho các b ng tính ó tùy thu c vào yêu c u tao tác và x lý s li u c a chúng. Sau ó, b n ch vi c m t p tin .xlw cho ki u không gian làm vi c phù h p v i nhu c u s d ng c a b n mà không c n ph i t n th i gian s p x p, t ch c các b ng tính các l n s d ng sau. Nh p d li u ng th i vào nhi u sheet. Thông thư ng ngư i dùng Excel có nh ng d li u mu n nh p vào nhi u sheet khác nhau. B n có th dùng công c Group các sheet l i có th nh p d li u ng th i. Nhưng cũng có 1 cách khác nhanh chóng và linh ho t hơn n u b n dùng vài dòng l nh VBA. Nhưng ph i nói n công c Group cái ã nh ? Công c Group dùng nh p d li u vào nhi u sheet 1 lúc ch c có nhi u ngư i chưa dùng n. Nó dùng k t n i các sheets v i nhau trong ph m vi work book. 1. Group các sheet l i b ng tay: nhóm các sheet l i, các b n ch vi c click ch n sheet th nh t, nơi b n s nh p li u t bàn phím vào, r i v a nh n Ctrl, v a nh n vào tên nh ng sheet khác mà b n mu n nh p li u ng th i. Khi b n gõ b t kỳ cái gì vào sheet hi n hành, d li u ó cũng s t ng nh p vào các sheet còn l i trong nhóm. i p v hoàn t t nhanh chóng (nghe gi ng 007 nh ). thoát kh i tình tr ng group, b n ch vi c ho c click ch n 1 sheet khác ngoài nhóm, ho c click chu t ph i lên tên 1 sheet trong nhómb t kỳ, ch n Ungroup Sheets. Ho n n u b n ch mu n g 1 trong s các sheet ra kh i nhóm, hãy Ctrl click vào tên sheet ó. Khi 2 hay nhi u sheet Group v i nhau, trên thanh tiêu c a Windows s có ch [Group] (tronh ngo c vuông). Nhìn vào ó b n có th bi t b n v n còn ang trong tình tr ng group các sheets.
Tr khi b n có c p m t s c như chim ưng, ho c bén như dao c o, thư ng thì b n không ý n d u hi u này, b n s quên và không ungroup khi công vi c ã xong. Chính vì th tôi khuyên b n ngay khi th c hi n xong thao tác nh p li u hàng lo t vào các sheet, ph i ungroup chúng ngay. Dù phương pháp này là d nh t, nhưng có nghĩa là b n ph i nh và nh group và ungoup khi c n, n u không b n s vô tình ghi è lên d li u nh ng sheet mà b n không nhìn th y trên màn hình hi n th i. Thí d như b n ch mu n ghi cùng lúc d li u vào 1 vùng nào ó c a các b ng tính, còn ngoài vùng ó thì d li u các sheet ph i khác nhau. Th nhưng khi các sheet ã group r i thì nó có thèm quan tâm b n ang nh p li u vùng nào âu? 2. Group các sheet l i 1 cách t ng: B n có th gi i quy t v n trên b ng cách dùng 1 o n code VBA h t s c ơn gi n. o n code làm vi c ư c, nó ph i ư c t trong các s ki n c a riêng sheet nào b n mu n. vào trang so n th o code c a sheet nào, thì click ph i chu t vào tên sheet ó và ch n View Code. B n có th ch n 1 trong các s ki n c a sheet, nh ng s ki n ch liên quan n sheet ó như i ô ch n, thay i n i dung 1 ô, kích ho t sheet, thoát kh i sheet, … th c thi o n code VBA c a mình. Thư ng thì b n ư c ưa th ng n trang so n th o code riêng c a sheet n u b n ch n View Code như trên hư ng d n. B n có th ki m tra l i qu th c nó thu c v sheet b n ch n hay không b ng cách nhìn vào c a s VBA Project bên trái, n u b n th y d ng thư m c This Workbook – Sheetname và 1 sheet ang ch n thì úng. n u không b n ph i tìm úng sheet b n c n và doubled click nó. Trư c tiên b n ph i t name cho vùng ô b n mu n nh p d li u trùng trong các sheet, gi s là “MyRange”. Sau ó gõ o n code này vào khung so n th o bên ph i: PHP Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("MyRange"), Target) Is Nothing Then Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select Else Me.Select End If End Sub Trong o n code trên, sheet5 ư c ghi u tiên trong m ng Array, vì b n mu n nó là sheet mà b n s gõ d li u nh p vào. B n có th ch n nh ng sheet khác group chúng l i bên dư i sheet5. Sau khi gõ code xong, ho c nh n nút View Object, ho c nh n nút View Microsoft Excel, ho c nh n Alt- Q, Alt-F11 ho c óng h n c a s VBA tr v màn hình Excel. Lưu b ng tính l i.
C n nh c l i r ng o n code trên ph i n m trong sheet ư c khai báo u tiên c a Array, là sheet mà b n s nh p d li u t bàn phím. Khi b n ã hoàn t t, m i khi b n click ch n 1 ô nào ó trong sheet5, o n code s ki m tra xem ô b n ch n có n m trong vùng ã t tên “MyRange” không. N u úng, code s Group các sheet l i v i nhau b n nh p d li u hàng lo t sheets. N u không ph i, nó ungroup chúng ra b ng cách ơn gi n là kích ho t sheet hi n hành. Dùng o n code trên s ti n l i ch nó gi i phóng b n kh i cái vi c ph i nh Group khi b t u và UnGroup khi k t thúc nh p li u trong vùng ch n. Bây gi n u b n mu n nh ng d li u như nhau ư c nh p vào các sheet ch n, nhưng nh ng v trí khác nhau, hãy dùng o n code khác: PHP Code: Private Sub worksheet_Change(ByVal Target As Range) If Not Intersect(Range("MyRange"), Target) Is Nothing Then With Range("MyRange") .Copy Destination:=Sheets("Sheet3").Range("A1") .Copy Destination:=Sheets("Sheet1").Range("D10") End With End If End Sub o n code này cũng ph i ư c t trong khung so n th o c a riêng sheet5. o n code này ho t ng khác v i o n code trên. Khi b n thay i n i dung c a 1 ô nào ó, code s xác nh xem ô hi n hành có n m trong vùng "MyRange" hay không, n u ph i thì copy nguyên vùng "MyRange" (c a sheet5) và paste vào ng th i ô A1 c a sheet1 và D10 c a sheet2. Ngư c l i thì không làm gì c . Ngăn ch n ngư i s d ng th c hi n m t s hành ng nào ó nh t nh M c dù Excel có h tr vi c b o v cho b ng tính (Protect Sheet, Protect Workbook), nhưng công c thô sơ này không bao g m vi c ch cho phép ngư i s d ng m t s c quy n h n ch - tr khi b n làm theo nh ng chiêu sau ây. B n có th qu n lý vi c tương tác v i các b ng tính c a b n b ng cách giám sát và áp ng các s ki n. Các s
ki n (event), là nh ng hành ng x y ra khi b n làm vi c v i các b ng tính ho c các Sheet. Nh ng s ki n này thư ng bao g m vi c m (open) m t b ng tính, lưu (save) nó, và óng (close) nó. B n có th b o Excel ch y nh ng mã Visual Basic (macro) m t cách t ng khi x y ra b t kỳ m t trong nh ng s ki n như th . Tuy nhiên, b n nên bi t r ng, ngư i s d ng có th vư t qua t t c nh ng mã b o v b i Visual Basic b ng cách vô hi u hóa vi c th c thi các macro: Nh n nút Office ➝ Excel Opions ➝ Trust Center ➝ Trust Center Setting ➝ Macro Settings [E2003: Tools | Macro | Security]. N u t m c an toàn là Disable all macros with notification [E2003: m c Medium], m i khi m m t b ng tính có ch a macro, Excel s xu t hi n m t h p thông báo và cho phép ngư i s d ng có cơ h i t t các macro. N u t m c an toàn là Disable all macros without notification [E2003: m c High], Excel s ngăn không cho ch y t t c các macro có trong b ng tính. Nói cách khác, khi c n ph i dùng các macro có trong b ng tính, ngư i s d ng s ch n m t m c an toàn thích h p cho phép s d ng các macro. Ngăn ch n vi c lưu l i b ng tính v i m t tên khác B n có th thi t l p thu c tính "Read only" cho b t kỳ m t b ng tính nào, b ng cách nh n Office ➝ Save, nh n nút Tools, ch n General Options [E2003: File | Save As | Tools | General Options] và kích ho t h p ki m Read only recommended. Vi c này ngăn ch n ngư i s d ng lưu l i nh ng thay i trên b ng tính, tr phi h lưu l i b ng tính v i m t tên khác ho c lưu vào m t nơi khác. Tuy nhiên, có th i u b n mu n là không cho lưu l i b ng tính c a b n v i tên khác, cũng không cho lưu vào m t nơi khác. Nói cách khác, b n mu n ngư i khác ch có th lưu l i b ng tính này v i chính tên g c c a nó, ch không ư c sao chép nó. i u này c bi t h u d ng khi có nhi u ngư i cùng s d ng chung m t b ng tính, và b n không mu n trong c ng c a mình y d y nh ng b n sao c a b ng tính này, cho dù là v i m t tên khác, hay m t thư m c khác. S ki n Before Save mà tôi s p nói n ã có t th i Excel 97. úng như tên g i c a nó, s ki n này ch xu t hi n ngay trư c khi b ng tính ư c lưu, cho phép b n can thi p k p th i vi c lưu l i b ng tính, ưa ra m t c nh báo, và không cho Excel lưu b ng tính. Trư c khi t th c hi n i u này vào b ng tính, hãy ch n ch n r ng b n ã lưu l i b ng tính c a b n. B i vì vi c ưa o n code sau ây vào môt b ng tính chưa ư c lưu, có th s gây cho b n nhi u r c r i. ưa o n code sau vào b ng tính, b n hãy ch n Develope ➝ Visual Basic (ho c nh n Alt+F11), và nh p úp chu t vào m c ThisWorkbook trong khung Project Explorer. N u trên Ribbon c a b n không có m c Develope, b n hãy vào trong Excel Options ➝ Popular, ánh d u vào tùy ch n Show Develope tab in the Ribbon, r i nh n OK. [E2003, nh n ph i chu t vào bi u tư ng Excel ngay bên trái menu File trên thanh menu, và ch n m c View Code, như minh h a hình bên dư i]: S d ng Quick access menu (trong Excel 2003) nh p code áp d ng cho workbook
B n hãy nh p o n code sau ây vào c a s VBE. Sau khi nh p xong, nh n Alt+Q tr v Excel, r i lưu l i b ng tính: PHP Code: Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim lReply As Long If SaveAsUI = True Then lReply = MsgBox("Sorry, you are not allowed to save this workbook as another name." _ & "Do you wish to save this workbook?", vbQuestion + vbOKCancel) Cancel = (lReply = vbCancel) If Cancel = False Then Me.Save Cancel = True End If End Sub Th m t tí. B n nh n Ctrl+S (ho c g i l nh Save) xem. B ng tính c a b n lưu bình thư ng. Bây gi b n nh n F12 (ho c g i l nh Save as), b n s g p m t c nh báo, nói r ng b n không th lưu l i b ng tính này dư i b t kỳ m t tên nào khác, tr phi b n vô hi u hóa các macro. Nói thêm, khi b n lưu m t b ng tính có ch a macro ho c code trong Excel 2007, b n s ư c nh c nh r ng b n ph i lưu file d ng cho phép macro ho t ng (macro-enable workbook), có ph n m r ng là *.xlsm, ch không th lưu d ng bình thư ng (*.xlsx). Ngăn ch n vi c in m t b ng tính ôi khi b n ph i r i bàn làm vi c i âu ó mà quên t t máy (chuy n này là chuy n thư ng), cho dù b n ã làm công vi c ngăn ch n vi c có ai ó chép b ng tính c a b n ra ngoài, như ã nói trên, có th b n còn lo xa hơn,
mu n r ng b ng tính này cũng s không b in ra khi b n v ng m t. N u th t s b n mu n như v y, b n hãy dùng s ki n Before Print. Hãy nh p o n code sau trong c a s VBE: PHP Code: Private Sub workbook_BeforePrint(Cancel As Boolean) Cancel = True MsgBox "Sorry, you cannot Print from this workbook", vbInformation End Sub Nh n Ctrl+Q quay v Excel và lưu l i. T bây gi , n u có ai có mu n ra l nh in b ng tính này, s không có gì x y ra. Dòng l nh MsgBox o n code trên ch là m t tùy ch n, nhưng b n nên s d ng nó, vì ít nh t thì nó cũng l i m t thông tin gì ó, ngư i khác s không tư ng l m r ng máy in c a h b hư, ho c là chương trình Excel có l i! N u b n ch mu n ngăn ch n in m t ph n nào ó trong b ng tính, ví d không cho in Sheet1 và Sheet2, b n s d ng o n code sau: PHP Code: Private Sub workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.Name Case "Sheet1", "Sheet2" Cancel = True MsgBox "Sorry, you cannot print this sheet from this workbook", _ vbInformation End Select End Sub Dĩ nhiên b n có th thêm vào b t kỳ Sheet nào có trong b ng tính c a b n. Ch vi c nh p tên c a nó vào trong hàng có l nh Case, và tách bi t Sheet này v i Sheet khác b ng d u ph y, và nh nh p tên Sheet trong m t c p d u nháy kép. Trong trư ng h p b n ch mu n ngăn ch n không cho in m t Sheet mà thôi, b n ch c n nh p tên c a Sheet ó (trong c p nháy kép) sau ch Case, và không c n gõ d u ph y. Ngăn ch n vi c chèn thêm Sheet vào m t b ng tính Excel có ch c năng Protect Workbook, không cho phép thay i c u trúc c a b ng tính như thêm, xóa, di chuy n, hay i tên các Sheet. Tuy nhiên, có th b n ch mu n ngăn ch n vi c thêm Sheet thôi, còn nh ng vi c khác thì v n cho phép. o n code sau ây giúp b n làm vi c ó: PHP Code: Private Sub Workbook_NewSheet(ByVal Sh As Object) Application.DisplayAlerts = False MsgBox "Sorry, you cannot add any more sheets to this workbook", _ vbInformation Sh.Delete Application.DisplayAlerts = True End Sub Khi có ai ó chèn thêm m t Sheet m i vào trong b ng tính, trư c tiên Excel s xu t hi n h p c nh cáo: "Xin l i, b n không th thêm b t kỳ Sheet nào vào trong b ng tính này", r i ngay l p t c, Excel s xóa cái Sheet m i thêm vào khi nút OK trong h p c nh báo ư c nh n. Công d ng c a dòng l nh Application.DisplayAlerts = False là không hi n th h p c nh báo xác nh n vi c xóa m t Sheet nào ó trong Excel. M t cách khác ngăn ch n vi c chèn thêm Sheet vào b ng tính là b n ch n Review trên thanh Ribbon, ch n l nh Protect Workbook trong nhóm Changes, r i ch n l nh Protect Structure and Windows [E2003: Tools | Protection | Protect Workbook... ánh d u ki m vào tùy ch n Structure] và nh n OK (có th t password n u thích). Tuy nhiên, như ã nói u bài, vi c này s ngăn ch n h t m i thao tác v i các Sheet trong b ng tính.
Ngăn ch n các nh c nh không c n thi t Exel luôn luôn c nh báo ho c nh c nh b n xác nh n các hành ng và thao tác c a b n. Bài này s hư ng d n b n cách lo i b các thông báo ho c nh c nh . Các ki u c nh báo mà tôi c p ây chính là các câu h i mà Excel h i b n có cho macro ho t ng hay không ho c là các thông báo v các hành ng xóa m t trang b ng tính. Sau ây là m t s lo i c nh báo c a Excel: Thông báo kích ho t Macro trong khi trong workbook không có macro L i này phát sinh do b n ã t ng th c hi n ghi macro trong workbook, và m c dù b n ã xóa t t c các mã l nh macro nhưng trong các l n m workbook sau ó Excel v n c nh báo b n là trong workbook có ch a macro. • Xóa macro: vào ngăn Developer | ch n Macro | ch n các macro và nh n Delete xóa [E2003: Tools | Macro | Macros]. • B n ch nh n ư c thông báo kích ho t macro khi thi t l p m c an toàn là “Disable all macro with notification” [E2003: m c Medium]. • N u m c an toàn ư c thi t l p là “Enable all macors” [E2003: m c Low] thì các macro s t ng kích ho t mà không có ti ng kêu c nh báo. • N u m c an toàn là “ Disable all macros without notification” [E2003: m c High] thì các macro t ng b khóa l i không ư c phép th c thi. Khi b n ghi m t macro, Excel chèn m t module vào ch a các l nh c a b n và các hàm. Vào lúc m m t b ng tính, Excel ki m tra xem b ng tính có ch a module nào không và chúng r ng hay ang ch a các mã l nh. Vi c xóa các macro c a b ng tính s xóa t t c các mã l nh ch a bên trong module mà không xóa các module – gi ng như vi c u ng c n lon s a và b l i v h p s a vào l i t l nh. Do v y, b n c n ph i xóa i các module này theo hư ng d n sau Ch n ngăn Developer | ch n nút Visual Basic t i nhóm Code ho c nh n t h p phím ALT+F11 [E2003: Tools | Macro | Visual Basic Editor (VBE) và ch n View | Project Explorer]. Các nút l nh trên ngăn Developer N u không th y c a s Project thì b n vào View và ch n Project Explorer ho c nh n t h p phím Ctrl+R C a s Project Explorer
Nh p chu t lên các ký hi u (+) xem các thánh ph n b n c a các nhánh con trong c a s Project và hãy tìm n các module. B n nh p ph i chu t lên t ng module và ch n l nh Remove Module t th c ơn ng c nh. N u ư c h i có xu t module ra thành t p tin thì hãy ch n No. Nh n t h p ALT+Q óng c a s VBE. Thông báo nh c nh lưu tr trong khi không có s thay i nào B n có th nh n th y r ng ôi khi b n ch m m t b ng tính và xem m t s thông tin trong ó mà Excel cũng nh c nh b n lưu l i các thay i trong Personal Macro Workbook m c dù b n không h thay i gì trong ó. Có th b n ã bi t ho c chưa, b n ã có s d ng m t hàm volatile (là hàm trong ó có s d ng phương th c volatile) trong Personal Macro Workbook c a mình. M t personal macro workbook là m t b ng tính n ư c t o l n u tiên b n ghi macro (Tools | Macro | Record NewMacro) v i tùy ch n “Personal Macro Workbook” t i “Store Macro in”. Macro này s ư c m m i khi b n s d ng Excel. M t hàm volatile s làm b ng tính t ng tính toán l i m i khi b n th c hi n m t thao tác nào ó trong Excel, bao g m c vi c m và óng b ng tính ho c ng d ng Excel. Hai trong s các hàm volatile ph bi n là hàm Today() và Now(). H p tho i Record Macro Vì th , m c dù b n ch c ch n là mình không có thay i gì trong b ng tính nhưng các hàm volatile này v n ang ho t ng âm th m. i u này ư c tính như là m t s thay i và Excel s c nh báo vi c lưu tr m c dù không t n t i b t kỳ s thay i nào trong b ng tính. N u b n mu n Excel d ng vi c thông báo lưu tr các thay i mà b n không h th c hi n, b n có m t vài cách th c hi n. Cách d th y nh t là ta không lưu các hàm volatile trong Personal Macro Workbook và nên xóa b t t c các hàm volatile ang có trong ó. Ho c, n u b n c n các hàm volatile nhưng không mu n Excel lưu l i các thay i không nhìn th y này b n có th thêm o n code sau vào Workbook module c a Personal Macro Workbook ánh l a nó là b ng tính ã ư c lưu r i. PHP Code: Private Sub Workbook_BeforeClose(Cancel As Boolean) Me.Saved = True End Sub m Personal Macro Workbook b n vào View | Unhide trong nhóm Window [E2003: Window | Unhide], ch n Personal.xls t h p tho i Unhide và nh p nút OK. Nh n t h p phím ALT+F11 vào c a s VBE, sau ó nh p ph i chu t lên ThisWorkbook t Personal.xls trong c a s Project r i ch n View Code và nh p o n mã trên vào. Sau khi nh p xong, b n nh n t h p phím ALT+Q óng c a s VBE l i.
T t nhiên, n u b n có s d ng hàm volatile tính toán l i b ng tính và b n mu n lưu các thay i này thì b n dùng o n mã sau thay cho o n mã nêu trên. PHP Code: Private Sub Workbook_BeforeClose(Cancel As Boolean) Me.Save End Sub o n mã trên s lưu l i Personal Macro Workbook m t cách t ng m i khi nó ư c m . D ng các c nh báo c a Excel v các macro ã ghi M t trong s nhi u h n ch c a vi c ghi macro là không t thêm vào các l nh lo i b các thông báo khi th c thi. Ví d như b n ghi m t macro xóa m t worksheet và khi th c thi macro này b n s nh n ư c thông báo xác nh n vi c xóa worksheet. Mu n t t c nh báo này, b n làm như hư ng d n sau: Ch n ngăn Developer | Macros t i nhóm Code ho c nh n ALT+F8 [E2003: Tools | Macro | Macros] g i h p tho i Macro. T i Macro in ch n “All Open Workbooks”, ch n tên macro mà b n v a ghi và nh n nút Edit. t con tr lên trư c dòng l nh u tiên c a th t c và nh p vào dòng l nh sau: Application.DisplayAlerts = False T i sau dòng l nh cu i cùng, b n thêm vào: Application.DisplayAlerts = True Macro c a b n sau khi thêm 2 dòng l nh trên s gi ng như sau: PHP Code: Sub MyMacro( ) ' ' MyMacro Macro Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub Lưu ý r ng, b n ã b t ch c năng c nh báo c a Excel l i t i dòng l nh cu i cùng c a macro. N u b dòng l nh này, b n s không bao gi nh n ư c các c nh báo n a và i u này có th gây nguy h i vì có th b n vô tình th c hi n m t thao tác nh m l n nghiêm tr ng nào ó mà không h nh n ư c c nh báo xác nh n hành ng. n sheet sao cho ngư i dùng không th dùng l nh unhide hi n ra ôi khi b n có nh ng thông tin trên 1 sheet nào ó và b n không mu n ngư i dùng c ư c. B n mu n có 1 khu v c (có th là 1 sheet) ghi chép nh ng d li u, công th c, nh ng chi ti t v n v t và không mu n cho ai nhìn th y. Thí d như 1 sheet ch a nh ng vùng làm d li u cho Data Validation, Combobox, nh ng tên bi n và giá tr bi n dùng làm const trong VBA, nh ng thông tin nh y c m và nh ng th khác. M c dù b n có th d u sheet ó b ng cách ch n View ➝Hide trong m c ch n Window ( i v i Excel 2003 là Format ➝Sheet ➝Hide), nhưng s t t hơn n u b n làm cách khác ngư i dùng không th ch n View ➝ Unhide trong m c ch n Window ( i
v i Excel 2003 là Format ➝Sheet ➝ Unhide). B n cũng có th dùng ch c năng khoá b ng tính, nhưng nó v n ư c nhìn th y. Hơn n a b n không th khoá nh ng ô ch a d li u k t n i v i nh ng control t o b i thanh công c Form. Thay vì v y, b n hãy thi t l p tr ng thái cho sheet d ng xlVeryHidden. Vào Developer ➝Visual Basic ho c nh n Alt-F11 vào trang so n th o VBE ( i v i Excel 2003 thì vào menu Tools ➝Macro ➝Visual Basic Editor và nhìn bên trái trong khung Project Explorer, n u không th y thì ch n View - Project Explorer. Tìm tên file và b m vào d u c ng hi n ra các tên sheet. Ch n tên sgeet nào b n mu n d u, m khung Property b ng cách vào menu View – Property Window (ho c nh n F4). Ch n tab Alphabetic, tìm dòng Visible và ch n 2- xlsSheetVeryHidden. Nh n Alt-Q tr v trang tính Excel, lưu s thay i này. Sheet c a b n s ch ng bao gi có th th y ư c b ng cách thông thư ng trên menu View – Unhide hay Format – Sgeet – Unhide. th y tr l i, b n ph i vào ch cũ, ch n l i -1 – xlSheetVisible
T thi t k m t b ng tính m u (template) N u như b n thư ng xuyên ph i th c hi n cùng m t ki u trình bày cho b ng tính c a mình, b n có th t thi t k ra m t b ng tính m u cho riêng b n, là m t b ng tính ã có s n tiêu , m t s c t, m t s hàng nào ó, th m chí là c Name và công th c; ng th i còn có th t o ra m t h p tho i riêng vào trong Thư vi n các Templates c a Excel. Không bi t là, có khi nào b n s d ng các Template không? Nghĩa là thay vì nh n Ctrl+N t o m t b ng tính m i, thì b n nh n vào nút Office, ch n l nh New [E2003: File | New], và ch n ra m t ki u b ng tính ã ư c thi t k s n trong thư vi n các Template, sau ó s a l i b ng tính này thành cái c a mình?
Ch n m t b ng tính ã ư c thi t k s n trong thư vi n Templates c a Excel N u b n ã t ng th y, ã t ng s d ng nh ng b ng tính ã ươc thi t k s n này, t b n s th y s h u d ng c a nó, nh nó mà chúng ta ti t ki m ư c bao nhiêu là th i gian cho vi c thi t k và trình bày. Và, trong công vi c, n u như b n c ph i thư ng xuyên thi t k nh ng b ng tính có c u trúc gi ng nhau, tôi nghĩ r ng vi c b n thư ng làm là m m t b ng tính ã có, xóa i ph n n i dung và lưu l i v i tên khác, ph i không? Nhưng cách làm này, ôi khi do sơ ý, b n s xóa luôn c nh ng công th c mà b n ã m t hàng gi nghĩ ra, r i ph i m t th i gian cho nh ng thao tác xóa... V y, có bao gi b n nghĩ n chuy n s làm m t cái gi ng như nh ng Template có s n c a Excel không? m i khi c n lo i m t b ng tính nào ó (m t b ng lương, m t b ng ch m công, m t b n báo giá, v.v...) thì b n ch vi c vào trong Thư vi n Templates, th m chí là vào trong "Thư vi n riêng" c a b n, l y m t cái m u ra r i ch vi c nh p s li u vào? Ti t ki m ư c bao nhiêu là th i gian. Chiêu th 6 này s giúp b n th c hi n i u tôi v a nói. T o m t b ng tính m u Nh ng Template cho phép b n có s n m t "d án" (project), có nghĩa là b n s có s n môt b ng tính, bao g m ph n tiêu c a b ng tính, m t s hàng và c t v i các tiêu c a nó, các công th c, và c các Name (n u có).. Ví d , ây là Template BillingStatement1 có s n trong Excel 2007:
Template BillingStatement1 có s n trong Excel 2007 B n cũng có th t o ra m t cái gi ng v y, sau ó lưu l i thành m t Template. Vi c này không có gì khó. B n m m t b ng tính m i, t o ra m t cái sư n: tô màu, nh d ng, t Name, thi t k công th c, v.v... như bình thư ng b n v n làm, nhưng khoan nh p d li u vào. B n hãy xóa h t các Sheet không c n thi t, ch gi l i nh ng Sheet ã ư c thi t k thôi. Ho c b n có th m m t b ng tính ã có s n, xóa h t nh ng d li u thô, ch gi l i ph n trình bày. Sau ó, b n nh n F12 (Save as...) r i ch n Excel Template:
Ch n lo i file là Excel Template trong c a s Save B n hãy t cho nó m t cái tên d nh , r i nh n Save. Theo m c nh thì Excel s lưu b ng tính m u này vào Thư m c ch a các Template ã ư c t o ra khi b n cài t Excel 2007: Excel s t ng ch n thư m c lưu các Template M i khi c n s d ng m t b ng tính có c u trúc y h t b ng tính b n v a lưu v i d ng Template như trên, b n nh n nút Office ➝ New [E2003: File | New], r i ch n ti p My Templates... c a s New Workbook:

CÓ THỂ BẠN MUỐN DOWNLOAD