Giáo án Excel

3, Hàm COUNTBLANK ()
- Cấu trúc : =COUNTBLANK(vùng dữ liệu)
- Hàm thực hiện đếm các ô rỗng trong vùng dữ liệu
4, Hàm COUNTIF ()
- Cấu trúc : =COUNTIF(vùng dữ liệu; điều kiện)
- Hàm thực hiện đếm số các ô giá trị thoả mãn điều kiện trong vùng dữ liệu
- Ví dụ
	=Countif(A1:A5; “>=5”)
5, Hàm SUMIF ()
- Cấu trúc : =SUMIF(cột1;điều kiện;cột2)
- Hàm thực hiện tính tổng trên cột 2 trên cột1 thoả mãn điều kiện
- Ví dụ 
	=Sumif(A2:A5; “Gạo”;C2:C5)
Bài 11
Cơ sở dữ liệu trong Excel
1, Khái niệm cơ bản
Cơ sở dữ liệu (CSDL) là tập hợp các dữ liệu được sắp xếp trên một vùng chữ nhật (gồm ít nhất 2 dòng) của bảng tính theo quy định sau
- Dòng đầu tiên ghi các tiêu đề của dữ liệu, mỗi tiêu đề trên một cột, các tiêu đề này được gọi là các trường (Field)
- Dòng thứ hai trở đi chứa dữ liệu, mỗi hàng là một bản ghi (Record)
2, Sắp xếp dữ liệu
- Nếu định sắp xếp cho toàn bộ CSDL : Chọn một ô bất kỳ trong vùng CSDL
- Nếu định sắp xếp cho một số bản ghi : Chọn vùng dữ liệu cần đưa vào sắp xếp
- Các bước thực hiện
	+ Chọn vùng CSDL hoặc cột cần sắp xếp
	+ Vào Data / Sort / Xh giao diện
	+ Lựa chọn
	Sort By : Trường ưu tiên thứ nhất trong khoá sắp xếp
	Then By : Trường ưu tiên thứ hai và thứ ba trong khoá sắp xếp
	Ascending : Sắp xếp tăng dần 
	Descending : Sắp xếp giảm dần
	My list has : Header Row (hoặc No Header Row) vùng dữ liệu có chứa hoặc không chứa hàng tiêu đề.
2, Lọc dữ liệu
a, các yếu tố cơ bản
- Miền dữ liệu (Database) : Chứa toàn bộ dữ liệu cần xử lý bao gồm cả hàng tiêu đề
- Miền tiêu chuẩn (Criteria) : Là miền bất kỳ trên bảng tính nằm ngoài vùng CSDL, chứa các điều kiện tiêu chuẩn. Miền tiêu chuẩn gồm tối thiểu 2 hàng : hàng đầu tiên chứa tiêu đề của miền tiêu chuẩn, các tiêu đề này 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à điều kiện của CSDL
* Miền tiêu chuẩn so sánh trực tiếp (TCSSTT): Cho phép ta đưa vào 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 đó. TCSSTT được tạo theo nguyên tắc sau :
	- Hàng đầu tiên ghi tiêu đề cho các điều kiện, lấy tên trường làm tiêu đề
	- Hàng thứ hai trở đi ghi các tiêu chuẩn so sánh, các tiêu chuẩn trên cùng một dòng được thực hiện đồng thời (AND), các tiêu chuẩn trên các dòng khác nhau được thực hiện không đồng thời (OR)
- ví dụ 1
Tên
Bình
à Lọc ra những người tên Bình
- ví dụ 2
Lọc ra những người có điểm trung bình trong khoảng từ 5 đến 8
Trung bình
Trung bình
>=5
<8
* Miền tiêu chuẩn so sánh gián tiếp (TCSSGT) : 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 nào đó
	- Hàng đầu tiên ghi tiêu đề cho các tiêu chuẩn, tên không được trùng với tên trường nào trong CSDL
	- Hàng thứ hai 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ả của công thức này là một giá trị lôgic True or False
Năm sinh
=Year(C2)<1975
- 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)
- Các bước thực hiện
	+ B1: Chọn vùng CSDL 
	+ B2 : Vào Tools / Auto / Autofilter / Excel tự động chèn những mũi tên vào bên phải các trường.
	+Chọn mũi tên tại trường dùng làm tiêu chuẩn để lọc
	+ Lựa chọn
	All : Hiện toàn bộ các bản ghi
	Blanks : Chỉ hiển thị bản ghi trống
	Nonblanks : Chỉ hiển thị các bản ghi chứa dữ liệu
	Custom  : Dùng các toán tử so sánh
