Excel常用工具分享

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

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

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

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

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

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

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

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

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