按包含 IP 地址的列对电子表格进行排序（每个单元格一个 IP 地址）

Question

使用默认排序引擎，以下列表被视为已排序：

10.0.0.219
10.0.0.22
10.0.0.223

这是不正确的。排序应该是：

10.0.0.22
10.0.0.219
10.0.0.223

假设前三个八位字节是静态的是不安全的。例如，其他 IP 地址包括：

10.35.10.11
10.28.66.30

Answer 1

我意识到这是一篇旧文章，但为了提供可行的解决方案，我提出以下内容。

只需将此公式放入相邻的单元格中，然后更新引用以指向包含您的 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")

Answer 2

有多种方法可以做到这一点，不确定这是为了更永久的使用还是只是一次性的。

1. 文本到列

   <块引用>

   您可以将 IP 地址分成几列，并使用高级排序将数据作为表格进行排序。

2. 排序

   <块引用>

   使用内置排序器，您可以创建自定义排序顺序。如果您只是在同一八位字节中添加地址（例如 10.0.0.x），那么您可以添加 10.0.0。作为您的订单，它会对剩余的订单进行数字排序。这可能不是预期的那样，但它又有效了。

   排序

3. 隐藏列

   <块引用>

   Google 建议了几个涉及附加列的答案，您可以在其中将 IP 地址分解为数值，然后按该列进行排序。

4. VBA

   <块引用>

   可以在 VBA 中完成这一切，并将所有数据放入内存并重写列表。这里的优势是重用和扩展的能力。

   VBA

Answer 3

如果您希望能够使用内置 =SORT() 函数之类的功能来执行此操作并避免创建额外的列，您可以将其添加为自定义脚本（扩展菜单 --> 应用程序）脚本）：

/**
 * Sorts a column of IP addresses.
 * Any strings that are not IP addresses will be sorted at the end of the list.
 * 
 * @param {Array<Array<string>>} ips The IP addresses to sort.
 * @return The sorted IP addresses.
 * @customfunction
 */
function SORT_IPS(ips) {
  const isIPAddress = input => {
    // https://stackoverflow.com/a/27434991
    return /^(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$/.test(input);
  }

  const dot2num = dot => {
    // https://stackoverflow.com/a/8105740
    var d = dot.split('.');
    return ((((((+d[0])*256)+(+d[1]))*256)+(+d[2]))*256)+(+d[3]);
  };

  const compareIPs = (a, b) => {
    if (isIPAddress(a[0]) && isIPAddress(b[0])) {
      // Both are IP addresses.
      // Sort as IP addresses.
      return dot2num(a[0]) - dot2num(b[0]);
    } else if (isIPAddress(a[0])) {
      // A is an IP address. B is not.
      // Put A before B.
      return -1;
    } else if (isIPAddress(b[0])) {
      // B is an IP address. A is not.
      // Put A after B.
      return 1;
    } else {
      // Neither is an IP address.
      // Sort like normal strings.
      return a[0].localeCompare(b[0]);
    }
  };

  return ips.sort(compareIPs);
}

保存该脚本后，您可以通过将 =SORT() 替换为 =SORT_IPS() 在电子表格中使用它。

Answer 4

可接受的解决方案将前导零添加回每个八位位组中，并添加一个新列作为排序依据。但如果您愿意添加一个新列作为排序依据；为什么不添加 IP 地址的整数值并按其排序。像这样：

例如，如果您的点分 IPv4 地址位于单元格 A2 中

--- 插入一个单元格 ---

=(2^24mid(A2,1,搜索(".",A2)-1))+(2^16mid(A2,搜索(".",A2)+1,搜索（”。”， A2,搜索(".",A2)+1)-搜索(".",A2)-1))+(2^8mid(A2,搜索(".",A2,搜索(". “,A2)+ 1)+1,搜索(".",A2,搜索(".",A2,搜索(".",A2)+1)+1)-搜索(".",A2,搜索(".", A2)+ 1)-1))+(2^0mid(干净(A2),搜索(".",A2,搜索(".",A2,搜索(".",A2)+1)+ 1)+1,3))

---end snip---

假设点分 IP 位于单元格 A2 中，带或不带前导零，如本例所示，并在排序后输出

原始 IP 十进制 IP：

192.168.1.100 3232235876

192.168.1.10 3232235786

1.1.1.1 16843009

192.168.1.14 3232235790

192.168.1.013 3232235789

192.168.001.1 3232235777

172.16.101.42 2886755626

172.16.101.04 2886755588

然后，使用 sort()函数中，我们按 B2 到 B9 的范围对多列进行排序 =sort(range, by column, asc=1) [单元格 A14 中的示例 =sort(A2:B9,B2:B9,1)]

排序的 IP 十进制 IP：

1.1。 1.1 16843009

172.16.101.04 2886755588

172.16.101.42 2886755626

192.168.001.1 3232235777

192.168.1.10 3232235786

192.168.1.013 3232235789

192.168.1.14 3232235790

192.168.1.100 3232235876

尽情享受。

Answer 5

尝试：

=LET(i; FILTER(A2:A; A2:A<>""); 
 SORT(i; BYROW(i; LAMBDA(i; JOIN("."; TEXT(SPLIT(i; ".";;); "000")))); 1))

如果需要，请将 1 更改为 0 以降序排列