函数text提高新知道

楼主
hlxz

1.TEXT(102,"!r0c00")模式
TEXT(102,"!r0c00") →>"r1c02"
前面加个indirect

indirect(TEXT(102,"!r0c00"),)-->INDIRECT("r1c02",)

就是等于行1列2 即B1的值
故展开

=INDIRECT(TEXT(1002,"!r00c00"),)-->B10的值
=INDIRECT(TEXT(1002,"!r00c0"),)-->B100的值
=INDIRECT(TEXT(102,"!r00c0"),)-->B10的值注意前后的变化

继续扩展:

=INDIRECT(TEXT({101;102;103;201;202;203;301;302;303},"!r0c00"),)-->
=INDIRECT({"r1c01";"r1c02";"r1c03";"r2c01";"r2c02";"r2c03";"r3c01";"r3c02";"r3c03"},)

题外话：（做个铺垫，只是为了学习这个方法）

SMALL(ROW($A$1:$C$3)*100+COLUMN($A$1:$C$3),ROW(1:9))-->{101;102;103;201;202;203;301;302;303}

2.TEXT({10;1;11;2;13;14;15;8;7;3},"[<=8]#;1")模式
先了解一下:

TEXT({10;1;11;2;13;14;15;8;7;3},"[<=8]#;1")-->{"1";"1";"1";"2";"1";"1";"1";"8";"7";"3"}

意思就是说大于8的为1,小于等于8的为原来值。
如果修改成大于等于：

=TEXT({10;1;11;2;13;14;15;8;7;3},"[>=8]#;1")-->{"10";"1";"11";"1";"13";"14";"15";"8";"1";"1"}

这样就大家能明白了。
继续展开

=TEXT({10;1;11;2;13;14;15;8;7;3},"[=8]#;1")-->={"1";"1";"1";"1";"1";"1";"1";"8";"1";"1"}
=TEXT({10;1;11;2;13;14;15;8;7;3},"[<>8]#;1")-->={"10";"1";"11";"2";"13";"14";"15";"1";"7";"3"}
=TEXT({10;1;11;2;13;14;15;8;7;3},"[<>""]#;1")-->={"10";"1";"11";"2";"13";"14";"15";"8";"7";"3"}

这样就大家就更明白

相关的例子

"[<="&LEN($A$6)&"]#;1"

如果LEN($A$6) 等于8，那么一样可以得到"[<=8]#;1"
而下列公式的计算后得到

ROW($1:$10)-1-->{0;1;2;3;4;5;6;7;8;9}
10-ROW($1:$10)-->{9;8;7;6;5;4;3;2;1;0}

这2个数组有什么用呢？
例1

={"1";"1";"1";"1";"1";"1";"3";"9";"8";"7"}*10^({9;8;7;6;5;4;3;2;1;0})-->
={"1";"1";"1";"1";"1";"1";"3";"9";"8";"7"}*{1000000000;100000000;10000000;1000000;100000;10000;1000;100;10;1}-->
={1000000000;100000000;10000000;1000000;100000;10000;3000;900;80;7}

用sum函数求和，可以得到1111113987。
例2

={"1";"1";"1";"1";"1";"1";"3";"9";"8";"7"}*10^({0;1;2;3;4;5;6;7;8;9})→>
={"1";"1";"1";"1";"1";"1";"3";"9";"8";"7"}*{1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000}→>
={1;10;100;1000;10000;100000;3000000;90000000;800000000;7000000000}

用sum函数求和，可以得到7893111111，与上面刚好相反。
例3

=SUM({"1";"1";"1";"1";"1";"1";"3";"9";"8";"7"}*(10^(-{0;1;2;3;4;5;6;7;8;9})))-->
=SUM({"1";"1";"1";"1";"1";"1";"3";"9";"8";"7"}*({1;0.1;0.01;0.001;0.0001;0.00001;0.000001;0.0000001;0.00000001;0.000000001}))-->
=SUM({1;0.1;0.01;0.001;0.0001;0.00001;0.000003;0.0000009;0.00000008;0.000000007})-->
=1.111113987

例4

