湖南商務職業技術學院畢業設計

目錄

1長沙菲雪格五金機電有限公司概況....................................1

1.1公司簡介...................................................1

1.2公司組織結構...............................................1

2長沙菲雪格五金機電有限公司辦公用品管理現狀及管理需求.............1

2.1公司辦公用品管理存在的問題.................................1

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制作辦公用品明細表.....................................9

4模型驗證及數據分析..............................................12

4.1模型驗證...................................................12

4.1.1購入記錄..............................................12

4.1.2領用記錄..............................................13

4.1.3期初數據..............................................14

4.2數據分析...................................................15

5總結.............................................................17

參考資料..........................................................18

湖南商務職業技術學院畢業設計

長沙菲雪格五金機電有限公司基于Excel進行辦公

用品管理的設計方案

1長沙菲雪格五金機電有限公司概況

1.1公司簡介

長沙菲雪格五金機電有限公司是一家從事五金零售,機械設備批發,五金產

品批發等業務的公司，成立于2021年04月13日，公司坐落在湖南省，企業的

經營范圍為:五金零售；五金機電產品的銷售、研發；機械設備、五金產品及電

子產品、五金建材、辦公用品的批發；家用電器及電子產品、五金產品、鋼制

床、柜、家具、五金制品、通用機械設備的零售；建材、辦公用品、電子產品、

電子產品及配件、通用機械設備的銷售。

1.2公司組織結構

圖1-1公司組織結構

該公司的組織結構是最上面的是總經理，總經理下面是副總經理，副總經

理既要對總經理負責又要對他的下級部門負責。公司根據管理專業化程度劃分

多個職能部，權責高度集中。

2長沙菲雪格五金機電有限公司辦公用品管理現狀及管理需求

2.1公司辦公用品管理存在的問題

該公司辦公用品管理存在多而且雜的問題。辦公用品的采購和領用比較零

散，沒有統一的管理方式，而且該公司還在采用傳統的手工建賬模式，用紙質

1

湖南商務職業技術學院畢業設計

來記錄辦公用品的采購、入庫、保管、領用，記賬等工作。這種傳統的記賬方

式要消耗大量的勞動力，且不方便查找相關的數據，做到人工的實時對賬比較

麻煩，安全性也得不到保障。以下是具體的一些問題：

(1)人工操作需要大量人力物力去記錄，不僅效率低，而且會產生大量的不

必要的人工成本，購買紙質本也會有一些管理費用出現。

(2)傳統的手工記賬可能會出現錯記、漏記、重復記錄等情況，不能夠真實

的反映出辦公用品的使用情況，不能查明真實情況就不能及時的查漏補缺，從

而影響公司的效益，不利于公司的發展。

(3)紙質記賬存在較大的安全性問題，資料容易遺失或泄漏，給企業帶來損

失。而且紙質保存的年限有限，不利于企業的長期發展。

2.2公司辦公用品的需求分析

(1)效率性，辦公用品如果管理不善也會影響企業生產經營的效率，因此在

效率方面我們也要格外重視。使用Excel能夠設計各種不同的表格和公式，表

格記錄的數據非常的清晰明了。員工不用再一個個的去記錄與核算，即使遇到

龐大的數據，員工只要輸入相對應的物品編碼、物品名稱、數量和單價等數據，

電腦就會通過設好的公式直接填列，這種方式極大的減少了工作量，提高了工

作效率。

(2)快速查詢，使用Excel記賬最大的表現是用表格記錄數據，一個工作簿

中可以有許多個數據表格。以表格的形式表現的數據簡單明了，方便企業管理

人員進行相關的數據統計。而且查找的時候只要用電腦直接查看即可，不用像

紙質賬簿那樣要同時查看好幾本賬簿一一對應。

(3)安全性，使用Excel來管理辦公用品能使其的管理更加合理化、規范化，

可以減少因人為原因而出現的辦公用品流失問題。而且Excel能夠量化辦公用

品的使用率，能夠起到一定的監督效果，有利于減少辦公用品的浪費情況。作

為一種科技智能系統它的數據保存將是無限接近永久的，不用再擔心保存問題。

3長沙菲雪格五金機電有限公司辦公用品Excel的模型

3.1設計模型框架

首先在Excel中建立一個工作簿，命名為“長沙菲雪格五金機電有限公司

基于Excel進行辦公用品管理的設計方案”。然后就開始建立各種相關的表格

