DIY is an excel version of subnet calculator

Hello everyone , I'm Xiao Fu . When doing network planning , Subnet partition is required ; When troubleshooting network faults , Need to identify the network segment address . The processing steps are based on IP Address / Network segment and mask , Calculate the subnet address 、 You can use IP Address, quantity and other information . At ordinary times , I can use a gadget like subnet calculator , Fast and convenient , The thief to use . however , Now this kind of gadget software on the Internet , There are various forms , They also carry advertisements , It's not easy to use . And the online version of the subnet calculator , Use only when there is a network , And I usually deal with network failures , It's a common thing that you can't surf the Internet until you deal with the fault . Simply , Make one of your own Excel Version of the subnet calculator , Design according to your own habits , Generate data according to your own needs ,DIY Own gadgets ！

What is subnet Division ？

In order to improve the IP Address usage , Divide a network into multiple subnets . from IP The address starts at the far left of the host bit , Put the host bit into the network bit , Get multiple subnet addresses . The part where the host bit becomes the network bit is called the subnet number , The number of subnets after division is equal to 2 Of N Power ,N The number of digits equal to the subnet number .

Demo

I have never done product and R & D , Feel like making a software , First of all Demo . So press IP Address calculation logic , Sort out the required data types . First , Input IP Address and mask , Get the subnet address , Then calculate the broadcast address . You can use IP The address range is the subnet address +1 , Broadcast address -1 . according to IP The first few digits of the address , Determine the address type . According to the address type , Calculate the number of subnets and the number of subnets 、 Number of hosts and number of hosts .

meanwhile ,IP Address writing is a decimal number , The calculation is based on binary numbers . Also carry out hexadecimal number conversion , Easy to view and calculate .

Calculation function

Here comes the hardest part , That is the formula for calculating each data .

First , The input data is IP Address , There are four decimal numbers , Set four cells , Enter four decimal numbers respectively , Automatically convert to binary representation , have access to DEC2BIN Function conversion . meanwhile , Also limit the input value , The scope is 0 ~ 255 , It can be set in the data validation view .

Then enter Network mask . Network mask , There are two ways to express , One is with IP The address is the same , Four decimal numbers , The other is to directly mark the number of mask bits , Just an integer . Enter the number of mask bits , More convenient , It's simpler , Of course, the implementation is more complex . Mask bits are converted to decimal mask addresses , have access to IFS or IF function , According to different values , Output different mask addresses . The value range of mask bits is 1 ~ 32（ Subnet partition , Value taking 0 It makes no sense , Excluded ）, To list 32 A possibility , The function is a little long . In order to shorten the length of the function , Used IF + MOD function . There are four cells , The formula for the first decimal number is ：IF( Mask bits >=8,255,(256-2^(8-MOD(F3,8)))) . Convert decimal to binary , Is also used DEC2BIN function .

Automatic calculation Subnet address , The subnet address is IP Address and mask address AND The result of bit operation , The bit operation function can be used directly BITAND , Get decimal numbers .

Automatic calculation Broadcast address , The network number of the broadcast address is the same as the subnet address , The host numbers are all 1 . You can use IP The inverse code of address and mask address OR An operation , That is to say BITOR(IP Address ,255- Mask address ) , Get the broadcast address .

Automatic calculation Available address range , The network segment address in the network segment （ That is, the subnet address ） And broadcast addresses cannot be assigned , Other addresses can be used , That is, the subnet address +1 And broadcast address -1 Address in range .

Automatic calculation Address type , Use LEFT Function to obtain IP The value of the first few digits of the address , Reuse IFS The function lists five types of addresses .

Automatic calculation The number of subnets , You can use MOD function , Calculate the number of mask bits divided by 8 The remainder of , That is, the number of subnets . Also exclude invalid ranges .

Automatic calculation Maximum number of subnets （ Number of subnets ）,2 The power of the number of subnets is the maximum number of subnets , There are also exclusion of invalid ranges .

Automatic calculation Number of hosts , namely 32 - The number of subnets .

Automatic calculation Maximum number of hosts （ That is, the number of hosts ）,2 The power of the number of subnets minus 2 , That is, the maximum number of hosts .

The final version

Finally found , Only when calculating the address type , Yes IP Binary number of address , The rest of the functions are decimal numbers . So we integrate decimal numbers and binary numbers , It looks more beautiful . In order to prevent misoperation , Input only IP Address and mask bits , The remaining cells cannot be edited . If you want to unprotect the worksheet , Modify parameters or interface by yourself , Enter the cancellation password fox , You can edit any cell freely .

obtain

Interested students , According to the content of the article , Do it yourself . In the way you like , Or a better formula , Make your own subnet calculator . If you want to whore for nothing , Also no problem . The way to get it is at the end of the link below ：

Excel Version of the subnet calculator （ Downloadable ）

Just move your fingers , It's simple . After work , Send it to everyone . Hope to support more , Master the Internet together , Become a master of technology ！