Excel数据处理与分析

（3）自定义排序序列（案例）
44
九、排序、筛选（2） 2.筛选
（1）自动筛选（案例）
案例筛选条件
以“F”字母开头的或名字第4个字母为“M”的顾客。 不包含1980年出生的。 产品名称以“G”开头“S”结尾的或是“Bread” 金额总计大雨200
45
九、排序、筛选（2）
（2）高级筛选（案例）
分数 ，先输入一个空格，再输入分数。如“ 1/3” 序列（等差），使用填充柄，右键填充。
3.输入日期时间
“2017-5-5”或“2017/5/5”(年份是4位) “0-29”被识别为“2000-2029”，“30-99”被识 别为“1930-1999”（年份是2位） 快速输入当前日期：Ctrl+; 时间Ctrl+Shift+;
步骤：选择任意单元格插入|表格
表格与数据列表的主要区别
激活“表格”中的任意单元格，将会在功能区中显示“表格” 专用的“表格工具”的“设计”选项卡。 单元格包含“表格”样式 默认情况下，在标题行中为“表格”的所有列启用筛选功能。 如果“表格”中包含活动单元格，那么当向下滚动工作表并 使用标题行消失时，“表格”的标题名称会替代工作表的列 表。 通过拖动表边框右下角的调整手柄，可以修改“表格”区域 的大小。
在筛选结果中只显示部分字段数据 进行多对多查询（案例） 关系（案例） 空值条件：= 非空值 ：<> 精确筛选（案例） 使用公式自定义筛选条件（案例） 复杂条件下的“高级筛选”（案例）
46
十、条件格式 使用条件格式，设置到期提醒。百度文库案例） 以下内容以后有机会再讨论
1.图标 2.分类汇总 3.数据透视表 4.单变量求解 5.模拟运算分析 6.合并计算
16
四、数据处理与表格编辑（3） 6.定位“特殊”区域
17
四、数据处理与表格编辑（4） 7.删除重复的行（案例）
18
四、数据处理与表格编辑（5）
8.选择性粘贴
（1）转置粘贴（案例）
（2）链接的图片
使用“链接的图片”可以使数据区域同步显示在另一区域
19
五、设置表格格式（1） 1.将数据区域转换为“表格”（案例）
选择“结构”，不能在工作簿中添加、删除、移动、隐藏和 重命名工作表。 选择“窗口”，不能在工作簿中移动或重新调整窗口的大小。
给文档加密
步骤：文件|信息|保护工作簿|用密码进行加密。 说明：打开加密的文档时需要输入密码。
26
六、优化Excel工作环境(4) 5.隐藏工作表
简单隐藏：右击工作表标签。 深度隐藏：调用开发工具→属性窗口
步骤：选择要打印的图标→文件|打印
2.不打印图表
步骤：打开“设置图标区格式”对话框。
30
七、打印工作表（2） 3.设置打印区域
31
七、打印工作表（3） 4.设置打印标题
这样每页都显示标题
32
七、打印工作表（4） 5.打印不连续区域
步骤：
先同时选中多个区域 按Ctrl+P 设置“打印选定区域”
9
三、数据验证（3） 3.限制小数(同整数） 4.限制序列
10
三、数据验证（4） 5.限制日期和时间
限制日期
限制时间
11
三、数据验证（5） 6.限制字符的长度
12
三、数据验证（5） 7.限制重复输入（案例）
13
三、数据验证（6） 8.数据有效性的复制与更改
同时修改具有相同数据验证条件的区域
15
四、数据处理与表格编辑（2） 3.选中当前行或列的数据区域
按Ctrl+Shift+方向键，可以选中从当前单元格到 本行或列中最近一个与空格相邻的非空单元格所组 成的单元格区域。
4.选择当前数据区域
选中数据区域中的任何单元格，按Ctrl+A
5.选取多个工作表的相同区域
先同时选取多个表，再选区域。
Excel 数据处理与分析
郭永玲
大纲
一、导入外部数据 二、数据输入 三、数据验证 四、数据处理与编辑 五、设置格式 六、优化环境 七、打印工作表 八、函数应用 九、排序、筛选 十、条件格式
2
一、导入外部数据 对已存在的数据进行加工，则先进行导入 操作。
复制粘贴（案例）
Ctrl+C Ctrl+V
注意：不同区域打印在不同页
33
七、打印工作表（5） 6.缩放打印
7.页眉、页脚设置
34
八、函数应用(1)
1.数学与三角函数
sum、average、rank.avg 、int、round（案例） 说明：
rank.avg函数是Excel2010版本中的新增函数，属于RANK函数的 分支函数。原rank函数在2010版本中更新为rank.eq，可以与rank 函数同时使用并且作用相同。与rank.avg函数的不同之处在于， 对于数值相等的情况，返回该数值的平均排名。而作为对比， 原rank函数对于相等的数值返回其最高排名。 如，A列中有两个最大值数值同为37，原有的rank函数返回他们 的最高排名同时为1，而rank.avg函数则返回他们平均的排名，即 (1+2)/2=1.5。同理，如有三个数值37的rank排名均为1，而他们 的rank.avg排名则为(1+2+3)/3=2。 使用 SUMIF 函数可以对区域 中符合指定条件的值求和
Sumif函数（案例）：
35
八、函数应用(2) 2.查找与引用
（1）Vlookup精确查询（案例） （2）Vlookup模糊查询（案例） （3）find，用于在第二个文本串中定位第一个文本 串，并返回第一个文本串的起始位置的值，该值从第 二个文本串的第一个字符算起。
36
八、函数应用(3)
（4）mid函数，获取子字符串。
八、函数应用(9) 5.逻辑函数
（1）if，and ，or（案例） （2）iferror用于容错性处理。
6.文本函数（略） 7.财务函数（术语太多）
43
九、排序、筛选（1） 1.排序
（1）单关键字排序 （2）多关键字排序
注意：
是否包含标题 当前整个数据区域还是选定区域。 按笔画排序 按行方向排序 按单元格背景颜色排序（案例）
20
五、设置表格格式（2） 2.快速应用数字格式
应用格式后，只会改变单元格的显示形式，而不会改变单元 格存储的真正内容。
21
五、设置表格格式（3） 3.应用内置样式
步骤：选择开始|样式|单元格样式
4.修改样式
步骤：右击样式修改
5.创建新样式
22
五、设置表格格式（4） 6.活用文档主题
步骤：选择区域页面布局|主题
23
六、优化Excel工作环境(1) 1.使用文档属性
使用文档属性对文档归档很有帮助 步骤：
文件|信息|属性|显示文档面板
24
六、优化Excel工作环境(2) 2.自定义默认工作簿
建立一个模板，在excel启动时使用该模板。 步骤：
创建一个工作簿，另存为一个模板（扩展名为xltx)。 将该模板保存到“XLSTART”文件夹中即可。
14
四、数据处理与表格编辑（1） 1.快速定位首末单元格
快速定位到A1单元格：Ctrl+Home 快速定位到使用区域的最后一个单元格：Ctrl+End
2.水平|垂直定位
在同一行中快速定位到与空格相邻的非空单元格： Ctrl+→或Ctrl+← 在同一行中快速定位到与空格相邻的非空单元格： Ctrl+↑或Ctrl+↓
（5）mid与find联合应用，拆分字符串（案例）
37
八、函数应用(4)
（6）match函数
（7）find与match区别
主要在第二个参数,find是在一个单元格中查找。Match是在一 组数中查找。
38
八、函数应用(5)
（8）index函数，定位数值
（9）index和match联合应用（案例） （10）offset，获取偏移区域（案例）
5
二、数据输入（3） 4.输入文本
0开头的数值，超过11位长度的数值都处理成文本。
方法1：先输入一个“’”再输入数值。 方法2：先将单元格的格式设置为“文本”，再输入数值。
类等差文本
填充柄
复制文本
填充柄，右键
序列
填充柄，
自定义序列
6
二、数据输入（4） 5.自动更正 6.单元格换行
自动换行 手动换行（Alt+Enter）
7
三、数据验证（1） 作用：对输入数据的准确性和规范性进行 控制 1.现有数据的校验
步骤： （1）选择现有数据 （2）设置有效性条件 （3）诠释无效数据
8
三、数据验证（2） 2.限制整数
只允许输入1-10之间的整数
输入的数值必须大于B列的所有值
导入
导入文本数据
3
二、数据输入（1） 1.输入环境
在指定区域内输入
在输入前先选择输入区域
光标跳转方向
默认情况下，按Enter/Shift+Enter键，光标跳转到下一单元 格/上一单元格（垂直方向），按Tab/Shift+Tab键，光标水 平方向跳转。
4
二、数据输入（2） 2.输入数值
47
THANK YOU !!
48
3.保护工作表
希望某些单元格不被修改、复制、删除等操作，可以设置保 护工作表。 步骤
1.解锁不需要保护的区域（允许用户修改）：选择区域→开始|单元格| 格式|锁定单元格 2.设置保护工作表：开始|单元格|格式|保护工作表
25
六、优化Excel工作环境(3) 4.保护工作簿
步骤：审阅|保护工作簿
41
八、函数应用(8)
（4）datedif，是Excel隐藏函数，在帮助和插入公式里 面没有。 返回两个日期之间的年\月\日间隔数。常使 用datedif函数计算两日期之差
参数：DATEDIF(start_date,end_date,unit) • Start_date 为一个日期，它代表时间段内的第一个日期或起始 日期。(起始日期必须在1900年之后) • End_date 为一个日期，它代表时间段内的最后一个日期或结 束日期。 • Unit 为所需信息的返回类型。
"Y" 时间段中的整年数。 "M" 时间段中的整月数。 "D" 时间段中的天数。 "MD" 起始日期与结束日期的同月间隔天数。 忽略日期中的月份和年份。 "YD" 起始日期与结束日期的同年间隔天数。忽略日期中的年份。 "YM" 起始日期与结束日期的间隔月数。忽略日期中年份
42
39
八、函数应用(6)
3.统计函数
（1）countA,countIf，统计个数。 （2）mode,获取出现频率最高的值
（3）trimmean,剔除极值求平均值。 （4）large，small，max,min
40
八、函数应用(7) 4.时间日期函数
（1）today，date （2）weekday：返回某日期为星期几，默认情况下， 其值为 1（星期天）到 7（星期六）之间的整数。 （3）weeknum:返回特定日期的周数。
27
六、优化Excel工作环境(5) 6.大量数据时，标题行始终可见（案例）
方法一
转化为表格
方法二
冻结窗格
方法三
拆分窗口
方法四
新建窗口（多窗口协同工作） 步骤：视图|窗口|新建窗口→重排窗口
28
六、优化Excel工作环境(6) 7.手工建立分级显示（案例）
29
七、打印工作表（1） 1.只打印图表