Excel常用操作

一、基础操作

1、Excel单元格格式

  • 常规：不设定任何格式，由Excel自动推断，默认为此格式。

  • 数值、货币、会计、日期、时间、百分比、分数、科学计数、文本、特殊：字面意思，当单元格设置为此类格式时，Excel尝试自动类型转换，当转换失败时，尝试自动推断。

  • 自定义：可自由组合任何格式，包含上述所有格式类型。下面介绍常用的几种：

Excel自定义格式构成：正数格式;负数格式;0值格式;文本格式 ，其中后3段可省略，当只有1段时正数格式应用所有数字，2段时正数格式应用正数和0，3段和4段各自按规定应用。

  • #：数字占位符，一个#代表一位数字，小数点右边多少个#，显示多少位小数，末尾四舍五入，不会显示无意义的0。小数点左边一个或多个#效果一样。

  • 0：数字占位符，一个0代表一位数字，与#区别是会保留无意义的0（包括整数和小数部分），一般用于定长展示数字。格式中可按需拼接文本，如00.00"万元"

  • ?: 数字占位符，一个?代表一个数字，如果小数位数不足，用空格补全，一般用于小数点对齐。也可用?/?表示一个分数，例如0.5转换为1/2。

image-YEIo.png

  • .：点表示小数点；","：逗号表示千分位。"%"显示百分比。

  • 文本占位符：@，引用原始文本；*，重复其后的字符直到填充整个单元格。

  • []：条件占位符，可写条件，也可指定颜色。条件格式化只限于使用三个条件，其中两个条件是明确的，另一个是“所有的其他”。条件要放到方括号中。

2、粘贴与类型转换

  1. 粘贴纯文本（不带格式， 保留公式结果）：右键 -> 选择性粘贴 -> 值

  2. 从外部复制文本粘贴为字符串，避免自动类型转换：先将Excel目标粘贴区域设置为文本，然后 右键 -> 选择性粘贴 -> 选择性粘贴 -> Unicode文本

  3. 科学计数法转换为文本：选中待转换区域->数据选项卡->分列->下一步->下一步->选择文本类型。

  4. 一列数据有文本类型数字，又有常规数字，统一转为数字：选中待转换区域->数据选项卡->分列->下一步->下一步->完成（默认选中常规类型）。

  5. 批量粘贴：excel单元格可以进行1->n的复制粘贴，复制一个单元格，选中多个空白单元格可以一次批量粘贴。

  1. 转置：通过转置可以快速将行变成列，或列变成行

3、快速填充与快速跳转

  1. 写出几个实例，选中后向下拖动，Excel自动推测填充内容。如果推断不出，则重复选区内容。

  1. 根据已有列长度，快速填充。如上图所示，当自动填充列相邻列有内容时，可双击右下角绿色点，excel会自动填充到邻列第一个空白单元格位置。

  2. 自动填充与单元格引用：当自动填充公式中有单元格引用时，Excel会自动在行列间递增，如果需要固定行列，可以使用单元格锁定。在行业编号前加$即可锁定，快捷键F4。如D5，锁定行后为D$5，锁定行列$D$5。锁定后，自动填充不会递增该行/列。

  3. 快速跳转：点击数据区任意单元格，按住Ctrl + 方向键，可以快速跳转到首行、尾行，首列、尾列。注意快速跳转终止条件是前进方向上第一个空白单元格。配合Shift建，可快速选中整个数据区（注意，数据区有空行、空列可能中断快速跳转）。

  4. 定位：可以在给定选区进行条件定位，如查找指定区域所有空白单元格。

4、分列、去重与CSV导入

  1. CSV导入在选择文件和编码后与分列操作一致。一般情况下CSV使用UTF-8存储，Excel默认使用GBK打开，存在中文乱码问题。需要手动指定编码。

这里分隔符号有两种选择，分隔符号/固定宽度，一般csv使用逗号分隔，当然非标准csv也可自行指定分隔符。固定宽度一般用于日志及其他格式化输出文本，例如linux ls（windows dir）命令输出。

  1. 分列除了文本分隔以外，还能进行数据统一化处理，比如一列数据既有文本格式的数字，又有常规格式数字，可以通过分列实现格式统一。分列本质上是数据的重新导入。

  2. 数据去重：删除重复项选项卡，可以按指定列进行去重，如果选择多列，多列联合去重。注意如果标题不在去重范围，需要将”列表包含标题“勾选上。

5、筛选与排序

  1. 如果标题列连续，可以选中任意单元格快速建立筛选器

