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

Excel常用函數(shù)

及透視表的應用

2021/6/281EXCEL幾個常用函數(shù)重要概念：絕對引用與相對引用求和有關的函數(shù)的應用排位函數(shù)RANK文本函數(shù)（取出字符串中的部分字符）文本函數(shù)TRIM文本函數(shù)(CONCATENATE)及連綴字符“&”查找和引用函數(shù)VLOOKUP2021/6/282重要概念：絕對引用與相對引用Excel公式中一個很重要的概念就是絕對引用與相對引用下面通過幾幅圖來講解一下相對引用：如圖，在C6單元格輸入“=A1”，引用了A1單元格的內容；向下向右拖動填充，隨著單元格的變化，拖動填充的單元格內容也會變化，這就是相對引用。2021/6/283重要概念：絕對引用與相對引用絕對引用：還是這個表格，在C6單元格中輸入“=A1”后按F4,會出現(xiàn)絕對引用的符號“=$A$1”，無論向下向右拖動填充單元格，填充的內容都與A1單元格相同，這就是絕對引用。2021/6/284重要概念：絕對引用與相對引用混合引用（絕對引用與相對引用結合使用）：

絕對引用中$相當于一把鎖，我們都知道單元格地址是由行和列組成的，在絕對引用中，當在C6單元格中輸入“=A1”按F4后，會變成=$A$1,即鎖定行與列，如果我們只需要絕對引用行或者絕對引用列的時候，我們需要怎么辦呢？很簡單，只需要再次按F4，就會出現(xiàn)=A$1或者=$A1。應用技巧：神奇的F42021/6/285重要概念：絕對引用與相對引用【混合引用】相對引用A列，絕對引用第1行:=A$12021/6/286重要概念：絕對引用與相對引用【混合引用】絕對引用A列，相對引用第1行:=$A12021/6/28701-求和有關的函數(shù)的應用1、Alt+=：快速批量求和（一鍵求和）方法：1、選中數(shù)據(jù)源。2、快捷鍵：Alt+=。其實Alt+=就是Sum函數(shù)的快捷鍵而已。.2021/6/28801-求和有關的函數(shù)的應用1、SUM函數(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ù)的單元格。2021/6/2892、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-求和有關的函數(shù)的應用2021/6/2810Sumif：1、單條件求和。舉例：計算男、女生的成績之和。方法：在目標單元格中輸入公式：=SUMIF(C3:C9,N3,K3:K9)。解讀：單條件求和函數(shù)為Sumif，其語法結構為：=Sumif（條件范圍，求和條件，求和范圍）。當條件范圍和求和范圍相同時，求和范圍課省略。02-求和有關的函數(shù)的應用2021/6/2811Sumif：2：隔列求和。舉例：計算全年計劃數(shù)與實際數(shù)方法：在目標單元格中輸入公式：=SUMIF($D$3:$K$3,L$3,$D4:$K4)。解讀：1、條件范圍為D3:K3，而且不會變化，所以采用絕對引用的形式。條件為“計劃”和“實際”，要根據(jù)不同的情況進行變化，而“計劃”和“實際”在不同列，同一行，故列采用相對引用的形式，而行采用絕對引用的形式。求和范圍為數(shù)據(jù)區(qū)D4：K4，列的范圍不會發(fā)生變化，而行要變化，所以采用絕對和相對引用相結合的形式。02-求和有關的函數(shù)的應用2021/6/2812Sumif：3：不同列求和。舉例：計算出“地區(qū)”總的銷售額方法：在目標單元格中輸入公式：=SUMIF(D:L,O3,C:K)。解讀：從公式中我們可以看出，使用的函數(shù)為單條件求和Sumif函數(shù)。02-求和有關的函數(shù)的應用2021/6/28132、SUMIF函數(shù)提示：1、指定條件時，可以使用通配符。2、求和區(qū)域和條件區(qū)域要大小一致，并且要注意兩者的起始位置需保持一致。02-求和有關的函數(shù)的應用2021/6/281403-排位函數(shù)RANK3、RANK(number,ref,[order])Number：表示需要排位的數(shù)值ref：可以是數(shù)組或也可以是單元格的引用，表示排名次的范圍。

