Bài 2 : MỘT SỐ KIẾN THỨC CƠ BẢN VỀ MS EXCEL

I. Lọc các bản ghi trùng lặp trong excel 2007

Quản Trị Mạng - Trong thực tế chúng ta thường xuyên phải gặp các trường hợp trùng lặp dữ liệu trong một danh sách hoặc một bảng dữ liệu và thường làm thủ công là dò tìm thông tin từng bản ghi một và loại bỏ chúng, công việc này có thể mất rất nhiều thời gian và dễ sai sót, nhầm lẫn đối với dữ liệu hàng ngàn bản ghi. Để làm cho công việc này dễ dàng hơn, các bạn có thể sử dụng công cụ lọc bỏ các bản ghi trùng lặp một cách nhanh nhóng, đơn giản và hiệu quả nhất. Ví dụ: Các dòng bôi màu đỏ có dữ liệu trùng trên trường TỜ BD, SỐ THỬA. Trường HỌ TÊN 1: Nguyễn Hoàng An có 2 dòng bản ghi chứa cùng thông tin về TỜ BD, SỐ THỬA. Chức năng này sẽ lọc bỏ bớt các dòng bản ghi trùng lặp thông tin: Đầu tiên, bạn chọn vùng cần lọc dữ liệu, ở đây là từ A1:J21, chọn menu Data -> nút Remove Duplicate Trong hộp thoại Remove Duplicate, các bạn sẽ thấy 2 nút lệnh: Select All: để chọn tất cả các trường trong bảng dữ liệu Unselect All: hủy chọn tất cả các trường trong bảng dữ liệu Nút check My data has header: nếu dữ liệu của bạn có hoặc không có các đặt tên các trường, nếu có đặt tên trường mà không dấu chọn kiểm tra nút này thì các trường sẽ thành thứ tự các cột theo ABC Nếu có tên trường và thực hiện đánh dấu kiểm tra vào mục này thì tên trường sẽ hiển thị. Ví dụ ở đây để lọc các thông tin trùng trên trường TỜ BD, SỐ THỬA ta thực hiện thao tác: - Chọn nút Unselect All để hủy chọn - Đánh dấu chọn vào 2 trường TỜ BĐ, SỐ THỬA, tiếp theo chọn OK Chương trình sẽ lọc các bản ghi trùng theo kết quả ở bảng dữ liệu sau: Các bạn sẽ thấy ở đây có tổng cộng 20 dòng bản ghi, sau khi thực hiện cho kết quả có 03 bản ghi trùng sẽ loại bỏ và còn 17 bản ghi. Hy vọng với thủ thuật nhỏ trên các bạn sẽ thấy hiệu quả khi lọc các bảng dữ liệu lớn, thời gian thực hiện rất nhanh.

