excel|将 Excel 中的数字和文本分成两列,必须弄懂 lookup 函数的查找原理

excel|将 Excel 中的数字和文本分成两列,必须弄懂 lookup 函数的查找原理

文章图片

excel|将 Excel 中的数字和文本分成两列,必须弄懂 lookup 函数的查找原理

文章图片

excel|将 Excel 中的数字和文本分成两列,必须弄懂 lookup 函数的查找原理

文章图片

excel|将 Excel 中的数字和文本分成两列,必须弄懂 lookup 函数的查找原理

文章图片

excel|将 Excel 中的数字和文本分成两列,必须弄懂 lookup 函数的查找原理

文章图片

excel|将 Excel 中的数字和文本分成两列,必须弄懂 lookup 函数的查找原理

文章图片


【excel|将 Excel 中的数字和文本分成两列,必须弄懂 lookup 函数的查找原理】这是网友的一个求助题 , 有一列数据表 , 单元格中可能会有数字和文本同时出现的情况 , 也可能没有 。


如果有 , 数字一定在文本前 , 数字和字符的数量都不固定 。
如何能快速将数字和文本分开成两列?
案例:将下图 1 中的数字和姓名拆分后分别放在不同的两列中 , 效果如下图 2 所示 。


解决方案:
1. 在 B2 单元格中输入以下公式 --> 下拉复制公式:

=IFERROR(-LOOKUP(1-LEFT(A2ROW($1:$20)))\"\")
公式释义:

  • LEFT(A2ROW($1:$20)):从左至右依次提取 A2 单元格中的第 1 至 20 位;
  • -...;在上述字符前加个负号 , 相当于乘以 -1 , 将数字变成了负数 , 文本则显示错误值;
  • LOOKUP(1...)):
    • lookup 函数原理是采用二分法在查找区域中匹配值;如果找不到 , 则会与小于或等于“查找值”的最大值进行匹配;
    • lookup 的查找区域必须按升序排序 , 如果没排 , lookup 也会认为已经按升序排好了 , 并且坚持二分法查找;
    • 由于用的是二分法 , 所以区域顺序混乱的时候 , lookup 并不一定会返回小于或等于“查找值”的最大值 , 而是坚持按二分法查找完“按规则应该查找的位置”(而不是人类以为的查找完所有的值) , 然后返回最近一个符合条件的值;
    • 上述 lookup 的查找原理非常重要 , 弄懂了 , 就知道在本案例中 , 由于一直找不到完全跟 1 匹配的值 , lookup 就会按二分法找到并返回数值中最后一个负数;
  • -():将查找结果再乘以 -1 , 就变回了正数 , 这个结果就是 A2 单元格中的所有数字;
  • iferror(\"\"):如果出错 , 就表示文本前面没有数字 , 则返回空值




我们通过分解步骤来帮助大家直观地理解一下各个函数的结果 。
2. 在公式栏中选中 left 公式部分 --> 按 F9

公式的结果是一组数组 , 分别是从 A2 单元格的第一个字符开始提取 1 位、提取 2 位……直至提取 20 位的结果 。 因为 A2 单元格中一共只有 5 位字符 , 所以数组中自第 5 个元素开始 , 往后的结果都是一样的 。


3. 选中 lookup 的整个公式部分 --> 按 F9

lookup 函数就找到了数组中的最后一个负数 。

4. 在 C2 单元格中输入以下公式 --> 下拉复制公式:
=RIGHT(A2LEN(A2)-LEN(B2))
公式释义:
  • 从右至左提取 A2 单元格中的每一位 , 提取的位数为 A2 单元格的总字符数减去 B2 单元格的总字符数 , 即数字位数;从而提取出文本内容