Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 25292|回复: 77

[技巧] [原创]自定义格式还有秘密

  [复制链接]
发表于 2011-6-21 10:30 | 显示全部楼层 |阅读模式
EXCEL经常会给我们带来惊喜,半年前我曾仔细测试“自义定格式代码”,又发现一些有用的东东。
A1单元格有数字“123”,B1输入公式“=TEXT(A1,"[DBNUM1][$-404]G/通用格式")”,结果为“一百二十三”。如果公式为“=TEXT(A1,"[DBNUM2][$-404]G/通用格式")”,得到的结果为“壹佰貳拾參”。公式改为“=TEXT(A1,"[DBNUM3][$-404]G/通用格式")”,得到的结果为“1百2十3”,其中的代码对应关系为:
123
DBNUM1
DBNUM2
DBNUM3
404
一百二十三
壹佰貳拾參
1百2十3
411
百二十三
壱百弐拾参
百2十3
412
一百二十三
壹百貳拾參
百2十3
804
一百二十三
壹佰贰拾叁
1百2十3
10000
一百二十三
壹佰贰拾叁
1百2十3
10404
一百二十三
壹佰貳拾參
1百2十3
10411
百二十三
壱百弐拾参
百2十3
10412
一百二十三
壹百貳拾參
百2十3
10804
一百二十三
壹佰贰拾叁
1百2十3
20000
一百二十三
壹佰贰拾叁
1百2十3
20404
一百二十三
壹佰貳拾參
1百2十3
20411
百二十三
壱百弐拾参
百2十3
20412
一百二十三
壹百貳拾參
百2十3
20804
一百二十三
壹佰贰拾叁
1百2十3
11000000
፩፪፫
፩፪፫
፩፪፫
27000000
일백이십삼
일백이십삼
일백이십삼

以上代码中404411都很有意义,比如10-19之间的数值转换为大写,用公式“=TEXT(A1,"[DBNUM1][$-411]g/通用格式")”,得到的是“十,十一,十二……十九”,而不是“一十,一十一,一十二……一十九”。
代码11000000可在制作表格时按数值或日期位置制作方格,而不再用插入符号了
代码27000000可得到韓语数值。

评分

参与人数 16 +208 金币 +50 收起 理由
007feiren + 9 赞一个!
1091126096 + 6
hhl395015715 + 1 俺穷人一个,就加一点哈。[$-411]这个是啥意.
shidai + 1
Zipall + 9 401确实比d方便.

查看全部评分

excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
 楼主| 发表于 2011-6-21 10:30 | 显示全部楼层
本帖最后由 faliming7 于 2011-6-21 10:35 编辑

如果A1单元格是个日期“1972-1-28”,B1输入公式“=TEXT(A1,"[$-1030000]YYYYMD")”,结果为“47128”。如果公式为“=TEXT(A1,"[DBNUM1][$-1030000]YYYYMD")”,结果为“3310”。代码对应关系如下:
1972-1-28
 
