excel - 如何在Excel中根据IP地址查找位置

Question

我有一个包含大约 5000 个与 IP 相关的用户事件的电子表格，并且我正在尝试仅使用 Excel 公式使用 IP 来确定位置。我的日志中的 IP 结构为 "dotted quads" (十进制表示法)。

我认为 VLOOKUP 是正确的选择，所以我下载了 WEBNet77's IPV4 IpToCountry database ，这似乎是一个全面且最新的资源(为此目的是否有更好的资源？)。该数据库包含大约 140K 行 IP 范围，表结构如下:

列标签 A - G 分别为:IP FROM、IP TO、REGISTRY、ASSIGNED、CTRY、CNTRY、COUNTRY。

注意，一个问题:A 到 B 列代表一系列 IP，但它们是“长格式”(我一开始没有意识到这一点)。因此，在使用这些值作为引用之前，我必须转换我的虚线四边形。然后，我想返回日志中每个 IP 的国家/地区(G 列)。

这里有什么帮助吗？

如果您认为有更好的方法在 Excel 中查找 IP > 国家/地区(可能使用网络，即 http://api.hostip.info/flag.php?ip=xxx.xxx.xxx。 xxx)，请告诉我。

Answer 1

您可以使用此函数将您的四 IP 转换为数字

Function GetNumericIP(quadIP As String) As Long

    Dim sections
    On Error Resume Next
    sections = Split(quadIP, ".")

    GetNumericIP = sections(3) * 256 ^ 0 + sections(2) * 256 ^ 1 + sections(1) * 256 ^ 2 + sections(0) * 256 ^ 3
End Function

您可以使用 MATCH/INDEX 来获取您的位置。诀窍是让您的 IP TO 列按升序排序。 Match 函数将返回范围内最后一个值小于给定值的行索引。

57.182.90.111  your input
968252015      =GetNumericIP(A1)
France         =INDEX(Country,MATCH(J6,IPTO,1)+1)
  (Or alternatively) =INDEX(Country,MATCH(J6,IPFROM,1))

请注意，我必须导入 CSV(而不仅仅是从外部引用它)，并将列转换为命名范围(Country、IPTO)。对于其他人的信息，问题中链接的 CSV 的列名称为:

• IP 来自
• IP 至
• 注册表
• 已分配
• CTRY
• CNTRY
• 国家/地区

或者，您可以仅使用公式将点分四元 IP 转换为其组成部分，尽管这有点麻烦。这是前两部分(我的 IP 在 I5 中)

=LEFT(I5,FIND(".",I5)-1)
=LEFT(RIGHT(I5,LEN(I5)-I10),SEARCH(".",RIGHT(I5,LEN(I5)-I10))-1)

你可以看到...有点痛苦。更简单:使用上面解释的 VBA 方法，或使用文本到列(在功能区上:数据 > 数据工具 > 文本到列 > 分隔 > 下一步 > 其他:“.” > 下一步 > 完成)。然后编写一个公式，将分割的点分四组 IP 转换为引用数据库中的 IP 格式。这个公式有效:

=sumproduct(J5:M5, 256^{3,2,1,0})

请记住在上面的 J5:M5 的列范围中进行子化。然后针对新值运行 MATCH/INDEX。