Excel宝典之text函数(百度文库版)

Excel宝典之TEXT函数作者：EXCEL宝典百度文库版2012年7月24日CCCPER这是一件精心准备的小礼物，赠与我即将出世的小宝贝，祝愿他健康快乐！浅见工作室-1-Excel宝典之TEXT函数浅见工作室Excel宝典之text函数前言不少“X粉”都使用过Text函数，其用法在很多人看来就是自定义单元格格式的函数版。结合不同的格式代码及条件，可以让对象显示不同的格式效果。然而，Text函数的作用远不止如此，与自定义单元格格式的代码和功能相比，Text函数可以做得更多、走得更远。Text函数是Excel在字符处理函数中少有的几个具有丰富想像力的Excel函数之一。在某些情况下，text函数还可以替换If、Choose函数，处理内存数组，甚至可以比肩VBA中的Format函数。能够在一个貌似简单的文本函数中集合如此复杂的功能，不能不说是一个小小的奇迹。但是，TEXT函数也不是一只温顺的小猫，当您自觉已经搞定它的时候，却依旧会遭遇到不少猫腻，感觉其不可驯服之处。下面的各种段落，就权当一本粗浅的《训猫说明书》，让您可以知悉其脾性，掌握其行踪，甚至教它去“逮耗子”。本文承蒙各Excel论坛高手的指点与赐教，并恳求进一步指点与发掘瑕疵。这是一个小小的礼物，赠与我即将出世的小宝贝，祝愿他健康快乐！CCCPER，2012年7月24日百度文库收藏版[e-mail]-2-EXCEL宝典之TEXT函数浅见工作室目录■学习TEXT函数的步骤■说明■语法■格式准则一、有关数字格式的准则二、有关日期和时间格式的准则三、有关货币、百分比和科学记数法格式的准则四、有关包含文本和添加空格的准则注释示例■格式补充说明部分■高级应用部分一、TEXT函数与单元格格式的关系二、TEXT函数的条件格式三、在TEXT函数的格式字符串（format_text）中使用变量四、在TEXT函数的数据源（value）中使用变量五、TEXT函数嵌套六、TEXT函数与IF函数比较七、TEXT函数format_text参数显示格式中的0、.和\或!八、TEXT函数之“0”、“#”、“？”的问题九、TEXT函数之“-”的问题十、关于INDIRECT+TEXT(数字，r0C00)此类格式的应用十一、TEXT函数中的[]十二、TEXT函数数据源（VALUE）的格式类型十三、format_text参数不能包含星号(*)？十四、format_text参数条件格式第四部分文本内容限制十五、TEXT函数中常见的{1,-1}释义十六、TEXT函数常见搭配使用的函数■总结■部分范例-3-Excel宝典之TEXT函数浅见工作室学习TEXT函数的步骤一、熟知自定义单元格格式中的格式准则；二、学会分辨格式字符串中的占位符；三、学会使用条件格式；四、学会TEXT函数与其他函数的配套应用。说明TEXT函数可将数值转换为文本，并可使用户通过使用特殊格式字符串来指定显示格式。需要以可读性更高的格式显示数字或需要合并数字、文本或符号时，此函数很有用。例如，假设单元格A1含有数字23.5。若要将数字格式设置为美元金额，可以使用以下公式：=TEXT(23.5,$0.00)在本例中，Excel会显示$23.50。也可以使用功能区“开始”选项卡上的“数字”组中的命令来设置数字格式。但是，只有整个单元格都为数字时，这些命令才起作用。如果需要设置数字格式并将其与其他文本合并，使用TEXT函数是最佳选择。例如，可以向前一个公式中添加文本：=TEXT(E1,$0.00)&perhourExcel会显示$23.50perhour。语法TEXT(value,format_text)TEXT函数语法具有以下参数：value数据源，必需。数值、计算结果为数值的公式，或对包含数值的单元格的引用。format_text格式字符串，必需。使用双引号括起来作为文本字符串的数字格式（颜色代码除外），例如，m/d/yyyy或#,##0.00。有关详细的格式准则，请参阅以下部分。格式准则一、有关数字格式的准则显示小数位和有效位若要设置分数或含有小数点的数字的格式，请在format_text参数中包含以下位占位符、小数点和千位分隔符。占位符说明0（零）如果数字的位数少于格式中零的数量，则显示非有效零。例如，如果键入8.9，但要将其显示为8.90，请使用格式#.00。#按照与0（零）相同的规则执行操作。但是，如果键入的数字在小数点任一侧的位数均少于格式中#符号的数量，Excel不会显示多余的零。例如，如果自定义格式为#.##且在单元格中键入了8.9，则会显示数字8.9。?按照与0（零）相同的规则执行操作。但是，对于小数点任一侧的非有效零，Excel会加上空格，使得小数点在列中对齐。例如，自定义格式0.0?会对齐列中数字8.9和88.99的小数点。-4-EXCEL宝典之TEXT函数浅见工作室.（句点）在数字中显示小数点。如果数字的小数点右侧的位数大于格式中的占位符，该数字会四舍五入到与占位符具有相同小数点位的数字。如果小数点左侧的位数大于占位符数，Excel会显示多余的位数。如果格式仅在小数点左侧含有数字符号(#)，小于1的数字会以小数点开头；例如，.47。显示内容显示格式使用此格式1234.591234.6####.#8.98.900#.0000.6310.60.#121234.56812.01234.57#.0#44.398102.652.844.398102.652.8（小数点对齐）???.???5.255.351/453/10（分数对齐）#???/???显示千位分隔符要将逗号显示为千位分隔符或按倍数1,000缩放数字，请在数字格式中包含以下分隔符。,（逗号）在数字中显示千位分隔符。如果格式中含有被数字符号(#)或零包围起来的逗号，Excel会分隔千位。位占位符后的逗号会以1,000为单位计量数字。例如，如果format_text参数为#,###.0,，Excel会将数字12,200,000显示为12,200.0。显示内容显示格式使用此格式1200012,000#,###1200012#,1220000012.20.0,,二、有关日期和时间格式的准则显示日、月和年若要将数字显示为日期格式（如日、月和年），请在format_text参数中使用以下代码。m将月显示为不带前导零的数字。mm根据需要将月显示为带前导零的数字。mmm将月显示为缩写形式（Jan到Dec）。mmmm将月显示为完整名称（January到December）。mmmmm将月显示为单个字母（J到D）。d将日显示为不带前导零的数字。dd根据需要将日显示为带前导零的数字。ddd将日显示为缩写形式（Sun到Sat）。dddd将日显示为完整名称（Sunday到Saturday）。aaa将日显示为缩写形式（日到六）。-5-Excel宝典之TEXT函数浅见工作室aaaa将日显示为完整名称（星期日到星期六）。yy将年显示为两位数字。yyyy将年显示为四位数字。显示内容显示格式使用此格式月1-12m月01-12mm月Jan–Decmmm月January–Decembermmmm月J–Dmmmmm日1-31d日01–31dd日Sun–Satddd日Sunday–Saturdaydddd日日–六taaa日星期日–星期六aaaa年00-99yy年1900-9999yyyy显示小时、分钟和秒钟若要显示时间格式（如小时、分钟和秒钟），请在format_text参数中使用以下代码。h将小时显示为不带前导零的数字。[h]以小时为单位显示经过的时间。如果使用了公式，该公式返回小时数超过24的时间，请使用类似于[h]:mm:ss的数字格式。hh根据需要将小时显示为带前导零的数字。如果格式含有AM或PM，则基于12小时制显示小时；否则，基于24小时制显示小时。m将分钟显示为不带前导零的数字。注释m或mm代码必须紧跟在h或hh代码之后或紧跟在ss代码之前；否则，Excel会显示月份而不是分钟。[m]以分钟为单位显示经过的时间。如果所用的公式返回的分钟数超过60，请使用类似于[mm]:ss的数字格式。mm根据需要将分钟显示为带前导零的数字。注释m或mm代码必须紧跟在h或hh代码之后或紧跟在ss代码之前；否则，Excel会显示月份而不是分钟。s将秒显示为不带前导零的数字。[s]以秒为单位显示经过的时间。如果所用的公式返回的秒数超过60，请使用类似于[ss]的数字格式。ss根据需要将秒显示为带前导零的数字。如果要显示秒的小数部分，请使用类似于h:mm:ss.00的数字格式。AM/PM、am/pm、A/P、a/p基于12小时制显示小时。时间介于午夜和中午之间时，Excel会使用AM、am、A或a表示时间；时间介于中午和午夜之间时，Excel会使用PM、pm、P或p表示时间。显示内容显示格式使用此格式小时0-23h小时00-23hh-6-EXCEL宝典之TEXT函数浅见工作室分钟0-59m分钟00–59mm秒0-59s秒00–59ss时间04:00:00hAM/PM时间4:36PMh:mmAM/PM时间4:36:03Ph:mm:ssA/P时间4:36:03.75h:mm:ss.00经过的时间（小时和分钟）01:02:00[h]:mm经过的时间（分钟和秒）62:16[mm]:ss经过的时间（秒和百分之一秒）3735.80[ss].00三、有关货币、百分比和科学记数法格式的准则包含货币符号若要在数字前加上美元符号($)，请在format_text参数的开头键入美元符号（例如，$#,##0.00）。若要以数字格式输入以下货币符号之一，请按NumLock并使用数字小键盘键入各符号对应的ANSI代码。显示内容使用此组合键¢Alt+0162£Alt+0163¥Alt+0165Alt+0128注释：必须使用数字小键盘；同时按Alt键和键盘最上一行的数字键将不会生成ANSI代码。自定义格式随工作簿一起保存。若要使Excel始终使用特定的货币符号，必须在启动Excel之前，更改“控制面板”的“区域选项”中所选择的货币符号。显示百分号若要将数字显示为百分数（例如，将.08显示为8%或将2.8显示为280%），请在format_text参数中包括百分号(%)。显示科学记数法若要以科学记数（指数）格式显示数字，请在format_text参数中使用以下指数代码。E（E-、E+、e-、e+）以科学记数（指数）格式显示数字。Excel会在“E”或“e”的右侧显示与小数点移动的位数对应的数字。例如，如果format_text参数为0.00E+00，Excel会将数字12,200,000显示为1.22E+07。如果将format_text参数更改为#0.0E+0，Excel会显示12.2E+6。（中文版EXCEL中e代表4位年，不用于科学记数）四、有关包含文本和添加空格的准则如果format_text参数是以下任一字符，则它们会显示：符号中文名称英文名称显示效果～波浪符Tilde～`反引号、重音号Backquote、graveaccent`-7-Excel宝典之TEXT函数浅见工作室！惊叹号Exclamationmark(英)Exclamationpoint（美）Bang#VALUE!@At15325#井号NumbersignHash（英）Pound（美）Sharp（音乐、程序）15325$美元符号Dollar$%百分号Percent%^扬抑符、脱字符Circumflex、Caret^&与号Ampersand、and&*星号，乘号，星，指针asterisk,multiply,star,pointer#VALUE!(左括号(left/open)parenthesis()右括号(left/open)parenthesis)_下横线、下划线Underscore、underscore#VALUE!-减号、负号、连字号Minus、dash、hyphen-+加号（正号）Plus+=等于号Equal={左大括号(left/open)brace{[左方括号