文|仟櫻雪

數據分析工具與時俱進么翰，Python、R語言暴心、SQL妓盲、Excel等等，但Excel作為基礎軟件专普，仍舊是基本的職場法寶悯衬，鑒于90%的基礎數據分析崗位都會準備Excel的機試，而對于單元格的數據格式設置檀夹，卻是“數據美化”的又一個神技筋粗。

久經職場的老鳥們，甚至簡單的認為單元格格式的“美化”炸渡，就是簡單的數據顯示娜亿，殊不知是小巫見大巫，自欺欺人蚌堵。

縱使該技能是“一葉障目”买决，但是對于眾多需要救場的職場小白來說，確實是不可多得的妙招吼畏。

單元格格式設置：將數據依據某些條件督赤，進行快速的設置顯示，例如：數值宫仗、日期够挂、文本等數據單元格或者區(qū)域，設置成想要顯示的格式藕夫。

核心要點：輸入值的數據區(qū)域VS設置格式后顯示的數據區(qū)域孽糖，本質上是一致的，僅僅是因為自定義格式的代碼而造成的“不要輕易相信你的眼睛”的錯覺毅贮。

Excel單元格格式設置的使用办悟，是辦公必備技能，快捷化數據分析的一大捷徑滩褥，主要的精髓如下：

神技1：快捷篇——快速啟動設置單元格格式

神技2：日常篇——常見單元格格式設置

神技3：干貨篇——自定義單元格格式設置

一病蛉、快捷篇——快速啟動設置“單元格格式”

Excel提供了大量的數據格式類型：常規(guī)、數值、貨幣铺然、會計專用俗孝、日期、時間魄健、百分比赋铝、分數、科學記數沽瘦、文本革骨、特殊和自定義等等，如果不做設置析恋，輸入數據時則默認為“常規(guī)”格式顯示良哲。

在選中需設置單元格格式的數據區(qū)域時，再啟動設置功能時助隧，有不同的快捷方式筑凫，常見的有4種。

1喇颁、設置單元格格式的快捷鍵組合：“Ctrl”+“1”漏健；

操作說明：選中需設置數據格式的單元格或區(qū)域，按下“Ctrl”鍵+數字鍵“1”橘霎，則彈出設置單元格格式的對話框蔫浆。

2、鼠標右鍵姐叁，單擊操作瓦盛，打開單元格格式設置對話框；

操作說明：選中需要設置數據格式的單元格或區(qū)域外潜，選擇右鍵菜單中的“設置單元格格式”原环，則彈出設置單元格格式的對話框。

3处窥、“開始”菜單欄選擇相應功能區(qū)嘱吗， 打開單元格格式設置對話框；

操作說明：選中需要設置數據格式的單元格或區(qū)域滔驾，在Excel的功能菜單區(qū)域“開始”菜單欄的“數字”設置區(qū)域谒麦，選擇“其他數字格式”，彈出“設置單元格格式”對話框哆致，選擇相關格式進行設置绕德。

4、“數字”區(qū)域右下角的神秘“三角”下拉標識摊阀；

操作說明：選中需要設置數據格式的單元格或區(qū)域耻蛇，在Excel的功能菜單區(qū)域“開始”菜單欄的“數字”設置區(qū)域踪蹬，點擊該區(qū)域的右下角的“三角”下拉標志，則彈出“設置單元格格式”對話框臣咖，選擇相關格式進行設置跃捣。

二、日常篇——常見單元格格式設置

選中需設置格式的單元格或數據區(qū)域后亡哄，啟動單元格對話框后枝缔，常見的數據格式如下：

注意：“會計專用”格式和“貨幣”格式的區(qū)別布疙；貨幣格式是右對齊蚊惯，會計格式是兩端對齊（貨幣的符號和數據最后一位數在兩端各自對齊）。

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”顽分，直接在“開始”菜單的“數字”區(qū)域，選擇“短日期”或“長日期”的日期格式：

