Excel課件130811上午Exce公式與函數(shù)數(shù)據(jù)透視表+下午專業(yè)圖表設(shè)計(jì)

1、目錄一、課堂練習(xí)答案：2二、數(shù)據(jù)處理公式與函數(shù)6lRANK函數(shù)6lFIND函數(shù)6lHYPERLINK函數(shù)8l數(shù)據(jù)查找：9三、高效數(shù)據(jù)分析17l數(shù)據(jù)排序：17l數(shù)據(jù)篩選：23l高級(jí)篩選：25l分類匯總28四、數(shù)據(jù)透視表：33五、專業(yè)圖表設(shè)計(jì)：42六、測(cè)試題答案：59一、課堂練習(xí)答案：上次留的作業(yè)是計(jì)算身份證號(hào)的年齡及性別，其中有15位身份證號(hào)。有18位身份證號(hào)，從今年1月1日起淘汰15位身份證號(hào)碼，但是有些系統(tǒng)還只認(rèn)15位身份證號(hào)碼。l 18位身份證號(hào)碼年齡的函數(shù)解法1：=datedif（date（mid（A2,7,4）,mid（A2,11,2），mid（A2,13,2），today（），“Y”

2、），回車得出結(jié)果。1）2）l 15位身份證號(hào)碼年齡函數(shù)在18身份證的函數(shù)公式下，計(jì)算年月日的數(shù)字?jǐn)?shù)減去2。即：=datedif（date（mid（A2,5,4）,mid（A2,9,2），mid（A2,11,2），today（），“Y”）l 18位身份證號(hào)碼年齡的函數(shù)解法2：=datedif（text（mid（A2,7,8）,“0000-00-00”），today（），“Y”）,回車得出結(jié)果。3）2種解法匯總：l 18位身份證號(hào)碼性別解法1：=if（iseven（if（len（A2）=18,mid（A2,17,1）,right（A2,1），“女”,“男”）1）2）3）l 18位身份證號(hào)碼性別解法

3、2：=if(iseven(mid(A2,15,3),“女”,“男”)1）2）匯總：二、數(shù)據(jù)處理公式與函數(shù)l RANK函數(shù)：如圖：如果想知道某個(gè)人銷售額有多少，因一部-3部有合并單元格，所以無法排序，用RANK函數(shù)，選定需要排名的區(qū)域，圖示：G2-G10，即函數(shù)為：=RANK(GA,$G$2:$G$10)，如要升序，在RANK函數(shù)中加個(gè)參數(shù)，=RANK(GA,$G$2:$G$10,1)*默認(rèn)的為降序，1為升序。 l FIND函數(shù)： FIND函數(shù)的認(rèn)識(shí)：中華人民共和國(guó)在單元格里排第幾位（如中華人民共和國(guó)在D2），=find（“共”,D2） 如圖：在I2里做一個(gè)有效性，在序列里添加嘉興，寧波，大連，

4、上海，四川。在I2下拉選擇的地名上，如：嘉興，在選定的包含有嘉興的文字的單元格背景上全部表出桔黃顏色。即數(shù)據(jù)庫中快速的標(biāo)出需要查找的數(shù)據(jù)并標(biāo)出顏色。解法：選擇全部區(qū)域標(biāo)出顏色應(yīng)使用條件格式管理規(guī)則新建規(guī)則使用公式設(shè)置格式單元格公式：=find（$I$2,$C2）設(shè)置格式標(biāo)顏色確定。l HYPERLINK函數(shù)：1）HYPERLINK函數(shù)使用不經(jīng)常使用，如圖：公式里有鏈接，點(diǎn)擊他可打開2）HYPERLINK函數(shù)使用不經(jīng)常使用，如圖：有路徑，可以打開文件：*設(shè)置路徑用超鏈接方式更為簡(jiǎn)單郵件格式及網(wǎng)頁格式的超鏈接批量取消，復(fù)制選擇性粘貼粘貼數(shù)值確定。l 數(shù)據(jù)查找：Match函數(shù)的用法：如圖：提取成龍，

5、輸入match函數(shù)：=match（K3,E2:E8,0）值、位置Match的反向用法：=index（E2:E8,3）位置、值多列多行的區(qū)域：=index（E2:H8,3,4），即出現(xiàn)丙。練習(xí)1：查找M002/ M006的商品名稱：答案：=index（$A$45: $A$50,match（A54,$B$45: $B$50,0）即得出數(shù)值，如圖：課堂練習(xí)：1）在費(fèi)用項(xiàng)目里用index函數(shù)和match函數(shù)選擇出各項(xiàng)目下從上表截取費(fèi)用數(shù)值。（根據(jù)前面的費(fèi)用項(xiàng)目選擇自動(dòng)改變費(fèi)用數(shù)值）答案：=index（A1:A6,match（A10,A1:A6,0）,match（B10,A1:D1,0）2）用vlook

6、up和match函數(shù)解法：=vlookup（A10, $A$1: $D$6,match（B10, $A$1: $D$1,0），0）2種解法：練習(xí)：邀請(qǐng)人數(shù)9人，簽到人員11人，如何快速的找出多出來的2個(gè)人。是/否/if/iserror/match 是/否/iserror/vlookup 是/否/if/countif格式規(guī)則使用公式設(shè)置格式用格式規(guī)則標(biāo)出無邀請(qǐng)人員。條件格式管理規(guī)則新建規(guī)則使用公式設(shè)置格式單元格公式=iserror(match（B2，$A$2: $A$10,0）)標(biāo)出顏色課外拓展練習(xí)：2) 用三個(gè)公式把邀請(qǐng)人員標(biāo)成是，否則標(biāo)否。是/否/iserror/match 公式=if（is

