《財務EXCEL操作技巧培訓 .pptx》由會員分享，可在線閱讀，更多相關《財務EXCEL操作技巧培訓 .pptx（55頁珍藏版）》請在匯文網(wǎng)上搜索。

1、財務EXCEL操作技巧培訓目錄EXCEL基本用途常見數(shù)據(jù)計算EXCEL快捷技巧124EXCEL輸出 第一部分EXCEL 基本用途PART 013創(chuàng)建統(tǒng)計表格1.14表至表 IT提數(shù)：.unl格式txt格式 外部獲取數(shù)據(jù)創(chuàng)建統(tǒng)計表格1.15IT提數(shù)：.unl格式直接拖入EXCEL 處理步驟：“完成”分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表創(chuàng)建1.260505數(shù)據(jù)透視表:一種交互式的表，可以進行某些計算，如求和 與計數(shù)等。分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.270505 說明分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表1.280505注 意 事 項分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.290505 注意事項1.不能出現(xiàn)同一字段既是篩選

2、字段又是行或列字段2.同一數(shù)值字段可以拖放多個到數(shù)值字段區(qū)域3.每次拖放字段到數(shù)據(jù)透視表中時，EXCEL都會重新更新（可推遲 更新）分析與篩選數(shù)據(jù)-數(shù)據(jù)重復項1.310數(shù)據(jù)重復項 刪 除查 找刪除重復項：數(shù)據(jù)刪除重復項查找重復項：數(shù)據(jù)透視表（計數(shù)項大于1的項為重復項）分析與篩選數(shù)據(jù)-數(shù)據(jù)重復項1.311數(shù)據(jù)重復項 刪 除查 找查找重復項：開始條件格式重復值分析與篩選數(shù)據(jù)-數(shù)據(jù)排序1.312數(shù)據(jù)排序清單排序高級排序數(shù)據(jù)清單排序 如果按照單列的內(nèi)容進行排序，可直接“數(shù)據(jù)”選項卡“”進行排序高級排序 按照多個條件對清單進行排序分析與篩選數(shù)據(jù)-數(shù)據(jù)篩選1.313數(shù)據(jù)篩選自動篩選自定義篩選高級篩選自動篩

3、選 字段名稱會變成一個下拉框自定義篩選 當自帶的篩選條件無法滿足需要時，可根據(jù)需要自定義篩選條件 分析與篩選數(shù)據(jù)-數(shù)據(jù)篩選1.314數(shù)據(jù)篩選自動篩選自定義篩選高級篩選高級篩選 使用高級篩選功能，必須先建立一個條件區(qū)域，用來指定篩選所滿足的條件。條件區(qū)域的第一行是所有作為篩選條件的字段名，這些字段名與數(shù)據(jù)清單中的字段名必須完全一樣。分析與篩選數(shù)據(jù)-分類匯總1.315分類匯總 在數(shù)據(jù)清單中自動計算分類匯總及總計值。用戶只需指定需要進行分類匯總的數(shù)據(jù)項、待匯總的數(shù)值和用于計算的函數(shù)(例如“求和”函數(shù))即可。匯總單元格排序選項卡“數(shù)據(jù)”“分類匯總”分析與篩選數(shù)據(jù)-分類匯總1.316分類匯總注意事項：匯

4、總前排序 在進行分類匯總前，一定要把需要按其匯總的字段進行排序，否則無法實現(xiàn)匯總。不足之處 如有多列行字段頭，分類匯總之后，則只保留匯總 字段，其他字段無法體現(xiàn)，相比之下，當需求字段 多時，使用數(shù)據(jù)透視表。分析與篩選數(shù)據(jù)-數(shù)據(jù)分級顯示1.317分級顯示 有時，我們既想看到匯總數(shù)據(jù)，又想看到每一個匯有時，我們既想看到匯總數(shù)據(jù)，又想看到每一個匯總數(shù)據(jù)下的明細數(shù)據(jù)，那么勢必導致在一張表里數(shù)據(jù)總數(shù)據(jù)下的明細數(shù)據(jù)，那么勢必導致在一張表里數(shù)據(jù)行較多。行較多。但電腦屏幕有限，不停的上下翻頁非常的不便利，但電腦屏幕有限，不停的上下翻頁非常的不便利，也不直觀，有沒有其他好辦法呢？也不直觀，有沒有其他好辦法呢？分

