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

Question

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

excellocationip-addressvlookupipv4

7

我有一个包含约5000个用户事件的电子表格，与IP地址相关，我正在尝试仅使用Excel公式使用IP地址确定位置。我的日志中的IP地址结构为"点分十进制"。

我想使用VLOOKUP来解决这个问题，所以我下载了WEBNet77的IPV4 IpToCountry数据库，这似乎是一个全面和最新的资源（是否有更好的资源可用于此目的？）。该数据库包括约140K行IP范围，工作表的结构如下：

IP address lookup

Colum标签A到G分别为：IP FROM，IP TO，REGISTRY，ASSIGNED，CTRY，CNTRY，COUNTRY。

请注意，有一个问题：A到B列表示IP范围，但它们处于“长格式”（一开始我没有意识到这一点）。因此，在将这些值用作参考之前，我必须转换我的点分十进制。然后，我想返回日志中每个IP的国家（列G）。

有什么帮助吗？

如果您认为在Excel中查找IP> Country有更好的方法（可能使用Web即http://api.hostip.info/flag.php?ip=xxx.xxx.xxx.xxx），请告诉我。

- samthebrand

请注意：在1993年已经淘汰了分类路由（A、B和C类网络）。现在的IP地址块不再是基于分类的。 - Sander Steffann

@SanderSteffann 感谢您的提醒。我已相应更新了问题。 - samthebrand

2个回答

1

我也需要从我们网站访问者的IP地址中找到国家名称，以便进行一些数据分析。为此，我使用了一些网络上的批量转换工具，比如：

这些工具很好，但一次只能查找最多500个项目。因此，您可以将数据分批处理。(我猜他们有付费服务，允许一次查找更多的项目，但我没有尝试过)

我是一位Excel专家。所以我认为应该有一种简单的方法(某种模式或公式)将这些范围转换为国家。并建立了一个供我们内部使用的工具。最近决定发布它，您可以在这里查看： IP to Country Converter Excel Template 这是一个付费工具，所以我不想在这里推销它。但是你可以自己建立一个：

基本上，它运行一个VBA宏将IP地址转换为数字值，并根据国家范围找到合适的匹配项。如果您熟悉VBA，循环大致如下：

Dim dta() As Variant
dta() = Range("IP_List").Value

Dim arr() As Variant
Dim arr_v6() As Variant

arr() = Range("IP_v4").Value
arr_v6() = Range("IP_v6").Value

'generate IPV4 according to pattern 1.2.3.4 = 4 + (3 * 256) + (2 * 256 * 256) + (1 * 256 * 256 * 256)

For i = 1 To UBound(dta)

If InStr(1, dta(i, 1), ".") > 0 Then
spl = Split(dta(i, 1), ".") 'split IP in 4 integers

ip_converted = spl(3) + (spl(2) * 256) + (spl(1) * 256 * 256) + (spl(0) * 256 * 256 * 256)

'loop through array and find the country

rw = 0
rw = Application.WorksheetFunction.Match(ip_converted, Range("IP_v4"), 1)
If rw > 0 Then
dta(i, 2) = Range("V4_Country").Cells(rw, 1)
GoTo skip:
End If

这真的简化了我的日常生活。所以我建议使用服务或类似模板。

- Onur

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Brad · Accepted Answer

您可以使用此函数将四段式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 FROM
IP TO
REGISTRY
ASSIGNED
CTRY
CNTRY
COUNTRY

另外，您可以使用公式将点分十进制IP转换为其组成部分，但这有点麻烦。这里是前两个部分（我的IP在I5中）。

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

您可以看到，将点分十进制IP地址转换为IP地址格式可能有些麻烦。更简单的方法是使用上面解释的VBA方法，或者使用“文本到列”（在功能区中：数据>数据工具>文本到列>分隔符>下一步>其他：“。”>下一步>完成）。然后编写一个公式将您的拆分点分十进制IP转换为参考数据库中的IP格式。这个公式是有效的： =sumproduct(J5:M5, 256^{3,2,1,0}) 请记得用您的列范围替换上面的J5:M5。然后运行MATCH/INDEX来匹配新值。