河北MK自营超市基于EXCEL进行办公用品管理的方案设计

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

目录

1河北MK自营超市简介...........................................................................................1

1.1超市发展背景..................................................................................................1

1.2MK自营超市组织结构...................................................................................1

2河北MK自营超市办公用品管理现状及管理需求...............................................2

2.1办公用品管理存在的问题..............................................................................2

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

3河北MK自营超市办公用品采购管理Excel模型................................................3

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

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

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

4.1模型验证录.......................................................................................................9

4.2数据分析.........................................................................................................12

5总结..........................................................................................................................12

参考资料......................................................................................................................13

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

河北MK自营超市基于EXCEL进行办公用品采购

业务管理的方案设计

1河北MK自营超市简介

1.1超市发展背景

河北MK自营超市是由一家从乡镇自营的便利店发展成现代河北MK自营

超市，员工由刚开始的5人发展成为现在的88人。超市面积也从原来的150平

方米发展到现如今的1000平方米，上下三层大规模超市。超市一楼主要为食品

和水果区、蔬菜区、粮油区；食品区、二楼主要是日化区、三楼主要是服装区。

MK自营超市在当地有两个大型的仓库、每个仓库面积达到1000平方米以上。

由于受到租金的影响，超市和仓库之间有一定的距离。MK自营超市地理位置优

越，位于乡镇主干道，人流量较大，目前的MK自营超市主要集中在乡镇，在

本县区仅有一家连锁超市与MK自营超市规模相当，但是MK自营超市的最大

优势在于货物比较齐全，经营范围广泛，物价低廉。超市发展总规模最大时拥有

四家分店，员工达到了150人，由于对市场的预估出现偏差，以及超市的库存堆

积严重问题，关闭了两家规模较小的分店。

1.2MK自营超市组织结构

MK自营超市主店共有63人，其中管理层有11人，相关负责人12人，导

购收银40人。具体组织框架如图1.1所示，超市分为财务部，仓储部，宣传部，

收银，后勤部，采购一部，采购二部，服饰部，食品部，非食品部，生鲜部，百

货部。

1

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

图1.1MK自营超市组织框架表

2河北MK自营超市办公用品管理现状及管理需求

2.1办公用品管理存在的问题

该超市仍使用传统的手工对账模式管理办公用品，即所有的办公用品的采购、

入库、领用、对账等记录全都通过手工记账方式完成，可办公用品的采购和领用

数量多且比较零散，导致了办公用品工作量增大，不利于管理人员的统计，传统

的办公用品管理还存在查询困难、统计不便、工作效率低下等问题，其具体表现

如下：

(1)该超市的办公用品种类数量繁多，进出库情况繁多，工作量大，管理人

员就容易出现错记、漏记、重复记录等问题，既增加了工作负担。又不能正确地

反映办公用品情况。还可能出现办公用品流失，影响效益，增加办公用品管理的

成本。

(2)使用纸质文档记录，长久下来，既不利于查找也不利于保存。日积月累，

纸质文档的数量和容量都比较庞大，所属管理部门用途不同，且比较分散，难于

集中管理。

(3)根据实际进出库时的办公用品种类数量金额等进行手工录入，重复、繁

琐、核对、核销的工作量，花费大量人力物力，浪费时间，工作效率低下，且手

工记录管理流程不规范，也不利于后期的结账，无法保证账实符合。

2.2办公用品管理的需求

为了提高超市商品经营的工作效率以及长期的经营发展，可以采用Excel表

格来对超市的商品进行管理。往常在对超市商品进行数量、类型等进行清点时，

2

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

需要查阅很多账本，还要对数量等进行清点对比才能了解清楚，这种情况往往会

很费时费力。使用Excel能够采用设置公式以及表格来管理办公用品，在一个工

作簿中设置不同的表格能够使办公用品的记录数据一目了然、清晰明了地呈现，

当需要查询时也可直接点击查看，不用像纸质账本那样需要同时查看好几本账才

能得到完整的数据;在记录出入库数据时，只需输入相应物品编码即可，物品名

称、数量等数据电脑会通过设置好的公式直接填列，这样减少了管理人员的工作

量，提高了工作效率，减少了企业的管理成本。长久以来，办公用品丢、浪费问

提增加企业管理成本，不利于提高企业效益。这里采用Excel对超市商品等进行

管理能够使办公用品管理更加规范，可以减少因管理人员失误而造成的办公用品

流失的问题。使用Excel管理办公用品数据清晰明了方便管理人员统计各部门使

用情况，将这一指标加入各部门的绩效考核中能极大程度地减少办公用品浪费的

情况，减少企业办公用品的支出，提高企业效益。

3河北MK自营超市办公用品采购管理Excel模型

3.1设计模型框架

新建一个工作薄，命名为“河北MK自营超市基于Excel进行办公用品管理

