Giáo án môn Tin học - Bài 3: Bảng tính excel

Bài 3
Bảng tính Excel
I/ Các khái niệm cơ bản:
Excel là phần mềm chuyên dùng cho công tác kế toán văn phòng chạy trên môi trường Windows do hãng MicroSoft sản xuất. Nó có các tính năng và ứng dụng như sau:
+ Thực hiện được nhiều phép tính từ đơn giản đến phức tạp.
+ Tổ chức và lưu trữ thônh tin dưới dạng bảng.
+ Khi có thay đổi về dữ liệu, bảng tính tự động tính lại theo số liệu mới...
1- Khởi động Excel: Giống Word
2- Màn hình Excel:
a) Thanh tiêu đề
b) Thanh menu ngang
c) Thanh công cụ
d) Thanh định dạng 
e) Thanh công thức (Formular ): Hiển thị toạ độ ô hiện hành và nội dung dữ liệu trong ô 
 hiện hành.
f) Tập bảng tính (Work book):
Là cửa sổ chứa nội dung tệp, tên tệp ngầm định là Book1
* Các thành phần của một tập bảng tính: 
Đường viền ngang: Ghi kí hiệu cột từ trái qua phải theo thứ tự chữ cái từ A, B, C,..,Z, AA, AB, AC,...,AZ, BA, BB, BC,..., BZ, CA... Cột cuối cùng kí hiệu là I V , có tổng số 256 cột.
Đường viền dọc: Ghi số thứ tự dòng từ trên xuống, có tổng số 65.536 dòng. 
Ô - Cell: Là giao điểm của 1 cột với 1 dòng, được xác định địa chỉ bởi cột trước, dòng sau
Ví dụ: 	nếu viết C3 thì sẽ là Cột C dòng 3.
Ô hiện hành: Là ô có khung viền quanh nơi con trỏ ô đậu vào, toạ độ của ô được hiển thị trên thanh công thức 
Bảng tính Sheet: Là một bảng gồm có 256 cột và 65.536 dòng. Theo ngầm định một tập bảng tính gồm có 3 Sheet. Ta có thể chèn thêm 255 Sheet
+ Cách chèn thêm 1 Sheet:
 Nhắp phải chuột lên trên Sheet cuối đ HTXH đ Chọn Insert đ HTXH đ OK.
+ Cách đổi tên Sheet: 
 Nhắp phải chuột lên tên Sheet cần đổi đ Rename đ rồi gõ vào tên mới đ Enter. 
3- các thao tác cơ bản trên bảng tinh:
a) Cách di chuyển con trỏ ô:
+ ơư¯ đ lên, xuống, trái, phải
+ Home : di chuyển con trỏ về ô đầu dòng
+ Ctrl + Home : Chuyển con trỏ về ô A1 
+ Pageup : di chuyển con trỏ ô lên 1 trang màn hình
+ Page Down : di chuyển con trỏ ô xuống 1 trang màn hình
+ Ngoài ra ta có thể nhắp chuột vào ô cần chuyển tới.
b) Vùng dữ liệu: 
Là 1 hoặc nhiều ô liên tục được xác định địa chỉ bởi đ/c ô đầu vùng và địa chỉ ô cuối vùng.
Dạng vùng:	địa chỉ ô đầu : địa chỉ ô cuối
Ví dụ: 	A3 : E8
	B1 : B5
Cách chọn vùng: (Bôi đen)
+ Chọn 1 ô: Nhắp chuột vào ô cần chọn 
+ Chọn 1 dòng: Nhắp chuột vào số thứ tự dòng 
+ Chọn 1 cột: Nhắp chuột vào kí hiệu cột 
+ Chọn 1 vùng: nhắp giữ nút trái chuột tại ô đầu vùng, rồi kéo xuống ô cuối vùng. 
c) Các kiểu dữ liệu:
Trong 1 ô chỉ có thể chứa được 1 kiểu dữ liệu, kiểu dữ liệu phụ thuộc vào kí tự đầu tiên khi gõ vào. Có 3 kiểu dữ liệu:
+ DL kiểu số: Kí tự đầu tiên gõ vào là các số từ 0 đến 9
+ DL kiểu chuỗi: Kí tự đầu tiên gõ vào là các chữ từ A đến Z
+ DL kiểu công thức: Kí tự đầu tiên gõ vào là dấu = 
Trong một công thức có thể chứa dữ liệu kiểu số, kiểu chuỗi (nếu là kiểu chuỗi, trong công thức phải đặt trong dấu ngoặc kép), toạ độ ô, địa chỉ vùng, các toán tử và các loại hàm.
Các toán tử sử dụng trong công thức:
+ Toán tử tính toán: + ; - ; * ; / chia; ^ luỹ thừa; %
Ví dụ:	=C4*D4
=C4/D4
210 = 2^10 = 1024
+ Toán tử chuỗi :	$ nối chuỗi 
+ Toán tử so sánh 	= ; khác ; >= ; ; < 
d) Cách nhập dữ liệu: 
Chuyển con trỏ ô đến ô cần nhập, nhập theo quy ước từng loại dữ liệu, nhập xong ấn ¿ hoặc chuyển con tró sang ô khác 
e) Sửa dữ liệu: Chuyển con trỏ ô đến ô cần sửa, ấn phím F2 , dùng phím đ chuyển con trỏ đến vị 
 trí cần sửa, sửa xong ấn ¿ 
