☰

如何使 Excel 中包含 IP 地址的单元格正确排序？

tag-icon • tag-icon microsoft-excel ip-address sorting
如何使 Excel 中包含 IP 地址的单元格正确排序？

我目前正在处理大量 IP 地址列表（数千个）。

但是，当我对包含 IP 地址的列进行排序时，它们的排序方式并不直观或易于遵循。

例如，如果我输入以下 IP 地址：

在此处输入图片描述

然后如果我按升序排序，我会得到以下结果：

在此处输入图片描述

有没有办法让我格式化单元格，以便例如显示 IP 地址 17.255.253.65后1.128.96.254 和前按升序排列时是 103.236.162.56 吗？

如果没有的话，还有其他方法可以帮助我实现这个最终目标吗？

答案1

您可能已经意识到，您的 IP 地址被视为文本而不是数字。它们被按文本排序，这意味着以“162”开头的地址将排在以“20”开头的地址之前（因为字符“1”排在字符“2”之前）。

您可以使用此答案中提供的公式：https://stackoverflow.com/a/31615838/4424957将 IP 地址拆分成各个部分。

如果您的 IP 地址在 A 列，请添加 BE 列，如下所示。

在此处输入图片描述

输入公式

=VALUE(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",999)),(B$1)*999-998,999)))

在单元格 B2 中将其复制到所有行的 BE 列，以获取每个 IP 地址的四个部分。现在按 B 列到 E 列（按此顺序）对整个范围进行排序，如下所示：

在此处输入图片描述

如果您不想看到辅助列（BE），您可以隐藏它们。

答案2

最简单的，3步解决方案我可以建议你，，，

  1. 选择 IP 地址列，应用文本到列命令。

  2. 在相邻的列中写入此公式

    =CONCATENATE(B3，“。”,C3，“。”,D3，“。”,E3)

  3. 最后按升序排序。

检查屏幕截图。

在此处输入图片描述

注意：

红色的是原始 IP 地址（在 A 列中）。

绿色的将文本应用到列（B 列到 E 列）后。

黑色的是在应用连接和排序之后（F 列）。

原因很简单，最初 IP 地址是文本数据，而 Excel 不接受任何单元格格式将其转换为数字。

希望这对你有帮助。

答案3

这是我之前编写的一个 VBA 函数，用于解决同一问题。它生成一个正确排序的 IPv4 地址填充版本。

Function SortAddress(Address As String)                     '   format address as XXX.XXX.XXX.XXX to permit sorting

Dim FirstByte As Integer, LastByte As Integer, I As Integer

SortAddress = ""
FirstByte = 1

For I = 0 To 2                                          '   process the first three bytes

    LastByte = InStr(FirstByte, Address, ".")           '   find the dot
                                                        '   append the byte as 3 digits followed by dot
    SortAddress = SortAddress & Format(Mid(Address, FirstByte, LastByte - FirstByte), "000\.")

    FirstByte = LastByte + 1                            '   shift the start pointer

Next I

SortAddress = SortAddress & Format(Mid(Address, FirstByte), "000") ' process the last byte

End Function

简单示例：

结果

结果

公式

公式

您可以按“可排序”列进行排序并将其隐藏。

答案4

如果您不想使用公式或 VBA，请使用 Power Query。（在 Excel 2016 中，获取和转换，在 Excel 2010 或 2013 中安装 PowerQuery 插件以继续操作）。

  1. 将表放入 PowerQuery 编辑器。
  2. 右键单击“复制列”来复制该列
  3. 在“主页”选项卡上，按分隔符“拆分列”。选择“分隔符的每个出现位置”
  4. 从左到右对每列进行升序排序。
  5. 选择之前分割的列，右键点击并删除，关闭并加载。

相关内容