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

作者：曹鑫

01、如果你是一個(gè)財(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ù)全流程的每個(gè)環(huán)節(jié)，這也是為什么說，數(shù)字經(jīng)濟(jì)時(shí)代的新生產(chǎn)資料是數(shù)據(jù)！看看這張數(shù)據(jù)能力圖，分成四個(gè)層面：需求層、數(shù)據(jù)層、分析層、輸出層；第一層是需求層，是目標(biāo)確定的過程，對(duì)整個(gè)業(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、面對(duì)這樣一個(gè)問題

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

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

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

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

03、解析問題

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

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

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

04、效果演示

import pandas as pd


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


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


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


df_yh_shou = df_yh[['收款金額','對(duì)方戶名']] 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['憑證號(hào)'] - df_count['對(duì)方戶名']


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


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

# 列出兩張表中具體的行 # 公司銀行存款明細(xì)賬中的多記/錯(cuò)記 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('./對(duì)賬數(shù)據(jù)/公司銀行存款明細(xì)賬.xlsx',header=1)
df_gs.head()

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

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

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

df_yh_shou = df_yh[['收款金額','對(duì)方戶名']] 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['憑證號(hào)'] - df_count['對(duì)方戶名']
df_count

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

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

# 多記/錯(cuò)記 df_gs[df_gs['借方'] == 1.00]

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

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