了，第一個是做一個封面，上面寫著“長沙菲雪格五金機電有限公司管理系統”

2

湖南商務職業技術學院畢業設計

的標題，下面是四個小標題：“辦公用品清單”、“辦公用品購買記錄”、“辦

公用品領用記錄”和“辦公用品明細表”。這四個標題分別對應四個表，而且

四個標題都會插入鏈接只要直接點就可以直接到達相對應的表，非常的簡單方

便。

圖3-1封面

3.2制作表格

3.2.1制作辦公用品清單

(1)設計表格

根據這個公司平常的辦公用品使用情況來看，本文將以“電腦”、“多功

能一體機”、“U盤”、“檔案盒”、“拉鏈袋”、“黑色簽字筆”、“紅色

簽字筆”、“訂書器”、“訂書釘”、“長尾夾”、“筆筒”、“記事本”、

“A4打印紙”、“便利貼”等辦公用品為例。

設置一個物品分類表表頭為“物品分類”和“類別名稱”，如下圖所示：

圖3-2物品分類表表頭

3

湖南商務職業技術學院畢業設計

設置一個辦公用品清單表表頭為“物品編碼”、“物品名稱”、“規格”

和“單位”，如下圖所示：

圖3-3辦公用品清單表表頭

(2)引入數據

由于這個公司以前對辦公品并沒有進行較為規范的分類，都統一為辦公用

品，導致辦公用品的管理混亂復雜，因此我們要改變其現狀，將其分類，并設

置編碼。設置分類編碼為1-9，對應的類別名稱分別為：“辦公設備”、“數碼

電腦耗”材、“文件管理類”、“書寫工具”、“桌面辦公”、“紙品本冊”、

“財務用品”、“膠粘用品”和“辦公生活用品”，然后將辦公用品依次對應

分類。

將“電腦”的物品編碼按照其順序和規律設置為“1001”；“多功能一體

機”設置為“2001”；“檔案盒”設置為“3001”以此類推。后續加入到其他

辦公用品也可以設置此編碼，然后將公司辦公用品的規格列式在“辦公用品清

單”中，以方便后面表格取值。

3.2.2制作辦公用品采購表

(1)設計表格

為了企業方便對辦公用品的采購情況進行詳細的記錄和管理，設置一個“

辦公用品購入表”，其表頭為“購買日期”、“物品編碼”、“物品名稱”、“

規格”、“單位”、“單價”、“數量”、“總金額”。如下圖所示：

圖3-4辦公品購入表表頭

(2)引入數據

按照辦公用品購買的日期依次填入“辦公用品購入表”中，“物品名稱”

和“物品編碼”等在“辦公用品清單”中出現過的數據可以通過設置表格格式

4

湖南商務職業技術學院畢業設計

取得，而“單價”、“數量”和“總金”額則按照采購發票如實填列。

(3)公式設置

a、錄入物品編碼

選中辦公用品中“物品編碼”那一欄，點開【公式】選項卡,單擊【定義名

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

引用位置默認為“=辦公用品清單！$E:$E”。如下圖所示：

圖3-5定義名稱

b、設置“物品編碼”的數據驗證

打開“辦公用品購入表”，點擊【數據】選項卡，點擊上面的【數據驗證】

打開“數據驗證”的對話框，輸入驗證條件，設置允許框為“序列”，來源設

置為“=辦公用品清單!$E:$E”，并且選擇“忽略空值”和“提供下拉箭頭”復

選框。如下圖所示：

圖3-6設置“數據驗證”

5

湖南商務職業技術學院畢業設計

c、錄入物品名稱

錄入“物品名稱”將會用到VLOOKUP函數來設置公式，通過它來設置“辦

公用品購入表”中“物品編碼”、“規格”、“單位”和“單價”列的取值公

式。只需要輸入物品編碼，系統會自動填好相對應的物品名稱、規格、單位和

單價。VLOOKUP函數的這個功能不僅操作簡單，而且大大的提高了效率。

舉例說明“物品名稱”列的取值公式設置，首先點擊C3單元格，點擊【fx

】插入函數，選擇函數類別為全部函數，選擇VLOOKUP函數，雙擊打開【函數

參數】的對話框，然后就在“Lookupvalue”欄輸入B3單元格，并用F4固定(

表示絕對引用)；在“tablearray”欄填入“辦公用品清單!$E$3:$H24”；在

“colindexnum”欄輸入“辦公用品”中“物品名稱”所在的列數，即2；在

