Excel 常用上手操作

基础操作、函数应用、数据分析、报表自动化完整路线

一、Excel 基础认知

Excel 是一个“表格录入工具 + 计算工具 + 数据分析工具 + 自动化平台”。学习时不要只背函数，而要理解数据从录入、清洗、计算、分析到输出报表的完整流程。

1. 文件格式

2. 视图和选择技巧

·冻结窗格：滚动大表时固定标题行或关键列。

·拆分窗口：同时查看同一张表中相距较远的区域。

·分页预览：提前检查打印分页。

·Ctrl+A：先选中当前数据区域，再按一次选中整个工作表。

·Ctrl+方向键：快速跳到连续数据区域边缘。

·Shift 连续选择，Ctrl 多选不连续区域。

二、单元格、格式与表格规范

1. 输入和编辑

2. 复制粘贴

·普通粘贴：内容、公式和格式一起粘贴。

·粘贴为值：只保留公式结果，适合定稿报表。

·粘贴格式：只复制样式，不改变数据。

·选择性粘贴运算：可批量把一列数字乘以 1，把文本数字转成真数字。

3. 表格规范

·第一行必须是字段名，如日期、客户、商品、数量、单价、销售额。

·一列只放一种类型的数据，不要把姓名和电话写在同一列。

·一行是一条记录，中间不要插空行或小计行。

·尽量少用合并单元格，因为会破坏排序、筛选和透视表。

·原始数据、处理过程和结果报表最好分开放在不同工作表。

三、排序、筛选与基础数据工具

练习：准备一张销售明细表，字段包括日期、区域、客户、商品、数量、单价、销售员。尝试按区域筛选、按销售额排序、按日期查看某个月的数据。

四、公式和函数核心基础

·公式都以等号 = 开头。

·公式可以引用单元格，例如 =B2*C2。

·向下复制公式时，相对引用会自动变化。

·公式出错时优先检查括号、逗号、引号、区域范围和数据类型。

示例：A1 放税率 13%，B2 放不含税金额。

含税金额公式：=B2*(1+$A$1)

向下复制时，B2 会变成 B3、B4，但税率始终引用 $A$1。

五、高频函数详解与应用示例

逻辑判断函数

求和统计函数

查找匹配函数

文本处理函数

日期函数

动态数组函数

函数综合案例：销售提成计算

提成等级：

=IFS(C2>=100000,"A",C2>=50000,"B",C2>=20000,"C",TRUE,"D")

提成比例：

=IFS(E2="A",5%,E2="B",3%,E2="C",1.5%,TRUE,0)

提成金额：

=IF(D2>=90%,C2*F2,0)

解释：销售额决定等级，等级决定比例，回款率低于 90% 时不发放提成。

六、数据清洗与数据验证

·条件格式高亮重复值可检查重复订单。

·数据条适合比较金额大小。

·色阶适合成绩、评分、热度。

·图标集适合表达状态，如红黄绿灯。

七、打印、页面与协作

八、数据透视表

数据透视表是 Excel 报表分析的核心工具，可以在不写公式的情况下按部门、区域、产品、月份等维度快速汇总数据。

1.把数据整理成标准明细表，确保第一行是字段名。

2.选中数据区域，点击“插入 → 数据透视表”。

3.把分类字段拖到“行”或“列”，把金额、数量拖到“值”。

4.按需要添加筛选字段、切片器和时间线。

5.数据源变化后右键透视表选择“刷新”。

·源数据不要有空列、空标题或合并单元格。

·新增数据后要刷新透视表。

·建议把源数据转换为超级表，新增行会自动纳入数据源。

·透视表适合汇总分析，不适合直接手工改数据。

九、图表与可视化

·图表标题尽量写结论，例如“华东区销售额连续三月增长”。

·减少不必要的网格线、边框和花哨颜色。

·数据标签只在关键点使用，避免满屏数字。

·同一套报表保持颜色一致。

十、动态数组与结构化表格

Ctrl+T 创建超级表后，Excel 会自动扩展公式、样式和数据范围，非常适合长期维护的报表。

结构化引用示例：

=[@数量]*[@单价]

=SUMIFS(销售表[销售额],销售表[区域],"华东")

