Bài giảng Tin học văn phòng - Chương 1: Excel nâng cao (Phần 1) - Hoàng Thanh Hòa

Giáo viên: Hoàng Thanh Hòa
 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
 hthoa@cofer.edu.vn Trang 2
• 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).
 hthoa@cofer.edu.vn Trang 3
• Để 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. 
 hthoa@cofer.edu.vn Trang 4
• Ví dụ:
 Field name
 Record STT Họ và tên Lớp Điểm
 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
 hthoa@cofer.edu.vn Trang 5
• 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. 
 hthoa@cofer.edu.vn Trang 6
• Sắp xếp 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 [Menu] Data/Sort..., hộp thoại sau 
 xuất hiện: 
 hthoa@cofer.edu.vn Trang 7
• 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
 ✓ 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
 hthoa@cofer.edu.vn Trang 8
• Sắp xếp theo nội dung của nhiều cột:
 ➢ My data has Header:
 ✓ 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:
 ✓ 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
 hthoa@cofer.edu.vn Trang 9
• 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. 
 hthoa@cofer.edu.vn Trang 10
• 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 
 (điều kiện lọc). 
 hthoa@cofer.edu.vn Trang 11
• Các yếu tố cơ bản để lọc dữ liệu:
 ➢ Các tiêu chuẩn trên cùng hà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 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*
 hthoa@cofer.edu.vn Trang 12
• 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 [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. 
 hthoa@cofer.edu.vn Trang 13
• Lọc tự động
 hthoa@cofer.edu.vn Trang 14
• 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 (Chọn khối ô chứa CSDL) 
 ✓ Thực hiện lệnh [Menu] Data/Advanced Filter. 
 hthoa@cofer.edu.vn Trang 15
• 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. 
 hthoa@cofer.edu.vn Trang 16
• 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. 
 ➢ 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. 
 hthoa@cofer.edu.vn Trang 17
• Lọc nâng cao:
 hthoa@cofer.edu.vn Trang 18
• Lọc nâng cao:
 hthoa@cofer.edu.vn Trang 19
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 
 bảng CSDL (database) thoả mãn điều kiện trong vùng 
 tiêu chuẩn (criteria).
• VD: Tính tổng của TỔNG ĐIỂM các học sinh có điểm 
 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)
 hthoa@cofer.edu.vn Trang 20
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)
 hthoa@cofer.edu.vn Trang 21
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. 
• 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)
 hthoa@cofer.edu.vn Trang 22
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. 
• VD: Tìm TỔNG ĐIỂM cao nhất của các học sinh có 
 điểm toán >=6 và văn >5
=DMAX(B4:I14,6,K4:L5)
=DMAX(B4:I14,“TỔNG ĐIỂM”,K4:L5)
=DMAX(B4:I14,G4,K4:L5)
 hthoa@cofer.edu.vn Trang 23
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. 
• VD: Tìm TỔNG ĐIỂM nhỏ nhất của các học sinh có 
 điểm toán >=6 và văn >5
=DMAX(B4:I14,6,K4:L5)
=DMAX(B4:I14,“TỔNG ĐIỂM”,K4:L5)
=DMAX(B4:I14,G4,K4:L5)
 hthoa@cofer.edu.vn Trang 24
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. 
 hthoa@cofer.edu.vn Trang 25
 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ụ: Cho bảng dữ liệu như sau: 
 hthoa@cofer.edu.vn Trang 26
 Để 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 
 Subtotal xuất hiện như sau: 
 hthoa@cofer.edu.vn Trang 27
• At Each Change in: 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 đó. 
 hthoa@cofer.edu.vn Trang 28
• 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. 
• Summary below data để đưa dòng tính toán/thống kê
 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. 
 hthoa@cofer.edu.vn Trang 29
 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