表格程序兩種數(shù)據(jù)加工的方法

劉可

摘 要 普通用戶加工數(shù)據(jù)時(shí)使用EXCEL等表格軟件的比率遠(yuǎn)高于數(shù)據(jù)庫管理軟件，表格軟件通過其內(nèi)置的豐富函數(shù)功能也能達(dá)到與數(shù)據(jù)庫查詢語言相同的目標(biāo)。本文是作者通過工作實(shí)踐總結(jié)的兩個(gè)較典型的數(shù)據(jù)處理案例：一是數(shù)據(jù)篩選計(jì)數(shù)，二是對(duì)多表進(jìn)行合并或比對(duì)。也可推廣適用于多種數(shù)據(jù)表的處理。

【關(guān)鍵詞】表格 計(jì)數(shù) 合并

使用SQL查詢語言的用戶能夠方便的生成符合其需求的數(shù)據(jù)集，但多數(shù)普通用戶并不能熟練掌握，處理數(shù)據(jù)常用工具以EXCEL或WPS表格多見。事實(shí)上，通過簡單的函數(shù)組合技巧，表格應(yīng)用也能快速的篩選并呈現(xiàn)用戶期望的數(shù)據(jù)集合。本文結(jié)合作者工作實(shí)踐，介紹兩種篩選數(shù)據(jù)的方法。

1 一對(duì)多關(guān)系的篩選計(jì)數(shù)

在個(gè)人外匯業(yè)務(wù)監(jiān)管中，監(jiān)管者需要探查境外機(jī)構(gòu)或個(gè)人將資金分拆匯入國內(nèi)多個(gè)個(gè)人賬戶的情形。從數(shù)據(jù)庫導(dǎo)出的表格文件來看，即是要查找相同付款人字段的多條記錄。從關(guān)系數(shù)據(jù)庫角度看，即通過付款人字段進(jìn)行分組，將收款人按計(jì)數(shù)進(jìn)行匯總。表格程序中的處理思路，首先按照付款人字段進(jìn)行表格排序。接著增加相同付款人標(biāo)志列，此列使用公式形如=IF（OR（LEFT（B2，N）=LEFT（B1，N），LEFT（B2，N）=LEFT（B3，N）），LEFT（B2，N），”--”）。其含義如下：若該行付款人字段值與相鄰（上或下）的值相同，則該標(biāo)志列的值即為付款人字段關(guān)鍵字，否則用特定符號(hào)定義該字段值。即若n行和n+1行付款人字段內(nèi)容相同，則該標(biāo)志列賦值為付款人名稱關(guān)鍵字字段內(nèi)容。這里使用LEFT函數(shù)，原因在于實(shí)際發(fā)生業(yè)務(wù)中，該字段提交內(nèi)容為文本，校驗(yàn)約束不嚴(yán)格，所以這里提取前N位字符作為判斷付款人名稱的實(shí)際關(guān)鍵字，N的值可以根據(jù)實(shí)際情況自行設(shè)置。

接著對(duì)工作表執(zhí)行插入“數(shù)據(jù)透視表”，將“對(duì)方付款人名稱”字段先后添加到行區(qū)域和數(shù)據(jù)區(qū)域（以WPS2013為工具描述），即生成所有付款人名稱相同的計(jì)數(shù)內(nèi)容，最后再將計(jì)數(shù)列按降序排列，就能直觀得出具有分拆行為的境外機(jī)構(gòu)或個(gè)人的實(shí)際分拆數(shù)量（結(jié)果見表1），并依此作為進(jìn)一步開展個(gè)人外匯業(yè)務(wù)現(xiàn)場監(jiān)管工作的數(shù)據(jù)基礎(chǔ)，做到有的放矢。

2 兩表連接實(shí)現(xiàn)表格合并或數(shù)據(jù)比對(duì)功能

