1、Excel函數(shù)及統(tǒng)計圖表應(yīng)用技巧,議程,編輯公式 常用函數(shù)的運用 函數(shù)的高級使用 數(shù)據(jù)的處理技巧 數(shù)據(jù)的整理 數(shù)據(jù)透視表中的計算,在Excel中編輯公式,在Excel中編輯公式,點擊等號（=）開始編輯公式 在公式中可以使用加、減、乘、除四則混合運算，括號、乘方（2表示2次方）等 公式復(fù)制過程中存在的問題 公式復(fù)制和引用關(guān)系 相對引用和絕對引用 在公式中按F4鍵可以在相對引用和絕對引用之間進行切換,追蹤單元格引用,公式公式審核 追蹤引用的單元格 追蹤從屬單元格 追蹤單元格引用錯誤,常用函數(shù)的運用,數(shù)學(xué)函數(shù),SUM、Average 加權(quán)平均的計算 MAX、MIN Round、RoundUp、Rou

2、ndDown 四舍五入函數(shù) Rank 排名函數(shù),文本函數(shù),字符運算 使用“&”符號連接字符 Len 計算字符串長度 Left、Mid、Right截取字符串函數(shù) 例：根據(jù)身份證號計算生日,日期函數(shù)的使用,條件判斷函數(shù)- IF,使用邏輯判斷函數(shù)進行條件判斷 獎金、稅金的計算,常用統(tǒng)計函數(shù)的使用,Sumif 對滿足指定條件的數(shù)值求和 CountIF 計算區(qū)域中滿足給定條件的單元格的個數(shù),函數(shù)的高級使用,高級查詢函數(shù)的使用,VLOOKUP( )函數(shù) 在表格的最左列查找某個數(shù)值，并返回該值右邊所對應(yīng)的第N列的數(shù)值 Vlookup(要查找的值，范圍，要顯示的內(nèi)容所在列數(shù)，查找類型) HLOOKUP( )函

3、數(shù) 在表格的首行查找指定的數(shù)值，并由此返回該值下方所對應(yīng)的第N行的數(shù)值,查詢函數(shù)的運用,Match 在一組數(shù)據(jù)中查找指定值所處的位置 Match函數(shù)和Vlookup函數(shù)結(jié)合使用 注意$(絕對引用)的使用,解決函數(shù)運算錯誤問題,Iserror 函數(shù) 如果值為任何錯誤值，則返回 TRUE Iserr 函數(shù) 如果值為除 #N/A 以外的任何錯誤值，則返回 TRUE,財務(wù)函數(shù),折舊函數(shù)： SLB直線折舊法 DB單倍余額折舊法 DDB雙倍余額折舊法,財務(wù)函數(shù),投資函數(shù)： PMT 基于固定利率及等額分期付款方式，返回貸款的每期付款額 PV 返回投資的現(xiàn)值 NPV 通過使用貼現(xiàn)率以及一系列未來支出（負值）和

4、收入（正值），返回一項投資的凈現(xiàn)值 FV 基于固定利率及等額分期付款方式，返回某項投資的未來值,數(shù)據(jù)的處理技巧,公式的反推,為公式指定目標(biāo)值求解 工具單變量求解 ToolGoal Seek,目標(biāo)值預(yù)測,根據(jù)現(xiàn)有公式，隨變量的變化，對目標(biāo)值變化的預(yù)測。 數(shù)據(jù)(Data)模擬運算表(Table) 單變量預(yù)測 雙變量預(yù)測,表格的基本操作,選擇區(qū)域 用鼠標(biāo)直接拖動選中矩形區(qū)域 在行列標(biāo)記上單擊選中整行整列 再拖動鼠標(biāo)的同時按住Ctrl鍵選擇不連續(xù)區(qū)域 按快捷鍵Ctrl+Shift+8（或Ctrl+*）可以選中當(dāng)前單元格周圍連續(xù)的矩形區(qū)域 按住Shift鍵可以選中兩點之間區(qū)域,選擇表格續(xù),快速移動光標(biāo)

