Excel常用工具分享

供應(yīng)鏈計劃部2015.5.29Excel常用工具分享二1大綱一數(shù)據(jù)透視表二兩軸折線圖三公式四函數(shù)五案例2數(shù)據(jù)透視表備注:使用數(shù)據(jù)透視表的前提:1.必須有表頭,且標題不能為空不能有重復(fù).2.整張工作表不能有合并單元格存在.數(shù)據(jù)透視表數(shù)據(jù)透視表備注:當(dāng)原數(shù)據(jù)更改時,已做的數(shù)據(jù)透視不會變.兩軸折線圖兩軸折線圖圖例兩軸折線圖兩軸折線圖步驟一—位置兩軸折線圖兩軸折線圖步驟二—成型兩軸折線圖兩軸折線圖步驟三—選擇次坐標軸兩軸折線圖兩軸折線圖步驟四—更改圖表類型兩軸折線圖兩軸折線圖步驟五—選擇折線圖兩軸折線圖兩軸折線圖步驟六—兩軸折線圖兩軸折線圖兩軸折線圖步驟七—美化,加數(shù)據(jù)公式EXCEL中最強大最有用的功能,也是有別與WORD或者PPT的所在,這個功能對于分析數(shù)據(jù)趨向來說,是一個無可取代的存在.公式一.單元格備注:每個單元格的行和列的交叉構(gòu)成單元格的位置活動單元格公式二.單元格的引用備注:每個單元格的行和列的交叉構(gòu)成單元格的位置引用格式對應(yīng)的區(qū)域D7D列和第7得交叉處的單元格F1:F124F列中第1行到124行的單元格Q:QQ列全部單元格F:GF列到G列的全部單元格102:102第102行的全部單元格3:10第3行到10行的全部單元格C1:E5C列第1行到E列第5行的單元格公式二.單元格的引用備注:三種引用是對于單元格位置而言,可以省掉反復(fù)重寫僅行號列標順序改變的公式的時間相對引用絕對引用混合引用公式三.什么是公式備注:一個完整的公式中可以沒有值或常量,但一定要有運算符運算符+單元格引用+值或常量+工作表函數(shù)=IF(F5>=3000,”大單”,”小單”)類型名稱運算符功能算術(shù)加號+加減號-減及負數(shù)斜杠/除乘號*乘百分號%顯示為百分比脫字符^乘方比較等號=等于大于號>大于小于號<小于大于等于號>=大于或等于小于等于號<=小于或等于不等于號<>不等于文本文本串聯(lián)符&將兩個或多個字符連接起來引用冒號:引用單元格區(qū)域逗號,合并多個單元格區(qū)域空格

