求助懂Excel的朋友。

该用户已被禁言

平时只进游戏论坛，不知道A9有没有这方面的专家，就是我想通过一个IP地址，如何生成对应的掩码和网关，求公式。
例如：192.168.1.66/24这IP，excel如何单单通过这串字符，生成独立的IP、掩码和网关信息？谢谢！

raumei

为什么不问ChatGPT

raumei

我帮你问了  要根据给定的IP地址和子网前缀长度生成相应的子网掩码和网关，可以使用以下公式和步骤：  1. 将子网前缀长度转换为二进制形式。例如，在您提供的例子中，子网前缀长度是24，对应于二进制数值的前24位为1，后面的位为0。  2. 将IP地址转换为二进制形式。例如，将192.168.1.66转换为二进制形式为11000000.10101000.00000001.01000010。  3. 使用子网前缀长度确定子网掩码。将前24位设置为1，后面的位设置为0，得到子网掩码为11111111.11111111.11111111.00000000，即255.255.255.0。  4. 网关地址是子网中的第一个可用地址。为了确定网关地址，您需要找到IP地址的网络部分（即前缀部分），然后将主机部分设置为0。在这种情况下，网络部分是192.168.1，主机部分为0，因此网关地址为192.168.1.0。  使用这些步骤和公式，您可以在Excel中生成独立的IP地址、子网掩码和网关信息。

feibu

Excel说的是Office的Excel吗？

miharusena

按"."把字符串分割成4部分，运算处理完毕后再合并回去

网络知识早还给老师了，只能较你excel部分了……

zynbdsdws

你可以使用Excel的文本函数和分割函数来提取IP、掩码和网关信息。下面是一种可能的方法：

假设你的IP地址字符串是放在单元格A1中，你可以按照以下步骤进行操作：

提取IP地址：使用LEFT函数和FIND函数结合，找到第一个斜杠"/"的位置，并提取出它之前的字符。公式如下：
less
Copy code
=LEFT(A1, FIND("/", A1)-1)
提取掩码：使用MID函数和FIND函数结合，找到第一个斜杠"/"的位置，并提取出它之后的两个字符。公式如下：
less
Copy code
=MID(A1, FIND("/", A1)+1, 2)
计算网关：将IP地址的最后一个数字减去1，并将其与前三个数字拼接在一起。公式如下：
less
Copy code
=LEFT(A1, FIND("/", A1)-1) & MID(A1, FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1), FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1)-FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1)) & (MID(A1, FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1)+1, FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1, FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1)+1)-FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1)-1)-1)
将上述公式应用到相应的单元格中，你就可以分别获得IP地址、掩码和网关的信息。

请注意，这些公式假设IP地址是标准的IPv4地址，且格式为xxx.xxx.xxx.xxx/yy，其中xxx是一个三位数或者一个两位数，yy是一个两位数。如果你的IP地址字符串的格式与此不符，你可能需要根据实际情况进行调整。

NihiRagnarK

纯用Excel写公式挺麻烦的

假设你的IP是在格子A1里

随便找两个格子分割IP和掩码长度，比如

A3是独立IP部分："=LEFT(A1,FIND("/",A1)-1)"  
A4是掩码长度："=RIGHT(A1,LEN(A1)- FIND("/",A1))"

然后把掩码长度转换成掩码
A6生成二进制的掩码："=TEXT(REPT("1",A4)&REPT("0",32-A4)"
A7生成十进制的掩码："=BIN2DEC(LEFT(A6,8)) & "." & BIN2DEC(MID(A6,9,8)) &  "." & BIN2DEC(MID(A6,17,8)) & "." & BIN2DEC(MID(A6,25,8))"

最后生成网关比较麻烦
先把分割IP成四个部分
A9：=LEFT(A3,FIND("~",SUBSTITUTE(A3,".","~",1))-1)
A10：=MID(A3,FIND("~",SUBSTITUTE(A3,".","~",1))+1,FIND("~",SUBSTITUTE(A3,".","~",2))-FIND("~",SUBSTITUTE(A3,".","~",1))-1)
A11：=MID(A3,FIND("~",SUBSTITUTE(A3,".","~",2))+1,FIND("~",SUBSTITUTE(A3,".","~",3))-FIND("~",SUBSTITUTE(A3,".","~",2))-1)
A12：=MID(A3,FIND("~",SUBSTITUTE(A3,".","~",3))+1,LEN(A3)-FIND("~",SUBSTITUTE(A3,".","~",3)))

和掩码二进制AND运算，假设放在B列
B9：=BITAND(BIN2DEC(LEFT(A6,8)),A9)
B10：=BITAND( BIN2DEC(MID(A6,9,8)),A10)
B11：=BITAND(BIN2DEC(MID(A6,17,8)),A11)
B12：=BITAND(BIN2DEC(MID(A6,25,8)),A12)+1


记得B12要+1，代表网关（第一个可用地址）

最后在A14整合网关地址：=B9 & "." & B10 &  "." & B11 & "." & B12

NihiRagnarK

试了一下，一些鬼畜的掩码长度应该都是可以的

该用户已被禁言

zynbdsdws 发表于 2023-7-4 22:41
你可以使用Excel的文本函数和分割函数来提取IP、掩码和网关信息。下面是一种可能的方法：

假设你的IP地址 ...

第三条公式输入后提示输入的公式存在错误。

该用户已被禁言

NihiRagnarK 发表于 2023-7-4 23:23
纯用Excel写公式挺麻烦的

假设你的IP是在格子A1里

A6存在公式错误，