4) Các thao tác với dòng, cột :
a) Thay đổi độ rộng cột, chiều cao dòng: Đưa trỏ chuột đến đường phân cách giữ ký hiệu cột 
 (dòng), trỏ chuột thành hình (mũi tên 2 chiều) nhắp giữ nút trái và rê chuột.
b) Chèn dòng, cột trống:
 Đặt con trỏ ô vào vị trí cần chèn đ vào Insert đ chọn Row - chèn dòng 
	 	 chọn Column - chèn cột 
c) Xoá cột, dòng:
Chọn dòng, cột bằng cách nhắp chuột vào kí hiệu cột (số thứ tự dòng)
Vào Edit đ chọn Delete
5- Các thao tác với tập bảng tính:
a) Ghi lên đĩa: 
b) Mở tệp mới: 
c) Mở tệp có sẵn: 
d) Thoát khỏi ExCel:
II/ sử dụng các hàm mẫu của ExCel:
Dạng hàm: = Tên hàm (Danh sách các trị số) 
Tên hàm: Do Excel quy định, người sử dùng chỉ cần gõ đúng tên, tên hàm không phân biệt chữ in hoa hay chữ thường.
Danh sách các trị số: Có thể là dữ liệu kiểu số, chuỗi, toạ độ ô, địa chỉ vùng và cũng có thể là một hàm khác.
1- Hàm Sum :
= Sum (Danh sách các trị số)
Dùng để tính tổng các giá trị có trong danh sách.
Ví dụ: Giả sử ta có DL trong các ô B1, B2 , B3 , B4 lần lượt là 2 , 4 , 6 , 8
Để thực hiện tính tổng có 2 cách: 
C1: Dùng công thức: =B1+B2+B3+B4 ¿ (= 20 )
C2: Dùng hàm: 
= Sum ( B1 : B4 ) ¿ = 20
2- Hàm Average :
=Average (Danh sách các trị s )
Để tính trung bình cộng của các giá trị có trong D/s
Ví dụ: B1 đến B4 là 2,4,6,8
= Average ( B1 : B4 ) ¿ ( = 5 )
3- Hàm Max :
= Max ( Danh sách các trị số ) 
Để tìm giá trị số học lớn nhất có trong D/s
Ví dụ: =Max ( B1: B4 ) ¿ ( = 8 )
4- Hàm Min :
= Min ( Danh sách các trị số ) 
Tìm giá trị số học nhỏ nhất của các giá trị có trong D/s
Ví dụ: = Min ( B1: B4 ) ¿ ( = 2 )
5- Hàm Round :
= Round ( Biểu thức số ; n )
Làm tròn giá trị biểu thức số đến n số lẻ 
Nếu n > 0 : Làm tròn về bên phải cột thập phân
 n < 0 : Làm tròn về bên trái cột thập phân