Order：其值是可選的，即可以填也可以為空。如果order為0或省略,Excel對數(shù)字的排名是基于ref降序排列而來。

如果order為1，Excel對數(shù)字的排位是基于ref升序排列而來。

簡單來說：RANK(用來排序的數(shù)值，整個數(shù)值序列，0(降序)/1(升序))2021/6/281503-排位函數(shù)RANK案例1：對成績進行排序（從大到小，降序）。因為要下拉進行公式的復制，所以要添加絕對引用。輸入公式=RANK(D2,$D$2:$D$8,0)。2021/6/281603-排位函數(shù)RANK案例2：rank函數(shù)對不連續(xù)單元格排名：不連續(xù)的單元格，第二個參數(shù)需要用括號和逗號形式連接起來。輸入公式=RANK(B5,(B5,B9,B13,B17),0)2021/6/281704-文本函數(shù)（取出字符串中的部分字符）取字符串函數(shù)MID：=MID(text,start_num,num_chars)

text是包含要提取字符的文本串，start_num是文本中要提取的第一個字符的位置，num_chars指定要提取的字符數(shù)LEFT：=LEFT(text,num_chars)

text是包含要提取字符的文本串，num_chars指定要由LEFT所提取的字符數(shù)RIGHT：=RIGHT(text,num_chars)

text是包含要提取字符的文本串，num_chars指定希望要RIGHT提取的字符數(shù)從字符串”Thisisanapple”分別取出字符“This”，“apple”，“is”2021/6/281805-文本函數(shù)TRIM清洗函數(shù)：TRIM/應用情景/在處理某系統(tǒng)中導出的數(shù)據(jù)時，你是否因為數(shù)據(jù)首尾存在空格、又或者存在換行符，而心生煩惱？而手動清除這些特殊格式真的是太麻煩了！TRIM函數(shù)可以很輕松的將單元格中數(shù)據(jù)首尾的空格去除；結合CLEAN函數(shù)，還可以消除換行符。函數(shù)語法：=TRIM(text)函數(shù)功能：1、可以去除引用目標的首尾空格，但對存在于字符間的空格無效；（注：要去除字符之間的空格可用SUBSTITUTE函數(shù)）2、使用該函數(shù)后，數(shù)據(jù)類型會變?yōu)椤拔谋尽薄?021/6/281905-文本函數(shù)TRIM/函數(shù)應用舉例/1、僅消除數(shù)據(jù)首尾的空白C4

=TRIM（B4）原始數(shù)據(jù)及修正（刪除空格）后數(shù)據(jù)展示：2021/6/282005-文本函數(shù)TRIM/函數(shù)應用舉例/2、刪除空格的同時刪除換行符。這個操作的完成光靠TRIM函數(shù)是不行的，需要CLEAN函數(shù)的助攻；實現(xiàn)該功能的公式如下：=CLEAN(TRIM(text))原始數(shù)據(jù)及修正（刪除空格）后數(shù)據(jù)展示：2021/6/282106-文本函數(shù)CONCATENATE及連綴字符“&”連接函數(shù)：CONCATENATE(text1,text2·

·

·

·

·

·)將若干個字符合并至一個字符項中1）2001,12,21合并寫成2001年12月21日=CONCATENATE(B3,"年",C3,"月",D3,"日")2）合并寫成2000year=CONCATENATE(A2,B2)2021/6/282207-查找和引用函數(shù)VLOOKUP大眾情人Vlookup函數(shù)語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)簡單來說：VLOOKUP(要找誰,在哪兒找,返回第幾列的內容,精確找還是近似找)2021/6/282307-查找和引用函數(shù)VLOOKUP應用實例方法：在目標單元格中輸入公式：=VLOOKUP(H3,B3:D9,3,0)。解讀：1、查找值為H3單元格的值，查找范圍為B3:D9，返回B3:D9范圍中第3列的值，即D列的值，匹配模式為精準匹配。2021/6/2824數(shù)據(jù)透視表應用2021/6/2825在學習excel數(shù)據(jù)透視表之前，我們有必要了解它的概念。什么是數(shù)據(jù)透視表呢？通俗地講：數(shù)據(jù)透視表是一種可以把明細表分類匯總，而且可以隨意改變匯總模式的一種工具。什么是數(shù)據(jù)透視表2021/6/2826

