怎麼在excel中把IP轉長整數

為什麼要在excel中把IP轉成長整數

php中有很多先進開發了這樣的工具,例如說ip2long,另外,也有人寫IP V6的轉換工具。

但是我要做批次匯入,從網路上下載了IP對照國別的資料,要做網址轉跳判別使用,我整理完後,需要的IP資料大約十多萬行。

CSV格式,轉完之後,還要弄成SQL語法,如果不使用excel來算,叫我用建資料的方式來做,一筆資料一筆資料回填,我可是一百萬、一千萬個不願意。

所以,有神人可以解求我嗎?

為什麼要把IP做成長整數

因為IP要做計算,下載的資料,只有IP的起始和結束,中間的IP是空的,要怎麼去計算這某一個IP在不在我們所設定的區間呢?

經過IP轉成長整數之後,就可以進行計算了。

我的資料有十多萬筆,程式不是我開發的,如果是我開發的,我應該會在資料庫中多加幾組index上去,讓資料庫能夠加速檢索資料。

真的有好心人

網路上,真的有好心人把相關的內容,用excel做出來,而且提供的不只有excel中的計算,還有SQLite和Oracle資料庫的算法,也都公開分享。在這裡

假設儲存格在A1
在B1中貼上下面的資料
=MID(A1,1,FIND(“|”,SUBSTITUTE(A1,”.”,”|”,1))-1)*2^24+MID(A1,FIND(“|”,SUBSTITUTE(A1,”.”,”|”,1))+1,FIND(“|”,SUBSTITUTE(A1,”.”,”|”,2))-FIND(“|”,SUBSTITUTE(A1,”.”,”|”,1))-1)*2^16+
MID(A1,FIND(“|”,SUBSTITUTE(A1,”.”,”|”,2))+1,FIND(“|”,SUBSTITUTE(A1,”.”,”|”,3))-FIND(“|”,SUBSTITUTE(A1,”.”,”|”,2))-1)*2^8+MID(A1,FIND(“|”,SUBSTITUTE(A1,”.”,”|”,3))+1,4)

如果資料不在A1,請將上面的語法,A1改成所對應的儲存格。算出來的,就是A1存儲格中IP的長整數數值。

如果是我寫php程式,我可能還是會在資料庫中存IP區間,使用的時候,再去做即時運算,畢竟長整數要存的資料,可是比IP區間多了很多。時間換近空間還是空間換時間,就視情況而定了。

IP轉長整數的算法(4則運算)
以IP1.2.3.4及1.2.3.10為例
1*2^24+2*2^16+3*2^8+4
=16909060(長整數)

1*2^24+2*2^16+3*2^8+10
=16909066

只要取得的ip的長整數運算值
16909066<=IP(長整數)>=16909060
就知道這個IP在這個IP區段中

IP轉長整數可以拿來做什麼用?

正常的用途,就是拿來判斷使用者,導他們去對應的國家及語言。

不正常的用法就是拿來針對搜尋引擎
也就是說,給搜尋引擎看的是一套資料,給人看的又是另一套資料,因為搜尋引擎都可以找的到IP區間。

所以,這麼搞的人不是沒有,很多黑帽SEO這樣搞。不過要小心,惡搞被抓,下場不好。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *