Excel 怎样计算网段地址

Question

如题所述

Answer 1

已知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)

#其他一些信息的计算函数#
已知IP地址用excel计算网段和其他信息_百度经验 http://jingyan.baidu.com/article/7908e85c6d7550af481ad2d3.html本回答被网友采纳