7、error（match（B2,$A$2: $A$10,0）,“否”，“是”）如圖： 是/否/iserror/vlookup 公式：=if（iserror（vlookup（B2,$A$2:$A$10,1,0），“否”，“） 是/否/if/countif 公式：if（countif（$A$2:$A$10,B2）=0，“否”，“） 解題匯總：三、高效數(shù)據(jù)分析l 數(shù)據(jù)排序：1）如圖：想對(duì)庫存數(shù)量排序，點(diǎn)一一個(gè)單元格排序，即可。合并單元格是無法排序的，只能用RANK排序。兩級(jí)排序：個(gè)把包按順序排列并價(jià)格從低到高排列，點(diǎn)一一個(gè)單元格，數(shù)據(jù)排序與篩選組排序按鈕主要關(guān)鍵字填寫單位，次序?yàn)榻敌?；添加條件次要關(guān)鍵

8、字填寫最終價(jià)格，次序?yàn)樯?；以上為列的排序。行的排序：?shù)據(jù)排序與篩選組排序按鈕取消數(shù)據(jù)包含標(biāo)題選項(xiàng)按行排序行1排序，點(diǎn)擊確定。2）如圖按照筆畫排序：選中區(qū)域，選排序，選項(xiàng)里選擇按比劃排序。3）按照職位高低排序：排序職位自定義序列（前提是要提前屬在EXL選項(xiàng)里，*8.3日教程已詳細(xì)介紹）問題：1）在每行經(jīng)銷商加一行標(biāo)題欄 全部選擇，插入相同的行數(shù)，右鍵插入活動(dòng)單元格下移確定往下拉（如圖）在空白處打入1，填充序列：在經(jīng)銷商處重新改成1，填充序列；點(diǎn)擊升序，完成排序。2）在每行經(jīng)銷商下面加2行空行；填充序列；在空白的地方再填充序列；點(diǎn)擊升序，完成排序。l 數(shù)據(jù)篩選：點(diǎn)擊篩選，全部標(biāo)題欄都顯示篩選；可

9、按升序降序篩選，也可按顏色等篩選。文本類：數(shù)值類：日期類：月份分類l 高級(jí)篩選：滿足其中一點(diǎn)的叫或條件，不同行為或；三點(diǎn)都滿足的叫與條件，缺一不可，而且需要在同一行，同行為與；在數(shù)據(jù)中點(diǎn)擊任意一個(gè)單元格，執(zhí)行數(shù)據(jù)的排序與篩選中的篩選，點(diǎn)擊高級(jí)篩選，列表區(qū)域選擇數(shù)據(jù)源區(qū)域：條件篩選，（注意不能挑著選，必須要與原表內(nèi)容完全一致），點(diǎn)擊確定；（同時(shí)在左側(cè)出現(xiàn)了隱藏行） 消隱藏行點(diǎn)篩選中的清除即恢復(fù)原始。 篩選內(nèi)容復(fù)制到其它地方，直接篩選：一定要復(fù)制到原表里的其它位置，暫不可以跨表復(fù)制；練習(xí)：在某個(gè)員工檔案表里查找一個(gè)對(duì)象，通常把篩選表放在數(shù)據(jù)源的前方。姓名輸入如王（變成模糊查找）、加一列年齡、在第一

