神奇的魔法—自定義格式讓單元格多樣顯示
?
作者:夏雪來源:部落窩教育發(fā)布時間:2018-07-10 14:51:13點擊:5975
小編有話說:聽說很多小伙伴對excel里的自定義格式功能很陌生,但這簡直就是神器呀,你一定要學(xué)會!學(xué)會了自定義格式,處理很多問題都可以不必再使用函數(shù)。今天小編就給大家分享一篇自定義格式教程,趕緊收藏喲!
在EXCEL軟件的默認(rèn)單元格格式中,一般是數(shù)值、日期或者文本等等。那有沒有什么方法,可以讓單元格內(nèi)的字符按照我們想要的格式來顯示呢?今天給大家介紹一個很好用的功能:自定義格式。
1.自定義格式的打開方式
選中任意單元格,點擊“開始”選項卡,在“數(shù)字組”右下角有個擴展按鈕,單擊可調(diào)出“設(shè)置單元格格式”對話框。
也可以直接按快捷鍵Ctrl+1就可以調(diào)出如下所示的對話框。點擊“數(shù)字”-“自定義”,右邊的“類型”下就是我們要填寫自定義格式代碼的地方,點擊確定后,單元格內(nèi)字符就會顯示成我們想要的樣子。
2.自定義基礎(chǔ)功能介紹
自定義格式的代碼分為四個區(qū)段,區(qū)段之間用半角分號(;)隔開,每個區(qū)段的代碼作用于不同類型的數(shù)值:
正數(shù)顯示格式 ; 負(fù)數(shù)顯示格式 ; 零值顯示格式 ; 文本顯示格式
此外,利用自定義格式讓字符顯示顏色也很簡單,只要在自定義代碼中加上[紅色]等字樣就能使字符顯示對應(yīng)的顏色。顏色可以顯示以下幾種:[黑色]、[綠色]、[白色]、[藍色]、[洋紅色]、[黃色]、[藍綠色]、[紅色]。
3.自定義格式主要應(yīng)用場景
3.1百分比的增幅顯示
在如下圖所示的表格中,最后一列數(shù)據(jù)正數(shù)負(fù)數(shù)標(biāo)記不同顏色,用上下箭頭代表正負(fù)符號。
自定義格式代碼為:
[紅色]↑0%;[綠色]↓0%;0%;
解析:分號隔開的四個區(qū)域,第一個區(qū)域表示正數(shù)區(qū)域,顯示紅色;第二個是負(fù)數(shù)區(qū)域顯示綠色,0是數(shù)值占位符,顯示數(shù)值本身,后面加上%即百分比顯示;第三個區(qū)域是零值顯示格式,不填顏色則默認(rèn)為黑色;最后一個區(qū)域是文本,表格中沒有文本,則什么都不填。
3.2添加文字顯示
如下所示的表格中,想把第一列的數(shù)字設(shè)置為第二列的顯示格式。
通常大家會使用文本連接符&來連接文本,這里我們直接用自定義格式代碼設(shè)置為:第0部分
解析:0表示數(shù)字的占位符,前后加上文字即可。
如下所示,如果第一列的數(shù)字是中文數(shù)字,需要設(shè)置為按照第二列格式顯示。
則自定義格式設(shè)置為:第@部分
解析:@表示文字的占位符。
3.3數(shù)字顯示為中文
如果我們需要把如下圖所示的第1列數(shù)字顯示為第2列和第3列的中文樣式,一般會直接在輸入的時候輸入中文,當(dāng)然,自定義格式也能很方便地幫你實現(xiàn)這個功能。
中文小寫自定義格式代碼設(shè)置為: [DBNUM1]
中文大寫自定義格式代碼設(shè)置為: [DBNUM2]
3.4數(shù)字分段顯示
在工作中我們還會需要將不同長度的數(shù)值分段顯示,如下所示:
分段顯示的自定義格式代碼設(shè)置為:
0000 0000 0000
解析:使用0進行占位,加上空格,則為分段顯示。
分段顯示前面不加0的自定義格式代碼設(shè)置為:
??00 0000 0000
解析:如果我們不想讓數(shù)值最前面自動加0,同時又想讓數(shù)值對齊顯示的話,可以用?代替前面顯示的0,這里?的個數(shù)取決于前面顯示0的最大個數(shù)。
不過由于EXCEL中能完全顯示數(shù)字的為11位以內(nèi)的數(shù)字;數(shù)值位數(shù)大于11位不超過15位,EXCEL顯示為9.03525E+13這種科學(xué)計數(shù)法,可以使用上方的自定義格式代碼,使其完全顯示;超過15的數(shù)字將顯示為0,所以這里使用的方法就不適用于超過15位的數(shù)值。超過15位的數(shù)值我們只能修改格式為文本使其全部顯示。
3.5日期時間顯示
如下所示的表格中,需要將第一列的日期和時間顯示為第二列的格式。
上方表格中自定義格式代碼設(shè)置分別為:
第一個:[DBNum1]yyyy"年"m"月"d"日" aaaa
第二個:d/mmm/yy,dddd
第三個:今天是aaaa
第四個:上午/下午 h"點"m"分"s"秒"
第五個:h:m:s am/pm
第六個:[m]分s秒
解析:這里面的英文字母分別是年、月、日、小時、分鐘、秒鐘的英文首字母。其中字母的個數(shù)則決定了顯示格式的字符數(shù)。
在日期格式里,yy表示18,而yyyy則表示2018;m和mm表示以數(shù)值顯示的月份7和07,mmm和mmmm表示英文顯示的月份Jul(簡寫)和July(全稱);同理d和dd表示以數(shù)值顯示的日期8和08,ddd和dddd表示英文顯示的星期Sun(簡寫)和Sunday(全稱)。
而時間格式里,m如果需要表示分鐘,就需要跟在h小時后面一起顯示,或者加上[ ] 表示從當(dāng)天零點開始累積到現(xiàn)在的分鐘數(shù), 意味著把小時也換算成分鐘。
若需要表示中文星期幾,則需要用字母a, aaaa顯示的是星期日,而aaa顯示的只有日。
4.注意的問題
自定義格式需要注意的是它并不會改變單元格本身的格式,只改變它的顯示方式。
如下所示的表格中,C列的大寫中文數(shù)字是由A列數(shù)字設(shè)置自定義格式代碼轉(zhuǎn)換而來,我們來試一試C列的中文數(shù)字是否可以求和呢?
在D列輸入公式:
SUM($C$30:C30),雙擊向下填充,可以看到累積求和結(jié)果如下。
由此可得,雖然C列單元格顯示為中文大寫,但實際仍是數(shù)值,可以進行數(shù)學(xué)運算。
當(dāng)然如果需要讓自定義格式轉(zhuǎn)換為實際的數(shù)值格式,我們可以使用剪貼板復(fù)制,然后將剪貼板里的數(shù)據(jù)復(fù)制到單元格。
或者復(fù)制到word或者txt文檔里再粘貼回單元格也可以完成格式的轉(zhuǎn)換。
好了,EXCEL的自定義格式就全部介紹完啦。如果大家感興趣的話,可以留言,下次給大家介紹自定義格式的高階用法條件判斷的顯示方式。
本文配套的練習(xí)課件請加入QQ群:316492581下載。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
相關(guān)推薦:
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!