取兩個單元格區(qū)域的交集優(yōu)先級運算符1冒號2空格3逗號4百分號5脫字符6乘號或除號7加號或減號8文本運算符9比較運算符公式函數(shù)一.什么是函數(shù)備注:1.參數(shù)與參數(shù)之間使用半角逗號進行分隔.當(dāng)以數(shù)組為參數(shù)時,各列的數(shù)值用逗號隔開,各行的數(shù)值用分號隔開.2.手工輸入函數(shù)公式時需確定當(dāng)前的輸入狀態(tài)為半角狀態(tài).函數(shù)就是預(yù)先定義好的特殊公式,通過使用一些稱之為參數(shù)的特定數(shù)值來完成特定的計算,分析等處理數(shù)據(jù)的任務(wù).IF(logical-test,value-if-true,value-if-false)函數(shù)名參數(shù)1參數(shù)2參數(shù)3嵌套函數(shù):IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)函數(shù)二.函數(shù)類型備注:函數(shù)類型說明函數(shù)符號或其他加載宏和自動化函數(shù)創(chuàng)建用戶自定義函數(shù)VisualBasicforApplications多維數(shù)據(jù)集函數(shù)從AmalysisServices中提取OLAP數(shù)據(jù).CUBEKPIMEMBER等數(shù)據(jù)庫函數(shù)分析數(shù)據(jù)清單中的數(shù)值是否符合特定條件DDAVERAGE,DCOUNT,DCOUNTA等日期和時間函數(shù)在公式中分析和處理日期值和時間值.DATE,DAY,EDATE等工程函數(shù)工程分析,在不同的數(shù)字系統(tǒng)(十進制,八進制等)間進行數(shù)值轉(zhuǎn)換等BESSELK等財務(wù)函數(shù)一般財務(wù)計算ACCRINT等信息函數(shù)確定單元格中數(shù)據(jù)的類型等INFO,CELL等邏輯函數(shù)進行真假判斷IF,AND,OR等查找和引用函數(shù)查找特定數(shù)值VLOOKUP,CHOOSE,AREAS等數(shù)學(xué)和三角函數(shù)處理簡單的計算ABS,ACOS,ACOSH等統(tǒng)計函數(shù)對數(shù)據(jù)區(qū)域進行統(tǒng)計分析AVEDEV等文本函數(shù)在公式中處理文字串ASC,BAHTTEXT,CHAR等函數(shù)三.EXCEL返回的錯誤類型備注:當(dāng)公式錯誤時,原因不僅僅只是上述內(nèi)容,建議當(dāng)公式錯誤時,查回所用函數(shù)的格式,一般找到原因.錯誤提示錯誤原因#######列不夠?qū)捇蚴褂昧素摰娜掌诨蜇摰臅r間#VALUE!使用的參數(shù)或操作數(shù)類型錯誤,比如錯輸入文本,編輯數(shù)組卻按了[ENTER]鍵等#DIV/O!數(shù)字被0除,如=5/0,使用空白單元格或包含0的單元格引用做除數(shù)#NAME?EXCEL未識別公式中的文本,例如函數(shù)名拼寫錯,輸入文本時沒用雙引號等.#N/A數(shù)值對函數(shù)或公式不可用,使用VLOOKUP,HLOOKUP,MATCH工作函數(shù)來定位值前沒排序.#REF!單元格引用無效,如引用的單元格被刪除函數(shù)344?500?EXCEL中有多少個函數(shù)?函數(shù)四.常用函數(shù)—數(shù)學(xué)和三角函數(shù)備注:1.按條件對指定單元格求和.2.第一參數(shù)和第三參數(shù)只能是單元格,區(qū)域引用,不能是數(shù)組.3.第二參數(shù)如果用了等于某條件.可以使用通配符:問號(?)和星號(*),問號匹配任意單個字符,星號匹配任意一串字符.SUMIF格式:SUMIF(range,critaria,sum-range)函數(shù)四.常用函數(shù)—數(shù)學(xué)和三角函數(shù)備注:1.返回某一單元格區(qū)域中所有數(shù)字之和.2.如果參數(shù)為錯誤值或為不能轉(zhuǎn)換為數(shù)字的文本,將會導(dǎo)致錯誤.3.如果還有更多條件,可同時羅列,用星號連接,按[Ctrl+Shift+Enetr]組合鍵后,顯示結(jié)果.SUM格式:SUM(number1,number2,…)函數(shù)四.常用函數(shù)—數(shù)學(xué)和三角函數(shù)備注:返回數(shù)字的絕對值A(chǔ)BS格式:ABS(number)函數(shù)四.常用函數(shù)—邏輯函數(shù)備注:1.根據(jù)指定的條件返回不同的結(jié)果.2.他用嵌套IF時,括號一定要匹配,有時會因為漏掉右括號而生產(chǎn)錯誤.IF格式:IF(Logical-test,value-true,value-if-false)函數(shù)四.常用函數(shù)—邏輯函數(shù)備注:1.判斷指定的多個條件是否全部成立.2.參數(shù)最多可以有255個.3.當(dāng)所列的條件全部滿足時,返回TURE,只要有一個條件不滿足,則返回FALSE.AND格式:AND(Logical1,logical2,…)函數(shù)四.常用函數(shù)—邏輯函數(shù)備注:1.當(dāng)指定的任一條件為真時,即返回真2.參數(shù)必須能計算為邏輯值,或者為包含邏輯值的數(shù)組或引用.3.如果參數(shù)包含直接輸入的非邏輯值,如文本,空白,空單元格等,將產(chǎn)生錯誤值#VALUE!.OR格式:OR(logical1,logical2,…)函數(shù)四.常用函數(shù)—文本函數(shù)備注:1.返回文本字符串中的字符個數(shù)2.不支持單元格區(qū)域引用,否則返回錯誤值.3.直接引用字符串時,要用半角雙引號將字符串括起來.4.始終將每個字符(不管是雙字節(jié)還是單字節(jié))按1計長度,比如單字節(jié)”a”和雙字節(jié)”啊”的長度都為1.LEN格式:LEN(text)函數(shù)四.常用函數(shù)—文本函數(shù)備注:1.替換字符串中的部分字符(根據(jù)字符數(shù)).2.第一參數(shù)是要替換期部分字符的文本.3.第二參數(shù)是待替換字符的起始位置.4.第三參數(shù)是被替換字符串的長度,如果忽略,相當(dāng)于輸入新字符串.5.第四參數(shù)是替換后的新字符串.REPLACE格式:REPLACE(old-text,start-num,num-chars,new-text)函數(shù)四.常用函數(shù)—文本函數(shù)備注:1.查找字符串起始位置(不區(qū)分大小寫)2.第一參數(shù)表示要查找的文本或文本所在的單元格,如果直接輸入文本,需加雙引號,可用通配符.3.第二參數(shù)包含要查找的文本或文本所在的單元格.4.第三參數(shù)指定從哪個字節(jié)開始搜索字符,如果省略,則假設(shè)其值為1.SEARCH格式:SEARCH(find-text,within-text,start-num)函數(shù)四.常用函數(shù)—文本函數(shù)LEFT格式:LEFT(text,num-chars)備注:1.返回文本值中最左邊的字符.2.當(dāng)?shù)谝粎?shù)是直接引用輸入字符串時,需用半角的雙引號將字符串括起來.3.第二參數(shù)必須大于或等于零.4.第二參數(shù)大于文本長度時,返回全部文本,省略時則假設(shè)值為1.函數(shù)四.常用函數(shù)—文本函數(shù)MID格式:MID(text,start-num,num-chars)備注:1.從文本字符串的指定位置起返回特定個數(shù)的字符.2.第一參數(shù)表示要提取字符的文本字符串,也可以是單元格引用.3.第二參數(shù)表示字符串中要提取字符的起始位置,如果大于文本長度,返回空文本.4.第三參數(shù)表示MID從文本中返回字符的個數(shù),如果是負數(shù),則返回錯誤值.函數(shù)四.常用函數(shù)—文本函數(shù)MIDB格式:MIDB(text,start-num,num-bytes)備注:1.從文本字符串的指定位置起返回特定個數(shù)的字符.2.用法與MID相似,但,MID以字符為單位,而MIDB以字節(jié)為單位.3.半角字符為1個字節(jié),漢字是2個字節(jié).函數(shù)四.常用函數(shù)—文本函數(shù)FIND格式:FIND(find-text,within-text,start-num)備注:1.查找字符串起始位置(區(qū)分大小寫).2.不允許使用通配符.3.第三參數(shù)如果大于所查找的字符的位置時,返回錯誤值.4.一般情況下忽略第三參數(shù),假設(shè)其值為1.函數(shù)四.常用函數(shù)—文本函數(shù)TEXT格式:TEXT(value,format-text)備注:1.設(shè)置數(shù)字格式并將其轉(zhuǎn)換為文本.2.第二參數(shù)不能包含”*”.3.其結(jié)果將不再作為數(shù)字參與計算.函數(shù)四.常用函數(shù)—統(tǒng)計函數(shù)AVERAGEA格式:AVERAGEA(value1,value2,…)備注:1.計算參數(shù)列表中數(shù)值的平均值.2.邏輯值和直接輸入到參數(shù)列表中代表數(shù)字的文本納入計算.3.參數(shù)中的TURE作為1計算,FALSE作為0計算,數(shù)組,引用參數(shù),空文本作為0計算.4.當(dāng)按某條件計算平均值時,需按[ctrl+shift+enter].

