Excel操作技巧與函數(shù)高效應(yīng)用(學(xué)員版)課件

Excel操作技巧及函數(shù)高效應(yīng)用1精選2021版課件

Excel是辦公自動(dòng)化中非常重要的一款軟件，它不僅僅能夠快捷方便地處理表格和進(jìn)行圖形分析，其更強(qiáng)大的功能體現(xiàn)在對(duì)數(shù)據(jù)的自動(dòng)處理和計(jì)算。函數(shù)作為Excel處理數(shù)據(jù)的一個(gè)最重要手段，功能是十分強(qiáng)大的，在生活和工作實(shí)踐中可以有多種應(yīng)用，您甚至可以用Excel來設(shè)計(jì)復(fù)雜的統(tǒng)計(jì)管理表格或者小型的數(shù)據(jù)庫(kù)系統(tǒng)。

你平時(shí)都用到Excel的哪些功能？你是否經(jīng)常進(jìn)行大量的數(shù)據(jù)整理？你是否經(jīng)常對(duì)數(shù)據(jù)進(jìn)行圖表化處理，分析數(shù)據(jù)趨勢(shì)？你是否可以將學(xué)到的Excel函數(shù)靈活運(yùn)用到實(shí)際工作中？。。。。。。精選2021版課件目錄

基礎(chǔ)操作-實(shí)用小技巧

函數(shù)應(yīng)用-常用幾類函數(shù)操作

數(shù)據(jù)透視表-數(shù)據(jù)整理的有效工具1精選2021版課件基礎(chǔ)操作快速填充定義名稱數(shù)據(jù)有效性條件格式壹

基礎(chǔ)操作-實(shí)用小技巧2精選2021版課件快速填充基礎(chǔ)操作1壹定位條件？3方法二：鼠標(biāo)雙擊方法一：鼠標(biāo)左鍵拖動(dòng)方法五：運(yùn)用快速填充“求和小計(jì)”ALT+=方法四：CTRL+D或CTRL+R延伸：自定義填充設(shè)置方法三：快捷鍵：CTRL+ENTER精選2021版課件定義名稱基礎(chǔ)操作2壹4EXCEL使用中遇到的部分問題實(shí)現(xiàn)數(shù)據(jù)區(qū)域的動(dòng)態(tài)化函數(shù)中某變量重復(fù)出現(xiàn)編輯函數(shù)麻煩函數(shù)嵌套固定次數(shù)函數(shù)公式太長(zhǎng)精選2021版課件定義名稱基礎(chǔ)操作2壹5可讀性與可理解性宏應(yīng)用基礎(chǔ)突破嵌套限制代替輔助區(qū)域簡(jiǎn)化公式便于修改定義名稱【定義名稱】定義名稱，就是給數(shù)值、字符串或單元格(組)起的名字。我們可以將一個(gè)或多個(gè)數(shù)值或單元格定義名稱，利用名稱可以實(shí)現(xiàn)很多功能，比如名稱可以帶入公式計(jì)算，名稱可以快速的定位到你想找到的數(shù)據(jù)行。定義名稱也是EXCEL高級(jí)宏應(yīng)用的基礎(chǔ)精選2021版課件定義名稱基礎(chǔ)操作2壹6利用名稱欄直接填寫1利用定義名稱選項(xiàng)2鼠標(biāo)右擊選擇3根據(jù)所選內(nèi)容創(chuàng)建4創(chuàng)建名稱方法精選2021版課件數(shù)據(jù)有效性基礎(chǔ)操作3壹7日期格式不標(biāo)準(zhǔn)數(shù)字亂七八糟輸入重復(fù)內(nèi)容名稱不統(tǒng)一不按規(guī)定統(tǒng)計(jì)只能輸入日期格式只能輸入小數(shù)或整數(shù)不允許重復(fù)輸入只能從下拉選項(xiàng)選擇以固定值開始或結(jié)尾工作中，你是否經(jīng)常統(tǒng)計(jì)一些信息？把表格發(fā)給別人，收回的表格中的數(shù)據(jù)千奇百怪。收上來的數(shù)據(jù)我想要的數(shù)據(jù)精選2021版課件數(shù)據(jù)有效性基礎(chǔ)操作3壹1.下拉式選擇輸入2.只能輸入日期3.只能輸入數(shù)字8精選2021版課件數(shù)據(jù)有效性基礎(chǔ)操作3壹自定義限制內(nèi)

