am kho toàn din t cơ bn đn nâng cao
Nhiu ví d & bài tp thc hành
CÔNG THỨC VÀ HÀM
MICROSOFT EXCEL 97-2013
CÔNG C TUYT VI CA BN
Tng hp & trình bày
TRẦN THANH PHONG
TP. HCM, 02/07/2014
Tài liu cn thit cho ngưi s dng bng tính
Các Nhà Tài Tr Gii Pháp Excel
www.giaiphapexcel.com
NHÀ TÀI TR VÀNG
CỌNG TY C PHN NGHIểN CU VẨ H TR DOANH
NGHIP HẨ NI ậ K TOÁN HẨ NI
a ch tr s chính: S 04, Ngõ 322, Lê Trng Tn, Thanh Xuân, Hà Ni
in thoi: 04.3566.8036 – 0974.089.926
Website:
www.ketoanhanoi.vn
Chuyên đào to k toán thc t mi trình đ; Dch v k toán trn gói cho
các doanh nghip trên phm vi toàn quc.
CỌNG TY C PHN H THNG 1-V (1VS)
a ch: Phòng 1507, tòa nhà Thành Công, 57 Láng H, Ba ình, Hà Ni
in thoi: 04.3514.8550 – Fax: 04.3514.8551
Website:
www.1vs.vn
Chuyên cung cp các dch v và gii pháp các phn mm k toán và qun lý
CỌNG TY C PHN BLUESOFTS
a ch: S nhà 32/106, ngõ 79, ng Cu Giy,
Phng Yên Hòa, Qun Cu Giy, Hà Ni
in thoi/Fax: 04.379.17200 – Mobile: 0904.210.337
Website:

in thoi: 08.3822.0678 – Fax: 08.3821.5383
Website:
www.sanglap.com.vn ; www.fcavn.com
Chuyên cung cp dch v t vn và kim toán. NHÀ TÀI TR NG CỌNG TY TNHH DV THU TỂM AN
C: 196 Vn Kip, P.3, Q.Bình Thnh, Tp.HCM
T: 08.66837342 – 0985 88 1339
Website: www.tamantax.com.vn - Email:
Chuyên cung cp dch v k toán, đi lý thu chuyên nghip, hp pháp và
tin cy.

WEBKETOAN.VN
a ch: 196 Vn Kip, Phng 3, Qun Bình Thnh, TP.HCM
in thoi: 08.62976941 – Mobile: 0985 881 339
Website:
www.webketoan.vn – Email:
Trang thông tin và din đàn chuyên ngành k toán.

Công thc và hàm Excel 97-2013 Gii Pháp Excel

www.giaiphapexcel.com

MC LC MC LC i
LI GII THIU xx
PHN I. KIN THC C BN V CÔNG THC VÀ HÀM 1
I.1. GII THIU CÔNG THC VÀ HÀM 1
I.1.1. Công thc 1
I.1.2. Hàm 3
I.2. NHP CÔNG THC VÀ HÀM 3
I.3. THAM CHIU TRONG CÔNG THC 6
I.3.1. Tng quan v tham chiu 6
I.3.2. Các loi tham chiu trong Excel 7
I.4. TÍNH TOÁN TRONG BNG 22
I.5. CÁC LI THÔNG DNG VÀ CÁCH X LÝ LI 23
I.5.1. Các li thông dng 23
I.5.2. Tùy chn tính toán 24
I.5.3. Kim tra công thc bng Formulas Auditing 25
PHN II. HÀM TRONG EXCEL 97-2013 28
II.1. HÀM X LÝ VN BN VÀ CHUI (TEXT FUNCTIONS) 28
ASC 28
BAHTTEXT 28
CHAR 29
CLEAN 30
CODE 30

T 52
TEXT 52
TRIM 54
UNICHAR (Excel 2013) 55
UNICODE (Excel 2013) 55
UPPER 56
VALUE 56
II.2. HÀM LUN LÝ (LOGICAL FUNCTIONS) 58
AND 58
FALSE 60
IF 60
IFERROR 65
IFNA (Excel 2013) 66
NOT 67

Công thc và hàm Excel 97-2013 Gii Pháp Excel | iii