5、Ctrl+home 將光標(biāo)移動到第一個單元格 Ctrl+End 將光標(biāo)移動到表格最后一個單元格 Ctrl+ 將光標(biāo)移動到當(dāng)前區(qū)域最右列(其他方向箭頭依此類推) 則Ctrl+Shift+向下箭頭,選擇當(dāng)前單元格至當(dāng)前區(qū)域最后一行的內(nèi)容,表格的編輯和調(diào)整,鼠標(biāo)拖動數(shù)據(jù)拖動表格邊框 直接拖拽邊框是移動； 按住CTRL鍵拖拽相當(dāng)于復(fù)制操作； 按ALT鍵拖拽在其他Sheet間移動； 按SHIFT鍵拖拽將表格從原位置移出并插入到目標(biāo)位置。（可用于交換兩列位置）,表格的編輯和調(diào)整,序列填充 填充柄 ：選中區(qū)域右下角的黑點 拖拽填充柄可實現(xiàn)序列填充，如：January, February, March等 一切

6、和數(shù)字有關(guān)的數(shù)據(jù)都有序列功能 反向拖動填充柄相當(dāng)于刪除 自定義填充序列,表格的編輯和調(diào)整,鼠標(biāo)拖動調(diào)整寬高 調(diào)整行高列寬，直接拖動行列標(biāo)記間分割線 同時選中多行或多列，調(diào)整其中任意一列（行）的寬度，其他各列（行）同時改變 雙擊行列標(biāo)記間分割線可根據(jù)內(nèi)容自動調(diào)整 精確調(diào)整寬高 格式行行高 格式列列寬,數(shù)據(jù)的整理,正確的錄入數(shù)據(jù),正確的數(shù)字格式 設(shè)置數(shù)字貨幣格式 日期型數(shù)字格式（非日期型格式靠分列進行轉(zhuǎn)換） 文本型數(shù)字格式（以單引號開頭輸入文本） 自定義數(shù)字格式 單元格格式快捷鍵 Ctrl+1,將規(guī)則文本導(dǎo)入EXCEL,Data Column to Text 如果文本文件中的數(shù)據(jù)以制表符、冒號、分

7、號或其他字符分隔，請選擇“分隔符號”。 如果文本文件中的所有項的長度相同，請選擇“固定寬度”。 可以手動調(diào)整分列的位置,也可以跳過某列而不導(dǎo)入.,表格的編輯和調(diào)整,剪切、復(fù)制、粘貼的使用 剪貼板的使用 選擇性粘貼 數(shù)值,格式,公式,列寬 操作:加,減,乘,除 轉(zhuǎn)置:行列互換,套用表格格式,套用表格樣式 操作:選中表體,開始樣式套用表格格式,30,豐富的條件格式,開始條件格式 什么是條件格式 將滿足特定條件的數(shù)據(jù)以指定格式顯示 使用條件格式顯示單元格的數(shù)據(jù) 例：將表格中大于500的數(shù)字以紅色顯示 目的：突出顯示滿足某個條件數(shù)據(jù) 可以設(shè)置顏色、圖標(biāo)等 可以使用顏色,圖標(biāo)集等作為條件的區(qū)分 最多可以

8、設(shè)置64種格式,管理條件格式規(guī)則,根據(jù)單元格值判斷 判斷單元格唯一性與重復(fù) 使用公式：活動單元格不能直接參與判斷時使用公式 調(diào)整規(guī)則順序,定義數(shù)據(jù)驗證,數(shù)據(jù)有效性 定義有效數(shù)據(jù)范圍：數(shù)值、日期、序列 設(shè)置輸入信息 設(shè)置出錯警告 圈釋無效數(shù)據(jù),創(chuàng)建下拉列表,數(shù)據(jù) 有效性設(shè)置 允許列表 數(shù)據(jù)源：選擇下拉菜單數(shù)據(jù)源 如果數(shù)據(jù)源在另外一個sheet則需要先給數(shù)據(jù)源命名,對數(shù)據(jù)表格的頁面設(shè)置,頁面設(shè)置 頁面布局頁面設(shè)置方向、縮放比例、頁邊距 分頁預(yù)覽視圖下調(diào)整打印范圍 視圖分頁預(yù)覽 打印重復(fù)標(biāo)題行： 頁面設(shè)置工作表:打印重復(fù)標(biāo)題行，打印重復(fù)標(biāo)題列 打印選項 打印順序,打印數(shù)據(jù)表格,Office按鈕打印

