如何根据Excel中的IP地址查找位置 [英] How to find location based on IP address in Excel

问题描述

我有一个与IP相关联的大约5000个用户事件的电子表格，而我正在尝试使用IP来仅使用Excel公式来确定位置。我的日志中的IP被组织为点缀四边形 （十进制符号）。

我认为VLOOKUP是去这里的方式，所以我下载了 WEBNet77的IPV4 IpToCountry数据库，这似乎是一个全面和最新的资源（为此有更好的资源？）。该数据库包含大约140K行的IP范围，表格如下所示：

Colum标签A - G分别为：IP FROM，IP TO，REGISTRY，ASSIGNED，CTRY，CNTRY，COUNTRY注意，一个问题：列A到B代表IP的一个范围，但它们是长格式（我起初没有意识到这一点） 。所以，在使用这些值作为参考之前，我必须转换我的虚线四边形。然后，我想返回我的日志中的每个IP的国家（列G）。

在这里有任何帮助？

如果您认为有更好的方式在Excel中查找IP> Country（可能使用网络即 http：//api.hostip .info / flag.php？ip = xxx.xxx.xxx.xxx ），请让我知道。

解决方案

您可以使用此功能将您的四路IP转换为数字

 函数GetNumericIP（quadIP As String）As Long 
 
 Dim sections 
 On Error Resume Next 
 sections = Split（quadIP，。）
 
 GetNumericIP = sections（3）* 256 ^ 0 +部分（2）* 256 ^ 1 +部分（1）* 256 ^ 2 +部分（0）* 256 ^ 3 
结束功能
  

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

  57.182.90.111您的输入
 968252015 = GetNumericIP（A1）
法国= INDEX（国家，MATCH（J6，IPTO，1）+1）
 （或者）= INDEX（国家，MATCH（J6，IPFROM，1））
  

导入CSV（不仅仅是外部引用），而是将列转换为命名范围（ Country ， IPTO ） 。对于其他人的信息，问题链接的CSV文件的列名为：

• IP FROM


• IP TO


• REGISTRY


• 已分配


• CTRY


• CNTRY


• COUNTRY

或者，您可以只使用虚拟四核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}）

记住在 c> J5：M5 的列范围内。然后对新值运行MATCH / INDEX。

I've got a spreadsheet of about 5000 user events associated with IPs, and I'm trying to use IP to determine location using just an Excel formula. The IPs in my log are structured as "dotted quads" (decimal notation).

I figured VLOOKUP is the way to go here, so I downloaded WEBNet77's IPV4 IpToCountry database, which seems like a comprehensive and up-to-date resource (is there a better resource out there for this purpose?). The database includes about 140K rows of IP ranges, with the sheet structured like so:

Colum labels A - G are, respectively: IP FROM, IP TO, REGISTRY, ASSIGNED, CTRY, CNTRY, COUNTRY.

Notice, a problem: columns A to B represent a range of IPs, but they are in the "long format" (I didn't realize this at first). So, before using these values as a reference, I'll have to convert my dotted quads. Then, I'd like to return Country (column G) for each of the IPs in my log.

Any help here?

And if you think there's a better way to lookup IP > Country in Excel (maybe using the web ie. http://api.hostip.info/flag.php?ip=xxx.xxx.xxx.xxx), please do let me know.

解决方案

You can convert your quad IP to a numeric using this function

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

And you can use MATCH/INDEX to get your location. The trick is to have your IP TO column sorted ascending. The Match function will return index of the row for the last value in the range which is less the given value.

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))

Notice I had to import the CSV (not just reference it externally) and I turned the columns into named ranges (Country, IPTO). For other's information, the CSV linked in the question has column names of:

• IP FROM
• IP TO
• REGISTRY
• ASSIGNED
• CTRY
• CNTRY
• COUNTRY

Alternately, you can convert your dotted quad IP into its constituent parts using only formulas, though it's a bit of a hassle. Here are the first two sections (my IP was in I5)

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

You can see...kind of a pain. Easier: use the VBA method explained above, OR use Text to Columns (on the ribbon: Data > Data Tools > Text To Columns > Delimited > Next > Other: "." > Next > Finish). Then write a formula to convert your split dotted-quad IP to the IP format in your reference database. This formula works:

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

Remember to sub in your column range for J5:M5 above. Then run MATCH/INDEX against the new value.

这篇关于如何根据Excel中的IP地址查找位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！