劉可

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

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

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

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

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

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

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

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

從兩種表的特性分析，收入表中有的機構(gòu)在支出表中不一定存在，反之亦然。現(xiàn)以收入表為基準，如收入表中的機構(gòu)在支出表中存在，將支出金額字段加入到收入表中；如收入表中的機構(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ù)的含義為，如收入表當前記錄表示機構(gòu)在支出表中不存在，則支出金額字段賦值為0；如存在則在支出表中找到相同機構(gòu)記錄，并將其交易金額字段值賦值給收入表的“支出金額”字段。VLOOKUP函數(shù)，用來在支出表的“組織機構(gòu)代碼”字段中查詢是否存在收入表當前記錄所代表的機構(gòu)信息；INDIRECT函數(shù)是用來將支出金額字段作相應(yīng)定位之后賦值到收入表相應(yīng)字段中；MATCH函數(shù)用來返回支出表“交易金額”字段中查找到的對應(yīng)的行值。

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

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

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

參考文獻

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

作者單位

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

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

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

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

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

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

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

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

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

從兩種表的特性分析，收入表中有的機構(gòu)在支出表中不一定存在，反之亦然?，F(xiàn)以收入表為基準，如收入表中的機構(gòu)在支出表中存在，將支出金額字段加入到收入表中；如收入表中的機構(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ù)的含義為，如收入表當前記錄表示機構(gòu)在支出表中不存在，則支出金額字段賦值為0；如存在則在支出表中找到相同機構(gòu)記錄，并將其交易金額字段值賦值給收入表的“支出金額”字段。VLOOKUP函數(shù)，用來在支出表的“組織機構(gòu)代碼”字段中查詢是否存在收入表當前記錄所代表的機構(gòu)信息；INDIRECT函數(shù)是用來將支出金額字段作相應(yīng)定位之后賦值到收入表相應(yīng)字段中；MATCH函數(shù)用來返回支出表“交易金額”字段中查找到的對應(yīng)的行值。

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

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

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

參考文獻

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

作者單位

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

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

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

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

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

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

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

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

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

從兩種表的特性分析，收入表中有的機構(gòu)在支出表中不一定存在，反之亦然?，F(xiàn)以收入表為基準，如收入表中的機構(gòu)在支出表中存在，將支出金額字段加入到收入表中；如收入表中的機構(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ù)的含義為，如收入表當前記錄表示機構(gòu)在支出表中不存在，則支出金額字段賦值為0；如存在則在支出表中找到相同機構(gòu)記錄，并將其交易金額字段值賦值給收入表的“支出金額”字段。VLOOKUP函數(shù)，用來在支出表的“組織機構(gòu)代碼”字段中查詢是否存在收入表當前記錄所代表的機構(gòu)信息；INDIRECT函數(shù)是用來將支出金額字段作相應(yīng)定位之后賦值到收入表相應(yīng)字段中；MATCH函數(shù)用來返回支出表“交易金額”字段中查找到的對應(yīng)的行值。

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

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

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

參考文獻

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

作者單位

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