Excel技能树
2022-09-27 14:11:55 0 举报AI智能生成
EXCEL知识大全
作者其他创作
大纲/内容
软件操作
排版基础
单元格格式
数据分列验证
数据有效性
允许”序列“
来源”是,否“
查找与替换
定位
定位到”空值“
输入某值后，【Ctrl+回车】全部填充
行列翻转
复制-选择性粘贴-“转置”
某列乘某值
某列×n — 复制n —选中某列 —选择性粘贴 —选“乘”——完成
建立副本
右键某个小工作簿-“移动或复制工作表”—工作簿“新工作簿”—“建立副本”
文档保护与打印
文档保护
另存为或者保存时有个“加密”提示，输入打开密码和编辑密码
打印
设置打印区域：选中数据—”页面布局“—“打印区域”—“设置打印区域”
打印每页都有标题：“页面布局”—”打印标题或表头“—打印标题“顶端标题行”
快捷键
Ctrl+C 复制
Ctrl+V 粘贴
Ctrl+X 剪切
Ctrl+S 保存
Ctrl+A 全选
Ctrl+Z 撤销
Ctrl+Y 恢复
Ctrl+B 加粗
Ctrl+I 斜体
Ctrl+F 查找
Ctrl+H 替换
Ctrl+G 定位
Ctrl+N 新建工作簿
Ctrl+C 复制
Ctrl+C 复制
条件格式
条件格式样式：突出显示/数据条/色阶/图标集
条件格式应用：数值大小/文本信息/最前最后规则
自定义条件格式
突出显示
【选中要突出显示的某列数据】-突出单元格-其他规则-使用公式确定单元格形式-【$D7＞$Z7】-设置字体颜色和底纹
📢注意：【$D7＞$Z7】绝对引用行列标，具体行列数不绝对引用
项目选取规则
高于或低于n/前后n名
函数进阶
数学函数
逻辑函数
IF/True/False
=IF（你＞50，“达标”，"不达标"）
如果你大于50，就达标，否则不达标
=OR(P18>90,Q18<100)
OR(条件1，条件2，条件3……)
"或"的关系，【满足其一】就可以输出“True”，否则输出“False”
"或"的关系，【满足其一】就可以输出“True”，否则输出“False”
=and(P18>90,Q18<100)
and(条件1，条件2，条件3……)
"且"的关系，【全部满足】就可以输出“True”，否则输出“False”
"且"的关系，【全部满足】就可以输出“True”，否则输出“False”
=IF(AND(SUM(P6:Z6)>10,SUM(P6:Z6)>20),"好",“不好")
如果这些条件全满足，就输出”好“，否则输出 ”不好“
=IF(OR(SUM(P6:Z6)>10,SUM(P6:Z6)>20),"好",“不好")
如果这些条件满足之一，就输出”好“，否则输出 ”不好“
时间与日期函数
=Year(F5)：提取年份数据
2022/1/20=2022
=Month(F5)：提取月份数据
2022/1/20=1
=Day(F5)：提取天数据
2022/1/20=20
=Date(F5,F6,F7)整合日期
2022 1 20=2022/1/20
=TODAY()：今天日期
=datedif(F5,F6,"y")：起始到结束经历年数y/月数m/天数d
2022/01/22 ，2023/01/22=1
查找函数
vlookup/match/index/lookup
vlookup：参考的数据是列向
hlookup：参考的数据是横向
引用函数
offset/Indirect
文本函数
数据公式
Find/Left/Right/text/round/mid/len
整容函数：TEXT（把谁整容，怎么整）
数值位数：TEXT(P5,"0000") ： 55=0055
日期格式：TEXT(P5,"yyyy.mm.dd") ： 2022-5-31 = 2022.05.31
查找字符：Find(要查找的字符串，在哪里查)
=Find(6,F4)： 123684=4 ：代表在数字123684中截止6共有多少字符，共有1236（共4个字符）
四舍五入：Round(要四舍五入的数，小数点后保留几位)
=round(F5,2) ：1.234=1.23，1.235=1.24
取整：Int(数值)
=INT(F5)： F5单元格的数字是5.66→5（取整）
Left(文本，从左取几个)
Left(F5,2)： 567=56
Right(文本，从右取几个)
Right(F5,2)： 567=67
Mid(文本,从第几个截取,取几个)
Mid(F5,3,4)： 56386877=3868
len(文本)有几个字符
len(F5)：220-我=5
lenb(文本)有几个字符
len(F5)：220-我=6
特殊：文字算2个字符
统计函数
SUM/count/sumif/countif/rank
Count：数值个数
Counta：非空值个数(空值就是空格，不包括0)
Countblank：空值个数（0值不算空值）
Countif：在范围内满足条件的个数
COUNTIF（范围，"条件"）=COUNTIF（F5:F6，"＞60"）
Countifs：多条件统计满足条件个数
COUNTIFS(范围1，"条件1"，范围2，"条件2"……)=COUNTIF（F5:F6，"销售部",Q5:Q6，"男"）
Sumif（条件区域，求和条件，求和区域）
=Sumif（地区代码列，F5，出勤小时数）：F5代表010Y北京区，在多个地区中，想查找北京区的出勤时间总数
Sumifs（求和区域，第1个条件区域，条件1，第2个条件区域，条件2……）
=SUMIFS(S18:S21,Q18:Q21,Q18,P18:P21,P18)
SUMPRODUCT 累积求和
SUMPRODUCT(数组或区域1，[数组或区域2]……[数组或区域3])
=SUMPRODUCT(R19:$R$21,$S19:$S$21)
第一列+第二列的和、第1+2+3列的和
Averageif：条件下求均值
=Averageif（条件区域，求均值条件，求均值区域）
Averageifs：条件下求均值
=Averageifs（求均值区域，限定条件区域1（比如销售部），条件1（比如销售1部），第2个条件区域，条件2……）
=Averageifs(S18:S21,Q18:Q21,Q18,P18:P21,P18)
Rank：排名
RANK（要排位的数字，比较的范围，[升序1/降序0]）
图表基础
6种常用图表
图表类型
柱状图/条形图/折线图/饼图/散点图
图表元素
标题/刻度/坐标轴/图例/网格线/自定义元素
图表美化
边框/背景/样式/主题
动态图表
数据透视表
基础
创建
图/表
数据源要求
每列数据的第一行包含【该列的标题】
数据源中不能包含【空行或空列】
数据源中不能包含【空单元格】
Ctrl+G 定位到”空值“
输入某值后，【Ctrl+回车】全部填充
数据源中不能包含【合并单元格】
数据源中不能包含【同类字段】
🌰：销售1部
操作
数据透视表的命名
数据透视表分析-左上角-命名数据透视表
右键数据-数据透视表选项-重命名
数据透视表的移动
数据透视表分析-移动数据透视表
数据透视表的删除
数据透视表分析-清除 或者 直接选中删除
字段
字段：代表列标题，每个字段代表一类数据
项：单个单元格内容
分类：筛选字段、行字段（表左侧）、列字段（表顶部）、值字段
筛选字段水平/垂直排列
数据透视表选项-显示字段-垂直/水平+每行显示几项
展开/折叠
数据透视表分析-最右边+-号
字段标题
数据透视表分析-最右边-字段标题
元素
字段/记录/项
选择某连续数据区域：先选中某首数据，按住shift键，再选中末数据
选择某不连续数据区域：Ctrl键
分析
排序/筛选/切片器
选中整个数据透视表
在某个单元格中鼠标变成黑色小箭头按住可以选中整行整列整个表
数据透视表-分析-选择-选择整个
刷新
手动刷新
数据源区域未更改
只是区域内的金额变动
右键-刷新
数据透视表分析-刷新数据
数据-全部刷新
数据源区域更改
数据透视表分析-更改数据源
自动刷新
打开工作簿自动刷新
右键数据透视表选项-数据-打开文件时自动刷新数据（其他的工作簿的数据也会自动更新，不仅仅是当下的这个表）
使用VBA实现自动刷新
插入-图形-右键制定宏-输入【ThisWorkbook.RefreshAll】-运行（当数据源变动时，点击图形实现更新）
另存为文件-Excel启用宏的工作簿
刷新后保持原列宽
布局和格式-取消勾选”更新时自动调整列宽“
清理垃圾条目
右键数据透视表选项-数据-保留删除的数据源项目-选择”无“
🌰12月份的数据源已删除，在数据透视表中也看不到12月份的数据
但！筛选时候有”12月“的勾选依然存在
🌰12月份的数据源已删除，在数据透视表中也看不到12月份的数据
但！筛选时候有”12月“的勾选依然存在
美化
样式/主题/布局
布局
压缩布局形势
行字段里有两项数据，在表里会在左侧【同一列】显示
大纲布局形式
行字段里有两项数据，在表里会在左侧以【两列】显示
表格布局形式
会对每一个行字段多加一行【汇总】数据
样式
a.设计-表格样式 b.亦可自定义保存
c.页面布局-主题
设置默认样式
设计-选择表格样式-右键-设为默认值
清除样式
设计-选择表格样式-清除样式
空白行
数据透视表-设计-空行-在每个项目后面插入空白行
分类汇总
数据透视表-设计-分类汇总-不显示/在底部显示/在顶部显示
合并字段项
右键数据透视表选项-布局-合并并使带标签的单元格居中
同时显示求和、均值等求值
右键行标签-字段设置-自定义-选中要求的值
行列总计显示
数据透视表-设计-总计-行列显示与否
右键数据表数据-数据透视表选项-汇总与筛选-勾选行列总计
数字格式
右键数据-数字格式
插入对数据的计算行/列
右键数据-数据透视表-分析-“字段、项目”-”计算字段“-命名计算行/列，输入公式
错值/空值
右键数据-数据透视表选项-对于空白值/空值替换为xx
切片器
插入切片器
使用切片器
共享切片器
前提要共享同一数据源
右键切片器-报表链接
更改切片器显示顺序
右键切片器-置于顶层
其他的垒加：点击上面”选项“-”上移一层“/”下移一层“
分组
数值按数据段分组
对行标签进行分组：右键-分组-划数据段 比如10~20、20~30
文本分组
对行标签的文本进行”分组“，单独会多出这部分的数据单成一组 比如：数据组1：aa、bb、cc
日期分组
日期形式但是”常规“格式——数据靠单元格【左边】——没有办法直接分组
日期形式是”日期“格式——数据靠单元格【右边】——可以直接分组
按年月日季度等分组-🌰原：5月13日、5月24日、6月11日→ 5月、6月...
百分比
子主题
动态
数据处理
排序
升序/降序/关键词排序/自定义排序
数据透视表排序
自动
右侧字段列表-选择升降序
数据透视表设计-报表布局-以表格形势-单成一列-筛选
数据选项卡-排序-升降序
右键数据-排序
手动
右键移动
手动拖动
按字母/笔画
右键数据-排序-其他选项
按求和项排序
点击筛选项标题下拉箭头-其他排序选项-按求和数值排序
按值排序（如求和项没有筛选选项）：数据-排序
自定义排序
文件-表格偏好设置-自定义序列
筛选
自动筛选/高级筛选/自定义筛选
保留所选项目
连续选中要保留的项-右键-筛选-仅保留所选项目
定位
Ctrl+G-
合并
合并单元格内容/合并工作表
分列
单元格内容分列
保护工作表
保护/加密/隐藏
数据验证
验证条件
整数/小数/序列/日期/时间/文本长度/自定义
验证提示
提示/警告
自定义数据验证
实战
邮件合并
财务数据
人事数据
销售数据
0 条评论
下一页
