EXCEL高级教案

1、欢迎你到厦门来！第一讲 制作电子表格的操作技巧一、选取的特殊技巧：1、 选定不相邻的文本矩形区域：按着ctrl”键，然后单击所选定的单元格（可拖拉）2、 选定相邻的多张工作表：单击要选择的第一工作表标签，然后按着shift键，再单击最后一张工作表，（不拖拉）3、 选定不相邻的多张工作表：先单击想要选定的第一张工作表的标签，按住ctrl键分别单击我们所要选定的工作表标签二、工作表的插入、删除、移动、复制及其它：1. 插入工作表：选择插入菜单中的工作表；2. 删除工作表：选择编辑菜单中的删除工作表3. 移动（在工作簿中）鼠标法: 单击标签，然后拖拉到相应位置.4. 复制（在工作簿中）鼠标法：按下

2、ctrl+拖拉到相应位置移动和复制到另外一本工作簿菜单法“:编辑”菜单的“移动和复制”（如下图）5. 重新命名工作表：双击选中的工作表的标签或“格式”菜单下的“工作表”中的“重命名”.6. 分割工作表：利用横向和竖向滚动条上的分割符号进行拖动；或窗口（菜单） 分割窗口7. 取消分隔：双击分隔符注：分割后的工作表还是一张工作表，对任一窗格内容的修改都会反映到另一窗格8. 隐藏表格线：工具（菜单） 选项 “视窗中的“网格线”（批注）9. 在工作表中增加注释：利用插入菜单中的批注10. 工具栏的显示/隐藏：“视图”菜单下的“工具栏”中的“各选项”三、输入特殊数据的技巧：1. 输入文字：(1) 默认的

