☰

MS Excel 子网计算器

tag-icon • tag-icon subnet microsoft-excel
MS Excel 子网计算器

我经常在这里使用方便的子网计算器：

http://www.subnet-calculator.com/

我觉得这很有用。但是，有没有人有这个版本的详细信息/链接，它提供了类似的功能，但在 excel 电子表格中。

然后我可以将其包含在工作表中，而不必继续访问该网站。如果我没有互联网接入，这也很方便 - 当我需要互联网时，这种情况经常发生。

答案1

以下函数（调用 Marcus Mansfield 的 IPSubnetCalc）将在给定子网时输出网络范围：

    Public Function IPRange(IPandMaskInput$)

    ' PROGRAM:
    ' IP Range Calculator by Tomas Pospisek that uses Marcus Mansfield's IPSubnetCalc
    '
    ' INPUTS:
    ' Input Expected xx.xx.xx.xx/yy
    ' Where xx = decimal IPv4 Octet
    '       yy = BitMask Value ( 0 - 32 ex.31 )
    '
    ' OUTPUT:
    ' IP Range in the form: "aa.aa.aa.aa - bb.bb.bb.bb"

        Range_From = IPSubnetCalc(4, IPandMaskInput)
        Range_To = IPSubnetCalc(5, IPandMaskInput)

        IPRange = Range_From & " - " & Range_To
    End Function

答案2

这是一个漂亮的基本的。创建自己的实际上是一项非常简单的任务。

答案3

我添加了一个新选项来计算网络中可用主机的数量。

希望能帮助到你。

瑞

Public Function IPSubnetCalc(intFn%, IPandMaskInput$)

' PROGRAM:
' IP Subnet Calculator by Marcus Mansfield
'
' CHANGES:
' Date: 2010-10-06
' Author: Rui Sousa (ruib.sousa@gmail)
' Change: Add funtion 6 to return number os hosts
'
' INPUTS:
' Input Expected xx.xx.xx.xx/yy
' Where xx = decimal IPv4 Octet
'       yy = BitMask Value ( 0 - 32 ex.31 )
'
'
' FUNCTIONS:
' 1 - Return Subnet Mask in dotted decimal
' 2 - Return Subnet Address in Dot. Dec.
' 3 - Return Broadcast Address in Dot. Dec.
' 4 - Return Lowest Client IP in Dot. Dec.
' 5 - Return Highest Client IP in Dot. Dec.
' 6 - Return Total Number of Host in the Network.
'


Dim IPAddress$, BitMask%
Dim IPOctet1%, IPOctet2%, IPOctet3%, IPOctet4%
Dim MaskOctet1%, MaskOctet2%, MaskOctet3%, MaskOctet4%
Dim Dot1%, Dot2%, Dot3%, Slash1%
Dim IPNetwork1BIN$, IPNetwork2BIN$, IPNetwork3BIN$, IPNetwork4BIN$


On Error Resume Next

Dot1 = InStr(1, IPandMaskInput, ".")
Dot2 = InStr(Dot1 + 1, IPandMaskInput, ".")
Dot3 = InStr(Dot2 + 1, IPandMaskInput, ".")
Slash1 = InStr(1, IPandMaskInput, "/")

'Check Basic Format
If Dot1 = 0 Or Dot2 = 0 Or Dot3 = 0 Or Slash1 = 0 Then
    IPSubnetCalc = "Incorrect Format"
    Exit Function
End If

'Split IP into 8bit Octets
IPOctet1 = Mid(IPandMaskInput, 1, Dot1 - 1)
IPOctet2 = Mid(IPandMaskInput, Dot1 + 1, Dot2 - Dot1 - 1)
IPOctet3 = Mid(IPandMaskInput, Dot2 + 1, Dot3 - Dot2 - 1)
IPOctet4 = Mid(IPandMaskInput, Dot3 + 1, Slash1 - Dot3 - 1)
BitMask = Right(IPandMaskInput, Len(IPandMaskInput) - Slash1)

