Excel软件使用教程以及常用技巧

Excel软件使用教程以及常用技巧

· 8 ·

文章标签：

Yolo-v8.3一键部署

1 入门：界面与基本操作（新手必会）

• 工作簿 / 工作表 / 单元格 ：工作簿（.xlsx）可包含多个工作表（Sheet1、Sheet2…）。单元格由列字母+行数字定位（如 A1）。

• 输入与编辑 ：双击或在编辑栏编辑；Esc 取消；Enter 确认并下移；Ctrl+Enter 在选定区域同时填充。

• 选择技巧 ：

  • Ctrl+→ / ← / ↑ / ↓：跳到数据区域边缘。

  • Shift + 空格：整行；Ctrl + 空格：整列。

• 填充柄（拖拽） ：自动序列（日期、数字、文本模式）与公式复制（相对/绝对引用区别，见下）。

• 格式 ：数字、货币、百分比、自定义格式（例如 0.00、#,##0、yyyy-mm-dd）。

• 打印预览与页面设置 ：页面布局 → 纸张方向、缩放、分页符预览（Page Break Preview）。

2 常用函数与公式（必须掌握）

优先级（建议学习顺序）：SUM、AVERAGE、COUNT → 条件函数 IF → 查找引用 VLOOKUP/XLOOKUP/INDEX+MATCH → 逻辑与文本函数 → 时间函数 → 聚合与条件聚合 → 动态数组（365）

基础公式

• 求和：

• 平均：
  =AVERAGE(B2:B20)

• 条件计数 / 求和：
  =COUNTIF(range, criteria)，=SUMIF(range, criteria, sum_range)
  多条件：=SUMIFS(sum_range, criteria_range1, crit1, criteria_range2, crit2)

逻辑

• IF：
  =IF(A1>100, "大于100", "≤100")

• 嵌套 IF（较复杂时推荐使用 IFS，Excel 365/2019 有 IFS）：
  =IFS(A1>90,"A",A1>80,"B",TRUE,"C")

查找引用（常见误区）

• VLOOKUP（靠左查右）：
  =VLOOKUP(lookup_value, table_array, col_index, FALSE)
  缺点：必须把查找列放最左；使用列索引，插列会导致错误。

• 推荐：XLOOKUP（Excel 365 / 2021）：
  =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  示例：=XLOOKUP(E2, A:A, B:B, "未找到")

• 通用稳定组合：INDEX + MATCH：
  =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) — 灵活且不易被列位置影响。

文本与分列

• 拼接：=A1 & " " & B1 或 =CONCAT(A1,B1) / =TEXTJOIN(" ",TRUE,A1:C1)（后两者更强）

• 提取：LEFT、RIGHT、MID、FIND、SEARCH
  示例：=LEFT(A1,4) 取左 4 个字符。

• 文本转列（分列）：数据 → 文本到列（按分隔符或固定宽度）。

日期时间

• TODAY/ NOW：=TODAY()、=NOW()

• 日期差：=DATEDIF(start,end,"d")、用 NETWORKDAYS 计算工作日。

• 格式化：=TEXT(A1,"yyyy-mm-dd")

聚合与条件聚合（高级）

• SUMPRODUCT 做条件计算/加权平均：
  =SUMPRODUCT((range1=crit1)*(range2)*(weights))/SUMIFS(weights,range1,crit1)

• AGGREGATE 可忽略错误或隐藏行取最小/最大等。

动态数组（仅 Excel 365/2021）

• FILTER：过滤返回数组。=FILTER(table, condition, "无数据")

• UNIQUE：去重。=UNIQUE(range)

• SORT/SORTBY：排序数组。

• 动态数组改变公式思路，能大幅简化数据处理。

3 数据整理与清洗（日常最常做）

• 去重 ：数据 → 删除重复项，或使用 UNIQUE（365）。

• 空值处理 ：IFERROR/IFNA 捕获错误：=IFERROR(formula, "")

