河北MK自營(yíng)超市基于EXCEL進(jìn)行辦公用品管理的方案設(shè)計(jì)

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

目錄

1河北MK自營(yíng)超市簡(jiǎn)介...........................................................................................1

1.1超市發(fā)展背景..................................................................................................1

1.2MK自營(yíng)超市組織結(jié)構(gòu)...................................................................................1

2河北MK自營(yíng)超市辦公用品管理現(xiàn)狀及管理需求...............................................2

2.1辦公用品管理存在的問(wèn)題..............................................................................2

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

3河北MK自營(yíng)超市辦公用品采購(gòu)管理Excel模型................................................3

3.1設(shè)計(jì)模型框架...................................................................................................3

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

4模型驗(yàn)證及數(shù)據(jù)分析................................................................................................9

4.1模型驗(yàn)證錄.......................................................................................................9

4.2數(shù)據(jù)分析.........................................................................................................12

5總結(jié)..........................................................................................................................12

參考資料......................................................................................................................13

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

河北MK自營(yíng)超市基于EXCEL進(jìn)行辦公用品采購(gòu)

業(yè)務(wù)管理的方案設(shè)計(jì)

1河北MK自營(yíng)超市簡(jiǎn)介

1.1超市發(fā)展背景

河北MK自營(yíng)超市是由一家從鄉(xiāng)鎮(zhèn)自營(yíng)的便利店發(fā)展成現(xiàn)代河北MK自營(yíng)

超市，員工由剛開始的5人發(fā)展成為現(xiàn)在的88人。超市面積也從原來(lái)的150平

方米發(fā)展到現(xiàn)如今的1000平方米，上下三層大規(guī)模超市。超市一樓主要為食品

和水果區(qū)、蔬菜區(qū)、糧油區(qū)；食品區(qū)、二樓主要是日化區(qū)、三樓主要是服裝區(qū)。

MK自營(yíng)超市在當(dāng)?shù)赜袃蓚€(gè)大型的倉(cāng)庫(kù)、每個(gè)倉(cāng)庫(kù)面積達(dá)到1000平方米以上。

由于受到租金的影響，超市和倉(cāng)庫(kù)之間有一定的距離。MK自營(yíng)超市地理位置優(yōu)

越，位于鄉(xiāng)鎮(zhèn)主干道，人流量較大，目前的MK自營(yíng)超市主要集中在鄉(xiāng)鎮(zhèn)，在

本縣區(qū)僅有一家連鎖超市與MK自營(yíng)超市規(guī)模相當(dāng)，但是MK自營(yíng)超市的最大

優(yōu)勢(shì)在于貨物比較齊全，經(jīng)營(yíng)范圍廣泛，物價(jià)低廉。超市發(fā)展總規(guī)模最大時(shí)擁有

四家分店，員工達(dá)到了150人，由于對(duì)市場(chǎng)的預(yù)估出現(xiàn)偏差，以及超市的庫(kù)存堆

積嚴(yán)重問(wèn)題，關(guān)閉了兩家規(guī)模較小的分店。

1.2MK自營(yíng)超市組織結(jié)構(gòu)

MK自營(yíng)超市主店共有63人，其中管理層有11人，相關(guān)負(fù)責(zé)人12人，導(dǎo)

購(gòu)收銀40人。具體組織框架如圖1.1所示，超市分為財(cái)務(wù)部，倉(cāng)儲(chǔ)部，宣傳部，

收銀，后勤部，采購(gòu)一部，采購(gòu)二部，服飾部，食品部，非食品部，生鮮部，百

貨部。

1

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

圖1.1MK自營(yíng)超市組織框架表

2河北MK自營(yíng)超市辦公用品管理現(xiàn)狀及管理需求

2.1辦公用品管理存在的問(wèn)題

該超市仍使用傳統(tǒng)的手工對(duì)賬模式管理辦公用品，即所有的辦公用品的采購(gòu)、

入庫(kù)、領(lǐng)用、對(duì)賬等記錄全都通過(guò)手工記賬方式完成，可辦公用品的采購(gòu)和領(lǐng)用

數(shù)量多且比較零散，導(dǎo)致了辦公用品工作量增大，不利于管理人員的統(tǒng)計(jì)，傳統(tǒng)

的辦公用品管理還存在查詢困難、統(tǒng)計(jì)不便、工作效率低下等問(wèn)題，其具體表現(xiàn)

如下：

(1)該超市的辦公用品種類數(shù)量繁多，進(jìn)出庫(kù)情況繁多，工作量大，管理人

