温州沿顺商贸有限公司基于Excel进行办公用品管理的方案设计

湖南商务职业技术学院毕业设计

目录

1公司简介及组织架构.................................................1

1.1公司简介.....................................................1

1.2公司组织架构.................................................1

2温州沿顺商贸有限公司办公用品管理现状及要求分析.....................2

2.1公司办公用品管理现状.........................................2

2.2公司办公用品管理需求.........................................2

3温州沿顺商贸有限公司办公用品管理EXCEL模型.........................2

3.1设计模型框架.................................................2

3.2表格制作.....................................................3

3.2.1制作办公用品清单........................................3

3.2.2制作办公用品购入表......................................4

3.2.3制作办公用品领用表......................................7

3.2.4制作办公用品明细表.....................................10

4模型验证及数据分析................................................14

4.1模型验证....................................................14

4.1.1代入购入记录...........................................14

4.1.2代入领用记录...........................................14

4.1.3引入办公用品明细表.....................................15

4.2数据分析....................................................16

5总结..............................................................16

参考资料............................................................18

I

湖南商务职业技术学院毕业设计

温州沿顺商贸有限公司基于EXCEL进行办公用品管

理的方案设计

1公司简介及组织架构

1.1公司简介

温州沿顺商贸有限公司是在浙江省注册的有限责任公司，于2017-02-04注

册，注册地址为浙江省温州市瓯海区国溪街塘下村下野路125号夏野路125号。

主要活动：销售（包括在线销售）：服装，服饰，鞋，皮革制品和其他产品。

公司秉承“务实，勤奋，负责”的企业精神，营造可靠，各种机遇，创新的经

营理念的良好经营环境。它使用全新的管理模式，完善的技术，周到的服务和

卓越的品质作为生存的基础。始终坚持客户至上，全心全意为客户服务。

1.2公司组织架构

图1-1公司组织结构图

该公司组织架构比较简单，如图1-1所示。主要由总经理负责公司整体战

略规划，副总经理对下级职能部门负责。职能部门较多，分别由研发部、生产

部、采购部、质检部和市场部、营销部、财务部、行政部等部门组成。

1

湖南商务职业技术学院毕业设计

2温州沿顺商贸有限公司办公用品管理现状及要求分析

2.1公司办公用品管理现状

随着企业规模的不断扩大和业务的日益复杂，办公用品的种类和数量也在

不断增加。然而，目前温州沿顺商贸有限公司在办公用品管理方面存在着诸多

问题，例如：

（1）随着企业业务的日益复杂和信息化程度的不断提高，办公用品的种类

和数量也在不断增加。除了传统的办公用品如纸张、笔等，还涌现出了许多新

型办公用品如电脑、打印机、复印机、扫描仪等。

（2）企业在办公用品采购方面缺乏规范的管理，采购流程不透明，采购人

员缺乏专业知识，导致采购的办公用品质量不稳定、价格过高、规格不符合要

求等问题。

（3）由于缺乏有效的库存管理，企业办公用品库存积压严重，占用了大量

的资金和空间，影响了企业的资金周转和办公效率。

（4）许多员工对办公用品的使用缺乏节约意识，随意浪费办公用品，如纸

张等，导致企业运营成本增加。

2.2公司办公用品管理需求

在现代办公环境中，办公管理用品的有效管理对于提高工作效率和降低成

本至关重要。Excel作为一款广泛使用的电子表格软件，具有强大的数据处理

和分析功能，为温州沿顺商贸有限公司办公用品的管理提供了潜在的解决方案。

而且Excel具有极大的使用灵活性和无限制的格式。它有助于进行日常会计核

算，也便于将来的查询。此外，温州沿顺商贸有限公司主要是一家在线销售公

司，大部分数据来自互联网，因此决定使用电子表格软件模板来解决温州沿顺

商贸有限公司对办公用品管理的问题。

3温州沿顺商贸有限公司办公用品管理EXCEL模型

3.1设计模型框架

首先，打开一个工作簿，命名为“温洲沿顺商贸有限公司办公用品Excel

模型”。新建一个工作表，将其命名为“首页”，点击“插入”，插入一个文

本框。再在此工作簿上建立四个模块，分别为办公用品清单、办公用品购入表、

2

