Đề tài Thư viện hàm Excel: hàm văn bản

Microsoft Excel 2007 Công thức và Hàm 1 2 49 50 51 52 53 54 55 56 57 153 154 155 156 157 158 159 160 161 162 163 177 178 179 180 181 182 183 184 185 190 191 192 C D E F G H I J K L M N THƯ VIỆN HÀM EXCEL 12 Hàm 2007 12 Cập nhật: 10-Jun-07 148 Tổng số hàm: 348 ASC Dùng cho các ngôn ngữ sử dụng bảng mã 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 ô 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ụ Hàm EXCEL D56=ASC("EXCEL") BAHTTEXT Chuyển một số sang chuỗi tiếng Thai và thêm hậu tố "Baht." vào Trong Microsoft Excel for Windows, bạn có thể đổi định dạng Baht thành các kiểu đkha1c bằng cách vào Control Panel, Regional and Language Options. Trong Excel for Macintosh, bạn có thể đổi định dạng Baht thành kiểu khác bằng cách sử dụng Control Panel for Numbers. Cú pháp BAHTTEXT(number) Number là số mà bạn muốn chuyển đổi thành chuỗi hoặc tham chiếu đến ô chứa số hoặc là công thức mà kết quả là số Ví dụ Con số Hàm 123 หน่ึงร้อยยีส่ิบสามบาทถ้วน E162=BAHTTEXT(D162) CHAR Trả về ký tự tương ứng với mã số của nó Cú pháp CHAR(Number) Number là mã ASCII của ký tự mà bạn muốn chuyển sang ký tự Ghi chú 65 A E183=CHAR(D183) 33 ! E184=CHAR(D184) CLEAN Loại bỏ các ký tự không in được ra khỏi văn bản Cú pháp Những hàm văn bản trong Excel giúp ta điều khiển các chuỗi văn bảng có trong bảng tính. Chúng ta có thể định dạng văn bản, tìm kiếm, thay thế và thực hiện rất nhiều thao tác trên văn bản. IFERROR Hàm văn bản Trần Thanh Phong 1 Microsoft Excel 2007 Công thức và Hàm 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 212 213 214 215 216 217 218 219 220 221 222 223 224 318 319 320 321 322 323 324 C D E F G H I J K L M N CLEAN(text) Text là chuỗi mà bạn muốn loại bỏ các ký tự không tin được Vií dụ text text E196=CLEAN(D196) Loại ký tự không in được CHAR(7) ra khỏi chuỗi D196=CHAR(7)&"text"&CHAR(7) CODE Trả về mã tương ứng với ký tự đầu tiên trong văn bản Cú pháp CODE(text) Text là một chuỗi văn bản mà bạn muốn trả về mã của ký tự đầu Ví dụ B 66 E205=CODE(D205) Bill Gate 66 E206=CODE(D206) CONCATENATE Nối kết các chuỗi văn bản thành một chuỗi văn bản đơn Cú pháp CONCATENATE (text1,text2,...) Ví dụ Các chuỗi Chúc mừng sinh nhật GPE lần thứ 1 Chúc mừng sinh nhật GPE lần thứ 1 D223=CONCATENATE(D219," ",D220," ",D221) DOLLAR Đổi một số sang một chuỗi với định dạng tiền tệ tương ứng có định dạng là $#,##0.00_);($#,##0.00) Cú pháp DOLLAR(number,decimals) Number là một giá trị số, một công thức mà kết quả là một số hay là một tham chiếu tới một ô chứa giá trị số Decimals là số những con số thập phân sau dấu chấm. Nếu bỏ qua Excel cho 2 số lẻ Ghi chú Text1, Text2,… có 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ố Trần Thanh Phong 2 Microsoft Excel 2007 Công thức và Hàm 325 326 327 328 329 330 331 332 333 351 352 353 354 355 356 357 358 359 360 361 362 363 395 396 397 398 399 400 401 402 403 404 405 406 C D E F G H I J K L M N Điểm khác biệt giữa dùng lệnh định dạng và dùng hàm Dollar là dùng hàm thì sẽ chuyển đổi định dạng của giá trị sang chuỗi Ví dụ Dữ liệu Hàm 1234.567 $1,234.57 E328=DOLLAR(D328, 2) Định dạng con số dưới dạng tiền tệ và làm tròn đến 2 số lẻ thập phân -1234.567 $1,200 E329=DOLLAR(D328, -2) Định dạng con số dưới dạng tiền tệ và làm tròn đến 2 ký tự số bên trái dấu thập phân -0.123 ($1,200) E330=DOLLAR(D329, -2) Định dạng con số dưới dạng tiền tệ và làm tròn đến 2 ký tự số bên trái dấu thập phân 99.888 ($0.1230) E331=DOLLAR(D330, 4) Định dạng con số dưới dạng tiền tệ và làm tròn đến 4 số lẻ thập phân $99.89 E332=DOLLAR(D331) Định dạng con số dưới dạng tiền tệ và làm tròn đến 2 số lẻ thập phân EXACT Cú pháp EXACT(text1,text2) Text1 và Text2 là hai chuỗi văn bản cần so sánh Ghi chú Ta có thể dùng toán tử so sánh (==) thay cho hàm EXACT. Ví dụ =A1==B1 tương đương với =EXACT(A1,B1). Ví dụ 1 Chuỗi 1 Chuỗi 2 Hàm Exact Hàm IF word word TRUE TRUE F360=EXACT(D360,E360) G360=IF(D360=E360,TRUE,FALSE) Word word FALSE TRUE F361=EXACT(D361,E361) G361=IF(D361=E361,TRUE,FALSE) w ord word FALSE FALSE F362=EXACT(D362,E362) G362=IF(D362=E362,TRUE,FALSE) FIND, FINDB 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 định của Office (Office, Excel Options,Popular, Language setting…) FINDB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc đị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 FIND(find_text,within_text,start_num) FINDB(find_text,within_text,start_num) Find_text là chuỗi ta muốn tìm Within_text là văn bản chứa chuỗi cần tìm Start_num là vị trí bắt đầu tìm, ký tự đầu tiên trong within_text là ký tự số 1. Nếu bỏ qua start_num thì Excel xem như là 1 Ghi chú Trả về TRUE nếu những chuỗi văn bản trong các tham số là giống hệt nhau, nếu không giống thì trả về FALSE. Hàm bỏ qua các định dạng nhưng phân biệt chữ HOA và thường Tìm một chuỗi văn bản trong một vă bản khác. Giá trị trả về là vị trí đầ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) Trần Thanh Phong 3 Microsoft Excel 2007 Công thức và Hàm 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 439 440 441 442 443 444 445 446 C D E F G H I J K L M N Nếu find_text là "" (rỗng), hàm FIND trả về vị trí ký tự đầu tiên trong chuỗi tìm (đó là vị trí start_num hoặc 1). 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à nhỏ 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 để bỏ qua các ký tự đầu chuỗi mà ta không cấn tìm. Ví dụ 1: Xin chào Microsoft Excel 2007 Hàm 23 D417=FIND("e",D415,1) Tìm ký tự "e" trong chuỗi bằng hàm FIND 20 D418=SEARCH("e",D415,1) Tìm ký tự "e" trong chuỗi bằng hàm SEARCH 2 D419=FIND("i",D415) Tìm ký tự "i" trong chuỗi 11 D420=FIND("i",D415,4) Tìm ký tự "i" trong chuỗi bắt đầu tìm tại vị trí ký tự thứ 4 Ví dụ 2 Các chuỗi Hàm Ceramic Insulators #124-TD45-87 Ceramic Insulators G424=MID(D424,1,FIND(" #",D424,1)-1) Copper Coils #12-671-6772 Copper Coils Trích chuỗi từ vị trí 1 đến vị trí của ký tự "#" trừ 1 Variable Resistors #116010 Variable Resistors Ví dụ 3 Hiển thị tên cột Cột thứ Tên chữ 5 E E430=LEFT(ADDRESS(1,D430,4),FIND(1,ADDRESS(1,D430,4))-1) 200 GR 15634 WCH 16385 #VALUE! Báo lỗi vì vượt quá số cột cho phép của Excel 2007 FIXED Làm tròn một số tới một số chính xác cho trước và đổi một số sang định dạng văn bản với những dấu chấm và dấu phẩy tương ứng Cú pháp FIXED(number,decimals,no_commas) Number là con số cần làm tròn và chuyển đổi Decimals là số con số trước và sau dấu thập phân (số số lẽ) No_commas là giá trị luận lý. Nếu TRUE không có dấu phẩy trong văn bản trả về. Nếu FALSE hay không có văn bản trả về sẽ có dấu phẩy Ghi chú 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. Trần Thanh Phong 4 Microsoft Excel 2007 Công thức và Hàm 447 448 449 450 451 452 453 454 455 456 457 588 589 590 591 592 593 594 595 596 597 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 C D E F G H I J K L M N Nếu decimals là số âm, number sẽ được làm tròn về bên trái của dấu phân cách thập phân. Nếu không khai báo decimals, Excel xem như là số 2. Nếu no_commas là FALSE hoặc không khai báo thì hàm trả về chuỗi có dấu phẩy. Ví dụ Dữ liệu Hàm 1234.567 1,234.6 E453=FIXED(D453, 1) Vì decimals = 1 là số dương, vậy làm tròn đến 1 con số sau dấu thập phân -1235.567 1,230 E454=FIXED(D453, -1) Vì decimals = -1 là số âm, vậy làm tròn 1 số trước dấu thập phân 44.332 -1240 E455=FIXED(D454, -1, TRUE) Làm tròn trước dấu thập phân 1 con số và không dùng dấu phẩy phân cách trong kết quả. 44.33 E456=FIXED(D455) Làm tròn đến 2 con số sau dấu thập phân JIS 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, Regional and Language Options Đố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 đó Ví dụ Xem ví dụ trong Help LEFT, LEFTB Trả về các ký tự bên trái xác định từ một chuỗi văn bản. LEFTB trả về các ký tự bên trái chuỗi văn bản dựa trên số byte xác định. LEFT dùng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), LEFTB dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS) LEFT luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Office (Office, Excel Options,Popular, Language setting…) LEFTB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc đị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 LEFT(text,num_chars) LEFTB(text,num_bytes) Text là chuỗi văn bản cần trích Num_chars xác định số ký tự cần trích Num_bytes xác định số ký tự cần trích dựa trên bytes. Ghi chú Num_chars phải lớn hơn hoặc bằng 0 Nếu num_chars lớn hơn chiều dài chuỗi hàm trả về cả chuỗi. Điểm khác biệt chính giữa định dạng ô chứa số và dùng hàm FIXED là hàm chuyển kết quả của nó sang chuỗi. Con số định dạng bằng công cụ định dạng vẫn là một con số. Trần Thanh Phong 5 Microsoft Excel 2007 Công thức và Hàm 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 C D E F G H I J K L M N Nếu num_chars không khai báo thì Excel xem như 1 Ví dụ 1 Các chuỗi Hàm Chào các bạn Chào F619=LEFT(D619,4) Lấy 4 ký tự bên trái Nguyễn Hoàng Long N F620=LEFT(D620) Lấy 1 ký tự bên trái Ví dụ 2 Tách họ, chữ lót và tên từ chuỗi đầy đủ họ và tên Phương án thay thế nếu không có Excel 2007 Họ và Tên Họ Chữ lót Tên Họ Tên Trần Thanh Phong Trần Thanh Phong Trần Phong Trương Thị Hồng Minh Trương Thị Hồng Minh Trương Minh Mai Thanh Mai Thanh Mai Thanh Hồng Hồng Hồng F624=IFERROR(LEFT(D624,FIND(" ",D624)-1)," ") G624=IF(LEN(F624&H624)+2>=LEN(D624),"",MID(D624,LEN(F624)+2,LEN(D624)-LEN(F624&H624)-2)) H624=IFERROR(RIGHT(D624,LEN(D624)-FIND("*",SUBSTITUTE(D624," ","*",LEN(D624)- LEN(SUBSTITUTE(D624," ",""))))),D624) LEN, LENB LEN Trả về chiều dài của chuỗi. Hàm này đếm khoảng trắng cũng như ký tự. LENB trả về số byte của chuỗi LEN dùng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), LENB dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS) LEN luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Office (Office, Excel Options,Popular, Language setting…) LENB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc đị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 LEN(text) LENB(text) Text là một chuỗi văn bản mà ta cần xác định chiều dài, khoảng trắng đếm như ký tự Ví dụ 1 LEN Các chuỗi Hàm Chào các bạn 12 F644=LEN(D644) Tính chiều dài của chuỗi văn bản Nguyễn Hoàng Long 17 F645=LEN(D645) Xem thêm ví dụ nâng cao tại hàm LEFT (ví dụ 2) Ví dụ 2 Loại bỏ danh xưng trong tên Họ và Tên Hàm Mrs. James Jones James Jones F651=IF(OR(LEFT(D651,2)={"Mr","Dr","Ms"}),RIGHT(D651,LEN(D651)-(FIND(".",D651)+1)),D651) Dr. Richard Speck Richard Speck J624=IF(ISERR(FIND(" ",D624))," ", LEFT(D624,FIND(" ",D624)-1)) K624=IF(ISERR(FIND(" ",D624)),D624, RIGHT(D624, LEN(D624)-FIND("*", SUBSTITUTE(D624," ","*",LEN(D624)- LEN(SUBSTITUTE(D624," ","")))))) Trần Thanh Phong 6 Microsoft Excel 2007 Công thức và Hàm 653 654 655 656 657 658 659 660 661 670 671 672 673 674 675 676 677 678 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 C D E F G H I J K L M N Mr. Ed Ed Ví dụ 3 Đếm số từ trong chuỗi Các chuỗi Hàm how many words? 3 F657=IF(LEN(D657)=0,0,LEN(TRIM(D657))-LEN(SUBSTITUTE(TRIM(D657)," ",""))+1) 1 3 4 5 6 9 6 Microsoft Excel 2 Excel 1 LOWER Đổi một chuỗi văn bản ra chữ thường Cú pháp LOWER(text) Text là chuỗi văn bản cần chuyển đổi, hàm không thay đổi các ký tự trong chuỗi không là ký tự Ví dụ Chuỗi Hàm XIN CHÀO xin chào E677=LOWER(D677) Đổi chữ HOA sang chữ thường MID, MIDB MID trích một số ký tự trong chuỗi văn bản với vị trí bắt đầu xác định và số ký tự cần trích xác định MIDB trích một số ký tự trong chuỗi văn bản với vị trí bắt đầu xác định và số byte cần trích xác định MID dùng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), MIDB dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS) MID luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Office (Office, Excel Options,Popular, Language setting…) MIDB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc đị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 MID(text,start_num,num_chars) MIDB(text,start_num,num_bytes) Text là chuỗi văn bản chứa các ký tự cần trích ra Start_num là vị trí của ký tự đầu tiên cần trích ra trong chuỗi văn bản. Num_chars là số ký tự cần trích ra Num_bytes là số byte cần trích ra Ghi chú Nếu start_num lớn hơn chiều dài chuỗi văn bản hàm MID trả về "" (chuỗi rỗng). Nếu start_num nhỏ hơn 1 hàm MID trả về lỗi #VALUE!. Nếu start_num ít hơn chiều dài của chuỗi văn bản, nhưng start_num + num_chars vượt quá chiều dài chuỗi thì hàm MID trả về các ký tự từ vị trí bắt đầu đến hết chuỗi Trần Thanh Phong 7 Microsoft Excel 2007 Công thức và Hàm 703 704 705 706 707 708 709 710 711 712 713 714 715 778 779 780 781 782 783 784 785 786 787 788 799 800 801 802 803 804 805 806 807 808 809 820 C D E F G H I J K L M N Nếu num_chars là số âm hàm MID trả về lỗi #VALUE!. Nếu num_bytes là số âm hàm MIDB trả về lỗi #VALUE!. Ví dụ 1 Các chuỗi Hàm Chào các bạn các F707=MID(D707,6,3) Trích chữ "các" ra khỏi chuỗi Nguyễn Hoàng Long Hoàng F708=MID(D708,8,5) Trích chữ "Hoàng" ra khỏi chuỗi Xem thêm ví dụ nâng cao tại hàm LEFT (ví dụ 2) Ví dụ 2: Trích tên tập tin Hàm c:\windows\text.txt text.txt F714=MID(D714,FIND("*",SUBSTITUTE(D714,"\","*",LEN(D714)-LEN(SUBSTITUTE(D714,"\",""))))+1,LEN(D714)) PHONETIC Trích các ký tự ngữ âm (furigana) từ chuỗi văn bản Cú pháp PHONETIC(reference) Reference là chuỗi văn bản, địa chỉ ô tham chiếu hoặc vùng địa chỉ chứa các ký tự furigana. Ghi chú Nếu reference là vùng địa chỉ thì các ký tự furigana nằm trong ô địa chỉ ở góc trên bên trái vùng sẽ được trích Nếu reference là vùng địa chỉ các ô không liền nhau hàm trả về lỗi #N/A Ví dụ Xem ví dụ trong Help PROPER Đổi sang chữ HOA các ký tự đầu trong một chữ, các chữ còn lại là chữ thường Cú pháp PROPER(text) Text là chuỗi văn bản cần chuyển đổi Ví dụ Các chuỗi Hàm Xin chào Xin Chào F806=PROPER(D806) 2-cent's worth 2-Cent'S Worth nGUyễn hoÀng lOng Nguyễn Hoàng Long REPLACE, REPLACEB Trần Thanh Phong 8 Microsoft Excel 2007 Công thức và Hàm 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 C D E F G H I J K L M N Replace thay thế một phần của chuỗi văn bản bắt đầu tại vị trí và số ký tự cho trước bằng một chuỗi văn bản khác. Replaceb thay thế một phần chuỗi bắt đầu tại vị trí và số byte cho trước bằng một chuỗi khác 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) REPLACE luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Office (Office, Excel Options,Popular, Language setting…) REPLACEB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc đị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 REPLACE(old_text,start_num,num_chars,new_text) REPLACEB(old_text,start_num,num_bytes,new_text) Old_text là chuỗi văn bản muốn thay một số ký tự. Start_num là vị trí ký tự trong old_text mà ta muốn thay bằng new_text. Num_chars là số ký tự trong old_text mà ta muốn hàm REPLACE thay bằng new_text. 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. Ví dụ 1 REPLACE Data Formula abcdefghijk abcde*k E837=REPLACE(D837,6,5,"*") Thay 5 ký tự, bắt đầu từ vị trí ký tự thứ 6 bằng dấu * 2009 2010 E838=REPLACE(D838,3,2,"10") Thay 2 ký tự cuối của 2009 bằng 10 123456 @456 E839=REPLACE(D839,1,3,"@") Thay 3 ký tự đầu bằng @ Ví dụ 2 REPLACEB Xem ví dụ trong Help REPT Lặp lại một chuỗi văn bản một số lần cho trước. Cú pháp REPT(text,number_times) Text là chuỗi văn bản bạn muốn lặp lại Number_times là số lần lặp, là số nguyên dương Ghi chú Nếu number_times là 0 (zero), hàm trả về "" (chuỗi rỗng). Nếu number_times không là số nguyên thì hàm bỏ phần số thập phân Kết quả của hàm không thể dài hơn 32,767 ký tự, nếu không hàm báo lỗi #VALUE!. Ví dụ 1 Hàm *-*-*- D856=REPT("*-", 3) Lặp lại chuỗi *- ba lần ---------- D857=REPT("-",10) Lặp lại chuỗi - mười lần Trần Thanh Phong 9 Microsoft Excel 2007 Công thức và Hàm 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 C D E F G H I J K L M N Ví dụ 2 Tháng SL bán Tháng 1 834 G861=REPT("=",E861/100) Tháng 2 1132 Tháng 3 1243 Tháng 4 1094 Tháng 5 902 Tháng 6 1543 Tháng 7 1654 Tháng 8 2123 Tháng 9 1566 Tháng 10 1434 Tháng 11 1321 Tháng 12 1654 Ví dụ 3 Tháng Dự toán Thực tế Sai lệch Jan 300 311 3.7% Jan Feb 300 298 -0.7% Feb Mar 300 305 1.7% Mar Apr 350 351 0.3% Apr May 350 402 14.9% May Jun 350 409 16.9% Jun Jul 500 421 -15.8% Jul Aug 500 454 -9.2% Aug Sep 500 474 -5.2% Sep Oct 500 521 4.2% Oct Nov 500 476 -4.8% Nov Dec 500 487 -2.6% Dec I882=IF(G882<0,REPT("n",-ROUND(G882*100,0)),"") RIGHT, RIGHTB RIGHT lấy một số các ký tự từ bên phải của một chuỗi văn bản dựa trên số ký tự ta xác định RIGHTB lấy một số các ký tự từ bên phải của một chuỗi văn bản dựa trên số byte ta xác định RIGHT dùng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), RIGHTB dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS) RIGHT luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Office (Office, Excel Options,Popular, Language setting…) Đồ thị ======== =========== ============ ========== ========= =============== ================ ===================== =============== ============== ============= ================ Thấp hơn dự toán Vượt dự toán n nnnn nnnnnnnnnnnnnnnn nnnnnnnnn nnnnn nnnnn nnn nn nnnnnnnnnnnnnnn nnnnnnnnnnnnnnnnn nnnn Trần Thanh Phong 10 Microsoft Excel 2007 Công thức và Hàm 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 974 975 976 C D E F G H I J K L M N RIGHTB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc đị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 RIGHT(text,num_chars) RIGHTB(text,num_bytes) Text là chuỗi văn bản chứa các ký tự cần trích Num_chars số ký tự mà hàm RIGHT cần trích ra. Num_bytes số byte mà hàm RIGHTB cần trích ra. Ghi chú Num_chars phải là số lớn hơn hay bằng 0 (zero). Nếu num_chars lớn hơn chiều dài của chuỗi văn bản, hàm RIGHT trả về cả chuỗi. Nếu num_chars không khai báo, Excel xem như là số 1. Ví dụ Các chuỗi Hàm Right Hàm Rightb Xin chào chào chào F910=RIGHT(D910,5) Nguyễn Hoàng Long g g G910=RIGHTB(D910,5) Xem thêm ví dụ nâng cao tại hàm LEFT (ví dụ 2) Ví dụ 2: Cho chuỗi Hàm Xin chào Microsoft Excel 2007 chào Microsoft Excel 2007 G916=RIGHT(D916,LEN(D916)-FIND(" ",D916,1)) HelloTheWorld HelloTheWorld G917=IFERROR(RIGHT(D917,LEN(D917)-FIND(" ",D917,1)),D917) HelloTheWorld HelloTheWorld G918=IF(ISERR(FIND(" ",D918)),D918,RIGHT(D918,LEN(D918)-FIND(" ",D918,1))) Trich tất cả chuỗi trừ từ đầu tiên, nếu dùng theo cách 1 thì sẽ báo lỗi khi chuỗi không có khoảng trắng, do vậy thêm hàm kiểm tra lỗi như cách 2 và 3 (cách 2 chỉ dùng được trên Excel 2007 với hàm mới IFError) Ví dụ 3 Tạo số thứ tự trong tiếng Anh Các con số Hàm 1 1st E925=D925&IF(OR(VALUE(RIGHT(D925,2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(D925))={1,2,3}),CHOOSE(RIGHT(D925),"st","nd","rd"),"th")) 3 3rd 45 45th 122 122nd SEARCH, SEARCH Tìm vị trí của một chuỗi trong một chuỗi thứ hai và trả về vị 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) Trần Thanh Phong 11 Microsoft Excel 2007 Công thức và Hàm 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 C D E F G H I J K L M N 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 định của Office (Office, Excel Options,Popular, Language setting…) SEARCHB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc đị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 là chuỗi văn bản mà ta muốn tìm Within_text là chuỗi văn bản mà ta cần tìm chuỗi find_text trong nó Start_num là vị trí trong chuỗi within_text tại đó hàm sẽ bằt đầu tìm. Ghi chú Dùng SEARCH và SEARCHB để xác định vị trí một ký tự hoặc chuỗi trong một chuỗi khác để sau đó 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 điều kiện. Dấu ? thay cho một ký tự nào đó và dấu * thay cho một chuỗi nào đó. Khi điều kiện lại là dấu ? hay * thì bạn đặt thêm dấu ~ phí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 nhỏ 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 để bỏ qua một số các ký tự bắt đầu chuỗi mà ta không quan tâm. Ví dụ Chuỗi Hàm Statements 7 E997=SEARCH("e",D997,6) Tìm ký tự "e"và bắt đầu tìm tại vị trí thứ 6 Profit Margin 8 E998=SEARCH(D999,D998) Tìm "margin" trong chuỗi "Profit Margin" margin Profit Amount E999=REPLACE(D998,SEARCH(D999,D998),6,"Amount") Thay từ "Margin" bằng từ "Amount" SUBSTITUTE Trong một chuỗi văn bản, thế một chuỗi mới cho một chuỗi cũ mà bạn đã xác định Cú pháp SUBSTITUTE(text,old_text,new_text,instance_num) Text là chuỗi hoặc tham chiếu đến ô chứa chuỗi văn bản cần thay thế. Old_text là chuỗi văn bản cần thay thế. New_text là chuỗi văn bản mới mà ta muốn thay cho old_text Ví dụ 1 Dữ liệu Hàm Số liệu doanh thuSố liệu chi phí E1048=SUBSTITUTE(D1048, "doanh thu", "chi phí") Thay thế "doanh thu" bằng "chi phí" Quí 1, 2008 Quí 2, 2008 E1049=SUBSTITUTE(D1049, "1", "2", 1) Thay thế chuỗi "1" bằng chuỗi "2" với vị trí thay thế thứ 1 Quí 1, 2011 Quí 1, 2012 E1050=SUBSTITUTE(D1050, "1", "2", 3) Thay thế chuỗi "1" bằng chuỗi "2" với vị trí thay thế thứ 3 Instance_num xác định old_text thứ mấy cần thay thế bằng new_text. Nếu khai báo instance_num thì chỉ có old_text được khai báo là được thay thế, nếu không khai báo thì tất cả old_text trong chuỗi Text được thay bằng new_text. Trần Thanh Phong 12 Microsoft Excel 2007 Công thức và Hàm 1051 1053 1054 1055 1056 1057 1058 1059 1060 1061 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 C D E F G H I J K L M N Các chuỗi Hàm Babba-booey 1 E1054=LEN(D1054)-LEN(SUBSTITUTE(D1054,"B","")) Có phân biệt chữ HOA và thường Babba-booey 4 E1055=LEN(D1055)-LEN(SUBSTITUTE(UPPER(D1055),"B","")) Không phân biệt chữ HOA và thường Ví dụ 3 Đếm số lần xuất hiện của từ trong chuỗi văn bản Các chuỗi Từ cần đếm Hàm Blonde on Blonde Blonde 2 G1059=(LEN(D1059)-LEN(SUBSTITUTE(D1059,F1059,"")))/LEN(F1059) Blonde on Blonde blonde 2 G1060=(LEN(D1060)-LEN(SUBSTITUTE(UPPER(D1060),UPPER(F1060),"")))/LEN(F1060) T Trả về văn bản từ giá trị do ta xác định Cú pháp T(value) Value là giá trị muốn kiểm tra Ghi chú Nếu value là chuỗi hoặc tham chiếu đến ô chứa chuỗi hàm trả về chính value. Nếu value không là chuỗi hàm trả về "" (chuỗi rỗng). Ví dụ Dữ liệu Hàm Rainfall Rainfall E1143=T(D1143) Vì ký tự đầu tiên là chuỗi hàm trả về chuỗi đó 19 Vì đây là số hàm trả về "" (chuỗi rỗng) TRUE Vì đây là giá trị luận lý, hàm trả về "" (chuỗi rỗng) TEXT Chuyển đổi một giá trị sang dạng văn bản theo định dạng qui định Cú pháp TEXT(value,format_text) Value là con số, một biểu thức trả về kết quả là con số hoặc tham chiếu đến ô chứa số Format_text là kiểu định dạng số dưới dạng văn bản đặt trong cặp dấu nháy kép Ghi chú Format_text không được chứa *. Ví dụ Nhân viên Doanh thu Hàm 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. Định dạng ô dùng lệnh chỉ thay đổi định dạng không làm thay đổi giá trị. Nhưng dùng hàm TEXT sẽ chuyển con số sang dạng chuỗi luôn và không dùng để tính toán như số được nữa. Trần Thanh Phong 13 Microsoft Excel 2007 Công thức và Hàm 1164 1165 1166 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 C D E F G H I J K L M N Buchanan 2800 Doanh thu bán hàng của Buchanan là $2800.00 F1164="Doanh thu bán hàng của " & D1164 &" là "&TEXT(E1164, "$0.00") Dodsworth 40% Dodsworth bán được 40% trên tổng doanh thu. F1165=D1165&" bán được "&TEXT(E1165,"0%")&" trên tổng doanh thu." TRIM Chuẩn hóa các khoản trắng trong một văn bản bằng cách loại bỏ các khoảng trắng dư thừa và chỉ để lại một khoảng trắng giữa các từ Cú pháp TRIM(text) Text là chuỗi văn bản hay một tham chiếu tới một ô chứa văn bản mà bạn muốn chuẩn hóa Ví dụ Chuỗi Sau khi dùng hàm Chương trình Fulbright Chương trình Fulbright F1180=TRIM(D1180) Xin chào Xin chào F1181=TRIM(D1181) UPPER Chuyển một chuỗi văn bản sang chữ HOA Cú pháp UPPER(text) Text là một chuỗi hay một tham chiếu tới một ô chứa văn bản mà ta muốn chuyển đổi Ví dụ Các chuỗi Hàm chào các bạn CHÀO CÁC BẠN F1215=UPPER(D1215) nguyễn hoàng NGUYỄN HOÀNG F1216=UPPER(D1216) VALUE Chuyển một chuỗi văn bảng sang một 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 Ghi chú Text có thể định dạng thành con số, ngày, hoặc thời gian do Excel nhận dạng, nếu nhận dạng không được sẽ báo lỗi #VALUE!. Hàm này chúng ta thường không sử dụng, nó được tạo ra để tương thích với các chương trình bảng tính khác Ví dụ Hàm 1000 D1228=VALUE("$1,000") Chuyển chuỗi "$1,000" thành số 0.2 D1229=VALUE("16:48:00")-VALUE("12:00:00") Đổi giờ ra các số tuần tự và trừ nhau "16:48:00"-"12:00:00" (0.2 hoặc 4:48) Trần Thanh Phong 14