《Excel与数据处理（第7版）》全套教学课件

Excel与数据处理第7版第1章Excel应用基础第2章

函数第3章

动态报表与数据查找第4章

数据管理与数据透视表基础第5章

使用Python提高数据处理效率第6章

数据采集与转换第7章

数据清洗、转换与PowerQuery第8章

数据建模与分析PowerPivot第9章

图表与数据可视化全套可编辑PPT课件

第1章Excel应用基础Excel与数据处理第7版本章学习目标1.工作簿、工作表和单元格2.单元格引用3.Excel基本类型数据4.连续编号和相同数据的输入5.大数字编号、多列组合及下拉列表数据的输入6.自定义输入序列7.用RANDBETWEEN、RAND和INT函数生成大批量的仿真数据8.应用单元格样式格式化单元格9.数字自定义格式的妙用10.日期时间类型数据的格式化11.应用数据条、色阶和图标集格式化数据1.1工作表、单元格、公式和引用1.1.1Excel的数据组织和文件格式图1-1Excel数据处理对象的逻辑结构Excel通过工作簿管理文档和数据，在一个工作簿中可以包括许多工作表，工作表分为数据表和图表两种类型，数据表由行、列确定的单元格构成，单元格是Excel处理数据的基本单元，在其中可以存放数字、文本和公式。工作簿、工作表、行、列、图表、单元格区或和单元格等都是对象，可以在程序代码中进行调用。其关系如图1-1所示。Excel2021的常用文件类型及其对应的扩展名格式扩展名说明Excel工作簿.xlsx默认的基于XML的文件格式，不能存储VBA宏代码Excel工作簿（代码）.xlsm基于XML且启用宏的文件格式，可存储VBA宏代码模板.xltx模板的默认文件格式，不能存储VBA宏代码或Excel4.0宏工作表（.xlm）模板（代码）.xltxm模板启用了宏的文件格式，存储VBA宏代码或Excel4.0宏工作表（.xlm）Excel97-2003工作簿.xlsExcel97-2003二进制文件格式（BIFF8）Excel97-2003模板.xltExcel97-2003模板的二进制文件格式（BIFF8）XML电子表格2003.xmlXML电子表格2003文件格式（XMLSS）Excel加载宏.xlam基于XML且启用宏的加载项，支持VBA项目和Excel4.0宏工作表（.xlm）的使用1.1工作表、单元格、公式和引用1.1.2模板、用户界面和工作表结构模板模板即模型、样板，是一种已经建立好的特殊工作簿，已在其中的工作表中设计好了许多功能，如单元格中的字体、字型，工作表的样式和功能（如财务计算，学生成绩统计，会议安排等。微软官网上有许多精美而功能强大的模板，包括个人理财、家庭收支、财务预算、个人简历、会议安排、采购订单、回执和收据、旅行度假、单位考勤、日程安排以及工作计划等。1.1工作表、单元格、公式和引用1.1.2模板、用户界面和工作表结构Excel用户界面由功能区和工作表区两部分组成。1.功能区功能选项卡和逻辑组每个选项卡又分为几个逻辑组（简称组），每个组中集成了完成某项子任务的命令控件。选项卡是动态的，某些选项卡平时隐藏，在执行相应的操作时会自动显示出来。PowerQuery等选项卡须通过设置后才会显示出来每个逻辑组中放置了许多操作命令按镅，以完成某种类型的子任务快速访问工具栏其中包含一组独立于当前所显示的选项卡的命令，无论用户选择哪个选项卡，它将一直显示，为用户提供操作的便利。用户可以根据需要设置其中的工具按钮.

功能区由“文件”菜单和开始、插入、页面公式、数据、视图等选项卡和工具栏构成。快速访问工具栏“文件”菜单单击可进入“BackStage”标题栏窗口最小化、还原和关闭按钮逻辑组命令按钮对话框启动器功能选项卡命令列表按钮1.功能区2.工作表区工作表就是人们常说的电子表格，是Excel中用于存储和处理数据的主要文档。由一些横向和纵向的网格组成，横向的称为行，纵向的称为列。一个工作表最多可有1,048,576行、16,384列数据。当前正在使用的工作表称为活动工作表。工作表标签工作表标签就是工作表的名称。单击工作表标签按钮可使对应的工作表成为活动工作表，双击工作表标签按钮可改变它们的名称。行号、列标题工作表由1048576行组成，每行的编号称为标题（行号）。工作表的每列用英文字母标志，称为列标题。单元格

工作表中的网格，它用列标题和行号定位。如A1就表示Ａ列第１行对应的单元格填充柄活动单元格右下角的小方块，拖动它可以将单元格的内容复制到鼠标拖过的单元格中。单元格区域是指多个连续单元格的组合。表示形式如下：左上角单元格:右下角单元格例如A2:B3代表一个单元格区域，包括A2、B2、A3、B3只包括行号或列号的单元格区域代表整行或整列。如，1:1

表示第一行的全部单元格组成的区域，1:5

表示由第1到5行全部单元格组成的区域；A:A

表示第一列全部单元格组成的区域，A:D

表由A、B、C、D四列的全部单元格组成的区域。2.工作表区全选按钮用于选定整个工作表所有的单元格。插入函数按钮是插入函数按钮，单击它，将弹出“插入函数”对话框，用于向公式中输入函数。名称框用于指示活动单元格的位置。编辑栏用于显示、修改活动单元格的内容2.工作表区工作表查看方式工作表下边框的右侧提供的三个按钮用于切换工作表的查看方式工作表缩放工具1.1.3工作表、单元格操作基础1.工作表操作工作表的切换工作表选择插入工作表复制工作表删除工作表移动工作表修改工作表标签的名字2.选择行或列选择单行：用鼠标单击要选择的行号连续多行：按住Shift，然后单击第1和最后两个行号不连续多行：按住Ctrl，依次单击要选择的行号列的选择方法同行的选择法。

按住Shift单击行号2、3就选中了！1.1.3工作表、单元格操作基础选中连续多行

