1、、類數(shù)據(jù)庫管理與數(shù)據(jù)透視圖表目的充分利用Excel數(shù)據(jù)共享、數(shù)據(jù)的一致性、統(tǒng)計匯總、列和行存儲結構、數(shù)據(jù)處理函數(shù)、透視表等方面的強大功能解決現(xiàn)實中復雜數(shù)據(jù)與信息管理問題。、本項目主要操作技能 相對引用應用 利用透視表管理和分析數(shù)據(jù) 用計算機的思維方式去解決現(xiàn)實中的問題 項目8：類數(shù)據(jù)庫管理與數(shù)據(jù)透視圖表 進銷存管理進銷存管理的典型報表進銷存管理系統(tǒng)制作步驟、建立工作簿、定義工作表名，生成管理系統(tǒng)基本框架。、構建原始數(shù)據(jù)資源，并利用有效性和相關函數(shù)構建數(shù)據(jù)鏈動與共享。 。、利用條件格式實現(xiàn)庫存和毛利報警顯示。 、通過透視表制作各匯總表和總報表。 5、根據(jù)需要，建立自己系統(tǒng)工具欄。1、建立工作簿、

2、定義工作表名 新建工作簿： 在工作簿存放文件夾空白處右擊，彈出快捷菜單 “新建”“Microsoft Excel 工作表” 重命名為“進銷存.xlsx”。 定義工作表 打開“進銷存.xlsx” 通過“插入”“工作表”，添加8張工作表 分別把8張工作表命名為“代碼表”、“入庫明細表”、“出庫明細表”、“入庫匯總表”、“出庫匯總表”、“成本毛利表”、“進銷存報表”、“庫存盤點表” 。 構建原始基礎數(shù)據(jù)代碼表，采用直接輸入數(shù)據(jù)的方式 ，可加單元格背景 ，如右圖。2、構建原始數(shù)據(jù)資源 （1） 建立“入庫明細表”： 輸入對應的標題輸入對應的標題 “入庫單號碼入庫單號碼”、“入庫日期入庫日期”、“物品代碼

3、物品代碼”、“數(shù)數(shù)量量” 列基本數(shù)據(jù)手工輸入。列基本數(shù)據(jù)手工輸入。 “代碼表代碼表”已有列項目通過已有列項目通過 VLOOKUP函數(shù)來調(diào)用即可，函數(shù)來調(diào)用即可，詳細如下：詳細如下：2、構建原始數(shù)據(jù)資源 （2）“供貨商”（D2）公式：“=VLOOKUP（$C2,代碼表!$A:$F,COLUMN（B1）,0） “物品名稱”（E2）公式：“=VLOOKUP（$C2,代碼表!$A:$F,COLUMN（C1）,0）”“規(guī)格”（F2）公式：“=VLOOKUP（$C2,代碼表!$A:$F,COLUMN（D1）,0）”“單位”（G2）公式：“=VLOOKUP（$C2,代碼表!$A:$F,COLUMN（E1）,

4、0）”“單價”（H2）公式：“=VLOOKUP（$C2,代碼表!$A:$F,COLUMN（F1）,0）” 2、構建原始數(shù)據(jù)資源 （3）2、構建原始數(shù)據(jù)資源 （4）“金額金額”，采用公式，采用公式“單價單價”“數(shù)量數(shù)量”填入填入 ，即：，即：J2為為 “=+H2*I2” 庫別、入庫類別、入庫制單為固定數(shù)據(jù)，通過數(shù)據(jù)有庫別、入庫類別、入庫制單為固定數(shù)據(jù)，通過數(shù)據(jù)有效性序列實現(xiàn)下拉選擇輸入效性序列實現(xiàn)下拉選擇輸入 。 如如“庫別庫別”設置數(shù)據(jù)有效性為設置數(shù)據(jù)有效性為“序列序列”，來源內(nèi)容為，來源內(nèi)容為“一庫，一庫，二庫，三庫，四庫二庫，三庫，四庫”，如右圖。，如右圖。同樣同樣“入庫類別入庫類別”、“

