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

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

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

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

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

處理步驟：“完成”創(chuàng)建統(tǒng)計(jì)表格1.15IT提數(shù)：.unl格式直接拖入EXCE分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表創(chuàng)建1.260505數(shù)據(jù)透視表:一種交互式的表，可以進(jìn)行某些計(jì)算，如求和與計(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注意事項(xiàng)分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表1.280505注意事項(xiàng)分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.290505

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

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

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

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

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

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

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

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

→

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

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

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

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

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

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

正常情況下，按圖二界面，其他選項(xiàng)保持默認(rèn)，然后點(diǎn)擊“完成”結(jié)束數(shù)據(jù)分列。分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.322分隔符號(hào)分列—選項(xiàng)卡“分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.323固定寬度分列—選項(xiàng)卡“數(shù)據(jù)”分列點(diǎn)擊”完成”可以通過鼠標(biāo)設(shè)置一條或多條分隔線，將字條按任意方式進(jìn)行分隔處理正常情況下，按圖二界面，其他選項(xiàng)保持默認(rèn)，然后點(diǎn)擊“完成”結(jié)束數(shù)據(jù)分列。分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.323固定寬度分列—選項(xiàng)卡“分析與篩選數(shù)據(jù)-快捷組合鍵1.424快捷組合鍵快捷組合鍵：Ctrl+C復(fù)制選定的單元格Ctrl+X剪切選定的單元格Alt+;選定指定單元格Ctrl+A選擇整個(gè)工作表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ù)計(jì)算PART0225第二部分PART02252.1公式引用-參考值“A1”26絕對(duì)引用相對(duì)引用混合引用231被引用的單元格隨位置變化而變化相對(duì)地址：B1(復(fù)制時(shí)，相對(duì)位置不變）被引用單元格不隨位置改變絕對(duì)地址：$A$1(復(fù)制時(shí)，始終不變）相對(duì)地址：$A1、A$1(上述相對(duì)，絕對(duì)混合）2.1公式引用-參考值“A1”26絕對(duì)引用相對(duì)引用混合引用2公式中的錯(cuò)誤2.227公式中的錯(cuò)誤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ù):求各支公司保費(fèi)合計(jì)數(shù)SUMIFS函數(shù):求各支公司不同銷售渠道的保費(fèi)合計(jì)數(shù)輸入公式

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

核算單位

目標(biāo)單位求和范圍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個(gè)數(shù)的平均值=AVERGAE(參數(shù)1，參數(shù)2..)MAXMINABS求N個(gè)數(shù)的最大值求N個(gè)數(shù)的最小值求某數(shù)的絕對(duì)值=MAX(參數(shù)1，參數(shù)2..)=MIN(參數(shù)1，參數(shù)2..)=ABS(參數(shù)1)常用函數(shù)—數(shù)學(xué)函數(shù)2.330SUMAVERGAE求N個(gè)數(shù)常用函數(shù)—計(jì)數(shù)函數(shù)2.331SUMIF/COUNTIF函數(shù)：區(qū)域內(nèi)滿足給定條件的單元格數(shù)目=COUNTIF（range，criteria)

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

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

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

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

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

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

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

2、算出獎(jiǎng)勵(lì)政策（保費(fèi)收入大于500，獎(jiǎng)勵(lì)政策為保費(fèi)的

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

常用函數(shù)—日期函數(shù)2.335SUMIF/DATEDIF/常用函數(shù)—查找匹配函數(shù)2.336VLOOKUPVLOOKUP函數(shù)：在表格或數(shù)值組的首列查找制定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列制定行處的數(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的被保險(xiǎn)人名稱輸入公式：

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

截取保單號(hào)機(jī)構(gòu)代碼使用MID函數(shù)：獲取字串符中間指定個(gè)數(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：要在哪一個(gè)字符串查找start_num：從within_text的第幾個(gè)字符開始查找LEN函數(shù)：反映文本字串符個(gè)數(shù)

這兩個(gè)函數(shù)常?；旌鲜褂茫。。〕Ｓ煤瘮?shù)—文本函數(shù)2.340SEARCH函數(shù):用來返回指常用函數(shù)—文本函數(shù)2.341目的：根據(jù)客戶信息提取保單號(hào)（客戶信息為A列，保單號(hào)為B列）方法：單擊B2單元格，在編輯欄輸入公式：=RIGHT(A2,LEN(A2)-SEARCH("P",A2,6)+1)RIGHT函數(shù)：表示從右邊取數(shù)LEN函數(shù)：整個(gè)文本字串符個(gè)數(shù)SEARCH函數(shù)：返回指定符號(hào)字串符的位置常用函數(shù)—文本函數(shù)2.341目的：根據(jù)客戶信息提取保單號(hào)常用函數(shù)—排序函數(shù)2.342SUMIF/RankLARGERANK排名函數(shù)語義規(guī)則：=RANK(number,ref,order)其中，number為要排名的單元格或數(shù)字，ref為區(qū)域范圍，order為排名的順序，即降序（用0表示）或升序（用1表示）。LARGE：返回?cái)?shù)據(jù)集中的第K個(gè)最大值。語義規(guī)則：=LARGE(array,k)其中，array為需要找到第k個(gè)最大值的數(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ù)表一信息，給各分公司保費(fèi)收入進(jìn)行排名結(jié)果方法：創(chuàng)建數(shù)據(jù)透視表，求出各個(gè)分公司保費(fèi)收入引入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”快速移動(dòng)：按下Ctrl+相應(yīng)的方向鍵或方向鍵組合。例如按下“Crtl+↓”即可到達(dá)選中最下方的單元格快速選?。喊聪翪trl+Shift+相應(yīng)的方向鍵或方向鍵組合，再復(fù)制即可自動(dòng)換行：如果出現(xiàn)多個(gè)保單號(hào)，需拆分為不同的列采用行列轉(zhuǎn)置：選擇需要轉(zhuǎn)置的數(shù)據(jù)（包括所有行或列標(biāo)簽）,按下Ctrl+C，選擇性粘貼中的轉(zhuǎn)置即可45EXCEL快捷技巧3自動(dòng)換行：如果出現(xiàn)多個(gè)保單號(hào)，需拆分為不EXCEL快捷技巧3快速恢復(fù)E+的數(shù)字格式顯示：數(shù)字長度大于11為會(huì)以科學(xué)計(jì)數(shù)顯示，分列選擇文本格式顯示即可?？焖偾笮信c列合計(jì)數(shù)：Ctrl+G→定位條件→空值→“ALT+=“多單元格批量輸入相同的內(nèi)容：將想要輸入內(nèi)容的單元格一起選中，然后在編輯欄中輸入想要的內(nèi)容，最后按下”Ctrl+Enter”（編輯欄不顯示→選項(xiàng)→高級(jí)→顯示→顯示編輯欄）對(duì)比同行數(shù)據(jù)是否相同：Ctrl+G→定位條件→行內(nèi)容差異單元格→填充顏色快速刪除錯(cuò)誤值：選擇需要定位的區(qū)域，按下快捷鍵Ctrl+G,打開“定位”對(duì)話框→選擇“定位條件”中的“公式”→“錯(cuò)誤”，成功定位后批量清除46EXCEL快捷技巧3對(duì)比同行數(shù)據(jù)是否相同：Ctrl+G→定位EXCEL快捷技巧3利用選擇性粘貼快速加減乘除47常用常忽視少用EXCEL快捷技巧3利用選擇性粘貼快速加減乘除47常用常忽視

