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

Chia sẻ: Hồng Ngọc Quý | Ngày: | Loại File: DOC | Số trang:7

Thêm vào BST

Báo xấu

2.430
lượt xem 1.032
download

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

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 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 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...

Chủ đề:

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

Lưu

Nội dung Text: 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 Ex 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ả 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ó 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ữ 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 T 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ư đượ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 ➝ Formu 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 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 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, 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 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ạ 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 s đế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 c 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ị ản đô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 đế (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 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ê 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à k 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ả 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 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êu 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ệ 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ã Vi 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 ➝ 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 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 độ a 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 m 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 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 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 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ạ 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 t 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 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 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]:
[ 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ấmphả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.Show End 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.Close End Sub Private Sub cmdOK_Click()