1、待統(tǒng)計數(shù)據(jù)條數(shù)很多，成千上萬條記錄，甚至幾十萬條記錄需要統(tǒng)計，這時用函數(shù)公式計算的速度緩慢，導致表格卡頓。

2、待統(tǒng)計的數(shù)據(jù)包含很多類別，需要分類統(tǒng)計，合并同類項等操作時。

3、待統(tǒng)計的數(shù)據(jù)需要按照不同的維度整理成多種格式的報表，還可能要根據(jù)領導的指示變換行列布局，增加新的統(tǒng)計維度。

4、原始數(shù)據(jù)中的字段分裂過于繁雜，需要把屬性相近的一些數(shù)據(jù)歸類為大類別數(shù)據(jù)再進行統(tǒng)計和分析，以便用戶更清洗直觀的查看數(shù)據(jù)趨勢。

5、當數(shù)據(jù)不但要形成報表，還需要配套的動態(tài)圖表輔助演示時。什么時候需要用數(shù)據(jù)透視表2021/6/2827

6、當需要數(shù)據(jù)報表中的某個數(shù)字進一步的分析，將其拆分為更詳細的數(shù)據(jù)記錄時。

7、當原始數(shù)據(jù)經常更新，而統(tǒng)計報表也需要同步更新時。

8、當查看數(shù)據(jù)報表時，需要多組條件篩選器，要求根據(jù)不同的篩選條件動態(tài)更新表格中的數(shù)據(jù)時。

9、當需要把數(shù)據(jù)按照某個維度拆分為多個分表，放置到多個工作表中展示時。

10、當需要統(tǒng)計同比、環(huán)比增長額和百分比時。什么時候需要用數(shù)據(jù)透視表2021/6/2828

1、計算速度快，計算上萬條記錄也僅需1秒搞定。

2、交互式動態(tài)演示，集數(shù)據(jù)透視表和數(shù)據(jù)透視圖渾然一體，動態(tài)圖表展示。

3、計算維度豐富，求和項、計數(shù)項、平均值、百分比、標準差等全部支持。

4、報表布局變換靈活，僅需拖曳字段到對應位置即可同步更新報表布局。

5、數(shù)據(jù)透視能力強，支持指定數(shù)據(jù)向下鉆取。

6、最后一個重要的優(yōu)勢就是簡單易學。數(shù)據(jù)透視表的優(yōu)勢2021/6/2829制作數(shù)據(jù)透視表原材料：以一張銷售明細表為例創(chuàng)建數(shù)據(jù)透視表2021/6/2830第1步選取明細表，鼠標點在明細表內任何地方，以Excel2010版為例：插入選項卡-數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表2021/6/2831第2步執(zhí)行“數(shù)據(jù)透視表”命令后，會進入數(shù)據(jù)透視表創(chuàng)建向導。在向導的第一步一般是默認選項，不需要設置，直接點確定即可。創(chuàng)建數(shù)據(jù)透視表2021/6/2832第3步進行前兩步后，會自動新建一個工作表，且在工作表中會有一個數(shù)據(jù)透視表空白區(qū)域，就象蓋樓房地基已打好，需要我們安排具體的房間位置。下面是添加數(shù)據(jù)透視表項目把地區(qū)拖動到行標簽把銷售量和銷售金額拖動到數(shù)值區(qū)域。創(chuàng)建數(shù)據(jù)透視表2021/6/2833拖動完成后，一個數(shù)據(jù)透視表的雛形展現(xiàn)在我們的面前，如下圖所示。創(chuàng)建數(shù)據(jù)透視表2021/6/2834在制作數(shù)據(jù)透視表時，需要注意以下幾點：1空字段名包括空列，無法制作數(shù)據(jù)透視表2相同的字段名，會自動添加序號，以示區(qū)別3字段所在行有合并單元格，等同于空字段，也無法創(chuàng)建數(shù)據(jù)透視表4如果有空行，會當成空值處理5是否存在非法日期和文本型數(shù)值什么樣的格式才能做出數(shù)據(jù)透視表2021/6