Como lidar com valores duplicados usando a função LARGE ()?

3

Eu quero calcular os 3 principais resultados com valores duplicados, mas nomes diferentes.

Exemplo de dados:

Name           score
A              40
B              55
C              37
D              55            

Resultado esperado:

order        score     name
1            55        B
1            55        D
3            40        A
    
por Dimah 05.06.2018 / 12:39

3 respostas

3

Você pode conseguir isso com uma tabela dinâmica, bem como com a função RANK() .

Etapa 1 - Preparando a tabela dinâmica

  1. Adicione uma coluna (por exemplo, em C) aos seus dados, com a seguinte fórmula: =RANK(B2,$B$2:$B$5) - > Isso exibirá como B2 está entre todos os valores em $B$2:$B$5

  • Selecione seus dados (incluindo a coluna de classificação) e clique em Inserir > Tabela Dinâmica
  • Coloque os campos "Pontuação" e "Nome" como linhas e o campo "Pontuação" como valores mostrados
  • Formate a tabela dinâmica clicando nos seguintes itens de menu:
  • Agoravocêdeveteralgosemelhanteaisso:

    Passo2-Exibindoos3principaisvalores(MétodoA)

    1. Cliquenasetapertodoprimeirocampodasuatabeladinâmica(classificação)
    2. Selecione"Filtros de valor"
    3. Selecione "10 melhores"

  • Insira 3 em vez de 10 para ter os três principais valores
  • Agoravocêdeveteralgoassim:

    Observação:comessemétodo,seváriosvalorestiveremamesmaclassificação(comoemseusdadosdeexemplo),elesserãomostradosváriasvezes.Esseéocomportamentopadrãodatabeladinâmicaedosrecursos"Top X".

    Se você não quiser 4 linhas, mas apenas 3, consulte o método B

    Passo 2 - Exibindo os 3 principais valores (Método B)

    1. Clique na seta perto do primeiro campo da sua tabela dinâmica (classificação)
    2. Selecione "Filtros de etiquetas"
    3. Selecione "Menor que ou igual a" e insira 3 quando solicitado

    Nota: tenha cuidado com este método: se houver 5 valores com rank 1, haverá 5 linhas. O segundo maior valor terá então a classificação 6 e não será exibido (é maior que 3). Então não é mais um top 3, mas acho que esse método vale a pena mencionar, já que em alguns casos pode funcionar.

    Quando os dados mudarem, não se esqueça de atualizar a tabela dinâmica!

        
    por 05.06.2018 / 13:37
    1

    Paraobterapontuação,emCellF3,escrevaestafórmulaepreencha3Rowdown:

    =LARGE(B$3:B$6,ROWS(F$3:F3)).

    Paraobteronome,emcélulaG3escrevaestafórmuladematrizepreencha:

    N.B.ConcluaestafórmuladematrizcomCtrl+Shift+Enter.

    {=IFERROR(INDEX(A$3:A$6,SMALL(IF(B$3:B$6=F3,ROW(B$3:B$6)-ROW(F$3)+1),COUNTIF(F$3:F3,F3))),"")}
    

    Para obter o pedido, em Cell E3 , escreva esta fórmula e preencha:

    =IFERROR(RANK(LARGE(B$3:B$6,ROWS(E$3:E3)),$F$3:$F$6),"")

    Como a fórmula funciona:

    • A fórmula na F3 encontra os 3 melhores resultados.
    • A fórmula no G3 encontra nomes para os 3 melhores resultados, compara o resultado do Coluna F com Coluna B e nome da coluna A
    • A fórmula no E3 localiza a classificação para as 3 melhores pontuações.
    por 05.06.2018 / 19:54
    1

    Isso pode ser feito com apenas duas fórmulas simples para as colunas order e score , além de uma terceira, um pouco mais complicada, para a coluna name .

    Configure uma planilha como a seguinte:

    Emseguida,arraydemúltiplascélulas-insiraessasfórmulasnosintervalosespecificados(lembrando-sedeexcluiros{e}):

    Fórmula1(D2:D4):

    {=RANK(E2:E4,E2:E4)}

    Fórmula 2 ( E2:E4 ):

    {=LARGE(B2:B5,{1;2;3})}
    

    Fórmula 3 ( F2:F4 ):

    {=INDEX(A:A,10^5-MOD(LARGE(10^5*B2:B5+10^5-ROW(B2:B5),{1;2;3}),10^5))}
    

    Para alterar dos três principais resultados para os primeiros N resultados, em que N é um número maior, digamos 10 , por exemplo, em vez de usar uma matriz codificada para as classificações, {1;2;3;4;5;6;7;8;9;10} , use o seguinte:

    ROW(INDEX(X:X,1):INDEX(X:X,10))
    

    Embora isso funcione como é, é preferível substituir X:X pela coluna na qual a fórmula foi inserida. Dessa forma, a fórmula não será interrompida se a coluna X for excluída.

    Esta é uma solução muito melhor que a onipresente e mais curta ROW(INDIRECT("1:10")) , pois é não volátil.

    Explicações da fórmula:

    As duas primeiras fórmulas são simples o suficiente.

    A versão prettificada da terceira fórmula é a seguinte:

    =
    INDEX(
      (A:A),
      10^5-
      MOD(
        LARGE(
          10^5*B2:B5+10^5-ROW(B2:B5),
          {1;2;3}
        ),
        10^5
      )
    )
    

    A fórmula funciona modificando as pontuações para que também contenham o complemento dez do índice da linha. Então, depois que LARGE selecionar a pontuação modificada apropriada, o índice será extraído e usado com INDEX() para obter o nome correspondente à pontuação selecionada.

    Percorrendo a fórmula em F3 deve-se esclarecer:

    • 10^5*B2:B5
      10^5*{40;55;37;55}
      {4000000;5500000;3700000;5500000} , as pontuações escaladas
    • 10^5-ROW(B2:B5)
      10^5-{2;3;4;5}
      {99998;99997;99996;99995} , o complemento dez dos números de linha das pontuações
    • {4000000;5500000;3700000;5500000}+{99998;99997;99996;99995}
      {4099998;5599997;3799996;5599995} , as pontuações "modificadas"
    • LARGE({4099998;5599997;3799996;5599995},{1;2;3})
      {5599997;5599995;4099998} , as três principais pontuações modificadas
    • 10^5-MOD({5599997;5599995;4099998},10^5)
      10^5-{99997;99995;99998}
      {3;5;2} , os números das três principais pontuações
    • INDEX(A:A,{3;5;2})
      {B;D;A}
      D desde que estamos na segunda célula da fórmula inserida na matriz de células múltiplas

    Notas:

    • A fórmula prettificada realmente funciona se inserida.
    • Os parênteses em torno de (A:A) são necessários para forçar o A:A a permanecer em sua própria linha.
    • Para pontuações idênticas, a fórmula retorna os nomes em ordem de linha crescente. A fórmula pode ser modificada para retornar os nomes em ordem decrescente usando o número de linhas simples em vez do complemento dez do número da linha ao modificar as pontuações:
    {=INDEX(A:A,MOD(LARGE(10^5*B2:B5+ROW(B2:B5),{1;2;3}),10^5))}
    
    • É até possível aleatorizar a ordem de pontuações idênticas, gerando uma pontuação modificada com um número aleatório entre a pontuação e o número da linha.
    por 05.06.2018 / 17:39