intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

Microsoft Excel 2007: Công thức tính và Hàm

Chia sẻ: Vu Tien DUNG | Ngày: | Loại File: PDF | Số trang:14

Thêm vào BST
Báo xấu
456
lượt xem 188
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tài liệu tham khảo Microsoft Excel 2007 - Công thức và Hàm

Chủ đề:

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

Lưu

Nội dung Text: Microsoft Excel 2007: Công thức tính và Hàm

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

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
2=>2