BI GINGNG DNG EXCEL TRONG A CHT CNG TRNH GII THIUTrong lnh vc a cht cng trnh - a k thut (CCT - KT), chng ta s gp mt s bi ton nh tnh ton, xy dng cc biu th nghim trong phng (th nghim ct, nn mt trc, thnh phn ht, nn c kt, ct ba trc,...), x l thng k kt qu th nghim trong phng, v biu th nghim hin trng (xuyn tnh, nn tnh nn, nn tnh cc, th nghim nn ngang, ct cnh,...), bng tng hp ch tiu c l t , chng trnh tnh ton nn mng,... Vi s pht trin mnh m ca cng ngh thng tin, nhng bi ton trn c th c gii quyt mt cch nhanh chng, chnh xc bng nhng cng c ng dng. l phn mm Excel. Phn mm Excel c bng tnh linh hot cng vi biu sn c gip chng ta gii quyt nhiu bi ton v th nghim trong phng, ngoi tri, tnh ton nn mng,... Excel cn xy dng sn cc dng tng quan cng vi phng trnh tng quan, phc v cho cng vic xy dng mi tng quan gia cc i lng. Cc hm thng k sn c trong Excel gip chng ta xc nh cc c trng thng k ca t . Tuy nhin, nu ch s dng n thun cc cng thc trong Excel th nhiu bi ton khng th gii quyt mt cch hon chnh c. c th gii quyt c cc bi ton trn mt cch hon ton t ng, chng ta phi s dng ngn ng lp trnh ng dng Visual Basic for Applications (VBA) trong Excel. VBA gip chng ta lp cc hm tu bin v th tc gii quyt mt vn no . Hin nay, sinh vin nhng kho i cng c trang b kin thc v Visual Basic, l nn tng pht trin ngn ng VBA. VBA l ngn ng d giao tip, thn thin vi ngi s dng. Khng ch ring vi ngnh k thut, VBA c ng dng rt hiu qu i vi ngnh kinh t, ti chnh, k ton, xy dng,...

BI 1: M UPhn mm Excel ch yu dng tnh ton, xy dng cc biu th nghim t trong phng, hin trng,... y l chng trnh c bng tnh mnh nht hin nay, cho php thc hin nhiu cng vic khc nhau. Mc ch ca bi ging l trang b cho sinh vin nhng kin thc c bn v phn mm Excel, gip sinh vin c th s dng ngay khi vit n mn hc, n tt nghip v tham gia sn xut sau ny. Chng trnh hc l 2003 v hin nay c Excel 2010. V c bn, t Excel 97 n 2003 khng c nhiu thay i. Excel 2007 nh du s thay i mnh m, th hin s pht trin vt bc ca Excel. Cc iu khin menu cc phin bn trc c thay i bng Ribbon tin li hn, bng tnh c kch thc ln hn, cc chc nng phong ph, a dng, dung lng file gim i nhiu,... Phm vi bi ging ny cp n Excel 2003, mt s chc nng c thay i trong cc phin bn khc nhau.

Hnh 1.1: Biu tng Excel 2007 trn DestopTitler bar Menu barStandard Toolbar Formatting ToolbarColumn heading

Formular bar

Active cellWorksheet

Row heading

Sheet name

Hnh 1.2: Ca s Excel

1. KHI NG EXCEL Nhy kp vo biu tng Excel trn mn hnh (hnh 1.1), ca s phn mm Excel s hin ra (hnh 1.2). 2. CA S NG DNG CA EXCEL Ca s Excel bao gm cc thnh phn sau:a. Thanh tiu (Title bar)

Biu tng Excel v tn file (Book1.xls).b. Thc n lnh (Menu bar)

Bao gm cc menu lnh, trong mi menu c nhiu cc menu con (hnh 1.3). Hnh 1.3: Thanh Menu Hnh 1.4: Thanh cng c chun (Standard Toolbar) Hnh 1.5: Thanh nh dng (Formatting Toolbar)c. Cc thanh cng c (Toolbars)

