電子表格培訓(xùn)

1、電子表格在審計(jì)中的應(yīng)用授課教師：李婭QQ:1103279210Phonemail:rzq_數(shù)據(jù)審計(jì)思路一：勾稽關(guān)系的復(fù)核：將具有內(nèi)在聯(lián)系的數(shù)據(jù)，按照勾稽關(guān)系進(jìn)行相互核對(duì)，達(dá)到驗(yàn)證被審計(jì)單位業(yè)務(wù)處理是否正確，有無人為違規(guī)調(diào)整等目的。ABNXY?是否相等財(cái)務(wù)數(shù)據(jù)財(cái)務(wù)數(shù)據(jù)業(yè)務(wù)數(shù)據(jù)業(yè)務(wù)數(shù)據(jù)文本文本TXT電子表格電子表格XLSX數(shù)據(jù)庫數(shù)據(jù)數(shù)據(jù)庫數(shù)據(jù)。醫(yī)保審計(jì)數(shù)據(jù)案例驗(yàn)證思路：判斷所有醫(yī)藥銷售記錄是否遵循了藥品數(shù)量、藥品單價(jià)與藥費(fèi)之間應(yīng)有的勾稽關(guān)系。實(shí)踐：借款憑證表.xlsx：請(qǐng)檢查借款憑證表中是否存在借款金額不等于本憑證累計(jì)收回貸款、本憑證貸款余額、核銷金額之和的記錄。醫(yī)保藥品

2、審計(jì).xlsx文件中的醫(yī)保中心銷售記錄工作表：請(qǐng)檢查應(yīng)收藥費(fèi)、個(gè)人交費(fèi)和醫(yī)保比例之間是否符合應(yīng)有的勾稽關(guān)系，并使用WPS電子表格工具進(jìn)行驗(yàn)證。 身份證號(hào)碼數(shù)據(jù)案例 根據(jù)數(shù)據(jù)中的身份證號(hào)碼，驗(yàn)證出生日期和性別，查看是否有身份證號(hào)碼與出生日期和性別不符的情況 身份證號(hào)碼構(gòu)成原則：（見下表） 該驗(yàn)證過程用到電子表格的公式函數(shù)功能，可以在參考書或網(wǎng)絡(luò)上學(xué)習(xí)如下函數(shù)的用法： LEN() / LENB() MID() / MIDB() LEFT() / LEFTB() RIGHT() / RIGHTB() IF() TRIM() 實(shí)踐： 身份證號(hào)碼審計(jì).xlsx中提供的數(shù)據(jù)，根據(jù)講解案例實(shí)現(xiàn)的思路，試填充

3、該電子表格 根據(jù)身份證號(hào)碼中得到的出生日期和性別，查看是否有身份證與出生日期和性別不符的情況數(shù)據(jù)審計(jì)思路二： 合規(guī)性驗(yàn)證：按照方針政策、法律、法規(guī)、內(nèi)部控制等，對(duì)被審計(jì)單位的數(shù)據(jù)進(jìn)行檢查，達(dá)到確定業(yè)務(wù)處理是否合法、合規(guī)的目的。法律法律法規(guī)法規(guī)政策政策條款條款。建立模型建立模型工具描述工具描述驗(yàn)證驗(yàn)證車購(gòu)稅=計(jì)稅價(jià)格*10%B1單元格中輸入：=A1*0.1車量購(gòu)置稅案例 根據(jù)交通部規(guī)定，國(guó)產(chǎn)車車輛購(gòu)置稅應(yīng)按發(fā)票價(jià)的10%計(jì)征；后來為杜絕因一輛車開具多張票據(jù)而造成少征車購(gòu)稅的現(xiàn)象，交通部又頒布了車購(gòu)稅最低征收額標(biāo)準(zhǔn)，明確只要發(fā)票價(jià)的10%小于最低征收額的，就按最低征收額征收車購(gòu)稅。審計(jì)思路： 根據(jù)