10、個(gè)年齡上輸入>18、在第二個(gè)年齡上輸入<24,婚否填入未婚，在第二行學(xué)歷為碩士，條件也為未婚。高級(jí)篩選列表區(qū)域（數(shù)據(jù)源）條件區(qū)域（給與條件的區(qū)域）篩選的結(jié)果復(fù)制到J6點(diǎn)擊確定。如圖：結(jié)果為以下三位。（因2王年齡不合適所以沒有篩選出來）l 分類匯總： 先按地區(qū)和訂單日期排序，點(diǎn)擊確定。 點(diǎn)中任何一個(gè)單元格數(shù)據(jù)分類匯總，如下表，點(diǎn)擊確定完成匯總；如果想加一個(gè)平均值，分類匯總里，點(diǎn)中訂單額，取消替換當(dāng)前分類匯總的對(duì)勾，點(diǎn)擊確定，完成；把匯總數(shù)值改成紅色，把平均值改成藍(lán)色，利用前面的折疊器；折完后：因選中會(huì)把隱藏行也給選中，所以選定區(qū)域，按F5定位條件，選擇可見單元格：改為紅色，即：選擇背

11、景顏色，選擇淺綠色：完成最后的美化：四、數(shù)據(jù)透視表：集合了匯總、篩選、公式與函數(shù)等及其它功能，非常智能的。制作數(shù)據(jù)表的規(guī)則：1）必須為標(biāo)準(zhǔn)的表即完整的2維表；必須要紅色條的列標(biāo)題；必須是當(dāng)行中唯一的數(shù)據(jù)。決不能出現(xiàn)合并單元格的情況；螞蟻線的部分是指的數(shù)據(jù)源的區(qū)域，選擇新的工作表（防止原數(shù)據(jù)源被覆蓋）即出現(xiàn)以下表格，若字段列表不小心關(guān)閉的情況下，點(diǎn)擊右鍵，顯示字段列表，即可重現(xiàn)；列表字段中的4個(gè)有優(yōu)先性，報(bào)表篩選是第一優(yōu)先，行標(biāo)簽為第二優(yōu)先，列標(biāo)簽為第三優(yōu)先，最后為數(shù)值。點(diǎn)中產(chǎn)品名稱，系統(tǒng)默認(rèn)為文本模式，分類自動(dòng)化為行標(biāo)簽，再點(diǎn)擊銷售額，點(diǎn)中它默認(rèn)進(jìn)入數(shù)值區(qū)，在選擇銷售人員，除了雙擊外還可拉拽到

12、下面的列標(biāo)簽。即2維表就完成了。清楚的看到每個(gè)人某個(gè)品牌賣了的銷售額，非常高效便捷。展開表格形式，執(zhí)行報(bào)表布局命令即：提問問題1：生成一個(gè)報(bào)表，所有的部門都賣了哪些產(chǎn)品，以及賣了產(chǎn)品的銷售額，請(qǐng)問需要幾個(gè)字段？答案：需要銷售部門，產(chǎn)品名稱及銷售額，即可完成。問題2：所有的部門都賣了哪些產(chǎn)品，以及產(chǎn)品的銷售額，還有銷售數(shù)量。答案：需要銷售部門，產(chǎn)品名稱、銷售額及數(shù)量，即可完成。問題3：所有的部門都賣了哪些產(chǎn)品，以及產(chǎn)品的銷售額及銷售數(shù)量，同時(shí)要統(tǒng)計(jì)賣的銷售額占公司的百分比份額。再拖一個(gè)銷售額在數(shù)值部分，雙擊透視表里的銷售額，修改自定義名稱為：占比值顯示方式占總和的百分比。問題4：所有的部門，賣了

