Những mẹo nhỏ khi dùng công thức trong Excel

Những mẹo nhỏ khi dùng công thức trong excel     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!Trước khi kết thúc phần mở đầu, xin nói điều này: Nếu bạn chẳng nhớ được gì (vì tôi nói nhiều quá), bạn chỉ cần nhớ một chuyện thôi: Excel sẽ chạy tốt hơn nếu tất cả các dữ liệu của bạn được xếp liên tục với nhau trong cùng một bảng. Nó sẽ giúp cho bạn thực hành các chiêu sau đây một cách dễ dàng hơn. 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.       Excel: Mỗi ngày một tuyệt chiêuBạ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.      
  [ In bài ] [ Trở về ]     Tạo biểu mẫu đăng nhập trong Excel    (Thứ Ba, 06/11/2007 - 3:50 PM)     Hỏi: Xin hỏi quý báo có cách nào viết một Form đăng nhập trong Excel không? Ví dụ, tạo một Form có 1 TextBox, 2 CommandButton. Nếu nhập đúng Password vào TextBox thì đóng Form. Nếu nhập sai 3 lần thì thoát khỏi Excel     Trả lời: Thực ra, để bảo vệ một tập tin Excel, bạn có thể chọn Tools > Protection > Protect Workbook. Nếu bạn muốn thiết kế một form riêng để làm chuyện này thì cũng được. Ví dụ sau đây minh họa cách tạo một form trên Excel. Việc kiểm tra 3 lần gõ mật khẩu thì bạn làm tương tự.Trước tiên, bạn tạo một workbook mới trên Excel, đặt lại tên Sheet1 là Du Lieu (rồi gõ vào Sheet1 dữ liệu tùy ý để sau này kiểm tra), Sheet3 là Login. Vì sheet đầu tiên bao giờ cũng có dữ liệu, bạn không nên cho nó active (mặc định mỗi lần mở workbook), nếu không như thế sẽ mất đi tính bảo mật. Còn sheet Login nên là một sheet chẳng có dữ liệu gì cả, form kiểm tra mật khẩu sẽ được hiển thị trên sheet này.Nhấn phím Alt+F11 hoặc chọn Tools > Macro > Visual Basic Editor để mở cửa sổ VBA của Excel. Cách soạn thảo VBA trong Excel tương tự như trong Access. Trong cửa sổ VBA, chọn Insert > UserForm để tạo một form mới. Bạn sử dụng các công cụ trên Toolbox để thiết kế form như hình 1. Đặt tên form là frmLogin, tên nút lệnh OK là cmdOK, tên nút lệnh Cancel là cmdCancel, rồi gõ đoạn mã 1 vào (bấm kép lên form để mở cửa sổ soạn thảo, hoặc bấm-phải chuột vào tên form trong cửa sổ Project – VBAProject).Quay về Excel, chọn Tools > Macro > Macros. Trên hộp thoại Macro, gõ tên Auto_Open vào ô Macro name, bấm Create để mở cửa sổ VBA và gõ đoạn mã sau đây:   Sub Auto_Open()frmLogin.ShowEnd Sub   Auto_Open là một macro tự động chạy mỗi lần workbook được mở. Đoạn mã trên hiển thị form frmLogin mỗi lần workbook được mở.Để các macro có thể tự động chạy, bạn phải hạ mức security xuống Medium hoặc Low: quay về Excel, chọn Tools > Macro > Security để mở hộp thoại Security, trong thẻ Security Level chọn Medium (người sử dụng sẽ quyết định macro được chạy hay không) hoặc Low, bấm OK. Bạn đóng workbook rồi mở lại để kiểm tra.  
  Đoạn mã 1   Const sMatKhau = "echip"Private Sub cmdCancel_Click()MsgBox "Rat tiec vi ban khong chung minh duoc minh co quyen mo workbook nay!"ActiveWorkbook.CloseEnd SubPrivate Sub cmdOK_Click()With TextBox1If .Text = sMatKhau ThenActiveWorkbook.Sheets("Du Lieu").ActivateUnload MeExit SubEnd IfMsgBox "Mat khau khong dung!".SetFocusEnd WithEnd SubPrivate Sub UserForm_Initialize()ActiveWorkbook.Sheets("Login").ActivateWith TextBox1.Text = "".PasswordChar = "*"End WithEnd Sub   p----------------------------------------------------- Thêm lệnh thường dùng vào nút phải chuột trong Excel     Hỏi: Làm thế nào để thêm một số lệnh thường dùng vào nút phải chuột trong Excel?       Đáp: Excel (hay các ứng dụng khác trong bộ Microsoft Office) quản lý nhiều menu khác nhau, mỗi menu được nhận dạng thông qua tên riêng. Thí dụ menu pop-up được hiển thị mỗi khi bạn ấn phải chuột trên 1 cell bảng tính có tên là menu "Cell". Microsoft cho phép người dùng được toàn quyền truy xuất, hiệu chỉnh nội dung của từng menu. Thí dụ macro VBA sau đây cho phép bạn thêm 1 lệnh (control) mới vào menu pop-up "Cell":Sub AddMenuItem()'định nghĩa biến tham khảo đến menu.Dim objCommandBar As Office.CommandBar'định nghĩa biến tham khảo đến control option.Dim objCommandBarButton As Office.CommandBarButton'Xác định menu "Cell"Set objCommandBar = Application.CommandBars.Item("Cell")'thêm 1 option (lệnh) mới vào menu "Cell"Set objCommandBarButton = objCommandBar.Controls.Add(msoControlButton)'thiết lập các thuộc tính cho option mớiWith objCommandBarButton.Caption = "Chuc nang 1".FaceId = 43.Style = msoButtonIconAndCaption.OnAction = "OnChucNang1"End WithEnd Sub'thủ tục xử lý option mớiSub OnChucNang1()'thí dụ thông báo cho người dùng biếtMsgBox "Bạn mới ấn chức năng 1"End Sub        Bảo vệ file Excel bằng mật mã     Nói đến Microsoft Exel thì hẳn bạn phải nghĩ ngay đến những con số và những phép tính, có thể đơn giản hoặc phức tạp. Những giá trị trong các bảng tính đó đôi khi lại rất quan trọng, chỉ cần sai một con số có thể ảnh hưởng đến nhiều việc.        Nếu bạn phải thường xuyên làm việc với những bảng tính của Excel thì vấn đề đặt ra là làm sao để bảo vệ những giá trị có trong đó để tránh bị sai sót hoặc mất mát ngoài ‎ý muốn. Và để đáp ứng yêu cầu đó, Microsoft Excel cũng đã đưa ra tiện ích giúp để đặt mật mã cho file Excel. Trong Microsoft Excel, bạn có thể bảo vệ từng hàng, từng cột, hoặc một nhóm các hàng các cột trong từng worksheet hoặc toàn bộ file bằng cách đặt password lên từng mục bạn chọn, mà những ai không được phép không thể can thiệp vào file Excel đã được bảo vệ để chỉnh sửa. Bạn có thể đặt 2 dạng password: - Password để mở file: Nếu bạn đặt password theo dạng này, bạn phải điền đúng password để mở file, nếu không bạn sẽ không thể mở file đó. - Password để chỉnh sửa file: Nếu bạn đặt password theo dạng này, bạn phải điền đúng pasword mới có thể chỉnh sửa lại nội dung file, nhưng vẫn xem nội dung của file đó. Để đặt password cho tài liệu Ecxel, thực hiện theo các bước sau: - Ở cửa sổ giao diện của Excel, click Tools từ thanh công cụ, và chọn Options từ menu sổ xuống.
  - Một cửa sổ mới xuất hiện, tại đây bạn chọn thẻ Security. Tại thẻ này, bên dưới mục ‘File encryption settings for this workbook’ bạn sẽ điền password đầu tiên vào ô ‘Password to open’. Password này có tác dụng để bảo vệ file của bạn chỉ được mở khi điền đúng password. Nếu bạn muốn sử dụng password để khóa chức năng chỉnh sửa file những vẫn có thể cho người khác xem nội dung file đó, bạn điền password vào hộp thoại ‘Password to modify’ ở bên dưới mục ‘Fire sharing settings to this workbook’.
  - Sau khi điền Password tại đây và nhấn Ok, một hộp thoại mới xuất hiện yêu cầu bạn xác nhận Password vừa điền, bạn chỉ cần điền lần lượt 2 Password vừa điền trước đó rồi nhấn Ok.