首页 > Excel专区 > Excel教程 >

Excel公式技巧之1/17和其他全数字(pandigitals)

Excel教程 2021-12-27 21:21:10

这篇文章介绍的技巧很有趣,不知道读完后,有没有感兴趣的朋友能再贡献出几个全数字(pandigitals)来。

技巧来源于使用公式从由数字字母组成的字符串末尾返回数字,例如在单元格A1中有下面的字符串:

ABCDEF123456

使用公式:

=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&”0123456789″)),LEN(A1))

将返回字符串末尾的数字123456。

我们已经知道,在公式中,将字符串“0123456789”与单元格A1中的字符串联接,以避免MIN函数返回错误,因为如果在单元格A1中没有包含从0-9中的所有数字,那么从FIND函数中返回的结果将包含错误值,而MIN函数不能忽略错误值,这样会导致公式的结果是错误值。

首先在单元格A1中的字符串末尾添加“0123456789”,确保要查找的字符串中现在至少包含1个0-9中的每个数字(这样,从FIND返回的所有10个值都是非错误值),同时通过将这个由0-9这10个数字组成的字符串放在A1的末尾以确保不会影响MIN函数所需的输出。

下面的公式也可以得到相同的结果:

=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&7^18)),LEN(A1))

这里使用了7^18来代替添加字符串“0123456789”。究其原因,7^18的结果为1628413597910450,包含0-9中的每个数字,因此可以达到与前面给出的公式相同的效果。

其实,还可以使用5^19来代替字符串“0123456789”,原因同上。那么,还能不能找到运算后的结果是这些全数字(pandigital)的计算式呢?

当然,这样的操作数不胜数,但需要注意的是,操作中的字符总数应尽可能短,并且在Excel中是可复制的,下展示一些这样的运算式/数字列表。

列C中检查这些生成的数字中是否包含0-9这10个数字,在单元格C2中的公式为:

=SUMPRODUCT(0+ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},B2)))

可以从中选择一个包含0-9这10个数字且最小的/最短的作为字符串“0123456789”的替换。


标签: excel函数excel函数公式excel表格制作excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.