Bài giảng môn Tin học văn phòng - Bài 4: Cơ sở dữ liệu trong execl - Thiều Quang Trung

BÀI 4 
CƠ SỞ DỮ LIỆU TRONG EXCEL 
GV: Th.S. Thiều Quang Trung 
Bộ môn Khoa học cơ bản 
Trường Cao đẳng Kinh tế đối ngoại 
• Khái niệm cơ sở dữ liệu 1 
• Sắp xếp, lọc dữ liệu 2 
• Các hàm CSDL 3 
• Tổng hợp số liệu 4 
Nội dung 
2 GV. Thiều Quang Trung 
Khái niệm Cơ sở dữ liệu trong Excel 
 CSDL trong Excel được tổ chức dưới dạng 
bảng gồm có các dòng và các cột. 
 Các cột gồm các ô chứa dữ liệu cùng kiểu 
gọi là các trường (Field). 
 Dòng đầu của bảng chứa các tiêu đề cột gọi 
là tên trường (Field name). Dòng thứ hai trở 
đi chứa thông tin của các trường gọi là bản 
ghi (Record). 
3 GV. Thiều Quang Trung 
Khái niệm CSDL trong Excel 
 Để Excel nhận biết CSDL một cách dễ dàng 
thì vùng CSDL phải tách biệt hẳn với các 
phần khác của bảng tính bởi các dòng trắng 
và cột trắng. 
 Giữa dòng các tên trường và bản ghi đầu 
tiên không được có dòng trắng. 
4 GV. Thiều Quang Trung 
STT Họ và tên Lớp Điểm trung bình 
1 Đinh Thu Hương TVP1 8.5 
2 Lê Quốc Trung TVP1 9.0 
3 Lý Thái Hùng TVP2 7.5 
4 Trần Quang Dũng TUD 8.0 
5 
Record (Bản ghi) 
Field name (Tên trường) 
Khái niệm CSDL trong Excel 
GV. Thiều Quang Trung 
Sắp xếp nhanh: 
 Click chuột vào ô bất kỳ trong trường muốn sắp 
xếp. 
 Click vào nút Sort Ascending để sắp xếp tăng 
dần. 
 Click vào nút Sort Descending để sắp xếp giảm 
dần. 
6 
Sắp xếp CSDL 
GV. Thiều Quang Trung 
Sắp xếp dữ liệu theo nội dung của nhiều cột: 
 Click chuột vào ô bất kỳ trong bảng CSDL hoặc bôi 
đen các dòng cần sắp xếp. 
 Thực hiện lệnh Data/Sort..., hộp thoại sau xuất hiện: 
7 
Sắp xếp CSDL 
GV. Thiều Quang Trung 
 Sort by: Click chọn tên trường cần sắp xếp. 
 Sort on: Click chọn giá trị của trường cần sắp 
xếp. 
 Order: Thứ tự ưu tiên của các trường. 
 Nhấn để bắt đầu sắp xếp. 
8 
Sắp xếp CSDL 
GV. Thiều Quang Trung 
9 
Lọc tự động 
 Đưa chuột trỏ đến một ô bất kỳ trong vùng CSDL 
hoặc đánh dấu vùng CSDL dữ liệu định lọc. 
 Thực hiện lệnh Data/Filter/Auto Filter, Excel sẽ tự 
động chèn những nút mũi tên vào bên phải của 
các tên trường trong CSDL. 
Lọc dữ liệu 
GV. Thiều Quang Trung 
10 
Lọc nâng cao 
 Lọc nâng cao là phương pháp dùng để lọc ra các bản 
ghi với các điều kiện lọc phức tạp hơn. Để sử dụng 
lọc nâng cao bắt buộc phải dùng vùng tiêu chuẩn để 
lọc dữ liệu. 
 Các bước tiến hành : 
