excel公式介绍（二）

包含了：查找文字位置函数、规范系统导出数据函数（大小写、删除多余格式）、处理数值和文本（文本转数值函数value）、（数值转文本函数TEXT）、Excel公式里什么时候要加双引号？什么时候不用加？替代函数、文本链接函数、结款日期函数、计算工龄/日期、vlookup函数查找数据、基础的财务函数等。方便大家快速找寻函数，帮助大家进行函数入门。

excel公式介绍（二）

• 查找文字位置
  • find函数
    • 写法：=find（
    • 找什么 ，某区域）
    • 如果要找字符时候一定要加双引号“ ”
    • 逻辑：你要找什么，在哪儿找（会区分大小写）
  • search函数
  • search同find的区别：
    • 1.要求不严格，英文不区分大小写，都会找到。
    • 2.find不支持通配符，search函数支持通配符
    • 严格度/范围：search函数>FIND函数
• 规范系统导出数据函数
  • 大写函数UPPER
    • 写法：=UPPER（）
  • 小写函数lower
    • 写法：=LOWER（）
  • 首字母大写函数proper
    • 写法：=proper（）
  • 删除空格函数TRIM（空格合理化）
    • （不用替换情况下，替换空白会使单词等连接，导致无法识别）
    • 前后不合理，去掉前后空白格
  • clean函数
    • clean函数可以解决vlookup函数中单元格出现非打印字符问题
    • 写法：=clean（）
• 处理数值和文本
  • 文本转数值函数value
    • 或者文本*1，也可以将文本变数值
  • 数值转文本函数TEXT
    • TEXT就是函数版的自定义数字格式
    • 用法
      • 将自定义中的格式代码粘贴到函数中即可
      • 数字换格式：只是表面换了
      • 而TEXT函数真的可以将数值转换为文本
    • 场景
      • 实现自动化更新日志或者报表的情况
• 替代函数
  • SUBSTITUTE函数(基于格式替换)
    • 写法：=SUBSTITUTE(，，，)中文记得加双引号" "
    • SUBSTITUTE函数是文本函数，记得*1才能变成数值
    • subtitute函数可以直接替换掉第几个特殊符号或者空格等等.....
    • 一个很好的场景
      • 背景
        • 20220718遇到了一个问题，想要把word中的文字复制粘贴并拆分在表格中，发现粘贴到表格中成为这样：
      • 解决办法
        • 于是想到了用SUBSTITUTE函数来 替换掉
        • =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B8,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")
        • 至于小数点可以用替换功能去替换掉，或者在写一个SUBSTITUTE函数
        • 用途
          • 1.可以实现文本前数字的删除
          • 1.可以实现文本后数字的删除
          • 1.可以实现文本中数字的删除
  • replace函数（基于位置替换函数）
    • 写法：=replace（单元格，第几位开始，替换几位）
• 文本链接函数
  • 简单链接concat函数
    • 写法：=concat（）
  • 复杂链接textjoin函数
    • 写法：=TEXTJOIN(",",TRUE,D2:P2)
    • 可以理解为：功能强大版的concat
• 结款日期函数
  • MONTH月函数
    • month函数可以得出月份，日期具体是几月
  • year年函数
    • year函数可以得出年份，日期具体是几几年
    • 写法同月函数
  • day日函数
    • day函数可以得出日子，日期具体是几号
    • 写法同月函数
  • =date（year（）+ 某年 ，month（） +某月 ，day（） +某日
    • DATE函数可以将年月日组合到一起，形成一个新的组合日期
    • 一些好的用法启发
      • 比如项目管理中，因为各种问题延期，那么我们就可以用date函数进行+或——工时，用以改变日期，计划类的时间改变都可以用date函数
