文章目录
前言
Excel小技巧记录
一、快捷键
1.添加行
按住shift鼠标移到左侧单元格下缘,变成双横线箭头后往下随便拖动几行就是添加几行。
2.快速筛选
cntr+shift+L
3.快速定位选中
到a1单元格:cntr+⬆️⬅️
连续选中:cntr+shift+⬇️➡️
二、引用
1.转换公式中的单元格及区域的引用方式
- 选中整个公式或单元格按下F4键,单元格中间和前面均有美元符号,表示对横、纵行单元格均进行绝对引用。
- 第二次按下F4键,仅单元格字母数字之间有美元符号,表示对横行进行绝对引用,列相对引用。
- 第三次按下F4键,字母前美元符号,表示对列绝对引用。
- 第四次按下F4键时,回到初始状态。
2.同一Excel文件中引用另一个工作表
-
感叹号(!)将工作表名称与单元格地址分隔开
如:= 数学分析!B3 -
如果工作表名称包含空格,则必须在引用中用单引号将该名称引起来。
如:=‘Mathematic Analysis’!B3
3.查找
- VLOOKUP(查找值,查找区域,对应的数据区域内的列号,[匹配查找值和首列类型,精确匹配输入0])
注:查找值必须在查找区域的首列 - MATCH(查找值,区域,[0])
给出有个查找值,返回在指定范围内的相对行号或列号 - INDEX()
与MATCH相反,根据索引号返回指定值 - OFFSET()
偏引
注:区域:ctrl+shift+enter
三、公式函数
1.逻辑
- IF(logical_test,[value_if_true],[value_if_false])
logical_test:逻辑表达式,比如,判断A2的值是否大于A1的值,本参数可以使用任何比较运算符。也可以使用函数的返回值,比如用and函数的返回值作为第一个参数。 - AND(logical1,logical2,…,Logical1, logical2,…表示待检测的 1到 N 个条件表达式。参数说明:所有参数的逻辑值为真时,返回TRUE
- OR(logical1,logical2,…) 一真都真
2. MOD
MOD(number,divisor)。Number必需,要计算余数的被除数。Divisor必需,为除数。
注:当除数和被除数的符号不一样时,即:一个为正一个为负。
余数=除数*(整商+1)-被除数。
四、数据透视
1.基础操作
- 生成:(来源)选中数据,插入~数据透视表,(位置)可选中定位某个表的某单元格;
- 修改操作:要定位到透视表任意单元格,上出现数据透视表工具;
- 移动或删除:选中整个数据透视表;
- 基础排序等等:选择某单元格,右击;
2.切片器和报表连接
- 生成:选中数据透视表的某单元格,分析~切片器
- 建立连接:右击切片器~选透视表
3.动态透视表
offset(目标单元格,行偏移,列偏移,[高:COUNTA(某一列)即:COUNTA(源数据!$A: $A)],COUNTA(源数据!$1:$1))
五、图表
1.基础图表
- 比较类:柱形图、条形图、折线图、雷达图
- 构成类:饼图(类别少)、树状图、旭日图(层级关系)
- 分布、联系:散点图、气泡图(三个维度)
- 时间序列图类:(折线图若变成俩图例俩线,如:销量、季度,轴标签仅年,可选择数据中删除季度图例,再编辑水平标签同时选中源数据的年和季度)
- 统计类:直方图(频数)、箱型图(百分位数)
- 增维分析图:如,先画一张两个维度的基础折线图,再画其中每个节点维度的扇形图,以图片形式粘贴到节点上。
2.公式动态图表
- 文件~选项~自定义功能区~开发工具
- 开发工具~插入~列表框~单元格内画控件
- 右键选中可拖拽或者设置控件格式,如选中列
- 复制源数据的行到空白处,作为动态表,如起始于G1
- 在复制的表头行下面选中与源数据相同大小的区域,=OFFSET(A1,G1(获取动态偏移值),0,动态表数据高度(如1),数据宽度)区域计算用ctrl+shift+enter
- 选中动态数据表,插入图表
- 选中控件~格式~上移一层,让控件悬浮于图表上
- 右键选中控件~单机图表~右键组合