All geek questions in one place

Ten orders in Excel based on complex command rules

I have an Excel spreadsheet in a format similar to the following ...

| NAME  | CLUB | STATUS | SCORE |
| Fred  |  a   | Gent   | 145   |
| Bert  |  a   | Gent   | 150   |
| Harry |  a   | Gent   | 195   |
| Jim   |  a   | Gent   | 150   |
| Clare |  a   | Lady   | 99    |
| Simon |  a   | Junior | 130   |
| John  |  b   | Junior | 130   |
   :
   :
| Henry |  z   | Gent   | 200   |

I need to convert this table to the "Top Ten" command list. Rules:

  • Each team point is taken from the sum of four members of this club.
  • These totals should be from the top four points, except ...
    • Each team must consist of at least one junior or lady.

For example, in the table above, the team score for Club A will be 625 not 640, since you will take the grades for Harry (190), Burt (150), Jim (150) and Simon (130). You could not take Fred's account (145), since that would only give you Gents.

My question is, can this be done easily, like a series of Excel formulas, or do I need to resort to using something more procedural?

, . . , , .

+3
6
Public Function TopTen(Club As String, Scores As Range)

    Dim i As Long
    Dim vaScores As Variant
    Dim bLady As Boolean
    Dim lCnt As Long
    Dim lTotal As Long

    vaScores = FilterOnClub(Scores.Value, Club)
    vaScores = SortOnScore(vaScores)

    For i = LBound(vaScores, 2) To UBound(vaScores, 2)
        If lCnt = 3 And Not bLady Then
            If vaScores(3, i) <> "Gent" Then
                lTotal = lTotal + vaScores(4, i)
                bLady = True
                lCnt = lCnt + 1
            End If
        Else
            lTotal = lTotal + vaScores(4, i)
            lCnt = lCnt + 1
            If vaScores(3, i) <> "Gent" Then bLady = True
        End If

        If lCnt = 4 Then Exit For
    Next i

    TopTen = lTotal

End Function

Private Function FilterOnClub(vaScores As Variant, sClub As String) As Variant

    Dim i As Long, j As Long
    Dim aTemp() As Variant

    For i = LBound(vaScores, 1) To UBound(vaScores, 1)
        If vaScores(i, 2) = sClub Then
            j = j + 1
            ReDim Preserve aTemp(1 To 4, 1 To j)
            aTemp(1, j) = vaScores(i, 1)
            aTemp(2, j) = vaScores(i, 2)
            aTemp(3, j) = vaScores(i, 3)
            aTemp(4, j) = vaScores(i, 4)
        End If
    Next i

    FilterOnClub = aTemp

End Function

Private Function SortOnScore(vaScores As Variant) As Variant

    Dim i As Long, j As Long, k As Long
    Dim aTemp(1 To 4) As Variant

    For i = 1 To UBound(vaScores, 2) - 1
        For j = i To UBound(vaScores, 2)
            If vaScores(4, i) < vaScores(4, j) Then
                For k = 1 To 4
                    aTemp(k) = vaScores(k, j)
                    vaScores(k, j) = vaScores(k, i)
                    vaScores(k, i) = aTemp(k)
                Next k
            End If
        Next j
    Next i

    SortOnScore = vaScores

End Function

=TopTen(H2,$B$2:$E$30), H2 .

+2

, Excel

, YES. ( "" ).

...

( , )

() :


    A          B    C        D
 1 NAME      CLUB STATUS  SCORE
 2 Kevin    a   Gent    145
 3 Lyle     a   Gent    150
 4 Martin   a   Gent    195
 5 Norm     a   Gent    150
 6 Oonagh   a   Lady    100
 7 Arthur   b   Gent    200
 8 Brian    b   Gent    210
 9 Charlie  b   Gent    190
10 Donald   b   Gent    220
11 Eddie    b   Junior  150
12 Quentin  c   Gent    145
13 Ryan     c   Gent    150
14 Sheila   c   Lady    195
15 Trevor   c   Gent    150
16 Ursula   c   Junior  200

, , , , , . , , ...

OK. ! ()

a

{=LARGE(IF(B2:B16="a",D2:D16,0),1)}

{} , Control-Shift-Enter . . / . , :

{=LARGE(IF($B$2:$B$16=$J3,IF($C$2:$C$16="Lady",$D$2:$D$16,0),0),1)}

, .

"a"


     J    K      L      M      N      O      P
 1 Club    1      2      3      4   Lady  Junior
 2 a     195    150    150    145    100      0

"", , .

, Q2 :

=SUM(K2:M2)+MIN(MAX(O2,P2),N2)

MAX (O2, P2) , . , , , . /.

, :

{=LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),1)+
LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),2)+
LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),3)+
MIN(LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),4),
MAX(LARGE(IF($B$2:$B$18=$J3,IF($C$2:$C$18="Lady",$D$2:$D$18,0),0),1),
LARGE(IF($B$2:$B$18=$J3,IF($C$2:$C$18="Junior",$D$2:$D$18,0),0),1)))}

...

, :


            Anyone                                          Lady        Junior                
Club        1           2           3           4           1           1           Total     
a           195         150         150         145         100         0           595       
b           220         210         200         190         0           150         780       
c           200         195         150         150         195         200         695

. ( ), , ,

  • RANK()
  • 10 MATCH() INDEX()

    A               B       C       D           E       F       G               H    
1   club            Sc      Rank    UniqRk              Pos     Club            Score
2   third-equal#1   80      3       79.999980   1       1       best            100  
3   second          90      2       89.999970   2       2       second          90   
4   third-equal#2   80      3       79.999960   3       3       third-equal#1   80   
5   best            100     1       99.999950   4       3       third-equal#2   80   
6   worst           70      5       69.999940   5       5       worst           70

A B - , E - , . :

C: =RANK(B2,$B$2:$B$6)      # what it says, with ties both getting the lower number
D: =B2-ROW()*0.00001        # score, modified slightly to ensure uniqueness
F: =SMALL($C$2:$C$6,E2)     # first output column, ranks including ties
G: =INDEX($A$2:$A$6,MATCH(LARGE($D$2:$D$6,E2),$D$2:$D$6,0))
                            # club name for position, using the modified score in D
H: =INDEX($B$2:$B$6,MATCH(LARGE($D$2:$D$6,E2),$D$2:$D$6,0))
                            # as G, but indexes into scores
+2

, , , .

, =If(a1=N,b1,0), A1 - , N - B1 , . .

, N, , $C$1, .

- Microsoft largeifs (, Microsoft)

+1

, . Pivot, , . 2003 , Excel 2007 , . . , , , 4- , . , :

( 1 - ... 2 3...), 4, 5 , 5 else, p 6 is... ..

0
source

Writing a solution in VBA would be my first choice, especially if the rules can become more complex.

0
source

I do not think that this can be done if the table is not sorted in any way. Most Excel search functions require ordered lists. This can be done using the VBA function.

0
source

Source: https://habr.com/ru/post/1696828/

More articles:


All Articles