EXCEL数据处理和分析技术.doc

第二章实践部分：EXCEL数据处理和分析技术要求：1.用宏程序统一不同表格中的内容格式，12.用冻结字段方法将各表汇总到一张表中63.用函数CONCATENATE注明各条记录的统一编号74.用函数MID设置发货年度95.制作数据透视表，对数据进行分析156.制作三维饼图，以分析结构187.制作折线图，以分析变化318.制作预测图，以分析变化趋势37将含“汇总表”的EXCEL工作表、数据透视表、三维饼图、折线图、显示预测线，预测公式和R2值 一起放在“学号加姓名加导出表”的EXCEL工作簿中，压缩上交教师。从罗斯文数据库中，将“产品销售情况表”中导出“点心”等八类产品表到EXCEL工作簿中1、 打开Northwind数据库，选择对象下的表，选择右边的产品表，右键，选择设计视图，2、 在设计视图，增加一个字段：“列字段”，数据类型文本，完成后单击右上角的关闭3、 再选择对象下的查询，选择订单查询，右键，设计视图，4、 将产品表中的列字段插入到设计表格中5、 单击左上角的视图， 6、 可见订单查询表，单击右上角的关闭7、 在查询中，单击工具栏上的新建，选择交叉表查询8、 选择订单查询表9、 选择类别名称，下一步 10、 列字段，下一步11、 单价，合计，下一步12、 完成，形成交叉表，可见有哪几类产品及其总价13、 再次打开订单查询表，右键，选择打开14、 选择产品类别，单击工具栏上的顺序排序按纽，以便选择八类产品15、 分别选择八类产品，方法是选择某类产品，单击工具栏上的筛选，全选表格，执行编辑，复制16、 粘贴到EXCEL表，方法是打开EXCEL，执行开始，程序，OFFICE，EXCEL 17、 保存为学号+姓名+ EXCEL数据处理和分析技术18、 将复制的某类产品表，粘贴到EXCEL表中， 19、 在左下角，将SHEET1表改为该产品名称的表20、 单击工具栏上的取消筛选按纽，再对其他类产品依次筛选，复制，粘贴到EXCEL表中21、 合计计算各类产品的单价总计，选择某一类别产品表，在列选择器中选择第三行，22、 执行窗口，冻结命令23、 将右侧光标下拉到底，选择单价列，到最下一行，再单击工具栏上的合计按纽，计算出单价总计24、 与ACCESS交叉表中的总计数进行核对，是否导出的记录与数据库数值相符，方法是回到查询类别下，选择订单查询_交叉表，右键，打开25、 将订单查询_交叉表全选，复制26、 粘贴到EXCEL表中，与导出的各类表总计数核对，是否相符类别名称单价之总计1点心8,098.86调味品4,830.30谷类/麦片4,481.00海鲜6,637.43日用品10,551.90肉/家禽7,972.48特制品5,083.30饮料12,614.00用宏程序统一不同表格中的内容格式，27、 将上述八个表用宏程序统一表格样式，设置宏程序的方法是：执行编辑菜单，全选，执行工具，宏，录制新宏28、 设置快捷键，CTRL+Z保存在个人宏工作簿29、 录制宏，执行：格式菜单，单元格命令30、 选择对齐选项卡，在水平和垂直对齐中分别选择“居中”，在文本控制中选择“自动换行”31、 选择字体选项卡：选择字体为宋体，字形为常规，字号为9 32、 再执行文件，页面设置， 33、 在页面选项卡中，设置方向为横向，缩放比例为75% 34、 选择页边距选项卡中的居中方式为水平35、 选择页眉/页脚选项卡，选择页眉为文件名，页脚为页码数36、 选择工作表选项卡，顶端标题行时单击表格中的标题行，自动显示标题行位置，在打印中选择网络线，以便打印表格，其它默认，确定37、 选择第三行，执行窗口菜单，冻结窗口命令，使第1、2行冻结，以便拉出序号38、 单击设计宏按纽，退出录制，以后对其它表格，只要全选表格，用快捷键，CTRL+z，系统自动运行上述步骤。用冻结字段方法将各表汇总到一张表中39、 为将八类表，汇总到一个工作表中，单击左下方第一个数据表标签，右键，插入，选择工作表40、 新建一个空工作表，双击该表标签，重命名为汇总表41、 将每个表，选择有记录的行，再复制粘贴到汇总表中。用函数CONCATENATE注明各条记录的统一编号42、 删除不需要的字段名行，方法是新建一个字段，名称为序号，在第1、第2行分别输入1，243、 将光标放在右下角，变成黑十字，往下拖动，形成序列号44、 单击单价列字段，单击工具栏上的排序命令，将有关字段名放在一起， 45、 选择该行，右键，删除46、 再单击序号，单击工具栏上排序，顺序按纽，恢复原顺序，再重新拉出序号，以消除删除序号的影响47、 添加统一编号，统一编号=发货日期+序号 ，不能重复，方法是，先增加一个字段：记录编号48、 选中需要输入统一编号的单元格，单击编辑栏中的FX，选择函数CONCATENATE49、 在搜索函数栏中输入“合并,将多个文本字符串合并成一个”，单击“转到”，可见显示函数CONCATENATE，50、 单击CONCATENATE，确定，在输入函数参数中，单击需要连接的单元格，即“发货日期+序号”确定51、 将光标放在单元格的右下角，按住往下拖动，可将该单元格的公式，复制到同列中的其它单元格中，完成记录编号字段的内容的输入 。用函数MID设置发货年度52、 对发货日期右边增加一列，名为发货年度53、 用函数MID将发货日期后的月份减去，只保留前四位年度，方法是：将发货日期复制粘贴到该列中54、 执行编辑，替换， 55、 将发货日期中 - 替代为点 .56、 替代后见图，目的是为运用函数MID做准备57、 如果不用替代的方法，用函数MID就只能是普通数字，而不是年代数58、 选中需要输入年度的单元格，单击编辑栏中的FX，在搜索函数栏中输入“从文本字符串中指定的超始位置起返回指定长度的字符”，单击“转到”，可见显示函数MID，59、 在函数参数的单元格中选择要输入发货年度的单元格，在起始位置输入1，在位长中输入4个字符，以保存发货日期的前四位。确定。60、 可见该单元格中输入了发货日期的前四位，即年代数。再选择有公式单元格，复制到最下一行61、 分别选择这记录编号和购货年度两列，执行编辑菜单，复制62、 再执行编辑菜单，选择性粘贴命令63、 选择数值，确定64、 粘贴，以消除公式，制作数据透视表，对数据进行分析65、 利用上述数据来建立数据透视表，以便进行各年发货情况分析。执行编辑，数据透视表和数据透视图命令66、 选择数据透视表，下一步67、 选择范围，下一步68、 单击布局按纽69、 在布局中，分别将发货年度拖到行，将列字段拖到列，将单价拖到数据格中，确定70、 完成71、 可见透视表格72、 因为上表中发货年有空，在汇总表中进行补充为20000和2001，再回到透视表，单击工具条上的刷新，表自动更新制作三维饼图，以分析各年度所占比例73、 选择发货年度字段和列字段，复制，选择性粘贴，数据，以消除公式影响，粘贴到新的地方74、 新增一字段，各年比例75、 用公式计算各年度发货额所占比例，在FX中输入=B21/$B$2776、 将各单元格公式复制粘贴到最下一行，再单击工具栏上的百分比按钮，转化为百分数，扩大小数位数为小数点后的2位77、 制作饼图，单击菜单栏中的“插入图表”或工具栏中的图表向导命令，如图所示。78、 在弹出的“图表向导-4 步骤之1- 图表类型”对话框中切换到“标准类型”选项卡，在“图表类型”列表框中单击“饼图”，然后在“子图表类型”中单击“三维饼图”，如图所示。79、 在数据区域中，按住CTRL，分别选择发货年代和各年比例两个字段，但不包含字段名和总计值，按下“按下不放可查看示例”按钮，示例框中显示出饼图的实例。80、 切换到“系列”选项卡，在该选项卡中可以设置分类标志为发货年度，值为各年比例，名称默认，单击该对话框中的“下一步”按钮，如图所示。81、 在弹出的“图表向导-4 步骤之3- 图表选项”对话框中切换到“标题”选项卡，在“图表标题”文本框中输入“各年发货数量变化情况”，如图所示。82、 单击图例，选择在底部83、 切换到“数据标志”选项卡，选择“类别名称”和 “%”复选框，这样，百分比数值就出现在饼图中的相应位置上，最后单击该对话框中的“下一步”按钮，如图所示。84、 在弹出的“图表向导-4 步骤之4- 图表位置”对话框中用户可以选择图表的插入位置。单击“完成”按钮。85、 看到创建的销售额三维饼图了，如图所示。86、 如果图表数据区域有误，可单击图表右键，选择数据源，重新选择数据区域87、 对图表确定格式，右键，选择图表区格式。88、 选择填充效果，选择双色89、 选择字体选项卡，确定合适的字号和字体90、 完成图表制作折线图，以分析变化91、 单击工具条上的图表向导，选择折线图92、 选择图表区域93、 选择标题选项卡等，设置标题各发货年度总值，图例等94、 下一步，选择图表位置，作为其中的对象插入95、 要改变图表区域格式，可右键，选择图表区格式，选择填充效果，选择双色96、 要改变绘图区格式，右键，选择绘图区格式，选择填充效果，选择双色97、 要改变曲线格式，右键，选择数据系列格式98、 选择颜色，粗细等99、 选择字体选项卡，确定合适的字号和字体100、 完成图表制作预测图，以分析变化趋势101、 为便于分析，调整数据，形成以下表发货年度总价199611168.51199728174.27199845180.03102、 在发货年度前插入一列，序号，103、 预测1999年-2000年变化情况，用上述办法形成折线图10