函數(shù)四.常用函數(shù)—統(tǒng)計函數(shù)AVERAGEIFS格式:AVERAVEIFS(average-range,criteria-rangel,criteria1,criteria-range2,criteria2,…)備注:1.返回滿足多重條件的所有單元格的平均值.2.第一參數(shù)為實際求和區(qū)域.3.第二參數(shù)為包含條件的區(qū)域.4.第三參數(shù)則是第一個條件,在其之后可繼續(xù)設(shè)置條件,可設(shè)1-127個條件.函數(shù)四.常用函數(shù)—統(tǒng)計函數(shù)COUNT格式:COUNT(value1,value2,…)備注:1.返回參數(shù)列表中的數(shù)字個數(shù).2.數(shù)字參數(shù),日期參數(shù)或代表數(shù)字的文本參數(shù),邏輯值都會被計算.3.如果參數(shù)是錯誤值,不能轉(zhuǎn)換為數(shù)字的文本,空單元格,將會被忽略.

函數(shù)四.常用函數(shù)—統(tǒng)計函數(shù)COUNTIF格式:COUNTIF(range,criteria)備注:1.計算區(qū)域中滿足給定條件的單元格的個數(shù).2.第二參數(shù)中可以使用通配符.函數(shù)四.常用函數(shù)—統(tǒng)計函數(shù)MAX格式:MAX(number1,number2,…)備注:1.返回一組值中的最大值.2.如果參數(shù)包含數(shù)字,返回0.