选中连续多列3.调整行高和列宽调整行高调整列宽双击某列或某行的边线也可以自动调整其适应的行高或列宽。把鼠标移到行号3的下边线上，当指针变成一个黑色“十字架”时，按下并拖动鼠左键，就可增、减该行的行高把鼠标移到B列的右边线上，当指针变成一个黑色“十字架”时，按下并拖动鼠标左键，就可增、减该列的列宽1.1.3工作表、单元格操作基础1634.行或列的隐藏、删除和插入隐藏、删除行或列选中对应的行或列后（可以是多行或多列）并单击右键，然后从弹出的快捷菜单中选择“隐藏”或“删除”命令。取消“隐藏”行或列同时选中包含隐藏行或列在内的区域，右击该区域的任一位置，从弹出的快捷菜单中选择“取消隐藏”命令。插入行或列选中要插入行（或列）的下一行（或列），再右击选中的行或列，从弹出的快捷菜单中选择“插入”命令，Excel将在选中行（或列）的前面增加一新行（或列）。1.1.3工作表、单元格操作基础5.选择单元格或单元格区域1.1.3工作表、单元格操作基础选择单个单元格鼠标单击选择连续多个单元格鼠标连续拖动，或单击区域的左上角单元格，按住Shift键再单击区域右下角单元格选择不连续多个单元格按住Ctrl键，单击所有不连续单元格6.单元格的清除、删除和插入1.1.3工作表、单元格操作基础清除单元格内容删除单元格注意单元格清除与删除的区别插入单元格移动单元格复制单元格【例1.1】如下图，D1单元格中的内容是多余的，从“王汉成”开始的成绩错位了，他的成绩应是45分，其余人的成绩应依次前移；“张自忠”的“高等数学”成绩为89分，但输入漏掉了，输成了后面同学的成绩。1.1.3工作表、单元格操作基础7.单元格合并和对齐方式1.1.3工作表、单元格操作基础【例1.2】利用单元格的合并和对齐方式制作某学院年度奖金表，如下图。选中A1:G1区域，单击“开始”→“对齐方式”→选中A2:G2区域，单击,再单击文本右对齐按钮8.单元格内的数据换行1.1.3工作表、单元格操作基础方法一：在单元格中输入数据时按Alt+Enter组合键方法二：单击“开始”→“对齐方式”→

按钮9.自动缩放单元格内容适应单元格宽度右击单元格→“设置单元格格式…”→“对齐”→“缩小字体填充”1.1.3工作表、单元格操作基础【例1.3】设计某电信公司用户档案，使表格第二行的标题能够自动适应列宽的调整，如下图。1.1.4公式和运算符

1.公式Excel的公式由运算符、数值、字符串、变量和函数组成。公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式。在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。例如：=10+4*6/2+(2+1)*50就是公式，可以在任何单元格中输入此公式．Excel会把公式的计算结果显示在相应的单元格中．1.1.4公式和运算符【例1.4】下图中，单元格B2、C2、C3、J2、J3、K2、K3的内容都是公式。其中，B2中的公式将计算单元格A2和A3中的数值之和，并把计算结果57显示在单元格B2中；单元格C3中的公式计算出30º的正弦值，最后将计算结果0.5显示在C3中，公式中的SIN是Excel中的一个数学函数，计算某个弧度的正弦值；单元格J2中的公式将计算F2:I2区域内所有单元格中的数据之和，最后将计算结果1700显示在J2中，公式中的SUM是Excel中的一个工作表函数，能够计算数据的总和……。单击“公式”→“公式审核”组中的按钮，可以用于公式及公式的值之间的切换。2.运算符及其优先级Excel的公式可以进行各种运算，如算术运算、比较运算、文本运算等。各种运算有不同的运算符和运算次序。运算符运算功能优先级()括号1-负号2%百分号3^乘方4*与/乘、除法5+与-加、减法6&文本连接7=、<、>、<=、>=、<>等于、小于、大于、小于等于、大于等于、不等于8说明括号的运算级别最高，在Excel的公式中只能使用小括号，无中括号和大括号。小括号可以嵌套使用，当有多重小括号时，最内层的表达式优先运算。同等级别的运算符从左到右依次进行。“&”为字符连接运算，其作用是把前后的两个字符串连接为一串。例如，“ADKDKD”&“DKA”的结果为“ADKDKDDKA”，“中国”&“人民”的结果为“中国人民”。“>=”表示大于或等于2.运算符及其优先级引用的概念引用即在公式中用到了其他单元格在表格中的位置。引用的作用在于标识工作表中的单元格或单元格区域，并指明公式中所使用的数据的单元格位置。引用不同工作簿中的单元格称为链接。引用的类型A1引用R1C1引用1.1.5单元格引用A1引用在默认情况下，Excel使用A1引用。在A1引用中，用字母标识列（从A到XFD，共16384列）；用数字标识行（从1到1048576行），这些字母和数字称为列标题和行号。若要引用某个单元格，只需输入列标题和行号。例如，B2引用列B和第2行交叉处的单元格。1.1.5单元格引用R1C1引用在R1C1中，Excel指出了行号在R后面，列标题在C后面的单元格的位置。如“R2C4”是指第2行第4列交叉处的单元格，R6C6则是指第6行第6列交叉处的单元格。一些特例如下表引用含义R[-2]C对活动单元格的同一列、上面两行的单元格的相对引用R[2]C[2]对活动单元格的下面两行、右面两列的单元格的相对引用R2C2对在工作表的第二行、第二列的单元格的绝对引用R[-1]对活动单元格整个上面一行单元格区域的相对引用R对当前行的绝对引用1.1.5单元格引用1.相对引用相对引用也称为相对地址引用，是指在一个公式中直接用单元格的列标与行号来取用某个单元格中的内容。1.1.5单元格引用在单元格F2中输入公式B2+C2将公式复制到单元格G4中，则G4中的公式为C4+D4相对引用的例子【例1.5】某班某次期末考试成绩如图所示，计算各同学的总分，即F列的数据。在F2计算公式=C2+D2+E2然后向下复制该公式！1.1.5单元格引用2.绝对引用绝对引用总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。

绝对引用的形式是在引用单元格的列号与行号前面加“$”符号。比如，$A$1就是对A1单元格的绝对引用。1.1.5单元格引用绝对引用案例【例1.6】某品牌皮鞋批发商3月份的销售数据如图所示，每双皮鞋的单价相同，计算各皮鞋代销商场应支付的总金额，。在D6输入公式：=$C$3*C6然后向下复制此公式！1.1.5单元格引用3.混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。比如，$A1、$B1

