记录生活
所有文章 分类 标签 60s Wallpaper Chat RSS Casual
记录生活

Contents

Excel操作技巧

   2419 words   5 minutes 

参考资料

文中所有快捷键 “+” 表示 先后 按下组合键，而不是键盘上的 “+”

基本概念

Excel 的适用场景 说明
数据录入、管理、分析 快速组织与统计信息
项目管理 甘特图、里程碑追踪
财务会计 报表、预算、成本核算

C4 表示第 C 列 与 第 4 行 的交叉点，列用字母，行用数字
Shift + F11 可以新建工作表
使用冒号 : 进行范围选择（如 A1:C10）

  • 填充操作

    填充图标位于单元格的右下角，将鼠标悬停在其上后，鼠标图标会更改为 十字
    单击并按住鼠标左键，拖动并标记要覆盖的范围

    填充类型 操作要点
    复制 选中一个需要复制的单元格 → 拖动右下角十字 → 复制内容
    序列 选中相邻两个有序值（如 1、2）→ 拖动 → 自动递增（数字、字母均可）
    日期序列 在单元格中输入日期（如 2021/07/29）→ 拖动 → 自动填充递增的日期
    公式填充 在 E2 中输入 =B2+C2，双击右下角或拖动 → 自动填充整列公式

    公式拖拽后出现 #REF! 错误怎么办？
    检查引用是否超出表格范围；如需固定引用，请使用 $ 锁定列/行

  • 删除单元格

    Delete 键只会删除单元格中的值，不会清除单元格的格式（颜色、边框等）

  • 引用

    类型 示例 说明
    相对引用 =A1 随单元格移动而自动变化
    绝对引用 =$A$1 行列均锁定
    列绝对、行相对 =$A1 列锁定，行随拖动变化
    行绝对、列相对 =A$1 行锁定，列随拖动变化

    https://gcore.jsdelivr.net/gh/yjjyjya/mypicbed/about_Other/pic/%E7%BB%9D%E5%AF%B9%E5%BC%95%E7%94%A8.png

  • 圆括号 ()

    用于改变计算顺序，使 Excel 首先计算括号内的数字，然后再算外部
    使用更高级的公式时，可能需要嵌套括号，我们可以把它看成一个洋葱，它有很多层，Excel 会先从内层开始计算

常用函数

函数名 功能描述
=IF 根据 TRUE 或 FALSE 条件返回值
=IFS 根据 一个或多个 TRUE 或 FALSE 条件返回值
=AVERAGE 计算平均值（算术平均值）
=AVERAGEIF/=AVERAGEIFS 条件平均值（单 / 多条件）
=COUNT 统计数值单元格数量
=COUNTA 统计范围内 所有有值的单元格，包括数字和字母
=COUNTBLANK 统计范围内的 空白单元格
=COUNTIF/=COUNTIFS 条件计数（单 / 多条件）
=MAX/=MIN 返回范围内的 最大值/最小值 */
=MEDIAN 返回数据的 中位数
=MODE 返回出现次数最多的数字（众数）
=STDEV.P 计算整个 总体 的标准偏差 (Std)
=STDEV.S 计算 样本 的标准偏差 (Std)
=SUM/=SUMIF/=SUMIFS 将一个范围内的数字 求和（全局/单条件/多条件）
=VLOOKUP 垂直查找（左侧键值匹配）
=AND/=OR/=XOR 逻辑运算

如何在大量数据中快速统计唯一值？
使用 =SUM(1/COUNTIF(range, range))（记得配合 Ctrl + Shift + Enter）
或在 Excel 365 直接使用 =UNIQUE(range)

VLOOKUP 函数

1

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

参数说明：
lookup_value: 需要搜索的关键字，可以是值或者对应的单元格引用
table_array: 需要查找的表格范围（建议使用 绝对引用 如 $A$1:$D$100）
col_index_num: 需要查找的数据的列编号，从左数起
[range_lookup]: 如果是数字，则为 TRUE 或 1；如果是文本，则为 FALSE 或 0

注意
VLOOKUP 的搜索列必须是 table_array 的第一列，若需要右侧匹配，可改用 INDEX/MATCH 或 XLOOKUP（Excel 365）

实用操作

排序

不建议单独对某列进行排序，而是对 整表 排序，避免单列排序导致行数据错位
操作步骤：选中任意单元格 → 数据 → 排序 → 选择排序列 → 指定升/降序