9、打印范圍 打印內(nèi)容 打印選擇區(qū)域 打印當(dāng)前工作表 打印整個工作薄 打印份數(shù),數(shù)據(jù)窗口的控制,凍結(jié)窗格 視圖窗口凍結(jié)窗格 拆分窗口 視圖窗口拆分窗口 新建并并排查看窗口 視圖窗口并排查看 組合特定區(qū)域 數(shù)據(jù)分級顯示組合,數(shù)據(jù)的安全性,保護工作薄 保護工薄的窗口和結(jié)構(gòu) 設(shè)置文件的打開和修改口令 保護工作表 整張工作表的保護 部分數(shù)據(jù)區(qū)域的保護 高級數(shù)據(jù)區(qū)域保護,Excel 數(shù)據(jù)處理技巧,表格的基本操作,選擇區(qū)域 用鼠標(biāo)直接拖動選中矩形區(qū)域 在行列標(biāo)記上單擊選中整行整列 再拖動鼠標(biāo)的同時按住Ctrl鍵選擇不連續(xù)區(qū)域 按快捷鍵Ctrl+Shift+8（或Ctrl+*）可以選中當(dāng)前單元格周圍連續(xù)的矩形區(qū)

10、域 按住Shift鍵可以選中兩點之間區(qū)域,表格的基本操作,選擇區(qū)域 用鼠標(biāo)直接拖動選中矩形區(qū)域 在行列標(biāo)記上單擊選中整行整列 再拖動鼠標(biāo)的同時按住Ctrl鍵選擇不連續(xù)區(qū)域 按快捷鍵Ctrl+Shift+8（或Ctrl+*）可以選中當(dāng)前單元格周圍連續(xù)的矩形區(qū)域 按住Shift鍵可以選中兩點之間區(qū)域,選擇表格續(xù),快速移動光標(biāo) Ctrl+home 將光標(biāo)移動到第一個單元格 Ctrl+End 將光標(biāo)移動到表格最后一個單元格 Ctrl+ 將光標(biāo)移動到當(dāng)前區(qū)域最右列(其他方向箭頭依此類推) 則Ctrl+Shift+向下箭頭,選擇當(dāng)前單元格至當(dāng)前區(qū)域最后一行的內(nèi)容,表格的編輯和調(diào)整,鼠標(biāo)拖動數(shù)據(jù)拖動表格邊框

11、 直接拖拽邊框是移動； 按住CTRL鍵拖拽相當(dāng)于復(fù)制操作； 按ALT鍵拖拽在其他Sheet間移動； 按SHIFT鍵拖拽將表格從原位置移出并插入到 目標(biāo)位置。（可用于交換兩列位置）,表格的編輯和調(diào)整,序列填充 填充柄 ：選中區(qū)域右下角的黑點 拖拽填充柄可實現(xiàn)序列填充，如：January, February, March等 一切和數(shù)字有關(guān)的數(shù)據(jù)都有序列功能 反向拖動填充柄相當(dāng)于刪除 自定義填充序列,表格的編輯和調(diào)整,鼠標(biāo)拖動調(diào)整寬高 調(diào)整行高列寬，直接拖動行列標(biāo)記間分割線 同時選中多行或多列，調(diào)整其中任意一列 （行）的寬度，其他各列（行）同時改變 雙擊行列標(biāo)記間分割線可根據(jù)內(nèi)容自動調(diào)整 精確調(diào)整寬

12、高 格式行行高 格式列列寬,正確的錄入數(shù)據(jù),正確的數(shù)字格式 設(shè)置數(shù)字貨幣格式 日期型數(shù)字格式（非日期型格式靠分列進行轉(zhuǎn)換） 文本型數(shù)字格式（以單引號開頭輸入文本） 自定義數(shù)字格式 單元格格式快捷鍵 Ctrl+1,表格的編輯和調(diào)整,剪切、復(fù)制、粘貼的使用 剪貼板的使用 選擇性粘貼 數(shù)值,格式,公式, 列寬 操作:加,減,乘,除 轉(zhuǎn)置:行列互換,編輯專業(yè)美觀的表格,套用表格格式,套用表格樣式 操作:選中表體,開始樣式套用表格格式,49,豐富的條件格式,開始條件格式 什么是條件格式 將滿足特定條件的數(shù)據(jù)以指定格式顯示 如何設(shè)置條件格式 Format Conditional Format 使用條件格式