- Trong đó
	+ ô 1 và ô 3 dùng để nhập điều kiện so sánh
	+ ô 2 và ô 4 dùng để nhập giá trị so sánh
- Huỷ lọc dữ liệu
	+ Huỷ lọc trên một trường : Kích chuột vào mũi tên chọn All
	+ Huỷ lọc toàn bộ : Vào Data / bỏ dấu chếch tại Auto Filter
	+ Hiện lại tất cả các hàng trong danh sách được lọc : Data/ Filter/ Show All
c, Lọc nâng cao (Advanced Filter)
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. chức năng lọc nâng cao này ứng với các tiêu chuẩn so sánh gián tiếp, bắt buộc phải dùng miền tiêu chuẩn. Các bước thực hiện như sau
B1 : Tạo miền tiêu chuẩn
B2 : Chọn miền dữ liệu lọc
B3 : Vào Data / Filter / Advanced Filter / Xh giao diện
B4 : Lựa chọn
* Mục Action
	+ Filter the list, In – Place : Lọc tại chỗ (ngay tại vị trí của CSDL chỉ hiện các bản ghi thoả mãn tiêu chuẩn lọc)
	+ Copy to another location : Trích các bản ghi thoả mãn điều kiện 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 trong ô Copy To
	+ List range : Địa chỉ miền dữ liệu nguồn đem lọc
	+ Criteria range : Địa chỉ miền tiêu chuẩn
	+ Copy to : Địa chỉ miền đích để chứa các bản ghi thoả mãn tiêu chuẩn lọc
	+ Unique records only : Chỉ hiện một bản ghi trong số các bản ghi trùng nhau
Bài 12
Các hàm cơ sở dữ liệu
1, ý nghĩa
-Dùng để trả lại một giá trị từ CSDL theo một điều kiện nào đó
2, Các hàm trong CSDL
- bảng CSDL dùng minh hoạ
Tên
tuổi
giới tính
lương
tuấn
24
nam
80
anh
28
nữ
100
quân
34
nam
56
nam
28
nam
57
thuỳ
34
nữ
30
thao
45
nữ
45
hảo
45
nữ
23
Vần H
tuổi
giới tính
lương
FALSE
28
nữ
>=50
1
100
198
100
a, Hàm Dsum ()
- cú pháp
	=DSUM(vùng dữ liệu;trường lọc;miền tiêu chuẩn lọc)
- Hàm thực hiện tính tổng trên một trường của cơ sở dữ liệu thoả mãn điều kiện ghi trong miền tiêu chuẩn
- ví dụ :
Tính tổng lương của những nhân viên có giới tính là nữ
= DSUM(B8:E15,4,D17:D18)
* Chú ý :
- Khi chỉ tính theo một điều kiện thì có thể thay hàm Dsum bằng hàm Sumif. Nhưng khi tính từ hai điều kiện trở lên hàm Dsum có thể thay thế bằng công thức mảng (Ctrl + Shift + Enter)
- Ví dụ : Tính lương của những người là nữ có lương từ 50 trở lên
	=DSUM(B8:E15,4,D17:E18)
Có thể thay bằng
=SUM(IF(D9:D15="nữ",1,0)*IF(E9:E15>=50,1,0)*E9:E15)
b, Hàm Daverage ()
- Cú pháp 
	=DAVERAGE(vùng dữ liệu;trường lọc;miền tiêu chuẩn lọc)
- Hàm thực hiện tính giá trị trung bình trên một trường của CSDL thoả mãn điều kiện ghi trong miền tiêu chuẩn
- Ví dụ : Tính tuổi trung bình của những nhân viên là Nữ
=DAVERAGE(B8:E15,2,D17:D18)
c, Hàm Dmax ()
- Cú pháp 
	=DMAX(vùng dữ liệu;trường lọc;miền tiêu chuẩn lọc)
- Hàm thực hiện tìm giá trị lớn nhất trên một trường của CSDL thoả mãn điều kiện ghi trong miền tiêu chuẩn
- Ví dụ : Tính lương cao nhất của những người có tuổi bằng 28
=DMAX(B8:E15,4,C17:C18)
d, Hàm Dmin ()
- Cú pháp
	=DMIN(vùng dữ liệu;trường lọc;miền tiêu chuẩn lọc)
- Hàm thực hiện tìm giá trị nhỏ nhất trên một trường của CSDL thoả mãn điều kiện ghi trong miền tiêu chuẩn
- Ví dụ : Tính lương nhỏ nhất của những người có tuổi bằng 28
=DMIN(B8:E15,4,C17:C18)
e, Hàm Dcount ()
- Cú pháp
	=DCOUNT(vùng dữ liệu;trường lọc;miền tiêu chuẩn lọc)
