Bài 3: Sắp xếp và lọc dữ liệu

Sắp xếp dữ liệu là công việc cần thiết với bất kì một danh sách nào. Ví dụ, danh sách học sinh trong lớp bao giờ cũng được sắp xếp theo thứ tự Alphabet của Họ và tên, hay danh sách tồng kết điểm cuối năm thường được xếp theo thứ tự từ cao xuống thấp của điểm tổng kết. Danh sách các tỉnh thành phố trong quyền danh bạ điện thoại cùng được xếp theo thứ tự,... Vì sắp xếp được dùng rất phổ biến nên Excel cung cấp một số công cụ tiện ích Để thực hiện việc này.

Các bước để thực hiện việc sắp xếp dữ liệu nhanh trên một trường như sau:

• Bước 1: Lựa chọn cột dữ liệu chứa thông tin cần sắp xếp.
• Bước 2: Trên thanh Ribbon, tại thẻ Home, trong nhóm Editing, nháy chuột vào nút lệnh Sort&Filter. Trên menu sổ xuống chọn Sort A to Z (nếu muốn sắp xếp theo thứ tự tăng dần) hoặc Sort Z to A (nếu muốn sắp xốp theo thử tự giảm dần). Ở bước 2, nếu không muốn chọn tại thẻ Home, có thể mở thẻ Data, trong nhóm Sort&Filter và nháy chuột vào nút cũng có tính năng tương tự.
• Bước 3: Xuất hiện một hộp thoại thông báo đã tìm thấy dừ liệu được lựa chọn để sắp xếp và đưa ra cho người dùng hai sự lựa chọn: Expand the selection (mờ rộng sự sắp xếp ra cả bảng) và Continue with the current selection (chi sấp xếp tại cột lựa chọn) (hình 8.67). Thông thường Expand the selection sẽ được lựa chọn vì việc sắp xốp diền ra trên một trường cụ thể nhưng các thông tin trong bảng dừ liệu là liên quan đến nhau. Khi có sự thay đôi thứ tự trên một trường thì các trường khác cùng thay đổi theo. Ví dụ trên bảng điểm của một lóp gồm rất nhiều thông tin như số thứ tự, họ và tên học sinh, ngày tháng năm sinh, giới tính, điềm thành phần và điểm tổng kết. Khi sắp xép theo điểm tổng kết giảm dần thì yêu cầu là các thông tin khác cũng phải đi cùng sự sắp xép đó.

Hình 8.65. Lệnh Sort &Filter trên nhóm Editing của thẻ Home

Hình 8.66. Lệnh Sort &Filter trên Data

Hình 8.67. Hộp thoại Sort Warning

• Bước 4: Nháy chọn Sort... để thực hiện việc sắp xếp dữ liệu.

Sắp xếp nhanh ở trên chỉ thực hiện được trên một trường. Nhưng giá sử có hai thông tin trong trường đó giống nhau thì việc sắp xếp sẽ ưu tiên thế nào? Ví dụ, trong danh sách lớp thường được sắp xếp theo tên, nếu có hai học sinh trùng tên thì sẽ sắp xếp theo họ. Để thực hiện việc đó cần lựa chọn công cụ sắp xếp dữ liệu trên nhiều trường như sau:

• Bước 1: Lựa chọn cột dữ liệu chứa thông tin chính cần sắp xếp.
• Bước 2: Trộn thanh Ribbon, tại thẻ Home, trong nhóm Editing, nháy chuột vào nút lệnh Sort&Filter. Trên menu sổ xuông chọn Custom Sort... Ở bước 2 nếu không muốn chọn tại thẻ Home, có thể mở the Data, trong nhóm Sort&Eilter và nháy chuột vào nút Sort cũng có tính năng tương tự.
• Bước 3: Xuất hiện một hộp thoại thông báo đã tìm thấy dữ liệu được lựa chọn để sắp xếp và đưa ra cho người dùng hai sự lựa chọn: Expand the selection (mở rộng sự sắp xếp ra cả bảng) và Continue with the current selection (chỉ sắp xếp tại cột lựa chọn). Thông thường Expand the selection sẽ được lựa chọn.
• Bước 4: Xuất hiện hộp thoại Sort (như Hình 8.68) và thực hiện các thao tác sau: Chọn trường sấp xếp chính bằng cách nháy chuột vào mùi tên xuống trong ô Sort by tại vùng Column. Trong ô Sort On chọn Values để sắp xếp theo giá trị (đây là lựa chọn mặc định cua Excel). Trong ô Order chọn Smallest to Largest (với dữ liệu kiểu sô) hay A to z (với dữ liệu kiểu văn bản) để sắp xếp tăng dần hoặc Largest to Smallest (với dữ liệu kiểu số) hay z to A (với dữ liệu kiểu vân bàn) Để săp xếp giam dần. Nháy chuột vào nút Add Level để chọn trường tiếp theo được sắp xốp nếu có hai bàn ghi ở trường chính trùng nhau. Việc lựa chọn lại được lặp lại giống như các lựa chọn với trường sắp xếp chính. Và cứ như thế cho đến khi hết các trường cần sắp xếp.
• Bước 5: Nháy chọn OK đế hoàn thành việc sắp xếp và quan sát kết quả.