員就容易出現(xiàn)錯(cuò)記、漏記、重復(fù)記錄等問(wèn)題，既增加了工作負(fù)擔(dān)。又不能正確地

反映辦公用品情況。還可能出現(xiàn)辦公用品流失，影響效益，增加辦公用品管理的

成本。

(2)使用紙質(zhì)文檔記錄，長(zhǎng)久下來(lái)，既不利于查找也不利于保存。日積月累，

紙質(zhì)文檔的數(shù)量和容量都比較龐大，所屬管理部門用途不同，且比較分散，難于

集中管理。

(3)根據(jù)實(shí)際進(jìn)出庫(kù)時(shí)的辦公用品種類數(shù)量金額等進(jìn)行手工錄入，重復(fù)、繁

瑣、核對(duì)、核銷的工作量，花費(fèi)大量人力物力，浪費(fèi)時(shí)間，工作效率低下，且手

工記錄管理流程不規(guī)范，也不利于后期的結(jié)賬，無(wú)法保證賬實(shí)符合。

2.2辦公用品管理的需求

為了提高超市商品經(jīng)營(yíng)的工作效率以及長(zhǎng)期的經(jīng)營(yíng)發(fā)展，可以采用Excel表

格來(lái)對(duì)超市的商品進(jìn)行管理。往常在對(duì)超市商品進(jìn)行數(shù)量、類型等進(jìn)行清點(diǎn)時(shí)，

2

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

需要查閱很多賬本，還要對(duì)數(shù)量等進(jìn)行清點(diǎn)對(duì)比才能了解清楚，這種情況往往會(huì)

很費(fèi)時(shí)費(fèi)力。使用Excel能夠采用設(shè)置公式以及表格來(lái)管理辦公用品，在一個(gè)工

作簿中設(shè)置不同的表格能夠使辦公用品的記錄數(shù)據(jù)一目了然、清晰明了地呈現(xiàn)，

當(dāng)需要查詢時(shí)也可直接點(diǎn)擊查看，不用像紙質(zhì)賬本那樣需要同時(shí)查看好幾本賬才

能得到完整的數(shù)據(jù);在記錄出入庫(kù)數(shù)據(jù)時(shí)，只需輸入相應(yīng)物品編碼即可，物品名

稱、數(shù)量等數(shù)據(jù)電腦會(huì)通過(guò)設(shè)置好的公式直接填列，這樣減少了管理人員的工作

量，提高了工作效率，減少了企業(yè)的管理成本。長(zhǎng)久以來(lái)，辦公用品丟、浪費(fèi)問(wèn)

提增加企業(yè)管理成本，不利于提高企業(yè)效益。這里采用Excel對(duì)超市商品等進(jìn)行

管理能夠使辦公用品管理更加規(guī)范，可以減少因管理人員失誤而造成的辦公用品

流失的問(wèn)題。使用Excel管理辦公用品數(shù)據(jù)清晰明了方便管理人員統(tǒng)計(jì)各部門使

用情況，將這一指標(biāo)加入各部門的績(jī)效考核中能極大程度地減少辦公用品浪費(fèi)的

情況，減少企業(yè)辦公用品的支出，提高企業(yè)效益。

3河北MK自營(yíng)超市辦公用品采購(gòu)管理Excel模型

3.1設(shè)計(jì)模型框架

新建一個(gè)工作薄，命名為“河北MK自營(yíng)超市基于Excel進(jìn)行辦公用品管理

的方案設(shè)計(jì)”。內(nèi)容設(shè)置主要包括：辦公用品清單、購(gòu)買記錄、領(lǐng)用記錄、1月

明細(xì)記錄在內(nèi)的4個(gè)表格和一個(gè)“封面”，方便管理人員調(diào)取查看超市辦公用品的

各類情況，單子表中文字就可以跳轉(zhuǎn)到想要查看的表格。如下方圖3.1所示。

圖3.1封面

3.2表格制作

3

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

3.2.1制作辦公用品清單

在此，文章主要以下列辦公用品為例進(jìn)行展示：“電腦”、“訂書機(jī)”、“計(jì)

算器”、“打印機(jī)”、“A4復(fù)印紙”、“復(fù)寫紙”、“稿紙”、“記事簿”、“中性筆”、“鉛

筆”、“白板筆”、“電池”、“紙杯”、“抑菌洗手液”、“消毒液”。

（1）設(shè)計(jì)表格

設(shè)置“物品分類”的表頭為“物品類別”、“物品名稱”。如圖3.2所示

圖3.2物品分類表頭

設(shè)置“辦公用品清單”的表頭為“物品編碼”、“物品名稱”、“規(guī)格”、“單位”。

