上海交通大学excel

信息素养教育计划 —读者篇 2011年10月13日 Microsoft ExcelMicrosoft Excel高级应用高级应用 陈嘉懿，jychen@lib.sjtu.edu.cn 2 信息素养教育计划 — 读者篇 2011年10月13日 主要内容 „高级应用技巧 „排序、筛选与汇总 „与函数 „数据透视表 „图表的制作 „宏的应用 3 信息素养教育计划 — 读者篇 EXCEL可以用来做什么，仅仅是 “电子表格”吗？ 2011年10月13日 4 信息素养教育计划 — 读者篇 Excel三国杀 2011年10月13日 5 信息素养教育计划 — 读者篇 Excel票价查询 2011年10月13日 6 信息素养教育计划 — 读者篇 Excel日历 2011年10月13日 7 信息素养教育计划 — 读者篇 Excel抽奖 2011年10月13日 8 信息素养教育计划 — 读者篇 EXCEL高级应用技巧 2011年10月13日 9 信息素养教育计划 — 读者篇 应用技巧 2011年10月13日 „快速录入数据 „自动填充功能 „等差、等比序列 „自定义序列 „行列快速转换 „选择性粘贴功能 „数据分列与合并 „添加不同视图 „冻结窗格 „选中冻结行列的下方或右方单元格 10 信息素养教育计划 — 读者篇 应用技巧 2011年10月13日 „数据有效性 „设定录入条件 „防止重复数据输入 „Countif函数 „格式：=COUNTIF(range,criteria)（表达式） „数据下拉列表制作 „条件格式 „标题行重复打印 „一个单元格内输入多行内容 „使用快捷键Alt+enter „批量修改数据 11 信息素养教育计划 — 读者篇 应用技巧 „自定义数据格式 „ “0”：数字占位符，如单元格内容大于占位符，则显示实际数字，如 果小于点位符的数量，则用0补足 „ “#”：数字占位符，只显有意义的零，小数点后数字如大于“#”的数 量，按“#”的位数四舍五入 „ “？”：数字占位符，在小数点两边为无意义的零添加空格，以便当 按固定宽度时，小数点可对齐 „ “，”：千位分隔符，如“，”后空，则把原来的数字缩小1000倍 „ “@”：文本占位符，如果只使用单个@，作用是引用原始文本 „ “*”：重复下一次字符，直到充满列宽 „ “_”（下划线）：留下一个和下一个字符同等宽度的空格 „ [颜色]：用指定的颜色显示字符，可有八种颜色可选：红色、黑色 、黄色，绿色、白色、兰色、青色和洋红 „ “！”：显示“”” „ [条件值]：设置格式的条件 2011年10月13日 12 信息素养教育计划 — 读者篇 排序、筛选与汇总 2011年10月13日 13 信息素养教育计划 — 读者篇 数据排序 „降序与升序 „单击工具栏中的“降序”按钮 „单击工具栏中的“升序”按钮 „条件排序 „排序选项 2011年10月13日 14 信息素养教育计划 — 读者篇 数据筛选 „自动筛选 „从“数据”下拉菜单中选择“筛选”命令，然后从“筛选”子 菜单中选择“自动筛选”命令 „工作表中第一行的各列将分别显示一个下拉按钮，自动 筛选就将通过它们来进行 „高级筛选 „And关系的条件放在同一行，or关系的条件放在同一列 „使用通配符与公式 2011年10月13日 15 信息素养教育计划 — 读者篇 数据筛选实例 „自定义自动筛选条件 „按工作表中条件进行高级筛选 „利用通配符与公式进行高级筛选 „批量删除空行 2011年10月13日 16 信息素养教育计划 — 读者篇 分类汇总 „统计与分类 „首先将需要分类汇总的列进行排序 „在菜单栏上选择“数据→分类汇总”选项 „选择“分类字段”、“汇总方式”、“汇总项” 2011年10月13日 17 信息素养教育计划 — 读者篇 公式与函数 2011年10月13日 18 信息素养教育计划 — 读者篇 单元格引用 „引用即在公式中用到了其他单元格在表格中的位 置 „相对引用 „是指在一个公式中直接用单元格的列标与行号来取用某 个单元格中的内容 „绝对引用 „绝对引用总是在指定位置引用单元格 „引用形式是在引用单元格的列号与行号前面加“$”符号 „外部引用 „对不同工作表中相同引用位置的单元格或区域的引用称 为外部引用。引用形式为： Sheet1:Sheetn!单元格（区域） 2011年10月13日 19 信息素养教育计划 — 读者篇 引用案例 2011年10月13日 20 信息素养教育计划 — 读者篇 引用案例 2011年10月13日 21 信息素养教育计划 — 读者篇 公式 „公式是在工作表中对数据进行计算分析的方程式 „格式：以“=”开头的一个运算式或函数 „参数与运算符 „参数：单元格引用、常量、函数 „运算符 „算术运算符：+ - * / % „比较运算符：< > = >= <= <> （判断结果为逻辑值） „其它运算符：& : , 空格 “=SUM((A:A,C:D) (1:2,5:5))”； 不用空格运算符： “=SUM(A1:A2,C1:D2, A5,C5:D5)” 2011年10月13日 22 信息素养教育计划 — 读者篇 函数 „函数的概念 „函数是Excel已经定义好的一些特殊公式，可以对一个或 多个数据进行计算，将计算结果存放于某个单元格中 „函数类型 „常用函数（求和、求平均值、判断条件等） „财务函数（利息、利率计算等） „日期与时间函数（返回日期、时间等） „数学与三角函数（正余弦、指数、平方和等） „统计函数（正态分布、方差等） „查找与引用函数（返回行列、单元格值、检索值等） „数据库函数 „文本函数（返回指定字符、数字转文本等） „条件函数（与、或、非等） „信息函数（检测值的有效性等） 2011年10月13日 23 信息素养教育计划 — 读者篇 常用函数的格式 „IF „用法 „ IF(条件, 表达式1, 表达式2) ƒ 其中表达式可以是数字、函数、单元格或单元格区域。 „功能 „条件成立时，函数结果为表达式１的值；条件不成立时，函数的 结果为表达式２的值 „可嵌套使用 „即在IF函数中还可以使用IF函数，最多可以嵌套7层 „比如：IF(A2>89,“A”,IF(A2>79,“B”)) „案例：根据身份证号提取出生日期 „ =IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2) ),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)), "错误身份证号")) 2011年10月13日 24 信息素养教育计划 — 读者篇 案例 „某班期末考试成绩表如图所示，统计每位同学的 总分，考试人数，最高和最低总分，以及不及格 人数 2011年10月13日 25 信息素养教育计划 — 读者篇 案例2 „某校奖金如图所示，根据职称确定。教授：2000 ，副教授：1500，讲师：1000，助教：500 2011年10月13日 26 信息素养教育计划 — 读者篇 案例2 „某校奖金如图所示，根据职称确定。教授：2000 ，副教授：1500，讲师：1000，助教：500 2011年10月13日 27 信息素养教育计划 — 读者篇 案例3 „VLOOKUP（x, table, n, r） „问题：某公司更换了新系统，原有的部分电话号码不能使 用，同时又新增了许多电话号码。系统变化后，大部分用 户仍然使用原来的用户档案，而那些不能使用的电话号码 和新增加的电话号码则需要重新建立用户档案 „ =VLOOKUP(D2,$A$2:$B$18,2,0) 2011年10月13日 E列是从旧 帐号的B列 找到的数 据，#N/A 表示D列的 帐号在A列 不存在 28 信息素养教育计划 — 读者篇 数据透视表 2011年10月13日 29 信息素养教育计划 — 读者篇 数据透视表 „什么是数据透视表 „是一种交互式的表，可以进行某些计算，如求和与计数 等。 „方便查看数据集，有可能看到之前没有注意到的数据细 节。 „使用数据透视表能够建立数据集的交互视图。可以方便地 将数据分组，汇总大量的数据形成有用的信息。 „什么情况下使用 „需要找出数据内部的关系并分组 „需要找出数据中某一字段的一系列特定值 „需要利用各种时间周期找出数据趋势 „需要创建常常包含新增加部分数据的分类汇总 „需要将数据组织成易于制成图表的格式 2011年10月13日 30 信息素养教育计划 — 读者篇 数据透视表的结构 „数据项 „行字段 „列字段 „页字段 2011年10月13日 31 信息素养教育计划 — 读者篇 图表的制作 2011年10月13日 32 信息素养教育计划 — 读者篇 图表类型 „条形图 „柱状图 „饼图 „面积图 „雷达图 „散点图 „气泡图 „两轴线柱图（实例操作） 2011年10月13日 33 信息素养教育计划 — 读者篇 动态改变图表类型 2011年10月13日 X值 1 3 4 8 14 20 Y值 5 8 1 8 2 4 Y值 0 1 2 3 4 5 6 7 8 9 1 3 4 8 14 20 Y值 折线图 34 信息素养教育计划 — 读者篇 宏的应用 2011年10月13日 35 信息素养教育计划 — 读者篇 宏 „什么是宏 „宏是一个指令集，用来告诉EXCEL来完成用户指定的动 作 „以VBA编程语言作为基础 „可以使用宏来完成枯燥的、频繁的重复性工作 „安全性选项设为“中”或“低” „录制宏 „执行“工具→宏→录制新宏”命令 „在“宏名”下面输入一个名称 „按下“确定”按钮开始录制 „进入操作过程，完成后，按 “停止录制”按钮即完成 „编辑宏 2011年10月13日 36 信息素养教育计划 — 读者篇 编辑宏 „代码结构 Sub 宏名称() 相关代码 End Sub „语法与常用语句 „与vb一样，变量不需要声明，但数组除外 „遍历： For a = 1 To Cells(65536, 1).End(xlUp).Row For b = 1 To Cells(1, 255).End(xlToLeft).Column Next Next „激活：Sheet1.Activate,Workbooks(“x.xls").Activate „复制行：Sheet1.Rows(a).Copy Sheets2.Rows(b) „打开另一个表格：Workbooks.Open ThisWorkbook.Path & "\xxx.xls“ „获取单元格的值：Cells(a, b).Value 2011年10月13日 37 信息素养教育计划 — 读者篇 调用宏 „在工具菜单里选择“宏”，点击执行 „在页面上添加按钮 „点击视图、工具栏、窗体，打开“窗体”工具栏 „点击工具栏上的“命令按钮”按钮，然后在工作表中拖拉 出一个按钮来 „系统自动弹出“指定宏”对话框，选中需要调用的宏，确 定返回 „将命令按钮上的字符修改为一个合适的内容，调整好命 令按钮的大小，将其定位在工作表合适位置上 „按一下该按钮，即可执行相应的宏 2011年10月13日 38 信息素养教育计划 — 读者篇 案例 2011年10月13日 „请欣赏一个用宏制作的酷毙了的动画 39 信息素养教育计划 — 读者篇 思考题 2011年10月13日 40 信息素养教育计划 — 读者篇 请各位读者别忘了填写反馈单 打开图书馆主页，点击图示位置，用jaccount账 号登陆后，选择“Microsoft Excel高级应用”课 程，在“反馈意见”栏下进行填写。 您的支持就是我们的动力，谢谢！ 2011年10月13日 41 信息素养教育计划 — 读者篇 2011年10月13日