Toolbar l cc thanh cng c, trn mi thanh ny c nhiu nt lnh, gip bn thc hin nhanh cc chc nng lnh thay v thc hin bng lnh n. Excel ngm nh hin th sn hai thanh cng c Standard v Formatting v chng thng c s dng ti (hnh 1.2, 1.4, 1.5). Khi lm vic, nu mun dng thanh no th cho hin th ln, nu khng th giu i gii phng khng gian trn mn hnh.d. Thanh cng thc (Formular bar)

Thanh cng thc c 2 phn: phn bn tri hin th a ch hin hnh hoc tn khi (Name box); phn bn phi hin th ni dung ca , gip cho vic xem d liu nhp trong , hoc xem li cng thc v cho php iu chnh.e. Ca s Workbook

L ca s lm vic chnh. Trong 1 workbook c ti a 255 Sheet, mi sheet c xem nh l mt bng tnh dng cha d liu, biu , hnh nh,... Mc nhin trong workbook c sn 3 sheet, bn c th thay i s sheet c sn mc nh ny khi to workbook mi bng cch chn: Menu\ Tools\ Options\ General, khai bo s sheet trong Sheets in new workbook. Ngoi ra, trong General bn c th quy nh font ch, kch c ch (Size) mc nh cho ca s workbook. Worksheet l t liu s cp lu tr v lm vic. Mt worksheet gm nhiu c thit lp bi cc ct v cc hng, l thnh phn ca worksheet v c gi l bng tnh. Cc ct ca bng tnh c tnh bng ch, t A, B, C,... n IV. Cc hng ca bng tnh c tnh t 1 n 16384. d hnh dung, chng ta coi workbook ging nh quyn sch, worksheet ging nh cc trang sch, cc ging nh ch trong trang sch . Giao gia ct v hng gi l (cell), mi mt c mt a ch ring bit thng qua v . V d: A1, B4, AB16. Tn ca cc sheet hin hnh trn thanh tab, nm pha di ca s workbook. di chuyn gia cc sheet ta bm ln tn ca sheet hoc dng t hp phm Ctrl+ PageDown, Ctrl+ PageUp. Tn ca sheet hin hnh s c sng v m hn. Gc phi v pha di l thanh trng thi, th hin v tr mn hnh trong bng tnh.

3. QUN L WORKBOOK

Hnh 1.6: Ca s lu workbook cha c tn.a. Lu workbook

Khi bn lu workbook ln u tin, bn phi t tn file v a ch bn mun lu. Ri sau mi ln lu workbook, Excel s cp nht vi nhng thay i sau cng nht. Lu workbook mi cha c tn: - Chn Menu\ File\ Save hoc n nt Save trn thanh Standard, ca s Save as hin ra (hnh 1.6). - Chn a v th mc (folder) trong hp Save in. Bn c th to folder mi lu workbook bng cch bm Create New folder ri g tn folder mi, OK. Trong hp File name, g tn workbook. Excel t ng chn ui cho file l xls.

Hnh 1.7: Lu workbook t ng To mt bn sao cho workbook (lu workbook vi mt tn mi): - M workbook mun to bn sao cho n hay tn mi. - Chn Menu\ File\ Save As. Trong hp File name, g tn workbook mi v ng dn. - Bm nt Save. Lu workbook t ng: - Chn Menu\ Tools\ Options\ Save. - nh du chn Save AutoRecover info every, nhp khong thi gian bng pht Excel t ng lu (hnh 1.7). - Chn cc la chn khc nu bn mun.

Ghi ch: Nu lnh AutoSave khng c trn thc n Tools, bn phi ci t chc nng ny bng cch chn Menu\ Tools\ Add-Ins, nh du chn AutoSave trong danh sch Add-Ins available.b. To workbook mi

- Chn Menu\ File\ New hoc n nt New hin ra. - Chn General, chn biu tng workbook.c. M file workbook c sn trong a

trn thanh Standard, ca s New

- Chn Menu\ File\ Open hoc n nt Open trn thanh Standard, ca s Look in hin ra. - Chn a, folder cha file cn m, chn file cn m ri bm Open.d. ng ca s workbook ang lm vic

- Chn Menu\ File\ Close hoc Ctrl+ F4, ca s Microsoft Excel hin ra (hnh 1.8). - Chn Yes lu file, No khng lu file, Cancel tr v.

