文|仟樱雪

数据分析工具与时俱进，Python、R语言、SQL、Excel等等，但Excel作为基础软件，仍旧是基本的职场法宝，鉴于90%的基础数据分析岗位都会准备Excel的机试，而对于单元格的数据格式设置，却是“数据美化”的又一个神技。

久经职场的老鸟们，甚至简单的认为单元格格式的“美化”，就是简单的数据显示，殊不知是小巫见大巫，自欺欺人。

纵使该技能是“一叶障目”，但是对于众多需要救场的职场小白来说，确实是不可多得的妙招。

单元格格式设置：将数据依据某些条件，进行快速的设置显示，例如：数值、日期、文本等数据单元格或者区域，设置成想要显示的格式。

核心要点：输入值的数据区域VS设置格式后显示的数据区域，本质上是一致的，仅仅是因为自定义格式的代码而造成的“不要轻易相信你的眼睛”的错觉。

Excel单元格格式设置的使用，是办公必备技能，快捷化数据分析的一大捷径，主要的精髓如下：

神技1：快捷篇——快速启动设置单元格格式

神技2：日常篇——常见单元格格式设置

神技3：干货篇——自定义单元格格式设置

Excel提供了大量的数据格式类型：常规、数值、货币、会计专用、日期、时间、百分比、分数、科学记数、文本、特殊和自定义等等，如果不做设置，输入数据时则默认为“常规”格式显示。

在选中需设置单元格格式的数据区域时，再启动设置功能时，有不同的快捷方式，常见的有4种。

操作说明：选中需设置数据格式的单元格或区域，按下“Ctrl”键+数字键“1”，则弹出设置单元格格式的对话框。

操作说明：选中需要设置数据格式的单元格或区域，选择右键菜单中的“设置单元格格式”，则弹出设置单元格格式的对话框。

操作说明：选中需要设置数据格式的单元格或区域，在Excel的功能菜单区域“开始”菜单栏的“数字”设置区域，选择“其他数字格式”，弹出“设置单元格格式”对话框，选择相关格式进行设置。

操作说明：选中需要设置数据格式的单元格或区域，在Excel的功能菜单区域“开始”菜单栏的“数字”设置区域，点击该区域的右下角的“三角”下拉标志，则弹出“设置单元格格式”对话框，选择相关格式进行设置。

选中需设置格式的单元格或数据区域后，启动单元格对话框后，常见的数据格式如下：

注意：“会计专用”格式和“货币”格式的区别；货币格式是右对齐，会计格式是两端对齐（货币的符号和数据最后一位数在两端各自对齐）。

操作：对于负数的设置较正数的格式比较独特，选中数据“-520.13”，按下快捷键“Ctrl”+“1”或神秘“小三角”标识，弹出单元格格式设置对话框：相关数值格式设置如图展示；

①负数显示在括号内且红色标记，保留2位小数：显示为红色的（520.13）；

②负数显示在括号内且黑色标记，保留2位小数：显示为黑色的（520.13）；

③负数显示红色标记且去掉负号，保留2位小数： 显示为红色的  520.13；

④负数显示黑色标记保留负号，保留2位小数：显示为黑色的  -520.13；

⑤负数显示红色标记保留负号，保留2位小数：显示为红色的  -520.13；

操作：对于负数的货币格式的设置，选中数据“-520.13”，按下快捷键“Ctrl”+“1”或神秘“小三角”标识，弹出单元格格式设置对话框：相关数值格式设置如图展示；

说明：除了多了货币的类型设置外，和“数值”格式的设置类型一致，只多一个货币符号在数据之前标识显示。

操作1：对于日期格式的数据设置，选中数据“520.13”，按下快捷键“Ctrl”+“1”或神秘“小三角”标识，弹出单元格格式设置对话框：相关数值格式设置如图展示；

说明：日期类型的格式较多，可根据不同的需求进行选择，例如：选择“2012-03-14”格式数据，则显示为“1901-06-03”等。