案例1.1.5单元格引用4.三维引用对同一工作簿内不同工作表中相同引用位置的单元格或区域的引用称为三维引用。引用形式为：Sheet1:Sheetn!单元格（区域）例如，Sheet1:sheet8!C5和Sheet1:Sheet8!B2:D6都是三维引用，前者包括Sheet1~sheet8这8个工作表中每个工作表的C5单元格，后者包括此8个工作表中每个工作表的B2:D6区域。1.1.5单元格引用5.内部引用与外部引用如果引用同一工作表中的单元格就称为内部引用，引用不同工作表中的单元格就称为外部引用1）引用相同工作表中的单元格，例： =G3+G5+G10*102）引用同一工作簿不同工作表中的单元格，例 =Sheet1!G3+Sheet2!G5+Sheet2!E273）引用已打开的不同工作簿中的单元格，例： =[Book1]Sheet1!$IL$4+[Book2]Sheet2!$E$74）引用未打开的不同工作簿中的单元格，例如：='C:\dk\[Book1.xlsx]Sheet2'!$B$4+'C:\dk\[Book1.xlsx]Sheet1'!$C$65）同一公式中存在几中不同的引用，例： =[Book1]Sheet1!$A$4+Sheet1!G7+F91.1.5单元格引用1.2数据输入和编辑基础概述数据输入是许多工作人员不得不面对的问题。针对不同类型和规律的数据采用不同的输入方法，不仅能减少数据输入的工作量，还能保障输入数据的正确性。如果不了解掌握不同类型数据的输入方法，可能就要成天座在电脑前，一五一十地输入数据，输完了还要检查。某些人要几天才能输入完成的数据，但有的人几分钟就完成了，而且数据不会出错！这就是为什么要掌握数据输入方法的原因！1.2.1Excel的数据类型Excel的数据类型包括：数字型、日期型、文本型、逻辑型数据四种数字型表现形式多样：货币、小数、百分数、科学计数法、各种编号、邮政编码、电话号码等多种形式【例1.7】Excel中数值型数据各种表现形式的示例1.Excel常见数据类型2.Excel数值型数据的取值范围功能最大限制数字精度15位单元格中可键入的最大数值9.99999999999999E+307最大正数1.79769313486231E+308最小负数-2.2251E-308最小正数2.229E-308最大负数-1E-3071.2.1Excel的数据类型数据输入并非易事，请看下面的例子【例1.8】建立如下图的电信公司用户档案。如果不讲技巧，一字不误的输入，费时费力。本章将介绍一些数据输入技术，应用这些技术可以减少数据输入过程的不少麻烦如何输入客户编号、月租费、保险号、缴费账号才是最快捷的呢？1.2.2基本数据类型的输入1.2.2基本数据类型的输入1.输入文本类型数据字符文本应逐字输入数字文本以‘开头输入，或用=“数字”方式输入。输入文本32，可输入：=“32”

或输入：'32输入的文本标点符号较多时，选择功能区中的“插入”→“符号”→按钮1.2.2基本数据类型的输入2.输入数值型数据（1）正数的输入：+234，13,333,333，12E3（2）负数的输入：-234，(234)，-12e2（3）小数的输入：纯小数可以省略前面的0（4）分数的输入:22/3,03/4（5）货币数据的输入:￥123，$21

数值在单元格中默认的对齐方式是右对齐。1.2.2基本数据类型的输入3.输入日期输入年月日的格式为“年/月/日”或“年-月-日”输入时间的格式为“时:分”或“时:分:秒”通过格式化得到其它形式的日期，这样可减少输入单击“开始”“数字”组的对话框启动器的箭头，弹出图示的“单元格格式”设置对话框1.2.3用Tab和Enter键输入选定区域的数据4.用TAB和ENTER输入选定区域的数据【例1.9】某老师将某门课程每位同学的各项成绩数据在纸质表中整理好了，需要依次输入到工作表中，如下图。用TAB和ENTER可以按行或列快速切换到下一个输入单元格1.2.4输入相同数据1.通过填充输入相同数据区域建立具有相同数据的不同工作表，可用复制方法复制一班表头【例1.10】为高三年级的5个班建立如图1-17所示的成绩表。1.2.4输入相同数据2.同时在多个工作表中直接输入相同数据【例1.11】某学院有37个班，每个班的学生档案表如下图所示，其中每个表的结构相同，包括学号、姓名、入学时间、性别、出生年月、班级、专业和备注等信息。单击工作表“2班”，再单击右三角形，直到最后的工作表名“37班”可见，按住Shift键，再单击“37班”，即选中“2班:37班”对应的36个工作表依次输入A1:H1中的表头内容1.2.4输入相同数据3.用Ctrl+Enter组合键输入相同数据用鼠标选中要输入相同数据的单元格区域（可以是多个不连续的独立单元格）。输入数据，输入后不是按Enter键，而是先按住Ctrl键再按Enter键，则所有被选中的单元格中都会输入相同的数据。1.2.4输入相同数据4.用填充柄输入相同数据在G2中输入“计算机通信”，然后Enter。单击G2，G2右下角会出现黑色小方块，它就是填充柄。向下拖动填充柄，拖过的单元格都被填入了“计算机通信”。双击此填充柄可自动复制生成G列数据5.双击填充柄快速输入相同数据

双击单元格G2的填充柄也会在G3:G11区域中填写与G2中相同的数据1.3编号的输入1.3.1连续编号的输入1.通过复制生成连续编号【例1.12】建立如下图所示的成绩表，其中1003020101～1003020200是100个连续的学号。2.用填充序列生成连续编号填充序列方式适用于输入较多的连续编号数据。【例1.13】某电信公司有电话号码档案，其中的电话号码为62460000～62480000，共有20000个号码，假设将电话号码62460000放在单元格A3中，62460001放在单元格A4中，然后依次向下，试快速输入这些电话号码。在A3中输入起始号码单击“开始”→“编辑”组右侧的下三角形。弹出“序列”对话框，在Excel弹出的“序列”对话框中指定“列”在步长中输入1在终止值中输入最后一个号码62480000。1.3.1连续编号的输入1.3.2利用自定义格式产生特殊编号【例1.14】某商场销售某品牌电器统计表，产品编号是唯一的，如下图所示。1、选中D列要设置格式的单元格区域2、在“开始”选项卡中，单击右边的箭头，Excel会弹出单元格格式设置对话框，。3、选中“数字”→“自定义”选项4、输入自定格式：PKQ-0000-00001.3.3超长数字编号的输入当在单元格中输入一个位数较多的数值时（比如超过15位的整数），Excel会自动将输入的数据显示为科学记数法，这可能并不符合我们的需要。产生这种问题的原因是数值精度问题，Excel的默认精度是15位，如果一个数值的长度超过15位，则15位之后的数字都被视为0。这种位数较多的大数字编号，可以用自定义格式进行输入,或者用文本形式输入

1.3.3超长数字编号的输入（1）“‘”输入法（2）文本格式设置法（3）自定义格式输入法右图所示的商场用户档案，其中客户编号为21位，且前18位全部相同。若直接输入，则Excel会显示为3.57E20解决方法是定义G列保存身份证号的单元格的自定义格式为："356800993102155129"000选中A3单元格，单击

“开始”选项卡“数字”组右下功的下箭头，弹出设置单元格格式对话框“类型”文本框中输入："356800993102155129"0001.3.3超长数字编号的输入1.3.4用“&”组合多个单元格【例1.15】下图客户资料表中的“区域代码”有17位数字，“顾客编号”从9001开始编号，“顾客身份识别码”由同行的“区域代码”和“顾客编号”组合而成，有20位。“&”为字符连接运算，其作用是把前后的两个字符串（也可以是数值）连接为一个字符串。如“ADKDKD”&“DKA”=ADKDKDDKA。123&45&678=“'12345678”