- Tạo vùng tiêu chuẩn. 
- Đánh dấu vùng CSDL 
- Thực hiện lệnh 
 Data/Advanced Filter. 
Lọc dữ liệu 
GV. Thiều Quang Trung 
Lọc nâng cao 
 Mục Action bao gồm : 
− Filter the list, in-place: Click chọn nếu muốn kết quả lọc 
danh sách hiển thị tại chính vị trí của CSDL. 
− Copy to another location: Click chọn nếu muốn kết quả 
lọc hiển thị ở vùng khác của bảng tính. 
 List range: Địa chỉ vùng chứa CSDL. 
 Criteria Range: Địa chỉ vùng tiêu chuẩn. 
 Copy to: Địa chỉ vùng chứa bảng dữ liệu kết quả lọc. 
 Ta có thể tự gõ địa chỉ vào các mục trên hoặc đưa chuột 
trỏ vào ô cần lấy địa chỉ rồi dùng chuột kéo rê vùng địa 
chỉ cần điền ngoài bảng tính. 
 Unique Records Only: Click chọn nếu muốn chỉ hiện 
một bản ghi trong số các bản ghi trùng nhau. 
GV. Thiều Quang Trung 11 
Các yếu tố cơ bản để lọc dữ liệu trên bảng 
tính 
 Vùng dữ liệu: Chứa toàn bộ dữ liệu cần xử lý kể cả 
dòng tiêu đề. 
 Vùng tiêu chuẩn: Chứa các tiêu chuẩn (là các điều 
kiện mà các bản ghi phải thoả mãn). 
12 
Lọc dữ liệu nâng cao 
GV. Thiều Quang Trung 
 Vùng tiêu chuẩn gồm tối thiểu 2 dòng. 
⁻ Dòng thứ nhất chứa tiêu đề của vùng tiêu 
chuẩn: là tên trường hoặc là tên bất kỳ phụ 
thuộc vào phương pháp thiết lập tiêu chuẩn là 
gián tiếp hay trực tiếp. 
⁻ Dòng thứ hai trở đi: là tiêu chuẩn của CSDL. 
13 
Lọc dữ liệu nâng cao 
GV. Thiều Quang Trung 
Vùng tiêu chuẩn so sánh trực tiếp: 
Được tạo theo nguyên tắc: 
 Phải có ít nhất 2 dòng, dòng đầu để ghi tên trường 
của CSDL cần làm điều kiện lọc (nên sao chép tên 
trường từ vùng CSDL). 
 Dòng thứ hai trở đi để ghi các tiêu chuẩn so sánh là 
các điều kiện lọc. Cách ghi: gõ giá trị cần so sánh, 
nếu so sánh khác với toán tử = thì trước giá trị phải 
có các toán tử so sánh như: >, >=, <, <=. 
14 
Lọc dữ liệu nâng cao 
GV. Thiều Quang Trung 
Lọc dữ liệu nâng cao 
 Các tiêu chuẩn trên cùng dòng được xét 
đồng thời (tương ứng hàm AND các điều 
kiện). 
 Các tiêu chuẩn trên các dòng khác nhau 