說明：短日期格式數據時施蜜，數據“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）對單元格或數據區(qū)域創(chuàng)建了自定義格式后袖牙，該格式會保存在Excel工作簿中侧巨，直到該格式被刪除，否則不會輕易被發(fā)現鞭达；

2）第二章中常見的數據格式設置（數值、貨幣皇忿、會計專用等）畴蹭，都可以通過自定義格式的代碼進行設置實現。

1鳍烁、自定義格式設置的基礎代碼

在單元格的自定義格式中叨襟，也是由基礎的代碼符號進行組合，常見的基礎代碼符號要點說明：

數字占位符幔荒，有三種：? 糊闽、# 、0

中括號 []? 表示條件

逗號和千位分隔符爹梁，如用在代碼最后右犹，表示將數字縮小1000倍

分號，隔開條件姚垃，末尾可省略

基礎占位符號解說：

①“G/通用格式”：將數據以常規(guī)的數字顯示念链；

例：選中單元格或數據區(qū)域，設置自定義格式通用代碼：G/通用格式积糯，則數據“520.13”掂墓，顯示為“520.13”；

②“#”：數字占位符看成，表示1個符號只代表1個數字占位君编，且只顯有意義的數字零，而不顯示無意義的零川慌，若小數點后數字是大于”#”的數量吃嘿，則按”#”的位數四舍五入偿荷；

例：選中單元格或數據區(qū)域，設置自定義格式通用代碼：保留2位小數（四舍五入）

代碼：###.##唠椭，則數字“520.138”跳纳，顯示為“520.14”；而數字“520.1”贪嫂，顯示為“520.10”寺庄；

③“0”：數字占位符，如果單元格的內容大于占位符力崇，則顯示實際數字斗塘，如果小于點位符的數量，則用數字0進行補足亮靴；

例：選中單元格或數據區(qū)域馍盟，設置自定義格式通用代碼：常用語特殊格式的數據設置，郵政編碼茧吊、銀行卡號贞岭、電話號等等

代碼：0000000，則數字“5201314”搓侄，顯示為“5201314”瞄桨；而數字“520”，顯示為“000520”讶踪；

④”@”：文本占位符芯侥，如果只使用單個@，作用是引用原始文本乳讥，

要在輸入數字數據之后自動添加文本柱查，使用自定義格式為：”文本內容”@；

要在輸入數字數據之前自動添加文本云石，使用自定義格式為：@”文本內容”唉工；

@符號的位置決定了Excel輸入的數字數據相對于添加文本的位置，如果使用多個@留晚，則可以重復文本酵紫；

例：設置某個公司某個部門的快速輸入設置：文本字樣英文引號設置

輸入通用格式代碼 ；错维；奖地；"騰訊"@"部"，快速在單元格內輸入“技術”字樣赋焕，則顯示為：“騰訊技術部”洋丐；

例：設置某個公司某個部門的快速重復輸入設置：因需使用多個@辆雾，可以重復文本褒繁，則自定義中需輸入代碼：@@@

輸入通用格式代碼：；僧界；；@@@臭挽，快速在單元格內輸入“技術”字樣捂襟，則顯示為：“技術技術技術”

⑤“*”：重復下一次字符，直到充滿列寬欢峰，獨特用于仿真密碼保護顯示葬荷，讓單元格中的數字顯示為連續(xù)的星號*；

例：選中單元格或數據區(qū)域纽帖，設置自定義格式通用代碼：@*-宠漩，單元格文本“上野樹里”顯示為“上野樹里-------------------”

例：選中單元格或數據區(qū)域，設置自定義格式通用代碼：**;**;**;*懊直，則單元格數字“5201314”顯示為“************”扒吁；

⑥“，”：千位分隔符室囊；

例：選中單元格或數據區(qū)域雕崩，設置自定義格式通用代碼：#,###，則數字“5201314”波俄，顯示為“5,201,314”晨逝；

