Bài giảng Tin học văn phòng - Bài 12: Quản trị dữ liệu

TIN HỌC VĂN PHÒNG Bài 12: QUẢN TRỊ DỮ LIỆU 1 Nội dung chính 1. Khái niệm cơ bản 2. Sắp xếp dữ liệu 3. Lọc dữ liệu 4. Các hàm cơ sở dữ liệu 5. Tổng kết theo nhóm 2 1. Khái niệm cơ bản I Cơ sở dữ liệu (CSDL) : tập hợp các thông tin được tổ chức dưới dạng bảng để dễ dàng liệt kê, tìm kiếm, lọc ra những thông tin thỏa mãn điều kiện nào đó I Vùng CSDL trong Excel : • là vùng chữ nhật của bảng tính gồm ít nhất 2 hàng • hàng đầu tiên chứa các tiêu đề (các trường - field) của dữ liệu, mỗi tiêu đề trên một cột • từ hàng thứ 2 trở đi chứa dữ liệu, mỗi hàng là một bản ghi (record) I Chú ý : • tên trường phải là dạng ký tự, không dùng công thức, địa chỉ ô • không nên có miền rỗng trong CSDL 3 2. Sắp xếp dữ liệu I Chọn vùng dữ liệu cần sắp xếp I Vào Data → Sort & Filter, chọn Sort • Sort by : chọn trường để sắp xếp • Order : chiều sắp xếp • Add Level : thêm các trường để sắp xếp • Delete Level : bỏ các trường ra khỏi danh sách sắp xếp 4 3. Lọc dữ liệu Để chuẩn bị cho việc lọc dữ liệu, cần xác định các yếu tố cơ bản sau : I Miền dữ liệu : vùng CSDL cần xử lý I Miền tiêu chuẩn (MTC) : chứa điều kiện để lọc, gồm ít nhất 2 hàng • hàng đầu chứa tiêu đề cho các tiêu chuẩn, tên tiêu đề phụ thuộc vào phương pháp thiết lập trực tiếp hay gián tiếp • các hàng tiếp theo chứa điều kiện I Miền đích : chứa các bản ghi thoả mãn tiêu chuẩn 5 Lọc dữ liệu Ví dụ : miền dữ liệu B2 : D7, miền tiêu chuẩn H2 : I3 6 Lọc dữ liệu I MTC trực tiếp • chứa các tiêu chuẩn để so sánh dữ liệu trong một trường với một giá trị nào đó • hàng đầu chứa tên tiêu chuẩn, phải là tên các trường • các hàng tiếp theo chứa các tiêu chuẩn, có thể thêm các toán tử so sánh như >, >=, <, <= trước các giá trị I Các tiêu chuẩn trên cùng hàng (điều kiện "và") được thực hiện đồng thời I Các tiêu chuẩn trên các hàng khác nhau (điều kiện "hoặc") được thực hiện không đồng thời 7 Lọc dữ liệu Ví dụ MTC trực tiếp I miền 1 : lọc ra những người có điểm toán trong khoảng từ 5 đến 8, cả hai tiêu đề đều ghi trường Toán, điều kiện ghi trên cùng một hàng I miền 2 : lọc ra những người có điểm toán dưới 5 hoặc trên 8, điều kiện ghi trên hai hàng I miền 3 : lọc ra những người có điểm toán trên 7 hoặc điểm tin trên 7, hai cột tiêu đề, điều kiện ghi trên hai hàng 8 Lọc dữ liệu I MTC gián tiếp • chứa các tiêu chuẩn để so sánh dữ liệu hoặc một phần dữ liệu trong một trường với một giá trị nào đó • hàng đầu chứa tên tiêu chuẩn, không được trùng tên trường nào • các hàng tiếp theo chứa các tiêu chuẩn dưới dạng công thức • công thức phải ghi địa chỉ của bản ghi đầu tiên và trả về giá trị True hoặc False I Ví dụ 135 Trªn hµng tiªu ®Ò cña tiªu chuÈn ph¶i cã 2 « ®Òu ghi tr-êng Trung b×nh, ngay phÝa d­íi ghi ®iÒu kiÖn (trªn cïng mét hµng) : Trung b×nh Trung b×nh >=5 <8 §Ó läc ra nh÷ng ng­êi XÕp lo¹i KÐm hoÆc Giái, XuÊt s¾c (tøc lµ ®iÓm Trung b×nh d­íi 5 hoÆc trªn 8 (ngoµi kho¶ng) lµm nh­ sau: Ngay phÝa d­íi ghi ®iÒu kiÖn (trªn hai hµng) : Trung b×nh <5 >8 §Ó läc ra danh s¸ch Nam hoÆc Tuæi trªn 21 : §iÒu kiÖn ghi trªn hai hµng : Tuæi Giíi tÝnh Nam >21 MiÒn tiªu chuÈn so s¸nh gi¸n tiÕp (TCSSGT) hay cßn gäi lµ tiªu chuÈn c«ng thøc: cho phÐp ®­a vµo c¸c tiªu chuÈn ®Ó so s¸nh d÷ liÖu hoÆc mét phÇn d÷ liÖu trong mét tr­êng víi mét gi¸ trÞ nµo ®ã. TCSSGT ®­îc t¹o ra theo nguyªn t¾c sau : • Hµng ®Çu ghi tiªu ®Ò cho c¸c tiªu chuÈn. Tiªu ®Ò nµy cã thÓ ®Æt bÊt kú nh­ng kh«ng ®­îc trïng víi tªn tr­êng nµo. • Tõ hµng thø hai trë ®i ghi c¸c tiªu chuÈn so s¸nh, mçi tiªu chuÈn lµ mét c«ng thøc. C«ng thøc nµy ph¶i chøa ®Þa chØ cña b¶n ghi ®Çu tiªn. KÕt qu¶ thùc hiÖn c«ng thøc nµy lµ mét gi¸ trÞ Logic : TRUE (§óng) hoÆc FALSE (Sai) N¨m sinh =YEAR(C3) < 1975 Sinh tr­íc 1975 Khi Ên ↵ , t¹i « tiªu chuÈn nµy sÏ xuÊt hiÖn FALSE (v× N¨m sinh cña Hïng lµ 1978) 136 Ngµy sinh ch½n =MOD(DATE(C3),2) =0 Sinh vµo ngµy ch½n Khi Ên ↵ , t¹i « tiªu chuÈn nµy sÏ xuÊt hiÖn TRUE (v× Ngµy sinh cña Hïng lµ 30) - MiÒn ®Ých (Copy to) : miÒn trèng trªn b¶ng tÝnh, dïng ®Ó chøa c¸c b¶n ghi ®¹t tiªu chuÈn. b - Läc tù ®éng (AutoFilter) - Chän miÒn d÷ liÖu ®Þnh läc (kÓ c¶ hµng tiªu ®Ò). - Data, Filter - Chän AutoFilter, Excel tù ®éng chÌn nh÷ng mòi tªn vµo bªn ph¶i cña c¸c tªn tr­êng. - Chän biÓu t­îng tam gi¸ c t¹i cét chøa d÷ liÖu dïng lµm tiªu chuÈn ®Ó läc (vÝ dô t¹i cét XÕp lo¹i). - Chän mét trong c¸c môc t¹i Menu : [All] : HiÖn toµn bé c¸c b¶n ghi [Blanks] : ChØ hiÖn c¸c b¶n ghi trèng [Nonblanks] : ChØ hiÖn c¸c b¶n ghi kh«ng trèng [Custom ...] : Dïng c¸c to¸n tö so s¸nh (sÏ ®­îc tr×nh bµy chi tiÕt trong phÇn tiÕp theo) PhÇn cßn l¹i lµ danh s¸ch gi¸ trÞ cña c¸c b¶n ghi trong CSDL t¹i cét ®ã. Khi cÇn läc c¸c b¶n ghi theo mét gi¸ trÞ cô thÓ nµo ®ã chØ cÇn chän gi¸ trÞ ®ã trong Menu (vÝ dô chän Trung b×nh). Dïng c¸c to¸n tö so s¸nh 9 Lọc tự động (Auto Filter) I Chọn miền dữ liệu cần lọc, kể cả tiêu đề I Vào Data → Sort & Filter, chọn Filter I Nhấp chuột vào biểu tượng tam giác ở góc cột tiêu đề để thêm điều kiện lọc 10 Lọc nâng cao (Advanced Filter) I Tạo miền tiêu chuẩn I Chọn miền dữ liệu cần lọc I Vào Data → Sort & Filter, chọn Advanced • List range : chọn miền dữ liệu • Criteria range : chọn miền tiêu chuẩn • Copy to : chọn miền đích nếu nhấn chọn Copy to another location • Filter the list, in-place : lọc và trả kết quả ngay tại chỗ 11 4. Các hàm cơ sở dữ liệu I Dùng để trả lại một giá trị từ CSDL theo một điều kiện nào đó. CSDL sau dùng để minh hoạ cho các ví dụ. 139 4. C¸c hµm C¬ së d÷ liÖu (CSDL) Dïng ®Ó tr¶ l¹i mét gi¸ trÞ tõ CSDL theo mét ®iÒu kiÖn nµo ®ã. CSDL sau ®©y dïng ®Ó minh ho¹ cho ¸c vÝ ô. DSUM (database, field, criteria): TÝnh tæng trªn mét cét (field) cña CSDL (database) tho¶ m·n ®iÒu kiÖn ghi trong miÒn tiªu chuÈn (criteria) VÝ dô: ®Ó tÝnh tæng L­¬ng cña N÷, t¹i mét « trèng nµo ®ã cña b¶ng tÝnh, sau khi nhËp c«ng thøc: = DSUM(A1:D10, 4 ,C12:C13) ta nhËn ®­îc gi¸ trÞ 280. Trong c«ng thøc nµy : 12 DSUM (database, field, criteria) I Tính tổng trên một cột (field) của các bản ghi trong CSDL (database) thoả mãn điều kiện viết trong miền tiêu chuẩn (criteria) I Ví dụ 1 : để tính tổng Lương của Nữ, tại một ô trống nào đó nhập công thức =DSUM(A1 :D10, 4, C12 :C13) hoặc =DSUM(A1 :D10, "Lương", C12 :C13) • giá trị 4 là số thứ tự của cột Lương trong CSDL I Khi tính tổng theo 1 điều kiện, có thể thay hàm DSUM bằng SUMIF. Khi tính từ 2 điều kiện trở lên (SUMIFS), hàm DSUM được thay bằng công thức mảng. I Ví dụ 2 : tính tổng Lương những người trên 32 tuổi 13 Các hàm cơ sở dữ liệu I DAVERAGE(database, field, criteria) • Tính giá trị trung bình trên một cột của các bản ghi trong CSDL thoả mãn điều kiện viết trong miền tiêu chuẩn • Ví dụ : tính tuổi trung bình của Nữ I DMAX/DMIN(database, field, criteria) • Tính giá trị lớn/nhỏ nhất trên một cột của các bản ghi trong CSDL thoả mãn điều kiện viết trong miền tiêu chuẩn • Ví dụ : tính lương cao/thấp nhất của những người 28 tuổi 14 Các hàm cơ sở dữ liệu I DCOUNT(database, field, criteria) • Đếm số ô chứa số trên một cột của các bản ghi trong CSDL thoả mãn điều kiện viết trong miền tiêu chuẩn • Ví dụ : để đếm số người là Nữ có Lương >= 50, dùng công thức =DCOUNT(A1 :D10, D1, C12 :D13) hoặc =DCOUNT(A1 :D10, B1, C12 :D13) • Tham số thứ 2 (field) tương ứng với một trường số bất kỳ, trong ví dụ này không dùng được trường Tên hay Giới tính I DCOUNTA(database, field, criteria) • Đếm số ô không rỗng trên một cột của các bản ghi trong CSDL thoả mãn điều kiện viết trong miền tiêu chuẩn 15 5. Tổng kết theo nhóm I Tạo các dòng tổng kết trong một CSDL. SubTotal sẽ chèn tại các vị trí cần thiết các tính toán thống kê theo yêu cầu của người sử dụng. 142 C¸c tuú chän kh¸c : • Replace Current SubTotal : Khi t¹o dßng tæng kÕt míi dßng nµy sÏ thay thÕ dßng tæng kÕt cò. Theo ngÇm ®Þnh c¸c dßng tæng kÕt sÏ nèi tiÕp nhau. • Page Break Between Group : ChÌn dÊu ng¾t trang t¹i mçi vÞ trÝ cã dßng SubTotal (tøc lµ ®­a mçi nhãm sang mét trang riªng biÖt). • Summary Below Data : §Æt dßng tæng kÕt ë cuèi mçi nhãm. NÕu bá dÊu tÝch dßng nµy sÏ ®­îc ®­a lªn tr­íc mçi nhãm. • Remove All : Huû bá mäi SubTotal ®· thùc hiÖn. 3/ Chän Ok ®Ó b¾t ®Çu t¹o. 16 Các bước thực hiện I Sắp xếp dữ liệu theo trường cần tạo SubTotal I Vào Data → Outline, chọn Subtotal • At each change in : chọn trường mà theo trường này, tại mỗi vị trí thay đổi Excel sẽ chèn vào một dòng Tổng kết • Use function : chọn hàm để tính toán tổng kết dữ liệu • Add subtotal to : chọn các trường cần tính toán 17 Ví dụ Cho bảng dữ liệu dưới đây, hãy làm các việc sau. I Sắp xếp theo trường Trung bình, thứ tự từ cao xuống thấp. I Dùng Subtotal để phân nhóm theo Xếp loại, cho ra điểm trung bình của các nhóm trên các trường Toán, Tin, Trung bình. I Tương tự, thực hiện phân nhóm theo Giới tính. 139 4. C¸c hµm C¬ së d÷ liÖu (CSDL) Dïng ®Ó tr¶ l¹i mét gi¸ trÞ tõ CSDL theo mét ®iÒu kiÖn nµo ®ã. CSDL sau ®©y dïng ®Ó minh ho¹ cho c¸c vÝ dô. DSUM (database, field, criteria): TÝnh tæng trªn mét cét (field) cña CSDL (database) tho¶ m·n ®iÒu kiÖn ghi trong miÒn tiªu chuÈn (criteria) VÝ dô: ®Ó tÝnh tæng L­¬ng cña N÷, t¹i mét « trèng nµo ®ã cña b¶ng tÝnh, sau khi nhËp c«ng thøc: = DSUM(A1:D10, 4 ,C12:C13) ta nhËn ®­îc gi¸ trÞ 280. Trong c«ng thøc nµy : 18