在Excel中按组排序数据 [英] Sorting data by groups in Excel

问题描述

所以，我环顾四周，试图自己解决这个问题。这不是一个绝对关键的问题，我只是想知道是否可以完成。

So, I've looked around and tried to solve this on my own. This isn't an absolutely crucial question currently, I just want to know if it could be done.

所以让我们来看一下列出一些数据看起来喜欢

So let's say I've got a list with some data that looks like

Date      Location 
01/24/14  H-12
01/25/14  BB-44
01/30/14  G-12
01/29/14  7A-55
01/28/14  NN-15
01/24/14  GG-47

我想要的是通过位置对数据进行排序，但我不希望是一般的方式，否则我最终会得到7A-55，BB-44，G-12，H-12，NN-15。我想要对数据进行排序，以便将双字母和单个字母排序在一起。例如。一旦一切都被排序，它应该是G-12，H-12，BB-44，NN-15，7A-55。

What I want is to be able to sort the data by Location, but I don't want it to be the general way, otherwise I'll end up with 7A-55, BB-44, G-12, H-12, NN-15. I want the data to be sorted so that double letters and single letters are sorted together. E.G. it should be G-12, H-12, BB-44, NN-15, 7A-55 once everything has been sorted.

我尝试创建一个自定义列表排序，但它不工作。打算的方式我尝试的自定义列表是AZ，AA-ZZ，7A（项目被列出，但为了节省空间，我写了他们）。

I've tried creating a custom list sort, but it doesn't work. the way intended. The custom list I tried was A-Z, AA-ZZ, 7A (items were listed out, but for saving space I wrote them like that).

像我说的，这个如果不能做，这不是一个特别大的交易，它只会使它变得更容易一些。

Like I said, this isn't a particularly huge deal if it can't be done, it just would have made it a little easier.

编辑1 这是我想要的输出

Date      Location
01/30/12  G-12
01/24/14  H-12
01/25/14  BB-44
01/24/14  GG-47
01/28/14  NN-15
01/29/14  7A-55

编辑

所有这些都在我想要的方面工作，虽然如果我不得不选择最喜欢的，基数36转换一。这是一些真正的开箱即用的思考，而我的数学怪人也很欣赏。感谢大家！

Edit

All of these worked in the regards i wanted to, although if I had to choose a favorite it would be the base 36 number conversion one. That was some real out-of-the-box thinking and the math geek in me appreciated it. Thanks everyone!

推荐答案

它有效，但有点复杂，所以只是为了好玩：
这个UDF返回可用作排序键的值。它将代码转换为四位数的基数36，即使用A-Z和0-9作为符号（如十六进制使用0-9和A-F）。要获得所需的输出，我以字母顺序放置符号，首先是字母（所以A= 0，0= 26）。
（缺少的数字用零填充，在这种情况下为A）

它的工作原理;）

Well it works, but is a bit complex, so rather just for fun: This UDF returns a value that can be used as sort key. It transforms the code into a four-digit base 36-number, i.e. using A-Z and 0-9 as symbols (like hex uses 0-9 and A-F). To get at your desired output, I literally put the symbols in this order, letters first (so "A" = 0 and "0" = 26). (The missing 'digits' are filled up with zeros, which are in this case "A"s)
It works ;)

Public Function Base36Transform(r As Range) As Long
    Dim s As String, c As String
    Dim v
    Dim i As Integer
    Dim rv As Long

    v = Split(r.Text, "-")
    s1 = v(0)
    s2 = v(1)
    s = Right("A" & s1, 2) & Right("A" & s2, 2)
    rv = 0
    For i = 1 To Len(s)
        c = Mid(s, Len(s) - i + 1, 1)
        If c Like "#" Then
            rv = rv + (Val(c) + 26) * (36 ^ (i - 1))
        Else
            ' c is like "[A-Z]"
            rv = rv + (Asc(c) - Asc("A")) * (36 ^ (i - 1))
        End If
    Next
    Base36Transform = rv
End Function

这篇关于在Excel中按组排序数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！