Vũ Hoàng Thương(theo quantrimang.com) II. Gán thêm nút chức năng Up và Down trong Excel Quản Trị Mạng - Trong bài viết dưới đây, chúng tôi sẽ giới thiệu với các bạn một số bước cơ bản để gán thêm nút chức năng Up và Down trong bảng tính Excel. Để thực hiện việc này, chúng ta cần kích hoạt chế độ hoạt động của ribbon Developer – vốn là tab chức năng đã được thiết lập sẵn theo những tiêu chuẩn nhất định và có sẵn ở chế độ mặc định. Việc kích hoạt và sử dụng tab này thường đơn giản và nhanh hơn rất nhiều so với tab tự thiết lập, các bước làm dưới đây được áp dụng trên chương trình Microsoft Outlook 2010, nhưng đối với Excel hoặc Word 2010 thì các bước thực hiện cũng tương tự. Trước tiên, các bạn mở Office 2010, sau đó nhấn menu File > Options: Cửa sổ Options hiển thị, chọn Customize Ribbon sau đó đánh dấu vào ô Developer bên dưới phần Main Tabs. Nhấn OK để lưu lại các sự thay đổi: Và tab Developer sẽ hiển thị trong thanh công cụ dưới dạng ribbon như hình dưới: Tại đây, các bạn nhấn Insert > Spin Button: Tiếp theo, chọn dòng dữ liệu cần đặt các nút này: Chọn Spin Button và nhấn Properties: Các tùy chọn tương ứng dành cho nút chức năng này sẽ hiển thị, các bạn nhấn Control và nhập dữ liệu theo yêu cầu. Phần giá trị liên kết này sẽ hiển thị trong bảng tính và được áp dụng trực tiếp qua nút tăng hoặc giảm giá trị này: Chúc các bạn thành công! T.Anh (theo GroovyPost) III. Thay đổi màu giữa các dòng khác nhau trong Microsoft Excel Quản Trị Mạng - Việc chọn và đổ màu riêng biệt đối với các vùng dữ liệu đặc biệt có ích khi bạn có 1 bảng tính với rất nhiều con số thống kê khác nhau. Trong bài viết dưới đây, chúng tôi sẽ giới thiệu với các bạn một số thao tác để làm việc này trong Microsoft Excel. Trước tiên, các bạn mở Excel và chọn vùng dữ liệu cần đổ màu, hoặc nhấn Ctrl + A để chọn toàn bộ: Chọn tiếp tab Home ở bên trên và Styles > Conditional Formatting: Menu có dạng drop – down hiển thị, các bạn chọn New Rule: Cửa sổ New Formatting Rule hiển thị, nhấn nút ở phía cuối màn hình với tên là: Use a Formula to Determine Which Cells to Format. Nếu trường dữ liệu trống, các bạn sử dụng công thức như dưới đây: =MOD(ROW()/2,1)>0 Cửa sổ New Formatting Rule sẽ trông giống như hình dưới: Nhấn nút Format, sau đó chọn màu để sử dụng, nhấn tiếp OK: Chế độ xem trước màu – Color Preview hiển thị trên cửa sổ New Formatting Rule như hình dưới, nhấn OK: Và phần dữ liệu trên bảng tính của bạn sẽ được đổ màu dựa theo giá trị và quy luật áp dụng. Ví dụ như hình dưới: Nếu muốn thay đổi lại màu hoặc thêm, bớt dòng dữ liệu thì chúng ta chỉ cần chọn thêm ô tương ứng, sau đó chọn Conditional Formatting > Manage Rules: Cửa sổ Conditional Formatting Rules Manager xuất hiện, nhấn nút Edit Rule: Chúng ta sẽ được quay trở lại phần Edit Formatting Screen, tại đây các bạn hãy điều chỉnh và thiết lập điều kiện để thay đổi theo ý muốn: Chúc các bạn thành công! >>> Video hướng dẫn tham khảo: IV. Xóa bỏ ô dữ liệu trống trong Excel 2007 hoặc 2010 Quản Trị Mạng - Trong bài viết dưới đây, chúng tôi sẽ hướng dẫn với các bạn một số thao tác cơ bản để xóa ô trống trong bảng tính Excel 2007 hoặc 2010. Trước tiên, các bạn mở bảng tính và nhấn Ctrl + A để chọn toàn bộ, hoặc chọn phần dữ liệu nhất định cần áp dụng: Tiếp theo, nhấn F5, menu Go To sẽ hiển thị, nhấn tiếp Special: Cửa sổ Go To Special tiếp theo xuất hiện, chúng ta chọn Blanks và nhấn OK: Sau đó, Excel sẽ lựa chọn tất cả các ô dữ liệu trống trong bảng tính, và để xóa bỏ thì các bạn nhấn [Ctrl] [-], cửa sổ Delete sẽ hiển thị, chọn tiếp Entire Row hoặc Entire Column để thay đổi các ô xung quanh và xóa bỏ ô dữ liệu trống: Chúc các bạn thành công! T.Anh (theo GroovyPost) V. Thủ thuật cơ bản với Sheet trong MS Excel Thay vì sử dụng các thẻ (tab) để quản lý nhiều cửa sổ làm việc như trên một số ứng dụng thì MS Excel lại hỗ trợ tích hợp các Sheet (bảng tính) cũng dưới dạng thẻ để giúp bạn dễ dàng quản lý các bảng tính và liên kết chúng với nhau. Nếu như không thích các thiết lập sẵn có của Sheet, bạn có thể tùy chính theo ý thích và kết hợp với các thao tác cơ bản khác trong quá trình làm việc với các Sheet. 1. Thay đổi kích thước hiển thị thẻ Sheet Theo mặc định, bạn sẽ thấy phần tab hiển thị các Sheet phía dưới có kích thước nhỏ, vậy để tăng kích thước của nó cho dễ thấy bạn hãy làm theo các bước sau đây: Với Windows XP: - Click chuột phải lên màn hình Desktop và chọn Properties, duyệt qua thẻ Appearance, nhấn nút Advancedbên dưới, từ danh mục Item bạn click chọn Sroobar, thay đổi kích thước trong mục Size và OK 2 lần để thừa nhận. Với Windows 7: - Click chuột phải lên Desktop và chọn Personalize, nhấn chọn mục Windows Color và click tiếp vào dòng link màu xanh Advanced Appearance settings, sau đó thực hiện các bước tương tự như với Windows XP. 2. Xác định số Sheet hiển thị mặc định Mặc định cửa sổ MS Excel chỉ hiển thị tối đa 3 Sheet khi mở một cửa sổ làm việc mới, và bạn có thể thêm hoặc xóa một Sheet dễ dàng. Tuy nhiên, để thay đổi số Sheet hiển thị mặc định, từ danh mục Tools chọnOptions, duyệt qua thẻ General và thay đôi con số trong mục Sheet in new workbook. (Với Office 2007, nhấn nút Office Button và chọn Excel Options, tại mục Popular trên Panel bên trái, bạn thay đổi con số tùy ý sau dòng chữ Include this many Sheet trong khung When creating new workbooks). 3. Thay đổi màu thẻ Sheet Như bạn thấy, các thẻ Sheet đều có màu tương tự như sau, mục đích của việc đặt màu riêng biệt cho mỗi Sheet là để bạn có thể dễ dàng phân loại bảng tính. Vậy để thay thay đổi màu mặc định bạn, bạn click chuột phải lên thẻ Sheet bất kỳ và chọn Tab Color sau đó chọn màu túy ý và OK. 4. Sao chép định dạng của một Sheet Mỗi Sheet đều có một định dạng in khác nhau trước khi in, để khỏi mất công lặp lại các định dạng này cho một Sheet khác bạn có thể tiến hành sao chép nhanh bằng các bước như sau: Chọn một thẻ Sheet có chứa định dạng mà bạn muốn sử dụng cho Sheet khác, nhấn giữ phím Ctrl đồng thời nhấn chọn thẻ Sheet cần cập nhật. Từ danh mục File, chọn Page Settup, nhấn OK để thừa nhận. (Với Office 2007, chọn lệnh Print từ nút Office Button, tại cửa sổ Print bạn nhấn nút Preview, sau cùng chọn biểu tượngPage Settup từ thanh công cụ trong cửa sổ Print Preview). 5. Ẩn vùng làm việc trong Sheet Muốn ẩn một Sheet, bạn click chọn nó rồi vào danh mục Format, chọn Sheet > Hide (để hiện lại, chọnUnhide). Nhưng trong một bảng tính dữ liệu, khi cần ẩn một vùng được chọn bạn thực hiện như sau: Click chọn một cột hoặc một hàng đầu tiên của vùng dữ liệu cần ẩn, sau đó nhấn giữ chuột đồng thời kéo chọn phạm vi vùng dữ liệu rồi nhấn chuột phải và chọn Hide. Để hiện lại, bạn chỉ cần truy cập lệnh Format > Row > Unhidehoặc Format > Column > Unhide. Theo XHTT VI. 3 phương thức cơ bản để tránh lỗi nhập dữ liệu trong Excel Bạn có thể dành nhiều thời gian cho việc định dạng và lập công thức tính toán trong bảng tính của mình, nhưng tất cả công việc của bạn đều lãng phí nếu như một ai đó nhập giá trị không phù hợp vào đó. Thông thường thì bạn sẽ sửa lại các lỗi nhập liệu khi gặp tình trạng này, nhưng đâu là điều thiết thực nhất mà bạn có thể tránh các lỗi nhập liệu? Hãy áp dụng các phương thức cơ bản dưới đây: 1. Xác nhận giá trị đầu vào Việc sử dụng tính năng Data Validation trong Excel có thể loại bỏ dữ liệu không phù hợp khi người dùng nhập vào. Nghĩa là bạn sẽ chỉ rõ các điều kiện mà một giá trị phải đáp ứng, và khi đó Excel sẽ bác bỏ giá trị không đáp ứng điều kiện đưa ra và thông báo lỗi nhập liệu. Để xác nhận dữ liệu nhập vào, từ giao diện cửa sổ MS Excel 2007/2010 bạn duyệt đến thẻ Data, nhấn vào biểu tượng Data Validation và chọn Data Validation (Với phiên bản Excel 2003 thì chọn Validation từ danh mục Data), tại cửa sổ hiện ra, bạn click vào thẻ Settings, trong khung Validation criteria hãy chọn Decimal cho mụcAllow, between cho mục Data và nhập giá trị Minimum và Maximum phía dưới và nhấn OK để thừa nhận. Chẳng hạn ở đây chúng tôi nhập giá trị từ 1 đến 1000, khi đó nếu người dùng nhập liệu ngoài vùng dữ liệu này thì lập tức sẽ có thông báo lỗi. Lưu ý: Bạn phải chọn vùng dữ liệu trên bảng tính trước khi thực hiện việc xác nhận. Ngoài ra, bạn có thể thiết lập thông báo điều kiện nhập liệu bằng cách chọn thẻ Input Message rồi nhập tiêu đề và nội dung hiển thị. Để tạo một cảnh báo bằng tiếng việt-cảnh báo này hiện ra ngay sau khi nhập sai dữ liệu (mặc định là tiếng anh), bạn chọn thẻ Error Alert, chọn kiểu hiển thị tại mục Style có thể là Stop, Warning hoặcInformation, tiếp đến nhập tiêu đề và nội dung cảnh báo. 2. Không nên sử dụng tính năng AutoComplete AutoComplete trong Excel là tính năng tự động hoàn chỉnh dữ liệu nhập ngay sau khi người dùng nhập giá trị đầu vào phù hợp hay có ký tự gần giống với giá trị hiện có. Và như vậy thì AutoComplete sẽ làm giảm thời gian nhập liệu nhưng nó có thể tăng khả năng nhập liệu sai khi có rất nhiều giá trị giống nhau. Để vô hiệu hóa tính năng này, bạn nhấn vào nút Office Button và chọn Excel Options, trong Panel bên trái hãy click lên mục Advanced, sau đó bỏ chọn trước dòng chữ Enable AutoComplete For Cell Values và OK (Đối với Excel 2003 bạn phải chọn Options từ Menu Tools, và chọn thẻ Edit). 3. Sử dụng một danh sách động (Dynamic list) Thêm hoặc xóa các giá trị sẽ không cập nhật các mục trong một danh sách đã xác nhận nếu chúng ta không sử dụng tính năng tham chiếu trong Excel. Đối với Excel 2007 và 2010, khi chuyển đổi một danh sách đã có thành bảng rồi tạo một list mới liên kết qua bảng này. Khi đó, nếu người dùng thêm hoặc xóa dữ liệu từ bảng thì trong list cũng tự động thay đổi theo.  Khi bạn thêm hoặc xóa dữ liệu trong cột L thì list N sẽ tự động cập nhật theo Trước hết, bạn chọn vùng dữ liệu trên một cột đơn, sau đó vào thẻ Insert chọn biểu tượng Table từ nhómTables, trong cửa sổ create Table bạn đánh dấu kiểm trước mục My table has headers và nhấn OK. Khi đó toàn bộ dữ liệu này đã chuyển thành bảng. Bây giờ bạn chọn một ô trống bất kỳ rồi vào thẻ Data và nhấn chọnData Validation như trên, nhưng trong thẻ Settings, đối với mục Allow bạn phải chọn List, click chuột vào ôSoucre và khoanh vùng dữ liệu là bảng đã tạo rồi nhấn OK, list vừa tạo sẽ giống như bạn sử dụng tính năngFilter. Theo XHTT VII. "Đóng gói" tập tin Excel Nhiều bạn đọc có yêu cầu “bảo mật” đối với những tập tin Excel giải quyết những bài toán của riêng mình để có thể “phân phối” chuyên nghiệp. Bài viết sẽ trình bày các bước cách thức “đóng gói” và tạo giao diện như một phần mềm bằng ngôn ngữ VB và người dùng phải cài đặt hoặc điền CD key khi mở tập tin. 1. Trong file Excel, bạn có thể viết một số đoạn code bằng ngôn ngữ VBA để xử lý dữ liệu trong file Excel hiện hành (và các file Excel khác nếu muốn). Các đoạn code VBA này sẽ truy xuất dữ liệu của file Excel thông qua mô hình hướng đối tượng. Thí dụ bạn có thể dùng các đối tượng ActiveWorkbook, Workbooks(<tên>) để truy xuất workbook hiện hành hay workbook với tên xác định, hay các đối tượng ActiveSheet hay Sheets(<tên>) để truy xuất worksheet hiện hành hay worksheet với tên xác định. Thí dụ thủ tục CreateSheet sau đây sẽ tạo 1 worksheet mới (trong workbook hiện hành) có tên là "YourSheet", rồi tự động nhập thông tin tọa độ của từng cell trong vùng cell từA1 tới D10: Sub CreateSheet() Dim rg As Range Dim row As Integer, col As Integer 'tạo worksheet mới Sheets.Add 'đặt tên cho worksheet mới ActiveSheet.Name = "YourSheet" 'thiết lập vùng cell cần xử lý Set rg = Range("A1:D10") 'nhập thông tin tọa độ cho từng cell 'duyệt theo hàng For row = 1 To 10 'duyệt theo cột For col = 1 To 4 rg.Cells(row, col).Value = "(" & row & "," & col & ")" Next col Next row 'lưu kết quả lên file ActiveWorkbook.SaveAs "c:\YourWB.xls" End Sub Thật ra các đối tượng ActiveWorkbook, Workbooks, ActiveSheet hay Sheets được truy xuất trong thủ tục trên chỉ là đối tượng con của đối tượng Application, đối tượng Application miêu tả ứng dụng Excel đang chạy. Do đó nếu muốn dùng đường dẫn tuyệt đối để truy xuất các đối tượng, thủ tục CreateSheet() trên có thể được viết lại như sau: Sub CreateSheet() Dim rg As Range Dim row As Integer, col As Integer 'tạo worksheet mới Application.Sheets.Add 'đặt tên cho worksheet mới Application.ActiveSheet.Name = "YourSheet" 'thiết lập vùng cell cần xử lý Set rg = Range("A1:D10") 'nhập thông tin tọa độ cho từng cell 'duyệt theo hàng For row = 1 To 10 'duyệt theo cột For col = 1 To 4 rg.Cells(row, col).Value = "(" & row & "," & col & ")" Next col Next row 'lưu kết quả lên file Application.ActiveWorkbook.SaveAs "c:\YourWB.xls" End Sub Bây giờ nếu muốn chuyển các đoạn code VBA đã chạy tốt trong file Excel thành ứng dụng VB 6.0 độc lập, bạn cần thực hiện một số công việc sau: - Sử dụng thư viện "Microsoft Excel x.0 Object Library" chứa các đối tượng Excel.Application,ActiveWorkbook, Workbooks, ActiveSheet, Sheets… trong Project quản lý ứng dụng VB 6.0 của bạn. - Viết code khởi tạo đối tượng Excel.Application miêu tả ứng dụng Excel, trước khi sử dụng đối tượng này để truy xuất các đối tượng con của nó. - Copy đoạn code VBA dùng các đối tượng bằng đường dẫn tuyệt đối đã chạy tốt vào đây. - Sau khi xử lý dữ liệu của file Excel, viết code đóng đối tượng Excel.Application lại. 2. Muốn bảo vệ phần mềm bởi người dùng bất hợp pháp, bạn có thể viết thêm đoạn code kiểm tra password, chèn đoạn code này vào đầu phần mềm. Để thấy rõ cách giải quyết các vấn đề vừa trình bày, chúng tôi xin giới thiệu qui trình điển hình để xây dựng 1 ứng dụng VB 6.0, nó sẽ yêu cầu người dùng nhập password, kiểm tra password nhập với password hợp pháp được lưu trong ứng dụng (hay trên file dữ liệu), nếu không đúng thì ứng dụng sẽ dừng, còn nếu đúng chương trình sẽ chạy tiếp. Chương trình gồm 1 form chính chứa 1 button Create, khi người dùng click chuột button này, ứng dụng sẽ chạy Excel, tạo 1 sheet mới có tên là "YourSheet", nhập tọa độ vào từng cell trong vùng cell từ A1 tới D10, lưu kết quả lên file "c:\YourWB.xls": 1. Chạy VB 6.0, khi cửa sổ New project hiển thị, chọn icon Standard EXE rồi button Open để tạo mới Project chứa Form đơn giản. 2. Chọn menu Project.Components để hiển thị cửa sổ Components, duyệt tìm và chọn thư viện có tên làMicrosoft Excel x.0 Object Library rồi OK để thêm các đối tượng trong thư viện này vào ToolBox của Project hiện hành. 3. Vẽ 1 button vào form, thíết lập lại thuộc tính (Name)=btnCreate, thuộc tính Caption="Create". 4. Ấn kép chuột vào button vừa tạo để tạo thủ tục xử lý sự kiện click chuột vào button rồi viết code cho thủ tục này như sau: Option Explicit 'khai báo hàm API cần dùng Private Declare Sub ExitProcess Lib "kernel32" (ByVal uExitCode As Long) 'định nghĩa hằng miêu tả password đã mật mã Const ENPASS = "zpvsqbtt" 'hàm mật mã chuỗi password đơn giản Function Encryph(pass As String) As String Dim bytes() As Byte Dim str As String Dim i As Integer bytes = StrConv(pass, vbFromUnicode) str = "" For i = 0 To UBound(bytes) str = str & Chr(bytes(i) + 1) Next i Encryph = str End Function 'thủ tục khởi tạo form Private Sub Form_Load() Dim pass As String 'hiển thị form nhập password frmPassword.Show vbModal pass = frmPassword.txtPassword Unload frmPassword 'kiểm tra password nhập If ENPASS <> Encryph(pass) Then 'nếu sai thì dừng ứng dụng ExitProcess (1) End If 'nếu đúng thì chạy tiếp bình thường End Sub 'thủ tục phục vụ click button Create Private Sub btnCreate_Click() 'định nghĩa các biến cần dùng Dim Application As Excel.Application Dim WorkBook As Excel.WorkBook Dim wksSheet As Excel.Worksheet Dim rg As Range Dim row As Integer, col As Integer 'tạo ứng dụng Excel Set Application = New Excel.Application 'tạo workbook Excel Set WorkBook = Application.Workbooks.Add 'đoạn code VBA đã có sẵn từ file Excel 'tạo worksheet mới Application.Sheets.Add 'đặt tên cho worksheet mới Application.ActiveSheet.Name = "YourSheet" 'thiết lập vùng cell cần xử lý Set rg = Range("A1:D10") 'nhập thông tin tọa độ cho từng cell 'duyệt theo hàng For row = 1 To 10 'duyệt theo cột For col = 1 To 4 rg.Cells(row, col).Value = "(" & row & "," & col & ")" Next col Next row 'lưu kết quả lên file Application.ActiveWorkbook.SaveAs "c:\YourWB.xls" 'dừng ứng dụng Excel Application.Quit Set Application = Nothing End Sub 5. Dời chuột về cửa sổ Project (thường nằm ở phía trên phải của màn hình), ấn phải chuột vào mục Project(phần tử gốc của cây Project) để hiển thị menu chức năng. Chọn mục Add.Form để hiển thị cửa sổ tạo form, chọn icon Form rồi button Open để tạo 1 form trống mới. Thiết lập lại thuộc tính Caption="Nhập password sử dụng", thuộc tính (Name)=frmPassword. 6. Vẽ 1 label, 1 textbox nhập password và 1 button OK vào form như hình vẽ: Thiết lập thuộc tính (Name) cho TextBox là txtPassword, thuộc tính (Name) cho button là btnOK. Ấn kép chuột vào button OK để tạo thủ tục xử lý sự kiện click chuột trên button này rồi viết code cho thủ tục như sau : Private Sub btnOk_Click() Me.Visible = False End Sub 7. Chọn menu File.Save Project As… để lưu Project lên đĩa. 8. Chọn menu Run.Start để chạy thử ứng dụng. 9. Khi ứng dụng chạy, form yêu cầu nhập password sẽ hiển thị, nếu người dùng nhập sai password qui định bởi ứng dụng, ứng dụng sẽ dừng. Nếu người dùng nhập đúng password thì form chính của ứng dụng sẽ hiển thị, lúc này nếu người dùng ấn vào button Create thì đoạn code tạo đối tượng Excel và xử lý dữ liệu sẽ chạy.