Excel常用函數(shù)及數(shù)據(jù)透視表的應(yīng)用

1、1,整理課件,Excel常用函數(shù)及透視表的應(yīng)用,整理課件,2,EXCEL幾個常用函數(shù),重要概念：絕對引用與相對引用 求和有關(guān)的函數(shù)的應(yīng)用 排位函數(shù)RANK 文本函數(shù)（取出字符串中的部分字符） 文本函數(shù)TRIM 文本函數(shù)(CONCATENATE)及連綴字符“&” 查找和引用函數(shù)VLOOKUP,整理課件,3,重要概念：絕對引用與相對引用,Excel公式中一個很重要的概念就是絕對引用與相對引用 下面通過幾幅圖來講解一下 相對引用： 如圖，在C6單元格輸入“=A1”，引用了A1單元格的內(nèi)容；向下向右拖動填充，隨著單元格的變化，拖動填充的單元格內(nèi)容也會變化，這就是相對引用。,整理課件,4,重要概念：絕對

2、引用與相對引用,絕對引用： 還是這個表格，在C6單元格中輸入“=A1”后按 F4,會出現(xiàn)絕對引用的符號“=$A$1”，無論向下向右拖動填充單元格，填充的內(nèi)容都與A1單元格相同，這就是絕對引用。,整理課件,5,重要概念：絕對引用與相對引用,混合引用（絕對引用與相對引用結(jié)合使用）： 絕對引用中$相當(dāng)于一把鎖，我們都知道單元格地址是由行和列組成的，在絕對引用中，當(dāng)在C6單元格中輸入“=A1”按F4后，會變成=$A$1,即鎖定行與列，如果我們只需要絕對引用行或者絕對引用列的時候，我們需要怎么辦呢？很簡單，只需要再次按F4，就會出現(xiàn)=A$1或者=$A1。 應(yīng)用技巧： 神奇的F4,整理課件,6,重要概念：

3、絕對引用與相對引用,【混合引用】相對引用A列，絕對引用第1行:=A$1,整理課件,7,重要概念：絕對引用與相對引用,【混合引用】絕對引用A列，相對引用第1行:=$A1,整理課件,8,01-求和有關(guān)的函數(shù)的應(yīng)用,1、Alt+=：快速批量求和（一鍵求和） 方法： 1、選中數(shù)據(jù)源。 2、快捷鍵：Alt+=。其實Alt+=就是Sum函數(shù)的快捷鍵而已。 .,整理課件,9,01-求和有關(guān)的函數(shù)的應(yīng)用,1、SUM函數(shù) 1）行或列求和：=SUM(H3:H12) 2）區(qū)域求和：=SUM(D3:D12,F3:F12) 注意： 1、SUM函數(shù)中的參數(shù)，即被求和的單元格或單元格區(qū)域不能超過30個。否則Excel就會提

4、示參數(shù)太多。 2、對需要參與求和的某個常數(shù)，可用“=SUM（單元格區(qū)域，常數(shù)）”的形式直接引用，一般不必絕對引用存放該常數(shù)的單元格。,整理課件,10,2、SUMIF函數(shù) SUMIF函數(shù)可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達式。 常規(guī)用法：=SUMIF(條件區(qū)域,指定的條件,求和區(qū)域） 要計算某企業(yè)某貸款余額情況。公式輸入為 =SUMIF（A2:A16,企業(yè)代碼,D2:D16) 其中“A2:A16”為提供邏輯判斷依據(jù)的單元格區(qū)域，“企業(yè)代碼”為判斷條件即只統(tǒng)計A2:A16區(qū)域中編號為該“企業(yè)代碼”的單元格，D2:D16為實際求和的單元格區(qū)域。,02-求和有關(guān)的函數(shù)的應(yīng)用,

