Excel操作技巧

# 01 Excel 入门

跨列居中：对齐方式 - 水平对齐 - 选定 “跨列居中”（Office365 版本）

# 自动调整栏宽

• 单栏调整：双击间隔线。
• 多栏同宽调整：选定栏数，拖动任一栏宽度调整。（调整列亦同）。

# 日期输入

• 输入使用斜线格式，方便 excel 函数等功能计算使用
• 使用 “右键单元格格式 - 数字 - 日期” 更换日期格式显示
• 更换后日期栏显示 “#######”，表示日期文字过长，调整栏宽即可
• “Ctrl+；” 输入今日日期，向下拖动自动填充往后日期，拖动后方框右下角图标可设置填充格式，向下输入选项，有重复内容，右键 - 从下拉列表中选择，可从已输入选项中选择输入

# 计算总金额

1. 在结果栏输入 “=”，无需输入数值坐标，直接选定条件数值，输入运算符号，回车自动显示结果

2. 复制计算结果，粘贴到需计算的第二栏，直接得出第二栏计算结果

3. 选择结果，向下拖动，自动计算

4. 添加货币格式

   a 选定数值，在数字功能区选择货币格式，功能区内可选择增减小数点。

   b 添加后，可复制该区域，使用格式刷将货币格式粘贴到另一区域

# 02 表格设计

# 表格框线设计

1. 打印前预览，默认网格线不被打印，需自行添加设计表格框线（预览符号：快速访问工具栏，没有可在工具栏小箭头内添加）

2. 添加表格框线

   a 页面布局 -> 工作表选项 -> 网格线 -> 打印（样式呆板）;

   b 手动设计：开始 -> 字体 -> 框线（选择颜色和样式）

3. 画框方式

   a 画笔（两种，绘制外框，绘制内框，按 Shift 切换橡皮功能，按 esc 退出）;

   b 选取范围，套用类型

# 背景设计

• excel 背景（背景无法打印，仅设计表格时美观用）：页面布局 -> 页面设置 -> 背景
• 设计表格背景：开始 -> 字体 -> 油漆 -> 选择颜色
• 隐藏网格线：页面布局 -> 工作表选项 -> 网格线 -> 查看（取消勾选）

# 03 冻结窗格与分割视图

冻结窗格：选定需冻结窗格的下一栏 / 列 -> 视图 -> 窗口 -> 冻结窗格 -> 冻结窗格

拆分视窗：选定需拆分的大小 -> 选定那一栏 -> 视图 -> 窗口 -> 拆分

# 04 资料排序

单栏排序：选取排序栏中任一存储格（划重点）–排序与筛选–升序或降序（中文排序依照笔画多少）

多栏排序：选取表格内任一存储格 -> 排序与筛选 -> 自订排序 -> 先指定第一层排序 -> 新增层级 -> 指定第二层排序

自订清单（依照用户自己设定的顺序来排列资料）：排序与筛选 -> 自定排序 -> 选择要排列的栏位 -> 顺序选定为自订清单 -> 清单项目 -> 输入自己想要的顺序。PS：自订清单可以按照自己设定的顺序自动完成内容填充

删除清单：删除清单 -> 选择建立的清单 -> 删除

# 05 资料筛选

筛选：点选表格内任一存储格–排序与筛选–筛选–类别栏–按条件筛选。PS：可同时在多个栏位中进行筛选，筛选时可按正序／倒序排序

模糊比对功能：类别栏–文字筛选–开始于–“李”。PS：以 “李” 姓业务员为例

色彩筛选 + 排序：上色（按住 control 不放并选取）-> 依色彩排序 -> 自订排序 -> 选择要排序的栏位 ->“值” 为储存格色彩

# 移除筛选

1. 类别栏–清除筛选
2. 还原表格–排序与筛选–清除／直接点选筛选

# 06 格式化为表格 & 交叉分析筛选器

表格生成方式（自动侦测表格大小）：页面布局 -> 色彩 -> 可更换多种配色

取消表格：表格样式 -> 套用无 -> 清除表格样式