设下图E列数据由C、D列数据组合而成。在E3中输入公式=C3&D3，然后向下复制！1.用&运算符组合数据2.用&和文本函数的结合&常和文本函数left、right、mid等合用，这几个函数都很简单，其调用语法如下：left(text,n)right(text,n)mid(text，n1,n2)其中：left截取text文本左边n个字符；比如left("1234",2)="12"right截取text文本右边的n个字符；比如right("1234",2)="34"mid从text的第n1字符开始，取n2个字符；如：MID("1234",2,2)="23"1.3.4用“&”组合多个单元格1.3.4用“&”组合多个单元格3.用&和文本函数的结合案例某电话公司的缴费帐号由身份证号的后15位（全长18位），后接电话号码构成。如下图所示。用&和文本函数可以很快建立缴费帐号。在I3输入公式“=Right(G3,17)&H3”，然后向下填充复制该公式到I列的其余单元格1.4采用下拉列表选择输入数据1.4.1通过“数据验证”创建数据输入下拉列表【例1.16】有教师信息表如图所示，D列的职称包括助教、讲师、副教授、教授、助工、工程师、高工和工人等下拉列表的适用范围项目个数少而规范的数据，比如职称、工种、单位及产品类型等，这类数据适宜采用Excel的“数据验证”检验方式，以下拉列表的方式输入。（1）选中要建立下拉列表的单元格区域。（2）单击“数据”→“数据工具”→“数据验证”控件（3）选择“设置”标签，然后从“允许”下拉列表中选择“序列”选项。（4）单击“来源”文本框右侧的按钮，然后选择预先输入了职称数据的单元格区域A2:A9。或是直接在“来源”文本框中输入职称名字“助教,讲师,副教授,教授……”

1.4采用下拉列表选择输入数据1.4.1通过“数据验证”创建数据输入下拉列表1.4.2利用右键快捷菜单从下拉列表中进行数据输入在输入某列数据时，如果所有可能出现的数据在该列前面的单元格中都已出现过了，后面的输入只是某个数据的重复，就可以利用鼠标右键菜单建立下拉列表进行数据的选择输入。1.4.3

利用窗体组合框创建下拉式列表利用“开发工具”中的表单控件，可以设计高质量的数据采集表，比如市场调查问卷，而且有利于收集数据的汇总统计。【例1.17】建立某学校的职工信息登记表（如图所示），D5单元格的职称中包括教授、副教授、教授级高工、高工、讲师、工程师、助教和助工等。用窗体组合框输入职称！1.4.3

利用窗体组合框创建下拉式列表窗体组合框建立过程（1）单击“开发工具”→“插入”→窗体组合框控件；（2）用出现的“+”指针在D5单元格中画出一个窗体组合框；（3）右击绘制的窗体组合框，选择“设置控件格式”，弹出“设置控件格式”对话框；（4）单击“数据源区域”输入“辅助输入数据表”的A2:A9区域，在“单元格链接”中输入D5，并选中“三维阴影”。1.4.4用数据验证设计二级下拉列表二级下拉列表概念二级下拉式列表是指第二级下拉列表的内容是以第一级下拉列表为依据确定的。例如在下图中，每个学院的部门是固定的，当学院选定之后，仅列出该学院的部门列表以供选择，用二级下拉式列表输入这类数据是最便捷的。现为图“所在学院”和“部门”建立二级下拉式列表输入。第一级菜单第二级菜单二级下拉列表建立过程建立二级菜单辅助工作表：1.一级菜单学院名称在首行2.二级菜单学院的部门名称在对应的列中3.指定D1:G5的首行为名称。方法：①选定D1:G5区域；②单击“公式”→“定义的名称”→“根据所选内容创建”；③在弹出的“以选定区域创建名称”对话框中，选中“首行”复选框；1.4.4用数据验证设计二级下拉列表1.5设置受限输入数据对输入到单元格中的数据进行一定程度的限制，比如限制小数位数、日期和时间的范围、字符的个数等。当输入的数据不符合限定规则时，Excel就显示一些警告信息，并且不接受输入的数据。如果工作表的数据是由其他人输入的，则有必要在别人输入数据时给出一些简要的提示信息，或对数据输入的规则作些说明。【例1.18】图1-31是某俱乐部的会员档案表，其中某些列的数据输入必须满足指定的条件才能接受。具体要求是：①A列的用户编号是以BL开头，总长度为6位字符的编码；②C列的身份证长度必须为18位，多一位或少一位都不行；③D列的出身日期必须是1980年1月1日到1989年12月31日之间的人才能加入80俱乐部；④I列活动支出总金额不能超出H1中的预算金额。在向此工作表输入数据时，当输入数据不满足对应列的上述限定，就显示出错误信息。1.5设置受限输入数据①通过“数据验证”设置必须输入长度固定且包含特定文字的内容在“公式”的文本框中输入：=AND(LEN(A3)=6,COUNTIF(A3,"BL*")=1)1.5设置受限输入数据②限定身份证长度必须为18位；③出身日期：1980年1月1日~1989年12月31日之间④活动支出总金额不能超出H1中的预算金额。1.5设置受限输入数据1.6利用填充序列输入数据1.6.1内置序列的输入1.数据序列的含义序列就是一组数据，这组数据有先后顺序，内容固定，在应用中常常以一组数据的整体形式出现。2、Excel内置序列对于经常使用的一些数据序列，比如月份、星期、季度等，Excel已经将它们内置在系统中，在输入这些数据时，只需要输入第一个数据，其余的可以采用填充复制的方法由Excel自动产生。1.6.2自定义序列用户可以将经常使用的数据序列添加到Excel系统中，该序列就会拥有与Excel内置序列相同功能。比如，输入该序列的第一项内容后，其后各项都可以通过填充复制产生。案例【例1.20】某大学有“计算机学院，通信学院，经济管理学院，法律学院，中药学院，电子技术学完”等多所学院，学校办公室经常要用到这些学院名称。现以此为例说明Excel自定义序列的建立方法。建立方法一（1）在工作表的某行或某列中输入自定义序列的内容，如在A列中输入各学院的名称，如图1-39的单元格区域A1:A9所示.（2）单击“文件”按钮→“Excel选项”→“高级”→“编辑自定义列表”。弹出图示的“自定义序列”对话框。1.6.2自定义序列（3）在“导入”按钮前面的文本框中输入“$A$1:$A:$9”，即自定义序列所在的单元格区域A1:A9，然后单击“导入”按钮。建立方法二（1）单击文件按钮→“Excel选项”→“高级”→“编辑自定义列表”。弹出图示的“自定义序列”对话框。（2）在“输入序列”中依次输入序列内容，单击“添加”按钮完成添加。（3）建立输入序列后，输入第一项，即“计算机学院”后，通过填充复制就能产生其它各项内容。1.6.2自定义序列1.7行列转置输入某些时候需要将工作表的行数据作为列数据输入，或将列数据作为新表的行数据。这样的工作表数据可通过行列转置快捷生成。【例1.21】某品牌电视2008年的销售统计数据如图2.30所示。其中的A、B两列数据是以前输入在工作表中的各省销售排名，在A列中包括有中国全部省份的名称。现在要构造D1：AA16的统计表1.7行列转置输入1、选中A列的省份名称所在的单元格区域A4:A34。2、在“开始”选项卡上的“剪贴板”组中，单击“复制”按钮。3、单击E4单元格4、在“开始”选项卡上的“剪贴板”组中，单击下面的三角形，然后从弹出的快捷菜单中选择“转置”，这样就完成了E4:AA4中的省份名称的输入。