Hình 8.68. Hộp thoại Sort

Một người muốn mua một máy tính, khi cầm bảng báo giá với vô vàn thông tin họ sẽ xem xét những gì? Họ thích máy tính bàn hay máy xách tay? Họ có khoảng bao nhiêu tiền để mua máy tính? Họ thích nhãn hiệu của hãng nào?... Mỗi câu trả lời sẽ thu hẹp phạm vi tìm kiếm cùa khách hàng và cuối cùng họ sẽ có một bảng báo giá chứa các thông tin cần thiết đến việc mua máy tính.

Một cách tương tự, quàn lí khách sạn cần thống kê số lượng khách trong tuần hay trong tháng, một nhân viên bản hàng cần thống kê mặt hàng nào bản chạy nhất trong năm hay quý, một giáo viên cần thống kê danh sách học sinh giòi của lớp mình để làm thi đua khen thường ...

Để thực hiện các công việc đó, Excel cung cấp một tính năng gọi là Lọc dữ liệu.

Cách đơn giản nhất để lọc dữ liệu là sử dụng công cụ lọc tự động AutoFilter: trước hết lựa chọn bảng dữ liệu cần lọc. Sau đó trên thanh Ribbon chọn thẻ Data trong nhóm Sort&Filter chọn lệnh Filter. Lúc này tại tiêu đề các cột của bảng dữ liệu sẽ xuất hiện mũi tên sổ xuống.

Nháy chuột vào mũi tên đó ở mỗi cột sẽ xuất hiện một menu sổ xuống bao gồm các giá trị có trong cột đó

Hình 8.69. Lọc tự động với AutoFilter

Hình 8.70. Lọc tự động với AutoFilter (tiếp)

Việc lọc dữ liệu với một điều kiện sẽ được tiến hành theo các  bước sau:

• Bước 1: Bỏ lựa chọn Select All. Các lựa chọn từng bản ghi cụ thể trong cột cũng được tự động bỏ theo.
• Bước 2: Nháy chuột vào giá trị cần lọc, ví dụ, lọc ra danh sách học sinh nữ thì lựa chọn Nữ trong cột giới tính. Khi thực hiện thao tác này thì bảng dữ liệu sẽ thay đổi, chỉ hiển thị những bản ghi thỏa mãn điều kiện lọc.

Hình 8.71. Lọc tự động với AutoFilter (tiếp)

• Bước 3: Sao chép dữ liệu đã được lọc.

Việc lọc thông tin sẽ làm thay đôi bảng dữ liệu ban đầu, nên để giữ lại cả bảng ban đầu và bằng thông tin sau khi lọc cần có bước sao chép dữ liệu đã được lọc.

Sao chép dữ liệu đã lọc được tiến hành như sau:

• Lựa chọn vùng dữ liệu vừa lọc xong, trên thanh Ribbon trong the Home chọn Copy, hoặc nhấn tổ họp phím Ctrl + C.

Hình 8.72. Sao chép dữ liệu sau khi lọc

• Đặt con trỏ chuột tại vị trí muốn đưa bảng dữ liệu sau khi lọc ra, trên thanh Ribbon trong thẻ Home chọn Paste hoặc nhấn tổ hợp phím Ctrl + V. Nếu muốn đưa bảng dữ liệu mới này vào các ứng dụng khác của Microsoft như Microsoft Word hay Microsoft PowerPoint thì mở ứng dụng ra và nhấn tổ hợp phím Ctrl + V.

Hình 8.73. Sao chép dữ liệu sau khi lọc (tiếp)

• Bước 4: Bỏ lọc và trả lại trạng thái ban đầu của bằng dữ liệu: lựa chọn một trong các cách sau:
  • Nhân chuột vào mũi tên ở tên cột vừa lọc, chọn Clear Filters.
  • Nhân chuột vào mũi tên ở tên cột vừa lọc, chọn Clear All.
  • Trên thanh Ribbon, chọn thẻ Data, chọn nhóm Sort&Filter, chọn lệnh Filter.

Hình 8.74. Bỏ lọc và trả lại trạng thái ban đầu

Việc lọc dữ liệu với một điều kiện như trên đôi khi không thoả mãn hết yêu cầu. Gia sử. ớ ví dụ trên, giáo viên muốn lọc ra danh sách nhùng học sinh nữ của lớp đạt học lực Gioi. Như vậy, sau khi lọc được danh sách các học sinh giỏi cua lớp thì cần tiếp tục lọc ra những em có giới tính là Nữ. Để thực hiện việc lọc tự động với hai hoặc nhiều điều kiện hơn nữa thì các bước tiến hành vần tương tự như trên. Sau khi lọc lần một theo một cột thì tại danh sách mới tiếp tục lựa chọn việc lọc trên cột tiếp theo. Các bước sau đó cùng tương tự như phần Lọc dữ liệu tự động với một điều kiện.

