标签 excel 下的文章

如果需要在某一文本字符串中替换指定的文本,请使用函数 SUBSTITUTE;
如果需要在某一文本字符串中替换特定位置处的任意文本,请使用函数 REPLACE。

SUBSTITUTE

SUBSTITUTE(text, old_text, new_text, [instance_num])
描述:在文本字符串中用 new_text 替换 old_text。

SUBSTITUTE 函数语法具有下列参数:

  • text 必需。需要替换其中字符的文本,或对含有文本(需要替换其中字符)的单元格的引用。
  • old_text 必需。需要替换的文本。
  • new_text 必需。用于替换 old_text 的文本。
  • Instance_num 可选。指定要将第几个 old_text 替换为 new_text。 如果指定了 instance_num,则只有满足要求的 old_text 被替换。 否则,文本中出现的所有 old_text 都会更改为 new_text。

例子1:
SUBSTITUTE("我们班一共有*个同学,男同学*人,女同学*人", "*", A2)
用单元格A2的内容替换字符串中的*号,通常可以用来补充固定模板中的数据。
我们班一共有30个同学,男同学30人,女同学30人
(因为没有指定替换哪一个*号,所以全部*号都被替换)

例子2
SUBSTITUTE("我们班一共有*个同学,男同学*人,女同学*人", "*", A3,2)
替换字符串中的第2个*号
我们班一共有*个同学,男同学16人,女同学*人

REPLACE和REPLACEB

根据指定位置的字符数,将部分字符串替换为不同的字符串。
REPLACE(old_text, start_num, num_chars, new_text)
REPLACEB(old_text, start_num, num_bytes, new_text)

  • 注意:这两个函数基本一致,只是在处理非英文字符的时候计算字符串的数量上不一致,国内可以一直使用replace。出错的情况下才需要考虑使用replaceb。

REPLACE 和 REPLACEB 函数语法具有下列参数:

  • old_text 必需。 要替换其部分字符的文本。
  • start_num 必需。 old_text 中要替换为 new_text 的字符位置。
  • num_chars 必需。 old_text 中希望 REPLACE 使用 new_text 来进行替换的字符数。
  • Num_bytes 必需。 old_text 中希望 REPLACEB 使用 new_text 来进行替换的字节数。
  • new_text 必需。 将替换 old_text 中字符的文本。

例子:
REPLACE("abcdefg",2,3, "+") ,将从第2个字符开始,连续3个字符bcd整体串替换为+。 a+efg

TEXT

TEXT(待格式化内容, "显示样式")

描述:将数据按指定方式展示。
例子1:
text(now(),"yyy年mm月dd日"),显示当前日期 2022年11月01日

例子2:
text(123,"00000"),显示5位数字,不足补0 00123 。这里如果超过5位数将显示为全部数字,并不是只显示5位。

例子3:
text(123.123,"# ?/?"),显示数字为几又几分之几,四舍五入: 123 1/8
这里#号后面有一个空格,问号代表分数的位数。
如果不加空格 text(123.123,"#?/?") 显示为几分之几格式: 895/8

注意:这个函数经常不单独使用,可以配合其它函数使用效果更好。

count(区域)

描述:计算所选范围内的数字格式的单元格数量,仅仅计算数字格式,文本格式的不可以计算

counta(区域)

描述:计算区域内非空单元格数量,即对区域内所有的文本,数值以及逻辑值进行统计。

countblank(区域)

描述:计算所选区域内空单元格的数量。

VLOOKUP基础用法
VLOOKUP函数是常用的一个内容查找函数,用于通过某一条件查询数据源中需要的内容。

语法:=VLOOKUP(查询值,数据源,显示序列,匹配参数)

以通过代码查询材料名称为例,录入公式:

=VLOOKUP(F2,B1:D9,2,0)
01.png

大家会发现,VLOOKUP函数的前三个参数都是一一关联的。

查找依据决定查找范围的起点;

而查找范围的选择又决定了显示序列。

模糊匹配
VLOOKUP函数第四个参数有两种:FALSE/0为精确查找,TRUE/1为模糊匹配,在工作中大部分场景下所应用的都是精确查找,那什么情况下会用到模糊匹配呢?

