Các Hàm Cơ Bản Trong Cơ Sở Dữ Liệu và Cách Sử Dụng | Môn Tin học ứng dụng - Đại học Kinh tế và Quản trị kinh doanh, Đại học Thái Nguyên

lOMoAR cPSD| 61152560
2.5. Các hàm trên Cơ sở dữ liệu
 Phần 1 chúng ta đã làm quen với các hàm thông dụng của Excel, tuy nhiên khi ta
muốn tính toán (đếm, tính tổng, thống kê ...) căn cứ vào 1 vài điều kiện nào đó thì không
thể thực hiện được, muốn giải quyết được ta phải sử dụng các hàm trên CSDL.
Các hàm trên Cơ sở dữ liệu Tên hàm Các đối Giải thích Công dụng số DSUM (các đối Database Vùng CSDL
Tính tổng trên một cột (field) số) của CSDL (database) thoả mãn điều kiện ghi trong Field
Trường (cột) cần tính vùng điều kiện (criteria). tổng
Lưu ý với thông số field ta
điền số thứ tự của cột (tính từ Criteria
Vùng chứa các điều trái sang) chứ không điền tên kiện để tính tổng trường.
DAVERAGE(các Database Vùng CSDL
Tính giá trị trung bình cộng đối số)
trên một cột (field) của Field
Trường (cột) cần tính CSDL (database) thoả mãn trung bình
điều kiện ghi trong miền Criteria
Vùng chứa các điều tiêu chuẩn (criteria) kiện để tính TB DMAX (các đối Database Vùng CSDL
Tính giá trị lớn nhất trên một số) cột (field) của CSDL Field
Trường (cột) cần tìm (database) thoả mãn điều giá trị lớn nhất kiện ghi trong miền tiêu chuẩn (criteria) lOMoAR cPSD| 61152560 Criteria Vùng chứa các điều kiện để tìm
DMIN (các đối số) Database Vùng CSDL
Tính giá trị nhỏ nhất trên Field
Trường (cột) cần tìm một cột (field) của CSDL giá trị nhỏ nhất
(database) thoả mãn điều kiện ghi trong miền tiêu Criteria
Vùng chứa các điều chuẩn (criteria) kiện để tìm
DCOUNT (các đối Database Vùng CSDL
Đếm số bản ghi của CSDL số)
(database) trên cột số (field) Field
Trường (cột) cần đếm thoả mãn điều kiện ghi trong
miền tiêu chuẩn (criteria) Criteria Vùng chứa các điều kiện để đếm DCOUNTA (các Database Vùng CSDL
Đếm số ô không rỗng của cột đối số)
bất kỳ (field) thoả mãn điều Field
Trường (cột) cần đếm kiện ghi trong miền tiêu chuẩn (criteria). Criteria Vùng chứa các điều kiện để đếm lOMoAR cPSD| 61152560
Bảng 2.4. Các hàm trên Cơ sở Dữ liệu
Hình 2.7: Sử dụng các hàm trên CSDL 
Ví dụ: Trong tháng 5 năm 2007 công ty CANHCAM có bảng lương như hình
trên. Người quản lý muốn biết các số liệu sau: Tổng lương của nữ giới có tuổi >30,
Lương trung bình nam giới có tuổi <35, Lương thấp nhất của nữ giới tuổi dưới 30, Lương
cao nhất của nam giới trên 35 tuổi, Số nhân viên nữ có lương > 2 triệu, Số nhân viên
nam có lương < 1 triệu. Để có được các thông tin đó, ta giải quyết như sau: 
Tạo ra 6 vùng điều kiện như hình dưới đây, mỗi 1 vùng để phục vụ cho 1 yêu
cầu cụ thể (lưu ý: không nhất thiết ta phải tạo cả 6 vùng cùng 1 lúc, ở đây tạo cả 6 vùng
cùng 1 lúc chủ yếu để cho việc giải thích được ngắn gọn) lOMoAR cPSD| 61152560
Hình 2.8: Điền dữ liệu vào các vùng điều kiện Vùn g Vị trí Giải thích
Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 1 Từ ô A22  E23
1, tính Tổng lương của nữ giới có tuổi >30
Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 2 Từ ô A25  E26
2, tính Lương trung bình nam giới có tuổi <35
Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 3 Từ ô A28  E29
3, tính Lương thấp nhất của nữ giới tuổi dưới 30
Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 4 Từ ô A31  E32
4, tính Lương cao nhất của nam giới trên 35 tuổi lOMoAR cPSD| 61152560
Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 5 Từ ô A34  E35
5, tính Số nhân viên nữ có lương > 2 triệu
Đây chính là vùng Criteria (vùng điều kiện) của yêu cầu 6 Từ ô A37  E38
6, tính Số nhân viên nam có lương < 1 triệu
Bẩng 2.5. Các bước thực hiện vùng điều kiện trên CSDL 
Tổng lương của nữ giới có tuổi >30, ta thực hiện theo các bước sau: Các bước Thao tác Giải thích 1
Đưa con trỏ chuột về ô E14
Chuẩn bị nhập công thức vào ô E14
Ta nhập hàm DSUM vào ô E14 với các thông số như trên. Trong đó: 2 =DSUM(A2:E13;5;A22:E23) A2:E13 là vùng CSDL.
5: là số thứ tự của cột cần tính tổng.
A22:E23 là vùng điều kiện lọc 3 Enter
Kết thúc việc nhập hàm
Bảng 2.6. Các bước thực hiện hàm Dsum 
Lương trung bình nam giới có tuổi <35, ta thực hiện theo các bước sau: Các bướ Thao tác Giải thích c 1
Đưa con trỏ chuột về ô E15
Chuẩn bị nhập công thức vào ô E15
Ta nhập hàm DAVERAGE vào ô E15 với
các thông số như trên. Trong đó: 2
=DAVERAGE(A2:E13;5;A25:E26) A2:E13 là vùng CSDL.
5: là số thứ tự của cột cần tính trung bình.
A25:E26 là vùng điều kiện lọc lOMoAR cPSD| 61152560 3 Enter
Kết thúc việc nhập hàm
Bảng 2.7. Các bước thực hiện hàm Daverage 
Lương thấp nhất của nữ giới tuổi dưới 30, ta thực hiện theo các bước sau: Các bước Thao tác Giải thích
Bảng 2.8. Các bước thực hiện hàm Dmin 
Lương cao nhất của nam giới trên 35 tuổi, ta thực hiện theo các bước sau: Các bước Thao tác Giải thích 1
Đưa con trỏ chuột về ô E17
Chuẩn bị nhập công thức vào ô E17
Ta nhập hàm DMAX vào ô E17 với các
thông số như trên. Trong đó: A2:E13 là vùng CSDL. 2 =DMAX(A2:E13;5;A31:E32)
5: là số thứ tự của cột cần tìm giá trị lớn nhất.
A31:E32 là vùng điều kiện lọc 3 Enter
Kết thúc việc nhập hàm
Bảng 2.9. Các bước thực hiện hàm Dmax 
Số nhân viên nữ có lương > 2 triệu, ta thực hiện theo các bước sau: Các bướ Thao tác Giải thích c 1
Đưa con trỏ chuột về ô E18
Chuẩn bị nhập công thức vào ô E18 lOMoAR cPSD| 61152560
Ta nhập hàm DCOUNT vào ô E18 với các
thông số như trên. Trong đó: 2
=DCOUNT(A2:E13;5;A34:E35) A2:E13 là vùng CSDL.
5: là số thứ tự của cột cần đếm.
A34:E35 là vùng điều kiện lọc 3 Enter
Kết thúc việc nhập hàm
Bảng 2.10. Các bước thực hiện hàm Dcount 
Số nhân viên nam có lương < 1 triệu, ta thực hiện theo các bước sau: Các Thao tác Giải thích bướ c 1
Đưa con trỏ chuột về ô E19
Chuẩn bị nhập công thức vào ô E19 2
=DCOUNTA(A2:E13;5;A37:E38) Ta nhập hàm DCOUNTA vào ô E19 với các
thông số như trên. Trong đó: A2:E13 là vùng CSDL.
5: là số thứ tự của cột cần đếm
A37:E38 là vùng điều kiện lọc 3 Enter
Kết thúc việc nhập hàm
Bảng 2.11. Các bước thực hiện hàm Dcounta
2.6. Đặt lọc dữ liệu:
2.6.1. Các thông số cơ bản để thực hiện việc lọc dữ liệu: 
Đối với những CSDL lớn, để có được thông tin dưới dạng rút gọn ta phải
thực hiện thao tác lọc trên CSDL đó, việc lọc dữ liệu đơn giản là việc ta muốn các dữ
liệu trên bảng tính chỉ hiện những thông tin cần thiết đối với chúng ta, các thông tin dư
thừa khác (thông tin không cần thiết) có thể ẩn đi. lOMoAR cPSD| 61152560 
Để thực hiện lọc 1 vùng CSDL, ta phải xác định các thông số cơ bản sau: Thông số Giải thích
Miền dữ liệu: Nơi chứa toàn bộ dữ liệu cần lọc, kể cả hàng tiêu Database đề.
Vùng chứa điều kiện lọc (vùng tiêu chuẩn): Là 1 vùng bất kỳ trên
bảng tính ngoài vùng CSDL, chứa các tiêu chuẩn (điều kiện lọc
mà các bản ghi phải thải mãn). Miền tiêu chuẩn gồm tối thiểu 2
hàng, hàng đầu chứa tiêu đề của miền tiêu chuẩn. Các tiêu đề này Criteria
hoặc 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 (trực tiếp hay gián tiếp). Từ hàng thứ hai trở
đi là tiêu chuẩn của CSDL.
Bảng 2.12. Các thông số của lọc 1 vùng CSDL 
Ở đây, ta sẽ phân biệt thế nào là 1 vùng tiêu chuẩn trực tiếp thế nào là 1 vùng tiêu chuẩn gián tiếp:
Vùng tiêu chuẩn trực tiếp
Hình thái thể hiện
Các nguyên tắc tạo lập
Hàng đầu ghi tiêu đề cho các tiêu chuẩn, lấy tên trường làm tiêu đề
Hàng thứ hai trở đi để ghi các điều kiện lọc, trước các
giá trị đó có thể thêm các toán tử so sánh như >, >=,<,
Vùng tiêu chuẩn trực tiếp là
vùng mà ta có thể nhập trực <=
tiếp các điều kiện để lọc mà
không cần phải thông qua Các tiêu chuẩn trên cùng 1 hàng được thực hiện đồng phép toán xử lý nào.
thời (tương đương như phép toán và AND)
Các tiêu chuẩn trên các hàng khác nhau được thực
hiện không đồng thời (tương đương phép toán hoặc OR)
Bảng 2.13. Các nguyên tắc tạo lập vùng tiêu chuẩn trực tiếp lOMoAR cPSD| 61152560
Vùng tiêu chuẩn gián tiếp
Hình thái thể hiện
Các nguyên tắc tạo lập
Vùng tiêu chuẩn gián tiếp là Hàng đầu ghi tiêu đề cho các tiêu chuẩn (Lưu ý: Tiêu
vùng mà ta không thể nhập đề này có thể đặt tên bất kỳ nhưng không được trùng
trực tiếp các điều kiện để lọc với tên trường nào của vùng CSDL)
Từ hàng thứ hai trở đi ghi các điều kiện lọc, mỗi điều
kiện là 1 công thức. Công thức (được bắt đầu với dấu
=) này phải chứa địa chỉ của bản ghi đầu tiên (hàng đầu
tiên của CSDL). 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)
mà phải thông qua 1 phép toán
xử lý nào đó lên trường cần Các tiêu chuẩn trên cùng 1 hàng được thực hiện đồng lọc.
thời (tương đương như phép toán và AND)
Các tiêu chuẩn trên các hàng khác nhau được thực
hiện không đồng thời (tương đương phép toán hoặc OR)
Bảng 2.14. Các nguyên tắc tạo lập vùng tiêu chuẩn gián tiếp 
Để hiểu rõ hơn thế nào là 1 vùng tiêu chuẩn trực tiếp hay gián tiếp ta sẽ xét
ở các ví dụ phần lọc nâng cao (Advanced Filter) dưới đây. 2.6.2. Các kiểu lọc :
2.6.2.1. Lọc tự động (AutoFilter): 
Công dụng: Khi sử dụng tính năng lọc tự động (lọc đơn giản), Excel sẽ tự
động phân nhóm trên các cột, công việc còn lại của ta chỉ là chọn nhóm nào sẽ được
hiện trên màn hình bảng tính. lOMoAR cPSD| 61152560
Hình 2.9: Lọc tự động Autofilter
 Ví dụ minh hoạ: Có bảng công việc thực hiện tại công ty quảng cáo BOCAP như
