財(cái)務(wù)ECEL操作技巧培訓(xùn)頁

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

2、篩選字段又是行或列字段2. 同一數(shù)值字段可以拖放多個(gè)到數(shù)值字段區(qū)域3. 每次拖放字段到數(shù)據(jù)透視表中時(shí)，EXCEL都會(huì)重新更新（可推遲 更新） 9分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項(xiàng)1.3數(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)） 10分析與篩選數(shù)據(jù)-數(shù)據(jù)重復(fù)項(xiàng)1.3數(shù)據(jù)重復(fù)項(xiàng) 刪 除查 找查找重復(fù)項(xiàng)：開始條件格式重復(fù)值11分析與篩選數(shù)據(jù)-數(shù)據(jù)排序1.3數(shù)據(jù)排序清單排序高級(jí)排序數(shù)據(jù)清單排序 如果按照單列的內(nèi)容進(jìn)行排序，可直接“數(shù)據(jù)”選項(xiàng)卡“ ” ”進(jìn)行排序高級(jí)排序 按照多個(gè)條件對(duì)清單進(jìn)行排序12分析與篩選數(shù)據(jù)-數(shù)據(jù)篩選1.3數(shù)據(jù)篩選自動(dòng)篩選自定義篩

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

4、匯總注意事項(xiàng)： 匯總前排序 在進(jìn)行分類匯總前，一定要把需要按其匯總的字段進(jìn)行排序，否則無法實(shí)現(xiàn)匯總。不足之處 如有多列行字段頭，分類匯總之后，則只保留匯總 字段，其他字段無法體現(xiàn)，相比之下，當(dāng)需求字段 多時(shí)，使用數(shù)據(jù)透視表。16分析與篩選數(shù)據(jù)-數(shù)據(jù)分級(jí)顯示1.3分級(jí)顯示 有時(shí)，我們既想看到匯總數(shù)據(jù)，又想看到每一個(gè)匯總數(shù)據(jù)下的明細(xì)數(shù)據(jù)，那么勢(shì)必導(dǎo)致在一張表里數(shù)據(jù)行較多。 但電腦屏幕有限，不停的上下翻頁非常的不便利，也不直觀，有沒有其他好辦法呢？分級(jí)顯示17分析與篩選數(shù)據(jù)-數(shù)據(jù)分級(jí)顯示1.3操作方法：數(shù)據(jù)組合選中組數(shù)進(jìn)行分級(jí)顯示18分析與篩選數(shù)據(jù)-數(shù)據(jù)有效性1.3數(shù)據(jù)有效性數(shù)據(jù)有效性： 防止重復(fù)信

5、息防止人工輸入錯(cuò)誤快速揪出無效數(shù)據(jù)自定義序列19分析與篩選數(shù)據(jù)-數(shù)據(jù)有效性1.3數(shù)據(jù)有效性選定目標(biāo)單元格數(shù)據(jù)數(shù)據(jù)驗(yàn)證在數(shù)據(jù)有效性窗口中選擇-設(shè)置允許里面選擇“序列”選項(xiàng)輸入鎖定來源（注意逗號(hào)要為全英文半角狀態(tài)下顯示）或直接選擇列20分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.3數(shù)據(jù)分列 一列分多列 對(duì)一列需要根據(jù)共同字符（含空格）分列的數(shù)據(jù)進(jìn)行分列，也可以強(qiáng)制指定字符寬度進(jìn)行分列。去除亂字符 對(duì)外部引入的數(shù)據(jù)進(jìn)行處理，去除TAB符、單分號(hào)符、占位符、部分位于首尾的空格符。21分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.3分隔符號(hào) 分列 選項(xiàng)卡“數(shù)據(jù)” 分列點(diǎn)擊”完成” 正常情況下，按圖二界面，其他選項(xiàng)保持默認(rèn)，然后點(diǎn)擊“完成

