excel部分教材.doc

目录第1节 基本操作2n1.1 数据输入2n1.2 数据编辑与核对7n1.3 数据保护11第2节 函数12n2.1 单元格引用12n2.2 函数12培训中心信息培训室 吕秀华 810789 86364952第1节 基本操作n 1.1 数据输入1、输入身份证号码（只能输入15位或18位身份证号码）步骤：选择需要设置的单元格，本题选择D2单元格 选择“数据/有效性”菜单；出现下面对话框，进行设置，确定。采用公式复制的方法，拖动填充柄至其他区域公式：“=AND(COUNTIF(D:D,D2)=1,OR(LEN(D2)=15,LEN(D2)=18)”2、输入邮政编码（只能输入6位邮政编码）步骤：选择需要设置的单元格，本题选择A2单元格 选择“数据/有效性”菜单出现下面对话框，进行设置，确定。采用公式复制的方法，拖动填充柄至其他区域公式：“=AND(COUNTIF(A:A,A2)=1,LEN(A2)=6)”3、数据有效性下拉列表（防止错误录入）步骤：选择需要设置的区域 选择“数据/有效性”菜单出现下面对话框，进行设置，确定。其中来源可以是直接的数据、单元区域引用、区域名称单位：是已经定义的包含单位信息的区域名称，如下图：4、自动更正功能分析一下这些数据：在“毕业学校”一栏，有好多个学生毕业于同一所学校，重复输入效率肯定不高。先自己定义几个字符来临时替代一下各个学校的名称（如用“4Z”代表“枣阳市第四中学”，等全部完成后再按“Ctrl+H”把所有的“4Z”全部替换为“枣阳市第四中学”不就行了？提示：尽量用一些简单好记、易录入、不会与其他内容发生混淆的字符来执行替换，不过不要光顾了简单好记，否则，你用“4”代替“枣阳市第四中学”试试，做了“全部替换”后结果怎样！如果你连Ctrl+H这一步也想省去，你可以试试EXCEL的“自动更正”功能。使用自动更正功能，可以简化用户对大量重复数据的输入操作，例如可以将上面的“4z”定义更正为“枣阳市第四中学”，定义的方法是：1.从工具菜单中选择“自动更正”，打开自动更正对话框。 2.在“替换”框中输入数据， 如：“4z”，在“替换为”框中输入数据，如：“枣阳市第四中学”，单击“添加”，再单击 “确定”。之后，只需在单元格中输入“4z”两字，即会自动更正为“枣阳市第四中学”。5、强行换行 Alt+enter6、自定义格式 在数字中增加文本(学生编号、代号) 设置自定义格式，控制用户输入 定义电话号码、保险号码的简化输入7、选择性粘贴（1）先对选定区域执行复制操作并指定粘贴区域（2）选择“编辑/选择性粘贴”命令，出现选择性粘贴对话框（3）在对话框中指定需要的粘贴方式（4）单击“确定”按钮8、模板n 1.2 数据编辑与核对1、将行列数据进行对调步骤：选择需要转换的区域 选择“编辑/复制”菜单定位至目标单元格选择“编辑/选择性粘贴”出现下面对话框，进行设置，确定。2、将数字型文本转换为数字Excel 2003：在以文本方式存储数字的单元格的左上角有个绿色的三角形，这个单元格成为活动单元格后，单元格左边有个惊叹号，点这个惊叹号会弹出菜单，在菜单中选择“转换为数字” Excel 2000 或以下，也适用 Excel xp、2003：用公式可以将文本转换为数字。例如：A1中以文本方式存储了数字，在B1单元格中输入公式 =A1 * 1在计算公式时，Excel 会自动将文本转换成数字。3、快速圈释无效数据校对数据是我们在日常办公中经常要做的工作，对于含有大量数据的表格，如何快速找到无效的录入数据、提高我们校对的效率现有某单位第一季度的加班统计表，统计加班费。能不能把超出范围的错误数据快速找出来，由于单位规定，每人每月的加班时间不得超过90小时（出于职工的健康着想），两种办法：1 使用条件格式选中表格中的数据区域，单击“格式”菜单中的“条件格式”命令，在弹出的“条件格式”的对话框完成设置后依次单击“确定”按钮返回到数据表格中，浏览一下表格，是不是看到超出范围的数字已经被标注了红颜色，这样改起来就方便多了。2使用有效性验证一般情况下，在录入数据之前设置数据的有效性可避免录入超出指定范围的非常规数据，但数据录入完成后设置数据的有效性有什么作用呢？通过下面的介绍大家就会明白了。选中表格中需要设置有效性的数据区域，单击“数据”菜单中的“有效性”命令，弹出“数据有效性”对话框，切换到“设置”选项卡进行相应的设置，单击“确定”按钮返回数据表中（此时表格的数据中看不到有什么变化）。右击工具栏打开“公式审核”工具栏，单击工具栏中的“圈释无效数据”按钮，此时我们会发现表格中的无效数据都被清清楚楚地标注出来了。4、快速比较两张表的差异（条件格式，函数）案例背景在实际工作中人力资源部经常需要核对某些数据，除了核对工资外，还需要核对员工身份证件号码、职工姓名、银行账号等信息。这些工作很单调且麻烦。若被核对的字符串简单，直接进行人工处理还是可以的；一旦字符很多，且排列顺序不一，这时人工查起来就显得比较麻烦。l 条件格式：步骤：选中d2 单击“格式/条件格式”菜单 出现下面对话框，进行设置，确定。=not(or(d2=I$2:I$9)l Vlookup函数1）在D2单元格中输入“=IF(ISERROR(VLOOKUP(A2,原始表!$A$1:$B$27,2,0), ,VLOOKUP(A2,原始表!$A$1:$B$27,2,0)”从原始表取数据2）在F2单元格输入“=IF(B2D2,有误,)”进行比较n 1.3 数据保护1、保护计算公式锁定和隐藏Excel公式在Excel中辛辛苦苦编制的计算公式，通常不希望使用者查看和修改。怎么办呢？利用Excel锁定、隐藏和保护工作表的功能，把公式隐藏和锁定起来。1、选中整个工作表数据区域，执行“格式单元格”命令，打开“单元格格式”对话框。2、切换到“保护”标签，清除“锁定”选项前面复选框中的“” 号，确定返回。3、执行“编辑定位”命令，打开“定位”对话框，单击其中的“定位条件”按钮，打开“定位条件”对话框。4、选中其中的“公式”选项，确定返回，一次性选定工作表中所有包含公式的单元格。5、再次打开“单元格格式”对话框，切换到“保护”标签下，在“锁定”和“隐藏”选项前面复选框中加上“” 号，确定返回。6、执行“工具保护保护工作表”命令，打开“保护工作表”对话框，输入密码，确定，再确认输入一次密码，确定返回即可。注意：如果自己需要查看或修改公式，请先执行“工具保护撤销工作表保护”命令，解除对工作表的保护。 2、Excel中彻底隐藏数据的技巧日常工作中，你是否遇到过需要把Excel工作表中部分单元格的内容隐藏起来，不想让浏览者查阅。其实方法非常简单，下面让我们一起看一下如何实现？1)要选中Excel表中需要隐藏内容的单元格（区域），执行“格式单元格”命令，打开“单元格格式”对话框，在“数字”标签的“分类”下面选中“自定义”选项，然后在右边“类型”下面的方框中输入“；”（三个英文状态下的分号）。2)切换到“保护”标签下，选中其中的“隐藏”选项，按“确定”按钮退出。3)执行“工具保护保护工作表”命令，打开“保护工作表”对话框，设置好密码后，“确定”返回。 经过这样的设置以后，上述单元格中的内容不再显示出来，就是使用Excel的透明功能也不能让其现形。 学技巧提示：在“保护”标签下，请不要清除“锁定”前面复选框中的“”号，这样可以防止别人删除你隐藏起来的数据第2节 函数-单元格引用-常用函数简介n 2.1 单元格引用1）.相对应用 ：指把含有单元格引用的公式复制或填充到一个新的位置时，公式中的单元格引用会随着目标单元格位置的改变而改变。2）.绝对应用 ：指把含有单元格引用的公式复制或填充到一个新的位置时，公式中的单元格引用不会改变。$A$1:$D$53）.混合引用n 2.2 函数 1、sum、average、max、min 、count（统计数字个数）、Counta（数据个数）2、逻辑函数：IF、Sumif和Countif 比较运算“=、=、=、” 逻辑运算“AND、NOT、OR、TRUE、FALSE(0)”(1) If函数：格式IF(logical,v1,v2)例如：IF(AND(1B4,B489,”优”,IF(E679,”良”,IF(E669,”中”,IF(E6=60,”及格”,”不及格”)IF(AND(D3=60,E3=60,F3=60,G3=60),”Pass”,”)(2) COUNTIF函数可以计算出符合条件的数据的个数COUNTIF（A3:A6,”apples”）=2; A3:A6单元格内容为：apples,oranges,peaches,applesCOUNTIF(B3:B6,”55”)COUNT(G6:G15,”60”)(3)SUMIF函数根据指定条件对若干单元格求和格式：SUMIF(range,criteria,sum_range) Range：区域 Criteria：哪些单元格将被相加求和的条件 Sum_range：只有当range中的相应单元格满足条件时，才对sum_range中的单元格求和。例如：SUMIF(D2:D10,”彩电”,G3:G10) 如果D2:D10区域的内容为“彩电”,就把G3:G10区域中数据求和3、日期及时间函数： date(year,month,day) YEAR(“1998/01/30”)=1998 MONTH(“1998/0130”)=1 DAY(“1998/01/30”)=30 TODAY（）； NOW() NETWORKDAYS(start_date,end_date,holidays)：计算两个给定日期间的工作日数值。例：NETWORKDAYS(“2002/05/01”,”2002/05/30”,”2002/05/28”)。 DATEDIF(date1,date2,inteval)interval：y、m、d、ym、yd、md；返回两个日期之间的差值；DATEDIF(DATE(1982,11,24),2005/12/1,m)=276 DATEDIF(DATE(1982,11,24),2005/12/2,md)=8Y: 两个日期相差的整年数；M: 两个日期相差的整月数；D：两个日期相差的天数Ym：忽略年份和日的差别，只计算月的差（将两个日期看作同一年）Yd：忽略年份的差别，只计算所差的天数（将两个日期看作同一年）md：忽略年份和月的差别，只计算所差的天数（将两个日期看作同年、同月）4、LOOKUP函数：从给定的向量或数组中查询出需要的数值格式：LOOKUP(VALUE,ARRAY)LOOKUP(“C”,“a”,”b”,”c”,”d”,1,2,3,4)=35、VLOOKUP函数：查看数组左边一列值，若找到要查找的值，则返回指定单元格的值 格式：VLOOKUP(value,table,n,range_lookup) 在数据表table的第一列中查找值为value的数据，如果找到，就返回表或区域中第n列且值与value同行的单元格中的数据。 Range_lookup为TRUE时，table第一列数据必须按升序排列，否则找不到正确的结果；Range_lookup为FALSE时，table第一列数据不需要排列。 如果VLOOKUP函数找不到value，且Range_lookup为TRUE，则返回小于等于value的最大值。 如果value小于table第一列中的最小值，VLOOKUP函数返回错误值。 如果VLOOKUP函数找不到value且Range_lookup为false，VLOOKUP函数返回错误值。6、ISERROR函数与Vlookup函数结合使用函数格式：ISERROR(value)参数value表示需要判断的值。如果value为除去#N/A以外的其他错误值则返回True，否则返回false。功能：判断某一个值是否为任意值。7、其他常用函数 INDIRECT函数的作用是返回由文本字符串指定的引用