Post on 02-Nov-2015
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 1/12
Nhng Hm c bn trong Excel
HM C BN TRONG EXCEL
Microsoft Excel, l mt phn mm ng dng rt hay. N l mt cng c thng k, tnh ton v qun l
nhn s v ti chnh. V l mt cng c h tr c lc cho gii k ton. Chnh v vy n c p dng v
s l rng ri trong cc cng ty, x nghip hin nay.
Do gii hn ca quyn sch ny, cng nh cho ng vi ci tiu ca n. Chnh v vy ti ch
cp n mt s phn quan trng, trc ht ti xin gii thiu 60 hm c bn ca Excel:
TN NGHA C PHP
Cng thc lun c bt u bng du bng (=)
Len n s k t Len(a ch)
Left Ct s k t bt u t bn tri Left(a ch, s k t ct)
Right Ct s k t bt u t bn phi Right(a ch, s k t ct)
Mid Ct s k t ty Mid(a ch ,s k t bt u, s k
t kt thc)
Upper Chuyn k t thnh k t hoa Upper(a ch)
Lower Chuyn k t thnh k t thng Lower(a ch)
Proper Chuyn k t u ca t thnh hoa Proper(a ch)
Trim Xa khong trng v ngha Trim(a ch)
Find Tm v tr k t Find( k t tm, a ch, v tr th
my)
Search Tm v tr k t Cng dng v cp php ging hm
find, nhng khng phn bit ch
hoa, thng
Substitute Tm v thay th k t Substitute(a ch, k t tm, k t
thay, v tr th my)
If Hm logic, tnh theo iu kin If(iu kin, gi tr ng, gi tr sai)
And Kt ni nhiu iu kin cng tha And(iu kin 1, iu kin 2)
Or Kt ni nhiu iu kin nhng trong ch
cn mt tha
Or(iu kin 1, iu kin 2)
Average Tnh trung bnh cng Average(a ch)
Max Tm s ln nht Max(a ch)
Min Tm s nh nht Min(a ch)
Count m gi tr s Count(a ch)
CountA m k t v s Counta(a ch)
Countblank m rng Countblank(a ch)
Countif m theo iu kin Countif(a ch cha iu kin, iu
kin)
Sum Tnh tng Sum(a ch)
Sumif Tnh tng theo iu kin Sumif(vng iu kin, iu kin,
vng gi tr tnh)
Sumproduct Tnh tng ca tch Sumproduct((a ch 1), (a ch))
Large Tm gi tr s ln th n Large(a ch, ln th my)
Small Tm gi tr s nh th n Small(a ch, nh th my)
Rank Xp hng(trong kiu = 0 l gim dn,
kiu = 1 l tng dn)
Rank(a ch, kiu)
Text nh dng biu thc(>100 0,00,0.0,0.00)
(
Text(gi tr, kiu nh dng)
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 2/12
Value Chuyn gi tr s dng chui thnh s
tnh ton
Value(a ch)
Round Lm trn s(trong n v lm trn =
sm lm trn n phn nguyn, bng s
dng ln trn n phn thp phn)
Round(gi tr, n v lm trn)
Vd:A1=37496.2748
Round(A1,2)=37496.27
Round(A1,-1)=37500
Int Ly phn nguyn nh hn gn nht Int(gi tr)
Trunc Ly phn nguyn Trunc(gi tr) vd:trunc(10/3)=3
Mod Ly phn d Mod(s b chia, s chia)
Sqrt Tnh cn bc hai Sqrt(gi tr) vd:sqrt(4)=2
Now Tr v ngy, gi hin hnh ca hu thng Now()
Today Tr v ngy hin hnh Today()
Weekday Tr v th ca ngy trong tun(trong 1
l ch nht)
Weekday(a ch)
Day Tr v gi tr ngy Day(today())
Month Tr v gi tr thng Month(today())
Year Tr v nm Year(today())
Data Tr v ngy thng nm Data(nm, thng, ngy)
Datavalue Tr ngy dng chui thnh biu thc ngy Datavalue(a ch)
Hour Tr v gi Hour(now())
Minute Tr v pht Minute(now())
Second Tr v giy Second(now())
Timevalue Tr gi dng chui thnh biu thc gi Timevalue(a ch)
Time Tr v gi pht giy Time(a ch)
vd:(10.15.20)=10:15:20
Isna Kin tra li #N/A(trong gi tr tr v
true l li, false l khng li)
Isna(hm tham chiu)
Isnumber Kim tra d liu c phi l kiu s
khng(trong gi tr tr v true l kiu s,
false l khng )
Isnumber(tham chiu)
Isblank Kin tra d liu trong c trng
khng(trong ga tr tr v true l khng
c d liu, false c d liu)
Isblank(a ch)
Iserror Kim tra cng thc c sai khng(trong
gi tr tr v true l sai, false l khng sai)
Iserror(a ch)
Code Tr k t v gi tr trong bng m Ascii Code(k t)
Char Tr k s v gi tr trong bn m Ascii Char(k s)
HM THAM CHIU
Ch : vng tham chiu lun lun c tuyt i($)
Hlookup Tham chiu theo dng t bng ph
(trong kiu = 0 l so snh chnh xc,
kiu =1 l gn chnh xc)
Hlookup(gi tr tham chiu, vng
tham chiu, s dng gi tr ly, kiu)
Vlookup Tham chiu theo ct t bn Phu (trong
kiu = 0 l so snh chnh xc, kiu =1 l gn
chnh xc)
Vlookup(gi tr tham chiu, vng
tham chiu, s ct gi tr ly, kiu)
Match Tham chiu mt chiu(tron vng tm
kim phi l mng mt chiu, v kiu = 0 l
tm kim chnh xc, kiu =1 l gn chnh xc
Match(gi tr tm kim, vng bng tm
kim, kiu)
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 3/12
Index Tham chiu ct dng t bng ph(nu s
th t dng = 0, th tr v gi tr l nguyn
ct(mng ct) v ngc li.
Index(vng bn ph, s th t dng,
s th t ct)
HM C S D LIUTn hm(vng bng c s d liu, s th t ct tnh, vng bng iu kin)
Trong vng bng CSDL gm a ch v c tiu .
STT ct tnh l s th t ca ct c tnh ton(ct u ca vng CSDL l 1).
Vng iu kin c xy dng ti v tr khc trn bng tnh theo yu cu sau:
+ Tiu bng iu kin trng vi bng CSDL khi iu kin l gi tr copy
+ Tiu bng iu kin khc vi bng CSDL khi iu kin l cng thc v lun tr v kt qu l True
hoc False.
Trong trng hp phi s dng nhiu iu kin th:
+ And S c t cng dng
+ Or - S c t lch dng
Dsum Tnh tng gi tr da trn vng iu kin Dsum(vng bn CSDL, s th t ct
tnh, vng iu kin)
Daverage Tnh trung bnh cng da trn vng iu
kin
Daverage(vng bn CSDL, s th t
ct tnh, vng iu kin)
Dmax Tm s ln nht da trn vng iu kin Dmax(vng bn CSDL, s th t ct
tnh, vng iu kin)
Dmin Tm s nh nht da trn vng iu kin Dmin(vng bn CSDL, s th t ct
tnh, vng iu kin)
Dcount m gi tr s da trn vng iu kin Dcount(vng bn CSDL, s th t
ct tnh, vng iu kin)
DcountA m k t s v k t da trn vng iu
kin
Dcounta(vng bn CSDL, s th t
ct tnh, vng iu kin)
m s t trong mt (cell) ca Excel 2003
Bn dng cng thc sau m s t trong mt :
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)
BNG M LI
M LI NGHA
#VALUE Khng tnh c, tr sai
#N/A Gia tr tham chiu khng tn ti
#NAME? Khng nhn c tn hm
#NUM! Tr s khng hp l
#DIV/0 Php chia cho khng
#REF! Khng tham chiu n c
#NUL! Rng
SAO CHP:
Edit/Paste Special
All: Sao chp tt c cc yu t lin quan n .
Formulas: Sao chp cng thc.
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 4/12
Values: Sao chp gi tr.
Formats: Sao chp nh dng.
Comments: Sao chp ch thch trong nu c.
Validation: Sao chp biu thc quy nh tnh hp l ca d liu nhp.
Operation-Sao chp c s dng ton t
None: khng s dng ton t khi Sao chp
Add: Sao chp cng(kt qu= ch+ ngun)
Subtract: Sao chp tr (kt qu = ch+ ngun)
Multiply: Sao chp nhn(kt qu = ch* ngun)
Divide: Sao chp chia(kt qu = ch/ ngun)
q Skip blanks: b qua nhng rng(khng cha d liu)
q Transpose: Sao chp chuyn v(ct thnh dng, v ngc li)
Paste Link: Sao chp c lin kt, nu d liu thay i, d liu ch cng thay i theo.
A CH:
L cch gi phn bit hoc khi gia cc v khi khc trong bng tnh.
a ch : l s kt hp gia k hiu ct v dng
a ch khi: l s kt hp gia u v cui, ngn cch nhau bi du 2 chm(:).
PHN LOI:
C 3 loi:
a ch tng i: khi thay i kt qu th a ch ca cc tham chiu cng thay i theo.
a ch tuyt i: khi thay i kt qu th a ch ca tham chiu khng thay i theo.
Vd:$Column $row
a ch hn hp: l s kt hp gia a ch tng i v tuyt i.
Vd:$Column row
CC KIU D LIU:
Kiu chui: Bao gm cc k t :A-Z, a-z, 0-9
c im: Lun c canh tri, khi s dng kiu chui trong cng thc lun c t trong dy nhy
kp().
Cc php ton: &
Kiu s: Bao gm 0-9
c im: lun c canh phi trong
Cc php ton: +, -, *, /, ^, %.
Kiu l lun: L kiu logic, thng c dng lm iu kin trong cc hm so snh.
c im: Lun tr v 1 trong 2 gi tr l: True hoc False
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 5/12
CC PHP TON:
PHP TON TNG NG
Equel to =
Greater than >
Less than =
Less than or Equel to
Kiu ngy gi:Date, Time:
Kiu ngy: Date
c im: Lun c canh phi trong , khng c php cng ngy
Cc php ton:
Ngy-Ngy = S
Ngy S = Ngy
Kiu gi: Time
c im: lun c canh phi trong , khng c php tr cho s
Cc php ton:
Gi Gi = Gi
Kiu cng thc: Bao gm cc ton t, ton hm, a ch, hm
c im: Lun bt u bng du bng( = ), mc u tin trong cng thc ging nh trong ton
hc: nhn chia trc, cng tr sau. Trong ngoc trc, ngoi ngoc sau.
Cng thc mng: l lp cng thc tnh kt qu cho khi ct hoc khi dng. Khi xy dng cng
thc mng lun c kt thc bi :Ctrl+Shift+Enter
Vd: Tng s mt hng tivi:
=Countif(A2:A5),tv.* = count(if(left(A2:A5,2)=TV,1,)
Vd2: tnh tng thnh tin cc mt hng TV c s lng
=Sum(if(left(A2:A5,2)=TV, IF(B2
NH DNG:
Format/CellsTab Number:
General: nh dng chung cho kiu d liu
Number: nh dng cho cc d liu s
+Decimal Place(2) quy nh my s l
+Use 100 Suparator(,) phn cch hng ngn
Currency: Quy nh kiu tin t
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 6/12
Date: La chn cch biu din d liu dng ngy
Time: La chn cch biu din d liu dng ngy
Percentage:Quy nh cch biu din d liu kiu phn trm(%)
Scientifie: Biu din d liu s kiu khoa hc.
Vd:1.000.000=1*106=1E+6
Vd2: -1.000.000= -1*106= -1E+6
Fraction: Biu din theo kiu phn s: 0.5 - 2/4
Zipcode: Biu din 1 m s
Custom: Ty nh dng cho cc kiu d liu.
Ch : 0 i din cho 1 k t s, nu khng c th c thay th bi s 0.
# - i din cho 1 k t s, nu khng c th c b trng.
NH DNG IU KIN:
L nh dng d liu m bng tnh cn c vo iu kin:
T khi vng d liu, chn:
Add: thm iu kin
Delete: xa iu kin
Format: nh dng bng tnh cn c vo iu kin
Trong Condtion1 c hai la chn:
1-Cell Value is : nh dng theo gi tr trong
2-Formula is : nh dng theo cng thc
Ch : thit lp cng thc lun c du =, lun tr v kt qu True hoc False
S dng a ch u tin ca khi thit lp cng thc.
Trong trng hp cn c vo iu kin 1 dng(mu tin)th c nh a ch ct cho cc a ch s dng
trong cng thc.
SP XP:
T khi bng d liu, chn:
Sort by: chn kha
Ascending: xp tng dn
Descending:xp gim dn
Trong mc My list has: xp hay khng xp dng tiu
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 7/12
PHN NHM CSDL:
L vic phn chia CSDL ra thnh tng nhm v la chn tnh ton trn :
Sp xp bng CSDL theo ct c phn nhm, t khi bng CSDL, chn:
Data/Subtotals
At each change in: chn ct phn nhm
Use function : chn hm tnh ton
Add subtotal to: chn nhng ct no mun thc hin thng k, tnh ton trn
q Replace current subtotals: thay th Subtotal hin hnh trc
q Page break between groups: ngt trang cho tng nhm
q Summary below data: to dng tng cng cho tt c cc nhm
Remove All: xa tt c cc SubTotal hin c.
KIM TRA NHP D LIU TRONG EXCELL:
T khi vng bng tnh cn kim tra trc khi nhp liu, chn
Data/Validation
Tab Settings: thit lp vic kim tra, trong mc Allow:
An ny value: kiu d liu bt k
Whole number: kiu s
Decimal: kiu thp phn
Data: kiu ngy
Time: kiu gi
List: kiu danh sch
Custom: thit lp cng thc kim tra vng d liu cho cc kiu ty : trong cng thc lun tr v gi
tr True hoc False, cng thc khng c di qu 500 k t.
Vd:thit lp cng thc kim tra d liu nhp cho ct m nhn vin nh sau:
Chiu di 5 k t, 2 k t u l s th t, 2 k t tip l m phn ban, k t cui l A hoc B
Cng thc:
=And(Len(A2)=5, Code(Left(A2,1) >= 48, Code(Mid(A2,2,1)) >= 48,
Isna(Match(Value(Mid(A2,3,1)),A11:A13,0))=False, Or(Code(Right(A2,1)=65, Code(Right(A2,1)=66))
Data: la chn php ton
q Ignore blank: b qua nhng trng
q Apply these changes to all other cells with the same settings: chn, nu mun tc ng cc thay
i, nu c, n nhng phm vi khc nu chng c cng kiu quy nh.
Clear All: xa tt c cc quy nh
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 8/12
Tab Input Message: xy dng ToolTip hng dn cho a ch, trong :
Title: xy dng tiu
Input message: xy dng ni dung
q Show input message when cell is selected: bt/tt thng bo v iu kin quy nh khi c chn.
Tab Error Alert: xy dng ca s bo li, trong :
Style: biu tng thng bo
Title: xy dng tiu
Error message: xy dng ni dung bo li
q Show error alert affter invalid data is entered: bt/tt thng bo li ngay sao khi d liu c nhp
vo.
TRCH LC TRN CSDL:
L vic lc ra cc mu tin t bng CSDL da trn iu kin,trong :
Trch lc t ng ngay trn bng CSDL:
T khi, chn:
Data/Filter/Auto filter, nhp mi tn chn kiu lc, trong :
All: lit k tt c cc d liu
Top 10: lit k 10 dng u tin
Custom: thit lp iu kin ty , trong :
Begin with: bt u vi
End with: kt thc vi
Not begien with: khng bt u vi
Not end with: khng bt u vi
Contain: cha ng gi tr
Not contain: khng cha ng gi tr
Ch : i vi trng hp c t 2 iu kin tr ln hoc vi cc ct khc nhau th chc nng ny khng
thc hin c
Trch lc ra mt bng ring: da v bng iu kin c thit lp ti v tr khc trn bng tnh
Thit lp bng iu kin: ging nh bng iu kin ca hm CSDL, cp bc trc.
T khi bng CSDL, chn:
Data/Filter/Advanced filter
Filter the list, in-place: trch lc ngay trn bng CSDL
copy to another location: trch lc d liu ra v tr khc
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 9/12
List range: a ch CSDL
Criteria range: a ch v tr cn copy ra
q Unique records only: loi b nhng mu tin trng lp
TRCH LC CSDL CH VI MT S CT QUY NH:
Xy dng bng kt qu vi mt s ct (fiell) quy inh, c tn fiell trng vi tn fiell ca bng CSDL.
X dng chc nng Advanced nh bnh thng, nhng Copy to:c ly a ch ca bng kt qu .
Ch :ta nn ly d 1 vi dng trng qu trnh lc c y .
THNG K TNG HP:
Dng thng k tng hp t 1 hoc nhiu bng CSDL thnh 1 bng tng hp da trn cc bng d liu:
To cu trc ca bng tng hp, c tiu trng vi tiu ca cc ct tng hp
T khi bng tng hp v cc dng trng lu tr kt qu, chn:
Data/Consolidate
Reference: a ch ca cc bng CSDL c tham chiu, ct u tin ca vng tham chiu phi trng vi
ct u tin ca bng tng hp
All references: lu cha tt c cc vng d liu c tham chiu thng qua Add
Chn Top row, Left column
Browse:dng m tp tin CSDL khc ca bng excell
q Create links to source data: to lin kt gia bng tng hp v bng tham chiu khi khc Sheft hoc
khc tp tin.
TH
L cch biu din d liu di dng hnh nh.
c thit lp qua 4 bc:Insert/Chart
Bc 1:Chart Type: La chn kiu th
Bc 2: Data Source: Xc nh vng d liu v th(nu t khi trc ri th b qua bc ny.
Bc 3: Chart Option: Xc nh thng s cho th:
Trong Tab Titles, vi nhng quy nh sau:
Char title: Tn th
Category(x)axis:Tn trc honh
Value(y)axis: Tn trc tung
Tab Axes, vi nhng quy nh sau:
q Category(x)axis: Bt/Tt gi tr trc honh
q Value(y)axis: Bt/Tt gi tr trc tung
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 10/12
Automatic: T ng nhn dng nhm nhn
Category: Nhm nhn quy nh mc Category Axis Label
Time-scale: Biu th nhm nhn theo dng ngy
Tab Gridlines, vi nhng quy nh sau:
Category(x)axis: Bt/Tt/Mt ng li ngang
Value(y)axis: Bt/Tt/Mt ng li dc
Tab Legend, vi nhng quy nh sau:
q Show Legend: Hin/n bng ch thch
Mc Placement: Quy nh v tr t bng ch thch
Tab Data Labels, vi nhng quy nh sau:
Mc data labels: Cc thng s v nhn ca d liu, trong
None: khng dng nhn
Show value: hin th gi tr lm nhn
Show percent: hin th gi tr dng phn trm
Show label: hin th hin th nhm nhn trn trc x ln th
Show label and percent: hin th nhm nhn v gi tr dng phn trm
Show bubble size: hin th kch thc bt ca th
q Legen key next to label: Bt/Tt cc k hiu ca chui d liu trong hp ch thch.
Tab Data Table: Hin/n bng d liu
Bc 4: Chart Location: Xc nh v tr t th, trong :
As new sheet: Nm trn 1 Sheet ring
As Object in: Nm trn Sheet hin hnh
NH DNG:
Do th c to ra bi kt hp nhiu thnh phn, nn nh dng th l nh dng trn tng thnh
phn. Nhp chut phi vo thnh phn, chn Format
IN N TRONG EXCELL
Xem Tng Quat Trang In:
File/Print Preview, xut hin hp thoi, trong :
Next: Hin th trang in k tip(nu c)
Previous: Hin th trang in trc
Zoom: Phng to/Thu nh
Print: Chuyn sang hp thoi Print
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 11/12
Setup: Chuyn sang hp thoi Page Setup
Margin: Bt/Tt cc ng bin ca l v ct
Page Break Preview: xem cc du ngt trang
In Ra My In:
File/Print:
Selection: In phn hin c nh du
Active sheet: In sheet hin hnh
Entire workbook: In tt c cc sheet trn book
Properties: xem cc thng s k thut v my in.
Print to file: in bng tnh thnh mt file di dng nh phn.
Number of copies: Xc nh s bn in ra bao nhiu
Collate: Bt/Tt vic chn in theo tng b hay tng trang.
Preview: Tr li ca s Preview
Quy nh Cc Thng S V Trang In:
File/Page Setup:
Tab Page, bao gm:
Portrait: in dc
Landscape: in ngang
Adjust to: xc nh t l phng/thu so vi kch thc chun, hoc
Fit to: in va vn vi bao nhiu trang do ta ch nh.
Paper Size: chn kh giy(A3=297 x 420, A4=210 x 297, A5 = 148.5 x 210).
Print Quality: Chn phn gii khi in(ph thuc vo tng my in c th).
First page number: chn cch nh s cho trang u tin(nn chn Auto)
Print: tr v ca s bt u in.
Print preview: xem li trang in
Options: xc nh thm 1 s thng s khc.
Tab Margins, bao gm:
Quy nh v thit lp nhng thng s v l ca giy, trong :
q Horizontally: canh gia theo chiu ngang ca trang in.
q Vertically: canh gia theo chiu dc ca trang in.
Tab Header/Footer, bao gm:
Cc thit t cho tiu trang in
22/08/2013 Phong Trn - ...kin thc khng th mua m c...: Nhng Hm c bn trong Excel
phontran.blogspot.com/2008/07/hm-excel-s-u-mi-hm-c-bn-trong-excel.html 12/12
Tab Sheet, bao gm:
Print area: xc nh vng in bng a ch.
Prin titles: chn nhng dng trn (Rows to repeat at top)hoc ct tri (Columns to repeat at left)no s
c in lp li mi u trang mi.
Prin: ch nh cc yu t trn bng tnh c c in ra hay khng, trong :
q Gridlines: bt/tt in ng li.
q Back and white: In trng en
q Draft quality: In nhp
q Row and column headings: bt/tt in heading(dng/ct a ch)
Comments: bt/tt in ch thch trong (nu c)
Page order: chn th t in: in xung ri sang tri(Down, then over), hoc in sang tri ri xung(Over,
then down).