运用Excel优化设计料账管理工作

陈红涛(胜利油田物资供应处，山东 东营 257000)

运用Excel优化设计料账管理工作

陈红涛(胜利油田物资供应处，山东 东营 257000)

本文根据Excel在料账管理中的应用现状，提出了优化设计的思路，详细的设计方案。有利于提高料帐管理工作的准确率和效率。通过实例详细介绍设计系统工作簿的方法，通过[选择性粘贴]实现各工作表间的数据同步，运用各种函数和公式取代计算过程，提高运算效率，最终达到只操作基础数据的目标。应用这些方法，不需要专门学习复杂的数据库知识，根据实际工作需要即可在Excel界面设计和开发简单的数据库系统，根据需要随时扩展系统功能，创造性的高效完成数据处理工作。

数据处理；料帐；函数；公式；链接

近年来，Excel软件以其强大的数据处理功能和简便易用的特点在办公领域得到普遍应用，逐渐成为仓储人员日常工作的得力助手[1]。运用Excel可以在一定程度上减轻仓储人员的工作量，提高仓储管理的效率。现在我们使用Excel的水平一般比较低，不能充分发挥Excel的潜力，因此有必要对Excel在仓储管理上的应用作出一个总结，使Excel强大的数据处理功能得到充分发挥。

1 应用现状

(1)由于套管规格多，发放量非常大，只能累计一周结一次帐,导致许多套管收结不及时，因此月结比较复杂。

(2)月结沿用手工方法，单据多而且易出差错，有时因为一个数字敲错，需要反复查几遍。

(3)套管每月收发量在3万吨左右，产生巨额劳务费，在卸车、倒运和装车三种情况下，首先要单井核对，然后累积计算工作量，最后依据劳务费率计算实际费用。

2 优化设计月结系统

2.1 新建“月结管理系统”工作簿

2.2 在系统工作簿内建立“上年结转”表格。

(1)根据需要建列，选取70行作为数据区域，以应变1年内的物资增减。

(2)手工键入物码、规格、单价和库存数量。

(3)“序号”列A3位置输入1，其余向下用拖动柄填充，实现了快速输入数字序列。

(4)“库存金额”列F3位置插入公式“=D3*E3”（单价*数量）自动计算库存金额，其余用拖动柄快速填充。

(5)设计总计行。E1位置插入公式“=SUM（E3：E72）”计算总库存数量，将E1公式拖入F1自动计算总库存金额。

(6)“库存项数”运用统计函数COUNTIF计算区域内符合给定条件的单元格的数量，在图中G1位置输入“=COUNTIF(F3: F72,"＜＞0")”自动计算，显示当月库存项数。

COUNTIF是一个统计函数，用于统计满足某个条件的单元格的数量；例如，统计特定城市在客户列表中出现的次数。COUNTIF(range,criteria)，其中range指明要统计数量的单元格的组，可以包含数字、数组或包含数字的引用，将忽略空白和文本值。criteria用于决定要统计哪些单元格的数量的数字、表达式、单元格引用或文本字符串。COUNTIF仅使用一个条件。如果要使用多个条件，请使用COUNTIFS。

2.3 复制生成“1月”月结表格

(1)选择“上年结转”表格中“序号、物码、规格、单价”数据区块，[复制]→[选择性粘贴]→[粘贴链接]至“1月”中的对应单元格。

(2)“库存数量”K3位置输入公式“=上年结转!E3+E3-H3”（期初+收入-发出）自动生成库存数量，核对此列与ERP是否一致。如果“库存数量”不一致，需要检查收发数量是否准确；如果“库存数量”一致，“库存金额”不一致时，可以判定有调价，需要手工输入新单价。

(3)“库存金额”N3位置输入公式“=L3+J3-G3-上年结转! G3”自动计算价差。收发金额列里输入相应公式“=E3+O3”“= P3+J3”自动生成调价后金额。

(4)设月计行，E1位置插入公式“=SUM（E3：E72）”自动生成当月“收入数量”，将E1公式向右拖至L1自动完成月计。

2.4 复制“1月”生成“2月”。

调整“库存数量”和“调价”公式，链接到“1月”对应数据。增设累计收发列，Q3位置输入公式“=E3+'1月'!E3”自动生成累计收发数量。其余公式使用填充柄拖动，迅速填充。

2.5 复制“2月”依次生成“3～12月”，图4所示。表中链接对应调整，依次链接。

2.6 软件调试

在“1月”表格输入数据，“2～12月”同步更新。大大降低了劳动强度，避免手工计算的差错，提高月结的工作效率，准确率100%。

3 优化设计劳务费结算

(1)新建“劳务费结算系统”工作簿。

(2)建“单重”和“费率”表格。手工输入规格、吨/米列。

(3)建“1月六队”表格。

①选择“单重”的“规格型号”列区域[复制]→[选择性粘贴]→[粘贴链接]将“1月六队”表格的规格链接到“单重”表格，便于维护。

②在“吨位”列E2位置输入公式“=C2*单重!C2”（数量*单重）自动生成吨位，其余拖动填充柄向下填充。

③在“分类总计”G2位置输入公式“=(ROUND(SUM(E2: E12),0))”将合计吨位四舍五入到整数。

ROUND用来返回某个数字按指定位数取整后的数字。ROUND(number,num_digits)，其中Number指明需要进行四舍五入的数字。Num_digits指明指定的位数，按此位数进行四舍五入。

ROUND函数和SUM函数联合使用时节省统计求和的步骤直接得到需要的结果。便于检查。

④应用费率链接到“费率”表，便于维护。

⑤“金额”列K12位置输入公式“=G12*I12”（总价=吨位*费率）自动生成总价。

⑥复制“1月六队”生成“1月五队”表格。

⑦依次建立2～12月计费表格。至此“劳务费结算系统”完成。

4 结语

改进后效果显著，原来结账需要4小时，现在对账以后仅用1小时就可以完成结账，提速75%。

应用上述方法，不需要专门学习复杂的数据库知识，根据实际工作需要即可在Excel界面设计和开发简单的数据库系统，根据需要随时扩展系统功能，创造性的高效完成数据处理工作[1]。

[1]《利用Excel软件实现简单数据库系统的设计与开发》，薛转花，《中国科技信息》，2010年21期.