5、整理課件,11,Sumif：1、單條件求和。舉例：計算男、女生的成績之和。 方法：在目標(biāo)單元格中輸入公式：=SUMIF(C3:C9,N3,K3:K9)。 解讀：單條件求和函數(shù)為Sumif，其語法結(jié)構(gòu)為：=Sumif（條件范圍，求和條件，求和范圍）。當(dāng)條件范圍和求和范圍相同時，求和范圍課省略。,02-求和有關(guān)的函數(shù)的應(yīng)用,整理課件,12,Sumif：2：隔列求和。舉例：計算全年計劃數(shù)與實際數(shù) 方法：在目標(biāo)單元格中輸入公式：=SUMIF($D$3:$K$3,L$3,$D4:$K4)。 解讀：1、條件范圍為D3:K3，而且不會變化，所以采用絕對引用的形式。 條件為“計劃”和“實際”，要根據(jù)不同的情況

6、進行變化，而“計劃”和“實際”在不同列，同一行，故列采用相對引用的形式，而行采用絕對引用的形式。求和范圍為數(shù)據(jù)區(qū)D4：K4，列的范圍不會發(fā)生變化，而行要變化，所以采用絕對和相對引用相結(jié)合的形式。,02-求和有關(guān)的函數(shù)的應(yīng)用,整理課件,13,Sumif：3：不同列求和。 舉例：計算出“地區(qū)”總的銷售額 方法：在目標(biāo)單元格中輸入公式：=SUMIF(D:L,O3,C:K)。 解讀：從公式中我們可以看出，使用的函數(shù)為單條件求和Sumif函數(shù)。,02-求和有關(guān)的函數(shù)的應(yīng)用,整理課件,14,2、SUMIF函數(shù) 提示： 1、指定條件時，可以使用通配符。 2、求和區(qū)域和條件區(qū)域要大小一致，并且要注意兩者的起始

