Excel技巧

发表于 更新于 分类于 阅读次数：

快捷键

功能 快捷键
编辑单元格内容 【F2】
单元格内换行 【Alt】+【Enter】
设置单元格格式 【Ctrl】+【1】
Ctrl+E的众多效用
（拼接单元格内容、拆分提取单元格内容、
替换字符、去除空格和符号、自动换行…）		 【Ctrl】+【 E】
筛选 【Ctrl】+【Shift】+【L】
输入当前日期 【Ctrl】+【;】
输入当前时间（不含日期） 【Ctrl】+【Shift】+【;】
切换sheet表 【Ctrl】+【PgUp/PgDn】
快速求和 【Alt】+【=】
数据透视表向导 【Alt+D】+【P】
隐藏行
取消隐藏行		 选定目标区域
【Ctrl】 + 【9】
【Ctrl】 + 【Shift】 + 【(】
隐藏列
取消隐藏列		 选定目标区域，
【Ctrl】 + 【0】
【Ctrl】 + 【Shift】 +【)】
展开或折叠单元格编辑栏 【Ctrl】+【Shift】+【U】
插入行
删除行		 【Ctrl】+【Shift】+【+】
【Ctrl】+【-】
设置千位分隔符，并四舍五入为整数 【Ctrl】+【Shift】+【!】
设置百分数形式 【Ctrl】+【Shift】+【%】
设置外边框 【Ctrl】+【Shift】+【&】

绝对引用行/列

使用$符号即可绝对引用行或列

复制粘贴单元格

筛选的情况下

未被筛选的行不会被复制

隐藏的情况下

隐藏的行/列依然会被复制，想要不复制被隐藏的行/列，需要在选中指定区域后，使用【Alt】+【;】快捷键，再进行复制，即可顺利粘贴。

多个单元格中输入相同的数据

  1. 选中所有目标单元格
  2. 输入所需数据，按下【Ctrl】+【Enter】组合键即可。

多个单元格中有部分内容需要重复输入

如：邮箱后缀、电话区号、订单号前缀

希望达到效果：只需向单元格中输入不一样的内容部分，重复部分自动填充

操作：

  1. 选定需要设置的单元格范围，设置单元格格式(Ctrl + 1)

  2. 数字 - 分类 - 自定义 - 类型

  3. 输入框中填写需要的格式

    1. 添加邮箱共同后缀

      输入@"@163.com"

    2. 添加电话区号：

      输入"0755-"@

    @用于在数字格式中包含文本，否则文本将不会显示出来

条件格式的设置

批量从身份证号中提取生日

编辑单元格：=TEXT(MID(身份证号所在单元格, 起始位数, 提取长度), "生日格式"(如0000-00-00))

筛选出重复值(唯一值)

  1. 选中需要筛选出重复值(唯一值)的范围
  2. 开始 - 样式 - 条件格式 - 突出显示单元格规则 - 重复值

去除重复值

  1. 选择需要去重的单元格区域
  2. 数据 - 数据工具 - 删除重复值

长数字输入

  • Excel单元格常规型数字的特殊机制
    1. 输入的数字**＞11位时，自动采用科学计数法**
    2. 数字的精度是15位

最佳解决方式：

设置单元格格式(Ctrl + 1) - 数字 - 分类 - 文本

冻结窗口

  • 冻结指定的某行/列：选中需要冻结的行或列，选择视图 - 窗口 - 冻结窗格即可
  • 冻结指定的多行以及多列：
    1. 视图 - 窗口 - 拆分，会显示一个十字型的拆分参考线。
    2. 移动拆分参考线至所需的行、列
    3. 视图 - 窗口 - 冻结窗口

粘贴

粘贴值

文件 - 选项 - 快速访问工具栏 - 从下列位置选择命令 - 所有命令 - 粘贴值 - 添加至右侧，之后就可通过【Alt】+ 【提示的另一个键】即可实现快捷键操作。

选中整行/整列 数据

  • 选中整行数据：选定起始单元格，Ctrl + Shift + →
  • 选中整列数据：选定起始单元格，Ctrl + Shift + ↓

数据透视表

数据透视表(Pivot Table) 是一种交互式的表，可以进行某些计算，如求和与计数等，所进行的计算与数据跟数据透视表中的排列有关。

之所以称为数据透视表，是因为可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。

  1. 选择需要透视的表格区域
  2. 插入 - 表格 - 数据透视表

函数

VLOOKUP函数

按列查找，最终返回查询序列中所对应的值。

与之对应的HLOOKUP是按行查找的。

  • 使用场景：
    1. 两张表格
    2. 两张表格中存在相同列
    3. 表2中存在表1不具备的字段，想把表2中的字段关联到表1中
  • 单元格中输入：=VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)
    1. lookup_value：查找的值
    2. table_array：要查找的区域
    3. col_index_num：需要返回的元素在区域中的第几列
    4. range_lookup：精确匹配/近似匹配
      • 1/TRUE：近似匹配
      • 0/FALSE：精确匹配

INDEX + MATCH 函数

  • 使用场景：

    功能与VLOOKUP函数基本相同，但是能弥补VLOOKUP函数的局限性(想要关联的列在相同列的左侧时，无法匹配)。

  • MATCH(lookup_value, lookup_array, [match_type])

    1. lookup_value：查找的值
    2. lookup_array：查找区域(只能1列宽)
    3. [match_type]：
      • 1：小于
      • 0：精确匹配
      • -1：大于

    结果返回查找区域中的所在行数

  • INDEX(array, row_num, [column_num])

    1. array：查找区域
    2. row_num：所在行数(通过MATCH函数获取)
    3. [column_num]：所在列数

AND 函数 / OR 函数

  • AND(logical1,[logical2],...)
  • OR(logical1,[logical2],...)

用于条件判断

IF 函数

IF(logical_test, [value_if_true], [value_if_false])：

  1. logical_test：逻辑判断
  2. [value_if_true]：结果为true时的值(字符串使用双引号标注)
  3. [value_if_false]：结果为false时的值

例子：使用IF函数，判断业绩完成情况

单元格中输入：=IF(N4-M4>=0,"已完成", M4-N4)

IF 函数嵌套 示例

=IF(AND(O4="已完成",R4="已完成"),"已完成",IF(AND(O4="已完成",R4<>"已完成"),"仅业绩完成",IF(AND(O4<>"已完成",R4="已完成"),"仅入会完成","均未完成")))

COUNTIFS 函数

COUNTIFS(criteria_range1, criteria1,...)

  • 使用场景：

    对区域中符合条件的单元格进行筛选计数。

  • 参数：

    • criteria_range1: 筛选区域

    • criteria1：筛选条件

    • criteria_range2

      …

SUMIFS 函数

SUMIFS(sum_range, criteria_range1, criteria1,...)

  • 使用场景：

    对区域中符合条件的单元格进行筛选求和。

  • 参数：

    • sum_range：求和区域

    • criteria_range1：筛选区域

    • criteria1：筛选条件

    • criteria_range2

      …

    求和区域和筛选区域，类似VLOOKUP函数中两张表的感觉。

LEFT、MID、RIGHT 函数

  • LEFT(text, [num_chars])
  • MID(text, start_num, num_chars)
  • RIGHT(text, [num_chars])
    • text：文本所在单元格
    • start_num：起始位置是第几个字符
    • num_chars：截取的字符长度

参考

数据分析中常用的9个Excel函数 - 简书