excel全能应用大全ppt课件.ppt

Excel高级应用,目录,第一章Excel基本操作,第二章公式与函数,第三章数据透视表与数据透视图,第四章Excel统计分析功能,第五章Excel与数据库应用,第六章Excel综合应用案例,2,1.1Excel数据录入,1.2格式化工作表,1.3数据有效性设定与规则,第一章Excel基本操作,1.4Excel安全设置与数据保护,1.5冻结窗口,1.6隐藏行与列,3,第一章Excel基本操作,Excel2003是一个操作简单、使用方便、功能强大的电子表格软件，它主要用于对数据的处理、统计分析与计算。了解Excel2003的基本知识是学好该软件的前提，下面将详细介绍Excel2003的基础知识。一、启动Excel2003启动Excel2003有多种方法，下面简单介绍常用的3种方法。,4,第一章Excel基本操作,（1）在Windows的桌面上双击快捷图标，即可启动Excel2003。（2）选择“开始”“所有程序”“MicrosoftOffice”“MicrosoftOfficeExcel2003”命令，即可启动Excel2003。（3）选择“开始”“运行”命令，即可弹出“运行”对话框。在“打开”文本框中输入“Excel.exe”，单击“确定”按钮，即可启动Excel2003应用程序。二.启动后的Excel窗口界面如下图：,5,2003版,6,2010版,快速启动栏,功能区,工作薄.xlsx,7,第一章Excel基本操作,1工作簿Excel2003工作簿是计算和存储数据的文件，每一个工作簿都由多张工作表组成，用户可以在单个文件中管理各种不同类型的信息，默认情况下，新建一个工作薄名称为Book1，一个工作簿包含3张工作表，分别为Sheet1，Sheet2和Sheet3。2工作表用户利用工作表可以对数据进行组织和分析，也可以同时在多张工作表中输入或编辑数据，还可以对不同工作表中的数据进行汇总计算。工作表由单元格组成，横向为行，分别以数字命名，如1，2，3，465536；纵向为列，分别以字母命名，如A，B，C，DIV。,8,第一章Excel基本操作,3单元格Excel2003工作簿最基本的核心就是单元格，它也是Excel工作簿的最小组成单位。单元格可以记录简单的字符或数据。单元格是由行号和列号标识的，如A1，B3，D8，F5等。4工作表标签工作表标签用来标识工作簿中不同的工作表。单击工作表标签，即可迅速切换至相应的工作表中。,9,第一章Excel基本操作,5名称框名称框位于工具栏的下方，用于显示工作表中光标所在单元格的名称。6编辑栏编辑栏用于显示活动单元格的数据和公式。7活动单元格当前处理被选中的状态的单元格。,10,第一章Excel基本操作,三、退出Excel2003退出Excel2003常用以下3种方法：（1）选择菜单栏的“文件/退出”菜单命令。（2）单击标题栏右侧的“关闭”按钮。（3）双击“控制菜单”按钮。,11,1.1Excel数据录入,一、新建工作簿当启动Excel2003时，系统会自动创建一个新的工作簿Book1.xls，并在新建的工作簿中创建3个空的工作表Sheet1，Sheet2和Sheet3。如果要创建新的工作簿，可以采用以下几种方法：（1）选择菜单栏“文件/新建”菜单命令，弹出“新建工作簿”任务窗格，在任务窗格的“新建”选区中单击“空白工作簿”超链接。（2）单击“常用”工具栏中的“新建”按钮。,12,1.1Excel数据录入_2,一、新建工作簿（3）如果想利用模板创建一个工作簿，可以在“新建工作簿”任务窗格的“新建”选区中单击“本机上的模板”超链接，弹出如图所示的“模板”对话框。打开“电子方案表格”选项卡，在其列表框中选择需要的模板,然后单击“确定”按钮。,13,1.1Excel数据录入_3,二、选中单元格对单元格进行各种编辑操作前，必须先将目标单元格选中，使其成为活动单元格，然后才能进行操作。在Excel中，用户可以使用以下8种方法选中目标单元格：,（1）用鼠标单击目标单元格，即可将其选中。（2）单击某个单元格，按住鼠标左键拖动鼠标到另一个单元格后释放鼠标，即可选中以这两个单元格为对角线的矩形区域。,14,1.1Excel数据录入_4,二、选中单元格（3）按住“Ctrl”键的同时依次单击多个不相邻的单元格或矩形区域，即可选中多个不相邻的单元格或矩形区域。（4）单击某个单元格，按住“Shift”键的同时单击另一个单元格，即可选中以这两个单元格为对角线的矩形区域。,（5）单击工作表左侧的行号标签可以选中某一行。（6）单击工作表上方的列号标签可以选中某一列。,15,1.1Excel数据录入_5,二、选中单元格（7）选中某行或某列后按住鼠标左键并进行拖动，即可选中相邻的多行或多列。（8）单击工作表左上角的“全选”按钮，或按”Ctrl+A”可以选中整个工作表。,16,默认情况下，文字左对齐，数字右对齐,若要把数字按照文字输入，输入时需要在数字前加“单引号”,输入分数,在分数前加上前导0及空格,输入日期时，年月日用分隔符“/”或“-”,输入时间时，要使用“：”作分隔符,输入当前日期，按“Ctrl+;”输入当前时间，按“Ctrl+Shift+;”,各种类型数据的输入方法,逻辑型数据的值为TURE和FALSE,17,默认情况下，文字左对齐，数字右对齐,快速输入数据方法在制作电子表格时，通常要在其中输入批量数据。如果一个一个地输入，这将十分麻烦且浪费时间，因此，用户可采取特定的方法来输入大批量的数据，以提高工作效率。,各种类型数据的输入方法_2,1.利用填充命令复制数据方法：单击菜单栏“编辑/填充”菜单命令,18,3.自定义填充序列方法：单击菜单栏“工具/选项”菜单命令，在打开的对话框中选择“自定义序列”选项卡,4.使用序列功能输入数据方法：单击菜单栏“编辑/填充/序列”菜单命令,5.使用“自动更正”简化重复录入文本方法：单击菜单栏“工具/自动更正选项”菜单命令,各种类型数据的输入方法_3,2.利用填充句柄填充数据方法：单击活动单元格右下角的“填充句柄”向下或向右拖动,19,2010版,20,21,例1-1某公司为了进行精确的绩效考核，提高工作效率，需要每天统计A、B、C、D、E五个部门的销售业绩和成本情况，并且要明确标注是否完成任务。对于财务部门的会计而言，每天都需要录入五个部门的销售数据和成本数据，并标注出每个部门是否完成任务。这就意味着每天都要做大量重复性的工作。有没有什么办法可以解决这个重复录入的问题呢？,1.1Excel数据录入_6,22,分析,文本数据部门名称和部门编号数值型数据销售量和成本日期型数据考核日期逻辑型数据是否完成任务,1.1Excel数据录入_7,23,=(销售额-成本)2200,24,从Access等数据库导入数据,从网络导入数据方法,数据导入技巧,25,26,27,28,29,30,设置单元格内容的对齐方式,数字的格式化,表格边框、底纹的设置,1.2格式化工作表,方法：“格式/单元格格式”菜单,字符的格式化,例子,工作表编辑完后，用户可以利用Excel提供的各种格式工具栏和排版命令美化工作表。例如设置背景图案、工作表标签颜色、单元格和表格的边框线等内容。,31,条件格式的设置,1.2格式化工作表_2,方法：“格式/条件格式”菜单命令,例：工作薄文件“家庭支出明细”的“家庭年度支出”工作表记录了某家庭一年的经济支出情况，为了更清楚地显示该家庭本年度的支出情况，请对超过3000元的支出标注红色背景，10003000元之间的支出标注蓝色背景，低于1000元的支出标注绿色背景。,例子,32,2010版,33,目的：为了减少错误、核查数据的正确性而提供的一种数据检验工具，该工具可以设置单元格数据输入的类型和范围，对错误的输入数据进行警告并且拒绝，可以有效防止出现输入错误。,1.3数据有效性设定与规则,34,1.3数据有效性设定与规则_2,步骤：1.设置数据类型和有效范围为了避免错误类型数据的输入，可以限定单元格中输入数据的类型、取值范围或文本字节长度。如果输入的数据不符合限定条件，Excel会拒绝接受输入。2.设置输入提示信息为了方便使用者快捷准确地输入数据，可以设置一个提示信息，告诉使用者不同编号、应输入的数据类型、范围及注意事项等，可以大大提高数据录入的准确性。,35,1.3数据有效性设定与规则_3,步骤：3.设置出错警告信息如果输入的数据不符合设置的数据类型或超出了有效性范围，可以让Excel显示出特定的警告信息，以提醒输入者。,方法：选择“数据/有效性”菜单命令,36,1.3数据有效性设定与规则_3,例：某大学一学期期末考试之后需要统计学生的成绩，为了方便老师们输入时更加准确快捷，需要对成绩表中的数据输入类型及范围设置相应的有效性。具体要求如下：,学号为9位字符，不能有缺失所有学科成绩为0100之间的数字输入成绩时，显示输入提示信息输入错误时显示出错信息,数据表,输入时的提示信息,输入错误时的警告信息,37,2010版,38,1.3数据有效性设定与规则_4,说明：数据有效性的功能可以规定单元格中输入的数据类型和有效范围，防止输入时的错误。但对于已经输入了数据的工作表，可以通过设置单元格的有效性规则，利用数据审核工具，将错误的数据找出并标明，起到查错的作用。,数据表,39,1.3数据有效性设定与规则_5,例:将下面数据表中高等数学、英语、计算机科目的成绩高于100分的数据圈释出来。,数据表,40,1.3数据有效性设定与规则_6,步骤：,1.选定要设定有效性范围的单元格区域C3:E12,2.用设定”数据有效性“的方法设置各门课成绩所在数据区域的数据有效性为0-100之间的整数,3.选择菜单命令“工具/公式审核/显示公式审核工具栏”,4.在“公式审核”工具栏上单击“圈释无效数据”按钮,41,1.3数据有效性设定与规则_7,步骤：,5.圈释后的效果如下图所示,42,2010版,43,1.4Excel安全设置与数据保护,Excel数据表完成之后，通常应根据需要对其进行安全设置，一方面可以保护数据安全，另一方面也便于管理。例如，我们可以为工作薄或工作表设置密码，使有权限的人才能查看；通过锁定单元格，规定哪些区域可以输入数据，哪些区域不能被修改等。Excel的安全设置包括对工作薄的保护、对工作表的数据保护及对工作表中单元格的保护。,44,1.4Excel安全设置与数据保护_2,方法：单击菜单栏“工具/选项”菜单命令，在打开的“选项对话框”中选择“安全性”选项卡，然后设置安全密码。,一.对工作薄的保护,45,2010版,方法一,46,2010版,方法二,47,1.4Excel安全设置与数据保护_3,通常打开一个Excel工作薄时，会出现名为“sheet1”、“sheet2”、“sheet3”三张工作表，这是Excel默认的设置。我们可以根据需要添加新的工作表或者删除无用工作表，并且可以更改工作表的名称。很多情况下，我们只希望对整个工作薄的某一两个工作表设定为只读或隐藏。这时，任何对于该工作表的操作均无效。,二.对工作表的保护,48,1.4Excel安全设置与数据保护_4,方法：单击菜单栏“工具/保护/保护工作表”菜单命令，在弹出的对话框中直接输入密码，单击“确定”按钮后，会弹出“确认密码”对话框，再次输入密码，然后单击“确定”按钮，完成对工作表的保护。这样，以后每次打开该工作表都需要输入密码才能进入。,二.对工作表的保护,若要撤销对工作表的保护，选择菜单命令“工具/保护/撤销工作表保护”,49,方法一,2010版,50,2010版,方法二,51,1.4Excel安全设置与数据保护_5,只有在工作表被保护时，锁定单元格或隐藏公式才有效。在一张Excel表中，可以根据需要设置哪些单元格是只读的，哪些是可以修改的。,三.对单元格的保护,52,1.4Excel安全设置与数据保护_6,三.对单元格的保护,例：将“安全设置与数据保护.xls”中“保护单元格”工作表的学号、姓名两列数据设为不可以修改,其余列数据可以修改。,53,1.4Excel安全设置与数据保护_7,三.对单元格的保护,(1)选定数据区域C3:E12,单击鼠标右键,在弹出的快捷菜单中,选择”设置单元格格式”.,(2)在打开的”单元格格式”对话框中,选择”保护”选项卡,取消”锁定”复选项,然后单击”确定”按钮.,54,1.4Excel安全设置与数据保护_8,三.对单元格的保护,(3)选择”工具/保护/保护工作表”菜单命令.在打开的“保护工作表”对话框中,如图进行设置,设置工作表的保护密码,选定未锁定单元格,55,1.5冻结窗口与拆分,当工作表中数据量比较大时,需要拖动滚动条才能看到所有的数据,但此时用户也许就会不能看到数据所属的列标题,可以采用通过将数据的列标识冻结,使之保持不动.,行冻结方法:单击选择要冻结行的下一行行号,然后单击菜单栏”窗口/冻结窗格”菜单命令,刚在选定行的上方插入一条冻结线.,列冻结方法:单击选择要冻结列的下一列列标,然后单击菜单栏”窗口/冻结窗格”菜单命令,刚在选定列的左侧插入一条冻结线.,56,2010版,57,1.6隐藏行与列,如果工作表中数据量比较大,不需要查看有些行或列,则可以通过隐藏行或列的方式减少对数据的浏览量.,行隐藏与取消行隐藏方法(略),列隐藏方法:在要隐藏列的列标上单击鼠标右键,在弹出的快捷菜单中选择”隐藏”,将该列隐藏.,取消列隐藏方法:将鼠标移向隐藏列的下一列,当光标变为时,单击鼠标右键,在弹出的快捷菜单中选择”取消隐藏”.,58,1.7数据筛选,在实际工作中，经常需要从Excel工作表中找出符合一定条件的几行或几列数据，例如在超市中，要迅速在成百上千条的商品销售记录中查找缺货商品，并且及时补充货源，这就要用到Excel的数据筛选功能。,Excel提供两种不同的筛选方式：自动筛选和高级筛选。,在进行高级筛选前，必须明确数据需要满足的条件，即建立条件区域。,59,60,1.7数据筛选_2,将公式作为筛选条件,例：一家电信公司的客户经理负责开展针对客户的优惠营销活动，在海量的数据集里需要对客户进行精确定位，如此才能找到最有营销价值的客户。请按照下列要求帮他迅速找出符合条件的营销对象：所在城市为北京、上海或杭州用户类别为神州行话费总额大于或等于用户数据表中的平均话费,61,1.7数据筛选_3,解析：,从题目的三个要求来看，条件为或条件，条件为与条件，条件需要应用公式进行计算。因此在建立数据区域的时候就要充分考虑到满足这三个条件。因为条件和条件的筛选在建立条件区域时需要有与数据表的标题行同样的字段，并且在相应的字段下方输入条件，而计算条件的列标题不能与数据表中的列标题相同，这与条件和条件建立条件区域的要求正好相反，所以，可以考虑分两步筛选的方法来实现目标。先筛选条件，再综合考虑条件和条件。,62,63,=average(D3:D9),=D3$C$12,第一步设置筛选条件,第二步高级筛选,注意：用公式作为筛选条件，筛选条件的列标题不能与原数据区域列标题相同,设置筛选原数据区域、条件区域、筛选结果区域,64,第三步再次设置筛选条件,第四步再次高级筛选,这一次的筛选结果作为下一次筛选的原数据区域,65,最终的筛选结果,说明：要将最终筛选结果复制到sheet2工作表中，应在第四步操作时，首先选择sheet2工作表，然后再选择“数据/筛选/高级筛选”菜单命令。,66,第二章公式与函数,在使用Excel处理数据的时候离不开公式和函数。公式的使用使得对工作表的处理更加简单，而函数则是公式使用过程中的一种内部工具，它可以被看做是比较复杂的公式。,67,2.1公式,2.2函数,2.2.2函数的种类,第二章公式与函数,2.2.4日期与时间函数,2.2.6文本函数,2.2.7查找与引用函数,2.2.5逻辑函数,2.2.1函数的输入,2.2.3数学与三角函数,68,在Excel中，公式是对工作表中的数据进行计算和操作的等式。公式中可以包含运算符、单元格引用、区域名称、常量或者函数和括号等，使用公式可以对工作表中的数据进行各种运算。,2.1公式,为D1定义名称“平均值”,69,公式的组成：以等号(=)开头的式子，式子中可以包括以下元素：,2.1公式,值或常量：通过键盘直接输入到单元格的数字或文本,单元格引用：通过使用一些固定的格式引用单元格中的数据,70,公式的组成：以等号(=)开头的式子，式子中可以包括以下元素：,2.1公式,区域名称：直接引用为该区域定义的名称,如为单元格A1定义名称为”日工资”.,工作表函数：包括函数及它们的参数,71,公式的组成：以等号(=)开头的式子，式子中可以包括以下元素：,2.1公式,运算符：连接公式中的基本元素并完成特定计算的符号,72,算术运算符该类运算符能够完成基本的数学运算,2.1公式,73,比较运算符该类运算符能够比较两个或者多个数字、文本串、单元格内容或者函数结果的大小关系,2.1公式,74,文本运算符该类运算符能够将两个文本连接起来合并成一个文本,2.1公式,75,引用运算符该类运算符能够将两个单元格或者区域结合起来生成一个联合引用,2.1公式,76,77,在使用公式和函数计算时，往往需要引用单元格中的数据。通过引用，可以在公式中使用工作表中不同部分的数据，还可以引用不同工作薄中的单元格数据。引用根据样式可以分为A1引用样式和R1C1引用样式，根据地址可以分为相对引用、绝对引用、混合引用及三维引用。,2.1公式,78,A1引用样式此样式通过引用字母和数字标识，在工作表中查找其纵横相交的单元格。它是最常用的引用样式。,2.1公式,79,R1C1引用样式此样式通过使用“R”加行数字和“C”加列数字来确定单元格的位置。,2.1公式,80,R1C1引用样式在没有特殊说明的情况下，系统默认使用的是A1引用样式，如果要使用R1C1引用样式，则需要进行设置。具体步骤如下：,2.1公式,选择菜单栏“工具/选项”菜单命令，打开“选项”对话框,在该对话框中选择“常规”选项卡，按下图所示进行设置,81,2010版,82,相对引用指公式所在的单元格与公式中引用的单元格之间的相对位置，也就是如果公式所在的单元格的位置发生了变化，那么引用的单元格的位置也相应地发生变化。所以当公式被复制或移动后，系统将自动地调整移动后函数的相对引用，使得能够引用相对于当前函数所在单元格位置的其它单元格。,2.1公式,表现形式：直接用单元格所处位置的列标和行号引用单元格特点：如果单元格所处的位置改变，引用也随之改变,83,步骤1：在单元格E3输入公式=B3+C3+D3，然后按回车键,步骤2：计算其他同学的总分不需要再次输入公式。选定E3单元格，将鼠标放在该单元格右下角的填充句柄上，当鼠标变成十形状时按住鼠标左键拖动到E8单元格。这时公式中参数的引用为相对引用，即被引用的单元格区域会自动改变。,E3,E4=,=B3+C3+D3,B,C,+,4,4,4,+D,84,绝对引用指被引用的单元格与公式所在的单元格的位置是绝对，即不管公式被复制到什么位置，公式中所引用的还是原来单元格区域的数据。,2.1公式,表现形式：A1引用样式在单元格的行号和列标前分别加上“$”，如$A$1、$B$2；R1C1引用样式在“R”的后面加行号，在“C”的后面加列号，如R3C4特点：如果单元格所处的位置改变，引用不发生改变,85,例：已知各科成绩和各科成绩在综合评定中所占的比例，要求综合评定成绩。,步骤1：在单元格F3输入公式=B3*$B$11+C3*$C$11+D3*$D$11，然后按回车键,步骤2：计算其他同学的综合评定成绩不需要再次输入公式。选定F3单元格，将鼠标放在该单元格右下角的填充句柄上，当鼠标变成十形状时按住鼠标左键拖动到F8单元格。,F3=B3*$B$11+C3*$C$11+D3*$D$11,F4=B4*$B$11+C4*$C$11+D4*$D$11,86,混合引用介于相对引用和绝对引用之间的引用，也就是说引用单元格的行和列之中一个是相对的，一个是绝对的。具有绝对列和相对行，或是绝对行和相对列两种形式。,2.1公式,表现形式：绝对列相对行$A1相对列绝对行A$1特点：如果单元格所处的位置改变，引用可能发生改变,87,例：已知各科成绩，要求分别求出所有同学各门课平均成绩。,步骤1：在单元格B10输入公式=(B$3+B$4+B$5+B$6+B$7+B$8)/6然后按回车键,步骤2：计算其他各门课的平均成绩不需要再次输入公式。选定B10单元格，将鼠标放在该单元格右下角的填充句柄上，当鼠标变成十形状时按住鼠标左键向右拖动到D10单元格。,B10=(B$3+B$4+B$5+B$6+B$7+B$8)/6,C10=(C$3+C$4+C$5+C$6+C$7+C$8)/6,88,A3,=A$1+$B2,B3=,$1,$B,+,B,2,89,三维引用指引用非当前工作表中的单元格，即其他工作表中的单元格。这些工作表可以与当前工作表处于同一个工作薄中，也可以与当前工作表处于不同的工作薄。,2.1公式,表现形式：工作薄名称工作表名称!单元格名称如：=Book1sheet2!A3=工资管理!B5,若公式与其引用的单元格位于同一个工作薄中，可以省略工作薄的名称,90,函数实际上是Excel预先定义好的公式，每个函数都有相同的结构形式：函数名（参数1，参数2，）参数可以是数字、文本、表达式、单元格或引用区域、数组、区域名称、逻辑值或者是其他的函数。,2.2函数,使用函数可以大大地简化公式，并能实现很多复杂的计算。函数的功能主要如下：可以使一些复杂的公式更易于使用。如=sum(A1:A100)使复杂数学表达式的输入简化，计算复杂的表达式可以使人们在应用中获得一些其他方式无法获得的数据，如INFO()可以返回有关当前操作环境的信息，如内存、操作系统等。,91,函数总是作为一个单元格公式的组成部分来使用。在使用函数时要注意以下几点：,2.2.1函数的输入,函数名可以是小写也可以是大写，Excel总是会将函数名转换成大写。,一定要把函数参数放在括号中，多个参数要用逗号分开，逗号的后面可加空格。,有的函数不带参数，但函数名后必须带括号。,每个函数都有一个语法行，在函数的后面跟上一个左括号时会弹出一个提示框，显示该函数的语法,92,要在工作表中使用函数必须先输入函数。函数的输入有两种常用的方法：手工输入和函数向导,2.2.1函数的输入,1.手工输入函数不用进行过多的操作，但需要用户对输入的函数非常熟悉，包括函数名称和各种对应的参数及类型。,如在E3单元格中输入=sum(B3:D3),然后按回车键确认输入,93,要在工作表中使用函数必须先输入函数。函数的输入有种常用的方法：手工输入和函数向导,2.2.1函数的输入,2.使用函数向导输入对于一些比较复杂的函数或者参数较多的函数，一般使用函数向导来输入。利用函数向导输入函数可以确保输入名称的正确性，同时还可以提供正确的参数次序及参数个数。,94,有函数向导输入函数的具体步骤如下：,2.2.1函数的输入,选要要输入函数的单元格E3,单击菜单栏“插入/函数”菜单命令或者单击编辑栏左侧的“插入函数”按钮，弹出“插入函数”对话框。,95,有函数向导输入函数的具体步骤如下：,2.2.1函数的输入,在“选择函数”列表框中选择所需要的函数，这里选择SUM后在“选择函数”列表框的下面会出现该函数的参数及对函数的简要说明。然后单击“确定”命令按钮。,96,有函数向导输入函数的具体步骤如下：,2.2.1函数的输入,在打开的“函数参数”对话框中输入该函数的参数，如果引用的是单元格或者单元格区域，也可以直接单击后面的“折叠”按钮，直接选择工作表中的区域，然后单击按钮返回到“函数参数”对话框。,设置完成后单击“确定”按钮，即可在选择的单元格中显示出该函数的结果。,97,Excel中的函数大致可以分为11类，分别是数学和三角函数、日期与时间函数、逻辑函数、查找与引用函数、文本函数、信息函数、数据库函数、工程函数、统计函数、财务函数以及用户自定义函数,2.2.2函数的种类,数学和三角函数可以处理简单的计算，如对数字取整的函数INT、计算单元区域中的数值总和的函数SUM或者解决一些复杂计算的函数。,日期与时间函数可以在公式中分析和处理日期值和时间值，例如可以使用Now函数返回当天的日期和时间,98,2.2.2函数的种类_2,逻辑函数可以进行真假值判断或者进行复合检验，例如可以使用IF函数确定条件为真还是假，并由此近回不同的数值。,文本函数可以在公式中处理字符串。例如改变大小写或者确定字符串的长度等操作。,查找与引用函数当需要在数据清单或表格中查找特定的数值，或者需要查找某一个单元格的引用时，可以使用查询和引用工作表函数。例如需要在表格中查找与第一列中的值相匹配的数值，可以使用VLOOKUP工作表函数。,99,2.2.2函数的种类_3,信息函数可以使用信息工作表函数确定存储在单元格中的数据的类型。信息函数包含一组称为IS的工作表函数，在单元格满足条件时返回TRUE.,数据库函数当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于1000且小于2500的行或记录的总数。,工程函数主要用于工程分析。这类函数中的大多数可以分为4种类型：计算机塞尔值或修正后的贝赛尔值的函数，对复数进行处理的函数，在不同的数字系统之间进行数值转换的函数。,100,2.2.2函数的种类_4,统计函数用于对数据区域进行统计分析。例如，统计工作函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和Y值截距，或构成直线的实际点的数值。,财务函数进行一般的财务运算，如确定贷款的支付额、投资的未来值或净现值，以及债卷或息票的价值等。,用户自定义函数如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足时用户可以通过VBA创建自定义函数。,101,2.2.3数学与三角函数,计算绝对值函数ABS格式：ABS(number)含义：返回参数number的绝对值；number可以是数值，也可以是单元格引用,例：某公司测试一台机器分割木材的精确程度。3根木材用于试验切割并测量数据，在测量切割的尺寸时机器切割的实际长度可能与要求长度有误差，要求下表给出差异的大小。,(1)在单元格D3中输入函数=ABS(C3-B3),然后按回车键,(2)拖动D3单元格填充句柄到D5单元格,102,2.2.3数学与三角函数,向下取整函数INT格式：INT(number)含义：将参数number向下舍入取整到最接近的整数,=B2-INT(B2),103,2.2.3数学与三角函数,例：假设某些商品的价格如下表所示，现在只有5000元，如果购买以下商品，请计算出能购买商品的最大数量。,(1)在单元格E3中输入函数=INT(D3/C3),然后按回车键确认输入,(2)拖动单元格E3的填充句柄到E6单元格,104,2.2.3数学与三角函数,取余(取模)函数MOD格式：MOD(number,divisor)含义：返回两数相除的余数，结果的正负号与除数相同。,MOD(12,-5)(1)求MOD(12,5)的值为2(2)用除数|-5|-2=3(3)3的符号与除数相同，所以MOD(12,-5)最终结果为了-3,说明：MOD函数常被用来判断一个数是否能被另一个数整除,105,2.2.3数学与三角函数,计算乘幂函数POWER格式：POWER(number,power)含义：返回底数number的power次幂,106,2.2.3数学与三角函数,计算所有参数的乘积函数PRODUCT格式：PRODUCT(number1,number2,)含义：返回所有以参数形式给出的数字相乘的乘积,例：假设某企业2009年度6月份的销售数量和销售单价如下表所示，用函数计算其销售额,107,2.2.3数学与三角函数,返回随机数-抽样调查函数RAND(自学)格式：RAND()含义：返回大于等于0及小于1的均匀分布随机数，每次计算都将返回一个新值,说明：若要生成a与b之间的随机实数，可以使用公式RAND()*(b-a)+a如果要使用函数RAND生成一个随机数，并且使之不随单元格的计算而改变，可以在编辑栏中输入=RAND(),保持编辑状态，然后按F9键，将公式永久性地改为随机数,108,2.2.3数学与三角函数,例：假设在全班50名同学中以随机方式抽出20名进行计算机水平的普查。,(1)在单元格C1中输入函数开始号码为1,(2)在单元格E1中输入结束号码为50,(3)在单元格B2中输入公式=1+RAND()*49,然后按回车键确认输入,(4)利用自动填充功能将此公式填充式单元格F4中,109,2.2.3数学与三角函数,四舍五入函数ROUND格式：ROUND(number,num_digits)含义：返回按num_digits位数对number进行四舍五入后的值,110,2.2.3数学与三角函数,向下舍入函数ROUNDDOWN格式：ROUNDDDOWN(number,num_digits)含义：返回按num_digits位数对number进行向下舍入后的值,111,2.2.3数学与三角函数,例：假设出租车的计费标准是：每公里单价是3元，不足一公里按一公里计价，以后每公里跳一次表，按下表输入不同的公里数，然后计算其费用。,112,2.2.3数学与三角函数,向上舍入函数ROUNDUP格式：ROUNDUP(number,num_digits)含义：返回按num_digits位数对number进行向上舍入后的值,例：网吧计费系统规定，不满一个单位按照一个单位计算。现假设每30分钟计价0.5元，请计算如下表所示的上网时间所花费的费用。,113,计算上网天数。在单元格D3中输入如下公式=C3-B3，然后按回车键确认输入,(2)拖动单元格D3的填充句柄到单元格D6,(3)选中单元格区域D3:D6，设置单元格的格式为数值,保留两位小数,(4)计算上网分钟数.在单元格E3中输入公式=D3*24*60,然后按回车键确认输入,(5)拖动单元格E3的填充句柄到单元格E6,如图所示,(6)计算上网费用.在单元格G3中输入如下公式=ROUNDUP(E3/30,0)*F3,(7)拖动单元格G3的填充句柄到单元格G6,如图所示,114,2.2.3数学与三角函数,根据指定条件对单元格求和函数SUMIF格式：SUMIF(range,criteria,sum_range)含义：在range单元格区域内对符合criteria条件的sum_range单元格区域求和,range:表示用于条件判断的单元格区域criteria:表示由数字和逻辑表达式等组成的判定条件sum_range:表示要求和的单元格、区域或引用,115,2.2.3数学与三角函数,例：假设某企业生产的空调价格如下表所示，利用SUNIF函数计算销售单价超过3500元的所有空调的销售金额和。,116,根据销售单价和销售数量计算出销售金额。在单元格E3中输入如下公式=C3*D3，然后按回车键确认输入,(2)拖动单元格E3的填充句柄到单元格E8,(3)在单元格B9中输入“销售单价超过3500元的销售金额之和”，然后在元格E9中输入如下函数=SUMIF(C3:C8,”3500”,E3:E8),按回车键确认输入,117,2.2.3数学与三角函数,SUMPRODUCT格式：SUMPRODUCT(array1,array2,array3)含义：在给定的几组数组中将数组间对应的元素相乘，并返回乘积之和,118,2.2.3数学与三角函数,向上舍入取最接近的数函数CEILING(自学)格式：CEILING(number，significance)含义：将参数number向上舍入(沿绝对值增大的方向)为最接近的指定基数significance的整倍数,说明：如果参数number为非数值型，该函数将返回错误值“#VALUE！”无论参数number为正数还是负数，该函数都按照远离0的方向向上舍入。如果参数number已经是significance整倍数，则该函数返回值为number的值如果参数number和significance的符号不同，该函数将返回错误值”#NUM!”,119,2.2.3数学与三角函数,120,2.2.3数学与三角函数,例：假设某企业在2009年16月使用电表的度数如下表所示，利用CEILING函数计算实际交纳的电费数，并且将其舍入为整数,(1)在单元格E3中输入公式=C3*D3,然后按回车键,(2)拖动E3单元格填充句柄到E8单元格,(3)在单元格F3中输入函数=CEILING(E3,1)，然后按回车键确认输入,(4)拖动F3单元格填充句柄到F8单元格,121,2.2.3数学与三角函数,向上取整取最接近的偶数函数EVEN(自学)格式：EVEN(number)含义：可以返回沿绝对值增大方向取整后最接近的偶数,说明：如果参数number为非数值型，该函数将返回错误值“#VALUE！”无论参数number为正数还是负数，该函数都按照远离0的方向向上舍入。如果参数number是恰好是偶数，则函数返回值为number值,122,2.2.3数学与三角函数,向上取整取最接近的奇数函数ODD(自学)格式：ODD(number)含义：可以返回沿绝对值增大方向取整后最接近的奇数,说明：如果参数number为非数值型，该函数将返回错误值“#VALUE！”无论参数number为正数还是负数，该函数都按照远离0的方向向上舍入。如果参数number是恰好是奇数，则函数返回值为number值,123,2.2.3数学与三角函数,向下舍入取最接近的数函数FLOOR(自学)格式：FLOOR(number，significance)含义：将参数number向下舍入(沿绝对值减小的方向)为最接近的指定基数significance的整倍数,说明：如果参数number为非数值型，该函数将返回错误值“#VALUE！”无论参数number为正数还是负数，该函数都按照接近0的方向向下舍入。如果参数number已经是significance的倍数，则该函数返回值为number的值如果参数number和significance的符号不同，该函数将返回错误值”#NUM!”,124,2.2.3数学与三角函数,例：下表为某超市的购物小票，用函数计算购买商品的总金额及实付金额，其中实付金额为舍入到角。,(1)在单元格D7中输入函数=SUN(D3:D6),然后按回车键,(2)在单元格D8中输入函数=FLOOR(D7,0.1)，然后按回车键确认输入,125,使用Excel可以制作各种数据表格，如财务报表、销售报表以及员工时间表等。在制作这些表格的时候经常会包含日期和时间。而在有些包含日期和时间的表格中则需要对此做一些计算，如计算年龄时就要将现在的日期与出生日期相减。,2.2.4日期和时间函数,DATE函数格式：DATE(year,month,day)含义：返回代表特定日期格式。如果单元格的格式设为“常规”，则返回日期的序列号。说明：在MicrosoftExcelforWindows系统中，1900年1月1日的序列号为1,126,2.2.4日期与时间函数,计算日期之差函数DATEDIF格式：DATEDIF(date1，date2，interval)含义：计算结束日期date2与起始日期date1的差值，返回值类型为interval的值,说明：参数date1与date2可以是表示日期的系列数、带引用的日期，也可以是一个函数。date1一定要早于date2参数interval可以包含y、m、d、ym、yd、md等6个值。其中y表示两个日期相差的整年数；m表示两个日期相差的整月数；d表示两个日期相差的天数；ym表示忽略年份的差别而只计算整月数的差；yd表示忽略年份的差别而计算差的天数；md表示忽略年份和月份的差别而只计算差的天数。,127,下表为DATEDIF函数应用举例：,128,下表为DATEDIF函数应用举例：,129,2.2.4日期与时间函数,返回小时数函数HOUR格式：HOUR(serial_number)含义：返回时间值的小时数，其返回值是一个介于0(12:00A.M.)到23(11：00P.M.)之间的整数,例：假设某公司员工出入公司的时间如下表所示，用HOUR函数计算员工的上班小时数,130,2.2.4日期与时间函数,返回分钟数函数MINUTE格式：MINUTE(serial_number)含义：返回时间值的分钟数，它是一个介于0到59之间的整数,131,2.2.4日期与时间函数,返回秒数函数SECOND格式：SECOND(serial_number)含义：返回时间值的秒数，返回的值为0到59之间的整数，参数可以是带引号的文本字符串，十进制数以及其他公式或函数的结果,例：话吧计算系统统计每个电话的时长,132,输入打电话的起始时间和结束时间，如下表所示，在单元格D3中输入公式=HOUR(C3-B3)，然后按回车键确认输入,(2)拖动单元格D3的填充句柄到单元格D6,(3)在单元格E3中输入公式=MINUTE(C3-B3),然后按回车键确认输入,(4)拖动单元格E3的填充句柄到单元格E6,(5)在单元格F3中输入公式=SECOND(C3-B3),然后按回车键确认输入,(6)拖动单元格F3的填充句柄到单元格F6,133,2.2.4日期与时间函数,返回年份函数YEAR格式：YEAR(serial_number)含义：返回日期中的年份，该值是1900年后的整数返回月份数函数MONTH格式：MONTH(serial_number)含义：返回日期中的月份，该值是介于1-12之间的整数返回日函数DAY格式：DAY(serial_number)含义：返回日期中的日，该值是介于1-31之间的整数,134,2.2.4日期与时间函数,返回当前日期时间函数NOW格式：NOW()含义：返回当前系统日期和时间,135,2.2.4日期与时间函数,返回当前日期函数TODAY格式：TODAY()含义：返回当前系统日期,返回时间函数TIME格式：TIME(hour,minute,scond)含义：返回特定时间,136,在处理工作表中的数据时经常需要转换代码或者文本，或者计算字符串的长度，返回特定的字符等，这时就要用到文本函数。文本函数是针对文本串进行一系列相关操作的一类函数，此函数用于处理文本串，改变大小写以及连接文字串等。,2.2.6文本函数,大小写转换函数格式：LOWER(text)UPPER(text)含义：将文本字符串转换成小写或大写文本字符串,137,2.2.6文本函数,首字母大写函数PROPER格式：PROPER(text)含义：可以将文本字符串的英文首字母以及任何非英文字母字符之后的首字母转换成大写，而将其余的字母转换成小写。,138,2.2.6文本函数,合并字符串函数CONCATENATE格式：CONCATENATE(text1，text2.)含义：可以将若干个字符串合并为一个字符串，其功能与“参数row_num,column_num和area_num必须指向reference中的单元格；,151,2.2.7查找与引用函数,MATCH函数格式：MATCH(lookup_value,lookup_array,match_type)含义：用于返回在指定方式下与指定数值匹配的数组中元素的相应位置说明：参数lookup_value表示需要在数据表中查找的数值；lookup_array连续的单元格区域，必须是一行或一列；match_type值为-1、0或者1：若match_type值为1，该函数查找小于或者等于lookup_value的最大数值，此时lookup_array必须按升序排列；若match_type值为0，该函数查找等于lookup_value的第一个数值，此时lookup_array可以按任意顺序排列；若match_type值为-1，该函数查找大于或者等于lookup_value的最小数值，此时lookup_array必须按降序排列；,152,例：已知各地之间相隔的距离如图所示，计算A地和D地之间相隔的距离,153,2.2.7查找与引用函数,LOOKUPHLOOKUPVLOOKUP函数LOOKUP系列函数能够从单行或单列区域或者从一个数组中返回值。这三个函数被用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP；当比较值位于要进行数据查找的左边一列时，使用函数VLOOKUP.,154,2.2.7查找与引用函数,格式：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)含义：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值说明：range_lookup为逻辑值，用以指明该函数是精确匹配还是近似匹配，若为TRUE或省略，则返回近似匹配值，也就是说如果找不到精确匹配值，则返回小于lookup_value的最大数值；或为FALSE，将查找精确匹配值；若range_lookup为TRUE，table_array的第一行的数值则必须按升序排列；若为FALSE，则table_array则不必进行排序；,155,例：假设某班级同学的考试成绩如下图所示，利用HLOOKUP函数查找出第6个同学的语文成绩,在单元格D11中输入如下公式：=HLOOKUP(“语文”,B2:F9,7,FALSE),156,例：下图为员工奖金数据表，不同的销售业绩额对应不同的奖金比例，用HLOOKUP函数查询奖金比例，然后再计算销售奖金,查找适当的奖金比例，在单元格D7中输入如下公式：=HLOOKUP(C7,$B$3:$G$4,2),(2)拖动单元格D7的填充句柄到单元格D10,(3)计算奖金，在单元格E7中输入如下公式：=C7*D7,(4)拖动单元格E7的填充句柄到单元格E10,157,2.2.7查找与引用函数,格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)含义：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值说明：range_lookup为逻辑值，用以指明该函数是精确匹配还是近似匹配，若为TRUE或省略，则返回近似匹配值，也就是