筛选

  1. 选中表头行 → 数据 → 筛选（出现下拉箭头）
  2. 点击下拉箭头即可 按数值、文字、颜色或条件 过滤
  3. 常用快捷键：Ctrl Shift L（打开/关闭筛选）

计算与图表

快捷键 功能
Alt + = 一键求和：自动在选中区域下方插入 SUM
Alt + F1 插入默认柱形图（基于选中数据）
F11 插入 独立图表（单独工作表）
Ctrl + Shift + Enter 输入 数组公式（在新版 Excel 中已逐步被“动态数组”取代）

文本 & 格式

快捷键 功能
Alt + Enter 单元格 换行
Ctrl + E Flash Fill：智能填充（根据模式自动完成）
Ctrl + H 替换：批量修改文本/数值
Ctrl + 1 打开 单元格格式 对话框
Ctrl + T 将选区转为 Excel 表格（结构化引用、自动筛选）
Ctrl + Shift + $ 快速设为 货币格式
Ctrl + Shift + % 快速设为 百分比格式

导航 & 选区

快捷键 功能
Alt + 方向键 快速 跳转到工作表边缘（同一连续数据区域的首/尾）
Ctrl + 方向键 移动到 数据块的边界（空白前/后）
Ctrl + 空格 选中 整列
Shift + 空格 选中 整行
Ctrl + Shift + L 打开/关闭 筛选
F5 → 条件 → 空值 → 删除 批量删除空行

其他常用操作

操作 步骤或快捷键
小写金额转大写 选中单元格 → 右键 → “设置单元格格式” → “特殊” → “中文大写数字”
快速输入 √ / × 设置字体为 Wingdings 2；P → √，O → ×，R → □√，S → ☒
批量下划线 选中单元格 → 设置单元格格式 → 自定义 → 输入 @*_
文字随单元格大小变化 设置单元格格式 → 对齐 → 勾选 缩小字体填充
图片随单元格大小变化 右键图片 → 大小和属性 → 选择 随单元格改变位置和大小
长短姓名对齐 设置单元格格式 → 对齐 → 水平 → 分散对齐
横向转纵向（转置） 复制 → 右键粘贴 → 转置
插入当前日期/时间 日期：Ctrl + ;  时间：Ctrl + Shift + ;
快速找不同（横向） 选中两列 → Ctrl + \（显示差异）
制作斜线表头 选中单元格 → Ctrl + 1 → 边框 → 斜线；再在 字体 中设置上/下标
插入二维码 开启 开发工具 → 插入 → BarCode Control（选择 QR 码，设置样式）
自动添加边框 选中区域 → 开始 → 条件格式 → 新建规则 → 使用公式确定要设置的单元格 → 设置边框
冻结窗格 选中首行或首列 → 视图 → 冻结窗格 → 选择相应选项

自定义单元格格式

https://www.sohu.com/a/668658789_121124012

双引号：需要添加的内容，例如要加一个单位 "元"

  • 0

    用 0 补足位数
    例如原本一个单元格的值为 12.34
    我们将其单元格格式的类型自定义为 0000.0000
    则最终该单元格展示出来的结果为 0012.3400

    "S"000，数字用 0 补足三位数，并在最前面添加字母 S

  • #

    假设原本一个单元格的值为 12.345
    我们将其单元格格式的类型自定义为 #.##
    则最终该单元格展示出来的结果为 12.35，自动进行 四舍五入
    如果自定义为 #.###，则结果为 12.345

  • ?

    用于在数据前后添加无意义的空格
    能够实现小数点对齐的效果，方便设置固定宽度
    假设两个单元格的值为 12.345、123.4
    我们将其单元格格式的类型自定义为 ????.????
    展示的结果为 __12.345_、_123.4___（此处通过下划线演示空格）

  • @

    表示原始文本 假设单元格的值为 财务部
    我们将其单元格格式的类型自定义为 腾讯公司@
    展示的结果为 腾讯公司财务部

  • *

    作用是重复 * 后面的字符，直到充满列宽
    例如在 $* 后面加一个空格，就可以实现 $ 符号 靠左对齐 的效果

目标 格式代码 说明
千位分隔 #,##0 1234567 → 1,234,567
货币（带单位） ¥#,##0.00"元" 1234.5 → ¥1,234.50元

小技巧：在“自定义”格式中使用 分号 可以分别设定正、负、零、文本的表现，例如 0.00;[红色]-0.00;"-"

Updated on 2022-05-14
Back | Home