excel 中 ip 位址的相加

excel

花輪 2014-01-08 11:11:15 ‧ 12674 瀏覽

分享至

最近同事碰到一個問題來問我，但我的 excel 功力很差，連樞紐分析都不會，所以求教於大家：

假設在 excel 某 sheet 中 A欄是 IP位址，現在要在 B欄內輸入 A的IP位址 +5
（例：A ＝ 192.168.1.1，B 要變成 192.168.1.6）
因為有5000個 IP，要如何作（下公式）才能一次算好填入呢？

我知道最笨的方法是把 A欄往右拖5欄，再把多的4欄刪掉，這應該不難，但若IP要加50或加100，那就比較累了..

請高手指導囉，感謝...

看更多先前的討論...收起先前的討論...

海綿寶寶 iT邦大神 1 級 ‧ 2014-01-08 11:50:50 檢舉

請教一下
ip 加 5
那 255 要進位嗎？

我往下拉到底(用向下填滿)
結果測出一個似乎是 Excel 的 bug (溢位？)

海綿寶寶 iT邦大神 1 級 ‧ 2014-01-08 11:53:26 檢舉

iT邦幫忙MVPantijava提到：
用向下填滿

又試了一下
用滑鼠「拉」好像不會錯

darkslayer iT邦好手 1 級 ‧ 2014-01-08 12:00:00 檢舉

向下拉只會把最後一個數字加一, 沒辦法做到ip的遞增

花輪 iT邦大師 1 級 ‧ 2014-01-08 13:16:05 檢舉

海綿大： ip 沒有255的啦，5000比也不是連續的，不能用「拉」的啦...

花輪 iT邦大師 1 級 ‧ 2014-01-08 13:16:19 檢舉

5000筆

ayu iT邦好手 2 級 ‧ 2014-01-08 19:47:18 檢舉

這個您看合不合用?
我試過 192.168.1.254 +5 可以變成 192.168.2.3
http://www.anyweb.co.nz/tutorial/excelip
IPIncrease(IP-Address As String, Step As Integer)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

6 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2014-01-08 12:08:12

最佳解答

A 欄是 IP
B 欄是數字(以5遞增)
看看這樣合不合用
如果合用的話
公式如下：

&lt;pre class="c" name="code">
A1="192.168." & ROUNDDOWN(B1/255,0) &  "." & MOD(B1, 255)
B1=1
B2=B1+5

先設好 A1,B1,B2
然後向下填滿或者往下拉就算了

回應 14
分享
檢舉

看更多先前的回應...收起先前的回應...

花輪 iT邦大師 1 級 ‧ 2014-01-08 13:19:45 檢舉

我...「拉」不出來～

海綿寶寶 iT邦大神 1 級 ‧ 2014-01-08 13:50:25 檢舉

1.先把 A1,B1,B2 公式設好
2.先選取 B1和B2
然後將游標移至紅色十字的位置
(游標由空心變成實心時，位置才對，不管顏色)
如下圖

然後開始往下拉
看要拉到那裡都可以

3.選取 A1
你可以如法泡製往下拉如下圖

或者在游標就正確位置後
Double Click
行！
又完成了一件美好的向下填滿

CalvinKuo iT邦大師 7 級 ‧ 2014-01-08 14:24:42 檢舉

海綿大出手果然不同反響...
B列也可填滿來做..

用$A$1取代"192.168."

CalvinKuo iT邦大師 7 級 ‧ 2014-01-08 14:26:19 檢舉

不同凡響

海綿寶寶 iT邦大神 1 級 ‧ 2014-01-08 16:55:33 檢舉

calvinkuo

厲害厲害

花輪 iT邦大師 1 級 ‧ 2014-01-17 11:02:38 檢舉

海綿大這招為什麼我還是做不出來？？

海綿寶寶 iT邦大神 1 級 ‧ 2014-01-17 11:17:56 檢舉

那一個部份做不出來呢

花輪 iT邦大師 1 級 ‧ 2014-01-17 11:21:50 檢舉

假如IP如下，我用上述的方法做出來也不是我要的。

花輪 iT邦大師 1 級 ‧ 2014-01-17 11:28:43 檢舉

我的 B欄在計算後除了 ip 第四組數字要 +5 外，前面那三組也要留著，不能是只有最後 +5 的數字。湊半天還是湊不起來。

因為 A欄的 IP 沒有連續性，所以選取後直接往下拉是行不通的，更何況還有相加後超過 255 的問題。

