Excel综合教程.doc

华润电力江苏分公司办公自动化培训教材Excel部分EXCEL教案一、 制作电子表格的操作技巧2一、选取的特殊技巧：2二、工作表的插入、删除、移动、复制及其它：2三、输入特殊数据的技巧：3四、相同数据的输入：4五. 输入的自动化4六、编辑工作表5七、 公式6一、在公式中使用运算符号：6二、公式位置的引用6三、控制重算的方式:7四使用自动求和按钮：其中分四种情况：8二：函数9一、基本概念：（函数）9二、有关函数的约定：9三、其它规定：9四函数的操作方法10五、函数10六常用函数综合13三、对数据的排序、筛选、分类汇总16一、记录单的创建16二、排序17三、筛选17四、分类汇总18五、模拟运算表18六、单变量求解19七、透视表的创建19八、图表的创建：23四、数据高级分析与决策25一、规划求解25二、方案管理器26三、有效数据的定义27四、合并计算28五、宏的创建29六、链接与EXCEL操作技巧31一、 制作电子表格的操作技巧一、选取的特殊技巧：1、 选定不相邻的文本矩形区域：按着ctrl”键，然后单击所选定的单元格（可拖拉）2、 选定相邻的多张工作表：单击要选择的第一工作表标签，然后按着shift键，再单击最后一张工作表，（不拖拉）3、 选定不相邻的多张工作表：先单击想要选定的第一张工作表的标签，按住ctrl键分别单击我们所要选定的工作表标签4、 扩展选取F8的使用(矩形选择)5、 特殊的光标区域移动：TAB（向右），TAB+SHIFT（向左移动），ENTER（向下），SHIFT+ENTER（向上），CTRL+方向键（定位第一个和最后一个相同的单元格）6、 每次选定同一单元格直接按下Enter键会默认移到下一个单元格上，按“Ctrl+Enter”组合键，而活动单元格仍为当前单元格。7、 快速复制上面单元格内容填充。Ctrl+ Alt+向下键 用有效性定义数据系列8、 如何快速定位到单元格？方法一：按F5键，出现“定位”对话框，在引用栏中输入欲跳到的单元格地址，单市“确定”按钮即可。9、 方法二：单击编辑栏左侧单元格地址框，输入单元格地址即可。10、 如何快速选取特定区域？使用F5键可以快速选取特定区域。例如，要选取A2：A1000，最简便的方法是按F5键，出现“定位”窗口，在“引用”栏内输入需选取的区域A2：A1000。11、 快速选定包含公式的单元格执行“编辑定位”命令，（或按F5）在随后弹出的对话框中，按“定位条件”按钮，选中“公式”选项后确定，即可一次性选中包含“公式”的所有单元格。12、Excel中快速互换两列数据用鼠标选定A列数据区域；把鼠标放在A列数据区域的右边；按下“Shift”键的同时，按下鼠标左键，这时鼠标变为向左的箭头；拖动鼠标至B列数据区域的右边，看到一条垂直的虚线（如果看到一条水平的虚线，表示在B列插入数据），同时松开“Shift”键和鼠标左键，这样就实现了A、B列的数据互换。同样，也可以实现两行数据的互换。二、工作表的插入、删除、移动、复制及其它：1. 插入工作表：选择插入菜单中的工作表；2. 删除工作表：选择编辑菜单中的删除工作表3. 移动（在工作簿中）鼠标法: 单击标签，然后拖拉到相应位置.4. 复制（在工作簿中）鼠标法：按下 ctrl+拖拉到相应位置移动和复制到另外一本工作簿菜单法“:编辑”菜单的“移动和复制”（如下图）5. 重新命名工作表：双击选中的工作表的标签或“格式”菜单下的“工作表”中的“重命名”.6. 隐藏和保护工作溥。7. 保护工作表。（保护部份单元格）8. 分割工作表：利用横向和竖向滚动条上的分割符号进行拖动；或窗口（菜单） 拆分窗口9. 取消拆分：双击拆分，冻结窗口。注：分割后的工作表还是一张工作表，对任一窗格内容的修改都会反映到另一窗格10. 隐藏表格线：工具（菜单） 选项 “视窗中的“网格线”（批注）11. 在工作表中增加注释：利用插入菜单中的批注12. 工具栏的显示/隐藏：“视图”菜单下的“工具栏”中的“各选项”三、输入特殊数据的技巧：1. 文字方向。2. 一个单元格多行文字的方法。ALT+Enter3. 输入文字：(1) 默认的单元格宽度是8个字符宽,如果超过8个字符时,我们可以通过格式中的列中的列宽改变宽度，或者使用鼠标移动，但一个单元格最多可以输入122汉字(2) 改变单元格内的汉字排列格式：格式中的单元格中的对齐,标题应采用合并单元格的方法。(3) 对于以”0”开头的数字串, 实际属于字符串, 应按 0592方法输入（即先输入） 或者对选定的单元格设定为文字格式(通过”格式”菜单中的”单元格”中的“数字”下的“文本”4. 输入数字：我们可以通过格式中的单元格中的数字来设置数字输入的各种格式，其中注意小数（7.89）科学记数（1.23E+0.8）12356789分数（0 /或00 7/8）5. 输入日期和时间：（1） 我们在以12小时计时时，数字与字母之间必须有；空格，分别代表上午，下午，例：5:00（2） 在既输入日期,又输入时间,日期与时间之间也必须有空格,键入日期时,我们可以使用斜杠()或连字符(-)，但我们可以选定不同的输出格式6. 数字输入对于分数，在输入可能和日期混淆的数值时，应在分数前加数字“0”和空格。例如，在单元格中输入“2/3”，Excel将夺天工认为你输入的是一个日期，在确认输入时将单元格的内容自动修改为“2月3日”。如果希望输入的是一个分数，就必须在单元格中输入“0 2/3”，请注意0后面的空格。7. 输入的特殊技巧“ctrl+” 复制上一单元格的内容；“ctrl+;”快速输入当前日期，“ctrl+:”快速输入当前时间8. 单元格的快速复制（CTRL+右键拖拉复制单元格）9. 从原有数据（文本）中选择字段输入：在数组下面单击右键-选择列表10. 输入负数在单元格中输入负数时，可在负数前输入“-”作标识，也可将数字置在（）括号内来标识，比如在单元格中输入“（88）”，按一下回车键，则会自动显示为“-88”。9、自动定位小数位 因为有一大批诸如123.5之类的学生成绩需要录入，如果录入前先进行下面的设置，将会使你的输入速度成倍提高（可以不需要输入小数点）。 单击“工具”“选项”“编辑”选项卡，选中“自动设置小数点”复选框，在“位数”微调编辑框中键入需要显示在小数点右面的位数。在此，我们键入“1”（如图3）。单击“确定”按钮。四、相同数据的输入：1、 同时对多个单元格输入相同的数据 选定要输入相同数据的单元格区域 输入数据（此数据会出现在选定单元格的左上角的第一单元格） 同时按下Ctrl+回车2、 同时选定工作表组 选定工作表组 在工作表组中的一张工作表内输入数据，那么该数据也会反映到这组工作表中3、填充柄的应用。五. 输入的自动化：对一些有规则的数据（等差，等比，星期KK星期日等）利用编辑菜单中的填充中的序列其基本步骤：（） 在序列中的第一个单元格中输入初值，例如：1（） 用拖拉选定区域（） 选择编辑中的填充”中的 ”序列”中的自动填充、等差、等比（步长值）如图3-1图3-1(5)、自定义序列：工具（菜单） 选项 自定义序列六、编辑工作表1、编辑（修改插入，删除）单元格内容，先选定所要编辑的单元格，然后双击2、编辑栏中的数据：先选定所要编辑的单元格，然后单击上面的编辑栏3、如何快速地复制单元格的格式？要将某一格式化操作复制到另一部分数据上，可使用“格式刷”按钮。4、变单元格中的字体、大小、颜色和对齐方式（注意分散对齐、跨列居中、填充）执行格式中的单元格中的各选项此项内容非常丰富如下图：5、时间的加减运算时间和时期可以相加、相减，并可以包含到其他运算中。如果要在公式中使用日期或时间，请用带引号的文本形式输入日期或时间值。例如，公式2005/4/30-2002/3/20”，将得到数值1137。或=DATE(2006,4,7)-DATE(2006,3,28) 将得到数值10天，3、 单元格的数字自定义功能之一：格式-单元格-数字-自定义-如“abc！0#”格式：工程部：566工程部：788开发部：888开发部：998开发部：567七、 公式一、在公式中使用运算符号：（加法）（减法）*（乘法）（除法） （乘方）&(文字运算符号)，例本月销售本月销售、 （不等于）、（小于等于）其运算的优先级跟数学上差不多，若要改变，请加括符其中要注意输入负数时，不能加括符，例：5*1050二、公式位置的引用1. 单元格地址的输入：例：1+B2+C32. 位置引用符号及说明符号说明区域(：冒号）引用位于两个引用位置之间的所有单元格，并包括这两个引用位置如：a1:b3合集（，；）引用两个指定的引用位置,例:3,6.；逗号和分号交集（空格）引用两个引用位置的公共单元,例:a2:c2ob1:b3 实际为b23. 相对地址引用：也就是当我们把一单元格里的公式拷贝到另外的单元格时，其格式会发生相对变化，也就是说公式的各个单元格地址会根据(2),相对于(1)的相对位置变化.公式单元格拷贝后单元格地址(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结果放于三格。=SUM（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）三、控制重算的方式:主要是通过工具菜单中的选项中的重新计算里面的各种设置来改变它，在公式中之所以其结果能自动改变，是因为上述对话框中设为“自动重算”，若设为“人工重算”，其单元格的值被改变时，公式的值不会自动改变，还得设置“重算所有文档”四使用自动求和按钮：其中分四种情况：1. 把多个单元格的数值之和放在一个单元格中：其步骤：(1) 先选定放置求和结果的单元格 (2) 按下自动求和按钮 (3) 用鼠标改变虚框的范围(4) 按下确认或回车键2. 在Excel中,我们还能够利用自动求和按钮一次输入多个求和公式例如对图5-1表中的部门和产品分别求总计,我们只需先选定总计栏中的B5:D5”单元格区域,然后按下求和按扭，或选E2：E5”单元格区域,按下确认按钮即可对列或行求和3. 此外，我们还可以利用选定操作对不相邻的单元格自动求和4. 对行、列一起求和:全部选定二：函数一、 基本概念：（函数）一、 什么是函数Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM 函数对单元格或单元格区域进行加法运算。二、 嵌套函数所谓嵌套函数，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。也就是说一个函数可以是另一个函数的参数。例如图1中所示的公式使用了嵌套的 AVERAGE 函数，并将结果与 50 相比较。这个公式的含义是：如果单元格F2到F5的平均值大于50，则求F2到F5的和，否则显示数值0。参数：我们传给函数用来执行运算的数值各个参数需要用括号（）括起来结果：函数返回的数值称为结果，工作表函数能自动实现决策，执行和数值返回语法：在公式中使用的字符次序称为语法，所有函数都有相同的基本语法如果一个公式以函数开头，也要象输入其它公式一样，输入一个等号二、有关函数的约定：1. 当我们手工输入函数时，需要一个等号,例：A1-A3（Sum(B2:C3)*100）+100和sqrt(B1);2. 当我们直接使用函数指南f(x)或插入-函数中的函数进行运算，不需要人为输入等号，系统在编辑栏自动产生一个等号在使用参数时，我们一定要注意必选项（粗体）和任选项（非粗体）对于参数，我们可以用区域代表一个参数（如：Sum(A1：A5)因为函数中仅能带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.注意: 如果将引用作为一个参数, 而且这一引用使用逗号做合并运算,则用括弧将引用括起来.例：Areas(A1,C1).注意：在公式中输入函数：例：A1-A3Sum(B2:C3)*100)+1003. 错误值的意义：错误值#DIN/01公式被零除#N/A没有可用的数值通常情况是，您将数值直接输入某些工作表单元格虽然这些单元格中会包含数据，但目前尚无数据引用这些单元格的公式将返回#N/A,而不会计算数值#NANE?Microsoft Excel不识别公式中使用的名字.#NULL!指定的两个区域不相交.#NUM!数字有问题.#REF!公式引用了无效的单元格#VALUE!参数或操作数的类型有错具体函数实例：四函数的操作方法1、单击需要输入函数的单元格。2、单击编辑栏中插入函数按钮 ，将会在编辑栏下面出现一个插入函数选项板，此时名称框将变成函数按钮3、快捷输入函数的参数如果你只记得函数的名称，而记不清它的所有格式，你可以先在单元格中输入一个等号及函数名，然后按下Ctrl+A键，Excel则自动打开“函数参数”对话框，引导你快速输入函数的参数。五、函数一、数学函数SUBTOTALSUM(number1,number2, .) 返回某一单元格区域中所有数字之和SUMIF根据指定条件对若干单元格求和SUMPRODUCTSUMSQSUMX2MY2SUMX2PY2SUMXMY2POWER幂函数 210=POWER（2，10=1024LOG求对数 LOG21024=LOG（1024，2=10FACT 求阶乘 例：FACT（5）=120二文本/日期/时间函数MID从第几个字节开始取，共取几个LEFT取左边的字节，RIGHT取右边的字节，HOUR返回时间值的小时数。即一个介于 0 (12:00 A.M.) 到 23 (11:00 P.M.) 之间的整数MINUTE返回时间值中的分钟。即一个介于 0 到 59 之间的整数。DATE(year,month,day) 返回代表特定日期的系列数NOW等于当前时间，（没有参数）TODAY等于当前日期，（没有参数）WEEKDAY返回星期几UPPER（TEXT）将字符转为大写函数LOWER（TEXT）将字符转为小写函数SEARCH返回从 start_num 开始首次找到特定字符或文本串的位置上特定字符的编号(第几个编号）LEN返回文本串中的字符个数VALUE(text)将代表数字的文字串转换成数字三查询与引用函数HLOOKUP在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定行处的数值。（水平方向查找）VLOOKUP在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。（垂直方向查找）LOOKUP从单行或单列区域或者从一个数组返回值MATCH返回在指定方式下与指定数值匹配的数组MOD返回两数相除的余数。结果的正负号与除数相同ROW返回指定引用的行标ROWS返回引用或数组的行数COLUMN返回指定引用的列标CEILING将数值向上取舍最接近的参数或倍数。FLOOR将数值向下取舍最接近的参数或倍数。INDEX返回表或区域中的值或值的引用四统计函数AVERAGE求参数的算术平均值函数AVERAGEA求参数的算术平均值函数（包含所有不为空的单元格TRIMMEAN求数据集的内部平均值。函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。比如，我们在计算选手平均分数中常用去掉一个最高分，去掉一个最低分，XX号选手的最后得分，就可以使用该函数来计算COUNT用于求单元格个数的统计函数(有效数值单元格，字符除外）COUNTA用于求单元格个数的统计函数(非空单元格个数，包括数值、字符等单元格）COUNTIF用于求某个区域中给定条件的单元格数目COUNTBLANK用于求单元格个数的统计函数(统计空单格个个数）FREQUENCY求区域中数据的频率分布MAX求数据集的最大值MAXMIN求数据集的最小值MINLARGE求数据集中第K个最大值LARGESMALL求数据集中第k个最小值SMALLMODE求数据集中出现频率最多的数MEDIAN求数据集中的中位数RANK一个数值在一组数值中的排位的函数PERCENTRANK求特定数值在一个数据集中的百分比排位的函数五、数据库函数DCOUNT计算列表或数据库的列中满足指定条件并且包含数字的单元格个数(条件可为表达式）DMAX计算列表或数据库的列中满足指定条件并且包含数字的最大数DSUM返回数据清单或数据库的指定列中，满足给定条件单元格中的数字之和。DAVERAGE返回数据库或数据清单中满足给定条件的数据列中数值的平均值INT将数字向下舍入到最接近的整数六、财务函数FVNPVDB使用固定余额递减法，计算一笔资产在给定期间内的折旧值。PMT计算在固定利率下，固定代款额的分期偿还额PV返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。七、逻辑函数AND判断参数的逻辑值，参数只要一个为假，则输出为假；参数全为真，则输出为真。OR只要一个为真，则输出为真，全假则输出为假。NOTFALSEIF条件函数TRUE六常用函数综合函数名作用SUM返回某一单元格区域中所有数字之和SUMIF根据指定条件对若干单元格求和AVERAGE求单元格区域中所有数字的平均值NOW返回当前日期和时间所对应的系列数。TODAY返回当前的日期HOUR返回时间值的小时数MINUTE返回时间值中的分钟YEAR返回时间值中的年份WEEKDAY返回某日期为星期几SUM(number1,number2, .) 返回某一单元格区域中所有数字之和SUMIF(range,criteria,sum_range) 根据指定条件对若干单元格求和AVERAGE（） 求平均值NOW（）返回当前日期和时间所对应的系列数。TODAY（）返回当前的日期 HOUR（）返回时间值的小时数。即一个介于 0 (12:00 A.M.) 到 23 (11:00 P.M.) 之间的整数MINUTE(serial_number) 返回时间值中的分钟。即一个介于 0 到 59 之间的整数。YEAR(serial_number)、MONTH（）DAY（）如：YEAR(1998/7/5) 等于 1998WEEKDAY(serial_number,return_type) 返回某日期为星期几COUNT（） 统计数值参数的个数COUNTA（）统计非空单元格的个数COUNTBLANK（） 统计空格单元格的个数COUNTIF(range,criteria) 计算给定区域内满足特定条件的单元格的数目FACT（） 求阶乘 例：FACT（5）=120POWER（） 幂函数 210=POWER（2，10）LOG（） 求对数 LOG21024=LOG（1024，2）MAX（）求最大值MIN（）求最小值IF(logical_test,value_if_true,value_if_false)执行真假值判断，根据逻辑测试的真假值返回不同的结果AND（）判断参数的逻辑值，参数只要一个为假，则输出为假；参数全为真，则输出为真。OR（） 只要一个为真，则输出为真，全假则输出为假。RANK(number,ref,order)还回一个数字在数字排列中的排位。Number 为需要找到排位的数字。Ref 为包含一组数字的数组或引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。 如果 order 为 0 或省略，Microsoft Excel 将 ref 当作按降序排列的数据清单进行排位。如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据清单进行排位INDEX(array,row_num,column_num) 返回数组中指定单元格或单元格数组的数值。INDEX(reference,row_num,column_num,area_num) 返回引用中指定单元格或单元格区域的引用。MATCH(lookup_value,lookup_array,match_type)返回在指定方式下与指定数值匹配的数组中元素的相应位置。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。INT（）取整ROUND（）四舍五入RAND（）返回大于等于 0 小于 1 的均匀分布随机数LEFT(text,num_chars) 、RIGHT（text,num_chars）基于所指定的字符数返回文本串中的第一个或前几个字符MID(text,start_num,num_chars) 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定CHAR（NUMBER）返回ASCII码值所代表的字符函数CODE（TEXT）返回字符的ASCII码值函数UPPER（TEXT）将字符转为大写函数LOWER（TEXT）将字符转为小写函数SEARCH(find_text,within_text,start_num) SEARCH 返回从 start_num 开始首次找到特定字符或文本串的位置上特定字符的编号LEN（）返回文本串中的字符数MOD(number,divisor) 返回两数相除的余数。结果的正负号与除数相同VALUE(text) 将代表数字的文字串转换成数字CEILING()FLOOR()Isna() 是否空值ISNUMBER（）ISBLANK（）ISLOGICAL（）ISTEXT（）等判断数数据库函数DSUM(database,field,criteria) 返回数据清单或数据库的指定列中，满足给定条件单元格中的数字之和。DAVERAGE(database,field,criteria) 返回数据库或数据清单中满足给定条件的数据列中数值的平均值DCOUNT（）等财务函数1， RATE（）求利率例：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)含义是:如果A1,A2,A3三个数的均值50,则求A1,A2,A3三个数的最大值,否则, 求A1,A2,A3三个数的和. 如下图中函数:IF(IF(C4= 男,65-D4,60-D4)0,退职,IF(C4=男,65-D4,60-D4)办公技巧：Excel定时提醒不误事=If(c31200,0,if(c31700,(c3-1200)*5%,if(c33000,(c3-1200)*10%-25,if(c38000,(c3-1200)*15%-125) 结果如下图：备注：所有的函数都可利用函数指南来操作，但一定要注意数值类型与函数类型要配比.三、对数据的排序、筛选、分类汇总一、记录单的创建1， 输入记录单的字段名2， 选定并执行数据/记录单 确定3， 在各字段中键入记录单的值4， 按“回车”即加入一条记录记录的插入：执行插入/单元格 整行 确定记录的查找：1，执行数据/记录单 键入欲查找记录的值 按“回车”二、排序执行数据菜单中的”排序，其中可以按照主关键字，次关键字，第三关键字，依次排列(也是说,首先按照主关键字排序,当主关键字中有相同字段时,再根据次关键字排序,依次类排).（图4-4）图4-4三、筛选执行数据菜单中的“筛选”中的“自动筛选或高级筛选”自动筛选：是指在分类汇总的基础上，选择某一部份（类别）的数据步骤：（1）、选定要筛选的单元格。（2）、数据（菜单） 筛选 自动筛选自定义筛选：（1）、在建立筛选的基础上，利用向下的箭头，选择自定义选项（2）、在自定义自动筛选方式对话框中进行条件设定（可同时设定两个条件）高级筛选：一般用在于条件比较复杂的寻找。1） 设定条件区域在数据清单的前方插入几个空行输入要设定条件的字段名称及条件若要对不同的列指定多重条件，请在条件区域的同一行输入所有的条件（条件标记必须和我们想评价的列标记相同）若要相同的列指定不同的条件，请把条件输入不同的行上2）数据（菜单） 筛选 高级筛选2） 根据对话框进行设置四、分类汇总1、 对欲分类的字段进行排序2、 执行数据/分类汇总3、 选择分类字段及汇总方式和汇总项 确定多级分类汇总：1、 进行两列以上排序2、 给主分类段分类汇总3、 给次分类段分类汇总并使“替换当前分类汇总”无效 确定五、模拟运算表 单模拟运算表：单变量模拟运算表的结构特点是，其输入数值被排列在一列中（列引用）或一行中（行引用）。单变量模拟运算表中使用的公式必须引用输入单元格。 1. 在一列或一行中，键入要替换工作表上的输入单元格的数值序列。2. 如果输入数值被排成一列，请在第一个数值的上一行且处于数值列右侧的单元格中，键入所需的公式。在同一行中，在第一个公式的右边，分别键入其它公式。 如果输入数值被排成一行，请在第一个数值左边一列且处于数值行下方的单元格内，键入所需的公式。在同一列中，在第一个公式的下方，分别键入其它公式。 3. 选定包含公式和需要被替换的数值的单元格区域。4. 在“数据”菜单中，单击“模拟运算表”命令。 5. 如果模拟运算表是列方向的，请在“输入引用列的单元格”编辑框中，为输入单元格键入引用。 如果模拟运算表是行方向的，请在“输入引用行的单元格”编辑框中，为输入单元格键入引用。 双模拟运算表：双变量模拟运算表中的两组输入数值使用同一个公式。这个公式必须引用两个不同的输入单元格。 1. 在工作表的某个单元格内，输入所需的引用两个输入单元格的公式。2. 在公式下面同一列中键入一组输入数值，在公式右边同一行中键入第二组输入数值。3. 选定包含公式以及数值行和列的单元格区域。4. 在“数据”菜单中，单击“模拟运算表”命令。 5. 在“输入引用行的单元格”编辑框中，输入要由行数值替换的输入单元格的引用。6. 在“输入引用列的单元格”编辑框中，输入要由列数值替换的输入单元格的引用。六、单变量求解工具菜单-单变量求解 七、透视表的创建数据透视表是一种对大量数据快速汇总和建立交叉表的交互式格式表格，它是一种组织数据的软设备。用户可以在透视表中指定想显示的字段和数据项，以确定如何组织数据。1、 单击工作表中的任意单元格2、 单击数据/数据透视表达式 下一步 下一步3、 拖动右边的字段按钮到图中来做成透视表的行列数据 例如把日期作为页字段，产品作为行字段，地区作为列字段，销售额作为数据项 如下图：最后结果是：一、 修改透视表1、 行列互换（1），在列字段名单元格A4按下左键拖曳到行字段中即可。（行同上）2、 添加和删除字段单击工具栏上的“数据透视表”/向导直接将欲添加的字段拖曳到透视表中将欲删除的字段拖曳出来。二、 刷新透视表（有以上两种方法）1， 执行数据/更新数据2， 单击“数据透视表”工具栏上的“！”三、 对数据透视表排序 （1），右单击字段名（如：地区）字段高级选择排序项确定确定 如下图：四、 改变透视表的汇总方式 单击工具栏上的“数据透视表”/字段 (如下图)六、显示和隐藏明细数据 数据透视表能够对数据进行分类汇总，也可显示或隐藏明细数据。 （1）、右单击透视表中的列字段名（如“地区”）（2）、执行“组及分级显示”显示明细数据（3）、在对话框中选择明细数据所在的字段，（如“产品”）确定 如下图）五、 创建计算字段 （1），右单击透视表公式计算字段弹出对话框 （2），在“名称”栏中输入计算字段的名称，在“公式”栏中输入计算字段的公式，如下图：（3）、单击“添加”确定最后的结果是：八、图表的创建：图表是将数据用图形的形式表示出来一、 使用向导创建图表将光标定在表格中单击工具栏上的“图表向导”按钮选择图表的类型（如下图）下一步按向导完成余下的操作图表类型的更改：右单击图表图表类型重新选择图表的类型其它项的更改：最快的方法是：需改什么就双什么 如改文字则双击文字使用趋势线（1）、激活图表执行“图表”菜单中“添加趋势线”选择类型选择数据系列确定使用误差线：1、 单击图表中要添加误差线的数据系列 如“渔类”执行格式/数据系列选择“误差线Y”2、 选择“正负偏差”在“误差量”栏中选择百分比确定地图数据将在电脑上演示四、数据高级分析与决策一、规划求解 简介：规划求解是数学中的优化问题，它通过改变多个输入单元格求出最优解，同时保证工作表中的其他公式保持在设置的极限之内。 下一实例说明规划求解的应用的操作步骤。某个厂家，在一段时间内生产两种产品，分别是产品A和产品B。受原材料的限制，产品A的产量不得超过4000，产品B的产量不超过4000；受机器和人员以及时间的限制，两种产量之和不得超过7000。产品A的单件利润为200元，每多生产100件，由于成本的降低，单件利润增加2元。产品B的单件利润为190元，每多生产100件单件利润增加3元。现在进行规划求解，求出产品A和产品B产量的最佳方案，使总利润最大。（1）、新建一张表，其中B4单元格的公式是“=B2+B3”，B6中的公式是=（200+B2/100*2）*B2+（190+B3/100*3）*B3（2）、执行工具/规划求解如图（3）、选择“最大值”，设置目标单元格为$B$6,可变单元格为$B$2:$B$3(4)、单击添加弹出“添加约束”对话框 输入约束值添加（5）、重复第（4）步，把所有约束条件都添加到“规划求解参数”对话框的“约束”列表框中，如下图（6）、单击“求解”确定最后的结果是：产品A为3000 产品B为4000 总利润率为：2020000练习题：用一块长20米，宽1米的铅皮靠墙围成一个矩形，若要矩形的体积最大，则此矩形的长与宽各应是多少米最合适。二、方案管理器方案是对问题不同的设想，以便观察对问题的影响，结论出最佳的投资方案。实例：创建步骤如下:(1),执行工具/方案添加在方案名框中键入”最好情况估计”,(因为这里是用年增长率来估计销售情况) 在可变单元格选为$C$2：$C$4,如下一图:确定(2),依次键入变量的值 .如下二图添加重复上步编辑”最坏情况估计”方案确定关闭显示方案:执行工具/方案选择要显示的方案名单击”显示”创建方案总结报告:执行工具/方案单击”方案总结”选择类型在”结果单元格”文本框中输入方案有效结果的单元格引用(如:$C$5)确定最后结果是三、有效数据的定义(例:学生成绩分是介于0至100之间)方法:(1),选择要定义有效数据的式区域(2),执行数据/有效数据(3),在设置选卡的许可下拉列表中选择小数,在数据列表中选择介于,在最小值文本框中输入0,最大值为100 如下图确定标识单元格中的错误数值：(1),选中欲标识的数据区域(2),执行工具/审核/显示审核工具栏(3)单击此工具栏上的圈释无效数据如下图:四、合并计算执行数据菜单-合并计算结果如下：五、宏的创建(1),打开工具/宏/录制新宏(2),在宏名中键入宏的名字(可以定义快捷键 如:CTRL+E)确定(3),在工作表中,执行一系列的操作(4),操作完成后单击停止例：工资表打印宏：Sub 生成工资条()Cells.Select选择整个表去掉表格线Range(F1).ActivateSelection.Borders(xlDiagonalDown).LineStyle = xlNoneSelection.Borders(xlDiagonalUp).LineStyle = xlNoneSelection.Borders(xlEdgeLeft).LineStyle = xlNoneSelection.Borders(xlEdgeTop).LineStyle = xlNoneSelection.Borders(xlEdgeBottom).LineStyle = xlNoneSelection.Borders(xlEdgeRight).LineStyle = xlNoneSelection.Borders(xlInsideVertical).LineStyle = xlNoneSelection.Borders(xlInsideHorizontal).LineStyle = xlNoneRows(2:2).Select选择第2行Selection.Insert Shift:=xlDown在第2行前插入一行，保持第2行为选中状态num = (ActiveSheet.UsedRange.Rows.Count) - 2) * 3这个数字是工资表中总人数乘以3，例如工资表中有20人，就是num=60col = ActiveSheet.UsedRange.Columns.Count这个数字是工资表中的列数，例如工资表中有20列，就是col=20num1 = 4Do While num1 = num循环插入空行Range(Cells(num1, 1), Cells(num1, col).Select选中第num1行的第1列到第col列Selection.Insert Shift:=xlDownSelection.Insert Shift:=xlDownnum1 = num1 + 3LoopRange(Cells(1, 1), Cells(1, col).SelectApplication.CutCopyMode = False剪切复制模式无效Selection.Copy复制选择区域Range(A2).Select选择A2单元格ActiveSheet.Paste从A2单元格起粘贴内容num2 = 5Do While num2 = num循环插入标题行Range(Cells(1, 1), Cells(1, col).SelectApplication.CutCopyMode = FalseSelection.CopyCells(num2, 1).SelectActiveSheet.Pas