- 前言
- 第一章 数据安全、协作类
- 第二章 高效处理数据类
- 为占位符、非强制的，比如0.50，用#.##就会显示为.5
- 定义文本显示方式
- 单元格加单位
- 复杂情况
- 总结
- 单元格样式
- 导入外部数据
- 定位功能
- 通配符
- *表示任意个数字符
- ？表示一个字符
- 照相机工具=截图工具
- 宏工具
- 第三章 公式的高级应用
- 公式中的基础运算符
- 数组公式的计算原理
- 利用公式标记数据
- 利用公式制定输入规则/数据有效性
- 小于一定数值时，才可输入。（小于等于才可输入）
- 名称与公式
- 多行、多列填充公式
- 条件求和和条件计数
- 其他条件统计函数
- 统计筛选后的数据
- 使用函数进行舍入运算
- “随机选取”相关问题
- 数组公示多条件求和
- 其他数学函数
- 使用逻辑判断函数
- IF函数与逻辑运算
- 处理公式中的运算错误
- 显示#使用+IFS函数
- 使用SWITCH函数
- 使用文本与日期函数
- 查找文字位置
- 规范系统导出数据
- 处理数值和文本
- 使用函数进行替换
- 文本链接函数
- 计算结款日期
- 使用函数计算工龄
- 处理星期问题
- 处理工作日问题
- 使用查找函数与引用函数
- vlookup返回多列结果
- 跨表引用数据
- 使用简单的财务函数
- 速算每月还款函数
- 第四章
- 创建和格式化高级图标
- 创建交互图表
前言
这是我的一些学习excel的心得体会，大家可以利用此文档进行快速的查找函数，学会函数的用法，也可以通过这篇文档来快速的进行了解excel高阶的一些的思维。希望可以帮助到大家，个人语雀id：宵夜。
第一章 数据安全、协作类
保护单元格：
审阅/sheet标签右击——锁定单元格
结果：编辑和删除/单元格格式是灰色无法进行操作
保护单元格有多重权限：可以给不同用户去设置不同的权限
举例：
筛选，只允许用户筛选：先筛选—自动筛选
条件格式：选择条件格式
只能改行，不能改列：所有能编辑行的都选上，列的不选
情景：
保护/和不保护并存
比如两行公式需要手工录入、而其余公式列/行需要保护，不进行修改
做法：选中不保护的单元格—右击—单元格格式—保护
Ps：选择隐藏的话你只能看到结果无法看到算法/公式
不同区域设置不同密码
区域权限和总权限：
1.创建可编辑的区域并设置编辑密码
审阅—允许用户编辑区域
2.开启工作表保护（加密码可以理解为总得权限）
审阅—保护工作表
ps：一定要保护工作表才能生效
保护工作簿（保护全部sheet表）
1.审阅当中保护工作簿用法
位置：审阅—保护工作簿
2.如何给工作簿设置打开密码
位置：文件—文档加密—密码加密（输入密码才可以编辑，编辑密码没啥用，还要设置工作簿保护密码）
3.使用wps账号加密文件
位置：文件—文档加密—文档权限（登录账号才可以编辑）
关于共享与修订工作簿
1.共享：多人同时编辑文档
位置：审阅—共享工作簿
2.修订：追溯哪些人查看了数据
位置：审阅—修订—突出显示修订
第二章 高效处理数据类
定义数字的显示方式
#.## 慎用
为占位符、非强制的，比如0.50，用#.##就会显示为.5
0.00
0.
0.？？
同样可以保存两位小数 但是比如1.2，就会显示为1.2 空格 空格占位符
优点：可以让小数都对对齐
问题：保留小数点后两位，不足的用0补齐，对于整数部分，超过千位需要在百位之间加一个逗号
思考：#、0、？不同的意思
定义文本显示方式
文本的占位符是 @，即文本就是@，加上GH前缀表示为，GH@
单元格加单位
再给数字进行加单位时
直接右击单元格格式——在数字格式后面加 “ “ 英文的半角双引号
复杂情况
单元格格式：;;; 三个分号
单元格格式：0.00”剩余”;-0.00”不足”;”-“;@
0.00”剩余” 表示 正数保留两位小数点 且 在数字后面加剩余
-0.00”不足” 表示 负数保留两位小数点 且 在数字后面加不足
“-“表示 如果为0时 用 - 表示
@表示 如果单元格为文本 就显示文本本身
特殊符号 ！
%，如果直接在整数前面加百分号，那么会变成原来数字的100倍
比如1.2，前面加上%，即，%0.00，
就会变为，%120.00
正确做法：
!%0.00 或 “%”0.00
！意思为，强制显示后一个字符
特殊符号 *
*~0.00 表示数字前面全部用~填充，单元格多宽，就重复多少次，具体效果为
场景/用法
复杂情况
将数字/文字格式前面加上 * （空格），意思为在前面加满空格
这样的话单元格永远没有左对齐，一直右对齐，无法左对齐
总结
单元格样式
样式和格式刷的区别
样式和格式刷看起来显示是一样的，但是对样式进行修改时，那么对样式进行统一修改会更为方便，凡是应用样式的单元格会进行统一变化
我的思考：可以用筛选、筛选出不同格式的单元格，然后替换单元格格式
样式单元格进行保护
凡是样式下的进行保护：修改单元格，保护，点击保护，再点击审阅，保护单元表，即可
导入外部数据
导入更强调的是数据的链接关系，数据更改，那么进行刷新或者选择新的数据源即可
TXT/csv格式
选择—数据—导入—使用分列数据
导入网页数据/抓取网页表格，刷新后即可更新每日最新数据（ps，体彩3d的这样数据的进行分析会不会很好）
选择编码
导入和复制数据的之间的区别
导入数据可以直接进行更新数据，而复制进来则仍然需要分列等操作
简而言之，就是可以之间复用之前的TXT文本的选项等
定位功能
定位文本和数字
常量就是数字和文本
文本转换为数字形式，选中单元格，选择开始—单元格选项卡—文本转换为数值
定位错误值
查找错误值
选择单元格，点击定位—公式—错误
复制或者操作部分区域（分类汇总后，该区域复制会复制全部区域）
先用定位工具进行定位—选择可见数据—然后进行复制或者粘贴
定位对象
除了值意外的所以东西都叫对象，比如图片，可以选择定位—对象来对对象进行批量操作
定位空白
跳跃式写公式的解决办法
先将第一列进行定位空白，然后第一个单元格输入公式，然后进行ctrl+回车
再将第二列以及后面的数据，定位空白，然后E5单元格输入公式，然后进行ctrl+回车
公式隔开了无法进行下拉公式
先进行空白定位，然后利用ctrl+回车进行处理
通配符
*表示任意个数字符
？表示一个字符
比如销售?部，就可以表示销售一部、销售二部、销售三部
易错点——单元格匹配
问题
错误提示：这里面的替换需要选择单元格匹配
单元格匹配这里，也叫全单元格匹配，经常和问号进行 一起搭配使用，整个单元必须和我一样，如图中问题，则意思为必须为六位，不可部分匹配或者替换
波浪线~主要对付？或者，使？或者不当做通配符
照相机工具=截图工具
当我们需要把不同文件的表格打印在一张纸上，可以使用照相机工具进行快速处理
与传统的截屏区别
会进行实时动态，你变我也变，图标、图片、数字，原表格变化，照相机后也会进行变化，点击照相机后的截屏则会跳到原表格中
ps：回顾一下区域保护
第一步:先全选，这是关键
第二步:把红框处的勾选去掉，如图所示
第三步:鼠标选中
第四步:单击右键，设置单元格格式
第五步:红框处都勾选后点击确定
第六步:审阅选项卡下，点击保护工作表
场景
照相机工具其实提供的是一个重排版方式、比如我每个月需要打印固定的（部分/整体）表格到一张纸，那么照相机就可以不要每次截屏、并且可以像图片一样进行排版、对齐等操作
宏工具
宏其实是一组连贯动作的集合，做宏主要是为了减少劳动
减少重复性劳动、将多个操作录制为一组连串的动作，节省时间
如果没有宏，先添加宏工具栏
总结
第三章 公式的高级应用
函数排错工具
由公式导致的表格删除、插入、排序卡顿问题
解决方法：
将自动重算关闭，但会导致整哥文档中的所有公式都不会进行自动计算
公式—重算工作簿/计算工作表（手动重算）
位置：开始—选项—重新计算—自动重算
公式出错，出现#N/A错误
解决方法：
简易方法：
选中公式按F9或者（FN+F9），进行强制运算，然后按下ESC返回公式，不进行强制运算
公式中循环引用错误（套娃错误）
进行公式运算时候，选择的一定是单元格，而不能是整个列，因为如果是整个列的话，就出现鸡和蛋问题，例如：公式求和的值B12，公式计算的是B列的整体的值，那么计算出来的B12也应该是公式的一部分，成为了累计死循环，所以选择的一定是单元格。
一个很有意思的鸡和蛋问题
循环引用位置：
迭代计算位置：
场景
用NOW函数和IF函数和循环引用进行实时记录数据值和数据产生时间
NOW函数可以获取时间
IF函数可以进行判断条件等
当前的值无法进行直接获取
解读原始函数：
如果A2为空，我自身就为空
如果A2不是空，就是输出NOW函数
如果A2为空，我自身就为空，如果A2不是空，就是输出NOW函数
单元格格式设置
问题：第二行的时间会更新为最新的时间，但是第一行之前的录入时间会变更为何第二行一摸一样的。下一个进来了，上一个也跟着变化了。
原因：因为在表格当中，任何单元格值发生了变化，都会激活整个工作表，所有函数一起更新
解读修改后函数：
如果A2为空，我自身就为空
如果A2不是空
如果（B2）本来就有时间，那我就等于我自己本身
如果（B2）自己没有时间，就等于当前时间
然后开启迭代计算，就可以完成
输入时间，并且会录入 当前时间的效果
总结
循环引用：就是直接或者间接的引用到自己了，默认状态下，循环引用错误，公式不会计算，只有开启迭代运算，才会进行循环引用公式。
公式中的基础运算符
算术运算符
| 算术运算符： | 意义 |
|---|---|
| + | 加 |
| - | 减 |
| * | 乘 |
| / | 除 |
| % | 百分比 |
| ^ | 乘幂 |
文本连字符
| 连字符 | |
|---|---|
| & | 连接 |
比较运算符
| 比较运算符 | 意义 |
|---|---|
| = | 等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于或等于 |
| <= | 小于或等于 |
| <> | 不等于 |
单元格引用
| 单元格引用 | 意义 |
|---|---|
| , | 联合运算符 多个引用合并成一个引用 |
| : | 区域运算符 引用两个单元格之间全部区域 |
| 交叉运算符 引用两个区域的共有区域 |
联合运算符
交叉运算符
运算优先级
| 最高优先级 | 引用运算符里面的冒号（：）以及逗号（，）和空格运算符。 |
|---|---|
| 第二优先级 | 负号（-） |
| 第三优先级 | 百分号（%） |
| 第四优先级 | 求幂（^） |
| 第五优先级 | 乘法（*）和除法（/） |
| 第六优先级 | 加法（+）和减法（-） |
| 第七优先级 | 连接运算符（&） |
| 第八优先级 | 等于（=），大于（>），小于（<），大于等于（>=），小于等于（<=），不等于（<>） |
快捷键：
ctrl+1快速打开单元格窗口
ctrl+shift 可以快速到表格的下边缘
冷知识：
表格的下边缘 为 1048576 因为2的20次方是1048576
比较运算符得到的是逻辑运算值
数组公式的计算原理
数组：原来是一个一个运算、现在是一组一组运算，就叫做数组。
按住啊ctrl+shift 再点击回车，出现中括号，表示确定为数组， 就会呈现数组的结果
ps：无法进行单一数值的删除，但用处不大
修改前
修改后
注意
利用公式标记数据
在条件格式中写公式可以将复杂问题简单化
能选单元格就选单元格，别拉取整个数据
绝对引用和相对引用的区别
此为绝对引用，即便选中整个区域，公式也无法进行复用
在使用相对引用时，公式或函数中的行或列会随着行号和列标的变化而自动变化。
绝对引用的特点就是在使用绝对引用时，公式或函数中的行或列是绝对不会发生变化的。
countif 函数
countif 函数用来统计该区域内，统计该数值出现了多少次
利用公式制定输入规则/数据有效性
前一列为XXX，后一列不可以输入的有效性
后一个不可以和前一个一样的有效性
小于一定数值时，才可输入。（小于等于才可输入）
只能防止手动填写的，不能防止复制粘贴的
数据有效性只会对空白单元输入时候有效，其他已输入单元格不会更改或者仅提示
个人觉得目前没什么卵用，看看即可
名称与公式
定义常量
定义区域
定义公式
公式—名称管理器
注意：定义名称一定要选对单元格，而且是相对引用
多行、多列填充公式
混合引用条件
我们既要将公式横着拖拽、也要将公式纵这拖拽情况，就一定会涉及混合引用
凡是涉及整行标记，一面填充，也会涉及混合引用
A1
相对引用，横向纵向都可以拉，拉取会变
$A$1
绝对引用，横向纵向都不可以拉，拉取不会变
$A1
混合引用，纵向可以拉，拉取会变，横向不可拉。
A$1
混合引用，横向可以拉，拉取会变，纵向不可拉。
用引用公式来做9*9乘法表格
$A2代表 A横向不会变，值定死，纵向会变。
B$1代表 B纵向不会变，值定死，横向会变。
column函数可以随着列号的变化来变化数字，如果column函数括号里不加任何其他常量，那么输出值直接就会为该列是第几列，
A列表示为1
B列表示为2
C列表示为3
D列表示为4
…………………..
依次往下
横向走不变、纵向走变化的情况
vlookup函数+混合引用
常规来说，vlookup函数你要索引批量数据，多行或者多列时候，你会进行手动输入每一个要变得，比如：
这样会很笨拙，一个一个复制粘贴
那么我们可以引入column函数来进行自动变化，我要索引的列
公式为：
解读为：
条件格式+混合引用
反思
$这个符号放谁的前面就固定谁
$A$1 意思就是说 横和纵你两个都别动，就乖乖在这个区域里面
$A1 意思就是说 横向你不能拉了，纵向你可以拉，行不管怎么样都是这个值，怎么拉也不会变，纵向会在第几行就是第几行，即锁定列
A$1 意思就是说 纵向你不能拉了，我定死了，横向你可以拉，是第几列我就变成第几列数据，即锁定行
条件求和和条件计数
sumif函数（单条件求和）
sumif函数（区域，条件，【求和区域】）
区域是指：正在该区域内进行搜索匹配字段
条件是指：你所要求得目标字段那个单元格
求和区域：就是你要算那个数字那一列
最后的结果输出的是符合该条件下的总和
sumifs函数（多条件求和）
sumifs函数（多条件求和，逻辑上是既….又….的值）
sumifs函数（求和区域，区域1，条件1，【区域2，条件2】…..）
求和区域：就是你要算那个数字那一列
区域1，在区域1中找条件1单元格内容
区域2，在区域1中找条件2单元格内容
条件和区域总是成对出现的
大于小于的区间 需要用“ ”
大于小于的区间 需要用“ ”，比如“>50”的共有多少总和
求取次数：比如某一单元格出现的总次数，可以用countif函数（即在一个区域当中，某一个值有几个）
与sumif的区别，countif算的其实是数量，单位个数。而sumif则求取的是总和
countifs函数则是表示多个区域和条件计数（2个以上的区域计数的和）
场景：countif、countifs、sumif、sumifs之间的用途
主要用于数据核对
活学活用、多表核对
当仅有一个限定条件，且值唯一我们就可以用sumif来进行校验，原理是因为，单一条件下只会有一个值，求和也当然是一个值了。
当有两个及以上限定条件，且值是唯一我们就可以用sumifs来进行校验，原理是因为，多个条件下只会有一个值，求和也当然是一个值了。
总结
条件函数：就是各种函数后面加上IF
其他条件统计函数
最大值条件函数
最小值条件函数
平均数条件函数
去掉最大最小值，求取平均数
换个说法就是：
大于最小值，写法为：=“>”&MIN某列
小于最大值，写法为：=“<”&MAX某列
原因，不能将函数也包含在“”之中
大于第N小的值，小于第N大的值情况
SMALL用于求函数中第N小的
比如，求取第三小的数：
=small（某列，3）
LARGE用于求函数中第N大的
比如，求取第三大的数：
=large（某列，3）
场景，比第三小数字大的数，和比第三大数字小的数的该列平均数：
=averageifs（求值列，条件列1，“>”&small（某列，3），条件列，“<”&large（某列，3））
统计筛选后的数据
subtotal函数
sum无论你是否使用筛选都对筛选无反应，总是求的是总集
subtotal中的sum函数会随着你筛选的数值条件不同而使求和的结果发生改变
分类两个
筛选求和
subtotal中的sum，即=subtotal（9，某列）1-11的函数：只会求去筛选之后的值
筛选+隐藏求和
subtotal中的sum，即=subtotal（109，某列）101-111的函数：会求去筛选之后的值以及隐藏之后的值
函数适用范围：
sum>subtotal9>subtotal109
结果为整体>结果为筛选后的结果>结果为筛选和隐藏后的结果
使用函数进行舍入运算
舍入方式一：四舍五入
误区：单元格样式改为保留两位小数，只是保留了表面，实际中复制粘贴仍然会是多位小数
正确方式：利用round函数，写法：=round（单元格，2/3/4）后面的数字代表保留几位小数
舍入方式二：直接进位
对小数部分直接进位，向前补一位
利用roundup函数，写法：=roundup（单元格，0/1/2）后面的数字代表保留几位小数，0代表直接保留整数
舍入方式三：直接舍去
对小数部分直接进行舍去，不向前进行补位
利用rounddown函数，写法：=rounddown（单元格，0/1/2）后面的数字代表保留几位小数，0代表直接保留整数
round函数中，数字也可以用负数来表示，比如12345，=round（单元格，-2），就会变为12300，-2就可以表示为可以按整百取整
场景
在统计之前处理求和问题，出现一分钱统计误差
先将数字进行数据处理，如四舍五入，然后再求和。
总结：
“随机选取”相关问题
rand函数
写法：=rand（），默认括号内不写数字，会在0-1之间随机产生数字，小数点后十五位的数字
randbetween函数
写法：=randbetween（1-5）
随机好随，但是难点在于防止重复
函数不能局限于现有公式，而是需要灵活变通和反思。比如：randbetween可以用于取某一范围的整数，同样我们可以用roundup函数（舍入近似函数）加上rand函数得到和randbetween一样的效果
场景：
方法一：
其他方法可以自己思考
数组公示多条件求和
数组公式三键结尾
注意这里面的花括号需要Ctrl+shift，告诉函数这是一个公式
sumproduct函数
sumproduct就是数组版的sum
例如：先乘后加数据等
多条件统计实例
第五列判断海鲜粉=海鲜粉
第六列判断销售一部=销售一部
第七列表示为且的关系（求交集）、两个true相乘为1，两个/一true一flase相乘为0。
因此第八列用第七列乘单价
第九列用sum处理即可解决
用一个公示可以
sumifs函数其实更快，要比sumproduct函数快17倍
其他数学函数
ABS绝对值
绝对值（正负都为正值），处理的是数字
写法：=ABS（单元格），然后下拉
MEDIAN中位值
求某一区域的中位值
若为偶数列：最中间那两个的平均值 ， 奇数列：最中间那个
写法：=MEDIAN（那一列）
MODE函数求众数
出现次数最多的，如果出现次数一样，就取第一个次数出现最多的
写法：=MODE（那一列）
MOD函数求余数
写法：=MOD（分母，分子）
除以2还余1就叫做奇数，能除赶紧就叫做偶数
场景：凡是奇数行都填上填充色
如果用格式刷，或者手动设置，由于部分行的删除，单元格样式并不会发生改变
ROW函数（行号）/column函数（列号）
写法：=ROW（单元格）就可以知道行数
公式拆解：
row函数知道行号，除以2，没有余数则表明是偶数行，利用mod函数，有余数
作业：隔两行填充一个，渐变色
使用逻辑判断函数
IF函数（条件函数）
IF嵌套（嵌套条件函数）
N情况，就用N-1个IF
IF函数区间用法
！！！！！表格中的位于某个区间错误
错误写法
在表格中位于某个区间，永远不能用：20>A2>1这样的写法来表示某个单元格位于某个区间，表示大于某值，小于某值。原因，A2会和20做一个判断，得到ture和false在和1进行判断，结果无意义
大于600的显示过了，那么剩下的一定小于600了，然后再大于400即可
像剥洋葱一样，一层一层往下去拨。写的时候要理清楚逻辑结构
总结：
IF函数与逻辑运算
AND函数
AND函数表示的且的关系，ABC…D等条件都满足
题目一为：
一个大于85，一个为优，则为优秀，其余为空白写法
题目二为：
OR函数或函数
小技巧：先写AND和OR函数
复杂情况：AND和OR函数的一起使用
=if（or（and（B2=“男”，C2>=22），and（B2=“女”，A2>=20）），“是”，“否”）
由内往外，由小往大
总结：
处理公式中的运算错误
IFERROR查错误函数
次函数范围太宽，有可能是函数写错了，也有可能是公式确实找不到信息。但都会改为你想要改的值。
写法：IFERROR（错误列，错误之后显示的）
=IFERROR（A2，0）如果A2错误就显示为0
场景
在运用vlookup函数时候，查询信息中原表中没有该信息，显示#N/A错误
IFNA函数查错误函数
IFNA函数用于查询公式确实找不到信息情况，如果公式错误依旧会显示公式出错误。
写法同IFERROR函数写法
例如：
IFNA函数和IFERROR区别
范围不同，所有的错误都算ERROR，所以IFERROR会屏蔽所有错误，而IFNA函数专门用来屏蔽VLOOKUP找不到的错误
总结
显示#使用+IFS函数
非必用函数
IF函数的升级版，加S表示多情况下。在此函数中每一种都需要写清楚情况
=IFS（E6>=2000000，“A级”，E6>=1200000，“B级”，E6>=600000，“C级”，E6<600000，“D级”）
解释为：
若E6大于2百万，为A级别，
否则，大于120万，为B级
否则，大于60万，为C级
否则，小于60万，为D级
总结
使用SWITCH函数
简而言之就是转换函数，一 一对应关系。工作当中可能有更简单的解决办法
写法：=switch
不要看帮助，不好理解
解释：选定单元格，选择对应关系即可
利用vlookup函数进行
对单元区域进行强制运算
SWITCH函数和IFS函数的对比
总结
使用文本与日期函数
从左截取：=left（，）
从右截取：=right（，）
从中间截取：=mid（A2，2，3）
计算字符数量：=len（）
注意：文本1和数字1，在表格中永远不相等
文本和数值不相等
left函数取出来的只能是文本，因此，想要数字格式可以让取出来的数字*1，会强制将取出来的数字变成文本，那么，这样在运用其他数值函数，比如vlookup函数就可以使用了。
字节函数（文本函数带B）
理解字节：
一个中文字是两个字节，字母和数字是单字节
如果是字节是文本，取不到时候就会留取空白，比如：空白的，取五个字节，那么输出结果为：空白 ，后面有“的”一半，就会用空格来表示
从左截取：=leftb（，）
从右截取：=rightb（，）
从中间截取：=midb（A2，2，3）
计算字符数量：=lenb（）
思维发散
注意vlookup索引的数值，left导出的是文本，以及查找值范围
可以利用字节、字符数来进行分列文本和数字情况（等同于智能分列）
原理：字节≠字符，汉字字节为2，字母和数字为1，正常来说截取的就是字节数，由于汉字存在，出现了错位，因而一减，就是汉字的数量了。
分列汉字
分列数字：先分列汉字，在倒退数字
查找文字位置
FIND函数
写法：=find（找什么，某区域）
如果要找字符时候一定要加双引号“ ”
逻辑：你要找什么，在哪儿找（会区分大小写）
search函数
search同find的区别：
1.要求不严格，英文不区分大小写，都会找到。
2.find不支持通配符，search函数支持通配符
严格度/范围：search函数>FIND函数
find函数 的嵌套
原理：找第二个“—” 的写法，找到第一个“-”+1即可完成
先找到第一个“-”，然后此为第找到第二个的起始位置，注意默认的，即第一个是没有起始位置，不在这个单元格后加1，而是在这个函数之后加1，表示第二个
实例
思路：
观察邮件，发现规律，以@为分隔符
先利用find函数找到@，即=find（“@”，A2）-1，其中减1，就表示我可以取到@之前的一位
其次利用left函数，从左往右开始截取
即，=left（A2，find（“@”，A2）-1）
结果一：（从左往右）
规范系统导出数据
大写函数UPPER
小写函数lower
首字母大写函数proper
写法：=proper（）
删除空格函数TRIM（空格合理化）
（不用替换情况下，替换空白会使单词等连接，导致无法识别）
前后不合理，去掉前后空白格
单元格带绿色三角情况
纯数字带三角才能说明是文本函数
纯文本/字母带三角，可能是有空字符串（专业说法叫做，非打印字符，有字符但是看不到）
解决vlookup函数问题
clean函数可以解决vlookup函数中单元格出现非打印字符问题
总结
处理数值和文本
文本转数值函数value
数值转文本函数TEXT
TEXT就是函数版的自定义数字格式
怎么用?
将自定义中的格式代码粘贴到函数中即可
数字换格式：只是表面换了
而TEXT函数真的可以将数值转换为文本
场景
实现自动化更新日志或者报表的情况
实现方法如下：
不用TEXT函数的话，只会显示日期的数，而非日期
=”截止到”&TEXT(E5,”yyyy/m/d”)&”，共”&E26&”元”
https://baijiahao.baidu.com/s?id=1733158272598692441&wfr=spider&for=pc（什么时候用” “）
1.公式中引用字符串，要加双引号
2.需要用“ ”代表空值
3.需要表示文本型日期
实现函数
八位数字转换为日期
首先利用TEXT函数，转换为0000-00-00形式
然后用VALUE函数，将文本转换为数值。或者1
乘1：1
最后调整单元格即可
使用函数进行替换
SUBSTITUTE函数(基于格式替换)
写法：=SUBSTITUTE(，，，)中文记得加双引号” “
SUBSTITUTE函数是文本函数，记得*1才能变成数值
subtitute函数可以直接替换掉第几个特殊符号或者空格等等…..
replace函数（基于位置替换函数）
写法：=replace（单元格，第几位开始，替换几位）
利用SUBSTITUTE函数来统计
原理，利用逗号来进行统计
首先利用SUBSTITUTE将逗号替换掉：=SUBSTITUTE(C2,”,”,””)
然后利用len函数求出先后两个状态的差值，加一即可得到人数：=LEN(C2)-LEN(SUBSTITUTE(C2,”,”,””))+1
总结
文本链接函数
简单链接concat函数
复杂链接textjoin函数
功能强大版的concat
写法：=TEXTJOIN(“,”,TRUE,D2:P2)
TURE表示不保留空值
FLASE表示保留空值（后期好分列）
计算结款日期
MONTH月函数
year年函数
day日函数
0
day函数可以得出日子，日期具体是几号
=date（year（）+ 某年，month（）+某月，day（）+某日）
DATE函数可以将年月日组合到一起，形成一个新的组合日期
date日期函数
可以利用date函数进行日期的加减，比如项目延期，预计完成时间需要加一定时间，可以利用此函数，先用day、month、year函数拆开，然后用date合。
总结
使用函数计算工龄
日期间隔函数datedif
间隔月/年/日：
datedif函数得到的一定是整数
=datedif（开始日期，结束日期，比较单位）
年份的比较单位：y
月份的比较单位：m
日子的比较单位：d
单位之间的组合
ym：忽略年份算月份
yd：忽略年份算日子
md：忽略年忽略月只算天数
场景：
截止到目前为止，已经安全生产xxx天
=”截止到今天，已经安全生产”&DATEDIF(A2,B2,”y”)&”年零”&DATEDIF(A2,B2,”yd”)&”天”
处理星期问题
weeknum函数
weekday函数
用weekday函数来进行标记格式周六周末
总结
处理工作日问题
间隔工作日函数networkdays
=networkdays（开始日期，截止日期，假期参数）
需要完整写出节假日的列表，用以计算工作日
设置“到期自动提醒”
today函数
年月日
now函数
年月日小时分钟
单元格输入1，改为日期表示为1900年1月1日
单元格输入1.5，改为日期表示为1900年1月1日中午12点（0.5，就是1/2天）
单元格输入1.25，改为日期表示为1900年1月1日早上六点（0.25，就是1/4天）
利用身份证算出年龄
公式——插入常用公式——身份证算出年龄公式
发货函数/实时更新函数
使用查找函数与引用函数
vlookup函数查找数据
精确匹配
近似匹配
找到最接近那个单元格的，只有数值才有近似匹配一说
近似匹配原则：找到小于等于自己的最大值
vlookup函数和hlookup函数
vlookup函数：竖表，通过左边找右边，0是精确匹配
hlookup函数：横表，横版的vlookup，注意错行问题，可以利用F4进行绝对引用
vlookup函数近似匹配的妙用
层次太多，用IF函数写起来很麻烦，可以用vlookup函数的近似匹配
因为vlookup函数近似匹配的区间划分必须从小到大，而且利用vlookup函数的近似匹配原则：找到小于等于自己的最大值。这样，可以有以下两种选择的妙用
P1计算提成比列
P2计算分数区间
用IF函数
用vlookup函数
总结
match和index函数
vlookup函数都是根据左边选右边，即VLOOKUP函数第二参数，一定是要确保数据表中的搜索的那一列一定是第一列。（vlookup函数一个找一个取两个动作）
此情况下，就无法使用vlookup函数，因为公司名称数据不是左边数据表的第一列。
只能通过左边找右边，不能通过右边找左边
必须从右往左找情况下，就需要用match和index函数
match函数负责找找
=match（找什么，哪一列，0精确匹配）vlookup函数找的那部分，只是找
index函数负责取回来
match+index函数=自由度更大的vlookup函数
注意都是单行或者单列，不跨行处理
vlookup返回多列结果
利用vlookup函数取回不连续间断性顺序的数据
=VLOOKUP($I2,$A$1:$E$21,MATCH(J$1,$A$1:$E$1,0),0)
=VLOOKUP($I2,$A$1:$E$21,MATCH(J$1,$A$1:$E$1,0),0)这里的$锁定了行，可以向右拖拽
=VLOOKUP($I2,$A$1:$E$21,MATCH(J$1,$A$1:$E$1,0),0)这里的$锁定了列，可以向下拖拽
换个index函数公式来完成
=index（数组，行序数，【列序数】，【区间序数】）
数组：index函数不光可以选择一列，还可以选择一堆。即可以是一列，也可以是一个区域
行序数：要取的是第几行。技巧：可以抽象化出来为match函数
列序数：要取的是第几列。技巧：可以抽象化出来为match函数
利用两个match给index函数定个坐标
=INDEX($A$1:$E$21,MATCH($I2,$A$1:$A$21,0),MATCH(J$1,$A$1:$E$1,0))
绿色部分定位列坐标
=INDEX($A$1:$E$21,MATCH($I2,$A$1:$A$21,0),MATCH(J$1,$A$1:$E$1,0))
紫色部分定位行坐标
=INDEX($A$1:$E$21,MATCH($I2,$A$1:$A$21,0),MATCH(J$1,$A$1:$E$1,0))
蓝色部分注意混合引用的问题
跨表引用数据
indirect函数（文本转引用）
=indirect（，）
直接把举例：输入A2，然后利用利用indirect函数直接可以吧A2单元格的内容可以索引过来
跨表抓取
利用=号来进行
场景：
手工构建出来一个文本的引用
这样的自由度非常大
可以任意抓取不同的表，不同的数据，但最终他是一个文本，引用不到数据，所以就需要一个indirect函数
跨sheet表进行vlookup和indirect
总结
使用简单的财务函数
速算每月还款函数
函数中都用的是月利率
函数中月还款一定是负值
函数中终值一定是0
PMT函数计算月还款
=pmt（利率，支付的总期数，现值，终值）
利率为年利率，月利率需要/12
支付的总期数：需要还多少期（月）
现值：一共欠银行多少钱（一共要还多少钱）
终值：就是0，一定等于0
NPER函数计算总期数
固定利率、固定总额，设置每月还款金额，推算出来总期数
=NPER（利率，定期支付额，现值，终值）
注意：定期支付金额是负的
PV函数计算最大贷款额
根据总期数、利率、月还款计算最大贷款额
=pv（利率，支付总期数，定期支付额，终值）
月还款设置为负值，还款额才为正值
FV函数计算贷款剩余
=fv（利率（月利率），支付总期数，定期支付额，现值）
现值：一共贷款多少钱
函数中月还款一定是负值
总结
第四章
在智能表中管理和分析数据
自定义排序和高级筛选
自定义排序
使用高级筛选
需要做一个条件区域
条件区域必须带表头，点击筛选——高级筛选，如果点了数据源中任意单元格，那么点击高级筛选列表区域就会直接选择该表格。条件区域选择自己做好的条件区域
同行表示且，不同行表示或的关系，例如图上区域就表示成员三的酒水饮料或者成员三的金额>1000数据。
总结
表格工具
如何用表格工具
点击开始选项卡——插入——选择表格工具
如何区分是否是表格：是否有表格工具出现
简单用透视表进行统计
透视表的缺点在于不能抓取实时数据，即新增数据时，最新数据无法进行一个抓取。
因此将单元格转换为表格，可以直接进行数据透析表的实时更新
表格工具就是帮助我们动态抓取数据
切片器
切片器可以理解为筛选的另一个形态，只不过看起来比筛选更好看
选项卡——插入——切片器
默认状态下无法使用切片器，需要转换为切片器
数据透析表字段设置
调整统计表的统计值：
在数据上点击任意位置，插入数据表
注意不要直接选择列，因为直接选择列有可能出现统计错误
在值中选择数字，点击求和项，默认是求和：可以改为计数、最大值、占比等
若同时需要多个数据：比如该数字下的百分比、计数、最大值等，可以在值里面多复制几个数字
使用父级百分比
总结
如何进行筛选排名前三的数据
透视表的排序
中文字按照默认拼音字母来进行排序，
透视表中的筛选，选择你要排序的那个依据，双击大类，然后点击值筛选，点击前十项，改为前三项即可
总结
快速日期统计数据
日期组合
在数据透析表中，右键选择组合。注意区分：年、季度。如想要去年的四季度和今年的一季度，就需要你进行季度+年份的组合方式，否则只会季度进行组合
按照年/季度/月份划分等
数值组合
例如想要查看数据区间，为了把数据做一个区间。
场景：可以运用到成绩统计之中
文本组合
对数据透析表进行分类
首先将同一属性下放到一起，挨着，然后选择组合
例如将这三类作为固定支出，进行组合
总结
数据透视表与公式
计算字段
建议在透视表里面添加公式
点击透析表——选择分析——字段、项目，字段与字段的运算叫做计算字段。
删除掉总计列：
点击数据透析表——点击设计——仅对列禁用
对计算项进行处理——点击费用属性——点击字段、项目——点击计算项
字段中的项目明细进行计算
解释计算字段和计算项目：
在原数据中：每一列数据都是一个字段、在每个字段当中、例如月份字段中包含一二三四月份的分类、每个分类叫做一个项目。
总结
数据透析表的显示样式（设计）
设计——分类汇总
分类汇总：父级和子级进行汇总
左边为分类汇总前，右边为分类汇总后
对字段进行设置：点击某一要改的行，单图中的大类单元格，点击右键，选择字段设置，就可以进行单独某个字段的分类汇总。
设计——总计
可以选择对行之间的启用禁用，去掉总计
左去掉总计前，右去掉总计后
设计——报表布局
左以压缩形式显示，右以大纲形式进行显示
可以进行以压缩形式显示（收缩至某几个列）
以大纲形式进行显示
以表格形式进行显示
重复显示项目（将空白的单元格，进行重复项填充）
设计——空行
总结
快速切换统计数据
行列字段是用来进行分类
筛选器是用来做筛选数据源的
值字段是用来做统计的
不同的表格用同一个切片器，可以点击切片器，选择 透视表链接，选择数据源。
切片器无法使用情况
文件格式为xls可能无法使用切片器，将文件格式另存为xlsx即可使用切片器
使用高级数据分析
根据客户预算制定报价
场景：进行反推算，即根据对方的预算，来提供我们的报价
举例：
此为我们提供的报价
当得知客户预算时，修改我们的单价报价，可以利用单变量求解
公式反推用的就是单变量求解：
数据——模拟分析——单变量求解
找到未回款的交易记录
规划求解工具——一个数理逻辑很强的工具；可以带着答案找问题
问题：
我需要找到和为：84759.61的数据排列方式
步骤：
思维：用公式可以量化说明
操作：在公式后加入辅助列，0表示不要，1表示要
思维：如果后面N个数字相加等于84759.61，那么辅助列标记为1。如果不是你就相加等于0。简言之，用0和1的方式来标记哪些数字要，哪些数字不要。不停地改0改1直到数字正好为84759.61
操作：=SUMPRODUCT($C$2:$C$21*D2:D21)
利用sumproduct函数，将最后两列（数值列和辅助列进行相乘）
利用规划求解工具（数据——模拟分析——规划求解）
设置目标值（最终得到的值）
设置范围单元格
设置条件
1.该辅助列范围内小于等于1
2.该辅助列范围内大于等于0
3.该辅助列范围内只能是整数，int整数类型
保留计算结果即可
创建和格式化高级图标
展示差异较大的数据（组合图）
两组数据在一组图表上一个坐标系下展示不下，比如：大数值和比例无法再同一个坐标轴表示。
点击插入选择组合图即可（插入次坐标轴）
总结
在堆积图上显示总计
位置
点击图表工具——最左边——添加元素
元数据中没有总计，很难在统计图中加上总计，因此需要在元数据加上总计列
如果将点击次坐标，放在次坐标上，就会跑在前一层上，然后将颜色改为无填充，然后加上数据标签，就可以实现总计的数据标签显示的效果。
堆积柱形图是没有数据标签放于外侧的选项，因此点击设置总计为簇形统计图，就有了标签防御外侧的选项。
总结
用堆积条形图制作甘特图
甘特图的作用：弄清项目的剩余任务、评估工作进度、处理项目规划和工作汇报问题。任务与任务、任务与逻辑之前的问题。
步骤
1.点击蓝色部分，将蓝色部分进行无填充、无线条设置：
2.点击左侧轴，设置坐标轴为逆序类别
3.设置坐标轴格式，找出最大值和最小值，然后将图表的水平轴设置最大轴最小轴。
4.点击数据轴，将分类间距调小，完成图形绘制。
原理
日期实际上也是数字，因此甘特图是开始日期和任务周期进行堆积
理解柱形图：把条形图横过来放就是柱形图
分类间距变小，整个条形会变粗
在折线图中绘制直线（可以辅助参照趋势）
在已生成统计图的统计表中，首先添加辅助列，输入开始数值和结束数值（即两个端点）
然后点击统计图编辑数据源，添加系列，然后设置中选择空单元格显示为：用直线连接数据点
折线图和面积图
XY散点图展示数据分布
并不是折线图去掉线就是散点图
折线图的场景就是根据时间看走势
散点图的场景就是看数据的分布情况，要设定好X轴和Y轴
散点图和折线图区别：
只有散点图不先选择数据表，需要先插入散点图空图，然后点击图，添加数据
快速向下：选中单元格，ctrl+shift+下箭头
一键新图表（图表模板工具）
自己可以设定为图表模板，不可修改模板存储路径
注意看自己的图表模板的位置
模板另存
模板新增
模板迁移
创建交互图表
交互式图表：图表上包含了一些控件，可以点击控件来控制图表上的数据，来看到不同的效果。
利用OFFSET函数取数据
=offset（参照区域，行数，列数，[高度]，[宽度]）
参照区域：一般为A1
高度：你要取多少行
宽度：你要取多少列
思维：
比如：以B1单元格为开始，下移7行（即函数中的行数为7），列数为0，高度为20。那么就可以为动态的交互表格形成一个函数基础
图表没有办法拿函数当数据源，因此将函数的值另外命名一下（在名称管理器当中）
动态图表思路：
滚动条变化会引起数字的变化，数字一变。取值范围就会变化，取值范围变化，那么图表上的对应数据则会发生变化。
总结：
若有收获，就点个赞吧
0 人点赞