5、入庫制入庫制單單”列也設置數(shù)據(jù)有效性為列也設置數(shù)據(jù)有效性為“序列序列”，來源分別為，來源分別為“入庫，入庫，調(diào)入，調(diào)出，報損，盤盈調(diào)入，調(diào)出，報損，盤盈”、“肖雨，劉嫄肖雨，劉嫄”。“月份月份” 通過函數(shù)取出通過函數(shù)取出B列入庫日期中的月份，公式列入庫日期中的月份，公式為為“=MONTH（B3）” 建立出庫明細表，方法同入庫明細表。2、構建原始數(shù)據(jù)資源 （5）其中：供貨商、物品名稱、規(guī)格、單位、成本單價列通過其中：供貨商、物品名稱、規(guī)格、單位、成本單價列通過VLOOKUPVLOOKUP調(diào)用代碼表中調(diào)用代碼表中的數(shù)據(jù)，分別的數(shù)據(jù)，分別 為：為：“=VLOOKUP=VLOOKUP（$D2,$D2,

6、代碼表代碼表!$A:$F,COLUMN!$A:$F,COLUMN（B1B1）,0,0）”， “=VLOOKUP=VLOOKUP（$D2,$D2,代碼表代碼表!$A:$F,COLUMN!$A:$F,COLUMN（C1C1）,0,0）”， “ “=VLOOKUP=VLOOKUP（$D2,$D2,代代碼表碼表!$A:$F,COLUMN!$A:$F,COLUMN（D1D1）,0,0）”， “ “=VLOOKUP=VLOOKUP（$D2,$D2,代碼表代碼表!$A:$F,COLUMN!$A:$F,COLUMN（E1E1）,0,0）”， “ “=VLOOKUP=VLOOKUP（$D2,$D2,代碼表代碼表

7、!$A:$F,COLUMN!$A:$F,COLUMN（F1F1）,0,0）” ” 。銷售金額、成本金額、毛利采用公式自動計算：銷售金額列輸入公式銷售金額、成本金額、毛利采用公式自動計算：銷售金額列輸入公式“=+K2=+K2* *J2”J2”，成本金額列輸入公式，成本金額列輸入公式“=+I2=+I2* *J2”J2”，毛利列輸入公式，毛利列輸入公式“=L2-M2”=L2-M2” 在入庫明細表中，單擊I列的列標，單擊“開始”選項卡，在“樣式”功能區(qū)里下拉“條件格式”，系統(tǒng)出現(xiàn)命令 ，單擊 ，系統(tǒng)彈出“新建格式規(guī)則”對話框，根據(jù)庫存管理的要求設置單元格值小于0，單擊“格式（F）”按鈕，彈出“單元格格

8、式”對話框，在“顏色”位置選擇“紅色”，在“字形”位置選擇“加粗”，單擊“確定”按鈕回到“新建格式規(guī)則”對話框，完成對庫存不足的報警的條件設定。同理設置上限。3、實現(xiàn)庫存和毛利報警顯示（1）3、實現(xiàn)庫存和毛利報警顯示（2）（1）自動篩選3、實現(xiàn)庫存和毛利報警顯示（3） （2）高級篩選 高級篩選的關鍵在于創(chuàng)建條件區(qū)域。數(shù)量小于等于“0”或大于“20”是一種“或”運算，其條件區(qū)域設置如圖所示的“或”運算條件區(qū)域設置。 建立入庫匯總表 在“入庫匯總表”中，選定單元格范圍A1:N14，單擊“插入”選項卡，下拉“表格”功能區(qū)中的“數(shù)據(jù)透視表”，在下拉菜單中選擇“數(shù)據(jù)透視表”，系統(tǒng)出現(xiàn)如圖 所示的“創(chuàng)建數(shù)

9、據(jù)透視表”對話框。4、通過透視表制作匯總表（1）數(shù)據(jù)透視表字段區(qū)域設置界面4、通過透視表制作匯總表（2）4、通過透視表制作匯總表（3）在“數(shù)據(jù)透視表字段列表”小窗口里，將“庫別”和“月份”字段拖放至“報表篩選”下；將“物品代碼”、“物品名稱”、“規(guī)格”字段拖放至“行標簽”下；將“數(shù)量”、“金額”字段拖放至“數(shù)值”下4、通過透視表制作匯總表（4）在“行標簽”區(qū)域里，下拉“物品代碼”，系統(tǒng)彈出快捷菜單，選擇“字段設置”，系統(tǒng)出現(xiàn)如左下圖 所示的對話框。單擊單選按鈕為“無”，按“確定”。系統(tǒng)出現(xiàn)如右下圖 所示。4、通過透視表制作匯總表（5）由于上圖 的數(shù)據(jù)透視表每一物品呈三行顯示，在觀看時不夠直觀，

