☰

Exemplo de dados

tag-icon • tag-icon microsoft-excel worksheet-function
Exemplo de dados

Estou escrevendo uma planilha de organização para uma equipe iniciante de esportes eletrônicos e meu objetivo aqui é calcular a classificação média no jogo dos jogadores da minha equipe.

Célula F2e cadaoutrolinha na coluna Fpossui um identificador de classificação, como "S3" ou "G1" (para classificações prata 3 ou ouro 1).

Tenho outra planilha que contém mapeamentos numéricos entre esses identificadores de classificação (I1 é 1, I2 é 2, etc.)

Portanto, para cada outra linha neste intervalo ( F2:F100), preciso obter o identificador, convertê-lo para sua forma numérica por meio de a VLOOKUPna Lookupsplanilha, fazer a FLOOR(AVERAGE()do resultado e converter esse número de volta para uma forma textual invertendo o VLOOKUP.

Já concluí algumas etapas, mas não tenho certeza de como calcular a média dos resultados de uma fórmula anterior como esta.

Para obter a versão numérica de uma classificação a partir de sua versão textual, a fórmula é:

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

O que eu não sei é:

  • Como fazer uma referência de célula que omite todas as outras células
  • Como calcular a média de uma grande quantidade de resultados VLOOKUP

Exemplo de dados

Folha principal

Planilha principal

Tabela de pesquisa

Planilha de pesquisas

Cada conjunto de duas colunas representa um único jogador, com a classificação desse jogador armazenada na linha F da coluna de número par. Na planilha de pesquisa, podemos ver que S3 representa uma pontuação numérica de 10. O G2 do próximo jogador teria uma pontuação de 15 e assim por diante.

Assim que tenho a soma desses números, faço a média, dando-me uma pontuação média para a equipe. De volta à tabela de pesquisa, isso é feito ao contrário da operação anterior e me dá uma classificação única que corresponde à pontuação média de cada jogador listado na planilha.

Tomando os três jogadores da ficha atualmente, eu escolheria S3, G2 e NA. Isso me dá uma pontuação de 10, 15 e 10. A média deles é 11 (arredondada para baixo), o que corresponde a uma classificação S2 usando a tabela de pesquisa. A célula que contém esta fórmula hipotética diria “S2”.

Responder1

P:F3, F5, F7, etc permanecerão em branco?
R: Eles vão. Deixei o campo acinzentado para mostrar que nada deve ser inserido lá.

A coisa mais fácil a fazer seria usar essas células em branco para armazenar os valores numéricos das classificações individuais. Você pode então calcular a média de F2:F101 e apenas os números verdadeiros serão considerados. Os números podem ser ocultados da exibição usando o formato de número de célula ;;;. Isso exibe uma célula em branco para qualquer coisa digitada ou retornada de uma fórmula, mantendo o valor bruto.

Se você não quiser copiar e colar 50 vezes, execute este pequeno subprocedimento após ajustar o nome da planilha principal na primeira linha 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

Agora você pode usar um dos seguintes itens para obter uma média dos valores de pesquisa.

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

Se você não quiser redirecionar essas células em branco, uma 'coluna auxiliar' é uma alternativa viável. Testei o método em branco em relação ao método da coluna auxiliar e os resultados foram idênticos.

¹ Se as classificações existentes forem retornadas por fórmula e não digitadas, entãoxlCellTypeConstantsdeverá ser substituído porxlCellTypeFormulas.

Responder2

Eu resolveria a primeira pesquisa, onde você precisa de um valor em cada linha, colocando o vlookup dentro de uma fórmula iferror que apenas retorna texto em branco quando não consegue encontrar um resultado:

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

Em seguida, você pode simplesmente usar a fórmula da média, dentro de uma fórmula de arredondamento para truncar os decimais. A fórmula média simplesmente ignorará as linhas em branco.

Por último, uma fórmula vlookup não funcionará para obter a "Classificação" dessa média porque ela sempre começa na esquerda e se move para a direita - onde você precisa olhar para a direita e mover para a esquerda. Para isso, uso um combo Index/Match:

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

Para a parte de correspondência, usei um parâmetro "1" para encontrar o valor mais próximo sem ultrapassar, assumindo que pode não ser uma correspondência exata. Confira o índice e as descrições das fórmulas de correspondência dentro do Excel para obter mais informações e opções.

Eu construí isso em uma planilha de amostra que mostra as fórmulas escritas. Espero que isto ajude:Solução de exemplo com exemplos de fórmulas mostrados

Responder3

Supondo que você queira obter a média das linhas pares, use a seguinte fórmula:

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

Caso contrário, use:

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

Então use =AVERAGE(G1:G5)para obter a média

informação relacionada