“rangelookup”欄輸入“FASLE”(精確查找)。如下圖所示：

圖3-7設置“VLOOKUP”函數

因此可得出“物品名稱”的取值公式是=VLOOKUP($B3,辦公用品清單

!$E$3:$H24,2,FALSE),及對照“物品編碼”在辦公用品第二列中獲取相應的“

物品名稱”，然后雙擊向下復制。“規格”、“單位”和“單價”列的取值公

式同上；“數量”列可以照實際的購入數量直接填入；“總金額”列的取值公

式為=“F3*G3”，即總金額=單價*數量，然后雙擊向下復制。

3.2.3制作辦公用品領用表

(1)設計表格

為了企業能夠簡潔明了的了解到辦公用品的具體購入情況，將利用Excel

建立一個“辦公用品領用表”。設置“辦公用品領用表”的表頭為“序號”、“

6

湖南商務職業技術學院畢業設計

領用日期”、“領用部門”、“物品編號”、“物品名稱”、“規格單位”和“

領用數量”。如下圖所示：

圖3-8“辦公用品領用表”表頭

(2)引入數據

按照辦公用品領用的日期依次填入“辦公用品領用表”中，“領用部門”、

“物品編號”、“規格”和“單價”可以通過設置表格格式取得，“物品名稱”

根據編碼填列，“領用數量”根據實際領用的情況填列。

(3)公式設置

a、填寫領用部門

選擇“領用部門”那一列，點擊【數據】，打開【數據驗證】，驗證條件“

允許”選擇“序列”，“來源”直接填部門名稱，即“服務部”、“產品部”、

“財務部”、“人事部”、“店面運營”、“區域經理”和“連鎖發展”。并

且同時選擇“忽略空值”和“提供下拉箭頭”。如下圖所示：

圖3-9“領用部門”數據驗證

b錄入物品編碼

同“領用部門”一樣，選擇“物品編碼”列點擊【數據】打開【數據驗證】

，“允許”選擇“序列”，“來源”點擊“辦公用品清單”，選擇“E2：E24”

7

湖南商務職業技術學院畢業設計

并用F4固定，即“=辦公用品清單!$E$2:$E$24”。如下圖所示：

圖3-10“物品編碼”的數據驗證

C錄入規格

錄入“規格”采用VLOOKUP函數，首先點擊F3單元格，點擊【fx】插入函

數，選擇函數類別為全部函數，選擇VLOOKUP函數，雙擊打開【函數參數】的

對話框，然后就在“Lookupvalue”欄輸入D3單元格，并用F4固定；在“區域”

欄填入“辦公用品清單!$E$3:$H$24”；然后輸入“辦公用品”中“規格”所在

的列數，即3；最后填入“FASLE”進行精確匹配。如下圖所示：

圖3-11錄入“規格”

8

湖南商務職業技術學院畢業設計

由此得出：“規格”的取值公式是=VLOOKUP($D3,辦公用品清單

!$E$3:$H24,3,FALSE),及對照“物品名稱”在辦公用品第二列中獲取相應的“

規格”，然后雙擊向下復制。“單位”的公式同上，點擊G3單元格，選擇【fx】

，雙擊打開【函數參數】的對話框，只需將在“colindexnum”欄輸入“辦公

用品”中“規格”所在的列數，改成“單位”所在列數，即4。其他的同“規格”

一樣。

d“物品名稱”根據填好的編碼填列，“領用數量”根據實際的數據填列。

3.2.4制作辦公用品明細表

(1)設計表格

為了能夠清楚的了解到公司期初的辦公用品情況，部門領用情況和期末的

辦公用品情況，對這些情況的一個統籌需要制作一個辦公用品明細表。設置“

辦公用品明細表”，表頭為“物品編碼”、“物品名稱”、“規格”、“單位”

、“期初數”、“本期入庫”、“服務部”、“人事部”、“產品部”、“財

務部”、“店面運營”、“連鎖發展”、“區域經理”、“期末數”。如下圖

所示：

圖3-12“辦公用品明細表”表頭

(2)引入數據

根據“辦公用品領用表”填入“物品編碼”采用“數據驗證”，“物品名

稱”、“規格”、“單位”、“本期入庫的數量和金額”、“部門領用數量”、

“期末的數量、金額和單價”采用相關的公式設置，“期初”的數量、單價和

金額根據實際留存的數量填列。

(3)公式設置

a錄入物品名稱

