CDA數據分析師 出品

作者：曹鑫

01、如果你是一個財務人員

我知道，一說到數字經濟，數字化轉型，數字化人才，你第一感覺就是：跟我有半毛錢關系。誒，不要著急！

至少 Excel 你天天在用吧？只不過你可能用的最多的就是復制粘貼記錄一下數據。你不要懷疑，這高低、左右，都算是數字化技能！因為數字化技能的核心就是數據能力，而且數據能力貫穿著公司業務全流程的每個環節，這也是為什么說，數字經濟時代的新生產資料是數據！看看這張數據能力圖，分成四個層面：需求層、數據層、分析層、輸出層；第一層是需求層，是目標確定的過程，對整個業務進行拆解，為數據工作指明方向；第二層是數據層，包含數據獲取、數據清洗、數據整；第三層是分析層，包含描述性統計制圖、業務根因分析，這里就涉及到專業的算法；第四層是輸出層，面向管理層、決策層、執行層，給出不同的數據報告、業務儀表盤、落地模型等。

02、面對這樣一個問題

今天遇到一個任務是「財務對賬」。

對賬，可以說是財務最常做的一個工作，也是基礎工作之一。就算你們公司的系統已經非常完整了，你還是會遇到兩個表要核對差異在哪里的情況。你會怎么做？

當數據量不大的時候，我們最簡單的做法，也是最符合第一直覺的做法，把兩張表放到一起，一左一右，左邊有個268，右邊有個268，這就對上了；左邊有個20.1，右邊沒找到20.1，這就是多記了，但是右邊有個21，所以也有可能是錯記了；左邊有個100，右邊也有個100，左邊還有個100，右邊沒有100了，那這里可能是多記了。剩下右邊還有個8，那這就是左邊漏記了，這樣就把不同情況都分析出來了。

但如果數據量大了，幾百上千行，甚至幾萬行，這個方法就有點累了，比如我們現在有的兩張數據表，一份公司銀行存款明細賬和一份銀行流水，我們需要將公司銀行存款明細中的借方與銀行流水的收款金額進行核對。別說幾百上千行了，光看這個100多行，我就覺得看著累。

03、解析問題

如果用 Python 來做，效率就會大大提升。我們先看看Python實現的邏輯，還是之前的例子：我們要看數據有沒有重復，就是統計每個數據在兩個表分別出現的次數，然后兩個表中的個數相減。

• 268，在兩張表中分別出現了1次，重復次數就是0，說明268這個數字不重復；
• 20.1，只在公司銀行存款明細賬中出現了一次，重復次數就是1次，說明20.1在公司賬多記錄了一次，也可能是記錯了；
• 100，在公司銀行存款明細中出現了2次，銀行流水出現了1次，重復次數就是1次，說明100在公司賬多記錄了一次，也可能是記錯了；
• 21，只在銀行流水出現了1次，重復次數就是-1次，說明21在公司賬里面漏記了；
• 8，跟21是一樣的情況，也是在公司賬里面漏記錄了，因為銀行流水就是銀行直接導出的，有錢出入才會有記錄，所以以銀行流水為準。

知道了邏輯，我們就可以來操作了。先看看效果，就是這30多行代碼，作為新人，你別怕，我們先看看有多爽！

04、效果演示

import pandas as pd


# 讀取公司明細賬
df_gs = pd.read_excel('./對賬數據/公司銀行存款明細賬.xlsx',header=1)


# 讀取銀行流水
df_yh = pd.read_excel('./對賬數據/銀行流水.xlsx',header=1)


df_gs_jie = df_gs[['憑證號','借方']] df_gs_jie = df_gs_jie.rename(columns={'借方':'金額'})


df_yh_shou = df_yh[['收款金額','對方戶名']] df_yh_shou = df_yh_shou.rename(columns={'收款金額':'金額'})


# 將兩張表的借方-收款拼接
mergedStuff_jie_shou= df_gs_jie.append(df_yh_shou)
mergedStuff_jie_shou = mergedStuff_jie_shou[mergedStuff_jie_shou['金額'] != 0]


df_count = mergedStuff_jie_shou.groupby(by='金額').count()


# 判斷金額出現的次數
df_count['重復次數']  =  df_count['憑證號'] - df_count['對方戶名']


# 重復次數不為0，就是沒有對上
df_result =  df_count[df_count['重復次數'] != 0].copy()


# 判斷錯誤問題
df_result['錯誤原因'] = df_result.apply(lambda x: '漏記' if x['重復次數']< 0 else ('重復記錄/多記' if x['重復次數'] > 1 else '多記/錯記'), axis=1) print('借方-收款出現的錯誤')
df_result[['錯誤原因']]

# 列出兩張表中具體的行 # 公司銀行存款明細賬中的多記/錯記 df_gs[df_gs['借方'] == 1.00]

# 銀行流水中的漏記 df_yh[(df_yh['收款金額'] == 637146.52) |
      (df_yh['收款金額'] == 27023289.88) ]

05、實操代碼

讀取兩張 Excel 表的數據

import pandas as pd # 讀取公司明細賬 df_gs = pd.read_excel('./對賬數據/公司銀行存款明細賬.xlsx',header=1)
df_gs.head()

# 讀取銀行流水 df_yh = pd.read_excel('./對賬數據/銀行流水.xlsx',header=1)
df_yh.head()

數據清洗：修改列名

df_gs_jie = df_gs[['憑證號','借方']] df_gs_jie = df_gs_jie.rename(columns={'借方':'金額'})
df_gs_jie.head()

df_yh_shou = df_yh[['收款金額','對方戶名']] df_yh_shou = df_yh_shou.rename(columns={'收款金額':'金額'})
df_yh_shou.head()

拼接兩張表

# 將兩張表的借方-收款拼接 mergedStuff_jie_shou= df_gs_jie.append(df_yh_shou)
mergedStuff_jie_shou = mergedStuff_jie_shou[mergedStuff_jie_shou['金額'] != 0] # 剔除金額為 0 的行 mergedStuff_jie_shou

根據金額進行統計

df_count = mergedStuff_jie_shou.groupby(by='金額').count()
df_count

# 判斷金額出現的次數 df_count['重復次數']  =  df_count['憑證號'] - df_count['對方戶名']
df_count

# 重復次數不為0，就是沒有對上 df_result =  df_count[df_count['重復次數'] != 0].copy()
df_result

# 判斷錯誤問題
df_result['錯誤原因'] = df_result.apply(lambda x: '漏記' if x['重復次數']< 0 else ('重復記錄/多記' if x['重復次數'] > 1 else '多記/錯記'), axis=1) print('借方-收款出現的錯誤')
df_result[['錯誤原因']]

# 多記/錯記 df_gs[df_gs['借方'] == 1.00]

# 漏記 df_yh[(df_yh['收款金額'] == 637146.52) |
      (df_yh['收款金額'] == 27023289.88) ]

未來，你只要修改好需要讀取的表，確定需要比對的列，然后一鍵運行，結果一瞬間就出來了，而且你之后每個月，每周，甚至每天要比對的時候，你只需要確定好你要比對的表，比對的數據列，就可以快速得到結果，代碼復用效率極高。你還可以進一步查看各自表中具體行的數據，方便你具體判斷。