財務(wù)EXCEL操作技巧培訓(xùn)課件

財務(wù)EXCEL操作技巧培訓(xùn)財務(wù)EXCEL操作技巧培訓(xùn)目錄EXCEL基本用途常見數(shù)據(jù)計算EXCEL快捷技巧124EXCEL輸出目錄EXCEL基本用途常見數(shù)據(jù)計算EXCEL快捷技巧124E

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

IT提數(shù)：.unl格式→txt格式

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

處理步驟：“完成”創(chuàng)建統(tǒng)計表格1.15IT提數(shù)：.unl格式直接拖入EXCE分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表創(chuàng)建1.260505數(shù)據(jù)透視表:一種交互式的表，可以進行某些計算，如求和與計數(shù)等。分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表創(chuàng)建1.260505數(shù)據(jù)透視表:一分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.270505

說明分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.270505說明分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表1.280505注意事項分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表1.280505注意事項分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.290505

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

分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.290505注意事項分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項1.310數(shù)據(jù)重復(fù)項

刪除查找刪除重復(fù)項：數(shù)據(jù)→刪除重復(fù)項查找重復(fù)項：數(shù)據(jù)透視表（計數(shù)項大于1的項為重復(fù)項）

→分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項1.310數(shù)據(jù)重復(fù)項分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項1.311數(shù)據(jù)重復(fù)項

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

如果按照單列的內(nèi)容進行排序，可直接“數(shù)據(jù)”選項卡“””進行排序高級排序

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

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

→

→分析與篩選數(shù)據(jù)-數(shù)據(jù)篩選1.313數(shù)據(jù)篩選自動篩選自定義篩選分析與篩選數(shù)據(jù)-數(shù)據(jù)篩選1.314數(shù)據(jù)篩選自動篩選自定義篩選高級篩選高級篩選

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

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

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

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

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

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

第二部分常用數(shù)據(jù)計算PART0225第二部分PART02252.1公式引用-參考值“A1”26絕對引用相對引用混合引用231被引用的單元格隨位置變化而變化相對地址：B1(復(fù)制時，相對位置不變）被引用單元格不隨位置改變絕對地址：$A$1(復(fù)制時，始終不變）相對地址：$A1、A$1(上述相對，絕對混合）2.1公式引用-參考值“A1”26絕對引用相對引用混合引用2公式中的錯誤2.227公式中的錯誤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.328SUMSUMIF/SSUM函常用函數(shù)—求和函數(shù)2.329案例SUMIF函數(shù):求各支公司保費合計數(shù)SUMIFS函數(shù):求各支公司不同銷售渠道的保費合計數(shù)輸入公式

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

核算單位

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

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

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

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

常用函數(shù)—計數(shù)函數(shù)2.331SUMIF/COUNTIF函常用函數(shù)—計數(shù)函數(shù)2.332SUMIF/

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

區(qū)域條件常用函數(shù)—計數(shù)函數(shù)2.332SUMIF/案例C常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.333SUMIF/COUNTIFCOUNTAIF函數(shù)：條件函數(shù)，它根據(jù)參數(shù)條件的真假返回不同的結(jié)果值。=IF（條件表達式，TRUE,FALSE)AND函數(shù)：“與”，當所有條件滿足，AND函數(shù)返回TRUE，有一個條件不滿足則為FALSE。

=AND（條件1，條件2，....,條件N)OR函數(shù)：“或”，只要有一個條件滿足，OR函數(shù)返回TRUE，所有條件不滿足則為FALSE。

=OR（條件1，條件2，....,條件N)注意：這三個函數(shù)常常聯(lián)合使用常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.333SUMIF/COU常用函數(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(D2>500,D2*$D$14,D2*$D$15)語義：如果總保費大于500，則總保費乘以8%，否則總保費乘以3%常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.334案例IF函數(shù)常用函數(shù)—日期函數(shù)2.335SUMIF/DATEDIF/VALUE函數(shù)：計算兩個日期的相隔年數(shù)、月數(shù)、天數(shù)應(yīng)收保費賬齡計算中經(jīng)常會引用例如：計算應(yīng)收保費逾期天數(shù)=DATEDIF(D2,G15,"d")或=VALUE(G15-D2)DATEDIF/VALUE日期顯示COUNTA函數(shù)：返回單元格內(nèi)非空值的個數(shù)=COUNTA（參數(shù)1，參數(shù)2，......)