的方案设计”。内容设置主要包括：办公用品清单、购买记录、领用记录、1月

明细记录在内的4个表格和一个“封面”，方便管理人员调取查看超市办公用品的

各类情况，单子表中文字就可以跳转到想要查看的表格。如下方图3.1所示。

图3.1封面

3.2表格制作

3

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

3.2.1制作办公用品清单

在此，文章主要以下列办公用品为例进行展示：“电脑”、“订书机”、“计

算器”、“打印机”、“A4复印纸”、“复写纸”、“稿纸”、“记事簿”、“中性笔”、“铅

笔”、“白板笔”、“电池”、“纸杯”、“抑菌洗手液”、“消毒液”。

（1）设计表格

设置“物品分类”的表头为“物品类别”、“物品名称”。如图3.2所示

图3.2物品分类表头

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

如图3.3所示。

图3.3办公用品清单表头

（2）引入数据

把“电脑”中物品的代码按照顺序以及它的规律进行设置，“电脑”的代码设置

为“1001”，“订书机”的代码设置为“1002”，“计算器”的代码设置为“1003”，“打印

机”的代码设置为“1004”，“A4复印纸”的代码设置为“2001”，“复写纸”的代码设

置为“2002”，“稿纸”的代码设置为“2003”，“记事簿”的代码设置为“2004”，“中性

笔”的代码设置为“3001”。“铅笔”的代码设置为“3002”，“白板笔”的代码设置为

“3003”，“电池”的代码设置为“4001”，“纸杯”的代码设置为“4002”，“抑菌洗

手液”的代码设置为“4003”，“消毒液”的代码设置为“4004”，后续加入商品就可

用此类编号依次往下写。

3.2.2制作办公用品购入表

（1）设计表格

为了能够更好的对办公用品采购进行管理，这里对“办公用品购入表”清单设

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

量”、“总金额”。如图3.4所示。

图3.4办公用品购入表表头

4

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

（2）引入数据

按照需要进行购买的办公用品类型依次填入到“购入表”中，“购买日期”、“物

品编号”、“物品名称”、“规格”、“单位”、“单价”、“数量”、“总金额”，清单中

出现的数据可以通过Excel表格的公式来进行取得“合计金额”则严格按照购买

发票金额进行填写。

（3）公式的设置

a.“物品编码”的定义名称以及数据验证

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

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

引用位置默认为“=办公用品清单!$E：SE”。

打开“办公用品购入表”，点击【数据】任务卡，单击【数据验证】打开

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

为“=物品编码”，同时选中“忽路空值”和“提供下拉箭头”。如下图所示：

图3.5设置定义名称图3.6设置数据验证

b.“物品名称”列的取值公式设置

选择C3单元格，点击【fx】插入函数，选择类别为全部，选择函数为VLOOKUP

函数，双击打开【函数参数】的对话框，在“测试条件”栏，在

“Lookupvalue”栏，输入B2单元格，并用F4固定（表示绝对引用）；在

“Tablearray”栏，选中“办公用品清单”中的内容E3:I8,并用F4固定；

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

即2；在“Range1ookup”栏，输入FALSE（精确查找）或者TRUE（近似查

找）。

5

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

图3.7设置VLOOKUP函数

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

单!$E$3:$H$17,2,FALSE)，在“购入表”第二列及之后需要获取相应的“物品名

称”公式，只需要再双击向下复制即可

c.“规格”、“单位”的取值同上，即对照“物品名称”在“办公用品清单”中获

取相应物品的信息。

d.“单价”、“数量”列按照购入数据直接输入。

e.“总金额”列的取值公式设置

选择H3单元格，点击【fx】插入函数，选择类别为全部，选择函数为IF函

数，双击打开【函数参数】的对话框，在“测试条件”栏，输入C3单元格，并用

采取F4绝对引用；在“假值”栏，选中“购入表”中的内容E3*F3,并用F4固定。

6

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

图3.8设置IF函数

所以“物品合计金额”列的取值公式=IF(B3="","",F3*G3)在“购入表”第二列及

之后需要获取相应的“合计金额”公式，只需要再双击向下复制即可。

3.2.3制作办公用品领用记录清单

（1）设计表格

为了能够更好的对商品领用的情况进行管理，这里对“领用记录”清单设置表

头为“序号”、“领用日期”、“领用部门”、“物品编号”、“物品名称”、“规格”、“单

位”、“领用数量”。

图3.9领用记录表头

（2）引入数据

将领用的商品分类次填入到“领用记录”表中，“序号”、“领用日期”、“领用部

门”、“物品编号”、“物品名称”、“规格”、“单位”、“领用数量”。清单中出现的

数据可以通过Excel表格的公式来进行填写。

（4）公式的设置

a.“领用部门”、“物品编号”列的定义名称以及数据验证同上

