CHƯƠNG III CHƯƠNG TRÌNH XỬ LÝ BẢNG TÍNH MICROSOFT EXCEL
BÀI 5. QUẢN LÝ VÀ TỔ CHỨC DỮ LIỆU
I. SẮP XẾP VÀ QUẢN LÝ DANH SÁCH DỮ LIỆU
− Sử dụng AutoFilter để chỉ hiện ra (lọc ra) những dòng dữ liệu mà thoả mãn yêu cầu của bạn.
− Thêm dữ liệu nhanh bằng chức năng AutoComplete và Pick From List.
− Thiết kế một dãy tiêu chuẩn khi lọc bằng Advanced Filter.
− Sắp xếp dữ liệu (Sort) theo các chỉ tiêu cụ thể.
1. Khái niệm dữ liệu
Trong Microsoft Excel, một trong những việc mà bạn sẽ phải làm thường xuyên là sắp xếp và quản lý các danh sách dữ liệu. Trong MS Excel, cơ sở dữ liệu là danh sách dữ liệu có liên quan với nhau mà mỗi dòng chứa các thông tin của một đối tượng nào đó được gọi là các bản ghi và dòng đầu tiên của mỗi cột chứa tiêu đề (tên) cột hay còn gọi là các trường.
Một trường là một mục cụ thể của thông tin trong danh sách dữ liệu của bạn, chẳng hạn trường: Stt, Họ và tên, Chức vụ, Phòng ban... Một bản ghi chứa tất cả các thông tin về một đối tượng cụ thể trong danh sách dữ liệu của bạn.
Trong ví dụ, một bản ghi chứa các thông tin cá nhân của một người:
Stt Họ và tên Bằng cấp Chức vụ Phòng ban
2 Vũ Minh Nguyệt Thạc sĩ Trưởng phòng Kế toán
Tập hợp toàn bộ các dữ liệu được tổ chức thành các trường và các bản ghi gọi là danh sách hoặc là cơ sở dữ liệu (Nói chung trong Microsoft Excel hai thuật ngữ này là tương đương với nhau).
2. Thêm dữ liệu nhanh bằng chức năng AutoComplete và Pick From List 2.1. AutoComplete
Chức năng Autocomplete để tăng tốc độ đánh máy những mục, cụm từ lặp lại trong một danh sách. Bạn chỉ cần gõ một hoặc hai ký tự đầu tiên, chức năng Autocomplete sẽ hoàn thành nốt những mục, cụm từ sẵn có cho bạn.
Để tắt (bật) chức năng Autocomplete
− Chọn menu Tools/ Options…
− Trong cửa số Options chọn tab Edit,
− Tích hoặc bỏ tích trong hộp Enable Autocomplete For Cell Values.
2.2. Pick From List
Thay vì phải đánh Kế toán, Kinh Doanh hoặc Marketing trong trường Phòng ban, bạn có thể dùng chức năng Pick From List để điền nốt dữ liệu. Tuy nhiên, để dùng chức
Giáo trình TIN VĂN PHÒNG 160 năng này, các ô cần điền dữ liệu phải ngay dưới các ô đã có những dữ liệu trên. Để điền dữ liệu, làm thao tác
− Bấm phải chuột vào ô trống, chọn Pick From List từ menu,
− Chọn một mục dữ liệu trong bảng liệt kê.
3. Hiển thị (lọc) dữ liệu
Khi làm việc với cơ sở dữ liệu (CSDL), bạn cần có khả năng tìm kiếm nhanh thông tin.
Chẳng hạn với CSDL đã cho, bạn có thể có những yêu cầu sau:
− Hiện ra tất cả những người là Cán bộ.
− Tìm những người là Cán bộ, có thâm niên từ 2 năm trở xuống và đi làm nhiều hơn hoặc bằng 24 ngày/tháng.
− Cho biết những người hoặc là Trưởng phòng, hoặc là Phó phòng, đi làm ít hơn hoặc bằng 20 ngày/tháng.
− Tìm những cán bộ là Giảng viên chính (GVChinh) hoặc Giảng viên (GV) hoặc làm ở bộ môn Kinh tế hoặc ở bộ môn Toán tin hoặc có thâm niên từ 10 năm trở lên.
Để làm được việc này, bạn có thể dùng tính năng lọc dữ liệu (filter) của MS Excel. Tuy nhiên, vùng dữ liệu phải tuân theo điều kiện về CSDL (đã đặt ra trong mục 1.2 của phần Các hàm tính toán thống kê).
MS Excel có hỗ trợ 2 tính năng lọc dữ liệu:
− Autofilter: dùng với các yêu cầu lấy dữ liệu đơn giản. Các bản ghi không thỏa mãn điều kiện lọc được ẩn đi.
− Advance filter: dùng với các yêu cầu lấy dữ liệu phức tạp. Có thể lấy ra danh sách các bản ghi thỏa mãn điều kiện sang vị trí khác (ngoài bảng).
3.1. Autofilter a. Cách làm
− Di chuyển con trỏ soạn thảo vào danh sách,
− Chọn menu Data/ Filter/ AutoFilter, khi đó bên cạnh mỗi tiêu đề cột sẽ xuất hiện một nút mũi tên đặt lọc dữ liệu,
− Kích chuột vào nút mũi tên này sẽ xuất hiện một danh sách tiêu chí (điều kiện) để lọc dữ liệu. Các tiêu chí trong một trường hoặc cột được liệt kê theo thứ tự Alphabetical nếu dữ liệu có kiểu ký tự và theo thứ tự từ nhỏ đến lớn nếu dữ liệu là kiểu số. Ngoài ra trong danh sách luôn luôn có 5 lựa chọn sau:
All: hiện ra tất cả các bản ghi trong cột đó.
Top 10: hiện ra 10 bản ghi có giá trị cao nhất (Top) hoặc thấp nhất (Bottom) trong cột.
Blanks: hiện ra tất cả những bản ghi trống trong cột đó.
NonBlanks: hiện ra tất cả các bản ghi có chứa thông tin (khác trống) trong cột đó.
Giáo trình TIN VĂN PHÒNG 161 Custom: giúp tạo ra các tiêu chí phức hợp mà không có trong danh sách tiêu
chí.
Với lựa chọn Custom bạn có thể sử dụng để xây dựng biểu thức điều kiện.
b. Bỏ chế độ Autofilter hoặc bỏ lọc tại cột bất kỳ
− Tại các cột có đặt lọc dữ liệu thì nút mũi tên có màu xanh (blue). Để tắt đặt lọc, chọn nút mũi tên trên cột đó rồi chọn All.
− Để tắt chế độ AutoFilter, chọn menu Data/ Filter/ AutoFilter.
c. Ví dụ
Chỉ hiện ra những người hoặc là Trưởng phòng, hoặc là Phó phòng, đi làm ít hơn hoặc bằng 20 ngày/tháng.
Điều kiện lọc dữ liệu được đặt trên trường Chức vụ với giá trị Trưởng phòng hoặc Phó phòng; trên trường Ngày công có giá trị không vượt quá 20.
− Di chuyển vào vùng dữ liệu, chọn menu Data/ Filter/ AutoFilter.
− Kích chuột vào nút mũi tên trên cột Chức vụ, chọn Custom.
− Hộp thoại Custom AutoFilter hiện ra, trong hộp thứ 1 của dòng 1: chọn Equals, trong hộp thứ 2 của dòng 1: chọn TP từ danh sách hoặc gõ giá trị TP.
− Kích chọn nút Or ngay phía dưới hộp chọn của dòng 1. Trong hộp thứ 1 của dòng thứ 2: chọn Equals, trong hộp thứ 2 của dòng thứ 2: chọn PP.
− Chọn OK, khi đó chỉ các bản ghi có giá trị trên cột Chức vụ và TP hoặc PP hiển thị.
− Kích chuột vào nút mũi tên trên cột Ngày công, chọn Custom.
− Tương tự như làm với cột Chức vụ, chọn Is less than or equals to trong hộp thứ nhất của dòng 1: chọn is less than or equals to, trong hộp thứ 2 của dòng 1: gõ 20.
− Chọn OK kết thúc.
3.2. Advanced Filter
Ưu điểm của Advanced Filter là có thể nhìn các tiêu chuẩn một cách tổng quát, bảng kết quả có thể đưa ra một vị trí khác với bảng dữ liệu gốc. Thêm nữa, có thể thực hiện lọc được với các điều kiện phức tạp (có từ 3 điều kiện trở lên trên 1 trường) mà Autofilter không làm được.
a. Tạo dãy tiêu chuẩn (vùng điều kiện lọc - criteria range)
− Copy toàn bộ dòng nhãn (tiêu đề) của bảng dữ liệu vào một dòng trống bất kỳ ở cuối bảng dữ liệu.
− Với từng cột có điều kiện, gõ tiêu chuẩn vào các hàng ngay dưới dòng nhãn cột đó (xem cách xây dựng vùng điều kiện trong phần 2.2 của Các hàm tính toán thống kê).
b. Sử dụng Advanced Filter
− Tạo dãy tiêu chuẩn
− Di chuyển con trỏ vào bảng dữ liệu hoặc bôi đen bảng dữ liệu
Giáo trình TIN VĂN PHÒNG 162
− Chọn menu Data/ Filter/ Advanced Filter, xuất hiện cửa sổ Advanced Filter,
− Chọn cách hiển thị kết quả lọc trong hộp Action
Filter the list, in-place: kết quả lọc ngay trong bảng dữ liệu Copy to another location: kết quả lọc được ra một vị trí khác
− Để mặc định vùng dữ liệu trong hộp List Range,
− Chọn vùng điều kiện lọc (đã tạo ở bước 1),
− Nếu muốn loại bỏ các hàng giống nhau trong tập kết quả chọn Unique Record Only,
− Chọn OK để chấp nhận các thông số của Filter. MS Excel sẽ thực hiện lọc theo các yêu cầu trên.
c. Ví dụ
Hiện ra những người có bằng Trung cấp hoặc bằng Đại học mà thuộc phòng Marketing và đi làm ít hơn 18 ngày/tháng.
Dãy tiêu chuẩn:
Bằng cấp Phòng ban Ngày công
TC Marketing <18
ĐH Marketing <18
Sau đó, thực hiện lọc dữ liệu như trong phần trên.
4. Sắp xếp dữ liệu (Sort)
Để phục vụ yêu cầu thống kê, hoặc khảo sát dữ liệu, có thể sắp xếp dữ liệu trong bảng theo các tiêu chí khác nhau. Nếu dữ liệu là kiểu số thì sắp xếp theo giá trị tăng/ giảm dần. Nếu dữ liệu là kiểu ký tự thì sắp xếp theo thứ tự A-Z hoặc ngược lại.
− Nếu bảng dữ liệu thỏa mãn các yêu cầu của một cơ sở dữ liệu, thì không cần đánh dấu bảng dữ liệu đó mà chỉ cần di chuyển con trỏ soạn thảo vào 1 ô bất kỳ trong danh sách. MS Excel sẽ chọn toàn bộ vùng dữ liệu
− Nếu không, thực hiện đánh dấu bảng dữ liệu.
4.1. Sử dụng
− Chọn bảng theo cách trên,
− Chọn menu Data/Sort, cửa sổ Sort xuất hiện,
− Nếu bảng có tiêu đề cột và có chọn hộp Header row (Tiêu đề cột) thì có thể sắp xếp các dữ liệu thông qua tên trường. Nếu không (No header row), chọn sắp xếp theo tên cột do MS Excel quy định (Column1, Column2,…),
− Chọn trường (cột/ tiêu chí) cần sắp xếp trong hộp Sort by, chọn kiểu sắp xếp tăng dần (Ascending)/ giảm dần (Descending) ngay cạnh tiêu chí đó,
− Nếu muốn sắp xếp thêm theo tiêu chí nào đó, chọn trường trong các hộp Then by phía dưới (dùng khi các dữ liệu đã cùng giá trị trên trường Sort by, thì sẽ sắp xếp theo giá trị trong cột Then by),
Giáo trình TIN VĂN PHÒNG 163
− Chọn OK khi kết thúc.
4.2. Ví dụ
Sắp xếp dữ liệu theo Phòng ban và theo Lương cơ bản giảm dần.
− Chọn bảng dữ liệu,
− Chọn menu Data/ Sort,
− Trong hộp Sort by, chọn Phòng ban,
− Trong hộp Then by, chọn Lương cơ bản và chọn Descending.
− Chọn OK.
Chú ý: Phần Sort by được ưu tiên sắp xếp trước sau đó dữ liệu mới được sắp xếp theo Then by. Theo ví dụ trên, trước hết các dữ liệu được sắp xếp theo Phòng ban, trong mỗi Phòng ban dữ liệu được sắp xếp theo thứ tự Lương cơ bản giảm dần.