CDA數(shù)據(jù)分析師 出品

作者：曹鑫

01、如果你是一個財(cái)務(wù)人員

我知道，一說到數(shù)字經(jīng)濟(jì)，數(shù)字化轉(zhuǎn)型，數(shù)字化人才，你第一感覺就是：跟我有半毛錢關(guān)系。誒，不要著急！

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

02、面對這樣一個問題

今天遇到一個任務(wù)是「財(cái)務(wù)對賬」。

對賬，可以說是財(cái)務(wù)最常做的一個工作，也是基礎(chǔ)工作之一。就算你們公司的系統(tǒng)已經(jīng)非常完整了，你還是會遇到兩個表要核對差異在哪里的情況。你會怎么做？

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

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

03、解析問題

如果用 Python 來做，效率就會大大提升。我們先看看Python實(shí)現(xiàn)的邏輯，還是之前的例子：我們要看數(shù)據(jù)有沒有重復(fù)，就是統(tǒng)計(jì)每個數(shù)據(jù)在兩個表分別出現(xiàn)的次數(shù)，然后兩個表中的個數(shù)相減。

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

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

04、效果演示

import pandas as pd


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


# 讀取銀行流水
df_yh = pd.read_excel('./對賬數(shù)據(jù)/銀行流水.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()


# 判斷金額出現(xiàn)的次數(shù)
df_count['重復(fù)次數(shù)']  =  df_count['憑證號'] - df_count['對方戶名']


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


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

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

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

05、實(shí)操代碼

讀取兩張 Excel 表的數(shù)據(jù)

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

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

數(shù)據(jù)清洗：修改列名

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

根據(jù)金額進(jìn)行統(tǒng)計(jì)

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

# 判斷金額出現(xiàn)的次數(shù) df_count['重復(fù)次數(shù)']  =  df_count['憑證號'] - df_count['對方戶名']
df_count

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

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

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

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

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