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

1、1整理ppt Excel常用函數(shù) 及透視表的應(yīng)用 整理ppt2 EXCEL幾個常用函數(shù) 重要概念：絕對引用與相對引用重要概念：絕對引用與相對引用 求和有關(guān)的函數(shù)的應(yīng)用求和有關(guān)的函數(shù)的應(yīng)用 排排位位函數(shù)函數(shù)RANK 文本函數(shù)（取出字符串中的部分字符）文本函數(shù)（取出字符串中的部分字符） 文本函數(shù)文本函數(shù)TRIM 文本函數(shù)文本函數(shù)(CONCATENATE)及連綴字符及連綴字符“&” 查找和引用函數(shù)查找和引用函數(shù)VLOOKUP 整理ppt3 重要概念：絕對引用與相對引用 Excel公式中一個很重要的概念就是絕對引用與相對引用 下面通過幾幅圖來講解一下 相對引用： 如圖，在C6單元格輸入“=A1”，引用

2、了A1單元格的內(nèi)容；向下向右 拖動填充，隨著單元格的變化，拖動填充的單元格內(nèi)容也會變 化，這就是相對引用。 整理ppt4 重要概念：絕對引用與相對引用 絕對引用： 還是這個表格，在C6單元格中輸入“=A1”后按 F4,會出現(xiàn)絕對 引用的符號“=$A$1”，無論向下向右拖動填充單元格，填充的 內(nèi)容都與A1單元格相同，這就是絕對引用。 整理ppt5 重要概念：絕對引用與相對引用 混合引用（絕對引用與相對引用結(jié)合使用）： 絕對引用中$相當于一把鎖，我們都知道單元格地址是由行和列 組成的，在絕對引用中，當在C6單元格中輸入“=A1”按F4后， 會變成=$A$1,即鎖定行與列，如果我們只需要絕對引用行或

3、者 絕對引用列的時候，我們需要怎么辦呢？很簡單，只需要再次 按F4，就會出現(xiàn)=A$1或者=$A1。 應(yīng)用技巧： 神奇的F4 整理ppt6 重要概念：絕對引用與相對引用 【混合引用】相對引用A列，絕對引用第1行:=A$1 整理ppt7 重要概念：絕對引用與相對引用 【混合引用】絕對引用A列，相對引用第1行:=$A1 整理ppt8 01-求和有關(guān)的函數(shù)的應(yīng)用 1 1、Alt+=Alt+=：快速批量求和：快速批量求和（一鍵求和）（一鍵求和） 方法：方法： 1 1、選中數(shù)據(jù)源。、選中數(shù)據(jù)源。 2 2、快捷鍵：、快捷鍵：Alt+=Alt+=。其實。其實Alt+=Alt+=就是就是SumSum函數(shù)的快捷鍵

4、而已。函數(shù)的快捷鍵而已。 . 整理ppt9 01-求和有關(guān)的函數(shù)的應(yīng)用 1 1、SUMSUM函數(shù)函數(shù) 1）行或列求和：=SUM(H3:H12) 2）區(qū)域求和：=SUM(D3:D12,F3:F12) 注意： 1、SUM函數(shù)中的參數(shù)，即被求和的單元格或單元格區(qū)域不能超過 30個。否則Excel就會提示參數(shù)太多。 2、對需要參與求和的某個常數(shù)，可用“=SUM（單元格區(qū)域，常 數(shù)）”的形式直接引用，一般不必絕對引用存放該常數(shù)的單元格。 整理ppt10 2 2、SUMIFSUMIF函數(shù)函數(shù) SUMIF函數(shù)可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、 文本或表達式。 常規(guī)用法：常規(guī)用法：=SUMI