Ví dụ: Giả sử trong ô B5 chứa 333,333
= Round ( B5 ; -3 ) ¿ ( = 333.000 )
= Round ( B5 ; -2 ) ¿ ( = 333.300 )	
6- Hàm Logic :
Là các hàm chỉ có 2 giá trị đúng hoặc sai
* Hàm And 
= And ( Đkiện 1; Đ/k 2; ... )
Cho giá trị đúng khi mọi đk nêu trong D/s có giá trị đúng
* Hàm OR
=OR ( Điều kiện1; Đ/k2; ... )
Cho giá trị đúng khi có 1 bất kỳ một đ/k nêu trong danh sách có giá trị đúng.
7- Hàm IF :
= if ( Điều kiện; gtrị đúng; gtrị sai )
Đ/k : Là một biểu thức Logic, Excel kiểm tra kết quả của đ/k. Nếu đúng thì gán giá trị đúng, nếu sai thì gán giá trị sai: 
Giá trị đúng; giá trị sai: Có thể là dữ liệu kiểu số, chuỗi, toạ độ ô, địa chỉ vùng và cũng có thể là một hàm IF khác.
Ví dụ1: Tính phụ cấp chức vụ trong bài tập 2.3
= IF(C4="GĐ";50000;if(OR(C4="PG";C4="TP");40000;if(And(C4="NV";E4>25);15000;0)))
Ví dụ2: Tính lương tháng trong BTTH 2.3
= IF(E4>25;(E4-25)*2*D4+25*D4;D4*E4)
* Địa chỉ tương đối & địa chỉ tuyệt đối :
Địa chỉ tương đối là địa chỉ có dạng cột dòng, khi sao chép công thức nó thay dổi theo phương, chiều và khoảng cách
Ví dụ: J22 Tương đối 
Địa chỉ tuyệt đối là địa chỉ có dạng $cột $dòng, khi sao chép công thức nó giữ nguyên không thay đổi 
Ví dụ: $J$22 đ/c tuyệt đối 
Cách viết địa chỉ tuyệt đối: Gõ địa chỉ tương đổi rồi ấn phím F4
Ví dụ: Tính thưởng năng suất trong bài 2.4
=J22/(D20+G20)*(D7*G7) - Dùng địa chỉ tương đối sẽ không sao chép công thức này được
=($J$22/$D$20+$G$20)*(D7+G7) - Dùng địa chỉ tuyệt đối.
III/ Định dạng dữ liệu trên bảng tính:
1- Định dạng dữ liệu số:
Tách, nhóm các số: chọn vùng dữ liệu, nhắp chuột vào nút để tách nhóm các số bắt đầu từ phải qua trái ba chữ số một nhóm và thêm vào phần lẻ thập phân hai chữ số 0.
Thêm số 0 vào phần lẻ thập phân: 
Chọn vùng dữ liệu đ Nhắp chuột vào nút 
Bớt một số 0 ở phần lẻ thập phân:
 Chọn vùng dữ liệu đ Nhắp chuột vào nút 