操作2：选中数据“520.13”，直接在“开始”菜单的“数字”区域，选择“短日期”或“长日期”的日期格式：

说明：短日期格式数据时，数据“520.13”，例如：选择“短日期”格式数据，则显示为“1901/6/3”;

长日期格式数据时，数据“520.13”，例如：选择“长日期”格式数据，则显示为“1901年6月3日”。

操作：对于时间的数据格式的设置，选中数据“520.13”，按下快捷键“Ctrl”+“1”或神秘“小三角”标识，弹出单元格格式设置对话框：相关数值格式设置如图展示；

说明：时间类型的格式较多，可根据不同的需求进行选择，例如：选择“13:30:55”格式数据，则显示为“3:07:12”等。

操作：对于特殊数据的格式的设置，选中数据“520.13”，按下快捷键“Ctrl”+“1”或神秘“小三角”标识，弹出单元格格式设置对话框：相关数值格式设置如图展示；

说明：

选择“邮政编码”设置数据，则数据“520.13”，显示为“00520”；

选择“中文小写数字”设置数据，则数据“520.13”，显示为“五百二十.一三”；

选择“中文大写数字”设置数据，则数据“520.13”，显示为“伍佰贰拾.壹叁”。

自定义单元格格式：是指在excel中，可以根据需求，而进行定制显示数据、文本、日期等特定的数据的格式。

定制自定义格式时，至多只能定义4种格式：且中间得用英文分号隔开“;“

正数格式；负数格式；零值格式；文本格式

若需要去掉其中某部分进行定义，则该部分应该以分号结束，例如：定制不不包含负数格式的显示方式；

正数格式；；零值格式；文本格式

注意：

1）对单元格或数据区域创建了自定义格式后，该格式会保存在Excel工作簿中，直到该格式被删除，否则不会轻易被发现；

2）第二章中常见的数据格式设置（数值、货币、会计专用等），都可以通过自定义格式的代码进行设置实现。

在单元格的自定义格式中，也是由基础的代码符号进行组合，常见的基础代码符号要点说明：

数字占位符，有三种：? 、# 、0

中括号 []  表示条件

逗号和千位分隔符，如用在代码最后，表示将数字缩小1000倍

分号，隔开条件，末尾可省略

基础占位符号解说：

①“G/通用格式”：将数据以常规的数字显示；

例：选中单元格或数据区域，设置自定义格式通用代码：G/通用格式，则数据“520.13”，显示为“520.13”；

②“#”：数字占位符，表示1个符号只代表1个数字占位，且只显有意义的数字零，而不显示无意义的零，若小数点后数字是大于”#”的数量，则按”#”的位数四舍五入；

例：选中单元格或数据区域，设置自定义格式通用代码：保留2位小数（四舍五入）

代码：###.##，则数字“520.138”，显示为“520.14”；而数字“520.1”，显示为“520.10”；

③“0”：数字占位符，如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用数字0进行补足；

例：选中单元格或数据区域，设置自定义格式通用代码：常用语特殊格式的数据设置，邮政编码、银行卡号、电话号等等

代码：0000000，则数字“5201314”，显示为“5201314”；而数字“520”，显示为“000520”；

④”@”：文本占位符，如果只使用单个@，作用是引用原始文本，

要在输入数字数据之后自动添加文本，使用自定义格式为：”文本内容”@；

要在输入数字数据之前自动添加文本，使用自定义格式为：@”文本内容”；

@符号的位置决定了Excel输入的数字数据相对于添加文本的位置，如果使用多个@，则可以重复文本；

例：设置某个公司某个部门的快速输入设置：文本字样英文引号设置

输入通用格式代码 ；；；"腾讯"@"部"，快速在单元格内输入“技术”字样，则显示为：“腾讯技术部”；

例：设置某个公司某个部门的快速重复输入设置：因需使用多个@，可以重复文本，则自定义中需输入代码：@@@

输入通用格式代码：；；；@@@，快速在单元格内输入“技术”字样，则显示为：“技术技术技术”