1.7行列转置输入【例1.22】建立下图中A2:D8区域的行列转换矩阵。矩阵涉及到较多的数据，它由紧密相连的一组单元格区域构成，矩阵的运算比较复杂，Excel提供了一组矩阵运算的函数。1、选中A2:D8。2、在“开始”选项卡上的“剪贴板”组中，单击“复制”按钮。3、单击G3单元格4、在“开始”选项卡上的“剪贴板”组中，单击下面的三角形，然后从弹出的快捷菜单中选择“转置”，这样就完成了G3:M6中的矩阵输入。1.8利用随机函数产生批量仿真数据1.Rand函数又称为随机函数，它产生一个介于0~1之间小数。对Rand函数的结果进行放大，能产生任意范围内的数据。

RAND()*100：可以产生0~100之间的数据RAND()*50+50：可以产生50~100之间的数据2.RANDBETWEEN函数RANDBETWEEN(bottom,top)，产生介于bottom和top之间的任意整数。1.8利用随机函数产生批量仿真数据【例1.23】用RAND函数建立如图1-43所示的学生成绩表，成绩为10～100之间的数字，精确到小数点后2位，用RANDBETWEEN函数建立图1-44所示的学生成绩表，成绩为整数。(1)选中C2:G11区域，输入公式：ROUND(RAND()*90+10,2)，再按Ctrl+Enter(3)选中单元格区域C2:J11，输入公式：=RANDBETWEEN(20,100)，按Ctrl+Enter说明：用rand产生的数据会随时发生变化，可将它贴粘为数据，它就不会发生变化了。其方法是：（1）选中用随机函数产生的数据区域，如图2.28中的C2:J11。然后单击“开始”选项卡中“剪贴板”组中的复制按钮。（2）在“开始”选项卡上的“剪贴板”组中，单击下面的三角形，从弹出的快捷菜单中选择“粘贴值”。1.9编辑工作表数据1.复制和粘贴2.剪切和移动3.选择性粘贴单元格或单元格区域具有许多内容如字体、字型、边框、色彩、单元格中的值、公式及有效性验证等，当对单元格进行复制和粘贴时，操作的是单元格或区域的全部内容。单元格的各个单项内容可以通过选择性粘贴进行单项粘贴，这项功能非常实用，也常用到！字型字体边框色彩公式底纹……1.9编辑工作表数据(1)粘贴值、字体、格式等单项内容D列的产品单价是用RANDBETWEEN随机函数生成的20~100之间的随机数；C列是RANDBETWEEN产生的20~500之间的随机数；当任一单元格的数据有变化时，随机数都会发生变化！通过复制和粘贴值可将区域C3:D13粘贴为纯数字，这些数值就不会变化了！1.9编辑工作表数据【例1.24】某商场在工作表中保存每天的销售记录，同一工作表中可能保存多天的销售记录，如图1-45所示。单元格区域A1:E13是2018年4月18日的销售记录，H1:I13是2018年4月19日的销售记录。这些数据都可以通过复制产生单

项含

义全部粘贴所复制数据对应的所有单元格内容和格式公式仅粘贴在所复制数据对应单元格中的公式数值仅粘贴在所复制单元格或区域中显示的数据的值格式仅粘贴所复制数据对应单元格的单元格格式批注仅粘贴附加到所复制的单元格的批注验证将所复制的单元格的数据有效性验证规则粘贴到粘贴区域所有使用源主题的单元粘贴使用复制数据应用的文档主题格式的所有单元格内容边框除外粘贴应用到所复制的单元格的所有单元格内容和格式，边框除外列宽将所复制的某一列或某个列区域的宽度粘贴到另一列或另一个列区域公式和数字格式仅粘贴所复制的单元格中的公式和所有数字格式选项值和数字格式仅粘贴所复制的单元格中的值和所有数字格式选项选择性粘贴”对话框中各粘贴单项的含义1.9编辑工作表数据1.9编辑工作表数据（2）粘贴运算1、复制A2:I22、选中A4:I43、“选择性粘贴”运算中的“乘”运算后，A4：I4的结果1.9编辑工作表数据（3）粘贴链接链接即连接，它不是将源单元格或区域中的数据或公式复制到目标单元格或区域中，而是建立两者之间的链接，并通过链接在目标单元格或区域中显示源单元格或区域中的内容。当链接源单元格或区域中的数据发生变化时，目标单格或区域中的数据也会发生变化。链接在汇总不同工作表、工作簿中的数据时非常有用。在企业数据的分析过程中，可能会涉及到对多个用户或部门的提交的不同工作簿的数据汇总到一个汇总工作簿中，应用链接来实现这样的操作非常有效。因为通过链接形成的汇总报表，当源工作簿发生更改时，链接汇总工作簿中的数据会被Excel自动修改，使之与源工作簿中的数据同步。【例1.25】某电视机厂将每个季度的销售数据保存在一个工作表中，在年末时需要制作汇总表，将每个季度的数据汇总在一个表中，并统计年度销售总数，如图所示。链接链接链接链接链接1.9编辑工作表数据Excel的格式化功能具有以下特性：应用了格式的单元格会一直保持其设置的格式，直到重新为它设置新格式或删除该格式；编辑一个单元格的内容（如输入、修改或删除单元格的数据）时，不会影响单元格原有的格式；当复制或剪切一个单元格（或区域），然后将它们粘贴到其他单元格（区域）时，单元格的格式也会一起被传递。Excel的格式化功能十分强大，涉及的内容非常多，包括对工作表，单元格，表格线的样式和美化，以及文本、数字、日期等不同数据类型的格式化。1.10数据格式化1.10.1格式化数字1.系统提供的数字格式数字、日期、时间等类型的数据在Excel中都是以数字形式存储的，同样的数字在具有不同格式的单元格中会表现出不同的形式。【例1.26】图中第3行所有单元格均输入的是10.3，第2行对应单元格是对它采用的格式。由此可见，同一数字的表现形式何其多也！1.10.1格式化数字Excel提供了许多数据格式，包括数据的精确度、显示方式等内容（货币：美元、欧元、人民币，百分比等）。增加或减少小数位数设置百分比显示方式分节显示数据货币数据显示方式单击此下三角形会弹出“设置单元格格式”对话框1.10.1格式化数字数据格式设置案例——货币形式的数据1.10.1格式化数字【例1.27】如下图，B3:E6区域的所有单元格中都是纯数字，利用数字格式化功能将它们设置成了会计数据和百分比形式。2.单元格自定义格式（1）自定义格式的意义自定义格式允许用户修改单元格的格式，对单元格中数据的显示形式、字体、字型以及单元格的边框、背景等内容进行设置。通过自定义格式不但可以将单元格数据显示为需要的样式，而且能够在输入数据的时候显示一些重要的提示信息，以减少输入数据的出错率。此外，通过自定义格式的设置，还能减少单元格内重复数据的输入。1.10.1格式化数字（2）自定义格式的形式格式组成正数格式码；负数格式码；0的格式码；文本的格式码说明①格式代码中最多可以指定四个节。各节以分号分隔，它们顺序定义了单元格显示正数、负数、零和文本时的格式。如果只指定了两个节，则第一部分用于表示正数和零，第二部分用于表示负数。如果只指定一个节，则所有数字都会使用该格式。1.10.1格式化数字2.单元格自定义格式1.10.1格式化数字②在格式码中指定的字符被用来作为占位符或格式指示符。0作为一个占位符，它在没有任何数字被显示的位置上显示一个0。③符号“_)”跟在一个正数的格式后面，以保证这个正数右边会留下一个空格，空格的宽度与圆括号“）”的宽度一样宽。④正数、负数和0靠单元格的右边线对齐。⑤如果要跳过某一节，则对该节仅使用分号即可。比如，若将单元格的格式设置为：#,###.00;;“零”;则当在此单元格中输入负数时，输入的负数就不会被显示出来（被隐藏了）。1.10.1格式化数字【例1.28】下图是用自定义格式显示数据的一个例子，本例概括了数字自定义格式的主要用途。格式符功能及用法通用格式对未格式化的单元格使用默认格式，在列宽许可的情况下，尽可能地显示较高的精度，在列宽不许可的情况下，使用科学记数法显示数值#用作数字的占位符，如果没有数字输入，就不显示一个0；设置为“#S”显示的数据，它右边的数字以四舍五入的形式处理。例如，以“$#,###.##”的形式显示数字233.5667，则该数字显示为“$233.57”；输入“