bảng trên, thứ tự thực hiện công việc của các phân xưởng chỉ được nhập vào theo ngày
tháng, đến cuối tháng người quản lý muốn biết phân xưởng nào (giả sử phân xưởng 3) đã
làm được những việc gì trong tháng, để có được thông tin trên ta làm như sau: Các bước Thao tác Giải thích 1 Chọn miền A2  E13
Chọn miền dữ liệu định lọc (kể cả hàng tiêu đề) 2 Vào menu Data /Filter
Excel tự động chèn những mũi tên /AutoFilter
vào bên phải của các tên trường 3
Chọn cột [Phân xưởng thực Bắt đầu lọc hiện] 4
Chọn một trong các mục được Lọc để hiển thị trên màn hình chỉ có
hiển thị, chọn phân xưởng 3
các thông tin về phân xưởng 3
Bảng 2.15. Các bước thực hiện lọc dữ liệu tự động
 Ý nghĩa các thông số của menu lọc tự động: lOMoAR cPSD| 61152560 Thứ tự Thông số Giải thích 1 All
Hiện toàn bộ các bản ghi 2 Blanks
Chỉ hiện các bản ghi trống 3 Nonblanks
Chỉ hiện các bản ghi không trống 4 Custom
Thêm 1 vài điều kiện để lọc
Bảng 2.16. Thông số của Menu lọc tự động
 Khi lựa chọn Custom (sử dụng các toán tử so sánh)  xuất hiện hộp thoại sau với 2