Hnh 1.8: Hp thoi khi thot ra khi Excel

BI 2: NHP V HIU CHNH D LIUKhi tin hnh nhp, iu chnh d liu hay trnh by, x l bng tnh, thng thng phi xc nh hay khi (vng) m bn mun tc ng ln n trc. Chnh v vy m cn bit cch chn nh th no cho nhanh v ng, mc u tin sau y s cp n vn . 1. CHN a. Chn n

- Cch 1: Bm chut trn cn chn. - Cch 2: Dng cc phm di chuyn di chuyn n cn chn.b. Chn khi phm vi lin k nhau

- Cch 1: gi chut trn 1 gc ca khi cn chn (con tr chut c dng ti B2) ko n gc i din ca khi (D4) v nh chut (hnh 2.1). - Cch 2: Chn 1 gc, gi Shift v bm trn gc i din hoc g cc phm di chuyn ti. Hnh 2.1: Chn khi lin k nhau bng cch gi v ko chutc. Chn khi khng lin nhau

Chn hay khi th nht, gi phm Ctrl v chn hay khi khc.d. Chn ton b cc trn hng hoc ct

- Chn hng : bm trn tn tiu hng (Row heading). - Chn ct : bm trn tn tiu ct (Column heading).e. Chn ton b cc trn sheet

Bm trn giao gia 2 thanh tiu ct v hng (hnh 2.1), hoc Ctrl+A. 2. CHN CC SHEET TRONG WORKBOOK ChnMt sheet Hai hay nhiu sheet k nhau Cc sheet khng k nhau Tt c cc sheet trong workbook

Cch thc hinBm trn tn ca tab sheet Bm trn tn ca tab sheet th nht, gi phm Shift v bm trn tn ca tab sheet th hai. Bm trn tn ca tab sheet th nht, gi phm Ctrl v bm trn tn ca tab sheet khc. Bm nt phi chut trn tn ca mt tab sheet, chn Select All Sheets trn thc n tt.

3. CC KIU D LIU TRONG EXCEL D liu nhp c th l nhn hay chui k t, s hoc cng thc.a. Nhn, chui k t (Label/ String)

Chui l t hp ca s, khong trng, ch v cc k t khng phi l s. Chui t ng c canh l tri trong . Khi chui nhp di hn rng ct th n trn qua bn phi nu bn phi khng c d liu. Nu bn phi c d liu th ch c mt phn chui va ng rng ca ct c hin th. Nh vy, thy ton b d liu trong , bn cn phi iu chnh li rng ct bng cch gi v ko chut trn bin bn phi ca tiu ct sang phi, hoc bm p t ng iu chnh rng ct da vo chui di nht cha trong cc trn ct (hnh 2.2).

b. S (Number)

S l t hp ca s, khng c c khong trng, ch v cc k t khng phi l s, tr k t "." (quy nh Vit Nam l ","). V tr s mc nhin nm bn phi v c dng s t nhin (General number). Khi s nhp qu ln hoc qu nh th s c dng s m (scientific).

iu chnh

Hnh 2.2: iu chnh rng ca ctc. Cng thc (Formular)

Trong Excel, bn c th t lp cng thc hoc s dng cc hm c lp sn. Cc hm c lp sn x l s liu, chui, tnh ton trong ti chnh, k ton,... Cc hm ny s c hc trong tit 4. C php ca cng thc phn bit vi cc d liu khc, cng thc c bt u t k t "=", tip theo l cc ton hng v ton t. Ton hng c th l cc gi tr khng thay i c (gi tr hng s), a ch hay phm vi khi , chui k t, tn khi hoc hm. Cc ton t tnh ton trong cng thc - Ton t s hc: Ton t+ * / ^ % ()

TnCng Tr/ m Nhn Chia Lu tha Du phn trm Ngoc

V d=2+2 =4-2 =4*2 =12/4 =4^2 =7*2% =3-(8/2)

Kt qu4 2 8 3 16 0.14 -1

- Ton t so snh (so snh hai gi tr v kt qu l True hoc False): Ton t= > < >= 9 =7=9 =7