233.5667”，则显示为“

$233.57”；输入“45.6”，该数字就显示为“$45.6”0用作数字的占位符，如果没有数字输入，就显示一个0；以“0S”格式表示十进制小数，右边的数字按四舍五入进行处理。如果以“#,##0.00”格式显示233.5667，则该数字显示为“235.57”；

233.5667显示为“

233.57”；45.6显示为“45.60”？当设置为等宽字体（如CourierNew）时，为无意义的零在小数点两边添加空格；可以对具有不等长数字的小数使用。例如，A1:A3设置为“???.??”格式，则这几个单元格中数字的小数点将对齐-跳过下划线后面所跟的一个字符宽度。如在正数的格式后跟“_)”，则在显示正数后，将留一个“)”字符的空白，如在正数的格式后跟“_W”，则在显示该数值后将留一个“W”字符所占宽度的空白.小数点的位置，指示千的位置，只需在第一个千的位置指示即可3.Excel提供的自定义格式控制符1.10.1格式化数字3.Excel提供的自定义格式控制符1.10.1格式化数字格式符功能及用法%把输入的数字乘以100，然后以百分数的形式显示出来E+，E-指示数值以科学计数法显示/把数字设置为分数显示方式，“/”作为分子分母的间隔符，如格式“#/#”，则“0.5”被显示为“1/2”\后面的字符将被显示在输入的数字前面，这种格式在显示一些编码时较有用。如对于自定义格式“\zk#”，输入数字234，将被显示为“zk234”"text"显示双引号内的文本。如设置自定义格式为“"33981"0#”，输入1将被显示为“3398101”；输入22，将被显示为“3398122”*用跟随在“*”后的文字填充剩余的列宽。如设置格式为“#*美元”，则输入78，将被显示为“78美元”@如果包含文本格式部分，则文本部分总是数字格式的最后部分。若要在数字格式中包括文本部分，可在要显示输入单元格中的文本的地方加入“@”[颜色]/[颜色n]用指定的颜色格式化单元格内容，可以是[黑色]、[白色]、[红色]、[青色]、[蓝色]、[黄色]、[洋红色]、[绿色]，如在定义了[红色]格式的单元格中输入的内容将以红色显示；[颜色n]是指定用调色板上的颜色格式化单元格内容，n可以是0~56中的一个数字4.应用举例1.10.1格式化数字【例1.29】自定义格式的综合应用。在图1-54中，A列是C列同行单元格设置的自定义格式，B列是在C列中输入的数据，C列是输入数据后的显示结果。例如，在C6中设置的自定义格式为“zk#”，当在C6中输入7575时，最终该数据显示为“zk7575”，其中数字7575前的字符“zk”是由Excel根据自定义格式加上去的。自定义格式的方法（1）选中要格式化的单元格。（2）单击“开始”|“数字”组右边的下三角形（3）选择弹出对话框的“数字”标签，然后选择“分类”列表框中的“自定义”选项。（4）在“类型”文本框中，输入自定义格式。（5）在单元格中输入数据，数据将以自定义格式显示。1.10.1格式化数字1.10.2文本格式化函数TEXT自定义格式存在的问题通过“设置单元格格式”对话框中的命令来将数字格式化为文本的方式存在一定的局限性。比如，只有整个单元格都为数字时才起作用，而且自定义格式所改变的仅仅是单元格的显示外观，其本质还是数字本身，仍然可以进行加、减等运算。再如，如果单元格中是以单引号“'”开头的强制为文本的数字，单元格格式化方法对它并不适用。文本格式化函数Text可以解决上述问题。Text的功能Text是将数字格式化为真正文本的函数，在需要以可读性更高的格式显示数字或需要合并数字、文本或符号时，此函数很有用。Text的用法TEXT(value,format_text)value是要格式化为文本的内容，它可以是数值、计算结果为数值的公式，或是内容为数值的单元格引用。1.10.2文本格式化函数TEXTformat_text是用双引号括起来作为文本字符串的数字格式码，它可以是表1-5中除了“*”和“[颜色代码]”之外的其它格式码，例如"#,##0.00"。例如，假设单元格A1含有数字123.5，公式=TEXT(A1,"￥0.00")的结果为：¥123.50=TEXT(A1,"￥0.00")&"每小时"的结果为：¥123.50每小时1.10.2文本格式化函数TEXT1.10.2文本格式化函数TEXT【例1.30】用Text函数设置小数或分数的对齐方式用TEXT函数进行本格式化设置，如将数字格式化为文本，设置小数或分数的对齐方式等内容，如图用text将数字转换成文本用text设置小数点对齐用text转换成分数形式用—text将文本数字转换成真正数字1．用TEXT函数设置小数和分数对齐方式2．用TEXT函数将格式化后的文本数字转换成数值型数字3.用Text将数字格式化为中文大小写数字【例1.31】某单位差旅发票如图1-56所示，当在此票据中填写D列和E列的发票张数和金额后，其余数据由Excel通过公式自动计算生成。在D11中输入大写金额生成的公式：=TEXT(E11*100,"[dbnum2]0拾0万0仟0佰0拾0元0角0分")Text中可用[dbnum2]格式码设计中文大字，用[dbnum1]设置中文小写格式：=TEXT(7052.24,"[dbnum1]")的结果为

