Início / computer / Perguntas / 1473766
Accepted
Karu
Asked: 2019-08-22 09:09:35 +0800 CST

Fórmula do Excel - Média dos resultados do vlookup para todos os outros campos em um campo de origem

  • 772

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

A célula F2e todas as outras linhas na coluna Ftêm um identificador de classificação, como "S3" ou "G1" (para classificações prata 3 ou ouro 1).

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

Portanto, para todas as outras linhas nesse intervalo ( F2:F100), preciso obter o identificador, convertê-lo em sua forma numérica por meio de um VLOOKUPcontra a Lookupsfolha, fazer um FLOOR(AVERAGE()do resultado e converter esse número de volta para um formato 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 um posto a partir de sua versão textual, a fórmula é:

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

O que 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 folha de consulta, podemos ver que S3 representa uma pontuação numérica de 10. O G2 do próximo jogador seria uma pontuação de 15 e assim por diante.

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

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

microsoft-excel worksheet-function

3 respostas

  1. Eu resolveria a primeira pesquisa, onde você precisa de um valor em todas as outras linhas, colocando a pesquisa dentro de uma fórmula iferror que apenas retorna um texto em branco quando não consegue encontrar um resultado:

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

    Em seguida, você pode apenas 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 de pesquisa não funcionará para obter a "Classificação" dessa média porque ela sempre começa à esquerda e se move para a direita - onde você precisa olhar para a direita e mover para a esquerda. Para isso, eu uso um combo Index/Match:

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

    Para a parte correspondente, usei um parâmetro "1" para que encontrasse o valor mais próximo sem ultrapassar, assumindo que pode não ser uma correspondência exata. Confira o índice e as descrições da fórmula 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 isso ajude: Exemplo de solução com exemplos de fórmula mostrados

    • 1
  2. Best Answer

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

    A coisa mais fácil a fazer seria usar essas células em branco para manter os valores numéricos das classificações individuais. Você pode 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 para obter uma média dos valores de pesquisa.

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

    Se você não deseja redirecionar essas células em branco, uma 'coluna auxiliar' é uma alternativa viável. Testei o método em branco com o 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, xlCellTypeConstants deverá ser substituído por xlCellTypeFormulas .

    • 1

  3. Supondo que você deseja obter a média de linhas pares, use a seguinte fórmula:

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

    Outra utilização:

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

    Em seguida, use =AVERAGE(G1:G5)para obter a média

    • 0

relate perguntas