5、級顯示分析與篩選數(shù)據(jù)-數(shù)據(jù)分級顯示1.318操作方法：數(shù)據(jù)組合選中組數(shù)進行分級顯示分析與篩選數(shù)據(jù)-數(shù)據(jù)有效性1.319數(shù)據(jù)有效性數(shù)據(jù)有效性：防止重復信息防止人工輸入錯誤快速揪出無效數(shù)據(jù)自定義序列分析與篩選數(shù)據(jù)-數(shù)據(jù)有效性1.320數(shù)據(jù)有效性選定目標單元格數(shù)據(jù)數(shù)據(jù)驗證在數(shù)據(jù)有效性窗口中選擇-設置允許里面選擇“序列”選項輸入鎖定來源（注意逗號要為全英文半角狀態(tài)下顯示）或直接選擇列分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.321數(shù)據(jù)分列 一列分多列 對一列需要根據(jù)共同字符（含空格）分列的數(shù)據(jù)進行分列，也可以強制指定字符寬度進行分列。去除亂字符 對外部引入的數(shù)據(jù)進行處理，去除TAB符、單分號符、占位符、部分位于首尾

6、的空格符。分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.322分隔符號 分列 選項卡“數(shù)據(jù)”分列點 擊”完 成”正常情況下，按圖二界面，其他選項保持默認，然后點擊正常情況下，按圖二界面，其他選項保持默認，然后點擊“完成完成”結束數(shù)據(jù)分列。結束數(shù)據(jù)分列。分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.323固定寬度 分列 選項卡“數(shù)據(jù)”分列點 擊”完 成”可以通過鼠標設置一條或多條分隔線，將字條按任意方式進行分隔處理可以通過鼠標設置一條或多條分隔線，將字條按任意方式進行分隔處理正常情況下，按圖二界面，其他選項保持默認，然后點擊正常情況下，按圖二界面，其他選項保持默認，然后點擊“完成完成”結束數(shù)據(jù)分列。結束數(shù)據(jù)分列。分析與篩選數(shù)據(jù)-快

7、捷組合鍵1.424快捷組合鍵快捷組合鍵：Ctrl+C 復制選定的單元格 Ctrl+X 剪切選定的單元格Alt+;選定指定單元格Ctrl+A 選擇整個工作表Ctrl+V 粘貼選定單元格Ctrl+D 使用“向下填充”命令將選定范圍內(nèi)最頂層單元格的內(nèi)容和格式復制到下面的單元格Ctrl+R 使用“向右填充”命令Ctrl+F 數(shù)據(jù)查找Ctrl+H 數(shù)據(jù)替換Ctrl+Z 撤銷 第二部分常用數(shù)據(jù)計算PART 02252.1公式引用-參考值“A1”26絕對引用相對引用混合引用231被引用的單元格隨位置變化而變化相對地址：B1(復制時，相對位置不變）被引用單元格不隨位置改變絕對地址：$A$1(復制時，始終不變）

8、相對地址：$A1、A$1(上述相對，絕對混合）公式中的錯誤2.227常用函數(shù) 求和函數(shù)2.328SUMSUMIF/SSUM函數(shù)：無條件求和=SUM（參數(shù)1，參數(shù)2，.參數(shù)N)Tips:求和快捷鍵快速求和“ALT+=”SUMIF函數(shù)：條件求和=SUMIF（range，criteria，sum_range)條件區(qū)域 條件 條件求和范圍 SUMIFS函數(shù)：多條件求和=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,.)條件求和范圍 條件區(qū)域1 條件1 條件區(qū)域2 條件2常用函數(shù) 求和函數(shù)2.329案例1.SUM

9、IF函數(shù):求各支公司保費合計數(shù)2.SUMIFS函數(shù):求各支公司不同銷售渠道的保費合計數(shù)1.輸入公式 =SUMIF(A2:A12,A2,C2:C12)核算單位 目標單位 求和范圍2.輸入公式 =SUMIFS(C2:C12,B2:B12,B2,E2:E12,E2)求和范圍 條件區(qū)域1 條件1 條件區(qū)域2 條件2常用函數(shù) 數(shù)學函數(shù)2.330SUMAVERGAE求N N個數(shù)的平均值=AVERGAE(參 數(shù) 1，參 數(shù)2.)MAX MIN ABS求N N個數(shù)的最大值求N N個數(shù)的最小值求某數(shù)的絕對值 =MAX(參數(shù)1，參數(shù)2.)=MIN(參數(shù)1，參數(shù)2.)=ABS(參數(shù)1)常用函數(shù) 計數(shù)函數(shù)2.331S

