Excel学习知识点整理（2020/3/9新添加未修改版）
2019-01-26 14:53:35 297 举报AI智能生成
excel作为游戏策划手上的刀 怎么能不打磨
作者其他创作
大纲/内容
函数
判断规则
判断数量函数
countif函数
选定的cells判断在range中出现次数
条件满足个数
记录range中的可数数量
返回最大值
max
查询函数
vlookup
函数=VLOOKUP(F5,B:C,2,0)
匹配函数
match
判断
if
判断逻辑形式IF({1,0}
列数函数
column函数
=column（reference)
=column（cells)
返回cells中的第一列的列号
返回cells中的第一列的列号
区域准确位置-index
=index(array,row_num,column_num)
【区域/行数/列数】
返回该位置的值
重要-感觉挺好玩的
将几个单元格内容以字符串形式串联在一起
phonetic
搜索函数
find/search
SEARCH函数支持通配符，而FIND函数不支持。
find函数区分大小写，而search函数则不区分。
调用系统
名函数-ren
="ren """&单元格&""" "&单元格
将内容输入txt中后修改为bat类型，一键更换对应的名字
超链接
hyperlink
数值
分布
二项分布
binomdist
泊松
poisson
正太pass
自然常数
exp
in
基本运算
阶乘
fact
求和
sum
条件求和
sumif
乘积
product
均值
average
平方
power
可用于逆运算
排列组合
combin
统计
标准差
stdevp
相关系数1
pearson
相关系数2
correl
随机
随机区间自然数
randbetween
随机0~1小数
rand
数字规则
取整
整数
int
四舍五入
roundup
字符串
单元格内字符处理
从左往右提取几个字符
left
从数据中间插入几个字符
REPLACE
=REPLACE(F1,4,,"*")
在F1单元格内，第4个位置，将空，替换为*
从数据中间提取几个字符
mid
mid(E2,1,9)
从右往左提取几个字符
right
返回字符串长度
len
去除前后空格
triim
替换指定的字符串
substitute
=SUBSTITUTE(A1," ","",2)
用新文本（空）替换掉第2次出现的旧文本（空格）
净化格式
Clean
连接字符串（2020版函数）
CONCAT
TEXTJOIN
常见问题以及解决方式
提示：无法删除个人信息
选项-设置
信任中心-取消保存删除个人属性
当文件通过函数调用后，出现-文本形式保存数字
问题
再次调用函数可能出错
解决
数据-分列
图片自适应单元格
右键设置-随单元格大小变化
页面布局-对齐网格
按规律拆分单元格
分列
选规格
拆
合并有内容的单元格
方法1
条件
单对字符串做处理
未有跨行/列操作
单对选定范围的的左上角为判定点，做同column相同的连续的row做【合并】处理
方法2
条件
任意
自定义函数
自己码
思路
串列方式，分割符
传输为range对象，自动保存为二维数组
自己码
不复制隐藏的行/列
Alt+；，选择现实的行，之后ctrl+c/v 就能避免复制隐藏掉的行
上下左右变成滚动条
Scroll lock键 在打开状态
导表后
表格内的整数 在导出文件出现小数点.999999999
实际单元格内数值被操作处理了，比如除法被简略近似值，所以只需要用int函数调整下即可
导表出现后置空值小尾巴
Sheet会保存使用过的单元格，所以出现删除数据等操作会出现空值小尾巴，需要将数据进行一次新建Sheet，复制黏贴操作即可恢复正常
替换在筛选后区域的数据
方法1：Vba 匹配不同值数组保存替换
优点：灵活，且条件可以随意修改
缺点：编写万一有点问题较为麻烦，得不偿失
缺点：编写万一有点问题较为麻烦，得不偿失
方法2：筛选后添加两条辅助列（column）
第一条记录所需数据的row 该辅助列全1即可；
第二条记录这个表格所有具有数据的列号，从1~N（数据最尾端）。
第一步：恢复筛选，在辅助列排序
第二步：在具有辅助列的行数黏贴数据
第三步：在具有所有数据排序的辅助列排序
第四步：删除两条辅助列
第一条记录所需数据的row 该辅助列全1即可；
第二条记录这个表格所有具有数据的列号，从1~N（数据最尾端）。
第一步：恢复筛选，在辅助列排序
第二步：在具有辅助列的行数黏贴数据
第三步：在具有所有数据排序的辅助列排序
第四步：删除两条辅助列
优点：影响范围小，可局部分批操作
缺点：万一有多匹配的内容需要替换操作繁杂
缺点：万一有多匹配的内容需要替换操作繁杂
寻找目标单元格有引用过的数据位置
上方tap栏-公式-追踪引用单元格
重要用于：公式太长，不够直观，使用该功能达到可视化的辅助功能
F4无法使用
热键冲突，一般直接查看自己的QQ热键设置
组合函数
数据操控篇
所需提取的数据不需后几位
=LEFT(源数据,LEN(位数)-2)
所提取的数据不需要前几位与后几位
=MID(LEFT(源数据,LEN(源数据)-2),从哪开始提,999)
提取所需查找的字符的后几位
find/search
=MID(源文件,FIND("所需查找的字符串",源文件),99)
提取第二个分隔符的位置
=find(查找值,源数据,find(查找值,源数据)+1)
提取第N个分隔符的位置
=find（查找值，substitute（数据，查找值，替换值，N））
可以通过replace替换位置数据
判断篇
查找判断是否重复
=IF(COUNTIF(A$2:A2,A2)=1,"","重复")
vlookup进行多条件查找
公式：｛=VLOOKUP(E2&F2,IF({1,0},A2:A10&B2:B10,C2:C10),2,0)｝
第一参数：我们将查找值通过使用&字符合并为一项
第二参数：我们利用if函数和数组构建一个二维数组，如下图所示
第三参数：2，在二维数组中得分在第二列，所以为2，
第四参数为0，为精确匹配
公式：｛=VLOOKUP(E2&F2,IF({1,0},A2:A10&B2:B10,C2:C10),2,0)｝
第一参数：我们将查找值通过使用&字符合并为一项
第二参数：我们利用if函数和数组构建一个二维数组，如下图所示
第三参数：2，在二维数组中得分在第二列，所以为2，
第四参数为0，为精确匹配
技巧
Workbook
F4
重复上次操作
切换函数引用方式
通配符
*
任意数据无论长度
?
任意一个字符
#
任意一位数字(0～9)
~
表示下一个符号非通配符
填充
双击填充
下列中所有都按规格填充
当双击填充完毕后数据右下角会出现蓝色框，可选
自定义填充
选项-高级-编辑自定义列表
Sheet
sheet一起选中
ctrl+shift+pg up/dn
也可以用ctrl+鼠标单选操作
切换工作表快捷键
ctrl+pageUP
ctrl+pageDown
定位条件
Ctrl+g
批量删除图片
搜索对象
剪贴板工具
选定黏贴的格式
Range
ctrl+shift+↑↓←→
选中大小+方向的所有区域 到内为空值的单元格停止
可重复操作：遇空停止后 再按一次可越过
shift+↑↓←→ 减少 手动控制
ctrl+方向
跳至方向最顶端
转移数据区域-会有误操作
shift+鼠标拖动状态
有绿色T标识
shift+ctrl
复制+转移
左上角输入精确地址
如：A1:C12
快速求和
Alt+=
在选中范围内进行可编辑单元格切换
正序
Enter
Tab
反序
Shift+Enter/Tab
Cells
引用
F4
绝对引用
1
左右引用
2
上下引用
3
自动合并
F9
快速返回当前时间
Ctrl+;
Ctrl+E
快速分列
Ctrl+D
快速填充（👇）
Ctrl+R
快速填充（👉）
Excel筛选之后粘贴如何跳过隐藏行？
操作类
Tab
在指定区域内切换，切换方式是由列切到行
此处可以补充下Excel的运行机制
Ctrl+PgUp/PgDn
切换不同的Sheet
Ctrl+Z
...撤回上一次操作
Ctrl+moveKey
快速抵达连续数据的末尾
面板键
呼出该单元格的操作框，实用来说不需要手动操作鼠标行为
Ctrl+Shift+"-"
将所选单元格区域内的边框全部恢复初始
CTRL+SHIFT+ENTER
数组运算
数据类
Ctrl+E
自动填充
规则：以上方手动填写的数据，匹配左侧数据发生的改变，找出其逻辑进行填充
修改：将左侧数据内*~??*1中的数字0修改为1
合并：将左侧数据内以目标格式连接
提取：将左侧数据内的某个点提取出来
【拉】
【逻辑】
以在所选区域为准 依照该区域的规则进行填充
方向箭头
拖动操作
移动该区域数据 若结束位置已有数据则做覆盖操作
Shift+拖动操作
移动该区域数据，并顶替掉对应位置
十字箭头
向下填充
试试与Ctrl+D的区别
选中
Ctrl+A
所选单元格附近所有相连区域
Shift+方向
以当前所选单元格区域
Ctrl+Shift+moveKey
快速抵达连续数据的末尾，并且一起选中经过的单元格区域
Ctrl+G
在以以选区域以条件选取所满足条件的单元格
图表设计
哈达表
要点
能用竖着着就别躺着
目的
方便数值分析
方便阅读
方便打印
可视图
对比柱形图
雷达图
对比变成放射线
条形图
名称太长用它
柱形图
数据对比用柱图
三维图容易误读
规折
波动规律用折线
成饼
成分占比用饼图
数据条可视化
条件格式中选择自己想要的
可以在数据中调节需要成为的样子
可以调整数据调的百分比
条件格式
管理规格
编辑规则
选择数字→最小0→最大公式→MAX函数
美化
套用表格模式
突出数据前10
项目选取规则
图标级
管理
编辑
数字
按自己规格来
可用于查找重复数据
突出
重复值
旋风图
编辑规则
条形图方向
两组数据
愉快的一些拓展功能
用Excel以单元格的形式展示图片
ExcelArtFreewire
0 条评论
下一页
