Excel讲稿new.doc

EXCEL讲稿 吉大培训：胡海艳Excel讲稿例1：员工档案表（重点:Excel基本制表）问题:1、工作表的美化：文字对齐、文字颜色、底纹设置？ 2、货币符号、零开头数字、长数字串如何输入？ 3、如何填充数字、日期、文本？ 4、如何利有有效性快速输入性别和部门？ 5、如何拆分和冻结窗格？职工编号姓名性别部门身份证号码出生年月住址联系电话基本工资00001张丰男人资2101061982050641331982-5-6铁西区8150792212001、职工编号：零开头数字方法1：(EN)+012方法2:格式单元格数字文本（数字作为文本）方法3:格式单元格数字自定义：0000问题：身份证号的输入方式？2、编号填充：Ctrl+; 当前日期 Ctrl+Shift+; 当前时间#错误：列宽太窄日期填充：日、工作日、月、年 编辑序列填充.等差：左键等比：右键等比文本序列：工具选项自定义序列左侧：新序列；右侧：输入添加文本序列可以从Excel中导入到自定义序列中问题：职工编号的输入？3、有效性：选中列数据有效性序列，在下方输入性别:男,女确定选中列数据有效性小数，在下方输入工资范围:最小值：0；最大值：3000确定选中列数据有效性输入法模式汉字：打开五笔或拼音 数字：关闭确定4、输入日期：1/3 即1月3日 2008/1/5 即2008-1-5 2008-1-5 即2008-1-5输入分数： 0空格1/25、边框设置及斜线表头格式单元格边框6、拆分与冻结窗格 窗口 拆分/冻结例2：制作工资表和成绩表（重点：公式运算及函数处理）问题:1、常用函数的使用：求和SUM、平均值AVERAGE、最大MAX、最小MIN、计数COUNT？ 2、数据区域不规则如何计算；相对地址(A1)和绝对地址($A$1)的异同？ 3、排名函数Rank如何使用？ 4、日期函数：Now、Day、Month、Year如何使用；已知出生日期，如何求年龄？ 5、如何使用IF语句求个人所得税(难点：IF嵌套)? 6、其它函数：Countif、Sumif、Left 、Right、Len、Large 、Small等函数 编号姓名出生日期年龄工资奖金补助工资和税费实得工资NO1刘向阳1982-6-9261200200015033504052945一、基本概念：1、公式(Formula):所有以等号（=）开头的表达式，包含5个重要的组成部分：1） 运算符 2）单元格引用 3）字符串或数字 4）工作表函数 5）括号例：=99*2*pi()+A1 公式结构2、函数(Function):由一 预先定义的公式组成的，以协助得到一些计算结果，函数通常会包含一个或多个参数（Argument）,也有少数无需使用任何参数。3、参数：是函数用来执行计算所需要的数据，它可以使用的数据有数值、文本、逻辑值、单元格引用和名称例：=SUM(A1:A6) 函数结构4、运算符 (可以用括号改变优先级)数学运算符：+ - * /文本运算符：&比较运算符：= = 引用运算符： ， ： 空格二、函数1、相对地址：公式中的单元格引用地址，会随着公式所在的单元格位置的改变而改变。如： E10绝对地址：公式中的单元格引用地址，不会随着单元格本身位置的改变而改变。如：$E$10 例举：如何使用绝对地址运算？ 混合引用：$A1 A$1外部引用：=sheet1!B2求和：SUM平均值：AVERAGE最大值：MAX 第n大的值:=Large(数组,n)最小值：MIN 第n小的值:=Small(数组,n)计数：COUNT统计数字的个数(人数)2、排列名次：RANK =Rank(数字,数组,0或不写:降序 如果为非0数：升序)=Rank(H10,$H$10:$H$18)3、分列：把姓和名从一个单元格，分到两个单元格选中列数据分列固定列宽调整分隔符确定与分列相反：合并姓和名列运算(即为合并两个单元格中的文本到一个单元格) =M10&N104、IF函数=IF(H102000,(H10-2000)*0.2,0)如果工资大于2000，那么个人所得税为(H10-2000)*0.2元,否则个人所和税为0元计算个人所得税：分三段，使用IF嵌套语句要求：小于2000不计税；大于2000并且小于3000，个人所得税0.2；大于3000，个人所得税0.3=IF(H103000,(H10-2000)*0.3,IF(H102000,(H10-2000)*0.2,0)5、条件格式：把数值大于或小于某个值的数标记成特殊的数字格式 选中数值格式条件格式单元格格式：大于40000 格式：字体、字形、颜色确定6、CountIF统计满足条件的单元格个数例：=COUNTIF(E10:E18,男)统计某个区域中男生人数 =COUNTIF(I10:I18,2000)统计某个区域中工资大于2000元的人数7、SumIF统计满足条件的和统计男生工资和：=SUMIF(E10:E18分类区域,E10条件区,M10:M18求和区)8、已知出生年月日，求出某人的年龄=now() 当前的日期和时间，无参数=day() 取日（数字），有参数 =day(2008-3-10) 结果10=month() 取月(数字) ,有参数 =modth(2008-3-10) 结果3=year() 取年() ,有参数 =year(2008-3-10) 结果2008出生日期 年龄H10 =Year(now()-year(H10)9、Left取字符串左端的若干个字符Right取字符串右端的若干个字符Mid取字符串中间的若干个字符例：电话号码：8510*10、Row() 取得行号Column()取得列标Mod(除数,被除数)返回两数相除的结果Int()将数值向下取整为最接近的整数Index(单元格区域,行数,列数) 在给定的单元格区域中，返回特定行列交叉处单元格的值或引用生成工资条=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,工资总表!C$1,INDEX(工资总表!$A:$J,INT(ROW()+4)/3),COLUMN()11、VLOOKUP函数主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列外的数值格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)参数说明：loopup_value代表需要查找的数值；(必须在table_array区域的首列中)Table_array代表需要在其中查找数据的单元格区域Col_index_num为在table_array区域中待返回匹配值的列序号Range_lookup为一逻辑值，如果为True或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于Loopup_value的最大数值；如果为False，则返回精确匹配值，如果找不到，则返回错误值#N/A。12、HLOOKUP函数：主要功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行外的数值格式：HLOOKUP（loopup_value,table_array,row_index_num,range_lookup）参数与VLOOKUP类似13、LOOKUP：在向量或数组中查找值格式：LOOKUP(lookup_value,lookup_range,result_range)三、公式与函数中的错误及更正在使用公式时，常见的错误有4大类型：语法错误、逻辑错误、引用错误、未完成计算的错误1、#Div/0!错误：除以0的错误，代表公式中的某些除数为0，或是其引用的单元格为空。出现错误的解决方法：1）=IF(ISBLANK(I18),I17/I18)2）=IF(ISERROR(I25/I26),I25/I26)2、#REF！错误：当公式中所引用的单元格地址无效时，就会出现此消息。3、#Value!错误：在公式中使用了错误的运算符，或是在函数中使用了错误的类型参数。例3：员工档案表（重点：数据处理）职工编号姓名性别部门身份证号码出生年月住址联系电话基本工资0001张丰男消保2101061982050641331982-5-6铁西区815079221200问题:1、数据排序:按部门升序排列,如果部门相同,则按基本工资升序排列? 2、筛选部门为市场且基本工资高于1200元的员工？ 3、自动筛选与高级筛选的不同方式? 4、按部门分类汇总每个部门的工资和? 5、合并计算的使用？ 6、数据透视表及透视图？1、排序：升序/降序 数字/汉字 :拼音 笔划/英文 a=1 z=26 主要关键字/次/第三有无标题行2、筛选：自动筛选 /高级筛选：可以多条件筛选，可以实现部分打印 自定义筛选：工资在2000到3000元之间 与：取中间 工资小于2000或者工资大于3000 或：取两边高级筛选：优点是可以不破坏原始表；要求：必须先设定条件区域 条件区域 列标题名 工资和条件2000多条件区域：a:工资和大于2000且性别为男(与) 工资和 性别 2000 男 b:工资和大于2000或者性别为男(或) 工资和 性别 2000 男同列中有多条件：要求工资和在2000-3000之间 工资和 工资和 2000 30003、分类汇总：要求：一定先按类排序，优点：分类求和、平均值. 选中表数据排序按类进行排序 选中表数据分类汇总类：性别 汇总方式：函数 汇总字段：一定是数值 确定删除汇总：在“分类汇总”对话框中选择全部删除4、合并计算：多个表中数据进行相加，要求：表的格式相同光标定位合计工作表中数据合并计算红K选择表1添加红K再选表2添加选上首行和最左列确定5、数据透视表：把一个数据表按照某一种函数运算之后的结果显示出来，把该表叫透视表选中表-数据-数据透视表及透视图-向导（3步）1）数据源类型：excel中2）数据源(利用“伸缩框选择”)3）布局：拖动方法；选项-确定6、单变量求解：已知结果和一个参与运算的数据，求另一个参与运算的数据（未知）；要求：必须含有公式先设定公式工具单变量求解目标单元格（结果），可变单元格（参与运算数据，未知）7、保护： 1)设定打开权限密码：工具选项安全性打开权限密码：重复两次确定保存 2)保护工