在業(yè)務(wù)系統(tǒng)中導(dǎo)出的基本數(shù)據(jù)表格通常無法適應(yīng)用戶意愿，如導(dǎo)出表的收入和支出一般是兩張表，而在較多情況下用戶希望得到收入與支出的復(fù)合表；或是對(duì)于兩個(gè)不同數(shù)據(jù)來源的業(yè)務(wù)數(shù)據(jù)，如用戶希望經(jīng)過對(duì)比發(fā)現(xiàn)兩表中一致或不一致的記錄，獲取多來源數(shù)據(jù)對(duì)比核查的線索。從關(guān)系數(shù)據(jù)庫的角度分析，即是通過多表連接來比較表格的內(nèi)容，而在表格程序中可以通過以下方法來實(shí)現(xiàn)。

從兩種表的特性分析，收入表中有的機(jī)構(gòu)在支出表中不一定存在，反之亦然。現(xiàn)以收入表為基準(zhǔn)，如收入表中的機(jī)構(gòu)在支出表中存在，將支出金額字段加入到收入表中；如收入表中的機(jī)構(gòu)在支出表中不存在，則顯示支出為0，支出金額字段如下所示：=IF（ISNA（VLOOKUP（A3，Sheet1！$A$3：$A$1021，1，F(xiàn)ALSE）），0，INDIRECT（"Sheet1！D"&MATCH（A3，Sheet1！$A$3：$A$1021，0）+2））。最外層IF函數(shù)的含義為，如收入表當(dāng)前記錄表示機(jī)構(gòu)在支出表中不存在，則支出金額字段賦值為0；如存在則在支出表中找到相同機(jī)構(gòu)記錄，并將其交易金額字段值賦值給收入表的“支出金額”字段。VLOOKUP函數(shù)，用來在支出表的“組織機(jī)構(gòu)代碼”字段中查詢是否存在收入表當(dāng)前記錄所代表的機(jī)構(gòu)信息；INDIRECT函數(shù)是用來將支出金額字段作相應(yīng)定位之后賦值到收入表相應(yīng)字段中；MATCH函數(shù)用來返回支出表“交易金額”字段中查找到的對(duì)應(yīng)的行值。

將以上公式復(fù)制到每個(gè)“支出金額”單元格中，可通過MATCH函數(shù)得到收入表中某機(jī)構(gòu)在支出表中的位置，值為#N/A的表示該機(jī)構(gòu)在支出表中不存在。“支出金額”表示該機(jī)構(gòu)在同一時(shí)間段的支出總額，值為0的即表示支出表中不存在該機(jī)構(gòu)的情形。再用相同的方法，以支出表為基準(zhǔn)做一次收入金額的插入操作。最后將兩個(gè)表格粘貼到一個(gè)新表中，通過在新列插入形如=COUNTIF（A$3：A3，A3）的函數(shù)，將輔助列值為2的記錄全部刪除，從而去除重復(fù)記錄，所得結(jié)果見表2。

以上方法亦可用在數(shù)據(jù)核查工作中，用于比對(duì)不同數(shù)據(jù)來源的表間記錄的異同，從而挖掘出因數(shù)據(jù)源不統(tǒng)一造成的難以發(fā)現(xiàn)的違規(guī)線索。

以上兩種方法以及其中涉及的EXCEL或WPS表格函數(shù)對(duì)于數(shù)據(jù)集操作雖常見但功能強(qiáng)大，經(jīng)過適當(dāng)?shù)倪壿嫿M合可以達(dá)到與SQL查詢語言同樣的數(shù)據(jù)加工效果。將會(huì)對(duì)提高工作效率與業(yè)務(wù)創(chuàng)新起到極大幫助，值得經(jīng)常使用表格軟件的用戶深入探索與掌握。

參考文獻(xiàn)

[1]李興勇.外匯監(jiān)管中國呢業(yè)務(wù)數(shù)據(jù)的篩查技巧[J].金融時(shí)代科技，2011（11）：79.

作者單位