4、車購(gòu)稅的條款，建立數(shù)據(jù)模型應(yīng)征金額MAX(發(fā)票價(jià)0.1，最低征收額 )違規(guī)金額ABS（應(yīng)征金額實(shí)際征收額 ） 使用電子表格對(duì)模型進(jìn)行計(jì)算，得到應(yīng)收車購(gòu)稅 將應(yīng)收車購(gòu)稅與實(shí)際征收車購(gòu)稅相比對(duì)，發(fā)現(xiàn)是否有違規(guī)征收的情況 若存在不符情況，統(tǒng)計(jì)出不合規(guī)的征收費(fèi)（少征、多征兩種情況） 實(shí)踐： 車輛購(gòu)置稅.xlsx：按照交通部規(guī)定，對(duì)車購(gòu)稅是否按照標(biāo)準(zhǔn)征收進(jìn)行審查，若有不合規(guī)的征收費(fèi)用，統(tǒng)計(jì)應(yīng)征收的費(fèi)用與實(shí)際征收費(fèi)用的差額。 提示：該案例中試用如下函數(shù)MAX()、ABS()，SUMIF()、SUBTOTAL().函數(shù)功能請(qǐng)參考網(wǎng)絡(luò)或者教材附：SUBTOTAL()第一個(gè)參數(shù)說明101 AVERAGE（平均值

5、）102 COUNT （數(shù)值計(jì)數(shù)）103 COUNTA （包涵文本計(jì)數(shù)）104 MAX （最大值）105 MIN （最小值）106 PRODUCT (所有數(shù)的積) 107 STDEV （估算樣本的標(biāo)準(zhǔn)偏差，反映了偏離相對(duì)于平均值的離散程度） 108 STDEVP （返回整個(gè)樣本總體的標(biāo)準(zhǔn)偏差。它反映了樣本總體相對(duì)于平均值的離散程度） 109 SUM （求和） 110 VAR （估算基于給定樣本的方差） 個(gè)人所得稅案例個(gè)人所得稅現(xiàn)行方法應(yīng)納個(gè)人所得稅稅額=應(yīng)納稅所得額適用稅率-速算扣除數(shù)扣除標(biāo)準(zhǔn)3500元/月（工資、薪金所得適用）個(gè)稅計(jì)算7級(jí)標(biāo)準(zhǔn)（請(qǐng)見下表） 審計(jì)思路 按照對(duì)個(gè)人所得稅的現(xiàn)行方法

6、，對(duì)其進(jìn)行模型的建立 實(shí)踐 對(duì)個(gè)人所得稅.xlsx的計(jì)稅工資，根據(jù)模型，試用IF()函數(shù)的嵌套實(shí)現(xiàn)個(gè)人所得稅的計(jì)算 可以有多種方法實(shí)現(xiàn)，個(gè)人所得稅(x).xlsx工作表中，提供了另外兩種實(shí)現(xiàn)方法，根據(jù)學(xué)員各人的情況，選擇掌握 實(shí)踐 公積金繳存案例 審計(jì)數(shù)據(jù)：公積金繳存案例.xlsx文件中的公積金繳存表 審計(jì)思路：根據(jù)公積金繳存規(guī)定，建立公積繳存模型注意考慮到各種違規(guī)情況 該案例數(shù)據(jù)中，公積金的繳存比例最高12%，當(dāng)?shù)氐钠骄べY為1400，要求：公積金繳存不能高于當(dāng)?shù)毓べY的3倍，試用WPS電子表格實(shí)現(xiàn)違規(guī)數(shù)據(jù)的查找和統(tǒng)計(jì)數(shù)據(jù)審計(jì)思路三： 審計(jì)數(shù)據(jù)關(guān)聯(lián)性的驗(yàn)證：在不同的數(shù)據(jù)系統(tǒng)中，相關(guān)數(shù)據(jù)銜接是否

7、嚴(yán)密，邏輯關(guān)系是否一致，避免主觀認(rèn)為計(jì)算機(jī)處理不會(huì)存在問題 個(gè)人住房貸款案例 根據(jù)中國(guó)人民銀行個(gè)人住房貸款管理辦法的要求，個(gè)人住房公積金貸款放款前必須辦理抵押登記手續(xù)。在審計(jì)時(shí)，審計(jì)人員從房產(chǎn)中心取得了住房公積金個(gè)人貸款房產(chǎn)抵押表，這張表中的抵押人就是辦理了抵押登記手續(xù)的貸款人；審計(jì)人員還從住房公積金管理分中心取得了個(gè)人貸款信息表，這張表中的人員就是所有取得貸款的人。由此可知，將兩邊數(shù)據(jù)進(jìn)行比對(duì)，發(fā)現(xiàn)問題線索。 審計(jì)思路： 個(gè)人貸款信息中的貸款人應(yīng)該是房產(chǎn)抵押表中的抵押人 驗(yàn)證兩張表中信息的關(guān)聯(lián)性，若不存在，說明兩表數(shù)據(jù)存在不一致性 實(shí)踐 根據(jù)個(gè)人住房貸款審計(jì).xlsx提供數(shù)據(jù)，驗(yàn)證個(gè)人貸款信

