Microsoft excel - Công thức và hàm

607 trang vanle 2920

Download

Bạn đang xem 20 trang mẫu của tài liệu "Microsoft excel - Công thức và hàm", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

microsoft_excel_cong_thuc_va_ham.pdf

Nội dung text: Microsoft excel - Công thức và hàm

Tổng hợp & trình bày TRẦN THANH PHONG CÔNG CỤ TUYỆT VỜI CỦA BẠN 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̉ tnhấ 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
Công thức và hàm Excel 97-2013 Giải Pháp Excel | xvii IV.2.5. Sử dụng hằng gợi nhớ 460 IV.3. HƯỚNG DẪN TRUYỀN THAM SỐ TRONG VBA (BYVAL & BYREF) 461 IV.4. GHI VÀ THỰC HIỆN MACRO 464 IV.4.1. Macro là gì? 464 IV.4.2. Ghi macro trong trường hợp sử dụng tham chiếu địa chỉ ô tuyệt đối 465 IV.4.3. Chạy macro khi sử dụng bảng điều khiển macro (Macro dialog box) 467 IV.4.4. Ghi macro trong trường hợp sử dụng tham chiếu địa chỉ ô tương đối 468 IV.4.5. Dùng phím tắt để thực hiện một macro (shortcut key) 469 IV.5. CÁCH THỰC HIỆN MỘT MACRO ĐƠN GIẢN 469 IV.5.1. Thực hiện macro từ một đối tượng đồ hoạ trong worksheet 469 IV.5.2. Chạy macro từ nút lệnh trên thanh công cụ 470 IV.5.3. Chạy macro từ l ệnh trong menu của Excel 473 IV.5.4. Thay đổi lựa chọn trong macro 476 IV.5.5. Sửa Macro 476 IV.6. NGỮ PHÁP VB (VISUAL BASIC GRAMMAR) 478 IV.6.1. Các đối tượng (Objects) 478 IV.6.2. Các phương thức (Methods) 480 IV.6.3. Các thuộc tính (Properties) 480 IV.6.4. Các biến (Variables) 481 IV.6.5. Sử dụng mảng (Array) 484 IV.6.6. Sử Dụng With - End With 485 IV.7. SỬ DỤNG GIÚP ĐỠ HELP 486 IV.7.1. Tại thời điểm đang viết code 486 IV.7.2. Sử dụng hộp thoại giúp đỡ với chủ đề cụ thể 486 IV.7.3. Trình duyệt đối tượng 488 IV.7.4. Các tập tin ví dụ 491 IV.8. MỘT SỐ CHỨC NĂNG ĐIỀU KHIỂN TRONG VBA 491 IV.8.1. Sử dụng Options 493 IV.8.2. Sử dụng VBAProject 495 IV.8.3. Sử dụng chức năng Security 497 IV.9. VIẾT MACRO 499 IV.9.1. Viết macro 499 IV.9.2. Sửa chữa lỗi 501 IV.10. THAM CHIẾU ĐẾN Ô VÀ VÙNG 502 IV.10.1. Tham chiếu kiểu A1 502 www.giaiphapexcel.com
Công thức và hàm Excel 97-2013 Giải Pháp Excel | xviii IV.10.2. Số chỉ mục (Index numbers) 504 IV.10.3. Số hàng và số cột (rows and columns) 504 IV.10.4. Đặt tên cho vùng (named ranges) 505 IV.10.5. Nhiều vùng (Multiple Ranges) 506 IV.10.6. Offset Cells 507 IV.10.7. Kiểu tham chiếu R1C1 508 IV.11. CẤU TRÚC ĐIỀU KHIỂN 509 IV.11.1. Câu lệnh IF 509 IV.11.2. Sử dụng Select Case 511 IV.11.3. Xây dựng các điều kiện 512 IV.12. HỘP THOẠI TRONG VBA 513 IV.12.1. Hộp thông báo (Message box) 513 IV.12.2. Phương thức InputBox (Inputbox Method) 516 IV.13. HÀNH ĐỘNG LẶP (LOOP) 518 IV.13.1. Do Loop 518 IV.13.2. Do While Loop 519 IV.13.3. Do Loop While 519 IV.13.4. Do Until Loop 520 IV.13.5. For Next 520 IV.13.6. For Each Next 521 IV.13.7. Lệnh thoát (Exit) 521 IV.13.8. Vòng lặp lồng 522 IV.14. CÁC HÀM TỰ ĐỊNH NGHĨA 523 Hàm trả về tên sheet hiện hành 523 Hàm trả về tên workbook hiện hành 524 Hàm trả về đường dẫn đến workbook hiện hành 524 Hàm trả về tên đầy đủ của workbook 524 Hàm trả về User hiện tại của Windows hoặc Excel 525 Hàm trả về công thức trong một ô (cell) xác định 526 Hàm kiểm tra xem một ô có chứa công thức hay không 526 Hàm kiểm tra xem một ô (cell) trong bảng tính có đang áp dụng tính năng Data Validation hay không 527 Hàm kiểm tra xem một ô (cell) có chứa chú thích (comment) hay không 528 Hàm tính tổng các ô tô nền theo màu xác định 528 Hàm tính tổng các ô chứa Font chữ theo màu xác định 529 www.giaiphapexcel.com
Công thức và hàm Excel 97-2013 Giải Pháp Excel | xix Hàm xoá bỏ các số 0 đứng trước giá trị số khỏi một ô xác định 530 Hàm xoá bỏ các ký tự chữ khỏi một ô xác định 530 Hàm xoá bỏ tất cả ký tự số khỏi một ô xác định 531 Hàm xác định vị trí của ký tự số đầu tiên trong chuỗi cho trước trong ô 532 Hàm tính tổng tất cả chữ số của con số cho trước trong ô (cell) 533 Hàm tính tổng tất cả chữ số của các con số trong vùng (range) 533 Hàm kiểm tra một worksheet có đang được bảo vệ (protected) hay không 534 Hạm tạo AutoText của riêng bạn 535 PHẦN V. THỰC HÀNH, TẢI VỀ & THAM KHẢO 536 V.1. THỰC HÀNH VỀ HÀM SUBTOTAL 536 V.2. THỰC HÀNH OFFSET 542 V.2.1. Giới thiệu hàm Offset 542 V.2.2. Offset và Validation List 544 V.3. NAME ĐỌC SỐ THÀNH CHỮ 548 V.4. CÁC BÀI TẬP THỰC HÀNH 556 PHẦN VI. PHỤ LỤC 557 VI.1. TÙY BIẾN CHO ĐỊNH DẠNG SỐ LIỆU 557 VI.1.1. Định dạng số liệu tự động 557 VI.1.2. Định dạng bằng cách sử dụng Ribbon 558 VI.1.3. Định dạng tùy biến 558 VI.2. CHỈ MỤC HÀM 562 www.giaiphapexcel.com
Công thức và hàm Excel 97-2013 Giải Pháp Excel | xx LỜI GIỚI THIỆU Các bạn đọc thân mến! Phần mềm bảng tính số một thế giới Microsoft Excel có một thư viện hàm phong phú và đa dạng, đáp ứng được hầu hết các nhu cầu tính toán của người sử dụng thuộc rất nhiều lĩnh vực khác nhau. Việc nhớ tất cả các hàm trong Excel và cách vận dụng chúng là một việc "rất khó khăn hoặc thậm chí là không thể nhớ hết". Tài liệu điện tử "Công thức và hàm Excel 97-2013" của diễn đàn "Giải Pháp Excel - www.giaiphapexcel.com" được tổng hợp dựa trên các bài viết đ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. Bên cạnh đó, tài liệu còn tập hợp nhiều hàm người dùng (UDF) hữu ích để bạn đọc có cơ hội tiếp xúc và thực hành với Macro và VBA trong Excel thông qua các hàm này. Để đọc tài liệu được hiệu quả, các bạn nên đọc trước phần I của tài liệu để có các khái niệm cơ bản về hàm, công thức, cách sử dụng địa chỉ tương đối và tuyệt đối, tên, Sau đó, các bạn nên vào phần V để tải về các bài tập và thực hành trên các bài toán cụ thể này. Trong quá trình làm bài, bạn gặp một hàm khó hiểu, bạn hãy tra cứu và đọc cách sử dụng hàm đó ở phần II. Các phần III và IV cung cấp các kiến thức nâng cao về hàm và công thức mảng cũng như các khái niệm cơ bản về Macro và VBA, đây là những bước đi đầu tiên giúp bạn có thể viết thêm các hàm người dùng phục vụ cho công việc của mình. Chúng tôi tin rằng, tài liệu này thật sự là một cẩm nang tra cứu cần thiết cho tất cả những ai đang làm việc với bảng tính Excel. www.giaiphapexcel.com
Công thức và hàm Excel 97-2013 Giải Pháp Excel | xxi Chúng tôi cũng xin chân thành cảm ơn tất cả thành viên của Giải Pháp Excel đã có những bài viết hữu ích vì cộng đồng Giải Pháp Excel và người dùng Internet. Tài liệu này được xây dựng dựa trên các bài viết của Trần Thanh Phong, Bùi Nguyễn Triệu Tường, Phan Tự Hướng, hoangdanh282vn, ndu96081631, xin chân thành cảm ơn các thành viên diễn đàn có bài viết sử dụng trong tài liệu này. Cuối cùng, dù chúng tôi đã rất cố gắng trong quá trình tổng hợp tài liệu này, nhưng cũng khó tránh khỏi những thiếu sót. Chúng tôi rất mong bạn đọc đóng góp ý kiến để tài liệu ngày càng hoàn thiện hơn ở các phiên bản sau. Mọi ý kiến đóng góp xin gửi về địa chỉ: ttphong@giaiphapexcel.com Chân thành cảm ơn, Liên hệ hợp tác: TP. HCM, ngày 29 tháng 06 năm 2014 Website: www.giaiphapexcel.com E-mail: info@giaiphapexcel.com Trần Thanh Phong Điện thoại: 848-6679 2225 Đại diện BQT Giải Pháp Excel ĐTDĐ: 0909.191.958 gặp Bình www.giaiphapexcel.com
Giới Thiệu Công Thức Và Hàm Giải Pháp Excel | 1 Phần I KIẾN THỨC CƠ BẢN VỀ CÔNG THỨC VÀ HÀM I.1. GIỚI THIỆU CÔNG THỨC VÀ HÀM I.1.1. Công thức Công thức giúp bảng tính hữu ích hơn ấr t nhiều, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại nhiều lần. Vậy công thức có các thành phần gì? Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu bằng (=) và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. Dấu bằng Trị số Toán tử Hàm Tham chiếu = 10000 – IF(A1>=1000,80%*A1,A1) Các thành phần của công thức www.giaiphapexcel.com
Giới Thiệu Công Thức Và Hàm Giải Pháp Excel | 2 Các toán tử trong công thức Toán tử Chức năng Ví dụ Kết quả + Cộng =3+3 3 cộng 3 là 6 - Trừ =45-4 45 trừ 4 còn 41 * Nhân =150*.05 150 nhân 0.50 thành 7.5 / Chia =3/3 3 chia 3 là 1 ^ Lũy thừa =2^4 2 lũy thừa 4 thành 16 =16^(1/4) Lấy căn bậc 4 của 16 thành 2 & Nối chuỗi =”Lê” & “Thanh” Nối chuỗi “Lê” và “Thanh” lại thành “Lê Thanh” = Bằng =A1=B1 Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE > Lớn hơn =A1>B1 Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE = Lớn hơn hoặc bằng =A1>=B1 Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE Khác =A1 = <> www.giaiphapexcel.com
Nhập Công Thức Và Hàm Giải Pháp Excel | 3 I.1.2. Hàm Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việc sử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tự chọn. Ví dụ: =Rand() hàm không có đối số =IF(A1>=5,”Đạt”,”Rớt”) hàm 3 đối số =PMT(10%,4,1000,,1 ) hàm nhiều đối số và đối số tùy chọn Các nhóm hàm chính trong Excel Add-In và DDE: Call, Registed.ID, Hàm lấy dữ liệu từ SSAS: Cubeset, Cubevalue, (có từ Excel 2007+) Hàm dữ liệu: Dmin, Dmax, Dcount, Hàm ngày và thời gian: Time, Now, Date, . Hàm kỹ thuật: Dec2Bin, Dec2Hex, Dec2Oct, Hàm tài chính: Npv, Pv, Fv, Rate, Hàm thông tin: Cell, Thông tin, IsNa, Hàm luận lý: If, And, Or, Hàm tham chiếu và tìm kiếm: Choose, Vlookup, OffSet, Hàm toán và lượng giác: Log, Mmult, Round, Hàm thống kê: Stdev, Var, CountIf, Hàm văn bản: Asc, Find, Text, Hàm Web: EncodeUrl, FilterXML, WebService Công thức mới trong Excel 2007: thêm vào 14 hàm mới so với Excel 2003- như là Averageif, Averageifs, Call, Countifs, Cubekpimember, Cubemember, Cubememberproperty, Cuberankedmember, Cubeset, Cubesetcount, Cubevalue, Iferror, Register.Id, Sumifs Công thức mới trong Excel 2010 : nâng cấp và thay đổi giải thuận tính toán của rất nhiều hàm nhằm tăng độ chính xác của hàm. Một số hàm mới như Networkingdays.Int, Rank.Ave, Percentile.Exc, Confidence.T, T.Dist, T.Dist.Rt và T.Dist.2t Công thức mới trong Excel 2013: có rất nhiều (51) hàm mới thêm vào như XOR, IFNA, WebService, I.2. NHẬP CÔNG THỨC VÀ HÀM Nhập công thức trong Excel rất đơn giản, muốn nhập công thức vào ô nào bạn chỉ việc nhập dấu bằng (=) và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. Bạn có thể nhìn vào thanh Formula để thấy được trọn công thức. Một điều hết sức lưu ý khi làm việc www.giaiphapexcel.com
Nhập Công Thức Và Hàm Giải Pháp Excel | 4 trên bảng tính là tránh nhập trực tiếp các con số, giá trị vào công thức mà bạn nên dùng đến tham chiếu. Minh họa dùng tham chiếu trong hàm Trong ví dụ trên, ở đối số thứ nhất của hàm NPV chúng ta không nhập trực tiếp suất chiết tính 10% vào hàm mà nên tham chiếu đến địa chỉ ô chứa nó là I2, vì nếu lãi suất có thay đổi thì ta chỉ cần nhập giá trị mới vào ô I2 thì chúng ta sẽ thu được kết quả NPV mới ngay không cần phải chỉnh sửa lại công thức. Giả sử các ô C2:G2 được đặt tên là DongTien, và ô I2 đặt tên là LaiSuat thì trong quá trình nhập công thức bạn có thể làm như sau: B1. Tại ô B4 nhập vào =NPV( B2. Nhấn F3, cửa sổ Paste Name hiện ra B3. Chọn LaiSuat và nhấn OK B4. Nhập dấu phẩy (,) và gõ F3 B5. Chọn DongTien và nhấn OK B6. Nhập dấu đóng ngoặc rồi nhập dấu + B7. Nhấp chuột vào ô B2 B8. Nhấn phím Enter Nhấn F3 Chèn tên vùng vào công thức www.giaiphapexcel.com
Nhập Công Thức Và Hàm Giải Pháp Excel | 5 Ghi chú: Nhấn phím F3 trong quá trình nhập công thức khi muốn chèn tên vùng đã đặt. Một trong những cách dễ dàng nhất để sử dụng hàm trong Excel là sử dụng thư viện hàm. Khi bạn muốn sử dụng hàm nào chỉ việc vào thanh Ribbon chọn nhóm Formulas Function Library chọn nhóm hàm chọn hàm cần sử dụng. Ngoài ra bạn có thể nhấn vào nút Insert Function để gọi hộp thoại Insert Function một cách nhanh chóng và khi cần tìm hiểu về hàm này bạn chỉ cần nhấn vào Help on this function. Hộp thoại Insert Function www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 6 I.3. THAM CHIẾU TRONG CÔNG THỨC I.3.1. Tổng quan về tham chiếu Các tham chiếu sử dụng trong công thức giúp cho chúng ta khỏi tốn công sửa chữa các công thức khi các giá trị tính toán có sự thay đổi. Có 3 loại tham chiếu sau: Tham chiếu địa chỉ tương đối: Các dòng và cột tham chiếu sẽ thay đổi khi chúng ta sao chép hoặc di dời công thức đến vị trí khác một lượng tương ứng với số dòng và số cột mà ta di dời. Ví dụ A5:B7, C4 Tham chiếu địa chỉ tuyệt đối: Các dòng và cột tham chiếu không thay đổi khi ta di dời hay sao chép công thức. Ví dụ $A$5:$B$7, $C$4 Tham chiếu hỗn hợp: Phối hợp tham chiếu địa chỉ tương đối và tuyệt đối. Ví dụ A$5 nghĩa là cột A tương đối và dòng 5 tuyệt đối. Ghi chú: Dấu $ trước thứ tự cột là cố định cột và trước thứ tự dòng là cố định dòng Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định dòng hoặc cột Ví dụ: Tính cột Thành tiền bằng cách lấy cột Số lượng nhân với cột Giá. Đổi giá trị cột Thành tiền sang VND bằng cách lấy cột Thành tiền nhân với Tỷ giá. Tính tổng các cột Thành tiền và cột Tiền VND. Minh họa địa chỉ tương đối và tuyệt đối B1. Tại ô D2 nhập vào =B2*C2 và Enter. Sau đó quét chọn cả vùng D2:D14 và gõ tổ hợp phím . Vào các ô D3, D4 D14 ta sẽ thấy công thức các dòng tự động được thay đổi tương ứng với khoảng cách so với ô D2. Trường hợp này chúng ta dùng địa chỉ tương đối của B2*C2 là vì chúng ta muốn khi sao chép công thức xuống phía dưới thì địa chỉ các ô tính toán sẽ tự động thay đổi theo. www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 7 B2. Tại ô E2 nhập vào =D2*B$17 và Enter, sau đó chép công thức xuống các ô E3:E14. Chúng ta cần cố định dòng 17 trong địa chỉ tỷ giá B17 vì ta muốn khi sao công thức xuống thì các công thức sao chép vẫn tham chiếu đến ô B17 để tính toán. B3. Tại ô D15 nhập vào =Sum(D2:D14) và chép công thức sang ô E15. I.3.2. Các loại tham chiếu trong Excel Một tham chiếu xác định một ô hay vùng trên bảng tính giúp Excel biết được vị trí chứa dữ liệu mà bạn muốn sử dụng trong công thức. Sử dụng tham chiếu trong công thức giúp một công thức có thể truy cập được đến dữ liệu chứa tại nhiều nơi khác nhau của bảng tính hay ngược lại một giá trị trong một ô có thể được sử dụng bởi nhiều công thức khác nhau. Ngoài ra, Excel cho phép tham chiếu đến các ô thuộc các sheet khác trong cùng workbook hoặc khác workbook. Các tham chiếu đến các ô trong workbook khác được gọi là các liên kết (link). Kiểu tham chiếu A1 Excel mặc định sử dụng kiểu tham chiếu A1, nghĩa là các cột được đặt tên theo các mẫu chữ cái (A đến Z và các tổ hợp chữ cái). Trong Excel 2003- các cột được đặt tên từ A đến IV (256 cột) và các dòng được đánh số từ 1 đến 65,536, còn trong Excel 2007+ thì các cột được đặt tên từ A đến XFD (16,384 cột) và cách dòng được đánh số từ 1 đến 1,048,576. Các ký tự và các con số này gọi là các tiêu đề cột và dòng (row and column headings). Để tham chiếu đến một ô, ta chỉ cần nhập vào ký tự đại diện cho cột và số thứ tự của dòng. Ví dụ tham khảo đến ô C4 thì ô này có ký tự cột là C và số dòng là 4. Tham chiếu đến Địa chỉ Ô tại cột A thuộc dòng 10 A10 Vùng địa chỉ trong cột A từ dòng 10 đến dòng 20 A10:A20 Vùng địa chỉ thuộc dòng 15 từ cột B đến cột E B15:E15 Toàn bộ các ô thuộc dòng 5 5:5 Toàn bộ các ô từ dòng 5 đến dòng 10 5:10 Toàn bộ các ô thuộc cột H H:H Toàn bộ các ô từ cột H đến cột J H:J Vùng địa chỉ từ cột A đến E và từ dòng 10 đến 20 A10:E20 www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 8 Tham khảo đến worksheet khác: Xét ví dụ sau, hàm AVERAGE tính toán bình quân các giá trị thuộc vùng B1:B10 trên Worksheet Marketing trong cùng Workbook. Tên worksheet Địa chỉ vùng tham chiếu trên worksheet =AVERAGE(Marketing!B1:B10) Dấu phân cách tên Sheet tham chiếu và vùng tham chiếu Ghi chú: Tên worksheet và dấu chấm than (!) được đặt trước vùng địa chỉ tham chiếu. Tham chiếu đến địa chỉ ở worksheet khác nhưng cùng workbook thì có dạng: Tên_sheet!Địa_chỉ_ô Ví dụ: =A2*Sheet2!A2 =A2*’Thong so’!B4 Khi tên sheet có chứa khoảng trắng thì để trong cặp nháy đơn ‘ ’ Tham chiếu đến địa chỉ trong workbook khác thì có dạng: [Tên_Workbook]Tên_sheet!Địa_chỉ_ô Ví dụ: =A2*[Bai2.xlsx]Sheet3!A4 =A2*’[Bai tap 2.xlsx]Sheet3’!A4 Khi tên Sheet hay Workbook có chứa khoản trắng để trong cặp nháy đơn ‘ ’ =A2*’C:\Tai lieu\[Bai tap 2.xlsx]Sheet3’!A4 Khi tham chiếu đến workbook khác mà workbook đó không mở =A2*’\\DataServer\Excel\[Bai tap 2.xlsx]Sheet3’!A4 Khi tham chiếu đến tập tin Excel chia sẽ trên máy chủ trong mạng nội bộ. Tham chiếu vòng Trong một số trường hợp nhập công thức, bạn nhận được hộp thoại thông báo tham chiếu vòng (Circular Reference) vì trong công thức có sử dụng giá trị trong ô mà bạn đang nhập công thức. Ví dụ: Tại ô A3 bạn nhập vào công thức =A1+A2+A3 Cảnh báo tham chiếu vòng trong Excel 2013 www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 9 Nếu các phép tính của bạn yêu cầu phải tham chiếu vòng thì bạn cần phải bật tính năng cho phép tính lặp lên. Cách thực hiện: Excel 2003: Vào Tools → Options → Calculation → chọn Iteration Excel 2007: Nhấn vào nút Office → Excel Options → Formulas → Calculation options → chọn Enable iterative calculation. Excel 2013: Vào File → Options → Formulas → Calculation options → chọn Enable iterative calculation. www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 10 o Maximun Iterations: Số lần tính lặp tối đa cho công thức mà Excel sẽ thực hiện. o Maximun Change: Sự lệch đổi tối đa cho phép của kết quả công thức của các lần tính. Đặt số càng nhỏ thì kết quả càng chính xác nhưng Excel cần nhiều thời gian để tính hơn. Ví dụ: Ô A1 chứa số 1, ô A2 chứa số 2, ô A3 nhập vào công thức =A1+A2+A3 và với thiết lập cho phép tính lặp 3 lần như trên thì kết quả ô A3 lần đầu tiên sau khi nhập công thức là 9. Nếu nhấn F9 thì kết quả sẽ là 18, Kết quả lần đầu trả về sau khi nhập công thức. Tham chiếu tương đối, tham chiếu tuyệt đối và tham chiếu hỗn hợp Tham chiếu tương đối (Relative references) Tham chiếu tương đối trong công thức (ví dụ như A1) dựa vào vị trí tương đối của địa chỉ ô chứa công thức và địa chỉ của ô tham chiếu đến. Khi vị trí của ô chứa công thức thay đổi thì địa chỉ ô tham chiếu đến cũng thay đổi tương ứng. Khi ta chép công thức sang các dòng hay cột khác, thì địa chỉ tham chiếu cũng tự động thay đổi tương ứng. Excel mặc định công thức nhập vào dùng tham chiếu tương đối. Ví dụ: Khi ta chép công thức tại ô B2 chứa tham chiếu tương đối đến ô A1 (=A1) xuống ô B3 thì khi đó công thức trong ô B3 tự động thay đổi tham chiếu đến ô A2 (=A2). Tham chiếu tuyệt đối (Absolute references) Một ô có công thức tham chiếu tuyệt đối đến một ô nào đó (ví dụ $A$1) khi đó dù cho ô chứa công thức bị di chuyển hay sao chép đến nơi khác thì công thức vẫn luôn luôn tham chiếu đến ô đó (A1). Excel mặc định công thức nhập vào dùng tham chiếu tương đối, do vậy ta cần chuyển sang tham chiếu tuyệt đối khi cần thiết (bằng cách đặt dấu $ trước các tiêu đề dòng và cột muốn cố định – phím tắt là F4). www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 11 Ví dụ: Nếu ta chép một công thức (tại ô B2 xuốn ô B3) có tham chiếu tuyệt đối đến ô =$A$1 thì công thức trong ô B3 vẫn là =$A$1. Tham chiếu hỗn hộp (Mixed references) Tham chiếu hỗn hợp là dạng tham chiếu kết hợp 2 loại tham chiếu tương đối và tuyệt đối, trong tham chiếu hỗn hợp chỉ có cột hoặc dòng được cố định (tuyệt đối). Tham chiếu cố định cột có dạng như $A1, $B1, và tham chiếu cố định dòng có dạng A$1, B$1, . Khi vị trí của ô chứa công thức thay đổi thì phần địa chỉ tương đối (trong tham chiếu hỗn hợp) sẽ thay đổi theo còn phần địa chỉ tuyệt đối (trong tham chiếu hỗn hợp) sẽ không thay đổi. Ví dụ: Khi ta chép công thức có chứa tham chiếu hỗn hợp (=A$1) trong ô B2 sang ô C3 thì công thức trong ô C3 thay đổi thành =B$1 (Địa chỉ tham chiếu trong công thức thay đổi 1 đơn vị từ cột A → B do không cố định cột, nhưng hàng 1 vẫn giữ nguyên do hàng đã bị cố định công thức trong C3 là B$1). Ví dụ: Tính cột “Thành tiền” bằng cách lấy cột “Số lượng” nhân với cột “Giá” và thống kê doanh số bán cho mỗi khách hàng theo tháng. Tại ô E2 ta nhập vào công thức =C2*D2 với địa chỉ các ô tham chiếu là tương đối vì ta muốn khi sao chép công thức từ ô E2 xuống các ô dưới thì địa chỉ các ô tham chiếu sẽ thay đổi theo. o Công thức trong ô E3 sẽ là =C3*D3 o Công thức trong ô E4 sẽ là =C4*D4 o o Công thức trong ô E21 sẽ là =C21*D21 www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 12 Công thức trong ô B27 là =SUMIFS($E$2:$E$21,$A$2:$A$21,$A27,$B$2:$B$21,B$26), công thức này sau đó được sao chép qua các ô bên phải và các ô bên dưới nó. o Ta cần cố định tuyệt đối các vùng Thành tiền ($E$2:$E$21), vùng ra điều kiện Tháng ($A$2:$A$21), vùng ra điều kiện Khách hàng ($B$2:$B$21) vì ta muốn công thức luôn tha, chiếu đến các vùng này khi sao chép sang các ô khác. o Ta chỉ cần cố định cột ô điều kiện Tháng ($A1) do ta muốn tham chiếu dòng thay đổi khi sao chép công thức xuống các ô bên dưới nhưng tham chiếu cột không đổi khi ta sao chép công thức sáng các ô bên phải. o Ta chỉ cần cố định dòng ô điều kiện Khách hàng (B$26) do ta muốn tham chiếu dòng không thay đổi khi sao chép công thức xuống các ô bên dưới nhưng tham chiếu cột thay đổi khi ta sao chép công thức sáng các ô bên phải. www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 13 Kiểu tham chiếu 3-D Khi chúng ta muốn phân tích dữ liệu có vùng địa chỉ giống nhau ở nhiều worksheet trong cùng workbook thì khi đó cần đến kiểu tham chiếu 3-D. Tham chiếu 3-D có dạng như ví dụ sau =SUM(Sheet2:Sheet6!A1), nghĩa là tính tổng các ô A1 nằm trong nhiều sheet (từ Sheet2 đến Sheet6). Tham chiếu 3-D có thể dùng để tham chiếu đến các ô nằm trên các sheet khác, (có thể đặt tên (Name) cho tham chiếu 3-D), sau đó có thể dùng các hàm sau để tính toán: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, và VARPA. Tham chiếu 3-D không thể dùng trong công thức mảng (công thức kết thúc bằng lệnh Ctrl+Shift+Enter). Tham chiếu 3-D không thể dùng các toán tử số học (+,-,*,/,^, &) , toán tử so sánh (= > = , toán tử logic (and, or, not ). Tham chiếu 3-D sẽ thay đổi như thế nào khi thực hiện các lệnh move, copy, insert, hay delete Các ví dụ sau minh hoạ sự thay đổi của tham chiếu 3-D khi bạn thực hiện các lệnh move, copy, insert, hay delete trong worksheet. Ví dụ ta có công thức =SUM(Sheet2:Sheet4!A1:B5) để tính tổng các ô A1:B5 từ worksheet 2 đến worksheet 4. Kết quả ô A2 là 60 Insert hay copy Khi chèn hay sao chép các sheet nằm từ Sheet2 (sheet đầu - endpoint) đến Sheet4 (sheet cuối – endpoint), Excel sẽ bao gồm luôn tất cả giá trị của các ô A1:B5 trên sheet mới thêm vào kết quả tính toán. www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 14 Ví dụ: Chèn Sheet7 vào sau Sheet2 với các giá trị trong vùng A1:B5 như hình sau: Kết quả ô A2 trong Sheet TongHop =SUM(Sheet2:Sheet4!A1:B5) → 70 Delete Khi xoá các sheet từ Sheet2 đến Sheet4, Excel sẽ xoá các giá trị thuộc sheet bị xoá khỏi kết quả tính toán. Ví dụ: Xoá Sheet7 khỏi Workbook, khi đó kết quả tại ô A2 trong Sheet TongHop =SUM(Sheet2:Sheet4!A1:B5) → 60 Move Khi di chuyển các sheet từ Sheet2 đến Sheet6 đến một vị trí nằm ngoài vùng tham chiếu sheet, Excel sẽ loại bỏ các giá trị nằm trên sheet di chuyển khỏi kết quả tính toán. Ví dụ: Di chuyển Sheet3 ra nằm sau Sheet4, khi đó các giá trị vùng A1:B5 trong Sheet3 bị loại khỏi kết quả tính toán. Khi đó kết quả tại ô A2 trong Sheet TongHop =SUM(Sheet2:Sheet4!A1:B5) → 40 Move một Sheet đầu cuối Khi di chuyển Sheet2 hoặc Sheet6 đến vị trí khác trong cùng workbook, Excel sẽ tự hiệu chỉnh kết quả tính toán cho phù hợp với vùng tham chiếu sheet. Ví dụ: Di chuyển Sheet4 ra nằm sau Sheet5, khi đó giá trị vùng A1:B5 trong Sheet5 sẽ bao gồm trong kết quả tính toán. Ô A2 trong Sheet TongHop =SUM(Sheet2:Sheet4!A1:B5) → 100 www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 15 Ví dụ: Di chuyển Sheet2 ra nằm sau Sheet4. Khi đó, công thức trong ô A2 trong Sheet TongHop biến đổi thành =SUM(Sheet3:Sheet4!A1:B5) → 50 Ví dụ: Di chuyển Sheet4 ra nằm trước Sheet2. Khi đó, công thức trong ô A2 trongSheet TongHop biến đổi thành =SUM(Sheet2:Sheet3!A1:B5) → 30 Delete một Sheet đầu cuối Khi xoá Sheet2 hoặc Sheet6, Excel sẽ tự hiệu chỉnh kết quả tính theo cho phù hợp với vùng tham chiếu sheet mới. Ví dụ: Xoá sheet cuối trong tham chiếu 3D Sheet4. Khi đó, công thức trong ô A2 trong Sheet TongHop biến đổi thành =SUM(Sheet2:Sheet3!A1:B5) → 30 www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 16 Kiểu tham chiếu R1C1 Ngoài ra chúng ta còn có thể chuyển kiểu tham chiếu từ dạng A1 sang dạng R1C1, kiểu tham chiếu R1C1 rất hữu ích cho việc tính toán vị trí dòng và cột trong VBA. Đối với kiểu R1C1, Excel ký hiệu “R” để chỉ dòng theo sau là số thứ tự dòng và ký hiệu "C" để chỉ cột theo sau là số thứ tự cột. Kiểu tham chiếu A1 và R1C1. Ô đang chọn có địa chỉ là R1C1 R: chỉ dòng C: chỉ cột Chỉ số sau R hay C nếu để trong móc vuông [ ] là tham chiếu tương đối Chỉ số sau R hay C không để trong móc vuông [ ] là tham chiếu tuyệt đối R (chính là R[0]) hay C (chính là C[0]) không có chỉ số theo sau là biểu thị cùng dòng hay cùng cột với ô hiện hành. Di chuyển lên dòng trên, cùng cột R[-1]C RC[-1] Ô hiện hành RC[1] Di chuyển qua Di chuyển qua cột bên trái, cột bên phải, cùng dòng cùng dòng R[1]C Di chuyển xuống dòng dưới, cùng cột www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 17 Để hiểu hơn về tham chiếu R1C1 xét các ví dụ sau: R[-2]C Tham chiếu tương đối đến ô nằm trên ô hiện hành 2 dòng trong cùng cột (tương đương ô B1) Ô hiện hành là R3C2 tham chiếu tương đối đến ô nằm phía trên nó 2 dòng và cùng cột → R[-2]C R1C2 Tham chiếu tuyệt đối đến ô tại dòng 1 cột 2 (tương đương ô B1) Ô hiện hành là R3C2 tham chiếu tuyệt đối đến ô tại dòng 1 cột 2 → R1C2 R[3]C[2] Tham chiếu tương đối đến ô nằm dưới ô hiện hành 3 dòng và nằm bên phải 2 cột Ô hiện hành là R3C2 tham chiếu tương đối đến ô nằm phía dưới nó 3 dòng và nằm bên phải 2 cột → R[3]C[2] www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 18 R[2] Tham chiếu tương đối đến cả dòng nằm dưới ô hiện hành 2 dòng C[-2]:C[-1] Tham chiếu tương đối đến cả 2 cột nằm trước ô hiện hành Ví dụ: Công thức trong bảng tính sử dụng tham chiếu R1C1 (xem hình bên dưới) Giải thích công thức ô tại dòng 2 cột 5 (tương đương E2): =RC[-2]*RC[-1] RC[-2] (tương đương C2) o R: Cùng dòng ô hiện hành → dòng 2 o C[-2]: Dịch chuyển về bên trái ô chứa công thức hiện hành 2 cột → cột 3 RC[-1] (tương đương ô D2) o R: Cùng dòng ô hiện hành → dòng 2 o C[-1]: Dịch chuyển về bên trái ô chứa công thức hiện hành 1 cột → cột 4 www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 19 Giải thích công thức ô tại dòng 27 cột 2 (tương đương B27): =SUMIFS(R2C5:R21C5,R2C1:R21C1,RC1,R2C2:R21C2,R26C) R2C5:R21C5: Vùng địa chỉ tuyệt đối từ ô có dòng 2 cột 5 đến ô dòng 21 cột 5 (tương đương $E$2:$E$21) R2C1:R21C1: Vùng địa chỉ tuyệt đối từ ô có dòng 2 cột 1 đến ô dòng 21 cột 1 (tương đương $A$2:$A$21) RC1: Địa chỉ hỗn hợp của ô có dòng cùng dòng ô chứa công thức và cột 1 (tương đương $A27) R2C2:R21C2: Vùng địa chỉ tuyệt đối từ ô có dòng 2 cột 2 đến ô dòng 21 cột 2 (tương đương $B$2:$B$21) R26C: Địa chỉ hỗn hợp của ô có dòng 26 và cùng cột với ô chứa công thức (tương đương B$26) www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 20 Tham chiếu R1C1 và Macro Khi ghi macro, Excel mặc định sinh ra các tham chiếu dưới dạng R1C1 khi gán công thức vào ô. Ví dụ: Ghi macro chèn công thức tính tổng cột “Thành tiền” của ví dụ trên thì Macro sinh ra code như sau: Sub Macro1() Range("E22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)" Range("E23").Select End Sub Ví dụ: Thủ tục chuyển đổi qua lại kiểu tham chiếu A1 và R1C1 Sub Shift_R1C1_A1() If Application.ReferenceStyle = xlR1C1 Then Application.ReferenceStyle = xlA1 Else Application.ReferenceStyle = xlR1C1 End If End Sub Tùy chọn để chuyển sang kiểu tham chiếu R1C1 Excel 2003 về trước: Tools | Options | General | Settings | chọn R1C1 reference style. www.giaiphapexcel.com
Tham Chiếu Trong Công Thức Giải Pháp Excel | 21 Excel 2007: Nút Office | Excel Options | Formulas | Working with formulas | chọn R1C1 reference style Excel 2013: File | Options | Formulas | Working with formulas | chọn R1C1 reference style www.giaiphapexcel.com
Tính Toán Trong Bảng Giải Pháp Excel | 22 I.4. TÍNH TOÁN TRONG BẢNG Đây là tính năng mới trong Excel 2007+, trong bảng biểu ta có thể dùng tên cột để làm tham chiếu tính toán. Muốn chuyển một danh sách thành bảng biểu ta hãy đặt ô hiện hành vào trong vùng danh sách, sau đó vào Ribbon Insert nhóm Tables Table hộp thoại Create Table Chọn My table has headers nếu có OK. Lưu ý các bảng được đặt tên tự động. Minh họa chuyển danh sách thành bảng B1. Tính cột Thành tiền: chọn ô D2 và nhập vào =[Số lượng]*[Giá] (hoặc =B2*C2) và Enter. Excel sẽ tự động sao chép công thức xuống giúp bạn. Dùng tên cột làm tham chiếu tính toán B2. Tính cột Tiền VND: chọn ô E2 và nhập vào =[Thành tiền]*H$1 (hoặc =D2*H$1) và Enter. Excel sẽ tự động sao chép công thức xuống giúp bạn. B3. Thêm dòng tính Tổng hãy để ô hiện hành vào bảng: Ribbon Table Tools Design nhóm Table Style Options chọn Total Row. www.giaiphapexcel.com
Các Lỗi Thông Dụng Và Cách Xử Lý Lỗi Giải Pháp Excel | 23 Chọn lựa hàm để tính toán Ghi chú: Nhấn để tạo bảng. I.5. CÁC LỖI THÔNG DỤNG VÀ CÁCH XỬ LÝ LỖI I.5.1. Các lỗi thông dụng Lỗi Giải thích #DIV/0! Trong công thức có chứa phép chia cho 0 (zero) hoặc chia ô rỗng #NAME? Do dánh sai tên hàm hay tham chiếu hoặc đánh thiếu dấu nháy #N/A Công thức tham chiếu đến ô mà có dùng hàm NA để jiễm tra sự tồn tại của dữ liệu hoặc hàm không có kết quả #NULL! Hàm sử dụng dữ liệu giao nhau của 2 vùng mà 2 vùng này không có phần chung nên phần giao rỗng #NUM! Vấn đề đối với giá trị, ví dụ như dùng nhầm số âm trong khi đúng phải là số dương #REF! Tham chiếu bị lỗi, thường là do ô tham chiếu trong hàm bị xóa #VALUE! Công thức tính toán có chứa kiểu dữ liệu không đúng. Tham chiếu vòng Trong một số trường hợp nhập công thức thì bạn nhận được hộp thoại thông báo tham chiếu vòng (Circular Reference) vì trong công thức có sử dụng giá trị trong ô mà bạn đang nhập công thức. www.giaiphapexcel.com
Các Lỗi Thông Dụng Và Cách Xử Lý Lỗi Giải Pháp Excel | 24 Ví dụ: Tại ô A3 bạn nhập vào công thức =A1+A2+A3, bạn sẽ nhận được cảnh báo tham chiếu vòng. Cảnh báo tham chiếu vòng Nếu các phép tính của bạn yêu cầu phải tham chiếu vòng thì bạn cần phải bật tính năng này lên. Tùy chọn tham chiếu vòng Ví dụ: Ô A1 chứa số 2, ô A2 chứa số 3, ô A3 nhập vào công thức =A1+A2+A3 và với thiết lập tính vòng nhưtrên thì kết quả ô A3 lần đầu tiên sau khi nhập công thức là 15. I.5.2. Tùy chọn tính toán Trong thực tế đôi khi chúng ta phải làm việc với một bảng tính rất phức tạp và có rất nhiều công thức. Theo mặc định, cứ mỗi sự thay đổi trong bảng tính thì Excel sẽ tự động tính lại tất cả các công thức có trong bảng tính, điều này làm giảm hiệu suất làm việc rất nhiều. Do vậy chúng ta nên điều chỉnh tùy chọn tính toán trong Excel. Vào Ribbon Formulas Calculation Calculation Options Có các tùy chọn sau (cũng có thể làm theo cách ở hình 2.10) Automatic: Được thiết lập mặc định, Excel sẽ tính toán lại tất cả các công thức khi có sự thay đổi về giá trị, công thức hoặc tên trong bảng tính. Automatic Except for Data Tables: Tính toán lại tất cả các công thức ngoại trừ các công thức trong các bảng biểu. Manual: Ra lệnh chó Excel tính toán lại khi cần, có các tổ hợp phím sau o F9: Tính toán lại các công thức trong tất cả workbook đang mở. o Shift + F9: Chỉ tính lại các công thức trong Sheet hiện hành. o Ctrl + Alt + F9: Tính toán lại các công thức trong tất cả workbook đang mở không cần quan tâm đến nội dung có thay đổi hay không. www.giaiphapexcel.com
Các Lỗi Thông Dụng Và Cách Xử Lý Lỗi Giải Pháp Excel | 25 I.5.3. Kiểm tra công thức bằng Formulas Auditing Một ô có dạng (có 1 góc nhỏ tô màu) là ô đó đang có ilỗ nào đó. Các lỗi thông dụng như: ô chứa công thức mà kết quả có lỗi, số mà lại định dạng như chuỗi, ô bị khóa, nhập liệu không đúng, Để dò tìm và sữa lỗi chúng ta dùng bộ công cụ Ribbon Formulas Formulas Auditing. Hiện đường nối Hiện ô dưới dạng Kiểm tra lỗi thể hiện mối liên công thức hệ giữa công thức và các ô Kiểm tra qua từng Xóa đường nối bước tính toán của công thức Nhóm lệnh Formulas Auditing Ghi chú: Để điều chỉnh các lựa chọn kiểm tra lỗi vào Office Excel Options nhóm Formulas Error checking và Error checking rules. Ví dụ: Kết quả tính NPV có lỗi như hình dưới để tìm lỗi ta vào Ribbon Formulas nhóm Formulas Auditing Error Checking. Excel sẽ kiểm tra lỗi toàn bộ bảng tính và báo cáo về các ô có chứa lỗi. Trường hợp này thì B4 chứa lỗi. Minh họa kiểm tra lỗi Bạn muốn tìm hiểu thêm về dạng lỗi thì nhấn vào nút Help on this error Muốn kiểm lỗi từng bước tính toán của công thức thì nhấn vào Show Calculation Steps www.giaiphapexcel.com
Các Lỗi Thông Dụng Và Cách Xử Lý Lỗi Giải Pháp Excel | 26 Muốn bỏ qua lỗi thì nhấn vào Ignore Error Muốn hiệu chỉnh công thức thì nhấn vào Edit in Formula Bar Nhấn Next đến lỗi kế và Previous về lỗi trước (nếu có) Nhấn vào Option nếu muốn hiệu chỉnh tùy chọn báo lỗi của Excel. Để kiểm tra các bước tính toán của công thức bị lỗi trên ta chọn Show Calculation Steps (hoặc nhấn trực tiếp vào nút Evaluate Formula trong nhóm Formulas Auditing). Nhấn vào các nút Evaluate để xem các bước tính toán của công thức. Lỗi do tham chiếu nhầm ô chứa chuỗi Kiểm tra các bước tính toán của công thức Nếu muốn xem ô B4 có quan hệ với các ô khác như thế nào hãy chọn ô B4, sau đó vàoRibbon Formulas nhóm Formulas Auditing Trace Precedents. Qua hình bên dưới ta thấy kết quả tính toán của ô B4 được tính từ các ô A2, C2:G2 và ô I2, từ đó chúng ta cũng có thể lần ra lỗi trong công thức. www.giaiphapexcel.com
Các Lỗi Thông Dụng Và Cách Xử Lý Lỗi Giải Pháp Excel | 27 Quét chọn và nhấn F9 Quét chọn và nhấn F9 Quét chọn và nhấn F9 Quét chọn và nhấn F9 Ô A2 chứa chuỗi làm công thức sai Minh họa tìm lỗi trong công thức dùng phím F9 www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 28 Phần II HÀM TRONG EXCEL 97-2013 II.1. HÀM XỬ LÝ VĂN BẢN VÀ CHUỖI (TEXT FUNCTIONS) ASC Dùng cho các ngôn ngữ sử dụng bảng mã 2-byte (DBCS), đổi các ký tự 2-bytes sang các ký tự 1- byte. Cú pháp: = ASC(text) text : Là chuỗi hoặc tham chiếu đến một ô có chứa chuỗi muốn chuyển đổi. Nếu text không chứa ký tự 2-bytes nào thì text sẽ giữ nguyên. Ví dụ: = ASC("Excel") “Excel” = ASC(“エクセル”) “エクセル” BAHTTEXT Dùng để chuyển đổi một số thành dạng chữ của tiếng Thái, rồi thêm hậu tố "Bath" vào. Cú pháp: =BAHTTEXT(number) number: Là một số hoặc tham chiếu đến một ô có chứa số. Ví dụ: Đọc số 2014 thành tiền Thái. =BAHTTEXT(2014) สองพันสิบสี่บาทถ้วน www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 29 CHAR Chuyển đổi một mã số trong bộ mã ANSI (có miền giá trị từ 1 - 255) sang ký tự tương ứng. Cú pháp: =CHAR(number) number: là một con số từ 1 đến 255, là mã số trong bảng mã ANSI. Ví dụ: Để kết quả có được là ký tự bản quyền ©, chúng ta nhập: =CHAR(169) Bảng mã ANSI Ví dụ: Dùng hàm char và toán tử nối chuỗi để tạo chuỗi như trong cột D, có xuống dòng sau Họ tên, chức danh và cơ quan công tác. Cột D được định dạng Wrap Text. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 30 CLEAN Dùng để xóa tất cả những ký tự không in ra được (nonprintable characters) trong chuỗi văn bản. Hàm CLEAN thường được dùng để gỡ bỏ những dấu ngắt đoạn, ngắt câu (có được do nhấn phím Enter), nghĩa là nối những đoạn bị xuống hàng lại với nhau. Cú pháp: =CLEAN(text) text: đoạn văn bản cần xóa những ký tự không in ra được. (Những ký tự không in ra được là những ký tự có mã số từ 1 đến 31 trong bảng mã ANSI.) Ví dụ: Hình sau đây cho thấy công dụng của hàm CLEAN. CODE Hàm CODE là ngược lại với hàm Char, nghĩa là, cho 1 ký tự, CODE sẽ cho biết mã số của ký tự đó. Nếu ốđ i số của CODE là một chuỗi, thì kết quả trả về sẽ là mã số của ký tự đầu tiên trong chuỗi đó. Cú pháp: =CODE(text) text: Là một ký tự hoặc một chuỗi (một câu) Ví dụ: =CODE("a") → 97 =CODE("A") → 65 =CODE("@") → 64 www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 31 CONCATENATE Dùng nố i nhiều chuỗi lại với nhau Cú pháp: =CONCATENATE(text1,text2, ) Text1, Text2, có thể có từ 2 đến 255 chuỗi văn bản mà ta muốn kết nối lại thành một chuỗi đơn. Ta có thể sử dụng tham chiếu tới những ô có chứa văn bản hay con số Ví dụ: Nối nhiều chuỗi thành một chuỗi =CONCATENATE("Giải pháp", " Excel", " - ", "Công cụ tuyệt vời của bạn) → Giải pháp Excel - Công cụ tuyệt vời của bạn www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 32 DBCS (Excel 2013) (Hàm này chắc là thay cho hàm JIS) Chuyển các ký tự 1-byte trong chuỗi văn bản sang các ký tự 2-byte. Tùy thuộc vào thiết lập ngôn ngữ mặc định trong Control Panel và của Office. Đối với tiếng Nhật, hàm này chuyển các ký tự 1-byte tiếng Anh hoặc katakana trong chuỗi thành các ký tự 2-byte. Cú pháp: =DBCS(text) text chuỗi hoặc tham chiếu đến ô chứa chuỗi. Nếu chuỗi không chứa ký tự 1-byte tiếng Anh hoặc katakana nào thì text không đổi. Ví dụ: =DBCS("EXCEL") "EXCEL" =DBCS(“エクセル”) “エクセル” DOLLAR Chuyển đổi một số thành dạng tiền tệ (dollar Mỹ), có kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn. Cú pháp: =DOLLAR(number, decimals) Number : Số cần chuyển sang dạng tiền tệ Decimals : Số số thập phân, mặc định là 2, nếu là số âm thì sẽ làm tròn về bên trái Ví dụ: Tại ô B15 nhập công thức: = "Tổng chi phí quí một năm 2008 là " & DOLLAR(76632, 0) Kết quả trả về: Tổng chi phí quí một năm 2008 là $76,632 Ở đây, số tiền này đã được định dạng thành số nguyên (decimals = 0). www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 33 EXACT Dùng để so sánh hai chuỗi với nhau. Cú pháp: =EXACT(text1,text2) Text1 và Text2 là hai chuỗi văn bản cần so sánh Lưu ý: Hàm bỏ qua các định dạng nhưng phân biệt chữ HOA và thường Nếu 2 chuỗi text1, text2 giống nhau hoàn toàn, hàm sẽ trả về TRUE; nếu không, sẽ trả về trị FALSE Ví dụ: =EXACT("Giải pháp", "Giải pháp") → TRUE =EXACT("Giải pháp", "Giải Pháp") → FALSE Ví dụ: So sánh hàm Exact và hàm IF. Kết quả trả về không giống nhau do hàm IF không phân biệt chữ HOA và thường khi so sánh. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 34 FIND Tìm một chuỗi văn bản trong một văn bản khác. Giá trị trả về là vị trí đầu tiên của ký tự đầu tiên trong chuỗi tìm. FIND dùng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), FINDB dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS) FIND luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Windows & Office FINDB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS làm ngôn ngữ mặc định. Ngược lại, FINDB đếm mỗi ký tự là 1. Các ngôn ngữ hỗ trợ mã DBCS: Japanese, Chinese (Simplified), Chinese (Traditional), và Korean. Công thức: =FIND(find_text, within_text [, start_num]) =FINDB(find_text, within_text [,start_num]) find_text: chuỗi văn bản cần tìm (chuỗi con) within_text: chuỗi văn bản chứa chuỗi cần tìm (chuỗi mẹ) start_num: vị trí bắt đầu tìm trong chuỗi within_text (mặc định là 1) Lưu ý: FIND và FINDB là có phân biệt chữ HOA và thường và không cho dùng ký tự thay thế. Nếu muốn tìm kiếm không phân biệt chữ HOA và thường hoặc dùng ký tự thay thế thì dùng SEARCH và SEARCHB. Nếu find_text là "" (rỗng), hàm FIND trả về vị trí ký tự đầu tiên trong chuỗi tìm (đó là vị trí start_num hoặc 1). www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 35 Find_text không thể chứa các ký tự thay thế. Nếu find_text không tìm thấy trong within_text, hàm FIND và FINDB báo lỗi #VALUE!. Nếu start_num là nhỏ hơn hay bằng zero, hàm FIND và FINDB báo lỗi #VALUE!. Nếu start_num lớn hơn chiều dài của within_text, hàm FIND và FINDB báo lỗi #VALUE!. Sử dụng start_num để bỏ qua các ký tự đầu chuỗi mà ta không cấn tìm. Ví dụ: Ví dụ: Trích xuất họ, tên đệm và tên (ví dụ với tên tiếng Anh, phần tên đệm được viết tắt) Giả sử Họ và Tên nằm ở ô A2, và đang có giá trị là Charlotte P. Cooper Đầu tiên, như bài trên, dùng công thức sau để tách phần Tên. =LEFT(A2, FIND(" ", A2) - 1) → Charlotte Công thức FIND(" ", A2) sẽ cho kết quả là 10, là vị trí của khoảng trắng đầu tiên (sau chữ Charlotte). Để tìm vị trí của khoảng trắng thứ hai, thì bạn phải gán vị trí bắt đầu tìm (start_num) là 11, hoặc là bằng kết quả của FIND(" ", A2) cộng thêm 1: =FIND(" ", A2, FIND(" ",A2) + 1) Rồi dùng kết quả của công thức này làm tham số cho hàm RIGHT() để trích ra phần Họ. =RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) +1)) → Cooper Để trích phần tên đệm, dùng hàm FIND để tìm vị trí của dấu chấm (.) rồi đưa vào trong công thức của hàm MID để tìm ký tự đứng trước dấu chấm: www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 36 =MID(A2, FIND(".", A2) - 1, 1) → P Hình sau đây là một minh họa cho phần vừa trình bày ở trên: FINDB Xem cách sử dụng tạo hàm FIND. FIXED Chuyển đổi một số thành dạng văn bản (text), có hoặc không kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn. Cú pháp: =FIXED(number [,decimals] [,no_commas]) number là số cần chuyển sang dạng text decimals là số số thập phân, mặc định là 2, nếu là số âm thì sẽ làm tròn về bên trái no_commas: TRUE hoặc FALSE, dùng TRUE nếu không muốn có dấu phân cách hàng ngàn, và FALSE (mặc định) thì có bao gồm cả dấu phân cách hàng ngàn Ví dụ: Bạn thử dùng hàm FIXED với con số 123456789,85 để ra lập công thức cho ra kết quả giống như cột B. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 37 JIS (Excel 2013-) Chuyển các ký tự 1-byte trong chuỗi văn bản sang các ký tự 2-byte. Tên hàm và các ký tự mà hàm chuyển đổi phụ thuộc vào thiết lập trong Control Panel, Region and Language. Đối với tiếng Nhật, hàm này sẽ chuyển ký tự 1-byte (katakana) trong chuỗi văn bản sang các ký tự 2-bytes. Cú pháp: =JIS(text) Text là chuỗi hoặc tham chiếu đến ô chứa chuỗi cần chuyển đổi. Nếu chuỗi không chứa ký 1- byte nào thì hàm trả về chính chuỗi đó. Ví dụ: =JIS("EXCEL") "EXCEL" =JIS(“エクセル”) “エクセル” LEFT Left: Dùng để trích xuất phần bên trái của một chuỗi một hoặc nhiều ký tự tùy theo sự chỉ định số lượng ký tự cần trích. LeftB: Dùng để trích xuất phần bên trái của một chuỗi một hoặc nhiều ký tự tùy theo sự chỉ định số byte cần trích. Cú pháp: =LEFT(text [, num_chars]) =LEFTB(text, [num_bytes]) text: chuỗi văn bản cần trích xuất ký tự num_chars: số ký tự cần trích ra phía bên trái chuỗi text, mặc định là 1. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 38 Num_bytes: số byte cần trích Lưu ý: num_chars phải là số nguyên dương Nếu num_chars lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là toàn bộ chuỗi Ví dụ: =LEFT(“Giai Phap Excel, 4) → Giai =LEFT(“你好”,2) 你 LEFTB chỉ đếm mỗi ký tự là 2 bytes khi một trong các ngôn ngữ hỗ trợ DBCS (double-byte character set) được thiết lập mặc định trong máy của bạn (Control Panel | Region and Language). Còn lại thì LEFTB sẽ giống như hàm LEFT (đếm mỗi ký tự là 1 byte). DBCS bao gồm Japanese, Chinese (Simplified), Chinese (Traditional), và Korean. Microsoft Office sử dụng ngôn ngữ nhập mặc định trong hệ điều hành Windows để xác định ngôn ngữ mặc định cho các chương trình Office. Để thay đổi ngôn ngữ mặc định trong các chương trình Office, bạn phải thay đổi ngôn ngữ nhập mặc định cho hệ điều hành Windows và sau đó thay đổi ngôn ngữ chỉnh sửa mặc định cho Office. LEFTB Xem cách sử dụng tại hàm LEFT. Thay đổi ngôn ngữ nhập liệu mặc định trong Windows Vào Control Panel | chọn Region and Language | chọn Keyboards and Languages | chọn Change Keyboards (Minh họa Windows 7) www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 39 Nhấn nút Add để thêm vào ngôn ngữ mới, ví dụ bạn chọn Chinese (Simplified) Sau khi thêm ngôn ngữ mới thì chọn ngôn ngữ đó làm ngôn ngữ nhập liệu mặc định tại khung Default input language. Bấm OK để đồng ý cho các thay đổi. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 40 Sau đó khởi ộđ ng Excel (Hình minh họa trên Excel 2013), vào File | Options | Language | khi đó bạn sẽ thấy Chinese (PRC) tại Choose Editing Languages, bạn hãy chọn nó làm mặc định. Bấm OK để chấp nhận các thay đổi, sau đó đóng và mở lại Excel để các thiết lập có hiệu lực. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 41 Sau khi các thiết lập trong Windows và Office hoàn tất chuyển sang ngôn ngữ hỗ trợ DBCS thì các hàm như LENB, LEFTB, RIGHTB, MIDB, FINDB, SEARCHB, sẽ hoạt động đúng chức năng của chúng. Ví dụ: Minh họa cách dùng hàm LENB, LEFTB và RIGHTB LEN LEN: Trả về số ký tự trong một chuỗi LENB: Trả về số byte trong một chuỗi. (Đếm mỗi ký tự là 2 byte khi một trong các ngôn ngữ hỗ trợ DBCS được thiết lập mặc định trong máy của bạn). www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 42 Cú pháp: =LEN(text) =LENB(text) Text: Là chuỗi văn bản cần tính chiều dài. Ví dụ: Tính chiều dài chuỗi LENB Xem cách sử dụng tại hàm LEN. LOWER Hàm LOWER đổi tất cả các ký tự trong một chuỗi văn bản thành chữ thường. Hàm này thường được dùng khi bạn cần định dạng lại một câu văn (thường là do chép ở đâu đó dán vào trong Excel) mà trong đó, chữ thường chữ hoa được gõ hoặc bị thay đổi lung tung Cú pháp: =LOWER(text) text: Là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng Ví dụ: Công thức sau đây sẽ sửa cell A2 (Giải pháp Excel) thành toàn là chữ thường =LOWER(A2) → giải pháp excel www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 43 MID Mid: Dùng để trích xuất một chuỗi con (substring) từ một chuỗi theo sự chỉ định vị trí bắt đầu và số lượng ký tự cần trích. MidB: Dùng để trích xuất một chuỗi con (substring) từ một chuỗi theo sự chỉ định vị trí bắt đầu và số lượng byte cần trích. (Đếm mỗi ký tự là 2 byte khi một trong các ngôn ngữ hỗ trợ DBCS được thiết lập mặc định trong máy của bạn) Công thức: =MID(text, start_num, num_chars]) =MIDB(text, start_num, num_bytes) text: chuỗi văn bản cần trích xuất start_num: vị trí bắt đầu trích ra chuỗi con, tính từ bên trái sang num_chars: số ký tự của chuỗi con cần trích ra. num_bytes: số bytes cần trích Lưu ý: num_chars phải là số nguyên dương start_num phải là số nguyên dương Nếu start_num lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là chuỗi rỗng Ví dụ: =MID("Giai Phap Excel", 6, 4) → Phap MIDB Xem cách sử dụng tại hàm MID. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 44 NUMBERVALUE (Excel 2013) Chuyển chuỗi thành dạng số phù hợp với thiết lập địa phương của máy tính. Cú pháp: =NUMBERVALUE(Text, [Decimal_separator], [Group_separator ]) Text: chuỗi cần chuyển đổi Decimal_separator: ký tự dùng để phân cách phần số nguyên và số thập phân. Group_separator: ký tự dùng để phân nhóm hàng tỷ, triệu, nghìn của con số Lưu ý: Nếu Decimal_separator và Group_separator không khai báo thì hàm sẽ lấy ký tự thiết lập của hệ thống hiện tại. Nếu có nhiều Decimal_separator và Group_separator được dùng thì hàm chỉ lấy ký tự đầu tiên. Nếu Text là chuỗi rỗng thì hàm trả về 0 (zero). Các khoảng trắng trong Text sẽ bị bỏ qua. Ví dụ “3 000” kết quả là “3000” Nếu Decimal_separator xuất hiện nhiều hơn 2 lần trong Text hàm sẽ trả về lỗi #VALUE! Nếu Group_separator xuất hiện trước Decimal_separator trong Text thì Group_separator sẽ bị bỏ qua. Nếu Group_separator xuất hiện sau Decimal_separator trong Text thì hàm trả về lỗi #VALUE! Nếu có bất kỳ đối số nào không hợp lệ hàm cũng trả về lỗi #VALUE! Nếu Text có chứa nhiều ký hiệu %% thì các ký hiệu này sẽ được dùng để tính toán. Ví dụ: =NUMBERVALUE("9%%") kết quả 0.0009 Ví dụ: PHONETIC Trích các ký tự ngữ âm (furigana) từ chuỗi tiếng Nhật. Cú pháp: =PHONETIC(reference) Reference là chuỗi ký tự, địa chỉ ô tham chiếu hoặc vùng địa chỉ chứa các ký tự furigana. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 45 Lưu ý: Nếu reference là vùng địa chỉ thì các ký tự furigana nằm trong ô địa chỉ ở góc trên bên trái vùng sẽ được trích. Nếu reference là vùng địa chỉ các ô không liền nhau hàm trả về lỗi #N/A. Ví dụ: Theo Help, ô A1 chứa "東京都" và ô A2 chứa “大阪府”. Kết quả ô B1 và B2 sẽ là: =PHONETIC(A1) “トウキョウト” =PHONETIC(A2) “オオサカフ” (Hàm này hiện đang chạy không ổn định, cho ra nhiều kết quả khác nhau! Xem thêm ) PROPER Hàm PROPER() đổi ký tự đầu tiên trong một chữ của một chuỗi văn bản thành chữ in hoa, còn tất cả các ký tự còn lại trong chuỗi đó trở thành chữ thường. Cú pháp: =PROPER(text) text : Là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng Ví dụ: Công thức sau đây sẽ định dạng lại chuỗi văn bản cell A7 (công cụ tuyệt vời của bạn) theo kiểu Proper: =PROPER(A7) → Công Cụ Tuyệt Vời Của Bạn REPLACE Replace: Dùng để thay thế một phần của chuỗi bằng một chuỗi khác, dựa vào số ký tự được chỉ định. ReplaceB: Dùng để thay thế một phần của chuỗi bằng một chuỗi khác, dựa vào số byte được chỉ định. Replace dùng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), Replaceb dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS). Cú pháp: =REPLACE(old_text,start_num,num_chars,new_text) =REPLACEB(old_text,start_num,num_bytes,new_text) old_text: chuỗi văn bản cần được xử lý start_num: vị trí bắt đầu tìm cái sẽ thay thế, tính từ bên trái sang num_chars: là số ký tự trong old_text mà ta muốn hàm REPLACE thay bằng new_tex t www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 46 num_bytes: là số bytes trong old_text mà ta muốn hàm REPLACEB thay bằng new_text new_text: là chuỗi mà ta muốn thay cho các ký tự trong old_text. Cái khó của hàm này là xác định được bởi start_num và num_chars. Làm sao biết được bắt đầu từ đâu và thay thế bao nhiêu chữ? - Bạn dùng hàm FIND() hoặc SEARCH() để xác định vị trí bắt đầu (start_num) - Dùng hàm LEN() để xác định số ký tự của chuỗi sẽ được thay thế (num_chars) Ví dụ: Đế thay số 2007 bằng 2014 trong câu Expense Budget for 2007 ở ô A10 Dùng công thức như sau: =REPLACE(A10, FIND("2007", A10), LEN("2007"), "2014")→ Expense Budget for 2014 Ví dụ: REPLACEB Xem cách sử dụng tại hàm REPLACE. REPT Dùng để lặp đi lặp lại một ký tự hoặc một chuỗi, với số lần được định trước. Cú pháp: =REPT(text, times) text: ký tự (character), một con số hoặc một chuỗi (string) cần được nhân bản times: số lần lặp đi lặp lại của text www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 47 Lưu ý: Nếu times = 0, hàm REPT sẽ trả về một chuỗi rỗng Nếu text là một con số, REPT sẽ làm tròn con số đó thành số nguyên Ví dụ: Tạo mã số học viên gồm 8 ký tự cho khóa 1 (K1) căn cứ và cột số thứ tự và điền thêm số 0 vào trước STT để mã học viên đủ 8 ký tự. Ví dụ: Vẽ biểu đồ tần suất bằng hàm REPT dựa trên số Phản hồi của cuộc khảo sát. Ví dụ: Vẽ biểu đồ thanh ngang bằng hàm REPT dựa trên số Phản hồi của cuộc khảo sát với việc sử dụng Font chữ Webdings. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 48 RIGHT Right: Dùng để trích phần bên phải của một chuỗi một hoặc nhiều ký tự tùy theo sự chỉ định số lượng ký tự cần trích. RightB: Dùng để trích phần bên phải của một chuỗi một hoặc nhiều ký tự tùy theo sự chỉ định số lượng byte cần trích. (Đếm mỗi ký tự là 2 byte khi một trong các ngôn ngữ hỗ trợ DBCS được thiết lập mặc định trong máy của bạn). Cú pháp: =RIGHT(text [, num_chars]) =RIGHTB(text, [num_bytes]) text: chuỗi văn bản cần trích ký tự num_chars: số ký tự cần trích ra phía bên phải của chuỗi text, mặc định là 1 num_bytes: số byte cần trích Lưu ý: num_chars phải là số nguyên dương Nếu num_chars lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là toàn bộ chuỗi text www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 49 Ví dụ: Trích 5 ký tự cuối trong chuỗi: =LEFT(“Giai Phap Excel, 5) → Excel Ví dụ: Sử dụng hàm RIGHTB đối với ngôn ngữ hỗ trợ DBCS: =RIGHT("你好",2) → kết quả trả về là 好 RIGHTB Xem cách sử dụng tại hàm RIGHT. SEARCH Tìm vị trí của một chuỗi trong một chuỗi thứ hai và trả về vị trí của ký tự đầu tiên của chuỗi thứ nhất tìm thấy trong chuỗi thứ 2 SEARCH dùng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), SEARCHB dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS) SEARCH luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Office SEARCHB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS là ngôn ngữ mặc định. Ngược lại, FINDB đếm mỗi ký tự là 1. Các ngôn ngữ hỗ trợ mã DBCS: Japanese, Chinese (Simplified), Chinese (Traditional), và Korean. Cú pháp: =SEARCH(find_text, within_text [, start_num]) =SEARCHB(find_text, within_text [, start_num]) find_text: chuỗi văn bản cần tìm (chuỗi con) within_text: chuỗi văn bản chứa chuỗi cần tìm (chuỗi mẹ) start_num: vị trí bắt đầu tìm trong chuỗi within_text (mặc định là 1) Lưu ý: Dùng SEARCH và SEARCHB để xác định vị trí một ký tự hoặc chuỗi trong một chuỗi khác để sau đó dùng các hàm MID, MIDB, REPLACE, REPLACEB. SEARCH và SEARCHB là không phân biệt chữa HOA hay thường, nếu muốn tìm có phân biệt chữ HOA và thường thì dùng FIND và FINDB. Ta có thể sử dụng các ký tự thay thế như ?, * trong find_text cần tìm. Dấu ? thay cho một ký tự nào đó và dấu * thay cho một chuỗi nào đó. Khi điều kiện lại là dấu ? hay * thì bạn đặt thêm dấu ~ phía trước nó. Nếu find_text không tìm thấy, hàm báo lỗi #VALUE!. Nếu start_num không khai báo Excel xem như là số 1. Nếu start_num nhỏ hơn hay bằng 0 (zero) hoặc lớn hơn chiều dài của within_text, hàm báo lỗi #VALUE!. Sử dụng start_num để bỏ qua một số các ký tự bắt đầu chuỗi mà ta không quan tâm. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 50 Ví dụ: SEARCHB Xem cách sử dụng tại hàm SEARCH. SUBSTITUTE Dùng để thay thế một chuỗi này bằng một chuỗi khác. Hàm này cũng tương tự hàm REPLACE, nhưng dễ sử dụng hơn. Cú pháp: =SUBSTITUTE(text, old_text, new_text [,instance_num]) text: chuỗi văn bản gốc, cần được xử lý old_text: chuỗi văn bản cần được thay thế new_text: chuỗi văn bản sẽ thay thế vào instance_num: số lần thay thế old_text bằng new_text, nếu không khai báo thì tất cả old_text tìm được sẽ được thay thế bằng new_text Ví dụ: Đế thay số 2007 bằng 2014 trong câu “Xin chào GPE 2007” tại ô A1 Dùng công thức như sau: =SUBSTITUTE(A1, "2007", "2014") → Xin chào GPE 2014 Ví dụ: Tạo số tài khoản công ty dựa trên 3 ký tự dạng chữ đầu tiên và số thứ tự dòng có thêm các số 0 vào trước cho đủ 4 ký tự số. =UPPER(LEFT(SUBSTITUTE(SUBSTITUTE(A2, ".", ""), " ", ""), 3)) & TEXT(ROW(A2), "0000") www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 51 Ví dụ: Loại bỏ các mã xuống dòng trong chuỗi. =SUBSTITUTE(A2, CHAR(10), " ") Ví dụ: Tách họ, chữ lót và Tên trong chuỗi Họ và Tên (Tiếng Việt) Đầu tiên chúng ta tách chữ đầu tiên trong chuỗi làm Họ =IFERROR(LEFT(A4,FIND(" ",A4)-1)," ") Tách chữ sau cùng bên phải làm Tên =IFERROR(RIGHT(A4,LEN(A4)-FIND("*",SUBSTITUTE(A4," ","*",LEN(A4)- LEN(SUBSTITUTE(A4," ",""))))),A4) Tách các họ và họ lót (nếu có) =IF(LEN(B4&D4)+2>=LEN(A4),"",MID(A4,LEN(B4)+2,LEN(A4)-LEN(B4&D4)-2)) www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 52 T Trả về một chuỗi nếu trị tham chiếu là chuỗi, ngược lại, sẽ trả về chuỗi rỗng Cú pháp: =T(value) Value: là giá trị muốn kiểm tra Lưu ý: Nếu value là chuỗi hoặc tham chiếu đến ô chứa chuỗi thì hàm sẽ trả về chính value đó. uNế value không là chuỗi hàm trả về "" (chuỗi rỗng). Ta không cần phải sử dụng hàm T vì Excel tự động thực hiện việc kiểm tra và tiến hành chuyển đổi khi cần. Hàm này tạo ra nhằm để tương thích với các chương trình bảng tính khác. Ví dụ: TEXT Chuyển đổi một số thành dạng văn bản (text) theo định dạng được chỉ định Công thức: =TEXT(number,format) number là số cần chuyển sang dạng text format: kiểu định dạng số hoặc ngày tháng năm Ví dụ: Công thức sau sẽ dùng hàm AVERAGE() để tính số trung bình của mảng cell A1:A31, và dùng hàm TEXT để chuyển đổi kết quả ra dạng nhiệt độ =”Nhiệt độ trung bình của lò phản ứng là “ & TEXT(AVERAGE(A1:A31), “#,##0.00°F”) → Nhiệt độ trung bình của lò phản ứng là 7,560.00°F Ví dụ: Đọc ô chứa ngày và viết thành câu như hình sau: www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 53 Ví dụ: Tạo mã số gồm 4 ký tự dựa trên con số ở cột STT và điền thêm các số 0 vào trước STT sao cho đủ 4 ký tự. Thể hiện thời gian cập nhật bảng tính lần sau cùng Có bao giờ bạn muốn biết được lần cuối cùng bảng tính này có thayi đổ là khi nào không? Một trong những cách làm là đặt chế độ tính toán của Excel là thủ công (manual calculation mode) và sử dụng hàm NOW trong một cell nào đó (hàm này cho ra kết quả là ngày giờ hiện tại). Như vậy, hàm NOW sẽ không tự cập nhật trừ phi bảng tính được lưu (save), hoặc được tính toán lại (recalculate), và nhờ đó mà bạn luôn biết được lần cuối cùng bảng tính này có thay iđổ là khi nào. Ví dụ, bạn dùng công thức: = “Bảng tính cập nhật lần cuối là: ” & NOW() Tuy nhiên, nếu chỉ có chừng đó, thì e ngrằ kết quả bạn nhận được sẽ không như ý, vì có thể nó sẽ là như thế này: Bảng tính cập nhật lần cuối là: 38572.51001 www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 54 Do Excel luôn hiểu ngày và giờ là một con số, cho nên con số này: 38572.51001 được hiểu là: con số phía bên trái của dấu phân cách (38572) là số chỉ ngày, và con số phía bên phải của dấu phân cách (51001) là số chỉ giờ Chỉ có ai rành lắm mới may ra đọc và hiểu được! Vì vậy, ta nên sửa công thức trên bằng cách dùng hàm TEXT để định dạng nó ra kiểu ngày giờ đàng hoàng, chẳng hạn kiểu DD/MM/YYYY HH:MM =“ Bảng tính cập nhật lần cuối là: ” & TEXT(NOW(), “dd/mm/yy hh:mm”) → Bảng tính cập nhật lần cuối là: 08/08/2005 12:14:00 PM TRIM Xóa tất cả những khoảng trắng dư thừa trong chuỗi văn bản, chỉ chừa lại những khoảng trắng nào dùng làm dấu cách giữa hai chữ. Những khoảng trắng dư thừa này có thể nằm ở bất kỳ đâu trong đoạn văn: ở đầu, ở cuối hoặc ở giữa Cú pháp: =TRIM(text) text: chuỗi văn bản cần xóa những khoảng trắng vô ích. Ví dụ: Hình sau đây cho thấy công dụng của hàm TRIM. Mỗi chuỗi văn bản trong khối A2:A7 đều có những khoảng trắng vô ích, nằm ở đầu, ở giữa hoặc ở cuối. Tôi đặt hàm TRIM ở cột C. Để các bạn thấy công dụng của hàm TRIM, tôi có dùng thêm hàm LEN đặt ở cột B và D, để đếm số ký tự trong chuỗi trước và sau khi thực hiện hàm TRIM. www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 55 UNICHAR (Excel 2013) Giống như hàm CHAR dùng cho bảng mã ASCII, hàm UniChar dùng cho bảng mã UNICODE Hàm trả về ký tự Unicode của mã số đầu vào. Cú pháp: =UNICHAR(number) Number Là ký tự số cần chuyển về Unicode Lưu ý: Ký tự Unicode trả về có thể là một chuỗi ký tự (ví dụ như UTF-8, UTF-16) Number là 0 thì hàm trả về lỗi #VALUE! Number là số nằm ngoài vùng cho phép, hàm trả về lỗi #VALUE! Nếu Number là mã dành riêng trong bảng mã Unicode (Cận trên hệ HEX từ D800-DBFF = 55296-56319; cận dưới hệ HEX từ DC00-DFFF = 56320-57343) và kiểu dữ liệu không hợp lệ, hàm báo lỗi #N/A! Ví dụ: =UNICHAR(7840) → Ạ UNICODE (Excel 2013) Giống như hàm Code dùng cho bảng mã ASCII, hàm UniCode dùng cho bảng mã UNICODE Hàm trả về mã dạng số của ký tự, nếu là chuỗi ký tự thì trả về mã số của ký tự đầu tiên. Cú pháp: = UNICODE(text) Text Là ký tự muốn chuyển thành mã số Nếu Text chứa mã dành riêng và kiểu dữ liệu không hợp lệ thì hàm trả về lỗi #VALUE! www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 56 Ví dụ: =UNICODE(“Ạ”) 7840 UPPER Hàm UPPER đổi tất cả các ký tự trong một chuỗi văn bản thành chữ in hoa. Cú pháp: =UPPER(text) text: là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng. Ví dụ: Công thức sau đây sẽ nối chuỗi văn bản trong cell A5 (Giải pháp) và cell B5 (Excel) lại với nhau, ở giữa có một dấu cách, và tất cả ký tự trong kết quả toàn là chữ in hoa. =UPPER(A5) & " " & UPPER(B5) → GIẢI PHÁP EXCEL VALUE Dùng để đổi một chuỗi đại diện cho một số thành kiểu số Cú pháp: =VALUE(text) Text: là chuỗi văn bản hay một tham chiếu đến ô chứa văn bản mà ta muốn chuyển đồi Lưu ý: Text có thể định dạng thành con số, ngày, hoặc thời gianmà Excel có thể nhận dạng được, nếu nhận dạng không được sẽ báo lỗi #VALUE!. Hàm này chúng ta thường ít sử dụng, nó được tạo ra để tương thích với các chương trình bảng tính khác. Ví dụ: www.giaiphapexcel.com
Hàm Xử Lý Văn Bản Và Chuỗi (Text Functions) Giải Pháp Excel | 57 Ví dụ: Để trích ra số 6500 trong SQA6500, bạn có thể dùng hàm RIGHT() =RIGHT("SQA6500", 4) → 6500 Tuy nhiên kết quả do hàm RIGHT có được sẽ ở dạng text, bạn không thể nhân chia cộng trừ gì với cái "6500" này được. Để có thể tính toán với "6500", bạn phải đổi nó sang dạng số: =VALUE(RIGHT("SQA6500",4)) → 6500 Cũng là 6500, nhưng bây giờ bạn có thể cộng trừ nhân chia với nó. www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 58 II.2. HÀM LUẬN LÝ (LOGICAL FUNCTIONS) AND Trả về giá trị TRUE nếu tất cả các điều kiện so sánh là đúng; trả về giá trị FALSE nếu có một hay nhiều điều kiện so sánh là sai. Cú pháp: =AND(logical1, [, logical2 ]) logical1, logical2, có thể có đến 255 điều kiện cần kiểm tra (trong Excel 2003 trở về trước, con số này là 30), các điều kiện này trả về TRUE hoặc FALSE Lưu ý: Các đối số phải có giá trị logic là TRUE hoặc là FALSE. Nếu đối số là mảng hay tham chiếu thì mảng hay tham chiếu đó chỉ chứa những giá trị logic. Nếu đối số là mảng hay tham chiếu có chứa chữ hoặc những ô rỗng, các giá trị đó sẽ được bỏ qua. Nếu dãy không chứa các trị logic, AND() sẽ trả về giá trị lỗi #VALUE! Ta có thể dùng hàm AND bất cứ chỗ nào bạn muốn, nhưng thường thì hàm AND hay được dùng chung với hàm IF. Ví dụ: =IF(AND(B2 > 0, C2 > 0), 1000, 0) Nếu giá trị ở B2 và ở C2 lớn hơn 0, thì (thưởng) 1000, còn nếu chỉ cần một trong hai ô B2 hoặc C2 nhỏ hơn 0, thì không thưởng chi cả. Ví dụ: Tìm các ngày thõa điều kiện ngày cho trước www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 59 Bài toán thống kê theo độ tuổi Ở vùng Thống kê theo độ tuổi, người ta muốn lập công thức dựa vào số tuổi ở cột B và câu trả lời ở cột C, thì biết được lứa tuổi nào chọn câu trả lời nào www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 60 Để xét độ tuổi từ 18 đến 34, công thức: AND(B9 >= 18, B9 = 18, B9 = 35, B9 = 50, B9 = 65, C9, "") FALSE Trả về giá trị là FALSE Cú pháp: =FALSE() Không có đối số Chúng ta có thể nhập trực tiếp giá trị FALSE vào công thức hoặc hàm khi tính toán. Excel sẽ tự hiểu đó là giá trị luận lý có giá trị FALSE. IF Trả về một giá trị nếu điều kiện có giá trị TRUE, và một giá trị khác nếu điều kiện có giá trị FALSE. Cú pháp: =IF(logical_test, value_is_true [, value_is_false]) logical_test: Là bất kỳ giá trị hay công thức nào có thể trả về TRUE hoặc FALSE. Ví dụ, A1=100 là một biểu thức logic. Nếu giá trị trong A1 bằng 100, biểu thức sẽ trả về giá trị TRUE, còn ngược lại thì biểu thức trả về giá trị FALSE. Đối số này có thể bất kỳ toán tử so sánh nào. value_is_true: Giá trị trả về nếu logical_test là TRUE. value_is_false: Giá trị trả về nếu logical_test là FALSE. Lưu ý: Có tới 64 hàm IF có thể lồng vào nhau (với Excel 2003 trở về trước thì con số này là 7) khi đối số value_is_true và value_is_falsexây dựng trên những điều kiện phức tạp hơn. Tuy nhiên, khi cần phải kiểm tra nhiều điều kiện, nên dùng các hàm LOOKUP, VLOOKUP, HLOOKUP, hoặc CHOOSE. www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 61 Nếu có bất kỳ đối số nào của hàm IF là mảng, mỗi phần tử trong mảng sẽ được tính toán khi câu lệnh IF được thực thi. Microsoft Excel có cung cấp một số hàm khác mà bạn có thể dùng để phân tích dữ liệu dựa vào một điều kiện nào đó. Ví dụ như hàm COUNTIF, COUNTIFS, hàm SUMIF hay SUMIFS. Ví dụ: Kiểm tra ngày đầu vào thuộc quí nào? =IF(OR(MONTH(A3)=1,MONTH(A3)=2, MONTH(A3)=3),"Quí I", IF(OR(MONTH(A3)=4,MONTH(A3)=5, MONTH(A3)=6),"Quí II", IF(OR(MONTH(A3)=7,MONTH(A3)=8, MONTH(A3)=9),"Quí III","Quí IV"))) Ví dụ: Doanh thu $500 thì hoa hồng 8%. =A11*IF(A11>=500,0.08,IF(A11>=100,0.05,0.03)) Ví dụ: Bài toán tính tiền thưởng dựa trên doanh số bán hàng Một cửa hàng quy định mức tiền thưởng trên doanh số bán hàng trong tháng cho nhân viên như sau: Nếu doanh số không đạt định mức đề ra, không thường Nếu doanh số vượt định mức, nhưng chưa tới 10% so với định mức, thưởng $1000 Nếu doanh số vượt định mức, và vượt hơn 10% so với định mức, thưởng $10.000 Cách làm: Giả sử cột B là định mức doanh số bán, cột C là doanh số thực tế trong tháng, để tính được phần trăm vượt định mức của nhân viên, tại cột D ta dùng công thức: www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 62 =(C2-B2)/C2 (nếu muốn hiện số %, bạn định dạng cho cột D theo kiểu Percentage) Và tại cột E, tính mức tiền thưởng, dùng công thức: =IF(D2 = 1000, "Big!", "Not big") Bây giờ thêm chút, A1 lớn hơn 1000 là "big" rồi, nhưng chẳng lẽ 10000 thì cũng chỉ là "big" ? Có lẽ nên tặng thêm một danh hiệu cao hơn: =IF(A1 >= 1000, IF(A1 >= 10000, "Really big!!", Big!"), "Not big") Hoặc là, đồng ý rằng = 1000, "Big!", IF(A1 < 100, "Small", "Not big")) Bạn để ý nhé, ở đây tôi đặt cái IF "con" không giống như ở trên, sao cũng được bạn à, miễn là đừng sai cú pháp của IF. www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 63 Và nếu thích, bạn có thể ghép tất cả lại: =IF(A1 >= 1000, IF(A1 >= 10000, "Really big!!", Big!"), IF(A1 0) Tổng của B3 đến B7: SUM(B3:B7) Rồi, bây giờ ghép chữ nếu vào: =IF(AND(B3:B7 > 0), SUM(B3:B7), "") Đây là một dạng công thức mãng. Bạn thử Enter xem, Excel sẽ báo lỗi #VALUE! liền. Bạn phải Ctrl-Shift-Enter mới được. www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 64 Ví dụ: Lập công thức trong F3:F8, biết rằng nó là tổng số tiền ở cột C nếu năm (year) ở cột B bằng năm ở cột E tương ứng. (Ví dụ: ở F3 là tổng số tiền của năm 2009) Các bạn xem lại công thức của bài trên một tí: =IF(AND(B3:B7 > 0), SUM(B3:B7), "") Công thức này, tôi sẽ sửa lại như sau: =SUM(IF(B3:B7 > 0, B3:B7, "") sẽ cho kết quả tương đương. Chúng ta sẽ dùng kiểu công thức thứ hai (tính SUM) cho bài này: =SUM(IF(YEAR($B$3:$B$18) = E3, $C$3:$C$18, 0)) Nhấn Ctrl-Shift-Enter sau khi gõ công thức. Và đây là đáp số: www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 65 IFERROR Trả về một giá trị xác định trước nếu công thức có lỗi, hoặc trả về kết quả của công thức nếu công thức đó không có i.lỗ Thường dùng IFERROR để bẫy lỗi trong các công thức. Cú pháp: =IFERROR(value, value_if_error) value: Là một biểu thức, một công thức cần kiểm tra có lỗi hay không. value_if_error: Giá trị trả về nếu value gây ra lỗi, là các loại lỗi sau đây: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, hoặc #NULL!. Lưu ý: Nếu value hoặc value_if_error là một ô rỗng, IFERROR() sẽ xem chúng như những chuỗi rỗng (""). Nếu value là một công thức mảng, IFERROR() trả về một mảng các kết quả cho mỗi ô của mảng trong value Ví dụ: Dùng hàm IFERROR bẫy lỗi Ví dụ: Chuyển đổi điểm chữ sang điểm số dựa trên bảng tra, nếu không tìm thấy trong bảng thì trả về “Ngoài bảng tra”. =IFERROR(VLOOKUP(F2,$A$2:$B$12,2,0),"Ngoài bảng tra") www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 66 IFNA (Excel 2013) Trả về giá trị xác định trước nếu biểu thức kiểm tra bị lỗi #N/A, nếu biểu thức không có lỗi thì trả về kết quả của biểu thức. Cú pháp: =IFNA(value, value_if_na) Value là biểu thức cần kiểm tra lỗi #N/A Value_if_na Giá trị trả về nếu biểu thức kiểm tra bị lỗi #N/A Lưu ý: Nếu Value hay Value_if_na là ô rỗng, IFNA xem như là chuỗi rỗng “” Nếu Value là công thức mảng, IFNA trả về một mảng kết quả của các ô tương ứng. Ví dụ: Chuyển đổi điểm chữ sang điểm số dựa trên bảng tra, nếu không tìm thấy trong bảng thì trả về “Ngoài bảng tra”. ==IFNA(VLOOKUP(F2,$A$2:$B$12,2,0),"Ngoài bảng tra") www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 67 NOT Cho kết quả là phép phủ định của biểu thức logíc. Hàm NOT cho kết quả TRUE nếu biểu thức logic là FALSE và ngược lại. Cú pháp: =NOT(logical) logical: một biểu thức, một điều kiện kiểu logic Ví dụ: =NOT(3>2) → TRUE OR Trả về giá trị TRUE nếu có một hay nhiều đối số là đúng (TRUE); trả về giá trị FALSE nếu tất cả các đối số là sai (FALSE). Có thể dùng hàm OR bất cứ chỗ nào, nhưng thường thì OR hay được dùng chung với hàm IF. Cú pháp: =OR(logical1, [, logical2 ]) Logical1,logical2, Có từ 1 đến 255 điều kiện cần kiểm tra xem TRUE hay FALSE (trong Excel 2003 trở về trước, con số này là 30). Lưu ý: Các đối số phải có giá trị logic là TRUE hoặc là FALSE. Nếu đối số là mảng hay tham chiếu thì mảng hay tham chiếu đó chỉ chứa những giá trị logic. Nếu đối số là mảng hay tham chiếu có chứa chữ hoặc những ô rỗng, các giá trị đó sẽ được bỏ qua. Nếu dãy không chứa các trị logic, OR sẽ trả về giá trị lỗi #VALUE! www.giaiphapexcel.com
Hàm Luận Lý (Logical Functions) Giải Pháp Excel | 68 Ví dụ: Chỉ khi tất cả biểu thức đều False thì hàm Or mới trả về kết quả là False. TRUE Trả về giá trị là TRUE Cú pháp: =TRUE() Không có đối số Chúng ta có thể nhập trực tiếp giá trị TRUE vào công thức hoặc hàm khi tính toán. Excel sẽ tự hiểu đó là giá trị luận lý có giá trị TRUE. XOR (Excel 2013) Trả về kết quả là TRUE nếu số lượng đối số TRUE là số lẻ, trả về FALSE khi số đối số TRUE là số chẵn. Cú pháp: =XOR(logical1, [logical2], ) Logical1, logical2, có từ 1 đến 254 đối số cần kiểm tra xem TRUE hay FALSE, có thể là giá trị logic, mảng hay vùng tham chiếu. Lưu ý: Các đối số phải trả về dạng TRUE hay FALSE, trong mảng hay vùng tham chiếu thì cũng phải là các giá trị logic. Nếu mảng hay vùng tham chiếu chứa chuỗi hoặc rỗng thì các giá trị này sẽ bị bỏ qua. Nếu vùng tham chiếu không chứa giá trị logic thì hàm trả về lỗi #VALUE! Bạn có thể dùng hàm XOR dưới dạng công thức mảng để xem các giá trị logic chứa trong mảng. Ví dụ: =XOR(1-1=2,3>4,4>3) TRUE =XOR(True,True,False,False) FALSE =XOR(False,False,True) TRUE www.giaiphapexcel.com
Hàm Ngày Tháng & Thời Gian (Date And Time Functions) Giải Pháp Excel | 69 II.3. HÀM NGÀY THÁNG & THỜI GIAN (DATE AND TIME FUNCTIONS) Nhóm hàm về Ngày Tháng và Thời Gian giúp chúng ta chuyển đổi những giá trị ngày tháng và thời gian thành những con số để có thể tính toán với chúng. Hệ thống ngày giờ trong Excel phụ thuộc vào cách thiết lập trong Regional Options của Control Panel. Trong phần này, tôi dùng định dạng ngày tháng theo kiểu Việt Nam: ngày/tháng/năm (dd/mm/yyyy). DATE Trả về một ngày tháng năm nào đó Cú pháp: =DATE(year, month, day) year: Số chỉ năm. Con số này có thể là 1 đến 4 ký số. Nếu nhỏ hơn 1900, Excel sẽ tự động cộng thêm 1900 vào để tính (ví dụ year = 100 thì Excel sẽ hiểu đó là năm 2000) Nếu từ 1900 đến 9999, thì Excel sẽ coi đó chính là năm ncầ tính Nếu nhỏ hơn 0 hoặc lớn hơn 10.000, Excel sẽ báo lỗi #NUM! month: Số chỉ tháng. Nếu con số này lớn hơn 12, thì Excel sẽ tự động quy đổi thành 12 bằng 1 năm và tăng số năm lên. day: Số chỉ ngày. Nếu con số này lớn hơn số ngày của tháng, thì Excel sẽ tự động quy đổi thành số ngày nhiều nhất của tháng cho phù hợp và tăng số tháng lên, nếu cần thì tăng cả số năm lên luôn. Ví dụ: =DATE(2014, 12, 25) kết quả là ngày 25/12/2014 =DATE(2014, 06, 32) → 02/07/2014 =DATE(2013, 13, 25) → 25/01/2014 =DATE(7, 25, 50) = 19/02/1909 (số ngày (date) = 50, lớn hơn số ngày nhiều nhất của một tháng (31), do đó, Excel sẽ lấy ngày là 19, và tăng số tháng (month) thêm 1; số tháng (month) = 25 + 1 = 26 = 2 + (2 x 12), do đó Excel sẽ lấy tháng là 02, và tăng số năm thêm 2; số năm (year) = 7 + 2 = 9, Excel sẽ lấy 9 + 1900 = 1909) Hàm DATE rất hữu dụng khi year, month, day là những công thức mà không phải là một con số, nó sẽ giúp chúng ta tính toán chính xác hơn. Khi nhập hàm DATE, bạn phải cẩn thận thứ tự year, month, day, vì nó rất dễ nhầm lẫn (theo kiểu VN chúng ta: ngày, tháng, năm) www.giaiphapexcel.com
Hàm Ngày Tháng & Thời Gian (Date And Time Functions) Giải Pháp Excel | 70 DATEDIF Có lẽ cách dễ nhất khi muốn tính toán ngày tháng năm là dùng hàm DATEDIF. Nhưng có một điều tôi không hiểu là: hàm này dùng tốt, nhưng không hề có trong danh sách hàm của Excel (Excel 2007 cũng không), và cũng không có một cái help nào cho nó cả! Cho nên, có một số người sử dụng Excel phải nói là có thâm niên, mà vẫn không hề biết Excel có hàm DATEDIF() Cú pháp: =DATEDIF(start_day, end_day, unit) start_day: Ngày đầu end_day: Ngày cuối (phải lớn hơn ngày đầu) unit: Chọn loại kết quả trả về (khi dùng trong hàm phải gõ trong dấu ngoặc kép) y : số năm chênh lệch giữa ngày đầu và ngày cuối m : số tháng chênh lệch giữa ngày đầu và ngày cuối d : số ngày chênh lệch giữa ngày đầu và ngày cuối md : số ngày chênh lệch giữa ngày đầu và tháng ngày cuối, mà không phụ thuộc vào số năm và số tháng ym : số tháng chênh lệch giữa ngày đầu và ngày cuối, mà không phụ thuộc vào số năm và số ngày yd : số ngày chênh lệch giữa ngày đầu và ngày cuối, mà không phụ thuộc vào số năm Ví dụ: =DATEDIF("01/01/2000", "31/12/2100", "y") = 100 (năm) =DATEDIF("01/01/2000", "31/12/2100", "m") = 1211 (tháng) =DATEDIF("01/01/2000", "31/12/2100", "d") = 36889 (ngày) =DATEDIF("01/01/2000", "31/12/2100", "md") = 30 (= ngày 31 - ngày 1) =DATEDIF("01/01/2000", "31/12/2100", "ym") = 11 (= tháng 12 - tháng 1) =DATEDIF("01/01/2000", "31/12/2100", "yd") = 365 (= ngày 31/12 - ngày 1/1) Tính số ngày làm việc giữa hai khoảng thời gian Bình thường, nếu lấy ngày tháng trừ ngày tháng, kết quả sẽ bao gồm luôn những ngày lễ, ngày nghỉ, v.v Còn nếu tính số ngày làm việc trong một khoảng thời gian, thì phải trừ bớt đi những ngày không làm việc. Trong Excel có một hàm chuyên để tính toán những ngày làm việc giữa hai khoảng thời gian mà không bao gồm các ngày thứ Bảy, Chủ Nhật và những ngày nghỉ khác được chỉ định: Hàm NETWORKDAYS (đúng nguyên nghĩa của nó: net workdays). Dĩ nhiên hàm này chỉ thích hợp với những cơ quan làm việc 5 ngày một tuần, chứ như chúng ta, làm tuốt, có khi là 365 ngày một năm thì hàm này vô tác dụng! www.giaiphapexcel.com
Hàm Ngày Tháng & Thời Gian (Date And Time Functions) Giải Pháp Excel | 71 DATEVALUE Chuyển đổi một chuỗi văn bản có dạng ngày tháng năm thành một giá trị ngày tháng năm để có thể tính toán được Cú pháp: =DATEVALUE(date_text) date_text: Chuỗi văn bản cần chuyển đổi Lưu ý: date_text có giới hạn trong khoảng từ 01/01/1900 đến 31/12/9999, nếu nằm ngoài khoảng này, hàm sẽ báo lỗi #VALUE! date_text phải được nhập trong cặp dấu móc kép (“”) Nếu date_text chỉ có hai phần, Excel sẽ hiểu như sau: nếu phần sau là một giá trị 12 và phần đầu là một giá trị < 13, nó xem như phần đầu là tháng, phần sau là năm, và cho giá trị tính ngày là 1. Ví dụ: =DATEVALUE("25/12/2014") → 41998 → 25/12/2014 =DATEVALUE("25/12") → 41998 → 25/12/2014 =DATEVALUE("12/25") → 45992 → 01/12/2025 =DATEVALUE("12/25/2014") → #VALUE! (do định dạng trong Control Panel là dd/mm/yyyy) =DATEVALUE("25 December 2014") → 41998 → 25/12/2014 DAY Cho biết số chỉ ngày trong một giá trị ngày tháng Cú pháp: =DAY(serial_number) serial_number: Biểu thức ngày tháng hoặc là một con số chỉ giá trị ngày tháng Ví dụ: Hôm nay là ngày 5/5/2014. Bạn gõ vào =DAY(TODAY()) → 5 DAYS (Excel 2013) Trả về số ngày giữa hai ngày cho trước. Cú pháp: =DAYS(end_date, start_date) End_date, Start_date là ngày kết thúc và bắt đầu mà ta muốn tính số ngày có trong thời đoạn này. Lưu ý: Excel lưu ngày dưới dạng số tuần tự. Ngày 1/1/1990 được ghi nhận là số 1 www.giaiphapexcel.com
Hàm Ngày Tháng & Thời Gian (Date And Time Functions) Giải Pháp Excel | 72 Nếu end_date và start_date đều là các con số thì hàm sẽ lấy end_date - start_date để tính toán số ngày giữa hai ngày. Nếu có một trong hai đối số là ngày dạng chuỗi thì hàm sẽ tự động chuyển chuỗi thành số bằng hàm DATEVALUE(date_text) rồi tính toán. Nếu đối số là con số vượt ngoài vùng dữ liệu ngày tháng thì hàm trả về lỗi #NUM! Nếu đối số là chuỗi ngày tháng không hợp lệ thì hàm trả về lỗi #VALUE! Ví dụ: DAYS360 Trả về số ngày nằm giữa hai ngày cho trước (một năm có 12 tháng * 30 ngày = 360 ngày). Cú pháp: =DAYS360(start_date, end_date [, method]) start_date, end_date: Ngày tháng đại diện cho ngày bắt đầu và ngày kết thúc của khoảng thời gian cần tính toán. Nên nhập bằng hàm DATE, hoặc dùng một kết quả trả về của một công thức khác, vì có thể sẽ xảy ra lỗi nếu bạn nhập trực tiếp ngày tháng dưới dạng text. method: Một giá trị logic (TRUE, FALSE) để chỉ cách tính toán, theo kiểu châu Âu hay theo kiểu Mỹ. FALSE: (hoặc không nhập) Tính toán theo kiểu Mỹ: Nếu start_date là ngày 31 của tháng, thì nó được đổi thành ngày 30 của tháng đó. Nếu end_date là ngày 31 của tháng và start_date nhỏ hơn 30, thì end_date được đổi thành ngày 1 của tháng kế tiếp. TRUE: Tính toán theo kiểu châu Âu: Hễ start_date hoặc end_date mà rơi vào ngày 31 của một tháng thì chúng sẽ được đổi thành ngày 30 của tháng đó. Ví dụ: So sánh số ngày chênh lệch giữa 01/01/2008 và 31/5/2008 theo kiểu một năm có 360 ngày và theo kiểu thườ ng (dùng hàm DATEDIF) www.giaiphapexcel.com
Hàm Ngày Tháng & Thời Gian (Date And Time Functions) Giải Pháp Excel | 73 =DAYS360("01/01/2008", "31/5/2008") = 150 =DAYS360("01/01/2008", "31/5/2008", TRUE) = 149 =DATEDIF("01/01/2008", "31/5/2008", "d") = 151 Ghi chú: Tính số ngày chênh lệch theo kiểu một năm có 360 ngày Hiện nay, vẫn còn một số hệ thống kế toán dùng kiểu tính thời gian là một tháng coi như có 30 ngày và một năm coi như có 360 ngày! Gặp trường hợp này, việc tính toán thời gian sẽ không đơn giản, vì thực tế thì số ngày trong mỗi tháng đâu có giống nhau. Có lẽ vì nghĩ đến chuyện đó, nên Excel có một hàm dành riêng cho các hệ thống kế toán dựa trên cơ sở một năm có 360 ngày, đó là hàm DAYS360. EDATE Hàm này trả về một ngày nào đó tính từ mốc thời gian cho trước và cách mốc thời gian này một số tháng được chỉ định. Thường người ta dùng hàm này để tính hạn bảo hành cho một sản phẩm, hoặc ngày đáo hạn hợp đồng Cú pháp: =EDATE(start_date, months) start_date: Ngày dùng làm mốc để tính. Nên nhập ngày này bằng hàm DATE(), hoặc dùng một kết quả trả về của một công thức khác, vì có thể sẽ xảy ra lỗi nếu bạn nhập trực tiếp ngày tháng dưới dạng text. months: Số tháng trước hoặc sau mốc thời gian start_date (nếu trước thì dùng số âm). Trong trường hợp months là số không nguyên, nó sẽ được làm tròn. Ô A1 đang chứa ngày cuối tháng 01/2008, tính ngày cuối tháng sau 3 tháng nữa, dùng công thức: =EDATE(A1, 3) → 30/4/2008 Lưu ý: EDATE() không phải là hàm để tính ngày cuối tháng, mà cho kết quả là ngày trùng với ngày của mốc thời gian muốn tính (start_date). Nếu như trường hợp kết quả trả về là một ngày không hợp lệ của một tháng (ngày 31/4 chẳng hạn), thì EDATE() sẽ lấy ngày cuối tháng của tháng đó (30/4). Ví dụ: Tôi mua một cái USB ngày hôm nay (08/01/2008), hạn bảo hành 36 tháng, vậy nó được bảo hành tới ngày nào? = EDATE(TODAY(), 36) = 08/01/2011 EOMONTH Trả về ngày cuối một tháng xác định. Cú pháp: =EOMONTH(start_date, months) www.giaiphapexcel.com
Hàm Ngày Tháng & Thời Gian (Date And Time Functions) Giải Pháp Excel | 74 start_date: Ngày dùng làm mốc để tính. Cũng giống hàm EDATE(), nên nhập ngày này bằng hàm DATE(), hoặc dùng một kết quả trả về của một công thức khác, vì có thể sẽ xảy ra lỗi nếu bạn nhập trực tiếp ngày tháng dưới dạng text. months: Số tháng trước hoặc sau mốc thời gian start_date (nếu trước thì dùng số âm). Trong trường hợp months là số không nguyên, nó sẽ được làm tròn. Ví dụ: =EOMONTH(TODAY(), 25) → 40237 = 28/02/2010 HOUR Cho biết số chỉ giờ trong một giá trị thời gian Cú pháp: =HOUR(serial_number) serial_number: Biểu thức thời gian hoặc là một con số chỉ giá trị thời gian Ví dụ: =HOUR(0.5) → 12 (giờ) ISOWEEKNUM (Excel 2013) Trả về số tuần ISO của năm theo ngày cho trước. Cú pháp: =ISOWEEKNUM(date) Date Là ngày tháng cần tính tuần trong năm Lưu ý: ISO WEEKNUM mặc định ngày thứ 2 là ngày đầu tuần và tuần đầu tiên của năm là tuần có chứa ngày thứ Năm. Excel lưu ngày dưới dạng số tuần tự. Ngày 1/1/1990 được ghi nhận là số 1 Nếu date là số không hợp lệ, hàm trả về lỗi #NUM! Nếu date là ngày không hợp lệ, hàm trả về lỗi #VALUE! Ví dụ: www.giaiphapexcel.com