5、F(=SUMIF(條件區(qū)域條件區(qū)域, ,指定的條件指定的條件, ,求和區(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)用 整理ppt11 SumifSumif：1 1、單條件求和。單條件求和。舉例舉例：計算男、女生的成績之和。：計算男、女生的成績之和。 方法：在目標單元格中輸入公式：方法：在目標單元格中輸入公式：=SUMIF(C3:C9,

6、N3,K3:K9)=SUMIF(C3:C9,N3,K3:K9)。 解讀：單條件求和函數(shù)為解讀：單條件求和函數(shù)為SumifSumif，其語法結(jié)構(gòu)為：，其語法結(jié)構(gòu)為：=Sumif=Sumif（條件范圍，求（條件范圍，求 和條件，求和范圍）。當條件范圍和求和范圍相同時，求和范圍課和條件，求和范圍）。當條件范圍和求和范圍相同時，求和范圍課 省略。省略。 02-求和有關(guān)的函數(shù)的應(yīng)用 整理ppt12 SumifSumif：2 2：隔列求和。舉例：計算全年計劃數(shù)與實際數(shù)：隔列求和。舉例：計算全年計劃數(shù)與實際數(shù) 方法：在目標單元格中輸入公式：方法：在目標單元格中輸入公式：=SUMIF($D$3:$K$3,L$

7、3,$D4:$K4)=SUMIF($D$3:$K$3,L$3,$D4:$K4)。 解讀：解讀：1 1、條件范圍為、條件范圍為D3:K3D3:K3，而且不會變化，所以采用絕對引用的形式。，而且不會變化，所以采用絕對引用的形式。 條件為條件為“計劃計劃”和和“實際實際”，要根據(jù)不同的情況進行變化，而，要根據(jù)不同的情況進行變化，而“計劃計劃”和和 “實際實際”在不同列，同一行，故列采用相對引用的形式，而行采用絕對在不同列，同一行，故列采用相對引用的形式，而行采用絕對 引用的形式。求和范圍為數(shù)據(jù)區(qū)引用的形式。求和范圍為數(shù)據(jù)區(qū)D4D4：K4K4，列的范圍不會發(fā)生變化，而行，列的范圍不會發(fā)生變化，而行 要

8、變化，所以采用絕對和相對引用相結(jié)合的形式。要變化，所以采用絕對和相對引用相結(jié)合的形式。 02-求和有關(guān)的函數(shù)的應(yīng)用 整理ppt13 SumifSumif：3 3：不同列求和。：不同列求和。 舉例：計算出舉例：計算出“地區(qū)地區(qū)”總的銷售額總的銷售額 方法：在目標單元格中輸入公式：方法：在目標單元格中輸入公式：=SUMIF(D:L,O3,C:K)=SUMIF(D:L,O3,C:K)。 解讀：從公式中我們可以看出，使用的函數(shù)為單條件求和解讀：從公式中我們可以看出，使用的函數(shù)為單條件求和SumifSumif函數(shù)。函數(shù)。 02-求和有關(guān)的函數(shù)的應(yīng)用 整理ppt14 2 2、SUMIFSUMIF函數(shù)函數(shù)

9、提示： 1、指定條件時，可以使用通配符。 2、求和區(qū)域和條件區(qū)域要大小一致，并且要注意兩者的起始位置需保持 一致。 02-求和有關(guān)的函數(shù)的應(yīng)用 整理ppt15 03-排位函數(shù)RANK 3 3、RANKRANK(number,ref,order) Number：表示需要排位的數(shù)值 ref：可以是數(shù)組或也可以是單元格的引用，表示排名次的范圍。 Order：其值是可選的，即可以填也可以為空。 如果order為0或省略,Excel 對數(shù)字的排名是基于ref降序排列而來。 如果order為1，Excel 對數(shù)字的排位是基于ref 升序排列而來。 簡單來說：簡單來說： 整理ppt16 03-排位函數(shù)RAN

10、K 案例1：對成績進行排序（從大到小，降序）。 因為要下拉進行公式的復(fù)制，所以要添加絕對引用。輸入公式 =RANK(D2,$D$2:$D$8,0)。 整理ppt17 03-排位函數(shù)RANK 案例2：rank函數(shù)對不連續(xù)單元格排名：不連續(xù)的單元格，第二個參數(shù) 需要用括號和逗號形式連接起來。 輸入公式=RANK(B5,(B5,B9,B13,B17),0) 整理ppt18 04-文本函數(shù)（取出字符串中的部分字符） 取字符串函數(shù)取字符串函數(shù) MID：=MID(text,start_num,num_chars) text是包含要提取字符的文本串，start_num是文本中要提取的 第一個字符的位置， n

11、um_chars指定要提取的字符數(shù) LEFT：=LEFT(text,num_chars) text是包含要提取字符的文本串，num_chars指定要由LEFT所 提取的字符數(shù) RIGHT：=RIGHT(text,num_chars) text是包含要提取字符的文本串，num_chars指定希望要 RIGHT提取的字符數(shù) 從字符串”This is an apple” 分別取出字符“This”， “apple”，“is” 整理ppt19 05-文本函數(shù)TRIM 清洗函數(shù)：清洗函數(shù)：TRIM / 應(yīng)用情景 / 在處理某系統(tǒng)中導出的數(shù)據(jù)時，你是否因為數(shù)據(jù)首尾存在空格、 又或者存在換行符，而心生煩惱？而

12、手動清除這些特殊格式真 的是太麻煩了！TRIM函數(shù)可以很輕松的將單元格中數(shù)據(jù)首尾的 空格去除；結(jié)合CLEAN函數(shù)，還可以消除換行符。 函數(shù)語法： =TRIM(text) 函數(shù)功能： 1、 可以去除引用目標的首尾空格，但對存在于字符間的空格無效； （注：要去除字符之間的空格可用SUBSTITUTE函數(shù)） 2、使用該函數(shù)后，數(shù)據(jù)類型會變?yōu)椤拔谋尽薄?整理ppt20 05-文本函數(shù)TRIM / 函數(shù)應(yīng)用舉例函數(shù)應(yīng)用舉例 / 1、僅消除數(shù)據(jù)首尾的空白 C4 =TRIM（B4） 原始數(shù)據(jù)及修正（刪除空格）后數(shù)據(jù)展示： 整理ppt21 05-文本函數(shù)TRIM / 函數(shù)應(yīng)用舉例函數(shù)應(yīng)用舉例 / 2、刪除空格

13、的同時刪除換行符。這個操作的完成光靠TRIM函數(shù)是 不行的，需要CLEAN函數(shù)的助攻； 實現(xiàn)該功能的公式如下： =CLEAN(TRIM(text) 原始數(shù)據(jù)及修正（刪除空格）后數(shù)據(jù)展示： 整理ppt22 06-文本函數(shù)CONCATENATE及連綴字符“&” 連接函數(shù)：連接函數(shù)：CONCATENATE(text1,text2 ) 將若干個字符合并至一個字符項中 1）2001,12,21合并寫成 2001年12月21日 =CONCATENATE(B3,年,C3,月 ,D3,日) 2）合并寫成2000year =CONCATENATE(A2,B2) 整理ppt23 07-查找和引用函數(shù)VLOOKUP

14、 大眾情人大眾情人Vlookup函數(shù)語法：函數(shù)語法： VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 簡單來說：簡單來說： VLOOKUP(要找誰要找誰,在哪兒找在哪兒找,返回第幾列的內(nèi)容返回第幾列的內(nèi)容,精確找還是近似找精確找還是近似找) 參數(shù)參數(shù)簡單說明簡單說明輸入數(shù)據(jù)類型輸入數(shù)據(jù)類型 lookup_value要查找的值數(shù)值、引用或文本字 符串 table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域 col_index_num返回數(shù)據(jù)在查找區(qū)域 的第幾列數(shù) 正整數(shù) range_lookup模糊匹配/精確匹配FALSE/TRUE

15、（0/1） 整理ppt24 07-查找和引用函數(shù)VLOOKUP 應(yīng)用實例 方法：在目標單元格中輸入公式：=VLOOKUP(H3,B3:D9,3,0)。 解讀： 1、查找值為H3單元格的值，查找范圍為B3:D9，返回B3:D9范 圍中第3列的值，即D列的值，匹配模式為精準匹配。 25整理ppt 數(shù)據(jù)透視表應(yīng)用 整理ppt26 在學習excel數(shù)據(jù)透視表之前，我們有必要了解它的概念。 什么是數(shù)據(jù)透視表呢？ 通俗地講： 數(shù)據(jù)透視表是一種可以把明細表分類匯總，而且可 以隨意改變匯總模式的一種工具。 什么是數(shù)據(jù)透視表 整理ppt27 1、待統(tǒng)計數(shù)據(jù)條數(shù)很多，成千上萬條記錄，甚至幾十萬條記錄 需要統(tǒng)計，這

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

17、看數(shù)據(jù)報表時，需要多組條件篩選器，要求根據(jù)不同的篩 選條件動態(tài)更新表格中的數(shù)據(jù)時。 9、當需要把數(shù)據(jù)按照某個維度拆分為多個分表，放置到多個工作 表中展示時 。 10、當需要統(tǒng)計同比、環(huán)比增長額和百分比時。 什么時候需要用數(shù)據(jù)透視表 整理ppt29 1、計算速度快，計算上萬條記錄也僅需1秒搞定。 2、交互式動態(tài)演示，集數(shù)據(jù)透視表和數(shù)據(jù)透視圖渾然一體，動態(tài) 圖表展示。 3、計算維度豐富，求和項、計數(shù)項、平均值、百分比、標準差等 全部支持。 4、報表布局變換靈活，僅需拖曳字段到對應(yīng)位置即可同步更新報 表布局。 5、數(shù)據(jù)透視能力強，支持指定數(shù)據(jù)向下鉆取。 6、最后一個重要的優(yōu)勢就是簡單易學。 數(shù)據(jù)透視

18、表的優(yōu)勢 整理ppt30 制作數(shù)據(jù)透視表 原材料：以一張銷售明細表為例 創(chuàng)建數(shù)據(jù)透視表 整理ppt31 第1步 選取明細表，鼠標點在明細表內(nèi)任何地方，以Excel2010版為例： 插入選項卡 - 數(shù)據(jù)透視表 創(chuàng)建數(shù)據(jù)透視表 整理ppt32 第2步 執(zhí)行“數(shù)據(jù)透視表”命令后，會進入數(shù)據(jù)透視表創(chuàng)建向?qū)АＴ?向?qū)У牡谝徊揭话闶悄J選項，不需要設(shè)置，直接點確定即可。 創(chuàng)建數(shù)據(jù)透視表 整理ppt33 第3步 進行前兩步后，會自動新建一個工作表，且在工作表中會有一 個數(shù)據(jù)透視表空白區(qū)域，就象蓋樓房地基已打好，需要我們安排具體 的房間位置。下面是添加數(shù)據(jù)透視表項目 把地區(qū)拖動到行標簽 把銷售量和銷售金額拖動到數(shù)值區(qū)域。 創(chuàng)建數(shù)據(jù)透視表 整理ppt34 拖動完成后，一個數(shù)據(jù)透視表的雛形展現(xiàn)在我們的面前，如下圖所示。 創(chuàng)建數(shù)據(jù)透視表 整理ppt35 在制作數(shù)據(jù)透視表時，需要注意以下幾點： 1 空字段名包括空列，無法制作數(shù)據(jù)透視