2- Thay đổi phông chữ, cỡ chữ, màu chữ: Giống Word 
3- Định vị dữ liệu (dùng cho các tiêu đề cột)
Chọn vùng dữ liệu đ nhắp chuột vào Format đ Cells... htxh đ chọn thẻ Alignment
Mục Horizontal: Căn dl theo chiều ngang (chọn Center)
Mục Vertical: Căn dl theo chiều dọc (chọn Center)
Mục Wrap text: Đánh dấu vào mục này, dl sẽ tự động xuồng dòng khi chiều rộng ô không đủ 
Orientation: Chọn hình thức trải dl
4- Tạo các đường kể cho bảng:
Chọn toàn bộ vùng dl cần kẻ 
Vào Format đ Cells... htxh đ chọn thẻ Border
+ Mục Line Style: Kiểu đường kẻ
+ Nút Outline: Kẻ khung viền quanh
+ Nút Inside: Kẻ các đường trong bảng , xong chọn OK
IV/ Bổ sung một số hàm sắp xếp dữ liệu:
1- Hàm Rank:
= Rank(x;danh sách các trị số) Xác định thứ hạng của giá trị x so với các giá trị có trong D/s
Ví dụ: Tính vị thứ (xếp thứ mấy) trong Bài tập 2.5
=Rank(M7;$M$7:$M$16) xác định giá trị trong ô M7 so với các giá trị trong các ô từ M7 đ M16 đứng thứ tự bao nhiêu.
2- Hàm Vlookup:
= Vlookup(x;Bảng;Cột tham chiếu;Cách dò)
X: Dò tìm giá trị x ở cột bên trái của bảng, khi tìm có thì lệch qua bên phải để lấy giá trị trong ô của nó ứng với giá trị của x.
Bảng: Gồm nhiều dòng, nhiều cột, cột bên trái luôn luôn chứa giá trị để dò tìm, các cột khác chứa giá trị tương ứng để tham chiếu.
Cột tham chiếu: Là thứ tự cột trong bảng, cột đầu tiên là 1, cột tham chiếu từ thứ hai trở đi.
Cách dò: Là 0 hoặc 1
Nếu là 1: D/s ở cột bên trái của bảng phải xếp theo thứ tự tăng dần.
Nếu là 0: D/s ở cột bên trái của bảng không cần xếp theo thứ tự tăng dần. 
Ví dụ: Bài tập 2.5
- Tính điểm Ưu tiên:
=Vlookup(F7;$C$21:$D$23;2;0) - Trong đó: $C$21:$D$23 là địa chỉ của Bảng
- Tính điểm Trung bình
=(G7*2+H7*2+I7+J7+K7+L7)/7
- Tính vị thứ:
=Rank(M7;$M$7:$M$16)
- Tính Kết quả:
=IF(M7>=8,0;"Giỏi";IF(M7>=6,5;"Khá";IF(M7>=5,0;"TB";"Yếu")))
3- Các hàm chuỗi:
- Hàm Left:
=Left(Chuỗi Text;n)	Cho kết quả là n ký tự của chuỗi text tính từ trái qua phải.
Ví dụ: Giả sử trong ô B5 chứa "Trung tâm tin học"
= Left(B5;3) ¿ (= Tru );	=Left(B5;2) ¿ (=Tr )
- Hàm Right
=Right(chuỗi Text;n)
Cho kết quả là n ký tự của chuỗi Text tính từ phải qua trái.
Ví dụ: =Right(B5;3) ¿ ( = học ) 
4- Sắp xếp dữ liệu:
ExCel cho phép sắp xếp dl giữa các ô được chọn độc lập với các ô ngoài khu vực chọn. Việc sắp xếp dl được thực hiện tối đa theo 3 cột.
Ví dụ: 	Tên	 Họ đệm	Ngày sinh 
Cách thực hiện:
- Chọn toàn bộ vùng dữ liệu cần sắp xếp 
- Nhắp chuột vào Data đ Sort đ htxh
- Nhắp chuột vào mục No header row
- Chọn cột cần sắp xếp chính trong khung Sort by
- Chọn 2 cột tiếp theo trong khung Then by
+ Chọn Ascending: sx tăng dần
+ Chọn Descending: sx giảm dần - Xong chọn OK
V/ cơ sở dữ liệu trên bảng tính :
1- Khái niệm về cơ sở dữ liệu (CSDL):
CSDL Là tập hợp các thông tin DL được tổ chức theo cấu trúc dòng và cột để có thể liệt kê, truy tìm, xoá hay rút trích những dòng DL thỏa mãn một tiêu chuẩn nào đó một cách thuận tiện và nhanh chóng. Để làm được điều này ta phải tạo ra 3 vùng DL.
Vùng CSDL (Database):
Gồm ít nhất có 2 dòng, dòng đầu tiên chứa các tiêu đề cột gọi là tên vùng tin. Tên vùng tin phải là DL kiểu chuỗi và không trùng lặp. Các dòng còn lại chứa DL, mỗi dòng gọi là mẫu tin.
Vùng tiêu chuẩn (Criteria):
ít nhất có hai dòng, dòng đầu tiên chứa tiêu đề cột, các dòng còn lại chứa điều kiện.
Vùng trích DL (Extract):
Chứa các mẫu tin trong vùng CSDL thỏa mãn điều kiện vùng tiêu chuẩn.
2- Lọc dữ liệu (Rút - Trích DL):
Ta phải xác định trước 3 vùng DL (như trên)
- Chọn toàn bộ vùng CSDL đ vào Data đ Chọn Filter đ Advanced Filter đ HTXH
- Trong khung Action chọn dòng Copy to another location
+ Mục List range: Nhập đ/c vùng CSDL
+ Mục Criteria range: Nhập đ/c vùng tiêu chuẩn
+ Mục Copy to : Nhập địa chỉ vùng trích DL - Xong chọn OK
Các mẫu tin trong vùng CSDL thỏa mãn điều kiện vùng tiêu chuẩn sẽ được chép vào vùng trích DL.
3- Các dạng vùng tiêu chuẩn \:
- Trích những người có số con lớn hơn 2 và chức vụ là "NV"
Số con
Chức vụ
> 2
NV
- Trích những người có số con lớn hơn 2 hay chức vụ là "NV"
Số con
Chức vụ
> 2
NV
* Nếu các ô đk khác cột có tính chất And (và)
* Nếu các ô đk khác dòng có tính chất OR (Hoặc)
4- Các hàm trong cơ sở dữ liệu:
a) Hàm Dsum:
=DSum(Vùng CSDL;n;Vùng tiêu chuẩn)
Cho kết quả là tổng DL số trên cột thứ n của những mẫu tin trong vùng CSDL thỏa mãn đk vùng tiêu chuẩn.
	Trong đó: n là thứ tự cột cần tính tổng trong vùng CSDL, cột đầu tiên là 1 tính từ trái qua phải.