容公

式限制單元格以“A”開頭=LEFT(A1,1)="A"限制單元格以“公司”結(jié)尾=RIGHT(A1,2)="公司"限制單元格第三個(gè)字符為“中”=COUNTIF(A1,"??中*")限制輸入重復(fù)數(shù)據(jù)=COUNTIF($A:$A,A1)=1限制單元格只能輸入數(shù)字=ISNUMBER(A1)限制單元格只能輸入文本=ISTEXT(A1)限制單元格只能輸入日期=YEAR(A1)=YEAR(NOW())9精選2021版課件條件格式基礎(chǔ)操作4壹對(duì)于一定區(qū)域的單元格，按照既定的規(guī)則，將符合條件的單元格的格式自動(dòng)顯示。102問題清單中已經(jīng)解決完的問題1快要到期的應(yīng)收賬款、應(yīng)付賬款5最佳結(jié)果的特別顯示4數(shù)值大小的對(duì)比顯示3超過現(xiàn)金、庫(kù)存的安全范圍精選2021版課件條件格式基礎(chǔ)操作4壹對(duì)于一定區(qū)域的單元格，按照既定的規(guī)則，將符合條件的單元格的格式自動(dòng)顯示。11精選2021版課件條件格式基礎(chǔ)操作4壹12手動(dòng)設(shè)置格式1.有可能選錯(cuò)區(qū)域2.重復(fù)設(shè)置3.條件變了，還得手動(dòng)修改條件格式1.預(yù)設(shè)格式2.不同條件，不同格式3.系統(tǒng)檢索、自動(dòng)修改精選2021版課件小結(jié)基礎(chǔ)操作壹131.左鍵拖動(dòng)2.雙擊3.CTRL+回車4.ALT+=5.CTRL+R6.CTRL+D快速填充1.名稱欄2.定義名稱選項(xiàng)3.右擊命名4.選中內(nèi)容創(chuàng)建表義名稱1.序列2.日期3.小數(shù)4.自定義數(shù)據(jù)有效性1.強(qiáng)調(diào)格式2.底紋顯示條件格式1.空值定位2.常量定位3.公式定位定位條件精選2021版課件函數(shù)應(yīng)用函數(shù)基礎(chǔ)邏輯函數(shù)IS類判斷函數(shù)統(tǒng)計(jì)函數(shù)貳文本函數(shù)日期函數(shù)數(shù)學(xué)函數(shù)查找引用函數(shù)

函數(shù)應(yīng)用-常用幾類函數(shù)操作14精選2021版課件函數(shù)基礎(chǔ)函數(shù)應(yīng)用1運(yùn)算符標(biāo)點(diǎn)函數(shù)字符單元格引用常量Excel函數(shù)【函數(shù)】Excel函數(shù)：是預(yù)先內(nèi)置在excel軟件中用于計(jì)算、分析等數(shù)據(jù)處理的功能模塊=LEFT(A6,FIND("省",A6)-1)=IF(ISODD(MID(B5,9,9)),"男","女")邏輯函數(shù)、IS類函數(shù)、統(tǒng)計(jì)函數(shù)、文本函數(shù)日期函數(shù)、數(shù)學(xué)函數(shù)、查詢和引用函數(shù)15貳精選2021版課件函數(shù)基礎(chǔ)函數(shù)應(yīng)用貳161算數(shù)運(yùn)算符：+、-、*、/、%、^1比較運(yùn)算符：=、>、>=、<、<=、<>2文本運(yùn)算符：&3運(yùn)算優(yōu)先級(jí)^、%、*和/、+和-、&4運(yùn)算符運(yùn)算符精選2021版課件函數(shù)基礎(chǔ)函數(shù)應(yīng)用貳171行數(shù)組1列數(shù)組2一維數(shù)組3多維數(shù)組4數(shù)

