怎么用條件格式做數(shù)據(jù)的柱狀對比圖,用顏色凸顯數(shù)據(jù)?
?
作者:小可來源:部落窩教育發(fā)布時間:2021-05-31 11:38:26點(diǎn)擊:4744
編按:
說到Excel中的條件格式,很多小伙伴都很喜愛,因?yàn)椴还苣闶荅xcel新手還是大神,這都是一個可以發(fā)揮很大作用,讓你一直使用的Excel功能。比如,不用任何Excel圖表功能,用它就能做柱狀對比圖;比如,不用公式,用它就能對你想要特殊標(biāo)注的數(shù)據(jù)進(jìn)行特殊的顏色凸顯設(shè)置……所以,條件格式用得好,工作輕松下班早!
嗨嘍嗨嘍大家好呀!提到條件格式,大家會覺得它真的很香。因?yàn)樗粌H能快速突出顯示我們想要的數(shù)據(jù),還能更直觀地顯示和比較數(shù)據(jù)……數(shù)據(jù)條件用得好,工作輕松下班早?。?/span>
一、直觀比較兩列數(shù)據(jù)
數(shù)據(jù)條的作用是能更直觀的比較各個數(shù)據(jù)的大小,正如初中學(xué)習(xí)的條形統(tǒng)計(jì)圖的作用一樣。下面,分別為B列與C列添加數(shù)據(jù)條!但是,如(圖一),B列和C列數(shù)據(jù)在相同的單位長度下所代表的值是不同的,B列的最大值是“380”,則“380”所在的單元格(一個單元格長度)被數(shù)據(jù)條填滿;同理,C列的最大值“390”所在的單元格(一個單元格長度)被數(shù)據(jù)條填滿。所以它們創(chuàng)建的數(shù)據(jù)條長度是不具有數(shù)據(jù)大小的可比性的。
那么,怎么能做到讓“一季度銷量”與“二季度銷量”的兩列數(shù)據(jù)具有可比性呢?
先來看看最終效果圖HHH
其實(shí)解決該問題只要將兩列數(shù)據(jù)條的最大值和最小值設(shè)置的相同就OK啦!如下圖(二)!
第一步:給數(shù)據(jù)格式做一些微調(diào)整。
用鼠標(biāo)選中B列和C列,單擊鼠標(biāo)右鍵后設(shè)置兩列列寬為一樣(筆者在這里設(shè)置為“25”),然后把B列數(shù)據(jù)右對齊,把C列數(shù)據(jù)左對齊。
第二步:打開條件格式界面。
用鼠標(biāo)選中B列數(shù)據(jù),找到【開始】選項(xiàng)卡下的【條件格式】,點(diǎn)擊下拉小三角,選擇【新建規(guī)則】。
第三步:設(shè)置條件格式。
在彈出的【新建格式規(guī)則】對話框中,選擇【選擇規(guī)則類型】下的【基于各自值設(shè)置所有單元格的格式】,在【編輯規(guī)則說明】下選擇“數(shù)據(jù)條”,在“最大值”和“最小值”中選擇“數(shù)字”選項(xiàng),在“值”的輸入框中輸入合適(根據(jù)實(shí)際數(shù)據(jù)情況)的最大值和最小值,筆者在這里輸入“100”和“400”。
此外,在“條形圖外觀”設(shè)置數(shù)據(jù)條顏色和外框線等,最后“條形圖方向”選擇“從右到左”,點(diǎn)擊【確定】按鈕。
第四步:選中C列數(shù)據(jù),設(shè)置條件格式。
按照第二步打開條件格式的【新建格式規(guī)則】對話框,除了“條形圖方向”選擇“從左到右”外,各項(xiàng)選擇與第三步的選項(xiàng)一樣。
二、設(shè)置負(fù)值與坐標(biāo)軸
如(圖一),C列的數(shù)據(jù)“與上月業(yè)績比較”比上月多的數(shù)據(jù)為正值,比上月少的為負(fù)值?,F(xiàn)在老板讓我們把C列數(shù)據(jù)做成有坐標(biāo)軸的數(shù)據(jù)條,老板的理想數(shù)據(jù)圖如(圖二),其實(shí)達(dá)到老板的要求很簡單,一起來學(xué)習(xí)吧~
第一步:設(shè)置條件格式。
打開條件格式的【新建格式規(guī)則】對話框,選擇【選擇規(guī)則類型】下的【基于各自值設(shè)置所有單元格的格式】,在【編輯規(guī)則說明】下選擇“數(shù)據(jù)條”(其他的數(shù)據(jù)條顏色或者線框?qū)傩钥筛鶕?jù)個人喜好設(shè)置),點(diǎn)擊對話框左下角的“負(fù)值和坐標(biāo)軸”。
第二步:屬性值設(shè)置。
在彈出的【負(fù)值和坐標(biāo)軸設(shè)置】對話框,選擇“坐標(biāo)軸設(shè)置”下的“單元格中點(diǎn)值”,再點(diǎn)擊【確定】按鈕。
擴(kuò)展小知識:
在上圖中,“坐標(biāo)軸設(shè)置”下的“自動(基于負(fù)值顯示在可變位置)”,具體指什么呢?與“單元格中點(diǎn)值”有啥區(qū)別呢?來吧,筆者很高興能與各位伙伴分享知識!
①“自動(基于負(fù)值顯示在可變位置)”
該選項(xiàng)的數(shù)據(jù)條是以0值為坐標(biāo)原點(diǎn)建立坐標(biāo)軸,正負(fù)值數(shù)據(jù)條以分界線為軸且方向相反;絕對值越大,數(shù)據(jù)條越長;所以如果負(fù)值變化了,坐標(biāo)軸的位置就會跟著變動。如下動圖,在C7單元格分別輸入“-100”、“-1000”、“-10000”,坐標(biāo)軸(分界線)的位置就會跟著改變,當(dāng)輸入“-10000”時,改變的就十分明顯。
②“單元格中點(diǎn)值”
同上,在C7單元格分別輸入“-100”、“-1000”、“-10000”,而坐標(biāo)軸(分界線)的位置不發(fā)生改變。
聰明的你一定懂了以上兩種坐標(biāo)軸設(shè)置的區(qū)別了吧~~~
三、標(biāo)識滿足條件的數(shù)據(jù)
圖下是一個班級的成績表,老師想把至少有兩科成績低于及格分60分的那一行的所有同學(xué)成績設(shè)置條件格式,用粉紅色底紋填充,該如何操作呢?
第一步:選中要設(shè)置條件格式的區(qū)域A2:G12,打開條件格式的【新建格式規(guī)則】,選擇【使用公式確定要設(shè)置的單元格】,在公式輸入框里輸入公式
=COUNTIF($B2:$G2,"<60")>1(英文狀態(tài)下的輸入法),輸入后點(diǎn)擊【格式(F)】。
第二步:選擇【設(shè)置單元格格式】下的【填充】選項(xiàng)卡,選擇你喜歡的顏色,筆者這里填充橙不橙粉不粉叫不上名字的顏色哈^0^,最后點(diǎn)擊【確定】按鈕。
設(shè)置完后,符合條件的“小李子”同學(xué)的整行成績就被填充顏色了~
同樣的成績表,筆者把它復(fù)制粘貼到A16:G27區(qū)域,但想把至少有兩科成績低于及格分60分同學(xué)的不及格分?jǐn)?shù)設(shè)置條件格式,單元格用顏色填充,又該如何操作呢?
其實(shí)這與上面的操作步驟相同,不過公式框公式輸入為
=(COUNTIF($B17:$G17,"<60")>1)*(B17<60)
或者
=AND(COUNTIF($B17:$G17,"<60")>1,B17<60)
Tip:“*”相當(dāng)于函數(shù)AND
完成設(shè)置后效果如下圖~
不同公式的詳細(xì)解析:
筆者盲猜很多人不理解為啥換個公式,設(shè)置出來的條件格式的結(jié)果差別就這么大呢?!嘻嘻,其實(shí)這與單元格引用在條件格式中的應(yīng)用和規(guī)律有關(guān),感興趣的伙伴強(qiáng)烈推薦看看阿碩小編的《自定義條件格式中的相對引用與絕對引用》,你一定會收獲滿滿噠CCC
小編把兩個效果圖和公式放在一張圖里面,更方便大家理解思考哈~~
由于篇幅有限,條件格式姑且分享到這里啦,希望能給小伙伴們帶來收獲哦~
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
2個實(shí)例讓你學(xué)會帶函數(shù)公式的條件格式
excel表格填充技巧視頻:橫向填充序號條件格式填充底紋顏色
版權(quán)申明:
本文作者小可;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請收好!