國家外匯管理局安徽省分局 安徽省合肥市 230091endprint

摘 要 普通用戶加工數(shù)據(jù)時(shí)使用EXCEL等表格軟件的比率遠(yuǎn)高于數(shù)據(jù)庫管理軟件，表格軟件通過其內(nèi)置的豐富函數(shù)功能也能達(dá)到與數(shù)據(jù)庫查詢語言相同的目標(biāo)。本文是作者通過工作實(shí)踐總結(jié)的兩個(gè)較典型的數(shù)據(jù)處理案例：一是數(shù)據(jù)篩選計(jì)數(shù)，二是對(duì)多表進(jìn)行合并或比對(duì)。也可推廣適用于多種數(shù)據(jù)表的處理。

【關(guān)鍵詞】表格 計(jì)數(shù) 合并

使用SQL查詢語言的用戶能夠方便的生成符合其需求的數(shù)據(jù)集，但多數(shù)普通用戶并不能熟練掌握，處理數(shù)據(jù)常用工具以EXCEL或WPS表格多見。事實(shí)上，通過簡單的函數(shù)組合技巧，表格應(yīng)用也能快速的篩選并呈現(xiàn)用戶期望的數(shù)據(jù)集合。本文結(jié)合作者工作實(shí)踐，介紹兩種篩選數(shù)據(jù)的方法。

1 一對(duì)多關(guān)系的篩選計(jì)數(shù)

在個(gè)人外匯業(yè)務(wù)監(jiān)管中，監(jiān)管者需要探查境外機(jī)構(gòu)或個(gè)人將資金分拆匯入國內(nèi)多個(gè)個(gè)人賬戶的情形。從數(shù)據(jù)庫導(dǎo)出的表格文件來看，即是要查找相同付款人字段的多條記錄。從關(guān)系數(shù)據(jù)庫角度看，即通過付款人字段進(jìn)行分組，將收款人按計(jì)數(shù)進(jìn)行匯總。表格程序中的處理思路，首先按照付款人字段進(jìn)行表格排序。接著增加相同付款人標(biāo)志列，此列使用公式形如=IF（OR（LEFT（B2，N）=LEFT（B1，N），LEFT（B2，N）=LEFT（B3，N）），LEFT（B2，N），”--”）。其含義如下：若該行付款人字段值與相鄰（上或下）的值相同，則該標(biāo)志列的值即為付款人字段關(guān)鍵字，否則用特定符號(hào)定義該字段值。即若n行和n+1行付款人字段內(nèi)容相同，則該標(biāo)志列賦值為付款人名稱關(guān)鍵字字段內(nèi)容。這里使用LEFT函數(shù)，原因在于實(shí)際發(fā)生業(yè)務(wù)中，該字段提交內(nèi)容為文本，校驗(yàn)約束不嚴(yán)格，所以這里提取前N位字符作為判斷付款人名稱的實(shí)際關(guān)鍵字，N的值可以根據(jù)實(shí)際情況自行設(shè)置。

接著對(duì)工作表執(zhí)行插入“數(shù)據(jù)透視表”，將“對(duì)方付款人名稱”字段先后添加到行區(qū)域和數(shù)據(jù)區(qū)域（以WPS2013為工具描述），即生成所有付款人名稱相同的計(jì)數(shù)內(nèi)容，最后再將計(jì)數(shù)列按降序排列，就能直觀得出具有分拆行為的境外機(jī)構(gòu)或個(gè)人的實(shí)際分拆數(shù)量（結(jié)果見表1），并依此作為進(jìn)一步開展個(gè)人外匯業(yè)務(wù)現(xiàn)場監(jiān)管工作的數(shù)據(jù)基礎(chǔ)，做到有的放矢。

2 兩表連接實(shí)現(xiàn)表格合并或數(shù)據(jù)比對(duì)功能

