第3章电子表格的制作规范与方法实验31电子表格制作规范与方法（一）一、 实验目的(1) 掌握电子表格制作的基础知识。 (2) 掌握电子表格的基本编辑方法。 (3) 掌握电子表格的格式排版操作方法。 二、 实验条件要求 (1) 硬件： 计算机。 (2) 系统环境： Windows 7。 (3) Microsoft Excel 2010软件或WPS表格软件。 三、 实验基本知识点 Microsoft Excel拥有直观的界面、出色的计算功能和图表工具等，是目前比较流行的一种个人计算机数据处理软件。 1. 工作簿窗口 工作簿 当启动Excel时，即打开了一个名为“工作簿1”的工作簿窗口，工作簿是运算和存储数据的文件。Excel 2010工作簿窗口与Word等其他软件工作窗口类似，在此仅介绍Excel 2010工作簿窗口及主要组成部分，如图31所示。默认情况下，工作簿窗口处于最大化状态。单击菜单栏右侧的“向下还原”按钮，即可将工作簿窗口缩小。 2. 编辑栏及其使用 单元格编辑栏用于编辑和显示当前活动单元格中的内容，如图32所示。若单元格中的数据是由公式算出的值，可在编辑栏中查看和修改它对应的公式。名称框显示的是单元格的名称即地址，由列标及行号组成。当某个单元格被激活，即成为活动单元格时，其名称（如A1）就会在名称框中出现。此后用户输入的文字或数据将在该单元格与编辑栏中同时显示。 图31工作簿窗口 图32编辑栏 四、 实验步骤1. 学生成绩表的输入、编辑及排版1） 启动Excel 2010 在Windows的“开始”菜单中，选择所有“程序”→Microsoft Office→Microsoft Excel 2010，如图33所示。 自动填充 2） 录入学生数据 参考图34所示的数据，将学生成绩信息录入到打开的Excel 2010的Sheet1工作表中。 3） 添加学生编号列，并自动填充编号值 （1） 单击列标A，选中“姓名”列（见图35） ，然后执行“插入”菜单中的“插入工作表列”命令，这样就在“姓名”列的左侧插入了一个新的空白列。 （2） 在A1单元格中输入“编号”，在A2单元格中输入数字1，如图36所示。然后，将指针移到A2单元格右下角的填充柄，按住鼠标左键向下拖至A8，拖动后的结果如图37所示。图33启动Excel 2010 图34实验基础数据 图35插入编号列图36输入“编号”列第一个数字1 图37拖动后的结果 （3） 拖动完成后，单击“自动填充选项”按钮，在出现的弹出式菜单中（见图38） 选中“填充序列”单选按钮，填充结果如图39所示。 图38选择填充方式 图39自动填充序列效果 4） 插入标题行 与插入列类似，单击行号1，选中第一行，执行“插入”菜单中的“插入工作表行”命令，即插入一个新的空白行。在单元格A1中输入“学生成绩表”，结果如图310所示。 图310插入标题行 5） 计算平均分 （1） 在G3单元格输入图311所示的计算平均分的公式并按Enter键，即可得到第一个同学的平均分。需要注意，公式的输入必须以“=”开头。本例中使用AVERAGE()函数来计算平均值，括号内的“D3:F3”代表由D3单元格到F3单元格所构成的矩形区域内的所有单元格的值，该内容既可以手动输入也可以拖动鼠标选择。 图311使用AVERAGE（）函数计算平均分 （2） 用自动填充的方法计算出其他同学的平均分。可以选中G3单元格，然后双击填充柄进行快速的自动填充。完成结果如图312所示。 图312计算平均分后的结果 求和、求平均 现在，已经完成了全部数据的输入，接下来将进行进一步的编辑排版。6） 设置工作表标签 （1） 双击工作表标签Sheet1，将该工作表重命名为“成绩表”，如图313所示。 图313重命名工作表 （2） 右击工作表标签，在弹出的快捷菜单中选择“工作表标签颜色”→“红色”，即可将该工作表标签背景设置为红色，如图314所示。 图314设置工作表标签颜色 7） 设置标题格式 （1） 选中A1～G1单元格，单击工具栏上的“合并后居中”按钮，将标题行合并居中，如图315所示。并设置其字体为黑体，字号为14。 （2） 设置A2～G2单元格字体为黑体，字号为12，结果如图316所示。 8） 设置单元格对齐方式 选中A2～G9单元格，单击工具栏上的“居中”及“垂直居中”按钮，设置所有单元格的水平及垂直方向对齐效果均为“居中”对齐，如图317所示。 图315设置标题行合并后居中 图316设置字体、字号结果 图317设置单元格对齐方式 9） 设置数字格式 （1） 选中D3～G9单元格，如图318所示，单击工具栏上“数字”区域右下角的箭头，打开“设置单元格格式”对话框。 （2） 在“设置单元格格式”对话框中，设置数字格式分类为“数值”，“小数位数”为1，如图319所示。单击“确定”按钮，即完成了将所有数字格式均设置为1位小数。 10） 设置适当的列宽 选中全部数据区域，单击“格式”菜单下的“自动调整列宽”命令，如图320所示，单元格的列宽就会根据单元格的内容进行适当的调整，调整后的结果如图321所示。图318选中要设置数字格式的单元格 图319设置数字格式 图320设置列宽 图321调整列宽后的结果 11） 设置边框和底纹 （1） 选中列表头（A2～G2单元格）右击，在弹出的快捷菜单中选择“设置单元格格式”命令，进入“设置单元格格式”对话框，选择“边框”选项卡，设置如图322所示的边框线条样式，颜色为“自动”，然后单击“上边框”按钮和“下边框”按钮，即可在预览框中预览设置的效果。 图322设置列表头边框 （2） 切换到“填充”选项卡，设置背景色为蓝色，如图323所示。再切换到“字体”选项卡设置字体颜色为白色，单击“确定”按钮，即完成了列表头的边框底纹设置。设置后的效果如图324所示。 （3） 拖动鼠标选择A3～G3单元格，然后按住Ctrl键，同时依次拖动选择单元格区域A5～G5、A7～G7、A9～G9，即选定编号为1、3、5、7四名同学的记录。选定的数据区域如图325所示。设置选定区域的填充背景色为淡蓝色，如图326所示。 （4） 再选中A9～G9单元格，设置其下边框，如图327所示，最终完成效果如图328所示。图323设置列表头底纹填充背景色 图324设置列表头边框和底纹后的效果 图325选定编号为1、3、5、7的学生记录 图326设置选定区域的底纹填充色 图327设置表格最后一行的边框 图328设置完边框底纹后的最终效果 条件格式 12） 设置条件格式 （1） 选中D3～G9数据区域，选择“条件格式”菜单下的“突出显示单元格规则”→“小于”命令，如图329所示。 （2） 设置数值小于60的单元格格式为“浅红填充色深红色文本”，如图330所示。 至此，该表格的编辑排版设置就全部完成了，最终效果如图331所示。 图329为选定的数据设置条件格式 图330设置条件格式效果 图331最终完成效果 2. 按要求完成题目 根据图332所示的“2018年6月全国部分城市房价数据”制作表格（该表格可以在本书指定网址下载，文件名为“实验31 操作2部分城市房价数据.xlsx”），要求如下。 图3322018年6月全国部分城市房价数据 （1） 按照房价由高到低的顺序进行排序。 （2） 在表格左侧插入一列“编号”列，并填充编号值。 （3） 将“平均房价”列的数据设置数字格式为“数值”，小数位数为0，使用千位分隔符。 （4） 将“同比上年”列的数据设置数字格式为“百分比”，小数位数为2。 身份证号提取 （5） 调整适当的行高、列宽，进行必要的字体、字号、边框、底纹等设置，使其更加美观。 （6） 使用条件格式将“同比上年”的百分比为正数的用红色字体显示，为负数的用绿色字体显示。 五、 课后作业 （1） 在Word中对上述数据进行处理，并说出Word和Excel两个软件在处理数据方面的优劣。 （2） 尝试更复杂的条件格式设置。 （3） 练习更多的电子表格编辑排版操作。 实验32电子表格制作规范与方法（二）一、 实验目的（1） 掌握电子表格中常用的公式及函数的使用。 （2） 掌握电子表格的基本数据处理方法。 （3） 掌握电子表格的图形化表达方法。 二、 实验条件要求 （1） 硬件： 计算机。 （2） 系统环境： Windows 7。 （3） Microsoft Excel 2010软件或WPS表格软件。 三、 实验基本知识点1. 公式Excel公式是用户根据需求自己构建的计算表达式。一个公式中可以包含各种运算符、常量、变量、函数及单元格引用等。输入一个公式时必须以等号“=”作为开头，然后再输入公式的表达式。在Excel中有4类运算符： 算术运算符、文本运算符、比较运算符和引用运算符。具体包含的符号如表31所示。表31Excel公式的运算符类型 类型符号算术运算符+（加）、-（减）、*（乘）、/（除）、^（乘方）、%（百分数）文本运算符=（等于）、<（小于）、>（大于）、<>（不等于）、<=（小于或等于）、>=（大于或等于）比较运算符&（文本连接）引用运算符冒号“:”（区域引用）、空格“”（区域交集）、逗号“,”（区域并集）单元格引用在Excel公式中，经常会用到单元格引用。单元格引用代表工作表中的一个单元格或一组单元格。通过使用单元格引用，可以在一个公式中使用工作表上不同部分的数据，也可以在几个公式中使用同一个单元格中的数据。单元格引用包含相对引用、绝对引用和混合引用3种形式，如表32所示。表32单元格引用的3种形式 形式说明相对引用引用的是单元格的相对地址，其引用形式为直接用列标和行号表示单元格，如E3绝对引用引用单元格的固定地址，其引用形式为在列标和行号的前面都加上$，如$A$1混合引用引用中既包含绝对引用又包含相对引用的形式称为混合引用，如A$1或$A12. 常用函数 Excel函数一共有11类： 数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。下面介绍一些常用函数。 （1） SUM函数： 计算所有参数数值的和。 语法格式：SUM(Number1, Number2, …)参数： “Number1，Number2，…”代表需要计算的值，可以是具体的数值、引用的单元格（区域）等。 （2） SUMIF函数： 根据指定条件对若干单元格、区域或引用求和。 语法格式：SUMIF(Range, Criteria, Sum_range)参数： Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。 （3） AVERAGE函数： 求出所有参数的算术平均值。 语法格式：AVERAGE(Number1, Number2,…)参数： “Number, Number2，…”为需要求平均值的数值或引用单元格（区域），参数不超过30个。 （4） IF函数： 根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 语法格式：IF(Logical,Value_if_true,Value_if_false)参数： Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果省略返回TRUE；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果省略返回FALSE。 （5） COUNT函数： 统计数组或单元格区域中含有数字的单元格个数。 语法格式：COUNT(Value1, Value2,…) 参数： “Value1，Value2，…”是包含或引用各种类型数据的参数（1～30个），其中只有数字类型的数据才能被统计。 （6） MAX函数： 求出一组数中的最大值。 语法格式： MAX(Number1, Number2,…)参数： “Number1，Number2，…”代表需要求最大值的数值或引用单元格（区域），参数不超过30个。 （7） MIN函数： 求出一组数中的最小值。 语法格式：MAX(Number1, Number2,…) 参数： “Number1，Number2，…”代表需要求最小值的数值或引用单元格（区域），参数不超过30个。 （8） LEFT函数： 根据指定的字符数返回文本串中的第一个或前几个字符。 语法格式：LEFT(Text, Num_chars)参数： Text是包含要提取字符的文本串；Num_chars指定函数要提取的字符数，它必须大于或等于0。 （9） RIGHT函数： 根据指定的字符数返回字符串右端指定个数字符。 语法格式：RIGHT(Text, Num_chars)参数： Text是包含要提取字符的文本串；Num_chars指定函数要提取的字符数，它必须大于或等于0。 （10） MID函数： MID返回文本串中从指定位置开始的特定数目的字符。 语法格式：MID(Text, Start_num, Num_chars)参数： Text是包含要提取字符的文本串；Start_num是文本中要提取的第一个字符的位置，文本中第一个字符的Start_num为1，以此类推；Num_chars指定函数要从文本中返回字符的个数。 （11） TODAY函数： 给出系统日期。 语法格式：TODAY()参数： 该函数不需要参数。 （12） NOW函数： 给出当前系统日期和时间。 语法格式：NOW()参数： 该函数不需要参数。 3. 图表 将工作表以图表方式表示，使观看者能够更快地理解工作表数据，图表能将工作表中的数字变为非常直观的图形格式，并且从图表上很容易看出数据变化的趋势。由于图表的直观性，因此在Excel中应用极广。Excel提供多种样式的图表给用户使用，如柱形图、条形图、折线图、饼图、面积图等基本图表方式，每一种方式又有几种简单的变化样式。选择图表类型取决于数据及如何表示数据。四、 实验步骤1. 图书销售表的数据处理锁定表头1） 计算销售额 （1） 在本书指定网址下载并打开“实验32 操作1图书销售表.xlsx”，如图333所示。 图333“图书销售表”原始数据 （2） 在F2单元格输入公式“=D2E2”，确认后即可计算出第一行记录的销售额。然后，用自动填充的方法（双击填充柄）计算出全部“销售额”值，结果如图334所示。 图334用公式计算出销售额 （3） 设置“销售额”列数据的数字格式为“数值”，小数位数为2，使用千位分隔符，结果如图335所示。2） 排序 （1） 如图336所示，单击“排序和筛选”菜单下的“自定义排序”命令，进入“排序”对话框。 图335设置“销售额”列的数字格式 排序 图336进入“排序”对话框 （2） 如图337所示，设置“主要关键字”为“图书类别”，“排序依据”为“数值”，“次序”为“升序”。单击“添加条件”按钮，设置“次要关键字”为“销售额（元）”，“排序依据”为“数值”，“次序”为“降序”。单击“确定”按钮，排序结果如图338所示。 3） 计算总销售量及总销售额 在D25单元格输入“总计： ”，在E25单元格插入函数“=SUM(E2:E24)”，如图339所示，计算出总销售量。类似地，在F25单元格插入函数“=SUM(F2:F24)”，如图340所示，计算总的销售额，结果如图341所示。 4） 计算“销售额占比”列 （1） 单击G2单元格，输入公式“=F2/$F$25”。注意，在该公式中引用F2单元格用的是相对引用，引用F25单元格用的是绝对引用，需写成“$F$25”形式，也可使用快捷键F4进行切换，如图342所示。图337设置“排序”方式 图338排序结果 图339用函数计算总销售量图340用函数计算总销售额 图341计算出总销售量及总销售额的结果 图342计算第一条记录的“销售额占比” （2） 然后双击“填充柄”，自动填充所有记录的“销售额占比”值。再设置G列的单元格格式，数字格式类型为“百分比”，“小数位数”为2，如图343所示。完成结果如图344所示。 图343计算设置百分比数字格式