☰

Contando uma coluna se uma string específica estiver em outra

tag-icon • tag-icon microsoft-excel worksheet-function
Contando uma coluna se uma string específica estiver em outra

Eu tenho uma coluna de classificações e preciso determinar a contagem de cada classificação (1-5), desde que sua tag (coluna 1) seja igual à tag especificada na tabela de saída. Isso deve ser colocado em uma tabela separada em outra folha, mostrando a porcentagem total de cada classificação. Pelas imagens abaixo, inseri as porcentagens manualmente, mas desejo usar uma fórmula para fazer isso automaticamente. Qualquer ajuda seria apreciada. Desde já, obrigado.

Tabela de saída:

tabela de saída

Coluna de entrada:

coluna de entrada

Responder1

Este seria o momento perfeito para aprender sobre tabelas dinâmicas. Dê aos seus dados alguns títulos que descrevam as colunas, por exemplo, "Equipe" e "Classificação". Em seguida, selecione os dados e títulos e vá em Inserir > Tabela Dinâmica.

Em seguida arraste o "Team" para a área de linhas e "Rank" para a área de colunas, depois "Team" para a área de valores (sugiro Team pois não é numérico, então o padrão será contar quantas vezes cada time teve cada classificação que você deseja - se você escolher usar Classificação, o padrão será adicioná-los, o que você não deseja e terá que mudar de Soma para Contagem).

Isso apenas conta quantas vezes cada equipe ficou em 1º, 2º, 3º etc. Clique com o botão direito em um dos resultados e selecione "Mostrar valores como>" e depois "% da linha". Você pode desativar os totais gerais, mas deixo um exercício para você descobrir (dica: Tabelas Dinâmicas> Faixa de Design). Espero que isso ajude e ajude você a começar com a incrível magia sem fórmulas das Tabelas Dinâmicas!

Responder2

Para as linhas e colunas do seu exemplo, e assumindo que sua planilha de entrada e sua planilha de saída são chamadas de “Entrada” e “Saída”, respectivamente,

  • Na planilha “Saída”, preencha as células B2:F2com 1até 5e insira AB1e CD2em A3e A4. Se você precisar que eles sejam gerados automaticamente a partir da planilha “Entrada”, informe-o.

  • na célula B3da planilha “Saída”, digite

    =COUNTIFS(Input!$A:$A, Output!$A3, Input!$B:$B, Output!B$2) / COUNTIFS(Input!$A:$A, Output!$A3)

  • arraste/preencha para a direita na Coluna F e para baixo na Linha 4, e

  • formato como porcentagem.

Explicação:

  • O denominador (a parte à direita de /) é

    COUNTIFS(Input!$A:$A, Output!$A3)

    Isso conta o número de linhas na planilha de entrada onde a coluna A contém a “tag” da coluna A da linha atual da planilha de saída. Para  Output!$A3 =  AB1, isso é 6.

  • O numerador (a parte à esquerda do /) é

    COUNTIFS(Input!$A:$A, Output!$A3, Input!$B:$B, Output!B$2)

    Este é um refinamento do acima; conta o número de linhas na planilha de entrada, onde a coluna A contém a “tag” da coluna A da linha atual da planilha de saída e a coluna B contém o número da linha 2 da coluna atual da planilha de saída. Para  Output!$A3 =  AB1e  Output!B$2 =  1, isso é 3.

  • Portanto o resultado da divisão é 3/6 = 0,5 = 50%.

informação relacionada