项目四 任务二 统计并分析药品库存表（课件）《计算机应用基础项目教程》（航空工业出版社）同步教学

计算机应用基础项目教程 模板来自于 http://docer.wps.cn 1 Excel是Office办公套装软件中的另一个重要成员，它是一款优秀的电子表格制作软件，具有强大的数据组织和处理功能。利用它可以快速制作出各种美观、实用的电子表格，以及对表格中的数据进行计算、统计、分析和预测等，并可按需要将表格打印出来。本项目主要学习Excel的使用方法。 项目四 使用Excel设计与制作电子表格 Transition Page 过渡页 学习 目标 01 掌握在工作表中输入、编辑数据并设置表格格式的方法。 02 掌握使用公式和函数对工作表数据进行计算的方法。 03 掌握使用排序、筛选、分类汇总和条件格式对工作表数据进行管理的方法。 04 掌握利用图表对工作表数据进行统计分析的方法。 05 掌握利用数据透视表和数据透视图对工作表数据进行统计分析的方法。 Transition Page 过渡页 任务二 统计并分析药品库存表 ——公式与工作表常用操作 过渡页 Transition Page 任务背景 库存表是企业的销售部门或财务管理人员用于记录商品流通过程中商品数量及金额的表格，它能及时反映各种物资的仓储、流通情况，为生产管理和成本核算提供依据。 通过库存分析，可为管理及决策人员提供库存资金占用、物资积压情况等不同的统计分析信息。 任务二 统计并分析药品库存表 图4-24 药品库存表效果 分析任务 本任务主要完成的工作是制作药品库存表，通过该任务让读者了解Excel的常用操作方法、使用公式计算数据、设置数字格式、单元格的引用、插入艺术字、设置工作表的页眉和页脚等知识。 任务二 统计并分析药品库存表 任务目标 能插入、删除、复制、移动和重命名工作表。 能使用公式计算工作表数据。 能为工作表数据设置数字格式，以及在工作表中插入与删除行、列和单元格。 能在工作表中插入艺术字并设置页脚。 任务实施 一、工作表常用操作 在Excel中，一个工作簿可以包含多个工作表，用户可以根据实际需要随时切换、插入、删除、移动与复制工作表。此外，为方便查看数据，还可以重命名工作表。下面用户先打开素材文件，然后对工作表进行编辑，操作步骤如下。 任务二 统计并分析药品库存表 步骤1 ☞ 启动Excel，然后在“文件”列表中选择“打开”选项，在打开的界面中单击“计算机”选项，然后在右侧列表中找到工作簿所在的文件夹，如图4-25所示。 图4-25 找到工作簿所在的文件夹 任务二 统计并分析药品库存表 步骤2 ☞ 在打开的对话框中选择要打开的工作簿“药品库存表素材”，如图4-26所示，然后单击“打开”按钮。 图4-25 找到工作簿所在的文件夹 任务二 统计并分析药品库存表 步骤3 ☞ 对工作表进行重命名和删除操作。单击工作表标签Sheet1选中该工作表，然后右击，从弹出的快捷菜单中选择“重命名”选项，此时，工作表标签呈灰底黑字显示，处于编辑状态，输入新工作表名称“库存表”，按【Enter】键或单击工作表的其他位置，完成工作表的重命名，如图4-27所示。用同样的方法将Sheet2工作表重命名为“分类比例”。 图4-27 重命名工作表 任务二 统计并分析药品库存表 提示 双击工作表标签或选择工作表后选择“开始”选项卡“单元格”组“格式”列表中的“重命名”选项，均可重命名工作表。 若要选取相邻的多个工作表，可单击第一个工作表标签，然后按住【Shift】键并单击最后一个工作表的标签，选中的工作表标签都变为白色，此时当前工作簿的标题栏中出现“工作组”字样。 若要选取不相邻的多个工作表，只需先单击第一个工作表的标签，然后按住【Ctrl】键再单击所需的工作表标签即可。 选中多个工作表后，在所选工作表的任意一个工作表中输入数据，在其他工作表的相同位置也将同步输入该数据。当工作簿包含多个工作表时，单击工作表标签左侧的翻页按钮 ，可以查看没有显示的工作表标签，当看到所需工作表标签后再单击它，即可选择该工作表。 任务二 统计并分析药品库存表 步骤4 ☞ 右击工作表标签Sheet3，在弹出的快捷菜单中选择“删除”选项，如图4-28（a）所示，即可将该工作表删除。如果工作表中有数据，则会出现如图4-28（b）所示的对话框，提示工作表中有数据，单击“删除”按钮，可将工作表删除。 图4-28 删除工作表 任务二 统计并分析药品库存表 提示 删除工作表时一定要慎重，因为删除的工作表将被永久删除，且不能恢复。若要删除一组工作表，可先选中这些工作表，然后再执行删除操作。 若想插入一个新的工作表，可右击工作表标签在弹出的快捷菜单中选择“插入”选项，即在当前工作表后插入一个新的工作表。 若想在同一工作簿中移动工作表，只需将工作表标签拖到所需位置即可。若在拖动过程中按住【Ctrl】键，则可复制工作表。若想在不同的工作簿中移动或复制工作表，首先打开源工作簿和目标工作簿，然后右击想要移动或复制的工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”选项，在弹出的“移动或复制工作表”对话框中选择目标工作簿即可（若希望复制，必须选择建立副本选项）。 任务二 统计并分析药品库存表 二、计算“库存表”工作表中的数据 1 公式的概念和运算符 公式是工作表中用于对单元格数据进行各种运算的等式，它必须以等号“=”开头。一个完整的公式，通常由运算符和操作数组成。运算符可以是算术运算符、比较运算符等；操作数可以是常量、参与运算的单元格地址和函数等，如图4-29所示。 =PI()*A2/3 运算符 常量 单元格地址 函数 图4-29 公式组成元素 任务二 统计并分析药品库存表 运算符是用来对公式中的元素进行运算而规定的特殊符号。Excel包含4种类型的运算符，即算术运算符、比较运算符、引用运算符和文本运算符。 算术运算符：其作用是完成基本的数学运算，并产生数字结果，如表4-1所示。 表4-1 算术运算符及其含义 算术运算符 含义 示例 +（加号） 加法 A1+A2 -（减号） 减法或负数 A1-A2 *（星号） 乘法 A1*2 /（正斜杠） 除法 A1/3 %（百分号） 百分比 50% ^（脱字号） 乘方 2^3 任务二 统计并分析药品库存表 比较运算符：其作用是比较两个值，结果为一个逻辑值，不是“TRUE（真）”，就是“FALSE（假）”，如表4-2所示。 表4-2 比较运算符及其含义 比较运算符 含义 示例 >（大于号） 大于 A1>B1 <（小于号） 小于 A1<B1 =（等于号） 等于 A1=B1 >=（大于等于号） 大于等于 A1>=B1 <=（小于等于号） 小于等于 A1<=B1 <>（不等于号） 不等于 A1<>B1 任务二 统计并分析药品库存表 引用运算符：其作用是对单元格区域进行合并计算，如表4-3所示。 表4-3 引用运算符及其含义 引用运算符 含义 示例 :（冒号） 区域运算符，用于引用单元格区域 B5:D15 ,（逗号） 联合运算符，用于引用多个单元格区域 B5:D15,F5:I15 （空格） 交叉运算符，用于引用两个单元格区域的交叉部分 B7:D6:C8 文本运算符：使用文本运算符“＆”（与）可将两个或多个文本值串起来产生一个连续的文本值。如输入“八达岭”＆“长城”可以生成“八达岭长城”。 任务二 统计并分析药品库存表 2 单元格引用 Excel为用户提供了相对引用、绝对引用和混合引用3种引用类型，以适应不同的应用场合。 相对引用指的是单元格的相对地址，其引用形式为直接用列标和行号表示单元格，例如B5，或用引用运算符表示单元格区域，如B5:D15。如果公式所在单元格的位置改变，引用也随之改变。默认情况下，公式使用相对引用，如本任务随后通过复制公式计算其他数据时便会使用相对引用。 绝对引用是指公式中总是引用指定位置的单元格。如果公式所在的单元格位置发生改变，公式中使用绝对引用的单元格不会变化。使用绝对引用时，需要在引用的单元格列标和行号前面都加上“$”号。例如，若在公式中引用$I$20单元格，则不论将公式复制或移动到什么位置，引用的单元格都不会改变。 引用中既包含绝对引用又包含相对引用的称为混合引用，如A$1或$A1等，用于表示列变行不变或列不变行变的引用。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。 任务二 统计并分析药品库存表 3 函数 函数是Excel程序预先定义好的表达式，它必须包含在公式中，每个函数都由函数名和参数组成。其中，函数名将执行操作，参数表示函数将作用的值的单元格地址，通常是一个单元格区域，也可以是更为复杂的内容。 用户可以利用函数快速完成相关数据的统计与分析，常用的函数有求和函数SUM()、平均值函数AVERAGE()、最大值函数MAX()、最小值函数MIN()等。 提示 编辑公式时，输入单元格地址后，按【F4】键可在绝对引用、相对引用和混合引用之间进行切换。 任务二 统计并分析药品库存表 步骤1 ☞ 计算“库存数量”。单击I3单元格，然后输入等号“=”，再输入操作数和运算符“F3+G3-H3”，如图4-30（a）所示。按【Enter】键或单击编辑栏中的“输入”按钮 确认，得到第一个药品的库存数量，如图4-30（b）所示。 图4-30 计算“库存数量” （a） （b） 也可单击单元格后在编辑栏中输入公式 任务二 统计并分析药品库存表 提示 也可在输入等号后单击要进行计算的单元格，然后输入运算符，再单击要进行计算的单元格。在单元格中输入公式后，如果发现错误，可单击含有公式的单元格，然后在编辑栏中进行修改，最后按【Enter】键确认。 任务二 统计并分析药品库存表 步骤2 ☞ 计算“库存金额”。在J3单元格中输入公式“=E3*I3”，按【Enter】键确认，得到第一个药品的库存金额，如图4-31所示。 图4-31 计算“库存金额” 任务二 统计并分析药品库存表 步骤3 ☞ 计算其他药品的“库存数量”和“库存金额”。选中I3:J3单元格区域，然后向下拖动该单元格区域右下角的填充柄，到J19单元格后释放鼠标，完成公式的复制，同时得到计算结果，如图4-32所示。 图4-32 利用填充柄复制计算其他药品的库存数量和库存金额 任务二 统计并分析药品库存表 步骤4 ☞ 计算所有药品的“期初数量”总和。单击要放置求和结果的单元格F20，然后单击“开始”选项卡“编辑”组中的“自动求和”按钮 ，此时工作表中用动态边框显示要进行计算的单元格区域，确认单元格区域正确与否。如果不正确，可重新在工作表中拖动鼠标进行选择。本例直接按【Enter】键得到计算结果，如图4-33所示。 图4-33 计算总计 任务二 统计并分析药品库存表 步骤5 ☞ 计算所有药品的“期初数量”平均值。单击F21单元格，然后单击“自动求和”按钮 右侧的三角按钮，在展开的列表中选择“平均值”项，此时工作表中也用动态边框显示要进行计算的单元格区域，拖动鼠标重新在工作表中选择要进行求平均值的单元格区域F3:F19，然后按【Enter】键，如图4-34所示。 图4-34 计算平均值 任务二 统计并分析药品库存表 步骤6 ☞ 计算“最大值”和“最小值”。参照步骤4和步骤5，分别在“自动求和”列表中选择“最大值”和“最小值”项，计算所有药品的“期初数量”最大值和最小值，结果如图4-35所示。其中要进行计算的数据区域均为F3:F19。 图4-35 计算最大值和最小值效果 任务二 统计并分析药品库存表 步骤7 ☞ 通过复制公式计算其余列的“总计”“平均值”“最大值”和“最小值”。选中单元格区域F20:F23，然后将鼠标指针移到所选区域右下角的填充柄上，按住鼠标左键向右拖动，到J23单元格后释放鼠标，如图4-36所示。 图4-36 通过复制公式完成计算 任务二 统计并分析药品库存表 三、计算“分类比例”工作表中的数据 首先在“分类比例”工作表中输入要进行计算的项目，然后再计算各类别占库存总数量的比例值，操作步骤如下。 步骤1 ☞ 单击“分类比例”工作表标签，切换到该工作表，输入图4-37所示的数据。 图4-37 录入“分类比例”工作表原始数据 任务二 统计并分析药品库存表 步骤2 ☞ 计算“保健食品”的库存数量。在“分类比例”工作表中单击B3单元格，输入等号“=”，如图4-38（a）所示，然后单击“库存表”工作表标签，单击I3单元格，输入加号+，再单击I4单元格，输入加号+，单击I5单元格，输入加号+，单击I6单元格，输入加号+，单击I7单元格，此时编辑栏的公式中会显示引用到的工作表及其中的单元格地址，如图4-38（b）所示。 图4-38 引用不同工作表间的单元格进行计算 （a） （b） 任务二 统计并分析药品库存表 提示 在同一工作簿中，不同工作表中的单元格可以相互引用并进行计算，表示方法为：工作表名称!单元格或单元格区域地址，如图4-38（b）所示。在步骤2中，用户也可以直接输入引用的工作表和单元格地址。 此外，除了可以在同一工作表、同一工作簿的不同工作表中引用单元格外，还可以在当前工作表中引用不同工作簿中的单元格，其表示方法为：[工作簿名称.xls]工作表名称！单元格或单元格地址。 任务二 统计并分析药品库存表 步骤3 ☞ 按【Enter】键确认并返回“分类比例”工作表，B3单元格显示计算结果，如图4-39（a）所示。用同样的方法，可计算出化学药品、中药、食品、外用膏贴等的库存数量，结果如图4-39（b）所示。 图4-39 计算药品的数量 （a） （b） 任务二 统计并分析药品库存表 步骤4 ☞ 计算“保健食品”的分类比例。在“分类比例”工作表的C3单元格输入“=B3/”，然后单击“库存表”工作表标签，再单击I20单元格，此时在编辑栏中显示C3单元格中的公式为“=B3/库存表!I20”，按【Enter】键返回“分类比例”工作表，并在C3单元格显示计算结果，如图4-40所示。 图4-40 计算“保健食品”的分类比例 任务二 统计并分析药品库存表 步骤5 ☞ 其他类别分类比例的计算。计算其他类别的分类比例，除了可以使用前面的操作方法外，还可使用绝对引用并复制公式得到，即把C3单元格公式由“=B3/库存表!I20”修改为“=B3/库存表!$I$20”，即把公式中的相对引用“库存表!I20”改为绝对引用“库存表!$I$20”，如图4-41所示。 图4-41 修改引用类型 $符号可在英文输入状态下按住【Shift】键的同时按主键盘区的数字键4输入 任务二 统计并分析药品库存表 步骤6 ☞ 按【Ctrl+C】组合键复制C3单元格中的公式，再选中C4:C7单元格区域，按【Ctrl+V】组合键，或直接向下拖动C3单元格右下角的填充柄，即可完成公式的复制，如图4-42所示。可看到公式中相对引用的单元格改变，绝对引用的单元格保持不变。 图4-41 修改引用类型 任务二 统计并分析药品库存表 四、设置单元格格式 在Excel中输入内容之后，往往要对它进行美化操作，为此，可以使用功能区中的工具按钮，或通过“设置单元格格式”对话框来进行设置。 步骤1 ☞ 自动套用格式。在“库存表”工作表中选择A2:J23单元格区域，然后单击“开始”选项卡“样式”组中的“套用表格格式”按钮，在展开的列表中选择一种样式，如“表格样式浅色10”，如图4-43（a）所示，在打开的对话框中单击“确定”按钮，得到效果，如图4-43（b）所示。 任务二 统计并分析药品库存表 （a） 任务二 统计并分析药品库存表 （b） 图4-43 自动套用格式 任务二 统计并分析药品库存表 步骤2 ☞ 选择A2:J2单元格区域，在“开始”选项卡的“对齐方式”组单击“居中”和“自动换行”按钮，如图4-44（a）所示，然后调整F～H列中的文本以2行显示，并调整第2行的行高，效果如图4-44（b）所示。 （a） （b） 图4-44 设置单元格对齐方式和调整列宽 任务二 统计并分析药品库存表 步骤3 ☞ 将J列单元格数据的小数位数设为1位。选中J3:J23单元格区域，然后单击“开始”选项卡“数字”组右下角的对话框启动器按钮 ，打开“设置单元格格式”对话框，在“分类”下拉列表中选择“数值”选项，在右侧的“小数位数”编辑框输入“1”，单击“确定”按钮，如图4-45所示。 图4-45 设置数字格式 也可单击编辑框右侧的微调按钮调整数值 任务二 统计并分析药品库存表 步骤4 ☞ 设置平均值的数字格式为1位小数。用户既可以采用步骤3的方法进行设置，也可采用复制单元格格式的方法得到：向左拖动J21单元格右下角的填充柄到F21单元格，如图4-46（a）所示，然后松开鼠标，再单击“自动填充选项”按钮，在展开的列表中选择“仅填充格式”选项，如图4-46（b）所示，即可完成所选单元格的数字格式设置。 图4-46 复制数字格式 （a） （b） 任务二 统计并分析药品库存表 步骤5 ☞ 设置“分类比例”的数字格式。切换到“分类比例”工作表，选中C3:C7单元格区域，同样打开“设置单元格格式”对话框，在“数字”选项卡的“分类”列表中选择“百分比”选项，在右侧的小数位数中设置小数位数为2，单击“确定”按钮，如图4-47所示。 在左侧的列表中选择数字类型，在右侧的设置区中对所选数字类型进行具体设置 图4-47 设置数据的小数位数 任务二 统计并分析药品库存表 提示 为单元格中的数值设置不同的数字格式，只是更改它的显示形式，不影响其实际值。用户也可利用“数字”组中的相应按钮来设置数字格式，如图4-48所示。 设置货币格式 设置百分比样式 设置千位分隔样式 增加和减少小数位数 图4-48 数字格式按钮的含义 任务二 统计并分析药品库存表 五、插入艺术字和页脚 下面在“库存表”的标题上方插入艺术字并为其添加自定义的页脚。操作步骤如下。 步骤1 ☞ 在表格的标题行上方插入一行，并在该行中插入艺术字。单击第1行的行号以选中第1行，再右击，在弹出的快捷菜单中选择“插入”选项，即可在所选行的上方插入一个新行，如图4-49所示。 图4-49 插入行 任务二 统计并分析药品库存表 提示 右击要插入行或列的位置，在弹出的快捷菜单中选择“插入”项，打开“插入”对话框，选择“整行”或“整列”单选钮，可在所选单元格的上方或左侧插入一行或一列。若在弹出的快捷菜单中选择“删除”选项，可打开“删除”对话框，在其中选择相应的单选钮，可删除所选单元格，或删除单元格所在的行或列。 任务二 统计并分析药品库存表 步骤2 ☞ 右击第1行的行号，在弹出的快捷菜单中选择“行高”选项，打开“行高”对话框，在“行高”编辑框输入“70”，如图4-50所示，单击“确定”按钮完成行高的设置。 图4-50 精确设置行高 任务二 统计并分析药品库存表 步骤3 ☞ 单击“插入”选项卡“文本”组中的“艺术字”按钮，在展开的列表中选择一种艺术字样式，如图4-51（a）所示，输入艺术字文本“哈药集团制药六厂”并将其拖到第一行的中间位置，如图4-51（b）所示。 图4-51 插入艺术字并拖动艺术字到合适位置 （a） （b） 任务二 统计并分析药品库存表 步骤4 ☞ 在工作表中插入页脚。在“插入”选项卡的“文本”组单击“页眉和页脚”按钮，进入页眉和页脚编辑状态。单击“页眉和页脚工具/设计”选项卡“导航”组中的“转至页脚”按钮，然后在“左”页脚编辑框单击，再单击“页眉和页脚元素”组中的“图片”按钮，打开“插入图片”窗口，单击“浏览”按钮，如图4-52所示。 图4-52 单击“浏览”按钮 任务二 统计并分析药品库存表 步骤5 ☞ 在打开的“插入图片”对话框中选择素材文件夹中的“项目四”>“哈药六厂.jpg”图片，单击“插入”按钮；在“中”编辑框中单击，然后单击“页眉和页脚元素”组中的“当前日期”按钮 ，此时可在工作表中看到添加的页脚，如图4-53所示。 图4-53 选择要作为页脚的图片并插入 任务二 统计并分析药品库存表 步骤6 ☞ 图4-54 关闭网格线 单击程序窗口右下角的“普通”按钮 ，返回工作表正常编辑状态。 步骤7 ☞ 为方便查看制作的表格，用户也可以将网格线去掉。单击“视图”选项卡“显示”组中的“网格线”复选框，取消其选中状态，如图4-54所示。至此，工作表制作完成，最后另存文件为“药品库存表（效果）”。 任务二 统计并分析药品库存表 高手秘籍 1 设置斜线表头 在电子表格的单元格中绘制斜线，可以在其中添加表格项目名称，便于查看单元格中的数据分类。要为单元格设置斜线表头，可在选中单元格后，在“设置单元格格式”对话框“边框”选项卡的“边框”设置区单击“斜线”按钮，如图4-55所示，或利用“直线”工具在单元格中绘制斜线，然后利用文本框输入分类名称。 图4-55 利用斜线按钮绘制斜线 任务二 统计并分析药品库存表 2 轻松设置“0”开头的数字编号 在制作表格时，如果要输入以0开头的数字编号，可以将单元格或单元格区域设置为“文本”格式，然后输入以0开头的数字编号即可看到效果，或输入英文“’”号后再输入以0开头的数字编号，如图4-56所示。 图4-56 输入以“0”开头的数字编号 任务二 统计并分析药品库存表 拓展训练 制作图书馆藏书分类比例统计表 利用所学知识制作图书馆藏书分类比例统计表，效果如图4-57所示。 图4-57 图书馆藏书分类比例统计表 任务二 统计并分析药品库存表 1 训练目的 强化练习单元格引用、利用公式和函数对工作表数据进行计算等操作。 2 训练内容 （1）打开素材文件“项目四”>“图书馆藏书分类比例统计表.xlsx”工作簿，然后将“Sheet1”重命名为“分类比例”。 （2）根据说明文本，利用公式计算“应配备总数”和“尚需”列数据。 （3）利用公式计算“金额”“总计”“最小值”和“最大值”数据。 任务二 统计并分析药品库存表 $$