常用EXCEL函数详解及实例-文本函数.xls

zylkfxyzylkfxy 常常用用ExcelExcel函函数数详详解解及及应应用用实实例例 四四 文文本本函函数数 序序号号函函数数函函数数定定义义页页码码 1Asc将字符串中的全角英文字母转换为半角字符1 2Char返回对应于文字代码的字符1 3Clean 删除其中含有的当前操作系统无法打印的字符 1 4Code返回文本字符串中第一个字符的数字代码1 5Concatenate将多个字符文本或单元格中的数据连接在一起 同 2 6Dollar可将数字转换为文本格式 并应用货币符号2 7Exact测试两个字符串是否完全相同2 8Find从首字符开始返回查找字符的起始位置编号3 9Findb使用字节数从首字符开始返回查找字符的起始位置编号3 10Fixed将数值带上千位分隔符和小数点标记进行取整4 11Left从文本字符串的第一个字符开始截取指定数目的字符4 12Leftb从文本字符串的最左边开始提取指定字节数的字符4 13Len返回文本字符串中的字符个数5 14Lenb返回文本字符串中用于代表字符的字节数5 15Lower将文本字符串中的所有大写字母转换为小写字母5 16Mid 从文本字符串的指定位置开始 截取指定数目的字符5 17Midb根据指定的位置和字节提取字符5 18Numberstring将数值转换成汉字数字的文本 隐藏函数 6 19Proper将文本字符串的首字母及任何非字母字符之后的首字母转换成大写6 20Replace根据所指定的字符数替换某文本字符串中的部分文本6 21Replaceb 根据所指定的字符数替换某文本字符串中的部分文本 为双字节字符使用 6 22Pert按照给定的次数重复显示文本7 23Right从文本字符串的最后一个字符开始截取指定数目的字符7 24Rightb从文本字符串的最后一个字符开始提取字节数的字符7 25Search 从开始首次找到特定字符或文本字符串位置上特定字符的编号 不区分大小写 8 26Searchb根据字符所使用的字节数找到特定字符或文本字符串位置上特定字符的编号8 27Substitute在文字串中用new text替代old text8 28T将数值转换成文本9 29Text将数值转换为按指定数字格式表示的文本9 30Trim除了单词之间的单个空格外 清除文本中的所有的空格9 31Upper将所有英文字母转换成大写字母9 32Value将表示数字的文字串转换成数字10 33Widechar将字符串中的半角 单字节 字母转换为全角 双字节 字符10 整整理理日日期期 20132013年年 常用Excel函数详解及应用实例常用Excel函数详解及应用实例zylkfxyzylkfxy 第 2 页 共 13 页 四四 文文本本函函数数 目录4 C41 ASC 1 ASC 全全角角换换成成半半角角 函数定义 将全角 双字节 字符更改为半角 单字节 字符 使用格式 ASC text 格式简义 ASC 目标单元格或文本 参数定义 Text为文本或对包含要更改文本的单元格的引用 如果文本中不包含任何全角字 母 则文本不会更改 例1 全角字符结果 公式显示 学习 函数学习EXCEL函数 ASC B10 引用单元格 直接输入 函数学习ASC函数 ASC 学习 函数 直接输入 目录4 C52 CHAR 2 CHAR对对应应于于数数字字代代码码的的字字符符 函数定义 返回对应于数字代码的字符 函数CHAR可将其他类型计算机文件中的代码转换为字符 使用格式 CHAR number 格式简义 CHAR 目标单元格 参数定义 Number是用于转换的字符代码 介于1到255之间 使用的是当前计算机字符集中的字符 注意事项 操作环境 Macintosh Windows 字符集 Macintosh字符集 ANSI 例2 1 英文大写字母 直向 2 英文小写字母 横向 A CHAR 65 A CHAR ROW 56 A REF C B CHAR 66 B或 CHAR ROW A65 CHAR COLUMN A1 64 a CHAR 97 C b CHAR 98 D 例3 返回 excel 的首字符代码 然后再转换为字符 101 CODE excel e 字符代码 101 e CHAR B27 将代码转换成字符 CHAR 12 CHAR 11 例4 判断下面单元格中的字符串是否全是字母字符 Bin Yang FALSE AND N CODE RIGHT UPPER B31 ROW INDIRECT 1 LEN B31 chrisfangTRUE 64 90 1 0 Qee用FALSE 简要说明原理 首先把字符串中所有字符都用Upper函数转为大写字母 有利于判断 然后用right函数 将整个字符串依次分段 再用code函数将分段后的首字符代码取出 分段后的首字符就是字符串 中的每一个字符 最后判断代码是否在65 90的区间内 大写英文字母的区间 举这个例子的 目的只是为了让大家了解到 code函数经过与其他函数的组合可以突破返回首字符代码的限制 可以有更广泛的应用 例5将中英文合并 并将中英文分开换行显示 中文英文1合并 换行显示 公式 苹果 apple 苹果apple 苹果 apple A42 CHAR 10 B42 香蕉 banana 香蕉banana 香蕉 banana 注 char 10 换行 同时在单元格格式中设置自 狮子 lion 狮子lion 狮子 lion 动换行 老虎 tiger 老虎tiger 老虎 tiger 目录4 C63 CLEAN 3 CLEAN 删删除除不不能能打打印印的的字字符符 函数定义 对从其他应用程序中输入的文本使用该函数 将删除其中含有的当前操作系统无法打印的字符 使用格式 CLEAN text 格式简义 CLEAN 目标单元格 参数定义 text文本是要从中删除不能打印字符的任何工作表信息 注意事项 单元格中软回车会生成代码 虽然肉眼看不到 事实是存在于表格中 此类字符输出至别的 文本 可能会产生乱码 用CLEAN函数删除 例6非打印字符结果公式 text text CLEAN B55 CHAR 7 CHAR 10 非打印字符串 目录4 C74 CODE 4 CODE 查查看看字字符符代代码码 函数定义 返回文本字符串中第一个字符的数字代码 返回的代码对应于计算机当前使用的字符集 使用格式 CODE text 格式简义 CODE 目标单元格 ABCDEFGHIJ 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 常用Excel函数详解及应用实例常用Excel函数详解及应用实例zylkfxyzylkfxy 第 3 页 共 13 页 参数定义 Text为需要得到其第一个字符代码的文本 注意事项 操作环境 Macintosh Windows 字符集 Macintosh字符集 ANSI 通过code 函数可以得知 数字0 9的编码为48 57 例7 判断全角还是半角 文本code char code 社会与法 电视好频道41392 CODE B66 41392代表全角 社会与法 电视好频道34 CHAR CODE B67 34代表半角 例8 判断中文 英文还是数字 数据判断code 判断公式 精英中文或全角英文48811 IF CODE B71 64 CODE B71 255 0 LENB B175 LEN B175 汉字 目录4 C139 FIXED 9 FIXED 给给数数值值附附加加千千位位分分隔隔符符和和小小数数分分隔隔符符 函数定义 将数字按指定的小数位数进行取整 利用句号和逗号 以小数格式对该数进行格式设置 并以文本形式返回结果 使用格式 FIXED number decimals no commas 格式简义 FIXED 目标单元格或数字式 保留位数 是否用千位分隔符 参数定义 Number 要进行四舍五入并转换成文本字符串的数 Decimals为一数值 用以指定小数点右边的小数位数 为正数时 在小数点第N 1位四舍五入 为0时 在小数部分全部四舍五入 为负数时 在整数第N位四舍五入 省略 时 被看作 2 在小数点第3位四舍五入 No commas为一逻辑值 用逻辑值来指定是否用 分隔 true时 不用 分隔 为 false时 用 分隔 注意事项 1 在Microsoft Excel中 Numbers的最大有效位数不能超过15位 但decimals可达到127 2 如果Decimals为负数 则参数number舍入到小数点左边 3 如果省略小数 则假设其值为2 4 如果其值为FALSE或被省略 则返回的文本中和往常一样包含逗号 5 该函数与使用 格式 菜单上的 单元格 命令格式化包含数字的单元格的主要区别在于 函数FIXED将其结果转换成文本 而 单元格 命令格式化的结果仍是数字 例19给下列1月份销售量添加千位分隔符 姓名性别1月结果 公式显示 赵一 女 18431 58618 431 6 FIXED D224 1 FALSE 保留1位小数和千位符 钱二 男 18431 58618431 59 FIXED D225 2 TRUE 保留2位小数不用千位符 孙三 男 18431 58618 432 FIXED D226 0 0 不保留小数使用千位符 李四 女 18431 58618431 6 FIXED D227 1 1 保留1位小数不使用千位符 周五 男 18431 58618 400 FIXED D228 2 FALSE 整数位倒第2位四舍五入 目录4 C1410 LEFT LEFTB 10 LEFT LEFTB 从从左左边边截截取取指指定定字字符符个个数数 截截取取指指定定字字节节数数的的字字符符 函数定义 LEFT从一个文本字符串的第一个字符开始 截取指定数目的字符 LEFTB从最左边提取指定字节数的字符 使用格式 LEFT text num chars leftb text num bytes 格式简义 LEFT 目标单元格 从内容的左边开始截取指定的字的个数 参数定义 Text是包含要提取字符的文本字符串 Num charsLEFT指定要由LEFT所提取的字符数 必须大于或等于0 LEFTB按字节指定要由leftb所提取的字符数 注意事项 1 如果num chars大于文本长度 则LEFT返回所有文本 2 如果省略num chars 则假定其为1 3 Num bytes 按字节指定要由LEFTB所提取的字符数 例20从左边截取字符串 中华人民共和国万岁中华人民 LEFT B217 4 指定从内容开始处截取4个字 中国共产党万岁中 LEFTB B218 2 指定从内容开始处截取2个字节字符 我爱大好河山我 LEFTB B219 3 指定从内容开始处截取3个字节字符 例21分别截取 左右字符串 11630177 QQ com11630177QQ com LEFT B247 FIND B247 1 63077177 MID B247 FIND B247 1 10 077177 截取 前的内容 截取某特征前的字符 例22将数量与单位分开 文本数字单位 28克 28克 LEFT B253 LEN B253 2 LENB B253 数字 369公斤 369公斤 RIGHT B253 LENB B253 LEN B253 单位 ABCDEFGHIJ 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 常用Excel函数详解及应用实例常用Excel函数详解及应用实例zylkfxyzylkfxy 第 6 页 共 13 页 56789吨 56789吨 目录4 C1611 LEN LENB 11 LEN LENB 计计算算文文本本字字符符串串的的字字符符数数 文文教教文文本本字字符符串串的的字字节节数数 函数定义 LEN返回文本字符串中的字符数 LENB返回文本字符串用于代表字符的字节数 此函数用于双字节字符 使用格式 LEN text LENB 同 格式简义 LEN 目标单元格 参数定义 Text是要查找其长度的文本 空格将作为字符进行计数 例23计算字符串的长度 汉字个数和非汉字个数 字符串字符个数 字节个数汉字个数非汉字个数公式 中华人3630 LEN B265 len的结果 中国888A6824 LENB B265 lenb结果 人民AAAEXP81026 LENB B265 LEN B265 汉字个数 中国A 12 881026 2 LEN B265 LENB B265 非汉字个数 中A 13 81135注 汉字个数含中文标点符号 目录4 C1812 LOWER 12 LOWER 所所有有大大写写字字母母转转换换为为小小写写字字母母 函数定义 将一个文本字符串中的所有大写字母转换为小写字母 使用格式 LOWER text 格式简义 LOWER 目标单元格或文本 参数定义 Text是要转换为小写字母的文本 函数LOWER不改变文本中的非字母的字符 例24将下列字符串的所有英文字母全部改为小写 字符串结果 公式也可直接输入字母 AAAaaa AAAaaaaaaaaa LOWER B278 aaaaaa LOWER AAAaaa AAAaaa aBaBaBababababab AaA中国Abaaa中国ab 目录4 C1913 MID MIDB 13 MID MIDB 指指定定位位置置截截取取字字符符数数 指指定定位位置置和和字字节节截截取取字字符符数数 函数定义 MID是从一个文本字符串的指定位置开始 截取指定数目的字符 MIDB是根据指定的位置和字节提取字符的个数 使用格式 MID text start num num chars MIDB text start num num bytes 格式简义 MID 目标单元格 内容的左边指定开始位置 指定截取的字的个数 参数定义 Text是包含要提取字符的文本字符串 Start num是文本中要提取的第一个字符的位置 文本中第一个字符的start num为1 以此类推 Num charsMID是指定希望MID从文本中返回字符的个数 MIDB是指定希望从文本中按字节返回字符的个数 注意事项 1 如果start num大于文本长度 则函数返回空文本 2 如果start num小于文本长度 但start num加上num chars num bytes 超过了文本的 长度 则函数只返回至多直到文本末尾的字符 3 如果start num小于1 则MID返回错误值 VALUE 4 如果num chars num bytes 是负数 则MID返回错误值 VALUE 例25 中国安徽省安庆市安庆市 MID B297 6 3 从字符串的第6个字开始截取 共截取3个 中国安徽省安庆市安徽省 MID B298 3 3 从字符串的第3个字开始截取 共截取3个 中国安徽省安庆市安徽省 MIDB B299 5 6 从字符串的第5个字开始截取 共截取6个字节 例26 单元格提取字符中的数字 数字前部分 字符串数字部分数字前部分 RM 12345 ABCD12345RM MID B303 FIND B303 1 COUNT MID B303 ABCD RH 12 DC12ABCD RHROW 1 20 1 数字部分 ABCDE RN 1 DC1ABCDE RN LEFT B303 FIND B303 1 数字前部分 ABCDE RB 123 AC123ABCDE RB 例27 将金额分别填写到相应的单元格内 金额万千百拾元角分 5689 01 568901 2456 78 245678 3918 00 391800 106 09 10609 公式 MID RIGHT B310 100 7 COLUMN 2 1 金额万千百拾元角分 6789 09 REF 78909 406 78 40678 ABCDEFGHIJ 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 常用Excel函数详解及应用实例常用Excel函数详解及应用实例zylkfxyzylkfxy 第 7 页 共 13 页 500185001800 1026 09 102609 公式 LEFT RIGHT B316 100 8 COLUMN A1 1 例28 多行合计逢10进位计算 357982 876545 123427 累计进位合计1357954 1357954 LEFT RIGHT SUMPRODUCT B323 I325 10 7 6 5 4 3 2 1 0 9 COLUMN B289 MID SUMPRODUCT B323 I325 10 7 6 5 4 3 2 1 0 COLUMN A1 1 目录4 C2114 NUMBERSTRING 14 NUMBERSTRING 阿阿拉拉伯伯数数字字换换成成汉汉字字 函数定义 将数值转换成汉字数字的文本 隐藏函数 使用格式 NUMBERSTRING number format 格式简义 NUMBERSTRING 要转换的阿拉伯数字或所在的单元格 汉字表达方式 参数定义 number为需要转换的阿拉伯数字 format用介于1 3之间的整数指定汉字的表示方式 1 将数值转换成以 一 二 三 和表示位数的 十 百 千 表示文本 2 将数值转换成以 壹 贰 叁 和表示位数的 拾 佰 仟 表示文本 3 将数值转换成以 一 二 三 表示文本 注意事项 此函数只能手动在单元格输入函数 帮助中也没有些函数的相关项目 例29 将下列数值改为中文大 小写 56238 五万六千二百三十八 NUMBERSTRING B342 1 3089 叁仟零捌拾玖 NUMBERSTRING B343 2 79862 12 七九八六二 NUMBERSTRING B344 3 2016 4 贰仟零壹拾陆元 NUMBERSTRING B345 2 元 451 45 肆佰伍拾壹 NUMBERSTRING B346 2 859621 09捌拾伍万玖仟陆佰贰拾壹元零角玖分 IF B3474 1 2 10 15 ROW 1 15 LEN B618 1 中 2 国 例49 计算字符串中数字的个数 赵一0558 2223334441313 SUM ISNUMBER MID B626 ROW 1 101 1 钱二133777888991111 2 LEN SUBSTITUTE B626 LENB SUBSTITUTE B626 例50 计算下列字符串中有多少个不重复的字符 精英培训excelexcelpxexcelpx9 精英培训EXCELexcelpxexcelpx13 SUM 1 LEN B630 LEN SUBSTITUTE B630 MID B630 ROW INDIRECT 1 LEN B630 1 例51 计算下列字符串 ExcelXP函数与Excel精英培训 求字符串中的字母个数 ABCDEFGHIJ 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 常用Excel函数详解及应用实例常用Excel函数详解及应用实例zylkfxyzylkfxy 第 12 页 共 13 页 12121212 COUNT AREAS INDIRECT MID B635 ROW INDIRECT 1 LEN B635 1 1 SUM ERROR TYPE INDIRECT MID B635 ROW INDIRECT 1 LEN B635 1 1 3 1 SUM LEN B635 LEN SUBSTITUTE UPPER B635 CHAR ROW 65 90 SUM CODE MID UPPER B635 ROW INDIRECT 1 LEN B635 1 77 5 13 求最后一个字母的位置 15 MAX IF ERROR TYPE INDIRECT MID B635 ROW INDIRECT 1 LEN B635 1 1 3 1 ROW INDIRECT 1 LEN B635 用空格将字母与汉字隔开 Excel培训Excel 培训 LEFT B635 2 LEN B649 LENB B649 RIGHT B649 LENB B649 LEN B649 将数字两位添加一个分隔符 1234567812 34 56 78 SUBSTITUTE TEXT B653 REPT x ROUND LEN B653 2 1 0 x ABCDEFGHIJ 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 文本函数例题标题文本函数例题标题zylkfxyzylkfxy 例01将全角字符转换为半角字符 例02对应数字代码的英文字母 例