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

Excel常用函數(shù)

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

SUMIF函數(shù)可對(duì)滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達(dá)式。常規(guī)用法：=SUMIF(條件區(qū)域,指定的條件,求和區(qū)域〕要計(jì)算某企業(yè)某貸款余額情況。公式輸入為=SUMIF〔A2:A16,企業(yè)代碼,D2:D16)其中“A2:A16〞為提供邏輯判斷依據(jù)的單元格區(qū)域，“企業(yè)代碼〞為判斷條件即只統(tǒng)計(jì)A2:A16區(qū)域中編號(hào)為該“企業(yè)代碼〞的單元格，D2:D16為實(shí)際求和的單元格區(qū)域。02-求和有關(guān)的函數(shù)的應(yīng)用10整理pptSumif：1、單條件求和。舉例：計(jì)算男、女生的成績(jī)之和。方法：在目標(biāo)單元格中輸入公式：=SUMIF(C3:C9,N3,K3:K9)。解讀：?jiǎn)螚l件求和函數(shù)為Sumif，其語(yǔ)法結(jié)構(gòu)為：=Sumif〔條件范圍，求和條件，求和范圍〕。當(dāng)條件范圍和求和范圍相同時(shí)，求和范圍課省略。02-求和有關(guān)的函數(shù)的應(yīng)用11整理pptSumif：2：隔列求和。舉例：計(jì)算全年方案數(shù)與實(shí)際數(shù)方法：在目標(biāo)單元格中輸入公式：=SUMIF($D$3:$K$3,L$3,$D4:$K4)。解讀：1、條件范圍為D3:K3，而且不會(huì)變化，所以采用絕對(duì)引用的形式。條件為“方案〞和“實(shí)際〞，要根據(jù)不同的情況進(jìn)行變化，而“方案〞和“實(shí)際〞在不同列，同一行，故列采用相對(duì)引用的形式，而行采用絕對(duì)引用的形式。求和范圍為數(shù)據(jù)區(qū)D4：K4，列的范圍不會(huì)發(fā)生變化，而行要變化，所以采用絕對(duì)和相對(duì)引用相結(jié)合的形式。02-求和有關(guān)的函數(shù)的應(yīng)用12整理pptSumif：3：不同列求和。舉例：計(jì)算出“地區(qū)〞總的銷(xiāo)售額方法：在目標(biāo)單元格中輸入公式：=SUMIF(D:L,O3,C:K)。解讀：從公式中我們可以看出，使用的函數(shù)為單條件求和Sumif函數(shù)。02-求和有關(guān)的函數(shù)的應(yīng)用13整理ppt2、SUMIF函數(shù)提示：1、指定條件時(shí)，可以使用通配符。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對(duì)數(shù)字的排名是基于ref降序排列而來(lái)。

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

簡(jiǎn)單來(lái)說(shuō)：RANK(用來(lái)排序的數(shù)值，整個(gè)數(shù)值序列，0(降序)/1(升序))15整理ppt03-排位函數(shù)RANK案例1：對(duì)成績(jī)進(jìn)行排序〔從大到小，降序〕。因?yàn)橐吕M(jìn)行公式的復(fù)制，所以要添加絕對(duì)引用。輸入公式=RANK(D2,$D$2:$D$8,0)。16整理ppt03-排位函數(shù)RANK案例2：rank函數(shù)對(duì)不連續(xù)單元格排名：不連續(xù)的單元格，第二個(gè)參數(shù)需要用括號(hào)和逗號(hào)形式連接起來(lái)。輸入公式=RANK(B5,(B5,B9,B13,B17),0)17整理ppt04-文本函數(shù)〔取出字符串中的局部字符〕取字符串函數(shù)MID：=MID(text,start_num,num_chars)

text是包含要提取字符的文本串，start_num是文本中要提取的第一個(gè)字符的位置，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í)，你是否因?yàn)閿?shù)據(jù)首尾存在空格、又或者存在換行符，而心生煩惱？而手動(dòng)去除這些特殊格式真的是太麻煩了！TRIM函數(shù)可以很輕松的將單元格中數(shù)據(jù)首尾的空格去除；結(jié)合CLEAN函數(shù)，還可以消除換行符。函數(shù)語(yǔ)法：=TRIM(text)函數(shù)功能：1、可以去除引用目標(biāo)的首尾空格，但對(duì)存在于字符間的空格無(wú)效；〔注：要去除字符之間的空格可用SUBSTITUTE函數(shù)〕2、使用該函數(shù)后，數(shù)據(jù)類(lèi)型會(huì)變?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、刪除空格的同時(shí)刪除換行符。這個(gè)操作的完成光靠TRIM函數(shù)是不行的，需要CLEAN函數(shù)的助攻；實(shí)現(xiàn)該功能的公式如下：=CLEAN(TRIM(text))原始數(shù)據(jù)及修正〔刪除空格〕后數(shù)據(jù)展示：21整理ppt06-文本函數(shù)CONCATENATE及連綴字符“&〞連接函數(shù)：CONCATENATE(text1,text2······)將假設(shè)干個(gè)字符合并至一個(gè)字符項(xiàng)中1〕2001,12,21合并寫(xiě)成2001年12月21日=CONCATENATE(B3,"年",C3,"月",D3,"日")2〕合并寫(xiě)成2000year=CONCATENATE(A2,B2)22整理ppt07-查找和引用函數(shù)VLOOKUP群眾情人Vlookup函數(shù)語(yǔ)法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)簡(jiǎn)單來(lái)說(shuō)：VLOOKUP(要找誰(shuí),在哪兒找,返回第幾列的內(nèi)容,精確找還是近似找)參數(shù)簡(jiǎn)單說(shuō)明輸入數(shù)據(jù)類(lèi)型lookup_value要查找的值數(shù)值、引用或文本字符串table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域col_index_num返回?cái)?shù)據(jù)在查找區(qū)域的第幾列數(shù)正整數(shù)range_lookup模糊匹配/精確匹配FALSE/TRUE（0/1）23整理ppt07-查找和引用函數(shù)VLOOKUP應(yīng)用實(shí)例方法：在目標(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ì)表分類(lèi)匯總，而且可以隨意改變匯總模式的一種工具。什么是數(shù)據(jù)透視表26整理ppt

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

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

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

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

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

6、當(dāng)需要數(shù)據(jù)報(bào)表中的某個(gè)數(shù)字進(jìn)一步的分析，將其拆分為更詳細(xì)的數(shù)據(jù)記錄時(shí)。

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

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

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

10、當(dāng)需要統(tǒng)計(jì)同比、環(huán)比增長(zhǎng)額和百分比時(shí)。什么時(shí)候需要用數(shù)據(jù)透視表28整理ppt

1、計(jì)算速度快，計(jì)算上萬(wàn)條記錄也僅需1秒搞定。

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

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

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

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

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