Giáo viên: Hoàng Thanh Hòa
hthoa@cofer.edu.vn
Trang 2
hthoa@cofer.edu.vn
1. Khái niệm CSDL trong Excel 2. Sắp xếp CSDL 3. Lọc dữ liệu 4. Các hàm trong CSDL 5. Sutotal 6. Data Validation 7. Conditional Formatting
• 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) và các dòng, kể từ dòng thứ hai trở đi chứa đầy đủ thông tin của các trường.
• 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).
Trang 3
hthoa@cofer.edu.vn
• Để 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.
Trang 4
hthoa@cofer.edu.vn
Field name
• Ví dụ:
STT Họ và tên
Lớp
Điểm
Record
1
Nguyễn Thụy Anh
CNTT1
7
2
Hoàng Nhật Nam
CNTT1
8
3
Nguyễn Quốc Toản
CNTT1
8
4
Võ Nhật Huy
CNTT1
7.5
Trang 5
hthoa@cofer.edu.vn
• Sắp xếp nhanh:
để sắp xếp tăng
➢ Click chuột vào ô bất kỳ trong trường muốn sắp xếp. ➢ Click vào nút Sort Ascending
dần.
➢ Click vào nút Sort Descending để sắp xếp giảm
Trang 6
hthoa@cofer.edu.vn
dần.
• Sắp xếp theo nội dung của nhiều cột:
đen các dòng cần sắp xếp.
➢ Click chuột vào ô bất kỳ trong bảng CSDL hoặc bôi
➢ Thực hiện lệnh [Menu] Data/Sort..., hộp thoại sau
Trang 7
hthoa@cofer.edu.vn
xuất hiện:
• Sắp xếp theo nội dung của nhiều cột:
➢ 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. ➢ Nếu dữ liệu bị trùng thì sắp xếp tiếp
Trang 8
hthoa@cofer.edu.vn
✓ Add level: thêm cấp mới ✓ Then by: chọn Field làm khóa sắp xếp tiếp theo ✓ Copy level: sao chép cấp đã sắp xếp ✓ Delete level: xóa cấp sắp xếp
• Sắp xếp theo nội dung của nhiều cột:
✓ Chọn thì sắp xếp theo dữ liệu của cột ✓ Không chọn thì sắp xếp theo tên cột ➢ Options: giúp thêm các tùy chọn sau:
➢ My data has Header:
Trang 9
hthoa@cofer.edu.vn
✓ Case sensitive: phân biệt chữ hoa chữ thường ✓ Sort top to bottom: sắp xếp từ trên xuống ✓ Sort left to right: sắp xếp từ trái qua phải
• Các yếu tố cơ bản để lọc dữ liệu:
Để thực hiện lọc dữ liệu phải xác định các yếu tố cơ bản
trên bảng tính:
1.Vùng dữ liệu: Chứa toàn bộ dữ liệu cần xử lý kể cả dòng
tiêu đề.
2.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).
Vùng tiêu chuẩn gồm tối thiểu 2 hàng. Hàng đầu chứa tiêu
đề của vùng tiêu chuẩn. Các hàng thứ 2 trở đi là tiêu chuẩn của CSDL.
Trang 10
hthoa@cofer.edu.vn
• Các yếu tố cơ bản để lọc dữ liệu:
2. Vùng tiêu chuẩn : Cho phép đưa vào các tiêu chuẩn để so sánh số liệu trong một trường của CSDL với một dữ liệu nào đó. Được tạo theo nguyên tắc như sau: ➢ Phải có ít nhất 2 hàng, hà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.
➢ Hàng thứ hai trở đi để ghi các tiêu chuẩn so sánh
Trang 11
hthoa@cofer.edu.vn
(điều kiện lọc).
• Các yếu tố cơ bản để lọc dữ liệu:
(tương ứng hàm AND các điều kiện).
➢ Các tiêu chuẩn trên cùng hàng được xét đồng thời
➢ Các tiêu chuẩn trên các hàng khác nhau được xét
không đồng thời (tương ứng hàm OR các điều kiện).
Điểm trung bình
Họ và tên
Điểm trung bình Họ và tên
>8
V*
>8
V*
Trang 12
hthoa@cofer.edu.vn
• Lọc tự động
hoặc đánh dấu vùng CSDL dữ liệu định lọc.
➢ Đưa chuột trỏ đến một ô bất kỳ trong vùng CSDL
Trang 13
hthoa@cofer.edu.vn
➢ Thực hiện lệnh [Menu] 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.
Trang 14
hthoa@cofer.edu.vn
• Lọc tự động
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.
• Lọc nâng cao ➢ Lọc nâng cao là phương pháp dùng để lọc ra các bản
➢ Các bước tiến hành :
Trang 15
hthoa@cofer.edu.vn
✓ Tạo vùng tiêu chuẩn. ✓ Đánh dấu vùng CSDL (Chọn khối ô chứa CSDL) ✓ Thực hiện lệnh [Menu] Data/Advanced Filter.
Trang 16
hthoa@cofer.edu.vn
• 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.
➢ Unique Records Only: Click chọn nếu muốn chỉ hiện
• Lọc nâng cao: ➢ 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.
Trang 17
hthoa@cofer.edu.vn
một bản ghi trong số các bản ghi trùng nhau.
Trang 18
hthoa@cofer.edu.vn
• Lọc nâng cao:
Trang 19
hthoa@cofer.edu.vn
• Lọc nâng cao:
1. 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
• VD: Tính tổng của TỔNG ĐIỂM các học sinh có điểm
bảng CSDL (database) thoả mãn điều kiện trong vùng tiêu chuẩn (criteria).
Trang 20
hthoa@cofer.edu.vn
toán >=6 và văn >5 =DSUM(B4:I14,6,K4:L5) =DSUM(B4:I14,“TỔNG ĐIỂM”,K4:L5) =DSUM(B4:I14,G4,K4:L5)
2. Hàm DAVERAGE • 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 (database) thoả mãn điều kiện trong vùng tiêu chuẩn (criteria)
• VD: Tính điểm trung bình của TỔNG ĐIỂM các học sinh có
điểm toán >=6 và văn >5 =DAVERAGE(B4:I14,6,K4:L5) =DAVERAGE(B4:I14,“TỔNG ĐIỂM”,K4:L5) =DAVERAGE(B4:I14,G4,K4:L5)
Trang 21
hthoa@cofer.edu.vn
3. Hàm DCOUNT • 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.
Trang 22
hthoa@cofer.edu.vn
• VD: Đếm số học sinh có điểm toán >=6 và văn >5 =DCOUNT(B4:I14,6,K4:L5) =DCOUNT(B4:I14,“TỔNG ĐIỂM”,K4:L5) =DCOUNT(B4:I14,G4,K4:L5)
• VD: Tìm TỔNG ĐIỂM cao nhất của các học sinh có
4. Hàm DMAX • 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.
điểm toán >=6 và văn >5
Trang 23
hthoa@cofer.edu.vn
=DMAX(B4:I14,6,K4:L5) =DMAX(B4:I14,“TỔNG ĐIỂM”,K4:L5) =DMAX(B4:I14,G4,K4:L5)
• VD: Tìm TỔNG ĐIỂM nhỏ nhất của các học sinh có
5. Hàm DMIN • 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.
điểm toán >=6 và văn >5
Trang 24
hthoa@cofer.edu.vn
=DMAX(B4:I14,6,K4:L5) =DMAX(B4:I14,“TỔNG ĐIỂM”,K4:L5) =DMAX(B4:I14,G4,K4:L5)
Trang 25
hthoa@cofer.edu.vn
6. Hàm DGET • 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.
Subtotal là chức năng cho phép người sử dụng tổng
Ví dụ: Cho bảng dữ liệu như sau:
Trang 26
hthoa@cofer.edu.vn
hợp số liệu theo nhóm.
Để tổng hợp dữ liệu của bảng trên lại theo Lớp, đồng thời tính xem tổng số học bổng mỗi lớp nhận được là bao nhiêu, ta thực hiện các bước sau: • Sắp xếp trường muốn nhóm lại theo chiều tăng (hoặc giảm) miễn sao các giá trị của cột cần nhóm lại mà giống nhau thì phải nằm kề nhau. Ở đây ta phải sắp xếp lại trường Lớp.
• Bôi đen CSDL muốn tổng hợp. • Thực hiện lệnh [menu] Data/Subtotal…, hộp hội thoại
Trang 27
hthoa@cofer.edu.vn
Subtotal xuất hiện như sau:
• At Each Change in: chọn trường
• Use Function: Chọn hàm cần tính
cần tạo nhóm tổng hợp
toán/thống kê
cần tính toán/thống kê
• Add Subtotal to: Chọn các trường
Trang 28
hthoa@cofer.edu.vn
• Click chọn vào Replace current subtotals để thay thế bảng tổng hợp đã có trước đó.
• Summary below data để đưa dòng tính toán/thống kê
• 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.
Trang 29
hthoa@cofer.edu.vn
vào phía dưới các bản ghi. • Kết quả có dạng như sau: • Nếu muốn huỷ tính năng Subtotal thì thực hiện lệnh [Menu] Data/ Subtotal.., -> Remove All.
Mọi chi tiết xin vui lòng liên hệ:
ThS. Hoàng Thanh Hòa
Email: hthoa@cofer.edu.vn Website: https://sites.google.com/site/thanhhoakhcb/ Di động: 0396935167
