☰

按 IP 地址对 Excel 列进行排序

tag-icon • tag-icon microsoft-excel sorting
按 IP 地址对 Excel 列进行排序

我有一个相当大的（超过 200 行）Excel 电子表格，其中列出了我的网络中的项目（即打印机、服务器、工作站、网络投影仪等），其中第一列是 IP 地址字段，其格式为192.168.x.y。当我尝试对其进行排序时，我的问题是它（作为示例）从192.168.0.85到192.168.0.9。我希望看到的是它基于前 3 个八位字节进行排序，然后按逻辑顺序对最后一个八位字节进行排序（即.1、.2、.3等）。这可能吗？如果可以，怎么做？

答案1

正如 nixda 在评论中提到的那样，辅助列将使这成为可能。 之后，您有两种选择来维护工作表：

  • 在拆分帮助列中添加所有新 IP。
  • 对于新添加的内容，重复“文本到列”的过程。

具体步骤如下：

  1. 选择您的 IP 列并单击Data>Text-to-Columns 文本到列

  2. 选择 Delimted 选项并单击 Next。选中Other复选框并输入句点.。单击 Next。 选择 . 作为分隔符

  3. 保留所有列，将其保留为常规，单击范围图标来编辑该Destination部分。 改变目的地 1

  4. 选择要显示新文本的列。按 Enter 键。 改变目的地 2

  5. 确保选择了范围，然后单击Data> Sort。输入排序标准。继续为每个八位字节添加级别。 排序

  6. 最终结果如下： 结果

答案2

我知道这是一篇旧帖子，但为了提供可行的解决方案，我提出以下内容。

只需将此公式放在相邻单元格中，并更新引用以指向包含您的 IP 地址的单元格（本例中为 A1）。这将产生类似于 010.121.008.030 的结果，然后可以按字母顺序（正确）排序。然后将新列的宽度设置为零，然后就好了。是时候享受一杯咖啡了。

=TEXT(MID(A1,1,FIND(".",A1)),"000")&"."&TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-1-FIND(".",A1)),"000")&"."&TEXT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-1-FIND(".",A1,FIND(".",A1)+1)),"000")&"."&TEXT(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)),"000")

答案3

除了 John Homer 的回答之外，我在此提供了一个公式，该公式也适用于 Apple 的 Numbers 应用程序，因为该TEXT应用程序没有该函数。也在 Google Docs 中进行了测试。

=DEC2HEX(LEFT(B2,FIND(".",B2)-1),2)
&
DEC2HEX(MID(
    B2,
    FIND(".",B2)+1,
    FIND(
        ".",
        B2,
        FIND(".",B2)+1
    ) - FIND(".",B2) - 1
),2)
&
DEC2HEX(MID(
    B2,
    FIND(
        ".",
        B2,
        FIND(".",B2)+1
    ) + 1,
    FIND(
        ".",
        B2,
        FIND(
            ".",
            B2,
            FIND(".",B2)+1
        )+1
    ) - FIND(
        ".",
        B2,
        FIND(".",B2)+1
    ) - 1
),2)
&
DEC2HEX(RIGHT(
    B2,
    LEN(B2) - FIND(
        ".",
        B2,
        FIND(
            ".",
            B2,
            FIND(".",B2)+1
        )+1
    )
),2)

答案4

Sub IPSplit()

HeaderRow = 1
ColimnName = "A"
BeginIPaddsressData = 2

Dim HeaderArray As Variant
HeaderArray = Array("IP oct 1", "IP oct 2", "IP oct 3", "IP oct 4")

Dim Octet() As String
Dim RangeSearch As Range, RangeFound As Range, LastCell As Range
Dim LastCellRowNumber As Long, LastCellColumnNumber As Long, RowNumber As Long

With ActiveSheet
Set LastCell = .Cells(HeaderRow, .Columns.Count).End(xlToLeft)
LastHeaderColumnNumber = LastCell.Column

Set RangeSearch = Range("1:1")
Set RangeFound = RangeSearch.Find(What:=HeaderArray(0), LookIn:=xlValues)

If RangeFound Is Nothing Then
RowNumber = 2
    If .Cells(RowNumber, .Columns.Count) <> vbNullString Then
        Set LastCell = .Cells(RowNumber, .Columns.Count)
        LastCellColumnNumber = LastCell.Column
    Else
        Set LastCell = .Cells(RowNumber, .Columns.Count).End(xlToLeft)
'Specifies the last column LastCellColumnNumber.

        LastCellColumnNumber = LastCell.Column
    End If

Range(Cells(HeaderRow, LastCellColumnNumber + 1), Cells(HeaderRow, LastCellColumnNumber + 4)).Value = HeaderArray
'Insert Header

Else
LastCellColumnNumber = RangeFound.Column - 1
End If

Set LastCell = .Cells(.Rows.Count, ColimnName).End(xlUp)
'Specifies the last cell number in the column ColimnName.

LastCellRowNumber = LastCell.Row   
End With

    For I = BeginIPaddsressData To LastCellRowNumber

    Octet = Split(Cells(I, ColimnName).Value, ".")
    For O = 0 To 3       
'cells populate the values of octets 1-4.
      If (UBound(Octet) - O) >= 0 Then      
         Cells(I, ColimnName).Offset(0, LastCellColumnNumber + O).Value = Octet(O)
      End If
    Next
Next
End Sub

相关内容