在業(yè)務(wù)系統(tǒng)中導(dǎo)出的基本數(shù)據(jù)表格通常無法適應(yīng)用戶意愿，如導(dǎo)出表的收入和支出一般是兩張表，而在較多情況下用戶希望得到收入與支出的復(fù)合表；或是對(duì)于兩個(gè)不同數(shù)據(jù)來源的業(yè)務(wù)數(shù)據(jù)，如用戶希望經(jīng)過對(duì)比發(fā)現(xiàn)兩表中一致或不一致的記錄，獲取多來源數(shù)據(jù)對(duì)比核查的線索。從關(guān)系數(shù)據(jù)庫的角度分析，即是通過多表連接來比較表格的內(nèi)容，而在表格程序中可以通過以下方法來實(shí)現(xiàn)。

從兩種表的特性分析，收入表中有的機(jī)構(gòu)在支出表中不一定存在，反之亦然。現(xiàn)以收入表為基準(zhǔn)，如收入表中的機(jī)構(gòu)在支出表中存在，將支出金額字段加入到收入表中；如收入表中的機(jī)構(gòu)在支出表中不存在，則顯示支出為0，支出金額字段如下所示：=IF（ISNA（VLOOKUP（A3，Sheet1！$A$3：$A$1021，1，F(xiàn)ALSE）），0，INDIRECT（"Sheet1！D"&MATCH（A3，Sheet1！$A$3：$A$1021，0）+2））。最外層IF函數(shù)的含義為，如收入表當(dāng)前記錄表示機(jī)構(gòu)在支出表中不存在，則支出金額字段賦值為0；如存在則在支出表中找到相同機(jī)構(gòu)記錄，并將其交易金額字段值賦值給收入表的“支出金額”字段。VLOOKUP函數(shù)，用來在支出表的“組織機(jī)構(gòu)代碼”字段中查詢是否存在收入表當(dāng)前記錄所代表的機(jī)構(gòu)信息；INDIRECT函數(shù)是用來將支出金額字段作相應(yīng)定位之后賦值到收入表相應(yīng)字段中；MATCH函數(shù)用來返回支出表“交易金額”字段中查找到的對(duì)應(yīng)的行值。

將以上公式復(fù)制到每個(gè)“支出金額”單元格中，可通過MATCH函數(shù)得到收入表中某機(jī)構(gòu)在支出表中的位置，值為#N/A的表示該機(jī)構(gòu)在支出表中不存在。“支出金額”表示該機(jī)構(gòu)在同一時(shí)間段的支出總額，值為0的即表示支出表中不存在該機(jī)構(gòu)的情形。再用相同的方法，以支出表為基準(zhǔn)做一次收入金額的插入操作。最后將兩個(gè)表格粘貼到一個(gè)新表中，通過在新列插入形如=COUNTIF（A$3：A3，A3）的函數(shù)，將輔助列值為2的記錄全部刪除，從而去除重復(fù)記錄，所得結(jié)果見表2。

以上方法亦可用在數(shù)據(jù)核查工作中，用于比對(duì)不同數(shù)據(jù)來源的表間記錄的異同，從而挖掘出因數(shù)據(jù)源不統(tǒng)一造成的難以發(fā)現(xiàn)的違規(guī)線索。

以上兩種方法以及其中涉及的EXCEL或WPS表格函數(shù)對(duì)于數(shù)據(jù)集操作雖常見但功能強(qiáng)大，經(jīng)過適當(dāng)?shù)倪壿嫿M合可以達(dá)到與SQL查詢語言同樣的數(shù)據(jù)加工效果。將會(huì)對(duì)提高工作效率與業(yè)務(wù)創(chuàng)新起到極大幫助，值得經(jīng)常使用表格軟件的用戶深入探索與掌握。

參考文獻(xiàn)

[1]李興勇.外匯監(jiān)管中國呢業(yè)務(wù)數(shù)據(jù)的篩查技巧[J].金融時(shí)代科技，2011（11）：79.

作者單位

國家外匯管理局安徽省分局 安徽省合肥市 230091endprint

