EXCEL中的TEXT 函数与日期函数详解

foodorwater

不知道有沒有同學轉過這個帖子，我覺得值得再次溫習。

TEXT 函数运用终极篇(小爪) 转自http://hi.baidu.com/huanhuanxiao ... 2f3d1663279890.html

TEXT
将数值转换为按指定数字格式表示的文本。
语法
TEXT(value,format_text)
Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。
Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。
说明
Format_text 不能包含星号 (*)。
通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。使用函数 TEXT 可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。
A B
1 销售人员 销售
2 Buchanan 2800
3 Dodsworth 40%
公式 说明（结果）
=A2&" sold "&TEXT(B2, "$0.00")&" worth of units." 将上面内容合并为一个短语 (Buchanan sold $2800.00 worth of units.)
=A3&" sold "&TEXT(B3,"0%")&" of the total sales." 将上面内容合并为一个短语 (Dodsworth sold 40% of the total sales.)
TEXT生成的三种日期格式：
e:
=text(now(),"e") 与 =text(now(),"YYYY") 是一样的
b
=text(now(),"b") 佛历2位年份
bbbb
=text(now(),"bbbb") 佛历4位年份 与公元纪年相差543年也就是公元纪年＋543就是了。
dbnum1
=TEXT(NOW(),"[dbnum1]yyyy年m月d日") 中文小写日期
dbnum2
=TEXT(NOW(),"[dbnum2]yyyy年m月d日") 中文大写日期
佛历为部分佛教国家计算纪元的方式，该计算方式以释迦摩尼去世当年度为计算基准。
西元1950年，锡兰首都可伦坡举办首届“世界佛教徒友谊会”，会中议决：佛陀诞生于西元前623年，成道于西元前588年，去世于西元元前543年。三年后的1954年，该年会于缅甸仰光举行，会中再决议佛教国家以“佛历”纪元，并以释迦牟尼涅盘日推算，西元1954年为佛历2498年。
日期转换
把20080808修改成2008－08－08的格式可以用以下公式：
=--text("20080808","#-00-00")
把英文月份化成数字月份：
=MONTH(--("Aug"&1))
=MONTH(--("August"&1))
返回一个月的最后一天，就是用下一个月的第一天－1就行。
得到上一个月的最后一天：
=today()-day(now())
确定季度：
=text(roundup(month(now())/3,0),"[dbnum1]第0季度")
星期：
=weekday(now())
=mod(today()-2,7)+1
=text(today(),"aaaa")
返回最近星期天的日期
=today()-weekday(today(),2)
=today()-mod(today()-1,7)
=today()+8-weekday(today())
=today()+6-mod(today()-2,7)
两个日期相隔的星期天数：
=int((weekday(date1,2)+date2-date1)/7)
转帖]TEXT 函数运用终极篇(小爪)
转自http://hi.baidu.com/huanhuanxiao ... 2f3d1663279890.html
2008-06-18 18:58
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

Viewsonicquanta

http://club.excelhome.net/thread-331382-1-1.html

我是从这个帖子里面了解text()的~~~
理解的还不完全，继续学习~~
谢谢jimmy

happy_2006

多谢，正需要啊！

顺有问题求教，
前两天看到有一个公式，是求和的，我把它稍微改变了一下：
=SUM(--(TEXT(SUBSTITUTE(A1:A10,"天",),"0.00;!0;0;!0")))&"天"

A1:A10是数字加字符"天"，其中有一个单元格是空，text函数将数组中空值转为0，如下:
{"1";"3";"5";"725";"9";"0.5";"";"3.5";"125";"19.5"}转为
{"1.0";"3.0";"5.0";"725.0";"9.0";"0.5";"0";"3.5";"125.0";"19.5"}

如此之后，便可以运算了，这个公式是这类问题中较佳的，那么这个格式"0.00;!0;0;!0"作何解？

[ 本帖最后由 happy_2006 于 2009-12-8 13:00 编辑 ]

chen_pinghui

谢谢楼主分享。

foodorwater

原帖由 happy_2006 于 2009-12-8 12:32 发表
多谢，正需要啊！

顺有问题求教，
前两天看到有一个公式，是求和的，我把它稍微改变了一下：
=SUM(--(TEXT(SUBSTITUTE(A1:A10,"天",),"0.00;!0;0;!0")))&"天"

A1:A10是数字加字符"天"，其中有一个单元格是空 ...

按照公式的設置，應該是
{"1";"3";"5";"725";"9";"0.5";"";"3.5";"125";"19.5"}轉為
{"1.00";"3.00";"5.00";"725.00";"9.00";"0.50";"0";"3.50";"125.00";"19.50"}
然後代碼中本身包含的字母、字符、數字等或在變量前加上強制字符符號"!"
如果遇到空格，返回0，如果遇到數字，返回兩位小數的文本型數值，當小數點後數字超過兩位，進行四捨五入，不足補足0顯示。

我的理解是這樣的，不知是否明白。

[ 本帖最后由 foodorwater 于 2009-12-8 14:04 编辑 ]

foodorwater

原帖由 Viewsonicquanta 于 2009-12-8 12:12 发表
http://club.excelhome.net/thread-331382-1-1.html

我是从这个帖子里面了解text()的~~~
理解的还不完全，继续学习~~
谢谢jimmy

這個置頂帖子相信很多筒子都拜讀過了，不過還是謝謝你，如果有另外一些新發現，不妨分享一下。

jyhxr

好东西,谢谢楼主提供分享,辛苦了!

huangyun

谢谢楼主分享，收藏学习。

happy_2006

多谢解说！

栀子香

收下了。谢谢！