七千○五十二.二四=TEXT(705224,“[dbnum1]0拾0万0仟0佰0拾0元0角0分”)的结果为：○拾○万七仟○佰五拾二元二角四分1.10.2文本格式化函数TEXT1.10.3格式化日期1.概述在Excel中，日期和时间实际上是以序列数字的形式存储，以日期形式显示的。所以当一个单元格的格式被设置为日期格式后，即使在其中输入纯粹的数字，该数字也会被显示为一个日期。【例1.32】下图是日期的各种中文格式的应用示例。1.10.3格式化日期2.日期的格式码1.10.3格式化日期3.日期格式化案例1.10.3格式化日期【例1.33】在图1-60中，B列和G列是已输入的日期和时间数据，利用TEXT函数、日期和时间的格式码进行日期、时间和文本格式的日期时间之间的转换。C3：=--TEXT(B3,"0-00-00")D3：=TEXT(C3,"yyyymmdd")E3：=--TEXT(C3,"yyyy-mm-dd")F3：=TEXT(E3,"yyyy-mm-dd")H3：=TEXT(G3,"h:m:s")I3：=TEXT(H3,"hhmmss")J3：=--TEXT(G3,"h:m:s")在对应列向下复制上述单元格公式1.10.4条件格式化1.条件格式概述条件格式是指基于某种条件更改单元格区域中数据的表现形式，如果条件成立，就基于该条件设置单元格区域的格式（如设置单元格的背景、用图形符号表示数据），否则不进行格式化。在Excel2021中，应用条件格式更容易达到以下效果：突出显示所关注的单元格或区域，强调异常值，使用数据条、颜色刻度和图标集来直观地显示数据。总体而言，对单元格数据进行条件格式设置有两种方法：一种是根据单元格的值进行条件格式设置，另一种是根据单元格中的公式进行条件格式设置。1.10.4条件格式化【例1.34】某中药材公司2018年的药材销售利润表如图1-61所示。在图1-61所示工作表中突出显示以下信息：利润小于20的药材销售情况，销售利润最高或最低的10种药材是哪些，用条型图直观显示出各药材在各省的销售情况，用不同的图形显示出各药材在各省的销售情况，标识出利润占前30%的药材销售情况。在Excel2021中，通过条件格式可以轻松地实现上述要求。条件格式位于“开始”选项卡的“样式”组中，单击该组中的“条件格式”按钮，将显示条件格式的命令列表，如下页图示。1.10.4条件格式化Excel2021条件格式的内容1.10.4条件格式化突出显示利润小于20的药材用数据条显示单元格数据突出显示利润10%的药材用图标集和色阶显示不同比例部分的数据1.10.4条件格式化（1）突出显示单元格规则对单元格区域设置一定的条件，将按指定的现则突出显示该区域中满足条件的单元格内容，默认的规则用某种色彩填充单元格背景。例如，如果单元格的值大于、小于或等于某个指定值，就将单元格的背景填充为绿色，突出显示该单元格中的值。（2）项目选取规则对选中单元格区域中小于或大于某个给定阈值的单元格实施条件格式。单击此规则中的“值最大的10项”、“值最大的10%项”、“高于平均值”等条件，Excel都会显示出一个条件设置对话框，可通过它设置单元格中的条件格式。1.10.4条件格式化（3）数据条数据条以彩色条型图直观地显示单元格数据。数据条的长度代表单元格中的数值。数据条越长，表示值越高，数据条越短，表示值越低。（4）色阶色阶用颜色的深浅表示数据的分布和变化，包括双色阶和三色阶。双色刻度使用两种颜色的深浅程度比较某个区域的单元格。颜色的深浅表示值的高低。（5）图标集使用图标集可以对数据进行注释，并可以按阈值将数据分为三到五个类别。每个图标代表一个值的范围，其形状或颜色表示的是当前单元格中的值相对于使用了条件格式的单元格区域中的值的比例。2.条件格式规则1.10.4条件格式化Excel2021中的条件格式是根据一组规则来实施完成的，这组规则称为条件格式规则。条件格式规则类型基于各自值设置所有单元格的格式只为包含以下内容的单元格设置格式”仅对排名靠前或靠后的数值设置格式仅对高于或低于平均值的数值设置格式仅对唯一值或重复值设置格式使用公式确定要设置格式的单元格单击“开始”选项卡中的“条件格式”按钮，从弹出的菜单中选择“新建规则”1.10.4条件格式化3.自定义条件格式（1）设置用不同比例显示数据的自定义格式【例1.35】某学院采用学评教制度，学生对其任课教师进行评分，某次学评教成绩如图1-68所示。要求：对该工作表进行格式化，标志出学评教分数最差的1/3，最好的15%（如图E列所示），以及中间的学评教成绩，以便奖励或惩罚。√最好的15%×最后的20%！中间的65%1.10.4条件格式化（1）选择要建立条件格式的区域，单击“开始”→“样式”组→“条件格式”→“新建规则”命令。弹出图示“新建格式规则”对话框。（2）在“格式样式”列表中选择条件格式类型（3）在“类型（T）”的列表中选择“百分点值”，然后在“值”下面的文框中输入值的范围，如图所示。

3.自定义条件格式（2）用自定义格式对单元格区域进行隔行着色1.10.4条件格式化3.自定义条件格式【例1.36】某学生成绩表如图1-70左图所示。对它进行格式化，如图1-70右图所示：成绩隔行着色，用红色粗体显示0～60之间的成绩，对90～100之间的单元格设置背景填充图案。条件格式规则1：成绩隔行着色，用红色粗体显示0～60之间的成绩条件格式规则2：90-100分填充图案背景（5）设置90分以上的填充图案：选中C3:F12区域，“开始”→“条件格式”→“突出显示单元格规则”→“大于”，输入“90”,“自定义格式”-“填充”-“图案样式”，选择一个背景填充图案（1）选中A3:E12区域，单击“开始”→“条件格式”→“新建规则”命令，弹出“新建格式规则”对话框。（2）选中“使用公式确定要设置格式的单元格”，然后在“为符合此公式的值设置格式”文本框中输入判定单元格是否为奇数行的公式“=MOD(ROW(),2)=1”。（3）单击“格式”按钮，弹出“设置单元格格式”对话框，选择其中的“填充”标签，然后在“背景色”调色板中选择一种颜色，作为奇数行单元格的背景色。

（4）设置60分以下条件格式：选中C3:F12区域，“开始”→“条件格式”→“突出显示单元格规则”→“小于”，输入“60”,“自定义格式”-“字体”

