《信息化办公软件高级应用（第二版） 教学课件 ppt 作者 李永平09项目八 信息化办公项目8》由会员分享，可在线阅读，更多相关《信息化办公软件高级应用（第二版） 教学课件 ppt 作者 李永平09项目八 信息化办公项目8（27页珍藏版）》请在金锄头文库上搜索。
1、、类数据库管理与数据透视图表目的 充分利用Excel数据共享、数据的一致性、统计汇总、列和行存储结构、数据处理函数、透视表等方面的强大功能解决现实中复杂数据与信息管理问题。 、本项目主要操作技能 相对引用应用 利用透视表管理和分析数据 用计算机的思维方式去解决现实中的问题,项目8：类数据库管理与数据透视图表 进销存管理,进销存管理的典型报表,进销存管理系统制作步骤,、建立工作簿、定义工作表名，生成管理系统基本框架。 、构建原始数据资源，并利用有效性和相关函数构建数据链动与共享。 。 、利用条件格式实现库存和毛利报警显示。 、通过透视表制作各汇总表和总报表。 5、根据需要，建立自己系统工具栏。,
2、1、建立工作簿、定义工作表名,新建工作簿： 在工作簿存放文件夹空白处右击，弹出快捷菜单 “新建”“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）,0）” “单价”（H2）公式： “=VLOOKUP（$C2,代码表!$A:$
4、F,COLUMN（F1）,0）”,2、构建原始数据资源 （3）,2、构建原始数据资源 （4）,“金额”，采用公式“单价”“数量”填入 ，即：J2为 “=+H2*I2” 库别、入库类别、入库制单为固定数据，通过数据有效性序列实现下拉选择输入 。,如“库别”设置数据有效性为“序列”，来源内容为“一库，二库，三库，四库”，如右图。同样“入库类别”、“入库制单”列也设置数据有效性为“序列”，来源分别为“入库，调入，调出，报损，盘盈”、“肖雨，刘嫄”。,“月份” 通过函数取出B列入库日期中的月份，公式为“=MONTH（B3）”,建立出库明细表，方法同入库明细表。,2、构建原始数据资源 （5）,其中：供货
5、商、物品名称、规格、单位、成本单价列通过VLOOKUP调用代码表中的数据，分别 为：“=VLOOKUP（$D2,代码表!$A:$F,COLUMN（B1）,0）”， “=VLOOKUP（$D2,代码表!$A:$F,COLUMN（C1）,0）”， “=VLOOKUP（$D2,代码表!$A:$F,COLUMN（D1）,0）”， “=VLOOKUP（$D2,代码表!$A:$F,COLUMN（E1）,0）”， “=VLOOKUP（$D2,代码表!$A:$F,COLUMN（F1）,0）” 。 销售金额、成本金额、毛利采用公式自动计算：销售金额列输入公式“=+K2*J2”，成本金额列输入公式“=+I2*J2
6、”，毛利列输入公式“=L2-M2”,在入库明细表中，单击I列的列标，单击“开始”选项卡，在“样式”功能区里下拉“条件格式”，系统出现命令 ，单击 ，系统弹出“新建格式规则”对话框，根据库存管理的要求设置单元格值小于0，单击“格式（F）”按钮，弹出“单元格格式”对话框，在“颜色”位置选择“红色”，在“字形”位置选择“加粗”，单击“确定”按钮回到“新建格式规则”对话框，完成对库存不足的报警的条件设定。同理设置上限。,3、实现库存和毛利报警显示（1）,3、实现库存和毛利报警显示（2）,（1）自动筛选,3、实现库存和毛利报警显示（3）,（2）高级筛选 高级筛选的关键在于创建条件区域。数量小于等于“0”
7、或大于“20”是一种“或”运算，其条件区域设置如图所示的“或”运算条件区域设置。,建立入库汇总表 在“入库汇总表”中，选定单元格范围A1:N14，单击“插入”选项卡，下拉“表格”功能区中的“数据透视表”，在下拉菜单中选择“数据透视表”，系统出现如图 所示的“创建数据透视表”对话框。,4、通过透视表制作汇总表（1）,数据透视表字段区域设置界面,4、通过透视表制作汇总表（2）,4、通过透视表制作汇总表（3）,在“数据透视表字段列表”小窗口里，将“库别”和“月份”字段拖放至“报表筛选”下；将“物品代码”、“物品名称”、“规格”字段拖放至“行标签”下；将“数量”、“金额”字段拖放至“数值”下,4、通过
8、透视表制作汇总表（4）,在“行标签”区域里，下拉“物品代码”，系统弹出快捷菜单，选择“字段设置”，系统出现如左下图 所示的对话框。单击单选按钮为“无”，按“确定”。系统出现如右下图 所示。,4、通过透视表制作汇总表（5）,由于上图 的数据透视表每一物品呈三行显示，在观看时不够直观，Excel2010提供了普通表格的显示方式。右击透视表中的任一位置，系统弹出快捷菜单，选择“数据透视表选项”，系统出现如图 所示。,美化入库汇总表：把数据项汇总标题改名，把“数据”所在行隐藏，选中“物品代码”行和总计行设置为白色、加粗字体、黑色单元格背景，实现如图效。果 。,4、通过透视表制作汇总表（6）,构建出库汇
9、总表 。 利用透视表的功能建立出库汇总表，方法完全同入库汇总表，最后效果如图。,4、通过透视表制作汇总表（7）,构建成本毛利表 。 利用透视表的功能建立成本毛利表 ，方法完全同入库汇总表，最后效果如图。,4、通过透视表制作汇总表（8）,构建进销存报表 。 分别把出入库汇总表复制到进销存报表 ，效果如图。,4、通过透视表制作汇总表（9）,输入报表各项数据标题：期初数量、期初库存金额、本月入库、本月入库金额、本月出库数量、本月成本金额、库存数量、库存金额。输入对应的计算公式： 期初数量为“=L5+J5-H5”；期初库存金额为“=M5+K5-I5”；本月入库为“=IF（ISERROR（VLOOKUP
10、（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（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,VLOO
11、KUP（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:$E$32,4,0）”。,4、通过透视表制作汇总表（10）,为了美观，可以给相应的数据加上背景或底纹，最后效果如图 。,4、通过透视表制作汇总表（11）,选择建立库存盘点表。 如果需要，还可以添加库存盘点表，同样借助出入库汇总表，按
12、建立进销存报表的步骤，先把出入库复制，再添加盘点项目即可，效果如图。,4、通过透视表制作汇总表（12）,总结（1）,Excel 强大的处理数据库的命令和函数，如数据有效性序列应用；条件格式应用；函数vlookup、month、column、iserror等应用及各类自定义计算，使其具备了能组织和管理大量数据的能力。 Excel的数据透视表和数据透视图操作使其具备良好数据自动分析汇总功能。 Excel的可以自定义菜单操作使其具备良好的系统结构框架功能。,总结（2）,利用EXCEL制作数据管理系统操作步骤通是： 1）建立工作簿、定义工作表名 2）构建原始数据资源 3）实现数据最高、低报警显示功能 4）通过透视表等功能制作数据各类汇总表,1某精细化工有限公司仓库的日数据清单如图所示，请建立销售表、进货表、库存表和进销存报表。 2从网上下载一个免费试用的进销存软件，安装后使用，与自己用Excel制作的进销存系统进行比较，体会Excel制作进销存系统的优缺点。 3想一想，可以用Excel制作不同部门或其他企业通用的进销存系统模板吗？,实践内容,