# 合计列（汇总行）

• 最后一栏会自动进行加总，也可以切换其他计算公式（存储格右侧三角形）
• 可以将此栏位公式向左套用（拖动存储格右下角黑十字）

# 添加行或列

• 添加行：选取汇总行上一行最后一列，按 tab 键
• 添加列：输入新一列列名，在此格内按下 enter 键
• 套用第一行公式直接拉右下角十字即可

# 交叉分析筛选器（切片器）

• 表格 -> 插入交叉分析筛选器（切片器）-> 勾选想筛选的栏位 -> 生成面板
• 面板可调整大小、栏位，按 delete 可删除，右上角可清除筛选条件
• 同时显示多个项目：按住 control 不放选择
• 多重筛选：选取其他面板内的选项

# 07 设置格式化的条件

更改／删除条件：选取要更改的列 -> 条件格式 -> 管理规则 -> 编辑规则 -> 可重新指定条件

数据条／色阶／图标集 -- 显示更有趣

清除规则：条件格式 -> 清除规则 -> 清除整个工作表的规则

# 设定格式化

• 选取想筛选的栏位所在列–功能区右上角 “条件格式”–自行设定条件和突出显示样式
• 栏位下方单元格新增突出显示的内容会自动填充显示样式

# 08 工作表设定 & 合并汇算

工作表复制（不同 excel 文件之间）：打开两个 excel 文件 -> 点选工作表右键 -> 移动／复制 -> 选取移动路径
-> 表格位置 -> 建立副本

工作组：按住 control 不放生成工作组

# 合并计算

• 在需要计算的表格之外建立新的空白工作表 -> 功能区 -> 数据 -> 合并计算
• 选取表格范围 -> 如顶端列、最左栏无数字 -> 勾选则不计算 ->“+” 增加范围
• 挨个添加工作表需要计算的范围 -> 勾选 “创建指向原数据的链接”-> 可查看数据来源

# 09 图表制作

表格制作步骤：框选整个表格（必须包含行、列标题）-> 插入 -> 选择表格样式

图表移动：图表设计栏右上角 -> 移动图表 -> 可选择移动到另一工作表／将图表生成全新工作表

图表內添加文字或项目：图表设计 -> 添加图表元素 -> 选取要添加的内容

表格变化与图表变化同步：新增表格内容后 -> 点选图表 -> 出现框选部分 -> 将新增内容框选进范围内 -> 表格变化

直方图点击直方就可以进行选择填充的图像，比如说可以添加我们增加想要的图像。

点击图像可以调整选择的数据范围。

圆形图可以双击图形，并将想要突出的部分分离出来。

点击图形后可以通过设计按钮对图形进行自定义操作。比如切换行列。

框选空白位置，点击插入按钮选择迷你图，在选择数据范围即可画出走势图。

散点图可以点击 + 按钮选择趋势线对图形进行简单分析。

组合图可以用于实现不同数量级的数据的展示。

# 10 枢纽分析表

建立：点选表格内任一存储格 -> 插入 -> 数据透视表 -> 可选范围和位置。PS：勾选想要显示的栏位标题 -> 拖拽标题至不同位置会在数据透视表发生相应变化。

排序：点选数据透视表內任意一格 -> 右键 -> 排序

查看数据来源：点选数据透视表内某格 -> 左键 ×2

设置值的显示方式：选择要设定的选项 -> 数据透视表字段 -> 汇总方式、数据显示方式

# 筛选

• 现有列筛选：右侧三角（清除 -> 选单内下拉）
• 筛选器筛选：勾选筛选项拖拽至筛选器内

# 资料分组

• 按住 cotrol 不放点选对象 -> 右键 -> 组成群组
• 取消 -> 选择群组 -> 右键 -> 取消

# 11 列印分页设定

通过分页线调整打印页面范围

页面布局可在打印时出现网格线和标题

设定页面宽度和高度可以自动缩放

只需打印几行资料 -> 框选内容 -> 设定打印范围