khung nhỏ để ghi thêm các điều kiện lọc. Giả sử ta chỉ muốn hiển thị trên bảng tính phân
xưởng 1 và phân xưởng 3 ta làm như sau:
Hình 2.10: Hộp thoại lọc tự động Autofilter khi chọn Custom TT Thao tác Giải thích
1 Tại ô trắng trên cùng bên tay trái chọn Equal
Chọn điều kiện bằng (=) (bằng)
Tại ô trắng trên cùng bên tay phải ta gõ [Phân xưởng 2 1] Điều kiện lọc 1
3 Kích chọn vào hộp lựa chọn Or (hoặc)
Điều kiện lọc 1 hoặc là ... lOMoAR cPSD| 61152560
Tại ô trắng dưới cùng bên tay trái chọn Equal 4 (bằng)
Chọn điều kiện bằng (=)
Tại ô trắng trên cùng bên tay phải ta gõ [Phân xưởng 5 3] Điều kiện lọc 2 6 Chọn OK
Hiển thị kết quả lọc
Bảng 2.17. Các bước lọc tự động Autofilter khi chọn Custom 
Ý nghĩa của các thông số còn lại trong hộp thoại Custom: TT Thông số Giải thích 1 Equals Bằng (=) 2 Does not equal
Không bằng (không phải là) 3 Is greater than Lớn hơn (>) 4 Is greater than or equal
Lớn hơn hoặc bằng (>=) to 5 Is less than Nhỏ hơn (<) 6 Is less than or equal to
Nhỏ hơn hoặc bằng (<=) 7 Begin with Khởi đầu bằng ... 8 Does not begin with
Khởi đầu không bằng ... 9 End with Kết thúc là ... 10 Does not end with Kết thúc không là ...
Bảng 2.18. Các thông số trong hộp thoại lọc tự động Autofilter
 Khi muốn hiển thị trở lại bình thường ta làm như sau: TT Mục đích Thao tác 1 Huỷ lọc trong 1 cột
