☰

在 Excel 文档中查找子网覆盖的 IP

tag-icon • tag-icon microsoft-excel worksheet-function ip
在 Excel 文档中查找子网覆盖的 IP

我有一个巨大的 excel 文档，其中包含 IP 列表，但也有包含子网的条目。例如，我可能会看到“IP”“/16”，前两个八位字节将是子网 IP 的前两个八位字节。我想知道是否有任何方法可以使用 excel 检查 IP 的前两个八位字节是否与具有 /16 子网的行之一匹配。基本上我想知道是否有办法找到相互覆盖的条目。例如 192.168.1.1 192.168.0.0 /16，192.168.1.1 将以红色突出显示或其他方式。我知道你会使用 if，或者至少我认为是这样，但我不知道如何拆分两个八位字节并进行比较，还要考虑到需要与之比较的那个必须在同一行中有一个“/16”。任何帮助都将不胜感激，如果这毫无意义，请发表评论，我会尽力澄清。谢谢！

答案1

=LEFT(A2,IFERROR(FIND("。",SUBSTITUTE(TRIM(SUBSTITUTE(A2,"。," ",1)),"。," ",1)),0))

答案2

我认为最简单的方法是先将 IP 拆分成八位字节。然后您可以使用连接重新连接八位字节 1 和 2。然后您可以使用条件格式、vlookup 或任何您喜欢的方法来比较值。

我正在寻找一些关于为类似的宠物项目操纵 IP 的指导，并发现下面的文章非常有用。

http://chentiangemalc.wordpress.com/2011/02/03/geeky-excel-formulas-subnet-math-group-by-subnet-using-built-in-excel-2010-formulas/

我发现第二个八位字节的公式有点错误，所以我重新修改了它。

# Return Octet 1 of the IP in Cell A2
=LEFT(A2,FIND(".",A2)-1)

# Return Octet 2 of the IP in Cell A2
=LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)

# Return Octet 3 of the IP in Cell A2
=LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)

# Return Octet 4 of the IP in Cell A2
=RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))))

答案3

稍微引用一​​下 David Kennedy 的回答，假设子网为 A2 格式，格式为 192.168.1.1/30，并且仅适用于 /32 到 /24 子网

First Octet   B2: =LEFT(A2,FIND(".",A2)-1)
Second Octet  C2: =LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)
Third Octet   D2: =LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)
Fourth Octet  E2: =LEFT(RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2)))))))),FIND("/",RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2)))))))))-1)
End of subnet F2: =E2+(2^(32-RIGHT(A2,LEN(A2)-FIND("/",A2))))-1
int version of first IP of subnet G2: =VALUE(CONCAT(TEXT(B2,0),TEXT(C2,"000"),TEXT(D2,"000"),TEXT(E2,"000")))
int version of last IP of subnet H2: =VALUE(CONCAT(TEXT(B2,0),TEXT(C2,"000"),TEXT(D2,"000"),TEXT(F2,"000")))

之后，您可以比较开头以查看它是否属于列出的子网之一，最简单的方法是将以下 A4 作为您的 IP，您要检查它是否在格式为 192.168.1.3 的子网中：

int of ip B4 : =VALUE(CONCATENATE(TEXT(MID(A4,1,FIND(".",A4,1)-1),"000"),TEXT(MID(A4,FIND(".",A4,1)+1,FIND(".",A4,1+FIND(".",A4,1))-FIND(".",A4,1)-1),"000"),TEXT(MID(A4,FIND(".",A4,1+FIND(".",A4,1))+1,FIND(".",A4,1+FIND(".",A4,1+FIND(".",A4,1)))-FIND(".",A4,1+FIND(".",A4,1))-1),"000"),TEXT(MID(A4,FIND(".",A4,1+FIND(".",A4,1+FIND(".",A4,1)))+1,FIND(".",A4,1+FIND(".",A4,1+FIND(".",A4,1)))-FIND(".",A4,1+FIND(".",A4,1))+1),"000")))
If in subnet C4: =AND(B4>=G$2,B4<=H$2)

在这个特定的情况下，我会检查第一个和最后一个列出的 IP，看看它是否在八位字节内

192.168.1.1/30 192 168 1 1 4 192168001001 192168001004
192.168.1.3 192168001003 真的
192.168.1.5 192168001005 错误的

答案4

对第四个八位字节尝试此操作。TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",15)),15))

相关内容