được xét không đồng thời (tương ứng hàm 
OR các điều kiện). 
15 
15 
GV. Thiều Quang Trung 
Ví dụ về lọc CSDL 
A B C D E F G H I J 
1 Mã HS Họ Tên Giới tính Ngày sinh Toán Lý Hóa Học bổng Lớp 
2 00147 Tran Thi Mai Nữ 28/12/1992 8 4 9.5 200000 K1 
3 00153 Tran Van Tri Nam 25/12/1994 10 7 9 300000 K1 
4 00159 Tran Thi Mai Anh Nữ 18/12/1993 4.5 7.5 5 250000 K1 
5 00171 Tran Van Tam Nam 08/11/1993 4.5 4.5 5 400000 K1 
6 00177 Pham Thi Trinh Nữ 15/11/1994 7 8.5 5 200000 K2 
7 00183 Nguyen Thi Tuyet Nữ 15/10/1994 8.5 4 4 150000 K2 
8 00189 Vu Tuyet Lan Nữ 20/10/1994 9.5 4 10 180000 K2 
9 00209 Nguyen Thuy Hue Nữ 15/09/1995 4.5 1.5 4 200000 K3 
10 00582 Nguyen Duc Nam Nam 20/09/1994 5 6 5.5 300000 K3 
11 00588 Bui Bach Hue Nữ 09/09/1995 8.5 8 6 250000 K3 
12 00594 Pham Thanh Mai Nữ 20/05/1996 6 9.5 5.5 400000 K3 
GV. Thiều Quang Trung 16 
Lọc điều kiện trực tiếp 
• Lọc theo giới tính là nữ, điểm toán >=5 ? 
• Lọc theo giới tính là nam hoặc điểm toán 
>=8 ? 
GV. Thiều Quang Trung 17 
Giới tính Toán 
Nữ >=5 
Giới tính Toán 
Nam 
>=8 
Vùng tiêu chuẩn so sánh gián tiếp: 
Được tạo theo nguyên tắc như sau: 
 Có ít nhất 2 dòng, dòng đầu ghi tiêu đề của các tiêu 
chuẩn có thể đặt tên bất kỳ nhưng không nên trùng 
với các tên trường đã tồn tại trong CSDL. 
 Dòng thứ 2 trở đi ghi tiêu chuẩn lọc bằng công thức 