8、息中的貸款人是否出現(xiàn)在房產(chǎn)抵押表的抵押人中 提示：該案例中試用如下函數(shù)VLOOKUP()函數(shù)功能請(qǐng)參考網(wǎng)絡(luò)或者教材 實(shí)踐 專業(yè)系統(tǒng)數(shù)據(jù)與財(cái)務(wù)軟件數(shù)據(jù)核對(duì)案例 審計(jì)數(shù)據(jù)：財(cái)務(wù)軟件引出與專業(yè)軟件引出核對(duì).xlsx文件 審計(jì)背景：隨著信息化的發(fā)展，被審計(jì)單位往往都具有各自行業(yè)的業(yè)務(wù)軟件，業(yè)務(wù)軟件與財(cái)務(wù)軟件往往采用同時(shí)記賬的方式，對(duì)于兩個(gè)系統(tǒng)導(dǎo)出數(shù)據(jù)的核對(duì)，也可以作為審計(jì)業(yè)務(wù)中的一個(gè)方向。 財(cái)務(wù)軟件引出與專業(yè)軟件引出核對(duì).xlsx文件中包含ERP賬面數(shù)和財(cái)務(wù)賬面數(shù)兩個(gè)工作表，試對(duì)兩個(gè)引出的數(shù)據(jù)表進(jìn)行核對(duì)，查找是否存在差異。如果存在差異，還要查出是哪個(gè)編碼的商品出現(xiàn)了差異。 練習(xí) 對(duì)工程項(xiàng)目統(tǒng)計(jì).xls

9、x提供的數(shù)據(jù)，核實(shí)各類型項(xiàng)目的支出情況 VLOOKUP()函數(shù)的應(yīng)用 練習(xí) 個(gè)人所得稅案例 試用VLOOKUP()函數(shù)實(shí)現(xiàn)個(gè)人所得稅的計(jì)算數(shù)據(jù)審計(jì)思路四： 分析性復(fù)核：根據(jù)審計(jì)人員的經(jīng)驗(yàn)，對(duì)數(shù)據(jù)的某些特征進(jìn)行統(tǒng)計(jì)分析，將可能有問題的分析結(jié)果作為審計(jì)線索進(jìn)行驗(yàn)證 銷售收入真實(shí)性審計(jì)案例 商品銷售明細(xì)表中顯示了商品銷售相關(guān)的業(yè)務(wù)數(shù)據(jù)，為了與財(cái)務(wù)數(shù)據(jù)的結(jié)果相比對(duì)，通常要對(duì)其進(jìn)行各種統(tǒng)計(jì)。 根據(jù)需求，對(duì)以下業(yè)務(wù)數(shù)據(jù)進(jìn)行統(tǒng)計(jì) 1）對(duì)所有商品的銷售金額進(jìn)行統(tǒng)計(jì) 2）各類商品的銷售金額進(jìn)行統(tǒng)計(jì) 3）查看單位中通購(gòu)買商品糖果的金額 審計(jì)思路 根據(jù)審計(jì)目的的需求，統(tǒng)計(jì)出相應(yīng)業(yè)務(wù)數(shù)據(jù) 與相關(guān)財(cái)務(wù)數(shù)據(jù)相比對(duì)，判斷是

