Thủ thuật Excel - Phần I

Thủ Thuật Excel - PHẦN I Thủ Thuật ExcelPHẦN I1.Tự động tính tổng với hàm Sum()Khi bạn tính tổng một cột nào đó ví dụ như từ cell A1 đến A5 có các số liệu sau : 1-2-3-4 và 5. Trong cell A7 sẽ là tổng của các giá trị đó, bạn click vào cell A7 và nhấn giữ Alt và nhấn dấu bằng ( = ) ngay lập tức sẽ được thực thiện phép tính cộng các giá trị ở trên lại với hàm Sum().2.Sắp xếpKhi bạn sắp xếp dữ liệu trong một cột của Excel, đôi khi bạn sẽ thấy Excel sắp xếp không đúng, ví dụ như khi bạn sắp xếp từ cao xuống thấp bạn sẽ thấy con số 95.5 nằm dưới số 95. Ðể khắc phục bạn làm như sau : 1/Chọn cột muốn sắp xếp, bằng cách click trên đầu cột đó. 2/Mở menu Format – Cell. 3/Trong hộp thoại format cell, click chọn tab Number và click trên General category. 4/ Sau đó sắp xếp lại cột đó.3.Lấy căn bậc 2Trong Excel nếu như bạn muốn lấy căn bậc hai của một số nào đó bạn có thể dùng hàm như sau : SQRT(number), trong đó number là số muốn lấy căn.4.Tạo tiêu đề cho nhiều sheet trong lần thực hiệnTrong Workbook của bạn có nhiều sheet và bạn muốn tạo cho mỗi sheet như vậy có tiêu đề giống nhau, bạn chỉ cần làm một lần như sau: nhấp chuột phải vào tab để chọn các sheet, chọn Select All Sheets từ menu phụ mở ra. Bạn đặt tiêu đề trên Sheet đầu tiên, có thể là chữ, hình ảnh ,... sau đó nhấp chuột phải lên tab của sheet hiện hành và chọn UnGroup Sheets từ menu phụ. Tiêu đề sẽ xuất hiện ở tất cả các sheet trong workbook.5.Xem cú pháp hàm ExcelNếu như bạn chưa biết hết các hàm trong Excel, hoặc bạn biết nhưng không nhớ hết được cú pháp các hàm trong Excel, bạn chọn menu Insert - Function, hộp thoại Paste Function xuất hiện, từ đây bạn có thể chọn hàm muốn sử dụng cho mục đích của bạn một cách chính xác.6.Có bao nhiêu Sheet trong WorkBookNếu như bạn mở một Workbook có rất nhiều sheet và bạn muốn xem qua xem có bao nhiêu sheet tất cả trong WorkBook đó bạn chỉ cần chọn Menu File - Properties , chọn tab Contents, sẽ liệt kê tất cả các sheet có trong file cho bạn xem.7.Importing Text sang ExcelKhi bạn có một dữ liệu text được soạn thảo trong Word, khi bạn import đoạn dữ liệu đó vào worksheet thì dữ liệu đó lại nằm trên một cột. Ðể dữ liệu nằm trên nhiều cột, bạn có thể làm bằng nhiều cách: 1/ Nếu như dữ liệu được format dưới dạng table thì bạn chọn Copy sau đó Paste nó vào Excel. 2/ Dữ liệu không được format theo dạng table mà ở dạng tab thì cũng làm tương tự như trên. 3/ Nếu dữ liệu không được định dạng table và tab thì bạn cũng Import như 2 trường hợp trên sau đó chọn cột có chứa dữ liệu, chọn menu Data, Text to Columns. Excel sẽ xuất hiện chế độ Wizard hướng dẫn bạn import dữ liệu đó vào worksheet của Excel8.Liên kết dữ liệu Excel và WordTrong tài liệu mà bạn đang làm việc với Excel cần có một số nội dung mà nội dung đó giống như trong một tài liệu soạn thảo trong Word. Mà bạn muốn là khi thay đổi, chỉnh sửa nội dung thì cả hai nơi tài liệu Word và Excel cùng thay đổi. Bạn làm như sau :Chọn đoạn văn bản bạn muốn sao chép từ Word và Copy đoạn văn đó.Khởi động Excel, chọn nơi nào bạn muốn có đoạn text đó chọn Menu Edit + Paste Special. Trong hộp thoại Paste Special xuất hiện, chọn mục Paste Link và chọn Microsoft Word Document Object từ list sau đó click OK.9.Sử dụng dấu ngoặc kép ( " " ) trong công thứcTrong một số công thức bạn làm việc cần có những đoạn text chen vào, ví dụ như bạn nhập công thức để xếp loại chẳng hạn, bạn sẽ gặp những text như là LOAI TOT , LOAI XAU,... thì khi đó những text đó sẽ được bao bọc bởi dấu ngoặc kép ( " " ). ví dụ như sau : IF ( C17="DUMMIES", D17 + D18,E17 + E18 )10.Tìm hiểu lỗi xảy ra.Khi bạn sử dụng VBA trong Excel và cho thi hành một ứng dụng nào đó, bạn sẽ thấy một thông báo lỗi nhưng bạn không biết lỗi đó là lỗi gì. Bạn chỉ cần thêm dòng lệnh sau :msgbox Err.Description & vbCrLf & Err.numberHộp thoại sẽ hiển thị và cho bạn biết lỗi xảy ra và số của lỗi đó.11.Tạo chú thích (comment) cho cellKhi bạn muốn làm một lời chú thích nào đó cho một cell, bạn chọn cell cần làm chú thích , chọn menu Insert / Comment ( hoặc nhấp chuột phải lên cell đó và chọn Comment ). Nhập vào lời chú thích cho cell đó. Khi bạn di chuyển mouse đến cell đó thì sẽ xuất hiện một popup với lời chú thích bạn đã nhập.12.Sửa chữa nội dung cho chú thích (Comment )Sau khi bạn đã tạo một comment ( chú thích ) cho một cell nào đó, bây giờ bạn muốn sữa nội dung của comment đó thì bạn click vào cell, chọn menu Insert - Edit Comment. Bạn sẽ sữa đổi nội dung trong cửa sổ comment. Bạn cũng có thể edit comment và delete comment bằng cách nhấp chuột phải mouse.13.Ðịnh hướng cho con trỏ khi nhấn EnterTrong Excel, khi bạn nhập liệu cho một cell, con trỏ sẽ nằm trong cell đó và bạn nhấn Enter thì nó sẽ di chuyển xuống cell khác. Nhưng có thể bạn muốn nó phải di chuyển qua trái, phải hoặc là đi lên. Muốn vậy, bạn chọn menu Tools - Options trong hộp thoại Options chọn tab Edit, click trên drop down menu Direction để chọn Right, Left, Up hoặc Down14.Tìm SheetBạn có thể sử dụng VBA để tìm một Sheet đang có mặt trong Workbook. Trước tiên bạn mở VB Editor Rồi thêm một module. Tiếp theo nhập đoạn code sau vào :Public Function SheetExists(name As String) As BooleanSheetExists = False / For Each sht In ThisWorkbook.Worksheets /If sht.Name = name Then/ SheetExists = True/End If /Next sht/End Function. (Lưu ý : sau mỗi gạch "/ " bạn phải xuống dòng)Khi bạn gọi hàm, bạn sẽ cần có một tên của sheet mà bạn muốn tìm. Nếu như vòng lặp For Loop tìm thấy sẽ trả về trị là True và còn không thì là trị False. Bạn có thể kiểm tra điều này tại cửa sổ Immediate như sau :?SheetExists("nameofsheet"). Nameofsheet là tên của một sheet trong workbook. Thủ Thuật Excel - PHẦN I Thủ Thuật ExcelPHẦN I1.Tự động tính tổng với hàm Sum()Khi bạn tính tổng một cột nào đó ví dụ như từ cell A1 đến A5 có các số liệu sau : 1-2-3-4 và 5. Trong cell A7 sẽ là tổng của các giá trị đó, bạn click vào cell A7 và nhấn giữ Alt và nhấn dấu bằng ( = ) ngay lập tức sẽ được thực thiện phép tính cộng các giá trị ở trên lại với hàm Sum().2.Sắp xếpKhi bạn sắp xếp dữ liệu trong một cột của Excel, đôi khi bạn sẽ thấy Excel sắp xếp không đúng, ví dụ như khi bạn sắp xếp từ cao xuống thấp bạn sẽ thấy con số 95.5 nằm dưới số 95. Ðể khắc phục bạn làm như sau : 1/Chọn cột muốn sắp xếp, bằng cách click trên đầu cột đó. 2/Mở menu Format – Cell. 3/Trong hộp thoại format cell, click chọn tab Number và click trên General category. 4/ Sau đó sắp xếp lại cột đó.3.Lấy căn bậc 2Trong Excel nếu như bạn muốn lấy căn bậc hai của một số nào đó bạn có thể dùng hàm như sau : SQRT(number), trong đó number là số muốn lấy căn.4.Tạo tiêu đề cho nhiều sheet trong lần thực hiệnTrong Workbook của bạn có nhiều sheet và bạn muốn tạo cho mỗi sheet như vậy có tiêu đề giống nhau, bạn chỉ cần làm một lần như sau: nhấp chuột phải vào tab để chọn các sheet, chọn Select All Sheets từ menu phụ mở ra. Bạn đặt tiêu đề trên Sheet đầu tiên, có thể là chữ, hình ảnh ,... sau đó nhấp chuột phải lên tab của sheet hiện hành và chọn UnGroup Sheets từ menu phụ. Tiêu đề sẽ xuất hiện ở tất cả các sheet trong workbook.5.Xem cú pháp hàm ExcelNếu như bạn chưa biết hết các hàm trong Excel, hoặc bạn biết nhưng không nhớ hết được cú pháp các hàm trong Excel, bạn chọn menu Insert - Function, hộp thoại Paste Function xuất hiện, từ đây bạn có thể chọn hàm muốn sử dụng cho mục đích của bạn một cách chính xác.6.Có bao nhiêu Sheet trong WorkBookNếu như bạn mở một Workbook có rất nhiều sheet và bạn muốn xem qua xem có bao nhiêu sheet tất cả trong WorkBook đó bạn chỉ cần chọn Menu File - Properties , chọn tab Contents, sẽ liệt kê tất cả các sheet có trong file cho bạn xem.7.Importing Text sang ExcelKhi bạn có một dữ liệu text được soạn thảo trong Word, khi bạn import đoạn dữ liệu đó vào worksheet thì dữ liệu đó lại nằm trên một cột. Ðể dữ liệu nằm trên nhiều cột, bạn có thể làm bằng nhiều cách: 1/ Nếu như dữ liệu được format dưới dạng table thì bạn chọn Copy sau đó Paste nó vào Excel. 2/ Dữ liệu không được format theo dạng table mà ở dạng tab thì cũng làm tương tự như trên. 3/ Nếu dữ liệu không được định dạng table và tab thì bạn cũng Import như 2 trường hợp trên sau đó chọn cột có chứa dữ liệu, chọn menu Data, Text to Columns. Excel sẽ xuất hiện chế độ Wizard hướng dẫn bạn import dữ liệu đó vào worksheet của Excel8.Liên kết dữ liệu Excel và WordTrong tài liệu mà bạn đang làm việc với Excel cần có một số nội dung mà nội dung đó giống như trong một tài liệu soạn thảo trong Word. Mà bạn muốn là khi thay đổi, chỉnh sửa nội dung thì cả hai nơi tài liệu Word và Excel cùng thay đổi. Bạn làm như sau :Chọn đoạn văn bản bạn muốn sao chép từ Word và Copy đoạn văn đó.Khởi động Excel, chọn nơi nào bạn muốn có đoạn text đó chọn Menu Edit + Paste Special. Trong hộp thoại Paste Special xuất hiện, chọn mục Paste Link và chọn Microsoft Word Document Object từ list sau đó click OK.9.Sử dụng dấu ngoặc kép ( " " ) trong công thứcTrong một số công thức bạn làm việc cần có những đoạn text chen vào, ví dụ như bạn nhập công thức để xếp loại chẳng hạn, bạn sẽ gặp những text như là LOAI TOT , LOAI XAU,... thì khi đó những text đó sẽ được bao bọc bởi dấu ngoặc kép ( " " ). ví dụ như sau : IF ( C17="DUMMIES", D17 + D18,E17 + E18 )10.Tìm hiểu lỗi xảy ra.Khi bạn sử dụng VBA trong Excel và cho thi hành một ứng dụng nào đó, bạn sẽ thấy một thông báo lỗi nhưng bạn không biết lỗi đó là lỗi gì. Bạn chỉ cần thêm dòng lệnh sau :msgbox Err.Description & vbCrLf & Err.numberHộp thoại sẽ hiển thị và cho bạn biết lỗi xảy ra và số của lỗi đó.11.Tạo chú thích (comment) cho cellKhi bạn muốn làm một lời chú thích nào đó cho một cell, bạn chọn cell cần làm chú thích , chọn menu Insert / Comment ( hoặc nhấp chuột phải lên cell đó và chọn Comment ). Nhập vào lời chú thích cho cell đó. Khi bạn di chuyển mouse đến cell đó thì sẽ xuất hiện một popup với lời chú thích bạn đã nhập.12.Sửa chữa nội dung cho chú thích (Comment )Sau khi bạn đã tạo một comment ( chú thích ) cho một cell nào đó, bây giờ bạn muốn sữa nội dung của comment đó thì bạn click vào cell, chọn menu Insert - Edit Comment. Bạn sẽ sữa đổi nội dung trong cửa sổ comment. Bạn cũng có thể edit comment và delete comment bằng cách nhấp chuột phải mouse.13.Ðịnh hướng cho con trỏ khi nhấn EnterTrong Excel, khi bạn nhập liệu cho một cell, con trỏ sẽ nằm trong cell đó và bạn nhấn Enter thì nó sẽ di chuyển xuống cell khác. Nhưng có thể bạn muốn nó phải di chuyển qua trái, phải hoặc là đi lên. Muốn vậy, bạn chọn menu Tools - Options trong hộp thoại Options chọn tab Edit, click trên drop down menu Direction để chọn Right, Left, Up hoặc Down14.Tìm SheetBạn có thể sử dụng VBA để tìm một Sheet đang có mặt trong Workbook. Trước tiên bạn mở VB Editor Rồi thêm một module. Tiếp theo nhập đoạn code sau vào :Public Function SheetExists(name As String) As BooleanSheetExists = False / For Each sht In ThisWorkbook.Worksheets /If sht.Name = name Then/ SheetExists = True/End If /Next sht/End Function. (Lưu ý : sau mỗi gạch "/ " bạn phải xuống dòng)Khi bạn gọi hàm, bạn sẽ cần có một tên của sheet mà bạn muốn tìm. Nếu như vòng lặp For Loop tìm thấy sẽ trả về trị là True và còn không thì là trị False. Bạn có thể kiểm tra điều này tại cửa sổ Immediate như sau :?SheetExists("nameofsheet"). Nameofsheet là tên của một sheet trong workbook. Ðề: Thủ Thuật Excel - PHẦN I Thủ Thuật ExcelPHẦN IITăng giảm giá trị trong Excel bằng chức năng Paste SpecialBạn có một bảng tính Excel trong đó có các cột ghi giá trị mua bán của các mặt hàng. Khi thời giá lên xuống thất thường, cần phải điều chỉnh tăng hoặc giảm cho phù hợp với tình hình mới, thông thường, bạn sẽ chèn thêm một cột mới vào cạnh cột ghi giá trị trước đó để nhập công thức điều chỉnh. Tuy nhiên, có một cách khác nhanh hơn và dễ hơn mà không cần tới công thức hoặc chèn thêm bất kỳ một cột nào, cách làm như sau:- Tăng lên: Gõ giá trị (số lần) tăng thêm vào bất kỳ ô trống nào trong bảng tính, vào menu Edit\ Copy, chọn vùng dữ liệu cần tăng thêm, vào menu Edit\ Paste Special, trong hộp thoại Paste Special, chọn Multiply, nhấn OK.- Giảm xuống: Nếu muốn giá trị giảm đi bao nhiêu lần thì làm tương tự như trên nhưng lần này chọn Divide trong hộp thoại Paste Special.Nhập liệu trong Excel bằng Combo BoxBạn có sẵn một danh sách họ tên CBCNV của cơ quan hoặc sinh viên của trường. Công việc yêu cầu bạn phải nhập một số họ tên trong danh sách đã có theo một tiêu chí nào đó (ví dụ sinh viên được hưởng học bổng) vào một bảng biểu Excel. Bạn không thể dò tìm trong danh sách rồi copy, dán vào biểu vì rất mất thời gian. Trong trường hợp này, bạn hãy làm theo cách sau đây để tạo cho mình một công cụ chọn dữ liệu giống như cách thức hoạt động của các chương trình quản trị cơ sở dữ liệu.Trước tiên, bạn hãy chuẩn bị nguồn dữ liệu. Tại danh sách nguồn, bạn chèn thêm một cột trước họ tên và gán mã cho từng họ tên trong danh sách. Điều này là cần thiết giúp cho việc nhập liệu dễ dàng và nhanh chóng nên bạn cần kiên nhẫn thực hiện. Đặt tên cho nguồn dữ liệu: Quét chọn 2 cột mã và họ tên, thêm một ít dòng trống nữa dự phòng cho trường hợp bạn muốn bổ sung họ tên mới. Nhấp vào Name Box (là hộp bên trái thanh công thức), gõ tên DS. Vào Data\ Sort, giữ nguyên các mặc định, nhấn OK. Danh sách nguồn này phải cùng chung workbook với biểu dữ liệu cần nhập.Nhấn chuột phải vào thanh công cụ, chọn Control Toolbox. Chọn công cụ Combo Box và nhấn một lần để đặt Combo Box vào sheet. Nhấn chuột phải vào Combo Box chọn Properties. Trên trang Properties, đặt các thuộc tính sau cho Combo Box: Name - DSACH, Font - Phông chữ đã dùng trong danh sách nguồn, BoundColumn - 2, ColumnCount - 2, ColumnWidths - 40 pt; 150 pt, ListWidths - 190 pt, ListRows - 12, ListFillRange - DS. Những thuộc tính khác giữ nguyên mặc định.Nhấn đúp vào Combo Box để mở cửa sổ lệnh (code), sửa Private Sub DSACH_Change() thành Private Sub DSACH_Click() và nhập 2 câu lệnh sau đây ngay bên dưới:ActiveCell.Value = DSACH.ValueActiveCell.Offset(1, 0).Range("A1").SelectNhấn chọn nút Exit Design Mode để thử công dụng của Combo Box. Chọn ô đầu tiên cần nhập liệu. Nhấn vào mũi tên bên phải hộp Combo, mỗi khi bạn gõ chữ đầu của mã họ tên, chương trình sẽ nhanh chóng nhảy đến đoạn danh sách có chữ này. Bạn chọn họ tên cần thiết trong danh sách. Họ tên vừa chọn sẽ được điền vào ô hiện hành, đồng thời dấu chọn được dời xuống dưới một ô.Cài đặt / gỡ bỏ thuộc tính Read Only cho bảng tínhThông thường, nếu muốn xác định thuộc tính cho một tập tin nào đó, bạn dùng chương trình duyệt file (như Windows Explorer chẳng hạn) tìm đến nó rồi gán thuộc tính cho nó. Với MS Excel, bạn không cần phải rườm rà như vậy. Bạn có thể ấn định hoặc gỡ bỏ thuộc tính Read Only cho bảng tính của mình ngay khi đang mở chúng. Bạn hãy làm như sau: Khởi động Excel > vào menu Tools\ Customize > nhấn thẻ Commands > Ở cột Categories, nhấp chọn mục File. Ở cột Commands, tìm đến lệnh Toogle Read Only. Đây là một chức năng ẩn của Excel. Kéo và thả lệnh này vào một nơi nào đó trên thanh menu hoặc thanh công cụ. Trong ví dụ này, tôi đặt nó vào menu File. Bây giờ, mỗi khi làm việc với các tập tin *.XLS, nếu muốn ấn định / gỡ bỏ thuộc tính Read Only cho nó, bạn chỉ việc cho thực hiện lệnh Toggle Read Only là xong. Khi đó, hãy nhìn lên thanh tiêu đề của cửa sổ Excel, bạn sẽ thấy được thuộc tính hiện thời của bảng tính thông qua dòng chữ [Read-Only] có xuất hiện hay không.Chia sẻ bảng tính Excel trong mạng cục bộMột trong những thuận lợi của mạng LAN là bạn có thể giao tiếp, chia sẻ dữ liệu với nhau, làm cho công việc thuận lợi và nhanh chóng hơn. Ngược lại, cũng có một vấn đề mà nhiều người sử dụng đang gặp phải, đó là việc xử lý đồng thời cùng một dữ liệu. Khi có LAN, đa phần dữ liệu chỉ được lưu trữ ở một nơi, nên khi một người dùng trước thì những người sau đó không thể xử lý được (như sửa đổi, lưu...).Để nhiều người có thể sử dụng dữ liệu cùng một lúc, Excel có một chức năng là Share Workbook (chia sẻ bảng tính) giúp mọi người có thể sửa đổi, sao lưu... bảng tính cùng một lúc.1. Mở bảng tính cần chia sẻ. Vào menu Tools > Share Workbook. Đánh dấu mục Allow changes by more than one user at the same time. 2. Đóng bảng tính lại. Chép nó vào một thư mục cần chia sẻ. Nhấn chuột phải vào thư mục đó > chọn Sharing and Security > chọn Share this folder > OK.Hai bước trên đã hoàn thành xong việc chia sẻ bảng tính. Nhưng để linh động hơn, bạn có thể thay đổi cách thức chia sẻ như sau: - Trong hộp thoại Share Workbook, chọn thẻ Advanced.- Track changes (theo dõi những sửa đổi):+ Keep change history for: nhập số ngày cần lưu lại các sửa đổi.+ Don't keep change history: không lưu lại những sửa đổi.- Update changes (cập nhật những sửa đổi):+ When file saved: khi lưu tập tin.+ Automatically every: chọn một khoảng thời gian để chương trình tự động lưu.+ Save my changes and see other's changes: lưu các thay đổi của bạn và xem của những người khác.+ Just see other user's changes: chỉ xem các thay đổi của người khác.- Conflicting changes between users (cách xử lý xung đột các sửa đổi giữa những người dùng):+ Ask me which changes win: hỏi ý kiến của bạn nên chấp nhận những sửa đổi nào.+ The changes being saved win: không chấp nhận những sửa đổi của người khác, chỉ lưu của bạn.- Include in personal view (những thay đổi cá nhân):+ Print settings: cho phép mỗi người đều có thể thay đổi các thiết lập in.+ Filter settings: cho phép mỗi người đều có thể thay đổi các thiết lập lọc.Trích xuất các ghi chú (Comments) trong Excel sang WordChức năng [Insert \ Comment] trong Excel cho phép bạn thêm chú thích cho các ô một cách nhanh chóng và dễ dàng. Nếu so với việc in các Comments này ra giấy thì việc copy chúng vào văn bản Word để xem có vẻ khó hơn nhiều, nhất là khi bạn có quá nhiều Comments. Công việc này sẽ dễ dàng hơn nếu bạn sử dụng file VBS, tải miễn phí tại www.abcsoft.info/mods/export_excel_comment.zip hay tại đây. File VBS này hoạt động tốt cho mọi phiên bản Microsoft Office và Windows. Cách sử dụng như sau: 1. Download file trên về và giải nén nó vào một thư mục nào đó. 2. Sau khi chạy file vbs này, bạn hãy điền vào đường dẫn đến file Excel bạn cần Export Comments rồi nhấn nút OK. 3. Chương trình sẽ trích xuất các Comments sang một file Word mới và thông báo cho bạn biết khi đã hoàn tất.4. Bạn hãy mở file Word đó để xem. Tên file Word sẽ được đặt theo dạng TênFileExcel_comments.doc. Vậy là xong rồi đó! Bạn có thấy nhanh hơn làm thủ công nhiều lần không nào? NGUYỄN AN LỘC (Bình Thạnh, TP.HCM)