如圖3.3所示。

圖3.3辦公用品清單表頭

（2）引入數(shù)據(jù)

把“電腦”中物品的代碼按照順序以及它的規(guī)律進(jìn)行設(shè)置，“電腦”的代碼設(shè)置

為“1001”，“訂書機(jī)”的代碼設(shè)置為“1002”，“計(jì)算器”的代碼設(shè)置為“1003”，“打印

機(jī)”的代碼設(shè)置為“1004”，“A4復(fù)印紙”的代碼設(shè)置為“2001”，“復(fù)寫紙”的代碼設(shè)

置為“2002”，“稿紙”的代碼設(shè)置為“2003”，“記事簿”的代碼設(shè)置為“2004”，“中性

筆”的代碼設(shè)置為“3001”。“鉛筆”的代碼設(shè)置為“3002”，“白板筆”的代碼設(shè)置為

“3003”，“電池”的代碼設(shè)置為“4001”，“紙杯”的代碼設(shè)置為“4002”，“抑菌洗

手液”的代碼設(shè)置為“4003”，“消毒液”的代碼設(shè)置為“4004”，后續(xù)加入商品就可

用此類編號(hào)依次往下寫。

3.2.2制作辦公用品購(gòu)入表

（1）設(shè)計(jì)表格

為了能夠更好的對(duì)辦公用品采購(gòu)進(jìn)行管理，這里對(duì)“辦公用品購(gòu)入表”清單設(shè)

置表頭為“購(gòu)買日期”、“物品編號(hào)”、“物品名稱”、“規(guī)格”、“單位”、“單價(jià)”、“數(shù)

量”、“總金額”。如圖3.4所示。

圖3.4辦公用品購(gòu)入表表頭

4

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

（2）引入數(shù)據(jù)

按照需要進(jìn)行購(gòu)買的辦公用品類型依次填入到“購(gòu)入表”中，“購(gòu)買日期”、“物

品編號(hào)”、“物品名稱”、“規(guī)格”、“單位”、“單價(jià)”、“數(shù)量”、“總金額”，清單中

出現(xiàn)的數(shù)據(jù)可以通過(guò)Excel表格的公式來(lái)進(jìn)行取得“合計(jì)金額”則嚴(yán)格按照購(gòu)買

發(fā)票金額進(jìn)行填寫。

（3）公式的設(shè)置

a.“物品編碼”的定義名稱以及數(shù)據(jù)驗(yàn)證

選中辦公用品清單中“物品編碼”欄，點(diǎn)開【公式】選項(xiàng)卡，單擊【定義名

稱】打開“新建名稱”對(duì)話框，設(shè)置名稱為“物品編碼”，范圍為“工作簿”，

引用位置默認(rèn)為“=辦公用品清單!$E：SE”。

打開“辦公用品購(gòu)入表”，點(diǎn)擊【數(shù)據(jù)】任務(wù)卡，單擊【數(shù)據(jù)驗(yàn)證】打開

“數(shù)據(jù)驗(yàn)證”對(duì)話框，輸入驗(yàn)證條件，設(shè)置允許框選擇為“序列”，來(lái)源設(shè)置

為“=物品編碼”，同時(shí)選中“忽路空值”和“提供下拉箭頭”。如下圖所示：

圖3.5設(shè)置定義名稱圖3.6設(shè)置數(shù)據(jù)驗(yàn)證

b.“物品名稱”列的取值公式設(shè)置

選擇C3單元格，點(diǎn)擊【fx】插入函數(shù)，選擇類別為全部，選擇函數(shù)為VLOOKUP

函數(shù)，雙擊打開【函數(shù)參數(shù)】的對(duì)話框，在“測(cè)試條件”欄，在

“Lookupvalue”欄，輸入B2單元格，并用F4固定（表示絕對(duì)引用）；在

“Tablearray”欄，選中“辦公用品清單”中的內(nèi)容E3:I8,并用F4固定；

在“colindexnum”欄，輸入“辦公用品清單”中“物品名稱”所在的列數(shù)，

即2；在“Range1ookup”欄，輸入FALSE（精確查找）或者TRUE（近似查

找）。

5

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

圖3.7設(shè)置VLOOKUP函數(shù)

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

單!$E$3:$H$17,2,FALSE)，在“購(gòu)入表”第二列及之后需要獲取相應(yīng)的“物品名

稱”公式，只需要再雙擊向下復(fù)制即可

c.“規(guī)格”、“單位”的取值同上，即對(duì)照“物品名稱”在“辦公用品清單”中獲

取相應(yīng)物品的信息。

