已知IP地址用excel计算网段和其他信息

已知IP地址用excel计算网段和其他信息，可用于大批量的IP管理和规划。

前期准备工作（包括相关工具或所使用的原料等）

microsoft excel

详细的操作方法或具体步骤

准备原始数据，如图中A列，是带有掩码的IP地址。具体计算函数请复制以下步骤中内容。

B2=LEFT(A2,FIND("@",SUBSTITUTE(A2,"/","@",1))-1)

C2=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))

#简单的以“/”来区分IP地址和掩码#

D2=LEFT(A2,FIND("@",SUBSTITUTE(A2,".","@",3)))&(QUOTIENT(MID(A2,FIND("@",SUBSTITUTE(A2,".","@",3))+1,FIND("@",SUBSTITUTE(A2,"/","@",1))-FIND("@",SUBSTITUTE(A2,".","@",3))-1),2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))))*2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))))&"/"&RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))

#计算逻辑是：地址最后一位除以[2的（32-掩码）次方]的商再乘以[2的（32-掩码）次方]

所以这种算法只适用于掩码是24到32的情况，

各个函数就不细说了#

E2 =LEFT(A2,FIND("@",SUBSTITUTE(A2,".","@",3)))&(QUOTIENT(MID(A2,FIND("@",SUBSTITUTE(A2,".","@",3))+1,FIND("@",SUBSTITUTE(A2,"/","@",1))-FIND("@",SUBSTITUTE(A2,".","@",3))-1),2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))))*2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))))

F2 =LEFT(A2,FIND("@",SUBSTITUTE(A2,".","@",3)))&(QUOTIENT(MID(A2,FIND("@",SUBSTITUTE(A2,".","@",3))+1,FIND("@",SUBSTITUTE(A2,"/","@",1))-FIND("@",SUBSTITUTE(A2,".","@",3))-1),2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))))*2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1))))+1)

G2 =LEFT(A2,FIND("@",SUBSTITUTE(A2,".","@",3)))&((QUOTIENT(MID(A2,FIND("@",SUBSTITUTE(A2,".","@",3))+1,FIND("@",SUBSTITUTE(A2,"/","@",1))-FIND("@",SUBSTITUTE(A2,".","@",3))-1),2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))))+1)*2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1))))-2)

H2=LEFT(A2,FIND("@",SUBSTITUTE(A2,".","@",3)))&((QUOTIENT(MID(A2,FIND("@",SUBSTITUTE(A2,".","@",3))+1,FIND("@",SUBSTITUTE(A2,"/","@",1))-FIND("@",SUBSTITUTE(A2,".","@",3))-1),2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))))+1)*2^(32-RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1))))-1)

#其他一些信息的计算函数#

注意事项

算法只适用于掩码是24到32的情况

经验内容仅供参考，如果您需解决具体问题(尤其法律、医学等领域)，建议您详细咨询相关领域专业人士。作者声明：本教程系本人依照真实经历原创，未经许可，谢绝转载。