# 12 页首，页尾设计 & 浮水印制作

点击视图 -> 页面布局，就可以看到出现了页眉和页脚等信息，点击所选位置即可进行操作。

点击页眉和页脚处还可以选择想要的格式。

浮水印的利用图片即可，在图片处按 enter 键可以将图片往下移动。

点击设置图片格式可以对图片进行更深一步的处理。

# 13 逻辑函数 IF

普通 IF 函数结构 (标点符号为英文)：缺点：只能判断两个条件 = IF（条件，“条件成立”，“条件不成立”）

巢状 IF 函数 (EXCEL2016 版本后 IFS 函数)：缺点：如需叠加层级过多，则公式过于复杂 = IF（条件，成立，IF（条件，成立，不成立））

# 14 VLOOKUP 函数 & F4 绝对参照

# VLOOKUP 函数

• 公式 = VLOOKUP (关键字，资料范围，列数，查询模式)
• 列数是指需要传回的是左起第几列的资料
• 参照表格需采用 “递增方式” 排列（最后一排数字最大）
• 查询模式：模糊比对：TRUE；精确比对：FALSE

# 绝对引用

• 通常情况下，VLOOKUP 函数无法自动填充，需将 “资料范围” 设定为固定区域；
  否则 “资料范围” 会因为 “相对引用” 规则，随 “关键字” 一行下移；
• 方法：选定公式中的资料范围 -> 笔记本（FN+F4）/ 电脑 F4-> 出现货币符号

# 15 IFERROR 函数 & 资料验证

公式 = IFERROR（要检查的公式，公式错误的讯息）

验证：选择存储格 -> 数据 -> 验证 -> 设置待验证内容和报错信息

# 16 COUNTIFS&SUMIFS 函数应用

SUMIFS 函数公式 = SUMIFS（加总范围，范围 1，条件 1，范围 2，条件 2，…）

# COUNTIF 函数

• 公式 = COUNTIF（资料范围，条件）
• 注意：文本资料一定要加双引号
• 小技巧： 条件可以指定为某空白存储格，便于随时修改

# COUNTIFS 函数

• 公式 = COUNTIFS (范围 1，条件 1，范围 2，条件 2，…)
• 涉及金额的筛选 = COUNTIFS (范围 1，条件 1，范围 2，“>”& 存储格)
• 存储格内金额可随时修改，符号根据需求选择
• 涉及日期的筛选：如需筛选特定两个日期间的资料 = COUNTIFS (范围 1，条件 1，范围 2，“>2018/5/15”，范围 2，“<2018/6/3”)
• 如需筛选特定月份资料：
  1. 利用 MONTH 函数建立辅助列，归纳出每笔资料所处的月份；
  2. 建立 COUNTIF 函数，筛选范围设定为新建的月份列

# 17 定义名称 & INDIRECT 函数 & 下拉选单设定

定义名称：框选资料范围（不含标题）-> 公式 -> 定义名称

INDIRECT 函数：以线索中的文字为媒介，取得目标存储格的内容。线索可以来自于两个不同的存储格，以 “&” 连接

# 18 让你事半功倍的 12 个小技巧

资料剖析：框选需要剖析的单元格 -> 数据 -> 分列 -> 分隔符号 -> 勾选分隔符号
或分列 -> 固定宽度 -> 在数据预览处手动拖动分栏线。

插入多个空白列：选取想要插入的列数 -> 鼠标右键 -> 插入。F4：重复前面的操作

移除重复资料：框选单元格 -> 数据 -> 删除重复值 -> 勾选

表格转置：框选单元 -> 复制 -> 选择性粘贴 -> 勾选转置

贴上运算值：输入操作值 -> 框选单元格 -> 选择性粘贴 -> 运算处点选操作

显示公式：公式 -> 显示公式

目标搜寻：数据 -> 模拟分析 -> 单变量求解 -> 填入对应值。PS：目标单元格内一定要有公式

表格对角线：开始 -> 边框 -> 绘制边框 -> 手动绘制所需边框或右键 -> 设置单元格格式 -> 边框 -> 选择所需边框