⑤“*”：重复下一次字符，直到充满列宽，独特用于仿真密码保护显示，让单元格中的数字显示为连续的星号*；

例：选中单元格或数据区域，设置自定义格式通用代码：@*-，单元格文本“上野树里”显示为“上野树里-------------------”

例：选中单元格或数据区域，设置自定义格式通用代码：**;**;**;*，则单元格数字“5201314”显示为“************”；

⑥“，”：千位分隔符；

例：选中单元格或数据区域，设置自定义格式通用代码：#,###，则数字“5201314”，显示为“5,201,314”；

⑦“\”：显示下一个字符，和“”””用途相同都是显示输入的文本，且输入后会自动转变为双引号表达，用这种格式显示下一个字符，"文本"，显示双引号里面的文本；

例：选中单元格或数据区域，设置自定义格式通用代码："人民币 "#,##0,,"百万"，

输入数据：“52013145201314”，显示为：“人民币52,013,145百万”

例：选中单元格或数据区域，设置自定义格式通用代码：  \人民币 #,##0,,\百万

输入数据：“52013145201314”，显示为：“人民币52,013,145百万”

⑧“？”：数字占位符，在小数点两边为无意义的零添加空格，以便当按固定宽度时，小数点可对齐，另外还用于对不等到长数字的分数；

例：选中单元格或数据区域，设置自定义格式通用代码：??.??.???.???

输入数据：“520.1314567”,显示为:“520.13.145.67”

⑨“！”：显示“"”，由于引号是代码常用的符号，在单元格中是无法用"""来显示出来“"”，要想显示出来，须在前加入“！”；

例：选中单元格或数据区域，设置自定义格式通用代码：#!"

输入数据：“520”,显示为:“520”

例：选中单元格或数据区域，设置自定义格式通用代码：#!"!"

输入数据：“520”，显示为：“520""”；

⑩“_”:表示在数字格式中创建N个字符宽的空格。

案例1：将数值为0的单元格显示设置为横杠显示为：“-”

自定义格式代码：G/通用格式;[=0]-    或者    G/通用格式;-G/通用格式;-

单元格格式设置方法：输入数据“0”，显示为“-”；

案例2：设置单元格在输入数字1时，自动显示男；输入数字0时，自动显示女；

自定义格式代码：[=1]男;[=0]女

单元格格式设置方法：输入数据“1”，显示为“男”；输入数据“0”，显示为“女”；

案例3：输入学生成绩，自动输出显示出大于80分的成绩为优秀，大于60分为及格，0分为不及格的等级差异；

自定义格式代码：[>80]0 优秀;[>60]0 及格;0 不及格

单元格格式设置方法：输入数据“88”，显示为“88 优秀”；输入数据“50”，显示为“50 不及格”；

案例4：用箭头显示业绩上升（输入正数）或者下降（输入负数），上升显示蓝色向上的箭头，下降显示红色向下的箭头；

自定义格式代码：[蓝色]↑0.0%;[红色]↓0.0%;0.0%

单元格格式设置方法：输入数据“30%”，显示为“蓝色向上箭头+30%”；输入数据“-3%”，显示为“红色向下箭头+-3% ”；

注意：输入正数百分比，输出结果为蓝色数据+↑，输入负数百分比，输出结果为红色数据+↓。

案例5：批量添加公司前缀或类同文本，让输入数据后，自动添加显示前缀；

自定义格式代码：统一显示的文本@   

单元格格式设置方法：员工“玉木宏”的部门归属单元格，输入“运营中心”，部门文本前自动添加上“日本株式会社”的公司前缀或者工作室前缀；

案例5：工资表某列为银行账号，前面12位数字相同：622200020011，设置该列的快速的直接输入最后几位数据，自动显示前面的11位数；

自定义格式代码：622200020011@   

单元格格式设置方法：员工“玉木宏”的银行卡尾号输入“1314”，输入的数据前自动添加上“622200020011”的一致的前缀；

案例6：批量给数值数据添加上单位，但是不会影响计算的数据显示设置；

自定义格式代码：G/通用格式"万""元"