湖南商务职业技术学院毕业设计

办公用品领用表、办公用品明细表。为了方便查看，分别对其做了一个超链接

的功能，点击框架中间，便可跳转到其对应的页面。如下图3-1所示。

图3-1办公用品设计方案、返回首页图

3.2表格制作

3.2.1制作办公用品清单

该表将以“笔记本”、“A4打印纸”、“便利贴”、“复写纸”、“打印

机”、“台式电脑”、“投影仪”、“插座”、“计算器”、“记号笔”、“中

性笔”、“洗手液”、“消毒液”、“纸杯”、“电池”等办公用品为例。

（1）设计表格

将“物品分类”的表头设置为“物品类别”、“类别名称”，如图3-2所

示。

图3-2物品分类表头

3

湖南商务职业技术学院毕业设计

将“办公用品清单”的表头设置为“物品编码”、“物品名称”、“规格”、

“单位”，如图3-3所示。

图3-3办公用品清单表头

（2）引入数据

将“笔记本”、“A4打印纸”、“便利贴”、“复写纸”归类为“办公用

纸”设置分类编码为“1”；按照分类的顺序及规律分别设置为“1001”，“1002”

“1003”，“1004”等。

将“打印机”、“台式电脑”、“投影仪”、“插座”、“计算器”归类

为“办公设备”设置分类编码为“2”；按照分类的顺序及规律分别设置为

“2001”，“2002”“2003”，“2004”，“2005”等。

将“记号笔”、“中性笔”归类为“办公用笔”设置分类编码为“3”；按

照分类的顺序及规律分别设置为“3001”，“3002”等。

将“洗手液”、“消毒液”、“纸杯”、“电池”归类为“其他日用”设

置分类编码为“4”；按照分类的顺序及规律分别设置为“4001”，“4002”，

“4003”，“4004”等。

将上述信息填入表格中，以后企业购入办公用品也可以此分类编号填写。

3.2.2制作办公用品购入表

（1）设计表格

为了方便公司对办公用品采购进行管理，这里对“办公用品购入表”清单

设置表头为“购买日期”、“物品编号”、“物品名称”、“规格”、“单位”、

“单价”、“数量”、“总金额”。如图3-4所示。

图3-4办公用品购入表表头

（2）引入数据

按照购买日期依次将购买的办公用品类型依次填入到“办公用品购入表”

中，“购买日期”、“物品编号”、“物品名称”、“规格”、“单位”、等

清单中有的数据可按照公式获得。“单价”、“数量”、“总金额”应当严格

按照购买发票金额进行填写。

（3）公式设置

4

湖南商务职业技术学院毕业设计

1录入物品编号

选中办公用品清单中“物品编码”栏，点击【公式】任务卡，单击【定义

的名称】，打开“新建名称对话框”，设置名称为“物品编码”，范围为“工

作簿”，引用位置默认为“=办公用品清单！$D:$D”。如下图3-5所示。

图3-5定义的名称

2设置数据验证

打开“办公用品购入表”，选中要录入物品编号的空白栏，点击【数据】

任务卡，单击【数据验证】打开“数据验证”对话框，设置验证条件，允许框

选择“序列”，来源设置为“=物品编码”，同时选中“忽略空值”和“提供下

拉箭头”复选框。如图3-6所示。

5

湖南商务职业技术学院毕业设计

图3-6设置“数据验证”

3录入物品名称

为了更好地提高工作效率，简化工作，可在“物品名称”、“规格”、“单

位”中录入VLOOKUP函数。VLOOKUP函数是Excel中的一个查找函数，用于

在表格或数据区域中查找指定的值，并返回与之对应的另一列中的值。

它的基本语法如下：=VLOOKUP(查找值,查找区域,返回列数,匹配条件)

其中，查找值是要查找的值，可以是一个单元格引用或一个值；查找区域

是包含查找值的数据区域，可以是一个单元格区域或一个表格；返回列数是要

返回的值所在的列数，从查找区域的第一列开始计数；匹配条件是一个可选参

数，用于指定查找的匹配方式，可以是TRUE或FALSE，默认为TRUE。

点击C3单元格，点击【fx】插入函数，找到VLOOKUP函数，双击打开“函

数参数”对话框，在“Lookupvalue”栏，输入“B3”单元格，并用F4固定，

