網(wǎng)友解答: 我確認(rèn)一下哈！比如IP：211.156.31.255 ，歸屬于上海市聯(lián)通，你是不希望在Excel中做這樣的查詢，通過以有IP地址，而算出IP地址的歸屬地呢？如果是的話，我就簡(jiǎn)

網(wǎng)友解答:

我確認(rèn)一下哈！

比如IP：211.156.31.255 ，歸屬于上海市聯(lián)通，你是不希望在Excel中做這樣的查詢，通過以有IP地址，而算出IP地址的歸屬地呢？

如果是的話，我就簡(jiǎn)單說思路哈！

首先，你需要有一個(gè)IP地址庫

地址庫內(nèi)包含許多的IP地址歸屬地區(qū)間，如下圖所示：（為了回答你這個(gè)問題，我還特地去CSDN上Down了一個(gè)IP地址庫來做實(shí)驗(yàn)）

IP地址庫中包含IP段起始IP十進(jìn)制數(shù)值，IP段結(jié)束IP十進(jìn)制數(shù)值，省、市、運(yùn)營商、地址、起始IP地址、結(jié)束IP地址等字段

第二：你需要將要查詢的IP地址，轉(zhuǎn)換為十進(jìn)制數(shù)字，用于之后的查詢。

這里需要特別的解釋一下，IPV4的地址是61.171.112.233，這樣子類型的，而這種類型的字符串是無法用在公式中參與計(jì)算的，所以需要人工將其轉(zhuǎn)換為10進(jìn)制數(shù)。

那么IP地址轉(zhuǎn)換為十進(jìn)制數(shù)字的原理是什么呢？

還拿61.171.112.233舉例，我們需要將（61、171、112、233）這四個(gè)數(shù)字轉(zhuǎn)換為十六進(jìn)制數(shù)，這個(gè)轉(zhuǎn)換我們可以用，電腦自帶的計(jì)算器（程序員）來計(jì)算，如下圖：

在計(jì)算器中輸入61后，我們得到十六進(jìn)制數(shù)3D，依次計(jì)算171、112、233，得到61.171.112.233的十六進(jìn)制IP地址3D.AB.70.E9，然后在把得到的4個(gè)十六進(jìn)制數(shù)按順序排好（3DAB70E9）轉(zhuǎn)換為十進(jìn)制數(shù)字，如下圖所示：

最后得出1034645737，這個(gè)就是我們要的61.171.112.233十進(jìn)制數(shù)值。

其實(shí)這一系列的計(jì)算看起來復(fù)雜，但底層的原理很簡(jiǎn)單，就是用IP地址的第一位乘以三個(gè)256加上IP地址的第二位乘以兩個(gè)256加上IP地址的第三位乘以一個(gè)256加上IP地址的第四位，以61.171.112.233為例，算法如下：

=61*256*256*256+171*256*256+112*256+233

在實(shí)際的應(yīng)用中我們不可能去一個(gè)一個(gè)的用計(jì)算器去計(jì)算每個(gè)IP地址所對(duì)應(yīng)的十進(jìn)制數(shù)值，我們可以用公式去批量的計(jì)算，這里我寫好了一個(gè)公式：

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

用這個(gè)公式就可以把IP地址批量的轉(zhuǎn)換了。

公式解釋：

這個(gè)公式中涉及到了三個(gè)Excel函數(shù)，MID、FIND、SUBSTITUTE

MID函數(shù)：返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定，語法為：MID(text, start_num, num_chars)

FIND函數(shù)：用于在第二個(gè)文本串中定位第一個(gè)文本串，并返回第一個(gè)文本串的起始位置的值，該值從第二個(gè)文本串的第一個(gè)字符算起，語法為：FIND(find_text, within_text, [start_num])

SUBSTITUTE函數(shù)：在文本字符串中用 new_text 替換 old_text。 如果需要在某一文本字符串中替換指定的文本，語法為：SUBSTITUTE(text, old_text, new_text, [instance_num])

