TEXT函数(高级精要).xls

EXECL 2007帮帮助助文文件件整整理理 23 523 5 说说明明 TEXT 函数可将数值转换为文本 并可使用户通过使用特殊格式字符串来指定显示格式 需要以可读性更高的格式显示数字或需要合并数字 文本或符号时 此函数很有用 例如 假设单元格 A1 含有数字 23 5 若要将数字格式设置为美元金额 可以使用以下公式 TEXT E1 0 00 在本例中 Excel 会显示 23 50 也可以使用功能区 开始 选项卡上的 数字 组中的命令来设置数字格式 但是 只有整个单元格都为数字时 这些命令才起作用 如果需要设置数字格式并将其与其他文本 合并 使用 TEXT 函数是最佳选择 例如 可以向前一个公式中添加文本 TEXT E1 0 00 小小于于0 0 0 贷贷 平平 正正 负负 零零 非非 数数值值 500 500 300 300 300 非非数数值值 2899999 00000 0 贷 正500 21 00000 0 贷 正300 402 31346 0 贷 正300 25403 43691 0 贷 正500 300 00000 0 贷 正300 0 000000 平 零300 15022226 80728 0 贷 正500 500 00000 0 贷 正500 3014 56037小于0 借 负 300 201223 43691小于0 借 负 300 112 31346 0 贷 正300 1 19000小于0 借 负 300 0 00100 0 贷 正300 101 12446 0 贷 正300 165478965 0 贷 正500 ni好ni好 ni好 非数值非数值 3 3 日日期期时时间间格格式式 日期时间格式 用常规格式 显示日期 年 DBNum4 G 通用格式y 1900 01 10 00 00 0010101000 1901 02 10 01 01 01407 0423727407 042372685185407 042372701 1902 03 14 02 02 02804 0847454804 08474537037804 084745402 1903 04 15 03 03 031201 1271181201 127118055561201 12711803 1904 05 16 04 04 041598 1694911598 169490740741598 16949104 1905 06 17 05 05 051995 2118631995 211863425931995 21186305 1906 07 19 06 06 062392 2542362392 254236111112392 25423606 1907 08 20 07 07 072789 2966092789 29660879632789 29660907 1900 01 14 00 00 0014141400 2011 04 12 13 58 2040645 5821740645 582174652840645 5821711 2008 08 08 00 00 0039668396683966808 1900 01 12 00 00 0012121200 备注 TEXT的格式设置可以参考论坛文章 自定义单元格格式一网打尽 或者根据单元格格式自己组合 0 000 0000 0000 1 191 1901 190011 21 1102 311102 3131102 313511021102 31 102 2103 442103 4372103 436921032103 42 103 1304 561304 5601304 560413051304 61 305 0 000 0000 0000 15022226 8115022226 80715022226 80731502222715022226 815 022 227 4015 68 4015 684 4015 6838 4016 4015 7 4 016 3014 56 3014 560 3014 5604 3015 3014 6 3 015 201223 44 201223 437 201223 4369 201223 201223 4 201 223 1012 31 1012 313 1012 3135 1012 1012 3 1 012 1 19 1 190 1 1900 1 1 2 1 20100203 0020100203 00020100203 00002010020320100203 20 100 203 0 000 0010 0010 101 12101 124101 1245101101 1101 165478965 00165478965 000165478965 0000165478965165478965 165 478 965 只只显显示示负负值值显显示示 0 0的的值值只只显显示示0 0值值只只显显示示文文本本 500 1级级 500 300 0 00 0 00 小小于于0 0 0 0 G 通用格式 1级2 899 9992899999 0 3级21 0021 0 2级402 31402 3 1级25 40325403 4 2级300 00300 0 3级0 000 1级15 022 22715022226 8 1级500500 0 1级 3 014 56小于0 1级 201 223 44小于0 3级112 31112 3 3级 1 19小于0 3级0 000 0 3级101 12101 1 1级165 478 965165478965 0 VALUE ni好ni好 条条件件格格式式的的结结构构 1 1 text text 数数据据源源 0 0显显示示值值 020 dbnum1 dbnum1 d 0 2899999二百八十九万九千九百九十九 2899999 21二十一 21 402 313456四百 二 三一三四五六 402 25403 436912二万五千四百 三 四三六九一二 25403 300三百 300 0 0 15022226 80728一千五百 二万二千二百二十六 八 七二八 15022227 500五百 500 3014 560368 VALUE 3015 201223 436912 VALUE 201223 112 313456一百一十二 三一三四五六 112 1 19000000000003 VALUE 1 0 001 0 101 124456一百 一 一二四四五六 101 165478965一亿六千五百四十七万八千九百六十五 165478965 ni好ni好ni好 中中文文大大小小写写数数字字 时 aaaahhh 星期二000 星期日101 星期五202 星期三303 星期一404 星期六505 星期四606 星期二707 星期六000 星期二1313 星期五000 星期四000 星期 1 1 0 0 0 0 千千元元 0 0 0 0 万万 0 000 0 000 1 19 100 0千元0 0万0 000 1102 205 6541 1千元0 1万0 001 2103 232 5312 1千元0 2万0 002 1304 427 7621 3千元0 1万0 001 0 0千元0 0万0 000 15022226 377 46715022 2千元1502 2万15 022 546133 136 4 0千元 0 4万 0 004 2297095 762 3 0千元 0 3万 0 003 106849645 531 201 2千元 20 1万 0 201 662053 654 1 0千元 0 1万 0 001 119 100 0千元0 0万0 000 20100203 20100 2千元2010 0万20 100 0 1 0千元0 0万0 000 101 57 458 1千元0 0万0 000 165478965 165479 0千元16547 9万165 479 用用 表表示示用用 填填充充 强强制制由由 或或 后后边边的的字字符符填填 充充 00 289999900 00 2100 00 40231 35 2540343 69 30000 00 00 1502222680 73 50000 00 301456 04 20122343 69 11231 35 119 00 10 10112 45 16547896500 00 ni好 时 分 h h 时hmhh m 2400 时0000 0 97691 时1101 1 192982 时2202 2 288273 时3303 3 383564 时4404 4 478855 时5505 5 574146 时6606 6 669437 时7707 7 3360 时0000 0 97549313 时135813 58 9520320 时0000 0 2880 时0000 0 时 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 5 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 16 5 重重复复显显示示文文本本 次次数数与与 个个数数有有关关 2899999 21 402 313456 25403 43691 300 0 15022226 81 500 3014 560368 201223 4369 112 313456 1 19 0 001 101 124456 165478965 ni好ni好ni好ni好ni好 分分 秒秒上 下午时间 hmmhh mm m m smm ss s sssss 00hh mm AM PM 00000 00144000 000 0086400000000 0012 00 AM 10101 015861411 101 013516846110101 0001 01 AM 20202 0211578822 202 026947292220202 0002 02 AM 30303 0317296233 303 0310377738330303 0003 03 AM 40404 0423013644 404 0413808184440404 0004 04 AM 50505 0528731055 505 0517238630550505 0005 05 AM 60606 0634448466 606 0620669076660606 0006 06 AM 70707 0740165877 707 0724099522770707 0007 07 AM 00000 00201600 000 00120960000000 0012 00 AM 135813 585852963858 2058 203511778300202019 8901 58 PM 00000 00571219200 000 00342731520000000 0012 00 AM 00000 00172800 000 00103680000000 0012 00 AM 时 分 日期 时间显示中文日期 h mm ss a pe m d h mm ss 00 dbnum1 yyyy年m月d日 dbnum2 yyyy年m月d日 12 00 00 a1900 1 10 0 00 00 00一九 年一月十日壹玖零零年壹月壹拾日 1 01 01 a1901 2 10 1 01 01 00一九 一年二月十日壹玖零壹年贰月壹拾日 2 02 02 a1902 3 14 2 02 02 00一九 二年三月十四日壹玖零贰年叁月壹拾肆日 3 03 03 a1903 4 15 3 03 03 00一九 三年四月十五日壹玖零叁年肆月壹拾伍日 4 04 04 a1904 5 16 4 04 04 00一九 四年五月十六日壹玖零肆年伍月壹拾陆日 5 05 05 a1905 6 17 5 05 05 00一九 五年六月十七日壹玖零伍年陆月壹拾柒日 6 06 06 a1906 7 19 6 06 06 00一九 六年七月十九日壹玖零陆年柒月壹拾玖日 7 07 07 a1907 8 20 7 07 07 00一九 七年八月二十日壹玖零柒年捌月贰拾日 12 00 00 a1900 1 14 0 00 00 00一九 年一月十四日壹玖零零年壹月壹拾肆日 1 58 20 p2011 4 12 13 58 19 89二 一一年四月十二日贰零壹壹年肆月壹拾贰日 12 00 00 a2008 8 8 0 00 00 00二 八年八月八日贰零零捌年捌月捌日 12 00 00 a1900 1 12 0 00 00 00一九 年一月十二日壹玖零零年壹月壹拾贰日 上 下午时间 自定义应用 dbnum3 yyyy年m月d日 dbnum4 yyyy年m月 d日 e m 月月报 m s Sm d yyd mm 年 月 日1900年1月10日1900 1 月月S1 10 0010 01 年 月 日1901年2月10日1901 2 月月报58614135168461 S2 10 0110 02 年 月 日1902年3月14日1902 3 月月报115788269472922 S3 14 0214 03 年 月 日1903年4月15日1903 4 月月报1729623103777383 S4 15 0315 04 年 月 日1904年5月16日1904 5 月月报2301364138081844 S5 16 0416 05 年 月 日1905年6月17日1905 6 月月报2873105172386305 S6 17 0517 06 年 月 日1906年7月19日1906 7 月月报3444846206690766 S7 19 0619 07 年 月 日1907年8月20日1907 8 月月报4016587240995227 S8 20 0720 08 年 月 日1900年1月14日1900 1 月月报201601209600 S1 14 0014 01 年 月 日2011年4月12日2011 4 月月报 585296383511778300 S4 12 1112 04 年 月 日2008年8月8日2008 8 月月报 571219203427315200 S8 8 088 08 年 月 日1900年1月12日1900 1 月月报172801036800 S1 12 0012 01 显示中文日期 Y年m月D日d mmmd mmmmd mmmm yyyy mmmmmmmm yy F800 00年1月10日10 Jan10 January10 January 0000 JanuaryJanuary 001900年1月10日 01年2月10日10 Feb10 February10 February 0101 FebruaryFebruary 011901年2月10日 02年3月14日14 Mar14 March14 March 0202 MarchMarch 021902年3月14日 03年4月15日15 Apr15 April15 April 0303 AprilApril 031903年4月15日 04年5月16日16 May16 May16 May 0404 MayMay 041904年5月16日 05年6月17日17 Jun17 June17 June 0505 JuneJune 051905年6月17日 06年7月19日19 Jul19 July19 July 0606 JulyJuly 061906年7月19日 07年8月20日20 Aug20 August20 August 0707 AugustAugust 071907年8月20日 00年1月14日14 Jan14 January14 January 0000 JanuaryJanuary 001900年1月14日 11年4月12日12 Apr12 April12 April 1111 AprilApril 112011年4月12日 08年8月8日8 Aug8 August8 August 0808 AugustAugust 082008年8月8日 00年1月12日12 Jan12 January12 January 0000 JanuaryJanuary 001900年1月12日 自定义应用 F400 0 00 00 1 01 01 2 02 02 3 03 03 4 04 04 5 05 05 6 06 06 7 07 07 0 00 00 13 58 20 0 00 00 0 00 00 一一 在在TEXTTEXT函函数数的的条条件件格格式式中中使使用用变变量量 例例题题请请按按住住F9F9 TEXT函数的条件格式公式样式 text 数据源 条件1 显示格式 条件2 显示格式 否则显示格式 文本显示格式 条件格式可以全部或者部分使用变量 当部分使用变量时 条件和显示格式的任何部分都可以使用变量 包括条件格式中的运算符号 当使用变量时 不使用变量的部分用引号 引起来 然后用 运算符号 格式代码 2 在条件代码中使用变量 公公式式样样式式 text 数据源 运算符号 运算符号 格式代码 运算符号应是比较运算符 格式代码是自定义单元格的代码 颜色代码除外 例子 0 3227随机数在0 3 0 5之间判定随机数大小 0 4675随机数 0 3 运算符号用单元格引用 3 在格式代码中使用变量 公公式式样样式式 text 数据源 条件代码 条件代码 相当于IF A1 条件1 显示1 3 当有三个时 如 text A1 条件1 显示1 条件2 显示2 显示3 相当于IF A1 条件1 显示1 IF A1 条件2 显示2 显示3 text A1 条件1 显示1 条件2 显示2 显示3 相当于IF A1 条件1 显示1 IF A1 条件2 显示2 显示3 欢迎你欢迎你 4 当有四个时 如 text A1 条件1 显示1 条件2 显示2 显示3 显示4 相当于IF ISTEXT A1 显示4 IF A1 条件1 显示1 IF A1 条件2 显示2 显示3 不合格不合格 text A1 条件1 显示1 条件2 显示2 显示3 相当于IF ISTEXT A1 IF A1 条件1 显示1 IF A1 条件2 显示2 显示3 三三 TEXTTEXT函函数数的的第第一一个个参参数数研研究究 1 第一个参数为一个单元格引用60及格如果A1 60 20显示优秀 如果A1 60为负数 即A10 2 0 5 此处0 是占位符 0 69四舍五入为1 结果是 1 51 5 解释 在TEXT函数中 如果在 0 和 前面没有 或者 就是占位符 否则就是被强制显示的字符 只有有了小数点占位符号 不是 小数点后面的小数部分才能被显示 否则只显示整数部分 并且没有被显示的部分会自动四舍五入 关于 0 和 的详细解释请参考补充说明部分 六六 TEXT函函数数的的参参数数中中带带 的的解解释释 1 TEXT函数的参数带一个 号的解释 见以下语句 1 TEXT 1 2 1 3 显示 3 3 3 2 TEXT 1 1 2 3 显示 2 2 2 3 TEXT 1 1 2 3 显示 3 3 3 4 TEXT 1 2 1 3 显示 3 3 3 5 TEXT 1 1 2 2 3 4 显示 4 4 4 6 TEXT 1 1 2 1 2 3 1 2 3 你好 欢迎 显示 你好 你好 10 TEXT 1 2 1 3 显示 3 3 3 个人解释 第一个参数的 作为运算符负号使用 如果第二个参数中 第一参数 的条件出现 则第二个参数的 作为运算符负号使用 如果第二个参数中 第一参数 的条件没有出现 则作为字符符号使用并且如果且TEXT的条件只有一部分时 显示原值 有两部分以上时 则多显示一个运算符负号 2 TEXT函数的参数带两个 号的解释 见以下语句 1 TEXT 1 1 2 1 3 4 显示 2 2 22 2 TEXT 1 1 2 1 3 4 显示 2 2 2 3 TEXT 1 1 2 1 3 4 显示 1 1 1 4 TEXT 1 1 2 1 3 4 显示 2 2 2 5 TEXT 1 1 2 1 3 显示 3 3 3 2 TEXT 1 1 2 3 显示 2 2 22 3 TEXT 1 1 2 3 显示 3 3 33 4 TEXT 6 1 2 3 显示 2 2 22 5 TEXT 6 1 2 3 4 显示 6 VALUE 6 TEXT 1 1 2 3 显示 3 3 33 7 TEXT 1 1 2 1 3 4 显示 1 1 1 10 TEXT 1 1 2 1 3 4 显示 1 1 1 概括 1 第一参数的 号如果多于1个且在引号内 则第一参数为字符 2 第一参数的 号如果只有1个或者多于1个但没在引号内 则第一参数为数值 3 如果第一参数的计算结果为正数 第二个参数的 不在引号内 全部为运算符符号使用 第二个参数的 在引号内时 紧挨着数字的一个 作为运算符负号使用 其它 作为字符符号使用 4 如果第一参数的计算结果为负数 4 1 第二个参数的 不在引号内时 当 为单数时 则有1个作为字符使用 其它作为运算符负号使用 双数时全部 作为运算符符号使用 4 2 第二个参数的 在引号内 则紧挨着数字的一个 作为运算符负号使用 其它 作为字符符号使用 并且如果第二参数中 第一参数 的条件没有出现 此时还要看TEXT的条件格式有几部分 有两部分且第二部分没有条件时或者有三部分但第二部分有条件 非第一参数 时 此时结果值前面还要多显示一个 字符 4 条件格式是区间 第一参数为负数 1 TEXT 3 4 4 5 显示 5 5 55 2 TEXT 3 4 4 5 6 显示 4 4 4 4 TEXT 3 3 4 5 显示 4 4 4 5 TEXT 3 4 4 6 显示 5 5 55 6 TEXT 3 4 5 6 显示 6 6 6 对第二参数的个人解释 4 1 如果条件区间的范围跨度包含正负数 第一部分 不满足条件且第二部分省略 除外 就会多显示一个负号 如果条件区间仅在负数范围内 就不会多显示负号 4 3 如果第一部分 不满足条件且第二部分省略 此时 显示第二部分的原数值 4 2 数字前的 都作为字符使用 第一部分 不满足条件且第二部分省略 除外 七七 关关于于INDIRECT TEXT INDIRECT TEXT 数数字字 r0C00 r0C00 此此类类格格式式的的应应用用 1 解释 TEXT 102 r0c00 函数的计算结果为 r1c02 前面再加个indirect函数 相当于INDIRECT r1c02 计算结果就是等于1行2列即B1的值 故展开该函数 INDIRECT TEXT 1002 r00c00 B10的值 INDIRECT TEXT 102 r00c0 B10的值 注意前后的变化 INDIRECT TEXT 103 r00c0 C100的值 INDIRECT TEXT 1003 r00c0 C100的值 INDIRECT TEXT 901 r00c0 A90的值 INDIRECT TEXT 1681 1682 1683 1691 1692 1693 1701 1702 1703 r00c0 得到一个数组INDIRECT r168c1 r168c2 r168c3 r169c1 r169c2 r169c3 r170c1 r170c2 r170c3 1474545 258 369 2 怎么解决繁体版TEXT