函數(shù)四.常用函數(shù)—統(tǒng)計函數(shù)MIN格式:MIN(value1,value2,…)備注:1.返回參數(shù)中的最小值.2.如果參數(shù)中不含數(shù)字,返回0.

函數(shù)四.常用函數(shù)—統(tǒng)計函數(shù)COUNTIFS格式:COUNTIFS(criteria-range1,criteria1,[criteria-range2,criteria2]…)備注:1.計算區(qū)域內(nèi)符合多個條件的單元格的數(shù)量.2.本函數(shù)僅適用于Excel2007和Excel2010.3.支持通配符為作條件.4.條件可以使用比較運算符,如>,<,>=…函數(shù)四.常用函數(shù)—日期和時間函數(shù)NOW格式:NOW()備注:1.返回當(dāng)前的日期和時間.2.該函數(shù)沒有參數(shù),但必須要有括號(),不論括號中輸入什么參數(shù),都返回錯誤值.3.返回值和該函數(shù)所在單元格的格式有關(guān),常規(guī)格式下序列號,自定義格式下顯示日期和時間格式.

函數(shù)四.常用函數(shù)—日期和時間函數(shù)TODAY格式:TODAY()備注:1.返回當(dāng)前日期.2.該函數(shù)沒有參數(shù),但必須要有括號(),不論括號中輸入什么參數(shù),都返回錯誤值.3.TODAY返回的日期可以進行數(shù)字運算.4.使用此函數(shù)時,必須檢查控制面板的日期設(shè)置是否準確,否則運算結(jié)果可能出錯.函數(shù)四.常用函數(shù)—日期和時間函數(shù)MONTH格式:MONTH(serial-number)備注:1.返回某日期對應(yīng)的月份.2.不論日期以何種日期格式顯示,都可以返回值.3.如果引用的單元格是非日期的形式,則返回錯誤值.

函數(shù)四.常用函數(shù)—日期和時間函數(shù)DAY格式:DAY(serial-number)備注:1.返回某日期對應(yīng)當(dāng)月的天數(shù).2.不論日期以何種日期格式顯示,都可以返回值.3.如果引用的單元格是非日期的形式,則返回錯誤值.4.只用于標準日期格式的計算,如果使用”2008,9,25”或”2008\9\25”格式,則返回錯誤值.函數(shù)四.常用函數(shù)—日期和時間函數(shù)WEEKDAY格式:WEEKDAY(serial-number,return-type)備注:1.返回某日為星期幾.2.第一參數(shù)如果是日期以外的文本,則返回錯誤值.3.第二參數(shù)確定返回類型的數(shù)字,具體類型見上右圖.

第二參數(shù)返回值1或省略數(shù)字1(星期日)到數(shù)字7(星期六)2數(shù)字1(星期一)到數(shù)字7(星期日)3數(shù)字1(星期一)到數(shù)字6(星期日)函