20221124-和秋叶一起学秒懂Excel 思维导图模板

七、图表美化

图表操作基础

在Excel插入图表

插入图表：插入-簇状柱形图

替换图表：选择图表-图表设计-更改图表类型

新增数据后更新图表

图表设计-选择数据-选择数据源

柱状图占比效果

插入-插入柱状图或条形图-簇状柱形图

右键-设置数据系列格式-系列重叠

选择柱形-格式-柱状填充/形状轮廓

同时使用折线图和柱状图

插入-插入柱形或条形图-簇状柱形图

选择图表-图表设计-更改图表类型-组合图

图表编辑

数据标签改为显示比率

设置目标线条

同时使用折线图和柱状图，目标线条为折线图

添加数据标签

添加数据比率列、并填充数据

选择图表-右侧加号-数据标签-更多选项：单元格中的值-数据标签区域-选择数据标签区域

更改数据标签的位置

选择数据标签-右键-设置数据标签格式：标签位置-轴内侧

调整坐标轴文字方向

选择坐标轴-右键-设置坐标轴格式：文本选项-文本框-文字方向-横排/堆积

调整坐标轴顺序

选择坐标轴-右键-设置坐标轴格式：坐标轴选项-逆序类别，选中上方横坐标轴删除

分离值坐标

组合柱状和折线-更改图表类型-组合图：折线图（勾选次坐标轴）

八、函数公式计算

统计函数

求和

快速求和

选择数据和目标填入单元格：快捷键Alt+=

定位求和

选择所有数据和空值-Ctrl+G-空值-Alt+=

有数值求和得0问题

文本数值求和得0：数值格式-转换为数字

分组统计

SUMIF(range, criteria, sum_range)

=SUMIF($B$2:$B$20, F2, $D$2:$D$20)

属性条件统计

SUMIF的条件使用数据比较

=SUMIF($B$2:$B$20, ">90", $D$2:$D$20)

多工作表汇总求和

SUM(多工作表单元格)

统计1月到4月的B2单元格总和：=SUM('1月:4月'!B2)

条件统计

COUNTIF(range, criteria)

统计成绩小于60的：=COUNTIF(B2:B20,"<60")

逻辑函数

条件判断IF

IF(logical, value_true, value_false)

=IF(C2<60, "不合格", "合格")

组合条件判断

AND(logical, logical2...)

=IF(AND(B2>1,C2<=10), "不合格", "合格")

文本函数

提取文本的部分内容

提取左侧内容

LEFT(text, [num_chars])

=LEFT(A2,3)

提取右侧内容

RIGHT(text, [num_chars])

=RIGHT(A2,4)

提取中间内容

MID(text, start_num, num_chars)

=MID(A2,4,3)

定位关键字提取

FIND(find_text, within_text, [start_num])

=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)

文本转换格式

日期转换

TEXT(value, format_text)

=TEXT(A2, "0000-00-00")

时间差函数

DATEDIF(start_date, end_date, unit)

=DATEDIF(A2, TODAY(), "Y")

九、日期时间计算

日期格式转换

更改文本为能被识别的日期

选择日期列-Ctrl+H：查找并替换连接符

更改日期显示效果

选择日期单元格-Ctrl+1-数字-自定义：yyyy.mm.dd

日期被识别为小数

选择日期单元格-开始-数字格式：文本

日期时间计算

使用DATEDIF计算工龄、减少小数位（保留1位小数）

使用减法计算时间差、乘法得到时间差小时数：=(C2-B2)*24

使用加法拼接日期和时刻、使用单元格格式转换为日期：=A2+B2

十、数据的查询与核对

数据核对技巧

找出两列数据差异

选择对比数据单元格-Ctrl+G-行内容差异单元格（可选填充颜色高亮所有差异单元格）

找出两个表格数据差异

选择对比数据单元格-开始-条件格式-新建规则

规则类型-使用公式*-为符合公式的值设置格式

公式：=A1<>表格2名称!A1

格式：可选设置填充颜色区别对比

数据核对公式

使用VLOOKUP查询数据

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

在B到E列中查找G2的值、找到后返回第4列列的值、0为精确匹配：=VLOOKUP(G2,B:E,4,0)

快速找出缺失项

使用数据多的一侧使用VLOOKUP查找自己，如果为#N/A则为缺失项

对比顺序不一致的数据项

添加辅助列-填充VLOOKUP查找对应结果-做差对比结果

VLOOKUP失败的原因

#NAME?：函数名称错误：例如使用VLOKUP、VLOOOKUP

#N/A：在查找区域找不到和查询值匹配的数据

#REF!：引用错误：函数中引用的位置呗删除或返回的列值超过查询区域最大列

#VALUE!：值错误：公式中引用了错误参数

十一、条件格式自动标记

条件格式基础

基础：有条件的设置格式

开始-条件格式

突出显示单元格规则

最前/最后规则

图标集

标记前N项

开始-条件格式-最前/最后规则-前10项：输入数字及格式

单元格数据条填充

选择单元格-开始-条件格式-数据条-填充

数据自动标记

数字大于0标记

选择单元格-开始-条件格式-突出显示单元格规则-大于：输入值和格式

日期小于标记

选择单元格-开始-条件格式-突出显示单元格规则-小于：=TODAY()和格式

符合条件整行标记

选择所有单元格-开始-条件格式-新建规则

使用公式确定

根据B2单元格确定公式：=$B2="完成"

样式

一、Excel软件基础

多表格操作必会技巧

大表格浏览技巧

冻结窗格

操作：视图-冻结窗格

功能：冻结行、列，或行和列同时冻结