If IPOctet1 > 255 Or IPOctet2 > 255 Or IPOctet3 > 255 Or IPOctet4 > 255 Then
    IPSubnetCalc = "Value Error, Octet > 255"
    Exit Function
End If
If IPOctet1 < 0 Or IPOctet2 < 0 Or IPOctet3 < 0 Or IPOctet4 < 0 Then
    IPSubnetCalc = "Value Error, Octet < 0"
    Exit Function
End If
If BitMask < 8 Or BitMask > 32 Or BitMask = 31 Then
    IPSubnetCalc = "Bitmask Error : Range 8 - 32, Excl. 31"
    Exit Function
End If

Select Case intFn
Case 1 'Subnet Mask Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))
    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 2 'Subnet Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

    IPNetwork1BIN = ""
    IPNetwork2BIN = ""
    IPNetwork3BIN = ""
    IPNetwork4BIN = ""

    For iCounter = 1 To 8

        If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
            IPNetwork1BIN = IPNetwork1BIN & "1"
        Else
            IPNetwork1BIN = IPNetwork1BIN & "0"
        End If

        If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
            IPNetwork2BIN = IPNetwork2BIN & "1"
        Else
            IPNetwork2BIN = IPNetwork2BIN & "0"
        End If

        If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
            IPNetwork3BIN = IPNetwork3BIN & "1"
        Else
            IPNetwork3BIN = IPNetwork3BIN & "0"
        End If

        If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
            IPNetwork4BIN = IPNetwork4BIN & "1"
        Else
            IPNetwork4BIN = IPNetwork4BIN & "0"
        End If

    Next

    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(IPNetwork1BIN)
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(IPNetwork2BIN)
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(IPNetwork3BIN)
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(IPNetwork4BIN)

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 3 'Broadcast Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

    IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

    BuildBIN = ""

    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
        Else
            BuildBIN = BuildBIN & "1"
        End If
    Next


    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 4 'Low IP Dot Dec

    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

    IPNetwork1BIN = ""
    IPNetwork2BIN = ""
    IPNetwork3BIN = ""
    IPNetwork4BIN = ""

    For iCounter = 1 To 8

        If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
            IPNetwork1BIN = IPNetwork1BIN & "1"
        Else
            IPNetwork1BIN = IPNetwork1BIN & "0"
        End If

        If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
            IPNetwork2BIN = IPNetwork2BIN & "1"
        Else
            IPNetwork2BIN = IPNetwork2BIN & "0"
        End If

        If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
            IPNetwork3BIN = IPNetwork3BIN & "1"
        Else
            IPNetwork3BIN = IPNetwork3BIN & "0"
        End If

        If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
            IPNetwork4BIN = IPNetwork4BIN & "1"
        Else
            If iCounter = 8 Then
                IPNetwork4BIN = IPNetwork4BIN & "1"
            Else
                IPNetwork4BIN = IPNetwork4BIN & "0"
            End If
        End If

    Next

    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(IPNetwork1BIN)
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(IPNetwork2BIN)
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(IPNetwork3BIN)
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(IPNetwork4BIN)

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4



Case 5 'High IP Dot Dec
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

    IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

    BuildBIN = ""

    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
        Else
            If iCounter = 32 Then
                BuildBIN = BuildBIN & "0"
            Else
                BuildBIN = BuildBIN & "1"
            End If
        End If
    Next


    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 6 'Number of Hosts/Net

    iZeroCounter = 0
    For iCounter = 1 To 32
        If iCounter > BitMask Then
            iZeroCounter = iZeroCounter + 1
        End If
    Next

    IPSubnetCalc = (2 ^ iZeroCounter) - 2



Case Else 'Incorrect Selection
    IPSubnetCalc = "Incorrect Function"
End Select

End Function

答案4

您是否特意想要一个电子表格来计算子网？有很多独立工具可以做到这一点。这里例如

相关内容