trung t©m tin häc ph¹m chinh
Chuyªn: §µo t¹o – L¾p ®Æt - Söa ch÷a m¸y vi tÝnh
§/c: minh quang – vò th – th¸i b×nh
§T: 036617487, D§: 0904615245, 0363617487
TÀI LIỆU HƯỚNG DẪN THỰC HÀNH MÔN TIN HỌC CƠ SỞ
PHẦN BẢNG TÍNH ĐIỆN TỬ EXCEL
th¸i b×nh th¸ng 9 - 2005
Bài 1. Làm quen với Excel...........................................................................................3


Bài 2. Nhập dữ liệu và định dạng bảng tính.............................................................14

 !"#$%
&'"'(!)*)+,"-*#$%..
/012" 
/3"4!56 7
Bài 3. Công thức và tính toán cơ bản........................................................................45
8
39+:,"8
Bài 4. Dùng hàm trong công thức..............................................................................55
;%88
<' -!-,"88
=-!->?88
/=-!-?@8A
/=-!-;@68B
/=-!-@CDAE
/=-!-;@ A.
/=-!-!5'A
F=-!-"A8
F=-!-G-@H-AA
F&'>9*I"A7

h"i>B8,M)";=.EEE=;6-?(h",.EEE,"';12
!,,%<>9.EEEXjC-*>?;6->k
dXH'5l!'"'D ,"8E)B8!B7;
=6D ,".EEE
&'2"#M8E)B8)B7;>mQR%,".EEE)
Q'2"#M.EEE>m@+QR%('#$,Q(5
HX.EEE=6Q(2QR%('#$,Q(
.EEE@+25,h"i>
n*UTT.EEE#"]-h",.EEEo>"2$"`#$p).EEEo9C#$%p)
q>>.EEEo$,1R>M p)["i,[".EEEo5H,Gp!U""@.EEEoQ%
; 9p)I,"[o,Gh#p
.II.3. Khởi động và thoát khỏi Excel
.II.3.1. Khởi động Excel.
06!- ()W5D Q>k
3,r[,",->r,">"To-Gp
&Y=6@M;*#S'@!"#6Q^o>",pV,">"T
  M  ,  -!  G h"i>) 5  @  !"  #6  Q^    ,    UTT  n,
.II.3.2. Thoát khỏi Excel
&YQ'QR,G:@'),Q(@"'X$Q2 )>;=
"'#S-*,"'>k
&k&gFile\Exit
&.k&@j V=,$;
&kO"#@#6Q^ %,,';
&kJ^%-Alt+F4
&8k&@#6Q^ %,,';@;=J -*,G;R&g
&">
&Aks@#6Q^ ,>@#,)@;=J -*
,G;R&g&">
,",Qb^QQ25;,#$%!";t)>mJ +
#'">k

3>#,k,2'
""#,k+&=+
Qk
u 3,k&y
u I",-k012
u O,ik/m
u [,kd xG
u \
o&@!"+;6g"Lz
#Np
&=6561,%61V'+#S'@{,2@"$
,?"L;Vj;6;Q;H1,%@',-!G
.II.5. Giới thiệu bảng tính của Excel
.II.5.1. Wookbook
|h""@#""@QR:(-*TQ,;t#"]-'h""@>o#$%p)
'#6;]o&,p\,"-*h""@#""@=6=?;.88>&'#$Yf
B8,M,Q()-'5Qb-MX-;1A>fB7)-'5-MX-;1
>,""45;>?>X-;1!5#S'>9D;R
Tools\Option,"*Sheet in new WookbookV#General}>?>X-;1-?
-M,"T-(
O ,'#$%@'=6 (Q,-*#$%
.II.5.2. Wooksheet
* on""@p>m=#$%oh""@>p),-|#$%#"]-
'!o,"i>p)'*o"->p!c"*!!g!+op
u Các hàng (rows)kB7!.EEE=A88A!,"-*#$%o'#$,Q(
=x=A~!p;!o,"ipS-M;XV-|!!;Q^;'
>?f;HA88A
u Các cột (columns)k,"-*#$%V=.8A*;V*;Q^;'>?
"q)n)&)\)•)/
u Các ô (cell)k|+;=;1x,01x!5;Q^';1#SV*!

&,€↑)↓
0656",N+;H;x"L;'5Vc 
o:c,#$%=: p
[• 0656",N+-*-!G
[O 0656",N+?Q(-*-!G
d"- 0656",N(+#,'J,"-*!
&,€d"- 0656",N(=,#,'V#$%
&,€ 0656",N(=Q(#,'V#$%
€←)€→
&='Q>kH+ b!+,?)>m56
;H+,?@HH"Q(-YH+ b
€↑)€↓
;:*)>m56"Q(-Y;H+
@HH=:*
€, &56",N+(*?c,"!
.II.6.3. Dùng hộp thoại Goto
 J%-I8"L@%D;R!@%H K"")J *"2
. K};1xV*-?56(,"*sT,)%8?56(-*+
,#$%6!";=)}#$%)JJ-!;1x+/%3.•
d8E
 &@U<
Ghi chúk
&=656(+Xg#S'};1x+X56(,"*-
n"!J,
.II.7. Các thao tác với file dữ liệu
.II.7.1. Ghi file hiện thời vào đĩa
3@ !"-'5) -(xQ,,"#*(V-'5%H
"'@N) ;=>m-J)";=X$Q,' ;=!";t;6>;=>9
H
&'#Q(D k

JEnter"L@%jSave
3#Q(:)>m* b,-'5%!";t!"-*T-((
T@'"L(TYQ;LM1,%@'
?QM2#$@'V)-M@Save as type;6g
&=6@jTools\General Optionso-M*"23U"p;6=-D
g@'
Always create backupkP+Q2 Y@>9;
Read-only recommendedk*%x;Q^-
Password to openk@y@-M 
Password to modifyk@y@-M;6>9
.II.7.3. Gọi một file có sẵn từ đĩa ra màn hình
<-?g-*T;W;Q^Q,f;t,-!G;6!- H)W5D 
'#Q(>k
MD;RFile)g Openo"L@%jU ,3,""#,pFJ 
**"2Open
,"*Look in)g;t!Q-Q,TX-M
<%;j!"TX-M)"L@%gTX-M!@%jOpen
/%k&'#Q(-MTnOEq>;Q^H!QG>k
Ghi chú:
*'X-;1)Q,T;Q^-MXJM?D;RTO";=);6
-M-*,"T;=x @%!"D;RFile!@%!"TX-M
,"h"i>B)=6-MT;Q^>9Qb5#S'@%!"j
Start),NH!"Documents!@%!"TX-M
2-*b;6-=6-MTV&=6->''T;-MM
Q(D;RWindow<X@%"2T!")W5@%!"T;=
.II.7.4. Tạo một file mới
062"-*T-(W5!-Q>k
MD;RFile)g New)J **"2k
&gh""@#""@!@%U<
Ghi chú:

.II.1. Các kiểu dữ liệu trong Excel
,Q(@ !"#$%)j$6;Q^-*>?@' -R#$
@6 ,"#$%;6,'>D>>=!X-e,"',G%"'!9C
 >!5
,"-*+x=6:-*@6 )@6 V+*!"@CD;X
a# '"2 >k
u dSo">p
u &+:oT",-p
u &'@6|!'',1"
.II.1.1. Kiểu hằng.
a) Text
H,_ =:G6;=!@6@CD)MH;*-L;1>m
61%#,'V+
/%kBảng điểm, Danh sách học sinh\
H,_ =:%J-*"L-*@CD2„)•){)…)ƒ"L
J'o>p,MGD;*6 !"=@6@CDop/%k
.ƒ)…~EB~B).EB8\
H}"!>?-!-?-'56!@CDG$}#l;X#SJ'5,o†p/%
kH-?}-*a@CD!'>?.8G$}†.8
b) Number
<CD!"!>?)MH;*-L;1 2>?>m61M%#$V+
H>?a-=6J‡uˆM%,Q("L;6,"J"L;R-#,#"]-k
&'>?+Qb)%8)8B\
<6!5'k!5'#GQb(Ja'!2oup5
2;:o‰p[X')=661>?"L#SHq)5;1
'61,"*"2I",-*'X-;1)Q,'>?
!5  '  " '  >?  5 f    ;H  .B8~8.8)  % f  !5  ‰‰BEE  ;H
‰.‰BBBB
<6bkQ,>?b"'>?afE;HEBBBBBBBB#l
;XfEkEEkEEo.kEEkEEqp;H.k8Bk8Bok8Bk8B[p

/  !"#$%;Q^D "'#Q(>k
<%"2+X !#GQb"L@2+:;6 
"+;g
<#l;X}#J@C@CD!"f#!%-)@CD;=>m;;Q^6 ,_
 oT",-#,p!",No=2•pJ'5,"+ b
H}X-)W5J%-Back Space;6=|;=!H} !","
@} =6c'%->k
Endk0656",N(?X ,_ 
Homek0656",N(X;X 
Backspacek06=@CD#,'V",N
Deletek06=@CD#$V",N
J%-,;656>+H"
Chú ý:
065 J,=6c'%-xQ(←oLeft Arrowp)→oRight Arrowp)
↑oUp Arrowp)↓oDown Arrowp
H5;C;1 ,Q(@J,)W5J%->"L@%!"*
X,_ <;= f}>m@+;Q^!"+ b
.II.3.2. Một số thủ thuật nhập dữ liệu nhanh.
"4 Q>k
H X!-*W5>?"La"-*5!";=)%Q'
W5k
).)))\
.))A)~)\
‘)I#),)\
"))h)\
=6Q>k
 K} !".+;XVW5o"LxX}!"-*+;XVW5,",Qb^X
