时光驿站

主题

Excel 常用公式 ​

时光2025/8/310 0 m

建议

这是一套为精心整理的 Excel 办公实用技巧大全，从基础操作到高阶函数，涵盖了日常办公中绝大多数场景的需求。掌握它们，办公效率将会飙升！

第一部分：基础核心操作技巧（提升效率的根本） ​

  1. 闪电填充 (Ctrl + E)

    • 功能：智能识别您的操作 pattern，自动完成数据拆分、合并、提取、格式化等。
    • 场景：从身份证号提取生日、将姓和名合并、给数据统一添加前缀/后缀。
    • 用法：在第一个单元格手动输入期望的结果 -> 选中该列 -> 按下 Ctrl + E。

  2. 快速填充序列

    • 输入前两个数字（如 1, 2）-> 选中它们 -> 拖动填充柄（单元格右下角的小点）。
    • 直接双击填充柄，可自动填充到相邻列的最后一行。

  3. 冻结窗格

    • 功能：锁定行或列，使其在滚动时始终保持可见。
    • 用法：查看首行/首列 ->「视图」->「冻结窗格」->「冻结首行」/「冻结首列」；如需冻结多行多列，选中目标单元格右下方的第一个单元格 ->「冻结窗格」。

  4. 快速定位 (Ctrl + G 或 F5)

    • 快速跳转到特定单元格、区域。
    • 定位空值：F5 ->「定位条件」->「空值」，可批量对空单元格操作（如批量输入 0 或“N/A”）。
    • 定位可见单元格：复制筛选后的数据时，先Alt + ;选中可见单元格，再复制粘贴，避免粘贴到隐藏行。

  5. 选择性粘贴 (Ctrl + Alt + V)

    • 值粘贴：只粘贴计算结果，不带公式。
    • 转置：将行数据快速转换为列数据，反之亦然。
    • 运算：将所有选中单元格统一+ / - * /某个固定值。

  6. 快速删除重复值

    • 「数据」->「删除重复值」，一键清理重复数据行。

  7. 自定义列表

    • 功能：自定义填充序列（如：销售一部、销售二部...）。
    • 用法：「文件」->「选项」->「高级」->「常规」->「编辑自定义列表」。

第二部分：数据整理与清洗技巧（规范数据是分析的前提） ​

  1. 分列功能

    • 功能：将一列文本按分隔符（如逗号、顿号、空格）或固定宽度拆分成多列。
    • 场景：拆分全名、分离地址和邮编、处理从系统导出的不规范数据。

  2. 数据验证（数据有效性）

    • 功能：限制单元格可输入的内容，防止他人录入无效数据。
    • 场景：
      • 下拉列表：制作性别（男/女）、部门等选择项。
      • 限制数字范围：如年龄必须在 18-65 之间。
      • 自定义文本长度：如手机号必须为 11 位。

  3. 查找和替换 (Ctrl + F / Ctrl + H)

    • 批量替换错误内容。
    • 使用通配符：*（代表任意多个字符）、?（代表单个字符）进行模糊查找替换。

  4. 格式刷超级用法

    • 单击格式刷：使用一次。
    • 双击格式刷：可以无限次使用，直到再次按下 ESC 键或单击格式刷按钮。

  5. 快速生成下拉菜单

    • 选中区域 ->「数据」->「数据验证」->「序列」-> 来源框中输入选项，用英文逗号隔开（如：已完成,未开始,进行中）。

第三部分：公式与函数核心技巧（数据分析的灵魂） ​

  1. 绝对引用与相对引用 (F4)

    • 在编辑公式时，按F4键可以循环切换引用方式（A1 -> $A$1 -> A$1 -> $A1）。这是编写正确公式的关键！

  2. 最常用函数组合

    • 求和：SUM / 条件求和：SUMIF / SUMIFS
    • 计数：COUNT / 条件计数：COUNTIF / COUNTIFS
    • 查找：VLOOKUP / XLOOKUP (Office 365 推荐，更强大)
    • 逻辑判断：IF + AND + OR
    • 日期计算：DATEDIF (计算两个日期之间的天数、月数或年数)

  3. IFERROR 函数

    • 美化表格，屏蔽错误值。=IFERROR(你的公式, "如果出错显示的内容")，如=IFERROR(VLOOKUP(...), "未找到")。

  4. 条件格式

    • 功能：让数据自己“说话”，自动突出显示重要单元格。
    • 场景：
      • 突出显示高于/低于平均值的数字。
      • 用数据条/色阶/图标集直观展示数据大小。
      • 标记出重复值。