組數(shù)組方向區(qū)分維度劃分精選2021版課件函數(shù)基礎(chǔ)函數(shù)應(yīng)用貳181相對(duì)引用絕對(duì)引用混合引用填充引用公式時(shí)，公式中的單元格是變動(dòng)的填充引用公式時(shí)，公式中的單元格是固定的填充引用公式時(shí)，公式中的單元格是行變動(dòng)或列變動(dòng)精選2021版課件邏輯函數(shù)函數(shù)應(yīng)用貳192

AND函數(shù)1

OR函數(shù)2

NOT函數(shù)3

IF函數(shù)4邏輯函數(shù)精選2021版課件邏輯函數(shù)函數(shù)應(yīng)用貳202邏輯值TRUE和FALSE轉(zhuǎn)換成1和0的各種方法(等值數(shù)學(xué)運(yùn)算)邏輯值數(shù)值公式位置TRUE1=--A1“--”在前FALSE0=A2*1“*”前后均可TRUE1=A3/1“/”在后TRUE1=A4-0“-”在后FALSE0=A5+0“+”前后均可FALSE0=N(A6)置于函數(shù)N中精選2021版課件邏輯函數(shù)函數(shù)應(yīng)用貳212總結(jié)規(guī)律：AND可以用*來代替；OR可以用+來代替。函數(shù)返回值函數(shù)返回值=AND(1,1,0)FALSE=AND(1,1,1)TRUE=OR(0,1,1)TRUE=OR(0,1,1)TRUE=NOT(1)FALSE=NOT(0)TRUEAND、OR、NOT函數(shù)：執(zhí)行邏輯判斷，返回TRUE或FALSE。AND（表達(dá)式）、OR（表達(dá)式）、NOT（表達(dá)式）精選2021版課件邏輯函數(shù)函數(shù)應(yīng)用貳222IF函數(shù)：執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。=IF(條件，TRUE，F(xiàn)ALSE）邏輯表達(dá)式條件不成立時(shí)返回的值條件成立時(shí)返回的值精選2021版課件邏輯函數(shù)函數(shù)應(yīng)用貳2323如果判斷標(biāo)準(zhǔn)有漢字內(nèi)容，則在漢字前后加上英文狀態(tài)下的雙引號(hào)""2如果函數(shù)IF的參數(shù)包含數(shù)組，則在執(zhí)行

語句時(shí)，數(shù)組中的每一個(gè)元素都將計(jì)算1OFFICE2003IF函數(shù)可以嵌套七層（07,64層）4一般來說，超過三層的嵌套，就不適合用IF函數(shù)了精選2021版課件IS類判斷函數(shù)函數(shù)應(yīng)用貳243IS類判斷函數(shù)：用來檢驗(yàn)數(shù)值或引用類型的九個(gè)工作表函數(shù)，概括為IS類函數(shù)?？梢詸z驗(yàn)數(shù)值的類型并根據(jù)參數(shù)取值返回TRUE或FALSE。序號(hào)函

數(shù)功

能1ISBLANK(value)值為空白單元格2ISERR(value)值為任意錯(cuò)誤值（除去#N/A）3ISERROR(value)值為任意錯(cuò)誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）4ISLOGICAL(value)值為邏輯值5ISNA(value)值為錯(cuò)誤值#N/A（值不存在）6ISNONTEXT(value)值為不是文本的任意項(xiàng)（注意此函數(shù)在值為空白單元格時(shí)返回TRUE）7ISNUMBER(value)值為數(shù)字8ISREF(value)值為引用9ISTEXT(value)值為文本精選2021版課件統(tǒng)計(jì)函數(shù)函數(shù)應(yīng)用貳254

MIN/MAX函數(shù)1

COUNT函數(shù)2

COUNTA函數(shù)3

COUNTIF函數(shù)4統(tǒng)計(jì)函數(shù)精選2021版課件統(tǒng)計(jì)函數(shù)函數(shù)應(yīng)用貳264MIN/MAX函數(shù)：找出系列數(shù)據(jù)中的最小/大值。MIN(number1,number2,...)MAX(number1,number2,...)MIN(數(shù)據(jù)區(qū)域或名稱)MAX(數(shù)據(jù)區(qū)域或名稱)最多30個(gè)數(shù)字通常做法精選2021版課件統(tǒng)計(jì)函數(shù)函數(shù)應(yīng)用貳274COUNT、COUNTA、COUNTIF函數(shù)COUNT求某區(qū)域數(shù)字單元格個(gè)數(shù)，非數(shù)字單元格不統(tǒng)計(jì)。求某區(qū)域非空單元格個(gè)數(shù)。求空單元格個(gè)數(shù)的是COUNTBLANK可以用在動(dòng)態(tài)數(shù)據(jù)表中。求一定區(qū)域中，符合條件的單元格個(gè)數(shù)?？梢允褂猛ㄅ浞?；英文字母不區(qū)分大小寫。COUNTACOUNTIF精選2021版課件文本函數(shù)函數(shù)應(yīng)用貳285

LEFT/RIGHT/MID函數(shù)1

LEN&LENB函數(shù)2

FIND函數(shù)3

SEARCH函數(shù)4文本函數(shù)精選2021版課件文本函數(shù)函數(shù)應(yīng)用貳295LEFTRIGHTMIDFINDSEARCH從字符串的左側(cè)、右側(cè)、中間截取指定長(zhǎng)度的字符。LENLENB計(jì)算字符串的字符或字節(jié)數(shù)。查找字符在字符串中的位置。返回從開始首次找到特定字符或文本字符串的位置上特定字符的編號(hào)組合使用精選2021版課件日期函數(shù)函數(shù)應(yīng)用貳306

YEAR/MONTH/DAY函數(shù)1

DATE函數(shù)2

EDATE/EMONTH函數(shù)3

DATEDIF函數(shù)4日期函數(shù)精選2021版課件日期函數(shù)函數(shù)應(yīng)用貳316在excel中，把日期數(shù)據(jù)視為一種數(shù)值的特殊表現(xiàn)形式。(可以說一個(gè)日期一定對(duì)應(yīng)一個(gè)數(shù)字，但一個(gè)數(shù)字不一定對(duì)應(yīng)一個(gè)日期)。數(shù)字1400001000029584652958466轉(zhuǎn)為日期1900-1-12009-7-61927-5-189999-12-31##############精選2021版課件日期函數(shù)函數(shù)應(yīng)用貳326提取數(shù)據(jù)DAY(表達(dá)式)提取日期組合數(shù)據(jù)MONTH(表達(dá)式)提取月份YEAR(表達(dá)式)提取年份DATE(YYYY,MM,DD)將三個(gè)數(shù)字組合為日期函數(shù)yearmonthdaydateeomonth日期年月日合并日期月份天數(shù)2005-8-72005872005-8-7312011-5-62011562011-5-6311985-12-15198512151985-12-15311999-4-2519994251999-4-2530精選2021版課件日期函數(shù)函數(shù)應(yīng)用貳336非標(biāo)準(zhǔn)日期格式如何轉(zhuǎn)化為標(biāo)準(zhǔn)日期格式1432Excel中的標(biāo)準(zhǔn)日期格式是用“/”或“-”連接顯示的數(shù)值型字段。但我們工作中有不同的使用習(xí)慣，如寫成：20131122、2013.11.22，也不是真正的日期。遇到非標(biāo)準(zhǔn)日期，如何轉(zhuǎn)化為標(biāo)準(zhǔn)日期呢？YYYYMMDD型非標(biāo)準(zhǔn)日期格式Y(jié)YYY.MM.DD型非標(biāo)準(zhǔn)日期格式Y(jié)YYY-MM-DD型非標(biāo)準(zhǔn)日期格式什么格式都有精選2021版課件日期函數(shù)函數(shù)應(yīng)用貳346銀行對(duì)賬單的處理精選2021版課件日期函數(shù)函數(shù)應(yīng)用貳356EDATE、EMONTH、DATEDIF函數(shù)EDATE計(jì)算指定月數(shù)之前或之后的幾個(gè)月的日期。計(jì)算指定日期之前或之后的幾個(gè)月的特定月份的月底的日。計(jì)算兩個(gè)日期之間的年數(shù)、月數(shù)和天數(shù)。EMONTHDATEDIF精選2021版課件數(shù)學(xué)函數(shù)函數(shù)應(yīng)用貳367

ROUND函數(shù)1

RAND&RANDBETWEEN函數(shù)2

SUMIF函數(shù)3

SUMIFS函數(shù)4數(shù)學(xué)函數(shù)

SUMPRODUCT函數(shù)5精選2021版課件數(shù)學(xué)函數(shù)函數(shù)應(yīng)用貳377如果num_digits大于0，則四舍五入到指定的小數(shù)位。如果num_digits等于0，則四舍五入到最接近的整數(shù)。如果num_digits小于0，則在小數(shù)點(diǎn)左側(cè)進(jìn)行四舍五入。ROUND函數(shù)：返回某個(gè)數(shù)字按指定位數(shù)保留小數(shù)后的數(shù)字=ROUND(number,num_digits)精選2021版課件數(shù)學(xué)函數(shù)函數(shù)應(yīng)用貳387ROUNDUP函數(shù)：遠(yuǎn)離零值，向上舍入數(shù)字。ROUNDDOWM函數(shù)：靠近零值，向下（絕對(duì)值減小的方向）舍入數(shù)字。數(shù)字位數(shù)ROUND取數(shù)ROUNDUP取數(shù)ROUNDDOWN取數(shù)4.999055489.455189.589.589.4-673.976-1-670-680-670870.342-2900900800精選2021版課件數(shù)學(xué)函數(shù)函數(shù)應(yīng)用貳397SUMIFSUMPRODUCT單條件求和。SUMIFS多條件求和，不可以對(duì)查找區(qū)域進(jìn)行函數(shù)處理。多條件求和，可以對(duì)查找區(qū)域進(jìn)行函數(shù)處理。=SUMIF(查找區(qū)域，關(guān)鍵字，求和區(qū)域)=SUMIFS(求和區(qū)域，查找區(qū)域，關(guān)鍵字，查找區(qū)域，關(guān)鍵字，…)=SUMPRODUCT(條件1*條件2*…*求和區(qū)域)精選2021版課件查找引用函數(shù)函數(shù)應(yīng)用貳408

LOOKUP函數(shù)1

VLOOKUP函數(shù)2

HLOOKUP函數(shù)3

C函數(shù)4查找函數(shù)精選2021版課件查找引用函數(shù)函數(shù)應(yīng)用貳418LOOKUP函數(shù)：在表格數(shù)組的首列查找指定的值，并由此返回表格數(shù)組當(dāng)前行中其他列的值。查找區(qū)域須升序排列。精選2021版課件查找引用函數(shù)函數(shù)應(yīng)用貳428VLOOKUP函數(shù)：在指定表格數(shù)組的首列查找指定的值，并由此返回表格數(shù)組當(dāng)前行中其他列的值。=VLOOKUP(查找值，查找區(qū)域，返回位置（列），查找方式)HLOOKUP與VLOOKUP是一種轉(zhuǎn)置關(guān)系精選2021版課件查找引用函數(shù)函數(shù)應(yīng)用貳438ROW：返回單元格或區(qū)域的行號(hào)COLUMN：返回單元格或區(qū)域的列號(hào)ROWS：計(jì)算行數(shù)COLUMNS：計(jì)算列數(shù)小技巧ROW、COLUMN、ROWS、COLUMNS函數(shù)在相對(duì)引用中的應(yīng)用。精選2021版課件查找引用函數(shù)函數(shù)應(yīng)用貳448查找結(jié)果是#N/A，怎么回事？用TRIM函數(shù)去掉空格數(shù)據(jù)有空格數(shù)值型和文本型的轉(zhuǎn)換格式不一致不想顯示#N/A確實(shí)找不到精選2021版課件小結(jié)函數(shù)應(yīng)用451.函數(shù)概念2.運(yùn)算符3.數(shù)組4.相對(duì)/絕對(duì)引用函數(shù)基礎(chǔ)1.IF2.AND3.OR4.NOT邏輯函數(shù)1.ISBLANK2.ISERR3.ISNUMBER4.ISTEXTIS類函數(shù)1.MAX2.MIN3.COUNTA4.COUNTIF統(tǒng)計(jì)函數(shù)貳精選2021版課件小結(jié)函數(shù)應(yīng)用461.LEFT/RIGHT2.MID3.LEN/LENB4.FIND5.SEARCH文本函數(shù)1.YAER/MONTH2.DAY/DATE3.EDATE4.EMONTH5.DATEDIF日期函數(shù)1.ROUND2.RANDBETWEEN3.SUMIF4.SUMIFS5.SUMPRODUCT數(shù)學(xué)函數(shù)1.LOOKUP2.VLOOKUP3.HLOOKUP4.SEARCH查找函數(shù)貳精選2021版課件數(shù)據(jù)透視表數(shù)據(jù)源表格要求數(shù)據(jù)透視匯總多個(gè)數(shù)據(jù)源繪制存貨ABC分類圖（折柱圖）叁數(shù)據(jù)透視表-數(shù)據(jù)整理的有效工具47精選2021版課件數(shù)據(jù)表格要求數(shù)據(jù)透視表叁481數(shù)據(jù)表格要求數(shù)據(jù)區(qū)域動(dòng)態(tài)化不要合并單元格明細(xì)中不要有空白單元格首行之下為數(shù)據(jù)明細(xì)首行為各字段臺(tái)頭列有標(biāo)行有數(shù)無空無合最奇妙精選2021版課件數(shù)據(jù)表格要求數(shù)據(jù)透視表叁491銷售日期商品名稱銷售量（臺(tái)）銷售收入（元）銷售地點(diǎn)銷售人員2010-1-15彩電4954,891北京李悅2010-1-20空調(diào)3997,768廣州袁濤2010-1-25冰箱4583,160上海王華2010-2-3彩電7268,256北京李玲2010-2-10微波爐2011,760長(zhǎng)春袁龍2010-2-19微波爐3318,150廣州程靜2010-2-22電腦53239,613天津李穎2010-3-1彩電7070,000上海王華2010-3-11空調(diào)3783,324太原戴軍2010-3-16電腦78373,386北京李悅2010-3-25冰箱79146,624沈陽楊梅2010-4-3彩電2018,120天津劉立2010-4-18電腦64266,048廣州袁濤2010-4-26彩電7267,896北京李玲示例：精選2021版課件數(shù)據(jù)透視數(shù)據(jù)透視表叁502年度月份銷售收入累計(jì)銷售收入環(huán)比增加額環(huán)比增長(zhǎng)率2010年1月

235,819

235,819

2月

337,779

573,598

101,96043%3月

673,334

1,246,932

335,55599%4月

394,544

1,641,476

-278,790-41%5月

601,264

2,242,740

206,72052%6月

94,818

2,337,558

-506,446-84%7月

603,974

2,941,532

509,156537%8月

394,190

3,335,722

-209,784-35%9月

523,254

3,858,976

129,06433%10月

577,445

4,436,421

54,19110%11月

486,799

4,923,220

-90,646-16%12月

455,207

5,378,427

-31,592-6%2011年1月

230,342

230,342

2月

76,857

307,199

-153,485-67%3月

798,732

1,105,931

721,875939%4月

195,585

1,301,516

-603,147-76%5月

410,649

1,712,165

215,064110%6月

482,006

2,194,171

71,35717%7月

175,082

2,369,253

-306,924-64%8月

29,233

2,398,486

-145,849-83%10月

225,301

2,623,787

196,068671%11月

25,388