Classificar Tabela Dinâmica do Excel por Porcentagem de Contagem

Question

Eu tenho dados de origem que mostram aprovações de quadro de horários no formato a seguir (para cerca de 850 funcionários e 200 gerentes):

  Employee Name  Manager Name  TS Approved? 
  Employee 1     Manager 1     No
  Employee 2     Manager 2     Yes
  Employee 3     Manager 3     Yes
  Employee 4     Manager 1     No
  Employee 5     Manager 3     No

Criei uma tabela dinâmica da seguinte maneira (o% não aprovado é apenas uma fórmula que tenho ao lado da tabela dinâmica):

                 Count TS Approved? 
  Manager Name   No    Yes   Total  % Unapproved
  Manager 1      11          11     100%
  Manager 2      6     10    16     38%
  Manager 3      7     18    25     28%
  Manager 4      5     8     13     38%
  Manager 5      5     4     9      56%
  Manager 6            3     3      0%
  Manager 7      5           5      100%

Eu preciso classificar para obter os 5 piores aprovadores por contagem, mas apenas 5. Meus problemas são:

• Se eu usar a tabela dinâmica "Top 10" na coluna "Não", ele mostrará 6 valores, pois não diferencia entre os três 5s
• Eu tentei adicionar o percentual para poder classificar Maiores - Menores em%, depois Maiores - Menores na contagem, e então apenas entre os 5 melhores - já que 5/5 (100%) não aprovados são piores que 5/8 (38 %) - mas não sabe como classificar em%.
• Se eu adicioná-lo como uma fórmula fora da tabela dinâmica (como acima), o Excel não me permitirá classificar a tabela dinâmica com base nesses dados. 'Você não pode mover parte de um Relatório de Tabela Dinâmica ...'
• Se eu adicionar os dados para mostrar como "% do total da linha pai" na tabela, ele só será classificado na contagem

Alguém pode pensar em como posso fazer o que eu quero, ou seja?

                 Count TS Approved? 
  Manager Name   No    Yes   Total  % Unapproved
  Manager 1      11          11     100%
  Manager 3      7     18    25     28%
  Manager 2      6     10    16     38%
  Manager 7      5           5      100%
  Manager 5      5     4     9      56%
  Manager 4      5     8     13     38%
  Manager 6            3     3      0%

Observação: posso fazer isso com bastante facilidade usando countifs em vez de uma tabela dinâmica, mas o ideal é que o formato da tabela dinâmica seja possível.

Obrigado!

Louise

    

Answer 1

Desafio interessante. Alguns dos problemas incluem:

• Os cálculos de campo não têm flexibilidade suficiente para conseguir o que você precisa
• Embora você possa exibir números como% do total, e parece que você pode classificá-los - ele realmente classifica os números subjacentes.

Eu tenho uma solução que faz uso de tabelas e tabela dinâmica. Pode haver uma solução mais simples disponível. As etapas são (feitas no Excel 2016):

1. Selecione dentro de seus dados brutos. Selecione a faixa "Inserir" e clique em "Tabela"
2. Na sua nova tabela, insira um cálculo para% NotApproved
3. Selecione a faixa "Design de ferramentas" "Ferramentas de tabela" e clique em "Resumir com tabela dinâmica"
4. Construa uma tabela dinâmica simples com o nome do gerente como as linhas e% NotApproved como os valores.
5. Classifique os nomes dos gerentes em ordem decrescente por% NotApproved

Aqui está um exemplo. A seguir, um snippet de 30 linhas de "dados brutos" semelhantes aos descritos na sua pergunta ...

Selecioneafaixa"Inserir" e clique em "Tabela" ...

Vocêobtémmelhoresdadosformatados.SelecioneD1,próximoaotítulodaúltimacolunaedigite"% No" - isso cria uma nova coluna na tabela com um novo título. Na célula D2, digite a seguinte fórmula ...

=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100

Quando você pressiona enter, ele é automaticamente preenchido na tabela. Esta fórmula faz:

1. IF([@[TS Approved?]]="No",1,0) Se a planilha de horas aprovada for "Não", obtenha um valor de 1.
2. COUNTIF([Manager Name],"="&[@[Manager Name]]) Determina quantas vezes o gerente nessa linha aparece na tabela.
3. Resultado de 1 dividido pelo resultado de 2 vezes 100

A tabela agora se parece com isso ...

Selecione"Ferramentas de Tabela", "Design" Ribbon e clique em Resumir com Tabela Dinâmica. Construa a tabela Pivot para ficar assim ...

...eclassifique-o...

...paraobterisso...

Embora pareça um monte de etapas para configurar, é muito fácil manter a Tabela e isso mantém automaticamente a Tabela Dinâmica mantida.

    

Answer 2

Não sei porque, mas eu percebi duas coisas no café da manhã esta manhã ...

1. Usar uma tabela é bom, mas talvez apenas complique o problema.
2. Embora você calcule sua% reprovada como a% de quadros de horários pelos quais o gerente é responsável, convém calculá-la como a porcentagem de todas as planilhas de horas não aprovadas.

Então, pensei em postar uma resposta alternativa.

Além de seus dados brutos, coloque um cabeçalho %No e esse cálculo abaixo (e preencha).

=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100

A fórmula calcula, se este quadro de horários não for aprovado, a porcentagem de todos os quadros de horários não aprovados.

Seus dados brutos agora são assim ...

Criesuatabeladinâmicaeclassifiqueem%Não.

Sevocêaindaquiserque%nãoaprovadoseja%dequadrosdetempopelosquaisogerenteéresponsável,useestaequaçãonacolunaD.

=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100