13、哪些產(chǎn)品，以及銷售額、數(shù)量。因?yàn)樽龅氖浅隹谫Q(mào)易，營(yíng)業(yè)額折合成美元的價(jià)格。答案：點(diǎn)中任何一個(gè)字段，數(shù)據(jù)透視表工具選項(xiàng)工具組公式計(jì)算字段名稱改為銷售額（美元），字段選擇銷售額雙擊。公式的部分：=銷售額/6.1點(diǎn)擊添加，字段部出現(xiàn)銷售額（美元），點(diǎn)擊確定。雙擊銷售額（美元），點(diǎn)擊左下角的數(shù)字格式，保留整數(shù)。在原數(shù)據(jù)表里，從第10行開始選中到結(jié)束，右下角小叉子，按住Ctrl鍵，復(fù)制數(shù)據(jù)至100行，第10行開始，雙擊時(shí)間，選擇按月累加時(shí)間，新做一個(gè)透視表，勾中日期，勾中銷售額，把行標(biāo)簽改為日期。日期右鍵，選擇組合命令，日月年季全選中，確定。*小技巧：雙擊透視表某個(gè)數(shù)據(jù)，瞬間生成報(bào)表明細(xì)。五、專業(yè)圖表設(shè)計(jì)

14、：位置：插入圖表組1） 執(zhí)行某個(gè)報(bào)表F11，一鍵完成圖表。如想更換圖表類型右鍵更改即可。2） 在某個(gè)報(bào)表嵌入圖表，點(diǎn)中任何一個(gè)格，Alt+F1一鍵完成。3） 選中數(shù)據(jù)區(qū)域，選擇插入插入柱狀圖，即可完成。4） 添加法。遠(yuǎn)離數(shù)據(jù)源，找一個(gè)空格，選擇插入插入柱狀圖，出現(xiàn)的圖表是個(gè)空表，空表右上角，右鍵選擇數(shù)據(jù)部分，圖表數(shù)據(jù)區(qū)域，選中添加把數(shù)據(jù)源部分選中系列名稱改為，水平標(biāo)簽部分選擇標(biāo)題行部分區(qū)域，點(diǎn)擊確定。圖表工具里包括設(shè)計(jì)、布局、格式3個(gè)選項(xiàng)卡，其中設(shè)計(jì)是更改圖表類型、更改類型的，即美化效果。格式里面是局部的調(diào)整。形狀填充可更換局部顏色。刪除可選中需要?jiǎng)h除的部分按Delete鍵，即可刪除，繪圖區(qū)可

15、在形狀填充做局部的換色，也可填充一個(gè)圖片，如插入一個(gè)產(chǎn)房，即可插入。圖片畫圖表：插入（插圖）形狀上箭頭在空格的地方畫一個(gè)空的箭頭美化上箭頭（變位逐變紅色）復(fù)制雙箭頭點(diǎn)中所有圖表中的柱子在柱子部分粘貼完成?；謴?fù)原樣：右鍵重設(shè)以匹配樣式，即可恢復(fù)。插入圖片：在2011年的部分插入一輛車，在格式里面插入圖片設(shè)置數(shù)據(jù)點(diǎn)格式填充伸展改為層疊。圖表的數(shù)值：標(biāo)簽組數(shù)據(jù)標(biāo)簽顯示在頂部?；謴?fù)刪除的縱坐標(biāo)軸：布局點(diǎn)擊坐標(biāo)軸顯示默認(rèn)縱坐標(biāo)軸數(shù)據(jù)不多的話，不易用柱形圖，使用柱形圖會(huì)導(dǎo)致越排越密，非常難看。當(dāng)數(shù)據(jù)多的時(shí)候最好改成折線，這是首選。實(shí)際操作：把左邊的圖變成右邊的圖；（刻度不一樣）執(zhí)行設(shè)置坐標(biāo)軸格式改變最小值

16、最大值的固定值即可。把左邊的圖變成右邊的圖。刪除縱坐標(biāo)，刪除網(wǎng)格線，直接改標(biāo)題，改下面的線：格式線條輪廓調(diào)整粗細(xì)；鼠標(biāo)右鍵，設(shè)置坐標(biāo)軸格式主要刻度先類型（無），數(shù)據(jù)標(biāo)簽添加數(shù)據(jù)標(biāo)簽。 增加趨勢(shì)線：（可右鍵增加趨勢(shì)線指數(shù)型），在插入形狀線條（曲線）畫曲線，在中途停一下，在往上畫，畫到頂部，雙擊鼠標(biāo)，定住。格式形狀輪廓加粗加箭頭，強(qiáng)調(diào)某一年圖表，先把某一個(gè)柱狀圖標(biāo)紅，插入形狀執(zhí)行標(biāo)準(zhǔn)編輯文字給曲線圖加顏色，突出走勢(shì)。復(fù)制原數(shù)據(jù)源6-9月的數(shù)據(jù)在右邊，點(diǎn)擊表中的繪圖區(qū)，在原數(shù)據(jù)源里出現(xiàn)藍(lán)框（表二），拉中它，往旁邊的格里拉，圖表里的7-9月變成紅色，點(diǎn)擊右鍵，更改系列圖標(biāo)類型，改成面積圖。把下面的圖表

