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

Excel常用函數(shù)

及透視表的應(yīng)用1整理pptEXCEL幾個常用函數(shù)重要概念：絕對引用與相對引用求和有關(guān)的函數(shù)的應(yīng)用排位函數(shù)RANK文本函數(shù)〔取出字符串中的局部字符〕文本函數(shù)TRIM文本函數(shù)(CONCATENATE)及連綴字符“&〞查找和引用函數(shù)VLOOKUP2整理ppt重要概念：絕對引用與相對引用Excel公式中一個很重要的概念就是絕對引用與相對引用下面通過幾幅圖來講解一下相對引用：如圖，在C6單元格輸入“=A1〞，引用了A1單元格的內(nèi)容；向下向右拖動填充，隨著單元格的變化，拖動填充的單元格內(nèi)容也會變化，這就是相對引用。3整理ppt重要概念：絕對引用與相對引用絕對引用：還是這個表格，在C6單元格中輸入“=A1〞后按F4,會出現(xiàn)絕對引用的符號“=$A$1〞，無論向下向右拖動填充單元格，填充的內(nèi)容都與A1單元格相同，這就是絕對引用。4整理ppt重要概念：絕對引用與相對引用混合引用〔絕對引用與相對引用結(jié)合使用〕：絕對引用中$相當(dāng)于一把鎖，我們都知道單元格地址是由行和列組成的，在絕對引用中，當(dāng)在C6單元格中輸入“=A1〞按F4后，會變成=$A$1,即鎖定行與列，如果我們只需要絕對引用行或者絕對引用列的時候，我們需要怎么辦呢？很簡單，只需要再次按F4，就會出現(xiàn)=A$1或者=$A1。應(yīng)用技巧：神奇的F45整理ppt重要概念：絕對引用與相對引用【混合引用】相對引用A列，絕對引用第1行:=A$16整理ppt重要概念：絕對引用與相對引用【混合引用】絕對引用A列，相對引用第1行:=$A17整理ppt01-求和有關(guān)的函數(shù)的應(yīng)用1、Alt+=：快速批量求和〔一鍵求和〕方法：1、選中數(shù)據(jù)源。2、快捷鍵：Alt+=。其實Alt+=就是Sum函數(shù)的快捷鍵而已。.8整理ppt01-求和有關(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就會提示參數(shù)太多。2、對需要參與求和的某個常數(shù)，可用“=SUM〔單元格區(qū)域，常數(shù)〕〞的形式直接引用，一般不必絕對引用存放該常數(shù)的單元格。9整理ppt2、SUMIF函數(shù)

SUMIF函數(shù)可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達(dá)式。常規(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)用10整理pptSumif：1、單條件求和。舉例：計算男、女生的成績之和。方法：在目標(biāo)單元格中輸入公式：=SUMIF(C3:C9,N3,K3:K9)。解讀：單條件求和函數(shù)為Sumif，其語法結(jié)構(gòu)為：=Sumif〔條件范圍，求和條件，求和范圍〕。當(dāng)條件范圍和求和范圍相同時，求和范圍課省略。02-求和有關(guān)的函數(shù)的應(yīng)用11整理pptSumif：2：隔列求和。舉例：計算全年方案數(shù)與實際數(shù)方法：在目標(biāo)單元格中輸入公式：=SUMIF($D$3:$K$3,L$3,$D4:$K4)。解讀：1、條件范圍為D3:K3，而且不會變化，所以采用絕對引用的形式。條件為“方案〞和“實際〞，要根據(jù)不同的情況進(jìn)行變化，而“方案〞和“實際〞在不同列，同一行，故列采用相對引用的形式，而行采用絕對引用的形式。求和范圍為數(shù)據(jù)區(qū)D4：K4，列的范圍不會發(fā)生變化，而行要變化，所以采用絕對和相對引用相結(jié)合的形式。02-求和有關(guān)的函數(shù)的應(yīng)用12整理pptSumif：3：不同列求和。舉例：計算出“地區(qū)〞總的銷售額方法：在目標(biāo)單元格中輸入公式：=SUMIF(D:L,O3,C:K)。解讀：從公式中我們可以看出，使用的函數(shù)為單條件求和Sumif函數(shù)。02-求和有關(guān)的函數(shù)的應(yīng)用13整理ppt2、SUMIF函數(shù)提示：1、指定條件時，可以使用通配符。2、求和區(qū)域和條件區(qū)域要大小一致，并且要注意兩者的起始位置需保持一致。02-求和有關(guān)的函數(shù)的應(yīng)用14整理ppt03-排位函數(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(升序))15整理ppt03-排位函數(shù)RANK案例1：對成績進(jìn)行排序〔從大到小，降序〕。因為要下拉進(jìn)行公式的復(fù)制，所以要添加絕對引用。輸入公式=RANK(D2,$D$2:$D$8,0)。16整理ppt03-排位函數(shù)RANK案例2：rank函數(shù)對不連續(xù)單元格排名：不連續(xù)的單元格，第二個參數(shù)需要用括號和逗號形式連接起來。輸入公式=RANK(B5,(B5,B9,B13,B17),0)17整理ppt04-文本函數(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〞18整理ppt05-文本函數(shù)TRIM清洗函數(shù)：TRIM/應(yīng)用情景/在處理某系統(tǒng)中導(dǎo)出的數(shù)據(jù)時，你是否因為數(shù)據(jù)首尾存在空格、又或者存在換行符，而心生煩惱？而手動去除這些特殊格式真的是太麻煩了！TRIM函數(shù)可以很輕松的將單元格中數(shù)據(jù)首尾的空格去除；結(jié)合CLEAN函數(shù)，還可以消除換行符。函數(shù)語法：=TRIM(text)函數(shù)功能：1、可以去除引用目標(biāo)的首尾空格，但對存在于字符間的空格無效；〔注：要去除字符之間的空格可用SUBSTITUTE函數(shù)〕2、使用該函數(shù)后，數(shù)據(jù)類型會變?yōu)椤拔谋鲸暋?9整理ppt05-文本函數(shù)TRIM/函數(shù)應(yīng)用舉例/1、僅消除數(shù)據(jù)首尾的空白C4=TRIM〔B4〕原始數(shù)據(jù)及修正〔刪除空格〕后數(shù)據(jù)展示：20整理ppt05-文本函數(shù)TRIM/函數(shù)應(yīng)用舉例/2、刪除空格的同時刪除換行符。這個操作的完成光靠TRIM函數(shù)是不行的，需要CLEAN函數(shù)的助攻；實現(xiàn)該功能的公式如下：=CLEAN(TRIM(text))原始數(shù)據(jù)及修正〔刪除空格〕后數(shù)據(jù)展示：21整理ppt06-文本函數(shù)CONCATENATE及連綴字符“&〞連接函數(shù)：CONCATENATE(text1,text2······)將假設(shè)干個字符合并至一個字符項中1〕2001,12,21合并寫成2001年12月21日=CONCATENATE(B3,"年",C3,"月",D3,"日")2〕合并寫成2000year=CONCATENATE(A2,B2)22整理ppt07-查找和引用函數(shù)VLOOKUP群眾情人Vlookup函數(shù)語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)簡單來說：VLOOKUP(要找誰,在哪兒找,返回第幾列的內(nèi)容,精確找還是近似找)參數(shù)簡單說明輸入數(shù)據(jù)類型lookup_value要查找的值數(shù)值、引用或文本字符串table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域col_index_num返回數(shù)據(jù)在查找區(qū)域的第幾列數(shù)正整數(shù)range_lookup模糊匹配/精確匹配FALSE/TRUE（0/1）23整理ppt07-查找和引用函數(shù)VLOOKUP應(yīng)用實例方法：在目標(biāo)單元格中輸入公式：=VLOOKUP(H3,B3:D9,3,0)。解讀：1、查找值為H3單元格的值，查找范圍為B3:D9，返回B3:D9范圍中第3列的值，即D列的值，匹配模式為精準(zhǔn)匹配。24整理ppt數(shù)據(jù)透視表應(yīng)用25整理ppt在學(xué)習(xí)excel數(shù)據(jù)透視表之前，我們有必要了解它的概念。什么是數(shù)據(jù)透視表呢？通俗地講：數(shù)據(jù)透視表是一種可以把明細(xì)表分類匯總，而且可以隨意改變匯總模式的一種工具。什么是數(shù)據(jù)透視表26整理ppt

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ù)再進(jìn)行統(tǒng)計和分析，以便用戶更清洗直觀的查看數(shù)據(jù)趨勢。