否则，需要选中全部标题列建立筛选器

  1. 筛选页面，可以对单列进行排序和筛选。排序规则为Excel默认规则（数字按大小，字符串按字典序）。还可以根据单元格或字体颜色筛选。

  1. 多列排序，类似SQL中order by a,b,c，有两种操作方式。

    • 可以从次要到主要字段，一次单独排序，比如先按成本倒序，在按科目升序。将相同科目排在一起，相同科目成本倒序排列。

    • 也可选中两列进行联合自定义排序。自定义排序中，还可以根据单元格颜色、字体颜色、单元格图标排列。

  2. 自定义排序方案，除了按字典序外，还可以自定义排序方案，点击【顺序】，选择【自定义列表】，可选择已定义的排序方案。

如果要新增排序方案，点击顺序 -> 自定义列表。

例如这里增加按姓名的自定义序列，排序如下

  1. 注意：当选择1列排序时，但数据有多列，Excel会提示扩展选定区域，此时一定要选择【扩展选定区域】，否则仅排列选定列，而其他列不会同步变化，导致数据混乱。

6、行列快捷操作

  1. 快速插入行、列，Excel默认将行插入到所选位置之前。

  1. 快速删除空白行/列：先使用条件定位到空白单元格，然后在选中单元格上右键删除整行，整列。

  1. 快速调整行业宽高。选中要调整的列或行，将鼠标移动到编号栏行/列分隔线处，双击即可自适应调整。

  1. 统一调整行列高/宽。也可以在格式菜单手动指定列宽和行高，用于精确控制。

7、常见字符串操作

  1. 输入数字作为字符串，常规类型单元格输入较长的数字会转换成科学计数法，如身份证号。可以先输入一个英文单引号，再输入数字即可以文本形式呈现。Excel会忽略起始单引号。

  1. 字符串操作及常用函数。

函数

描述

示例

=、EXACT

比较两个单元格值是否一致

A1=A2, 返回TRUE/FALSE，注意等号不区分大小写。

EXACT("AB", "AB") 返回TRUE

&、CONCAT

字符串拼接

