第一讲制作电子表格的操作技巧
一、选取的特殊技巧：
选定不相邻的文本矩形区域：按着＂ctrl”＂键，然后单击所选定的单元格（可拖拉）
选定相邻的多张工作表：单击要选择的第一工作表标签，然后按着shift键，再单击最后一张工作表，（不拖拉）
选定不相邻的多张工作表：先单击想要选定的第一张工作表的标签，按住＂ctrl＂键分别单击我们所要选定的工作表标签．
二、工作表的插入、删除、移动、复制及其它：
插入工作表：选择＂插入＂菜单中的＂工作表＂；
删除工作表：选择＂编辑＂菜单中的＂删除工作表＂
移动（在工作簿中）鼠标法:单击标签，然后拖拉到相应位置.
复制（在工作簿中）鼠标法：按下ctrl+拖拉到相应位置
移动和复制到另外一本工作簿菜单法“:编辑”菜单的“移动和复制”（如下图）
重新命名工作表：＂双击＂选中的工作表的标签或“格式”菜单下的“工作表”中的“重命名”.
分割工作表：利用横向和竖向滚动条上的分割符号进行拖动；或窗口（菜单）分割窗口
取消分隔：双击分隔符
注：分割后的工作表还是一张工作表，对任一窗格内容的修改都会反映到另一窗格．
隐藏表格线：工具（菜单）选项“视窗＂中的“网格线”（批注）
在工作表中增加注释：利用＂插入＂菜单中的＂批注＂．
工具栏的显示/隐藏：“视图”菜单下的“工具栏”中的“各选项”
三、输入特殊数据的技巧：
输入文字：
默认的单元格宽度是8个字符宽,如果超过8个字符时,我们可以通过＂格式＂中的＂列＂中的＂列宽＂改变宽度，或者使用鼠标移动，但一个单元格最多可以输入122汉字．
改变单元格内的汉字排列格式：＂格式＂中的＂单元格＂中的＂对齐＂,标题应采用"合并单元格"的方法。
对于以”0”开头的数字串,实际属于＂字符串＂,应按’0592方法输入（即先输入‘）或者对选定的单元格设定为文字格式(通过”格式”菜单中的”单元格”中的“数字”下的“文本”．
输入数字：我们可以通过＂格式＂中的＂单元格＂中的＂数字＂来设置＂数字输入＂的各种格式，其中注意小数（7.89）．科学记数（1.23E+0.8）＝12356789．分数（0７(８）．
输入日期和时间：
我们在以12小时计时时，数字与字母之间必须有一空格，ａｍ／ａ．ｐｍ／ｐ分别代表上午，下午，例：5:00(ａｍ．
在既输入日期,又输入时间,日期与时间之间也必须有空格,键入日期时,我们可以使用斜杠(＼)或连字符(--)，但我们可以选定不同的输出格式．
二、相同数据的输入：
同时对多个单元格输入相同的数据
选定要输入相同数据的单元格区域
输入数据（此数据会出现在选定单元格的左上角的第一单元格）
同时按下Ctrl+回车
同时选定工作表组
选定工作表组
在工作表组中的一张工作表内输入数据，那么该数据也会反映到这组工作表中
三.输入的自动化：对一些有规则的数据（等差，等比，星期((星期日等）．利用＂编辑＂菜单中的＂填充＂中的＂序列＂．
其基本步骤：
在序列中的第一个单元格中输入初值，例如：1
用拖拉选定区域．
选择＂编辑＂中的＂填充”中的”序列”中的自动填充、等差、等比（步长值）．
如图3-1
图3-1
(5)、自定义序列：工具（菜单）选项…自定义序列
四、编辑工作表
1、编辑（修改．插入，删除）单元格内容，先选定所要编辑的单元格，然后双击．
2、编辑栏中的数据：先选定所要编辑的单元格，然后单击上面的编辑栏．
3、变单元格中的字体、大小、颜色和对齐方式（注意分散对齐）．
执行＂格式＂中的＂单元格＂中的各选项(此项内容非常丰富．如下图：
第二讲公式的输入及函数应用
一、在公式中使用运算符号：
＋（加法）－（减法）（乘法）／（除法）(（乘方）&(文字运算符号)，
例＝＂本月＂＆＂销售＂＝本月销售、＜＞（不等于）、＜＝（小于等于）(其运算的优先级跟数学上差不多，若要改变，请加括符(其中要注意输入负数时，不能加括符，例：＝5－10＝－50．
三、公式位置的引用．
单元格地址的输入：例：＝Ａ1+B2+C3
位置引用符号及说明
符号 说明 区域(：冒号） 引用位于两个引用位置之间的所有单元格，并包括这两个引用位置a1:b3 合集（，；） 引用两个指定的引用位置,例:Ｃ3,Ｃ6.；逗号和分号 交集（空格） 引用两个引用位置的公共单元,例:a2:c2(b1:b3实际为b2 相对地址引用：也就是当我们把一单元格里的公式拷贝到另外的单元格时，其格式会发生相对变化，也就是说公式的各个单元格地址会根据(2),相对于(1)的相对位置变化.．
公式单元格 拷贝后单元格地址 (1)(b2 =a1+a2+C6((1)2 (2)(C2 =b1+b2+d6((2)2 E2 =d1+d2+F6 绝对地址引用：就是在我们要把公式拷贝或填入到新位置，并且使那些固定单元格地址保持不变时使用，通常我们是在行号和列号前面添加美无＂(＂
混合地址引用：混合地址是指只有行或者列变为绝对地址．
三维地址引用：是指在一本工作簿中不同的工作表引用单元格，三维引用的一般格式为：工作名！单元格地址，例：＝sheet1！ａ1+ａ2(注意:其中的a2是指当前工作区的a2)
数组：就是单元的集合或是一组处理的值集合
数组的输入：
（1）输入公式如：=B2：B4C2：C4 结果放于三格。
=SUM（B2：B4C2：C4）结果放于一格
=B2:B4{120；140；160}结果放于三格，逗号表示水平，分号表垂直。
(2)Ctrl+shift+Enter
数组的扩充：在使用数组时，其它运算对象应该和第一个数组具有相同的维数。必要是Excel会将运算对象扩展，以符合操作需要的维数。
如：=SUM（{1，2，3}+4）会自动将数值扩充成=SUM（{1，2，3}+{4，4，4}）
五、控制重算的方式:
主要是通过＂工具＂菜单中的＂选项＂中的＂重新计算＂里面的各种设置来改变它，在公式中之所以其结果能自动改变，是因为上述对话框中设为“自动重算”，若设为“人工重算”，其单元格的值被改变时，公式的值不会自动改变，还得设置“重算所有文档”
六．使用自动求和按钮：(其中分四种情况：
把多个单元格的数值之和放在一个单元格中：
其步骤：
先选定放置求和结果的单元格
按下＂自动求和＂按钮
用鼠标改变虚框的范围．
按下＂确认＂或＂回车键＂
在Excel中,我们还能够利用自动求和按钮一次输入多个求和公式．例如对图5-1表中的部门和产品分别求总计,我们只需先选定总计栏中的＂B5:D5”单元格区域,然后按下＂求和＂按扭，或选＂E2：E5”单元格区域,按下＂确认＂按钮即可．(对列或行求和．
此外，我们还可以利用选定操作对不相邻的单元格自动求和．
对行、列一起求和:全部选定
第二讲之二：函数
一、基本概念：（函数）
参数：我们传给函数用来执行运算的数值．各个参数需要用括号（）括起来．
结果：函数返回的数值称为结果，(工作表函数能自动实现决策，执行和数值返回．
语法：在公式中使用的字符次序称为语法，所有函数都有相同的基本语法．如果一个公式以函数开头，也要象输入其它公式一样，输入一个＂等号＂．
二、有关函数的约定：
当我们手工输入函数时，需要一个等号,例：＝A1-A3（Sum(B2:C3)100)+100和＝sqrt(B1);
当我们直接使用＂函数指南f(x)或插入\函数＂中的＂函数＂进行运算，不需要人为输入等号，系统在编辑栏自动产生一个等号．
(在使用参数时，我们一定要注意必选项（粗体）和任选项（非粗体）．
(对于参数，我们可以用区域代表一个参数（如：Sum(A1：A5)因为函数中仅能带30个参数．
三、其它规定：
参数名和参数类型：
参数名：我们可以根据参数的名称来判断参数的类型，以免输入的数据类型错误
参数类型：数，文字（需要用双引号），逻辑值（True,False）.错误值(#NAME?#Null!)；引用（(A(10）．数组Sum(B2:D2B3:D3).
参数表中使用逗号：（高级班才讲）
(用户必须用逗号分隔单个参数，但应注意不要额外键入逗号，因为一个逗号将代表一个参数，例：Aver(1,2,3,4,5)返回值3,而Aver(,,l,2,3,4,5)却返回2.14.
(注意:如果将引用作为一个参数,而且这一引用使用逗号做合并运算,则用括弧将引用括起来.例：Areas((A1,C1)).
(注意：在公式中输入函数：
例：＝A1-A3／Sum(B2:C3)100)+100
错误值的意义：
错误值 #DIN/01 公式被零除． #N/A 没有可用的数值．通常情况是，您将数值直接输入某些工作表单元格．虽然这些单元格中会包含数据，但目前尚无数据．引用这些单元格的公式将返回 #N/A, 而不会计算数值． #NANE? MicrosoftExcel不识别公式中使用的名字. #NULL! 指定的两个区域不相交. #NUM! 数字有问题.． #REF! 公式引用了无效的单元格． #VALUE! 参数或操作数的类型有错． 具体函数实例：
SUM（）求和
AVERAGE（）求平均值
COUNT（）统计参数的个数
FACT（）求阶乘例：FACT（5）=120
POWER（）幂函数210=POWER（2，10）
LOG（）求对数LOG21024=LOG（1024，2）
MAX（）
MIN（）
STDEV（）估算样本的标准偏差
财务函数
RATE（）求利率
语法：RATE（NPER，PMT，PV，FV，TYPE）
其参数的含义：
NPER：期数PMT：付款数PV：现值FV：未来值TYPE：类型
例：金额为8000的4年期贷款，月支付款为200，则该笔贷款的月利率为多少？
=RATE（412，-200，8000，0，0）
PMT（RATE，NPER，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，2012，0，50000，1）
练习：若每月向银行定期存款800，年利率为7%，那么30年后银行存款应为多少？（用FV（）
逻辑函数：
AND（）判断参数的逻辑值，参数只要一个为假，则输出为假；参数全为真，则输出为真。
OR（）只要一个为真，则输出为真，全假则输出为假。
IF（）执行真假值判断，根据逻辑测试的真假值返回不同的结果。
AND(A1>0,A2>0,A3>0):输出的结果是”真”AND(A1>0,A2>0,A3<0):输出的结果是”假”
OR(A1>0,A2>0,A3>0):输出的结果是”真”OR(A1<0,A2>0,A3>0):输出的结果是”真”
OR(A1<0,A2<0,A3<0):输出的结果是”假”
IF(A1>0,A12,A1/2):输出的结果是”110”IF(A2<0,A12,A1/5):输出的结果是”11”
SUMIF(A1:B7,“>50”,A1:B7)其含义是:求A1:B7大于50的单元格的和
函数嵌套：
例：IF(AVERAGE(A1:A3)>50,MAX(A1:A3),SUM(A1:A3))
含义是:如果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(c3<1200,0,if(c3<1700,(c3-1200)5%,if(c3<3000,(c3-1200)10%-25,if(c3<8000,(c3-1200)15%-125))))结果如下图：
备注：所有的函数都可利用函数指南来操作，但一定要注意数值类型与函数类型要配比.
第三讲：对数据的排序、筛选、分类汇总
记录单的创建
输入记录单的字段名
选定并执行数据/记录单确定
在各字段中键入记录单的值
按“回车”即加入一条记录
记录的插入：执行插入/单元格整行确定
记录的查找：1，执行数据/记录单键入欲查找记录的值按“回车”
排序：执行＂数据＂菜单中的”排序＂，其中可以按照主关键字，次关键字，第三关键字，依次排列(也是说,首先按照主关键字排序,当主关键字中有相同字段时,再根据次关键字排序,依次类排).（图4-4）
图4-4
筛选：执行＂数据＂菜单中的“筛选”中的“自动筛选或高级筛选”
自动筛选：是指在分类汇总的基础上，选择某一部份（类别）的数据
步骤：
（1）、选定要筛选的单元格。
（2）、数据（菜单）筛选自动筛选
自定义筛选：
（1）、在建立筛选的基础上，利用向下的箭头，选择自定义选项
（2）、在自定义自动筛选方式对话框中进行条件设定（可同时设定两个条件）
高级筛选：一般用在于条件比较复杂的寻找。
设定条件区域
在数据清单的前方插入几个空行
输入要设定条件的字段名称及条件
若要对不同的列指定多重条件，请在条件区域的同一行输入所有的条件（条件标记必须和我们想评价的列标记相同）
若要相同的列指定不同的条件，请把条件输入不同的行上
2）数据（菜单）筛选高级筛选
根据对话框进行设置
三、分类汇总
对欲分类的字段进行排序
执行数据/分类汇总
选择分类字段及汇总方式和汇总项确定
多级分类汇总：
进行两列以上排序
给主分类段分类汇总
给次分类段分类汇总并使“替换当前分类汇总”无效确定
四、模拟运算表
单模拟运算表：
单变量模拟运算表的结构特点是，其输入数值被排列在一列中（列引用）或一行中（行引用）。单变量模拟运算表中使用的公式必须引用。
在一列或一行中，键入要替换工作表上的输入单元格的数值序列。
如果输入数值被排成一列，请在第一个数值的上一行且处于数值列右侧的单元格中，键入所需的公式。在同一行中，在第一个公式的右边，分别键入其它公式。
如果输入数值被排成一行，请在第一个数值左边一列且处于数值行下方的单元格内，键入所需的公式。在同一列中，在第一个公式的下方，分别键入其它公式。
选定包含公式和需要被替换的数值的单元格区域。
在“数据”菜单中，单击“模拟运算表”命令。如果模拟运算表是列方向的，请在“输入引用列的单元格”编辑框中，为输入单元格键入引用。
如果模拟运算表是行方向的，请在“输入引用行的单元格”编辑框中，为输入单元格键入引用。双变量模拟运算表中的两组输入数值使用同一个公式。这个公式必须引用两个不同的。
在工作表的某个单元格内，输入所需的引用两个输入单元格的公式。
在公式下面同一列中键入一组输入数值，在公式右边同一行中键入第二组输入数值。
选定包含公式以及数值行和列的单元格区域。
在“数据”菜单中，单击“模拟运算表”命令。在“输入引用行的单元格”编辑框中，输入要由行数值替换的输入单元格的引用。
在“输入引用列的单元格”编辑框中，输入要由列数值替换的输入单元格的引用。
单击数据/数据透视表达式下一步下一步
拖动右边的字段按钮到图中来做成透视表的行列数据例如把日期作为页字段，产品作为行字段，地区作为列字段，销售额作为数据项如下图：
最后结果是：
修改透视表
行列互换
（1），在列字段名单元格A4按下左键(拖曳到行字段中即可。（行同上）
添加和删除字段
单击工具栏上的“数据透视表”/向导(直接将欲添加的字段拖曳到透视表中将欲删除的字段拖曳出来。
刷新透视表（有以上两种方法）
执行数据/更新数据
单击“数据透视表”工具栏上的“！”
对数据透视表排序
（1），右单击字段名（如：地区）(字段…(高级(选择排序项(确定(确定如下图：
改变透视表的汇总方式
单击工具栏上的“数据透视表”/字段…(如下图)
六、显示和隐藏明细数据
数据透视表能够对数据进行分类汇总，也可显示或隐藏明细数据。
（1）、右单击透视表中的列字段名（如“地区”）
（2）、执行“组及分级显示”(显示明细数据
（3）、在对话框中选择明细数据所在的字段，（如“产品”）(确定如下图）
创建计算字段
（1），右单击透视表(公式(计算字段(弹出对话框
（2），在“名称”栏中输入计算字段的名称，在“公式”栏中输入计算字段的公式，如下图：
（3）、单击“添加”(确定
最后的结果是：
图表的创建：
图表是将数据用图形的形式表示出来
使用向导创建图表
将光标定在表格中(单击工具栏上的“图表向导”按钮(选择图表的类型（如下图）
下一步(按向导完成余下的操作
图表类型的更改：
右单击图表(图表类型(重新选择图表的类型
其它项的更改：
最快的方法是：需改什么就双什么
如改文字则双击文字
使用趋势线
（1）、激活图表(执行“图表”菜单中“添加趋势线”(选择类型(选择数据系列(确定
使用误差线：
单击图表中要添加误差线的数据系列如“渔类”(执行格式/数据系列(选择“误差线Y”
选择“正负偏差”(在“误差量”栏中选择百分比(确定
地图数据将在电脑上演示
第五讲：数据高级分析与决策
规划求解
简介：规划求解是数学中的优化问题，它通过改变多个输入单元格求出最优解，同时保证工作表中的其他公式保持在设置的极限之内。
下一实例说明规划求解的应用的操作步骤。
某个厂家，在一段时间内生产两种产品，分别是产品A和产品B。受原材料的限制，产品A的产量不得超过4000，产品B的产量不超过4000；受机器和人员以及时间的限制，两种产量之和不得超过7000。产品A的单件利润为200元，每多生产100件，由于成本的降低，单件利润增加2元。产品B的单件利润为190元，每多生产100件单件利润增加3元。现在进行规划求解，求出产品A和产品B产量的最佳方案，使总利润最大。
（1）、新建一张表，其中B4单元格的公式是“=B2+B3”，B6中的公式是
=（200+B2/1002）B2+（190+B3/1003）B3
（2）、执行工具/规划求解(如图
（3）、选择“最大值”，设置目标单元格为$B$6,可变单元格为$B$2:$B$3
(4)、单击添加(弹出“添加约束”对话框(输入约束值(添加
（5）、重复第（4）步，把所有约束条件都添加到“规划求解参数”对话框的“约束”列表框中，如下图
（6）、单击“求解”(确定
最后的结果是：产品A为3000产品B为4000总利润率为：2020000
练习题：用一块长20米，宽1米的铅皮靠墙围成一个矩形，若要矩形的体积最大，则此矩形的长与宽各应是多少米最合适。
方案管理器
方案是对问题不同的设想，以便观察对问题的影响，结论出最佳的投资方案。
实例：
新建如下表：其中C4单元格中的公式是“=B4（1+$B$16）”,将这个公式复制到D4和E4;C7中的公式是’=B7(1+$B$17)’,将其复制到D7和E7;C8中的公式是’=B8(1+$B$18)’,将其复制到D8和E8;C9中的公式是’=B9(1+$B$19)’,将其复制到D9和E9;总计一行的单元格中的公式都是三种产品成本的和;净收入一行的单元格中的公式都是每一年的销售额减去总计销售成本;E18单元格中显示几年的总净收入,它是各年净收入的和;B16到B19单元格显示的是销售额和各种产品销售成本的年增长率.这个工作表根据销售额和各种产品销售成本的年增长率来估计未来几年公司的销售额情况和净收入情况,并计算这几年的总净收入.对此可以建立最好情况估计和最坏情况估计两个方案.
创建步骤如下:
(1),执行工具/方案(添加(在方案名框中键入”最好情况估计”,(因为这里是用年增长率来估计销售情况)在可变单元格选为$B$16:$B$19,如下一图:(确定
(2),依次键入变量的值.如下二图(添加(重复上步编辑”最坏情况估计”方案(确定(关闭
显示方案:
执行工具/方案(选择要显示的方案名(单击”显示”
创建方案总结报告:
执行工具/方案(单击”方案总结”(选择类型(在”结果单元格”文本框中输入方案有效结果的单元格引用(如:$E$18)(确定
最后结果是
有效数据的定义(例:学生成绩分是介于0至100之间)
方法:(1),选择要定义有效数据的式区域
(2),执行数据/有效数据
(3),在’设置’选卡的’许可’下拉列表中选择’小数’,在’数据’列表中选择’介于’,在’最小值’文本框中输入0,最大值为100如下图(确定
标识单元格中的错误数值：
(1),选中欲标识的数据区域
(2),执行工具/审核/显示审核工具栏
(3)单击此工具栏上的’圈释无效数据’
如下图:
宏的创建
(1),打开’工具’/宏/录制新宏
(2),在’宏名’中键入宏的名字(可以定义快捷键如:CTRL+E)(确定
(3),在工作表中,执行一系列的操作
(4),操作完成后单击’停止’
例:创建一个使表格内容居中的宏(在电脑上演示)
注:按快捷键将运行宏
自定义函数
例：将公式
IF（AND（A1>=0,A1<=9999.99）,A10.8,IF(AND(A1>=10000,A1<=19999.99),A10.105,IF(AND(A1>=20000,A1<=39999.99),A10.12,IF(AND(A1>=40000),A10.14)))）
编辑成自定义函数,其代码如下:
Functioncommission(Sales)
Tierl1=0.08
Tierl2=0.105
Tierl3=0.12
Tierl4=0.14
SelectCaseSales
Case0to9999.99:Commission=SalesTierl1
Case10000to19999.99:Commission=SalesTierl2
Case20000to39999.99:Commission=SalesTierl3
Caseis>=40000:Commission=SalesTierl4
EndSelect
EndFunction
第六讲：链接与EXCEL操作技巧
与WORD的信息交换
执行插入/对象(选择“从文件创建”标签(单击‘浏览’(选择欲交换信息的WORD文档(插入(选中“链接”可产生链接效果(确定
二、不同工作表间或工作薄间的链接
实例：有三个工作簿文件，分别是期中成绩.XLS,期末成绩.XLS,成绩单成绩.XLS.而成绩单成绩.XLS是引用前两个文件的数据来计算学期成绩的,计算公式是成绩单成绩=0.4期中成绩+0.6期末成绩.其步骤为:
(1),打开这三个工作簿.
(2),选定成绩单成绩.XLS中欲填的数据区域(如B3:E7)如下图
(3),在编辑栏中输入=0.4
(4),单击期中成绩.XLS,选定B3:E3数据区域
(5),在编辑栏中接着输入+0.6
(6),单击期末成绩.XLS,选定B3:E3数据区域
如图:
(7),按CTRL+SHIFT+ENTER组合键
注:当被链接的文件数据变动时,则目标文件自动更新
EXCEL的操作技巧
(1),填充柄的妙用
(2),工作表的保护,密码的设置,公式的隐藏
(3),窗口的拆分,冻结拆分窗口
(4),工具栏的定置
欢迎你到厦门来！
1
|
|