对其绝对引用；在“Tablearray”栏，选中“办公用品清单”中的D3:G17单

元格，并用F4固定；在“Colindexnum”栏，输入“办公用品清单”中“物

品名称”所在的列数，即“2”；在“Rangelookup”栏，输入FALSE(精确查

找）。如下图3-7所示。

6

湖南商务职业技术学院毕业设计

图3-7设置VLOOKUP函数

所以“物品名称”栏的取值公式=VLOOKUP($B3,办公用品清

单!$D$3:$G$17,2,FALSE)，再双击向下复制。“规格”的取值公式=VLOOKUP($B3,

办公用品清单!$D$3:$G$17,3,FALSE)，再双击向下复制；“单位”的取值公式

=VLOOKUP($B3,办公用品清单!$D$3:$G$17,4,FALSE)，再双击向下复制。“总金

额”列的取值公式=F3*G3，即总金额=单价*数量，再双击向下复制。

3.2.3制作办公用品领用表

（1）设计表格

为了将各部门办公用品的领用情况进行详细的记录，便于公司了解各部门，

对公司的开支做好监督和管理。领用表中栏目分别设置为“序号”、“领用日

期”、“领用部门”、“物品编号”、“物品名称”、“规格”、“单位”、

“领用数量”。如图3-8所示。

图3-8办公用品领用表表头

（2）引入数据

按照各部门办公用品领用日期依次填入“办公用品领用表”中，“领用部门”、

“物品编号”可以设置数据验证填写，“物品名称”、“规格”、“单位”同

样可以设置VLOOKUP函数获取，“领用数量”则严格按照各部门领用办公用品

数量填写。

7

湖南商务职业技术学院毕业设计

（3）设置公式

1设置“领用部门”数据验证

单击“办公用品领用表”点击“领用部门”下方空白格，点击【数据】任务

栏，单击【数据验证】打开“数据验证”对话框，设置验证条件，允许框选择

“序列”，来源设置为“研发部,生产部,采购部,质检部,市场部,营销部,销售

部,财务部,行政部”，同时选中“忽略空值”和“提供下拉箭头”复选框。如

图3-9所示。

图3-9设置领用部门数据验证

2设置“物品编号”数据验证

选中要录入物品编号的空白栏，点击【数据】任务卡，单击【数据验证】

打开“数据验证”对话框，设置验证条件，允许框选择“序列”，来源设置为

“=办公用品清单!$D:$D”，同时选中“忽略空值”和“提供下拉箭头”复选框。

如图3-10所示。

8

湖南商务职业技术学院毕业设计

图3-10设置物品编号数据验证

3录入“物品名称”、“规格”、“单位”

同办公用品购入表一样，需要用到VLOOKUP函数，点击E3单元格，点击【fx】

插入函数，找到VLOOKUP函数，双击打开“函数参数”对话框，在“Lookupvalue”

栏，输入“D3”单元格，并用F4固定，对其绝对引用；在“Tablearray”栏，

选中“办公用品清单”中的D3:G17单元格，并用F4固定；在“Colindexnum”

栏，输入“办公用品清单”中“物品名称”所在的列数，即“2”；在“Rangelookup”

栏，输入FALSE(精确查找）。如下图3-11所示。

9

湖南商务职业技术学院毕业设计

图3-11设置VLOOKUP函数

即“物品名称”的取值公式=VLOOKUP($D3,办公用品清

单!$D$3:$G$17,2,FALSE)；“规格”的取值公式=VLOOKUP($D3,办公用品清

单!$D$3:$G$17,3,FALSE)；“单位”的取值公式=VLOOKUP($D3,办公用品清

单!$D$3:$G$17,4,FALSE)，分别双击向下复制。“领用数量”按照实际领用数

量填写。

3.2.4制作办公用品明细表

（1）设计表格

为了更方便的进行预算管理，公职办公用品的开支，制作一个“办公用品

明细表”更清晰的了解办公用品的库存情况，避免不必要的重复采购和缺货。

表头分别为“物品编号”、“物品名称”、“规格”、“单位”、“期初数（数

量、单价、金额）”、“本期入库（数量、金额）”、“期末数（数量、单价、

金额）”，还应细分到各个部门，如“研发部领用数量”,“生产部领用数量”,

“采购部领用数量”,“质检部领用数量”,“市场部领用数量”,“营销部领用

数量”,“销售部领用数量”,“财务部领用数量”,“行政部领用数量”。如图

3-12所示。

图3-12办公用品明细表表头

（2）引入数据

10

湖南商务职业技术学院毕业设计

将“物品编码”用数据验证填入，同“办公用品领用表”一样，再将“物

品名称”、“规格”、“单位”用VLOOKUP函数自动填入。“期初数”按照上

月月末数进行填写。各部门的领用根据“办公用品领用表”中的领用数填写。

“期末数量=期初数量+本期入库数量-本期领用数量”，期末数单价根据月末一

次加权平均单价公式填写。“（本期期初金额+本期入库金额）/（本期期初数

量+本期入库数量）”期末金额=期末数量*单价。

（3）设置公式

1设置物品编号数据验证

选中要录入物品编号的空白栏，点击【数据】任务卡，单击【数据验证】

打开“数据验证”对话框，设置验证条件，允许框选择“序列”，来源设置为

“=办公用品清单!$D:$D”，同时选中“忽略空值”和“提供下拉箭头”复选框。

如图3-13所示。

图3-13设置物品编号数据验证

2录入“物品名称”、“规格”、“单位”

“物品名称”的取值公式=VLOOKUP($D3,办公用品清

单!$D$3:$G$17,2,FALSE)；“规格”的取值公式=VLOOKUP($D3,办公用品清

单!$D$3:$G$17,3,FALSE)；“单位”的取值公式=VLOOKUP($D3,办公用品清

单!$D$3:$G$17,4,FALSE)，分别双击向下复制。“领用数量”按照实际领用数

11

湖南商务职业技术学院毕业设计

量填写。如下图3-14所示。

图3-13设置VLOOKUP函数

3录入本期入库的“数量”、“金额”

可以采用SUMIF函数，单击单元格H5输入函数=SUMIF(办公用品购入

表!$B$3:$B$17,$A$5:$A$19,办公用品购入表!$G$3:$G$17)，如图3-14所示，

并向下填充到其他单元格。单击单元格I5输入函数=SUMIF(办公用品购入

表!$B$3:$B$17,$A$5:$A$19,办公用品购入表!$H$3:$H$17)，如图3-15所示，

并向下填充到其他单元格。

图3-14设置SUMIF函数

12

湖南商务职业技术学院毕业设计

图3-15设置SUMIF函数

各部门的领用情况也可以采用SUMIFS函数，单击J5单元格输入=SUMIFS

(办公用品领用表!$H$3:$H$19,办公用品领用表!$C$3:$C$19,J$4,办公用品领

用表!$D$3:$D$19,$A5)，如图3-16所示，双击填充到其他单元格。

图3-16设置SUMIFS函数

13

湖南商务职业技术学院毕业设计

4模型验证及数据分析

4.1模型验证

为了确保模型的准确性，可靠性和实用性，将代入公司部门购入数据和领

用数据。评估模型是否能够准确的模拟或预测实际情况。

4.1.1代入购入记录

12023年8月1日购入台式电脑10台，单价5688元/台。

22023年8月2日购入投影仪5台，单价1200元/台。

32023年8月8日购入计算器20个，单价20元/个。

42023年8月9日购入打印纸20包，单价90元/包。

52023年8月11日购入打印机5台，单价4000元/台。

62023年8月12日购入复写纸30盒，单价8元/盒。

72023年8月18日购入中性笔30盒，单价20元/盒。

82023年8月20日购入一次性纸杯20箱，单价70元/箱。

代入数据：

图4-1办公用品购入表

4.1.2代入领用记录

12023年8月1日生产部领用插座10个。

22023年8月4日市场部领用记号笔2盒，计算器2个，复写纸4本。

32023年8月8日销售部领用笔记本5本，复写纸4本，计算器2个。

42023年8月18日财务部领用复写纸5本，中性笔5盒，插座1个。

52023年8月20日行政部领用投影仪1台，打印机1台，台式电脑3台。

62023年8月28日营销部领用打印机1台，电池2盒，投影仪1台。

代入数据：

14

湖南商务职业技术学院毕业设计