Excel2016基本操作技巧(三).docxVIP

Excel2016基本操作技巧(三)

数据有效性设置

在Excel2016中，数据有效性是一项强大的功能，它可以帮助我们确保输入到单元格中的数据符合特定的条件，从而提高数据的准确性和一致性。

1.设置数据输入范围

假如我们要制作一个学生成绩表，要求输入的成绩必须在0-100分之间。首先，选中需要设置成绩的单元格区域，比如C2:C50（假设学生有50人）。然后，点击“数据”选项卡，在“数据工具”组中找到“数据有效性”按钮并点击。在弹出的“数据有效性”对话框中，“设置”选项卡下，“允许”下拉列表中选择“整数”，“数据”下拉列表中选择“介于”，在“最小值”框中输入“0”，“最大值”框中输入“100”。点击“确定”后，当我们在该区域输入成绩时，如果输入的值不在0-100之间，Excel会弹出错误提示框，阻止不符合要求的数据输入。

2.创建下拉列表

若在制作员工信息表时，部门列的信息是固定的，如“销售部”“技术部”“财务部”等。选中部门列对应的单元格区域，如D2:D100（假设员工有100人）。同样点击“数据”选项卡中的“数据有效性”，在“设置”选项卡的“允许”下拉列表中选择“序列”。在“来源”框中输入“销售部,技术部,财务部”（注意用英文逗号分隔），也可以通过点击“来源”框右侧的折叠按钮，在工作表中选择包含这些部门名称的单元格区域。点击“确定”后，在这些单元格中会出现下拉箭头，点击箭头就可以从下拉列表中选择部门名称，避免手动输入可能出现的错误。

3.设置输入提示信息

为了让用户更清楚应该输入什么样的数据，我们可以设置输入提示信息。还是以学生成绩表为例，选中成绩单元格区域C2:C50，点击“数据有效性”，切换到“输入信息”选项卡。在“标题”框中输入“成绩输入提示”，在“输入信息”框中输入“请输入0-100之间的整数成绩”。点击“确定”后，当鼠标悬停在该区域的单元格上时，会弹出提示框显示我们设置的信息，方便用户正确输入数据。

条件格式应用

条件格式可以根据指定的条件自动更改单元格的外观，使数据的呈现更加直观和易于分析。

1.突出显示特定数值

在销售数据表格中，我们想突出显示销售额大于10000的单元格。选中销售额所在的单元格区域，如E2:E20（假设记录有20条）。点击“开始”选项卡，在“样式”组中找到“条件格式”按钮，选择“突出显示单元格规则”-“大于”。在弹出的对话框中，在左侧框中输入“10000”，右侧下拉列表中选择一种突出显示的格式，如“浅红填充色深红色文本”。点击“确定”后，销售额大于10000的单元格会按照我们设置的格式显示，方便我们快速识别高销售额的数据。

2.使用色阶显示数据分布

在分析学生成绩的分布情况时，我们可以使用色阶来直观展示。选中成绩单元格区域C2:C50，点击“条件格式”，选择“色阶”，可以从预设的色阶样式中选择一种，如“绿-黄-红”色阶。此时，成绩较低的单元格会显示为绿色，中等成绩的单元格显示为黄色，成绩较高的单元格显示为红色，通过颜色的深浅我们可以快速了解成绩的分布情况。

3.基于公式设置条件格式

假设我们有一个包含学生姓名、语文成绩、数学成绩和总分的表格，我们想突出显示总分高于班级平均分的学生。首先，计算出班级平均分，比如在某个空白单元格（如F52）中输入“=AVERAGE(F2:F51)”（假设学生有50人，总分在F列）。然后选中总分所在的单元格区域F2:F51，点击“条件格式”，选择“新建规则”，在“选择规则类型”中选择“使用公式确定要设置格式的单元格”。在“为符合此公式的值设置格式”框中输入“=$F2$F$52”（注意使用绝对引用和相对引用）。点击“格式”按钮，设置我们想要的突出显示格式，如加粗字体和蓝色填充色。点击“确定”后，总分高于班级平均分的学生的总分单元格会按照我们设置的格式显示。

数据透视表与图表结合分析

数据透视表和图表是Excel中强大的数据分析工具，将它们结合使用可以更深入地分析数据。

1.创建数据透视表

以销售数据为例，数据包含日期、产品名称、销售数量、销售额等列。选中数据区域的任意单元格，点击“插入”选项卡，在“表格”组中点击“数据透视表”。在弹出的“创建数据透视表”对话框中，确认数据区域无误后，点击“确定”。在数据透视表字段列表中，将“产品名称”拖到“行”区域，将“销售额”拖到“值”区域。此时，数据透视表会按照产品名称对销售额进行汇总，我们可以快速了解不同产品的销售总额。

2.美化数据透视表

为了让数据透视表更加美观和易读，我们可以进行一些设置。点击数据透视表的任意单元格，在“数据透视表工具”的“设计”选项卡中，我们可以选择不同的报表样式，如“数据透视表样式中等深浅12”