☰

Ejemplo de datos

tag-icon • tag-icon microsoft-excel worksheet-function
Ejemplo de datos

Estoy escribiendo una hoja de organización para un equipo incipiente de deportes electrónicos y mi objetivo aquí es calcular la clasificación promedio en el juego de los jugadores de mi equipo.

celular F2y cadaotrola fila de la columna Ftiene un identificador de rango, como "S3" o "G1" (para los rangos plata 3 u oro 1).

Tengo otra hoja que contiene asignaciones numéricas entre estos identificadores de rango (I1 es 1, I2 es 2, etc.)

Entonces, para cada dos filas en este rango ( F2:F100), necesito obtener el identificador, convertirlo a su forma numérica por medio de una VLOOKUPcontra la Lookupshoja, hacer una FLOOR(AVERAGE()del resultado y convertir ese número nuevamente a una forma textual invirtiendo el VLOOKUP.

Ya he realizado un par de pasos, pero no estoy seguro de cómo promediar los resultados de una fórmula anterior como esta.

Para obtener la versión numérica de un rango a partir de su versión textual, la fórmula es:

=VLOOKUP(F2, Lookups!A2:B29,2, FALSE)

Lo que no sé es:

  • Cómo hacer una referencia de celda que omita cada dos celdas
  • Cómo promediar una gran cantidad de resultados de BUSCARV

Ejemplo de datos

hoja principal

Hoja de cálculo principal

Tabla de búsqueda

Hoja de cálculo de búsquedas

Cada conjunto de dos columnas representa a un solo jugador, y el rango de ese jugador se almacena en la fila F de la columna par. En la hoja de búsqueda, podemos ver que S3 representa una puntuación numérica de 10. El G2 del siguiente jugador sería una puntuación de 15, y así sucesivamente.

Una vez que tengo la suma de estos números, tomo el promedio, lo que me da una puntuación promedio para el equipo. Volviendo a la tabla de búsqueda, esto se hace a la inversa de la operación anterior y me da una clasificación única que corresponde a la puntuación promedio de cada jugador que figura en la hoja.

Tomando a los tres jugadores en la hoja actualmente, elegiría S3, G2 y NA. Esto me da una puntuación de 10, 15 y 10. El promedio de estos es 11 (redondeado hacia abajo), lo que corresponde a una clasificación de S2 según la tabla de búsqueda. La celda que contiene esta fórmula hipotética diría "S2".

Respuesta1

P:¿Permanecerán en blanco F3, F5, F7, etc.?
R: Lo harán. He atenuado el campo para mostrar que no se debe ingresar nada allí.

Lo más fácil sería utilizar estas celdas en blanco para contener los valores numéricos de los rangos individuales. Luego puede promediar F2:F101 y solo se considerarán los números verdaderos. Los números se pueden ocultar de la visualización utilizando el formato de número de celda ;;;. Esto muestra una celda en blanco para cualquier cosa escrita o devuelta por una fórmula mientras se conserva el valor sin formato.

Si no desea copiar y pegar 50 veces, ejecute este breve subprocedimiento después de ajustar el nombre de la hoja de trabajo principal en la primera línea de código.

Option Explicit

Sub PopulateLookups()

    With Worksheets("sheet1")
        With .Range(.Cells(2, "F"), .Cells(.Rows.Count, "F").End(xlUp))
            With .SpecialCells(xlCellTypeConstants, xlTextValues)  '<~~ see footnote
                With .Offset(1, 0)

                    .FormulaR1C1 = "=vlookup(r[-1]c, lookups!r2c1:r29c2, 2, false)"
                    .NumberFormat = ";;;"

                End With
            End With
        End With
    End With

End Sub

Ahora puede utilizar uno de los siguientes para lograr un promedio de los valores de búsqueda.

=average(F2:F100)
=average(F:F)

Si no desea reutilizar esas celdas en blanco, una 'columna auxiliar' es una alternativa viable. Probé el método en blanco con el método de la columna auxiliar y los resultados fueron idénticos.

¹ Si los rangos existentes se devuelven mediante una fórmula y no se escriben, entoncesConstantes xlCellTypedebe ser reemplazado porFórmulas de tipo de celda xl.

Respuesta2

Resolvería la primera búsqueda, donde se necesita un valor en cada dos filas, colocando vlookup dentro de una fórmula iferror que simplemente devuelve texto en blanco cuando no puede encontrar un resultado:

=IFERROR(VLOOKUP(F2,Lookups!A2:B29,2,0),"")

A continuación, puedes usar la fórmula promedio, dentro de una fórmula de redondeo hacia abajo para truncar los decimales. La fórmula promedio simplemente ignorará las filas en blanco.

Por último, una fórmula de búsqueda virtual no funcionará para obtener el "Rango" de este promedio porque siempre comienza en la izquierda y se mueve hacia la derecha, donde es necesario mirar hacia la derecha y moverse hacia la izquierda. Para esto, uso un combo Índice/Coincidencia:

=INDEX(Lookups!A2:B29,MATCH(E11,Lookups!B2:B29,1),1)

Para la parte de coincidencia, utilicé un parámetro "1" para encontrar el valor más cercano sin pasarse, asumiendo que podría no ser una coincidencia exacta. Consulte las descripciones de fórmulas de índice y coincidencia dentro de Excel para obtener más información y opciones.

Construí esto en una hoja de muestra que muestra las fórmulas escritas. Espero que esto ayude:Solución de muestra con ejemplos de fórmulas mostradas

Respuesta3

Suponiendo que desea obtener el promedio de las filas pares, utilice la siguiente fórmula:

=IF(ROW()/2 = ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

De lo contrario, utilice:

=IF(ROW()/2 <> ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

Luego use =AVERAGE(G1:G5)para obtener el promedio

información relacionada