MicrosoftExcel基础应用知识及练习

1、制作：谢展Microsoft Excel 应用应用授课人：谢展制作：谢展第1单元 Excel 入门 浏览 Excel（基本功能） 表格的制作和美化 计算功能 数据分析与管理功能 数据图表功能 表格中图形与图像的应用制作：谢展 选择多重单元格 Windows选择风格，支持”Ctrl”和“Shift”键。 名称框输入地址，如“A1:C5,E3” 单元格区域命名快速命名 插入/名称 创建和编辑工作表 创建空白工作簿 根据现有的工作簿创建工作簿 根据模板创建工作簿制作：谢展 定位光标 利用鼠标定位 利用键盘定位 方向键（利用Atl快速定位） 回车键（选项/编辑 确定方向） 使用 编辑/定位 命令 使用

2、帮助信息制作：谢展 数据类型与输入 数值型数据 常规格式：1000.01-123 科学计数格式：1.23E41.23e-3 日期和时间格式：10/01/20063:22:43 AM 数值型数据输入0 3/21.51 1/2-0 7/3-2.33333333333333-2 1/30 -7/32000-7-32000-7-3制作：谢展 日期和时间的输入 更改默认显示，2k问题 当前Ctrl+;Shift+Ctrl+; 1900时间系统 文本性数据的输入 强制文本符号 强制换行Alt+Enter制作：谢展 序列与自定义序列 填充方式： 填充柄菜单/编辑/填充 自定义序列 数据的有效性 如例 利用命

3、名单元格引用其它工作表数据设置单元格数据有效性 练习：自定义序列填充制作：谢展第2单元 公式和格式 创建公式和函数 公式的组成部分：等号 运算符函数单元格引用常量 公式中的运算符 算数运算符：+ - * / % 比较运算符：= = = 文本运算符：& 引用运算符冒号逗号空格制作：谢展 函数 预定义的公式 公式和函数粘贴与移动 使用填充柄复制粘贴 拖动 自动求和 填充指令制作：谢展 单元格引用 将单元格行、列坐标位置称为单元格引用 引用样式分类 A1引用样式如：C1 B10:C18 8:8 M:N R1C1引用样式选项/常规如：R1C1 R 三维引用样式如：SUM(Sheet1:Sheet10!

4、B4)制作：谢展 单元格引用类型 绝对引用 被引用的单元格与引用的单元格的位置关系不变，不随公式或函数的粘贴和移动改变。 表达方式：$A$1R1C1 相对引用 引用关系会随着公式或函数所在单元格位置变化而保持相对位置关系。 表达方式：A1R1C-1 混合引用 表达方式：A$1R1C制作：谢展 格式化数字 自定义格式练习符号符号含义含义#显示所在位置的非零数字0同上，但可以显示无效零,放在格式最后，数据以千为单位显示,放在格式最后，数据以百万为单位显示字符串同“”0*字符数字后用*号，之后为“字符”填充制作：谢展 自动套用格式 格式/自动套用格式练习 使用条件格式 格式/条件格式练习 函数的运用

5、 日期函数 Day()，Month()，Year()返回日期序号 Now()，Today() 练习：若股东大会于2007/5/10召开，使用taday()函数计算今天到股东大会还有多少天。制作：谢展 文本函数 Len (s)与Lenb (s)函数返回s字符串长度 如：Len (“计算机CPU”)=6Lenb (“计算机CPU”)=9 Mid (s, m, n)与Midb (s, m, n)函数返回s串从m位置开始共n个字符或文字 如：Mid (“CPU”, 2, 1)=“P” Left (s , n)、right (s , n)以及Leftb (s , n)、rightb (s , n)函数

6、如：Left (“Microsoft”)=“M”Left (“Microsoft”, 5)=“Micro” Find (sub, s, n) 、Findb查找函数 如：Find (“ab”, “xaBcaba”, 1)=5 练习制作：谢展 财务函数 求某项投资的未来值 FV (rate, nper, pmt, pv, type) Rate: 各期利率 Nper: 总投资期 Pmt: 各期应付金额 Pv: 现值（本金），如省略则假设其值为零 Type:为0或1，指定付款时间是期初还是期末，省略表示为零 例：如果某人十年后需要一笔较大支出，他计划每月存款1000元，按照年息1.85%，求十年以后该

