Task6：文本函数&查找函数

一 文本函数

1 Text函数

• 作用：将数值转换为指定格式的文本

• 语法：TEXT(value,format_text)
  （1）转换为大写
  消费日期转换为大写
  =TEXT(A2,”[DBNUM1]yyyy年m月d日”)
  消费金额转成大写
  =TEXT(C2,”[DBNUM2]”)

  DBNUM1和DBNUM2为2种常见的中文格式，一般金额用DBNUM2，日期用DBNUM1

  
  （2）日期转换为周次
  将客户的消费日期转换为周次，即星期几
  =TEXT(A2,”aaaa”)

  
  （3）取客户消费的年、月、日
  取年
  =TEXT(A2,”yyyy”) 或者 =TEXT(A2,”e”)
  取月
  =TEXT(A2,”yyyy”) 或者 =TEXT(A2,”e”)
  取日
  =TEXT(A2,”yyyy”) 或者 =TEXT(A2,”e”)

  

2 mid函数

• 格式：=MID(text,startnum,numchars)
  • test:为要提取的文本字符串
  • start_num:为文本中要提取的第一个字符串的位置
  • num_chars为提取字符串的长度

（1）提取身份证中的生日，并转换成2022年12月4日这种格式

1. 提取生日 =MID(A2,7,8)
2. 格式化的生日 =TEXT(MID(A2,7,8),”0000年00月00日”)
   

问题：这里TEXT函数格式为什么没有使用yyyy年mm月dd日 这种格式？
解答：因为MID(A2,7,8)提取出来的是文本，不是日期，因此Excel无法识别日期的年月，所以用数字格式进行代替
（2）类似的函数：LEFT，RIGHT

1. LEFT函数，以字符串左侧为起始位置，返回指定数量的字符
   【LEFT函数】=MID(text,,num_chars)
   • text:要提取的字符串或单元格引用
   • num_chars:要提取的字符数量
2. RIGHT函数，从字符串右侧首字符开始，从右向左提取指定的字符，其功能和LEFT函数完全一样，只是方向不同
   【RIGHT函数】=MID(text,num_chars)
   • text:要提取的字符串或单元格引用；
   • num_chars:要提取的字符数量

3 replace函数

• 作用：把一个文本字符串，人为指定一个位置，用定个数新字符进行替换。
• 格式：REPLACEI(oldtext,startnum,numchars,newtext)
  • old_text:需要替换的文本
  • start_num:需要替换文本的开始位置
  • num_chars:替换文本的长度
  • new_text:替换内容

（1）将电话后5位进行屏蔽

• =REPLACE(A2,11,5,”#####”)
  

---

二 查找函数

1 Vlookup

• VLOOKUP函数：
  • VLOOKUP(查找对象,查找区域,要返回的值在查找区域的第几列,查找方式)
  • 功能：跨表查找匹配数据
  • 注意：查找对象必须在查找区域的第一列；模糊查找为1，精确查找为0。
• 当查找对象不在区域的最左边时：
  • 使用IF({1,0},……)
  • 功能：分别返回IF的两个值，同时把两个结果组成一个数组。
  • 解释：公式IF({1,0},返回值1,返回值2)中，条件返回值就有1和0两个，当返回1时，结果为返回值1，当返回值为0时，结果为返回值2，也就是说：公式IF({1,0},返回值1,返回值2) = “返回值1”，“返回值2”。

  具体IF({1,0},……)用法

（1）据岗位编号，得到该员工的应发工资
=VLOOKUP(K2,A2:H50,8,0)

（2）根据岗位编号在最后一列，得到该员工的应发工资
=VLOOKUP(K2,IF({1,0},H2:H19,G2:G19),2,0)
解释：将G2:G17列与H2:H19列做成了一个临时数据组，并让这2列交换位置，这样G2:G19列就在临时数组中的第2列，因此VLOOKUP中第3个参数为2

（3）根据性别与部门信息，获得员工编号
=VLOOKUP(J2&K2,IF({1,0},C:C&D:D,A2:A20),2,0)
解释：将多个条件通过&连接成一个字符串，同时将待查找的数据也连接起来，从而通过这个连接的特殊字符来查找数据

（4）根据不完整编号进行模糊查询
=VLOOKUP(“*”&K2,A2:F20,2,0)
通配符匹配规则:

2 Xlookup

• 目前只有Office 365支持

• 和VLOOKUP区别：

  • 不用输入要返回的值区域在第几列，直接选中值返回列即可
  • 可以选中查询顺序，可以逆向查找

• 格式： =XLOOKUP(想要查找值, 想要在哪个数据区域中查找, 要返回的数据区域，, [ifnotfound], [matchmode], [searchmode])

  • ifnotfoun：，找不到结果，就返回第四参数，如果省略第四参数函数默认返回#N/A这个错误值
  • match_mode：指定匹配类型
    • 0 ，精确匹配，未找到结果，返回 #N/A。 这是默认选项。
    • -1，近似匹配，未找到结果，返回下一个较小的项。
    • 1，近似匹配，未找到结果，返回下一个较大的项。
    • 2 ，通配符匹配
  • search_mode：指定要使用的搜索模式
    • 1，从第一项开始执行搜索。 这是默认选项
    • -1，从最后一项开始执行反向搜索
    • 2，根据 lookup_array 按升序排序的二进制搜索。 如果未排序，将返回无效结果
    • -2，根据lookup_array 按降序排序的二进制搜索。 如果未排序，将返回无效结果

---

【动图来源：Datawhale】