7、位置需保持一致。,02-求和有關(guān)的函數(shù)的應(yīng)用,整理課件,15,03-排位函數(shù)RANK,3、RANK(number,ref,order) Number：表示需要排位的數(shù)值 ref：可以是數(shù)組或也可以是單元格的引用，表示排名次的范圍。 Order：其值是可選的，即可以填也可以為空。 如果order為0或省略,Excel 對數(shù)字的排名是基于ref降序排列而來。 如果order為1，Excel 對數(shù)字的排位是基于ref 升序排列而來。 簡單來說： RANK(用來排序的數(shù)值，整個數(shù)值序列，0(降序)/1(升序),整理課件,16,03-排位函數(shù)RANK,案例1：對成績進行排序（從大到小，降序）。 因為要下

8、拉進行公式的復(fù)制，所以要添加絕對引用。輸入公式=RANK(D2,$D$2:$D$8,0)。,整理課件,17,03-排位函數(shù)RANK,案例2：rank函數(shù)對不連續(xù)單元格排名：不連續(xù)的單元格，第二個參數(shù)需要用括號和逗號形式連接起來。 輸入公式=RANK(B5,(B5,B9,B13,B17),0),整理課件,18,04-文本函數(shù)（取出字符串中的部分字符）,取字符串函數(shù) MID：=MID(text,start_num,num_chars) text是包含要提取字符的文本串，start_num是文本中要提取的第一個字符的位置， num_chars指定要提取的字符數(shù) LEFT：=LEFT(text,num

9、_chars) text是包含要提取字符的文本串，num_chars指定要由LEFT所提取的字符數(shù) RIGHT：=RIGHT(text,num_chars) text是包含要提取字符的文本串，num_chars指定希望要RIGHT提取的字符數(shù),從字符串”This is an apple”分別取出字符“This”，“apple”，“is”,整理課件,19,05-文本函數(shù)TRIM,清洗函數(shù)：TRIM / 應(yīng)用情景 / 在處理某系統(tǒng)中導(dǎo)出的數(shù)據(jù)時，你是否因為數(shù)據(jù)首尾存在空格、又或者存在換行符，而心生煩惱？而手動清除這些特殊格式真的是太麻煩了！TRIM函數(shù)可以很輕松的將單元格中數(shù)據(jù)首尾的空格去除；結(jié)合

10、CLEAN函數(shù)，還可以消除換行符。 函數(shù)語法： =TRIM(text) 函數(shù)功能： 1、 可以去除引用目標(biāo)的首尾空格，但對存在于字符間的空格無效；（注：要去除字符之間的空格可用SUBSTITUTE函數(shù)） 2、使用該函數(shù)后，數(shù)據(jù)類型會變?yōu)椤拔谋尽薄?整理課件,20,05-文本函數(shù)TRIM,/ 函數(shù)應(yīng)用舉例 / 1、僅消除數(shù)據(jù)首尾的空白 C4 =TRIM（B4） 原始數(shù)據(jù)及修正（刪除空格）后數(shù)據(jù)展示：,整理課件,21,05-文本函數(shù)TRIM,/ 函數(shù)應(yīng)用舉例 / 2、刪除空格的同時刪除換行符。這個操作的完成光靠TRIM函數(shù)是不行的，需要CLEAN函數(shù)的助攻； 實現(xiàn)該功能的公式如下： =CLEAN(

11、TRIM(text) 原始數(shù)據(jù)及修正（刪除空格）后數(shù)據(jù)展示：,整理課件,22,06-文本函數(shù)CONCATENATE及連綴字符“&”,連接函數(shù)：CONCATENATE(text1,text2 ) 將若干個字符合并至一個字符項中,1）2001,12,21合并寫成 2001年12月21日 =CONCATENATE(B3,年,C3,月,D3,日),2）合并寫成2000year =CONCATENATE(A2,B2),整理課件,23,07-查找和引用函數(shù)VLOOKUP,大眾情人Vlookup函數(shù)語法： VLOOKUP(lookup_value,table_array,col_index_num,rang

12、e_lookup) 簡單來說： VLOOKUP(要找誰,在哪兒找,返回第幾列的內(nèi)容,精確找還是近似找),整理課件,24,07-查找和引用函數(shù)VLOOKUP,應(yīng)用實例 方法：在目標(biāo)單元格中輸入公式：=VLOOKUP(H3,B3:D9,3,0)。 解讀： 1、查找值為H3單元格的值，查找范圍為B3:D9，返回B3:D9范圍中第3列的值，即D列的值，匹配模式為精準(zhǔn)匹配。,25,整理課件,數(shù)據(jù)透視表應(yīng)用,整理課件,26,在學(xué)習(xí)excel數(shù)據(jù)透視表之前，我們有必要了解它的概念。 什么是數(shù)據(jù)透視表呢？ 通俗地講： 數(shù)據(jù)透視表是一種可以把明細表分類匯總，而且可以隨意改變匯總模式的一種工具。,什么是數(shù)據(jù)透視表

13、,整理課件,27,1、待統(tǒng)計數(shù)據(jù)條數(shù)很多，成千上萬條記錄，甚至幾十萬條記錄需要統(tǒng)計，這時用函數(shù)公式計算的速度緩慢，導(dǎo)致表格卡頓。 2、待統(tǒng)計的數(shù)據(jù)包含很多類別，需要分類統(tǒng)計，合并同類項等操作時。 3、待統(tǒng)計的數(shù)據(jù)需要按照不同的維度整理成多種格式的報表，還可能要根據(jù)領(lǐng)導(dǎo)的指示變換行列布局，增加新的統(tǒng)計維度。 4、原始數(shù)據(jù)中的字段分裂過于繁雜，需要把屬性相近的一些數(shù)據(jù)歸類為大類別數(shù)據(jù)再進行統(tǒng)計和分析，以便用戶更清洗直觀的查看數(shù)據(jù)趨勢。 5、當(dāng)數(shù)據(jù)不但要形成報表，還需要配套的動態(tài)圖表輔助演示時。,什么時候需要用數(shù)據(jù)透視表,整理課件,28,6、當(dāng)需要數(shù)據(jù)報表中的某個數(shù)字進一步的分析，將其拆分為更詳細的

14、數(shù)據(jù)記錄時。 7、當(dāng)原始數(shù)據(jù)經(jīng)常更新，而統(tǒng)計報表也需要同步更新時。 8、當(dāng)查看數(shù)據(jù)報表時，需要多組條件篩選器，要求根據(jù)不同的篩選條件動態(tài)更新表格中的數(shù)據(jù)時。 9、當(dāng)需要把數(shù)據(jù)按照某個維度拆分為多個分表，放置到多個工作表中展示時 。 10、當(dāng)需要統(tǒng)計同比、環(huán)比增長額和百分比時。,什么時候需要用數(shù)據(jù)透視表,整理課件,29,1、計算速度快，計算上萬條記錄也僅需1秒搞定。 2、交互式動態(tài)演示，集數(shù)據(jù)透視表和數(shù)據(jù)透視圖渾然一體，動態(tài)圖表展示。 3、計算維度豐富，求和項、計數(shù)項、平均值、百分比、標(biāo)準(zhǔn)差等全部支持。 4、報表布局變換靈活，僅需拖曳字段到對應(yīng)位置即可同步更新報表布局。 5、數(shù)據(jù)透視能力強，支持

15、指定數(shù)據(jù)向下鉆取。 6、最后一個重要的優(yōu)勢就是簡單易學(xué)。,數(shù)據(jù)透視表的優(yōu)勢,整理課件,30,制作數(shù)據(jù)透視表 原材料：以一張銷售明細表為例,創(chuàng)建數(shù)據(jù)透視表,整理課件,31,第1步 選取明細表，鼠標(biāo)點在明細表內(nèi)任何地方，以Excel2010版為例：插入選項卡 - 數(shù)據(jù)透視表,創(chuàng)建數(shù)據(jù)透視表,整理課件,32,第2步 執(zhí)行“數(shù)據(jù)透視表”命令后，會進入數(shù)據(jù)透視表創(chuàng)建向?qū)?。在向?qū)У牡谝徊揭话闶悄J(rèn)選項，不需要設(shè)置，直接點確定即可。,創(chuàng)建數(shù)據(jù)透視表,整理課件,33,第3步 進行前兩步后，會自動新建一個工作表，且在工作表中會有一個數(shù)據(jù)透視表空白區(qū)域，就象蓋樓房地基已打好，需要我們安排具體的房間位置。下面是添加

