DIY 一个 Excel 版的子网计算器

大家好，我是小弗。做网络规划时，需要用到子网划分；排查网络故障时，需要识别网段地址。处理步骤都是根据 IP 地址/网段和掩码，推算出子网地址、可用 IP 地址数量等信息。平时，我会使用子网计算器这种小工具，快速又方便，贼好用。但是，现在网上这种小工具软件，格式五花八门，还会携带广告，用着不顺手。而在线版的子网计算器，只能在有网络的情况下使用，而我通常是去处理网络故障，处理完故障才能上网是常有的事情。索性，自己做一个 Excel 版的子网计算器，按照自己的使用习惯进行设计，按照自己的需求生成数据，DIY 自己的小工具！

什么是子网划分？

为了提高 IP 地址的使用率，把一个网络划分成多个子网。从 IP 地址主机位的最左边开始，把主机位划入网络位，得到多个子网地址。主机位变成网络位的部分叫做子网号，划分后的子网数量等于 2 的 N 次方，N 等于子网号的位数。

Demo

没做过产品和研发的我，觉得做个软件，先要有 Demo 。于是按 IP 地址计算逻辑，梳理出需要的数据类型。首先，输入 IP 地址和掩码，得到子网地址，再推算出广播地址。可用 IP 地址范围是子网地址 +1 ，广播地址 -1 。根据 IP 地址的前几位，判断地址类型。根据地址类型，推算出子网位数和子网数量、主机位数和主机数量。

同时，IP 地址书写是十进制数，计算是按二进制数。还要进行进制数转换，方便查看和计算。

计算函数

最难的地方来了，那就是计算出来各个数据的公式。

首先，输入的数据是 IP 地址，一共是四个十进制数，设置四个单元格，分别输入四个十进制数，自动转换成二进数表示，可以使用 DEC2BIN 函数转换。同时，也要对输入数值进行限制，范围是 0 ~ 255 ，可在数据验证视图设置。

然后是输入网络掩码。网络掩码，有两种表示方法，一种是跟 IP 地址一样，四个十进制数，另一种就是直接标记掩码位数，一个整数即可。输入掩码位数，更方便，也更简单，当然实现也更复杂。掩码位数转换成十进制的掩码地址，可以使用 IFS 或 IF 函数，根据不同的数值，输出不同的掩码地址。掩码位数的取值范围是 1 ~ 32（子网划分，值取 0 没有意义，已排除），要列举 32 种可能性，函数有点长。为了缩短函数长度，使用了 IF + MOD 函数。一共四个单元格，第一个十进制数的公式是：IF(掩码位数>=8,255,(256-2^(8-MOD(F3,8)))) 。十进制转换成二进制，也是使用 DEC2BIN 函数。

自动计算子网地址，子网地址是 IP 地址和掩码地址 AND 位运算的结果，可直接使用位运算函数 BITAND ，得到十进制数。

自动计算广播地址，广播地址的网络号跟子网地址相同，主机号全是 1 。可用 IP 地址和掩码地址的反码进行 OR 位运算，也就是 BITOR(IP地址,255-掩码地址) ，得到广播地址。

自动计算可用地址范围，网段内的网段地址（即子网地址）和广播地址是不能分配使用的，其余地址都可以使用，即子网地址 +1 和广播地址 -1 范围内的地址。

自动计算地址类型，使用 LEFT 函数获取 IP地址前几位的值，再使用 IFS 函数列举五类地址。

自动计算子网位数，可使用 MOD 函数，计算掩码位数除以 8 的余数，即子网位数。还要排除无效的范围。

自动计算最大子网数（即子网数量），2 的子网位数的次方即最大子网数，也有排除无效的范围。

自动计算主机位数，即 32 - 子网位数。

自动计算最大主机数（即主机数量），2 的子网位数的次方减 2 ，即最大主机数。

定版

最后发现，只在计算地址类型时，用到了 IP 地址的二进制数，其余函数都是用的十进制数。于是把十进制数和二进制数整合到一起，看着也美观一些。为了防止误操作，只能输入 IP 地址和掩码位数，其余单元格禁止编辑。如果想解除工作表保护功能，自己修改参数或界面等，输入解除密码 fox ，就可以自由编辑任何单元格了。

获取

有兴趣的同学，可以按照文章的内容，自己动手做做看。用自己喜欢的方式，或者更好的公式，做属于自己的子网计算器。如果想白嫖，也没问题。获取方式在下面链接的末尾处：

Excel 版的子网计算器（可下载）

只需动动手指，非常简单。下班后，统一发给大家。希望多多支持，一起精通网络，成为技术高手！