kiểm tra giá trị logic ứng với mẫu tin đầu tiên. Kết 
quả là ở ô vừa nhập sẽ xuất hiện TRUE hoặc FALSE 
tuỳ theo giá trị của mẫu tin đầu tiên. 
18 
Lọc dữ liệu nâng cao 
GV. Thiều Quang Trung 
Lọc điều kiện gián tiếp 
• Lọc theo giới tính là nữ, điểm toán >=5 ? 
• Lọc theo giới tính là nam hoặc điểm toán 
>=8 ? 
GV. Thiều Quang Trung 19 
Điều kiện lọc 
=AND(D2=“Nữ",F2>=5) 
Điều kiện lọc 
=OR(D2=“Nam",F2>=8) 
Các hàm cơ sở dữ liệu Excel 
• DSUM 
• DAVERAGE 
• DCOUNT 
• DMAX 
• DMIN 
• DGET 
GV. Thiều Quang Trung 20 
Hàm DSUM 
• Cú pháp: DSUM(database, field, criteria). 
• Chức năng: Tính tổng giá trị của trường “field” trong 
bảng CSDL (database) thoả mãn điều kiện trong vùng 
tiêu chuẩn (criteria). 
• Ví dụ: Tính tổng gía trị học bổng của lớp K3 dành cho 
học sinh nữ ? 
 =DSUM(A1:J12,"Học bổng",L1:M2) 
 Với L1:M2 là vùng tiêu chuẩn: 
21 GV. Thiều Quang Trung 
Lớp Giới tính 
K3 Nữ 
• Cú pháp: DAVERAGE(database, field, criteria). 
• Chức năng: Tính trung bình cộng các số trong trường 
“field” trong bảng CSDL thoả mãn điều kiện trong 
vùng tiêu chuẩn. 
22 
Hàm DAVERAGE 
GV. Thiều Quang Trung 
• Cú pháp: DCOUNT(database, field, criteria). 
• Chức năng: Đếm số lượng các bản ghi chứa giá trị số 
trên trường “field” trong bảng CSDL thoả mãn điều 
kiện trong vùng tiêu chuẩn. Có thể bỏ qua tham số 
“field”. 
• Ví dụ: Đếm số học sinh sinh năm 1994 
 =DCOUNT(A1:J12,,L7:M8) 
Với L7:M8 là vùng tiêu chuẩn gián tiếp chứa công thức: 
=YEAR(E2)=1994 
E2 là ô chứa năm sinh của mẫu tin đầu tiên trong CSDL 
23 
Hàm DCOUNT 
GV. Thiều Quang Trung 
• Cú pháp: DMAX(database, field, criteria). 
• Chức năng: Hàm trả về giá trị lớn nhất của trường 
“field” trong bảng CSDL thoả mãn điều kiện trong 
vùng tiêu chuẩn. 
• Ví dụ: Tìm ngày sinh nhỏ nhất trong các nữ sinh ? 
 =DMAX(A1:J12, “Ngày sinh",M1:M2) 
Với M1:M2 là vùng tiêu chuẩn trực tiếp chứa Giới tính 
"Nữ" 
24 
Hàm DMAX 
GV. Thiều Quang Trung 
• Cú pháp: DMIN(database, field, criteria). 
• Chức năng: Hàm trả về giá trị nhỏ nhất của trường 
“field” trong bảng CSDL thoả mãn điều kiện trong 
vùng tiêu chuẩn. 
• Ví dụ: Tìm ngày sinh lớn nhất trong các nữ sinh ? 
 =DMIN(A1:J12, “Ngày sinh",M1:M2) 
Với M1:M2 là vùng tiêu chuẩn trực tiếp chứa Giới tính 
"Nữ" 
25 
Hàm DMIN 
GV. Thiều Quang Trung 
• Cú pháp: DGET(database, field, criteria). 
• Chức năng: Hàm trả về giá trị tìm được ở trường 
“field” trong bảng CSDL thoả mãn điều kiện trong 
vùng tiêu chuẩn. 
• Ví dụ: Tìm tên học sinh có ngày sinh nhỏ nhất? 
 =DGET(A1:J12, "Tên",M10:M11) 
Với M10:M11 là vùng tiêu chuẩn gián tiếp chứa công 
thức =MAX(E2:E12), trong đó E2:E12 là dữ liệu trường 
"Ngày sinh" 
26 
Hàm DGET 
GV. Thiều Quang Trung 
• Subtotal là chức năng cho phép người sử 
dụng tổng hợp số liệu theo nhóm. 
• Ví dụ: nhóm danh sách theo giới tính và đếm 
số lượng mẫu tin cho từng nhóm. 
27 
Tổng hợp số liệu theo nhóm 
GV. Thiều Quang Trung 
Tổng hợp số liệu theo nhóm 
GV. Thiều Quang Trung 28 
• Các bước thực hiện: 
– Sắp xếp trường muốn 
nhóm lại theo chiều tăng 
(hoặc giảm). 
Ví dụ sắp xếp lại trường 
Giới tính. 
– Thực hiện lệnh 
Data/Subtotal, 
hộp hội thoại Subtotal 
xuất hiện như sau: 
29 
Tổng hợp số liệu theo nhóm 
GV. Thiều Quang Trung 
• At Each Change in: Click vào nút mũi tên phía bên phải để 
chọn trường cần tạo nhóm tổng hợp. 
• Use Function: Chọn hàm cần tính toán/thống kê. 
• Add Subtotal to: Chọn các trường cần tính toán/thống kê. 
• Click chọn vào Replace current subtotals để thay thế 
bảng tổng hợp đã có trước đó. 
• Click chọn vào Page break between groups nếu muốn 
mỗi nhóm được tính toán/ thống kê nằm trên mỗi trang. 
• Click chọn vào Summary below data để đưa dòng tính 
toán/thống kê vào phía dưới các bản ghi. 
30 
Tổng hợp số liệu theo nhóm 
GV. Thiều Quang Trung 
• Nếu muốn huỷ tính năng Subtotal thì thực hiện 
Data/ Subtotal,click vào nút Remove All. 
31 
Tổng hợp số liệu theo nhóm 
GV. Thiều Quang Trung 
GV. Thiều Quang Trung 32