13、顯示單元格的數(shù)據(jù) 例：將表格中大于500的數(shù)字以紅色顯示 目的：突出顯示滿足某個條件數(shù)據(jù) 可以設(shè)置顏色、圖標(biāo)等 可以使用顏色,圖標(biāo)集等作為條件的區(qū)分 最多可以設(shè)置64種格式,管理條件格式規(guī)則,根據(jù)單元格值判斷 判斷單元格唯一性與重復(fù) 使用公式：活動單元格不能直接參與判斷時使用公式 調(diào)整規(guī)則順序,定義數(shù)據(jù)驗證,數(shù)據(jù)有效性 定義有效數(shù)據(jù)范圍：數(shù)值、日期、序列 設(shè)置輸入信息 設(shè)置出錯警告 圈釋無效數(shù)據(jù),創(chuàng)建下拉列表,數(shù)據(jù) 有效性設(shè)置 允許列表 數(shù)據(jù)源：選擇下拉菜單數(shù)據(jù)源 如果數(shù)據(jù)源在另外一個sheet則需要先給數(shù)據(jù)源命名,對數(shù)據(jù)表格的頁面設(shè)置,頁面設(shè)置 頁面布局頁面設(shè)置方向、縮放比例、頁邊距 分頁預(yù)

14、覽視圖下調(diào)整打印范圍 視圖分頁預(yù)覽 打印重復(fù)標(biāo)題行： 頁面設(shè)置工作表:打印重復(fù)標(biāo)題行，打印重復(fù)標(biāo)題列 打印選項 打印順序,打印數(shù)據(jù)表格,Office按鈕打印 打印范圍 打印內(nèi)容 打印選擇區(qū)域 打印當(dāng)前工作表 打印整個工作薄 打印份數(shù),數(shù)據(jù)窗口的控制,凍結(jié)窗格 視圖窗口凍結(jié)窗格 拆分窗口 視圖窗口拆分窗口 新建并并排查看窗口 視圖窗口并排查看 組合特定區(qū)域 數(shù)據(jù)分級顯示組合,數(shù)據(jù)的安全性,保護工作薄 保護工薄的窗口和結(jié)構(gòu) 設(shè)置文件的打開和修改口令 保護工作表 整張工作表的保護 部分數(shù)據(jù)區(qū)域的保護 高級數(shù)據(jù)區(qū)域保護,用圖表說話,使用圖表分析數(shù)據(jù),插入圖表 應(yīng)用圖表樣式 選中圖表設(shè)計圖表樣式 更改圖

15、表類型 設(shè)計更改圖表類型 明確圖表分析的目標(biāo),明確圖表分析目標(biāo),柱狀圖對比關(guān)系的幾種選擇,條型圖,分類標(biāo)志易讀 時間趨向弱,處理丟失數(shù)據(jù),忽略丟失數(shù)據(jù)，有空距 將丟失數(shù)據(jù)處理為0 內(nèi)插值,次坐標(biāo)軸,制作第2Y軸圖表,首先創(chuàng)建為折線圖 在以第2Y軸為刻度的系列單擊鼠標(biāo)右鍵，設(shè)置數(shù)據(jù)系列格式 在坐標(biāo)軸位置選擇“次坐標(biāo)軸” 更改此系列的圖表類型以和主坐標(biāo)軸區(qū)分.,漲跌柱線,趨勢線,趨勢線的的估計值與實際數(shù)值的接近程度,處理趨勢線,支持趨勢線的圖表類型 柱形圖、條形圖、折線圖、面積圖、XY散點圖、股價圖、氣泡圖 適合趨勢線的數(shù)據(jù) 成對的數(shù)字數(shù)據(jù) 基于時間的數(shù)據(jù) 添加趨勢線,線性趨勢預(yù)測,添加線性趨勢線 趨勢線預(yù)測 設(shè)置截距 顯示公式：Y=ax+b 顯示R平方值,圖表替數(shù)據(jù)說話-根據(jù)數(shù)據(jù)設(shè)計圖表,分組對比,坐標(biāo)軸多級分類,不同內(nèi)容數(shù)據(jù)對比,不同內(nèi)容數(shù)據(jù)對比2,可以騰飛的圖表,添加輔助數(shù)據(jù)項,處理自選圖形