Excel操作技巧與函數高效應用(學員版)課件

Excel操作技巧及函數高效應用1精選2021版課件

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

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

基礎操作-實用小技巧

函數應用-常用幾類函數操作

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

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

容公

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

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

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

AND函數1

OR函數2

NOT函數3

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

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

數功

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

MIN/MAX函數1

COUNT函數2

COUNTA函數3

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

LEFT/RIGHT/MID函數1

LEN&LENB函數2

FIND函數3

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

YEAR/MONTH/DAY函數1

DATE函數2

EDATE/EMONTH函數3

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

ROUND函數1

RAND&RANDBETWEEN函數2

SUMIF函數3

SUMIFS函數4數學函數

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

LOOKUP函數1

VLOOKUP函數2

HLOOKUP函數3

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