信息化办公项目类数据库管理与数据透视图表进销存管理

1、、类数据库管理与数据透视图表目的充分利用Excel数据共享、数据的一致性、统计汇总、列和行存储结构、数据处理函数、透视表等方面的强大功能解决现实中复杂数据与信息管理问题。、本项目主要操作技能 相对引用应用 利用透视表管理和分析数据 用计算机的思维方式去解决现实中的问题 项目8：类数据库管理与数据透视图表 进销存管理进销存管理的典型报表进销存管理系统制作步骤、建立工作簿、定义工作表名，生成管理系统基本框架。、构建原始数据资源，并利用有效性和相关函数构建数据链动与共享。 。、利用条件格式实现库存和毛利报警显示。 、通过透视表制作各汇总表和总报表。 5、根据需要，建立自己系统工具栏。1、建立工作簿、

2、定义工作表名 新建工作簿： 在工作簿存放文件夹空白处右击，弹出快捷菜单 “新建”“Microsoft Excel 工作表” 重命名为“进销存.xlsx”。 定义工作表 打开“进销存.xlsx” 通过“插入”“工作表”，添加8张工作表 分别把8张工作表命名为“代码表”、“入库明细表”、“出库明细表”、“入库汇总表”、“出库汇总表”、“成本毛利表”、“进销存报表”、“库存盘点表” 。 构建原始基础数据代码表，采用直接输入数据的方式 ，可加单元格背景 ，如右图。2、构建原始数据资源 （1） 建立“入库明细表”： 输入对应的标题输入对应的标题 “入库单号码入库单号码”、“入库日期入库日期”、“物品代码

3、物品代码”、“数数量量” 列基本数据手工输入。列基本数据手工输入。 “代码表代码表”已有列项目通过已有列项目通过 VLOOKUP函数来调用即可，函数来调用即可，详细如下：详细如下：2、构建原始数据资源 （2）“供货商”（D2）公式：“=VLOOKUP（$C2,代码表!$A:$F,COLUMN（B1）,0） “物品名称”（E2）公式：“=VLOOKUP（$C2,代码表!$A:$F,COLUMN（C1）,0）”“规格”（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、构建原始数据资源 （3）2、构建原始数据资源 （4）“金额金额”，采用公式，采用公式“单价单价”“数量数量”填入填入 ，即：，即：J2为为 “=+H2*I2” 库别、入库类别、入库制单为固定数据，通过数据有库别、入库类别、入库制单为固定数据，通过数据有效性序列实现下拉选择输入效性序列实现下拉选择输入 。 如如“库别库别”设置数据有效性为设置数据有效性为“序列序列”，来源内容为，来源内容为“一库，一库，二库，三库，四库二库，三库，四库”，如右图。，如右图。同样同样“入库类别入库类别”、“

5、入库制入库制单单”列也设置数据有效性为列也设置数据有效性为“序列序列”，来源分别为，来源分别为“入库，入库，调入，调出，报损，盘盈调入，调出，报损，盘盈”、“肖雨，刘嫄肖雨，刘嫄”。“月份月份” 通过函数取出通过函数取出B列入库日期中的月份，公式列入库日期中的月份，公式为为“=MONTH（B3）” 建立出库明细表，方法同入库明细表。2、构建原始数据资源 （5）其中：供货商、物品名称、规格、单位、成本单价列通过其中：供货商、物品名称、规格、单位、成本单价列通过VLOOKUPVLOOKUP调用代码表中调用代码表中的数据，分别的数据，分别 为：为：“=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列的列标，单击“开始”选项卡，在“样式”功能区里下拉“条件格式”，系统出现命令 ，单击 ，系统弹出“新建格式规则”对话框，根据库存管理的要求设置单元格值小于0，单击“格式（F）”按钮，弹出“单元格格

8、式”对话框，在“颜色”位置选择“红色”，在“字形”位置选择“加粗”，单击“确定”按钮回到“新建格式规则”对话框，完成对库存不足的报警的条件设定。同理设置上限。3、实现库存和毛利报警显示（1）3、实现库存和毛利报警显示（2）（1）自动筛选3、实现库存和毛利报警显示（3） （2）高级筛选 高级筛选的关键在于创建条件区域。数量小于等于“0”或大于“20”是一种“或”运算，其条件区域设置如图所示的“或”运算条件区域设置。 建立入库汇总表 在“入库汇总表”中，选定单元格范围A1:N14，单击“插入”选项卡，下拉“表格”功能区中的“数据透视表”，在下拉菜单中选择“数据透视表”，系统出现如图 所示的“创建数

9、据透视表”对话框。4、通过透视表制作汇总表（1）数据透视表字段区域设置界面4、通过透视表制作汇总表（2）4、通过透视表制作汇总表（3）在“数据透视表字段列表”小窗口里，将“库别”和“月份”字段拖放至“报表筛选”下；将“物品代码”、“物品名称”、“规格”字段拖放至“行标签”下；将“数量”、“金额”字段拖放至“数值”下4、通过透视表制作汇总表（4）在“行标签”区域里，下拉“物品代码”，系统弹出快捷菜单，选择“字段设置”，系统出现如左下图 所示的对话框。单击单选按钮为“无”，按“确定”。系统出现如右下图 所示。4、通过透视表制作汇总表（5）由于上图 的数据透视表每一物品呈三行显示，在观看时不够直观，

10、Excel2010提供了普通表格的显示方式。右击透视表中的任一位置，系统弹出快捷菜单，选择“数据透视表选项”，系统出现如图 所示。 美化入库汇总表：把数据项汇总标题改名，把“数据”所在行隐藏，选中“物品代码”行和总计行设置为白色、加粗字体、黑色单元格背景，实现如图效。果 。4、通过透视表制作汇总表（6） 构建出库汇总表 。 利用透视表的功能建立出库汇总表，方法完全同入库汇总表，最后效果如图。 4、通过透视表制作汇总表（7） 构建成本毛利表 。 利用透视表的功能建立成本毛利表 ，方法完全同入库汇总表，最后效果如图。 4、通过透视表制作汇总表（8） 构建进销存报表 。 分别把出入库汇总表复制到进销

11、存报表 ，效果如图。4、通过透视表制作汇总表（9） 输入报表各项数据标题：期初数量、期初库存金额、本月入库、本月入库金额、本月出库数量、本月成本金额、库存数量、库存金额。输入对应的计算公式：为“=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） 为了美观，可以给相应的数据加上背景或底纹，最后效果如图 。4、通过透视表制作汇总表（11） 选择建立库存盘点表。 如果需要，还可以添加库存盘点表，同样借助出入库汇总表，按建立进销存报表的步骤，先把出入库复制，再添加盘点项目即可，效果如图。 4、通过透视表制作汇总表（12）总结（1） Excel 强大的处理数据库的命令和函数，如数据有效性序列应用；条件格式应用；函数vlookup、month、column、iserror等应用及各类自定义计算，使其具备了能组织和管理大量数据的能力。 Excel的数据透视表和数据透视图操作使其具备良好数据自动分析汇总功能。 Excel的可以自定义菜单操作使其具备良好的系统结构框架功能。总结（2） 利用EXCEL制作数据管理系统操作步骤通是： 1）建立