Bấm nút chuột tại của cột đó, chọn All lOMoAR cPSD| 61152560 2 Huỷ lọc toàn bộ
Data /Filter /chọn AutoFilter và xoá dấu  3
Hiện lại tất cả các hàng Data /Filter /Show All
trong danh sách được lọc
Bảng 2.19. Khôi phục lại dữ liệu ban đầu
2.6.2.2. Lọc nâng cao (Advanced Filter)
 Công dụng: Lọc nâng cao (Advanced Filter) dùng để tìm các bản ghi thoả mãn các
điều kiện phức tạp hơn. Khi thực hiện chức năng lọc này bắt buộc phải dùng miền tiêu
chuẩn (điều kiện lọc).
 Các bước thực hiện như sau: TT Thao tác Giải thích
1 Tạo miền tiêu chuẩn (các điều kiện lọc)
Nhập vào các điều kiện lọc
2 Chọn vùng CSDL định lọc Bắt đầu lọc
Vào menu Data /Filter /Chọn Advanced 3 Filter
Sử dụng tính năng lọc nâng cao
Bảng 2.20. Các bước thực hiện lọc nâng cao 
Ý nghĩa các mục trong hộp thoại Advanced Filter như sau : lOMoAR cPSD| 61152560
Hình 2.11: Hộp thoại lọc Advanced Filter TT Thông số Giải thích 1 Filter the List, in place Lọc ngay tại vùng CSDL 2 Copy to Another
Lọc ra vùng khác, trích các bản ghi đạt tiêu chuẩn Location
lọc sang miền khác của bảng tính, địa chỉ của miền
này được xác định tại khung Copy to 3 List Range
Vùng CSDL nguồn muốn lọc 4 Criteria Range
Vùng chứa điều kiện lọc 5 Copy to
Miền đích để chứa các bản ghi đạt tiêu chuẩn lọc 6 Unique Record Only
Chỉ hiện 1 bản ghi trong số các bản ghi trùng nhau lOMoAR cPSD| 61152560
Bảng 2.21. Các thông số trong hộp thoại Advance Filter
Hình 2.12: Minh hoạ cách sử dụng lọc Advanced Filter (vùng điều kiện trực tiếp)
 Ví dụ 1: Thông tin về các cá nhân của công ty CHAUCHAU như bảng trên, người