7、账户存款额。制作：谢展 贷款分期偿还额 PMT (rate, nper, pv, fv, type) 练习：如果某人贷款600000元，贷款利率为5.25%，他计划15年后还清，计算每月应付款。 求某项投资的现值 PV (rate, nper, pmt, fv, type)制作：谢展 折旧函数 直线折旧法 SLN (cost, salvage, life) 年折旧额=（原始成本-预计净残值）/使用年限 如：某人购买一辆价值30000元的卡车，折旧年限5年，残值为7500元，则年折旧额=sln (30000, 7500, 5)=4500 年数总和法 SYD (cost, salvage, lif

8、e, per) 年折旧额=（原始成本-预计净残值）*尚可使用年限/使用年限之和 如上例：用syd函数计算1-5期的折旧额syd(30000, 7500, 5, 1)= 7500syd(30000, 7500, 5, 2)= 6000 syd(30000, 7500, 5, 3)= 4500syd(30000, 7500, 5, 4)= 3000 syd(30000, 7500, 5, 5)= 1500制作：谢展 其它常用函数 Row ()Column () Countif (range, crteria) 如计算人数 控制数据唯一 Countblank (range) Sumif (range

9、, criteria, sum_range)制作：谢展第3单元 预览及打印 拼写检查 设置打印选项 设置打印区域 设置打印标题 创建页眉页脚 为同一工作簿内工作表设置同样的页眉页脚 应用Web特征 示例：运用单元格命名快速打印区域制作：谢展第4单元 图表及地图 使用图表向导 示例 编辑图表 增加数据：更改数据源 或 拷贝粘贴 更改标题字体、图例格式等 创建饼型图 讲练习制作：谢展 创建地图 绘图及插入对象 示例：利用 插入图片 创建企业权利组织结构图 练习组合制作：谢展第5单元 数据库入门 数据清单 数据清单是一系列工作表数据行，主要用于管理数据的结构。因此，在Excel中执行数据库操作命令时

10、，把数据清单看成一个数据库。 建立数据清单的准则 每张清单仅使用一个数据清单 相似项数据位于同一列 避免空行和空列制作：谢展 建立和管理数据 建立 通过 数据/记录单 增加记录和查找数据 创建组 排序数据 单列排序 多列排序 筛选数据 自动筛选 条件筛选 高级筛选 使用列表制作：谢展 汇总数据 分类汇总嵌套分类汇总（排序后汇总） 合并数据 按照位置合并计算 按照分类合并计算 练习 在示例数据基础上，运用高级筛选筛选出“年薪”大于40万的女业务员（复制到其它位置）；分类汇总“各部门”的“年薪”总额。 练习 利用排序功能，实现隔行插入单元格制作：谢展第6单元 数据库管理 数据导入和导出 讲练习 导

11、出HTML文件 添加交互的使用制作：谢展 创建透视图表 发布透视表到Web制作：谢展第7单元 工作表管理 链接工作表 链接工作簿 设定密码 保护储存格和对象制作：谢展 合并工作表 群组及大纲 创建报表 制作：谢展第8单元 Visual Basic 制作：谢展第9单元 IF逻辑块 趋势分析，变量求解，频率分布，方案管理器，审核工作表，数据确认， 使用IF函数， 嵌套IF函数，合并逻辑函数 制作：谢展第10单元 高级数据管理 使用 HLOOKUP （ VLOOKUP ）例 语法：vlookup (lookup_value, table_array, col_index_num, range_loo

12、kup) Lookup_value:为需要在table_arry第一列中查找的数值。可以为数值、引用或文本字符串。 Table_array:为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。 Col_index_num:为 table_array 中待返回的匹配值的列序号。 Range_lookup:为一逻辑值。False(0)为精确比较，true(1)或省略取近似值。制作：谢展 Excel 可以创建动态模型。当用户改变公式所涉及的单元格的值时，动态模型可以运用此公式重新计算。 案例： 贷款情况，实际还贷情况根据许多因素确定。 在不同利率情况下，月还款是多少？ 首付款不同的情况下，

13、月还款多少？ 制作：谢展 可以使用以下方法 手工进行假设分析手工进行假设分析：插入一个新的数值，并且观察公式中的结果 模拟运算数据表模拟运算数据表：创建模拟运算数据表。用户可以系统地修改数值或者公式来了解不同条件下的结果 创建单变量数据表例 创建双变量数据表例制作：谢展 优点： 可以处理非常复杂的动态计算数据 假设条件可以随时更换 引用计算方法、计算公式可以统一更换 不足 一次只能够改变1-2个输入单元格 创建过程不够直观 双输入数据表只能显示1个数据结果 无法自动找到最优解制作：谢展 使用规划求解工具 规划求解是Excel提供的另一个功能强大的工具。 用户使用规划求解，可以使用Excel自动

14、的找到目标单元格的最佳值。 规划求解是一个插件，选择“工具”加载宏”命令，选定名为“规划求解”的插件即可。 规划求解会在一下情况中用到： 目标单元格依赖于其它单元格的变化，用户需要使目标单元格最大或最小化，或者使之与某些某些数值相等。 目标单元格的解必须服从一定的约束和限制制作：谢展 进行规划求解的步骤：1.分析条件。2.选择“工具”规划求解”命令，调出“规划求解参数”对话框。3.指定“目标单元格”以及其变化目标。4.指定“可变单元格”。5.添加“约束条件”。6.点击“求解”，选择“保存规划求解结果”，即可得到最优答案。制作：谢展 在“规划求解选项”对话框中可以控制规划求解过程的诸多方面。 最长运算时间：制定用户需要规划求解解决一个问题所花费时间的最大值。若计算复杂可以调整此处增加允许计算时间。 迭代次数：规划求解是用迭代的方法得出的最优答案，迭代次数高可以得到更精确地答案。 精度：指定单元格定位和约束公式必须满足约束条件的最接近程度，精度