Excel Text函数的9大使用实例

在 Excel 中,Text函数用于把数字、日期转为文本,转换时要指定格式。可指定的格式比较多,主要有保留小数位、给数字加百分号或千位分隔符、在数字前加货币符号、把日期按年月日显示、时间按 24 小时或 12 小时显示、格式中带条件等,所有这些格式将用具体实例演示,总共分为九大实例,以下就是它们的具体操作方法,实例操作所用版本均为 Excel 2016。

一、Text函数语法

1、表达式:TEXT(Value, Format_Text)

中文表达式:TEXT(数值, 格式)

2、说明:

A、保留指定小数位数时,如果要保留小数部分末尾的 0,用 0 占位符,例如 5.604 用格式 0.00 或 #.00 保留两位小数,结果为 4.60;如果不希望保留小数部分末尾的 0,用 # 占位符,例如 5.604 用格式 #.## 保留两位小数,结果为 5.6。

B、保留指定小数位数时,如果要求小数部分末尾的 0 用空格代替,用 ? 占位符,例如 4.503 用格式 0.0?,结果为 4.5。

二、Text函数的使用方法及实例

(一)用 0 和 # 保留小数

1、选中 A1 单元格,输入公式 =TEXT(A1,"0.00"),按回车,返回 34.50;双击 A1 单元格,把公式中的 0.00 改为 #.##,按回车,返回 34.5;操作过程步骤,如图1所示:

图1

2、用格式 0.00 时,Text函数把数值转为文本时会保留小数部分末尾的 0;用格式 #.## 时,则会省略小数部分末尾的 0。

(二)把小数转为整数和分数

选中 B1 单元格,把公式 =TEXT(A1,"0 ?/?") 复制到 B1,按回车,返回 2 1/8;双击 B1,把公式中的 0 改为 #,按回车,返回相同的结果;操作过程步骤,如图2所示:

图2

(三)以 0 开头的数字用Text函数转换后仍以0开头

1、选中 B1 单元格,输入公式 =TEXT(A1,REPT(0,8)),如图3所示:

图3

2、按回车,返回 02512569,原 A1 中的数字前面有三个 0,返回结果只有一个 0,如图4所示:

图4

3、公式说明:公式 =TEXT(A1,REPT(0,8)) 用 REPT函数把 0 重复 8 次,即得格式 00000000,然后把 A1 中的数字 0002512569 按 8 个 0 的格式返回文本;如果用 Rept函数把 0 重复 9 次,即 REPT(0,9),则返回结果 002512569。

(四)用井号 # 和星号 * 把数字转为文本并取整

1、选中 B1 单元格,把公式 =TEXT(A1,"#*,") 复制到 B1,按回车,返回 24;把鼠标移到 B1 单元格右下角的填充柄上,按住鼠标左键,往下拖,则 A2 和 A3 单元格中的数字也被转为文本并取整;操作过程步骤,如图5所示:

图5

2、从对三个数字的取整可以看出,每个数字都按四舍五入取整;另外要注意取整格式 #* 后带一个半角逗号(,)。

(五)给数字加上货币符号

1、假如要给价格加上元(¥)。选中 E2 单元格,把公式 =TEXT(C2,"¥#") 复制到 E2,按回车,则给 C2 中的价格加上 ¥;用往下拖的方法给其它价格加上 ¥,操作过程步骤,如图6所示:

2、如果要保留指定小数位数(例如保留一位小数),可以把公式改为 =TEXT(C2,"¥#.0"),如图7所示:

图7

3、按回车,返回 89.0,同样用往下拖的方法按指定格式返回其它价格,如图8所示:

图8

(六)给数字加上百分号 %

1、选中 B2 单元格,把公式 =TEXT(A1,"0.0%") 复制到 B2,按回车,则 A1 单元格的数字被加上 %,并自动扩大 100 倍;用往下拖的方法给其它数字加上 %,操作过程步骤,如图9所示:

图9

2、如果只是给小数加上百分号,不要求扩大 100 倍,可以把公式改为 =TEXT(A1/100,"0.0%"),操作过程步骤,如图10所示:

图10

(七)给数字加上千位分隔符

1、把公式 =TEXT(A1,"#,###") 复制到 B1,按回车,返回 235,362,同样用往下拖的方法给其它数字加上千位分隔符,操作过程步骤,如图11所示:

图11

2、如果有小数要保留指定小数位数(例如保留两位小数),可以把公式改为 =TEXT(A1,"#,###.00")

(八)按指定格式显示日期和时间

1、按指定格式显示日期

A、把公式 =TEXT(A1,"YYYY年MM月dd日") 复制到 B1 单元格,按回车,返回 2018年9月20日,用往下拖的方法把其它日期显示为指定格式;双击 B1 单元格,把年月改为-,删除“日”,按回车,返回 2018-09-20,同样往下拖把其它日期改为指定格式,操作过程步骤,如图12所示:

图12

B、还可以把日期显示为其它格式,例如把短横线改为点(.),只需把公式改为 =TEXT(A1,"YYYY.MM.dd")。另外,如果“年”只允许显示两位,可以把公式改为 =TEXT(A1,"YY.MM.dd")。

2、按指定格式显示时间

A、把公式 =TEXT(A1,"h:mm:ss") 复制到 B1 单元格,按回车,返回 9:23:38,往下拖把其它时间显示为时分秒的格式;双击 B1 单元格,把公式改为 =TEXT(A1,"h:mm:ss AM/PM"),按回车,返回 12 小时制的时间,同样往下拖把其它时间改为12小时制;再次双击 B1 单元格,把秒去掉,公式变为 =TEXT(A1,"h:mm AM/PM"),按回车,返回只有时分的时间;操作过程步骤,如图13所示:

图13

B、格式 "h:mm:ss AM/PM" 中,AM 表示上午,PM 表示下午。如果要求把日期和时间都按指定格式输出,公式可以这样写:=TEXT(A1,"YYYY年MM月dd日 h:mm:ss")。

(九)格式带条件

1、假如要求价格大于 0,显示价格,价格为 0 或空显示 0。把公式 =TEXT(C2,"[>"&C7&"]0") 复制到 E2,按回车,返回 89,用往下拖的方法把其余价格显示为指定格式,操作过程步骤,如图14所示:

图14

2、价格大于 0 的都显示为价格,价格为 0 和空都显示为 0,说明格式 "[>"&C7&"]0") 无误。如果反过来要求价格小于等于 0 的显示价格,价格大于 0 的显示 0,格式可以这样写 "[>"&C7&"]!0",即在 0 前加 !,则公式变为 =TEXT(C2,"[>"&C7&"]!0")。

人已赞赏
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