新同事用條件格式制作的圖表,竟然比我的還好看?
?
作者:E圖表述來源:部落窩教育發(fā)布時間:2020-01-06 17:45:01點擊:3080
編按:
哈嘍,大家好!在平時的工作中,我們會制作各種各樣的圖表,但大都離不開這三個步驟:選中數(shù)據(jù)--插入圖表--選擇圖表的樣式。通過這三個步驟制作出的圖表都是浮于單元格上方的,而其實單元格本身,也是可以用來制作圖表的,且樣式美觀,我們稱它為“像素圖表”。下面就跟著作者E圖表述的步伐,感受一下“像素圖表”的魅力吧!
【前言】
圖表是即簡單又復(fù)雜的一個模塊,說它簡單,是因為只要有作圖數(shù)據(jù)源,只需在工具欄中點擊插入,再選擇圖表類型,EXCEL就可以給我們制作出一張圖表,相當方便;說它復(fù)雜,是因為如果要做出一張“賞心悅目”的圖表,就必須對數(shù)據(jù)源的內(nèi)容進行分析,挖掘出有意義的數(shù)據(jù),結(jié)合預(yù)計的圖表類型,用函數(shù)或者VBA制作出能夠被此圖表類型使用的數(shù)據(jù)源,然后再進行配色、排版等美化操作。
很多人都喜歡設(shè)計圖表,作者也不例外,每一次的設(shè)計過程都是一次創(chuàng)作的過程,預(yù)算的結(jié)果可能一樣,但是每次設(shè)計的圖表格式卻不盡相同,這是一個美妙的過程。
之前作者E圖表述也給大家分享過一些創(chuàng)意圖表的做法,今天呢,我們另辟蹊徑,帶大家看看EXCEL中的另一種圖表的形式,作者稱它為“像素圖表”。
【正文】
什么是像素圖表呢?其實就是一種叫法,一個一個的單元格是EXCEL軟件中最小的操作對象,我們可以把它看作是EXCEL展示中的一個“像素”?!跋袼貓D表”就是在單元格中才會有的一種圖表,它的制作大致可以分為如下四種方式:
一、迷你圖
假如我們有如下圖這樣的數(shù)據(jù)源:
你會選擇什么類型的圖表去展示呢?其實無論你用什么圖表,要處理上述的數(shù)據(jù)源,估計都會是一個勞心費神的過程。我們看下圖,如果用“迷你圖”來操作的話,會是什么效果:
省時省力,整齊美觀,既避免了繁雜的函數(shù)過程,又做出了一張精美的數(shù)據(jù)匯報。我們下面就來看看這個圖是如何制作的,順便來了解一下 “微圖表”的功能。
【制作過程】
步驟1:
選中H3單元格,在工具欄中,點擊插入——“迷你圖”——柱形圖,此時會彈出“創(chuàng)建迷你圖”的設(shè)置窗口,我們可以在“數(shù)據(jù)范圍”中選擇數(shù)據(jù)所在的單元格區(qū)域地址,在“位置范圍”中輸入迷你圖的放置單元格地址。如下圖所示:
點擊“確定”按鈕,就得到了C3:G3區(qū)域的一個迷你柱形圖的圖表展示。
步驟2:
選中H3單元格,此時我們可以在工具欄上看到“迷你圖的設(shè)計工具欄”,在這里可以對我們剛插入的迷你圖進行“格式”上的調(diào)整。
在“顯示”欄中選擇“高點”“低點”,“樣式”欄中選擇一個中意的配色,作者案例中的設(shè)置如下:
步驟3:
設(shè)置格式之后,復(fù)制H3單元格,再選擇H4:H14單元格區(qū)域進行“粘貼”,就完成了迷你柱形圖的制作。
按照同樣的方式,在不給出步驟的情況下,大家能不能完成C15:G15區(qū)域的“迷你折線圖”呢?可以加入“部落窩”的QQ群,下載模擬練習(xí)課件試一試。
【注意】設(shè)置“顯示”欄中高低點顏色的功能在這里喲!
二、數(shù)據(jù)條、色階、圖標集
這是第二種制作“像素圖表”的方式,下面是我們模擬的數(shù)據(jù)源:
如果我們使用圖表的做法,可能需要一個組合圖,還要涉及主坐標和次坐標的參數(shù)設(shè)置?,F(xiàn)在我們換一種方式,比如像下圖這樣,是不是也能清楚地展示數(shù)據(jù)。
從這個圖中,我們不僅可以看出“計劃數(shù)量和完成數(shù)量的對比”,還可以看出“每個月完成率的比較”,更重要的是,制作這樣的一個圖,要比圖表的操作“簡單得多”。
【制作過程】
步驟1:
選中C3:C14單元格區(qū)域,在工具欄——“開始”選項卡中,選擇“條件格式”——“數(shù)據(jù)條”,再選擇其中一種格式。
步驟2:
再次選擇工具欄中的條件格式——“管理規(guī)則”,彈出如下窗口。
點擊“編輯規(guī)則”,進入“編輯格式規(guī)則”的窗口,按照下圖進行設(shè)置:
這樣就得到了,我們模擬圖中“計劃數(shù)量”的效果,同理制作“完成數(shù)量”的“數(shù)據(jù)條”,圖形方向默認即可。
步驟3:
選中E3:E14單元格區(qū)域,在“條件格式”中,選擇“色階”中的某一個格式,及此完成圖表的制作。
圖標集和色階的用法基本一樣,我們就不過多闡述了,有興趣的同學(xué)可以用數(shù)據(jù)操作一下,一定會有很多的心得。
三、條件格式圖
既然講到了用條件格式制作“圖表”的方法,那么我們就可以發(fā)散思維,看看利用條件格式還可以怎么制作圖表。數(shù)據(jù)源如下圖所示:
我們需要通過圖表的形式展示這幾個數(shù)據(jù)占總數(shù)據(jù)的百分比,當然我們可以利用“堆積柱形圖”來做,效果如下所示:
但如果我們使用條件格式來處理這類問題,呈現(xiàn)的方式就會顯得比較“高級”。如下圖所示,我們用100個單元格來標記占比的圖表。
【制作過程】
步驟1:
選中F:O列共10列的區(qū)域,調(diào)整列寬為2。在EXCEL中,列寬為2,行高為14.25,可以組成一個正方形的單元格;
步驟2:
在D3單元格輸入函數(shù)=C3/SUM($C$3:$C$7),用于計算第一個數(shù)據(jù)占總費用的比例,下拉得到每個數(shù)據(jù)的占比情況;
在E3單元格輸入函數(shù)=SUM($D$3:D3),計算數(shù)據(jù)的累積占比,得到下圖的數(shù)據(jù)。
步驟3:
制作條件格式,在工具欄中點開“條件格式”中的“管理規(guī)則”選項。
因為我們有五個內(nèi)容,所以需要設(shè)置五個內(nèi)容的條件格式。
選擇“使用公式確定要設(shè)置格式的單元格”的方式,設(shè)置條件格式的規(guī)則類型。
這里要“吐槽”一下,在條件格式的函數(shù)錄入窗格中,因為窗格的顯示有限,不能夠看清整體的函數(shù)錄入,十分的不便,所以這里給大家分享一個“小竅門”。
【函數(shù)條件格式錄入的小竅門】
隨便找?guī)讉€空白單元格,輸入公式后再粘貼到條件格式的錄入框中。
選擇F12:O21單元格區(qū)域,在條件格式中分別設(shè)置以下五個函數(shù)條件,別忘了設(shè)置需要顯示的格式。
在R7單元格輸入函數(shù):
=AND(((ROW()-12)*10+(COLUMN()-5))>=0*100,((ROW()-12)*10+(COLUMN()-5))<=$E$3*100)
在R8單元格輸入函數(shù):
=AND(((ROW()-12)*10+(COLUMN()-5))>=$E$3*100,((ROW()-12)*10+(COLUMN()-5))<=$E$4*100)
在R9單元格輸入函數(shù):
=AND(((ROW()-12)*10+(COLUMN()-5))>=$E$4*100,((ROW()-12)*10+(COLUMN()-5))<=$E$5*100)
在R10單元格輸入函數(shù):
=AND(((ROW()-12)*10+(COLUMN()-5))>=$E$5*100,((ROW()-12)*10+(COLUMN()-5))<=$E$6*100)
在R11單元格輸入函數(shù):
=AND(((ROW()-12)*10+(COLUMN()-5))>=$E$6*100,((ROW()-12)*10+(COLUMN()-5))<=$E$7*100)
大家應(yīng)該不難看出,我們的核心函數(shù)就是(ROW()-12)*10+(COLUMN()-5),函數(shù)解析如下:
因為我們設(shè)置條件格式區(qū)域的初始單元格是F12,所以確定了12(行數(shù))和5(左邊的空列)這兩個數(shù)字,我們用下面的圖示說明這些值的意義:
再用這個序號和我們E3:E7單元格區(qū)域中的值對應(yīng),值在范圍內(nèi)就顯示設(shè)置的填充顏色,如此一來就形成了我們上面的示例的效果。這里再囑咐一下同學(xué)們,不要只是看或者參悟教程的意義,一定要動手做一遍,不動手是永遠不會的!!
四、函數(shù)作圖法
這篇文章主要是講解在單元格中的作圖方式,眾所周知,函數(shù)就是在單元格中起作用的,那么有沒有可以做圖表的函數(shù)呢?我們一起來“見證奇跡”!
一張由函數(shù)制作的“膠卷圖”,我們一起來看看它的做法。
【操作步驟】
區(qū)域模塊的劃分,按下表設(shè)置各個行高:
由于篇幅原因,我們對于整體的排版就不做詳細介紹了,同學(xué)們可以通過“部落窩”官方QQ群,下載此教程的素材。我們主要來看看,制作這張“膠卷圖”使用了什么函數(shù)。
在D5單元格中輸入函數(shù):
=REPT(UNICHAR(9611),D10/50)
函數(shù)解析:
這么復(fù)雜的一個圖,函數(shù)部分居然 “如此簡單”,但是這么簡潔的函數(shù),卻不是常用的函數(shù)。
REPT函數(shù):根據(jù)指定次數(shù)重復(fù)文本。
白話語法:REPT(文本,重復(fù)的次數(shù))。
UNICHAR函數(shù):返回由給定數(shù)值引用的UNICODE字符。
這里的9611是什么字符呢?就是“▋”這個啦。接著用銷售額除以某一個常數(shù),把REPT函數(shù)的第二參數(shù)(重復(fù)的次數(shù))縮減一些(否則這個圖會很長,不好排版也不好看),并按照下圖設(shè)置單元格的“對齊”格式,將字體調(diào)整為斜體,整體的效果就出來了。
【編后語】
今天的文章,同學(xué)們有沒有感覺到“單元格圖表”的魅力呢?反正作者E圖表述是覺得這一點也不亞于“正式圖表”的效果,反而有點小“另類”。當我們對EXCEL有了更多的思考、更多的創(chuàng)意用法,你就會對這個軟件有更多的認識,有更多的喜愛。
本文配套的練習(xí)課件請加入QQ群:747953401下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
漸變色曲線圖《年終報告,你需要這張Excel圖表來“撐場面”》
俄羅斯方塊圖《被領(lǐng)導(dǎo)贊賞的俄羅斯方塊圖,你會做嗎?》
用excel制作紅綠燈提醒的效果《紅燈錯,綠燈對,再也不怕數(shù)據(jù)錄錯了!》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的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單元格中的算式,四種求和方法請收好!