=sum({"1";"1";"1";"1";"1";"1";"3";"9";"8";"7"}*10^(-{9;8;7;6;5;4;3;2;1;0}))-->
=sum({"1";"1";"1";"1";"1";"1";"3";"9";"8";"7"}*{0.000000001;0.00000001;0.0000001;0.000001;0.00001;0.0001;0.001;0.01;0.1;1}) -->
=SUM({0.000000001;0.00000001;0.0000001;0.000001;0.00001;0.0001;0.003;0.09;0.8;7})-->
=7.893111111

2楼
hlxz

问题：
A1 = 123456789 要求B2 等于 a+1 a+2 a+3 a+4 a+5 a+6 a+7 a+8 a+9

解答：

=SUBSTITUTE(TEXT(A1,REPT("]+0 ",9)),"]","a")-->=TEXT(A1,REPT("a+0 ",9))

引出了一个text 函数用法

=TEXT(A1,REPT("a+0 ",9))-->
=TEXT(123456789,"a+0 a+0 a+0 a+0 a+0 a+0 a+0 a+0 a+0 ")-->
a+1 .a+2 a+3 a+4 a+5 a+6 a+7 a+8 a+9

问题：
A1 = 123456789 要求B1 等于01:23:45:67:89

解答：

=TEXT(A1,REPT("00\:",(LEN(A1)-1)/2)&"00")-->
=TEXT(A1,"00\:00\:00\:"&"00")-->
=TEXT(A1,"00\:00\:00\:00")-->
01:23:45:67:89

3楼
hlxz

问题：
如何完成下列转换：

45-->.4.5
123456789-->.1.2.3.4.5.6.7.8.9

解答:

=SUBSTITUTE(TEXT(C11,REPT("a0",LEN(C11))),"a",".")
=TEXT(A1,REPT("!.0",LEN(A1)))

4楼
hlxz

	关于函数TEXT
原值	公式结果	公式
1	一	=TEXT(A3,"[DBNUM1]")
10	一十	=TEXT(A4,"[DBNUM1]")
11	一十一	=TEXT(A5,"[DBNUM1]")
1	一	=TEXT(A6,"[DBNUM1]0")
10	一○	=TEXT(A7,"[DBNUM1]0")
11	一一	=TEXT(A8,"[DBNUM1]0")
1	壹	=TEXT(A9,"[DBNUM2]")
10	壹拾	=TEXT(A10,"[DBNUM2]")
11	壹拾壹	=TEXT(A11,"[DBNUM2]")
1	壹	=TEXT(A12,"[DBNUM2]0")
10	壹零	=TEXT(A13,"[DBNUM2]0")
11	壹壹	=TEXT(A14,"[DBNUM2]0")
1	１	=TEXT(A15,"[DBNUM3]")
10	１十	=TEXT(A16,"[DBNUM3]")
11	１十１	=TEXT(A17,"[DBNUM3]")
1	１	=TEXT(A18,"[DBNUM3]0")
10	１０	=TEXT(A19,"[DBNUM3]0")
11	１１	=TEXT(A20,"[DBNUM3]0")
12.3	12.3	=TEXT(A21,"＃＃.＃＃")
12.3	12.30	=TEXT(A22,"＃＃.＃0")
12.35	12.35	=TEXT(A23,"＃＃.＃0")
0.123	12.30%	=TEXT(A24,"＃＃.＃0％")
0.123	23/187	=TEXT(A25,"＃？？/？？？")
1234	￥1,234	=TEXT(A26,"￥＃,＃＃0")
2007-8-9	2007	=TEXT(A27,"YYYY")
2007-8-9	07	=TEXT(A28,"YY")
2007-8-9	08	=TEXT(A29,"MM")
2007-8-9	09	=TEXT(A30,"DD")
2007-8-9	星期四	=TEXT(A31,"aaaa")
2007-8-9	四	=TEXT(A32,"aaa")
2007-8-9	aa	=TEXT(A33,"aa")
8:26	08	=TEXT(A34,"hh")
8:26	08:26	=TEXT(A35,"hh：mm")
8:26	08:26:00	=TEXT(A36,"hh：mm：ss")

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素，经与ExcelTip.Net站长Apolloh商议并征得其同意，现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示，供有需要的人士查询使用，也慰缅曾经的论坛时代。所示各个帖子的原作者如对版权有异议，可与本人沟通提出，或于本站点留言，我们会尽快处理。在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一

猜您喜欢

评论列表

sitemap