planilla - Los diez primeros pedidos en Excel según las complejas reglas del equipo

orden de pedido formato

Question

Tengo una hoja de cálculo excel en un formato similar al siguiente ...

| 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   |

Necesito convertir esta tabla en una lista de los equipos "Top Ten". Las reglas son

• Cada puntaje del equipo se toma de la suma de cuatro miembros de ese club.
• Estos totales deben ser de los mejores cuatro puntajes, excepto ...
  • Cada equipo debe consistir en al menos un Junior o Lady

Por ejemplo, en la tabla anterior, la puntuación del equipo para el club A sería de 625, no de 640, ya que tomaría los puntajes de Harry (190), Bert (150), Jim (150) y Simon (130). No podría tomar la puntuación de Fred (145) ya que eso solo le daría a los Gents.

Mi pregunta es, ¿se puede hacer esto fácilmente como una serie de fórmulas de Excel, o tendré que recurrir a usar algo más de procedimiento?

Idealmente, la solución debe ser automática en las selecciones del equipo, no quiero tener que crear una fórmula separada hecha a mano para cada equipo. Tampoco necesariamente tendré una lista cuidadosamente ordenada de los miembros de cada club. Aunque probablemente podría generar la lista a través de una hoja de cálculo adicional.

Answer 1

Escribir una solución en VBA sería mi primera opción, especialmente si las reglas tienen la posibilidad de volverse más complejas.

Answer 2

No creo que esto se pueda hacer a menos que la tabla esté ordenada de alguna manera. La mayoría de las funciones de búsqueda de Excel requieren listas ordenadas. Esto ciertamente podría hacerse con una función VBA.

Answer 3

Use una tabla dinámica que actuará como una consulta de base de datos sobre los datos que tiene. Haga pivotar para que los equipos bajen las columnas y los miembros del equipo junto con su tipo de estado crucen la tabla dinámica. No estoy seguro para 2003, pero Excel 2007 te permite ordenar para que los puntajes más altos aparezcan a la izquierda. Entonces su primera suma simplemente puede tomar los primeros tres puntajes para cada equipo. Sin embargo, para obtener la última suma de personas, debe determinar si puede usar el 4to puntaje, o si tiene que usar el máximo de los tipos junior o Lady. Eso podría hacerse usando una fórmula compleja y de fuerza bruta de la siguiente manera:

si (tipo de posición 1 es un junior o una dama o ... 2 o 3 ...) luego use la posición 4 más, si la posición 5 es una junior o dama, luego use 5 else si p 6 es ... y así sucesivamente .

Answer 4

esto se puede hacer fácilmente como una serie de fórmulas de Excel

Respuesta corta, SÍ. (Dependiendo de su definición de "fácil").

Respuesta larga...

(Creo que esto funciona)

Aquí están mis (breves) datos de prueba:

    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

Ahora, si he entendido las reglas correctamente, queremos los mejores cuatro puntajes, excepto que si el puntaje más alto por una dama o un junior no está entre los mejores cuatro, lo usamos en lugar del cuarto más alto. Lo he reformulado un poco, por razones que pueden ser evidentes ...

DE ACUERDO. ¡Arregla fórmulas al rescate! (Espero)

El puntaje más alto del equipo a debe ser

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

donde {} indica una fórmula de matriz creada usando Control-Shift-Enter para ingresar la fórmula. Los cuatro mejores están creados de manera similar. Para el bit Lady / Junior, necesitamos un poco más de complejidad. Tomando a la Señora, necesitamos esto:

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

Junior puede ser dejado como un ejercicio seguro para el estudiante, espero.

Ahora estoy mirando una mesa con el siguiente diseño para el club "a"

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

El puntaje del club debería ser los tres puntajes "cualquiera" más la mejor dama o menor si no están entre los cuatro primeros .

Entonces en Q2 estoy poniendo esto:

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

MAX (O2, P2) me dice la mejor puntuación de dama o junior, que debe incluirse. Si es más alto que el cuarto puntaje más alto del equipo, entonces ya está en la lista y solo tomamos los cuatro primeros. De lo contrario, reemplazamos la cuarta puntuación más alta con la mejor dama / junior.

Ahora podríamos hacerlo todo en una fórmula, sustituyendo las partes en la fórmula final:

{=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)))}

Pero no lo recomiendo ...

Entonces, para los datos anteriores, termino con esto:

            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       

Ratas En mi emoción en (creo) conseguir que la parte difícil funcione, olvidé mencionar que

• La lista de puntajes puede estar en cualquier orden
• Puedes obtener la clasificación de los clubes con RANK ()
• Luego puede sacar los 10 primeros en otra tabla usando MATCH () e 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   

Las columnas A y B son nuestros puntajes calculados, la columna E es el orden en que los clubes se mostrarán en la mesa final. Las otras fórmulas son las siguientes:

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

Answer 5

Lo que hago es cojo, pero funciona.

Simplemente haga una nueva columna y luego inserte esta fórmula =If(a1=N,b1,0) donde A1 es la columna de criterios, N es el criterio y B1 está en la columna de la que está tratando de obtener el tamaño. Entonces solo hago la fórmula grande en otra columna.

A veces me pongo de moda y en lugar de extender una N , hago que diga $C$1 , luego deletreo los criterios en esa celda.

La respuesta perfecta sería que Microsoft agregara un gran largeifs (lea este Microsoft)

Answer 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

Use como =TopTen(H2,$B$2:$E$30) donde H2 contiene la letra del club.