• 合并列/行表 ：使用 & 或 TEXTJOIN；Power Query 更强（见高阶）。

• 分列合并（文本到列/CONCAT） 。

• 查找并替换 ：Ctrl+H，支持通配符 *、?。

• 数据验证（防错） ：数据 → 数据验证，设置下拉、数值范围、公式规则。

• 条件格式 ：高亮重复值、top/bottom、基于公式的格式（比如标红负值：公式 =A1<0）。

4 数据透视表（PivotTable） — 强力工具

• 创建 ：插入 → 数据透视表 → 选择数据与位置。

• 拖拽将字段放到“行、列、值、筛选器” ，即可做分组汇总、交叉表、聚合（求和/计数/平均）。

• 切片器（Slicer）和时间线（Timeline） ：交互式筛选控件（视觉操作便捷）。

• 刷新 ：数据源改变需刷新（右键 → 刷新），可设置后台刷新或连接外部数据。

• 显示值方式 ：% of Row/Column/Grand Total，差异百分比等。

5 图表与数据可视化（让数据“说话”）

• 常用图表 ：柱状图、折线图、饼图（慎用）、散点图、面积图、组合图（柱+线）。

• 制作流程 ：选数据 → 插入 → 选择图表 → 右键格式化 → 图表元素（标题、图例、数据标签）。

• 建议 ：

  • 轴刻度、网格线、颜色选择要为信息服务（避免视觉噪音）。

  • 使用图表模板：右键图表 → 另存为模板。

• 动态图表 ：用表格（插入 → 表） + 数据透视或动态命名范围（OFFSET、INDEX）实现自动扩展，Excel 365 用动态数组更方便。

6 自动化：Power Query、宏 (VBA)、公式自动化

Power Query（获取与转换数据）

• 位置：数据 → 从表/范围 / 从文本/CSV / 从Web / 从数据库。

• 用途：导入、清洗（拆列、替换、过滤、合并列、透视/取消透视）、合并多个表（Merge）或追加（Append）。

• 优点：流程可重用、一步步记录，适合 ETL 场景。

宏与 VBA（适合重复任务）

• 录制宏（开发工具栏 → 录制宏）可快速生成 VBA 脚本。

• 简单示例（在模块中）：

Sub Hello() MsgBox "Hello, Excel!" End Sub

• 要点：尽量避免在共享工作簿中放置未签名宏；保存为 .xlsm。

Office 脚本 / Power Automate（365 环境）

• 可在云端自动化与跨应用流程集成（适合企业使用）。

7 性能与大表处理（避免卡顿）

• 使用表（Ctrl+T）和数据透视表代替大量手动公式。

• 避免大量数组公式或挥发性函数（如 INDIRECT, OFFSET, NOW, RAND），它们会频繁重算。

• 将不经常变的数据转换为值（复制 → 粘贴为值）。

• 分页加载、分区数据或用 Power Query 预处理再加载。

• 如果数据量极大，考虑用 Power BI 或数据库（SQL）做分析，Excel 做展示。

8 常用快捷键速查（提高效率）

• 保存：Ctrl+S；撤销：Ctrl+Z；重做：Ctrl+Y

• 复制/粘贴/剪切：Ctrl+C / Ctrl+V / Ctrl+X

• 全选：Ctrl+A；插入行：Ctrl+Shift+"+"；删除行：Ctrl+"-"

• 查找/替换：Ctrl+F / Ctrl+H

• 新建工作簿：Ctrl+N；切换工作表：Ctrl+PageUp/PageDown

• 快速输入今天日期/时间：Ctrl+; / Ctrl+Shift+;