10、Excel2010提供了普通表格的顯示方式。右擊透視表中的任一位置，系統(tǒng)彈出快捷菜單，選擇“數(shù)據(jù)透視表選項”，系統(tǒng)出現(xiàn)如圖 所示。 美化入庫匯總表：把數(shù)據(jù)項匯總標題改名，把“數(shù)據(jù)”所在行隱藏，選中“物品代碼”行和總計行設置為白色、加粗字體、黑色單元格背景，實現(xiàn)如圖效。果 。4、通過透視表制作匯總表（6） 構建出庫匯總表 。 利用透視表的功能建立出庫匯總表，方法完全同入庫匯總表，最后效果如圖。 4、通過透視表制作匯總表（7） 構建成本毛利表 。 利用透視表的功能建立成本毛利表 ，方法完全同入庫匯總表，最后效果如圖。 4、通過透視表制作匯總表（8） 構建進銷存報表 。 分別把出入庫匯總表復制到進銷

11、存報表 ，效果如圖。4、通過透視表制作匯總表（9） 輸入報表各項數(shù)據(jù)標題：期初數(shù)量、期初庫存金額、本月入庫、本月入庫金額、本月出庫數(shù)量、本月成本金額、庫存數(shù)量、庫存金額。輸入對應的計算公式：為“=L5+J5-H5”；為“=M5+K5-I5”；為“=IF（ISERROR（VLOOKUP（A5,入庫匯總表!$A$5:$E$20,4,0）,0,VLOOKUP（A5,入庫匯總表!$A$5:$E$20,4,0）”；為“=IF（ISERROR（VLOOKUP（A5,入庫匯總表!$A$5:$E$20,5,0）,0,VLOOKUP（A5,入庫匯總表!$A$5:$E$20,5,0）”；為“=IF（ISERROR

12、（VLOOKUP（A5,出庫匯總表!$A$5:$E$20,4,0）, 0,VLOOKUP（A5,出庫匯總表!$A$5:$E$20,4,0）”；為“=IF（ISERROR（VLOOKUP（A5,出庫匯總表!$A$5:$E$20,5,0）,0,VLOOKUP（A5,出庫匯總表!$A$5: $E$20,5,0）”；為“=D5-IF（ISERROR（VLOOKUP （A5,$A$22: $E$32, 4,0）, 0,VLOOKUP （A5,$A$22:$E$32,4,0）”；為“=E5-IF（ISERROR（VLOOKUP（A5,$A$22:$E$32,4,0）,0,VLOOKUP（A5,$A$22:

13、$E$32,4,0）”。4、通過透視表制作匯總表（10） 為了美觀，可以給相應的數(shù)據(jù)加上背景或底紋，最后效果如圖 。4、通過透視表制作匯總表（11） 選擇建立庫存盤點表。 如果需要，還可以添加庫存盤點表，同樣借助出入庫匯總表，按建立進銷存報表的步驟，先把出入庫復制，再添加盤點項目即可，效果如圖。 4、通過透視表制作匯總表（12）總結（1） Excel 強大的處理數(shù)據(jù)庫的命令和函數(shù)，如數(shù)據(jù)有效性序列應用；條件格式應用；函數(shù)vlookup、month、column、iserror等應用及各類自定義計算，使其具備了能組織和管理大量數(shù)據(jù)的能力。 Excel的數(shù)據(jù)透視表和數(shù)據(jù)透視圖操作使其具備良好數(shù)據(jù)自動分析匯總功能。 Excel的可以自定義菜單操作使其具備良好的系統(tǒng)結構框架功能。總結（2） 利用EXCEL制作數(shù)據(jù)管理系統(tǒng)操作步驟通是： 1）建立