excel拖动排列步骤如下：1.我在EXCEL中输入以下数字，如图：2.现在我要想复制“1”，则拖动单元格A1，但此该你会发现，在下拖动下方有个方框，如图：3.上步出现的情况便是以顺序的方式出现，但你只需要点动那个右下方的方框，选择“复制单元格”即可......
Excel公式技巧：从字符串中提取数字——数字位于字符串开头
Excel教程
2022-01-12 21:49:37
Excel公式技巧：从字符串中提取数字——数字位于字符串开头
本文主要研究从字符串开头提取数字的技术：
1. 这些数字是连续的
2. 这些连续的数字位于字符串的开头
3. 想要的结果是将这些连续的数字返回到单个单元格
对于下面研究的每种解决方案，我们需要在两种不同的情况下测试其健全性：
1. 字符串中除开头外其他地方没有数字的情况，例如123ABC。
2. 字符串中除开头外其他地方也有数字，要么在末尾，要么在中间，例如123ABC456或123ABC456DEF。
无论字符串中除开头外是否还有其他数字，将要研究的某些解决方案都可以很好地工作，但有些解决方案则存在局限性。在分析每种解决方案时，将会明确说明。
LOOKUP与LEFT
公式1：
=-LOOKUP(1,-LEFT(A1,ROW(INDIRECT(“1:”& LEN(A1)))))
如果单元格A1中的内容为“123ABC”，那么上述公式1返回“123”。其解析过程如下：
ROW(INDIRECT(“1:” & LEN(A1)))
生成一个由1至单元格A1中字符串长度数的整数组成的单列数组：
{1;2;3;4;5;6}
这样，公式1变为：
=-LOOKUP(1,-LEFT(A1,{1;2;3;4;5;6}))
由于LOOKUP强制生成数组，因此LEFT在这里不是返回单个值，而是返回由六个值组成的数组，每个值对应于将LEFT的num_chars参数指定为1、2、3、4、5、6应用于A1中的字符串，即：
=-LOOKUP(1,-{“1″;”12″;”123″;”123A”;”123AB”;”123ABC”})
其中的数组乘以-1，得到：
=-LOOKUP(1,{-1;-12;-123;#VALUE!; #VALUE!; #VALUE!})
对于LOOKUP来说，如果在lookup_vector中未找到lookup_value，并且假设lookup_vector中没有大于lookup_value的值，则该函数将从lookup_vector中返回最后一个值（本例中为数字）。该函数还会忽略lookup_vector中的任何错误值。这就是在开始给lookup_vector（通过创建一个由负数、零（如果期望提取的字符串以0开头例如0123ABC）或错误值组成的数组）中的值添加负号的原因，可以确保lookup_value为1永远是一个充分而合法的选择。在这里，由于在lookup_vector中找不到1，公式返回数组中最后一个数值，即-123。
当然，这绝对不是处理这种公式结构的唯一方法，只要确保选择的lookup_value的值足够大。其实这并不困难，让lookup_value使用所谓的“大数”（即9.99999999999999E+307，这是Excel中允许的最大正数），确保这种公式构造有效。或者，有些人喜欢仅取“非常大”的值，例如10^10（其好处是看起来不像“大数”那么笨拙）。
由于公式1中LOOKUP函数返回-123，因此在前面添加一个负号使其变为想要的123。
但是，公式1并不可靠。因为某些字符串可能会返回其他结果，例如单元格中的值为12JUN，那么：
=-LOOKUP(1,-LEFT(A1,ROW(INDIRECT(“1:”& LEN(A1)))))
转换为：
=-LOOKUP(1,-LEFT(A1,{1;2;3;4;5}))
转换为：
=-LOOKUP(0,-{“1″;”12″;”12J”;”12JU”;”12JUN”})
此时，会转换为：
=-LOOKUP(0,{-1;-12;#VALUE!;#VALUE!;-43994})
这是由于在强制将“12JUN”转换成数字时，Excel认为其是日期“2020-6-12”，因此将其转换为相应的序列数字。此时，LOOKUP函数返回-43994。
当然，这不是唯一会出现这种情况的字符串，实际上，任何可以被Excel解释为日期的字母数字都会如此，例如30SEP、01FEB等，这也会导致不正确的结果。
此外，公式1对于诸如123E3等形式的字符串也无效，其结果将是123000。因为在通常情况下，将123E3输入单元格后，Excel会自动将其转换成科学计数格式。
LEFT与COUNT
公式2：
=0+LEFT(A1,COUNT(0+MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))
这是一个数组公式。
仍以单元格A1中的数据是“123ABC”为例。公式2可以转换为：
=0+LEFT(A1,COUNT(0+MID(A1,{1;2;3;4;5;6},1)))
转换为：
=0+LEFT(A1,COUNT(0+{“1″;”2″;”3″;”A”;”B”;”C”}))
转换为：
=0+LEFT(A1,COUNT({1;2;3;#VALUE!;#VALUE!;#VALUE!}))
COUNT函数忽略错误值，得到：
=0+LEFT(A1,3)
结果为：
123
下面，尝试一个公式2可不可以处理除字符串开头外其他位置还存在数字的情形，例如如果单元格A1中的数据是“123ABC45”，那么公式2可以转换为：
=0+LEFT(A1,COUNT(0+MID(A1,{1;2;3;4;5;6;7;8},1)))
转换为：
=0+LEFT(A1,COUNT(0+{“1″;”2″;”3″;”A”;”B”;”C”;”4″;”5″}))
转换为：
=0+LEFT(A1,COUNT({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}))
转换为：
=0+LEFT(A1,5)
转换为：
=0+”123AB”
结果为：
#VALUE!
原因是字符串的末尾有其他数字，因此COUNT函数统计的数字个数大于字符串开头的数字个数，这样LEFT取值仍是字母数字混合的字符串。
LEFT、MATCH与ISNUMBER
公式3：
=0+LEFT(A1,MATCH(FALSE,ISNUMBER(0+MID(A1,ROW(INDIRECT(“1:”& LEN(A1))),1)),0)-1)
这是一个数组公式。
仍以单元格A1中的数据为“123ABC”，公式3可转换为：
=0+LEFT(A1,MATCH(FALSE,ISNUMBER({1;2;3;#VALUE!;#VALUE!;#VALUE!}),0)-1)
对于ISNUMBER函数来说，传递给它的如果是错误值则返回FALSE，因此上述公式可转换为：
=0+LEFT(A1,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)
转换为：
=0+LEFT(A1,4-1)
转换为：
=0+LEFT(A1,3)
结果为：
123
在公式3中，MATCH/ISNUMBER组合确保字符串中除开头以外的数字不会影响最终的结果。例如如果单元格A1中的数据是“123ABC45”，那么公式3可以转换为：
=0+LEFT(A1,MATCH(FALSE,ISNUMBER({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}),0)-1)
转换为：
=0+LEFT(A1,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE},0)-1)
转换为：
=0+LEFT(A1,4-1)
转换为：
=0+LEFT(A1,3)
结果为：
123
LEFT、MATCH与ISERR
与公式3的构造一致，唯一的不同是使用ISERR函数代替了ISNUMBER函数，并强制返回由数字组成的数组。
公式4：
=0+LEFT(A1,MATCH(1,0+ISERR(0+MID(A1,ROW(INDIRECT(“1:”& LEN(A1))),1)),0)-1)
这是一个数组公式。
仍以单元格A1中的数据为“123ABC”，公式4可转换为：
=0+LEFT(A1,MATCH(1,0+ISERR({1;2;3;#VALUE!;#VALUE!;#VALUE!;4;5}),0)-1)
转换为：
=0+LEFT(A1,MATCH(1,0+{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE},0)-1)
转换为：
=0+LEFT(A1,MATCH(1,{0;0;0;1;1;1;0;0},0)-1)
转换为：
=0+LEFT(A1,4-1)
转换为：
=0+LEFT(A1,3)
结果为：
123
与公式3一样，字符串中除开头有数字外其它部位存在数字时不影响结果。
标签： excel公式怎么用excel函数公式Excel常用函数excel教程
相关文章