• 公式模式切换（显示/隐藏公式）：Ctrl+`（左上角）

• 插入图表：Alt+F1（默认图表）或 F11（新图表工作表）
  （建议把常用的 10-20 个快捷键记住，工作流流畅度成倍提升）

9 实用小技巧（实战中常用）

• 命名范围 ：公式更可读。选区域 → 在名称框输入名称（如 Sales2025），公式：=SUM(Sales2025)

• 表格格式（Ctrl+T） ：自动扩展、结构化引用（[ColumnName]）、更容易用于数据透视和图表。

• 结构化引用示例 ：=SUM(Table1[Amount])

• 条件格式与图标集 ：快速把 KPI 可视化（红/黄/绿、上下箭头）。

• 锁定窗格（冻结窗格） ：查看大表时固定表头。

• 保护工作表/工作簿 ：审阅 → 保护工作表，可设置密码防止误改（注意密码管理）。

• 版本兼容 ：用 .xlsx 保存，若包含宏用 .xlsm；使用新函数（XLOOKUP、FILTER）前确认同事/客户是否有支持的 Excel 版本。

• 备注与批注 ：用于团队协作，使用“新评论”进行线程式讨论（365 更好）。

• 快速透视表分组 ：右键 → 分组（日期按月/季度/年，数值按区间）。

10 常见问题与排错

• 公式返回 #N/A ：查找值不存在或匹配方式错误（VLOOKUP 第四参数应为 FALSE 精确匹配）。

• #REF! ：引用被删除（例如删除了列）。

• #VALUE! ：类型不对（把文本当数字）。

• 计算慢/不变更 ：检查是否设置为手动计算（公式 → 计算选项 → 自动）。

• 图表数据更新不及时 ：确认图表数据源是否为“表”而不是静态范围，或刷新数据透视。

11 进阶主题（推荐掌握）

• 高级函数 ：LET（定义中间变量提高可读性和效率，365）、LAMBDA（定义自用函数，365）、SEQUENCE、XMATCH。

• Power Query 深入 ：M 语言基础、合并多文件、参数化查询。

• Power Pivot 与数据模型 ：建立关系、使用 DAX（度量、计算列）进行复杂度量计算（大数据级别分析）。

• 自动化与 API ：Power Automate、Office 脚本、VBA 与外部数据源集成。

• 与数据库/BI 工具集成 ：SQL、Power BI，转移到专业数据仓库与可视化工具。

12 学习路径与练习建议（按月进度）

1. 第1周：熟悉界面、快捷键、基础公式、表格与格式化；每天做 30 分钟练习（例如记账表）。

2. 第2周：数据清洗（分列、查找替换、条件格式）、透视表与基础图表。

3. 第3周：查找引用（VLOOKUP → INDEX+MATCH → XLOOKUP），多条件汇总（SUMIFS/COUNTIFS）。

4. 第4周：Power Query 入门（导入 & 清洗真实 CSV）、录制简单宏。

5. 第2个月：掌握动态数组、LET、Power Pivot、DAX（若用 Office 365 企业版），练习真实项目（销售报表、财务月报）。

6. 持续：每天看 1 篇实战教程或做 1 个小项目（例如自动化报表），并在工作中替换重复手动流程为公式/查询/宏。

13 常见场景范例（快速上手）

1. 按月份汇总销售额（透视表） ：数据建表 → 插入透视表 → 行放“月份”字段（或将日期分组为月） → 值放“销售额”求和。

2. 用 XLOOKUP 合并两张表 ：=XLOOKUP(A2, Customers[ID], Customers[Name], "无")

3. 按多个条件计数 ：=COUNTIFS(DateRange,">="&StartDate,DateRange,"<="&EndDate,RegionRange,Region)

4. 自动化月报（Power Query） ：把月度 CSV 批量追加 → 清洗（去空、类型转换）→ 加列计算 → 加载到数据模型/表。

14 安全与协作

• 避免在公开共享时保留敏感数据（脱敏/移除）。

• 版本控制：使用 OneDrive/SharePoint 的版本历史功能。

• 共同编辑（实时协作）需使用云文档（OneDrive/SharePoint）；多人同时编辑注意表格与宏的冲突（宏文件不可直接多人同时编辑）

爱学习的Java小男孩 ](https://blog.csdn.net/2401_85900754)

• 8

上一篇：