Excel 的强大功能, 神奇!
1.快速定位数据 ctrl+g 定位条件; 或者菜单中: 查找与选择->定位条件
(1) 删除所有的错误值(ref这种)
(2) 实现空格自动填充0: ctrl+g 定位条件: 注意输入0 应该按 ctrl+enter, 否则只有当前空格值为0
(3) 自动填充, 不用一个一个拉, =上一个单元格, ctrl+enter.
如下面的填充部门的例子, 定位所有空格, 然后输入公式"=上一个单元格,", ctrl+enter.
(4) 快速选出两列中值不一样的行, 填充红色, 类似也可以两行中标记不同的(查找姓名是否打错等)
以下实例中: 快速选择中的行内容差异单元格
2. 强大的自动填充功能
按住右键下拉, 会出现选择菜单, 可以将日期按工作日, 月, 年填充
已输入2,4, 按住右键下拉 -->可选择等差序列
已输入3,9, 右键下拉 --> 可选择等比序列
自动生成的序列: 第一季度, 第二季度,, excel选项->高级->编辑自定义列表-->输入序列
如果懒得输入, 可在现有的表格中导入进来
序列号: 文本超过了11位没办法自动填充, 怎么办?
输入x001, x002进行填充, 然后设置单元格格式为文本, 进行替换, 注意替换的时候, 需要将x 替换为'330200001111, 少引号会出错!!
补充: 在2016版本中, 可以自动分离中文与英文,
在中文一列输入 你好, 左键下拉可选择快速填充 下面得到开心, 好的, 或者按快捷键ctrl+e.
3. 排序注意点
注意要把 数据包含标题 勾上, 否则出现的是 列A, 列B...
注意数值转化为文本, 文本中9 >34, 文本是按照第一个字母来排序的!
按照多个字段进行排序: 先按照部门排, 再按照工资排 (点击 添加条件, 增加多个字段)
点击选项...: 还有按照拼音, 笔画等进行排序!
也可以按照单元格的颜色进行排序: 先绿色, 再黄色....
可以根据之前自定义的序列进行排序: 经理, 部门负责人, 职员 先添加到自定义序列即可
4. 快速制作工资条(这个在我的VBA笔记中也有提到过) ,并使得相邻两条空一行
复制标题栏-> 选中余下行的区域, 插入复制的单元格, 得到一样多的标题栏-> 添加辅助列的数字-> 进行排序->删除多余
如何对生成的每条工资条进行添加边框??
区域中全部添加框线-->定位空格-->删除竖线
5. 选择性粘贴
(1) 保留数值, 保留源格式, 复制过去之后右键有菜单可以选, 或者直接原来的数据选中标题,Ctrl接着选中之后的数据, 再复制过去, 直接就是保留了数值+原格式!
(2) 复制整张表, 列宽改变了怎么办(复制后右击, 选择性粘贴, 勾选列宽)
看看选择性粘贴的菜单: 还有跳过单元格进行粘贴的那种!!
补充: 剪贴板中的全部粘贴功能
如果需要将很多在不同sheet中的表复制到汇总表中, 每次复制粘贴很烦, 可以多次复制, 但只粘贴一次吗?
按右下角这个箭头的 可以打开 剪贴板 --> 全部粘贴
6. 自动筛选
筛选的本质是隐藏不符合条件的数据
选择一个单元格 , ctrl +shift+L, 自动筛选, 标题栏每一列出现了下拉箭头
再次 ctrl +shift+L, 取消筛选
(1) 姓 王 的人: 点击下拉箭头-->文本筛选(开头是), 也有结尾是
(2) 可以按照单元格颜色筛选
高级筛选: 实现 或 的关系
现有两列数据: 全体员工, 到场员工, 想标记下到场与不到场的人.
选择第一列数据-->筛选(高级)-->这一列作为列表区域, 选择第三列为条件区域-->得到结果, 将其标红即到场的人
7. 表格打印
如何自动调整列宽? 让第二列的数字全部显示?-全选, 在BC列之间鼠标出现两竖之后 双击!
ctrl+p 打印, 弹出对话框, 也有打印预览
(1) 纸张方向(页面布局: 横向即可)
(2) 调整打印区域: 选中区域--> 设置打印区域--> ctrl+p看看效果
(3) 居中打印: 打印对话框中左下角-->页面设置的居中方式
(4) 每一页都要有标题行: 页面布局--> 打印标题--> 选择标题行!
(5) 缩放打印: 列太多了, 如何把它们挤在一页上? ctrl+p, 左侧缩放--> 将所有的列调整为一页!
也可以在页面设置中的缩放 进行.
(6) 页眉页脚: 在页面设置中进行.
8. 工作表安全
(1) 设置密码才能打开
要删除就 还是同样操作, 删除原密码就行了
(2) 不允许被编辑
选中所有单元格--> 设置单元格格式-->保护-->勾选锁定
接着, 审阅--> 保护工作表, 设置密码
要撤销密码还是同样的上一步(此时选项为 撤销保护), 把密码删掉就行了
9. countif 规避重复值(主键不允许重复)
(1)countif(范围, 条件)= 满足条件的个数
=countif(A2:A30,">100") 要用双引号!
条件也可以引用单元格, 表示等于这个单元格的值的个数
(2) 标记重复值
开始--> 条件格式--> 突出显示单元格规则-->重复值
(3) 删除重复值: 数据--> 删除重复项
如何实现在输入数据的时候若有出现重复值进行提醒?
选择B2开始的一列(选择B2再ctrl+shift+下箭头), 不要选择B整列! --> 数据-->数据有效性-->设置如上所示
这里写B2, 因为先前选择了B2, 之后会自动变为B3, B4,.... 这里是相对引用!
出错警告 中可自己写一警告提示语.
身份证号也是同样做, (提示需要设置为文本)
实际中认为330280000011111001与330280000011111002 居然被认为一样? 为啥?
excel对于文本有比较边界, 它认为前面15位数字都是一样的就默认为一样了, 怎么处理
在上述条件中 = countif(C:C, C2&"*")<=1, * 表示任意字符, 这样就会比较所有的信息
数据有效性的缺陷: 如果复制过来一样的, 系统不会报错! 人工逐个输入一样的会报错!!
10. 数据分列
应用(1): 分割身份证号: 330281 地址, 19940620生日, 123 顺序码, 奇数表示男性, 4 校验码
数据-->分列-->固定宽度-->添加竖线来切割, 往上一拉就可以删除竖线.
点击下一步, 我们只要提取出生日期, 因此不需要的可以 勾选 不导入此列.
让中间的日期导出, 还可调节日期格式
应用(2): 叫每个人填写出生日期, 每个人格式乱七八槽, 如何统一?
设置单元格格式, 调整为日期没啥用, 没法改变1994.06.20这种样式的
正解: 数据-->分列-->固定宽度(下一步)--下一步-->勾选 日期, 完成!
11. 利用条件格式做 到期提醒
应用(1): 超市食品管理员管理食品的到期时间提醒! 比如实现到期剩余30天的提醒, how to make it?
选中数据-->条件格式-->新建规则->选择公式--> 输入公式=B2-today()<30 , 设置标记格式.
应用(2): 考勤统计(抓出迟到者)
有时候上述会有问题, 对两列时间设置了单位格格式是时间可能也会有问题, 这时候可以利用数据分列
选中第二第三列, 下一步, 下一步 进行常规导出就能解决问题!
12.数据透视表
将一维表做成二维表: 想得到每个学校每个班级的学生人数, 总分, 最高分, 最低分, 平均分
在数据区域中随便选一个单元格-->插入数据透视表-->选择透视表放置位置(选个单元格)
-->拖动 学校, 班级 到行标签, 拖动分数5次到值 (有5种类型的值要计算!)
如何做成表的样子? 利用设计中的前面三个选项进行
不进行分类汇总, 不总计(对行对列禁用), 报表布局为(以表格形式呈现)
现在修改五个统计值, 双击 求和项: 分数, 弹出值设置, 设置为计数!, 最后修改列名,
Ctrl+1 设置单元格格式
13.忘记保存了如何恢复?
选项中设置自动保存时间
现在你关闭一个文件手贱点了不保存, 怎么得到excel自动保存的最新版本?
仍然打开选项-->保存,
有一个自动回复文件位置, 复制路径, 去这个文件夹, win+E , 粘贴路径即可
里面会有上一个文件, 在excel中进行还原就好了!!
补充最后一点:
以往我操作每行求和, 先写入公式, 在用鼠标拉倒底(双击十字也行),
现在还有另一个方法: 先 全部选中, ctrl+shift+下, 键入公式, ctrl+enter即可全部填充
其实两个方法都很好!