先来看应用场景,根据不同金额计算提成比例:
02.png

按照以往的思维模式,大家可能会选择通过IF函数的多层嵌套来实现比例的计算:

=IF(B2>=20000,12%,IF(B2>=15000,9%,IF(B2>=10000,7%,IF(B2>=5000,5%,3%))))*B2

是不是括号都不太能数的清?而一旦提成比例选项越多,公式就越复杂,这时VLOOKUP函数的模糊匹配就能完美解决区间匹配的问题!

来看第四个参数的区别:

1、FALSE/0:精确查找,必须有一个对应的值存在,否则显示#N/A
03.png

2、TRUE/1:模糊匹配,如果找不到精确匹配值,则返回小于查询值的最大数值
04.png

因此,VLOOKUP函数的模糊匹配可以实现区间查找,在一定程度上可以替代IF函数的多层嵌套。
05.gif

注意:由于模糊匹配时,如果找不到精确匹配值则返回小于查询值的最大数值,因此要求数据源第一列中的值必须以升序排序。

原文链接:https://blog.csdn.net/happy_leon/article/details/80919069

特别提醒,是lookup函数,不是vlookup函数!

1、普通正向查询

公式:=LOOKUP(1,0/($B$2:$B$7=A11),$D$2:$D$7)

万能套用:=lookup(1,0/(查找值=查找列),结果列)
01.jpeg

2、逆向查询

公式:=LOOKUP(1,0/(A11=$B$2:$B$7),$A$2:$A$7)

其实LOOKUP函数不分正逆向,万能套用是一样的

万能套用:=lookup(1,0/(查找值=查找列),结果列)
02.jpeg

3、多条件查询

公式:=LOOKUP(1,0/((B1:B8=A12)*(D1:D8=B12)),A1:A8)

万能套用:=lookup(1,0/(条件1=查找列1)*(条件2=查找列2),结果列)
03.jpeg

4、模糊查找匹配

公式:=LOOKUP(B2,$G$2:$G$7,$F$2:$F$7)

万能套用:=lookup(查找值,查找列,结果列)
04.jpeg

5、查找最后一条记录

公式:=LOOKUP(1,0/($B$2:$B$10=A14),$C$2:$C$10)

万能套用:=lookup(1,0/(查找值=查找列),结果列)
05.jpeg

6、提取末尾的数字

公式:=LOOKUP(9^9,RIGHT(A2,ROW($1:$6))*1)

万能套用:=lookup(9^9,right(查找值,row(1:最大数字是几位))*1)
06.jpeg

7、提取开头的数字

公式:=LOOKUP(9^9,LEFT(A6,ROW($1:$6))*1)

万能套用:=lookup(9^9,left(查找值,row(1:最大数字是几位))*1)
07.jpeg

8、提取中间的数字

公式:=LOOKUP(9^9,MID(A8,MATCH(1,MID(A8,ROW(1:6),1)^0,0),ROW(1:6))*1)

输入完按CTRL+SHIFT+ENTER

万能套用的公式是:A8是查找值,6是最大位数,往里面套用

9、查找一列中最后一个

查找最后一个文本:LOOKUP("座",A:A)

查找最后一个数字:LOOKUP(9E+307,A:A)

查找最后一个文本或数字:LOOKUP(1,0/(A:A<>""),A:A)

万能套用:将A:A改成需要查找的列
08.jpeg

10、通过简称查找全称:

公式:=LOOKUP(9^9,FIND(A9,$A$1:$A$5),$B$1:$B$5)

万能套用:=lookup(9^9,find(查找值,查找列),结果列)
09.jpeg

11、通过全称查找简称

公式:=LOOKUP(9^9,FIND($A$1:$A$5,A9),$B$1:$B$5)

万能套用:=lookup(9^9,find(查找列,查找值),结果列)
10.jpeg

能理解就好,如果不能理解的话,那就收藏,下次碰到类似的要求的,直接套用公式即可。
本文转自百度百家号:Excel自学成才