• 计算工龄/日期
  • 日期间隔函数datedif
    • 间隔月/年/日：
      • 年份的比较单位：y
      • 月份的比较单位：m
      • 日子的比较单位：d
      • 单位之间的组合
        • 简而言之就是忽略前面，算后面
        • ym：忽略年份算月份
        • yd：忽略年份算日子
        • md：忽略年忽略月只算天数
    • datedif函数得到的一定是整数
    • =datedif（开始日期，结束日期，比较单位）
      • 记得单位要加双引号
  • weeknum函数
    • 计算一年的第几周
  • 工作日函数
    • 间隔工作日函数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函数
    • 注意
      • vlookup函数都是根据左边选右边，即VLOOKUP函数第二参数，一定是要确保数据表中的搜索的那一列一定是第一列。（vlookup函数一个找一个取两个动作）
      • 只能通过左边找右边，不能通过右边找左边
      • 必须从右往左找情况下，就需要用match和index函数
        • match函数负责找找
          • =match（找什么，哪一列，0精确匹配）vlookup函数找的那部分，只是找
        • index函数负责取回来
          • =index（要取什么，在第几行），只是取
        • match+index函数=自由度更大的vlookup函数
    • 精确匹配
      • 一模一样
    • 近似匹配
      • 找到最接近那个单元格的，只有数值才有近似匹配一说
      • 近似匹配原则：找到小于等于自己的最大值
    • vlookup函数近似匹配的妙用
      • 层次太多，用IF函数写起来很麻烦，可以用vlookup函数的近似匹配
      • 因为vlookup函数近似匹配的区间划分必须从小到大，而且利用vlookup函数的近似匹配原则：
      • 找到小于等于自己的最大值。 这样，可以有以下两种选择的妙用
        • 计算提成比列
        • 计算分数区间
  • vlookup函数和hlookup函数
    • vlookup函数：竖表，通过左边找右边，0是精确匹配
    • hlookup函数：横表，横版的vlookup，注意错行问题，可以利用F4进行绝对引用
• 财务函数
  • PMT函数计算月还款
    • =pmt（利率，支付的总期数，现值，终值）
    • 用法解释
      • 利率为年利率，月利率需要/12
      • 支付的总期数：需要还多少期（月）
      • 现值：一共欠银行多少钱（一共要还多少钱）
      • 终值：就是0，一定等于0
  • NPER函数计算总期数
    • 固定利率、固定总额，设置每月还款金额，推算出来总期数
    • =NPER（利率，定期支付额，现值，终值）
    • 注意：定期支付金额是负的
  • PV函数计算最大贷款额
    • 根据总期数、利率、月还款计算最大贷款额
    • =pv（利率，支付总期数，定期支付额，终值）
    • 月还款设置为负值，还款额才为正值
  • FV函数计算贷款剩余
    • =fv（利率（月利率），支付总期数，定期支付额，现值）
    • 现值：一共贷款多少钱
    • 函数中月还款一定是负值
• 注意事项类
  • 绝对引用和相对引用的区别
    • 绝对引用，即便选中整个区域，公式也无法进行复用 在使用相对引用时，公式或函数中的行或列会随着行号和列标的变化而自动变化。 绝对引用的特点就是在使用绝对引用时，公式或函数中的行或列是绝对不会发生变化的。
  • 混合引用
    • A1 相对引用，横向纵向都可以拉，拉取会变 $A$1 绝对引用，横向纵向都不可以拉，拉取不会变 $A1 混合引用，纵向可以拉，拉取会变，横向不可拉。 A$1 混合引用，横向可以拉，拉取会变，纵向不可拉。
    • 用引用公式来做9*9乘法表格
• Excel公式里什么时候要加双引号？什么时候不用加？
  • 1.公式中引用字符串，要加双引号
    • 字符串包括文本、字母、符号以及文本型数值，公式中一旦引用这些，必须要加英文状态下的双引号。
  • 2.需要用“”代表空值
    • “”双引号里面什么也没有，在Excel中代表空值、空单元格。
  • 3.需要表示文本型日期
    • 日期本质上是数值型数据，但有时候需要用文本型表示。
  • 4.公式定义名称不能加双引号
  • 5.单元格引用不能添加双引号