Excel高级班课程课件

《Excel高级班课程课件》由会员分享，可在线阅读，更多相关《Excel高级班课程课件（50页珍藏版）》请在金锄头文库上搜索。

1、Excel 2010高级使用技巧,Contents,1、Excel2010的数据录入技巧； 2、自定义数字格式； 3、Excel2010条件格式设置； 4、数据有效性； 5、EXCEL多工作表链接、冻结与并排； 6、EXCEL工作表的数据保护功能；,一、数据录入和格式化工作表,1.1、数据录入的相关技巧,数据录入技巧 灵活使用TAB、ALT、ENTER键 录入分数：零+空格+分数 录入文本格式数字（邮编、电话等） ：单引号+录入内容 输入当前日期、时间：Ctrl+; 和 Ctrl+Shift+; 录入特殊字符（对号、平方）;Alt+41420, Alt+178 不连续区域的填充：Ctrl+En

2、ter Shift+F8 填充柄录入（序列填充）,实例01 数据录入,案例分析 （1）在数字后面自动添加单位（如：元、千克等）#单位 （2）输入身份证的前几位 51102519 （3）输入条形码前的某些重复数字 “999999999” （4）定义小数点对齐、分数对齐 ?.? # ?/? （5）自定义各种特殊日期格式 mmmm d, yyyy (dddd) December 30,2008 (Tuesday) yyyy“年”m“月”d“日”aaaa 2008年12月30日星期四,1.2、自动限定数据格式,实例02 自定义数字格式,1、EXCEL表格的排版技巧； 2、EXCEL条件格式,1.3、E

3、xcel2010条件格式设置；,1.3.1、单元格格式设置,文字的对齐与旋转 合并单元格 使用工具栏上的快捷按钮 格式单元格对齐合并单元格 自动换行 格式单元格对齐自动换行 强制换行 ALT+ENTER 文字旋转 格式单元格对齐文字方向 设定边框和底纹 使用工具栏上的“边框”“底纹”快捷按钮 格式单元格边框图案,实例03 对齐技巧与边框底纹,1.3.2、条件格式管理器设置,什么是条件格式 当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定的格式 多条件格式的规则 按从上到下的顺序判断条件并执行 最多指定三个条件 根据公式自动设置格式 在条件格式中使用公式和函数 实现隔行变色等的特殊格

4、式 掌握ROW和MOD函数的用法 =mod(row(),2)=0偶数行变色 =mod(row(),2)=1奇数行变色=mod(column(),2)=0列变色,实例04 条件格式,提高数据录入准确性-有效性检查。什么是数据的有效性？ 数据有效性是一个包含帮助你在工作表中输入资料提示信息的工具. 它有如下功能： (1) 给用户提供一个选择列表 (2) 限定输入内容的类型或大小 (3) 自定义设置 数据有效性的设置 =countif(a:a,a1)=1重复数据 提供下拉式菜单的输入 设置输入提示 设置出错警告 自定义有效性条件,1.4、数据的有效性,实例05 数据有效性,1.5、如何处理大型工作表

5、,在Excel多个工作表间快速切换 技巧一：用快捷键快速导航工作表 Ctrl+Page Up 移到上一个工作表 Ctrl+Page Down 移到下一个工作表 技巧二：右键菜单准确定位工作表 右击导航状态按钮选择所需工作表标签名 冻结工作表窗口 大型工作表数据的分组、分级显示 命名单元格或单元格区域，以便能够快速返回到这些工作表区域 组合工作表，以便同时在多张工作表中输入共用数据,实例06 冻结窗格,并排比较数据 两个工作簿将在同一个窗口中打开。当一个工作簿滚动时，Excel 将使第二个工作簿自动滚动相应数量的单元格,同时比较两个工作簿可能很困难,实例07-1 并排比较数据1,实例07-2 并

6、排比较数据2,1.6、数据保护功能,保护工作表、工作簿 （1）为工作簿加密码（2）为工作表加密码（3）设置不同区域不同密码（4）设置只有部分单元格可编辑,二、公式与函数的应用及案例,双击填充柄复制公式 区别公式中各种单元格引用方式 绝对引用、相对引用、混合引用、三维引用=book1.xlsSheet1!A1 跨工作表或工作簿的数据计算 不同工作表间数据的计算 =Sheet3!D2+Sheet2!D4 不同工作簿间数据的计算=成绩单.xlsSheet1!$C$2+Sheet2!D6 自动求和与自动计算功能的使用,2.1 公式的基本用法,函数：是预定义的公式，通过使用一些称为参数的特定值来安特定的