⑦“\”：顯示下一個字符，和“”””用途相同都是顯示輸入的文本懦铺，且輸入后會自動轉變?yōu)殡p引號表達，用這種格式顯示下一個字符支鸡，"文本"冬念，顯示雙引號里面的文本；

例：選中單元格或數據區(qū)域牧挣，設置自定義格式通用代碼："人民幣 "#,##0,,"百萬"急前，

輸入數據：“52013145201314”，顯示為：“人民幣52,013,145百萬”

例：選中單元格或數據區(qū)域瀑构，設置自定義格式通用代碼：? \人民幣 #,##0,,\百萬

輸入數據：“52013145201314”裆针，顯示為：“人民幣52,013,145百萬”

⑧“？”：數字占位符寺晌，在小數點兩邊為無意義的零添加空格世吨，以便當按固定寬度時，小數點可對齊呻征，另外還用于對不等到長數字的分數耘婚；

例：選中單元格或數據區(qū)域，設置自定義格式通用代碼：??.??.???.???

輸入數據：“520.1314567”,顯示為:“520.13.145.67”

⑨“陆赋！”：顯示“"”沐祷，由于引號是代碼常用的符號嚷闭，在單元格中是無法用"""來顯示出來“"”，要想顯示出來赖临，須在前加入“胞锰！”；

例：選中單元格或數據區(qū)域兢榨，設置自定義格式通用代碼：#!"

輸入數據：“520”,顯示為:“520”

例：選中單元格或數據區(qū)域嗅榕，設置自定義格式通用代碼：#!"!"

輸入數據：“520”，顯示為：“520""”色乾；

⑩“_”:表示在數字格式中創(chuàng)建N個字符寬的空格誊册。

2、自定義格式設置的經典案例實戰(zhàn)

案例1：將數值為0的單元格顯示設置為橫杠顯示為：“-”

自定義格式代碼：G/通用格式;[=0]-? ? 或者? ? G/通用格式;-G/通用格式;-

單元格格式設置方法：輸入數據“0”暖璧，顯示為“-”案怯；

案例2：設置單元格在輸入數字1時，自動顯示男澎办；輸入數字0時嘲碱，自動顯示女；

自定義格式代碼：[=1]男;[=0]女

單元格格式設置方法：輸入數據“1”局蚀，顯示為“男”麦锯；輸入數據“0”，顯示為“女”琅绅；

案例3：輸入學生成績扶欣，自動輸出顯示出大于80分的成績?yōu)閮?yōu)秀，大于60分為及格千扶，0分為不及格的等級差異料祠；

自定義格式代碼：[>80]0 優(yōu)秀;[>60]0 及格;0 不及格

單元格格式設置方法：輸入數據“88”，顯示為“88 優(yōu)秀”澎羞；輸入數據“50”髓绽，顯示為“50 不及格”；

案例4：用箭頭顯示業(yè)績上升（輸入正數）或者下降（輸入負數）妆绞，上升顯示藍色向上的箭頭顺呕，下降顯示紅色向下的箭頭；

自定義格式代碼：[藍色]↑0.0%;[紅色]↓0.0%;0.0%

單元格格式設置方法：輸入數據“30%”括饶，顯示為“藍色向上箭頭+30%”株茶；輸入數據“-3%”，顯示為“紅色向下箭頭+-3% ”巷帝；

注意：輸入正數百分比忌卤，輸出結果為藍色數據+↑，輸入負數百分比楞泼，輸出結果為紅色數據+↓驰徊。

案例5：批量添加公司前綴或類同文本笤闯，讓輸入數據后，自動添加顯示前綴棍厂；

自定義格式代碼：統(tǒng)一顯示的文本@? ?

單元格格式設置方法：員工“玉木宏”的部門歸屬單元格颗味，輸入“運營中心”，部門文本前自動添加上“日本株式會社”的公司前綴或者工作室前綴牺弹；

