Excel培训教程副本.pptx

Excel2007培训教程,目 录,PART1：Excel2007基础知识PART2：数据输入与编辑PART3：Excel函数基础PART4：数据管理工具,1.Excel2007基础知识,PART 1,1.1 Excel2007概述,Excel用户水平的5个层次,1.2 Excel2007的工作界面,Office按钮功能区编辑栏快速访问工具栏状态栏,1.3 EXCEL2007信息结构,工作簿（Book），Excel运算和储存数据的文件，扩展名是.xls。在一个工作簿中可以包含多个工作表，默认包含3个工作表，名称是Sheet1、Sheet2、Sheet3。工作表（Sheet），相当于工作簿（一本书）中的一页，是储存、处理数据的主要空间。一个工作表最多可以包含1048576行（1-1048576）、16384列（A-XFD）。单元格（Cell）及单元格区域（Range），单元格是组成Excel工作簿的最小单位。每个单元格用其所在的列标和行号标识，称为单元格地址，如A3等。在工作表的众多单元格中，用户正在操作的单元格称为活动单元格或当前单元格。而单元格区域是指由若干个单元格（可以连续，也可以不连续）构成的区域。,（1）工作簿的基本操作,（1）插入（2）删除（3）重命名（4）移动或复制（5）保护工作表（6）隐藏工作表（7）为工作表标签设置颜色（8）拆分、冻结工作表,（2）工作表的基本操作,（1）创建工作簿（2）打开工作簿（3）保存工作簿（4）关闭工作簿,（3）单元格的基本操作,（1）选定单元格（2）移动和复制单元格（3）命名单元格（4）合并单元格（5）隐藏单元格（6）在单元格输入数据（7）单元格的格式化（8）为单元格添加批注（9）设置单元格的保护措施,1.3.1 单元格区域的选取方法,1.选择多个连续单元格:先选左上角单元格，然后按shift键，再选定右下角单元格；2.选择多个不连续单元格：先选第一个单元格，然后按住ctrl键,然后再依次单击其他单元格；3.选择一个单元格区域：一可在地址栏直接输入该区域，然后按回车；二可用鼠标左键选定该区域左上角单元格，并一直拖动至区域右下角单元格；4.利用全选钮可以选择全部单元格；5.如果要选择正在处理的整个单元格区域，按“Ctrl+Shift+*”。,1.3.2 单元格区域的表示方法,1矩形区域的表示：A2:D5（冒号）2单行、单列、若干行、若干列区域：6:6 H:H 3:5 D:F 3两区域的公共部分：（交集）(B4:K6 D1:F8)（空格）4多个区域：（并集）（A1:B5,K8,9:9,H:J）（逗号)5同一工作簿不同工作表的单元格：(感叹号)=SUM(Sheet1!A2:B4,Sheet2!F5)6不同工作簿的单元格的表示：(方括号)(工作簿文件名工作表!单元格),基础知识 实例,例1：区域表示,如当前工作簿名称为abcc.xlsx，其Sheet2工作表中的单元格E37的内容为公式:=SUM(Sheet3!D5,G8,练习题_函数.xlsxSheet2!B3)，则单元格E37的内容涉及_个工作表？A）1 B）2 C）3 D）4,例2：区域表示,区域(A2:K8 D:E)是包含多少个单元格？,2.Excel数据的输入与编辑,PART2,1.文本型；文本是字符或者任何数字和字符的组合，没有大小。输入到单元格内的任何字符集，只要不被系统解释成数字、公式、日期、时间或者逻辑值，则Excel 2007一律将其视为文本，系统默认的对齐方式是单元格内靠左对齐。2.数值型；即有大小，可以进行各种计算的数据。数字、日期、时间、逻辑型等都属于数值型。数值型表现形式多样：货币、小数、百分数、科学计数法、各种编号、邮政编码、电话号码等，系统默认的对齐方式是单元格内靠右对齐。3.公式；公式是由“=”、“运算符”和“单元格地址”组成；单元格存储的是公式的计算结果；,2.1 Excel2007的数据类型,2.2 输入数据,文本数据是能够输入计算机的各种符号（包括空格）。将数字作为文本输入时,在数字前加单引号或加等号和双引号。如输入邮政编码100038，应输入100038，或输入“100038”，其单元格的结果为字符型的100038。对已经输入的数字要作文本处理时，可以对单元格的格式进行设置。即在格式菜单中选择单元格命令即可设置。一个单元格最多可输入32767个字符，但默认只显示8个字符,超出的字符覆盖在右侧的单元格上,但实际仍然只占一个单元格。,2.2.1文本数据输入,2.2.2 数值数据输入,输入普通数字时，可以采用普通记数法和科学记数法。如12345或1.2345E4；0.012或.012；输入负数时，应在输入的数字前输入减号“”或将负数置入括号中，如输入234，可以直接输入234或输入(234)。输入分数时，为避免将输入的分数视作日期，应在分数前键入0(零)+空格。如输入“0 1/2”则单元格结果为1/2。输入上下标，如录入M3，可先输入M3，然后选取3，打开单元格格式对话框，选择上标即可；,问题：1.如果直接输入“1/5”会是什么结果？2.输入身份证时常见的问题。,1.日期的输入格式为：“年/月/日”或“月/日”；2.时间的输入格式为：“时：分”；3.如果要输入当天的日期,可按Ctrl+；(分号)；如果要输入当前的时间,可按Ctrl+Shift+：(冒号)。,2.2.3 输入日期与时间,第16页,第17页,1.输入公式时,需在公式前输入“”，如输入25，则应在单元格中输入“25”，输入后显示结果。2.输入公式时所有符号均要求是英文状态下的半角字符，不区分大小写。,2.2.4 输入公式,2.2.5 数据有效性,我们可以通过设置，控制单元格可接受数据的类型，以便有效地减少和避免输入数据的错误。比如可以在某个时间单元格中设置“有效条件”为“时间”，那么该单元格只接受时间格式的输入，如果输入其他字符，则会显示错误信息。,2.3 填充数据,在同一行或列中自动填充数据的方法很简单，只需选中包含填充数据的单元格，然后用鼠标拖动填充柄(位于选定区域右下角的小黑色方块。将鼠标指针指向填充柄时，鼠标指针更改为黑色十字形状)，经过需要填充数据的单元格后释放鼠标即可。,2.3.1同一行（列）的填充数据,2.3.2 填充一系列数字、日期或其他项目,在Excel 2007中，可以自动填充一系列的数字、日期或其他数据，比如在第一个单元格中输入了“一月”，那么使用自动填充功能，可以将其后的单元格自动填充为“二月”、“三月”、“四月”等。,2.3.3 手动控制创建序列,在“开始”选项卡的“编辑”组中，单击“填充”按钮旁的倒三角按钮，在弹出的快捷菜单中选择“系列”命令，打开“序列”对话框。在“序列产生在”、“类型”、“日期单位”选项区域中选择需要的选项，然后在“预测趋势”、“步长值”和“终止值”等选项中进行选择，单击“确定”按钮即可。,2.4 查找和替换数据,如果需要在工作表中查找一些特定的字符串，那么查看每个单元格就过于麻烦，特别是在一份较大的工作表或工作簿中。使用Excel提供的查找和替换功能可以方便地查找和替换需要的内容。,2.4.1 查找与某种格式匹配的单元格,在Excel中，经常需要查找某些数据。在Excel中既可以查找出包含相同内容的所有单元格，也可以查找出与活动单元格中内容不匹配的单元格。它的应用进一步提高了编辑和处理数据的效率。,2.4.2 替换文字或数字,通常，查找文字或数字是为了改错或者成批替换，以便将某些内容替换为其他的内容。,注意：默认状态下，如果查找字符A，会查找到所有包含A的单元格（不区分大小写）；如果只查找包含字符的A单元格，则需要选择选项按钮，选中单元格匹配选项；,若想查看工作表上与查找内容有关的数据，可不必关闭“查找”窗口。只要将鼠标指针指向该窗口的标题栏处,按住鼠标左键不放,拖动鼠标,把窗口拖动到其他区域即可。技巧：在不能准确地知道查找内容所包含的字符时,可以使用通配符。通配符有两种,一种是“？”,其作用是可代替任意一个字符;另一种是“*”,其作用是可代替任意一个或多个字符。如果用户要查找的内容本身含有这两个符号,就必须在这些字符的前面加上“”符号。例如,要查找的字符串为“帮助？”，在键入查找内容时需输入“帮助？”。,2.4.3通配符查找与替换数据,1.利用格式工具栏上的相应按钮。2.执行单元格|格式命令，打开单元格格式对齐对话框，然后根据需要设置。,第26页,2.5 单元格格式化,2.6.1 引用的概念 引用即在公式中用到了其他单元格在表格中的位置。引用的作用在于标识工作表中的单元格或单元格区域，并指明公式中所使用的数据的单元格位置。注：引用不同工作簿中的单元格称为链接。2.6.2 引用的样式A1样式R1C1样式,2.6 单元格引用,相对地址：由列标、行号组成。如：D3、G6（公式中单元格地址，随公式位置的移动而自动调整)。绝对地址：列标和行号前分别加“$”。如：$D$3、$G$6（公式中引用的单元格地址是固定不变的)。混合地址：列标或行号前加“$”。如：$D3、G$6（公式中引用的单方向地址是固定不变的)。,2.6.3 单元格引用方式,注：在单元格引用中不区分大写。按F4键可以在不同引用方式之间切换。,单元格地址引用练习,说明下述单元格地址引用的含义1.“=$A$4+Sheet3!B23”2.“=Sum(Sheet1:Sheet3!G6)”3.“=Sum(Sheet2:Sheet6!B2:E7)”4.如图一，在A5单元格输入如下公式，并将公式填充至A5:D7区域，计算个单元格结果。,图一,图二,图三,单元格地址引用练习,5.在图1中，在A5单元格输入如下公式，并将公式填充至A5：D7区域，计算个单元格结果。,图1,图2,图3,3.Excel2007函数基础,PART 3,Excel中的函数与数学中的函数概念是不同的，是指Excel已经定义好了的一些特殊公式，它们可以对一个或多个数据进行计算，然后把计算的结果存放在某个单元格中。按照来源，可分为内置函数和扩展函数两类。前者只要启动Excel，就可以使用它们，而后者必须通过选择工具|加载宏命令加载，然后才能使用。例如SUM表示返回单元格区域中所有数值的和；AVERAGE表示计算参数的算术平均值等等。,3.1 Excel函数概述,=（B2+B3+B4+B5+B6+B7+B8+B9+B10）/9,=Average（B2:B10）,使用函数不仅可以实现各类比较抽象、复杂的运算，还可以避免使用公式时，键入一长串的计算过程所带来的不便，从而提高了计算速度和准确性，改变了传统的计算方式。例如：,3.1.1Excel函数的意义,3.1.2 EXCEL函数的语法格式,一个函数的表达式是由三部分组成：“=”、“函数名”、“(计算区域)”例如：=SUM(Number1,Number2）=：表示执行计算操作；函数名：具体的运算法则，一般是英文单词的缩写表示；计算区域:表示参与计算的数值或单元格区域,也称参数区域；,1.函数必须以等号作为计算的开始；2.一个函数只有唯一的名称，并决定了函数的功能和用途，用户不能自行定义；3.括号应紧跟在函数名称的后面，当括号中有多项参数时，用逗号隔开，当括号中有省略号（.）时，表明可以有多个该种类型的参数参与计算；没有参数的函数也必须加上括号。4.参数是函数最复杂的组成部分，它规定了函数的运算对象、顺序和结构等等。参数可以是常量、逻辑值、数组、单元格引用，也可以是其他一个或多个函数。参数的类型和位置必须满足函数语法的要求。,3.1.3 函数的相关说明,Excel有300多个函数，分类如下:数学与三角函数【60个】（例如，SUM,SUMIF,MOD）财务函数【52个】（例如，PV,FV,PMT）日期和时间函数【20个】（例如，DAY,YEAR）统计函数【80个】（例如，AVERAGE,COUNTIF,LARGE）查找与引用函数【17个】（例如，VLOOKUP,CHOOSE,INDEX）数据库函数【13个】（例如，DCOUNT,DMIN）文本函数【28个】（例如，LEN,LEFT,CONCATENAT）逻辑函数【6个】（例如，IF,AND,OR）信息函数【9个】（例如，CELL,ISBLANK,TYPE）工程函数【39个】（例如，Oct2bin）用户自定义函数,2.2 EXCEL2007 函数分类,2.3.1 IF函数功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。格式：IF(logical_test,value_if_true,value_if_false)IF(逻辑表达式,真值,假值)AND(逻辑判断式1,逻辑判断式2,.)说明：Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。Value_if_true即logical_test为TRUE时返回的值。Value_if_false即logical_test为FALSE时返回的值。,2.3 函数举例,（1）IF函数单条件判断返回文本,如图，某单位检查预算执行情况，如果超出预算在执行情况处显示“超支”，否则显示“正常”；,第39页,如图，某公司计算销售提成，对销售额大于30000元的按照20%计提，对小于30000元的按照10%计提；,（2）IF函数单条件判断并计算,（3）IF函数单条件判断返回引用区域,如图，某公司计算相应分公司的销售总额，根据A2的显示分别计算相应部门的销售总额；,第40页,第41页,（4）IF函数多条件判断,如图，这是一次考试的期末测试，教师想进行综合成绩评定，即给学生“优秀、良好、中等、及格、不及格”的评定，在综合成绩下显示。用IF函数编辑公式。具体标准如右下图：,SUMIF(C3:C13,211,E3:E13)=SUMIF(D3:D13,2000,F3:F13)=SUMIF(C3:C13,211)=SUMIF(D3:D13,2000)=SUMIF(B3:B13,彩电)=,功能：根据指定条件对若干单元格求和。格式：SUMIF(range,criteria,sum_range)SUMIF(引用1,条件,引用2)说明：range为用于条件判断的单元格区域;criteria为确定哪些单元格将被相加求和的条件;sum_range是需要求和的实际单元格。,14,97800,633,21900,0,当省略参数三时，SUNIF将直接对参数一中的单元格根据条件满足与否来求和；,2.3.2 SUMIF 函数,如图，对销售金额大于D2单元格的数值求和；,=SUMIF(A2:A9,接本例,对销售金额大于销售金额平均数的求和；,=SUMIF(A2:A9,&AVERAGE(B2:B9),接本例,对商品名称包含”A”的销售金额求和；,=SUMIF(A2:A9,*A*,B2:B9),对名称第四、五个字符为“A2”且字符总长度为6位的金额求和；,=SUMIF(A2:A9,“?A2?,B2:B9),(1)SUNIF函数单条件求和,如图，对符合入库数量大于4小于10的商品入库数量求和；,=SUMIF(C2:C11,4)-SUMIF(C2:C11,=10),对B列品名分别为“AA”“BB”“CC”的商品入库数量求和；,=SUMIF(B2:B11,AA,C2:C11)+SUMIF(B2:B11,BB,C2:C11)+SUMIF(B2:B11,CC,C2:C11),对品名分别为“AA”的“手机”入库数量求和；,=SUMIF(F2:F11,手机AA,C2:C11),(2)SUNIF多条件及多区域求和,如图，在一月和二月的销售报表中，对商品名称为A1的销售数量求和；,=SUMIF(A4:D11,A1,B4:E11),对于不相邻区域的求和，就是把多个区域的结构调整一致，即区域的大小和条件区域保持一致，且结构一致；,(3)SUNIF不相邻区域求和,功能：返回某个数字按指定位数取整后的数字。格式：ROUND(number,num_digits)ROUND(数字,指定位数)说明：Number是需要进行四舍五入的数字。Num_digits是指定的位数，按此位数进行四舍五入。如果num_digits 大于0，则四舍五入到指定的小数位。如果num_digits 等于0，则四舍五入到最接近的整数。如果num_digits 小于 0，则在小数点左侧进行四舍五入。举例:ROUND(3.28,1)=ROUND(-1.475,2)=ROUND(21.5,-1)=ROUND(26.5,-1)=ROUND(46.5,-2)=ROUND(56.5,-2)=,3.3,-1.48,20,30,0,100,2.3.3.ROUND 函数,如图工资表，E列是默认格式，F列是通过设置数字格式保留2位小数的格式，G列是用ROUND 函数四舍五入后的数字；问题：利用F列和G列两个四舍五入后的合计数相差0.01；究竟哪个正确？,如果该单位工资发放为银行代理，如以F列的为准的工资表交到银行后，银行的工作人员按显示数值输入，最终的合计数就会与交给的银行的工资表合计出现误差；造成麻烦；,ROUND 处理工资表计算误差,注意：数字格式设置的只是显示格式，而参加运算的是实际数值；所以最好的方法用ROUND函数处理；,AVERAGE 函数 功能:返回参数的平均值（算术平均值）。格式:AVERAGE(number1,number2,.)说明:Number1,number2,.为需要计算平均值的1到30个参数。直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算,而引用的将忽略。举例:AVERAGE(0,2,2,3,2,3,4)=,2,2.3.4 AVERAGE 函数(MAX(),MIN(),利用MAX函数设置变动序号,如图，工资表中要在第一列设置序号，序号必须符合下列要求：1.序号随行的添加或删除，可自动调整为新的连续序号；2.在小计行、合计行和空行前不加序号；,A2=IF(OR(B2=,B2=小计,B2=合计),MAX($A$1:A1)+1),VLOOKUP函数、HLOOKUP（）功能：以文字的形式显示在一个工作簿中查看对某个单元格的引用情况。（首列查找）格式:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP(查找目标,查找区域,相对数列,查找方式)HLOOKUP与VLOOKUP相对应为首行查找；,2.3.5查找和引用函数,说明:Lookup_value（查找目标）为需要在数据表第一列中查找的数值。可以为数值、引用或文字串。Table_array（查找范围）为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。Col_index_num（相对数列）为table_array中待返回的匹配值的列序号。range_lookup（查找方式）分精确查找和模糊查找；如果range_lookup为TRUE，则table_array（查找范围）的第一列中的数值必须按升序排列，也可用1；如果range_lookup为FALSE，则table_array（查找范围）不必进行排序，也可用0；。,VLOOKUP函数,VLOOKUP(王建国,A2:F14,2)=VLOOKUP(王建,A2:F14,2)=VLOOKUP(王,A2:F14,2)=VLOOKUP(张军,A2:F14,5)=VLOOKUP(“张 国,A2:F14,5)=,82,84,79,69,36,举例:对A2:A14进行升序排列,（1）VLOOKUP应用单个区域查找,（2）VLOOKUP应用多个区域查找,如图在奖金统计表中，根据员工姓名查找其奖金数额；,方法一：在D2中编辑如下公式，然后向下复制；=VLOOKUP(A2,IF(C2=沈阳,$G$3:$G$6,$J$3:$J$6),2,0),思考：这种方法的弊端在哪里？,（2）VLOOKUP应用多个区域查找,为了克服方法一的弊端，我们采取这种方法二；首先将表格进行相应调整，重点在辅助列；=VLOOKUP(A2,$G$2:$J$9,4,0),辅助列,辅助列,思考：这种方法构建辅助列的要点在哪里？,（2）VLOOKUP应用多个区域查找,第56页,如图，这次考试的期末测试，教师想进行创新，成绩评定给学生“A-I”由高到低的9级评定，在综合成绩下显示。具体标准如右下图：以前我们IF函数编辑过公式，现在看看有没有更简便的方法；,（3）VLOOKUP应用模糊查找的用处,如图，先把标准整理成右侧的格式，思考整理标准的要点是什么？,（3）VLOOKUP应用模糊查找的用处,第57页,文本函数可以处理公式中的字符串。例如，可以改变大小写或确定字符串的长度，可以将日期插入字符串或连接字符串上，还可以将数字转换为文本。2.5.6.1 LEN函数(LENB)功能：返回文本字符串中的字符数。格式：LEN(text)说明:Text是要查找其长度的文本。空格将作为字符进行计数。举例：LEN(“Phoenix,AZ”)=LEN(“”)=,11,0,2.5.6 文本函数,2.5.6.2 MID(B)函数（LEFT(B),RIGHT(B),FIND,SEARCH）功能：返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。格式：MID(text,start_num,num_chars)说明:Text是包含要提取字符的文本字符串。Start_num是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars指定希望 MID 从文本中返回字符的个数。Num_bytes指定希望 MIDB 从文本中返回字符的个数（按字节）。举例：MID(“microsoft Excel”,3,2)=MID(“文本函数”,3,2)=MIDB(文本函数,3,2)=,cr,函数,本,文本函数,4.数据管理工具,PART4,Excel中的数据表格就是一个简单的数据库，所以可以将表格中的每项数据信息视为记录，并按照数据库的结构形式进行管理，如进行数据排序、筛选、汇总以及创建数据透视表。数据查找是指为了更加清晰地分析数据,有时只需从原始数据中提取满足条件的数据记录,而原数据不会改变,也不会被隐藏；数据筛选是指将数据库或数据清单中所有不满足条件的数据记录隐藏起来,只显示满足条件的数据记录；数据计算是指对数据库或数据清单的数据做求和、平均数、比较大小、计算个数等各种计算；,4.1 数据管理概述,4.2.1 数据排序排序规则:数值:按数值的大小;字母:按字母的先后顺序;日期:按日期的先后;汉字:按汉语拼音的顺序或按笔画顺序;逻辑值:升序时FALSE排在TRUE前面,降序时相反;空格:总是排在最后。,4.2 数据排序和筛选,4.2.2 数据筛选 筛选是查找数据清单中数据的快速方法。经过筛选后的数据清单只显示包含指定条件的数据行，以供用户浏览、分析。自动筛选为用户提供了在具有大量记录的数据清单中快速查找符合某种条件记录的功能。使用自动筛选功能筛选记录时，字段名称将变成一个下拉列表框的框名。当自带的筛选条件无法满足需要时，也可以根据需要自定义筛选条件。使用高级筛选功能，必须先建立一个条件区域，用来指定筛选的数据所需满足的条件。,4.2 数据排序和筛选,4.3 分列,分列就是把单元格中字符按一定规则填充到多列中。分列还可以处理导入的文本文件或从网页上复制的以表格存放的内容。分裂的主要功能：1.数据转换。2.复杂数据的排序。3.修改数据。,4.3.1 拆分整列为多列,分列就是把单元格中字符按一定规则填充到多列中。分列还可以处理导入的文本文件或从网页上复制的以表格存放的内容。,如图把A列内容分隔成日期、物品名称、数量、单价和金额5列内容；,1.选取A列，执行数据|分列命令，打开文本分列向导对话框；,【分隔符号】文本类型，适用于上下排列不整齐，分隔符号位置不固定的情况；【固定列宽】文本类型，适用于直线上下分隔的情况；,4.3.1 拆分整列为多列,2-A.选取固定列宽，单击下一步；,根据提示在“数据预览”中设置分列线的位置,4.3.1 拆分整列为多列,2-B.如果选取的是分隔符号，单击下一步；,如果选取的是【分隔符号】，则提示设置分隔符号，本例中分隔符号为“空格”，所以选取【空格】项；如果使用特殊字符或文字进行分列，【分隔符号】栏中应选取【其他】选项，然后在后面的文本框中输入相应字符或文字；,4.3.1 拆分整列为多列,3.在向导步骤3中完成数据类型设置，单击完成；,完成设置后，EXCEL会按“分列预览效果”的格式将被分隔内容放置到后面的各列，并在第一列存放原文本列（A列）。如果要把分列后的数据存放到其他位置，则单击【目标区域】后的 按钮，选取目标单元格；如果不显示分隔后某列，则选取列数据格式栏中的不导入此列；在设置列数据格式时，每一列的数据格式都应该单独设置；,4.3.1 拆分整列为多列,分列后结果,4.3.1 拆分整列为多列,数据透视表会自动将数据源中的数据按用户设置的布局进行分类，从而方便用户分析表中的数据，如可以通过选择字段来筛选统计表中的数据。,4.4 数据透视表,