17、改成寶馬logo，添加一個(gè)logo圖標(biāo)，縮小復(fù)制，點(diǎn)中所有寶馬的數(shù)據(jù)點(diǎn)，粘貼。問題：數(shù)據(jù)源在左邊，把時(shí)間和銷售額添加在下表里。答案：點(diǎn)擊布局?jǐn)?shù)據(jù)標(biāo)簽居中。出現(xiàn)了銷售額。然后右鍵設(shè)置數(shù)據(jù)標(biāo)簽格式勾中類別名稱分隔符，選擇分行符，點(diǎn)擊確定完成。如果數(shù)據(jù)比較多的時(shí)候，想知道每個(gè)分類軸對(duì)應(yīng)的每個(gè)點(diǎn)的關(guān)系，最好加個(gè)垂直線（位置：布局分析折線選擇垂直線）當(dāng)英文過長(zhǎng)的時(shí)候最好選擇橫軸圖表，如下圖。餅圖指的是百分比份額，通常表現(xiàn)5,6塊數(shù)據(jù)足矣。數(shù)據(jù)過多，看起來會(huì)非常亂，非常的不舒服。餅圖需要強(qiáng)調(diào)重點(diǎn)。如何把左邊的表改成右邊的圖，布局圖表布局點(diǎn)第一個(gè)，快速出現(xiàn)百分比數(shù)。調(diào)整扇區(qū)位置。右鍵設(shè)置數(shù)據(jù)系列格式調(diào)整旋轉(zhuǎn)

18、度數(shù)即可完成。復(fù)合圖。首先把數(shù)據(jù)源降序排列，選中數(shù)據(jù)源區(qū)域，插入餅圖，選擇復(fù)合餅圖，設(shè)計(jì)欄圖表布局選擇第一個(gè)，即會(huì)在圖上出現(xiàn)百分比數(shù)據(jù)。（第二個(gè)餅圖是3值，如果想至4值，在餅形圖上右鍵鼠標(biāo)，設(shè)置數(shù)據(jù)點(diǎn)格式，在第二繪圖區(qū)包含數(shù)據(jù)部分改成需要的值數(shù)即可；如果想把左邊的數(shù)據(jù)餅圖放在右圖，同樣選中要挪到右邊的餅圖，右鍵鼠標(biāo)，設(shè)置數(shù)據(jù)點(diǎn)格式，選中點(diǎn)屬于第二繪圖區(qū)即可）散點(diǎn)圖是表示關(guān)系的，表現(xiàn)2種數(shù)字之間的關(guān)系。散點(diǎn)圖沒有分類軸，都是數(shù)值軸，是x和y的變化。對(duì)比圖的做法：復(fù)制原數(shù)據(jù)源，為2份。 清除第二個(gè)表的G列兒童數(shù)據(jù)，=-B2回車，G排數(shù)據(jù)均為負(fù)數(shù)。 化妝為整數(shù)顯示，選定負(fù)數(shù)區(qū)域，設(shè)置單元格格式，自定義，類型0:0.插入圖標(biāo)里的條形圖第一個(gè)。圖例點(diǎn)布局圖例顯示頂端顯示。左邊4個(gè)坐標(biāo)軸，修改右邊的坐標(biāo)軸，設(shè)置坐標(biāo)軸格式，修改固定值，最大值改為200。點(diǎn)擊右邊部分，設(shè)置數(shù)據(jù)系列格式，選擇完全重疊。選擇名稱列，右鍵設(shè)置坐標(biāo)軸格式，調(diào)整坐標(biāo)軸標(biāo)簽至低，主要刻度類型調(diào)整無，確定完成。非常規(guī)圖表，復(fù)合圖2-3種圖表復(fù)合在一起的表。2維表和三位表是不能組織在一起的。第二個(gè)圖例被第一個(gè)覆蓋的情況，點(diǎn)擊圖例，點(diǎn)擊increased單詞，右鍵設(shè)置數(shù)據(jù)系列格式，選擇次坐標(biāo)軸，更改系列圖表類型，改為折線。高于10%標(biāo)紅色，低于10%標(biāo)