使用VBA对值列表进行排序(从大到小) [英] Rank A List Of Values (Largest to Smallest) Using VBA

问题描述

我有一个VBA代码，用于返回完成的工作的乘员组ID，工作人员完成的工作总数，工作人员工作了多少天，然后返回每天的平均工作数量.它们没有特定的顺序，并且所有这些类别在excel电子表格中彼此相邻.在这些其他类别旁边的一栏中，我想编写一个VBA代码，根据每天谁拥有最多的工作量为每行分配一个值(1到10，因为有10名工作人员).

I have a VBA code to return a Crew ID for a job completed, the total number of jobs that crew did, how many days that crew was working, and then return the average number of jobs per day. They are in no particular order and all of these categories are adjacent to each other in the excel spreadsheet. In a column next to these other categories, I want to write a VBA code to assign each row a value (1 to 10, since there are 10 crews) based on who has the highest number of jobs per day.

我看了其他代码和建议，但是我发现的只是排序.我不想对它们进行排序，因为我可以轻松地在Excel中完成这些操作，并且如果我在VBA中为该特定列对值进行排序，则它们将不会与有关它们的其余信息保持一致.关于如何根据每天平均工作量最高或最低的人，在相邻的列中打印数字1到10，对如何对这些列进行排名"有何建议?

I have looked at other code and suggestions but all I can find is sorting. I don't want to sort them since I can easily do that in Excel and if I sort the values in VBA for that specific column, they will not line up with the rest of the information about them. Any suggestions on how I can "rank" these columns by printing the numbers 1 through 10 in the column adjacent based on who has the highest to lowest jobs per day average?

谢谢大家的帮助！

推荐答案

我从会计应计制"中剥离了此代码-为Excel Application开具发票，它是数据字段"上的多种类型之一.

I ripped this code out of Accounting Accrual - Invoicing true up Excel Application, it is one of many sorts on a data "field".

您可以执行类似的操作.为了使您更清楚地申请: wsBuild是一个命名工作表(在开发人员中，无论工作表名称或位置如何，都可以直接调用它). "twb"仅表示它正在本工作簿中工作，因为我正在合并多个工作簿中的数据(数据导出，本例中为3).

You can do something similar. To make this more clear for you to apply: wsBuild is a named sheet (in developer, you can call it directly regardless of worksheet name or position). "twb" just indicates that it is working in thisworkbook, as I am consolidating data from several workbooks (data exports, 3 in this case).

twbNameCol是在其他函数中找到的供应商名称的列号

twbNameCol is the col number for the vendor name found in a different function

twbJobNumberCol是在其他函数中找到的销售订单的列号

twbJobNumberCol is the col number for the sales order found in a different function

twbTot1Col是在其他函数中找到的销售金额的列号

twbTot1Col is the col number for the sales amount found in a different function

如果要动态查找标题，只需以这种方式选择比导入模板高的数字，则该数字可容纳总共2个列标题，一个用于应计，一个用于发票，您可能不需要这样做像这样，因为我从系统中导出，所以我知道标头名称，或者您可以自己创建，所以diff accrual是我要查找的最后一列，因此我打破了这一点的循环:

If you want to find the headers dynamically just choose a number higher than your import template in a manner like this, this one accommodates for having 2 total column headers one for accrual and one for invoices, you may not to need to do something like this, since I export from a system I know the header names, or you can make your own, diff accrual is the last column I am looking to find the position on so I break the loop at that point:

twbTot1Col = 0
twbTot2Col = 0
'First let's define the column positions
    For j = 1 To 50
        If InStr(wsBuild.Cells(1, j), "Totals") > 0 And twbTot1Col = twbTot2Col Then
            'It is the first time
            twbTot1Col = j
        ElseIf InStr(wsBuild.Cells(1, j), "Totals") > 0 And twbTot1Col <> twbTot2Col Then
            twbTot2Col = j
        ElseIf InStr(wsBuild.Cells(1, j), "JobNumber") > 0 Then
            twbJobNumCol = j
        ElseIf InStr(wsBuild.Cells(1, j), "New VAP") > 0 Then
            twbNewVAPCol = j
        ElseIf InStr(wsBuild.Cells(1, j), "New AP") > 0 Then
            twbNewAPCol = j
        ElseIf InStr(wsBuild.Cells(1, j), "Name") > 0 Then
            twbNameCol = j
        ElseIf InStr(wsBuild.Cells(1, j), "Diff Accrual") > 0 Then
            twbDiffCol = j
            j = 100 'Break loop
        Else
            'Do nothing
        End If
    Next j

twbLastRow是数据范围的最后一行(数据范围是2到LastRow)

twbLastRow is the last row in the data range (data range is 2 to LastRow)

发现是这样的

twbLastRow = wsBuild.Cells(Rows.Count, 1).End(xlUp).Row

这是排序代码的示例，在此1、2、3之前有一些列插入步骤，因此我只复制了排序

Here is an example of the sort code, there are some column insertion steps prior to this 1, 2, 3, so I only copied the sort

With wsBuild
'4) Perform 3 line sort
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range(.Cells(2, twbNameCol), .Cells(twbLastRow, twbNameCol)), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        .Sort.SortFields.Add Key:=.Range(.Cells(2, twbJobNumCol), .Cells(twbLastRow, twbJobNumCol)), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        .Sort.SortFields.Add Key:=.Range(.Cells(2, twbTot1Col), .Cells(twbLastRow, twbTot1Col)), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal


         .Sort.SetRange .Range(.Cells(1, 1), .Cells(twbLastRow, twbDiffCol + 2))
         .Sort.Header = xlYes
         .Sort.MatchCase = False
         .Sort.Orientation = xlTopToBottom
         .Sort.SortMethod = xlPinYin
         .Sort.Apply
    End With

如果您对此有所了解，则可以按照自己喜欢的任何方式对数据进行排名".此类为 1.供应商，然后 2.职位编号，然后 3.应计金额，然后乐趣便开始将发票导入与应计金额进行匹配.如果正确定义了排序范围，则不会丢失或错配相邻列中的任何数据.我想我正在游说让您使用排序.

If you take a look at this, you can "rank" your data in any which way you would like. This sort is 1. vendor then 2. job number then 3. accrual amount then the funs begins to match up invoice import against the accruals. If you define your sort range properly you will not lose or mismtach any data in adjacent columns. I guess I am lobbying for you to use sort.

如果提供一些代码，答案实际上可能是一种数据，插入一个等级列，放入等级，然后将数据按原样进行排序.而不是根据特定属性运行函数来排名"(更长的时间，但可行).欢呼，WWC

If you provide some code the answer could actually be a sort of your data, insert a rank column, put the ranks in, then unsort your data back to the way it was. Rather than running a function to "rank" based on a specific attribute (longer ..... but doable). Cheers, WWC

这篇关于使用VBA对值列表进行排序(从大到小)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！