“物品名稱”列的取值公式設置，首先點擊B5單元格，點擊【fx】插入函

數，選擇函數類別為全部函數，選擇VLOOKUP函數，雙擊打開【函數參數】的

對話框，要查找的值為A5單元格，并用F4固定；在“區域”欄填入“辦公用

品清單!$E$3:$H24”；然后輸入“辦公用品”中“物品名稱”所在的列數，即

9

湖南商務職業技術學院畢業設計

2；最后進行精確匹配。如下圖所示：

圖3-13錄入“物品名稱”

b錄入“規格”和“單位”同上；

c錄“本期入庫”的數量和金額

采用SUMIF函數，SUMIF函數是Excel常用函數。使用SUMIF函數可以對報表

范圍中符合指定條件的值求和。

第一個參數:“Range”為條件區域，就是你所填入的數據的具體所在區域，如：

某某表的某行某列單元格

第二個參數:“Criteria”是求和條件，滿足這個求和所要求的條件。

第三個參數:“Sumrange”為實際求和的區域，就是需要求和的這個單元格或

者求和的這個區域具體在哪。

當省略第三個參數時，則條件區域就是實際求和區域。

點擊H5單元格，點擊【公式】插入函數【fx】，選擇函數類別為全部函數

，選擇SUMIF函數，雙擊打開【函數參數】的對話框，然后就在“Range”欄輸

入“辦公用品購入表!$B$3:$B$22”；在“Criteria”欄輸入“A5:A25”并用F4

固定(表示絕對引用)；在“Sumrange”欄輸入“辦公用品購入

表!$G$3:$G$22”。如下圖所示：

10

湖南商務職業技術學院畢業設計

圖3-14“本期入庫”的數量和金額

所以“本期入庫數量”的公式為“=SUMIF(辦公用品購入表

!$B$3:$B$22,$A$5:$A$25,辦公用品購入表!$G$3:$G$22)”，“本期入庫金額”

同上。

d錄入“部門領用數量”

采用SUMIFS函數，該函數可快速對多條件單元格求和，SUMIFS函數功能十分

強大，可以通過不同范圍的條件求規定范圍的和。

“sumrange”：是指進行求和的單元格或單元格區域。

“criteriarange”：是指與求和單元格格式處于同一行的條件區域。

“criterial”：是指來自條件區的，一個具體的參與判斷的值。

點擊J5單元格，點擊【公式】插入【fx】，選擇函數類別為全部函數，選

擇SUMIFS函數，雙擊打開【函數參數】的對話框，然后就在“sumrange”欄輸

入“辦公用品領用表!$H$3:$H$21”，即點擊辦公用品領用表，選擇“H3:H21”

并用F4固定(表示絕對引用)；在“criteria_range1”欄輸入“辦公用品領用

表!$C$3:$C$21”，即點擊辦公用品領用表，選擇“C3:C21”并用F4固定；在

“criterial”欄輸入“J3”，用F4固定，即是對財務部的領用電腦的數量的

求和；在“criteriarange2”欄輸入“辦公用品領用表!$D$3:$D$21”，即點擊

辦公用品領用表，選擇“D3:D21”并用F4固定；在“criteria2”欄輸入

“A5”，其為對應的是“物品編碼”。如下圖所示：

11

湖南商務職業技術學院畢業設計

圖3-15錄入“部門領用數量”

e錄入“期末數量、單價和金額”

設置“期末數量”公式為“=E5+H5-J5-K5-L5-M5-N5-O5”，即為期末數量=(期

初數量+本期入庫數量-銷售部領用數量-人事部領用數量-財務部領用數量-成

本部領用數量-采購部領用數量-保潔部領用數量)；“期末單價”公式為

“=(G5+I5)/(E5+H5)”，即為期末單價=(期初金額+本期入庫金額)/(期初數量+

本期入庫數量)；“期末金額”公式為“=Q5*P5”，即為期末金額=(期末數量*

期末單價)。

4模型驗證及數據分析

4.1模型驗證

將公司10月份的購入數據和領用數據代入到Excel模型中進行數據驗證，

可以看到以日期為記錄順序，每日購入的物品和領用的物品，以及物品的單位、

單價、數量和總金額都能夠一一對應。

4.1.1購入記錄

2022年10月3日購買了電腦2臺，單價5599元/臺；多功能一體機一臺，

單價1399元/臺。

10月5日購買了U盤5個，單價39.9元/個。

10月7日購買了黑色簽字筆20支，單價0.63元/支；紅色簽字筆20支，