www.giaiphapexcel.com
OR 67
TRUE 68
XOR (Excel 2013) 68
II.3. HÀM NGÀY THÁNG & THI GIAN (DATE AND TIME FUNCTIONS) 69
DATE 69
DATEDIF 70
DATEVALUE 71
DAY 71
DAYS (Excel 2013) 71
DAYS360 72
EDATE 73
EOMONTH 73

INDEX 98
INDIRECT 106
LOOKUP 107
MATCH 109
OFFSET 109
ROW 111
ROWS 111
TRANSPOSE 111
VLOOKUP 112
II.5. HÀM TOÁN & LNG GIÁC (MATH AND TRIGONOMETRY FUNCTIONS) 114
ABS 114
ACOS 114
ACOSH 114
ACOT (Excel 2013) 115
ACOTH (Excel 2013) 116
AGGREGATE (Excel 2010) 116
ARABIC (Excel 2013) 119
ASIN 120
ASINH 120
ATAN 121
ATAN2 121
ATANH 122
BASE (Excel 2013) 123
CEILING 123
CEILING.MATH (Excel 2013) 124
CEILING.PRECISE (Excel 2010) 125
COMBIN 126
COMBINA 127
COS 128
COSH 128

MUNIT (Excel 2013) 149
ODD 150
PI 150
POWER 151
PRODUCT 151
QUOTIENT 151
RADIANS 152
RAND 152
RANDBETWEEN 153
ROMAN 153
ROUND 154

Công thc và hàm Excel 97-2013 Gii Pháp Excel | vi

www.giaiphapexcel.com
ROUNDDOWN 155
ROUNDUP 156
SEC (Excel 2013) 156
SECH (Excel 2013) 156
SERIESSUM 157
SIGN 158
SIN 158
SINH 158
SQRT 159
SQRTPI 159
SUBTOTAL 160
SUM 161
SUMIF 161
SUMIFS (Excel 2007+) 162
SUMPRODUCT 164

CHISQ.INV.RT (Excel 2010) 181
CHITEST 181
CHISQ.TEST (Excel 2010) 183
CONFIDENCE 183
CONFIDENCE.NORM (Excel 2010) 184
CONFIDENCE.T (Excel 2010) 185
CORREL 185
COUNT 186
COUNTA 187
COUNTBLANK 187
COUNTIF 188
COUNTIFS 190
COVAR 191
COVARIANCE.P (Excel 2010) 192
COVARIANCE.S (Excel 2010) 192
CRITBINOM 194
DEVSQ 195
EXPONDIST 195
EXPON.DIST (Excel 2010) 196
F.DIST (Excel 2010) 196
F.DIST.RT (Excel 2010) 197
FDIST 198
F.INV (Excel 2010) 199
F.INV.RT (Excel 2010) 200
FINV 200
FISHER 200
FISHERINV 201
FORECAST 201
FREQUENCY 202


MIN 229
MINA 230
MODE 230
MODE.MULT (Excel 2010) 231
MODE.SNGL (Excel 2010) 232
NEGBINOM.DIST (Excel 2010) 232
NEGBINOMDIST 234
NORM.DIST (Excel 2010) 234

Công thc và hàm Excel 97-2013 Gii Pháp Excel | ix

www.giaiphapexcel.com
NORMDIST 235
NORM.INV (Excel 2010) 236
NORMINV 236
NORM.S.DIST (Excel 2010) 236
NORMSDIST 237
NORM.S.INV (Excel 2010) 238
NORMSINV 238
PEARSON 238
PERCENTILE 239
PERCENTILE.EXC (Excel 2010) 240
PERCENTILE.INC (Excel 2010) 241
PERCENTRANK 242
PERCENTRANK.EXC (Excel 2010) 243
PERCENTRANK.INC (Excel 2010) 244
PERMUT 245
PERMUTATIONA (Excel 2013) 245
PHI (Excel 2013) 246
POISSON 247

