论文部分内容阅读
摘要 本文通过Excel函数在总承包采购管理工作中库存管理、库存分析、库存物资的处理、销售清单的确定四个案例中的运用介绍了Excel函数在总承包采购管理工作中的的运用。
关键词 Excel函数 工程总承包管理 采购管理 库存管理
中图分类号：F253. 2 文献标识码：A
Excel函数的功能是非常强大的，在工作中灵活地运用它，能够极大地减少工作量，为工作带来事半功倍的效果。下面就通过Excel函数在总承包采购管理工作中库存管理、库存分析、库存物资的处理、销售清单的确定四个案例中的运用介绍Excel函数的运用。
一、Excel函数在库存管理中实现“日清月结”
（一）事情的起因。
在工程总承包初期，由于没有系统的工程管理软件，很多作业都是手工作业。而手工作业是每月月底结帐，所以准确的库存都只是在月底知道，很难把握实时的库存信息，这显然不能满足工程的需要。
（二）解决方案的构思与实施。
构思：库存工作表中每种材料应具有唯一的明细，这样方便查询库存。库存有进有出，故进有一块、出有一块。通过设置总入库量、总出库量之后可以得出库存。
实施：进按合同进，这样在行底可以合计，以检查数据的正确性。出按施工单位/子项出，以进行成本核算。总入库量列对所有合同合计，出库量列对所有施工单位/子项合计，庫存列可以得出库存。工作中，仓储工作人员每进行完一笔业务，在帐目中登记之后，实时库存立刻显现出来。对于多人操作问题，可将文件设置为共享模式，可供多人同时操作存取数据。
以上“日清”工作完成以后，剩下的就是“月结”工作。在手工作业中，每月月底需对各类型材料的月增加数量、月增加金额、月减少数量、月减少金额、结存数量、结存金额数据进行月度总结。而在总承包工程中，各类型材料的明细众多，数据动态巨大，手工统计的工作量是非常繁重的，需要将同种类型材料明细当月进进出出的数量及金额累加。如何在Excel中解决这一问题，也成为减轻仓储工作人员工作量的一项重要事情。
实现办法：根据各类型材料月结这一概念，首先将各类型材料进行“广义”的编码。在这里对“广义”打上引号，是因为在总承包工程中，材料的种类繁多，逐一编码的工作量很大，一时无法完成。在这里只是将同种类型材料设作同一编码，以便统计使用。第二步将每月月底与手工帐目核对无误的库存工作表进行备份，并将备份工作表改名为当前年、月、日。这样既醒目，又可避免因每天操作库存工作表不可避免造成数据损失之后无纠正来源。第三步，设置月结工作表，在这里主要是用两个函数SUMIF、SUMPRODUCT。
月增加数量=SUMIF(库存工作表的类型列,当前类型,库存工作表的总入库量)-SUMIF(上一次备份库存表的类型列, 当前类型, 上一次备份库存表的类型列的总入库量)。意义：当前类型材料截止到目前总的入库量-截止到上月结帐时总的入库量，即为当前类型材料当月入库量。
月增加金额=SUMPRODUCT((库存工作表类型列=当前类型)*(库存工作表总入库量列*库存工作表单价列))- SUMPRODUCT((上一次备份库存表类型列=当前类型)*( 上一次备份库存表总入库量列*上一次备份库存表单价列))。意义：当前类型材料截止到目前总的入库金额-当前类型材料截止到上月结帐时总的入库金额，即当前类型材料当月入库金额。
月减少数量：参考月增加数量。
月减少金额：参考月增加金额。
结存数量：参考月增加数量。
结存金额：参考月增加金额。
（三）解决方案的效果。
通过以上解决方案以后，当每天的出入情况在库存工作表中输入后，库存情况立即显现出来，月结表中也会有所显现，月底核对完库存工作表之后，月结情况也随之出来了。从而极大地减轻了仓储工作人员的工作量。
二、Excel函数在库存分析中的运用
（一）事情的起因。
工程总承包在进行到中后期以后，仓库已库存较多材料，而在工程中却紧缺另外许多种材料。基于对工程结束之后库存材料偏多的担忧，需对库存材料进行一次分析，以查出哪些材料是多余的，哪些材料还需要再采购，以便于后期采购工作的进行以及多余材料的处理。
（二）解决方案的思路。
各材料的总计划量（已有施工单位各子项计划表）即为总需求量，总入库量（已有材料库存表，内含各材料总入库量）+未入库量（已到货未办理入库手续）+在途物资（即已签合同未到货）可视为总采购量，通过这两个数据的量差（采购量差）可以判定材料是否有多买。通过采购量差与库存的比较，可以知道库存的材料是否是有用的，或是其中有多少是无用的。
（三）方案实施前的准备。
此方案主要通过Sumproduct函数进行多条件取值，即判定三个条件:材料名称，材质，规格，从各计划分表中汇总各材料计划量与各材料总入库量作比较，以查出该材料是否有多采购的情况，所以要对各单位各子项计划分表的各种材料名称、材质、规格的表述方式与库存工作表的表述方式进行统一。这个工作量是很大的，在没有统一的材料编码及综合的系统软件的前提下，这是不得已的选择。
在准备过程中为简化方案的难度，还清理了所有的未入库量，并视所有已签合同已执行完，此时总采购量=总入库量。
（四）解决方案的实施。
首先设置库存分析工作表框架：从库存工作表中拷贝出库存明细（含材料名称、材质、规格等信息），在其后设置总入库量列、库存列、采购量差列、计划合计列及所有施工单位各子项列。
第2步用SUMPRODUCT函数在库存分析工作表中对库存工作表数据动态取值，分别取出各材料明细总入库量数据及库存数据。公式：=SUMPRODUCT((库存工作表!材料名称区域=当前材料名称)*(库存工作表！材质区域=当前材质)*(库存工作表! 规格区域=当前规格) *(库存工作表!总入库量区域))，意义：对库存工作表中材料名称、材质、规格分别等于当前材料名称、材质、规格的总入库量数据取值。库存列可参考本公式。
第3步在库存分析工作表中用SUMPRODUCT函数对各单位各子项计划表各材料总计划数据取值。第一个公式可参考第2步公式。后面列改动引用工作表名称即可，其它行套用得所有施工单位所有子项所有材料总计划。
在计划合计列汇总各材料总计划量，在采购量差列设置计划合计与总入库量的量差，于是一个动态的库存分析工作表便产生了。
在此基础之上，通过比较库存与采购量差的关系，可进一步提炼库存分析数据，以得出各类库存无计划物资的分布。
公式=SUMPRODUCT((材料类型区域=某种类型)*(采购量差>0)*(采购量差<库存)*( 采购量差))+SUMPRODUCT((材料类型区域=某种类型)*( 采购量差>0)*( 采购量差>=库存)*(库存))意为：某种类型材料有多采购的时候，库存小于多采购量时取库存，库存大于等于多采购量时取多采购量，其和便某种类型材料总体多采购量。
（五）运用的效果。
通过设置动态库存分析工作表，节省了统计全厂各种材料总计划量的时间。最重要的是，通过它，能够对库存材料的状况有了一个详细的了解，对后期材料的采购以及多余材料的处理有了一个参考性的依据和指导性的方向。
三、Excel函数在库存物资处理中的运用
（一）事情的起因。
工程进行到后期以后，仓库有一些库存，需要查明这些库存中哪些是有计划的，又是哪些单位哪些子项未用的，以利于库存物资的处理。
（二）方案设计。
首先在各单位各子项计划表中设置已领用列、量差列、库存列，。通过对库存工作表中库存列和当前单位及子项出库数据的取值可以取出库存数据和已领用数据，量差设置可得。通过设置，可以看出各单位各子项未领用材料明细库存情况。
第2步设置各单位各子项未领用材料库存总表,以便于总体掌握库存物资的未领用情况。列出所有单位及其工程子项,并将钢材类材料细分为线材，板材，型材，管材四类，以便于分别掌握它们总的未领用库存情况。
第3步汇总各单位各子项计划表中数据。由于在总表中钢材类材料有细分，在这里也必须细分。通过在各计划表中增加材料类别列，分别指定1，2，3，4对应上述材料类别予以解决统计问题。
第4步：在总表中对各计划分表计划数据及已领用数据取值。取值用Sumif函数即可，由于其引用的工作表名称可由总表中施工单位+子项编号组成（与各计划分表名称表述方式一致），所以考虑嵌套使用INDIRECT函数来动态引用各个计划分表以省去改动参数。
（三）运用的效果。
通过此方案设计，对于详细的材料未领用库存情况及总体的未领用库存情况均有了一个充分的了解，对于库存物资的处理也有了一个参考的依据。
四、Excel函数在设备销售清单的确定中的运用
（一）事情的起因
工程收尾阶段，所采购的设备在经过多次销售，并经转手以后已不知具体哪些设备未销售了，只知已销售金额与总合同金额是对不上的，需要清理出未销售的设备清单。
（二）方案分析。
这一问题初看起来时犹如是大海捞针的，因为总的设备合同有500多个，总的设备明细有6000多项，而且有些合同只销售了部分，这就更加增加了问题的难度。
已有的素材：1）各次已销售设备清单（含合同号、设备名称、合同金额等等）。
2）合同资金支付台帐（含合同号、合同金额等等）。
3）每一合同具有Word文本，内含合同金额及明细。
（三）解决方案的思路。
用合同金额减去销售清单中各合同已销售金额，应该可以看出各合同已销售或未销售的情况。
（四）实施步骤。
第一步汇总各次已销售设备清单，并拷贝合并的清单中合同号列至新增工作表sheet1，通过数据/删除重复项选项（Excel2007）删除重复项，保留唯一合同号。
第二步在sheet1中用Sumif函数通过对已销售设备清单汇总得出已销售清单中各合同销售额。
第三步拷贝合同资金支付台帐中合同编号、合同金额列至sheet1,将两份清单分别排序，可大体对应起来。在合同资金区域后用Sumif函数取出对应合同的已销售金额，可得出差额，此差额即为各合同已销售或未销售的情况。在其中会涉及将两份清单中合同编号表述方式统一的问题，统一之后即可。
（五）运用的效果。
通过实施之后，有问题的合同已详细定位出来。通过它可以对那些部分未销售或完全未销售的合同拟定设备销售清单，为设备的最终完全销售铺平了道路。
五、结束语
在工作中靈活运用Excel函数，能够极大地提升工作的效率。经过Excel函数处理数据统计中的一些问题，能够为工程管理带来很大的帮助。
（作者单位：中铝国际遵义工程总承包项目部）
参考文献：
[1]胡顺起，丰晓君，周茂山等. Excel函数在农业气象测墒工作中的巧用. 安徽农业科学，2010，38(2)：837—839.
[2]吴丰科,苏厚勤,朱汉群. Excel对象模型在统计报表中的应用研究. 计算机应用与软件，2009，26(2)：201-204.
关键词 Excel函数 工程总承包管理 采购管理 库存管理
中图分类号：F253. 2 文献标识码：A
Excel函数的功能是非常强大的，在工作中灵活地运用它，能够极大地减少工作量，为工作带来事半功倍的效果。下面就通过Excel函数在总承包采购管理工作中库存管理、库存分析、库存物资的处理、销售清单的确定四个案例中的运用介绍Excel函数的运用。
一、Excel函数在库存管理中实现“日清月结”
（一）事情的起因。
在工程总承包初期，由于没有系统的工程管理软件，很多作业都是手工作业。而手工作业是每月月底结帐，所以准确的库存都只是在月底知道，很难把握实时的库存信息，这显然不能满足工程的需要。
（二）解决方案的构思与实施。
构思：库存工作表中每种材料应具有唯一的明细，这样方便查询库存。库存有进有出，故进有一块、出有一块。通过设置总入库量、总出库量之后可以得出库存。
实施：进按合同进，这样在行底可以合计，以检查数据的正确性。出按施工单位/子项出，以进行成本核算。总入库量列对所有合同合计，出库量列对所有施工单位/子项合计，庫存列可以得出库存。工作中，仓储工作人员每进行完一笔业务，在帐目中登记之后，实时库存立刻显现出来。对于多人操作问题，可将文件设置为共享模式，可供多人同时操作存取数据。
以上“日清”工作完成以后，剩下的就是“月结”工作。在手工作业中，每月月底需对各类型材料的月增加数量、月增加金额、月减少数量、月减少金额、结存数量、结存金额数据进行月度总结。而在总承包工程中，各类型材料的明细众多，数据动态巨大，手工统计的工作量是非常繁重的，需要将同种类型材料明细当月进进出出的数量及金额累加。如何在Excel中解决这一问题，也成为减轻仓储工作人员工作量的一项重要事情。
实现办法：根据各类型材料月结这一概念，首先将各类型材料进行“广义”的编码。在这里对“广义”打上引号，是因为在总承包工程中，材料的种类繁多，逐一编码的工作量很大，一时无法完成。在这里只是将同种类型材料设作同一编码，以便统计使用。第二步将每月月底与手工帐目核对无误的库存工作表进行备份，并将备份工作表改名为当前年、月、日。这样既醒目，又可避免因每天操作库存工作表不可避免造成数据损失之后无纠正来源。第三步，设置月结工作表，在这里主要是用两个函数SUMIF、SUMPRODUCT。
月增加数量=SUMIF(库存工作表的类型列,当前类型,库存工作表的总入库量)-SUMIF(上一次备份库存表的类型列, 当前类型, 上一次备份库存表的类型列的总入库量)。意义：当前类型材料截止到目前总的入库量-截止到上月结帐时总的入库量，即为当前类型材料当月入库量。
月增加金额=SUMPRODUCT((库存工作表类型列=当前类型)*(库存工作表总入库量列*库存工作表单价列))- SUMPRODUCT((上一次备份库存表类型列=当前类型)*( 上一次备份库存表总入库量列*上一次备份库存表单价列))。意义：当前类型材料截止到目前总的入库金额-当前类型材料截止到上月结帐时总的入库金额，即当前类型材料当月入库金额。
月减少数量：参考月增加数量。
月减少金额：参考月增加金额。
结存数量：参考月增加数量。
结存金额：参考月增加金额。
（三）解决方案的效果。
通过以上解决方案以后，当每天的出入情况在库存工作表中输入后，库存情况立即显现出来，月结表中也会有所显现，月底核对完库存工作表之后，月结情况也随之出来了。从而极大地减轻了仓储工作人员的工作量。
二、Excel函数在库存分析中的运用
（一）事情的起因。
工程总承包在进行到中后期以后，仓库已库存较多材料，而在工程中却紧缺另外许多种材料。基于对工程结束之后库存材料偏多的担忧，需对库存材料进行一次分析，以查出哪些材料是多余的，哪些材料还需要再采购，以便于后期采购工作的进行以及多余材料的处理。
（二）解决方案的思路。
各材料的总计划量（已有施工单位各子项计划表）即为总需求量，总入库量（已有材料库存表，内含各材料总入库量）+未入库量（已到货未办理入库手续）+在途物资（即已签合同未到货）可视为总采购量，通过这两个数据的量差（采购量差）可以判定材料是否有多买。通过采购量差与库存的比较，可以知道库存的材料是否是有用的，或是其中有多少是无用的。
（三）方案实施前的准备。
此方案主要通过Sumproduct函数进行多条件取值，即判定三个条件:材料名称，材质，规格，从各计划分表中汇总各材料计划量与各材料总入库量作比较，以查出该材料是否有多采购的情况，所以要对各单位各子项计划分表的各种材料名称、材质、规格的表述方式与库存工作表的表述方式进行统一。这个工作量是很大的，在没有统一的材料编码及综合的系统软件的前提下，这是不得已的选择。
在准备过程中为简化方案的难度，还清理了所有的未入库量，并视所有已签合同已执行完，此时总采购量=总入库量。
（四）解决方案的实施。
首先设置库存分析工作表框架：从库存工作表中拷贝出库存明细（含材料名称、材质、规格等信息），在其后设置总入库量列、库存列、采购量差列、计划合计列及所有施工单位各子项列。
第2步用SUMPRODUCT函数在库存分析工作表中对库存工作表数据动态取值，分别取出各材料明细总入库量数据及库存数据。公式：=SUMPRODUCT((库存工作表!材料名称区域=当前材料名称)*(库存工作表！材质区域=当前材质)*(库存工作表! 规格区域=当前规格) *(库存工作表!总入库量区域))，意义：对库存工作表中材料名称、材质、规格分别等于当前材料名称、材质、规格的总入库量数据取值。库存列可参考本公式。
第3步在库存分析工作表中用SUMPRODUCT函数对各单位各子项计划表各材料总计划数据取值。第一个公式可参考第2步公式。后面列改动引用工作表名称即可，其它行套用得所有施工单位所有子项所有材料总计划。
在计划合计列汇总各材料总计划量，在采购量差列设置计划合计与总入库量的量差，于是一个动态的库存分析工作表便产生了。
在此基础之上，通过比较库存与采购量差的关系，可进一步提炼库存分析数据，以得出各类库存无计划物资的分布。
公式=SUMPRODUCT((材料类型区域=某种类型)*(采购量差>0)*(采购量差<库存)*( 采购量差))+SUMPRODUCT((材料类型区域=某种类型)*( 采购量差>0)*( 采购量差>=库存)*(库存))意为：某种类型材料有多采购的时候，库存小于多采购量时取库存，库存大于等于多采购量时取多采购量，其和便某种类型材料总体多采购量。
（五）运用的效果。
通过设置动态库存分析工作表，节省了统计全厂各种材料总计划量的时间。最重要的是，通过它，能够对库存材料的状况有了一个详细的了解，对后期材料的采购以及多余材料的处理有了一个参考性的依据和指导性的方向。
三、Excel函数在库存物资处理中的运用
（一）事情的起因。
工程进行到后期以后，仓库有一些库存，需要查明这些库存中哪些是有计划的，又是哪些单位哪些子项未用的，以利于库存物资的处理。
（二）方案设计。
首先在各单位各子项计划表中设置已领用列、量差列、库存列，。通过对库存工作表中库存列和当前单位及子项出库数据的取值可以取出库存数据和已领用数据，量差设置可得。通过设置，可以看出各单位各子项未领用材料明细库存情况。
第2步设置各单位各子项未领用材料库存总表,以便于总体掌握库存物资的未领用情况。列出所有单位及其工程子项,并将钢材类材料细分为线材，板材，型材，管材四类，以便于分别掌握它们总的未领用库存情况。
第3步汇总各单位各子项计划表中数据。由于在总表中钢材类材料有细分，在这里也必须细分。通过在各计划表中增加材料类别列，分别指定1，2，3，4对应上述材料类别予以解决统计问题。
第4步：在总表中对各计划分表计划数据及已领用数据取值。取值用Sumif函数即可，由于其引用的工作表名称可由总表中施工单位+子项编号组成（与各计划分表名称表述方式一致），所以考虑嵌套使用INDIRECT函数来动态引用各个计划分表以省去改动参数。
（三）运用的效果。
通过此方案设计，对于详细的材料未领用库存情况及总体的未领用库存情况均有了一个充分的了解，对于库存物资的处理也有了一个参考的依据。
四、Excel函数在设备销售清单的确定中的运用
（一）事情的起因
工程收尾阶段，所采购的设备在经过多次销售，并经转手以后已不知具体哪些设备未销售了，只知已销售金额与总合同金额是对不上的，需要清理出未销售的设备清单。
（二）方案分析。
这一问题初看起来时犹如是大海捞针的，因为总的设备合同有500多个，总的设备明细有6000多项，而且有些合同只销售了部分，这就更加增加了问题的难度。
已有的素材：1）各次已销售设备清单（含合同号、设备名称、合同金额等等）。
2）合同资金支付台帐（含合同号、合同金额等等）。
3）每一合同具有Word文本，内含合同金额及明细。
（三）解决方案的思路。
用合同金额减去销售清单中各合同已销售金额，应该可以看出各合同已销售或未销售的情况。
（四）实施步骤。
第一步汇总各次已销售设备清单，并拷贝合并的清单中合同号列至新增工作表sheet1，通过数据/删除重复项选项（Excel2007）删除重复项，保留唯一合同号。
第二步在sheet1中用Sumif函数通过对已销售设备清单汇总得出已销售清单中各合同销售额。
第三步拷贝合同资金支付台帐中合同编号、合同金额列至sheet1,将两份清单分别排序，可大体对应起来。在合同资金区域后用Sumif函数取出对应合同的已销售金额，可得出差额，此差额即为各合同已销售或未销售的情况。在其中会涉及将两份清单中合同编号表述方式统一的问题，统一之后即可。
（五）运用的效果。
通过实施之后，有问题的合同已详细定位出来。通过它可以对那些部分未销售或完全未销售的合同拟定设备销售清单，为设备的最终完全销售铺平了道路。
五、结束语
在工作中靈活运用Excel函数，能够极大地提升工作的效率。经过Excel函数处理数据统计中的一些问题，能够为工程管理带来很大的帮助。
（作者单位：中铝国际遵义工程总承包项目部）
参考文献：
[1]胡顺起，丰晓君，周茂山等. Excel函数在农业气象测墒工作中的巧用. 安徽农业科学，2010，38(2)：837—839.
[2]吴丰科,苏厚勤,朱汉群. Excel对象模型在统计报表中的应用研究. 计算机应用与软件，2009，26(2)：201-204.