excel常用技能
1.基础技能
1.1 下拉框设置
a. 选中需要设置的列或单元格,数据 ---》 数据验证
b.验证条件 ---> 序列(多个值逗号隔开)
1.2 进度条百分比显示设置
开始 ---> 条件格式 --->新建规则--->编辑规则
1.3 相对引用和绝对引用
相对引用:公式引用时相对位置不变,行号、列号相对递增。使用场景:如图计算每种产品的销售金额,=SUM(C2:E2)
绝对引用:公式填充时应用的单元格绝对位置不变,行和列、序号都不变。绝对引用的符号是“$”。例如$A$1
。绝对引用适用于那些需要固定引用的场景,例如在计算某个商品的总销售额时,单价这个数值是固定不变的,使用绝对引用来引用单价所在的单元格是非常合适的。
混合引用:例如,$A1
表示列固定,行号相对变动;A$1
表示行固定,列号相对变动。适用于需要部分固定引用的情况,例如在处理大型数据表时,固定某些列或行以便于数据管理。
九九乘法表:=$A1*$B1,列字母不变,一直为A;行数字不变,一直为1
1.4 自定义序列
a.文件 ---> 选项 , 高级--->常规--->编译自定义列表,自定义序列--->添加--->确定
b.单元格输入 ---> 下拉,即立刻填充
1.5 设置不间断序列
设置:=row() - 1,下拉后自增,删除后自动变更序号
1.6 同时输入多个单元格
选中要输入的单元格 ---> 编辑栏输入内容 ---> ctrl+enter
1.7 批量加单位
选中需要配置的单元格、行、列 ---> 设置单元格格式 --->自定义--->通用格式 --->#单位(#前后均可以添加单位)
1.8 行列转换
复制内容---> 选择单元格右键--->点击选择性粘贴 --->转置--->确定
1.9 行行或列列位置互换
选中该列---> 点击shift--->手表放在列沿,拖动即可完成替换
1.10 行列快速求和
选中需要求和的行列 ---> alt+等于
如图:行列均选中空单元格,则行列均求和;否则默认求列值。
行列均选中空单元格
不选择空单元格
1.11 仅显示固定数量的行列
选中行或列,ctrl+shift+右键(下键),右键隐藏
隐藏后的效果
选中效果
ctrl+shift+右键或下键,可保证选中从当前行/列到最后的行/列
1.12 单元格-格式校验
选择需校验的单元格(选中的单元格、某行、某列)---> 数据 --->数据验证--->数据验证--->验证条件(可设置出错告警)。
默认验证条件为任何值,自定义选项可设置函数,更严谨的格式验证。
1.13 条件格式
根据单元格的值自动应用不同的格式,从而帮助用户更好地理解和分析数据
开始 ---> 条件格式 ---> 新建规则:
-
突出显示单元格规则:
- 大于、小于、介于、等于:用户可以选择这些条件来突出显示满足特定条件的单元格。例如,可以选择“大于”条件,并将满足条件的单元格背景设置为红色1。
- 排名靠前或靠后的值:用户可以设置仅对排名前10%或后10%的值应用格式,以便快速识别数据中的极端值2。
-
颜色刻度:
- 最小值、中间值、最大值:用户可以为数据设置不同的颜色,例如从红色到绿色的渐变,以直观展示数据的分布情况1。
-
数据条:
- 长度表示数值大小:数据条的长度可以根据单元格的值变化,长度越长表示数值越大,适用于展示数值范围较大的数据1。
-
图标集:
- 交通灯、符号:用户可以选择不同的图标样式,如交通灯(红黄绿)来表示数据的好坏,适用于展示评级或状态信息1。
-
使用公式确定格式:
- 自定义公式:用户可以输入自定义公式来设置格式。例如,可以使用公式
=TODAY()>45209
来隐藏超过特定日期的数据,从而在数据到期后自动“销毁”数据3。
- 自定义公式:用户可以输入自定义公式来设置格式。例如,可以使用公式
-
管理条件格式规则:
- 编辑、删除规则:用户可以随时编辑或删除已应用的条件格式规则,以便根据需要调整格式1。
1.14 锁定单元格
锁定选中单元格
a.取消初始化表格锁定:默认勾选
b.选择需要锁定的单元格
c.审阅-->保护工作表
锁定整个表格:
审阅 ---> 保护工作表
2.函数
2.1 统计函数-count
a.=count(区域,区域,......)
统计数量,只针对为数值的单元格;
示例如下:
b.=countif(区域,条件)
功能:统计出满足某些条件的单元格数量
c.=countifs(区域1,条件1,区域2,条件2,.....)
功能:统计多条件的单元格数量
2.2 统计函数-sum
a.=sum(num1,.....)
单区域、多区域、合并单元格
参数可以为常量、单元格区域、单元格引用
b.=sumif(range,criteria,sum_range)
range:条件区域
criteria:条件
sum_range:实际求和区域,不填默认为range
c.=sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range:求和区域
criteria_range1:条件区域
criteria1:条件
2.3 统计函数-Average
计算1组数据的平均数
Average_range:求值的区域
criteria_range1:条件区域
criteria1:条件
2.4 统计函数-其他函数
Max:提取一组数中的最大值
Min:一组数的最小值
Large函数:返回第N个最大值,=LARGE(D:D,F2)
Small函数:提取第N个最小的数,=SMALL(A1:A10)
Rank函数:计算某个值在一组数据中的排名,=RANK(B2,B:B)