b.“物品名称”、“规格”、“单位”列的取值公式

选择E3单元格，点击【fx】插入函数，选择类别为全部，选择函数为

VLOOKUP函数，双击打开【函数参数】的对话框，在“测试条件”栏，在

“Lookupvalue”栏，输入D3单元格，并用F4固定（表示绝对引用）；在

“Tablearray”栏，选中“办公用品清单”中的内容E3:H17,并用F4固定；

7

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

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

即2；在“Range1ookup”栏，输入FALSE（精确查找）或者TRUE（近似查

找）。

图3.10设置领用记录VLOOKUP函数

所以这列的取值公式=VLOOKUP($D3,办公用品清单!$E$3:$H$17,2,FALSE)，

只需要再双击向下复制即可。

3.2.4制作1月明细表

（1）设计表格

为了能够更好的进行管理，这里对“明细表”设置表头为“物品编码”、“物品

名称”、“规格”、“单位”、“期初数”、“期末数”等办公用品的基础信息。

图3.11库存资产盘点表头

（2）引入数据

在“1月办公用品明细表〞中，“物品编码〞、“物品名称”、“规格”、“单位”

等填写方法同上。期初数按照上月结余如实填写。本期入库数量和金额，使用表

格公式从“办公用品购入表”中按照相应的物品编码合计取得。各部门领用数量

使用公式从“办公用品领用表”中按照相应的物品编码和领用部门合计取得。

（3）公式的设置

8

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

a.“本期入库数量”列的取值公式

选择H3单元格，点击【fx】插入函数，选择类别为全部，选择函数为SUMIF

函数，双击打开【函数参数】的对话框，在“测试条件”栏，在“Sumnrange”

栏中输入“办公用品领用表”的“领用数量”列，并用F4固定；在

“Criteriarangel”栏中输入“办公用品领用表”的“领用部门”列，并用F4

固定;在“Criterial〞栏中输入“1月办公用品明细表”中的各部门“领用数量”

列，并用F4固定；在“Criteriarange2”栏中输入“办公用品领用表”中的“物

品编码”列，并用F4固定；在“Criteria2”栏中输入“1月办公用品

明细表”中的“物品编码”列,得到结果。如下图所示：

图3.12设置SUMIF函数

所以各部门领用数量的公式=SUMIFS(办公用品领用表!$H$3:$H$28,办公用

品领用表!$C$3:$C$28,J$3,办公用品领用表!$D$3:$D$28,$A5)，只需要再双击向

下复制即可。

b.“本期入库的金额”、“各部门的领用数量”等填写方法同上。

C.“期末数的数量”的公式=期初数＋本期入库-各部门领用数量之和，“期

末数的单价”采取月末平均取数，公式=（月初库存的实际成本+当月各批进货的

实际成本）/（月初库存数量＋当月各批进货数最之和）。

4模型验证及数据分析

4.1模型验证录

4.1.1办公用品购入表

（1）2022年1月16日购电脑15台，单价为4800/台；打印机2台，单价

为3500/台；中性笔30盒，单价为18/盒。

9

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

（2）2022年1月18日购进纸杯10箱，单价为80/箱；抑菌洗手液20瓶，

单价为15/瓶；稿纸30本，单价5/本。

（3）2022年1月22日购进铅笔10盒，单价为9/盒；复写纸50本，单价

为7/本；白板笔10盒，单价32/盒。

（4）2022年1月25日购进消毒液5瓶，单价为20/瓶；A4打印纸70包，

单价为26/包；记事簿100本，单价2/本。

代入数据：

图4.1办公用品购入表

4.1.2领用记录表

（1）2020年1月16日业务部申领复写纸5本、纸杯4箱、电脑8台。

（2）2022年1月4日成本部申领订书机5个、打印机1台、记事簿20本。

（3）2022年1月10日采购部申领复写纸10本、记事簿10本、中性笔4

盒。

（4）2022年1月15日成本部申领白板笔2盒、铅笔3盒。

（5）2022年1月20日财务部申领A4打印纸15包、复写纸10本、稿纸30

本、记事簿10本、中性笔2盒、铅笔2盒。

（6）2022年1月24日人事部申领中性笔3盒、打印机1台、A4打印纸7

包、复写纸10本、稿纸10本、纸杯2箱。

（7）2022年1月29日成本部申领计算器6个，保洁部申领抑菌洗手液5

瓶、消毒液5瓶。

代入数据：

10

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

图4.2办公用品领用记录表

4.1.4期初数据

上月结余可得本期期初商品数据库存数量为：电脑库存2台共10000元，订

书机库存7个共105元，计算器9个共126元，A4复印纸2包共60元，复写纸

3本共18元，稿纸12本共72元，记事簿7本共7元，中性笔9盒共180元，

铅笔8盒共80元，白板笔