quản lý muốn có danh sách các nữ nhân viên có có quê ở Hà Nội, để có được danh sách đó ta làm như sau: TT Thao tác Giải thích
Đây chính là vùng tiêu chuẩn trực
1 Tạo miền tiêu chuẩn từ ô A16  F17 tiếp 2 Chọn vùng A2  F12
Đây là vùng CSDL muốn lọc
Vào menu Data /Filter /Chọn Advanced 3 Filter
Sử dụng tính năng lọc nâng cao
4 Gõ $A$2:$F$12 vào ô List Range
Địa chỉ của vùng CSDL muốn lọc lOMoAR cPSD| 61152560
5 Gõ $A$16:$F$17 vào ô Criteria Range
Địa chỉ của vùng tiêu chuẩn 6 Chọn OK
Kết thúc lọc, hiển thị kết quả
Bảng 2.22. Các bước thực hiện lọc bằng Advace Filter
 Ví dụ 2: Cũng tại công ty CHAUCHAU như bảng trên, cuối năm các nhân viên của
Sở Lao động xuống kiểm tra về tình hình lao động của công ty, muốn biết được liệu công
ty có vi phạm các quy định trong Luật lao động như: Có sử dụng người dưới 18 tuổi không;
Thu nhập cả năm có dưới 6 triệu không? Để giải quyết các vấn đề đó ta làm như sau:
Hình 2.13: Minh hoạ cách sử dụng lọc Advanced Filter (vùng điều kiện gián tiếp) lOMoAR cPSD| 61152560 TT Thao tác Giải thích
Tạo miền tiêu chuẩn từ 1 ô A16  F17
Đây là vùng tiêu chuẩn gián tiếp
Gõ vào ô C17 với nội dung như trên để kiểm tra xem C17
lao động có dưới 18 tuổi hay không (năm hiện tại năm 2 =(YEAR(TODAY())-
sinh), ở đây cho ta FALSE vì Hải sinh năm 1978 đã YEAR(C3))<18 lớn hơn 18 tuổi
Gõ vào ô F18 (lưu ý: gõ vào ô F18 chứ không phải ô
F17 vì đây là phép toán Hoặc nên không được cùng F18=F3<6000000
dòng 17) với nội dung như trên để xem tổng thu nhập
có <6.000.000 hay không, ở đây cho ta kết quả
FALSE vì Hải thu nhập >6.000.000 3 Chọn vùng A2  F12
Đây là vùng CSDL muốn lọc Vào menu Data
4 /Filter /Chọn Advanced Sử dụng tính năng lọc nâng cao Filter Gõ $A$2:$F$12 vào ô 5 List Range
Địa chỉ của vùng CSDL muốn lọc Gõ $A$16:$F$18 vào ô 6 Criteria Range
Địa chỉ của vùng tiêu chuẩn 7 Chọn OK
Kết thúc lọc, hiển thị kết quả
Bảng 2.23. Các bược thực hiện lọc Advanced Filter (vùng điều kiện gián tiếp)