1

Clb tin hc itc thi nguyn

1. khi ng v mn hnh Excel 1.1 Khi ng

Sau khi khi ng WINdows lm theo mt trong cc cch sau Kch p chut vo biu tng excel ngoi mn hnh

Nhy vo start/program/microsoft office/microsoft excel.2. Mn hnh

a Cc thnh phn ca mn hnh Excel Mn hnh ca Excel l mt ca s c phng to v trng gn ging mn hnh ca Word, bao gm cc thnh phn sau : Thanh tiu (Title bar): dng trn cng ca mn hnh, khi mi khi ng Excel ti y ghi Microsoft Excel Book1, khi ta t tn cho bng tnh, tn ny km theo phn m rng . XLS s thay th t Book1. Cc thanh Menu, Cng c, nh dng ging nh ca Word. Phn ln cc biu tng trn cc thanh ny c cng dng ngha nh trong Word, ngha ca mt s biu tng dng ring cho Excel c ghi trn trang 3. Thanh Cng thc (Formula Bar): l dng th nm ca mn hnh hin th to (a ch hoc tn) , nt hu b (, nt la chn (, ni dung d liu trong hin ti ( c khung vin chung quanh) Thanh Trng thi (Status bar): l dng cui cng hin th cc ch hot ng ca Excel : Ready: ang sn sng lm vic. Enter : ang nhp d liu hay cng thc. Pointer : ang ghi cng thc tham chiu n mt a ch. Edit : ang iu chnh d liu hay cng thc trong hin ti Thanh th tn bng tnh (Sheet tabs): l dng ngay trn thanh Trng thi, hin th tn ca cc bng tnh (khi chng cha c t tn, ti y ghi Sheet1, Sheet2, ..., Sheet16). Bn tri l cc nt chuyn ti cc bng tnh. Thanh cun Dc (Vertical Scroll Bar), cun Ngang (Horizontal Scroll Bar) ging nh trong Word. Ca s Bng tnh (Worksheet Window) l phn ln nht dng nhp d liu, tnh ton, v th nh sau :

b Cc thnh phn ca ca s Bng tnh : Ct (Column): L tp hp cc trong bng tnh theo chiu dc c nh th t bng ch ci (t tri sang phi bt u t A, B, C, ... AA, AB n IV, tng s c 256 ct). Ngoi cng bn tri l nt chn (nh du khi) ton b bng tnh. Hng (Row): L tp hp cc trong bng tnh theo chiu ngang c nh th t bng s t 1 n 16.384). (Cell): L giao ca mt ct v mt hng. a ch ca c xc nh bng ct trc, hng sau, v d C4, AB25. hin ti : L c khung vin chung quanh vi mt chm vung nh gc phi di (Mc in) hay cn gi l Con tr (sau y gi tt l con tr). To ca ny c hin th trn thanh Cng thc. Con tr bn phm: L vch ng nhp nhy biu th v tr k t s c chn vo. Con tr chut c cc dng sau : Du ( : dng a con tr v v tr no . Du ( : dng chn lnh, biu tng hoc cun bng tnh.c Dch chuyn con tr trong Bng tnh theo mt trong cc cch sau : Tr chut vo cn chuyn ti, bm nt tri. n cc phm mi tn: chuyn ti cc hng, ct ln cn. PgUp, PgDn : ln hoc xung mt mn hnh. Home : V A1. Tab : Sang phi mt mn hnh Shift + Tab : Sang tri mt mn hnh End + Home : n cui cng ca bng tnh F5, a ch , : V , v d v nhanh H22, ta n phm F5, g H22 ri n

Ch : Cc thao tc c du ( ch thc hin c khi chn lnh Tools, Option, Transition, in du tu chn Transition Navigation Keys

3. Ra khi Excel

Theo mt trong cc cch sau : Chn File, Exit hoc n Alt + F4 Nhy vo du ( trn cng gc phi ca mn hnh tr v Windows ngha cc biu tng trn thanh cng c

(ToolBar) ngha cc biu tng trn thanh nh dng (Formating) ngha cc thnh phn ca thanh cng thc(FormULA BAR)4. cc thao tc vi bng tnh 4.1 Lu (ghi) bng tnh ln a

Chn biu tng Save hoc mc File, Save. Nu y l ln u tin thc hin thao tc ny vi bng tnh, ta phi g vo tn cho bng tnh trong File Name theo quy tc t tn tp ca DOS, Excel s t gn kiu .XLS cho n. Tn bng tnh s xut hin trn thanh tiu ca ca s. Sau trong qu trnh lm vic, ta thng xuyn ghi bng tnh ln a bng cch trn m khng cn t tn cho n.

Nu ta cn lu gi bng tnh vi tn khc, chn mc File, Save As v t tn mi cho n. 4.2 M bng tnh c trn a

Chn biu tng Open hoc mc File, Open. Xut hin hp thoi Open vi danh sch cc bng tnh trong khung File Name c xp theo th t A,B,C. Ta chn tp cn thit ri chn OK. Nu bng tnh ta cn li trn a khc hoc th mc khc, ta chn a t Drives, nhy p ti th mc cn thit ca khung Directories Excel cn c cch m bng tnh khc: chn mc File, xut hin Menu dc vi danh sch 4*bng tnh mi lm gn nht pha di. Ta chn tp cn thit t danh sch ny. Danh sch cc bng tnh ny c th nhiu hn tu thuc vo ngi ci t. 4.3 ng bng tnh Trc khi chuyn sang bng tnh khc hoc lm vic khc, phi ghi tp ln a sau mi ng n bng cch chn mc File, Close. Nu qun cha ghi tp ln a, Excel s hi : Do you want to save change to . XLS ? Chn Yes ghi li, No khng ghi nhng thay i va to ra cho bng tnh.4.4 Chn thm 1 bng tnh Insert / Worksheet, hoc Nhy nt phi chut trn thanh th tn bng tnh gi Menu tt (sau y chng ta quy c gi thao tc ny l [Menu tt], chn Insert Worksheet 4.5 Xo bt 1 bng tnh Edit, Delete Sheet, hoc [Menu tt], Delete sheet

4.6 i tn bng tnh

Nhy p vo th tn (tc l vo tn bng tnh, sau y chng ta quy c gi l th tn) trn thanh th tn hoc Format / Sheet / Rename hoc [Menu tt], Rename G vo tn mi, hoc OK 4.7 Sao chp / Chuyn 1 bng tnh Cch 1: Gi Ctrl trong khi ko th th tn ti mt th tn khc (Sheet khc). Nu khng gi Ctrl bng tnh s c chuyn i. Cch 2: Edit, Move or Copy Sheet. Chn v tr t bng tnh hin ti trc bng tnh no trong khung Before Sheet. Nu nh du chn vo Creat a Copy, Excel s sao chp bng tnh ch khng chuyn n. Ch dng cch 2 nu bng tnh ngun v ch cch xa nhau (khng thy th tn ca chng cng mt lc). 4.8 Tch bng tnh

C 3 cch gip ta ng thi thy nhng phn khc nhau ca bng tnh Cch 1: Tr chut vo thanh tch cho xut hin mi tn 2 u, ko th n ti v tr cn tch. Cch 2: a con tr v v tr cn tch, chn mc Window / Split. Sau b tch chn Window / Remove Split Cch 3: a con tr v v tr cn tch, chn mc Window / Freeze Panes. Sau b tch chn Window / Unfreeze Panes 4.9 n v hin li 1 bng tnh

Chn Format, Sheet, Hide n bng tnh hin li bng tnh Format, Sheet, Unhide 4.10 Bo v bng tnh nhng thit lp v Bo v v che giu (xem trang 26) c tc dng, cn bo v bng tnh. Cch lm nh sau : Tool / Protection Chn Protect Sheet bo v bng tnh, chn Protect Workbook bo v tp bng tnh. Nu cn thit g mt khu vo vng Password. 2 ln g phi ging nhau v lu rng mt khu trong Excel phn bit ch hoa vi ch thng ! b tnh trng bo v : chn Tool / Protection / Unprotect Sheet hay Unprotect Workbook. Nu c mt khu, phi g vo, nu ng ta mi cp nht c bng tnh.4.11 Chn nhiu bng tnh

a. Lin k: Nhy chut vo th tn u, gi Shift trong khi nhy chut vo th tn cui

b. Cch nhau: Gi Ctrl trong khi ln lt nhy chut vo cc th tn c. b vic chn mt bng tnh no: gi Ctrl trong khi nhy chut vo th tn ca bng tnh 5. X l d liu trong bng tnh 5.1 Cc kiu d liu

Trong mi ch c th cha mt kiu d liu. Kiu d liu ca ph thuc vo k t u tin g vo. Cc kiu d liu trong mt c phn ra nh sau : a Dng chui (Text)

Bt u bi cc ch ci a n z hoc A n Z Nhng d liu chui dng s nh : s in thoi, s nh, m s, .v.v. khi nhp vo phi bt u bng du nhy n (') v khng c gi tr tnh ton. Theo mc nh, d liu dng chui c cn sang tri .

b Dng s (Number) Bt u bi : Cc s t 0 n 9. Cc du +, , (, *, $ (hoc mt du n v tin khc tu thuc vo vic t cc thng s quc t ca Windows). Theo mc nh, d liu dng s c cn sang phi . c Dng cng thc (Formulas)

Bt u bi cc du = hoc +. Sau khi n cng thc nhp vo ch th hin trn thanh cng thc cn kt qu ca n c th hin trong .

Nu thy : C th l do : ##### : Ct qu hp #DIV/0! : Chia cho 0 #NAME? : Thc hin php tnh vi mt bin khng xc nh (tn khng gn vi mt hay mt vng no c) #N/A : Tham chiu n mt rng hoc khng c trong danh sch #VALUE! : Sai v kiu ca ton hng (v d: ly s chia cho k t hoc ngy thng)d Dng Ngy (Date), gi (Time) Trong cch trnh by di y : DD l 2 con s ch Ngy MM l 2 con s ch Thng YY l 2 con s ch Nm Nhp theo dng MM/DD/YY hoc DD/MM/YY tu thuc vo vic t cc thng s quc t ca Windows, v d nu t thng s quc t kiu Php, ta g vo 27/09/04, trng hp dng kiu M (ngm nh) ta g vo 09/27/04. Khi nhp sai dng thc, Excel t ng chuyn sang dng chui (cn sang tri ) v ta khng th dng d liu kiu ny tnh ton.C th nhp ngy bng cch :

Nhp hm =DATE(YY,MM,DD), y l cch nhp ngy tt nht.

Sau chn Format, Cells, Number, Date v chn dng th hin ngy khung bn phi.

c bit : Ctrl + ; (du chm phy) cho Ngy h thng Ctrl + Shift + ; cho Gi h thng

Theo mc nh, d liu dng ngy thng c cn sang phi .5.2 Cc ton t trong cng thc

a Ton t s + cng tr * nhn (v d = 10*50 cho kt qu 500) / chia = 126/3 42 ^ lu tha = 5^2 25 % phn trm = 50%*600 300 Th t u tin ca cc php ton nh sau: lu tha trc ri n nhn chia v sau cng mi n cng tr. Cc php ton cng mc u tin (nh nhn chia hoc cng tr) thc hin t tri sang phi. Mun thay i th t u tin, dng cc cp ngoc trn, ton t trong cp ngoc su nht s c thc hin trc. V d: cc A1, B1, C1 cha cc s 2,3, 4, nu trong D1 g =A1+B1*C1 s c kt qu 14, g =(A1+B1)*C1 s c kt qu 20.b Ton t chui & Ni chui ="Tin "&"hoc" Tin hc

c Ton t so snh > ln hn >= ln hn hoc bng < > khc

< nh hn B1 s nhn c kt qu TRUE = A1=30) (phi biu thc trong du nhy kp )

+ Tn bt u bng ch H dng cng thc : =COUNTIF(A2:A10,H*) (phi ch v du * trong du nhy kp ) Hm COUNTIF ch m theo 1 iu kin, v d ch m s N hoc ch m s Tui t 30 tr ln. Nu cn m s N c tui t 30 tr ln (kt hp 2 iu kin) phi dng hm DCOUNTDEGREES(x) : i radian sang

V d : = DEGREES(PI( )/4) tr v 45 () EXP(x) : Hm m ex (tr v lu tha ca e). V d : = EXP(1) tr v 2.718281 INT(x) : S nguyn ln nht khng v-t qu (nh hn) x.

V d : = INT(2.789) tr v 2 = INT(-2.4) tr v -3 (v -3 nh hn -2.4) LN(x) : logarit t nhin ca x. LOG(number, base) : Logarit c s base ca number

LOG10(number) : Logarit c s 10 ca number (log10x)

MDETERM(DC) : nh thc ma trn cho bi a ch vng DC.MINVERSE(DC) : Ma trn ng-c ca DC.

MMULT(M1,M2) : Tch hai ma trn M1,M2.

MOD(n,t) : S d- ca php chia nguyn n/t. V d : =MOD(11,3) tr v 2

PI( ) : Tr v s bng 3.141592654

RAND( ) : S ngu nhin gia 0 v 1.

RANDBETWEEN(n1, n2) S nguyn ngu nhin gia hai s nguyn n1 v n2.ROUND(x,n) Lm trn s x vi chnh xc n con s th n - Nu n < 0 th x -c lm trn n ch s thp phn th n - Nu n > 0 th x -c lm trn n ch s bn tri th n ca du (chm) thp phn V d : A1 cha s 347 645.146, khi cng thc

=ROUND(A1,2) tr v 347 645.15 =ROUND(A1,1) tr v 347 645.10

=ROUND(A1,-3) tr v 348 000 (lm trn n hng nghn) SIGN(x) : Xc nh du ca mt s. Tr v 1 nu x > 0, 0 nu x = 0, -1 nu x3,6>4) tr v TRUE =AND(5>3,6=16,"Ng-i ln","Tr em") cho kt qu l Ng-i ln nu tui t 16 tr ln, l Tr em nu tui d-i 16 kim tra xem D2 cha s no dng = IF(D2>0,"S d-ng", IF(D2=0,"S khng",S m")) NOT(logic) : hm ph nh. V d : = NOT(1+1>2) tr v TRUE = NOT(1+1=2) tr v FALSEOR(logic1,logic2,...) Nhn gi tr TRUE nu mt trong cc biu thc logic1,logic2,.. l TRUE, nhn gi tr FALSE nu tt c cc biu thc l FALSE.

V d : =OR(5>3,6