精通EXCEL
2020-03-13 17:52:02 2 举报AI智能生成
excel基础知识整理
作者其他创作
大纲/内容
函数
文本清洗函数
查找
find
截取值
left
right
mid
拼接
concatenate
替换
replace
substitute
去两端空格
trim
计算长度
len
关联匹配函数
vlookup
返回指定范围内查找指定值，的指定列的值
一般用于合并表
vlookup（匹配值，查找范围，返回列的索引，近似/模糊匹配）
相当于根据某一值，连接表
index
返回指定范围，指定行、列的值
match
返回某范围内匹配某值的位置
row
返回所在行
col
offset
偏移
hyperlink
创建一个快捷方式（跳转），用来打开存储在网络服务器、Intranet或internet中的文件。
逻辑运算函数
true
false
if
判断条件为真，返回 ...，否则返回...
and
or
一个参数为真，即为真
not
统计计算函数
subtotal
函数中的瑞士军刀（统计函数序号，参数）
randbtween
生成随机数
rank
排名（参与排名的数值，排名的数值区域，升序/降序）
=RANK(F28,$F$22:$F$32,1)
count
计算非空单元格个数
countif
计算符合给定条件的区域中的非空单元格个数
=COUNTIF(C2:C32,"<2000")
countifs
=COUNTIFS(C2:C32,">1000",C2:C32,"<2000")
countifs（求和区域，条件区域，条件1，条件区域2，条件2）
sum
给定区域求和
sumif
sumifs
=SUMIFS(C2:C32,C2:C32,">1000",C2:C32,"<2000")
sumproduct
返回对应区域 累加求和 值
AVERAGE
averageif
AVERAGEIFS
=AVERAGEIFS(E2:E32,E2:E32,">2000",E2:E32,"<3000")
max
最大
min
最小
ROUND
按指定位数对数字进行四舍五入
ROUND(对谁操作, 哪位操作（>0 小数点右侧，反之）) ，返回是操作之后的值
int
向下取整
MOD
返回两数相除的余数
时间序列函数
year
month
weekday
一周中的第几天
weeknum
本周是一年中的第几周
day
date
参数分别是年 月 日
now
today
days()
时间之差
Datedif(start_date,end_date,unit （Y M 或D）)
计算2个日期之间的天/月/年
=DATEDIF(A2,A32,"D")
基础
快捷方式
ctrl+N
打开空白单元格
录入数据
下一个单元格
tab
enter
填充柄
使用：鼠标放在右下角，成细十字 状，开始拖动
右下角出现选项
复制填充
序列填充
格式刷
单击
刷一次
双击
连续刷
字符串使用双引号
设置单元格格式
常规在开始中，不常规点三角，或右键
get
拖拽设置行高
保护部分单元格和筛选
保护单元格
先设置单元格-保护 解锁，再选中部分 锁定， 再保护工作表
筛选
开始中简单筛选
高级
基础函数
sum
avg
count
max
sumif
sumifs
countif
countifs
rank
排序
逻辑函数
if
and
or
not
示例
判断闰年
=IF(MOD(B2,4)>0,"平年",IF(MOD(B2,100)>0,"闰年",IF(MOD(B2,400)>0,"平年","闰年")))
数据透视表
定义/用途
可以快速汇总、分析大量数据表格的 交互式分析工具
提升工作效率
常见场景
找出同类数据在不同时期的某种特定关系
以友好的方式查看大量数据表格
对数值数据快速分类汇总、按分类查看数据信息
建立交叉表格
快速计算数值数据的汇总信息，差异，个体占总体的百分比等
数据源经常变化
对数据源的要求
每列数据第一行包含列标题
不包含空行、空列、空单元格、合并单元格
去除空值方法
f5定位-定位条件-空值 ，输入0 ，ctrl+enter 全部替换为0
查找替换
不包含同类字段
判断依据：看上去既可作为标题，也可作为数据的内容
例如 ： 姓名 部门 一月 二月
500 1000
500 1000
改为 ：姓名 部门 月份 金额
一月 500
二月 1000
一月 500
二月 1000
创建数据透视表
插入-数据透视表-选中数据源/一般放到新的表中
数据透视表操作：
1. 开始可以全部选中，数据透视表会自动分配
实际 可以根据具体业务 拖拽想要查看的数据
认识数据透视表
基本术语
数据源
创建数据透视表的数据来源，可以是单元区域、定义的名称、另一个数据透视表、外部数据等
字段
数据源中列标题
项
每个字段中包含的数据
四大区域
行区域
列区域
值区域
筛选区域
可以对整个数据透视表进行筛选
基本操作
数据源变更时需刷新，
增加行等时，需变更数据源
设置数据透视表样式
排序，筛选，切片器
切片器
多个数据透视表中共享切片器
满足
数据透视表来源一个数据源
数据透视表共享一个数据透视表缓存
创建
1.选中一个数据透视表−分析−插入切片器
2.创建链接
单机切片器−切片器工具−报表链接
作用
使数据筛选更简单直接
分组
数值数据
选中数值−分析−分组−组字段
文本数据
手动选择数据−右击创建分组
日期数据
与数值数据相同
数据分析
设置数据计算方式
值区域单元格右击-值显示方式/值字段设置
总计的百分比
所有数据总和显示为百分百，其他为占总和的百分比
行、列汇总百分比
行列处为百分百
百分比
选中一个基准为百分百，其他为相对他的百分比
父类汇总百分比（只能在行和列有2个以上时可以使用）
父行汇总百分比
含义
每个值占整个的百分比
父列汇总百分比
父级汇总百分比
区别，父行父列 百分百为整体的整体，（1+2=50% ，+2+3=100%）此处父级为百分百，即1百分比+2百分比=百分百
差异和差异百分比
有一个基准，相对基准的差异
按某一字段汇总（百分比）
加上前边的值
计算字段
定义
虚拟的字段，数据透视表的列，不在数据源中
只能出现在值区域
创建
选择值字段数据-分析-字段、项目和集-计算字段
修改
删除
计算项
临时添加的虚拟数据
计算项不会出现在字段列表窗格，而计算字段会
相当添加了一个数据行，添加方式与计算字段不同
创建
选择行或列字段中的单元格-字段、项目和集-计算项
多出插入项， 应用：可计算具体数据的差异等
注：有分组和自定义汇总时不能使用计算项
数据透视图
认识
与普通图表的区别
数据源类型
数据透视图可使用外部数据
可创建图表的类型
具有交互性
数据透视图是动态图
格式设置丢失问题
刷新后可能丢失标签等丢失问题
与数据透视表关系
限制
无法创建气泡图 、散点图、骨架图等
无法切换数据源的位置
无法调整数据标签大小
若添加了趋势线，数据透视表中变更数据，趋势线会消失
创建
分析-数据透视图
0 条评论
下一页
