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

網(wǎng)友解答:

我確認一下哈！

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

如果是的話，我就簡單說思路哈！

首先，你需要有一個IP地址庫

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

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

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

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

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

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

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

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

其實這一系列的計算看起來復雜，但底層的原理很簡單，就是用IP地址的第一位乘以三個256加上IP地址的第二位乘以兩個256加上IP地址的第三位乘以一個256加上IP地址的第四位，以61.171.112.233為例，算法如下：

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

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

=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地址批量的轉(zhuǎn)換了。

公式解釋：

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

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

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

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

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

通過以上三個函數(shù)的組合，再加上IP的轉(zhuǎn)換公式，我們就輕松的通過上邊的公式的得出IP地址的十進制數(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)建的一個表格，并命名了表格名稱，如果不創(chuàng)建表格，也可以用常規(guī)的單元格區(qū)域引用,如$A$1:$A$200。

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

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

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

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

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

如果覺得用于，點個贊吧！

網(wǎng)友解答:

利用Excel將IP轉(zhuǎn)化為地址的方法很多，比如將IP先轉(zhuǎn)成對應(yīng)的號段，然后到號段對照表里去查其所屬具體地址，但是，實際上這個號段對照表要自己下載或者維護的話非常麻煩，所以，個人并不推薦該方法，另一方面，在網(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 最后整理（提取冒號之后的文本，然后再提取句號前的文本即可）

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

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

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

---

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