T.INV.2T (Excel 2010) 274
T.TEST (Excel 2010) 274
TINV 275
TREND 275
TRIMMEAN 278
TTEST 279
VAR 280
VARA 281
VARP 282
VARPA 283
WEIBULL 288
WEIBULL.DIST (Excel 2010) 289
Z.TEST (Excel 2010) 289
ZTEST 289
II.7. HÀM K THUT (EGINEERING FUNCTIONS) 291
BESSELJ 291
BESSELI 292
BESSELK 292
BESSELY 292
BIN2DEC 292
BIN2HEX 293
BIN2OCT 293
BITAND (Excel 2013) 294
BITLSHIFT (Excel 2013) 295
BITOR (Excel 2013) 296
BITRSHIFT (Excel 2013) 297

Công thc và hàm Excel 97-2013 Gii Pháp Excel | xi

www.giaiphapexcel.com

IMPOWER 315
IMPRODUCT 315
IMSEC (Excel 2013) 315
IMSECH (Excel 2013) 316
IMSIN 317

Công thc và hàm Excel 97-2013 Gii Pháp Excel | xii

www.giaiphapexcel.com
IMSINH (Excel 2013) 317
IMSQRT 318
IMSUB 318
IMSUM 318
IMTAN (Excel 2013) 319
OCT2BIN 319
OCT2DEC 320
OCT2HEX 320
II.8. HÀM TRA CU THÔNG TIN (INFORMATION FUNCTIONS) 322
CELL 322
ERROR.TYPE 326
INFO 327
ISBLANK 328
ISERR 330
ISERROR 330
ISEVEN 330
ISFORMULA (Excel 2013) 330
ISLOGICAL 331
ISNA 331
ISNONTEXT 331
ISNUMBER 331

DURATION 350
EFFECT 351
FV 352
FVSCHEDULE 353
INTRATE 354
IPMT 355
IRR 356
ISPMT 357
MDURATION 358
MIRR 359
NOMINAL 360
NPER 360
NPV 361
ODDFPRICE 363
ODDLPRICE 365
ODDFYIELD 367
ODDLYIELD 368
PDURATION (Excel 2013) 369
PMT 370
PPMT 371
PRICE 373
PRICEDISC 374
PRICEMAT 375

Công thc và hàm Excel 97-2013 Gii Pháp Excel | xiv

www.giaiphapexcel.com
PV 377
RATE 378
RECEIVED 379

SQL.REQUEST 406
II.12. HÀM KHI (CUBE FUNCTIONS) 409

Công thc và hàm Excel 97-2013 Gii Pháp Excel | xv

www.giaiphapexcel.com
CUBEKPIMEMBER 409
CUBEMEMBER 410
CUBEMEMBERPROPERTY 411
CUBERANKEDMEMBER 412
CUBESET 413
CUBESETCOUNT 414
CUBEVALUE 415
II.13. HÀM WEB (WEB FUNCTIONS) 416
ENCODEURL (Excel 2013) 416
FILTERXML (Excel 2013) 416
WEBSERVICE (Excel 2013) 417
PHN III. S DNG HÀM MNG TRONG EXCEL 419
III.1. CÁC KHÁI NIM V CÔNG THC MNG 419
III.1.1. Công thc mng tr kt qu v mt vùng nhiu ô 420
III.1.2. Công thc mng tr kt qu v mt ô 421
III.2. CÁC THAO TÁC VI CÔNG THC MNG 424
III.2.1. To mt mng t các giá tr trong mt dãy 424
III.2.2. To mt hng mng t các giá tr trong mt dãy 425
III.2.3. Chn mt dãy công thc mng 425
III.2.4. Hiu chnh mt công thc mng 425
III.2.5. M rng và thu hp công thc mng nhiu ô 426
III.3. VÍ D VÀ NG DNG 426
III.3.1. Công thc mng tr kt qu v mt ô 426
Đêm các ký t trong mt dãy 426

