数据分析之“Excel单元格格式”(全)

文|仟樱雪

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

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

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

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

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

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

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

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

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


一、快捷篇——快速启动设置“单元格格式”

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

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

1、设置单元格格式的快捷键组合:“Ctrl”+“1”;

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

2、鼠标右键,单击操作,打开单元格格式设置对话框;

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

3、“开始”菜单栏选择相应功能区, 打开单元格格式设置对话框;

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

4、“数字”区域右下角的神秘“三角”下拉标识;

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


二、日常篇——常见单元格格式设置

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

注意:“会计专用”格式和“货币”格式的区别;货币格式是右对齐,会计格式是两端对齐(货币的符号和数据最后一位数在两端各自对齐)。

1、“数值”格式

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

负数显示在括号内且红色标记,保留2位小数:显示为红色的(520.13);

负数显示在括号内且黑色标记,保留2位小数:显示为黑色的(520.13);

负数显示红色标记且去掉负号,保留2位小数: 显示为红色的  520.13;

负数显示黑色标记保留负号,保留2位小数:显示为黑色的  -520.13;

负数显示红色标记保留负号,保留2位小数:显示为红色的  -520.13;

2、“货币”格式

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

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

3、“日期”格式

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

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

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

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

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

4、“时间”格式

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

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

5、“特殊”格式

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

说明:

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

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

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


三、干货篇——自定义单元格格式设置

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

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

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

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

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

注意:

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

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

1、自定义格式设置的基础代码

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

数字占位符,有三种:? 、# 、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个字符宽的空格。

2、自定义格式设置的经典案例实战

案例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日,数据分析工作迷惘期的清醒~

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,511评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,495评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,595评论 0 225
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,558评论 0 190
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,715评论 3 270
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,672评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,112评论 2 291
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,837评论 0 181
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,417评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,928评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,316评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,773评论 2 234
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,253评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,827评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,440评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,523评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,583评论 2 249

推荐阅读更多精彩内容

  • excel单元格自定义格式1 代码结构组成代码码分为四个部分,中间用“;”号分隔,具体如下:正数格式;负数格式;零...
    木先生is澜阅读 6,680评论 0 3
  • 文|仟樱雪 BIG DATA不仅是口号,如今90%的基础数据分析岗位都会准备Excel的机试,每每挂掉无数人, 作...
    仟樱雪阅读 7,504评论 0 10
  • 翻译自“Collection View Programming Guide for iOS” 0 关于iOS集合视...
    lakerszhy阅读 3,713评论 1 22
  • 刚才接触过了Object类,可以发现Object类能够接收所有的引用数据类型(对象、数组、接口),于是这里面又出现...
    河神_6961阅读 262评论 0 0
  • 女儿梦 有个女儿梦,多年来在我的心灵种下 女儿梦啊摇啊摇摇进男人寂寞的心灵里俗语云:女儿是父亲前世的情人女儿啊,多...
    南溪向南北歌流海阅读 234评论 0 0