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

发布于 2024-12-10 18:32:44 字数 257 浏览 0 评论 0原文

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

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

Using the default sorting engine, the following list is considered sorted:

10.0.0.219
10.0.0.22
10.0.0.223

This is incorrect. The sorting should be:

10.0.0.22
10.0.0.219
10.0.0.223

It is not safe to assume the first three octets will be static. For instance, other IP addresses include:

10.35.10.11
10.28.66.30

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

音盲 2024-12-17 18:32:44

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

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

I realize this is an old post, but in the interest of providing a working solution, I present the following.

Just put this formula in an adjacent cell and update the references to point to the cell containing your IP address (A1 in this example). This will produce a result similar to 010.121.008.030, which can then be sorted (correctly) alphabetically. Then set the column width to zero and voila. Time to enjoy a cup of coffee.

=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")
冷默言语 2024-12-17 18:32:44

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

  1. 文本到列

    <块引用>

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

  2. 排序

    <块引用>

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

    排序

  3. 隐藏列

    <块引用>

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

  4. VBA

    <块引用>

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

    VBA

There's several ways to do this, not sure if this is for more permanent use or just a one off.

  1. Text to Columns

    You can split the IP address into columns and sort the data as a table using an advance sort.

  2. Sort

    Using the built in sorter, you can create a custom sort order. If you are just doing addresses in the same octet (e.g. 10.0.0.x) then you can add 10.0.0. as your order and it will do a numeric sort on the left overs. This is probably not as intended, but then again it works.

  3. Hidden Column

    Google suggests several answers involving an additional column where you break out the IP address as a numeric value and then sort on that column.

  4. VBA

    Could do this all in VBA and slurp all the data up into memory and rewrite the list. Advantage here would be the ability to reuse and extend.

寄居者 2024-12-17 18:32:44

如果您希望能够使用内置 =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() 在电子表格中使用它。

If you want to be able to do this with something like the built-in =SORT() function and avoid creating extra columns, you can add this as a custom script (Extensions menu --> Apps Script):

/**
 * 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);
}

After saving that script, you can use it in your spreadsheet by replacing =SORT() with =SORT_IPS().

凉墨 2024-12-17 18:32:44

可接受的解决方案将前导零添加回每个八位位组中,并添加一个新列作为排序依据。但如果您愿意添加一个新列作为排序依据;为什么不添加 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

尽情享受。

The accepted solution adds the leading zeros back into each octet and adds a new column to sort by. But if you are willing to add a new column to sort by; why not add the integer value of the IP address and sort by that. like this:

Exmple if your dotted IPv4 address is in Cell A2

---snip into one cell---

=(2^24mid(A2,1,search(".",A2)-1))+(2^16mid(A2,search(".",A2)+1,search(".",A2,search(".",A2)+1)-search(".",A2)-1))+(2^8mid(A2,search(".",A2,search(".",A2)+1)+1,search(".",A2,search(".",A2,search(".",A2)+1)+1)-search(".",A2,search(".",A2)+1)-1))+(2^0mid(clean(A2),search(".",A2,search(".",A2,search(".",A2)+1)+1)+1,3))

---end snip---

assuming the dotted IP is in cell A2 with or withour leading zeros like this example and output after sorting

Original IP Decimal 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

Then, using the sort() function, we sort multiple columns by range of B2 thru B9 =sort(range, by column, asc=1) [example in cell A14 =sort(A2:B9,B2:B9,1)]

Sorted IP Decimal 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

Enjoy.

温柔女人霸气范 2024-12-17 18:32:44

尝试:

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

在此处输入图像描述

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

try:

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

enter image description here

change 1 to 0 for descending order if needed

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文