摘 要 普通用戶加工數(shù)據(jù)時(shí)使用EXCEL等表格軟件的比率遠(yuǎn)高于數(shù)據(jù)庫管理軟件，表格軟件通過其內(nèi)置的豐富函數(shù)功能也能達(dá)到與數(shù)據(jù)庫查詢語言相同的目標(biāo)。本文是作者通過工作實(shí)踐總結(jié)的兩個(gè)較典型的數(shù)據(jù)處理案例：一是數(shù)據(jù)篩選計(jì)數(shù)，二是對(duì)多表進(jìn)行合并或比對(duì)。也可推廣適用于多種數(shù)據(jù)表的處理。

【關(guān)鍵詞】表格 計(jì)數(shù) 合并

使用SQL查詢語言的用戶能夠方便的生成符合其需求的數(shù)據(jù)集，但多數(shù)普通用戶并不能熟練掌握，處理數(shù)據(jù)常用工具以EXCEL或WPS表格多見。事實(shí)上，通過簡單的函數(shù)組合技巧，表格應(yīng)用也能快速的篩選并呈現(xiàn)用戶期望的數(shù)據(jù)集合。本文結(jié)合作者工作實(shí)踐，介紹兩種篩選數(shù)據(jù)的方法。

1 一對(duì)多關(guān)系的篩選計(jì)數(shù)

在個(gè)人外匯業(yè)務(wù)監(jiān)管中，監(jiān)管者需要探查境外機(jī)構(gòu)或個(gè)人將資金分拆匯入國內(nèi)多個(gè)個(gè)人賬戶的情形。從數(shù)據(jù)庫導(dǎo)出的表格文件來看，即是要查找相同付款人字段的多條記錄。從關(guān)系數(shù)據(jù)庫角度看，即通過付款人字段進(jìn)行分組，將收款人按計(jì)數(shù)進(jìn)行匯總。表格程序中的處理思路，首先按照付款人字段進(jìn)行表格排序。接著增加相同付款人標(biāo)志列，此列使用公式形如=IF（OR（LEFT（B2，N）=LEFT（B1，N），LEFT（B2，N）=LEFT（B3，N）），LEFT（B2，N），”--”）。其含義如下：若該行付款人字段值與相鄰（上或下）的值相同，則該標(biāo)志列的值即為付款人字段關(guān)鍵字，否則用特定符號(hào)定義該字段值。即若n行和n+1行付款人字段內(nèi)容相同，則該標(biāo)志列賦值為付款人名稱關(guān)鍵字字段內(nèi)容。這里使用LEFT函數(shù)，原因在于實(shí)際發(fā)生業(yè)務(wù)中，該字段提交內(nèi)容為文本，校驗(yàn)約束不嚴(yán)格，所以這里提取前N位字符作為判斷付款人名稱的實(shí)際關(guān)鍵字，N的值可以根據(jù)實(shí)際情況自行設(shè)置。

接著對(duì)工作表執(zhí)行插入“數(shù)據(jù)透視表”，將“對(duì)方付款人名稱”字段先后添加到行區(qū)域和數(shù)據(jù)區(qū)域（以WPS2013為工具描述），即生成所有付款人名稱相同的計(jì)數(shù)內(nèi)容，最后再將計(jì)數(shù)列按降序排列，就能直觀得出具有分拆行為的境外機(jī)構(gòu)或個(gè)人的實(shí)際分拆數(shù)量（結(jié)果見表1），并依此作為進(jìn)一步開展個(gè)人外匯業(yè)務(wù)現(xiàn)場監(jiān)管工作的數(shù)據(jù)基礎(chǔ)，做到有的放矢。

2 兩表連接實(shí)現(xiàn)表格合并或數(shù)據(jù)比對(duì)功能