常用函數(shù)—日期函數(shù)2.335SUMIF/DATEDIF/常用函數(shù)—查找匹配函數(shù)2.336VLOOKUPVLOOKUP函數(shù)：在表格或數(shù)值組的首列查找制定的數(shù)值，并由此返回表格或數(shù)組當前列制定行處的數(shù)值。函數(shù)語法規(guī)則如下：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)常用函數(shù)—查找匹配函數(shù)2.336VLOOKUPVLOOK常用函數(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ù)和邏輯函數(shù)2.337案例：VLOOKU常用函數(shù)—文本函數(shù)2.338解決方法：分列：指定列→分列→完成CLEAN函數(shù)：刪除文本中不能打印的字符TEXT函數(shù)：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本TRIM函數(shù)：取消字符串兩側(cè)的空格示文本函數(shù)問題：兩個單元格完全一樣，但卻無法匹配？表象蒙蔽→數(shù)據(jù)清理兩個看上去完全一樣的單元格，其實往往包含了不同的字串符?。?！常用函數(shù)—文本函數(shù)2.338解決方法：文本函數(shù)問題：兩個常用函數(shù)—四舍五入、整數(shù)、文本函數(shù)2.339MINABS求某數(shù)的絕對值ROUND函數(shù):返回某個數(shù)字制定返回位數(shù)后的數(shù)字=Round(參數(shù)，2）INT函數(shù):取整數(shù)LEFT函數(shù)、RIGHT函數(shù):獲取字串符指定的左邊或右邊個數(shù)的文本=LEFT(TEXT,N)

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

這兩個函數(shù)常常混合使用?。?！常用函數(shù)—文本函數(shù)2.340SEARCH函數(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.341目的：根據(jù)客戶信息提取保單號常用函數(shù)—排序函數(shù)2.342SUMIF/RankLARGERANK排名函數(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ù)2.342SUMIF/Rank常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.343案例：根據(jù)表一信息，給各分公司保費收入進行排名結(jié)果方法：創(chuàng)建數(shù)據(jù)透視表，求出各個分公司保費收入引入RANK函數(shù):

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

需要排序的值排序引用的范圍升序或降序常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.343案例：根據(jù)表一信息

第三部分EXCEL快捷技巧PART0444第三部分PART0444EXCEL快捷技巧3快速轉(zhuǎn)換不同的工作表：”CRTL(+SHIFT)+PgDn/PgUp”快速移動：按下Ctrl+相應(yīng)的方向鍵或方向鍵組合。例如按下“Crtl+↓”即可到達選中最下方的單元格快速選?。喊聪翪trl+Shift+相應(yīng)的方向鍵或方向鍵組合，再復(fù)制即可自動換行：如果出現(xiàn)多個保單號，需拆分為不同的列采用行列轉(zhuǎn)置：選擇需要轉(zhuǎn)置的數(shù)據(jù)（包括所有行或列標簽）,按下Ctrl+C，選擇性粘貼中的轉(zhuǎn)置即可45EXCEL快捷技巧3自動換行：如果出現(xiàn)多個保單號，需拆分為不EXCEL快捷技巧3快速恢復(fù)E+的數(shù)字格式顯示：數(shù)字長度大于11為會以科學(xué)計數(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對比同行數(shù)據(jù)是否相同：Ctrl+G→定位EXCEL快捷技巧3利用選擇性粘貼快速加減乘除47常用常忽視少用EXCEL快捷技巧3利用選擇性粘貼快速加減乘除47常用常忽視

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

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

視圖→分頁預(yù)覽→鼠標指向垂直分頁符當其變?yōu)殡p向箭頭時拖至打印最右一列EXCEL表格打印452如何把多數(shù)據(jù)放在一張表上？EXCEL表格打印453每一頁打印行標題或列標題EXCEL表格打印453每一頁打印行標題或列標題問題咨詢EMAIL:jiamengjie@TEL題咨詢謝謝！謝謝！財務(wù)EXCEL操作技巧培訓(xùn)財務(wù)EXCEL操作技巧培訓(xùn)目錄EXCEL基本用途常見數(shù)據(jù)計算EXCEL快捷技巧1574EXCEL輸出目錄EXCEL基本用途常見數(shù)據(jù)計算EXCEL快捷技巧124E

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

IT提數(shù)：.unl格式→txt格式

外部獲取數(shù)據(jù)創(chuàng)建統(tǒng)計表格1.14表至表外部獲取數(shù)據(jù)創(chuàng)建統(tǒng)計表格1.160IT提數(shù)：.unl格式直接拖入EXCEL

處理步驟：“完成”創(chuàng)建統(tǒng)計表格1.15IT提數(shù)：.unl格式直接拖入EXCE分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表創(chuàng)建1.2610505數(shù)據(jù)透視表:一種交互式的表，可以進行某些計算，如求和與計數(shù)等。分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表創(chuàng)建1.260505數(shù)據(jù)透視表:一分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.2620505

說明分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.270505說明分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表1.2630505注意事項分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表1.280505注意事項分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.2640505

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

分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.290505注意事項分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項1.365數(shù)據(jù)重復(fù)項

刪除查找刪除重復(fù)項：數(shù)據(jù)→刪除重復(fù)項查找重復(fù)項：數(shù)據(jù)透視表（計數(shù)項大于1的項為重復(fù)項）

→分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項1.310數(shù)據(jù)重復(fù)項分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項1.366數(shù)據(jù)重復(fù)項

刪除查找查找重復(fù)項：開始→條件格式→重復(fù)值分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項1.311數(shù)據(jù)重復(fù)項分析與篩選數(shù)據(jù)-數(shù)據(jù)排序1.367數(shù)據(jù)排序清單排序高級排序數(shù)據(jù)清單排序

如果按照單列的內(nèi)容進行排序，可直接“數(shù)據(jù)”選項卡“””進行排序高級排序

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

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

→

→分析與篩選數(shù)據(jù)-數(shù)據(jù)篩選1.313數(shù)據(jù)篩選自動篩選自定義篩選分析與篩選數(shù)據(jù)-數(shù)據(jù)篩選1.369數(shù)據(jù)篩選自動篩選自定義篩選高級篩選高級篩選

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

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

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

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

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

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

第二部分常用數(shù)據(jù)計算PART0280第二部分PART02252.1公式引用-參考值“A1”81絕對引用相對引用混合引用231被引用的單元格隨位置變化而變化相對地址：B1(復(fù)制時，相對位置不變）被引用單元格不隨位置改變絕對地址：$A$1(復(fù)制時，始終不變）相對地址：$A1、A$1(上述相對，絕對混合）2.1公式引用-參考值“A1”26絕對引用相對引用混合引用2公式中的錯誤2.282公式中的錯誤2.227常用函數(shù)—求和函數(shù)2.383SUMSUMIF/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.328SUMSUMIF/SSUM函常用函數(shù)—求和函數(shù)2.384案例SUMIF函數(shù):求各支公司保費合計數(shù)SUMIFS函數(shù):求各支公司不同銷售渠道的保費合計數(shù)輸入公式

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

核算單位

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

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

求和范圍條件區(qū)域1條件1條件區(qū)域2條件2常用函數(shù)—求和函數(shù)2.329案例SUMIF函數(shù):求各支公常用函數(shù)—數(shù)學(xué)函數(shù)2.385SUMAVERGAE求N個數(shù)的平均值=AVERGAE(參數(shù)1，參數(shù)2..)MAXMINABS求N個數(shù)的最大值求N個數(shù)的最小值求某數(shù)的絕對值=MAX(參數(shù)1，參數(shù)2..)=MIN(參數(shù)1，參數(shù)2..)=ABS(參數(shù)1)常用函數(shù)—數(shù)學(xué)函數(shù)2.330SUMAVERGAE求N個數(shù)常用函數(shù)—計數(shù)函數(shù)2.386SUMIF/COUNTIF函數(shù)：區(qū)域內(nèi)滿足給定條件的單元格數(shù)目=COUNTIF（range，criteria)

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

常用函數(shù)—計數(shù)函數(shù)2.331SUMIF/COUNTIF函常用函數(shù)—計數(shù)函數(shù)2.387SUMIF/

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

區(qū)域條件常用函數(shù)—計數(shù)函數(shù)2.332SUMIF/案例C常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.388SUMIF/COUNTIFCOUNTAIF函數(shù)：條件函數(shù)，它根據(jù)參數(shù)條件的真假返回不同的結(jié)果值。=IF（條件表達式，TRUE,FALSE)AND函數(shù)：“與”，當所有條件滿足，AND函數(shù)返回TRUE，有一個條件不滿足則為FALSE。

=AND（條件1，條件2，....,條件N)OR函數(shù)：“或”，只要有一個條件滿足，OR函數(shù)返回TRUE，所有條件不滿足則為FALSE。

=OR（條件1，條件2，....,條件N)注意：這三個函數(shù)常常聯(lián)合使用常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.333SUMIF/COU常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.389

案例IF函數(shù):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%常用函數(shù)—條件函數(shù)和邏輯函數(shù)2.334案例IF函數(shù)常用函數(shù)—日期函數(shù)2.390SUMIF/DATEDIF/VALUE函數(shù)：計算兩個日期的相隔年數(shù)、月數(shù)、天數(shù)應(yīng)收保費賬齡計算中經(jīng)常會引用例如：計算應(yīng)收保費逾期天數(shù)=DATEDIF(D2,G15,"d")或=VALUE(G15-D2)DATEDIF/VALUE日期顯示COUNTA函數(shù)：返回單元格內(nèi)非空值的個數(shù)=COUNTA（參數(shù)1，參數(shù)2，......)

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

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

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

這兩個函數(shù)常常混合使用?。?！常用函數(shù)—文本函數(shù)2.340SEARCH函數(shù):用來返回指常用函數(shù)—文本函數(shù)2.396目的：根據(jù)客戶信息提取保單號（客戶信息為A列，保單號為B列）方法：單擊B2單元格，在編輯欄輸入公式：=RIGHT(A2,LEN(A2)-SEARCH("P",A2,6)+1)RIGHT函數(shù)：表示從右邊取數(shù)LEN函數(shù)：整個文本字串符個數(shù)SEARCH函數(shù)：返回指定符號字串符的位置常用函數(shù)—文本函數(shù)2.341目的：根據(jù)客戶信息提取保單號常用函數(shù)—排序函數(shù)2.397SUMIF/RankLARGERANK排名函數(shù)語義規(guī)則：=RANK(number,ref,order)其中，number為要排名的單元格或數(shù)字，ref為區(qū)域范圍，order為排名的順序，即降