自动列出所有区域：

=UNIQUE(销售表[区域])

筛选华东区订单：

=FILTER(销售表,销售表[区域]="华东")

十一、Power Query 自动清洗

Power Query 用于导入、清洗、合并数据。第一次设置好步骤后，以后只需刷新，特别适合每月重复处理同类文件。

·合并多个格式一致的销售文件。

·清理系统导出的 CSV：删除空行、改字段名、设置数据类型。

·把宽表变成长表，例如 1-12 月列转换成“月份、金额”两列。

·从文件夹批量导入数据并一键刷新。

6.把所有月度销售文件放入同一个文件夹。

7.选择“数据 → 获取数据 → 自文件 → 从文件夹”。

8.点击“合并并转换数据”。

9.在 Power Query 编辑器中删除无用列、统一字段名、设置数据类型。

10.点击“关闭并上载”。

11.以后新增月份文件后，只需点击“刷新”。

let

Source = Excel.CurrentWorkbook(){[Name="销售表"]}[Content],

ChangedType = Table.TransformColumnTypes(Source,{{"日期", type date},{"数量", Int64.Type},{"单价", type number}}),

AddedSales = Table.AddColumn(ChangedType, "销售额", each [数量] * [单价], type number),

FilteredRows = Table.SelectRows(AddedSales, each [销售额] > 0)

in

FilteredRows

解释：读取名为“销售表”的表格，统一类型，新增销售额列，并过滤异常记录。

十二、VBA、宏与 Office Scripts 自动化

1. 录制宏

12.打开“开发工具”选项卡。没有的话进入“文件 → 选项 → 自定义功能区”，勾选“开发工具”。

13.点击“录制宏”，输入宏名称，例如 FormatReport。

14.手动完成一次需要重复的操作，例如设置标题、调整列宽、添加筛选。

15.点击“停止录制”。

16.以后运行该宏即可重复同样操作。

2. VBA 示例：一键整理当前工作表格式

Sub FormatCurrentSheet()

With ActiveSheet

.Rows(1).Font.Bold = True

.Rows(1).Interior.Color = RGB(217, 234, 247)

.UsedRange.Columns.AutoFit

.UsedRange.Borders.LineStyle = xlContinuous

If .AutoFilterMode = False Then .UsedRange.AutoFilter

End With

MsgBox "当前工作表格式已整理完成"

End Sub

3. VBA 示例：按部门拆分工作表

Sub SplitByDepartment()

Dim ws As Worksheet, rng As Range, deptCol As Long

Dim dict As Object, cell As Range, dept As Variant

Set ws = ActiveSheet

Set rng = ws.Range("A1").CurrentRegion

deptCol = 3 '假设第 3 列是部门

Set dict = CreateObject("Scripting.Dictionary")

For Each cell In rng.Columns(deptCol).Cells

If cell.Row > 1 And cell.Value <> "" Then dict(cell.Value) = 1

Next cell

For Each dept In dict.Keys

rng.AutoFilter Field:=deptCol, Criteria1:=dept

Worksheets.Add After:=Worksheets(Worksheets.Count)

ActiveSheet.Name = Left(dept, 31)

rng.SpecialCells(xlCellTypeVisible).Copy ActiveSheet.Range("A1")

Next dept

ws.AutoFilterMode = False

MsgBox "已按部门拆分完成"

End Sub

4. VBA 示例：刷新并导出 PDF

Sub RefreshAndExportPDF()

ThisWorkbook.RefreshAll

Application.CalculateUntilAsyncQueriesDone

Sheets("报表").ExportAsFixedFormat Type:=xlTypePDF, _

Filename:=ThisWorkbook.Path & "\月度报表.pdf", _

Quality:=xlQualityStandard, IncludeDocProperties:=True, _

IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "报表已刷新并导出 PDF"

End Sub

5. Office Scripts 示例：网页版 Excel 自动格式化

function main(workbook: ExcelScript.Workbook) {

const sheet = workbook.getActiveWorksheet();

const range = sheet.getUsedRange();

range.getFormat().autofitColumns();

range.getFormat().autofitRows();

const header = range.getRow(0);

header.getFormat().getFont().setBold(true);