d.“單價(jià)”、“數(shù)量”列按照購(gòu)入數(shù)據(jù)直接輸入。

e.“總金額”列的取值公式設(shè)置

選擇H3單元格，點(diǎn)擊【fx】插入函數(shù)，選擇類別為全部，選擇函數(shù)為IF函

數(shù)，雙擊打開【函數(shù)參數(shù)】的對(duì)話框，在“測(cè)試條件”欄，輸入C3單元格，并用

采取F4絕對(duì)引用；在“假值”欄，選中“購(gòu)入表”中的內(nèi)容E3*F3,并用F4固定。

6

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

圖3.8設(shè)置IF函數(shù)

所以“物品合計(jì)金額”列的取值公式=IF(B3="","",F3*G3)在“購(gòu)入表”第二列及

之后需要獲取相應(yīng)的“合計(jì)金額”公式，只需要再雙擊向下復(fù)制即可。

3.2.3制作辦公用品領(lǐng)用記錄清單

（1）設(shè)計(jì)表格

為了能夠更好的對(duì)商品領(lǐng)用的情況進(jìn)行管理，這里對(duì)“領(lǐng)用記錄”清單設(shè)置表

頭為“序號(hào)”、“領(lǐng)用日期”、“領(lǐng)用部門”、“物品編號(hào)”、“物品名稱”、“規(guī)格”、“單

位”、“領(lǐng)用數(shù)量”。

圖3.9領(lǐng)用記錄表頭

（2）引入數(shù)據(jù)

將領(lǐng)用的商品分類次填入到“領(lǐng)用記錄”表中，“序號(hào)”、“領(lǐng)用日期”、“領(lǐng)用部

門”、“物品編號(hào)”、“物品名稱”、“規(guī)格”、“單位”、“領(lǐng)用數(shù)量”。清單中出現(xiàn)的

數(shù)據(jù)可以通過(guò)Excel表格的公式來(lái)進(jìn)行填寫。

（4）公式的設(shè)置

a.“領(lǐng)用部門”、“物品編號(hào)”列的定義名稱以及數(shù)據(jù)驗(yàn)證同上

b.“物品名稱”、“規(guī)格”、“單位”列的取值公式

選擇E3單元格，點(diǎn)擊【fx】插入函數(shù)，選擇類別為全部，選擇函數(shù)為

VLOOKUP函數(shù)，雙擊打開【函數(shù)參數(shù)】的對(duì)話框，在“測(cè)試條件”欄，在

“Lookupvalue”欄，輸入D3單元格，并用F4固定（表示絕對(duì)引用）；在

“Tablearray”欄，選中“辦公用品清單”中的內(nèi)容E3:H17,并用F4固定；

7

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

在“colindexnum”欄，輸入“辦公用品清單”中“物品名稱”所在的列數(shù)，

即2；在“Range1ookup”欄，輸入FALSE（精確查找）或者TRUE（近似查

找）。

圖3.10設(shè)置領(lǐng)用記錄VLOOKUP函數(shù)

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

只需要再雙擊向下復(fù)制即可。

3.2.4制作1月明細(xì)表

（1）設(shè)計(jì)表格

為了能夠更好的進(jìn)行管理，這里對(duì)“明細(xì)表”設(shè)置表頭為“物品編碼”、“物品

名稱”、“規(guī)格”、“單位”、“期初數(shù)”、“期末數(shù)”等辦公用品的基礎(chǔ)信息。

圖3.11庫(kù)存資產(chǎn)盤點(diǎn)表頭

（2）引入數(shù)據(jù)

在“1月辦公用品明細(xì)表〞中，“物品編碼〞、“物品名稱”、“規(guī)格”、“單位”

等填寫方法同上。期初數(shù)按照上月結(jié)余如實(shí)填寫。本期入庫(kù)數(shù)量和金額，使用表

格公式從“辦公用品購(gòu)入表”中按照相應(yīng)的物品編碼合計(jì)取得。各部門領(lǐng)用數(shù)量

使用公式從“辦公用品領(lǐng)用表”中按照相應(yīng)的物品編碼和領(lǐng)用部門合計(jì)取得。

（3）公式的設(shè)置

8

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

a.“本期入庫(kù)數(shù)量”列的取值公式

選擇H3單元格，點(diǎn)擊【fx】插入函數(shù)，選擇類別為全部，選擇函數(shù)為SUMIF

函數(shù)，雙擊打開【函數(shù)參數(shù)】的對(duì)話框，在“測(cè)試條件”欄，在“Sumnrange”