10、否有不合規(guī)的數(shù)據(jù)修改 實(shí)踐 對(duì)銷售收入真實(shí)性審計(jì).xlsx中商品銷售明細(xì)表中的數(shù)據(jù)進(jìn)行如下三種數(shù)據(jù)的統(tǒng)計(jì) 1）對(duì)所有商品的銷售金額進(jìn)行統(tǒng)計(jì) 2）各類商品的銷售金額進(jìn)行統(tǒng)計(jì) 3）查看單位中通購(gòu)買商品糖果的金額 提示：該案例中試用如下函數(shù)SUM()，SUMIF()函數(shù)功能請(qǐng)參考網(wǎng)絡(luò)或者教材練習(xí) 審計(jì)人員需要了解某單位“現(xiàn)金”科目總體情況，試用電子表格展現(xiàn)各月“現(xiàn)金”科目的會(huì)計(jì)月份、科目編碼、科目名稱、借方發(fā)生額、貸方發(fā)生額等字段 打開某企業(yè)憑證表.xlsx a）統(tǒng)計(jì)各月份現(xiàn)金科目的貸方發(fā)生額 b）使用圖表展示各月份的貸方發(fā)生額，將不正常的月份確定為審計(jì)疑點(diǎn) c）檢索出貸方發(fā)生額最高的月份的現(xiàn)金科目

11、憑證信息 d）檢索結(jié)果按貸方金額進(jìn)行降序排列后，第一條記錄即是當(dāng)月現(xiàn)金支出最多的一筆業(yè)務(wù)，將此筆業(yè)務(wù)作為疑點(diǎn)保存。 圖表展現(xiàn)參考下圖 預(yù)算經(jīng)費(fèi)超支審計(jì)案例對(duì)預(yù)算經(jīng)費(fèi)超支審計(jì)數(shù)據(jù)進(jìn)行各種匯總，應(yīng)用到電子表格的排序、分類匯總、圖表、數(shù)據(jù)透視表等數(shù)據(jù)分析方法和形式查看顯示，發(fā)現(xiàn)審計(jì)疑點(diǎn)。 審計(jì)過程 打開預(yù)算經(jīng)費(fèi)超支審計(jì).xlsx文件中的預(yù)算經(jīng)費(fèi)超支明細(xì)表工作表，計(jì)算超支超支額和超支率。 將預(yù)算經(jīng)費(fèi)支出明細(xì)表工作表復(fù)制一份，命名為“各部門匯總統(tǒng)計(jì)”，在“各部門匯總統(tǒng)計(jì)”工作表中統(tǒng)計(jì)各部門的實(shí)際支出數(shù)、年初預(yù)算數(shù)、超支額、超支率。 將匯總結(jié)果拷貝到新的工作表“超支部門情況表”中，自動(dòng)篩選出超支率0的數(shù)據(jù)

12、，并復(fù)制成兩張新的工作表，分別為“超支部門的超支率圖表”和“超支較嚴(yán)重部門統(tǒng)計(jì)”。 在“超支部門的超支率圖表”工作表中，創(chuàng)建超支率圖表。 在“超支較嚴(yán)重部門統(tǒng)計(jì)”工作表中，利用高級(jí)篩選統(tǒng)計(jì)超支較嚴(yán)重部門（超支額在100000以上，或超支率在各超支部門超支率的平均值以上）數(shù)據(jù)采集和清理 數(shù)據(jù)采集 文本數(shù)據(jù)的采集 a)固定長(zhǎng)度 b)分隔符 c)長(zhǎng)文本字符數(shù)據(jù) d)日期型的數(shù)據(jù) 數(shù)據(jù)庫數(shù)據(jù)的采集 a)鏈接 b)部分?jǐn)?shù)據(jù)的采集 實(shí)踐練習(xí) 將貸款利率定長(zhǎng).txt文本文件中的所有數(shù)據(jù)采集到WPS電子表格中。 將長(zhǎng)文本數(shù)據(jù).txt文本文件中的所有數(shù)據(jù)采集到WPS電子表格中。 將銷售數(shù)據(jù)庫.accdb數(shù)據(jù)庫文件的銷售記錄表中東南部地區(qū)銷量在500以上的數(shù)據(jù)，采集到WPS電子表格中，且結(jié)果依據(jù)銷量升序排序。 數(shù)據(jù)清理 1.導(dǎo)入數(shù)據(jù)過程的清理（日期型） 2.數(shù)據(jù)格式的轉(zhuǎn)換 a)長(zhǎng)數(shù)字的顯示、設(shè)置文本型代碼、小數(shù)點(diǎn)對(duì)齊（單元格自定義格式） b)將公式的值變成實(shí)際數(shù)值（選擇性粘貼