W5'!!5,"Xp
. &go"L-*p+;X
 0L,N*2I"#H!J€3;=,*>$"L?Q(

. K}>?X
 JEnter
3?;Q^!">m;Q^]>#$+
/%k-?}>?AA.!"+&~)W5@%+&~)}AA.,]J,
Chú ý:
• *'X-;1),")Xa;Q^`'(X5#M
JJ-opO";=)j$}>?8A:@+;Q^}8)AH}
8)A>m6;=! @6
• <}-*>?a-)W5}Joup)>;=}H'">?/%ku.)u8A~)
uB\
• <>?;Q^}!"!R;*,*V-*+)>m6 >?;=Q(2Q
€Eo2@"g)t!a(E

p"L………………06-"!#*>?)W5
(,*;*,**o-X>p
.II.3.5. Gõ dữ liệu kiểu ngày tháng.
<} @6!5'!b,"#$%)x }j"
-e-!=6D;*#H
n$>;a5 @'@+2!5'oOp!bo-pV
Gõ Kiểu Ghi chú
uq, u--- !5)'
B‰.‰~B -‰‰55 ')!5)`-
.u3u~B u---u55 !5)')`-
3u~B ---u55 ')`-
.kEq k--q‰[ Kb)j>'"L
.kEkEEq k--k>>q‰[ Kb)j)a5>'"L
.kE k-- Kb)j
.kEkEE k--k>> Kb)j)a5
B‰.‰~B.kEE -‰‰55k-- ')!5)`-)b)j
Chú ý:

Tại sao khi gõ số 100000 dưới dạng 100 000 vào một ô của bảng tính và nhấn phím Enter thì dữ
liệu lại nằm bên trái ô?
Tại sao khi gõ ngày 26 tháng 3 năm 1999 dưới dạng 26/3/19 và nhấn phím Enter thì dữ liệu lại
nằm bên trái ô?
Nhập các dữ liệu vào bảng tính theo mẫu sau. Khi nhập chú ý sử dụng các kỹ thuật nhập nhanh.
Sau khi nhập dữ liệu xong, kiểm tra và điều chỉnh dữ liệu cho thật chính xác. Thoát khỏi
Excel, xuất hiện hội hội thoại hỏi ta có lưu file hiện thời và đĩa hay không, hãy kích nút Yes
và ghi vào đĩa với tên BD10A.xls.
Mở file vừa ghi, sửa dữ liệu của bài tập số 4 trong bài ở trên và ghi vào đĩa với tên BD10A.xls.
Dùng lệnh Save As để sao chép thành file BD10B.xls trong cùng thư mục. Sau đó sửa lại theo
mẫu sau và ghi lại vào đĩa.
.III Các thao tác với hàng, cột, ô trong một bảng tính
.III.1. Dịnh dạng cột, dòng, ô
.III.1.1.Thay đổi độ rộng cột.
a) Dùng chuột.
 &g>?*X;x;*,*
. 0L*M-4$cg2X;*>""#H!2-Y
;X
 s*>,'"L$;6$-"L`;*,**$*@QC
b) Dùng thực đơn.
 &g>?*X;x;*,*
. MD;RFormat),N!" Column!@%H!"Width)J **
"2
 ,"*Column Width)},1>?";*,**"'',1fE(.88o#6
1">?@CD;Q^4}";*,*V+p
 <%U<
.III.1.2.Thay đổi độ cao hàng.
a) Dùng chuột.
 &g>?!X;x;*"
. 0L*M-4Q(cg2X;!>""#H!2-Y

+;? ,"cXg
• &g'+'k@%g-*+)Ctrl!@%!"'+_2
.III.3. Chèn hàng, cột và ô
0+@>@} )=X“-!)*"L+;6;- 
&'"'“-;Q^D Q>k
.III.3.1.Chèn hàng.
&g>?!X“#S',*2c;!
<%j$V*2cg;6J D;Rl!g Inserto"L-M
D;RInsert!g Rowsp
HXH)}*!"'!,?f“-
Ví dụ:
.III.3.2.Chèn cột.
 &g>?*X“#S',*2c;*
. <%j$V*2cg;6J D;Rl!g Inserto"L
-MD;RInsert!g Columnsp
 HXH)}*!"'*,?f“-
Ví dụ:
.III.3.3.Chèn ô.
 &g>?+X“
. <%j$V*2cg;6J D;Rl!g Inserto"L
-MD;RInsert!g CellspFJ **"2k
 &g-*,"'Dg>k
Shift Cells Rightk“!;y5'+;Q^g>$
Shift Cells Downsk“!;y5'+;Q^g?Q(
Entire Rowk“"!#*'!,?%,'+;Wg
Entire Columnk“"!#*'*,?!"#,''+;Wg
 <%U<
8 HXH)}*!"'+,?f“-
Ví dụ:
.III.4. Huỷ hàng, cột và ô