7、顺序和结构执行计算 函数的使用方式=函数名称（参数1，参数2，参数3) 函数的嵌套特性 一个函数可以引用其他函数的结果作为参数 Excel最多支持七层嵌套函数 逻辑文本类函数应用 日期时间函数应用 查询引用类函数应用 统计计数类函数应用 财务函数应用,2.2 函数应用与常用函数应用举例,IF ANDOR LEN MID LEFT RIGHT,2.2.1逻辑文本类函数,判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值,IF函数,语法：IF(logical_test,value_if_true,value_if_false),条件为真时,条件为假时,=IF(A2=100,A2+B

8、2,),=IF(LEN(A1)=18,MID(A1,7,4)&-&MID(A1,11,2)&-&MID(A1,13,2),IF(LEN(A1)=15,19&MID(A1,7,2)&-&MID(A1,9,2)&-&MID(A1,11,2),),从身份证号码中可以提取完整的出生日期,MID: 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。,LEN :返回文本字符串中的字符数,AND OR 函数,AND函数的作用：是对多个逻辑值进行交集计算，返回逻辑值语法：AND(logical1,logical2) =AND(1A2, A2100) =IF(AND(1A3, A3100), A

9、3, 数值超出范围),OR函数的作用：是对多个逻辑值进行并集计算，返回逻辑值语法：OR(logical1,logical2,) OR函数的作用：是对多个逻辑值进行并集计算，返回逻辑值语法：OR(logical1,logical2,),=OR(TRUE) 参数为 TRUE (TRUE) =OR(1+1=1,2+2=5) 所有参数的逻辑值为 FALSE (FALSE) =OR(TRUE,FALSE,TRUE) 至少一个参数为 TRUE (TRUE),MIDB函数作用自文字指定位置开始向右提取指定长度的字符串，操作对象均为文本，MID以字符为单位，MIDB以字节为单位截取语法：MID (text,s

10、tart_num,num_bytes) LEFT返回文本字符串中的第一个或前几个字符，RIGHT返回文本字符串中的最后一个或后几个字符语法：LEFT(text,num_chars)语法：RIGHT(text,num_chars),MID(MIDB) LEFT RIGHT函数,DATE：返回代表特定日期的序列号 语法：DATE(year,month,day) NOW：返回计算机内部时钟的当前日期和时间 语法：NOW( ) TODAY：返回当前日期的序列编号 语法：TODAY( ) 注意：序列号是 Microsoft Excel 日期和时间计算使用的日期-时间代码。Microsoft Excel

11、可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 ，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。,2.2.2日期与时间函数：,=YEAR(TODAY()-YEAR(B2),=LEFT(D2,6),=TEXT(B2,yyyy-mm),计算年龄,从日期中提取年月,LOOKUP HLOOKUP VLOOKUP,2.2.3查询和引用类函数,LOOKUP,使用LOOKUP函数是在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值LOOKUP(lookup_valu

12、e,lookup_vector,result_vector)说明：lookup_vector为第一个包含一行或一列的区域；result_vector为第二个包含一行或一列的区域，其大小必须与参数lookup_vector相同,VLOOKUP_实现竖直查找,在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时,可以使用此函数 语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),=VLOOKUP(34, A1:D11, 2, FALSE),=vlookup(A2,she

13、et1!$A$1:$D$5,2,0),Vlookup数据关联,Vlookup数据比较,=VLOOKUP(A1,Sheet2!$A$1:$B$12,2,0),HLOOKUP实现水平查找,使用HLOOKUP函数可以在表格的首行查找指定的数值,并由此返回指定行处与该数值同一列的数值，当比较值位于数据表首行时，可使用此函数 语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),COUNT COUNTIF SUMIF),2.2.4统计计数类函数,COUNT,COUNT函数的作用：返回包含数字以及包含参数列表中的数字的单元格的个数。

14、利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。 语法：COUNT(value1,value2,.) 参数：Value1, value2, . 为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。 说明 函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。,COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事

15、、工资和成绩统计中有广泛用途。,COUNTIF函数,语法： COUNTIF（range,criteria),SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式。,SUMIF函数,语法： SUMIF（range,criteria,sum_range),c6=SUMIF(B2:C5,B6,C2:C5),INT,INT函数的作用：将数字向下舍入到最接近的整数。 语法：INT(number) 参数：number是需要进行向下舍入取整的实数。,MOD,返回两数相除的余数。结果的正负号与除数相同。 语法 MOD(number,divisor)；Number为被除数；Diviso

16、r为除数。 注解 如果 divisor 为零，函数 MOD 返回错误值 #DIV/0!。 函数 MOD 可以借用函数 INT 来表示: MOD(n, d) = n - d*INT(n/d),PMT（）贷款函数； 基于固定利率及等额分期付款方式，返回贷款的每期付款额。 语法PMT(rate,nper,pv,fv,type) Rate贷款利率。 Nper该项贷款的付款总数。 Pv现值，或一系列未来付款的当前值的累积和，也称为本金。 Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。 Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。 注解 PMT 返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为