第四部分：数据分析与可视化技巧（让报告会说话） ​

  1. 超级表 (Ctrl + T)

    • 将普通区域转换为“超级表”，自动扩展格式和公式，自带筛选器，汇总行，且美观。

  2. 数据透视表

    • Excel 中最强大的功能，没有之一！
    • 功能：无需写复杂公式，通过拖拽字段即可快速完成海量数据的分类汇总、排序、计数、平均值计算等。
    • 场景：制作月报、周报，分析销售数据、客户数据等任何需要汇总的表格。

  3. 数据透视图

    • 数据透视表的图形化展示，联动交互，图表随数据透视表的变化而变化。

  4. 切片器 + 日程表

    • 为数据透视表和超级表添加直观的筛选按钮，报告颜值和易用性瞬间提升。

  5. 常用图表选择指南

    • 趋势：折线图
    • 占比：饼图/圆环图
    • 比较：柱形图/条形图
    • 关联：散点图/气泡图

第五部分：快捷键之王（键盘侠的终极追求） ​

掌握这些，鼠标点一下的操作你只需按一下。

  • Ctrl + A：全选
  • Ctrl + C/V/X：复制/粘贴/剪切
  • Ctrl + Z/Y：撤销/恢复
  • Ctrl + F/H：查找/替换
  • Ctrl + ;：输入当前日期
  • Ctrl + Shift + ;：输入当前时间
  • Ctrl + Enter：在选中的多个单元格中同时输入相同内容
  • Ctrl + D/R：向下填充/向右填充
  • Ctrl + 1：快速打开“设置单元格格式”对话框
  • Alt + =：自动求和
  • Alt -> D -> F -> F：快速应用/取消筛选
  • F2：编辑活动单元格
  • F4：重复上一步操作（如插入行、设置格式）或切换引用方式
  • F12：另存为

第六部分：思维与习惯（高手之道） ​

  1. 保持数据源规范：一维表结构（第一行是标题，每一行是一条记录，每一列是一个字段），避免合并单元格。
  2. 原始数据与报表分析分离：永远在一张工作表上存放原始数据，在另一张表上用公式或透视表进行分析。动原始数据是灾难的开始。
  3. 多用辅助列：复杂的公式可以拆解成多个简单的步骤用辅助列完成，易于理解和调试。
  4. 命名区域：给重要的数据区域起个名字，让公式更易读（如=SUM(销售总额)）。
  5. 保护工作表/工作簿：做完表格后，保护公式单元格和结构，防止他人误操作。

其他技巧：在两个字的名字中间加空格 ​

bash
=IF(LEN(A2)=2,LEFT(A2,1)&" "&RIGHT(A2,1),A2)

公式解释

  1. LEN(A2): 这个函数计算单元格 A2 中文本字符串的长度（包括空格和其他非字母字符）。
  2. LEFT(A2,1): 如果 A2 中有文本，此函数将返回该文本最左边的一个字符。
  3. RIGHT(A2,1): 同样地，如果 A2 中有文本，此函数将返回该文本最右边的一个字符。
  4. IF(条件, 真值, 假值): 这是一个条件判断函数，它首先评估一个逻辑条件，如果条件为真，则返回第二个参数；如果条件为假，则返回第三个参数。

将这些组合起来，公式的意思是：

  • 如果 A2 单元格中的文本长度为 2 个字符（LEN(A2)=2），那么：

    • 将左边的第一个字符 (LEFT(A2,1)) 和右边的第一个字符 (RIGHT(A2,1)) 之间插入一个空格，并将它们连接起来 (LEFT(A2,1)&" "&RIGHT(A2,1)).

  • 如果 A2 单元格中的文本长度不是 2 个字符，那么就直接返回 A2 中的内容 (A2)。

示例:

  • 如果 A2 包含 "AB"，公式将返回 "A B".
  • 如果 A2 包含 "ABC" 或 "A" 或任何其他长度不等于 2 的文本，公式将返回原始文本 "ABC"、"A" 等等。

VitePress Algolia Twikoo EdgeOne Copyright

版权所有 © 2023-2025 时光驿站