iT邦幫忙

0

excel 中 ip 位址的 相加

花輪 2014-01-08 11:11:1512352 瀏覽

最近同事碰到一個問題來問我,但我的 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,那就比較累了..汗Orz

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

看更多先前的討論...收起先前的討論...
請教一下
ip 加 5
那 255 要進位嗎?

我往下拉到底(用向下填滿)
結果測出一個似乎是 Excel 的 bug (溢位?)
iT邦幫忙MVPantijava提到:
用向下填滿


又試了一下
用滑鼠「拉」好像不會錯
吃
向下拉只會把最後一個數字加一, 沒辦法做到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 }} |
{{ formatDate(item.duration) }}
直播中
28
海綿寶寶
iT邦大神 1 級 ‧ 2014-01-08 12:08:12
最佳解答

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

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

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

看更多先前的回應...收起先前的回應...
花輪 iT邦大師 1 級 ‧ 2014-01-08 13:19:45 檢舉

我...「拉」 不出來~哭

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 檢舉

不同凡響 毆飛搖頭

calvinkuo

厲害厲害
讚

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

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

那一個部份做不出來呢
疑惑

花輪 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 是我自己輸入的,不是用公式。
往下拉的結果是...慘不忍睹!落寞

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

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

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

重來一次
試做結果如下

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

<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就會發生~

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

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

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

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

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

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

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

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

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

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

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

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

hwarnder iT邦新手 5 級 ‧ 2014-01-12 16:19:37 檢舉

我也常用VBA解決內定函數的不足,不然重組太多的函數,常會讓人看得眼花潦亂。

8
kuo0605
iT邦新手 5 級 ‧ 2014-01-09 22:40:16

1.輸入第一、二行的IP(最後一碼差5)
2.將兩個儲存格選起來(黑框)
3.滑鼠移到點選右下角紅色十字的地方(此時滑鼠會變成黑色十字)
4.承上,按住右鍵往下拉,即可完成

hcsvieken iT邦研究生 4 級 ‧ 2014-01-10 08:21:35 檢舉

192.168.1.251
192.168.1.256
192.168.1.261
192.168.1.266
192.168.1.271
192.168.1.276
192.168.1.281
192.168.1.286
拉超過 255 了 ,kuo大。

fran633 大,拉不出,是便祕嗎?常拉不出是肚子沒東西(沒吃)?

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

哈哈~~拉不出是因為 "拉出超過255也沒用",且5000筆IP也沒有規律性,真要這樣拉,應該也不必在這邊發問了...暈

K大:雖然不必想太複雜,但您的答案,連我這個 EXCEL 程度頗差的也會,SORRY~

10
allen42
iT邦新手 5 級 ‧ 2014-01-10 13:19:25

A1 欄是 IP
B1 欄是起始數字
C1 欄是遞增數字(例如5)
在A2輸入公式
=$A$1&ROUNDDOWN(((ROW()-2)*$C$1+$B$1)/255,0)&"."&MOD((ROW()-2)*$C$1+$B$1,255)
之後下填滿到A5001
完工

看更多先前的回應...收起先前的回應...
花輪 iT邦大師 1 級 ‧ 2014-01-10 20:14:38 檢舉

哇~~好可怕的公式......倒

hwarnder iT邦新手 5 級 ‧ 2014-01-12 16:16:00 檢舉

能看到多人不一樣的解決問題的方法構思,覺得吸收不少觀念,很棒。

Excel是很棒工具,內含豐富功能,因太多功能,也只懂得部份,反覆利用,條條大路通羅馬,都能解決問題.

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

Allen大的方法也不行耶,因為 原始的 IP 沒規律性,那 B欄的起始數字就要手動自己產生(輸入),5000筆也很辛苦..

我要發表回答

立即登入回答