16、數(shù)據(jù)透視表項目 把地區(qū)拖動到行標(biāo)簽 把銷售量和銷售金額拖動到數(shù)值區(qū)域。,創(chuàng)建數(shù)據(jù)透視表,整理課件,34,拖動完成后，一個數(shù)據(jù)透視表的雛形展現(xiàn)在我們的面前，如下圖所示。,創(chuàng)建數(shù)據(jù)透視表,整理課件,35,在制作數(shù)據(jù)透視表時，需要注意以下幾點： 1 空字段名包括空列，無法制作數(shù)據(jù)透視表 2 相同的字段名，會自動添加序號，以示區(qū)別 3 字段所在行有合并單元格，等同于空字段，也無法創(chuàng)建數(shù)據(jù)透視表 4 如果有空行，會當(dāng)成空值處理 5 是否存在非法日期和文本型數(shù)值,什么樣的格式才能做出數(shù)據(jù)透視表,整理課件,36,制作成數(shù)據(jù)透視表之前，需要看看明細表是否存在下面的問題： 1、存在空列或沒有行標(biāo)題字段。 影響：明細表如果有標(biāo)題為空，將無法制作數(shù)據(jù)透視表。 整理: 把標(biāo)題補充完整。,源表的整理,整理課件,37,2、存在相同的標(biāo)題。 影響：數(shù)據(jù)透視表會自動添加序號以區(qū)分 整理：盡量不要存在相同的列標(biāo)題。,源表的整理,整理課件,38,3、存在合并單元格 影響：合并單元格除第1個格外，其他均作為空值處理。 整理：取消合并單元格，填充完整。,源表的整理,整理課件,39,4、存在非法