阅读全文大概需要5.5分钟。
本文是专栏《Excel必知必会》的第一篇教程,如果想了解专栏内容规划,请参阅开篇。温馨提示:如果您已经特别熟悉Excel,大可不必再看这篇文章,或只挑选部分。文中对Excel的说明和操作基于Mac Excel2016和Windows Excel 365
简单回顾下,开篇讲到专栏的内容规划:
- 掌握高频函数,做到会查,会用
- 图表和数据可视化
- 掌握数据透视表
- 利用VBA实现自动化任务
在正式开始如上四个核心知识点之前,我觉得应该有一些托白,但是Excel很容易上手,为此我觉得内容上加一些Excel常见使用技巧,一些看似常规实则相当好用,熟练之后你甚至可能觉得比较强大,毕竟很多人的应用场景可能不需要可视化技巧或者VBA。
为此我准备用2-3篇内容说一说Excel那些隐秘的,但却可以大大提高效率的技巧,其中涉及到单元格格式化,工作表及视图,快捷键,名称以及数组......
内容虽不多,却是Excel进阶的必备,可谓大基本功!
正文开始,先看下图:
别误会,我并不是要从零说起Excel, 虽然大部分人可能每天都会打开Excel,但是为数不多的人可以做到将Excel功能分区熟记于心。事实上,你会发现大部分软件操作界面几乎一致,都包括:标题栏、菜单栏、功能区、快捷工具栏以及状态栏。
作为行列式表单工具,Excel又包含了行/列标题、用于公式书写的编辑栏、工作表标签以及名称区。关于Excel的操作界面你需要知道,Excel的标题栏、菜单栏、功能区以及快捷工具是可以定制的,尤其是学习完VBA之后,我们可以将自己开发的小程序添加到各分区,快速调用。
进入主题
一. 单元格操作技巧
1.单元格样式A1以及R1C1
工作表第一个单元格即首行首列相交处,可以用A1表示,其中A是列号,1则是行号;或者使用R1C1形式,Row1, Column1;可在Excel选项中设置,知道就行,实际用默认A1。
2. 单元格引用:绝对引用,相对引用,混合引用
绝对引用:$A$1, $A$1:$C$100;相对引用:A1,A1:C100;混合引用:$A1,A$1,$A1:C$100。美元$在字母前锁定列,在数字前锁定行,F4键切换引用样式。
3. 双击格式刷,格式刷锁定
多个不连续单元格需要使用格式刷,不用反复点击格式刷。
4. 快速移动到单元格区域边界
鼠标移动到活动单元格上/下/左/右边框,出现箭形十字(windows)/手掌(mac)双击快速定位到单元格区域的边界,快捷键是CTRL + 方向键。
5. 多列同时调整列宽/多行同时调整行高
连续多行/列选中后,按住shift键,通过鼠标同时调整选中行/列的行高/列宽。
6. 列快速移动
选中整列,鼠标移动到列左/右侧,出现十字箭头符号,按住Shift键,按下鼠标左键,将选择列拖动到目标位置
7. Tab 键自动补充
单元格输入函数/名称时Excel会根据当前字符匹配函数/名称,此时方向键选择目标,按下Tab键,目标自动补充到单元格内。
8. 自动换行
单元格输入内容太多,占用其他单元格,选中自动换行后,单元格宽度根据内容自动调整,另一个应用场景,可以将超链接应用到整个单元格。
二. 工作表/工作薄操作技巧
- 工作表标签右键
<1. 工作表标签颜色区分;<2. 工作表隐藏/取消隐藏;<3. 全选所有工作表 <4. 移动/复制
2. 多表快速定位
<1. CTRL + 工作表标签导航⬅️/➡️:快速切换到工作表的首页/末页;<2. 鼠标置于工作表标签左侧的导航按钮⬅️/➡️:右键查看所有工作表
3. 窗口操作【工作表】
窗口冻结:冻结首行/首列;冻结窗口【冻结多行,多列】; 拆分:选定行/列 选择拆分,此时工作表会以堆叠形式一分为二。
4. 窗口操作【工作薄】
新建窗口:相当于活动工作薄的副本,工作薄的任何编辑,副本同时更新;隐藏/取消工作薄,隐藏的工作薄无法在Excel视图中显示出来。
三. 迷你图
使用方法:选定某个单元格--插入---迷你图---折线图/柱形图/盈亏图
支持 折线图、柱形图、盈亏图,简单作为数据的辅助说明,可视化小工具。
四. 分组显示
使用方法:选择连续多行/多列---数据---组合---行/列组合
一般应用于 树形结构数据/分级数据,分组的效果就像在Word中建立标题一样,分为主标题、一级标题、二级标题,一个主标题包括几个一级标题,同时一个一级标题又包括几个二级标题。
举个栗子,这是一个简单的消费清单,分组前是这样:
很明显,果蔬生鲜和日用品属于第一级,水果和蔬菜分别属于第二级,西瓜、苹果、香蕉、青椒、土豆、西红柿则分属第三级;日用品下只有垃圾袋和卫生纸,我们也分为第二级好了。按照这个分级结构,分组后效果如下:
可以看出,数据已经分为3级,左侧可以折叠。
记得设置分组方向,默认是明细数据的下方,此案列应该去除勾选;
五. 分类汇总
操作:框选数据区域--数据---分类汇总(Windows)/小记(Mac)
参考如上清单,从数据库的角度来说,此清单不符合规范的,规范后的数据应如下
看到区别了吧,首行由表名变成了表头,表头每一个单元格代表一个类别/属性,是对此列内容的描述,在数据库里称之为字段。除表头外,其他行在数据库中视作一条记录。
至于为什么用了三个字段来描述具体的采购项目,也是遵循了数据库第一范式,字段不可分割原则!不好理解?那简单做个应用,做个分类汇总,汇总各二级品类的消费额。
Tips: 单选数据区任一单元格,CTRL + A,快速选择整个数据区;
设置分类字段为二级品类,汇总方式为求和,汇总项为消费,结果如下:
可以看到,左侧是分级显示,并在每个二级品类下出现了汇总结果。同样的我也完全可以按照一级品类,三级品类来进行汇总,因为他们都是不同的字段,这就是字段分割的好处。
Excel数据存储尽量规范化,保证首行是字段,同时字段不能为空值,避免合并单元格;分类汇总知道就好,大杀器「数据透视表」完全可以替代;
六. 查找/替换、定位、筛选
1. 查找和替换
最基本的操作了,通过设置查找范围,匹配样式快速定位查找/替换目标,重点是查找/替换是支持模糊查找的,使用通配符完成复杂查找。
三大基本通配符:“*” 代表任意数量[>=0]个字符;“?” 代表任意一个字符;"~" 转义字符,通配符是英文书写状态!
举个栗子,如下数据:
- 查找内容:?茶 , 符合条件的结果为“奶茶”
- 查找内容:*茶,符合条件的结果为“奶茶”,“茶”
- 查找内容: 赵?,符合条件的结果为"赵零","赵?","赵二一"
- 查找内容:赵~?, 符合条件的结果为"赵?"
快捷键分别是CTRL + F, CTRL + H;
2. 定位
定位是是个比较强大的功能,如果说查找和替换目标是单元格内容,定位的目标则是找到符合条件的单元格本身。
还是上面这份数据,我现在要在A3:A11空白区域,填充对应销售区域。
操作方法:选择数据源A3:F11---CTRL + G 打开定位---定位条件---空值---公式编辑栏输入=A3---CTRL + Enter
得到最终结果:
定位快捷键是CTRL + G;另外在MAC里,使用路径:编辑---查找---定位
3. 筛选
Excel必会的高级选项筛选,包括两个内容:去除重复项,条件筛选。
- 去除重复项
操作:数据---排序和筛选---高级---选择---选择列表区域---勾选选择不重复记录
选择列表区域的时候一定要包括字段,默认筛选结果第一项是行标题。
- 条件筛选
操作:数据---排序和筛选---高级---选择---选择列表区域---选择条件区域
条件筛选是另一个比较强大的功能,既然是条件,当然可以是一个或者多个条件,多条件之间也可以使用“与","或”进行链接,做几个练习:
Q:求南方和北方地区,2014年销售大于150的数据
条件区域必须包含字段;条件区域支持通配符
Q:求南方和北方地区,2014年销售大于150且小于等于300的数据
「与」操作,条件按行设置,每一列代表一个字段
Q:求南方和北方地区,2014年销售大于150或等小于100的数据
「或」操作,同一个字段下的多个条件执行或运算
七. 超级表
超级表不仅是表格美化,当一个表转成超级表后输入新的数据可以被自动格式化,此外超级表可以添加切片器,切片器是一个很强大的工具,经常与数据透视表配合使用,实现快速筛选,数据可视化的操作。
选择超级表右键可以转换为普通数据区域
正文结束!
PS:
不要只看,掏出你的Excel试一试,看看到底是不是很哇塞!
文中演示的Excel版本有mac的也有windows,其实本来打算用mac演示,写到一半结果电脑蓝屏了,无奈切换到公司的笔记本,系统语言默认是英文的,见谅。
之前说过,我有我分享,阅读并理解全文应该没有难度,没什么辅助材料给你,但是如果需要思维导图,欢迎私我!
最后,若此文对你有帮助,记得点赞哇,有问题欢迎留言私信!