"A"&"B", 返回AB，在字符串常量中"""转移为"

CONCAT("A", "B", "C") 返回"ABC"，参数个数不限，Office2016无效。

LEN

字符串长度

LEN("Hello"),返回5

FIND

字符换查找

FIND("World", "Hello World"),返回7，第7个字符开始为目标字符串，查找失败返回错误，可以用ISERR捕获

LEFT、RIGHT、MID

字符串截取，分别从左边、中间、右边截取

LEFT("HELLO WORLD", 5) 返回"HELLO", 从第一个开始长度为5子串

MID("HELLO WORLD", 7, 5) 返回 "WORLD"，从第7个开始长度为5子串

RIGHT("HELLO WORLD", 5) 返回 "WORLD"，从右往左数5个字符

substitute

REPLACE

字符串替换

SUBSTITUTE("Hello World", "Hello", "你好") 返回"你好 World"

REPLACE("Hello World", 7, 5, "TOM") 将第7个字符开始的5个字符替换为TOM

LOWER、UPPER

转大写、小写

UPPER("Abc")返回 "ABC"

LOWER("Abc") 返回"abc"

REPT

字符串重复指定次数

REPT("AB", 3) 返回"ABABAB"

TRIM

去掉字符串收尾空格

TRIM(" AB CD ")返回"AB CD"

二、Excel样式

1、简单对齐

以下分别为垂直对齐（靠上、垂直居中、靠下），水平对齐（靠左、居中、靠右），缩进（向左缩进、向右缩进，注意，缩进只是样式改变，不影响原始值）。在单元格格式设置中，对齐选项卡还有更多样式。

2、条件格式

  1. 基本条件格式，将成绩小于60的分数标记为红色

  1. 将第一名标记为绿色

  1. 将进展设置为条形图

也可设置为色阶（渐变色）、添加图标

  1. 管理条件格式

或者从菜单进入：格式 -> 条件格式 ，选择当前工作表，条件格式可以添加多个，按顺序依次施加效果，如果有冲突则以靠前的为准

该面板为条件格式通用编辑器，可以添加、删除、调整生效顺序。每一个条件格式需要指定规则、生效区域。添加一条规则：

这里有多种样式可选择，如经典的背景和字体颜色，还有进度条等其他类型。

当对单元格施加条件格式后，直接修改单元格样式则只生效与条件格式不冲突的部分。如下图，对成绩修改字体颜色将不生效，但修改字体大小可以生效。

条件格式中数据发生改变后将自动进行样式变换，如将张三的数学成本改成30，则变为红色背景

  1. 自定义判断条件，实现斑马纹表格

这里我们先定义一个参照列N1、N2交替出现，然后自定义条件$U6="N1"设置背景为浅蓝色。Excel处理时，会自动对引用单元格进行行列增加（与自动填充类似），我们的参照列为U列，所以锁定U列，行号自动增加。

  1. 如果为全局表格设施类似斑马纹，也可以使用Row()函数获取行号, Column()获取列号。如将表单全局按行设置斑马背景色，按列交替设置单元格格式，使其将0展示为短横线。

列上单元格格式如下

3、分组与冻结

  1. 利用excel分组展示层级关系，可以结构化管理梳妆结构的数据，例如对蔬菜、水果进行分级展示：

选中需要收起来的行/列，点击数据-> 组合 -> 组合...，注意如果组合后折叠方向相反，比如汇总项在第一行，组合后折叠到第5行，则需要修改折叠方向。数据->分组和分组展示 -> 设置

设置汇总项的位置，取消【明细数据的下方】则汇总项在明细数据的上方，分组向上折叠，左右类似。

  1. 清除分组：选定组合区域，点击 数据->分组和分组展示 -> 取消组合；也可以点击 数据->分组和分组展示 -> 清除分级显示，取消整个表格的组合。

  2. 冻结窗格，如我们想固定前N行N列，可以选中N+1行N+1列处单元格，点击冻结窗格即可。

如图冻结12行之前和E列之前的部分。此时滑动表格，冻结部分不变。如果想冻结首行、首列可将单元格停留在B2处；冻结首行，单元格停留在A2处。

  1. 取消冻结：窗口 -> 取消冻结窗口

三、数据分析

1、数据查找

  1. VLOOKUP：数据匹配神器。参数如下 VLOOKUP(要匹配的单元格，查找区域，返回列在查找区域的相对位置，是否精确查找）

如图所示，根据D列学号在AB列区域查找成绩；其中参数如下：

  • 参数1：D2，标识需要匹配D2的值，向下快速填充的时候会自动变为D3、D4...

  • 参数2：$A$1:$B$5，表示在A1到B5的区域内查找，注意，对于往下的每一行，该查找区域不变，因此需要锁定单元格，防止自动填充变换查找区域，也可以直接写成A:B，就不用担心自动填充带来的问题。

  • 参数3：在查找区域返回的列，此处返回第2列，成绩。

  • 参数4:0表示精确匹配。

  1. VLOOKUP限制

  • 一次只能匹配一个值，无法进行多列同时匹配。例如需要同时联合学号与科目进行匹配，可使用CONCAT或&将匹配列合并为一列处理。

  • 查找区域匹配的列只能在左侧第一列，如上图，查找区域为C11:E15，但VLOOKUP只查找C列，如果要匹配的值在D列则无效。

  • 返回的列必须在查找列的右边，如上图，查找区域如果要返回成绩列，则成绩列必须在查找列key右边，否则无法返回有效值。解决方案：

    • Index + Match函数：Index用于返回所选区域相对位置的单元格，Index(指定区域，行相对位置，列相对位置); Match用于匹配指定区域（某列）指定值，反回相对行号，Match(查找值，查找区域，查找模式），注意查找区域只能是一列

INDEX($C$20:$D$24,MATCH(J21,$D$20:$D$24,0),1)，该函数内部Match表示在D20:D24区域查找J21的值，0表示精确查找，Match返回相对行号4。外层INDEX表示在C20:D24区域找相对行为4，相对列为1处单元格的值，即INDEX(C20:D24, 4, 1)。

  • 第二种方式就是将查找列和返回列对调，这里使用了IF的数组公式，简单理解数组公式就是多个输入，多个输出。比如IF({0, 1},"A", "B")，结果返回"B","A"，{0, 1}是一个数组，IF第一个参数分别填充0（False）、1（True），0的时候返回B，1的时候返回A。有了数组公式，就可以将查找列在内存中调换，然后VLOOKUP查找调换后的区域。

VLOOKUP(J29,IF({0,1},$C$28:$C$32, $D$28:$D$32),2,0)，这里使用IF将C列和D列进行调换，然后正常使用VLOOKUP返回第二列。

  • VLOOKUP不区分大小写，解决思路使用其他函数判断字符串，代理VLOOKUP判断。示例：

VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(E38,$B$38:$B$41), $C$38:$C$41),2, 0) 首先EXACT精确判断两个文本是否一致，Exact(E38, B38:B41)即用E38的值与B38:B41这个区域每个值比较，返回一列TRUE/FALSE，如图中绿色部分。CHOOSE(n, v1, v2,...vn)返回从第二个参数开始的第n项。配合数组公式，CHOOSE({1,2}, EXACT(E38,$B$38:$B$41), $C$38:$C$41) 就表示返回Exact的列和C38:C41(也就是匹配目标值)，相当于构建了一个临时表，如图中黄色部分。最后VLOOKUP对黄色表进行检索。

  1. XLOOKUP：仅Office 2021或Office 365可用。XLOOKUP是VLOOKUP的增强版，其参数如下：

v2-8a8ea3ddabbd28667a3bde973cf9c88c_r.jpg

  • XLOOKUP常规使用：

XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5)，从A2:A5区域匹配D2，返回B2:B5区域。后三个参数取默认值，无容错值、精确匹配、从第一项开始搜索。可以看到XLOOKUP将匹配区域和返回区域分开，解决了VLOOKUP返回值必须在查找区域右边的限制，同时手动选择返回区域更加简洁直观。

  • XLOOKUP支持多列联合匹配，多列返回

XLOOKUP(F9&H9,$A$9:$A$12&$B$9:$B$12,$C$9:$D$12,{"无成绩","无评价"},0,1)，该示例中查询值为F9与H9的拼接，同理查询区域也使用&拼接A9:A12&B9:B12，返回C、D两列。第四个参数定义了缺省值，为一个数组，因为要返回两列的值。

  • XLOOKUP通配符匹配

XLOOKUP("*"&C16&"*",A16:A17,A16:A17,,2,1)，首先通配符匹配需要第5个参数为2（通配符模式）。"*"&C16&"*"表示匹配串为*华为*，匹配区域A16:A17, 返回A16:A17，无缺省值。

3、数据透视

  1. 创建透视表：将原始数据选中，插入->透视表->选择透视表位置（此处为便于演示，选在同一个页面，默认新建一个sheet）

创建完透视表后，按需将字段拖动到行、列、数据区、筛选器即可

双击字段可以对数据进行明细下钻，如查看2025-08 CDN产品的销售明细

  1. 透视表样式：在设计栏目，可以对透视表样式调整，如设置表格颜色，标题，分类汇总，层级展示样等等。比如去掉分类汇总。

将多个行上的字段平铺展示，并重复所有标签。

  1. 透视表控制栏

如图，数据区域，在源数据更改后，或调整数据源范围后，可以点刷新更新透视表。

筛选区域可以插入切片器，对透视表筛选，比如查看202508月阿里CDN的销售额。

可对透视数据创建图表进行分析。图表右键菜单可修改图表样式。

4、常用数据分析函数

  1. COUNTIF、SUMIF：按条件计数、求和。如SUMIF(C:C,"ECS",D:D)，筛选C列为ECS的行，并对D列求和。

  2. SUBTOTAL：SUBTOTAL(功能代码，数据区)，如SUBTOTAL(9, A1:A5)与SUM(A1:A5)一致。它最大的作用是通过制定第一个参数（功能代码），可以不统计隐藏行。比如SUBTOTAL(109,A1:A5) ,就可以忽略A1:A5中隐藏行的值进行SUM。

  3. PRODUCT、SUMPRODUCT：Product将参数列表中所有值相乘，Product(A1:A3)即A1*A2*A3。SUMPRODUCT(array1, array2...)，将多个数字对应位置相乘并相加。如

即1*2 + 2*3 + 3*5 = 23 ,SUMPRODUCT默认是求对应位置乘集，也可做对应位置+-*/运算，只需要将参数分隔符逗号替换为运算符号。例如

SUMPRODUCT(K32:K34+L32:L34/M32:M34)，即1+2/9+2+3/5+3+5/4 ，参数中运算符优先级与普通算术一样。

  1. GROUPBY：Office2016无效。与SQL中GROUPBY概念一致，GROUPBY有8个参数，分别是维度列、聚合列、聚合函数、有无标题及是否展示、是否展示总计、排序字段、过滤设置、层次机构配置只有前3个字段是必须的。

GROUPBY(C1:C7,D1:D7,SUM,3,1)，即按C列分组，使用SUM聚合D列，数据区包含标题并展示，显示小计。如果开启自动计算，修改数据区，GROUPBY结果会实时更新。

  1. RAND：产生一个0-1之间的随机数，如果要产生a与b之间的随机数，可使用rand()*(b-a) + a