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