3、单元格宽度是8个字符宽,如果超过8个字符时,我们可以通过格式中的列中的列宽改变宽度，或者使用鼠标移动，但一个单元格最多可以输入122汉字(2) 改变单元格内的汉字排列格式：格式中的单元格中的对齐,标题应采用合并单元格的方法。(3) 对于以”0”开头的数字串, 实际属于字符串, 应按 0592方法输入（即先输入） 或者对选定的单元格设定为文字格式(通过”格式”菜单中的”单元格”中的“数字”下的“文本”2. 输入数字：我们可以通过格式中的单元格中的数字来设置数字输入的各种格式，其中注意小数（7.89）科学记数（1.23e+0.8）12356789分数（0/）3. 输入日期和时间：（1） 我们在以1

4、2小时计时时，数字与字母之间必须有一空格，分别代表上午，下午，例：5:00（2） 在既输入日期,又输入时间,日期与时间之间也必须有空格,键入日期时,我们可以使用斜杠()或连字符(-)，但我们可以选定不同的输出格式二、相同数据的输入：1、 同时对多个单元格输入相同的数据 选定要输入相同数据的单元格区域 输入数据（此数据会出现在选定单元格的左上角的第一单元格） 同时按下ctrl+回车2、 同时选定工作表组 选定工作表组 在工作表组中的一张工作表内输入数据，那么该数据也会反映到这组工作表中三. 输入的自动化：对一些有规则的数据（等差，等比，星期kk星期日等）利用编辑菜单中的填充中的序列其基本步骤：（

5、） 在序列中的第一个单元格中输入初值，例如：1（） 用拖拉选定区域（） 选择编辑中的填充”中的 ”序列”中的自动填充、等差、等比（步长值）如图3-1图3-1(5)、自定义序列：工具（菜单） 选项 自定义序列四、编辑工作表1、编辑（修改插入，删除）单元格内容，先选定所要编辑的单元格，然后双击2、编辑栏中的数据：先选定所要编辑的单元格，然后单击上面的编辑栏3、变单元格中的字体、大小、颜色和对齐方式（注意分散对齐）执行格式中的单元格中的各选项此项内容非常丰富如下图：第二讲 公式的输入及函数应用一、在公式中使用运算符号：（加法）（减法）*（乘法）（除法） （乘方）&(文字运算符号)，例本月销售本月销售

6、、 （不等于）、（小于等于）其运算的优先级跟数学上差不多，若要改变，请加括符其中要注意输入负数时，不能加括符，例：5*1050三、公式位置的引用1. 单元格地址的输入：例：1+b2+c32. 位置引用符号及说明符号说明区域(：冒号）引用位于两个引用位置之间的所有单元格，并包括这两个引用位置a1:b3合集（，；）引用两个指定的引用位置,例:3,6.；逗号和分号交集（空格）引用两个引用位置的公共单元,例:a2:c2ob1:b3 实际为b23. 相对地址引用：也就是当我们把一单元格里的公式拷贝到另外的单元格时，其格式会发生相对变化，也就是说公式的各个单元格地址会根据(2),相对于(1)的相对位置变化

7、.公式单元格拷贝后单元格地址(1)b2=a1+a2+c6(1)2(2)c2=b1+b2+d6(2)2e2=d1+d2+f64. 绝对地址引用：就是在我们要把公式拷贝或填入到新位置，并且使那些固定单元格地址保持不变时使用，通常我们是在行号和列号前面添加美无$5. 混合地址引用：混合地址是指只有行或者列变为绝对地址6. 三维地址引用：是指在一本工作簿中不同的工作表引用单元格，三维引用的一般格式为：工作名！单元格地址，例：sheet1！1+2(注意:其中的a2是指当前工作区的a2)7. 数组：就是单元的集合或是一组处理的值集合数组的输入：（1）输入公式 如：=b2：b4*c2：c4结果放于三格。=s

8、um（b2：b4*c2：c4） 结果放于一格=b2:b4*120；140；160结果放于三格，逗号表示水平，分号表垂直。(2) ctrl+ shift+ enter数组的扩充：在使用数组时，其它运算对象应该和第一个数组具有相同的维数。必要是excel会将运算对象扩展，以符合操作需要的维数。如：=sum（1，2，3+4）会自动将数值扩充成=sum（1，2，3+4，4，4）五、控制重算的方式:主要是通过工具菜单中的选项中的重新计算里面的各种设置来改变它，在公式中之所以其结果能自动改变，是因为上述对话框中设为“自动重算”，若设为“人工重算”，其单元格的值被改变时，公式的值不会自动改变，还得设置“重算

9、所有文档”六使用自动求和按钮：其中分四种情况：1. 把多个单元格的数值之和放在一个单元格中：其步骤：(1) 先选定放置求和结果的单元格 (2) 按下自动求和按钮 (3) 用鼠标改变虚框的范围(4) 按下确认或回车键2. 在excel中,我们还能够利用自动求和按钮一次输入多个求和公式例如对图5-1表中的部门和产品分别求总计,我们只需先选定总计栏中的b5:d5”单元格区域,然后按下求和按扭，或选e2：e5”单元格区域,按下确认按钮即可对列或行求和3. 此外，我们还可以利用选定操作对不相邻的单元格自动求和4. 对行、列一起求和:全部选定第二讲之二：函数一、基本概念：（函数）参数：我们传给函数用来执行

10、运算的数值各个参数需要用括号（）括起来结果：函数返回的数值称为结果，工作表函数能自动实现决策，执行和数值返回语法：在公式中使用的字符次序称为语法，所有函数都有相同的基本语法如果一个公式以函数开头，也要象输入其它公式一样，输入一个等号二、有关函数的约定：1. 当我们手工输入函数时，需要一个等号,例：a1-a3（sum(b2:c3)*100)+100和sqrt(b1);2. 当我们直接使用函数指南f(x)或插入函数中的函数进行运算，不需要人为输入等号，系统在编辑栏自动产生一个等号在使用参数时，我们一定要注意必选项（粗体）和任选项（非粗体）对于参数，我们可以用区域代表一个参数（如：sum(a1：a5

11、)因为函数中仅能带30个参数三、其它规定：1. 参数名和参数类型：参数名：我们可以根据参数的名称来判断参数的类型，以免输入的数据类型错误参数类型：数，文字（需要用双引号），逻辑值（true, false）.错误值(#name? #null!)；引用（$a$10）数组sum(b2:d2*b3:d3).2. 参数表中使用逗号：（高级班才讲）用户必须用逗号分隔单个参数，但应注意不要额外键入逗号，因为一个逗号将代表一个参数，例：aver(1,2,3,4,5)返回值3, 而aver(, , l,2,3,4,5)却返回2.14.注意: 如果将引用作为一个参数, 而且这一引用使用逗号做合并运算,则用括弧将引

12、用括起来.例：areas(a1,c1).注意：在公式中输入函数：例：a1-a3sum(b2:c3)*100)+1003. 错误值的意义：错误值#din/01公式被零除#n/a没有可用的数值通常情况是，您将数值直接输入某些工作表单元格虽然这些单元格中会包含数据，但目前尚无数据引用这些单元格的公式将返回#n/a,而不会计算数值#nane?microsoft excel不识别公式中使用的名字.#null!指定的两个区域不相交.#num!数字有问题.#ref!公式引用了无效的单元格#value!参数或操作数的类型有错具体函数实例：1， sum（） 求和2， average（） 求平均值3， count

13、（） 统计参数的个数4， fact（） 求阶乘 例：fact（5）=1205， power（） 幂函数 210=power（2，10）6， log（） 求对数 log21024=log（1024，2）7， max（）8， min（）9， stdev（） 估算样本的标准偏差财务函数1， rate（）求利率语法：rate（nper，pmt，pv，fv，type）其参数的含义：nper：期数 pmt：付款数 pv：现值 fv：未来值 type：类型例：金额为8000的4年期贷款，月支付款为200，则该笔贷款的月利率为多少？ =rate（4*12，-200，8000，0，0）2， pmt（rate，n

14、per，pv，fv，type）例：若需要10个月付清年利率为8%的10000贷款的月付款为多少？ =pmt（8%/12，10，10000，0，0）又例：对于同一笔贷款，如果支付期在每期初，则： =pmt（8%/12，10，10000，0，1）（注：type=0 则为期未 type=1 则为期初）又例：如果需要以按月定期存款方式在20年内存款50000，假设年利率为6%，则每月应向银行支付多少？ =pmt（6%/12，20*12，0，50000，1）练习：若每月向银行定期存款800，年利率为7%，那么30年后银行存款应为多少？（用fv（）逻辑函数：and（）判断参数的逻辑值，参数只要一个为假，则

15、输出为假；参数全为真，则输出为真。or（） 只要一个为真，则输出为真，全假则输出为假。if（） 执行真假值判断，根据逻辑测试的真假值返回不同的结果。例：a1=55，a2=60，a3=30and(a10,a20,a30):输出的结果是”真” and(a10,a20,a30,a20,a30) :输出的结果是”真” or(a10,a30) :输出的结果是”真”or(a10,a20,a30,a1*2,a1/2): 输出的结果是”110” if(a250”,a1:b7)其含义是:求a1:b7大于50的单元格的和函数嵌套：例：if(average(a1:a3)50,max(a1:a3),sum(a1:a3

16、)含义是:如果a1,a2,a3三个数的均值50,则求a1,a2,a3三个数的最在值,否则, 求a1,a2,a3三个数的和. 如下图中函数:if(if(c4= 男,65-d4,60-d4)0,退职,if(c4=男,65-d4,60-d4)=if(c31200,0,if(c31700,(c3-1200)*5%,if(c33000,(c3-1200)*10%-25,if(c3=0,a1=10000,a1=20000,a1=40000),a1*0.14)）编辑成自定义函数,其代码如下:function commission(sales) tierl1=0.08 tierl2=0.105 tierl3=

17、0.12tierl4=0.14 select case sales case 0 to 9999.99: commission=sales* tierl1 case 10000 to 19999.99: commission=sales* tierl2case 20000 to 39999.99: commission=sales* tierl3case is=40000: commission=sales* tierl4end selectend function第六讲：链接与excel操作技巧一、 与word的信息交换执行插入/对象选择“从文件创建”标签单击浏览选择欲交换信息的word文档插入选中“链接”可产生链接效果确定二、不同工作表间或工作薄间的链接实例：有三个工作簿文件，分别是期中成绩.xls ,期末成绩.xls,成绩单成绩.xls.而成绩单成绩.xls是引用前两个文件的数据来计算学期成绩的,计算公式是成绩单成绩=0.4*期中