欄中輸入“辦公用品領(lǐng)用表”的“領(lǐng)用數(shù)量”列，并用F4固定；在

“Criteriarangel”欄中輸入“辦公用品領(lǐng)用表”的“領(lǐng)用部門”列，并用F4

固定;在“Criterial〞欄中輸入“1月辦公用品明細(xì)表”中的各部門“領(lǐng)用數(shù)量”

列，并用F4固定；在“Criteriarange2”欄中輸入“辦公用品領(lǐng)用表”中的“物

品編碼”列，并用F4固定；在“Criteria2”欄中輸入“1月辦公用品

明細(xì)表”中的“物品編碼”列,得到結(jié)果。如下圖所示：

圖3.12設(shè)置SUMIF函數(shù)

所以各部門領(lǐng)用數(shù)量的公式=SUMIFS(辦公用品領(lǐng)用表!$H$3:$H$28,辦公用

品領(lǐng)用表!$C$3:$C$28,J$3,辦公用品領(lǐng)用表!$D$3:$D$28,$A5)，只需要再雙擊向

下復(fù)制即可。

b.“本期入庫(kù)的金額”、“各部門的領(lǐng)用數(shù)量”等填寫方法同上。

C.“期末數(shù)的數(shù)量”的公式=期初數(shù)＋本期入庫(kù)-各部門領(lǐng)用數(shù)量之和，“期

末數(shù)的單價(jià)”采取月末平均取數(shù)，公式=（月初庫(kù)存的實(shí)際成本+當(dāng)月各批進(jìn)貨的

實(shí)際成本）/（月初庫(kù)存數(shù)量＋當(dāng)月各批進(jìn)貨數(shù)最之和）。

4模型驗(yàn)證及數(shù)據(jù)分析

4.1模型驗(yàn)證錄

4.1.1辦公用品購(gòu)入表

（1）2022年1月16日購(gòu)電腦15臺(tái)，單價(jià)為4800/臺(tái)；打印機(jī)2臺(tái)，單價(jià)

為3500/臺(tái)；中性筆30盒，單價(jià)為18/盒。

9

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

（2）2022年1月18日購(gòu)進(jìn)紙杯10箱，單價(jià)為80/箱；抑菌洗手液20瓶，

單價(jià)為15/瓶；稿紙30本，單價(jià)5/本。

（3）2022年1月22日購(gòu)進(jìn)鉛筆10盒，單價(jià)為9/盒；復(fù)寫紙50本，單價(jià)

為7/本；白板筆10盒，單價(jià)32/盒。

（4）2022年1月25日購(gòu)進(jìn)消毒液5瓶，單價(jià)為20/瓶；A4打印紙70包，

單價(jià)為26/包；記事簿100本，單價(jià)2/本。

代入數(shù)據(jù)：

圖4.1辦公用品購(gòu)入表

4.1.2領(lǐng)用記錄表

（1）2020年1月16日業(yè)務(wù)部申領(lǐng)復(fù)寫紙5本、紙杯4箱、電腦8臺(tái)。

（2）2022年1月4日成本部申領(lǐng)訂書機(jī)5個(gè)、打印機(jī)1臺(tái)、記事簿20本。

（3）2022年1月10日采購(gòu)部申領(lǐng)復(fù)寫紙10本、記事簿10本、中性筆4

盒。

（4）2022年1月15日成本部申領(lǐng)白板筆2盒、鉛筆3盒。

（5）2022年1月20日財(cái)務(wù)部申領(lǐng)A4打印紙15包、復(fù)寫紙10本、稿紙30

本、記事簿10本、中性筆2盒、鉛筆2盒。

（6）2022年1月24日人事部申領(lǐng)中性筆3盒、打印機(jī)1臺(tái)、A4打印紙7

包、復(fù)寫紙10本、稿紙10本、紙杯2箱。

（7）2022年1月29日成本部申領(lǐng)計(jì)算器6個(gè)，保潔部申領(lǐng)抑菌洗手液5

瓶、消毒液5瓶。

代入數(shù)據(jù)：

10

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計(jì)

圖4.2辦公用品領(lǐng)用記錄表

4.1.4期初數(shù)據(jù)

上月結(jié)余可得本期期初商品數(shù)據(jù)庫(kù)存數(shù)量為：電腦庫(kù)存2臺(tái)共10000元，訂

書機(jī)庫(kù)存7個(gè)共105元，計(jì)算器9個(gè)共126元，A4復(fù)印紙2包共60元，復(fù)寫紙

3本共18元，稿紙12本共72元，記事簿7本共7元，中性筆9盒共180元，

鉛筆8盒共80元，白板筆