Cng các phn t trong mng và không tính nu phn t xut hin ln th 2 tr lên. . 443
III.3.2. Công thc mng tr kt qu v nhiu ô 443
To mng s nguyên liên tc 443
Ch tr v các giá tr dng trong dãy 443
Tr v các ô Nonblank trong dãy 444
Đo th t các ô trong dãy 445
Sp xp các giá tr s trong mt dãy đng 446
Tr v danh sách các phn t duy nht trong mt dãy 447
To dng lch 448
PHN IV. MACRO, VBA VÀ MT S HÀM T TO 449
IV.1. CÁC KIU D LIU CA VBA 449
IV.1.1. Bin d liu 449
IV.1.2. Cú pháp đnh ngha tên bin 449
IV.1.3. t tên bin theo "ký hiu Hungarian" 450
IV.1.4. Các lu ý v bin 454
IV.2. TM VC TRUY XUT, THI GIAN SNG CA BIN & TH TC 455
IV.2.1. Tm vc truy xut bin 455
IV.2.2. Thi gian sng ca bin 458
IV.2.3. Lu ý v khai báo bin Static: 459
IV.2.4. Tm vc th tc/ hàm 459

Công thc và hàm Excel 97-2013 Gii Pháp Excel | xvii

www.giaiphapexcel.com
IV.2.5. S dng hng gi nh 460
IV.3. HNG DN TRUYN THAM S TRONG VBA (BYVAL & BYREF) 461
IV.4. GHI VÀ THC HIN MACRO 464
IV.4.1. Macro là gì? 464
IV.4.2. Ghi macro trong trng hp s dng tham chiu đa ch ô tuyt đi 465
IV.4.3. Chy macro khi s dng bng điu khin macro (Macro dialog box) 467

Công thc và hàm Excel 97-2013 Gii Pháp Excel | xviii

www.giaiphapexcel.com
IV.10.2. S ch mc (Index numbers) 504
IV.10.3. S hàng và s ct (rows and columns) 504
IV.10.4. t tên cho vùng (named ranges) 505
IV.10.5. Nhiu vùng (Multiple Ranges) 506
IV.10.6. Offset Cells 507
IV.10.7. Kiu tham chiu R1C1 508
IV.11. CU TRÚC IU KHIN 509
IV.11.1. Câu lnh IF 509
IV.11.2. S dng Select Case 511
IV.11.3. Xây dng các điu kin 512
IV.12. HP THOI TRONG VBA 513
IV.12.1. Hp thông báo (Message box) 513
IV.12.2. Phng thc InputBox (Inputbox Method) 516
IV.13. HÀNH NG LP (LOOP) 518
IV.13.1. Do Loop 518
IV.13.2. Do While Loop 519
IV.13.3. Do Loop While 519
IV.13.4. Do Until Loop 520
IV.13.5. For Next 520
IV.13.6. For Each Next 521
IV.13.7. Lnh thoát (Exit) 521
IV.13.8. Vòng lp lng 522
IV.14. CÁC HÀM T NH NGHA 523
Hàm tr v tên sheet hin hành 523
Hàm tr v tên workbook hin hành 524
Hàm tr v đng dn đn workbook hin hành 524
Hàm tr v tên đy đ ca workbook 524

VI.1.2. nh dng bng cách s dng Ribbon 558
VI.1.3. nh dng tùy bin 558
VI.2. CH MC HÀM 562
Công thc và hàm Excel 97-2013 Gii Pháp Excel | xx

www.giaiphapexcel.com
LI GII THIU Các bn đc thân mn!
Phn mm bng tính s mt th gii Microsoft Excel có mt th vin hàm phong phú và đa dng,
đáp ng đc hu ht các nhu cu tính toán ca ngi s dng thuc rt nhiu lnh vc khác nhau.
Vic nh tt c các hàm trong Excel và cách vn dng chúng là mt vic "rt khó khn hoc thm
chí là không th nh ht".
Tài liu đin t "Công thc và hàm Excel 97-2013" ca din đàn "Gii Pháp Excel -
www.giaiphapexcel.com" đc tng hp da trên các bài vit đã đng trên din đàn Gii Pháp
Excel và b sung các ni dung liên quan, vi s h thng hoá theo mt cu trúc nht quán giúp
ngi đc d tip thu và tra cu.

hn  các phiên bn sau.
Mi ý kin đóng góp xin gi v đa ch:
Chân thành cm n,
TP. HCM, ngày 29 tháng 06 nm 2014
Trn Thanh Phong
i din BQT Gii Pháp Excel
Liên h hp tác:
Website: www.giaiphapexcel.com
E-mail:
in thoi: 848-6679 2225
TD: 0909.191.958 gp Bình