單價0.63元/支。

10月11日購買了5.5cm的檔案盒5個，單價8.10元/個；7.5cm的檔案盒

12

湖南商務職業技術學院畢業設計

5個，單價8.90元/個；長尾夾1盒，單價5.30元/盒。

10月12日購買了固體膠2個，單價1.80元/個。

10月13日購買了透明膠帶2箱，單價8.79元/箱。

10月14日購買了訂書器4個，單價9.80元/個。訂書釘8盒，單價0.91

元/盒。

10月17日購買了記事本5本，單價13.5元/本。費用報銷單2本，單價2

元/本。

10月18日購買了A4打印紙6箱，單價93元/箱。拉鏈袋8個，單價8.8

元/個。

10月21日印泥2個，單價4.6元/個。

10月24日購買了垃圾袋3包，單價11.8元/bao。

10月25日購買了便利貼5個，單價1.9元/個。

10月27日購買了抽紙1箱，單價69.9元/箱。

代入數據：

圖4-1購入記錄

4.1.2領用記錄

2022年10月7日店面運營領用U盤2個，服務部領用檔案盒1個。

10月8日人事部領用檔案盒1個，產品部領用紅色簽字筆5支，區域經理

領用黑色簽字筆10支。

10月10日區域經理領用紅色簽字筆5支，連鎖發展領用戴爾電腦1臺。

10月11日服務部領用多功能一體機1臺。

10月13日財務部領用U盤3個，店面運營領用A4打印紙2箱。

13

湖南商務職業技術學院畢業設計

10月17日連鎖發展部領用了訂書器2個，訂書釘2盒。

10月18日產品部領用了透明膠帶(大)1箱。

10月19日財務部領用了費用報銷單1本。

10月21日領用了固體膠棒1個，人事部領用了A4打印紙2箱。

10月24日服務部領用了A4打印紙2箱。

10月26日連鎖發展部領用了垃圾袋2包。

10月31日財務部領用了長尾夾1盒。

代入數據：

圖4-2領用記錄

4.1.3期初數據

由上月結余可得本期辦公用品的期初庫存如下：電腦的庫存為2臺共11198

元，U盤庫存為5個共199.5元，5.5cm檔案盒庫存為2個共16.2元，7.5cm檔

案盒庫存為1個共8.9元，拉鏈袋庫存為2個共17.6元，黑色簽字筆庫存為3

支共1.89元，紅色簽字筆庫存為2支共1.26元，訂書器庫存為2個共19.6元

，訂書釘庫存為1盒共0.91元，長尾夾庫存為1盒共5.3元，回形針1盒共2.7

，筆筒庫存為2個共12.8元，記事本庫存為3本共40.5元，A4打印紙庫存為2

箱共93元，便利貼庫存為2個共3.8元，印泥庫存為2個共9.2元，費用報銷

單庫存為1本共2元，透明膠帶(大)庫存為1箱共8.79元，固體膠棒庫存為2

個共3.6元，抽紙庫存為1箱共69.9元，垃圾桶庫存為2個，垃圾袋庫存為0

包共0元。

代入數據：物品名稱、規格和單位都是通過VLOOKUP函數，引用了辦公用品清

14

湖南商務職業技術學院畢業設計

單的數據得到；期初數量根據實際數據填入，期初單價引入辦公購入表的數據，

期初金額由期初數量乘以期初單價；本期數量和金額通過應用SUNIF函數，引

入辦公用品購入表數據和10月辦公用品明細表數據填入；每個部門的領用數據

通過SUNIFS函數，引用辦公用品領用表的數據依次填入；最后的期末數量、單

價和金額通過簡單的加減得到。

圖4-3期初數據

4.2數據分析

圖4-4各部門辦公用品領用情況

由“辦公用品明細表”中10月份的數據得出的餅狀圖，統計了各部門辦公

用品領用情況。其中連鎖發展的辦公用品成本最多為73%，其次是服務部為21%

，最少的是區域經理。由此看出，這家公司在本年可能有計劃發展連鎖店，連

鎖發展負責選址、裝修、開業、階段性推廣、人工的工牌、名片等事物。而本

15

湖南商務職業技術學院畢業設計

月處在第四季了，連鎖店已步入開業階段，員工逐漸步入日常工作，日常產生

的辦公用品隨之增多。利用excel處理數據使本月的辦公用品情況一目了然，

達到了辦公用品管理規范化的目的。