DBNUM1
DBNUM2
DBNUM3
1030000
47年1月28
33年1月0日
33年1月0日
33年1月0日
1039999
47年1月28
33年1月0日
33年1月0日
33年1月0日
30000
47年1月28
三十三年一月○日
叁拾叁年壹月零日
33年1月0日
39999
47年1月28
33年1月0日
33年1月0日
33年1月0日
60000
1391年12月12
一三一七年八月二十九日
壹叁壹柒年捌月贰拾玖日
1317年8月29日
69999
1391年12月12
1317年8月29日
1317年8月29日
1317年8月29日
170000
1972年1月28
一九○○年一月○日
壹玖零零年壹月零日
1900年1月0日
179999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
970000
1972年1月28
一九○○年一月○日
壹玖零零年壹月零日
1900年1月0日
979999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1170000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1179999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1970000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1979999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
80000
1972年1月28
一九○○年一月○日
壹玖零零年壹月零日
1900年1月0日
89999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
880000
1972年1月28
一九○○年一月○日
壹玖零零年壹月零日
1900年1月0日
889999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
100000
1972年1月28
一九○○年一月○日
壹玖零零年壹月零日
1900年1月0日
109999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
900000
1972年1月28
一九○○年一月○日
壹玖零零年壹月零日
1900年1月0日
909999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1900000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1909999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
110000
1972年1月28
一九○○年一月○日
壹玖零零年壹月零日
1900年1月0日
139999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
910000
1972年1月28
一九○○年一月○日
壹玖零零年壹月零日
1900年1月0日
939999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1910000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1939999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1100000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1109999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1110000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
1139999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
2000000
١٩٧٢١٢٨
١٩٠٠١٠
١٩٠٠١٠
١٩٠٠١٠
3999999
۱۹۷۲۱۲۸
۱۹۰۰۱۰
۱۹۰۰۱۰
۱۹۰۰۱۰
4000000
१९७२१२८
१९००१०
१९००१०
१९००१०
4999999
१९७२१२८
१९००१०
१९००१०
१९००१०
5000000
১৯৭২১২৮
১৯০০১০
১৯০০১০
১৯০০১০
5999999
১৯৭২১২৮
১৯০০১০
১৯০০১০
১৯০০১০
6000000
੧੯੭੨੧੨੮
੧੯੦੦੧੦
੧੯੦੦੧੦
੧੯੦੦੧੦
6999999
੧੯੭੨੧੨੮
੧੯੦੦੧੦
੧੯੦੦੧੦
੧੯੦੦੧੦
7000000
૧૯૭૨૧૨૮
૧૯૦૦૧૦
૧૯૦૦૧૦
૧૯૦૦૧૦
7999999
૧૯૭૨૧૨૮
૧૯૦૦૧૦
૧૯૦૦૧૦
૧૯૦૦૧૦
8000000
୧୯୭୨୧୨୮
୧୯୦୦୧୦
୧୯୦୦୧୦
୧୯୦୦୧୦
8999999
୧୯୭୨୧୨୮
୧୯୦୦୧୦
୧୯୦୦୧୦
୧୯୦୦୧୦
9000000
௧௯௭௨௧௨௮
௧௯00௧月0日
௧௯00௧月0日
௧௯00௧月0日
9999999
௧௯௭௨௧௨௮
௧௯00௧月0日
௧௯00௧月0日
௧௯00௧月0日
10000000
၁၉၇၂年၁月၂၈
၁၉၀၀年၁月၀日
၁၉၀၀年၁月၀日
၁၉၀၀年၁月၀日
11000000
፩፱፯፪年፩月፪፰
፩፱00年፩月0日
፩፱00年፩月0日
፩፱00年፩月0日
20000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
20999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
21000000
一九七二年一月二十八
一九○○年一月○日
一九○○年一月○日
一九○○年一月○日
21999999
一九七二年一月二十八
一九○○年一月○日
一九○○年一月○日
一九○○年一月○日
22000000
壹玖柒貳年壹月貳拾捌
壹玖零零年壹月零日
壹玖零零年壹月零日
壹玖零零年壹月零日
22999999
壹玖柒貳年壹月貳拾捌
壹玖零零年壹月零日
壹玖零零年壹月零日
壹玖零零年壹月零日
23000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
23999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
24000000
一九七二年一月二十八
一九00年一月0日
一九00年一月0日
一九00年一月0日
24999999
一九七二年一月二十八
一九00年一月0日
一九00年一月0日
一九00年一月0日
25000000
壹九七貳年壹月貳拾
壹九零零年壹月零
壹九零零年壹月零
壹九零零年壹月零
25999999
壹九七貳年壹月貳拾
壹九零零年壹月零
壹九零零年壹月零
壹九零零年壹月零
26000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
26999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
27000000
일구칠이일이십팔
일구영영일영
일구영영일영
일구영영일영
27999999
일구칠이일이십팔
일구영영일영
일구영영일영
일구영영일영
28000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
28999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
29000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
29999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
30000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
30999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
31000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
31999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
32000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
32999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
33000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
33999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
34000000
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日
34999999
1972年1月28
1900年1月0日
1900年1月0日
1900年1月0日

回复

使用道具 举报

 楼主| 发表于 2011-6-21 10:30 | 显示全部楼层
本帖最后由 faliming7 于 2011-6-21 10:38 编辑

以上代码中,如30000-39999之间产生的结果是一样的。其他也是如此,可以看出,这些代码不仅数字如1234等代表着不同的含义,而且似乎与代码数字所处的位置有关。测试发现代码最长为8位。这些代码所产生的日期很多令我莫名其妙,不知道是什么日期,但这些代码在20072010版中又会产生不同的结果。有些结果十分令人振奋。如110000-139999910000-9399991910000-19399991110000-1139999之间的代码都产生阴历日期,其他有些代码与2003版产生的结果不同,但目前不知道是什么日期。(注:=TEXT(NOW(),"[DBNum2][$-130000]yyyymd")产生阴历日期曾由luxueyan2008
2008-5-30 13:05报道,链接地址为:http://www.excelpx.com/thread-54853-1-1.html
包括曾有人报出的“=TEXT(A1,"[$-f800]")”得到结果“1972128星期五”;B代表佛历;以及显示日本年历等等,EXCEL自定义格式似乎有无数的秘密还未开发。
猜测,代码中不同的数字有不同的含义,数字所处的位置有不同的含义,在亚洲版本中,微软有意用自定义格式显示出亚洲不同国家的年历,但是这些东西微软并未公布,个人猜想可能是微软某些程序员的不成熟作品。
回复

使用道具 举报

 楼主| 发表于 2011-6-21 10:30 | 显示全部楼层
占位
回复

使用道具 举报

发表于 2011-6-21 11:23 | 显示全部楼层
好贴啊
回复

使用道具 举报

发表于 2011-6-21 12:36 | 显示全部楼层
法老师的钻研精神,佩服啊
回复

使用道具 举报

发表于 2011-6-21 13:17 | 显示全部楼层
这钻研精神,佩服!
回复

使用道具 举报

发表于 2011-6-21 13:36 | 显示全部楼层
回复

使用道具 举报

发表于 2011-6-21 18:45 | 显示全部楼层
好资料,收藏了先!谢谢
回复

使用道具 举报

发表于 2011-6-21 20:34 | 显示全部楼层
好东西 先收藏
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-28 23:46 , Processed in 0.452676 second(s), 7 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表