20221124-和秋叶一起学秒懂Excel
2022-11-27 17:06:59 0 举报AI智能生成
和秋叶一起学秒懂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：设计-数据透视表样式
表格拆分技巧
新建数据透视表，使用数据透视表显示报表筛选页进行拆分
0 条评论
下一页