6、”結(jié)束數(shù)據(jù)分列。22分析與篩選數(shù)據(jù)-數(shù)據(jù)分列1.3固定寬度 分列 選項(xiàng)卡“數(shù)據(jù)” 分列點(diǎn)擊”完成”可以通過鼠標(biāo)設(shè)置一條或多條分隔線，將字條按任意方式進(jìn)行分隔處理正常情況下，按圖二界面，其他選項(xiàng)保持默認(rèn)，然后點(diǎn)擊“完成”結(jié)束數(shù)據(jù)分列。23分析與篩選數(shù)據(jù)-快捷組合鍵1.4快捷組合鍵快捷組合鍵：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ù)替換

7、Ctrl+Z 撤銷24 第二部分常用數(shù)據(jù)計(jì)算PART 02252.1公式引用-參考值“A1”絕對(duì)引用相對(duì)引用混合引用231被引用的單元格隨位置變化而變化相對(duì)地址：B1(復(fù)制時(shí)，相對(duì)位置不變）被引用單元格不隨位置改變絕對(duì)地址：$A$1(復(fù)制時(shí)，始終不變）相對(duì)地址：$A1、A$1(上述相對(duì)，絕對(duì)混合）26公式中的錯(cuò)誤2.227常用函數(shù) 求和函數(shù)2.3SUMSUMIF/SSUM函數(shù)：無條件求和=SUM（參數(shù)1，參數(shù)2，.參數(shù)N)Tips:求和快捷鍵快速求和“ALT+=”SUMIF函數(shù)：條件求和=SUMIF（range，criteria，sum_range) 條件區(qū)域 條件 條件求和范圍 SUMIFS

8、函數(shù)：多條件求和 =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .) 條件求和范圍 條件區(qū)域1 條件1 條件區(qū)域2 條件228常用函數(shù) 求和函數(shù)2.3案例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 條件229常用函數(shù) 數(shù)學(xué)函數(shù)2.3S

9、UMAVERGAE求N個(gè)數(shù)的平均值=AVERGAE(參數(shù)1，參數(shù)2.) MAX MIN ABS求N個(gè)數(shù)的最大值求N個(gè)數(shù)的最小值求某數(shù)的絕對(duì)值 =MAX(參數(shù)1，參數(shù)2.) =MIN(參數(shù)1，參數(shù)2.)=ABS(參數(shù)1)30常用函數(shù) 計(jì)數(shù)函數(shù)2.3SUMIF/COUNTIF函數(shù)：區(qū)域內(nèi)滿足給定條件的單元格數(shù)目=COUNTIF（range，criteria) 區(qū)域 條件COUNTIF COUNTACOUNTA函數(shù)：返回單元格內(nèi)非空值的個(gè)數(shù)=COUNTA（參數(shù)1，參數(shù)2，.) 31常用函數(shù) 計(jì)數(shù)函數(shù)2.3SUMIF/ 案例COUNTIF函數(shù)：計(jì)算保費(fèi)金額大于500的保單數(shù)輸入公式=COUNTIF(C

10、2:C12,500) 區(qū)域 條件32常用函數(shù) 條件函數(shù)和邏輯函數(shù)2.3SUMIF/COUNTIF COUNTAIF函數(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)合使用33常用函數(shù) 條件函數(shù)和邏輯函數(shù)2.3 案例IF函數(shù):1、判斷A列與B列核算單位是否一致 2、算出獎(jiǎng)勵(lì)

11、政策（保費(fèi)收入大于500，獎(jiǎng)勵(lì)政策為保費(fèi)的 8%，小于500為3%1、輸入公式 =if(A1=B1,0,1)語義：如果單元格A1等于B1，顯示為真（0），否則為假（1）2、輸入公式=IF(D2500,D2*$D$14,D2*$D$15)語義：如果總保費(fèi)大于500，則總保費(fèi)乘以8%，否則總保費(fèi)乘以3%34常用函數(shù) 日期函數(shù)2.3SUMIF/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)

