財務EXCEL操作技巧培訓

財務EXCEL操作技巧培訓目錄EXCEL基本用途常見數據計算EXCEL快捷技巧124EXCEL輸出

第一部分EXCEL基本用途PART013創(chuàng)建統計表格1.14表至表

IT提數：.unl格式→txt格式

外部獲取數據創(chuàng)建統計表格1.15IT提數：.unl格式直接拖入EXCEL

處理步驟：“完成”分析與篩選數據-數據透視表創(chuàng)建1.260505數據透視表:一種交互式的表，可以進行某些計算，如求和與計數等。分析與篩選數據-數據透視表布局1.270505

說明分析與篩選數據-數據透視表1.280505注意事項分析與篩選數據-數據透視表布局1.290505

注意事項1.不能出現同一字段既是篩選字段又是行或列字段2.同一數值字段可以拖放多個到數值字段區(qū)域3.每次拖放字段到數據透視表中時，EXCEL都會重新更新（可推遲更新）

分析與篩選數據-數據重復項1.310數據重復項

刪除查找刪除重復項：數據→刪除重復項查找重復項：數據透視表（計數項大于1的項為重復項）

→分析與篩選數據-數據重復項1.311數據重復項

刪除查找查找重復項：開始→條件格式→重復值分析與篩選數據-數據排序1.312數據排序清單排序高級排序數據清單排序

如果按照單列的內容進行排序，可直接“數據”選項卡“””進行排序高級排序

按照多個條件對清單進行排序分析與篩選數據-數據篩選1.313數據篩選自動篩選自定義篩選高級篩選自動篩選—字段名稱會變成一個下拉框自定義篩選

當自帶的篩選條件無法滿足需要時，可根據需要自定義篩選條件

→

→分析與篩選數據-數據篩選1.314數據篩選自動篩選自定義篩選高級篩選高級篩選

使用高級篩選功能，必須先建立一個條件區(qū)域，用來指定篩選所滿足的條件。條件區(qū)域的第一行是所有作為篩選條件的字段名，這些字段名與數據清單中的字段名必須完全一樣。分析與篩選數據-分類匯總1.315分類匯總

在數據清單中自動計算分類匯總及總計值。用戶只需指定需要進行分類匯總的數據項、待匯總的數值和用于計算的函數(例如“求和”函數)即可。匯總單元格排序→選項卡“數據”→“分類匯總”分析與篩選數據-分類匯總1.316分類匯總注意事項：

匯總前排序在進行分類匯總前，一定要把需要按其匯總的字段進行排序，否則無法實現匯總。不足之處如有多列行字段頭，分類匯總之后，則只保留匯總字段，其他字段無法體現，相比之下，當需求字段多時，使用數據透視表。分析與篩選數據-數據分級顯示1.317分級顯示

有時，我們既想看到匯總數據，又想看到每一個匯總數據下的明細數據，那么勢必導致在一張表里數據行較多。但電腦屏幕有限，不停的上下翻頁非常的不便利，也不直觀，有沒有其他好辦法呢？分級顯示分析與篩選數據-數據分級顯示1.318操作方法：數據→組合→選中組數進行分級顯示分析與篩選數據-數據有效性1.319數據有效性數據有效性：防止重復信息→防止人工輸入錯誤快速揪出無效數據自定義序列分析與篩選數據-數據有效性1.320數據有效性選定目標單元格→數據→數據驗證在數據有效性窗口中選擇-設置→允許里面選擇“序列”選項→輸入鎖定來源（注意逗號要為全英文半角狀態(tài)下顯示）或直接選擇列分析與篩選數據-數據分列1.321數據分列

一列分多列對一列需要根據共同字符（含空格）分列的數據進行分列，也可以強制指定字符寬度進行分列。去除亂字符對外部引入的數據進行處理，去除TAB符、單分號符、占位符、部分位于首尾的空格符。分析與篩選數據-數據分列1.322分隔符號分列—選項卡“數據”分列點擊”完成”

正常情況下，按圖二界面，其他選項保持默認，然后點擊“完成”結束數據分列。分析與篩選數據-數據分列1.323固定寬度分列—選項卡“數據”分列點擊”完成”可以通過鼠標設置一條或多條分隔線，將字條按任意方式進行分隔處理正常情況下，按圖二界面，其他選項保持默認，然后點擊“完成”結束數據分列。分析與篩選數據-快捷組合鍵1.424快捷組合鍵快捷組合鍵：Ctrl+C復制選定的單元格Ctrl+X剪切選定的單元格Alt+;選定指定單元格Ctrl+A選擇整個工作表Ctrl+V粘貼選定單元格Ctrl+D使用“向下填充”命令將選定范圍內最頂層單元格的內容和格式復制到下面的單元格Ctrl+R使用“向右填充”命令Ctrl+F數據查找Ctrl+H數據替換Ctrl+Z撤銷