案例5：工資表某列為銀行賬號浦马，前面12位數字相同：622200020011，設置該列的快速的直接輸入最后幾位數據张漂，自動顯示前面的11位數晶默；

自定義格式代碼：622200020011@? ?

單元格格式設置方法：員工“玉木宏”的銀行卡尾號輸入“1314”，輸入的數據前自動添加上“622200020011”的一致的前綴航攒；

案例6：批量給數值數據添加上單位磺陡，但是不會影響計算的數據顯示設置；

自定義格式代碼：G/通用格式"萬""元"

單元格格式設置方法：輸入數據“9885206”漠畜，數據顯示為“9885206萬元”币他；

說明：輸入員工“胡歌”的原始收入數據“9885206”，則在數據后面批量添加上單位“萬元”憔狞，即為9885206萬元蝴悉；

案例7：經常會用到的設置收入/銷售額等數據，需設置數據區(qū)域瘾敢，按照“萬”為單位級別顯示拍冠，不帶單位，保留2位小數簇抵，但是第1位小數四舍五入倦微，第2位小數用0填充；

自定義格式代碼：0!.0,"0"

單元格格式設置方法：輸入數據“9885206”正压，數據顯示為“988.50”；

說明：輸入員工“胡歌”的原始收入數據“9885206”责球，則顯示為：“988.50”焦履，以萬級別顯示，保留1位小數雏逾，第2位小數用0補充嘉裤；

案例8：經常會用到的設置收入/銷售額等數據，需設置數據區(qū)域栖博，按照“萬”或者“萬元”為單位顯示屑宠，且保留1位小數；

自定義格式代碼：0!.0,"萬"

單元格格式設置方法：輸入數據“9885206”仇让，數據顯示為“988.5萬”典奉；

說明：輸入員工“胡歌”的原始收入數據“9885206”躺翻，數據以萬級顯示且?guī)挝唬@示為：“988.5萬”卫玖，保留1位小數公你；

自定義格式代碼：0!.0,"萬元"

單元格格式設置方法：輸入數據“9885206”，數據顯示為“988.5萬元”假瞬；

說明：輸入員工“胡歌”的原始收入數據“9885206”陕靠，數據以萬級顯示且?guī)挝唬瑒t顯示為：“988.5萬元”脱茉；

案例9：經常會用到的設置收入/銷售額等數據剪芥，需設置數據區(qū)域，按照“十萬”為單位級顯示琴许，且保留2位小數税肪；

自定義格式代碼：#0!.00,

單元格格式設置方法：輸入數據“988526”，數據顯示為“98.85”虚吟；

說明：輸入員工“胡歌”的原始收入數據“988526”寸认，按照十萬為單位級別，顯示為“98.85”串慰，保留2位小數偏塞；

案例10：經常會用到的設置收入/銷售額等數據，需設置數據區(qū)域邦鲫，按照“百萬”為單位級顯示灸叼，且保留2位小數；

自定義格式代碼：0.00,,

單元格格式設置方法：輸入數據“9885206”庆捺，數據顯示為“9.89”古今；

說明：輸入員工“胡歌”的原始收入數據“9885206”，按照百萬為單位級別滔以，顯示為“9.89”捉腥，保留2位小數；

案例11：經常會用到的設置日期格式數據你画，需設置數據區(qū)域抵碟，按照“日期+星期幾”顯示；

自定義格式代碼：yyyy/mm/dd aaaa

單元格格式設置方法：輸入數據“2018/4/10”坏匪，數據顯示為“2018/4/10? 星期二”拟逮；

說明：輸入員工“趙麗穎”的入職日期為“2018/4/10”，日期+星期的顯示格式适滓，顯示為“2018/4/10? 星期二”敦迄；

案例12：經常會用到的設置日期格式數據，需設置數據區(qū)域，按照“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日诺苹，數據分析工作迷惘期的清醒~