EXCEL将网段地址分解成明细地址

如图，将图1中四个网段地址分解成图2中的明细地址

图1 网段地址

 图2 明细地址

一、将网段地址分解

图3 分解网段地址

网络地址公式为 B2=left(A2,len(A2)-3)

掩码位数公式为 C2=right(A2,2)

主机位数公式为 D2=32-C2

主机数公式为 E2=2^D2-1

整理网络地址和主机数，得到图4

 图4 网络地址和主机数

 

二、利用Power Query进行分行

选中A2:B5，在Excel中依次点击数据-来自表格/区域-添加列-自定义列，在自定义列公式中输入={1..[主机数]}，点击确定，自定义下拉扩展到新行，关闭编辑器，点保留

 图5 Power Query（1）

 图6 Power Query（2）

 图7 Power Query（3）

三、将主机号加到网络地址上

明细地址公式为D4=LEFT(A2,FIND("X",SUBSTITUTE(A2,".","X",3))-1)&"."&RIGHT(A2,LEN(A2)-FIND("X",SUBSTITUTE(A2,".","X",3)))+C2

图8 计算明细地址

大功告成！