→“字形”→“加粗倾斜”，选择红色1.10.4条件格式化4.条件格式规则的管理(1)条件格式的限制条件格式最多可以包含64个条件。(2)条件格式的优先级当对相同的单元格或区域设置了多重条件后，有可能引起条件规则的冲突。例如，一个规则将单元格字体颜色设置为红色，而另一个规则将单元格字体颜色设置为绿色。因为这两个规则冲突，所以只应用一个规则。发生冲突后到底应用哪个规则呢？要根据各条件规则的优先级来确定(3)条件格式规则管理条件规则的优先级可通过“条件格式规则管理器”对话框进行设置和管理，通过此对话框，还可以在工作簿中创建、编辑、删除和查看所有条件格式规则。1.10.4条件格式化

1、本题应用了图标集、色阶、数据条等多重条件2、单击“开始”→“样式”组→“条件格式”旁边的箭头→“管理规则”。Excel将弹出“条件格式规则管理器”对话框3、选择“新建规则”或以建立新的规则；选择“编辑规则”可对条件格式进行修改5、单击上、下箭头可以提高或降低当前稳定中条件格式规则的优先级4、条件格式规则：这些规则都是应用于同一单元格或区域的，排在前面的优先级高；排在后面的优级低，可以通过“删除规则”右边的上下箭头移动规则，更改优先级本章小结用Excel建立的表格文件以工作簿的形式存放在磁盘上，工作簿由一个或多个工作表组成，一个工作表可以保存人们日常工作中的一个表格。工作表的最小组成单位是单元格，数据都存放在单元格中。公式是Excel计算工作表数据的主要方法，在公式中可以通过引用获取单元格中的数值，引用包括相对引用、绝对引用和混合引用。不同类型的数据具有不同的输入方法，相同的数据最好通过复制或利用公式产生，连续的编号或序号可利用填充的方法产生。自定义格式对于单元格的数据输入、数据显示非常重要，在输入大量有规律的数据时，通过单元格自定义格式的设置可以简化输入。此外，用户可以利用自定义格式，对输入到单元格中的数据进行简单的正确性检验。TheEnd谢谢大家！第2章函数Excel与数据处理第7版认识函数好比EXCEL的武林秘笈，如果要成为EXCEL高手，谁都必须学习它。函数也是EXCEL与Word之类软件中的表格的主要区别，它使EXCEL具有强大的运算能力。EXCEL提供了涉及各个不同领域的函数接近400个。运用它们解决工作中的实际问题，能够极大提高工作效率，使枯燥的工作变得轻松、简单。但学习是一个枯燥而艰辛的过程！当然嘛，谁让你想成为高手呢？学习目标1.名称及其应用2.求和、计数和平均值函数3.最大、最小、排名和百分比统计函数4.条件判定、条件计数和条件求和函数5.用数学函数和随机函数生成仿真数据6.日期、文本函数及其应用7.巧用错误信息函数2.1

名称2.1.1名称的定义

可以给一个单元格或单元格区域取一个名称，这个名称可以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。名称其实是一个标识符，由字母或下划线开头的一个或多个字符组成。名称不区分字母的大小写，比如ABC、abc、Abc都是同一个名称。在Excel中，还可以用汉字作为名称。可以为同一单元格或单元格区域定义多个不同的名称。名称一旦定义就可以在同一工作薄的不同工作表之间共用。在Excel中，同一个工作簿中的名称是共享的，在一个工作表中定义的名称，可以被另一个工作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的名称在不同的工作表中查找到正确的单元格或单元格区域。2.1.1

名称的定义在Excel中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单元格组合定义一个名称（或多个名称）定义名称有多种方法，如定义、粘贴、指定或标志等1.使用定义方法定义名称【例2.1】下图中，B2单元格被命名为“存款利率”，在D5中输入的公式是“=C5*存款利率”，其中的“存款利率”就是B2单元格的名称。

B2单元格的名称定义方法如下：2.1.1

名称的定义（1）单击“公式”|“定义的名称”组中|“定义名称”命令按钮.（2）在弹出的“新建名称”对话框的“引用位置”输入要定义名称的单元格引用位置。（3）在“在当前工作簿中的名称”中输入名称2.1.1

名称的定义2.使用指定的方式定义名称“指定”的方式一次可定义多个名称：可以将表格的首行或首列指定为相应的列或行的名称。方法选中要指定名称的单元格区域。单击“公式”|“定义的名称”组中|“根据所选内容创建”命令按钮。在弹出的“以选定区域创建名称”对话框中，选中“首行”、“最左列”的复选框。这样就把该表的首行、首列指定成了相应的列、行的名称2.1.1

名称的定义指定首行、最左列为名称的案例【例2.2】下图是某建筑工地的工人奖金统计表，一共有几百个工人，其中的某些工人会随时查询自己的奖金，这就需要在A列的几百行数据中进行查找。名字能够很好地解决这类问题，方法如下。2.1.1

名称的定义3.用名称栏定义名称工作表全选按钮（工作表列标A左边的按钮）上面的编辑框称为名称栏，它实时显示当前单元格的名称或引用。可以用来定义名称。【例2.3】下图中，C4:C8区域中输入的是数组公式：“=数量*单价”，其定义方法请见图中的标注。名称栏2.1.1

名称的定义4.定义常量名称用名字代表常量，如圆周率3.14159，自然对数的底数e等，然后在整个工作簿中引用这些常量名，会给数据处理带来许多方便，这使用户在运用这些数据时不必关心它所在的工作表或单元格，直接用其名字就可以了。常量名称的另一个好处:当常量值变化时,只需要修改常量定义就行了,不必须在各具工作表的公式中寻找常量值并进行修改.2.1.1

名称的定义常量名称的定义【例2.4】下图是用名字常量pi=3.14计算圆周长和圆面积的简单例子。在名称中输入名称指定名称的有效范畴输入名称对应的公式或常数直接应用了常量名称Pi计算圆的周长和面积2.1.1

名称的定义1.引用另一个工作表中的名称定义一个工作薄级的名字后，该名字就能被本工作簿中的各个工作表直接引用。【例2.5】图2-6是某蔬菜超市的销售记录。该超市把蔬菜单价存放在一张工作表中，如图2-6（a）所示，每种蔬菜的销售记录则保存在不同的工作表中，如图2-6（b）所示。现以小白菜的销售金额计算为例，说明名字的跨表引用问题。2.1.2

名称应用举例通过名称查找蔬菜单价指定A2:B12最左边为名称2.1.2

名称应用举例某蔬菜商在一个工作表中保存蔬菜的单价，在另一工作表中保存销售记录，现要查找销售记录表中各蔬菜的单价。指定A2:B11区域的“最左列”为名称在D2中输入公式：=INDIRECT(B2)*C2向下复制该公式！2.1.2

名称应用举例指定A2:B12的最左列为名称；用名称查找蔬菜单价，见D列；若不用名称，单价要从C列的几百个数据中去查找！

这种方法虽然比直接从工作表的单元格中查找数据方便了不少，但输入量仍然较大。用它处理成千上万行的数据查询仍然低效率。最佳方法是用名称和Indire