储存格内换行：ALT+enter

插入图片注解：选取单元格 -> 右键 -> 插入批注 -> 删除注释框中的名字 -> 在边框处点击右键 -> 设置批注格式 -> 颜色与线条 -> 颜色 -> 填充效果 -> 图片 -> 选择图片。PS：只有在边框处点击右键才会有颜色与线条选项

# 快速选取资料

• Ctrl + 键盘方向键：选取框移动到表格的四个角落
• Ctrl+shift + 键盘方向键：选取对应的范围
• 标题列左上角：选取整张表格

# 储存格选取框

• 选取需要移动的单元格 -> 鼠标移至单元格边框 -> 拖动单元格
• 移动时按住 Ctrl，复制内容
• 移动时按住 shift，插入移动处，而不发生覆盖

# 19 表格数值格式

开始 -> 数字右边的按钮 -> 数字 -> 自定义 -> 类型下面的输入框或快捷键 Ctrl+！

可以添加符号，或者在开头或末尾添加文字。PS：添加文字资讯时记得加上””

示例：0.00;(0.00);(0.00);@代表正值；负值；零值；文字

添加颜色：[所选颜色]，添加到所选区域前即可。只有 8 种颜色或 [颜色 + 色彩编号]，例如 [颜色 33]

若语法结构内某类型没有格式，那么将会隐藏该类型
可以在格式中加入判断语句，例如 [>90]，一个语法最多有两个判断语句若想有多个判断语句：开始 -> 设定格式化条件

点选格式后再点选自定义格式即可看到它的语法结构

# 符号表

符号	意义
#	遇到无意义的 0 将不会显示
?	遇到无意义的 0 将会空一格作为显示
0	强制显示每一个指定的位数
@	预留文字位置
*	重复 * 后面的符号
,	千
,	百万
_	预留一个_后面符号的宽度

# 20 时间格式 & 工龄与工时计算