- Hàm thực hiện đếm số bản ghi của CSDL trên trường lọc thoả mãn điều kiện ghi trong miền tiêu chuẩn
- Ví dụ : Đếm những người có tên bắt đầu bằng chữ H
=DCOUNT(B8:E15,2,B17:B18)
3, Tổng kết theo nhóm (Sub Total)
- Chức năng này cho phép tạo các dòng tổng kết trong một CSDL, Sub Total 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 người dùng. Các bước thực hiện như sau
	+ B1 : Sắp xếp dữ liệu theo trường cần tạo Sub Total
	+ B2 : Chọn Data / Sub Totals / Xh giao diện
	+ B3 : Lựa chọn	
	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 tức là dòng thực hiện các phép tính
	Use Function : Chọn hàm để tính toán tổng kết dữ liệu, hàm ngầm định là Sum
	Add Sub Total to : Chọn các trường cần tính toán
	+ B4 : Chọn OK
Bài 13 
Biểu đồ trong Excel – In bảng tính
A, Biểu đồ trong Excel
	Khả năng biểu diễn số liệu bằng đồ thị của Excel rất phong phú. Các biểu đồ được cài đặt trên bảng tính sẽ tăng thêm sức hấp dẫn và thuyết phục của số liệu
1, Các bước tạo biểu đồ mới
- B1 : Chọn miền dữ liệu cần tạo biểu đồ
- B2 : Vào Insert / Chart / Xh giao diện Chart Wizard – Step 1 of 4 – Chart Type
- B3 : Lựa chọn
	Mục Standard Type : Chọn kiểu biểu đồ thích hợp cần biểu diễn
Nhấn chọn Next xuất hiện giao diện Chart Wizard – Step 2 of 4 – Chart Data Source
	Mục Data Range : Chọn vùng CSDL cần tạo biểu đồ
	Mục Series In : 
	Row : Phân tích đồ thị theo dạng dòng
	Columns : Phân tích đồ thị theo dạng cột
Nhấn chọn Next xuất hiện giao diện Chart Wizard – Step 3 of 4 – Chart Option
	Mục Titles : Thiết lập các thông số cho đồ thị
	Chart Title : Nhập tiêu đề chung cho đồ thị
	Category (X) Axit : Nhập tiêu đề trục X
	Value (Y) Axit : Nhập tiêu đề trục Y
Nhấn chọn Next xuất hiện giao diện Chart Wizard – Step 4 of 4 – Chart Location
	Mục Place Chart : Chọn không gian chứa biểu đồ
	As new sheet : Tạo biểu đồ trên một Sheet mới (trang mới
	As Object in : Tạo biểu đồ trên Sheet đang làm việc
Nhấn chọn Finish
2, Chỉnh sửa đồ thị
- Ta nháy đúp vào đối tượng cần sửa xuất hiện khung chữ nhật với các chấm vuông tại các góc và các cạnh của đối tượng
	+ Để thay đổi kích thước đối tượng : Di chuột vào chấm vuông khi con trỏ biến thành mũi tên hai chiều thì nháy chuột trái giữ và kéo
	+ Di chuyển đối tượng : Chọn đối tượng giữ và kéo
	+ Xoá đối tượng : Chọn đối tượng sau đó nhấn Delete
B, In bảng tính
1, Xem trước khi in
- Chọn File / Print Preview để xem bố cục của bảng tính
- Kiểm tra xem máy in đã sẵn sàng chưa
2, In bảng tính
- Để in toàn bộ bảng tính ta chọn biểu tượng Print trên thanh công cụ
- In theo lựa chọn
	+ File / Print / Xh giao diện
Mục Printer 
	+ Name : Lựa chọn máy in kết nối với máy tính
Mục Prin Range : Lựa chọn in
	+ All : In toàn bộ bảng tính
	+ Page(s) From – To : In một dãy trang liên tiếp
Mục Print What : In theo điều kiện
	+ Selections : In một khối đã được lựa chọn trước
	+ Selected Sheet(s) : In một hoặc một nhóm bảng tính
	+ Entire Workbook : In toàn bộ tập bảng tính
Mục Copies :
	+ Number of Copies : Lựa chọn số bản in
	+ Collate : Nếu đánh dấu thì sẽ in theo thứ tự từ trang đầu đến trang cuối sau đó vòng lại theo số lần theo hướng nhân bản ở trên, nếu không đánh dấu sẽ in từng trang theo số lượng nhân bản
- Nhấn chọn OK