第二部分常用數據計算PART02252.1公式引用-參考值“A1”26絕對引用相對引用混合引用231被引用的單元格隨位置變化而變化相對地址：B1(復制時，相對位置不變）被引用單元格不隨位置改變絕對地址：$A$1(復制時，始終不變）相對地址：$A1、A$1(上述相對，絕對混合）公式中的錯誤2.227常用函數—求和函數2.328SUMSUMIF/SSUM函數：無條件求和=SUM（參數1，參數2，.....參數N)Tips:求和快捷鍵—快速求和“ALT+=”SUMIF函數：條件求和=SUMIF（range，criteria，sum_range)

條件區(qū)域

條件

條件求和范圍SUMIFS函數：多條件求和=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)

條件求和范圍條件區(qū)域1條件1條件區(qū)域2條件2常用函數—求和函數2.329案例SUMIF函數:求各支公司保費合計數SUMIFS函數:求各支公司不同銷售渠道的保費合計數輸入公式

=SUMIF(A2:A12,A2,C2:C12)

核算單位

目標單位求和范圍2.輸入公式

=SUMIFS(C2:C12,B2:B12,B2,E2:E12,E2)

求和范圍條件區(qū)域1條件1條件區(qū)域2條件2常用函數—數學函數2.330SUMAVERGAE求N個數的平均值=AVERGAE(參數1，參數2..)MAXMINABS求N個數的最大值求N個數的最小值求某數的絕對值=MAX(參數1，參數2..)=MIN(參數1，參數2..)=ABS(參數1)常用函數—計數函數2.331SUMIF/COUNTIF函數：區(qū)域內滿足給定條件的單元格數目=COUNTIF（range，criteria)

區(qū)域條件COUNTIFCOUNTACOUNTA函數：返回單元格內非空值的個數=COUNTA（參數1，參數2，......)

常用函數—計數函數2.332SUMIF/

案例COUNTIF函數：計算保費金額大于500的保單數輸入公式=COUNTIF(C2:C12,">500")

區(qū)域條件常用函數—條件函數和邏輯函數2.333SUMIF/COUNTIFCOUNTAIF函數：條件函數，它根據參數條件的真假返回不同的結果值。=IF（條件表達式，TRUE,FALSE)AND函數：“與”，當所有條件滿足，AND函數返回TRUE，有一個條件不滿足則為FALSE。=AND（條件1，條件2，....,條件N)OR函數：“或”，只要有一個條件滿足，OR函數返回TRUE，所有條件不滿足則為FALSE。=OR（條件1，條件2，....,條件N)注意：這三個函數常常聯合使用常用函數—條件函數和邏輯函數2.334

案例IF函數:1、判斷A列與B列核算單位是否一致

2、算出獎勵政策（保費收入大于500，獎勵政策為保費的

8%，小于500為3%1、輸入公式=if(A1=B1,0,1)語義：如果單元格A1等于B1，顯示為真（0），否則為假（1）2、輸入公式=IF(D2>500,D2*$D$14,D2*$D$15)語義：如果總保費大于500，則總保費乘以8%，否則總保費乘以3%常用函數—日期函數2.335SUMIF/DATEDIF/VALUE函數：計算兩個日期的相隔年數、月數、天數應收保費賬齡計算中經常會引用例如：計算應收保費逾期天數=DATEDIF(D2,G15,"d")或=VALUE(G15-D2)DATEDIF/VALUE日期顯示COUNTA函數：返回單元格內非空值的個數=COUNTA（參數1，參數2，......)

常用函數—查找匹配函數2.336VLOOKUPVLOOKUP函數：在表格或數值組的首列查找制定的數值，并由此返回表格或數組當前列制定行處的數值。函數語法規(guī)則如下：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)常用函數—條件函數和邏輯函數2.337案例：VLOOKUP函數:根據表2匹配出表1的被保險人名稱輸入公式：

