大学生会计技能竞赛（四）

小AO作为2022年其赛事的一等奖的获奖者，跟各位小伙伴们分享一下区域赛的相关题型及相关知识点（大数据分析方面）：
1、题型主要是以填空题为主。对区域赛来说还是比较简单的，小AO区域赛能拿满分，这是幸运的。
2、大数据方面。主要考三个模块：pandas、numpy、matplotlib。了解这三个模块的相关方法以及一些内置函数。如：merge、contains、groupby、replace、apply、map、pivot_table、还有读取数据read_excel都是高频词。同时，对于可视化要了解各个图形的相关方法
一共有四个样卷，本文为样卷四，其他样卷可在小AO专栏查看；需要数据的小伙伴们，可以找小AO或者评论区@小AO

制作内部经营数据分析
运用python语言，对公司的内部经营数据分析，对现有产品根据类型和材质来统计产品的营业额、利润及利润率。

一、内部经营数据制作步骤：
第一步：读取【天津同杉商贸有限责任公司业财资料.xlsx】工作簿，根据给定的【期初余额】中的统计【总账科目-库存商品】的【期初数量】、【借方余额】创建为【qcye_df】表；

第二步：读取【天津同杉商贸有限责任公司业财资料.xlsx】工作簿，根据给定的【记账凭证】中的统计【总账科目-库存商品】的【入库数量】、【借方金额】创建为【buy_data】表；

第三步：将【qcye_df】与【buy_data】拼接为【kc_data】表，可以计算得出【期初与本期入库数量】、【期初与本期进价】；

第四步：读取【天津同杉商贸有限责任公司业财资料.xlsx】工作簿，根据给定的【记账凭证】中的统计【总账科目-主营业务收入&预计负债】的【出库数量】【贷方金额】创建为【sale_data】表；

第五步：把【kc_data】表中【期初与本期入库数量】【期初与本期进价】列与的【sale_data】表中【销售数量】【销售额】拼接为【data】表，计算出【月末一次加权平均单价】【利润】【利润率(%)】列；

第六步：对产品明细进一步划分按类型材质区分各产品的营业额和利润，运用折柱混合进行可视化分析。

二、内部经营数据制作内容：
1.导入包。

import pandas as pd

2.读取【天津同杉商贸有限责任公司业财资料.xlsx】中的【期初余额】表，内容只保留【总账科目】中时【库存商品】的行次。

file = 'excel/2022BKQY财务数据分析案例/天津同杉商贸有限责任公司业财资料.xlsx'#文件路径
qcye_df = pd.read_excel(file,sheet_name='期初余额',header=1)#读取
qcye_df = qcye_df[qcye_df['总账科目'].str.contains('库存商品')]#筛选出总账科目包含库存商品的数据
qcye_df

3.紧接着根据【明细账科目】分组，【期初数量】、【借方余额】求和。

qcye_df = qcye_df.groupby(['明细账科目'])[['期初数量','借方余额']].sum()
qcye_df

4.读取【天津同杉商贸有限责任公司业财资料.xlsx】中的【记账凭证】工作表。

jzpz_df = pd.read_excel(file,sheet_name='记账凭证')#读取
jzpz_df

5.分析采购数量，那么我们锁定需要采集的对象是【总账科目】中包含【库存商品】的行次。

buy_data =jzpz_df[jzpz_df['总账科目'].str.contains('库存商品')]
buy_data = buy_data[['明细科目','入库数量','借方金额']]
buy_data

6.buy_data表根据【明细科目】分组，求和。

buy_data = buy_data.groupby(['明细科目']).sum()#分组求和
buy_data 

7.先将记录期初库存数据的【qcye_df】和本期入库数据的【buy_data 】拼接，再新增【期初与本期入库数量】、【期初与本期进价】列。

