Excel使用

标签：表格 单元格 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,"

标签：表格,单元格,Excel,右键,使用,格式,筛选,排序
From： https://www.cnblogs.com/xiqin-huang/p/18366927