Hình 8.75. Lọc tự động với hai điều kiện

Tìm kiếm các ô rỗng

Giả sử trong danh sách nộp điểm của giáo viên bị sót điểm của một hoặc một vài học sinh nào đó. Người kiểm tra điểm cần nhanh chóng tìm xem điểm của học sinh nào bị thiếu để yêu cầu giáo viên bô sung. Sử dụng tính năng tìm kiếm các ô rỗng của lọc tự động sẽ giúp giải quyết yêu cầu đó một cách nhanh chóng.

Các thao tác tiến hành Lọc tự động vẫn tương tự như trên. Tại phần chọn giá trị lọc, nháy chuột chọn Blank ở cuối danh sách và nháy chọn OK. Những bàn ghi nào bị thiếu sẽ được lọc ra.

Lọc theo giá trị số

Ngoài tính năng lọc ra các ban ghi có thông tin chính xác như điều kiện lọc thì chức năng lọc tự động còn có thể lọc ra các bản ghi có điêu kiện lọc nằm trong một khoang nào đó. Để lựa chọn chức năng này thay vì đánh dấu vào một giá trị cụ thể thì chọn lệnh Number Filters. Bảng dưới đây sẽ mô tả các khoảng lọc có thể lựa chọn:

Filters	Ý nghĩa
Equals...	Bằng
Does not equal	Không bằng
Greater than	Lớn hơn
Greater than or equal to	Lớn hơn hoặc bằng
Less than	Nhỏ hơn
Less than or equal to	Nhỏ hơn hoặc bằng
Between	Giữa
Top 10	10 giá trị đầu tiên
Above Average	Lớn hơn giá trị trung bình
Below Average	Nhỏ hơn giá trị trung bình
Custom Filter	Điều kiện lọc tự lập

Sau khi lựa chọn một loại điều kiện thì bảng Custom Auto Filter xuất hiện như hình 8.76, nhập giá trị và chọn OK đe thực hiện việc lọc.

Hình 8.76. Hộp thoại Custom AutoFliter

Lọc theo giá trị văn bản

Tương tự với tính năng lọc theo giá trị số, nếu cột được lựa chọn lọc chứa dữ liệu kiểu văn ban thì bằng Text Filters sẽ như sau:

Filters	Ý nghĩa
Equals...	Bằng
Does not equal	Không bằng
Begin Withs...	Bắt đầu bằng
End Withs...	Kết thúc bằng
Contains...	Chứa
Does not Contains...	Không chứa
Custom Filter	Điều kiện lọc tự lập

Lưu ý: Nêu điều kiện lọc có kí tự ? nghĩa là bất kì một kí tự nào cũng được chấp nhận. Ví dụ, cần lọc ra những học sinh có tên bắt đầu là H và chỉ gồm ba chữ cái thì điều kiện lọc sẽ là H??. Nêu điều kiện lọc có kí tự * nghĩa là một dãy kí tự nào đó được chấp nhận. Ví dụ, cần lọc ra những học sinh có tên bắt đầu là A thì điều kiện lọc sẽ là A*. 

Lọc nâng cao vói Advanced Filter

Khi tất cả các tính nâng lọc tự động trên vẫn chưa giải quyết được những yêu cầu lọc phức tạp, hãy nghĩ tới lọc nâng cao với Advanced Filter. Để thực hiện lọc nâng cao cần chđ ý tới điều kiện lọc. Điều kiện này phải được tạo thành một vùng điều kiện riêng. Quy tắc tạo vùng điều kiện này giống như vùng điều kiện của nhóm hàm cơ sở dữ liệu.

Các bước thực hiện lọc nâng cao như sau:

• Bước 1: Tạo vùng điều kiện lọc (nằm ngoài cơ sở dữ liệu là bằng tính ban đầu).
• Bước 2: Lựa chọn bằng dữ liệu cần lọc.
• Đước 3: Trên thanh Ribbon, chọn thẻ Data. Trong nhóm Sort&Filter, chọn Advanced.
• Bước 4: Trên hộp thoại hiện ra, chọn Copy to another location (Để bảng tính sau khi được lọc sẽ được đưa sang vùng mới không trùng với bảng tính cũ). Lựa chọn vùng điều kiện trong ô Criteria range.

Nháy chọn ô đầu tiên sẽ đặt kết quá lọc trong ô Copy to. Nháy chọn OK để hoàn tất (có thể chọn thêm unique records only đề loại bó các ban ghi trùng nhau).

Hình 8.77 minh hoạ các thao tác lọc nâng cao để lọc ra những người quá hạn trả tiền lãi (tiền lãi quá hạn > 0) và có tháng đến hạn là tháng 11 trong bảng Danh sách khách hàng vay tiền.

Hình 8.77. Các thao tác lọc nâng cao Advanced Filter