在業(yè)務(wù)系統(tǒng)中導(dǎo)出的基本數(shù)據(jù)表格通常無法適應(yīng)用戶意愿，如導(dǎo)出表的收入和支出一般是兩張表，而在較多情況下用戶希望得到收入與支出的復(fù)合表；或是對(duì)于兩個(gè)不同數(shù)據(jù)來源的業(yè)務(wù)數(shù)據(jù)，如用戶希望經(jīng)過對(duì)比發(fā)現(xiàn)兩表中一致或不一致的記錄，獲取多來源數(shù)據(jù)對(duì)比核查的線索。從關(guān)系數(shù)據(jù)庫的角度分析，即是通過多表連接來比較表格的內(nèi)容，而在表格程序中可以通過以下方法來實(shí)現(xiàn)。

從兩種表的特性分析，收入表中有的機(jī)構(gòu)在支出表中不一定存在，反之亦然。現(xiàn)以收入表為基準(zhǔn)，如收入表中的機(jī)構(gòu)在支出表中存在，將支出金額字段加入到收入表中；如收入表中的機(jī)構(gòu)在支出表中不存在，則顯示支出為0，支出金額字段如下所示：=IF（ISNA（VLOOKUP（A3，Sheet1！$A$3：$A$1021，1，F(xiàn)ALSE）），0，INDIRECT（"Sheet1！D"&MATCH（A3，Sheet1！$A$3：$A$1021，0）+2））。最外層IF函數(shù)的含義為，如收入表當(dāng)前記錄表示機(jī)構(gòu)在支出表中不存在，則支出金額字段賦值為0；如存在則在支出表中找到相同機(jī)構(gòu)記錄，并將其交易金額字段值賦值給收入表的“支出金額”字段。VLOOKUP函數(shù)，用來在支出表的“組織機(jī)構(gòu)代碼”字段中查詢是否存在收入表當(dāng)前記錄所代表的機(jī)構(gòu)信息；INDIRECT函數(shù)是用來將支出金額字段作相應(yīng)定位之后賦值到收入表相應(yīng)字段中；MATCH函數(shù)用來返回支出表“交易金額”字段中查找到的對(duì)應(yīng)的行值。

將以上公式復(fù)制到每個(gè)“支出金額”單元格中，可通過MATCH函數(shù)得到收入表中某機(jī)構(gòu)在支出表中的位置，值為#N/A的表示該機(jī)構(gòu)在支出表中不存在。“支出金額”表示該機(jī)構(gòu)在同一時(shí)間段的支出總額，值為0的即表示支出表中不存在該機(jī)構(gòu)的情形。再用相同的方法，以支出表為基準(zhǔn)做一次收入金額的插入操作。最后將兩個(gè)表格粘貼到一個(gè)新表中，通過在新列插入形如=COUNTIF（A$3：A3，A3）的函數(shù)，將輔助列值為2的記錄全部刪除，從而去除重復(fù)記錄，所得結(jié)果見表2。

以上方法亦可用在數(shù)據(jù)核查工作中，用于比對(duì)不同數(shù)據(jù)來源的表間記錄的異同，從而挖掘出因數(shù)據(jù)源不統(tǒng)一造成的難以發(fā)現(xiàn)的違規(guī)線索。

以上兩種方法以及其中涉及的EXCEL或WPS表格函數(shù)對(duì)于數(shù)據(jù)集操作雖常見但功能強(qiáng)大，經(jīng)過適當(dāng)?shù)倪壿嫿M合可以達(dá)到與SQL查詢語言同樣的數(shù)據(jù)加工效果。將會(huì)對(duì)提高工作效率與業(yè)務(wù)創(chuàng)新起到極大幫助，值得經(jīng)常使用表格軟件的用戶深入探索與掌握。

參考文獻(xiàn)

[1]李興勇.外匯監(jiān)管中國呢業(yè)務(wù)數(shù)據(jù)的篩查技巧[J].金融時(shí)代科技，2011（11）：79.

作者單位

國家外匯管理局安徽省分局 安徽省合肥市 230091endprint