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. ‌突出显示单元格规则‌：

   • ‌大于、小于、介于、等于‌：用户可以选择这些条件来突出显示满足特定条件的单元格。例如，可以选择“大于”条件，并将满足条件的单元格背景设置为红色‌1。
   • ‌排名靠前或靠后的值‌：用户可以设置仅对排名前10%或后10%的值应用格式，以便快速识别数据中的极端值‌2。

2. ‌颜色刻度‌：

   • ‌最小值、中间值、最大值‌：用户可以为数据设置不同的颜色，例如从红色到绿色的渐变，以直观展示数据的分布情况‌1。

3. ‌数据条‌：

   • ‌长度表示数值大小‌：数据条的长度可以根据单元格的值变化，长度越长表示数值越大，适用于展示数值范围较大的数据‌1。

4. ‌图标集‌：

   • ‌交通灯、符号‌：用户可以选择不同的图标样式，如交通灯（红黄绿）来表示数据的好坏，适用于展示评级或状态信息‌1。

5. ‌使用公式确定格式‌：

   • ‌自定义公式‌：用户可以输入自定义公式来设置格式。例如，可以使用公式=TODAY()>45209来隐藏超过特定日期的数据，从而在数据到期后自动“销毁”数据‌3。

6. ‌管理条件格式规则‌：

   • ‌编辑、删除规则‌：用户可以随时编辑或删除已应用的条件格式规则，以便根据需要调整格式‌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)