拆分表格

操作：选择行或列，视图-拆分

功能：拆分成两个区域，拖动分割线调整下方位置

工作表切换技巧

状态栏切换

单击工作表切换按钮

切换按钮组合Ctrl键：快速到第一个和最后一个

工作表切换按钮右键：选择已有的工作表

并列查看

操作：视图-新建窗口；视图-重排窗口

工作表隐藏和显示

隐藏工作表

单个：单个工作表右键-隐藏

批量：Shift单击多个工作表右键-隐藏

取消隐藏

工作表右键-取消隐藏，选择取消隐藏的工作表

图片、形状排版

插入图片技巧

自动对齐到单元格

单个对齐：按住Alt拖动图片或形状，快速对齐到单元格边框

批量对齐：按住Ctrl多选图片或形状，图片格式-对齐

批量选择图片或形状

开始-查找-选择对象，框选需要的图片或形状

选择所有：Ctrl+G，定位条件-对象

图片随单元格移动

选择图片-Ctrl+1-大小与属性-随单元格改变位置和大小

二、快速录入数据

序号录入

单元格序号录入

拖拽填充

选择多个单元格，拖动单元格右下角

Ctrl键填充

选择单元格，按住Ctrl键不放，拖动右下角

序列填充

选择开始单元格，开始-填充-序列，选择列、步长和终止值

合并单元格的序列填充

大小一致的单元格填充

参考单元格序号填入

大小不一致的单元格填充

选择待填充的单元格，在编辑栏使用公式：=COUNTA($A$1:A1)

新增删除行保持序号连续

使用ROW()-1为单元格指定值，选择所有数据，Ctrl+T-创建表，转换为智能表格

快速输入

下拉列表

下拉单元格（可多选），数据-数据验证-设置-允许序列，来源可选手动输入或单元格内数据

二级下拉列表

批量填充

批量填充数值

Ctrl+G定位多个单元格，输入数值-Ctrl+Enter批量填充

批量填充公式

Ctrl+G定位多个单元格，输入=公式，Ctrl+Enter批量填充

三、表格排版技巧

行列排版美化

行高、列宽调整

单行、列调整：拖拽行、列分界线即可调整

自动调整行高、列宽：单击左上角的倒三角全选所有单元格，双击行或列的分割线

横排转竖排

选择转置数据，复制，在粘贴位置右键-粘贴选项-转置

插入行、列技巧

鼠标右键插入

多选行-行号右键-插入

快捷键操作行

插入：多选行-Ctrl+Shift++

删除：多选行-Ctrl+-

鼠标拖拽插入

选择行，按住Shift-拖动右下角增加指定行

批量删除多行

筛选删除

通过数据-筛选过滤指定行，选择目标行-右键-删除行

定位删除

Ctrl+G定位-右键-删除-删除整行

单元格美化

001显示为1

开始-数字格式-文本

相同内容单元格合并

手动合并一次，开始-格式刷，拖拽批量合并

单元格绿色小三角

方法一：单元格-错误检查选项-转换为数字

方法二：复制单元格-选择性粘贴-数值

5位数字显示为万

单元格-右键-设置单元格格式-数字-自定义：0!.0,"万"

科学计数法完整显示

开始-数字格式-文本

四、表格打印技巧

打印页面设置

让表格正好占一页纸

方法1：视图-分页预览-拖拽所有蓝色分页线到右侧删除

方法2：页面布局-宽度、高度：1页

在纸张中心打印

页面布局-页面设置-页边距-居中方式：勾选垂直和水平

页眉页脚设置

每页都显示页眉

通用方式：页面布局-页面设置-页眉/页脚-自定义页眉

快捷方式：窗口右下角页面布局按钮-在页眉区域输入内容

每页添加页码

页面布局-页面设置-页眉/页脚-自定义页脚：&[页码]/&[总页数]

页眉页脚编辑模式：页眉页脚-插入页码+插入页数

每页添加表格标题

页面布局-打印标题-工作表-顶端标题行：选择标题所在行

五、排序与筛选

排序

单列快捷排序

选择排序列单元格-数据-降序/升序

多列组合排序

数据-排序-添加条件

随机排序

添加辅助列-填充=RAND()-数据-升序

提取文本部分排序

添加辅助列-填充【=--LEFT(A2,LEN(A2)-1)】-数据-升序

筛选

多条件筛选

筛选：数据-筛选

同时过滤王和汪：在过滤条件中勾选：【将当前所选内容添加到筛选器】

取消筛选：数据-清除

筛选重复数据

添加辅助列-填充【COUNTIF(A:A,A2)】-筛选出现次数大于1的记录

六、数据透视表

数据透视表基础

使用透视表统计

插入-数据透视表-创建-新工作表：拖拽行和值进行统计

再次打开透视表字段列表

数据透视表分析-字段列表

日期按月统计

日期作为行-在透视表日期右键-组合-选择年、月

快速统计数据

多维度数据统计

插入-数据透视表-创建-新工作表：拖拽多行和多值进行统计

日期多纬度统计

日期作为行-在透视表日期右键-组合-选择日期纬度

布局排版

合并相同单元格

设计-报表布局-以表格形式显示；

单元格-右键-数据透视表选项-布局和格式：合并且居中排列待标签的单元格

美化统计结果

设计-报表布局-以表格形式显示；

技巧1：设计-报表布局-重复所有项目标签

技巧2：设计-分类汇总-不显示分类汇总

技巧3：射击-总计-对行和列禁用

技巧4：设计-数据透视表样式

表格拆分技巧

新建数据透视表，使用数据透视表显示报表筛选页进行拆分