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

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

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

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

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

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

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

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

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