Excel公式技巧:十进制数转换为指定数

用公式解决问题：给定一个十进制正整数，将其转换为指定的十进制数。如下图1所示，在单元格A2中，有一个给定的十进制正整数值，在单元格B2中，有一个指定的十进制值，在示例中，它是4，在单元格C2中，有转换后的结果，在单元格D2中，公式用于检查结果是否正确。

图1

单元格C2中的公式为：

=sumproduct(mod(floor(a2/b2^(row(indirect("1:20"))-1),1),b2)*10^(row(indirect("1:20"))-1)

单元格D2中的公式为：

=sumproduct(b2^(row(indirect("1:“透镜(C2)))-1)，0中间(C2，1透镜(C2)-行(间接(“1:”透镜(C2)))，1))

下面是如何推导公式的详细说明。

对于任何二进制数，它的一般形式是：

图2。

其中，x表示十进制数，a0，a1，a2，…，an为常数。

例如，对于熟悉的十进制系统，整数173可以表示为：

(1X100) (7X10) (3X1)

你也可以写：

(1X10^2) (7X10^1) (3X10^0)

我们可以很容易地分解成这样的表达式，但是如何指导Excel这样做呢？我们可以给出什么指令，使得在给定一个值如173时，可以生成一系列返回值，即1、7、3分别对应10 2、10 1、10 0的系数？我们需要做的是想办法把173的值以某种方式转换成一百，七个十，三个一。

我们一般用以下方法来推导：

173年有个“百”。

减去一百，剩下的73个有七个十。

减去七个“十”后，剩下的三个有一个“一”。

这些都是很基本的东西。当然，我们可以简单地在Excel中生成等价的算法流程，如上。唯一的麻烦是上面算法中的每一行都依赖于前一行。也就是说，我们应该这样设定：

图3。

可能很难将上面图3所示工作表中的尝试压缩为一个公式，因为D列中的每个公式都包含对其上一行值的引用。

因此，沿着上面的公式链，如果我们想用一个公式从初始值173得到3，复合公式是：

=int(d2-(int(d2/10^2)*10^2)-(int((d2-(int(d2/10^2)*10^2))/10^1)*10^1)/10^0)

这显然不是我们想要的公式，太长了！如果要转换成二进制，可以想象一下公式会是什么样子！

幸运的是，有另一种方法可以获得每个值。还是以173为例，这次计算这个值除以100、10和1得到的结果，然后将得到的结果除以基数10得到余数：

图4

在这里，我们可以看到“Result”列中的值并不依赖于上面的每一行，这次我们可以使用下面的简单公式得到3:

=MOD(INT($A$2/10^0),10)

上述方法的数组版本就是沿着这个思路构建的。我们唯一需要确定的是要计算的数组大小，也就是在指定基数下需要的最大索引是多少？

在上面的例子中，显然需要的是“100”。因此，对于索引10，我们需要一个由三个元素组组成的数组：0、1和2。如果我们考虑10，301，444的值，我们显然需要达到10 ^ 7，因此我们的数组将由8个元素组成(从10 ^ 0到10 ^ 7)。

事实上，我决定采用一种更“懒”的方法。不是动态确定这个值，而是简单地使用20的大索引上限(至少在Excel术语中)。

当然，当我们使用10以外的基数时，这个过程没有什么不同。以本文开头给出的例子为例，即552转换为四进制数，其部分公式为：

b2^(row(indirect("1:20"))-1)

获取20个值的数组，该数组由0到4的19次方的结果组成：

{1;4;16;64;256;1024;4096;16384;65536;262144;1048576;4194304;16777216;67108864;268435456;1073741824;4294967296;17179869184;68719476736;274877906944}

然后除以552并向下舍入：

FLOOR(A2/{ 1；4;16;64;256;1024;4096;16384;65536;262144;1048576;4194304;16777216;67108864;268435456;1073741824;4294967296;17179869184;68719476736;274877906944},1)

获取：

{552;138;34;8;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

然后，求余数除以4:

MOD({ 552；138;34;8;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}，B2)

获取第一个数组：

{0;2;2;0;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

第二个数组更直接，由0到19的10次方的结果组成：

{1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000;10000000000;100000000000;1000000000000;10000000000000;100000000000000;1000000000000000;10000000000000000;100000000000000000;1000000000000000000;10000000000000000000}

因此，最终的结果是：

=SUMPRODUCT({ 0；2;2;0;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}*{1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000;10000000000;100000000000;1000000000000;10000000000000;100000000000000;1000000000000000;10000000000000000;100000000000000000;1000000000000000000;10000000000000000000})

拿到20220。

你可以检查结果是否正确。如下所示：

(2x4^4)(0x4^3)(2x4^2)(2x4^1)(0x4^0)

等于：

(2X256) (0X64) (2X16) (2X4) (0X1)

等于：

512 0 32 8 0

结果是：

552。