如果對(duì)這幾個(gè)公式有什么不太理解的地方，就自己研究一下吧。

通過以上三個(gè)函數(shù)的組合，再加上IP的轉(zhuǎn)換公式，我們就輕松的通過上邊的公式的得出IP地址的十進(jìn)制數(shù)值了，如下圖：

第三：查詢

下面我們就可以查詢IP地址所屬的地區(qū)了

首先我們用公式查詢一下我們要查的IP在IP庫中是否存在，公式如下：

=IF(SUMPRODUCT((IP數(shù)據(jù)表[start_ip_dec]<=B2)*(IP數(shù)據(jù)表[end_ip_dec]=B2)),"是","否")

公式中【IP數(shù)據(jù)表[start_ip_dec]】是我為IP庫創(chuàng)建的一個(gè)表格，并命名了表格名稱，如果不創(chuàng)建表格，也可以用常規(guī)的單元格區(qū)域引用,如$A$1:$A$200。

如需了解創(chuàng)建表格的知識(shí)，請(qǐng)移步：https://www.toutiao.com/i6512004237377929741/

如上圖所示：有一條IP是在IP庫中不存在的，經(jīng)確認(rèn)，確實(shí)如此，結(jié)束IP超了：

下面你就可以自由發(fā)揮了，我這里簡(jiǎn)單的用lookup查一下，IP對(duì)應(yīng)地區(qū)：

公式中用IF做了一下判斷，排除lookup查詢時(shí)可能會(huì)出現(xiàn)的誤差。

但是用Lookup還是會(huì)出現(xiàn)有誤差的情況，公式并不完善，奈何實(shí)力有限，還希望有大神能補(bǔ)充我的想法，幫我完善這個(gè)回答，謝謝啦！

如果覺得用于，點(diǎn)個(gè)贊吧！

網(wǎng)友解答:

利用Excel將IP轉(zhuǎn)化為地址的方法很多，比如將IP先轉(zhuǎn)成對(duì)應(yīng)的號(hào)段，然后到號(hào)段對(duì)照表里去查其所屬具體地址，但是，實(shí)際上這個(gè)號(hào)段對(duì)照表要自己下載或者維護(hù)的話非常麻煩，所以，個(gè)人并不推薦該方法，另一方面，在網(wǎng)絡(luò)上，有大量的網(wǎng)站可供查詢ip的具體地址，因此，我們完全可以利用Excel，直接連接相應(yīng)的ip地址查詢網(wǎng)站，獲取相應(yīng)的查詢結(jié)果，具體步驟如下：

一、將ip地址放入PowerQuery，如下圖所示：二、在Power Query查詢編輯器中，添加自定義列，調(diào)用需要查詢的ip地址第三步：整理數(shù)據(jù)

通過上一步的自定義公式，我們已經(jīng)從ip地址查詢網(wǎng)站中獲得相應(yīng)的數(shù)據(jù)，如下圖所示：

因此，我們只需要將該內(nèi)容整理出來即可，具體如下：

3.1 展開List，如下圖所示：

3.2 篩選地址結(jié)果所在的行

3.3 最后整理（提取冒號(hào)之后的文本，然后再提取句號(hào)前的文本即可）

最終結(jié)果整理完畢，即可將結(jié)果數(shù)據(jù)返回Excel中，如下圖所示：

通過以上過程，我們就可以隨時(shí)查詢各種ip的地址情況，而且并不需要自己去下載、保存和維護(hù)ip地址段的相關(guān)數(shù)據(jù)。

實(shí)際上，現(xiàn)在Excel中有了Power Query，大量的數(shù)據(jù)查詢工作都可以直接交給Power Query去自動(dòng)完成，比如身份證號(hào)碼相關(guān)信息的查詢等等。

---

歡迎關(guān)注【Excel到PowerBI】我是大海，微軟認(rèn)證Excel專家，企業(yè)簽約Power BI顧問讓我們一起學(xué)習(xí)，共同進(jìn)步！