Các thao tác lọc, tìm kiếm, trích rút, sắp xếp các bản ghi trong Excel

TRƯỜNG ĐẠI HỌC THƯƠNG MẠI Bộ Môn : Tin Học Đại Cương Đề tài : Các thao tác lọc , tìm kiếm ,trích rút ,sắp xếp các bản ghi trong Excel Tin Học Đại Cương Khái niệm về cơ sở dữ liệu Sắp xếp dữ liệu Các thao tác lọc, rút trích dữ liệu Khái niệm về cơ sở dữ liệu: Cơ sở dữ liệu(CSDL) là tập hợp các thông tin được tổ chức và sắp xếp theo một cấu trúc nhất định trong một bảng tính gồm nhiều cột và hàng. Trên một cơ sở dữ liệu dạng bảng của Excel ta có thể: Lọc (Filter), xoá (Delete) rút trích (Extract) những dòng dữ liệu thoả mãn một tiêu chuẩn nào đó. Để thực hiện các thao tác này cần tạo ra những vùng sau: Khái niệm về cơ sở dữ liệu( Minh họa về cơ sở dữ liệu ) Khái niệm về cơ sở dữ liệu 1. Vùng dữ liệu (Database): Là vùng cơ sở dữ liệu gồm ít nhất 2 dòng (Row). Dòng đầu tiên chứa các tiêu đề cột (Field name), các dòng còn lại chứa dữ liệu gọi là mẩu tin (Record). 2. Vùng tiêu chuẩn (Criteria): Là vùng tiêu chuẩn chứa điều kiện để tìm kiếm, xoá, rút trích hay trích lọc. Vùng này gồm ít nhất 2 dòng. Dòng đầu chứa tiêu đề (Field name), các dòng còn lại chứa điều kiện. Khái niệm về cơ sở dữ liệu 3. Vùng rút trích (Extract): Là vùng trích dữ liệu chứa các mẩu tin của vùng dữ liệu (Database) thoả điều kiện của vùng tiêu chuẩn. Vùng rút trích (Extract) cũng có dòng đầu tiên chứa các tiêu đề muốn rút trích. Sắp xếp dữ liệu Hình : Hộp thoại xắp xếp dữ liệu Sắp xếp dữ liệu Đưa con trỏ ô vào vùng dữ liệu cần sắp xếp. Chọn thực đơn Data / Sort, hộp thoại Sort xuất hiện: Chọn cột cần sắp xếp trong hộp Sort by. Chọn Ascending để sắp xếp tăng dần. Chọn Descending để sắp xếp giảm dần. Nếu muốn sắp xếp cho 2 hay 3 cột cùng lúc, chọn chúng trong các hộp Then by còn lại. Sắp xếp dữ liệu Chọn Header row để xác nhận chỉ sắp xếp các dòng chứa thông tin (các bản ghi) trong vùng dữ liệu (không sắp xếp dòng tiêu đề). Chọn No header row để sắp xếp cả dòng tiêu đề. Nhấn OK. Các thao tác lọc, rút trích dữ liệu (Minh họa ví dụ lọc dữ liệu tự động ) Các thao tác lọc, rút trích dữ liệu Khi muốn tìm kiếm hay liệt kê các mẩu tin (Record) theo một yêu cầu nào đó. Trong trường hợp này ta dùng phương pháp lọc (Filter) hoặc phương pháp rút trích dữ liệu (Extract). Các thao tác lọc, rút trích dữ liệu1. Lọc tự động - Đưa con trỏ ô vào vùng cần lọc (hoặc chọn vùng cần lọc). - Nhấn chọn Data\Filter\Autofilter. Khi đó các nút mũi tên sẽ xuất hiện tại tiêu đề các cột trong vùng cơ sở dữ liệu. - Nhấn chuột vào nút mũi tên để chọn các giá trị của cột cần xác định điều kiện lọc. Khi đó các mẩu tin thoả điều kiện sẽ được hiển thị còn các mẩu tin không thoả sẽ bị dấu (hide) đi. Các thao tác lọc, rút trích dữ liệu1. Lọc tự động - Muốn hiển thị lại các mẩu tin, chọn Data\Filter\Show all. - Muốn bỏ chế độ lọc, chọn Data\Filter\Autofilter một lần nữa. * Trong danh sách còn có những lựa chọn khác: - All: Hiện trở lại tất cả các bản ghi. Các thao tác lọc, rút trích dữ liệu1. Lọc tự động Top 10: Xuất hiện hộp thoại Top 10 AutoFilter cho phép lọc ra một số bản ghi chứa giá trị cao (Top) hoặc thấp (Bottom). Hình : Hộp thoại thiết lập lọc dữ liệu theo các chỉ tiêu lựa chọn có sẵn Các thao tác lọc, rút trích dữ liệu1. Lọc tự động + Chọn Top hay Bottom trong hộp thứ nhất. + Thay đổi số bản ghi cần lấy trong hộp thứ hai. + Hộp thứ ba cho phép xác định số bản ghi cần lấy theo số đếm bình thường (Items) hay theo đơn vị phần trăm (Percent). Các thao tác lọc, rút trích dữ liệu1. Lọc tự động Custom: Xuất hiện hộp thoại Custom AutoFilter cho phép lọc theo các điều kiện phức hợp. Hình : Hộp thoại đặt lọc dữ liệu theo điều kiện Các thao tác lọc, rút trích dữ liệu1. Lọc tự động * Nếu có một điều kiện lọc thì chỉ cần sử dụng hai hộp danh sách bên trên như sau: + Chọn một toán tử so sánh trong hộp danh sách bên trái: - equals = - does not equal - is greater than > - is greater than or equal to >= - is less than < - is less than or equal to <= Các thao tác lọc, rút trích dữ liệu1. Lọc tự động - begins with bắt đầu bằng kí tự... - does not begin with không bắt đầu bằng kí tự... - ends with kết thúc bằng kí tự... - does not end with không kết thúc bằng kí tự... - contains có chứa kí tự... - does not contain không chứa kí tự... Các thao tác lọc, rút trích dữ liệu1. Lọc tự động + Nhập vào hộp bên phải giá trị cần so sánh. * Trường hợp có hai điều kiện lọc thì phải lặp lại các thao tác trên đối với 2 hộp danh sách còn lại (chọn toán tử so sánh trong hộp bên trái, nhập giá trị cần so sánh vào hộp bên phải). + Chọn And để kết hợp hai điều kiện trên theo tiêu chuẩn "và" (giống hàm And). + Chọn Or để kết hợp hai điều kiện trên theo tiêu chuẩn "hoặc" (giống hàm Or). + Nhấn OK. Các thao tác lọc, rút trích dữ liệu1. Lọc tự động * Ví dụ: Cho bảng danh sách các học sinh trong lớp. Yêu cầu 1: Hiện ra màn hình những học sinh tiên tiến (có điểm trung bình từ 7,5 đến 8,5). - Thực hiện vào thực đơn Data  Filter  AutoFilter để xuất hiện các nút mũi tên bên phải các tiêu đề cột. - Nhấn nút mũi tên tại cột "Điểm trung bình" và chọn Custom trong danh sách hiện ra. - Nhập vào hộp Custom AutoFilter các giá trị như sau rồi nhấn OK Các thao tác lọc, rút trích dữ liệu1. Lọc tự động Hình : Minh họa ví dụ thiết lập các điều kiện đặt lọc Các thao tác lọc, rút trích dữ liệu1. Lọc tự động Yêu cầu 2: Tìm những học sinh có họ Lê. - Nhấn nút mũi tên tại cột "Họ tên" và chọn Custom trong danh sách hiện ra. - Nhập vào hộp Custom AutoFilter các giá trị như sau rồi nhấn OK Các thao tác lọc, rút trích dữ liệu1. Lọc tự động Hình : Minh họa ví dụ về thiết lập điều kiện đặt lọc Các thao tác lọc, rút trích dữ liệu2. Rút trích dữ liệu * Trong thao tác này ta cần xác định vùng dữ liệu (Database) và tạo trước vùng tiêu chuẩn (Criteria) cùng vùng rút trích (Extract). - Đưa con trỏ ô vào ô bất kì trong vùng dữ liệu. - Thực hiện chọn thực đơn Data / Filter / Advanced Filter, xuất hiện hộp thoại Advanced Filter. - Nếu chọn Filter the list, in-place: Các mẩu tin đã trích lọc sẽ xuất hiện trong vùng dữ liệu này. Các thao tác lọc, rút trích dữ liệu2. Rút trích dữ liệu - Chọn Copy to another location: Đưa các mẩu tin đã trích lọc sang vùng khác. - Nhập địa chỉ của vùng dữ liệu cần trích lọc trong hộp List range. - Nhập địa chỉ của vùng điều kiện (tiêu chuẩn) trong hộp Criteria range. - Nhập địa chỉ ô đầu của vùng rút trích trong hộp Copy to Các thao tác lọc, rút trích dữ liệu2. Rút trích dữ liệu Hình : Hộp thoại thiết lập các thuộc tính trích rút dữ liệu Các thao tác lọc, rút trích dữ liệu2. Rút trích dữ liệu Hình b: Minh họa ví dụ về trích rút dữ liệu Các thao tác lọc, rút trích dữ liệu2. Rút trích dữ liệu - Nếu chọn Unique record only, dữ liệu lọc ra mà có những dòng trùng nhau sẽ chỉ hiện một dòng. - Nhấn OK để thực hiện. THE END