Ví dụ: 	Tính tổng thu nhập của cán bộ nữ 
+ Tạo vùng tiêu chuẩn là D11:D12
+ Tổng thu nhập cán bộ nữ =DSum(A2:I9;9;D11:D12)
b) Hàm Daverage:
=Daverage(VùngCSDL;n;Vùng tiêu chuẩn)
	Cho kết quả là giá trị trung bình của các mẫu tin trong vùng CSDL thỏa mãn đk vùng tiêu chuẩn.
Ví dụ: 	Tính trung bình thu nhập của cán bộ nữ 
+ Tạo vùng tiêu chuẩn là E11:E12
+ Tổng thu nhập cán bộ nữ = Daverage(A2:I9;9;E11:E12)
c) Hàm Dmax:
=DMax(Vùng CSDL;n;vùng tiêu chuẩn )
Cho kết quả là giá trị lớn nhất trên cột thứ n của các mẫu tin trong vùng CSDL thỏa mãn đk vùng tiêu chuẩn. 
d) Hàm DMin: 	Ngược lại với hàm DMax
e) Hàm Dcounta:
=DCounta(Vùng CSDL;n;Vùng Tiêu chuẩn)
Cho kq là các ô chứa DL trên cột thứ n của những mẫu tin trong vùng CSDL thỏa mãn đk vùng tiêu chuẩn.
Ví dụ: Thống kê xem có bao nhiêu cán bộ nữ - bài 2.8
+ Tạo vùng tiêu chuẩn D11:D12
+ Số cán bộ là nữ: =DCounta(A2:I9;3;D11:D12) = 5
VI/ tạo lập biểu đồ:
Tạo biểu đồ từ dữ liệu trên bảng tính được thực hiện theo 4 bước như sau:
Bước 1: -Nhập bảng DL
	- Chọn toàn bộ vùng DL cần lập biểu đồ
	- Nhắp chuột vào nút Chart Wizard
	- Nhắp chọn kiểu biểu đồ ở khung trái
	- Chọn kiểu chi tiết ở khung phải 
	- Nhắp vào Next đ Bước 2
Bước 2: Chọn cách phân tích DL
	- Chọn Rows: Vẽ theo dòng 
	Columns: Vẽ theo cột 
	- Nhắp vào Next đ Bước 3
Bước 3: Biểu đồ nháp 
	- Chọn thẻ Titler đ Nhắp vào khung Chartitler để gõ vào tiêu đề của biểu đồ 
- Tiêu đề trục X, Y
- Chọn thẻ Data Lebels - Nhắp chuột vào dòng Show Lsbel and Percent để đưa số liệu phần trăm (%) lên các cột của biểu đồ - Nhắp Next đ Bước 4
Bước 4: Chọn vị trí đặt biểu đồ 
- Trong khung As Object in: Chọn Sheet cần đặt biểu đồ đ Finish.
VII/ IN ấN:
1- Chọn cỡ giấy, hướng in:
- Vào File đ chọn Page Setup đ htxh
- Chọn thẻ Page 
	+ Mục Orientation: Chọn hướng in
	Chọn Portrait : in dọc giấy 
	Chọn Landscape: In ngang giấy
	+ Mục Paper Size : Chọn cỡ giấy 
(Chọn A4 210 x 297 mm)
+ Mục Print Quality: Chọn chất lượng in. (Chọn cáng nhiều dpi nét chữ càng đẹp) đ OK
2- Xem trước khi in:
Trước khi in ta nên xem lại bố cục của trang tài liệu bằng cách nhắp chuột vào nút Print Preview đ Màn hình chế độ View xuất hiện (sử dụng các nút công cụ)
+ Zoom: phóng to, thu nhỏ màn hình (hoặc dùng chuột).
+ Print: Mở hộp thoại in để in ra giấy.
+ SetUp: Mở hộp thoại Page Setup để chọn cỡ giấy, hướng in.
+ Cloes: Đóng chế độ View
3- In ra giấy:
- Vào File đ Chọn Print (hoặc ấn Ctrl + P) đ htxh
- Mục Printer : Chọn kiểu máy in
- Mục Print range : Chọn cách in ra
	All : In toàn bộ bảng tính
	Pages From:......To....... (in từ trang ........đến trang .......) 
- Mục Copies : Chọn số lần in ra đ OK 
VIII/ Bảo vệ bảng tính:
Bảo vệ không cho phép mở bảng tính: Giống Word
Bảo vệ không cho phép thay đổi dữ liệu:
- Mở tệp bảng tính cần đặt khoá
- Vào Tool đ chọn Protection đ Protect Sheet đ ht 1 xh - Gõ vào từ khoá và ấn ¿ hộp thoại thứ 2 xuất hiện - Gõ lại từ khoá một lần nữa và ấn ¿