Datadif (开始日期，结束日期，计算单位）计算单位用”y”,”m”。PS：找不到这个函数，但是可以直接输入使用

Networkdays（开始日期，结束日期，假日）

Networkdays.intl (开始日期，结束日期，自定周末，假日)。自定周末输入对应的数字即可

# 符号表

符号	意义
[DBNUM1]	将阿拉伯数字转换为中文数字
aaaa	以星期的方式显示
Ctrl+；	插入现在的日期
Ctrl+shift+；	插入现在的时间
Today（）	实时日期
Now（）	实时时间，按 F9 或点击单元格时更新
[h]	计算已经过的小时数
[m]	计算已经过的分钟数

# 21 如何计算成绩排名

rank.eq (主体，比较范围，排序方式)

比较范围注意要使用 F4 来作为绝对参照

排序方式默认以降序排序，需要升序排序时在排序方式内填入 1 即可

rank.avg () 与 rank.eq 类似，只是当成绩相同时，rank.avg 采用排名的平均值

# 22 提取表格内信息

命令	作用
Left（资料位置，捉取字数）	捉取数据左侧的数据
Right（资料位置，捉取字数）	捉取数据右侧的数据
mid（资料位置，开始位置，捉取字数）	捉取数据中间的数据
Find（要搜寻的文字，资料来源，搜寻起点）	查找文字所在位置
Len（资料）	计算储存格中的字数和空格

# 23 index+match

hlookup 与 vlookup 类似

Index（栏 / 列范围，顺位）或 index（资料范围，列数，栏数）

Match（查找对象，查找范围，比对方式）

综合运用例子 INDEX (C3:F10,MATCH (I2,D3:D10,0),MATCH (H3,C2:F2,0))

# 24 保护表格

可供修改的单元格：框选单元格 -> 右键 -> 设置单元格格式 -> 保护 -> 取消勾选锁定。

隐藏单元格公式：框选单元格 -> 右键 -> 设置单元格格式 -> 保护 -> 勾选隐藏

隐藏：选定行或列 -> 右键 -> 隐藏

允许编辑范围：审阅 -> 允许编辑范围 -> 新建 -> 设定标题、范围、密码 -> 确认密码 -> 确认

PS：以上操作需取消保护工作表，开启保护工作表后效果才会出现

保护工作表：审阅 -> 保护工作表 -> 输入密码 -> 确定密码

保护工作簿与保护工作表类似

# Excel 表设置密码

• 文件 -> 信息 -> 保护工作簿 -> 用密码进行加密 -> 设定密码 -> 确认密码
• 另存为 -> 工具 -> 常规选项 -> 设定密码

# 25 重复内容

删除重复信息：框选单元格 -> 数据 -> 删除重复值 -> 勾选选项 -> 确定

防止重复内容：选定行或列 -> 数据 -> 数据验证 -> 允许中选定自定义 -> 填入公式 -> 确定。公式例子：=countif (A:A,A1)=1

# 标注重复信息

• 框选单元格 -> 开始 -> 条件格式 -> 突出显示单元格规则 -> 重复值 -> 确定
• 框选单元格 -> 开始 -> 条件格式 -> 新建规则 -> 使用公式确定要设置格式的单元格 -> 输入公式 -> 修改后一参数值为相对参照 -> 格式 -> 填充 -> 选择颜色 -> 确定
• 公式例子：=countif ($E$2:$E$14,$E2)>1

# 26 随机函数的使用

# 常见函数

函数	作用
Randbetween (最小值，最大值)	从最小值和最大值的范围内产生一个整数
Choose (序号，可供选择的选项)	根据序号选择对应选项
Rand()	产生 0 到 1 的随机数
Roundup (数值，位数)	无条件进位函数

# 常见例子

• =INDEX(C3:C14,RANDBETWEEN(1,12))
• =CHOOSE (RANDBETWEEN (1,2),“A 卷”,“B 卷”)
• =CHOOSE(ROUNDUP(RANK(D3,$D$3:$D$14)/4,0),“A 组”,“B 组”,“C 组”)
• =INDEX($C$3:$C$14,RANK(D3,$D$3:$D$14))

# 27 进度追踪表

勾选框块：功能区空白处点击右键 -> 自定义功能区 -> 勾选开发工具 -> 确定 -> 开发工具 -> 插入 -> 选择勾选框块

完成格与状态的信息传输：框块右键 -> 设置控件格式 -> 选择单元格

进度栏函数设置：=IF (I3=TRUE,1,IF ($C$3>=G3,0,“🕒”))。PS：时钟符号可以：1.win10：wins+；2.UNICHAR (128336)

添加刻度：点击饼图 -> 设计 -> 选择数据 -> 添加 -> 系列值输入对应数值的 1（如 ={1,1,1,1,1,1,1,1,1,1,1}）-> 更换图表类型 -> 勾选次坐标轴 -> 选择未完成部分的饼图 -> 填充选择无填充

# 28 甘特图

插入 -> 堆积条形图 -> 设计 -> 选择数据 -> 图例项选择 “开始日期” 和 “天数”-> 水平轴标签框选 “任务名称”-> 点击右侧条形 -> 设置数据系列格式 -> 填充 -> 无填充 -> 点击任务名称 -> 坐标轴设定 -> 勾选逆序刻度值 -> 点击日期标识 -> 坐标轴设定 -> 最小值（设定为对应数值，可通过设定单元格格式的通用格式查看）-> 单位的大选项为间距设定 -> 点击右侧条形 -> 点击 + 号 -> 勾选误差线 -> 更多选项 -> 选择正偏差、无线端 -> 自定义选择对应数列 -> 填充 -> 实线 -> 调整宽度

# 29 sumproduct

Sumproduct（A，B）：将 A，B 分别相乘后相加

=SUMPRODUCT((C3:C9=C11)*(D3:D9=D11)*F3:F9)

=SUMPRODUCT(C3:C8,D3:D8)

规则管理设定规则时需要加上一个$来锁定 =OR(AND($E3>1000,$D3>5),$C3=“有”)