5、當(dāng)數(shù)據(jù)不但要形成報表，還需要配套的動態(tài)圖表輔助演示時。什么時候需要用數(shù)據(jù)透視表27整理ppt

6、當(dāng)需要數(shù)據(jù)報表中的某個數(shù)字進(jìn)一步的分析，將其拆分為更詳細(xì)的數(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ù)透視表28整理ppt

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

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

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

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

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

6、最后一個重要的優(yōu)勢就是簡單易學(xué)。數(shù)據(jù)透視表的優(yōu)勢29整理ppt制作數(shù)據(jù)透視表原材料：以一張銷售明細(xì)表為例創(chuàng)立數(shù)據(jù)透視表30整理ppt第1步選取明細(xì)表，鼠標(biāo)點在明細(xì)表內(nèi)任何地方，以Excel2021版為例：插入選項卡-數(shù)據(jù)透視表創(chuàng)立數(shù)據(jù)透視表31整理ppt第2步執(zhí)行“數(shù)據(jù)透視表〞命令后，會進(jìn)入數(shù)據(jù)透視表創(chuàng)立向?qū)?。在向?qū)У牡谝徊揭话闶悄J(rèn)選項，不需要設(shè)置，直接點確定即可。創(chuàng)立數(shù)據(jù)透視表32整理ppt第3步進(jìn)行前兩步后，會自動新建一個工作表，且在工作表中會有一個數(shù)據(jù)透視表空白區(qū)域，就象蓋樓房地基已打好，需要我們安排具體的房間位置。下面是添加數(shù)據(jù)透視表工程把地區(qū)拖動到行標(biāo)簽把銷售量和銷售金額拖動到數(shù)值區(qū)域。創(chuàng)立數(shù)據(jù)透視表33整理ppt拖動完成后，一個數(shù)據(jù)透視表的雛形展現(xiàn)在我們的面前，如以下圖所示。創(chuàng)立數(shù)據(jù)透視表34整理ppt在制作數(shù)據(jù)透視表時，需要注意以下幾點：1空字段名包括空列，無法制作數(shù)據(jù)透視表2相同的字段名，會自動添加序號，以示區(qū)別3字段所在行有合并單元格，等同于空字段，也無法創(chuàng)立數(shù)據(jù)透視表4如果有空行，會當(dāng)成空值處理5是否存在非法日期和文本型數(shù)值什么樣的格式才能做出數(shù)據(jù)透視表35整理ppt制作成數(shù)據(jù)透視表之前，需要看看明細(xì)表是否存在下面的問題：1、存在空列或沒有行標(biāo)題字段。影響：明細(xì)表如果有標(biāo)題為空，將無法制作數(shù)據(jù)透視表。整理:把標(biāo)題補(bǔ)充完整。源表的整理36整理ppt2、存在相同的標(biāo)題。影響：數(shù)據(jù)透視表會自動添加序號以區(qū)分整理：盡量不要存在相同的列標(biāo)題。源表的整理37整理ppt3、存在合并單元格影響：合并單元格除第1個格外，其他均作為空值處理。整理：取消合并單元格，填充完整。源表的整理38整理ppt4、存在非法日期。影響：生成的數(shù)據(jù)透視表中，無法按日期格式進(jìn)行年、月