kc_data = pd.merge(qcye_df,buy_data,right_index=True,left_index=True,how='outer')#两表拼接的并集
kc_data['期初与本期入库数量'] =kc_data['期初数量']+kc_data['入库数量']
kc_data['期初与本期进价'] =kc_data['借方余额']+kc_data['借方金额']
kc_data

8.分析销量、销售额，那么我们锁定需要采集的对象是【总账科目】中包含【主营业务收入】或者【预计负债】的行次。

sale_data =jzpz_df[(jzpz_df['总账科目'].str.contains('主营业务收入'))|(jzpz_df['总账科目'].str.contains('预计负债'))]
sale_data =sale_data[['明细科目','出库数量','贷方金额']]#筛选出需要的内容
sale_data[['明细科目','产品明细']] = sale_data['明细科目'].str.split('-',1,expand=True)#将明细科目内容根据-分割成2个列表
sale_data = sale_data.groupby(['产品明细']).sum()#分组求和
sale_data

9.列名重命名，【出库数量】改为【销售数量】，【贷方金额】改为【销售额】。

sale_data = sale_data.rename(columns={'出库数量':'销售数量','贷方金额':'销售额'})
sale_data

10.将记载销售数量的sale_data表【销售数量】【销售额】和kc_data表的【期初与本期入库数量】【期初与本期进价】列拼接之后，新增【月末一次加权平均单价】【利润】【利润率(%)】列。

data = pd.merge(sale_data[['销售数量','销售额']],kc_data[['期初与本期入库数量','期初与本期进价']],right_index=True,left_index=True,how='outer')#两表拼接的并集
data = data.fillna(0)#缺失值用0填充
data['月末一次加权平均单价'] = (data['期初与本期进价']/data['期初与本期入库数量']).round(2)
data['利润'] = data['销售额']-data['销售数量']*data['月末一次加权平均单价']
data['利润率(%)'] = (data['利润']/data['销售额']*100).map(lambda x:round(x,2))#计算结果保留两位小数
data.index.name = '产品明细'#索引名重命名
data = data.reset_index()#索引重置
data[['按类型材质区分','按片数区分']] = data['产品明细'].str.rsplit('-',1,expand=True)#从右侧开始将产品明细拆分为2个列表
data

11.构建数据透视表，索引为按类型材质区分，值为销售额、利润，聚合函数为求和，最后重新计算利润率(%)。

data1 = data.pivot_table(index='按类型材质区分',values=['销售额','利润'],aggfunc='sum')
data1['利润率(%)'] = (data1['利润']/data1['销售额']*100).map(lambda x:round(x,2))
data1

12.使用折柱混合图展示按类型材质区分产品的营业额和利润。

#从matplotlib库导入pyplot模块并将其命名为plt
import matplotlib.pyplot as plt
import numpy as np
plt.rcParams['font.family']='kaiTi' # 用楷体显示中文
plt.rcParams['axes.unicode_minus']=False # 正常显示负号 
plt.rcParams["font.size"] = 12 #设置字体大小
#绘制主坐标轴上的图表
plt.figure(figsize=(10,6),dpi=90)
x = np.array([i for i in range(len(data1.index))])
width = 0.2
plt.bar(x, data1['销售额'], width=width, label='销售额',color='#3399cc')
plt.bar(x+width, data1['利润'], width=width, label='利润',color='#99ff66')
#加上X轴和Y轴的坐标标签
plt.xlabel('产品')
plt.ylabel('金额（元）')
#加上图例，图列的位置设置在左上
plt.legend(loc='upper left')
#调用twinx方法，绘制次坐标轴的折线图
plt.twinx()
plt.plot(data1['利润率(%)'],color='m',label='利润率(%)',lw=2.5)
plt.title('按类型材质统计产品营业额利润')
#加上Y轴的坐标标签
plt.ylabel('利润率(%)')
#加上图例，图例位置设置在右上
plt.legend(loc='upper right')
#图表内增加网格线
plt.grid()
#展示
plt.show()