单元格格式设置方法：输入数据“9885206”，数据显示为“9885206万元”；

说明：输入员工“胡歌”的原始收入数据“9885206”，则在数据后面批量添加上单位“万元”，即为9885206万元；

案例7：经常会用到的设置收入/销售额等数据，需设置数据区域，按照“万”为单位级别显示，不带单位，保留2位小数，但是第1位小数四舍五入，第2位小数用0填充；

自定义格式代码：0!.0,"0"

单元格格式设置方法：输入数据“9885206”，数据显示为“988.50”；

说明：输入员工“胡歌”的原始收入数据“9885206”，则显示为：“988.50”，以万级别显示，保留1位小数，第2位小数用0补充；

案例8：经常会用到的设置收入/销售额等数据，需设置数据区域，按照“万”或者“万元”为单位显示，且保留1位小数；

自定义格式代码：0!.0,"万"

单元格格式设置方法：输入数据“9885206”，数据显示为“988.5万”；

说明：输入员工“胡歌”的原始收入数据“9885206”，数据以万级显示且带单位，显示为：“988.5万”，保留1位小数；

自定义格式代码：0!.0,"万元"

单元格格式设置方法：输入数据“9885206”，数据显示为“988.5万元”；

说明：输入员工“胡歌”的原始收入数据“9885206”，数据以万级显示且带单位，则显示为：“988.5万元”；

案例9：经常会用到的设置收入/销售额等数据，需设置数据区域，按照“十万”为单位级显示，且保留2位小数；

自定义格式代码：#0!.00,

单元格格式设置方法：输入数据“988526”，数据显示为“98.85”；

说明：输入员工“胡歌”的原始收入数据“988526”，按照十万为单位级别，显示为“98.85”，保留2位小数；

案例10：经常会用到的设置收入/销售额等数据，需设置数据区域，按照“百万”为单位级显示，且保留2位小数；

自定义格式代码：0.00,,

单元格格式设置方法：输入数据“9885206”，数据显示为“9.89”；

说明：输入员工“胡歌”的原始收入数据“9885206”，按照百万为单位级别，显示为“9.89”，保留2位小数；

案例11：经常会用到的设置日期格式数据，需设置数据区域，按照“日期+星期几”显示；

自定义格式代码：yyyy/mm/dd aaaa

单元格格式设置方法：输入数据“2018/4/10”，数据显示为“2018/4/10  星期二”；

说明：输入员工“赵丽颖”的入职日期为“2018/4/10”，日期+星期的显示格式，显示为“2018/4/10  星期二”；

案例12：经常会用到的设置日期格式数据，需设置数据区域，按照“m月份”显示；

自定义格式代码：m月份

单元格格式设置方法：输入数据“2018/4/10”，数据显示为“4月份”；

说明：输入员工“赵丽颖”的入职日期为“2018/4/10”，m月份的显示格式，显示为“4月份”；

案例13：需计算两个跨天的时间之间间隔几小时几分，也可以使用自定义格式完成；

自定义格式代码：[H]小时m分

单元格格式设置方法：输入数据“2017/3/12”和“2018/4/10”，时间差数据显示为“9456小时0分”；

说明：输入员工“赵丽颖”的入职日期为：“2017/3/12”；离职日期为：“2018/4/10”，在职时长显示为“9456小时0分”；

案例14：显示日期的格式设置基础说明

代码：“YYYY”或“YY”：按四位（1900~9999）或两位（00~99）显示年

代码：“MM”或“M”：以两位（01~12）或一位（1~12）表示月。

代码：“DD”或“D”：以两位（01~31）或一位（1-31）来表示天。

例：通用时间代码：YYYY-MM-DD，时间数据“2018年4月10日”显示为：“2018-04-10 ”；

面对单元格格式,不要相信你的眼睛系列到此收尾，但数据分析之Excel的内容还有很多，如果大家有兴趣，欢迎留言补充，谢谢，您的打赏是我最大的赞赏!

更新时间：2018年4月10日，数据分析工作迷惘期的清醒~