第四部分EXCEL輸出PART0448第四部分PART0448EXCEL表格保護(hù)449

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

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

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

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

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

處理步驟：“完成”創(chuàng)建統(tǒng)計(jì)表格1.15IT提數(shù)：.unl格式直接拖入EXCE分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表創(chuàng)建1.2610505數(shù)據(jù)透視表:一種交互式的表，可以進(jìn)行某些計(jì)算，如求和與計(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注意事項(xiàng)分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表1.280505注意事項(xiàng)分析與篩選數(shù)據(jù)-數(shù)據(jù)透視表布局1.2640505

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

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

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

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

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

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

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

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

→

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

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

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

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

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

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

正常情況下，按圖二界面，其他選項(xiàng)保持默認(rèn)，然后點(diǎn)擊“完成”結(jié)束數(shù)據(jù)分列。分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.322分隔符號(hào)分列—選項(xiàng)卡“分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.378固定寬度分列—選項(xiàng)卡“數(shù)據(jù)”分列點(diǎn)擊”完成”可以通過鼠標(biāo)設(shè)置一條或多條分隔線，將字條按任意方式進(jìn)行分隔處理正常情況下，按圖二界面，其他選項(xiàng)保持默認(rèn)，然后點(diǎn)擊“完成”結(jié)束數(shù)據(jù)分列。分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.323固定寬度分列—選項(xiàng)卡“分析與篩選數(shù)據(jù)-快捷組合鍵1.479快捷組合鍵快捷組合鍵：Ctrl+C復(fù)制選定的單元格Ctrl+X剪切選定的單元格Alt+;選定指定單元格Ctrl+A選擇整個(gè)工作表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ù)計(jì)算PART0280第二部分PART02252.1公式引用-參考值“A1”81絕對(duì)引用相對(duì)引用混合引用231被引用的單元格隨位置變化而變化相對(duì)地址：B1(復(fù)制時(shí)，相對(duì)位置不變）被引用單元格不隨位置改變絕對(duì)地址：$A$1(復(fù)制時(shí)，始終不變）相對(duì)地址：$A1、A$1(上述相對(duì)，絕對(duì)混合）2.1公式引用-參考值“A1”26絕對(duì)引用相對(duì)引用混合引用2公式中的錯(cuò)誤2.282公式中的錯(cuò)誤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ù):求各支公司保費(fèi)合計(jì)數(shù)SUMIFS函數(shù):求各支公司不同銷售渠道的保費(fèi)合計(jì)數(shù)輸入公式

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

核算單位

目標(biāo)單位求和范圍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個(gè)數(shù)的平均值=AVERGAE(參數(shù)1，參數(shù)2..)MAXMINABS求N個(gè)數(shù)的最大值求N個(gè)數(shù)的最小值求某數(shù)的絕對(duì)值=MAX(參數(shù)1，參數(shù)2..)=MIN(參數(shù)1，參數(shù)2..)=ABS(參數(shù)1)常用函數(shù)—數(shù)學(xué)函數(shù)2.330SUMAVERGAE求N個(gè)數(shù)常用函數(shù)—計(jì)數(shù)函數(shù)2.386SUMIF/COUNTIF函數(shù)：區(qū)域內(nèi)滿足給定條件的單元格數(shù)目=COUNTIF（range，criteria)

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

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

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

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

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

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

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

2、算出獎(jiǎng)勵(lì)政策（保費(fèi)收入大于500，獎(jiǎng)勵(lì)政策為保費(fèi)的

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

常用函數(shù)—日期函數(shù)2.335SUMIF/DATEDIF/常用函數(shù)—查找匹配函數(shù)2.391VLOOKUPVLOOKUP函數(shù)：在表格或數(shù)值組的首列查找制定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列制定行處的數(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的被保險(xiǎn)人名稱輸入公式：

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

截取保單號(hào)機(jī)構(gòu)代碼使用MID函數(shù)：獲取字串符中間指定個(gè)數(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：要在哪一個(gè)字符串查找start_num：從within_text的第幾個(gè)字符開始查找LEN函數(shù)：反映文本字串符個(gè)數(shù)

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