订阅已知IP地址用excel计算网段和其他信息
来源：网络收集 点击： 时间：2024-05-17准备原始数据，如图中A列，是带有掩码的IP地址。具体计算函数请复制以下步骤中内容。
2/4B2 =LEFT(A2,FIND("@",SUBSTITUTE(A2,"/","@",1))-1)
C2 =RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))
#简单的以“/”来区分IP地址和掩码#
3/4D2 =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)))
#计算逻辑是：地址最后一位除以的商再乘以
所以这种算法只适用于掩码是24到32的情况，
各个函数就不细说了#
4/4E2=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的情况
excel网段版权声明：
1、本文系转载，版权归原作者所有，旨在传递信息，不代表看本站的观点和立场。
2、本站仅提供信息发布平台，不承担相关法律责任。
3、若侵犯您的版权或隐私，请联系本站管理员删除。
4、文章链接：http://www.1haoku.cn/art_782675.html
