|
亲爱的姑娘，不管我的容颜如何变幻，但爱你的心至死不渝！ --------- 龙逸凡 上面图片中十六个单元格，内容不一，有阿拉伯数字，汉字大写的数字，百分点时间、日期、星期，还有肉麻的情诗，大家可能会觉得奇怪，这与今天的文章有何关系？ 实际上，上面图片单元格中的值都是同一个值：42975，那为什么能显示出不同的内容呢？ 这就用到了Excel中一个常用且重要的功能：自定义格式。 从广义上来讲，Excel中的数据类型分为二类：数值、文本。朋友们可能会说，不是还有日期、时间、百分比等数据类型吗？实际上，这些都是数值型。我们可以在Excel中同时按下Ctrl和分号，输入系统今天的日期（“2017/8/25”），然后复制它，将其“选择性粘贴-数值”到一个空白单元格，粘贴后的值就是42972。 或者，也可以在单元格中输入数字42972，点击右键-设置单元格格式，将单元格设置为日期格式，刚才的数字就会变为“2017/8/25”。（如果输入1，设置为日期格式就是1900/1/1） 同样的，我们可通过设置单元格格式，将其设置为货币、时间、百分比、分数、大写金额等等。如下图： 上面都是系统预先自定义好的一些格式，我们还可根据需要，按照自定义格式的规则，打造自己的格式，比如题图中的那些示例。 注： 由于自定义格式较为复杂，在本文中不展开，大家可参考文章最后面附的几则技巧或《“偷懒”的技术：打造财务Excel达人》中的第三章。 在Excel中，实际值和显示值两者可以是分离的，可以是不一样的，比如上图中的C17、C18单元格，使用自定义格式将42975显示为12345678、99999，甚至显示为C12:C16单元格区域的文本。这样就解答了新手们常问的几个问题： 1、为什么我点击千位分隔符样式，将下图I列的值，设置为添加千位分隔符且只显示二位小数后，我在其他地方编制公式="金额"&I4&“万元"，会显示“金额3580.2188万元”，而不是我要的 ”金额3,580.22万元“ 2、为什么在下图中编制公式： =D2&"净利润"&C18&"万元" 希望显示为“2017年1月净利润67.30万元”，却显示为“42766净利润67.3011万元”？？ 要显示为“2017年1月净利润67.30万元”可以使用TEXT函数来格式化数据，将上面的公式改为： =TEXT(D2,"yyyy年m月")&"净利润"&TEXT(C18,"#,###.00")&"万元" 对新手来讲，这一点似乎不可理解。 要理解这一点的话，我们记住下面这一句话就是了： 数据是脸，格式是面具，同一张脸，可以戴不同的面具，不管面具是怎么样，但脸还是那张脸。 七夕情人节快到了，套用一句应景的情诗，就是文前的那句话： 不管我的容颜如何变幻，但爱你的心至死不渝！海枯石烂不变心！ -------附几则自定义格式的技巧------------------------ 技巧2：数字格式中的智多星——自动设置满足指定条件的数字格式。 在格式代码中加入带中括号的条件，条件由比较运算符和数值两部分组成。例如，下面的格式以红色字体显示小于和等于 100 的数字，而以蓝色字体显示大于 100 的数字： [红色][<=100];[蓝色][>100] 龙逸凡注：此功能有点类以于条件格式。但此条件仅限于由比较运算符和数值两部分组成 （此类运算符包括：= 等于、> 大于、< 小于、>= 大于等于、<= 小于等于和 <> 不等于）。而条件格式的适用范围更广，还可使用由函数组成的公式组成的条件，格式范围也更多更广。 技巧3：自动显示固定宽度：如将格式自定义成000000，则输入小于6位的数字，自动在前面以0填充，输入大于6位的数字或文本则不变。 技巧4：如果单元格区域既含有五位数的邮政编码又含有九位数的邮政编码，则可以使用自定义格式以便同时正确显示这两种类型的邮政编码。方法是：单击“分类”列表中的“自定义”，然后在“类型”框中键入下列内容： [<=99999]00000;00000-0000 技巧5:Excel中的障眼法 1、在【格式】菜单上，单击〖单元格〗子菜单，再单击“数字”选项卡, 在“分类”列表中，单击“自定义”, 在“类型”框中键入“"Excel偷懒的技术"”试试,不管输入什么,显示出来的都是“Excel偷懒的技术”了。 2、如需将输入的所有数字均以*显示，自定义格式为“**;**;**”，同理，将输入的所有数字均以A显示，自定义格式为“*A;*A;*A”。 技巧6: Excel中的隐身术 方法1:将单元格字体颜色设置成与底色一样的颜色 方法2: 【格式】→〖单元格〗→“数字”选项卡, 在“分类”列表中，单击“自定义”, 在“类型”框中键入“;;;”（三个分号）（此格式隐藏单元格所有的数值或文本）；格式“;;”（两个分号）隐藏数值而不隐藏文本。格式“##;;;”只显示正数；格式“;;0;”只显示零值。格式“""”隐藏正数和零值，负数显示为-，文字不会隐藏；格式“???”仅隐藏零值,而不隐藏非零值和文本。（格式“???”有四舍五入显示的功能，故格式“???”不仅隐藏0值，连小于0.5的值都隐藏了，同理,它也将0.6显示为1，将1.8显示为2。建议用：工具>选项>视图>窗口选项，零值前的对勾去掉，但此方法的缺点是整个表不显示零值，而不是某区域不显示零值。） 技巧7：Excel中的变形术 1、在输入数字前加一个单引号’，数字就“变形”为文本了。输入的单引号不会显示出来，也不计入字符串的长度（你可用len函数试试）。如果要再次将该文本格式的数字转换为数字格式，请“使用选择性粘贴→乘”的功能（请参见“选择性粘贴→粘贴时运算”部分）。 2、将数字直接以百元为单位显示，自定义格式为：“#!.00"百元"”或“0".00""百元"”；直接以千元为单位显示，自定义格式为：“#,"千元"”或“0.00,"千元"”；直接以万元为单位显示，自定义格式为：“#!.0,"万元"”或“#!.0000"万元"”；将数字直接以百万元显示，自定义格式为“#,,"百万元"”或“0.00,,"百万元"”。财务人员、统计人员一定要掌握这几个自定义格式，很有用哦。 －－－－－－－－－－－－－－－－－－－－ |
|
|
来自： 偷懒的技术 > 《自定义格式条件格式》