花輪 iT邦大師 1 級 ‧ 2014-01-17 11:52:17 檢舉

上面例圖內 B欄那兩個 ip 是我自己輸入的，不是用公式。
往下拉的結果是...慘不忍睹！

海綿寶寶 iT邦大神 1 級 ‧ 2014-01-17 15:11:24 檢舉

我現在才發現
我誤會題目了

你是要手動輸入欄A的IP 然後產生右方的 +5 IP 對吧

我以為是
手動輸入欄 B 的值，然後產生欄 A 的 IP

海綿寶寶 iT邦大神 1 級 ‧ 2014-01-17 15:15:17 檢舉

重來一次
試做結果如下

這次公式更可怕了
請如下設定

&lt;pre class="c" name="code">
A1=你自己輸入的IP
B1=MID($A1,(FIND(".",$A1,FIND(".",$A1)+1)+1),(FIND(".",$A1,FIND(".",$A1,FIND(".",$A1)+1)+1))-(FIND(".",$A1,FIND(".",$A1)+1)+1))*255+MID($A1,FIND(".",$A1,FIND(".",$A1,FIND(".",$A1)+1)+1)+1,3)
C1=LEFT($A1,FIND(".",$A1,FIND(".",$A1)+1)-1) & "." & ROUNDDOWN(($B1+1)/255,0) & "." & MOD(($B1+1),255)
D1=LEFT($A1,FIND(".",$A1,FIND(".",$A1)+1)-1) & "." & ROUNDDOWN(($B1+2)/255,0) & "." & MOD(($B1+2),255)
E1=LEFT($A1,FIND(".",$A1,FIND(".",$A1)+1)-1) & "." & ROUNDDOWN(($B1+3)/255,0) & "." & MOD(($B1+3),255)
F1=LEFT($A1,FIND(".",$A1,FIND(".",$A1)+1)-1) & "." & ROUNDDOWN(($B1+4)/255,0) & "." & MOD(($B1+4),255)
G1=LEFT($A1,FIND(".",$A1,FIND(".",$A1)+1)-1) & "." & ROUNDDOWN(($B1+5)/255,0) & "." & MOD(($B1+5),255)

花輪 iT邦大師 1 級 ‧ 2014-01-20 13:21:50 檢舉

哇～海綿大真的太強了，先弄個 b1 來計算已避免 ip 相加後超過 255 的問題，同時又考慮到超過255 後的第三個 byte 要進位，真神！！

只是，b1 的公式真的看不懂說，只有一堆＄a1，find，+1 跟括號...

治 iT邦新手 2 級 ‧ 2017-06-12 17:01:43 檢舉

三年前的，不小心翻到了XD
剛剛玩了一下，發現，如果是要減IP的話，這公式會有問題，
比如：192.21.71.250 減一之後，會變成 192.21.72.249
只要是遇到250就會發生~

登入發表回應

darkslayer

iT邦好手 1 級 ‧ 2014-01-08 11:45:37

方法一: LEFT(A1,10)&RIGHT(A1,1)+5 這個比較笨, 很容易加錯
方法二: 將ip分成四個儲存格, 然後在第五格把它合併成正常的ip,要加加減減用前面那四格操作.
第五格公式: A1 & "." & B1 & "." & C1& "." & D1

回應 2
分享
檢舉

花輪 iT邦大師 1 級 ‧ 2014-01-08 13:19:11 檢舉

哈哈～方法2也試過，也有點麻煩，且表格是別人家的，不能亂改...

CalvinKuo iT邦大師 7 級 ‧ 2014-01-08 13:49:57 檢舉

開一張新表格計算IP，做好後再貼值回去不就得了..

登入發表回應

as400excel

iT邦新手 2 級 ‧ 2014-01-08 12:08:16

=LEFT(A1,10) & (MID(A1,11,3) + 5)

回應 1
分享
檢舉

花輪 iT邦大師 1 級 ‧ 2014-01-08 13:23:13 檢舉

這公式若不是 xxx.xxx.x.* 公式就要改了...

登入發表回應

summertw

iT邦好手 1 級 ‧ 2014-01-09 14:08:26

工欲善其事，必先利其器...
此【利】字應該包括找到對的工具..
Excel應該可以作到，但是用VBA吧，光靠這些巨集函數要做到，可能會花很大的工夫...
若不會VBA，就把資料存成CSV或是TXT檔，再寫一支小程式把它處理完，同樣回存成CSV檔...
這樣Excel就可以直接讀取了...
工欲善其事，必先利其器...