1、准备原始数据，如图中A列，是带有掩码的IP地址。具体计算函数请复制以下步骤中内容。
2、B2 =LEFT(A2,FIND("@",SUBSTITUTE(A2,"/","@",1))-1)C2 =RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",1)))#简单的以“/”来区分IP地址和掩码#
3、D2 =LEFT(A2,FIND("@",SUBSTITUTE(A2,".","@",3)))&(QUOTIENT(MID(A2,FIND("@",SUBSTI皈其拄攥TUTE(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的情况，各个函数就不细说了#
4、E2=LEFT(A2,FIND("@",SU幞洼踉残BSTITUTE(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)#其他一些信息的计算函数#