Công thức và hàm Excel 97-2013 (Tài liệu cần thiết cho người sử dụng bảng tính): Phần 1

Chia sẻ: Chạy Ngay Đi | Ngày: | Loại File: PDF | Số trang:443

Thêm vào BST

Báo xấu

68
lượt xem 19
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tài liệu điện tử "Công thức và hàm Excel 97-2013" được tổng hợp dựa trên các bài viết đã đă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. Ngoài phần hướng dẫn kiến thức cơ bản về công thức và hàm, hướng dẫn sử dụng các hàm Excel 97-2013, hướng dẫn sử dụng hàm và công thức mảng, kiến thức VBA cơ bản,… tài liệu còn tập hợp hơn 100 bài tập thực hành cho các bạn nghiên cứu và có điều kiện áp dụng lý thuyết vào những tình huống cụ thể, sinh động và đa dạng. Sách được chia thành 2 phần, phần 1 là những kiến thức cơ bản về công thức và hàm, hàm trong excel 97-2013. Mời các bạn cùng tham khảo.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Công thức và hàm Excel 97-2013 (Tài liệu cần thiết cho người sử dụng bảng tính): Phần 1

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á trị trong một dãy ............................................................. 424 III.2.2. Tạo một hằng mảng từ các giá trị trong một dãy .................................................... 425 III.2.3. Chọn một dãy công thức mảng ............................................................................... 425 III.2.4. Hiệu chỉ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 dãy .......................................................................................... 426 Đếm các ô Text trong một dãy ........................................................................................ 426 Đếm các ô lỗi trong một dãy ........................................................................................... 427 Tính tổng một dãy có chứa các lỗi .................................................................................. 428 Tính tổng 3 giá trị nhỏ nhất trong một dãy ..................................................................... 428 Tính tổng 3 giá trị lớn nhất trong một dãy ...................................................................... 429 Tính tổng n giá trị lớn nhất trong một dã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 dã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 đị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