Xem mẫu
- Tổng hợp & trình bày
CÔNG CỤ TUYỆT VỜI CỦA BẠN
TRẦN THANH PHONG
Tài liệu cần thiết cho người sử dụng bảng tính
CÔNG THỨC VÀ HÀM
MICROSOFT EXCEL 97-2013
Tham 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
TP. HCM, 02/07/2014
- Các Nhà Tài Trợ Giải Pháp Excel
Ban quản trị Giải Pháp Excel
xin cảm ơn các nhà tài trợ cho diễn đàn
trong việc phát hành quyển sách này đến đông đảo bạn đọc.
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: www.bluesofts.net – Email: sales@bluesofts.net
Chuyên sản xuất và kinh doanh phần mềm doanh nghiệp.
www.giaiphapexcel.com
- Các Nhà Tài Trợ Giải Pháp Excel
NHÀ TÀI TRỢ BẠC
OVERTURE CAFÉ
Địa chỉ: 109 Trần Quốc Thảo, P.7, Q.3, TP.HCM
Điện thoại: 08.7307.8888 (đặt bàn), 0985.67.51.51 (tư vấn audio),
0908.444.111 (hợp tác)
Website: www.overturecafe.com – Email: phucsinh72@gmail.com
CÔNG TY CỔ PHẦN TIN HỌC LẠC VIỆT
Địa chỉ: 23 Nguyễn Thị Huỳnh, P.8, Q. Phú Nhuận, TP.HCM
Điện thoại: 08.3842.3333 – Fax: 08.3842.2370
Website: www.lacviet.com.vn ; www.newhorizons.edu.vn
Chuyên: dịch vụ và đào tạo CNTT
CÔNG TY TNHH KIỂM TOÁN TƯ VẤN SÁNG LẬP Á CHÂU
(FCA)
Địa chỉ: Tầng 2, 130 Nguyễn Công Trứ, Q.1, TP.HCM
Đ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: info@tamantax.com.vn
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: info@webketoan.vn
Trang thông tin và diễn đàn chuyên ngành kế toán.
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel
Sách này được Giải Pháp Excel giữ bản quyền, chúng tôi
tặng thành viên và khuyến khích việc truyền đạt giới thiệu
đến người sử dụng qua các hình thức sao chép nguyên
bản. Chúng tôi nghiêm cấm tất cả các hành vi sử dụng việc
phát tán sách để trục lợi, hoặc chỉnh sửa nội dung sách.
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | i
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
CONCATENATE ............................................................................................................. 31
DBCS (Excel 2013) ........................................................................................................... 32
DOLLAR ........................................................................................................................... 32
EXACT.............................................................................................................................. 33
FIND.................................................................................................................................. 34
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | ii
FINDB ............................................................................................................................... 36
FIXED ............................................................................................................................... 36
JIS (Excel 2013-) ............................................................................................................... 37
LEFT ................................................................................................................................. 37
LEFTB ............................................................................................................................... 38
LEN ................................................................................................................................... 41
LENB ................................................................................................................................ 42
LOWER ............................................................................................................................. 42
MID ................................................................................................................................... 43
MIDB ................................................................................................................................ 43
NUMBERVALUE (Excel 2013) ...................................................................................... 44
PHONETIC ....................................................................................................................... 44
PROPER ............................................................................................................................ 45
REPLACE ......................................................................................................................... 45
REPLACEB ...................................................................................................................... 46
REPT ................................................................................................................................. 46
RIGHT ............................................................................................................................... 48
RIGHTB ............................................................................................................................ 49
SEARCH ........................................................................................................................... 49
SEARCHB ........................................................................................................................ 50
SUBSTITUTE ................................................................................................................... 50
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
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | iii
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
HOUR................................................................................................................................ 74
ISOWEEKNUM (Excel 2013) .......................................................................................... 74
MONTH ............................................................................................................................ 75
MINUTE ........................................................................................................................... 75
NETWORKDAYS ............................................................................................................ 75
NETWORKDAYS.INTL (Excel 2010) ............................................................................ 76
NOW ................................................................................................................................. 78
SECOND ........................................................................................................................... 79
TIME ................................................................................................................................. 79
TIMEVALUE.................................................................................................................... 80
TODAY ............................................................................................................................. 80
YEAR ................................................................................................................................ 80
YEARFRAC...................................................................................................................... 81
WEEKDAY ....................................................................................................................... 81
WEEKNUM ...................................................................................................................... 85
WORKDAY ...................................................................................................................... 86
WORKDAY.INTL (Excel 2010) ...................................................................................... 86
II.4. HÀM DÒ TÌM VÀ THAM CHIẾU (LOOKUP FUNCTIONS) ....................................................... 93
ADDRESS ......................................................................................................................... 93
AREAS .............................................................................................................................. 93
CHOOSE ........................................................................................................................... 94
COLUMN.......................................................................................................................... 94
COLUMNS ....................................................................................................................... 95
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | iv
FORMULATEXT (Excel 2013) ....................................................................................... 95
HLOOKUP ........................................................................................................................ 96
HYPERLINK .................................................................................................................... 97
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
COT ................................................................................................................................. 129
COTH .............................................................................................................................. 129
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | v
CSC ................................................................................................................................. 130
CSCH .............................................................................................................................. 130
DECIMAL (Excel 2013) ................................................................................................. 131
DEGREES ....................................................................................................................... 132
EVEN .............................................................................................................................. 132
EXP ................................................................................................................................. 133
FACT ............................................................................................................................... 133
FACTDOUBLE .............................................................................................................. 134
FLOOR ............................................................................................................................ 134
FLOOR.MATH (Excel 2013) ......................................................................................... 134
FLOOR.PRECISE (Excel 2010) ..................................................................................... 135
GCD ................................................................................................................................ 136
INT .................................................................................................................................. 136
ISO.CEILING (Excel 2010) ............................................................................................ 138
LCM ................................................................................................................................ 139
LN.................................................................................................................................... 139
LOG ................................................................................................................................. 140
LOG10 ............................................................................................................................. 140
MDETERM ..................................................................................................................... 141
MINVERSE .................................................................................................................... 142
MOD................................................................................................................................ 143
MMULT .......................................................................................................................... 147
MROUND ....................................................................................................................... 148
MULTINOMIAL ............................................................................................................ 149
MUNIT (Excel 2013) ...................................................................................................... 149
ODD ................................................................................................................................ 150
PI ..................................................................................................................................... 150
POWER ........................................................................................................................... 151
PRODUCT ...................................................................................................................... 151
QUOTIENT ..................................................................................................................... 151
RADIANS ....................................................................................................................... 152
RAND.............................................................................................................................. 152
RANDBETWEEN .......................................................................................................... 153
ROMAN .......................................................................................................................... 153
ROUND ........................................................................................................................... 154
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | vi
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
SUMSQ ........................................................................................................................... 164
SUMX2MY2 ................................................................................................................... 165
SUMXPY2 ...................................................................................................................... 166
SUMXMY2 ..................................................................................................................... 166
TAN ................................................................................................................................. 166
TANH .............................................................................................................................. 166
TRUNC ........................................................................................................................... 167
II.6. HÀM THỐNG KÊ (STATISTICAL FUNCTIONS) .................................................................... 168
AVERAGE ...................................................................................................................... 168
AVERAGEA ................................................................................................................... 168
AVEDEV ........................................................................................................................ 169
AVERAGEIF .................................................................................................................. 169
AVERAGEIFS ................................................................................................................ 171
BETADIST...................................................................................................................... 172
BETA.DIST (Excel 2010) ............................................................................................... 173
BETAINV ....................................................................................................................... 173
BETA.INV (Excel 2010)................................................................................................. 174
BINOMDIST ................................................................................................................... 174
BINOM.DIST (Excel 2010) ............................................................................................ 175
BINOM.DIST.RANGE (Excel 2013) ............................................................................. 175
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | vii
BINOM.INV (Excel 2010) .............................................................................................. 176
CHIDIST ......................................................................................................................... 177
CHIINV ........................................................................................................................... 178
CHISQ.DIST (Excel 2010) ............................................................................................. 178
CHISQ.DIST.RT (Excel 2010) ....................................................................................... 179
CHISQ.INV (Excel 2010) ............................................................................................... 180
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
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | viii
F.TEST (Excel 2010) ...................................................................................................... 204
FTEST ............................................................................................................................. 204
GAMMA (Excel 2013) ................................................................................................... 205
GAMMA.DIST (Excel 2010) ......................................................................................... 205
GAMMA.INV (Excel 2010) ........................................................................................... 205
GAMMADIST ................................................................................................................ 206
GAMMAINV .................................................................................................................. 207
GAMMALN.PRECISE (Excel 2010) ............................................................................. 207
GAMMALN .................................................................................................................... 207
GEOMEAN ..................................................................................................................... 208
GROWTH ....................................................................................................................... 209
GAUSS (Excel 2013) ...................................................................................................... 210
HARMEAN ..................................................................................................................... 211
HYPGEOM.DIST (Excel 2010) ..................................................................................... 211
HYPGEOMDIST ............................................................................................................ 213
INTERCEPT ................................................................................................................... 214
KURT .............................................................................................................................. 215
LARGE............................................................................................................................ 215
LINEST ........................................................................................................................... 216
LOGEST.......................................................................................................................... 221
LOGINV.......................................................................................................................... 225
LOGNORM.DIST (Excel 2010) ..................................................................................... 226
LOGNORM.INV (Excel 2010) ....................................................................................... 227
LOGNORMDIST ............................................................................................................ 227
MAX................................................................................................................................ 227
MAXA ............................................................................................................................. 228
MEDIAN ......................................................................................................................... 228
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
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | ix
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
POISSON.DIST (Excel 2010)......................................................................................... 247
PROB .............................................................................................................................. 248
QUARTILE ..................................................................................................................... 248
QUARTILE.EXC (Excel 2010) ...................................................................................... 250
QUARTILE.INC (Excel 2010) ....................................................................................... 250
RANK.............................................................................................................................. 252
RANK.AVG (Excel 2010) .............................................................................................. 255
RANK.EQ (Excel 2010) ................................................................................................. 256
RSQ ................................................................................................................................. 256
SKEW.............................................................................................................................. 257
SKEW.P (Excel 2013)..................................................................................................... 257
SLOPE ............................................................................................................................. 258
SMALL ........................................................................................................................... 260
STANDARDIZE ............................................................................................................. 261
STDEV ............................................................................................................................ 262
STDEV.S (Excel 2010) ................................................................................................... 263
STDEVA ......................................................................................................................... 263
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | x
STDEVP .......................................................................................................................... 263
STDEVP.S (Excel 2010) ................................................................................................. 264
STDEVPA ....................................................................................................................... 264
STEYX ............................................................................................................................ 269
T.DIST (Excel 2010) ....................................................................................................... 270
T.DIST.2T (Excel 2010) ................................................................................................. 271
T.DIST.RT (Excel 2010) ................................................................................................. 271
TDIST.............................................................................................................................. 272
T.INV (Excel 2010)......................................................................................................... 273
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
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | xi
BITXOR (Excel 2013) .................................................................................................... 298
COMPLEX ...................................................................................................................... 299
CONVERT ...................................................................................................................... 300
DEC2BIN ........................................................................................................................ 302
DEC2HEX ....................................................................................................................... 303
DEC2OCT ....................................................................................................................... 303
DELTA ............................................................................................................................ 304
ERF.................................................................................................................................. 304
ERF.PRECISE (Excel 2010) ........................................................................................... 305
ERFC ............................................................................................................................... 305
ERFC.PRECISE (Excel 2010) ........................................................................................ 306
GESTEP .......................................................................................................................... 306
HEX2BIN ........................................................................................................................ 306
HEX2DEC ....................................................................................................................... 307
HEX2OCT ....................................................................................................................... 308
IMREAL.......................................................................................................................... 308
IMAGINARY.................................................................................................................. 309
IMABS ............................................................................................................................ 309
IMARGUMENT ............................................................................................................. 309
IMCONJUGATE ............................................................................................................ 310
IMCOS ............................................................................................................................ 310
IMCOSH (Excel 2013).................................................................................................... 310
IMCOT (Excel 2013) ...................................................................................................... 311
IMCSC (Excel 2013) ....................................................................................................... 312
IMCSCH (Excel 2013) .................................................................................................... 312
IMDIV ............................................................................................................................. 313
IMEXP ............................................................................................................................ 313
IMLN ............................................................................................................................... 314
IMLOG10 ........................................................................................................................ 314
IMLOG2 .......................................................................................................................... 314
IMPOWER ...................................................................................................................... 315
IMPRODUCT ................................................................................................................. 315
IMSEC (Excel 2013) ....................................................................................................... 315
IMSECH (Excel 2013) .................................................................................................... 316
IMSIN.............................................................................................................................. 317
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | xii
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
ISODD ............................................................................................................................. 331
ISREF .............................................................................................................................. 331
ISTEXT ........................................................................................................................... 332
N ...................................................................................................................................... 332
NA ................................................................................................................................... 332
SHEET (Excel 2013) ....................................................................................................... 333
SHEETS (Excel 2013) .................................................................................................... 333
TYPE ............................................................................................................................... 334
II.9. HÀM TÀI CHÍNH (FINANCIAL FUNCTIONS) ....................................................................... 335
ACCRINT ....................................................................................................................... 335
ACCRINTM .................................................................................................................... 336
AMORDEGRC ............................................................................................................... 337
AMORLINC.................................................................................................................... 338
COUPDAYBS ................................................................................................................. 339
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | xiii
COUPDAYS ................................................................................................................... 340
COUPDAYSNC .............................................................................................................. 341
COUPNCD ...................................................................................................................... 342
COUPNUM ..................................................................................................................... 342
COUPPCD ...................................................................................................................... 343
CUMIPMT ...................................................................................................................... 344
CUMPRINC .................................................................................................................... 345
DB ................................................................................................................................... 346
DDB ................................................................................................................................ 347
DISC ................................................................................................................................ 348
DOLLARDE ................................................................................................................... 349
DOLLARFR .................................................................................................................... 350
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
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | xiv
PV .................................................................................................................................... 377
RATE .............................................................................................................................. 378
RECEIVED ..................................................................................................................... 379
RRI (Excel 2013) ............................................................................................................ 380
SLN ................................................................................................................................. 381
SYD ................................................................................................................................. 382
TBILLEQ ........................................................................................................................ 382
TBILLPRICE .................................................................................................................. 383
TBILLYIELD.................................................................................................................. 384
VDB ................................................................................................................................ 385
XIRR ............................................................................................................................... 386
XNPV .............................................................................................................................. 387
YIELD ............................................................................................................................. 388
YIELDDISC .................................................................................................................... 390
YIELDMAT .................................................................................................................... 391
II.10. HÀM CƠ SỞ DỮ LIỆU (DATABASE FUNCTIONS) .............................................................. 392
II.10.1. Nói chung về Các Hàm Quản lý Cơ sở dữ liệu ...................................................... 392
II.10.2. Một số ví dụ về cách dùng Criteria để nhập điều kiện ........................................... 392
DAVERAGE ................................................................................................................... 397
DCOUNT ........................................................................................................................ 397
DCOUNTA ..................................................................................................................... 398
DGET .............................................................................................................................. 398
DMAX ............................................................................................................................. 398
DMIN .............................................................................................................................. 399
DPRODUCT ................................................................................................................... 399
DSTDEV ......................................................................................................................... 399
DSTDEVP ....................................................................................................................... 399
DSUM ............................................................................................................................. 400
DVAR.............................................................................................................................. 400
DVARP ........................................................................................................................... 400
GETPIVOTDATA .......................................................................................................... 401
II.11. HÀM ADD-INS & HÀM NGOẠI (ADD-INS & DDE FUNCTIONS) ........................................ 403
EUROCONVERT ........................................................................................................... 403
SQL.REQUEST .............................................................................................................. 406
II.12. HÀM KHỐI (CUBE FUNCTIONS)....................................................................................... 409
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | xv
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
Đếm các ô Text trong một dãy ........................................................................................ 426
Đếm các ô lỗi trong một dãy ........................................................................................... 427
Tính tổng một dãy có chứa các lỗi .................................................................................. 428
Tính tổng 3 giá trị nhỏ nhất trong một dãy ..................................................................... 428
Tính tổng 3 giá trị lớn nhất trong một dãy ...................................................................... 429
Tính tổng n giá trị lớn nhất trong một dãy ...................................................................... 429
Tính tổng theo điều kiện ................................................................................................. 429
Tính bình quân lọai bỏ giá trị 0 ....................................................................................... 430
Kiểm tra Text cần tìm có trong dãy Text không? ........................................................... 430
So sánh 2 dãy................................................................................................................... 431
Trả về vị trí của giá trị lớn nhất trong mảng .................................................................. 432
www.giaiphapexcel.com
- Công thức và hàm Excel 97-2013 Giải Pháp Excel | xvi
Tìm số dòng chứa giá trị xuất hiện thứ n trong tổng số lần xuất hiện của giá trị trong dãy
......................................................................................................................................... 433
Trả về chuỗi dài nhất trong dãy các chuỗi ...................................................................... 434
Xác định dãy chứa các giá trị hợp lệ hay không ............................................................ 434
Cộng các con số của số nguyên ...................................................................................... 435
Cộng các giá trị đã làm tròn............................................................................................ 436
Cộng các giá trị cách nhau n khoảng trong dãy .............................................................. 436
Loại bỏ các ký tự khác số khỏi chuỗi ............................................................................. 438
Xác định giá trị gần đúng nhất trong dãy với giá trị cho trước ..................................... 438
Trả về giá trị cuối cùng trong một cột ............................................................................ 439
Trả về giá trị cuối cùng trong một dòng ......................................................................... 440
Xếp hạng bằng công thức mảng ..................................................................................... 440
Tạo bảng chéo động ........................................................................................................ 441
Đếm nhiều điều kiện ....................................................................................................... 442
Đếm số phần tử duy nhất trong danh sách...................................................................... 442
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
www.giaiphapexcel.com
nguon tai.lieu . vn