Excel办公应用培训

Excel办公应用培训,目录,一、软件介绍及基本操作,软件介绍,MicrosoftExcel是微软公司的办公软件Microsoftoffice的组件之一，是由Microsoft为Windows和AppleMacintosh操作系统的电脑而编写和运行的一款试算表软件。Excel是微软办公套装软件的一个重要的组成部分，它可以进行各种数据处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。,用于AppleMacintosh的版本包括：1985年ExcelforMacintosh1989年Excel2.2forMacintosh1990年Excel3.01992年Excel4.01993年Excel5.01998年Excel982000年Excel20012001年Excelv.X2004年Excel20042008年Excel20082010年Excel2011,用于MicrosoftWindows的版本包括：1987年Excel2forWindows1990年Excel31992年Excel41993年Excel51995年Excel95-亦称71997年Excel97-亦称81999年Excel2000-亦称92001年ExcelXP-亦称102003年Excel2003-亦称112006年Excel2007-亦称122010年Excel2010-亦称142013年Excel2013-亦称15,软件界面介绍功能区、自定义快速访问栏,一、软件介绍及基本操作,快捷访问工具栏,软件界面介绍功能区、自定义快速访问栏,一、软件介绍及基本操作,功能区开始插入页面布局公式数据审阅视图,显示开发工具选项卡,自定义功能区选项卡,鼠标滑轮滚动快速切换选项卡,软件界面介绍自定义状态栏,一、软件介绍及基本操作,状态栏平均值计数数值计数最大值最小值求和,软件界面介绍文件选项,一、软件介绍及基本操作,新建,打开,另存为,打印,选项,打开文件位置,文件选项卡,软件界面介绍文件选项,一、软件介绍及基本操作,选项设置界面,常用基本操作快捷键,移动到工作簿中下一个工作表：【CTRL】+【PAGEDOWN】,移动到当前数据区域的边缘：【CTRL】+【】,移动到工作簿中前一个工作表：【CTRL】+【PAGEUP】,移动到下一工作簿或窗口：【CTRL】+【F6】或【CTRL】+【TAB】,显示“查找”对话框：【SHIFT】+【F5】或【Ctrl】+【F】,一、软件介绍及基本操作,常用基本操作快捷键,选择整个工作表。如果工作表包含数据，则按【Ctrl】+【A】将选择当前区域，再次按【Ctrl】+【A】将选择整个工作表：【Ctrl】+【A】。,引用单元格时，切换单元格引用模式（A1$A1A$1$A$1）：【F4】,一、软件介绍及基本操作,显示“VisualBasic编辑器”：【ALT】+【F11】,在单元格中折行：【ALT】+【ENTER】,编辑活动单元格并将插入点放在单元格内容的结尾。如果禁止在单元格中进行编辑，它也会将插入点移到编辑栏中：【F2】,常用基本操作视图操作,一、软件介绍及基本操作,冻结窗格,冻结首行,冻结首列,拆分,切换窗口,宏,常用基本操作数据验证,一、软件介绍及基本操作,1.选中设置区域,2.点击“数据”，点击“数据验证”,3.选择“序列“,4.选取或直接填写序列值，点击“确定”,常用基本操作条件格式,一、软件介绍及基本操作,常用基本操作条件格式,一、软件介绍及基本操作,常用基本操作条件格式,一、软件介绍及基本操作,常用基本操作条件格式,一、软件介绍及基本操作,常用基本操作条件格式,一、软件介绍及基本操作,常用基本操作文件加密,一、软件介绍及基本操作,常用基本操作文件加密,一、软件介绍及基本操作,目录,公式功能区简介名称管理器,二、公式及函数应用,1.选中设置区域,2.点击“定义名称”,3.输入自定义名称,4.点击确定,公式功能区简介追踪引用从属单元格,二、公式及函数应用,二、公式及函数应用,公式功能区简介,二、公式及函数应用,公式功能区简介,二、公式及函数应用,IF(logical_test,value_if_true,value_if_false),最多可以使用64个IF函数作为value_if_true和value_if_false参数进行嵌套以构造更详尽的测试。如果IF的任意参数为数组，则在执行IF语句时，将计算数组的每一个元素。,IFERROR(value,value_if_error),如果公式的计算结果错误，则返回您指定的值；否则返回公式的结果。使用IFERROR函数可捕获和处理公式中的错误。如果Value或Value_if_error是空单元格，则IFERROR将其视为空字符串值()。如果Value是数组公式，则IFERROR为value中指定区域的每个单元格返回一个结果数组。,公式功能区简介,二、公式及函数应用,OR(logical1,logical2,.),参数的计算结果必须是逻辑值（如TRUE或FALSE），或者参数必须是包含逻辑值的数组或引用。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的单元格区域未包含逻辑值，则AND函数将返回#VALUE!错误值。可以使用OR数组公式以查看数组中是否出现了某个值。若要输入数组公式，请按Ctrl+Shift+Enter。,AND(logical1,logical2,.),参数的计算结果必须是逻辑值（如TRUE或FALSE），或者参数必须是包含逻辑值的数组或引用。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的单元格区域未包含逻辑值，则AND函数将返回#VALUE!错误值。,公式功能区简介,二、公式及函数应用,FIND(find_text,within_text,start_num),SEARCH(find_text,within_text,start_num),FIND区分大小写，并且不允许使用通配符。如果find_text为空文本()，则FIND会匹配搜索字符串中的首字符（即编号为start_num或1的字符）。,SEARCH函数不区分大小写。如果要执行区分大小写的搜索，可以使用FIND函数。可以在find_text参数中使用通配符（问号(?)和星号(*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在字符前键入波形符()。,公式功能区简介,二、公式及函数应用,LEFT(text,num_chars),RIGHT(text,num_chars),MID(text,start_num,num_chars),LEFT从文本字符串的第一个字符开始返回指定个数的字符。RIGHT根据所指定的字符数返回文本字符串中最后一个或多个字符。Num_chars必须大于或等于零。如果num_chars大于文本长度，则LEFT返回全部文本。如果省略num_chars，则假定其值为1。,返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。,公式功能区简介,二、公式及函数应用,TEXT(value,format_text),VALUE(text),将数值转换为文本，可以使用特殊格式字符串指定显示格式。要以可读性更高的格式显示数字，或要将数字与文本或符号合并时，此函数非常有用。常用占位符：0、#、？日期时间格式符号：m、d、y、h、s,文本可以是MicrosoftExcel可识别的常数、日期或时间格式中的任何一种格式。如果文本不是这些格式中的一种格式，则VALUE返回错误值#VALUE!。由于Excel会根据需要自动将文本转换为数字，因此通常无需在公式中使用VALUE函数。提供此函数是为了与其他电子表格程序兼容。,公式功能区简介,二、公式及函数应用,日注水130方，累计注水15496.6方,本次注水188方本轮累计注水34808方累计注水100912方,日注水:192方,本轮累计注水295方数量少，可修改,=IFERROR(FIND(日注水:,E2,1),IFERROR(FIND(日注水,E2,1),IFERROR(FIND(本次注水,E2,1),0),=IFERROR(FIND(方,E2,F2),0),=IF(IFERROR(FIND(日注水：,E2,1),0),4,IF(IFERROR(FIND(日注水,E2,1),0),3,IF(IFERROR(FIND(本次注水,E2,1),0),4,),=IFERROR(VALUE(MID(E2,F2+H2,G2-F2-H2),),示例：批量提取注水数据,公式功能区简介,二、公式及函数应用,YEAR(serial_number),MONTH(serial_number),DAY(serial_number),DATE(year,month,day),返回对应于某个日期的年份、月份、天数,返回表示特定日期的连续序列号通过将DATE函数与其他函数结合使用，可以将Excel无法识别的日期转换为Excel可识别的序列号。,公式功能区简介,二、公式及函数应用,示例：日期文本转换,公式功能区简介,二、公式及函数应用,TRANSPOSE(array),1.选中输出区域,2.输入transpose函数,3.按下Ctrl+Shift+Enter,24行列转置42步骤：,公式功能区简介,二、公式及函数应用,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),MATCH(lookup_value,lookup_array,match_type),使用VLOOKUP函数搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。在table_array第一列中搜索文本值时，请确保table_array第一列中的数据没有前导空格、尾部空格、直引号（或）与弯引号（或“）不一致或非打印字符。否则，VLOOKUP可能返回不正确或意外的值。,公式功能区简介,二、公式及函数应用,EXP(number),LOG(number,base),LOG10(number),PI(),POWER(number,power),LN(number),返回数字乘幂的结果,可以使用“”代替POWER，以表示基数乘幂运算的幂。,返回e的n次幂。常数e等于2.71828182845904，是自然对数的底数。,根据指定底数返回数字的对数;如果省略base，则假定其值为10。,返回数字以10为底的对数。,返回数字的自然对数。自然对数以常数e(2.71828182845904)为底。,返回数字3.14159265358979（数学常量pi），精确到15个数字。,公式功能区简介,二、公式及函数应用,使用SUMIF函数可以对区域中符合指定条件的值求和SUMIF(A列,判断条件):计算A列中符合判断条件的数值之和SUMIF(A列,判断条件,B列):计算A列符合判断条件时,对应的B列数值之和,SUMIF(range,criteria,sum_range),对区域中满足多个条件的单元格求和。,SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,.),SUMPRODUCT(array1,array2,array3,.),在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。,公式功能区简介,二、公式及函数应用,AVERAGE(number1,number2,.),参数可以是数字或者是包含数字的名称、单元格区域或单元格引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。,AVERAGEA(value1,value2,.),参数可以是下列形式：数值；包含数值的名称、数组或引用；数字的文本表示；或者引用中的逻辑值，例如TRUE和FALSE。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。包含TRUE的参数作为1计算；包含FALSE的参数作为0计算。包含文本的数组或引用参数将作为0（零）计算。空文本()计算为0（零）。,公式功能区简介,二、公式及函数应用,AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,.),如果average_range为空值或文本值，则AVERAGEIFS返回错误值#DIV0!。如果条件区域中的单元格为空，AVERAGEIFS将其视为0值。区域中包含TRUE的单元格计算为1；包含FALSE的单元格计算为0。仅当average_range中的每个单元格满足为其指定的所有相应条件时，才对这些单元格进行平均值计算。AVERAGEIFS中每个criteria_range的大小和形状必须与sum_range相同。,AVERAGEIF(range,criteria,average_range),忽略区域中包含TRUE或FALSE的单元格。如果average_range中的单元格为空单元格，AVERAGEIF将忽略它。如果条件中的单元格为空单元格，AVERAGEIF就会将其视为0值。如果区域中没有满足条件的单元格，AVERAGEIF将返回错误值#DIV/0!。您可以在条件中使用通配符，即问号(?)和星号(*)。Average_range无需与range具备同样的大小和形状。,公式功能区简介,二、公式及函数应用,COUNT(value1,value2,.),COUNTA(value1,value2,.),如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如1），则将被计算在内。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为错误值或不能转换为数字的文本，则不会被计算在内。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将不计算在内。,COUNTA函数计算包含任何类型的信息（包括错误值和空文本()）的单元格。例如，如果区域中包含的公式返回空字符串，COUNTA函数计算该值。COUNTA函数不会对空单元格进行计数。,公式功能区简介,二、公式及函数应用,COUNTBLANK(range),COUNTIF(range,criteria),COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2),包含返回（空文本）的公式的单元格也会计算在内。包含零值的单元格不计算在内。要运行此公式，您需要关闭迭代计算。,您可以在条件中使用通配符，即问号(?)和星号(*)。问号匹配任意单个字符，星号匹配任意一串字符。如果要查找实际的问号或星号，请在字符前键入波形符()。条件不区分大小写；例如，字符串apples和字符串APPLES将匹配相同的单元格。使用COUNTIF函数匹配超过255个字符的字符串时，将返回不正确的结果#VALUE!。,公式功能区简介,二、公式及函数应用,1.点击“开发工具”,2.点击“VisualBasic”,3.点击“插入”,4.点击“模块”,5.输入自定义函数,二、公式及函数应用,使用Excel帮助和搜索引擎查询函数的详细用法,目录,三、分类汇总及数据透视表应用,分类汇总是把数据清单中的数据分门别类地统计处理，不需建立公式，Excel将会自动对各类别的数据进行求和、求平均值等多种计算，并且把汇总的结果以“总计”和“分类汇总”的方式显示出来。只要输入的指令正确，统计的结果马上出来，而且有详有略。,源数据和汇总数据同时存在合并单元格可以进行分类汇总分类汇总对相邻的重复单元格统计分类汇总速度比数据透视表慢,分类汇总,三、分类汇总及数据透视表应用,2.选择分类字段：包括数据源所有字段名称，只能单选,3.选择汇总方式：包括求和、计数、平均值、最大值和一些统计参数，只能单选,4.选定汇总项：包括数据源所有字段名称，可以多选,5.选择执行选项，点击“确定”,1.选择数据，点击“数据”，点击“分类汇总”,三、分类汇总及数据透视表应用,1.选择合并数据范围,2.点击“分类汇总”,3.点击“确定”,4.点击“确定”,三、分类汇总及数据透视表应用,5.选择汇总列,6.点击“查找和选择”,7.点击“定位条件”,8.点击“空值”，点击“确定”,三、分类汇总及数据透视表应用,9.点击“合并后居中”,10.选择筛选列，定位条件选“常量”,11.点击“删除”，点击“删除工作表行”,12.复制筛选列，粘贴格式到源数据列，删除筛选列,三、分类汇总及数据透视表应用,数据透视表（PivotTable）是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关。之所以称为数据透视表，是因为可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。,数据透视表定义,三、分类汇总及数据透视表应用,数据透视表建立数据透视表,三、分类汇总及数据透视表应用,1.选中源数据,2.点击“插入”-“数据透视表”,3.点击“新工作表”,4.点击“确定”,数据透视表操作数据透视表,三、分类汇总及数据透视表应用,5.拖拽字段到筛选器,6.拖拽字段到列,7.拖拽字段到行,8.拖拽字段到值,数据透视表修改汇总方式,三、分类汇总及数据透视表应用,汇总方式：求和、计数、平均值,1.右键单击表头,2.点击“值汇总依据”,3.点击“求和”“计数”“平均值”,数据透视表修改汇总方式,三、分类汇总及数据透视表应用,汇总方式：求和、计数、平均值,右键单击表头-点击“值字段设置”,双击表头,点击值中的汇总项-“值字段设置”,数据透视表修改汇总方式,