10、UMIF/COUNTIF函數(shù)：區(qū)域內(nèi)滿足給定條件的單元格數(shù)目=COUNTIF（range，criteria)區(qū)域 條件COUNTIF COUNTACOUNTA函數(shù)：返回單元格內(nèi)非空值的個數(shù)=COUNTA（參數(shù)1，參數(shù)2，.)常用函數(shù) 計數(shù)函數(shù)2.332SUMIF/案例COUNTIF函數(shù)：計算保費金額大于500的保單數(shù)輸入公式=COUNTIF(C2:C12,500)區(qū)域 條件常用函數(shù) 條件函數(shù)和邏輯函數(shù)2.333SUMIF/COUNTIF COUNTAIF函數(shù)：條件函數(shù)，它根據(jù)參數(shù)條件的真假返回不同的結果值。=IF（條件表達式，TRUE,FALSE)AND函數(shù)：“與”，當所有條件滿足，AND函數(shù)

11、返回TRUE，有一個條件不滿足則為FALSE。=AND（條件1，條件2，.,條件N)OR函數(shù)：“或”，只要有一個條件滿足，OR函數(shù)返回TRUE，所有條件不滿足則為FALSE。=OR（條件1，條件2，.,條件N)注意：這三個函數(shù)常常聯(lián)合使用常用函數(shù) 條件函數(shù)和邏輯函數(shù)2.334 案例IF函數(shù):1、判斷A列與B列核算單位是否一致 2、算出獎勵政策（保費收入大于500，獎勵政策為保費的 8%，小于500為3%1、輸入公式=if(A1=B1,0,1)語義：如果單元格A1等于B1，顯示為真（0），否則為假（1）2、輸入公式=IF(D2500,D2*$D$14,D2*$D$15)語義：如果總保費大于500