=VLOOKUP(A2,F1:H12,3,0）兩張表共同點：保單號—A列與F列A2目標查找值F1：H12查找區(qū)域3：需要匹配的項為該區(qū)域第三列0：模糊查找常用函數—文本函數2.338解決方法：分列：指定列→分列→完成CLEAN函數：刪除文本中不能打印的字符TEXT函數：將數值轉換為按指定數字格式表示的文本TRIM函數：取消字符串兩側的空格示文本函數問題：兩個單元格完全一樣，但卻無法匹配？表象蒙蔽→數據清理兩個看上去完全一樣的單元格，其實往往包含了不同的字串符?。?！常用函數—四舍五入、整數、文本函數2.339MINABS求某數的絕對值ROUND函數:返回某個數字制定返回位數后的數字=Round(參數，2）INT函數:取整數LEFT函數、RIGHT函數:獲取字串符指定的左邊或右邊個數的文本=LEFT(TEXT,N)

截取保單號機構代碼使用MID函數：獲取字串符中間指定個數的文本=MID(text,start_num,num_chars)常用函數—文本函數2.340SEARCH函數:用來返回指定的字符串在原始字符串中首次出現的位置,從左到右查找，忽略英文字母的大小寫。=SEARCH(find_text,within_text,start_num)find_text：要查找的文本字符串within_text：要在哪一個字符串查找start_num：從within_text的第幾個字符開始查找LEN函數：反映文本字串符個數

這兩個函數常常混合使用?。?！常用函數—文本函數2.341目的：根據客戶信息提取保單號（客戶信息為A列，保單號為B列）方法：單擊B2單元格，在編輯欄輸入公式：=RIGHT(A2,LEN(A2)-SEARCH("P",A2,6)+1)RIGHT函數：表示從右邊取數LEN函數：整個文本字串符個數SEARCH函數：返回指定符號字串符的位置常用函數—排序函數2.342SUMIF/RankLARGERANK排名函數語義規(guī)則：=RANK(number,ref,order)其中，number為要排名的單元格或數字，ref為區(qū)域范圍，order為排名的順序，即降序（用0表示）或升序（用1表示）。LARGE：返回數據集中的第K個最大值。語義規(guī)則：=LARGE(array,k)其中，array為需要找到第k個最大值的數組或數字型數據區(qū)域，k為返回的數據在數組或數據區(qū)域里的位置(從大到小)。SMALL函數同理常用函數—條件函數和邏輯函數2.343案例：根據表一信息，給各分公司保費收入進行排名結果方法：創(chuàng)建數據透視表，求出各個分公司保費收入引入RANK函數:

=RANK($L$2:$L$9,$K$2:$L$9,0)

需要排序的值排序引用的范圍升序或降序

第三部分EXCEL快捷技巧PART0444EXCEL快捷技巧3快速轉換不同的工作表：”CRTL(+SHIFT)+PgDn/PgUp”快速移動：按下Ctrl+相應的方向鍵或方向鍵組合。例如按下“Crtl+↓”即可到達選中最下方的單元格快速選取：按下Ctrl+Shift+相應的方向鍵或方向鍵組合，再復制即可自動換行：如果出現多個保單號，需拆分為不同的列采用行列轉置：選擇需要轉置的數據（包括所有行或列標簽）,按下Ctrl+C，選擇性粘貼中的轉置即可45EXCEL快捷技巧3快速恢復E+的數字格式顯示：數字長度大于11為會以科學計數顯示，分列選擇文本格式顯示即可?？焖偾笮信c列合計數：Ctrl+G→定位條件→空值→“ALT+=“多單元格批量輸入相同的內容：將想要輸入內容的單元格一起選中，然后在編輯欄中輸入想要的內容，最后按下”Ctrl+Enter”（編輯欄不顯示→選項→高級→顯示→顯示編輯欄）對比同行數據是否相同：Ctrl+G→定位條件→行內容差異單元格→填充顏色快速刪除錯誤值：選擇需要定位的區(qū)域，按下快捷鍵Ctrl+G,打開“定位”對話框→選擇“定位條件”中的“公式”→“錯誤”，成功定位后批量清除46EXCEL快捷技巧3利用選擇性粘貼快速加減乘除47常用常忽視少用

第四部分EXCEL輸出PART0448EXCEL表格保護449

有時，要求分公司、支公司、業(yè)務員、出單員等報送一定的表格，為了簡化，表格全部設好，只需要相關人員往指定的空格錄入即可?？墒潜砀癜l(fā)下去，經常被改的面目全非。公式、格式、匯總數據都不對，怎么辦？保護工作表只允許選定指定區(qū)域EXCEL表格保護450步驟：確保單元格鎖定狀態(tài)：選中全表，點擊“設置單元格格式”→右鍵“保護”→選”鎖定“選項卡中點”審閱“，選擇”保護工作表“選定要鎖定的列或行（輸入密碼）EXCEL表格保護451步驟：結果顯示說明：想鎖定哪行或哪列，只需在設置單元格的時候鎖定即可，其余行列取消鎖定。結果為鎖定行列被鎖定，其他行列可填寫。EXCEL表格打印452如何把多數據放