|
我确认一下哈！ 比如IP：211.156.31.255 ，归属于上海市联通，你是不希望在Excel中做这样的查询，通过以有IP地址，而算出IP地址的归属地呢？ 如果是的话，我就简单说思路哈！ 首先，你需要有一个IP地址库地址库内包含许多的IP地址归属地区间，如下图所示：（为了回答你这个问题，我还特地去CSDN上Down了一个IP地址库来做实验） IP地址库中包含IP段起始IP十进制数值，IP段结束IP十进制数值，省、市、运营商、地址、起始IP地址、结束IP地址等字段 第二：你需要将要查询的IP地址，转换为十进制数字，用于之后的查询。这里需要特别的解释一下，IPV4的地址是61.171.112.233，这样子类型的，而这种类型的字符串是无法用在公式中参与计算的，所以需要人工将其转换为10进制数。 那么IP地址转换为十进制数字的原理是什么呢？ 还拿61.171.112.233举例，我们需要将（61、171、112、233）这四个数字转换为十六进制数，这个转换我们可以用，电脑自带的计算器（程序员）来计算，如下图： 在计算器中输入61后，我们得到十六进制数3D，依次计算171、112、233，得到61.171.112.233的十六进制IP地址3D.AB.70.E9，然后在把得到的4个十六进制数按顺序排好（3DAB70E9）转换为十进制数字，如下图所示： 最后得出1034645737，这个就是我们要的61.171.112.233十进制数值。 其实这一系列的计算看起来复杂，但底层的原理很简单，就是用IP地址的第一位乘以三个256加上IP地址的第二位乘以两个256加上IP地址的第三位乘以一个256加上IP地址的第四位，以61.171.112.233为例，算法如下： =61*256*256*256+171*256*256+112*256+233 在实际的应用中我们不可能去一个一个的用计算器去计算每个IP地址所对应的十进制数值，我们可以用公式去批量的计算，这里我写好了一个公式： =MID(A1,1,FIND('|',SUBSTITUTE(A1,'.','|',1),1)-1)*256*256*256+MID(A1,FIND('|',SUBSTITUTE(A1,'.','|',1),1)+1,FIND('|',SUBSTITUTE(A1,'.','|',2),1)-FIND('|',SUBSTITUTE(A1,'.','|',1),1)-1)*256*256+MID(A1,FIND('|',SUBSTITUTE(A1,'.','|',2),1)+1,FIND('|',SUBSTITUTE(A1,'.','|',3),1)-FIND('|',SUBSTITUTE(A1,'.','|',2),1)-1)*256+MID(A1,FIND('|',SUBSTITUTE(A1,'.','|',3))+1,3) 用这个公式就可以把IP地址批量的转换了。 公式解释： 这个公式中涉及到了三个Excel函数，MID、FIND、SUBSTITUTE MID函数：返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定，语法为：MID(text, start_num, num_chars) FIND函数：用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起，语法为：FIND(find_text, within_text, [start_num]) SUBSTITUTE函数：在文本字符串中用 new_text 替换 old_text。 如果需要在某一文本字符串中替换指定的文本，语法为：SUBSTITUTE(text, old_text, new_text, [instance_num]) 如果对这几个公式有什么不太理解的地方，就自己研究一下吧。 通过以上三个函数的组合，再加上IP的转换公式，我们就轻松的通过上边的公式的得出IP地址的十进制数值了，如下图： 第三：查询下面我们就可以查询IP地址所属的地区了 首先我们用公式查询一下我们要查的IP在IP库中是否存在，公式如下： =IF(SUMPRODUCT((IP数据表[start_ip_dec]<=b2)*(ip数据表[end_ip_dec]>=B2)),'是','否') 公式中【IP数据表[start_ip_dec]】是我为IP库创建的一个表格，并命名了表格名称，如果不创建表格，也可以用常规的单元格区域引用,如$A$1:$A$200。 如需了解创建表格的知识，请移步：https://www.toutiao.com/i6512004237377929741/ 如上图所示：有一条IP是在IP库中不存在的，经确认，确实如此，结束IP超了： 下面你就可以自由发挥了，我这里简单的用lookup查一下，IP对应地区： 公式中用IF做了一下判断，排除lookup查询时可能会出现的误差。 但是用Lookup还是会出现有误差的情况，公式并不完善，奈何实力有限，还希望有大神能补充我的想法，帮我完善这个回答，谢谢啦！ 如果觉得用于，点个赞吧！ |
|
|