12、，則總保費乘以8%，否則總保費乘以3%常用函數(shù) 日期函數(shù)2.335SUMIF/DATEDIF/VALUE函數(shù)：計算兩個日期的相隔年數(shù)、月數(shù)、天數(shù) 應收保費賬齡計算中經(jīng)常會引用 例如：計算應收保費逾期天數(shù)=DATEDIF(D2,G15,d)或=VALUE(G15-D2)DATEDIF/VALUE日期顯示COUNTA函數(shù)：返回單元格內(nèi)非空值的個數(shù)=COUNTA（參數(shù)1，參數(shù)2，.)常用函數(shù) 查找匹配函數(shù)2.336VLOOKUPVLOOKUP函數(shù)：在表格或數(shù)值組的首列查找制定的數(shù)值，并由此返回表格或數(shù)組當前列制定行處的數(shù)值。函數(shù)語法規(guī)則如下：=VLOOKUP(lookup_value,table_a

13、rray,col_index_num,range_lookup)常用函數(shù) 條件函數(shù)和邏輯函數(shù)2.337案例：VLOOKUP函數(shù):根據(jù)表2匹配出表1的被保險人名稱輸入公式：=VLOOKUP(A2,F1:H12,3,0）兩張表共同點：保單號A列與F列A2 目標查找值F1：H12 查找區(qū)域3：需要匹配的項為該區(qū)域第三列0：模糊查找常用函數(shù) 文本函數(shù)2.338解決方法：分列：指定列分列完成CLEAN函數(shù)：刪除文本中不能打印的字符TEXT函數(shù)：將數(shù)值轉換為按指定數(shù)字格式表示的文本TRIM函數(shù)：取消字符串兩側的空格示文本函數(shù)問題：兩個單元格完全一樣，但卻無法匹配？表象蒙蔽數(shù)據(jù)清理兩個看上去完全一樣的單元格

14、，其實往往包含了不同的字串符！常用函數(shù) 四舍五入、整數(shù)、文本函數(shù)2.339 MIN ABS求某數(shù)的絕對值ROUND函數(shù):返回某個數(shù)字制定返回位數(shù)后的數(shù)字=Round(參數(shù)，2）INT函數(shù)函數(shù):取整數(shù)LEFT函數(shù)、函數(shù)、RIGHT函數(shù)函數(shù):獲取字串符指定的左邊或右邊個數(shù)的文本=LEFT(TEXT,N)截取保單號機構代碼截取保單號機構代碼使用使用MID函數(shù)函數(shù)：獲取字串符中間指定個數(shù)的文本=MID(text,start_num,num_chars)常用函數(shù) 文本函數(shù)2.340SEARCH函數(shù):用來返回指定的字符串在原始字符串中首次出現(xiàn)的位置,從左到右查找，忽略英文字母的大小寫。=SEARCH(fi

15、nd_text,within_text,start_num)find_text：要查找的文本字符串within_text：要在哪一個字符串查找start_num：從within_text的第幾個 字符開始查找LEN函數(shù)：反映文本字串符個數(shù) 這兩個函數(shù)常?；旌鲜褂?！常用函數(shù) 文本函數(shù)2.341目的：根據(jù)客戶信息提取保單號（客戶信息為A列，保單號為B列）方法：單擊B2單元格，在編輯欄輸入公式：=RIGHT(A2,LEN(A2)-SEARCH(P,A2,6)+1)RIGHT函數(shù)：表示從右邊取數(shù)LEN函數(shù)：整個文本字串符個數(shù)SEARCH函數(shù)：返回指定符號字串符的位置常用函數(shù) 排序函數(shù)2.342SUMI

16、F/Rank LARGERANK 排名函數(shù)語義規(guī)則：=RANK(number,ref,order)其中，number為要排名的單元格或數(shù)字，ref為區(qū)域范圍，order為排名的順序，即降序（用0表示）或升序（用1表示）。LARGE：返回數(shù)據(jù)集中的第K個最大值。語義規(guī)則：=LARGE(array,k)其中，array 為需要找到第 k 個最大值的 數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域，k 為返回的數(shù)據(jù)在 數(shù)組或數(shù)據(jù)區(qū)域里的位置(從大到小)。SMALL函數(shù)同理常用函數(shù) 條件函數(shù)和邏輯函數(shù)2.343案例：根據(jù)表一信息，給各分公司保費收入進行排名結果方法：創(chuàng)建數(shù)據(jù)透視表，求出各個分公司保費收入引入RANK函數(shù):=RA

17、NK($L$2:$L$9,$K$2:$L$9,0)需要排序的值 排序引用的范圍 升序或降序 第三部分EXCEL快捷技巧PART 0444EXCEL快捷技巧3快速轉換不同的工作表：”CRTL(+SHIFT)+PgDn/PgUp”快速移動：按下Ctrl+相應的方向鍵或方向鍵組合。例如按下“Crtl+”即可到達選中最下方的單元格快速選取：按下 Ctrl+Shift+相應的方向鍵或方向鍵組合，再復制即可自動換行：如果出現(xiàn)多個保單號，需拆分為不同的列采用行列轉置：選擇需要轉置的數(shù)據(jù)（包括所有行或列標簽）,按下Ctrl+C，選擇性粘貼中的轉置即可45EXCEL快捷技巧3快速恢復E+的數(shù)字格式顯示：數(shù)字長度

18、大于11為會以科學計數(shù)顯示，分列選擇文本格式顯示即可?？焖偾笮信c列合計數(shù)：Ctrl+G定位條件空值“ALT+=“多單元格批量輸入相同的內(nèi)容：將想要輸入內(nèi)容的單元格一起選中，然后在編輯欄中輸入想要的內(nèi)容，最后按下”Ctrl+Enter”（編輯欄不顯示選項高級顯示顯示編輯欄）對比同行數(shù)據(jù)是否相同：Ctrl+G定位條件行內(nèi)容差異單元格填充顏色快速刪除錯誤值：選擇需要定位的區(qū)域，按下快捷鍵 Ctrl+G,打開“定位”對話框選擇“定位條件”中的“公式”“錯誤”，成功定位后批量清除46EXCEL快捷技巧3利用選擇性粘貼快速加減乘除47常用常忽視少用 第四部分EXCEL輸出PART 0448EXCEL表格保

19、護449 有時，要求分公司、支公司、業(yè)務員、出單員等報送一定的表格，為了簡化，表格全部設好，只需要相關人員往指定的空格錄入即可?？墒潜砀癜l(fā)下去，經(jīng)常被改的面目全非。公式、格式、匯總數(shù)據(jù)都不對，怎么辦？保護工作表只允許選定指定區(qū)域EXCEL表格保護450 步驟：確保單元格鎖定狀態(tài)：選中全表，點擊“設置單元格格式”右鍵“保護”選”鎖定“選項卡中點”審閱“，選擇”保護工作表“選定要鎖定的列或行（輸入密碼）EXCEL表格保護451步驟：結果顯示說明：說明：想鎖定哪行或哪列，只需在設置單元格的時候鎖定即可，其余行列取消鎖想鎖定哪行或哪列，只需在設置單元格的時候鎖定即可，其余行列取消鎖定。結果為鎖定行列被鎖定，其他行列可填寫。定。結果為鎖定行列被鎖定，其他行列可填寫。EXCEL表格打印452如何把多數(shù)據(jù)放在一張表上？視圖分頁預覽鼠標指向垂直分頁符當其變?yōu)殡p向箭頭時拖至打印最右一列EXCEL表格打印453每一頁打印行標題或列標題問題咨詢問題咨詢問題咨詢問題咨詢 EMAIL: TEL :0791-86631202謝 謝！