12、非空值的個(gè)數(shù)=COUNTA（參數(shù)1，參數(shù)2，.) 35常用函數(shù) 查找匹配函數(shù)2.3VLOOKUPVLOOKUP函數(shù)：在表格或數(shù)值組的首列查找制定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列制定行處的數(shù)值。函數(shù)語法規(guī)則如下：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)36常用函數(shù) 條件函數(shù)和邏輯函數(shù)2.3案例：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：模糊查找3

13、7常用函數(shù) 文本函數(shù)2.3解決方法：分列：指定列分列完成CLEAN函數(shù)：刪除文本中不能打印的字符TEXT函數(shù)：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本TRIM函數(shù)：取消字符串兩側(cè)的空格示文本函數(shù)問題：兩個(gè)單元格完全一樣，但卻無法匹配？ 表象蒙蔽數(shù)據(jù)清理兩個(gè)看上去完全一樣的單元格，其實(shí)往往包含了不同的字串符！38常用函數(shù) 四舍五入、整數(shù)、文本函數(shù)2.3 MIN ABS求某數(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ù)：獲

14、取字串符中間指定個(gè)數(shù)的文本=MID( text, start_num, num_chars)39常用函數(shù) 文本函數(shù)2.3SEARCH函數(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ù)常?；旌鲜褂?！40常用函數(shù) 文本函數(shù)2.3目的：根據(jù)客戶信息提取保單號(hào)（客戶信息為A列，保單號(hào)為B列）方

15、法：?jiǎn)螕鬊2單元格，在編輯欄輸入公式： =RIGHT(A2,LEN(A2)-SEARCH(P,A2,6)+1)RIGHT函數(shù)：表示從右邊取數(shù)LEN函數(shù)：整個(gè)文本字串符個(gè)數(shù)SEARCH函數(shù)：返回指定符號(hào)字串符的位置41常用函數(shù) 排序函數(shù)2.3SUMIF/ Rank LARGERANK 排名函數(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è)最大值的

16、 數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域，k 為返回的數(shù)據(jù)在 數(shù)組或數(shù)據(jù)區(qū)域里的位置(從大到小)。SMALL函數(shù)同理42常用函數(shù) 條件函數(shù)和邏輯函數(shù)2.3案例：根據(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) 需要排序的值 排序引用的范圍 升序或降序43 第三部分EXCEL快捷技巧PART 0444EXCEL快捷技巧3快速轉(zhuǎn)換不同的工作表：”CRTL(+SHIFT)+PgDn/PgUp”快速移動(dòng)：按下Ctrl+相應(yīng)的方向鍵或方向鍵組合。例如按下“Crtl+”即可到達(dá)選中最下方的單元格快速選?。喊聪?/p>

17、 Ctrl+Shift+ 相應(yīng)的方向鍵或方向鍵組合，再?gòu)?fù)制即可自動(dòng)換行：如果出現(xiàn)多個(gè)保單號(hào)，需拆分為不同的列采用行列轉(zhuǎn)置：選擇需要轉(zhuǎn)置的數(shù)據(jù)（包括所有行或列標(biāo)簽）,按下Ctrl+C，選擇性粘貼中的轉(zhuǎn)置即可45EXCEL快捷技巧3快速恢復(fù)E+的數(shù)字格式顯示：數(shù)字長(zhǎng)度大于11為會(huì)以科學(xué)計(jì)數(shù)顯示，分列選擇文本格式顯示即可。快速求行與列合計(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利用選擇性粘貼快速加減乘除常用常忽視少用47 第四部分EXCEL輸出PART 0448EXCEL表格保護(hù)4 有時(shí)，要求分公司、支公司、業(yè)務(wù)員、出單員等報(bào)送一定的表格，為了簡(jiǎn)化，表格全部設(shè)好，只需要相關(guān)人員往指定的空格錄入即可。 可是表格發(fā)下去，經(jīng)常被改的面目全非。公式、格式、匯總數(shù)據(jù)都不對(duì)，怎么辦？保護(hù)工作表只允許選定指定區(qū)域49EXCEL表格保護(hù)4